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.

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

52. By Maxime Chambreuil (http://www.savoirfairelinux.com) on 2013-09-07

[FIX] PEP8 compliance and review comments

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

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

53. By Maxime Chambreuil (http://www.savoirfairelinux.com) on 2013-12-20

[FIX] PEP8 and file permission issues

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

Thanks Yannick.

I made the changes according to your review.

[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

Unmerged revisions

=== 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	2013-12-20 18:51:45 +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	2013-12-20 18:51:45 +0000
@@ -0,0 +1,61 @@
+# -*- 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.
+
+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.
+    """,
+    '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	2013-12-20 18:51:45 +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	2013-12-20 18:51:45 +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	2013-12-20 18:51:45 +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	2013-12-20 18:51:45 +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	2013-12-20 18:51:45 +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	2013-12-20 18:51:45 +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	2013-12-20 18:51:45 +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	2013-12-20 18:51:45 +0000
@@ -0,0 +1,83 @@
+# -*- 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',
+    ],
+    '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	2013-12-20 18:51:45 +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	2013-12-20 18:51:45 +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	2013-12-20 18:51:45 +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	2013-12-20 18:51:45 +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	2013-12-20 18:51:45 +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&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_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	2013-12-20 18:51:45 +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