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_catalog = current_database() and t1.table_schema = current_schema() and t1.column_default like 'nextval(%%)' and t2.table_catalog = current_database() and t2.table_schema = current_schema() 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(%s)" % (self._rdbms_old_metadata['routine_type'], self._routine_name, self._rdbms_old_metadata['routine_args']) 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(%s)" % ( self._rdbms_old_metadata['routine_type'], self._routine_name, self._rdbms_old_metadata['routine_args']) StaticDataLayer.execute_none(sql)
def _drop_obsolete_routines(self): """ Drops obsolete stored routines (i.e. stored routines that exists 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(%s)" % (values['routine_type'], routine_name, values['routine_args']) StaticDataLayer.execute_none(sql)
def _get_old_stored_routine_info(self): """ Retrieves information about all stored routines in the current schema. """ query = """ select t1.routine_name routine_name , t1.routine_type routine_type , array_to_string(array_agg(case when (parameter_name is not null) then concat(t2.parameter_mode, ' ', t2.parameter_name, ' ', t2.udt_name) end order by t2.ordinal_position asc), ',') routine_args from information_schema.routines t1 left outer join information_schema.parameters t2 on t2.specific_catalog = t1.specific_catalog and t2.specific_schema = t1.specific_schema and t2.specific_name = t1.specific_name and t2.parameter_name is not null where routine_catalog = current_database() and routine_schema = current_schema() group by t1.routine_name , t1.routine_type 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_routine_parameters_info(self): query = """ select t2.parameter_name parameter_name , t2.data_type parameter_type , t2.udt_name column_type from information_schema.routines t1 left outer join information_schema.parameters t2 on t2.specific_catalog = t1.specific_catalog and t2.specific_schema = t1.specific_schema and t2.specific_name = t1.specific_name and t2.parameter_name is not null where t1.routine_catalog = current_database() and t1.routine_schema = current_schema() and t1.routine_name = '%s' order by t2.ordinal_position """ % 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_tst_bigint, p_tst_int, p_tst_smallint, p_tst_bit, p_tst_money, p_tst_numeric, p_tst_float, p_tst_real, p_tst_date, p_tst_timestamp, p_tst_time6, p_tst_char, p_tst_varchar): return StaticDataLayer.execute_sp_none( "select tst_parameter_types01(%s::bigint, %s::int, %s::smallint, %s::bit(4), %s::money, %s::numeric, %s::numeric, %s::real, %s::date, %s::timestamp, %s::timestamp, %s::char, %s::varchar)", p_tst_bigint, p_tst_int, p_tst_smallint, p_tst_bit, p_tst_money, p_tst_numeric, p_tst_float, p_tst_real, p_tst_date, p_tst_timestamp, p_tst_time6, p_tst_char, p_tst_varchar)
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() StaticDataLayer.commit() StaticDataLayer.execute_none(routine_source) StaticDataLayer.commit()
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_catalog = current_database() and table_schema = current_schema() and table_name similar to '[a-zA-Z0-9_]*' and column_name similar to '[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 1=0 and table_catalog = current_database() and table_name similar to '[a-zA-Z0-9_]*' and column_name similar to '[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(p_count): ret = {} rows = StaticDataLayer.execute_sp_rows( "select tst_test_rows_with_index1(%s::int)", 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 tst_test_rows_with_key1(p_count): ret = {} rows = StaticDataLayer.execute_sp_rows( "select tst_test_rows_with_key1(%s::int)", 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_routine_parameters_info(self): query = """ select t2.parameter_name parameter_name , t2.data_type parameter_type , t2.udt_name column_type from information_schema.routines t1 left outer join information_schema.parameters t2 on t2.specific_catalog = t1.specific_catalog and t2.specific_schema = t1.specific_schema and t2.specific_name = t1.specific_name and t2.parameter_name is not null where t1.routine_catalog = current_database() and t1.routine_schema = current_schema() and t1.routine_name = '%s' order by t2.ordinal_position """ % 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_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" , udt_name "column_type" , null "table_schema" from information_schema.columns where table_catalog = current_database() and table_schema = current_schema() union all select table_name "table_name" , column_name "column_name" , udt_name "column_type" , table_schema "table_schema" from information_schema.columns where table_catalog = current_database() 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'] self._replace_pairs[key] = 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_test_singleton1a_with_lob(p_count, p_blob): return StaticDataLayer.execute_sp_singleton1( "select tst_test_singleton1a_with_lob(%s::int, %s::bytea)", p_count, p_blob)
def tst_test_singleton1a(p_count): return StaticDataLayer.execute_sp_singleton1( "select tst_test_singleton1a(%s::int)", p_count)
def tst_test_argument_bool(p_bool): return StaticDataLayer.execute_singleton1( "select tst_test_argument_bool(%s::bool)", p_bool)
def tst_test_rows1(p_count): return StaticDataLayer.execute_sp_rows( "select tst_test_rows1(%s::int)", p_count)
def tst_test_rows1_with_lob(p_count, p_blob): return StaticDataLayer.execute_sp_rows( "select tst_test_rows1_with_lob(%s::int, %s::bytea)", p_count, p_blob)
def tst_test_percent_symbol(): return StaticDataLayer.execute_sp_rows( "select tst_test_percent_symbol()")
def tst_test_none_with_lob(p_count, p_blob): return StaticDataLayer.execute_sp_none( "select tst_test_none_with_lob(%s::bigint, %s::bytea)", p_count, p_blob)
def tst_test_none(p_count): return StaticDataLayer.execute_sp_none( "select tst_test_none(%s::bigint)", p_count)
def tst_magic_constant01(): return StaticDataLayer.execute_sp_singleton1( "select tst_magic_constant01()")
def tst_test_argument_date(p_date): return StaticDataLayer.execute_singleton1( "select tst_test_argument_date(%s::date)", p_date)
def tst_test_argument_bit(p_bit): return StaticDataLayer.execute_singleton1( "select tst_test_argument_bit(%s::bit(4))", p_bit)
def tst_test_log(): return StaticDataLayer.execute_sp_log("select tst_test_log()")
def tst_test_argument_bytea(p_bytea): return StaticDataLayer.execute_singleton1( "select tst_test_argument_bytea(%s::bytea)", p_bytea)
def tst_test_argument_numeric(p_num): return StaticDataLayer.execute_singleton1( "select tst_test_argument_numeric(%s::numeric)", p_num)
def tst_test_argument_char(p_char): return StaticDataLayer.execute_singleton1( "select tst_test_argument_char(%s::char)", p_char)
def tst_test_argument_timestamp(p_ts): return StaticDataLayer.execute_singleton1( "select tst_test_argument_timestamp(%s::timestamp)", p_ts)
def tst_test_argument_int(p_int): return StaticDataLayer.execute_singleton1( "select tst_test_argument_int(%s::int)", p_int)
def tst_test_function(p_a, p_b): return StaticDataLayer.execute_singleton1( "select tst_test_function(%s::int, %s::int)", p_a, p_b)