openerp-community team mailing list archive
-
openerp-community team
-
Mailing list archive
-
Message #01262
lp:~openerp-community/openobject-extension/import_odbc_base_external_dbsource into lp:openobject-extension
Daniel Reis (SECURITAS SA) has proposed merging lp:~openerp-community/openobject-extension/import_odbc_base_external_dbsource into lp:openobject-extension.
Requested reviews:
extra-addons-commiter (extra-addons-commiter)
For more details, see:
https://code.launchpad.net/~openerp-community/openobject-extension/import_odbc_base_external_dbsource/+merge/124158
import_odbc is now fixed and base_external_dbsource was improved and tested.
--
https://code.launchpad.net/~openerp-community/openobject-extension/import_odbc_base_external_dbsource/+merge/124158
Your team OpenERP Community is subscribed to branch lp:~openerp-community/openobject-extension/import_odbc_base_external_dbsource.
=== added directory 'base_external_dbsource'
=== added file 'base_external_dbsource/__init__.py'
--- base_external_dbsource/__init__.py 1970-01-01 00:00:00 +0000
+++ base_external_dbsource/__init__.py 2012-09-13 11:09:25 +0000
@@ -0,0 +1,24 @@
+# -*- coding: utf-8 -*-
+##############################################################################
+#
+# Daniel Reis
+# 2011
+#
+# This program is free software: you can redistribute it and/or modify
+# it under the terms of the GNU Affero General Public License as
+# published by the Free Software Foundation, either version 3 of the
+# License, or (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU Affero General Public License for more details.
+#
+# You should have received a copy of the GNU Affero General Public License
+# along with this program. If not, see <http://www.gnu.org/licenses/>.
+#
+##############################################################################
+
+import base_external_dbsource
+
+# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:
=== added file 'base_external_dbsource/__openerp__.py'
--- base_external_dbsource/__openerp__.py 1970-01-01 00:00:00 +0000
+++ base_external_dbsource/__openerp__.py 2012-09-13 11:09:25 +0000
@@ -0,0 +1,62 @@
+# -*- coding: utf-8 -*-
+##############################################################################
+#
+# Daniel Reis, 2011
+# Additional contributions by Maxime Chambreuil, Savoir-faire Linux
+#
+# This program is free software: you can redistribute it and/or modify
+# it under the terms of the GNU Affero General Public License as
+# published by the Free Software Foundation, either version 3 of the
+# License, or (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU Affero General Public License for more details.
+#
+# You should have received a copy of the GNU Affero General Public License
+# along with this program. If not, see <http://www.gnu.org/licenses/>.
+#
+##############################################################################
+
+{
+ 'name': 'External Database Sources',
+ 'version': '61.3',
+ 'category': 'Tools',
+ 'description': """
+This module allows you to define connections to foreign databases using ODBC,
+Oracle Client or SQLAlchemy.
+
+Databases sources can be configured in Settings > Configuration -> Data sources.
+
+Depending on the database, you need:
+ * to install unixodbc and python-pyodbc packages to use ODBC connections.
+ * to install FreeTDS driver (tdsodbc package) and configure it through ODBC to
+ connect to Microsoft SQL Server.
+ * to install and configure Oracle Instant Client and cx_Oracle python library
+ to connect to Oracle.
+ """,
+ 'author': 'Daniel Reis',
+ 'website': 'http://launchpad.net/addons-tko',
+ 'images': [
+ 'images/screenshot01.png',
+ ],
+ 'depends': [
+ 'base',
+ ],
+ 'init': [],
+ 'data': [
+ 'base_external_dbsource_view.xml',
+ 'security/ir.model.access.csv',
+ ],
+ 'demo': [
+ 'base_external_dbsource_demo.xml',
+ ],
+ 'test': [
+ 'dbsource_connect.yml',
+ ],
+ 'installable': True,
+ 'active': False,
+}
+
+# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:
=== added file 'base_external_dbsource/base_external_dbsource.py'
--- base_external_dbsource/base_external_dbsource.py 1970-01-01 00:00:00 +0000
+++ base_external_dbsource/base_external_dbsource.py 2012-09-13 11:09:25 +0000
@@ -0,0 +1,159 @@
+# -*- coding: utf-8 -*-
+##############################################################################
+#
+# Daniel Reis
+# 2011
+#
+# This program is free software: you can redistribute it and/or modify
+# it under the terms of the GNU Affero General Public License as
+# published by the Free Software Foundation, either version 3 of the
+# License, or (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU Affero General Public License for more details.
+#
+# You should have received a copy of the GNU Affero General Public License
+# along with this program. If not, see <http://www.gnu.org/licenses/>.
+#
+##############################################################################
+
+import os
+from osv import fields, osv
+from openerp.tools.translate import _
+import openerp.tools as tools
+import logging
+_logger = logging.getLogger(__name__)
+
+CONNECTORS = []
+
+try:
+ import sqlalchemy
+ import pymssql
+ CONNECTORS.append( ('mssql', 'Microsoft SQL Server') )
+except:
+ _logger.info('MS SQL Server not available. Please install "slqalchemy" and "pymssql" python package.')
+
+try:
+ import sqlalchemy
+ import MySQLdb
+ CONNECTORS.append( ('mysql', 'MySQL') )
+except:
+ _logger.info('MySQL not available. Please install "slqalchemy" and "mysqldb" python package.')
+
+try:
+ import pyodbc
+ CONNECTORS.append( ('pyodbc', 'ODBC') )
+except:
+ _logger.info('ODBC libraries not available. Please install "unixodbc" and "python-pyodbc" packages.')
+
+try:
+ import cx_Oracle
+ CONNECTORS.append( ('cx_Oracle', 'Oracle') )
+except:
+ _logger.info('Oracle libraries not available. Please install "cx_Oracle" python package.')
+
+import psycopg2
+CONNECTORS.append( ('postgresql', 'PostgreSQL') )
+
+try:
+ import sqlalchemy
+ CONNECTORS.append( ('sqlite', 'SQLite') )
+except:
+ _logger.info('SQLAlchemy not available. Please install "slqalchemy" python package.')
+
+class base_external_dbsource(osv.osv):
+ _name = "base.external.dbsource"
+ _description = 'External Database Sources'
+ _columns = {
+ 'name': fields.char('Datasource name', required=True, size=64),
+ 'conn_string': fields.text('Connection string', help="""\
+Sample connection strings:
+- Microsoft SQL Server: mssql+pymssql://username:%s@server:port/dbname?charset=utf8
+- MySQL: mysql://user:%s@server:port/dbname
+- ODBC: DRIVER={FreeTDS};SERVER=server.address;Database=mydb;UID=sa
+- ORACLE: username/%s@//server.address:port/instance
+- PostgreSQL: dbname='template1' user='dbuser' host='localhost' port='5432' password=%s
+- SQLite: sqlite:///test.db
+"""),
+ 'password': fields.char('Password' , size=40),
+ 'connector': fields.selection(CONNECTORS, 'Connector', required=True,
+ help = "If a connector is missing from the list, check the " \
+ + "server log to confirm that the required componentes were detected."),
+ }
+
+ def conn_open(self, cr, uid, id1):
+ #Get dbsource record
+ data = self.browse(cr, uid, id1)
+ #Build the full connection string
+ connStr = data.conn_string
+ if data.password:
+ if '%s' not in data.conn_string:
+ connStr += ';PWD=%s'
+ connStr = connStr % data.password
+ #Try to connect
+ if data.connector == 'cx_Oracle':
+ os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.UTF8'
+ conn = cx_Oracle.connect(connStr)
+ elif data.connector == 'pyodbc':
+ conn = pyodbc.connect(connStr)
+ elif data.connector in ('sqlite','mysql','mssql'):
+ conn = sqlalchemy.create_engine(connStr).connect()
+ elif data.connector == 'postgresql':
+ conn = psycopg2.connect(connStr)
+
+ return conn
+
+ def execute(self, cr, uid, ids, sqlquery, sqlparams=None, metadata=False, context=None):
+ """Executes SQL and returns a list of rows.
+
+ "sqlparams" can be a dict of values, that can be referenced in the SQL statement
+ using "%(key)s" or, in the case of Oracle, ":key".
+ Example:
+ sqlquery = "select * from mytable where city = %(city)s and date > %(dt)s"
+ params = {'city': 'Lisbon', 'dt': datetime.datetime(2000, 12, 31)}
+
+ If metadata=True, it will instead return a dict containing the rows list and the columns list,
+ in the format:
+ { 'cols': [ 'col_a', 'col_b', ...]
+ , 'rows': [ (a0, b0, ...), (a1, b1, ...), ...] }
+ """
+ data = self.browse(cr, uid, ids)
+ rows, cols = list(), list()
+ for obj in data:
+ conn = self.conn_open(cr, uid, obj.id)
+ if obj.connector in ["sqlite","mysql","mssql"]:
+ #using sqlalchemy
+ cur = conn.execute(sqlquery, sqlparams)
+ if metadata: cols = cur.keys()
+ rows = [r for r in cur]
+ else:
+ #using other db connectors
+ cur = conn.cursor()
+ cur.execute(sqlquery, sqlparams)
+ if metadata: cols = [d[0] for d in cur.description]
+ rows = cur.fetchall()
+ conn.close()
+ if metadata:
+ return{'cols': cols, 'rows': rows}
+ else:
+ return rows
+
+ def connection_test(self, cr, uid, ids, context=None):
+ for obj in self.browse(cr, uid, ids, context):
+ conn = False
+ try:
+ conn = self.conn_open(cr, uid, obj.id)
+ except Exception, e:
+ raise osv.except_osv(_("Connection test failed!"), _("Here is what we got instead:\n %s") % tools.ustr(e))
+ finally:
+ try:
+ if conn: conn.close()
+ except Exception:
+ # ignored, just a consequence of the previous exception
+ pass
+ #TODO: if OK a (wizard) message box should be displayed
+ raise osv.except_osv(_("Connection test succeeded!"), _("Everything seems properly set up!"))
+
+base_external_dbsource()
=== added file 'base_external_dbsource/base_external_dbsource_demo.xml'
--- base_external_dbsource/base_external_dbsource_demo.xml 1970-01-01 00:00:00 +0000
+++ base_external_dbsource/base_external_dbsource_demo.xml 2012-09-13 11:09:25 +0000
@@ -0,0 +1,15 @@
+<?xml version="1.0"?>
+<openerp>
+ <data>
+
+ <record model="base.external.dbsource" id="demo_postgre">
+ <field name="name">PostgreSQL local</field>
+ <field name="conn_string">dbname='postgres' password=%s</field>
+ <field name="password">postgresql</field>
+ <field name="connector">postgresql</field>
+ </record>
+
+ </data>
+</openerp>
+
+
=== added file 'base_external_dbsource/base_external_dbsource_view.xml'
--- base_external_dbsource/base_external_dbsource_view.xml 1970-01-01 00:00:00 +0000
+++ base_external_dbsource/base_external_dbsource_view.xml 2012-09-13 11:09:25 +0000
@@ -0,0 +1,54 @@
+<?xml version="1.0"?>
+<openerp>
+ <data>
+
+ <!-- DBSource -->
+
+ <record model="ir.ui.view" id="view_dbsource_tree">
+ <field name="name">base.external.dbsource.tree</field>
+ <field name="model">base.external.dbsource</field>
+ <field name="type">tree</field>
+ <field name="arch" type="xml">
+ <tree>
+ <field name="name"/>
+ <field name="connector"/>
+ <field name="conn_string"/>
+ </tree>
+ </field>
+ </record>
+
+ <record model="ir.ui.view" id="view_dbsource_form">
+ <field name="name">base.external.dbsource.form</field>
+ <field name="model">base.external.dbsource</field>
+ <field name="type">form</field>
+ <field name="arch" type="xml">
+ <form>
+ <field name="name"/>
+ <field name="password" password="True"/>
+ <newline/>
+ <field name="connector" colspan="2"/>
+ <newline/>
+ <field name="conn_string" colspan="4"/>
+ <newline/>
+ <button name="connection_test" string="Test Connection" type="object" icon="gtk-network" colspan="4"/>
+ </form>
+ </field>
+ </record>
+
+ <record model="ir.actions.act_window" id="action_dbsource">
+ <field name="name">External Database Sources</field>
+ <field name="res_model">base.external.dbsource</field>
+ <field name="view_type">form</field>
+ <field name="view_mode">tree,form</field>
+ <field name="view_id" ref="view_dbsource_tree"/>
+ </record>
+
+ <menuitem name="Database Sources"
+ id="menu_dbsource"
+ parent="base.next_id_15"
+ action="action_dbsource"/>
+
+ </data>
+</openerp>
+
+
=== added directory 'base_external_dbsource/images'
=== added file 'base_external_dbsource/images/screenshot01.png'
Binary files base_external_dbsource/images/screenshot01.png 1970-01-01 00:00:00 +0000 and base_external_dbsource/images/screenshot01.png 2012-09-13 11:09:25 +0000 differ
=== added directory 'base_external_dbsource/security'
=== added file 'base_external_dbsource/security/ir.model.access.csv'
--- base_external_dbsource/security/ir.model.access.csv 1970-01-01 00:00:00 +0000
+++ base_external_dbsource/security/ir.model.access.csv 2012-09-13 11:09:25 +0000
@@ -0,0 +1,2 @@
+id,name,model_id:id,group_id:id,perm_read,perm_write,perm_create,perm_unlink
+access_base_external_dbsource_group_system,bae_external_dbsource_group_system,model_base_external_dbsource,base.group_system,1,1,1,1
=== added directory 'base_external_dbsource/test'
=== added file 'base_external_dbsource/test/dbsource_connect.yml'
--- base_external_dbsource/test/dbsource_connect.yml 1970-01-01 00:00:00 +0000
+++ base_external_dbsource/test/dbsource_connect.yml 2012-09-13 11:09:25 +0000
@@ -0,0 +1,5 @@
+-
+ Connect to local Postgres.
+-
+ !python {model: base.external.dbsource}: |
+ self.connection_test(cr, uid, [ref("demo_postgresql")]
=== added directory 'import_odbc'
=== added file 'import_odbc/__init__.py'
--- import_odbc/__init__.py 1970-01-01 00:00:00 +0000
+++ import_odbc/__init__.py 2012-09-13 11:09:25 +0000
@@ -0,0 +1,24 @@
+# -*- coding: utf-8 -*-
+##############################################################################
+#
+# Daniel Reis
+# 2011
+#
+# This program is free software: you can redistribute it and/or modify
+# it under the terms of the GNU Affero General Public License as
+# published by the Free Software Foundation, either version 3 of the
+# License, or (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU Affero General Public License for more details.
+#
+# You should have received a copy of the GNU Affero General Public License
+# along with this program. If not, see <http://www.gnu.org/licenses/>.
+#
+##############################################################################
+
+import import_odbc
+
+# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:
=== added file 'import_odbc/__openerp__.py'
--- import_odbc/__openerp__.py 1970-01-01 00:00:00 +0000
+++ import_odbc/__openerp__.py 2012-09-13 11:09:25 +0000
@@ -0,0 +1,84 @@
+# -*- coding: utf-8 -*-
+##############################################################################
+#
+# Daniel Reis
+# 2011
+#
+# This program is free software: you can redistribute it and/or modify
+# it under the terms of the GNU Affero General Public License as
+# published by the Free Software Foundation, either version 3 of the
+# License, or (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU Affero General Public License for more details.
+#
+# You should have received a copy of the GNU Affero General Public License
+# along with this program. If not, see <http://www.gnu.org/licenses/>.
+#
+##############################################################################
+
+{
+ 'name': 'Import data from SQL and ODBC data sources.',
+ 'version': '61.3',
+ 'category': 'Tools',
+ 'description': """
+Import data directly from other databases.
+
+Installed in the Administration module, menu Configuration -> Import from SQL.
+
+Features:
+ * Fetched data from the databases are used to build lines equivalent to regular import files. These are imported using the standard "import_data()" ORM method, benefiting from all its features, including xml_ids.
+ * Each table import is defined by an SQL statement, used to build the equivalent for an import file. Each column's name should match the column names you would use in an import file. The first column must provide an unique identifier for the record, and will be used to build its xml_id.
+ * SQL columns named "none" are ignored. This can be used for the first column of the SQL, so that it's used to build the XML Id but it's not imported to any OpenERP field.
+ * The last sync date is the last successfull execution can be used in the SQL using "%(sync)s", or ":sync" in the case of Oracle.
+ * When errors are found, only the record with the error fails import. The other correct records are commited. However, the "last sync date" will only be automaticaly updated when no errors are found.
+ * The import execution can be scheduled to run automatically.
+
+Examples:
+ * Importing suppliers to res.partner:
+ SELECT distinct
+ [SUPPLIER_CODE] as "ref"
+ , [SUPPLIER_NAME] as "name"
+ , 1 as "is_supplier"
+ , [INFO] as "comment"
+ FROM T_SUPPLIERS
+ WHERE INACTIVE_DATE IS NULL and DATE_CHANGED >= %(sync)s
+
+ * Importing products to product.product:
+ SELECT PRODUCT_CODE as "ref"
+ , PRODUCT_NAME as "name"
+ , 'res_partner_id_'+SUPPLIER_ID as "partner_id/id"
+ FROM T_PRODUCTS
+ WHERE DATE_CHANGED >= %(sync)s
+
+Improvements ideas waiting for a contributor:
+ * Allow to import many2one fields (currently not supported). Done by adding a second SQL sentence to get child record list?
+ * Allow "import sets" that can be executed at different time intervals using different scheduler jobs.
+ * Allow to inactivate/delete OpenERP records when not present in an SQL result set.
+ """,
+ 'author': 'Daniel Reis',
+ 'website': 'http://launchpad.net/addons-tko',
+ 'images': [
+ 'images/snapshot1.png',
+ 'images/snapshot2.png',
+ ],
+ 'depends': [
+ 'base',
+ 'base_external_dbsource',
+ ],
+ 'init': [],
+ 'data': [
+ 'import_odbc_view.xml',
+ 'security/ir.model.access.csv',
+ ],
+ 'demo': [
+ 'import_odbc_demo.xml',
+ ],
+ 'test': [],
+ 'installable': True,
+ 'active': False,
+}
+
+# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:
=== added directory 'import_odbc/images'
=== added file 'import_odbc/images/snapshot1.png'
Binary files import_odbc/images/snapshot1.png 1970-01-01 00:00:00 +0000 and import_odbc/images/snapshot1.png 2012-09-13 11:09:25 +0000 differ
=== added file 'import_odbc/images/snapshot2.png'
Binary files import_odbc/images/snapshot2.png 1970-01-01 00:00:00 +0000 and import_odbc/images/snapshot2.png 2012-09-13 11:09:25 +0000 differ
=== added file 'import_odbc/import_odbc.py'
--- import_odbc/import_odbc.py 1970-01-01 00:00:00 +0000
+++ import_odbc/import_odbc.py 2012-09-13 11:09:25 +0000
@@ -0,0 +1,214 @@
+# -*- coding: utf-8 -*-
+##############################################################################
+#
+# Daniel Reis
+# 2011
+#
+# This program is free software: you can redistribute it and/or modify
+# it under the terms of the GNU Affero General Public License as
+# published by the Free Software Foundation, either version 3 of the
+# License, or (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU Affero General Public License for more details.
+#
+# You should have received a copy of the GNU Affero General Public License
+# along with this program. If not, see <http://www.gnu.org/licenses/>.
+#
+##############################################################################
+
+import sys
+from datetime import datetime
+from osv import fields, osv
+import logging
+_logger = logging.getLogger(__name__)
+_loglvl = _logger.getEffectiveLevel()
+SEP = '|'
+
+class import_odbc_dbtable(osv.osv):
+ _name="import.odbc.dbtable"
+ _description = 'Import Table Data'
+ _order = 'exec_order'
+ _columns = {
+ 'name': fields.char('Datasource name', required=True, size=64),
+ 'enabled': fields.boolean('Execution enabled'),
+ 'dbsource_id': fields.many2one('base.external.dbsource', 'Database source', required=True),
+ 'sql_source': fields.text('SQL', required=True, help='Column names must be valid "import_data" columns.'),
+ 'model_target': fields.many2one('ir.model','Target object'),
+ 'noupdate': fields.boolean('No updates', help="Only create new records; disable updates to existing records."),
+ 'exec_order': fields.integer('Execution order', help="Defines the order to perform the import"),
+ 'last_sync': fields.datetime('Last sync date', help="Datetime for the last succesfull sync. Later changes on the source may not be replicated on the destination"),
+ 'start_run': fields.datetime('Time started', readonly=True),
+ 'last_run': fields.datetime('Time ended', readonly=True),
+ 'last_record_count': fields.integer('Last record count', readonly=True),
+ 'last_error_count': fields.integer('Last error count', readonly=True),
+ 'last_warn_count': fields.integer('Last warning count', readonly=True),
+ 'last_log': fields.text('Last run log', readonly=True),
+ 'ignore_rel_errors': fields.boolean('Ignore relationship errors',
+ help = "On error try to reimport rows ignoring relationships."),
+ 'raise_import_errors': fields.boolean('Raise import errors',
+ help = "Import errors not handled, intended for debugging purposes."
+ + "\nAlso forces debug messages to be written to the server log."),
+ }
+ _defaults = {
+ 'enabled': True,
+ 'exec_order': 10,
+ }
+
+ def _import_data(self, cr, uid, flds, data, model_obj, table_obj, log):
+ """Import data and returns error msg or empty string"""
+
+ def find_m2o(field_list):
+ """"Find index of first column with a one2many field"""
+ for i, x in enumerate(field_list):
+ if len(x)>3 and x[-3:] == ':id' or x[-3:] == '/id':
+ return i
+ return -1
+
+ def append_to_log(log, level, obj_id = '', msg = '', rel_id = ''):
+ if '_id_' in obj_id:
+ obj_id = '.'.join(obj_id.split('_')[:-2]) + ': ' + obj_id.split('_')[-1]
+ if ': .' in msg and not rel_id:
+ rel_id = msg[msg.find(': .')+3:]
+ if '_id_' in rel_id:
+ rel_id = '.'.join(rel_id.split('_')[:-2]) + ': ' + rel_id.split('_')[-1]
+ msg = msg[:msg.find(': .')]
+ log['last_log'].append('%s|%s\t|%s\t|%s' % (level.ljust(5), obj_id, rel_id, msg))
+
+
+ _logger.debug( data )
+ cols = list(flds) #copy to avoid side effects
+ errmsg = str()
+ if table_obj.raise_import_errors:
+ model_obj.import_data(cr, uid, cols, [data], noupdate=table_obj.noupdate)
+ else:
+ try:
+ model_obj.import_data(cr, uid, cols, [data], noupdate=table_obj.noupdate)
+ except:
+ errmsg = str(sys.exc_info()[1])
+
+ if errmsg and not table_obj.ignore_rel_errors:
+ #Fail
+ append_to_log(log, 'ERROR', data, errmsg )
+ log['last_error_count'] += 1
+ return False
+ if errmsg and table_obj.ignore_rel_errors:
+ #Warn and retry ignoring many2one fields...
+ append_to_log(log, 'WARN', data, errmsg )
+ log['last_warn_count'] += 1
+ #Try ignoring each many2one (tip: in the SQL sentence select more problematic FKs first)
+ i = find_m2o(cols)
+ if i >= 0:
+ #Try again without the [i] column
+ del cols[i]
+ del data[i]
+ self._import_data(cr, uid, cols, data, model_obj, table_obj, log)
+ else:
+ #Fail
+ append_to_log(log, 'ERROR', data, 'Removed all m2o keys and still fails.' )
+ log['last_error_count'] += 1
+ return False
+
+ return True
+
+
+ def import_run(self, cr, uid, ids=None, context=None):
+
+ db_model = self.pool.get('base.external.dbsource')
+ actions = self.read(cr, uid, ids, ['id', 'exec_order'])
+ actions.sort(key = lambda x:(x['exec_order'], x['id']))
+
+ #Consider each dbtable:
+ for action_ref in actions:
+
+ obj = self.browse(cr, uid, action_ref['id'])
+ if not obj.enabled: continue #skip
+
+ _logger.setLevel(obj.raise_import_errors and logging.DEBUG or _loglvl)
+ _logger.debug('Importing %s...' % obj.name)
+
+ #now() microseconds are stripped to avoid problem with SQL smalldate
+ #TODO: convert UTC Now to local timezone (http://stackoverflow.com/questions/4770297/python-convert-utc-datetime-string-to-local-datetime)
+ model_name = obj.model_target.model
+ model_obj = self.pool.get(model_name)
+ xml_prefix = model_name.replace('.', '_') + "_id_"
+ log = {'start_run': datetime.now().replace(microsecond=0),
+ 'last_run': None,
+ 'last_record_count': 0,
+ 'last_error_count': 0,
+ 'last_warn_count': 0,
+ 'last_log': list()}
+ self.write(cr, uid, [obj.id], log)
+
+ #Prepare SQL sentence; replace "%s" with the last_sync date
+ if obj.last_sync: sync = datetime.strptime(obj.last_sync, "%Y-%m-%d %H:%M:%S")
+ else: sync = datetime.datetime(1900, 1, 1, 0, 0, 0)
+ params = {'sync': sync}
+ res = db_model.execute(cr, uid, [obj.dbsource_id.id], obj.sql_source, params, metadata=True)
+
+ #Exclude columns titled "None"; add (xml_)"id" column
+ cidx = [i for i, x in enumerate(res['cols']) if x.upper() != 'NONE']
+ cols = [x for i, x in enumerate(res['cols']) if x.upper() != 'NONE'] + ['id']
+
+ #Import each row:
+ for row in res['rows']:
+ #Build data row; import only columns present in the "cols" list
+ data = list()
+ for i in cidx:
+ #TODO: Handle imported datetimes properly - convert from localtime to UTC!
+ v = row[i]
+ if isinstance(v, str): v = v.strip()
+ data.append(v)
+ data.append( xml_prefix + str(row[0]).strip() )
+
+ #Import the row; on error, write line to the log
+ log['last_record_count'] += 1
+ self._import_data(cr, uid, cols, data, model_obj, obj, log)
+ if log['last_record_count'] % 500 == 0:
+ _logger.info('...%s rows processed...' % (log['last_record_count']) )
+
+ #Finished importing all rows
+ #If no errors, write new sync date
+ if not (log['last_error_count'] or log['last_warn_count']):
+ log['last_sync'] = log['start_run']
+ level = logging.DEBUG
+ if log['last_warn_count']: level = logging.WARN
+ if log['last_error_count']: level = logging.ERROR
+ _logger.log(level, 'Imported %s , %d rows, %d errors, %d warnings.' % (
+ model_name, log['last_record_count'], log['last_error_count'] ,
+ log['last_warn_count'] ) )
+ #Write run log, either if the table import is active or inactive
+ if log['last_log']:
+ log['last_log'].insert(0, 'LEVEL|== Line == |== Relationship ==|== Message ==')
+ log.update( {'last_log': '\n'.join(log['last_log'])} )
+ log.update({ 'last_run': datetime.now().replace(microsecond=0) }) #second=0,
+ self.write(cr, uid, [obj.id], log)
+ #Finished
+ _logger.debug('Import job FINISHED.')
+ return True
+
+
+ def import_schedule(self, cr, uid, ids, context=None):
+ cron_obj = self.pool.get('ir.cron')
+ new_create_id = cron_obj.create(cr, uid, {
+ 'name': 'Import ODBC tables',
+ 'interval_type': 'hours',
+ 'interval_number': 1,
+ 'numbercall': -1,
+ 'model': 'import.odbc.dbtable',
+ 'function': 'import_run',
+ 'doall': False,
+ 'active': True
+ })
+ return {
+ 'name': 'Import ODBC tables',
+ 'view_type': 'form',
+ 'view_mode': 'form,tree',
+ 'res_model': 'ir.cron',
+ 'res_id': new_create_id,
+ 'type': 'ir.actions.act_window',
+ }
+
+import_odbc_dbtable()
=== added file 'import_odbc/import_odbc_demo.xml'
--- import_odbc/import_odbc_demo.xml 1970-01-01 00:00:00 +0000
+++ import_odbc/import_odbc_demo.xml 2012-09-13 11:09:25 +0000
@@ -0,0 +1,15 @@
+<?xml version="1.0"?>
+<openerp>
+ <data>
+
+ <record model="import.odbc.dbtable" id="demo_postgresql_users">
+ <field name="name">Users from PostgreSQL </field>
+ <field name="dbsource_id" ref="base_external_dbsource.demo_postgre"/>
+ <field name="sql_source">select usename as "login", usename as "name" from pg_catalog.pg_user</field>
+ <field name="model_target" ref="base.model_res_users"/>
+ </record>
+
+ </data>
+</openerp>
+
+
=== added file 'import_odbc/import_odbc_view.xml'
--- import_odbc/import_odbc_view.xml 1970-01-01 00:00:00 +0000
+++ import_odbc/import_odbc_view.xml 2012-09-13 11:09:25 +0000
@@ -0,0 +1,86 @@
+<?xml version="1.0"?>
+<openerp>
+ <data>
+
+ <!-- Table form -->
+
+ <record model="ir.ui.view" id="view_import_dbtable_form">
+ <field name="name">import.odbc.dbtable.form</field>
+ <field name="model">import.odbc.dbtable</field>
+ <field name="type">form</field>
+ <field name="arch" type="xml">
+ <form>
+ <field name="name" search="1"/>
+ <field name="exec_order"/>
+ <field name="model_target"/>
+ <field name="dbsource_id" search="1"/>
+ <field name="noupdate"/>
+ <field name="enabled"/>
+ <field name="ignore_rel_errors"/>
+ <field name="raise_import_errors"/>
+ <field name="last_sync"/>
+ <group colspan="2">
+ <button name="import_run" string="Run Import" type="object" icon="gtk-execute"/>
+ <button name="import_schedule" string="Schedule Import" type="object" icon="gtk-paste"/>
+ </group>
+ <field name="sql_source" colspan="4"/>
+ <separator string="Last execution" colspan="4"/>
+ <field name="last_record_count"/>
+ <field name="start_run"/>
+ <field name="last_warn_count"/>
+ <field name="last_run"/>
+ <field name="last_error_count"/>
+ <field name="last_log" colspan="4"/>
+ </form>
+ </field>
+ </record>
+
+ <!-- Table Tree -->
+
+ <record id="view_import_dbtable_tree" model="ir.ui.view">
+ <field name="name">import.odbc.dbtable.tree</field>
+ <field name="model">import.odbc.dbtable</field>
+ <field name="type">tree</field>
+ <field name="arch" type="xml">
+ <tree colors="grey: enabled==False; red:last_error_count>0; blue:last_warn_count>0">
+ <field name="exec_order"/>
+ <field name="name"/>
+ <field name="model_target"/>
+ <field name="dbsource_id"/>
+ <field name="enabled"/>
+ <field name="last_run"/>
+ <field name="last_sync"/>
+ <field name="last_record_count"/>
+ <field name="last_error_count"/>
+ <field name="last_warn_count"/>
+ </tree>
+ </field>
+</record>
+
+
+<!-- Tree Search -->
+ <record id="view_import_dbtable_filter" model="ir.ui.view">
+ <field name="name">import.odbc.dbtable.filter</field>
+ <field name="model">import.odbc.dbtable</field>
+ <field name="type">search</field>
+ <field name="arch" type="xml">
+ <search string="Search ODBC Imports">
+ <field name="name"/>
+ <field name="dbsource_id"/>
+ <field name="model_target"/>
+ </search>
+ </field>
+ </record>
+
+<!--Menu-->
+ <record model="ir.actions.act_window" id="action_import_dbtable">
+ <field name="name">Import from SQL</field>
+ <field name="res_model">import.odbc.dbtable</field>
+ <field name="view_type">form</field>
+ </record>
+ <menuitem name="Import from SQL" id="menu_import_dbtable" parent="base.next_id_15" action="action_import_dbtable"/>
+</data>
+</openerp>
+
+
+
=== added directory 'import_odbc/security'
=== added file 'import_odbc/security/ir.model.access.csv'
--- import_odbc/security/ir.model.access.csv 1970-01-01 00:00:00 +0000
+++ import_odbc/security/ir.model.access.csv 2012-09-13 11:09:25 +0000
@@ -0,0 +1,2 @@
+id,name,model_id:id,group_id:id,perm_read,perm_write,perm_create,perm_unlink
+access_import_odbc_dbsource_group_system,import_odbc_dbtable_group_system,model_import_odbc_dbtable,base.group_system,1,1,1,1
Follow ups
