def _get_labels(self):
"""
Gets all primary key labels from the MySQL database.
"""
query_string = """
select t1.TABLE_NAME `table_name`
, t1.COLUMN_NAME `id`
, t2.COLUMN_NAME `label`
from information_schema.COLUMNS t1
inner join information_schema.COLUMNS t2 ON t1.TABLE_NAME = t2.TABLE_NAME
where t1.TABLE_SCHEMA = database()
and t1.EXTRA = 'auto_increment'
and t2.TABLE_SCHEMA = database()
and t2.COLUMN_NAME like '%%\\_label'"""
tables = StaticDataLayer.execute_rows(query_string)
for table in tables:
query_string = """
select `%s` as `id`
, `%s` as `label`
from `%s`
where nullif(`%s`,'') is not null""" % (table['id'],
table['label'],
table['table_name'],
table['label'])
rows = StaticDataLayer.execute_rows(query_string)
for row in rows:
self._labels[row['label']] = row['id']
def _drop_routine(self):
"""
Drops the stored routine if it exists.
"""
if self._rdbms_old_metadata:
sql = "drop %s if exists %s" % (self._rdbms_old_metadata['routine_type'], self._routine_name)
StaticDataLayer.execute_none(sql)
def _get_labels(self):
"""
Gets all primary key labels from the MySQL database.
"""
query_string = """
select t1.TABLE_NAME `table_name`
, t1.COLUMN_NAME `id`
, t2.COLUMN_NAME `label`
from information_schema.COLUMNS t1
inner join information_schema.COLUMNS t2 ON t1.TABLE_NAME = t2.TABLE_NAME
where t1.TABLE_SCHEMA = database()
and t1.EXTRA = 'auto_increment'
and t2.TABLE_SCHEMA = database()
and t2.COLUMN_NAME like '%%\\_label'"""
tables = StaticDataLayer.execute_rows(query_string)
for table in tables:
query_string = """
select `%s` as `id`
, `%s` as `label`
from `%s`
where nullif(`%s`,'') is not null""" % (table['id'], table['label'],
table['table_name'], table['label'])
rows = StaticDataLayer.execute_rows(query_string)
for row in rows:
self._labels[row['label']] = row['id']
def _drop_routine(self):
"""
Drops the stored routine if it exists.
"""
if self._rdbms_old_metadata:
sql = "drop %s if exists %s" % (
self._rdbms_old_metadata['routine_type'], self._routine_name)
StaticDataLayer.execute_none(sql)
def _get_correct_sql_mode(self):
"""
Gets the SQL mode in the order as preferred by MySQL.
"""
sql = "set sql_mode = %s" % self._sql_mode
StaticDataLayer.execute_none(sql)
query = "select @@sql_mode;"
tmp = StaticDataLayer.execute_rows(query)
self._sql_mode = tmp[0]['@@sql_mode']
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:
print("Dropping %s %s" % (values['routine_type'], routine_name))
sql = "drop %s if exists %s" % (values['routine_type'], routine_name)
StaticDataLayer.execute_none(sql)
def _get_correct_sql_mode(self):
"""
Gets the SQL mode in the order as preferred by MySQL.
"""
sql = "set sql_mode = %s" % self._sql_mode
StaticDataLayer.execute_none(sql)
query = "select @@sql_mode;"
tmp = StaticDataLayer.execute_rows(query)
self._sql_mode = tmp[0]['@@sql_mode']
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:
print("Dropping %s %s" %
(values['routine_type'], routine_name))
sql = "drop %s if exists %s" % (values['routine_type'],
routine_name)
StaticDataLayer.execute_none(sql)
def connect(self):
"""
Connects to the MySQL instance.
"""
StaticDataLayer.config['host'] = self._host
StaticDataLayer.config['user'] = self._user
StaticDataLayer.config['password'] = self._password
StaticDataLayer.config['database'] = self._database
StaticDataLayer.config['port'] = self._port
StaticDataLayer.config['charset'] = self._character_set_client
StaticDataLayer.config['collation'] = self._collation_connection
StaticDataLayer.config['sql_mode'] = self._sql_mode
StaticDataLayer.connect()
def connect(self):
"""
Connects to the MySQL instance.
"""
StaticDataLayer.config['host'] = self._host
StaticDataLayer.config['user'] = self._user
StaticDataLayer.config['password'] = self._password
StaticDataLayer.config['database'] = self._database
StaticDataLayer.config['port'] = self._port
StaticDataLayer.config['charset'] = self._character_set_client
StaticDataLayer.config['collation'] = self._collation_connection
StaticDataLayer.config['sql_mode'] = self._sql_mode
StaticDataLayer.connect()
def _get_routine_parameters_info(self):
query = """
select t2.PARAMETER_NAME parameter_name
, t2.DATA_TYPE parameter_type
, t2.DTD_IDENTIFIER column_type
, t2.CHARACTER_SET_NAME character_set_name
, t2.COLLATION_NAME collation
from information_schema.ROUTINES t1
left outer join information_schema.PARAMETERS t2 on t2.SPECIFIC_SCHEMA = t1.ROUTINE_SCHEMA and
t2.SPECIFIC_NAME = t1.ROUTINE_NAME and
t2.PARAMETER_MODE is not null
where t1.ROUTINE_SCHEMA = database()
and t1.ROUTINE_NAME = '%s'""" % self._routine_name
routine_parameters = StaticDataLayer.execute_rows(query)
for routine_parameter in routine_parameters:
if routine_parameter['parameter_name']:
value = routine_parameter['column_type']
if 'character_set_name' in routine_parameter:
if routine_parameter['character_set_name']:
value += ' character set %s' % routine_parameter['character_set_name']
if 'collation' in routine_parameter:
if routine_parameter['character_set_name']:
value += ' collation %s' % routine_parameter['collation']
self._parameters.append({'name': routine_parameter['parameter_name'],
'data_type': routine_parameter['parameter_type'],
'data_type_descriptor': value})
def tst_parameter_types01(p_param00, p_param01, p_param02, p_param03,
p_param04, p_param05, p_param06, p_param07,
p_param08, p_param09, p_param10, p_param11,
p_param12, p_param13, p_param14, p_param15,
p_param16, p_param17, p_param26, p_param27):
return StaticDataLayer.execute_sp_none(
"call tst_parameter_types01(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
p_param00, p_param01, p_param02, p_param03, p_param04, p_param05,
p_param06, p_param07, p_param08, p_param09, p_param10, p_param11,
p_param12, p_param13, p_param14, p_param15, p_param16, p_param17,
p_param26, p_param27)
def _load_routine_file(self):
"""
Loads the stored routine into the MySQL 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()
self._drop_routine()
sql = "set sql_mode ='%s'" % self._sql_mode
StaticDataLayer.execute_none(sql)
sql = "set names '%s' collate '%s'" % (self._character_set,
self._collate)
StaticDataLayer.execute_none(sql)
StaticDataLayer.execute_none(routine_source)
def _load_routine_file(self):
"""
Loads the stored routine into the MySQL 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()
self._drop_routine()
sql = "set sql_mode ='%s'" % self._sql_mode
StaticDataLayer.execute_none(sql)
sql = "set names '%s' collate '%s'" % (self._character_set, self._collate)
StaticDataLayer.execute_none(sql)
StaticDataLayer.execute_none(routine_source)
def _get_columns(self):
"""
Retrieves metadata all columns in the MySQL schema.
"""
query = """
(
select table_name
, column_name
, data_type
, character_maximum_length
, numeric_precision
, ordinal_position
from information_schema.COLUMNS
where table_schema = database()
and table_name rlike '^[a-zA-Z0-9_]*$'
and column_name rlike '^[a-zA-Z0-9_]*$'
order by table_name
, ordinal_position
)
union all
(
select concat(table_schema,'.',table_name) table_name
, column_name
, data_type
, character_maximum_length
, numeric_precision
, ordinal_position
from information_schema.COLUMNS
where table_name rlike '^[a-zA-Z0-9_]*$'
and column_name rlike '^[a-zA-Z0-9_]*$'
order by table_schema
, table_name
, ordinal_position
)
"""
rows = StaticDataLayer.execute_rows(query)
for row in rows:
# Enhance row with the actual length of the column.
row['length'] = self.derive_field_length(row)
if row['table_name'] in self._columns:
if row['column_name'] in self._columns[row['table_name']]:
pass
else:
self._columns[row['table_name']][row['column_name']] = row
else:
self._columns[row['table_name']] = {row['column_name']: row}
def _get_columns(self):
"""
Retrieves metadata all columns in the MySQL schema.
"""
query = """
(
select table_name
, column_name
, data_type
, character_maximum_length
, numeric_precision
, ordinal_position
from information_schema.COLUMNS
where table_schema = database()
and table_name rlike '^[a-zA-Z0-9_]*$'
and column_name rlike '^[a-zA-Z0-9_]*$'
order by table_name
, ordinal_position
)
union all
(
select concat(table_schema,'.',table_name) table_name
, column_name
, data_type
, character_maximum_length
, numeric_precision
, ordinal_position
from information_schema.COLUMNS
where table_name rlike '^[a-zA-Z0-9_]*$'
and column_name rlike '^[a-zA-Z0-9_]*$'
order by table_schema
, table_name
, ordinal_position
)
"""
rows = StaticDataLayer.execute_rows(query)
for row in rows:
# Enhance row with the actual length of the column.
row['length'] = self.derive_field_length(row)
if row['table_name'] in self._columns:
if row['column_name'] in self._columns[row['table_name']]:
pass
else:
self._columns[row['table_name']][row['column_name']] = row
else:
self._columns[row['table_name']] = {row['column_name']: row}
def tst_test_rows_with_index1_with_lob(p_count, p_blob):
ret = {}
rows = StaticDataLayer.execute_sp_rows(
"call tst_test_rows_with_index1_with_lob(%s, %s)", p_count, p_blob)
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_old_stored_routine_info(self):
"""
Retrieves information about all stored routines in the current schema.
"""
query = """
select ROUTINE_NAME routine_name
, ROUTINE_TYPE routine_type
, SQL_MODE sql_mode
, CHARACTER_SET_CLIENT character_set_client
, COLLATION_CONNECTION collation_connection
from information_schema.ROUTINES
where ROUTINE_SCHEMA = database()
order by routine_name"""
rows = StaticDataLayer.execute_rows(query)
self._rdbms_old_metadata = {}
for row in rows:
self._rdbms_old_metadata[row['routine_name']] = row
def _get_old_stored_routine_info(self):
"""
Retrieves information about all stored routines in the current schema.
"""
query = """
select ROUTINE_NAME routine_name
, ROUTINE_TYPE routine_type
, SQL_MODE sql_mode
, CHARACTER_SET_CLIENT character_set_client
, COLLATION_CONNECTION collation_connection
from information_schema.ROUTINES
where ROUTINE_SCHEMA = database()
order by routine_name"""
rows = StaticDataLayer.execute_rows(query)
self._rdbms_old_metadata = {}
for row in rows:
self._rdbms_old_metadata[row['routine_name']] = row
def tst_test_rows_with_key1_with_lob(p_count, p_blob):
ret = {}
rows = StaticDataLayer.execute_sp_rows(
"call tst_test_rows_with_key1_with_lob(%s, %s)", p_count, p_blob)
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 type from MySQL and saves them as replace pairs.
"""
sql = """
select TABLE_NAME table_name
, COLUMN_NAME column_name
, COLUMN_TYPE column_type
, CHARACTER_SET_NAME character_set_name
, NULL table_schema
from information_schema.COLUMNS
where TABLE_SCHEMA = database()
union all
select TABLE_NAME table_name
, COLUMN_NAME column_name
, COLUMN_TYPE column_type
, CHARACTER_SET_NAME character_set_name
, TABLE_SCHEMA table_schema
from information_schema.COLUMNS
order by table_schema
, table_name
, column_name"""
rows = StaticDataLayer.execute_rows(sql)
for row in rows:
key = '@'
if row['table_schema']:
key += row['table_schema'] + '.'
key += row['table_name'] + '.' + row['column_name'] + '%type@'
key = key.lower()
value = row['column_type']
if row['character_set_name']:
value += ' character set ' + row['character_set_name']
self._replace_pairs[key] = value
def _get_column_type(self):
"""
Selects schema, table, column names and the column type from MySQL and saves them as replace pairs.
"""
sql = """
select TABLE_NAME table_name
, COLUMN_NAME column_name
, COLUMN_TYPE column_type
, CHARACTER_SET_NAME character_set_name
, NULL table_schema
from information_schema.COLUMNS
where TABLE_SCHEMA = database()
union all
select TABLE_NAME table_name
, COLUMN_NAME column_name
, COLUMN_TYPE column_type
, CHARACTER_SET_NAME character_set_name
, TABLE_SCHEMA table_schema
from information_schema.COLUMNS
order by table_schema
, table_name
, column_name"""
rows = StaticDataLayer.execute_rows(sql)
for row in rows:
key = '@'
if row['table_schema']:
key += row['table_schema'] + '.'
key += row['table_name'] + '.' + row['column_name'] + '%type@'
key = key.lower()
value = row['column_type']
if row['character_set_name']:
value += ' character set ' + row['character_set_name']
self._replace_pairs[key] = value
def _get_routine_parameters_info(self):
query = """
select t2.PARAMETER_NAME parameter_name
, t2.DATA_TYPE parameter_type
, t2.DTD_IDENTIFIER column_type
, t2.CHARACTER_SET_NAME character_set_name
, t2.COLLATION_NAME collation
from information_schema.ROUTINES t1
left outer join information_schema.PARAMETERS t2 on t2.SPECIFIC_SCHEMA = t1.ROUTINE_SCHEMA and
t2.SPECIFIC_NAME = t1.ROUTINE_NAME and
t2.PARAMETER_MODE is not null
where t1.ROUTINE_SCHEMA = database()
and t1.ROUTINE_NAME = '%s'""" % self._routine_name
routine_parameters = StaticDataLayer.execute_rows(query)
for routine_parameter in routine_parameters:
if routine_parameter['parameter_name']:
value = routine_parameter['column_type']
if 'character_set_name' in routine_parameter:
if routine_parameter['character_set_name']:
value += ' character set %s' % routine_parameter[
'character_set_name']
if 'collation' in routine_parameter:
if routine_parameter['character_set_name']:
value += ' collation %s' % routine_parameter[
'collation']
self._parameters.append({
'name':
routine_parameter['parameter_name'],
'data_type':
routine_parameter['parameter_type'],
'data_type_descriptor':
value
})
def get_bulk_insert_table_columns_info(self):
"""
Gets the column names and column types of the current table for bulk insert.
"""
query = """
select 1 from
information_schema.TABLES
where TABLE_SCHEMA = database()
and TABLE_NAME = '%s'""" % self._table_name
table_is_non_temporary = StaticDataLayer.execute_rows(query)
if len(table_is_non_temporary) == 0:
query = 'call %s()' % self._routine_name
StaticDataLayer.execute_sp_none(query)
query = "describe `%s`" % self._table_name
columns = StaticDataLayer.execute_rows(query)
tmp_column_types = []
tmp_fields = []
n1 = 0
for column in columns:
p = re.compile('(\\w+)')
c_type = p.findall(column['Type'])
tmp_column_types.append(c_type[0])
tmp_fields.append(column['Field'])
n1 += 1
n2 = len(self._columns)
if len(table_is_non_temporary) == 0:
query = "drop temporary table `%s`" % self._table_name
StaticDataLayer.execute_none(query)
if n1 != n2:
raise Exception(
"Number of fields %d and number of columns %d don't match." %
(n1, n2))
self._columns_types = tmp_column_types
self._fields = tmp_fields
def get_bulk_insert_table_columns_info(self):
"""
Gets the column names and column types of the current table for bulk insert.
"""
query = """
select 1 from
information_schema.TABLES
where TABLE_SCHEMA = database()
and TABLE_NAME = '%s'""" % self._table_name
table_is_non_temporary = StaticDataLayer.execute_rows(query)
if len(table_is_non_temporary) == 0:
query = 'call %s()' % self._routine_name
StaticDataLayer.execute_sp_none(query)
query = "describe `%s`" % self._table_name
columns = StaticDataLayer.execute_rows(query)
tmp_column_types = []
tmp_fields = []
n1 = 0
for column in columns:
p = re.compile('(\\w+)')
c_type = p.findall(column['Type'])
tmp_column_types.append(c_type[0])
tmp_fields.append(column['Field'])
n1 += 1
n2 = len(self._columns)
if len(table_is_non_temporary) == 0:
query = "drop temporary table `%s`" % self._table_name
StaticDataLayer.execute_none(query)
if n1 != n2:
raise Exception("Number of fields %d and number of columns %d don't match." % (n1, n2))
self._columns_types = tmp_column_types
self._fields = tmp_fields
def tst_magic_constant01():
return StaticDataLayer.execute_sp_singleton1(
"call tst_magic_constant01()")
def tst_test_singleton1a(p_count):
return StaticDataLayer.execute_sp_singleton1(
"call tst_test_singleton1a(%s)", p_count)
def tst_test_singleton1a_with_lob(p_count, p_blob):
return StaticDataLayer.execute_sp_singleton1(
"call tst_test_singleton1a_with_lob(%s, %s)", p_count, p_blob)
def tst_test_function(p_a, p_b):
return StaticDataLayer.execute_singleton1(
"select tst_test_function(%s, %s)", p_a, p_b)
def disconnect():
"""
Disconnects from the MySQL instance.
"""
StaticDataLayer.disconnect()
def tst_test_log():
return StaticDataLayer.execute_sp_log("call tst_test_log()")
def tst_test_max_allowed_packet(p_tmp_blob):
return StaticDataLayer.execute_sp_singleton1(
"call tst_test_max_allowed_packet(%s)", p_tmp_blob)
def tst_test_row0a(p_count):
return StaticDataLayer.execute_sp_row0("call tst_test_row0a(%s)",
p_count)
def disconnect():
"""
Disconnects from the MySQL instance.
"""
StaticDataLayer.disconnect()
def tst_test_none(p_count):
return StaticDataLayer.execute_sp_none("call tst_test_none(%s)",
p_count)
def tst_test_none_with_lob(p_count, p_blob):
return StaticDataLayer.execute_sp_none(
"call tst_test_none_with_lob(%s, %s)", p_count, p_blob)
def tst_test_percent_symbol():
return StaticDataLayer.execute_sp_rows(
"call tst_test_percent_symbol()")