Merge lp:~openerp-community/server-env-tools/7.0-modules-from-openobject-extension into lp:~server-env-tools-core-editors/server-env-tools/7.0

[Maxime Chambreuil (http://www.savoirfairelinux.com)]

[ADD] import_odbc and base_external_dbsource from lp:openobject-extension/7.0

[Nicolas Bessi - Camptocamp (nbessi-c2c-deactivatedaccount)]

Hello,

Thanks for the revirew

Some comments below:

Please use explicit relative import: from . import

in __openerp__ remove the init key please it is unused

in base_external_source.py:

just a details but the order of import is not standard:
-atop should be standard lib call,
-below external used module,
-finally localy imported module

please use OpenERP/community convention to for the model:
from openerp.osv import orm, fields

class base_external_dbsource(orm.Model):

line 71: back slash is not needed
line 82 back slash an + is not needed

in conn_open
            if '%s' not in data.conn_string:
                connStr += ';PWD=%s'
I'm not sure this is a judicious choice.
What if password set in FREETDS config files

In function connexion_test you raise an exception to said that connection works.
It will be better to return an ir.act_windows.

A quick pass to a lynter to fix PEP8 will be nice.

In demo data used for yaml test

        <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>

Depends on postgres configuration. I have no better idea but a comment in test should be made to validate this first.

Thanks fro the patch. I will look if I can integrate it in connector_odbc.

Regards

Nicolas

review: Needs Fixing (code review, no tests)

[Daniel Reis (dreis-pt)]

Maxime: I can work on the cleanup of this code, if you like.

[Maxime Chambreuil (http://www.savoirfairelinux.com) (max3903)]

Thanks Daniel. Please go ahead.

[Maxime Chambreuil (http://www.savoirfairelinux.com) (max3903)]

Daniel ?

[Yannick Vaucher @ Camptocamp (yvaucher-c2c)]

Please remove executable right on those files (chmod -x):

base_external_dbsource/base_external_dbsource_demo.xml
base_external_dbsource/__init__.py
base_external_dbsource/security/ir.model.access.csv
base_external_dbsource/test/dbsource_connect.yml
import_odbc/import_odbc_demo.xml
import_odbc/__init__.py
import_odbc/security/ir.model.access.csv

base_external_dbsource.py|33 col 1| F401 'pymssql' imported but unused
base_external_dbsource.py|40 col 1| F811 redefinition of unused 'sqlalchemy' from line 32
base_external_dbsource.py|41 col 1| F401 'MySQLdb' imported but unused
base_external_dbsource.py|65 col 1| F811 redefinition of unused 'sqlalchemy' from line 40

base_external_dbsource.py|166 col 1| F821 undefined name 'osv'
base_external_dbsource.py|177 col 1| F821 undefined name 'osv'

Use orm.execpt_orm instead

And at least split the l.43 in import_odbc/import_odbc

Cheers

review: Needs Fixing (code review, no tests)

[Maxime Chambreuil (http://www.savoirfairelinux.com) (max3903)]

Thanks Yannick.

I made the changes according to your review.

[Yannick Vaucher @ Camptocamp (yvaucher-c2c)]

Thanks for the changes

LGTM

review: Approve (code review, no tests)

[Nicolas Bessi - Camptocamp (nbessi-c2c-deactivatedaccount)]

LGTM

review: Approve

[Alexandre Fayolle - camptocamp (alexandre-fayolle-c2c)]

I get an error when I try to install import_odbc:

2014-03-20 13:55:00,065 32374 ERROR test_ocb_oca70 openerp.tools.view_validation: Invalid XML: A `string` attribute must be on a `form` node.
2014-03-20 13:55:00,078 32374 ERROR test_ocb_oca70 openerp.tools.convert: Parse error in /home/afayolle/work/oca/server-env-tools/7.0/import_odbc/import_odbc_view.xml:7:
<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>
Traceback (most recent call last):
  File "/home/afayolle/work/oerp/openobject-server/ocb-7.0/openerp/tools/convert.py", line 847, in parse
    self._tags[rec.tag](self.cr, rec, n)
  File "/home/afayolle/work/oerp/openobject-server/ocb-7.0/openerp/tools/convert.py", line 814, in _tag_record
    id = self.pool.get('ir.model.data')._update(cr, self.uid, rec_model, self.module, res, rec_id or False, not self.isnoupdate(data_node), noupdate=self.isnoupdate(data_node), mode=self.mode, context=rec_context )
  File "/home/afayolle/work/oerp/server/ocb-7.0/openerp/addons/base/ir/ir_model.py", line 969, in _update
    res_id = model_obj.create(cr, uid, values, context=context)
  File "/home/afayolle/work/oerp/server/ocb-7.0/openerp/addons/base/ir/ir_ui_view.py", line 103, in create
    return super(view, self).create(cr, uid, values, context)
  File "/home/afayolle/work/oerp/openobject-server/ocb-7.0/openerp/osv/orm.py", line 4543, in create
    self._validate(cr, user, [id_new], context)
  File "/home/afayolle/work/oerp/openobject-server/ocb-7.0/openerp/osv/orm.py", line 1557, in _validate
    raise except_orm('ValidateError', '\n'.join(error_msgs))
except_orm: ('ValidateError', u'Error occurred while validating the field(s) arch: Invalid XML for View Architecture!')
Traceback (most recent call...

review: Needs Fixing (tried to install)

[Mario Arias (the-clone-master)]

I just found two small issues:

import_odbc_view:
Line 45: the "tree" is missing the "string" attribute, that is mandatory. Just add 'string="Tree Name"' as attribute and the error will be gone.
Line 81: base.next_id_15 is not present in OpenERP Saas3. Not sure what menu was that, but this is fixed just defining what would be the right menu to place the module under...

Also it seems that base_external_dbsource branch in LP is more "up to date" than included module in this one...
I will check change history and diff, and will publish back any findings...

Regards,
Mario

review: Needs Fixing (code review and test)

[Mario Arias (the-clone-master)]

Being consistent with base_external_dbsource, line 81 on import_odbc_view should be set to "base.next_id_9", so that table load rules menu appears next to database source definition menu option...

[lp:~openerp-community/server-env-tools/7.0-modules-from-openobject-extension]

54. By Maxime Chambreuil (http://www.savoirfairelinux.com) on 2014-04-17

[FIX] Menu, views and version number

[Maxime Chambreuil (http://www.savoirfairelinux.com) (max3903)]

I just fixed the branch according to your reviews.

Thanks.

[Yannick Vaucher @ Camptocamp (yvaucher-c2c)]

import_odbc install without error

and Mario's comment where resolved.

Approved

review: Approve

=== 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	2014-04-17 12:03:09 +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/>.
+#
+##############################################################################
+
+from . 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	2014-04-17 12:03:09 +0000
@@ -0,0 +1,66 @@
+# -*- 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': '1.3',
+    'category': 'Tools',
+    'description': """
+This module allows you to define connections to foreign databases using ODBC,
+Oracle Client or SQLAlchemy.
+
+Database 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.
+
+Contributors
+============
+
+* Maxime Chambreuil <[e-mail]>
+    """,
+    'author': 'Daniel Reis',
+    'website': 'http://launchpad.net/addons-tko',
+    'images': [
+        'images/screenshot01.png',
+    ],
+    'depends': [
+        'base',
+    ],
+    '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	2014-04-17 12:03:09 +0000
@@ -0,0 +1,175 @@
+# -*- 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
+import logging
+from openerp.osv import orm, fields
+from openerp.tools.translate import _
+import openerp.tools as tools
+_logger = logging.getLogger(__name__)
+
+CONNECTORS = []
+
+try:
+    import sqlalchemy
+    CONNECTORS.append(('sqlite', 'SQLite'))
+    try:
+        import pymssql
+        CONNECTORS.append(('mssql', 'Microsoft SQL Server'))
+    except:
+        _logger.info('MS SQL Server not available. Please install "pymssql"\
+                      python package.')
+    try:
+        import MySQLdb
+        CONNECTORS.append(('mysql', 'MySQL'))
+    except:
+        _logger.info('MySQL not available. Please install "mysqldb"\
+                     python package.')
+except:
+    _logger.info('SQL Alchemy not available. Please install "slqalchemy"\
+                 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'))
+
+
+class base_external_dbsource(orm.Model):
+    _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 components 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 orm.except_orm(_("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 orm.except_orm(_("Connection test succeeded!"),
+                             _("Everything seems properly set up!"))
+
+#EOF

=== 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	2014-04-17 12:03:09 +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	2014-04-17 12:03:09 +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 string="External DB Sources">
+                    <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 string="External DB Source">
+                    <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_9" 
+                  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	2014-04-17 12:03:09 +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	2014-04-17 12:03:09 +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	2014-04-17 12:03:09 +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	2014-04-17 12:03:09 +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/>.
+#
+##############################################################################
+
+from . 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	2014-04-17 12:03:09 +0000
@@ -0,0 +1,88 @@
+# -*- 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': '1.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.
+
+Contributors
+============
+
+* Maxime Chambreuil <[e-mail]>
+    """,
+    'author': 'Daniel Reis',
+    'website': 'http://launchpad.net/addons-tko',
+    'images': [
+        'images/snapshot1.png',
+        'images/snapshot2.png',
+    ],
+    'depends': [
+        'base',
+        'base_external_dbsource',
+    ],
+    '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	2014-04-17 12:03:09 +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	2014-04-17 12:03:09 +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	2014-04-17 12:03:09 +0000
@@ -0,0 +1,218 @@
+# -*- 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 openerp.osv import orm, fields
+import logging
+_logger = logging.getLogger(__name__)
+_loglvl = _logger.getEffectiveLevel()
+SEP = '|'
+
+
+class import_odbc_dbtable(orm.Model):
+    _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."
+                                     "\nLater 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)})
+            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',
+            }
+
+#EOF

=== 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	2014-04-17 12:03:09 +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	2014-04-17 12:03:09 +0000
@@ -0,0 +1,90 @@
+<?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 string="Table">
+	            <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 string="Tables" colors="grey: enabled==False; red:last_error_count&gt;0; blue:last_warn_count&gt;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_9"
+                 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	2014-04-17 12:03:09 +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