2
0
-- /opt/MODULOS-6.1/import_odbc.py	2013-05-15 08:23:59.158992062 +0000
0
++ /opt/MODULOS-6.1/import_odbc_base_external_dbsource/import_odbc/import_odbc.py	2013-05-13 09:58:18.978992838 +0000
3
@@ -134,7 +134,7 @@
4
134
            model_name = obj.model_target.model
134
            model_name = obj.model_target.model
5
135
            model_obj  = self.pool.get(model_name)
135
            model_obj  = self.pool.get(model_name)
6
136
            xml_prefix = model_name.replace('.', '_') + "_id_"
136
            xml_prefix = model_name.replace('.', '_') + "_id_"
8
137
            log = {'start_run': datetime.now().replace(microsecond=0),
137
            log = {'start_run': datetime.today().replace(microsecond=0),
9
138
                   'last_run': None,
138
                   'last_run': None,
10
139
                   'last_record_count': 0,
139
                   'last_record_count': 0,
11
140
                   'last_error_count': 0,
140
                   'last_error_count': 0,
12
@@ -144,7 +144,7 @@
13
144
144
14
145
            #Prepare SQL sentence; replace "%s" with the last_sync date
145
            #Prepare SQL sentence; replace "%s" with the last_sync date
15
146
            if obj.last_sync: sync = datetime.strptime(obj.last_sync, "%Y-%m-%d %H:%M:%S")
146
            if obj.last_sync: sync = datetime.strptime(obj.last_sync, "%Y-%m-%d %H:%M:%S")
17
147
            else:             sync = datetime.datetime(1900, 1, 1, 0, 0, 0)
147
            else:             sync = datetime(1900, 1, 1, 0, 0, 0)
18
148
            params = {'sync': sync}
148
            params = {'sync': sync}
19
149
            res    = db_model.execute(cr, uid, [obj.dbsource_id.id], obj.sql_source, params, metadata=True)
149
            res    = db_model.execute(cr, uid, [obj.dbsource_id.id], obj.sql_source, params, metadata=True)
20
150
                
150
                
21
@@ -183,7 +183,7 @@
22
183
            if log['last_log']: 
183
            if log['last_log']: 
23
184
                log['last_log'].insert(0, 'LEVEL|== Line ==    |== Relationship ==|== Message ==')
184
                log['last_log'].insert(0, 'LEVEL|== Line ==    |== Relationship ==|== Message ==')
24
185
            log.update( {'last_log': '\n'.join(log['last_log'])} )
185
            log.update( {'last_log': '\n'.join(log['last_log'])} )
26
186
            log.update({ 'last_run': datetime.now().replace(microsecond=0) }) #second=0, 
186
            log.update({ 'last_run': datetime.today().replace(microsecond=0) }) #second=0, 
27
187
            self.write(cr, uid, [obj.id], log)
187
            self.write(cr, uid, [obj.id], log)
28
188
        #Finished
188
        #Finished
29
189
        _logger.debug('Import job FINISHED.')
189
        _logger.debug('Import job FINISHED.')
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-01-24 13:37:33 +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
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-01-24 13:37:33 +0000
34
@@ -0,0 +1,61 @@
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': '61.3',
59
25
    'category': 'Tools',
60
26
    'description': """
61
27
Connect your OpenERP server to external databases to exchange data through SQL.
62
28
Supports PostgreSQL, MySQL, SQLite, MS SQL Server, Oracle and ODBC.
63
29
64
30
Configure and test your connections at:
65
31
Settings > Technical > Database Structure > Database Sources
66
32
67
33
Depending on the type of database you are conencting, you will to install
68
34
additional libraries on your system:
69
35
70
36
  ODBC: packages unixodbc and python-pyodbc.
71
37
  MS SQL Server: FreeTDS driver (tdsodbc package).
72
38
  Oracle: Oracle Instant Client and cx_Oracle python library.
73
39
""",
74
40
    'author': 'Daniel Reis',
75
41
    'website': 'http://launchpad.net/addons-tko',
76
42
    'images': [
77
43
        'images/screenshot01.png',
78
44
    ],
79
45
    'depends': [
80
46
        'base',
81
47
    ],
82
48
    'init': [],
83
49
    'data': [
84
50
        'base_external_dbsource_view.xml',
85
51
        'security/ir.model.access.csv',
86
52
    ],
87
53
    'demo': [
88
54
        'base_external_dbsource_demo.xml',
89
55
    ],
90
56
    'test': [
91
57
        'test/dbsource_connect.yml',
92
58
    ],
93
59
}
94
60
95
61
# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:
96
0
62
97
=== added file 'base_external_dbsource/base_external_dbsource.py'
98
--- base_external_dbsource/base_external_dbsource.py	1970-01-01 00:00:00 +0000
99
+++ base_external_dbsource/base_external_dbsource.py	2014-01-24 13:37:33 +0000
100
@@ -0,0 +1,178 @@
101
1
# -*- coding: utf-8 -*-
102
2
##############################################################################
103
3
#
104
4
#    Daniel Reis
105
5
#    2011
106
6
#
107
7
#    This program is free software: you can redistribute it and/or modify
108
8
#    it under the terms of the GNU Affero General Public License as
109
9
#    published by the Free Software Foundation, either version 3 of the
110
10
#    License, or (at your option) any later version.
111
11
#
112
12
#    This program is distributed in the hope that it will be useful,
113
13
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
114
14
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
115
15
#    GNU Affero General Public License for more details.
116
16
#
117
17
#    You should have received a copy of the GNU Affero General Public License
118
18
#    along with this program.  If not, see <http://www.gnu.org/licenses/>.
119
19
#
120
20
##############################################################################
121
21
122
22
import os
123
23
from openerp.osv import fields, orm
124
24
from openerp.tools.translate import _
125
25
import openerp.tools as tools
126
26
import logging
127
27
_logger = logging.getLogger(__name__)
128
28
129
29
CONNECTORS = []
130
30
131
31
try:
132
32
    import sqlalchemy
133
33
    import pymssql  # lint:ok
134
34
    CONNECTORS.append(('mssql', 'Microsoft SQL Server'))
135
35
except:
136
36
    _logger.info('MS SQL Server not available. Please install "slqalchemy"'
137
37
                 ' and ''"pymssql" python package.')
138
38
139
39
try:
140
40
    import sqlalchemy  # lint:ok
141
41
    import MySQLdb  # lint:ok
142
42
    CONNECTORS.append(('mysql', 'MySQL'))
143
43
except:
144
44
    _logger.info('MySQL not available. Please install "slqalchemy"'
145
45
                 ' and "mysqldb" python package.')
146
46
147
47
try:
148
48
    import pyodbc
149
49
    CONNECTORS.append(('pyodbc', 'ODBC'))
150
50
except:
151
51
    _logger.info('ODBC libraries not available. Please install "unixodbc"'
152
52
                 ' and "python-pyodbc" packages.')
153
53
154
54
try:
155
55
    import cx_Oracle
156
56
    CONNECTORS.append(('cx_Oracle', 'Oracle'))
157
57
except:
158
58
    _logger.info('Oracle libraries not available. Please install "cx_Oracle"'
159
59
                 ' python package.')
160
60
161
61
import psycopg2
162
62
CONNECTORS.append(('postgresql', 'PostgreSQL'))
163
63
164
64
try:
165
65
    import sqlalchemy  # lint:ok
166
66
    CONNECTORS.append(('sqlite', 'SQLite'))
167
67
except:
168
68
    _logger.info('SQLAlchemy not available. Please install "slqalchemy"'
169
69
                 ' python package.')
170
70
171
71
172
72
class BaseExternalDBSource(orm.Model):
173
73
    _name = "base.external.dbsource"
174
74
    _description = 'External Database Sources'
175
75
    _columns = {
176
76
        'name': fields.char('Datasource name', required=True, size=64),
177
77
        'conn_string': fields.text('Connection string', help="""\
178
78
Sample connection strings:
179
79
- Microsoft SQL Server: mssql+pymssql://username:%s@server:port/dbname?charset=utf8
180
80
- MySQL: mysql://user:%s@server:port/dbname
181
81
- ODBC: DRIVER={FreeTDS};SERVER=server.address;Database=mydb;UID=sa
182
82
- ORACLE: username/%s@//server.address:port/instance
183
83
- PostgreSQL: dbname='template1' user='dbuser' host='localhost' port='5432' password=%s
184
84
- SQLite: sqlite:///test.db
185
85
"""),
186
86
        'password': fields.char('Password', size=40),
187
87
        'connector': fields.selection(CONNECTORS, 'Connector', required=True,
188
88
            help="If a connector is missing from the list, check the "
189
89
                 " log to confirm that the required componentes"
190
90
                 " were detected."),
191
91
    }
192
92
193
93
    def conn_open(self, cr, uid, id1):
194
94
        """
195
95
        Create and return a connection defined in
196
96
        the id1 base_external_dbsource record
197
97
        """
198
98
        #Get dbsource record
199
99
        data = self.browse(cr, uid, id1)
200
100
        #Build the full connection string
201
101
        connStr = data.conn_string
202
102
        if data.password:
203
103
            if '%s' not in data.conn_string:
204
104
                connStr += ';PWD=%s'
205
105
            connStr = connStr % data.password
206
106
        #Try to connect
207
107
        if data.connector == 'cx_Oracle':
208
108
            os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.UTF8'
209
109
            conn = cx_Oracle.connect(connStr)
210
110
        elif data.connector == 'pyodbc':
211
111
            conn = pyodbc.connect(connStr)
212
112
        elif data.connector in ('sqlite', 'mysql', 'mssql'):
213
113
            conn = sqlalchemy.create_engine(connStr).connect()
214
114
        elif data.connector == 'postgresql':
215
115
            conn = psycopg2.connect(connStr)
216
116
217
117
        return conn
218
118
219
119
    def execute(self, cr, uid, ids, sqlquery, sqlparams=None, metadata=False,
220
120
                context=None):
221
121
        """
222
122
        Executes SQL and returns a list of rows.
223
123
        `sqlparams` can be a dict of values, that can be referenced in the SQL
224
124
        statement using "%(key)s" or, in the case of Oracle, ":key".
225
125
        Example:
226
126
            sqlquery = "select * from mytable "
227
127
                       "where city = %(city)s and date > %(dt)s"
228
128
            params = {'city': 'Lisbon', 'dt': datetime.datetime(2000, 12, 31)}
229
129
        If `metadata==True`, it will instead return a dict containing the
230
130
        rows list and  the columns list, in the format:
231
131
            { 'cols': [ 'col_a', 'col_b', ...]
232
132
            , 'rows': [ (a0, b0, ...), (a1, b1, ...), ...] }
233
133
        """
234
134
        sqlparams = sqlparams or []
235
135
        data = self.browse(cr, uid, ids)
236
136
        rows, cols = list(), list()
237
137
        for obj in data:
238
138
            conn = self.conn_open(cr, uid, obj.id)
239
139
            if obj.connector in ["sqlite", "mysql", "mssql"]:
240
140
                #using sqlalchemy
241
141
                cur = conn.execute(sqlquery, sqlparams)
242
142
                if metadata:
243
143
                    cols = cur.keys()
244
144
                rows = [r for r in cur]
245
145
            else:
246
146
                #using other db connectors
247
147
                cur = conn.cursor()
248
148
                cur.execute(sqlquery, sqlparams)
249
149
                if metadata:
250
150
                    cols = [d[0] for d in cur.description]
251
151
                rows = cur.fetchall()
252
152
            conn.close()
253
153
        if metadata:
254
154
            return{'cols': cols, 'rows': rows}
255
155
        else:
256
156
            return rows
257
157
258
158
    def connection_test(self, cr, uid, ids, context=None):
259
159
        """Test a connection. Returns an os"""
260
160
        for obj in self.browse(cr, uid, ids, context):
261
161
            conn = False
262
162
            try:
263
163
                conn = self.conn_open(cr, uid, obj.id)
264
164
            except Exception, e:
265
165
                raise orm.except_orm(
266
166
                    _("Connection test failed!"),
267
167
                    _("Here is what we got instead:\n %s") % tools.ustr(e))
268
168
            finally:
269
169
                try:
270
170
                    if conn:
271
171
                        conn.close()
272
172
                except Exception:
273
173
                    # ignored, just a consequence of the previous exception
274
174
                    pass
275
175
        #TODO: if OK a (wizard) message box should be displayed
276
176
        raise orm.except_orm(
277
177
            _("Connection test succeeded!"),
278
178
            _("Everything seems properly set up!"))
279
0
179
280
=== added file 'base_external_dbsource/base_external_dbsource_demo.xml'
281
--- base_external_dbsource/base_external_dbsource_demo.xml	1970-01-01 00:00:00 +0000
282
+++ base_external_dbsource/base_external_dbsource_demo.xml	2014-01-24 13:37:33 +0000
283
@@ -0,0 +1,13 @@
284
1
<?xml version="1.0"?>
285
2
<openerp>
286
3
    <data>
287
4
288
5
        <record model="base.external.dbsource" id="demo_postgre">
289
6
            <field name="name">PostgreSQL local</field>
290
7
            <field name="conn_string">dbname='postgres' password=%s</field>
291
8
            <field name="password">postgresql</field>
292
9
            <field name="connector">postgresql</field>
293
10
        </record>
294
11
295
12
    </data>
296
13
</openerp>
297
0
14
298
=== added file 'base_external_dbsource/base_external_dbsource_view.xml'
299
--- base_external_dbsource/base_external_dbsource_view.xml	1970-01-01 00:00:00 +0000
300
+++ base_external_dbsource/base_external_dbsource_view.xml	2014-01-24 13:37:33 +0000
301
@@ -0,0 +1,50 @@
302
1
<?xml version="1.0"?>
303
2
<openerp>
304
3
    <data>
305
4
306
5
        <record model="ir.ui.view" id="view_dbsource_tree">
307
6
            <field name="name">base.external.dbsource.tree</field>
308
7
            <field name="model">base.external.dbsource</field>
309
8
            <field name="type">tree</field>
310
9
            <field name="arch" type="xml">
311
10
                <tree string="External DB Sources">
312
11
                    <field name="name"/>
313
12
                    <field name="connector"/>
314
13
                    <field name="conn_string"/>
315
14
                </tree>
316
15
            </field>
317
16
        </record>
318
17
        
319
18
        <record model="ir.ui.view" id="view_dbsource_form">
320
19
            <field name="name">base.external.dbsource.form</field>
321
20
            <field name="model">base.external.dbsource</field>
322
21
            <field name="type">form</field>
323
22
            <field name="arch" type="xml">
324
23
                <form string="External DB Source">
325
24
                    <field name="name"/>
326
25
                    <field name="connector"/>
327
26
                    <field name="conn_string"/>
328
27
                    <field name="password" password="True"/>
329
28
                    <button name="connection_test" string="Test Connection" 
330
29
                            type="object" icon="gtk-network"/>
331
30
                </form>
332
31
            </field>
333
32
        </record>
334
33
335
34
        <record model="ir.actions.act_window" id="action_dbsource">
336
35
            <field name="name">External Database Sources</field>
337
36
            <field name="res_model">base.external.dbsource</field>
338
37
            <field name="view_type">form</field>
339
38
            <field name="view_mode">tree,form</field>
340
39
            <field name="view_id" ref="view_dbsource_tree"/>
341
40
        </record>
342
41
343
42
        <menuitem name="Database Sources"
344
43
                  id="menu_dbsource" 
345
44
                  parent="base.next_id_9" 
346
45
                  action="action_dbsource"/>
347
46
348
47
    </data>
349
48
</openerp>
350
49
351
50
352
0
51
353
=== added directory 'base_external_dbsource/images'
354
=== added file 'base_external_dbsource/images/screenshot01.png'
355
1
Binary files base_external_dbsource/images/screenshot01.png	1970-01-01 00:00:00 +0000 and base_external_dbsource/images/screenshot01.png	2014-01-24 13:37:33 +0000 differ
52
Binary files base_external_dbsource/images/screenshot01.png	1970-01-01 00:00:00 +0000 and base_external_dbsource/images/screenshot01.png	2014-01-24 13:37:33 +0000 differ
356
=== added directory 'base_external_dbsource/security'
357
=== added file 'base_external_dbsource/security/ir.model.access.csv'
358
--- base_external_dbsource/security/ir.model.access.csv	1970-01-01 00:00:00 +0000
359
+++ base_external_dbsource/security/ir.model.access.csv	2014-01-24 13:37:33 +0000
360
@@ -0,0 +1,2 @@
361
1
id,name,model_id:id,group_id:id,perm_read,perm_write,perm_create,perm_unlink
362
2
access_base_external_dbsource_group_system,bae_external_dbsource_group_system,model_base_external_dbsource,base.group_system,1,1,1,1
363
0
3
364
=== added directory 'base_external_dbsource/test'
365
=== added file 'base_external_dbsource/test/dbsource_connect.yml'
366
--- base_external_dbsource/test/dbsource_connect.yml	1970-01-01 00:00:00 +0000
367
+++ base_external_dbsource/test/dbsource_connect.yml	2014-01-24 13:37:33 +0000
368
@@ -0,0 +1,5 @@
369
1
-
370
2
  Connect to local Postgres.
371
3
-
372
4
  !python {model: base.external.dbsource}: |
373
5
    self.connection_test(cr, uid, [ref("demo_postgre")])
Status:	Needs review
Proposed branch:	lp:~dreis-pt/openobject-extension/base_external_dbsource-v7
Merge into:	lp:openobject-extension
Diff against target:	373 lines (+333/-0) 7 files modified base_external_dbsource/__init__.py (+24/-0) base_external_dbsource/__openerp__.py (+61/-0) base_external_dbsource/base_external_dbsource.py (+178/-0) base_external_dbsource/base_external_dbsource_demo.xml (+13/-0) base_external_dbsource/base_external_dbsource_view.xml (+50/-0) base_external_dbsource/security/ir.model.access.csv (+2/-0) base_external_dbsource/test/dbsource_connect.yml (+5/-0)
To merge this branch:	bzr merge lp:~dreis-pt/openobject-extension/base_external_dbsource-v7
Related bugs:	Link a bug report
Reviewer	Review Type	Date Requested	Status
Maxime Chambreuil (http://www.savoirfairelinux.com)			Needs Fixing on 2014-01-24
David (community)			Needs Information on 2013-05-10
Alexandre Fayolle - camptocamp	code review, no test	2013-04-18	Approve on 2013-04-26
Review via email: mp+159673@code.launchpad.net
2	0	-- /opt/MODULOS-6.1/import_odbc.py 2013-05-15 08:23:59.158992062 +0000	0	++ /opt/MODULOS-6.1/import_odbc_base_external_dbsource/import_odbc/import_odbc.py 2013-05-13 09:58:18.978992838 +0000
3	@@ -134,7 +134,7 @@
4	134	model_name = obj.model_target.model	134	model_name = obj.model_target.model
5	135	model_obj = self.pool.get(model_name)	135	model_obj = self.pool.get(model_name)
6	136	xml_prefix = model_name.replace('.', '_') + "_id_"	136	xml_prefix = model_name.replace('.', '_') + "_id_"
8	137	log = {'start_run': datetime.now().replace(microsecond=0),	137	log = {'start_run': datetime.today().replace(microsecond=0),
9	138	'last_run': None,	138	'last_run': None,
10	139	'last_record_count': 0,	139	'last_record_count': 0,
11	140	'last_error_count': 0,	140	'last_error_count': 0,
12	@@ -144,7 +144,7 @@
13	144		144
14	145	#Prepare SQL sentence; replace "%s" with the last_sync date	145	#Prepare SQL sentence; replace "%s" with the last_sync date
15	146	if obj.last_sync: sync = datetime.strptime(obj.last_sync, "%Y-%m-%d %H:%M:%S")	146	if obj.last_sync: sync = datetime.strptime(obj.last_sync, "%Y-%m-%d %H:%M:%S")
17	147	else: sync = datetime.datetime(1900, 1, 1, 0, 0, 0)	147	else: sync = datetime(1900, 1, 1, 0, 0, 0)
18	148	params = {'sync': sync}	148	params = {'sync': sync}
19	149	res = db_model.execute(cr, uid, [obj.dbsource_id.id], obj.sql_source, params, metadata=True)	149	res = db_model.execute(cr, uid, [obj.dbsource_id.id], obj.sql_source, params, metadata=True)
20	150		150
21	@@ -183,7 +183,7 @@
22	183	if log['last_log']:	183	if log['last_log']:
23	184	log['last_log'].insert(0, 'LEVEL\|== Line == \|== Relationship ==\|== Message ==')	184	log['last_log'].insert(0, 'LEVEL\|== Line == \|== Relationship ==\|== Message ==')
24	185	log.update( {'last_log': '\n'.join(log['last_log'])} )	185	log.update( {'last_log': '\n'.join(log['last_log'])} )
26	186	log.update({ 'last_run': datetime.now().replace(microsecond=0) }) #second=0,	186	log.update({ 'last_run': datetime.today().replace(microsecond=0) }) #second=0,
27	187	self.write(cr, uid, [obj.id], log)	187	self.write(cr, uid, [obj.id], log)
28	188	#Finished	188	#Finished
29	189	_logger.debug('Import job FINISHED.')	189	_logger.debug('Import job FINISHED.')