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 _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 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 _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)