2017-12-11 10:14:07 +01:00
|
|
|
# The COPYRIGHT file at the top level of this repository contains the full
|
|
|
|
# copyright notices and license terms.
|
2017-12-15 10:13:28 +01:00
|
|
|
from trytond.model import fields
|
2018-09-05 18:16:18 +02:00
|
|
|
from trytond.modules.edw.connector import BackendConnector
|
2017-12-11 10:14:07 +01:00
|
|
|
import pymssql
|
|
|
|
|
|
|
|
INSERT_QUERY = (
|
2018-01-04 12:27:17 +01:00
|
|
|
"""INSERT INTO [%(tablename)s] """ +
|
2017-12-11 10:14:07 +01:00
|
|
|
"""(%(fields)s) """ +
|
2017-12-15 10:13:28 +01:00
|
|
|
"""VALUES (%(values)s);""")
|
2017-12-11 10:14:07 +01:00
|
|
|
|
|
|
|
TRUNCATE_QUERY = (
|
2018-01-04 12:27:17 +01:00
|
|
|
"""TRUNCATE TABLE [%(tablename)s]; """)
|
2017-12-11 10:14:07 +01:00
|
|
|
|
|
|
|
CREATE_QUERY = (
|
|
|
|
"""IF NOT EXISTS (select * from sysobjects """ +
|
|
|
|
""" where name='%(tablename)s' and xtype='U') """ +
|
2018-01-04 12:27:17 +01:00
|
|
|
""" CREATE TABLE [%(tablename)s] (%(fields)s);""")
|
2017-12-11 10:14:07 +01:00
|
|
|
|
|
|
|
DELETE_QUERY = (
|
|
|
|
"""IF EXISTS (select * from sysobjects """ +
|
|
|
|
""" where name='%(tablename)s' and xtype='U') """ +
|
2018-01-04 12:27:17 +01:00
|
|
|
""" DROP TABLE [%(tablename)s]; """)
|
2017-12-11 10:14:07 +01:00
|
|
|
|
|
|
|
|
|
|
|
class BackendConnectorMSsql(BackendConnector):
|
|
|
|
"""MS SQL Connector"""
|
|
|
|
|
|
|
|
def connect(self):
|
|
|
|
url_server = self.uri.split("//")[1]
|
|
|
|
server = url_server.split("/")[0]
|
|
|
|
database = url_server.split("/")[1]
|
|
|
|
conn = pymssql.connect(server=server,
|
|
|
|
user=self.username, password=self.password, database=database)
|
|
|
|
return conn
|
|
|
|
|
|
|
|
def create_table(self, fields, tablename):
|
|
|
|
query = self._get_create_query(fields, tablename)
|
|
|
|
self.execute_query(query)
|
|
|
|
|
|
|
|
def fill(self, results, tablename):
|
|
|
|
query = self._get_insert_query(tablename, results)
|
|
|
|
self.execute_query(query, results)
|
|
|
|
|
|
|
|
def truncate(self, tablename):
|
|
|
|
query = self._get_truncate_query(tablename)
|
|
|
|
self.execute_query(query)
|
|
|
|
|
|
|
|
def delete_table(self, tablename):
|
|
|
|
query = self._get_delete_query(tablename)
|
|
|
|
self.execute_query(query)
|
|
|
|
|
|
|
|
def _get_create_query(self, fields, tablename):
|
|
|
|
fields_sql = []
|
|
|
|
for key, value in fields.iteritems():
|
2018-01-04 12:27:17 +01:00
|
|
|
fields_sql.append(self._format_object_name(key) + ' ' + value)
|
2017-12-11 10:14:07 +01:00
|
|
|
fields_sql = ','.join(fields_sql)
|
|
|
|
return CREATE_QUERY % {'tablename': tablename, 'fields': fields_sql}
|
|
|
|
|
|
|
|
def _get_insert_query(self, tablename, results):
|
|
|
|
result = results[0]
|
2018-01-04 12:27:17 +01:00
|
|
|
fields = [self._format_object_name(key) for key in result.keys()]
|
|
|
|
fields = ','.join(fields)
|
2017-12-11 10:14:07 +01:00
|
|
|
values = ''
|
|
|
|
for field in result.keys():
|
|
|
|
values += '%(' + field + ')s,'
|
|
|
|
values = values[:-1]
|
|
|
|
return INSERT_QUERY % {'tablename': tablename,
|
|
|
|
'fields': fields, 'values': values}
|
|
|
|
|
2018-01-04 12:27:17 +01:00
|
|
|
def _format_object_name(self, name):
|
|
|
|
return '[%s]' % name
|
|
|
|
|
2017-12-11 10:14:07 +01:00
|
|
|
def _get_truncate_query(self, tablename):
|
|
|
|
return TRUNCATE_QUERY % {'tablename': tablename}
|
|
|
|
|
|
|
|
def _get_delete_query(self, tablename):
|
|
|
|
return DELETE_QUERY % {'tablename': tablename}
|
|
|
|
|
|
|
|
def get_mapped_types(self):
|
2017-12-14 18:03:11 +01:00
|
|
|
return {
|
2017-12-15 10:13:28 +01:00
|
|
|
fields.Integer: 'int',
|
|
|
|
fields.Many2One: 'int',
|
|
|
|
fields.Char: 'nvarchar(max)',
|
|
|
|
fields.Text: 'nvarchar(max)',
|
|
|
|
fields.Selection: 'nvarchar(max)',
|
|
|
|
fields.Date: 'date',
|
|
|
|
fields.Numeric: 'numeric(32, 18)',
|
|
|
|
fields.TimeDelta: 'nvarchar(32)',
|
|
|
|
fields.Timestamp: 'datetime',
|
|
|
|
fields.Float: 'numeric(32, 18)',
|
2017-12-22 13:30:34 +01:00
|
|
|
fields.Reference: 'nvarchar(max)',
|
|
|
|
fields.Boolean: 'bit',
|
|
|
|
fields.DateTime: 'datetime',
|
|
|
|
fields.Time: 'nvarchar(32)',
|
|
|
|
fields.One2One: 'int'
|
2017-12-11 10:14:07 +01:00
|
|
|
}
|
|
|
|
|
|
|
|
def execute_query(self, query, results=None):
|
|
|
|
with self.connect() as connection:
|
|
|
|
cursor = connection.cursor()
|
|
|
|
try:
|
|
|
|
if results:
|
|
|
|
cursor.executemany(query, results)
|
|
|
|
else:
|
|
|
|
cursor.execute(query)
|
|
|
|
connection.commit()
|
|
|
|
except Exception as e:
|
|
|
|
connection.rollback()
|
|
|
|
raise e
|
|
|
|
finally:
|
|
|
|
connection.close()
|