def connect(self):
"""
Connects to the database.
"""
StaticDataLayer.connect(self._host,
self._user,
self._password,
self._database)
def _drop_routine(self):
"""
Drops the stored routine if it exists.
"""
if self._rdbms_old_metadata:
if self._rdbms_old_metadata['type'].strip() == 'P':
sql = "drop procedure [%s].[%s]" % (self._rdbms_old_metadata['schema_name'], self._routine_base_name)
elif self._rdbms_old_metadata['type'].strip() in ('FN', 'TF'):
sql = "drop function [%s].[%s]" % (self._rdbms_old_metadata['schema_name'], self._routine_base_name)
else:
raise Exception("Unknown routine type '%s'." % self._rdbms_old_metadata['type'])
StaticDataLayer.execute_none(sql)
def _get_routine_parameters_info(self):
query = """
select par.name parameter_name
, typ.name type_name
, typ.max_length
, typ.precision
, typ.scale
from sys.schemas scm
inner join sys.all_objects prc on prc.[schema_id] = scm.[schema_id]
inner join sys.all_parameters par on par.[object_id] = prc.[object_id]
inner join sys.types typ on typ.user_type_id = par.system_type_id
where scm.name = '%s'
and prc.name = '%s'
order by par.parameter_id""" % (self._routines_schema_name, self._routine_base_name)
routine_parameters = StaticDataLayer.execute_rows(query)
if len(routine_parameters) != 0:
for routine_parameter in routine_parameters:
if routine_parameter['parameter_name']:
parameter_name = routine_parameter['parameter_name'][1:]
value = routine_parameter['type_name']
self._parameters.append({'name': parameter_name,
'data_type': routine_parameter['type_name'],
'data_type_descriptor': value})
def _get_routine_parameters_info(self):
query = """
select par.name parameter_name
, typ.name type_name
, typ.max_length
, typ.precision
, typ.scale
from sys.schemas scm
inner join sys.all_objects prc on prc.[schema_id] = scm.[schema_id]
inner join sys.all_parameters par on par.[object_id] = prc.[object_id]
inner join sys.types typ on typ.user_type_id = par.system_type_id
where scm.name = '%s'
and prc.name = '%s'
order by par.parameter_id""" % (self._routines_schema_name,
self._routine_base_name)
routine_parameters = StaticDataLayer.execute_rows(query)
if len(routine_parameters) != 0:
for routine_parameter in routine_parameters:
if routine_parameter['parameter_name']:
parameter_name = routine_parameter['parameter_name'][1:]
value = routine_parameter['type_name']
self._parameters.append({
'name':
parameter_name,
'data_type':
routine_parameter['type_name'],
'data_type_descriptor':
value
})
def _drop_obsolete_routines(self):
"""
Drops obsolete stored routines (i.e. stored routines that exits in the current schema but for
which we don't have a source file).
"""
for routine_name, values in self._rdbms_old_metadata.items():
if routine_name not in self._source_file_names:
if values['routine_type'].strip() == 'P':
print("Dropping procedure %s.%s" % (values['schema_name'], values['routine_name']))
sql = "drop procedure [%s].[%s]" % (values['schema_name'], values['routine_name'])
elif values['routine_type'].strip() in ('FN', 'TF'):
print("Dropping function %s.%s" % (values['schema_name'], values['routine_name']))
sql = "drop function [%s].[%s]" % (values['schema_name'], values['routine_name'])
else:
raise Exception("Unknown routine type '%s'." % values['type'])
StaticDataLayer.execute_none(sql)
def _drop_routine(self):
"""
Drops the stored routine if it exists.
"""
if self._rdbms_old_metadata:
if self._rdbms_old_metadata['type'].strip() == 'P':
sql = "drop procedure [%s].[%s]" % (
self._rdbms_old_metadata['schema_name'],
self._routine_base_name)
elif self._rdbms_old_metadata['type'].strip() in ('FN', 'TF'):
sql = "drop function [%s].[%s]" % (
self._rdbms_old_metadata['schema_name'],
self._routine_base_name)
else:
raise Exception("Unknown routine type '%s'." %
self._rdbms_old_metadata['type'])
StaticDataLayer.execute_none(sql)
def _load_routine_file(self):
"""
Loads the stored routine into the SQL Server instance.
"""
print("Loading %s %s" % (self._routine_type, self._routine_name))
self._set_magic_constants()
routine_source = []
i = 0
for line in self._routine_source_code_lines:
new_line = line
self._replace['__LINE__'] = "'%d'" % (i + 1)
for search, replace in self._replace.items():
tmp = re.findall(search, new_line, re.IGNORECASE)
if tmp:
new_line = new_line.replace(tmp[0], replace)
routine_source.append(new_line)
i += 1
routine_source = "\n".join(routine_source)
self._unset_magic_constants()
if self._rdbms_old_metadata:
if self._pystratum_old_metadata and self._pystratum_old_metadata[
'designation'] == self._pystratum_metadata['designation']:
p = re.compile("(create\\s+(procedure|function))",
re.IGNORECASE)
matches = p.findall(routine_source)
if matches:
routine_source = routine_source.replace(
matches[0][0], 'alter %s' % matches[0][1])
else:
print(
"Error: Unable to find the stored routine type in modified source of file '%s'."
% self._source_filename)
else:
self._drop_routine()
StaticDataLayer.execute_none(routine_source)
def _get_labels(self):
"""
Gets all primary key labels from the MySQL database.
"""
query_string = """
select scm.name schema_name
, tab.name table_name
, cl1.name label
, cl2.name id
from sys.schemas scm
inner join sys.tables tab on tab.[schema_id] = scm.[schema_id]
inner join sys.all_columns cl1 on cl1.[object_id] = tab.[object_id]
inner join sys.all_columns cl2 on cl2.[object_id] = tab.[object_id]
where cl1.name like '%_label'
and cl2.name like '%_id'
and cl2.is_identity = 1"""
tables = StaticDataLayer.execute_rows(query_string)
for table in tables:
query_string = """
select tab.[%s] id
, tab.[%s] label
from [%s].[%s].[%s] tab
where nullif(tab.[%s],'') is not null""" \
% (table['id'],
table['label'],
self._database,
table['schema_name'],
table['table_name'],
table['label'])
rows = StaticDataLayer.execute_rows(query_string)
for row in rows:
if row['label'] not in self._labels:
self._labels[row['label']] = row['id']
else:
# todo improve exception.
Exception("Duplicate label '%s'")
def tst_test_rows_with_index1(p_count):
ret = {}
rows = StaticDataLayer.execute_rows('exec [dbo].[tst_test_rows_with_index1] %s', p_count)
for row in rows:
if row['tst_c01'] in ret:
if row['tst_c02'] in ret[row['tst_c01']]:
ret[row['tst_c01']][row['tst_c02']].append(row)
else:
ret[row['tst_c01']][row['tst_c02']] = [row]
else:
ret[row['tst_c01']] = {row['tst_c02']: [row]}
return ret
def _get_columns(self):
"""
Retrieves metadata all columns in the MySQL schema.
"""
query = """
select scm.name schema_name
, tab.name table_name
, col.name column_name
, isnull(stp.name,utp.name) data_type
, col.max_length
, col.precision
, col.scale
, col.column_id
from sys.columns col
inner join sys.types utp on utp.user_type_id = col.user_type_id and
utp.system_type_id = col.system_type_id
left outer join sys.types stp on utp.is_user_defined = 1 and
stp.is_user_defined = 0 and
utp.system_type_id = stp.system_type_id and
utp.user_type_id <> stp.user_type_id and
stp.user_type_id = stp.system_type_id
inner join sys.tables tab on col.object_id = tab.object_id
inner join sys.schemas scm on tab.schema_id = scm.schema_id
where tab.type in ('U','S','V')
order by scm.name
, tab.name
, col.column_id"""
rows = StaticDataLayer.execute_rows(query)
for row in rows:
row['length'] = MsSqlConstants.derive_field_length(row)
if row['schema_name'] in self._columns:
if row['table_name'] in self._columns[row['schema_name']]:
if row['column_name'] in self._columns[row['schema_name']][
row['table_name']]:
pass
else:
self._columns[row['schema_name']][row['table_name']][
row['column_name']] = row
else:
self._columns[row['schema_name']][row['table_name']] = {
row['column_name']: row
}
else:
self._columns[row['schema_name']] = {
row['table_name']: {
row['column_name']: row
}
}
def tst_test_rows_with_index1(p_count):
ret = {}
rows = StaticDataLayer.execute_rows(
'exec [dbo].[tst_test_rows_with_index1] %s', p_count)
for row in rows:
if row['tst_c01'] in ret:
if row['tst_c02'] in ret[row['tst_c01']]:
ret[row['tst_c01']][row['tst_c02']].append(row)
else:
ret[row['tst_c01']][row['tst_c02']] = [row]
else:
ret[row['tst_c01']] = {row['tst_c02']: [row]}
return ret
def _load_routine_file(self):
"""
Loads the stored routine into the SQL Server instance.
"""
print("Loading %s %s" % (self._routine_type, self._routine_name))
self._set_magic_constants()
routine_source = []
i = 0
for line in self._routine_source_code_lines:
new_line = line
self._replace['__LINE__'] = "'%d'" % (i + 1)
for search, replace in self._replace.items():
tmp = re.findall(search, new_line, re.IGNORECASE)
if tmp:
new_line = new_line.replace(tmp[0], replace)
routine_source.append(new_line)
i += 1
routine_source = "\n".join(routine_source)
self._unset_magic_constants()
if self._rdbms_old_metadata:
if self._pystratum_old_metadata and self._pystratum_old_metadata['designation'] == self._pystratum_metadata['designation']:
p = re.compile("(create\\s+(procedure|function))", re.IGNORECASE)
matches = p.findall(routine_source)
if matches:
routine_source = routine_source.replace(matches[0][0], 'alter %s' % matches[0][1])
else:
print("Error: Unable to find the stored routine type in modified source of file '%s'." %
self._source_filename)
else:
self._drop_routine()
StaticDataLayer.execute_none(routine_source)
def tst_parameter_types02(tst_bigint, tst_int, tst_smallint, tst_tinyint,
tst_bit, tst_money, tst_smallmoney, tst_decimal,
tst_numeric, tst_float, tst_real, tst_date,
tst_datetime, tst_datetime2, tst_datetimeoffset,
tst_smalldatetime, tst_time, tst_char,
tst_varchar, tst_text, tst_nchar, tst_nvarchar,
tst_ntext, tst_binary, tst_varbinary, tst_image,
tst_xml):
return StaticDataLayer.execute_none(
'exec [dbo].[tst_parameter_types02] %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s',
tst_bigint, tst_int, tst_smallint, tst_tinyint, tst_bit, tst_money,
tst_smallmoney, tst_decimal, tst_numeric, tst_float, tst_real,
tst_date, tst_datetime, tst_datetime2, tst_datetimeoffset,
tst_smalldatetime, tst_time, tst_char, tst_varchar, tst_text,
tst_nchar, tst_nvarchar, tst_ntext, tst_binary, tst_varbinary,
tst_image, tst_xml)
def tst_parameter_types01(p_tst_bigint, p_tst_binary, p_tst_bit,
p_tst_char, p_tst_date, p_tst_datetime,
p_tst_datetime2, p_tst_datetimeoffset,
p_tst_decimal, p_tst_float, p_tst_int,
p_tst_money, p_tst_nchar, p_tst_numeric,
p_tst_nvarchar, p_tst_real, p_tst_smalldatetime,
p_tst_smallint, p_tst_smallmoney, p_tst_time,
p_tst_tinyint, p_tst_varbinary, p_tst_varchar):
return StaticDataLayer.execute_none(
'exec [dbo].[tst_parameter_types01] %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s',
p_tst_bigint, p_tst_binary, p_tst_bit, p_tst_char, p_tst_date,
p_tst_datetime, p_tst_datetime2, p_tst_datetimeoffset,
p_tst_decimal, p_tst_float, p_tst_int, p_tst_money, p_tst_nchar,
p_tst_numeric, p_tst_nvarchar, p_tst_real, p_tst_smalldatetime,
p_tst_smallint, p_tst_smallmoney, p_tst_time, p_tst_tinyint,
p_tst_varbinary, p_tst_varchar)
def tst_test_rows_with_key1(p_count):
ret = {}
rows = StaticDataLayer.execute_rows('exec [dbo].[tst_test_rows_with_key1] %s', p_count)
for row in rows:
if row['tst_c01'] in ret:
if row['tst_c02'] in ret[row['tst_c01']]:
if row['tst_c03'] in ret[row['tst_c01']][row['tst_c02']]:
raise Exception('Duplicate key for %s.' % str((row['tst_c01'], row['tst_c02'], row['tst_c03'])))
else:
ret[row['tst_c01']][row['tst_c02']][row['tst_c03']] = row
else:
ret[row['tst_c01']][row['tst_c02']] = {row['tst_c03']: row}
else:
ret[row['tst_c01']] = {row['tst_c02']: {row['tst_c03']: row}}
return ret
def _get_old_stored_routine_info(self):
"""
Retrieves information about all stored routines in the current schema.
"""
query = """
select scm.name schema_name
, prc.name routine_name
, prc.[type] routine_type
from sys.all_objects prc
inner join sys.schemas scm on scm.schema_id = prc.schema_id
where prc.type in ('P','FN','TF')
and scm.name <> 'sys'
and prc.is_ms_shipped=0"""
rows = StaticDataLayer.execute_rows(query)
self._rdbms_old_metadata = {}
for row in rows:
self._rdbms_old_metadata[row['schema_name'] + '.' + row['routine_name']] = row
def tst_test_rows_with_key1(p_count):
ret = {}
rows = StaticDataLayer.execute_rows(
'exec [dbo].[tst_test_rows_with_key1] %s', p_count)
for row in rows:
if row['tst_c01'] in ret:
if row['tst_c02'] in ret[row['tst_c01']]:
if row['tst_c03'] in ret[row['tst_c01']][row['tst_c02']]:
raise Exception('Duplicate key for %s.' % str(
(row['tst_c01'], row['tst_c02'], row['tst_c03'])))
else:
ret[row['tst_c01']][row['tst_c02']][
row['tst_c03']] = row
else:
ret[row['tst_c01']][row['tst_c02']] = {row['tst_c03']: row}
else:
ret[row['tst_c01']] = {row['tst_c02']: {row['tst_c03']: row}}
return ret
def _get_column_type(self):
"""
Selects schema, table, column names and the column types from the SQL Server instance and saves them as replace
pairs.
"""
sql = """
select scm.name schema_name
, tab.name table_name
, col.name column_name
, isnull(stp.name,utp.name) data_type
, col.max_length
, col.precision
, col.scale
from sys.columns col
inner join sys.types utp on utp.user_type_id = col.user_type_id and
utp.system_type_id = col.system_type_id
left outer join sys.types stp on utp.is_user_defined = 1 and
stp.is_user_defined = 0 and
utp.system_type_id = stp.system_type_id and
utp.user_type_id <> stp.user_type_id and
stp.user_type_id = stp.system_type_id
inner join sys.tables tab on col.object_id = tab.object_id
inner join sys.schemas scm on tab.schema_id = scm.schema_id
where tab.type in ('U','S','V')
order by scm.name
, tab.name
, col.column_id"""
rows = StaticDataLayer.execute_rows(sql)
for row in rows:
key = '@%s.%s.%s%%type@' % (row['schema_name'], row['table_name'], row['column_name'])
key = key.lower()
value = self._derive_data_type(row)
self._replace_pairs[key] = value
def tst_test_function(p_a, p_b):
return StaticDataLayer.execute_singleton1(
'select [dbo].[tst_test_function](%s, %s)', p_a, p_b)
def connect(self):
"""
Connects to the database.
"""
StaticDataLayer.connect(self._host, self._user, self._password,
self._database)
def tst_test_rows(p_count):
return StaticDataLayer.execute_rows('exec [dbo].[tst_test_rows] %s', p_count)
def tst_test_function(p_a, p_b):
return StaticDataLayer.execute_singleton1('select [dbo].[tst_test_function](%s, %s)', p_a, p_b)
def tst_parameter_types02(tst_bigint, tst_int, tst_smallint, tst_tinyint, tst_bit, tst_money, tst_smallmoney, tst_decimal, tst_numeric, tst_float, tst_real, tst_date, tst_datetime, tst_datetime2, tst_datetimeoffset, tst_smalldatetime, tst_time, tst_char, tst_varchar, tst_text, tst_nchar, tst_nvarchar, tst_ntext, tst_binary, tst_varbinary, tst_image, tst_xml):
return StaticDataLayer.execute_none('exec [dbo].[tst_parameter_types02] %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s', tst_bigint, tst_int, tst_smallint, tst_tinyint, tst_bit, tst_money, tst_smallmoney, tst_decimal, tst_numeric, tst_float, tst_real, tst_date, tst_datetime, tst_datetime2, tst_datetimeoffset, tst_smalldatetime, tst_time, tst_char, tst_varchar, tst_text, tst_nchar, tst_nvarchar, tst_ntext, tst_binary, tst_varbinary, tst_image, tst_xml)
def tst_parameter_types01(p_tst_bigint, p_tst_binary, p_tst_bit, p_tst_char, p_tst_date, p_tst_datetime, p_tst_datetime2, p_tst_datetimeoffset, p_tst_decimal, p_tst_float, p_tst_int, p_tst_money, p_tst_nchar, p_tst_numeric, p_tst_nvarchar, p_tst_real, p_tst_smalldatetime, p_tst_smallint, p_tst_smallmoney, p_tst_time, p_tst_tinyint, p_tst_varbinary, p_tst_varchar):
return StaticDataLayer.execute_none('exec [dbo].[tst_parameter_types01] %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s', p_tst_bigint, p_tst_binary, p_tst_bit, p_tst_char, p_tst_date, p_tst_datetime, p_tst_datetime2, p_tst_datetimeoffset, p_tst_decimal, p_tst_float, p_tst_int, p_tst_money, p_tst_nchar, p_tst_numeric, p_tst_nvarchar, p_tst_real, p_tst_smalldatetime, p_tst_smallint, p_tst_smallmoney, p_tst_time, p_tst_tinyint, p_tst_varbinary, p_tst_varchar)
def disconnect(self):
"""
Disconnects from the database.
"""
StaticDataLayer.disconnect()
def tst_test_singleton1(p_count):
return StaticDataLayer.execute_singleton1(
'exec [dbo].[tst_test_singleton1] %s', p_count)
def tst_magic_constant02():
return StaticDataLayer.execute_singleton1('exec [dbo].[tst_magic_constant02]')
def tst_test_rows(p_count):
return StaticDataLayer.execute_rows('exec [dbo].[tst_test_rows] %s',
p_count)
def disconnect(self):
"""
Disconnects from the database.
"""
StaticDataLayer.disconnect()
def tst_test_singleton1(p_count):
return StaticDataLayer.execute_singleton1('exec [dbo].[tst_test_singleton1] %s', p_count)
def tst_magic_constant02():
return StaticDataLayer.execute_singleton1(
'exec [dbo].[tst_magic_constant02]')
