1
=== added directory 'base_external_dbsource'
2
=== added file 'base_external_dbsource/__init__.py'
3
--- base_external_dbsource/__init__.py	1970-01-01 00:00:00 +0000
4
+++ base_external_dbsource/__init__.py	2014-04-17 12:03:09 +0000
5
@@ -0,0 +1,24 @@
6
1
# -*- coding: utf-8 -*-
7
2
##############################################################################
8
3
#
9
4
#    Daniel Reis
10
5
#    2011
11
6
#
12
7
#    This program is free software: you can redistribute it and/or modify
13
8
#    it under the terms of the GNU Affero General Public License as
14
9
#    published by the Free Software Foundation, either version 3 of the
15
10
#    License, or (at your option) any later version.
16
11
#
17
12
#    This program is distributed in the hope that it will be useful,
18
13
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
19
14
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
20
15
#    GNU Affero General Public License for more details.
21
16
#
22
17
#    You should have received a copy of the GNU Affero General Public License
23
18
#    along with this program.  If not, see <http://www.gnu.org/licenses/>.
24
19
#
25
20
##############################################################################
26
21
27
22
from . import base_external_dbsource
28
23
29
24
# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:
30
0
25
31
=== added file 'base_external_dbsource/__openerp__.py'
32
--- base_external_dbsource/__openerp__.py	1970-01-01 00:00:00 +0000
33
+++ base_external_dbsource/__openerp__.py	2014-04-17 12:03:09 +0000
34
@@ -0,0 +1,66 @@
35
1
# -*- coding: utf-8 -*-
36
2
##############################################################################
37
3
#
38
4
#    Daniel Reis, 2011
39
5
#    Additional contributions by Maxime Chambreuil, Savoir-faire Linux
40
6
#
41
7
#    This program is free software: you can redistribute it and/or modify
42
8
#    it under the terms of the GNU Affero General Public License as
43
9
#    published by the Free Software Foundation, either version 3 of the
44
10
#    License, or (at your option) any later version.
45
11
#
46
12
#    This program is distributed in the hope that it will be useful,
47
13
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
48
14
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
49
15
#    GNU Affero General Public License for more details.
50
16
#
51
17
#    You should have received a copy of the GNU Affero General Public License
52
18
#    along with this program.  If not, see <http://www.gnu.org/licenses/>.
53
19
#
54
20
##############################################################################
55
21
56
22
{
57
23
    'name': 'External Database Sources',
58
24
    'version': '1.3',
59
25
    'category': 'Tools',
60
26
    'description': """
61
27
This module allows you to define connections to foreign databases using ODBC,
62
28
Oracle Client or SQLAlchemy.
63
29
64
30
Database sources can be configured in Settings > Configuration -> Data sources.
65
31
66
32
Depending on the database, you need:
67
33
 * to install unixodbc and python-pyodbc packages to use ODBC connections.
68
34
 * to install FreeTDS driver (tdsodbc package) and configure it through ODBC to
69
35
   connect to Microsoft SQL Server.
70
36
 * to install and configure Oracle Instant Client and cx_Oracle python library
71
37
   to connect to Oracle.
72
38
73
39
Contributors
74
40
============
75
41
76
42
* Maxime Chambreuil <maxime.chambreuil@savoirfairelinux.com>
77
43
    """,
78
44
    'author': 'Daniel Reis',
79
45
    'website': 'http://launchpad.net/addons-tko',
80
46
    'images': [
81
47
        'images/screenshot01.png',
82
48
    ],
83
49
    'depends': [
84
50
        'base',
85
51
    ],
86
52
    'data': [
87
53
        'base_external_dbsource_view.xml',
88
54
        'security/ir.model.access.csv',
89
55
    ],
90
56
    'demo': [
91
57
        'base_external_dbsource_demo.xml',
92
58
    ],
93
59
    'test': [
94
60
        'dbsource_connect.yml',
95
61
    ],
96
62
    'installable': True,
97
63
    'active': False,
98
64
}
99
65
100
66
# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:
101
0
67
102
=== added file 'base_external_dbsource/base_external_dbsource.py'
103
--- base_external_dbsource/base_external_dbsource.py	1970-01-01 00:00:00 +0000
104
+++ base_external_dbsource/base_external_dbsource.py	2014-04-17 12:03:09 +0000
105
@@ -0,0 +1,175 @@
106
1
# -*- coding: utf-8 -*-
107
2
##############################################################################
108
3
#
109
4
#    Daniel Reis
110
5
#    2011
111
6
#
112
7
#    This program is free software: you can redistribute it and/or modify
113
8
#    it under the terms of the GNU Affero General Public License as
114
9
#    published by the Free Software Foundation, either version 3 of the
115
10
#    License, or (at your option) any later version.
116
11
#
117
12
#    This program is distributed in the hope that it will be useful,
118
13
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
119
14
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
120
15
#    GNU Affero General Public License for more details.
121
16
#
122
17
#    You should have received a copy of the GNU Affero General Public License
123
18
#    along with this program.  If not, see <http://www.gnu.org/licenses/>.
124
19
#
125
20
##############################################################################
126
21
127
22
import os
128
23
import logging
129
24
from openerp.osv import orm, fields
130
25
from openerp.tools.translate import _
131
26
import openerp.tools as tools
132
27
_logger = logging.getLogger(__name__)
133
28
134
29
CONNECTORS = []
135
30
136
31
try:
137
32
    import sqlalchemy
