savoirfairelinux-openerp team mailing list archive
-
savoirfairelinux-openerp team
-
Mailing list archive
-
Message #00070
[Merge] lp:~dreis-pt/openobject-extension/base_external_dbsource-v7 into lp:openobject-extension
Daniel Reis (SECURITAS SA) has proposed merging lp:~dreis-pt/openobject-extension/base_external_dbsource-v7 into lp:openobject-extension.
Requested reviews:
extra-addons-commiter (extra-addons-commiter)
For more details, see:
https://code.launchpad.net/~dreis-pt/openobject-extension/base_external_dbsource-v7/+merge/159673
Ported base_external_dbsource to v7.
--
https://code.launchpad.net/~dreis-pt/openobject-extension/base_external_dbsource-v7/+merge/159673
Your team extra-addons-commiter is requested to review the proposed merge of lp:~dreis-pt/openobject-extension/base_external_dbsource-v7 into lp:openobject-extension.
=== modified file 'base_external_dbsource/__openerp__.py'
--- base_external_dbsource/__openerp__.py 2012-09-12 15:19:28 +0000
+++ base_external_dbsource/__openerp__.py 2013-04-18 16:24:29 +0000
@@ -1,7 +1,7 @@
# -*- coding: utf-8 -*-
##############################################################################
#
-# Daniel Reis, 2011
+# Daniel Reis, 2011
# Additional contributions by Maxime Chambreuil, Savoir-faire Linux
#
# This program is free software: you can redistribute it and/or modify
@@ -24,18 +24,19 @@
'version': '61.3',
'category': 'Tools',
'description': """
-This module allows you to define connections to foreign databases using ODBC,
-Oracle Client or SQLAlchemy.
-
-Databases sources can be configured in Settings > Configuration -> Data sources.
-
-Depending on the database, you need:
- * to install unixodbc and python-pyodbc packages to use ODBC connections.
- * to install FreeTDS driver (tdsodbc package) and configure it through ODBC to
- connect to Microsoft SQL Server.
- * to install and configure Oracle Instant Client and cx_Oracle python library
- to connect to Oracle.
- """,
+Connect your OpenERP server to external databases to exchange data through SQL.
+Supports PostgreSQL, MySQL, SQLite, MS SQL Server, Oracle and ODBC.
+C
+Configure and test your connections at:
+Settings > Technical > Database Structure > Database Sources
+
+Depending on the type of database you are conencting, you will to install
+additional libraries on your system:
+
+ ODBC: packages unixodbc and python-pyodbc.
+ MS SQL Server: FreeTDS driver (tdsodbc package).
+ Oracle: Oracle Instant Client and cx_Oracle python library.
+""",
'author': 'Daniel Reis',
'website': 'http://launchpad.net/addons-tko',
'images': [
@@ -53,10 +54,8 @@
'base_external_dbsource_demo.xml',
],
'test': [
- 'dbsource_connect.yml',
- ],
- 'installable': True,
- 'active': False,
+ 'test/dbsource_connect.yml',
+ ],
}
# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:
=== modified file 'base_external_dbsource/base_external_dbsource.py'
--- base_external_dbsource/base_external_dbsource.py 2012-09-13 10:08:37 +0000
+++ base_external_dbsource/base_external_dbsource.py 2013-04-18 16:24:29 +0000
@@ -1,159 +1,177 @@
-# -*- coding: utf-8 -*-
-##############################################################################
-#
-# Daniel Reis
-# 2011
-#
-# This program is free software: you can redistribute it and/or modify
-# it under the terms of the GNU Affero General Public License as
-# published by the Free Software Foundation, either version 3 of the
-# License, or (at your option) any later version.
-#
-# This program is distributed in the hope that it will be useful,
-# but WITHOUT ANY WARRANTY; without even the implied warranty of
-# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-# GNU Affero General Public License for more details.
-#
-# You should have received a copy of the GNU Affero General Public License
-# along with this program. If not, see <http://www.gnu.org/licenses/>.
-#
-##############################################################################
-
-import os
-from osv import fields, osv
-from openerp.tools.translate import _
-import openerp.tools as tools
-import logging
-_logger = logging.getLogger(__name__)
-
-CONNECTORS = []
-
-try:
- import sqlalchemy
- import pymssql
- CONNECTORS.append( ('mssql', 'Microsoft SQL Server') )
-except:
- _logger.info('MS SQL Server not available. Please install "slqalchemy" and "pymssql" python package.')
-
-try:
- import sqlalchemy
- import MySQLdb
- CONNECTORS.append( ('mysql', 'MySQL') )
-except:
- _logger.info('MySQL not available. Please install "slqalchemy" and "mysqldb" python package.')
-
-try:
- import pyodbc
- CONNECTORS.append( ('pyodbc', 'ODBC') )
-except:
- _logger.info('ODBC libraries not available. Please install "unixodbc" and "python-pyodbc" packages.')
-
-try:
- import cx_Oracle
- CONNECTORS.append( ('cx_Oracle', 'Oracle') )
-except:
- _logger.info('Oracle libraries not available. Please install "cx_Oracle" python package.')
-
-import psycopg2
-CONNECTORS.append( ('postgresql', 'PostgreSQL') )
-
-try:
- import sqlalchemy
- CONNECTORS.append( ('sqlite', 'SQLite') )
-except:
- _logger.info('SQLAlchemy not available. Please install "slqalchemy" python package.')
-
-class base_external_dbsource(osv.osv):
- _name = "base.external.dbsource"
- _description = 'External Database Sources'
- _columns = {
- 'name': fields.char('Datasource name', required=True, size=64),
- 'conn_string': fields.text('Connection string', help="""\
-Sample connection strings:
-- Microsoft SQL Server: mssql+pymssql://username:%s@server:port/dbname?charset=utf8
-- MySQL: mysql://user:%s@server:port/dbname
-- ODBC: DRIVER={FreeTDS};SERVER=server.address;Database=mydb;UID=sa
-- ORACLE: username/%s@//server.address:port/instance
-- PostgreSQL: dbname='template1' user='dbuser' host='localhost' port='5432' password=%s
-- SQLite: sqlite:///test.db
-"""),
- 'password': fields.char('Password' , size=40),
- 'connector': fields.selection(CONNECTORS, 'Connector', required=True,
- help = "If a connector is missing from the list, check the " \
- + "server log to confirm that the required componentes were detected."),
- }
-
- def conn_open(self, cr, uid, id1):
- #Get dbsource record
- data = self.browse(cr, uid, id1)
- #Build the full connection string
- connStr = data.conn_string
- if data.password:
- if '%s' not in data.conn_string:
- connStr += ';PWD=%s'
- connStr = connStr % data.password
- #Try to connect
- if data.connector == 'cx_Oracle':
- os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.UTF8'
- conn = cx_Oracle.connect(connStr)
- elif data.connector == 'pyodbc':
- conn = pyodbc.connect(connStr)
- elif data.connector in ('sqlite','mysql','mssql'):
- conn = sqlalchemy.create_engine(connStr).connect()
- elif data.connector == 'postgresql':
- conn = psycopg2.connect(connStr)
-
- return conn
-
- def execute(self, cr, uid, ids, sqlquery, sqlparams=None, metadata=False, context=None):
- """Executes SQL and returns a list of rows.
-
- "sqlparams" can be a dict of values, that can be referenced in the SQL statement
- using "%(key)s" or, in the case of Oracle, ":key".
- Example:
- sqlquery = "select * from mytable where city = %(city)s and date > %(dt)s"
- params = {'city': 'Lisbon', 'dt': datetime.datetime(2000, 12, 31)}
-
- If metadata=True, it will instead return a dict containing the rows list and the columns list,
- in the format:
- { 'cols': [ 'col_a', 'col_b', ...]
- , 'rows': [ (a0, b0, ...), (a1, b1, ...), ...] }
- """
- data = self.browse(cr, uid, ids)
- rows, cols = list(), list()
- for obj in data:
- conn = self.conn_open(cr, uid, obj.id)
- if obj.connector in ["sqlite","mysql","mssql"]:
- #using sqlalchemy
- cur = conn.execute(sqlquery, sqlparams)
- if metadata: cols = cur.keys()
- rows = [r for r in cur]
- else:
- #using other db connectors
- cur = conn.cursor()
- cur.execute(sqlquery, sqlparams)
- if metadata: cols = [d[0] for d in cur.description]
- rows = cur.fetchall()
- conn.close()
- if metadata:
- return{'cols': cols, 'rows': rows}
- else:
- return rows
-
- def connection_test(self, cr, uid, ids, context=None):
- for obj in self.browse(cr, uid, ids, context):
- conn = False
- try:
- conn = self.conn_open(cr, uid, obj.id)
- except Exception, e:
- raise osv.except_osv(_("Connection test failed!"), _("Here is what we got instead:\n %s") % tools.ustr(e))
- finally:
- try:
- if conn: conn.close()
- except Exception:
- # ignored, just a consequence of the previous exception
- pass
- #TODO: if OK a (wizard) message box should be displayed
- raise osv.except_osv(_("Connection test succeeded!"), _("Everything seems properly set up!"))
-
-base_external_dbsource()
+# -*- coding: utf-8 -*-
+##############################################################################
+#
+# Daniel Reis
+# 2011
+#
+# This program is free software: you can redistribute it and/or modify
+# it under the terms of the GNU Affero General Public License as
+# published by the Free Software Foundation, either version 3 of the
+# License, or (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU Affero General Public License for more details.
+#
+# You should have received a copy of the GNU Affero General Public License
+# along with this program. If not, see <http://www.gnu.org/licenses/>.
+#
+##############################################################################
+
+import os
+from openerp.osv import fields, orm
+from openerp.tools.translate import _
+import openerp.tools as tools
+import logging
+_logger = logging.getLogger(__name__)
+
+CONNECTORS = []
+
+try:
+ import sqlalchemy
+ import pymssql # lint:ok
+ CONNECTORS.append(('mssql', 'Microsoft SQL Server'))
+except:
+ _logger.info('MS SQL Server not available. Please install "slqalchemy"'
+ ' and ''"pymssql" python package.')
+
+try:
+ import sqlalchemy # lint:ok
+ import MySQLdb # lint:ok
+ CONNECTORS.append(('mysql', 'MySQL'))
+except:
+ _logger.info('MySQL not available. Please install "slqalchemy"'
+ ' and "mysqldb" python package.')
+
+try:
+ import pyodbc
+ CONNECTORS.append(('pyodbc', 'ODBC'))
+except:
+ _logger.info('ODBC libraries not available. Please install "unixodbc"'
+ ' and "python-pyodbc" packages.')
+
+try:
+ import cx_Oracle
+ CONNECTORS.append(('cx_Oracle', 'Oracle'))
+except:
+ _logger.info('Oracle libraries not available. Please install "cx_Oracle"'
+ ' python package.')
+
+import psycopg2
+CONNECTORS.append(('postgresql', 'PostgreSQL'))
+
+try:
+ import sqlalchemy # lint:ok
+ CONNECTORS.append(('sqlite', 'SQLite'))
+except:
+ _logger.info('SQLAlchemy not available. Please install "slqalchemy"'
+ ' python package.')
+
+
+class BaseExternalDBSource(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 "
+ " log to confirm that the required componentes"
+ " were detected."),
+ }
+
+ def conn_open(self, cr, uid, id1):
+ """
+ Create and return a connection defined in
+ the id1 base_external_dbsource record
+ """
+ #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):
+ """Test a connection. Returns an os"""
+ 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!"))
=== modified file 'base_external_dbsource/base_external_dbsource_demo.xml'
--- base_external_dbsource/base_external_dbsource_demo.xml 2012-09-12 15:19:28 +0000
+++ base_external_dbsource/base_external_dbsource_demo.xml 2013-04-18 16:24:29 +0000
@@ -1,15 +1,13 @@
-<?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>
-
-
+<?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>
=== modified file 'base_external_dbsource/base_external_dbsource_view.xml'
--- base_external_dbsource/base_external_dbsource_view.xml 2012-10-30 21:07:19 +0000
+++ base_external_dbsource/base_external_dbsource_view.xml 2013-04-18 16:24:29 +0000
@@ -2,8 +2,6 @@
<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>
@@ -11,8 +9,8 @@
<field name="arch" type="xml">
<tree string="External DB Sources">
<field name="name"/>
- <field name="connector"/>
- <field name="conn_string"/>
+ <field name="connector"/>
+ <field name="conn_string"/>
</tree>
</field>
</record>
@@ -24,13 +22,11 @@
<field name="arch" type="xml">
<form string="External DB Source">
<field name="name"/>
+ <field name="connector"/>
+ <field name="conn_string"/>
<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"/>
+ <button name="connection_test" string="Test Connection"
+ type="object" icon="gtk-network"/>
</form>
</field>
</record>
=== modified file 'base_external_dbsource/images/screenshot01.png'
Binary files base_external_dbsource/images/screenshot01.png 2012-08-06 03:31:18 +0000 and base_external_dbsource/images/screenshot01.png 2013-04-18 16:24:29 +0000 differ
=== modified file 'base_external_dbsource/test/dbsource_connect.yml'
--- base_external_dbsource/test/dbsource_connect.yml 2012-09-12 15:19:28 +0000
+++ base_external_dbsource/test/dbsource_connect.yml 2013-04-18 16:24:29 +0000
@@ -1,5 +1,5 @@
-
Connect to local Postgres.
--
+-
!python {model: base.external.dbsource}: |
- self.connection_test(cr, uid, [ref("demo_postgresql")]
+ self.connection_test(cr, uid, [ref("demo_postgre")])
Follow ups
