How can i execute a SQL statement after module installation and a different sql statement after every modul update?
Odoo is the world's easiest all-in-one management software.
It includes hundreds of business apps:
- CRM
- e-Commerce
- Accounting
- Inventory
- PoS
- Project management
- MRP
This question has been flagged
7
Replies
63381
Views
You can execute Model's method upon module's install or upgrade. Here's an example:
First, add the method that will do the initialization actions. For example:
class crm_routing_users(osv.osv):
"""For each Department and Role, which user is the responsible"""
_name = "crm.routing.users"
def _migrate_from_project_issue_profiling(self, cr, uid, ids=None, context=None):
"""Migrate from project.issue.profiling. since this module can completely replace it."""
if ids is not None:
raise NotImplementedError("Ids is just there by convention! Please don't use it.")
cr.execute("select exists(select * from information_schema.tables " "where table_name=project_issue_profiling)")
if cr.fetchone()[0]:
cr.execute(""" INSERT INTO crm_routing_users ( create_uid, create_date, write_date, write_uid, notes, user_id, section_id, department_id) SELECT create_uid, create_date, write_date, write_uid, notes, user_id, section_id, department_id FROM project_issue_profiling""")
_logger.info("Successful data copy from project.issue.profiling to crm.routing.users")
return True
Next, declare the call in an XML data file:
<openerp>
<data>
<!-- Migration for "project_service_profiling". Can be skipped if that module is not in use. -->
<function model="crm.routing.users" name="_migrate_from_project_issue_profiling"/>
</data>
</openerp>
EDIT:
Finally, add the XML data file to tour _openerp_.py manifest, in the data (v7) section. If you want it to run only on first install, add noupdate="1" to the data tag.
Are you sure there could be an init section in module manifest? Please check my comment on GEM's answer below. I think there is not init section and there is no difference between xml declaration using init_xml and update_xml attributes. At least in v7.0. Using noupdate="1" on data tag will run function tags only at module install.
Hi,
for the answer just create a file.sql with your requests inside, exemple :
DELETE FROM ir_ui_view_sc WHERE user_id = 1 and name = 'Clients';
add in your module_name/__openerp__.py :
in section
'update_xml': ["file.sql",],
for v7 :
'data': ["file.sql",],
use insert , update ... Bye
Regarding .xml and .sql files, there is no difference between module attributes init_xml, update_xml and data. The file will always be updated/executed on any module installation or update. Check the load_module_graph() function in openerp/modules/loading.py
You can create a method init(self, cr) in your model and it will be called upon module installation or upgrade.
You can check for update or install mode by checking the the existence of data or structures that should have been created in initialization or you can just use common sql or replace mechanism to use the same sql for both states.
Most reports use this method to create required views. Check this example from addons/account/report/account_report:
class report_account_receivable(osv.osv):
_name = "report.account.receivable"
_description = "Receivable accounts"
_auto = False
_columns = {
'name': fields.char('Week of Year', size=7, readonly=True),
'type': fields.selection(_code_get, 'Account Type', required=True),
'balance':fields.float('Balance', readonly=True),
'debit':fields.float('Debit', readonly=True),
'credit':fields.float('Credit', readonly=True),
}
_order = 'name desc'
def init(self, cr):
tools.drop_view_if_exists(cr, 'report_account_receivable')
cr.execute("""
create or replace view report_account_receivable as (
select
min(l.id) as id,
to_char(date,'YYYY:IW') as name,
sum(l.debit-l.credit) as balance,
sum(l.debit) as debit,
sum(l.credit) as credit,
a.type
from
account_move_line l
left join
account_account a on (l.account_id=a.id)
where
l.state <> 'draft'
group by
to_char(date,'YYYY:IW'), a.type
)""")
I have just to create report by using sql query (the same with above). But I have the problem: If I execute the query by postgreSQL maestro, there are 600 records. By postgreSQL Maestro, I open the view there are also 600 records. But the tree view in Openerp V7 there are only 19 records. Anyone help me?
As long as we write less SQL sentences, the ORM can make more optimizations for us in the future and the maintenance will be easier.
There are cases where you have made strong changes to your models already used in production and the ORM can't make the automatic inference of the sql statements needed to give the database a logical state corresponding to your new code (ex: fix your table structure, move data between columns, calculate quickly default values for new not null columns using sql expressions, etc..).If your case is one of those data maintenance problems, you can use the migration scripts as specified in the MigrationManager descriptions: http://bit.ly/1UqiO8h.This mechanism is used extensively inside openupgrade: http://bit.ly/1QDc40R You can find a lot of samples there.
In your case, just create a migration folder with a -pre or -post update script:
<moduledir>
`-- migrations
|-- 8.0
| |-- pre-migration.py
In the pre-migration.py script you can check the version and put both queries. The first two digits passed to the script is always the odoo version the others come from your module version ( __openerp__.py of your module)
You can install the module specifying one version 1.1 and then change the module version to 1.2, 1.3, etc... in
def migrate(cr, version):
if version == '8.0.1.1':
cr.execute("""UPDATE ... FROM account_analytic_journal WHERE .... """)
else
cr.execute(anothersql)