138
33
    CONNECTORS.append(('sqlite', 'SQLite'))
139
34
    try:
140
35
        import pymssql
141
36
        CONNECTORS.append(('mssql', 'Microsoft SQL Server'))
142
37
    except:
143
38
        _logger.info('MS SQL Server not available. Please install "pymssql"\
144
39
                      python package.')
145
40
    try:
146
41
        import MySQLdb
147
42
        CONNECTORS.append(('mysql', 'MySQL'))
148
43
    except:
149
44
        _logger.info('MySQL not available. Please install "mysqldb"\
150
45
                     python package.')
151
46
except:
152
47
    _logger.info('SQL Alchemy not available. Please install "slqalchemy"\
153
48
                 python package.')
154
49
try:
155
50
    import pyodbc
156
51
    CONNECTORS.append(('pyodbc', 'ODBC'))
157
52
except:
158
53
    _logger.info('ODBC libraries not available. Please install "unixodbc"\
159
54
                 and "python-pyodbc" packages.')
160
55
161
56
try:
162
57
    import cx_Oracle
163
58
    CONNECTORS.append(('cx_Oracle', 'Oracle'))
164
59
except:
165
60
    _logger.info('Oracle libraries not available. Please install "cx_Oracle"\
166
61
                 python package.')
167
62
168
63
import psycopg2
169
64
CONNECTORS.append(('postgresql', 'PostgreSQL'))
170
65
171
66
172
67
class base_external_dbsource(orm.Model):
173
68
    _name = "base.external.dbsource"
174
69
    _description = 'External Database Sources'
175
70
    _columns = {
176
71
        'name': fields.char('Datasource name', required=True, size=64),
177
72
        'conn_string': fields.text('Connection string', help="""
178
73
Sample connection strings:
179
74
- Microsoft SQL Server:
180
75
  mssql+pymssql://username:%s@server:port/dbname?charset=utf8
181
76
- MySQL: mysql://user:%s@server:port/dbname
182
77
- ODBC: DRIVER={FreeTDS};SERVER=server.address;Database=mydb;UID=sa
183
78
- ORACLE: username/%s@//server.address:port/instance
184
79
- PostgreSQL:
185
80
  dbname='template1' user='dbuser' host='localhost' port='5432' password=%s
186
81
- SQLite: sqlite:///test.db
187
82
"""),
188
83
        'password': fields.char('Password', size=40),
189
84
        'connector': fields.selection(CONNECTORS, 'Connector',
190
85
                                      required=True,
191
86
                                      help="If a connector is missing from the\
192
87
                                      list, check the server log to confirm\
193
88
                                      that the required components were\
194
89
                                      detected."),
195
90
    }
196
91
197
92
    def conn_open(self, cr, uid, id1):
198
93
        #Get dbsource record
199
94
        data = self.browse(cr, uid, id1)
200
95
        #Build the full connection string
201
96
        connStr = data.conn_string
202
97
        if data.password:
203
98
            if '%s' not in data.conn_string:
204
99
                connStr += ';PWD=%s'
205
100
            connStr = connStr % data.password
206
101
        #Try to connect
207
102
        if data.connector == 'cx_Oracle':
208
103
            os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.UTF8'
209
104
            conn = cx_Oracle.connect(connStr)
210
105
        elif data.connector == 'pyodbc':
211
106
            conn = pyodbc.connect(connStr)
212
107
        elif data.connector in ('sqlite', 'mysql', 'mssql'):
213
108
            conn = sqlalchemy.create_engine(connStr).connect()
214
109
        elif data.connector == 'postgresql':
215
110
            conn = psycopg2.connect(connStr)
216
111
217
112
        return conn
218
113
219
114
    def execute(self, cr, uid, ids, sqlquery, sqlparams=None, metadata=False,
220
115
                context=None):
221
116
        """Executes SQL and returns a list of rows.
222
117
223
118
            "sqlparams" can be a dict of values, that can be referenced in
224
119
            the SQL statement using "%(key)s" or, in the case of Oracle,
225
120
            ":key".
226
121
            Example:
227
122
                sqlquery = "select * from mytable where city = %(city)s and
228
123
                            date > %(dt)s"
229
124
                params   = {'city': 'Lisbon',
230
125
                            'dt': datetime.datetime(2000, 12, 31)}
231
126
232
127
            If metadata=True, it will instead return a dict containing the
233
128
            rows list and the columns list, in the format:
234
129
                { 'cols': [ 'col_a', 'col_b', ...]
235
130
                , 'rows': [ (a0, b0, ...), (a1, b1, ...), ...] }
236
131
        """
237
132
        data = self.browse(cr, uid, ids)
238
133
        rows, cols = list(), list()
239
134
        for obj in data:
240
135
            conn = self.conn_open(cr, uid, obj.id)
241
136
            if obj.connector in ["sqlite", "mysql", "mssql"]:
242
137
                #using sqlalchemy
243
138
                cur = conn.execute(sqlquery, sqlparams)
244
139
                if metadata:
245
140
                    cols = cur.keys()
246
141
                rows = [r for r in cur]
247
142
            else:
248
143
                #using other db connectors
249
144
                cur = conn.cursor()
250
145
                cur.execute(sqlquery, sqlparams)
251
146
                if metadata:
252
147
                    cols = [d[0] for d in cur.description]
253
148
                rows = cur.fetchall()
254
149
            conn.close()
255
150
        if metadata:
256
151
            return{'cols': cols, 'rows': rows}
257
152
        else:
258
153
            return rows
259
154
260
155
    def connection_test(self, cr, uid, ids, context=None):
261
156
        for obj in self.browse(cr, uid, ids, context):
262
157
            conn = False
263
158
            try:
264
159
                conn = self.conn_open(cr, uid, obj.id)
265
160
            except Exception, e:
266
161
                raise orm.except_orm(_("Connection test failed!"),
267
162
                                     _("Here is what we got instead:\n %s")
268
163
                                     % tools.ustr(e))
269
164
            finally:
270
165
                try:
271
166
                    if conn:
272
167
                        conn.close()
273
168
                except Exception:
274
169
                    # ignored, just a consequence of the previous exception
275
170
                    pass
276
171
        #TODO: if OK a (wizard) message box should be displayed
277
172
        raise orm.except_orm(_("Connection test succeeded!"),
278
173
                             _("Everything seems properly set up!"))
279
174
280
175
#EOF
281
0
176
282
=== added file 'base_external_dbsource/base_external_dbsource_demo.xml'
283
--- base_external_dbsource/base_external_dbsource_demo.xml	1970-01-01 00:00:00 +0000
284
+++ base_external_dbsource/base_external_dbsource_demo.xml	2014-04-17 12:03:09 +0000
285
@@ -0,0 +1,15 @@
286
1
<?xml version="1.0"?>
287
2
<openerp>
288
3
    <data>
289
4
290
5
        <record model="base.external.dbsource" id="demo_postgre">
291
6
            <field name="name">PostgreSQL local</field>
292
7
            <field name="conn_string">dbname='postgres' password=%s</field>
293
8
            <field name="password">postgresql</field>
294
9
            <field name="connector">postgresql</field>
295
10
        </record>
296
11
        
297
12
    </data>
298
13
</openerp>
299
14
300
15
301
0
16
302
=== added file 'base_external_dbsource/base_external_dbsource_view.xml'
303
--- base_external_dbsource/base_external_dbsource_view.xml	1970-01-01 00:00:00 +0000
304
+++ base_external_dbsource/base_external_dbsource_view.xml	2014-04-17 12:03:09 +0000
305
@@ -0,0 +1,54 @@
306
1
<?xml version="1.0"?>
307
2
<openerp>
308
3
    <data>
309
4
310
5
        <!-- DBSource -->
311
6
312
7
        <record model="ir.ui.view" id="view_dbsource_tree">
313
8
            <field name="name">base.external.dbsource.tree</field>
314
9
            <field name="model">base.external.dbsource</field>
315
10
            <field name="type">tree</field>
316
11
            <field name="arch" type="xml">
317
12
                <tree string="External DB Sources">
318
13
                    <field name="name"/>
319
14
		    <field name="connector"/>
320
15
		    <field name="conn_string"/>
321
16
                </tree>
322
17
            </field>
323
18
        </record>
324
19
        
325
20
        <record model="ir.ui.view" id="view_dbsource_form">
326
21
            <field name="name">base.external.dbsource.form</field>
327
22
            <field name="model">base.external.dbsource</field>
328
23
            <field name="type">form</field>
329
24
            <field name="arch" type="xml">
330
25
                <form string="External DB Source">
331
26
                    <field name="name"/>
332
27
                    <field name="password" password="True"/>
333
28
                    <newline/>
334
29
		    <field name="connector" colspan="2"/>
335
30
                    <newline/>
336
31
		    <field name="conn_string" colspan="4"/>
337
32
                    <newline/>
338
33
                    <button name="connection_test" string="Test Connection" type="object" icon="gtk-network" colspan="4"/>
339
34
                </form>
340
35
            </field>
341
36
        </record>
342
37
343
38
        <record model="ir.actions.act_window" id="action_dbsource">
344
39
            <field name="name">External Database Sources</field>
345
40
            <field name="res_model">base.external.dbsource</field>
346
41
            <field name="view_type">form</field>
347
42
            <field name="view_mode">tree,form</field>
348
43
            <field name="view_id" ref="view_dbsource_tree"/>
349
44
        </record>
350
45
351
46
        <menuitem name="Database Sources"
352
47
                  id="menu_dbsource" 
353
48
                  parent="base.next_id_9" 
354
49
                  action="action_dbsource"/>
355
50
356
51
    </data>
357
52
</openerp>
358
53
359
54
360
0
55
361
=== added directory 'base_external_dbsource/images'
362
=== added file 'base_external_dbsource/images/screenshot01.png'
363
1
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
56
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
364
=== added directory 'base_external_dbsource/security'
365
=== added file 'base_external_dbsource/security/ir.model.access.csv'
366
--- base_external_dbsource/security/ir.model.access.csv	1970-01-01 00:00:00 +0000
367
+++ base_external_dbsource/security/ir.model.access.csv	2014-04-17 12:03:09 +0000
368
@@ -0,0 +1,2 @@
369
1
id,name,model_id:id,group_id:id,perm_read,perm_write,perm_create,perm_unlink
370
2
access_base_external_dbsource_group_system,bae_external_dbsource_group_system,model_base_external_dbsource,base.group_system,1,1,1,1
371
0
3
372
=== added directory 'base_external_dbsource/test'
373
=== added file 'base_external_dbsource/test/dbsource_connect.yml'
374
--- base_external_dbsource/test/dbsource_connect.yml	1970-01-01 00:00:00 +0000
375
+++ base_external_dbsource/test/dbsource_connect.yml	2014-04-17 12:03:09 +0000
376
@@ -0,0 +1,5 @@
377
1
-
378
2
  Connect to local Postgres.
379
3
- 
380
4
  !python {model: base.external.dbsource}: |
381
5
    self.connection_test(cr, uid, [ref("demo_postgresql")]
382
0
6
383
=== added directory 'import_odbc'
384
=== added file 'import_odbc/__init__.py'
385
--- import_odbc/__init__.py	1970-01-01 00:00:00 +0000
386
+++ import_odbc/__init__.py	2014-04-17 12:03:09 +0000
387
@@ -0,0 +1,24 @@
388
1
# -*- coding: utf-8 -*-
389
2
##############################################################################
390
3
#
391
4
#    Daniel Reis
392
5
#    2011
393
6
#
394
7
#    This program is free software: you can redistribute it and/or modify
395
8
#    it under the terms of the GNU Affero General Public License as
396
9
#    published by the Free Software Foundation, either version 3 of the
397
10
#    License, or (at your option) any later version.
398
11
#
399
12
#    This program is distributed in the hope that it will be useful,
400
13
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
401
14
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
402
15
#    GNU Affero General Public License for more details.
403
16
#
404
17
#    You should have received a copy of the GNU Affero General Public License
405
18
#    along with this program.  If not, see <http://www.gnu.org/licenses/>.
406
19
#
407
20
##############################################################################
408
21
409
22
from . import import_odbc
410
23
411
24
# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:
412
0
25
413
=== added file 'import_odbc/__openerp__.py'
414
--- import_odbc/__openerp__.py	1970-01-01 00:00:00 +0000
415
+++ import_odbc/__openerp__.py	2014-04-17 12:03:09 +0000
416
@@ -0,0 +1,88 @@
417
1
# -*- coding: utf-8 -*-
418
2
##############################################################################
419
3
#
420
4
#    Daniel Reis
421
5
#    2011
422
6
#
423
7
#    This program is free software: you can redistribute it and/or modify
424
8
#    it under the terms of the GNU Affero General Public License as
425
9
#    published by the Free Software Foundation, either version 3 of the
426
10
#    License, or (at your option) any later version.
427
11
#
428
12
#    This program is distributed in the hope that it will be useful,
429
13
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
430
14
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
431
15
#    GNU Affero General Public License for more details.
432
16
#
433
17
#    You should have received a copy of the GNU Affero General Public License
434
18
#    along with this program.  If not, see <http://www.gnu.org/licenses/>.
435
19
#
436
20
##############################################################################
437
21
438
22
{
439
23
    'name': 'Import data from SQL and ODBC data sources.',
440
24
    'version': '1.3',
441
25
    'category': 'Tools',
442
26
    'description': """
443
27
Import data directly from other databases.
444
28
445
29
Installed in the Administration module, menu Configuration -> Import from SQL.
446
30
447
31
Features:
448
32
 * 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.
449
33
 * 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.
450
34
 * 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.
451
35
 * 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.
452
36
 * 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.
453
37
 * The import execution can be scheduled to run automatically.
454
38
455
39
Examples:
456
40
 * Importing suppliers to res.partner:
457
41
      SELECT distinct
458
42
            [SUPPLIER_CODE] as "ref"
459
43
          , [SUPPLIER_NAME] as "name"
460
44
          , 1 as "is_supplier"
461
45
          , [INFO] as "comment"
462
46
        FROM T_SUPPLIERS
463
47
       WHERE INACTIVE_DATE IS NULL and DATE_CHANGED >= %(sync)s
464
48
465
49
 * Importing products to product.product:
466
50
      SELECT PRODUCT_CODE as "ref"
467
51
           , PRODUCT_NAME as "name"
468
52
           , 'res_partner_id_'+SUPPLIER_ID as "partner_id/id"
469
53
        FROM T_PRODUCTS
470
54
       WHERE DATE_CHANGED >= %(sync)s
471
55
472
56
Improvements ideas waiting for a contributor:
473
57
 * Allow to import many2one fields (currently not supported). Done by adding a second SQL sentence to get child record list?
474
58
 * Allow "import sets" that can be executed at different time intervals using different scheduler jobs.
475
59
 * Allow to inactivate/delete OpenERP records when not present in an SQL result set.
476
60
477
61
Contributors
478
62
============
479
63
480
64
* Maxime Chambreuil <maxime.chambreuil@savoirfairelinux.com>
481
65
    """,
482
66
    'author': 'Daniel Reis',
483
67
    'website': 'http://launchpad.net/addons-tko',
484
68
    'images': [
485
69
        'images/snapshot1.png',
486
70
        'images/snapshot2.png',
487
71
    ],
488
72
    'depends': [
489
73
        'base',
490
74
        'base_external_dbsource',
491
75
    ],
492
76
    'data': [
493
77
        'import_odbc_view.xml',
494
78
        'security/ir.model.access.csv',
495
79
    ],
496
80
    'demo': [
497
81
        'import_odbc_demo.xml',
498
82
    ],
499
83
    'test': [],
500
84
    'installable': True,
501
85
    'active': False,
502
86
}
503
87
504
88
# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:
505
0
89
506
=== added directory 'import_odbc/images'
507
=== added file 'import_odbc/images/snapshot1.png'
508
1
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
90
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
509
=== added file 'import_odbc/images/snapshot2.png'
510
2
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
91
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
511
=== added file 'import_odbc/import_odbc.py'
512
--- import_odbc/import_odbc.py	1970-01-01 00:00:00 +0000
513
+++ import_odbc/import_odbc.py	2014-04-17 12:03:09 +0000
514
@@ -0,0 +1,218 @@
515
1
# -*- coding: utf-8 -*-
516
2
##############################################################################
517
3
#
518
4
#    Daniel Reis
519
5
#    2011
520
6
#
521
7
#    This program is free software: you can redistribute it and/or modify
522
8
#    it under the terms of the GNU Affero General Public License as
523
9
#    published by the Free Software Foundation, either version 3 of the
524
10
#    License, or (at your option) any later version.
525
11
#
526
12
#    This program is distributed in the hope that it will be useful,
527
13
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
528
14
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
529
15
#    GNU Affero General Public License for more details.
530
16
#
531
17
#    You should have received a copy of the GNU Affero General Public License
532
18
#    along with this program.  If not, see <http://www.gnu.org/licenses/>.
533
19
#
534
20
##############################################################################
535
21
536
22
import sys
537
23
from datetime import datetime
538
24
from openerp.osv import orm, fields
539
25
import logging
540
26
_logger = logging.getLogger(__name__)
541
27
_loglvl = _logger.getEffectiveLevel()
542
28
SEP = '|'
543
29
544
30
545
31
class import_odbc_dbtable(orm.Model):
546
32
    _name = "import.odbc.dbtable"
547
33
    _description = 'Import Table Data'
548
34
    _order = 'exec_order'
549
35
    _columns = {
550
36
        'name': fields.char('Datasource name', required=True, size=64),
551
37
        'enabled': fields.boolean('Execution enabled'),
552
38
        'dbsource_id': fields.many2one('base.external.dbsource', 'Database source', required=True),
553
39
        'sql_source': fields.text('SQL', required=True, help='Column names must be valid "import_data" columns.'),
554
40
        'model_target': fields.many2one('ir.model', 'Target object'),
555
41
        'noupdate': fields.boolean('No updates', help="Only create new records; disable updates to existing records."),
556
42
        'exec_order': fields.integer('Execution order', help="Defines the order to perform the import"),
557
43
        'last_sync': fields.datetime('Last sync date',
558
44
                                     help="Datetime for the last succesfull sync."
559
45
                                     "\nLater changes on the source may not be replicated on the destination"),
560
46
        'start_run': fields.datetime('Time started', readonly=True),
561
47
        'last_run': fields.datetime('Time ended', readonly=True),
562
48
        'last_record_count': fields.integer('Last record count', readonly=True),
563
49
        'last_error_count': fields.integer('Last error count', readonly=True),
564
50
        'last_warn_count': fields.integer('Last warning count', readonly=True),
565
51
        'last_log': fields.text('Last run log', readonly=True),
566
52
        'ignore_rel_errors': fields.boolean('Ignore relationship errors',
567
53
                                            help="On error try to reimport rows ignoring relationships."),
568
54
        'raise_import_errors': fields.boolean('Raise import errors',
569
55
                                              help="Import errors not handled, intended for debugging purposes."
570
56
                                              "\nAlso forces debug messages to be written to the server log."),
571
57
    }
572
58
    _defaults = {
573
59
        'enabled': True,
574
60
        'exec_order': 10,
575
61
    }
576
62
577
63
    def _import_data(self, cr, uid, flds, data, model_obj, table_obj, log):
578
64
        """Import data and returns error msg or empty string"""
579
65
580
66
        def find_m2o(field_list):
581
67
            """"Find index of first column with a one2many field"""
582
68
            for i, x in enumerate(field_list):
583
69
                if len(x) > 3 and x[-3:] == ':id' or x[-3:] == '/id':
584
70
                    return i
585
71
            return -1
586
72
587
73
        def append_to_log(log, level, obj_id='', msg='', rel_id=''):
588
74
            if '_id_' in obj_id:
589
75
                obj_id = '.'.join(obj_id.split('_')[:-2]) + ': ' + obj_id.split('_')[-1]
590
76
            if ': .' in msg and not rel_id:
591
77
                rel_id = msg[msg.find(': .')+3:]
592
78
                if '_id_' in rel_id:
593
79
                    rel_id = '.'.join(rel_id.split('_')[:-2]) + ': ' + rel_id.split('_')[-1]
594
80
                    msg = msg[:msg.find(': .')]
595
81
            log['last_log'].append('%s|%s\t|%s\t|%s' % (level.ljust(5), obj_id, rel_id, msg))
596
82
        _logger.debug(data)
597
83
        cols = list(flds)  # copy to avoid side effects
598
84
        errmsg = str()
599
85
        if table_obj.raise_import_errors:
600
86
            model_obj.import_data(cr, uid, cols, [data], noupdate=table_obj.noupdate)
601
87
        else:
602
88
            try:
603
89
                model_obj.import_data(cr, uid, cols, [data], noupdate=table_obj.noupdate)
604
90
            except:
605
91
                errmsg = str(sys.exc_info()[1])
606
92
        if errmsg and not table_obj.ignore_rel_errors:
607
93
            #Fail
608
94
            append_to_log(log, 'ERROR', data, errmsg)
609
95
            log['last_error_count'] += 1
610
96
            return False
611
97
        if errmsg and table_obj.ignore_rel_errors:
612
98
            #Warn and retry ignoring many2one fields...
613
99
            append_to_log(log, 'WARN', data, errmsg)
614
100
            log['last_warn_count'] += 1
615
101
            #Try ignoring each many2one (tip: in the SQL sentence select more problematic FKs first)
616
102
            i = find_m2o(cols)
617
103
            if i >= 0:
618
104
                #Try again without the [i] column
619
105
                del cols[i]
620
106
                del data[i]
621
107
                self._import_data(cr, uid, cols, data, model_obj, table_obj, log)
622
108
            else:
623
109
                #Fail
624
110
                append_to_log(log, 'ERROR', data, 'Removed all m2o keys and still fails.')
625
111
                log['last_error_count'] += 1
626
112
                return False
627
113
        return True
628
114
629
115
    def import_run(self, cr, uid, ids=None, context=None):
630
116
        db_model = self.pool.get('base.external.dbsource')
631
117
        actions = self.read(cr, uid, ids, ['id', 'exec_order'])
632
118
        actions.sort(key=lambda x: (x['exec_order'], x['id']))
633
119
634
120
        #Consider each dbtable:
635
121
        for action_ref in actions:
636
122
            obj = self.browse(cr, uid, action_ref['id'])
637
123
            if not obj.enabled:
638
124
                continue  # skip
639
125
640
126
            _logger.setLevel(obj.raise_import_errors and logging.DEBUG or _loglvl)
641
127
            _logger.debug('Importing %s...' % obj.name)
642
128
643
129
            #now() microseconds are stripped to avoid problem with SQL smalldate
644
130
            #TODO: convert UTC Now to local timezone
645
131
            #http://stackoverflow.com/questions/4770297/python-convert-utc-datetime-string-to-local-datetime
646
132
            model_name = obj.model_target.model
647
133
            model_obj = self.pool.get(model_name)
648
134
            xml_prefix = model_name.replace('.', '_') + "_id_"
649
135
            log = {'start_run': datetime.now().replace(microsecond=0),
650
136
                   'last_run': None,
651
137
                   'last_record_count': 0,
652
138
                   'last_error_count': 0,
653
139
                   'last_warn_count': 0,
654
140
                   'last_log': list()}
655
141
            self.write(cr, uid, [obj.id], log)
656
142
657
143
            #Prepare SQL sentence; replace "%s" with the last_sync date
658
144
            if obj.last_sync:
659
145
                sync = datetime.strptime(obj.last_sync, "%Y-%m-%d %H:%M:%S")
660
146
            else:
661
147
                sync = datetime.datetime(1900, 1, 1, 0, 0, 0)
662
148
            params = {'sync': sync}
663
149
            res = db_model.execute(cr, uid, [obj.dbsource_id.id],
664
150
                                   obj.sql_source, params, metadata=True)
665
151
666
152
            #Exclude columns titled "None"; add (xml_)"id" column
667
153
            cidx = [i for i, x in enumerate(res['cols']) if x.upper() != 'NONE']
668
154
            cols = [x for i, x in enumerate(res['cols']) if x.upper() != 'NONE'] + ['id']
669
155
670
156
            #Import each row:
671
157
            for row in res['rows']:
672
158
                #Build data row; import only columns present in the "cols" list
673
159
                data = list()
674
160
                for i in cidx:
675
161
                    #TODO: Handle imported datetimes properly - convert from localtime to UTC!
676
162
                    v = row[i]
677
163
                    if isinstance(v, str):
678
164
                        v = v.strip()
679
165
                    data.append(v)
680
166
                data.append(xml_prefix + str(row[0]).strip())
681
167
682
168
                #Import the row; on error, write line to the log
683
169
                log['last_record_count'] += 1
684
170
                self._import_data(cr, uid, cols, data, model_obj, obj, log)
685
171
                if log['last_record_count'] % 500 == 0:
686
172
                    _logger.info('...%s rows processed...' % (log['last_record_count']))
687
173
688
174
            #Finished importing all rows
689
175
            #If no errors, write new sync date
690
176
            if not (log['last_error_count'] or log['last_warn_count']):
691
177
                log['last_sync'] = log['start_run']
692
178
            level = logging.DEBUG
693
179
            if log['last_warn_count']:
694
180
                level = logging.WARN
695
181
            if log['last_error_count']:
696
182
                level = logging.ERROR
697
183
            _logger.log(level, 'Imported %s , %d rows, %d errors, %d warnings.' % (
698
184
                model_name, log['last_record_count'], log['last_error_count'],
699
185
                log['last_warn_count']))
700
186
            #Write run log, either if the table import is active or inactive
701
187
            if log['last_log']:
702
188
                log['last_log'].insert(0, 'LEVEL|== Line ==    |== Relationship ==|== Message ==')
703
189
            log.update({'last_log': '\n'.join(log['last_log'])})
704
190
            log.update({'last_run': datetime.now().replace(microsecond=0)})
705
191
            self.write(cr, uid, [obj.id], log)
706
192
707
193
        #Finished
708
194
        _logger.debug('Import job FINISHED.')
709
195
        return True
710
196
711
197
    def import_schedule(self, cr, uid, ids, context=None):
712
198
        cron_obj = self.pool.get('ir.cron')
713
199
        new_create_id = cron_obj.create(cr, uid, {
714
200
            'name': 'Import ODBC tables',
715
201
            'interval_type': 'hours',
716
202
            'interval_number': 1,
717
203
            'numbercall': -1,
718
204
            'model': 'import.odbc.dbtable',
719
205
            'function': 'import_run',
720
206
            'doall': False,
721
207
            'active': True
722
208
            })
723
209
        return {
724
210
            'name': 'Import ODBC tables',
725
211
            'view_type': 'form',
726
212
            'view_mode': 'form,tree',
727
213
            'res_model': 'ir.cron',
728
214
            'res_id': new_create_id,
729
215
            'type': 'ir.actions.act_window',
730
216
            }
731
217
732
218
#EOF
733
0
219
734
=== added file 'import_odbc/import_odbc_demo.xml'
735
--- import_odbc/import_odbc_demo.xml	1970-01-01 00:00:00 +0000
736
+++ import_odbc/import_odbc_demo.xml	2014-04-17 12:03:09 +0000
737
@@ -0,0 +1,15 @@
738
1
<?xml version="1.0"?>
739
2
<openerp>
740
3
    <data>
741
4
742
5
        <record model="import.odbc.dbtable" id="demo_postgresql_users">
743
6
            <field name="name">Users from PostgreSQL </field>
744
7
            <field name="dbsource_id" ref="base_external_dbsource.demo_postgre"/>
745
8
            <field name="sql_source">select usename as "login", usename as "name" from pg_catalog.pg_user</field>
746
9
            <field name="model_target" ref="base.model_res_users"/>
747
10
        </record>
748
11
749
12
    </data>
750
13
</openerp>
751
14
752
15
753
0
16
754
=== added file 'import_odbc/import_odbc_view.xml'
755
--- import_odbc/import_odbc_view.xml	1970-01-01 00:00:00 +0000
756
+++ import_odbc/import_odbc_view.xml	2014-04-17 12:03:09 +0000
757
@@ -0,0 +1,90 @@
758
1
<?xml version="1.0"?>
759
2
<openerp>
760
3
    <data>
761
4
762
5
        <!-- Table form -->
763
6
764
7
        <record model="ir.ui.view" id="view_import_dbtable_form">
765
8
            <field name="name">import.odbc.dbtable.form</field>
766
9
            <field name="model">import.odbc.dbtable</field>
767
10
            <field name="type">form</field>
768
11
            <field name="arch" type="xml">
769
12
	        <form string="Table">
770
13
	            <field name="name" search="1"/>
771
14
	            <field name="exec_order"/>
772
15
	            <field name="model_target"/>
773
16
	            <field name="dbsource_id" search="1"/>
774
17
	            <field name="noupdate"/>
775
18
	            <field name="enabled"/>
776
19
	            <field name="ignore_rel_errors"/>
777
20
	            <field name="raise_import_errors"/>
778
21
                    <field name="last_sync"/>
779
22
                    <group colspan="2">
780
23
                        <button name="import_run" string="Run Import" type="object" icon="gtk-execute"/>
781
24
                        <button name="import_schedule" string="Schedule Import" type="object" icon="gtk-paste"/>
782
25
                    </group>
783
26
	            <field name="sql_source" colspan="4"/>
784
27
                    <separator string="Last execution" colspan="4"/>
785
28
                    <field name="last_record_count"/>
786
29
                    <field name="start_run"/>
787
30
                    <field name="last_warn_count"/>
788
31
	            <field name="last_run"/>
789
32
                    <field name="last_error_count"/>
790
33
                    <field name="last_log" colspan="4"/>
791
34
                </form>
792
35
            </field>
793
36
        </record>
794
37
795
38
        <!-- Table Tree -->
796
39
797
40
        <record id="view_import_dbtable_tree" model="ir.ui.view">
798
41
            <field name="name">import.odbc.dbtable.tree</field>
799
42
	    <field name="model">import.odbc.dbtable</field>
800
43
	    <field name="type">tree</field>
801
44
	    <field name="arch" type="xml">
802
45
                <tree string="Tables" colors="grey: enabled==False; red:last_error_count&gt;0; blue:last_warn_count&gt;0">
803
46
			<field name="exec_order"/>
804
47
			<field name="name"/>
805
48
			<field name="model_target"/>
806
49
			<field name="dbsource_id"/>
807
50
			<field name="enabled"/>
808
51
			<field name="last_run"/>
809
52
			<field name="last_sync"/>
810
53
			<field name="last_record_count"/>
811
54
			<field name="last_error_count"/>
812
55
			<field name="last_warn_count"/>
813
56
		</tree>
814
57
	</field>
815
58
</record>
816
59
817
60
       <!-- Tree Search -->
818
61
       <record id="view_import_dbtable_filter" model="ir.ui.view">
819
62
           <field name="name">import.odbc.dbtable.filter</field>
820
63
           <field name="model">import.odbc.dbtable</field>
821
64
           <field name="type">search</field>
822
65
           <field name="arch" type="xml">
823
66
               <search string="Search ODBC Imports">
824
67
                   <field name="name"/>
825
68
                   <field name="dbsource_id"/>
826
69
                   <field name="model_target"/>
827
70
               </search>
828
71
           </field>
829
72
       </record>
830
73
831
74
       <!--Menu-->
832
75
       <record model="ir.actions.act_window" id="action_import_dbtable">
833
76
           <field name="name">Import from SQL</field>
834
77
           <field name="res_model">import.odbc.dbtable</field>
835
78
           <field name="view_type">form</field>
836
79
       </record>
837
80
838
81
       <menuitem name="Import from SQL"
839
82
                 id="menu_import_dbtable"
840
83
                 parent="base.next_id_9"
841
84
                 action="action_import_dbtable"/>
842
85
843
86
    </data>
844
87
</openerp>
845
88
846
89
847
90
848
0
91
849
=== added directory 'import_odbc/security'
850
=== added file 'import_odbc/security/ir.model.access.csv'
851
--- import_odbc/security/ir.model.access.csv	1970-01-01 00:00:00 +0000
852
+++ import_odbc/security/ir.model.access.csv	2014-04-17 12:03:09 +0000
853
@@ -0,0 +1,2 @@
854
1
id,name,model_id:id,group_id:id,perm_read,perm_write,perm_create,perm_unlink
855
2
access_import_odbc_dbsource_group_system,import_odbc_dbtable_group_system,model_import_odbc_dbtable,base.group_system,1,1,1,1
Status:	Merged
Approved by:	Yannick Vaucher @ Camptocamp on 2014-07-04
Approved revision:	54
Merged at revision:	79
Proposed branch:	lp:~openerp-community/server-env-tools/7.0-modules-from-openobject-extension
Merge into:	lp:~server-env-tools-core-editors/server-env-tools/7.0
Diff against target:	855 lines (+778/-0) 13 files modified base_external_dbsource/__init__.py (+24/-0) base_external_dbsource/__openerp__.py (+66/-0) base_external_dbsource/base_external_dbsource.py (+175/-0) base_external_dbsource/base_external_dbsource_demo.xml (+15/-0) base_external_dbsource/base_external_dbsource_view.xml (+54/-0) base_external_dbsource/security/ir.model.access.csv (+2/-0) base_external_dbsource/test/dbsource_connect.yml (+5/-0) import_odbc/__init__.py (+24/-0) import_odbc/__openerp__.py (+88/-0) import_odbc/import_odbc.py (+218/-0) import_odbc/import_odbc_demo.xml (+15/-0) import_odbc/import_odbc_view.xml (+90/-0) import_odbc/security/ir.model.access.csv (+2/-0)
To merge this branch:	bzr merge lp:~openerp-community/server-env-tools/7.0-modules-from-openobject-extension
Related bugs:	Link a bug report
Reviewer	Review Type	Date Requested	Status
Yannick Vaucher @ Camptocamp		2014-01-07	Approve on 2014-07-04
Mario Arias (community)	code review and test		Needs Fixing on 2014-03-21
Alexandre Fayolle - camptocamp	tried to install	2014-01-07	Needs Fixing on 2014-03-20
Nicolas Bessi - Camptocamp (community)		2014-01-07	Approve on 2014-03-18
Review via email: mp+200744@code.launchpad.net