def read_table_definition(schema_name, table_name):
''' Get the metadata about schema_name:table_name from the SQL
connection.
'''
query = "SELECT \n"
query += " COLUMN_NAME,\n"
query += " DATA_TYPE,\n"
query += " IS_NULLABLE,\n"
query += " ORDINAL_POSITION\n"
query += " FROM \n"
query += " INFORMATION_SCHEMA.COLUMNS \n"
query += " WHERE \n"
query += " TABLE_NAME = '" + table_name + "' \n"
if schema_name != None:
query += " AND TABLE_SCHEMA = '" + schema_name + "' \n"
query += " ORDER BY \n"
query += " ORDINAL_POSITION ASC"
rows = data_source.get_instance().select_many(query)
retval = []
for raw in rows:
row = list(raw)
# log.debug("row: " + str(row))
if row[2] == 'NO':
row[2] = 0
else:
row[2] = 1
retval.append(row)
return retval
def read_foreign_keys(schema_name, table_name):
''' Read the foreign key information from the SQL table table_name.
'''
query = "EXEC sp_fkeys "
query += "@fktable_owner = " + schema_name + ", "
query += "@fktable_name = [" + table_name + "]"
rows = data_source.get_instance().select_many(query)
return rows
def read_tables_in_schema(schema_name):
''' Get the metadata about the tables on schema schema_name from the
connection.
'''
query = "SELECT\n"
query += " SCHEMA_NAME(SCHEMA_ID) AS SN,\n"
query += " NAME\n"
query += " FROM SYS.TABLES\n"
if schema_name != None:
query += " WHERE SCHEMA_NAME(SCHEMA_ID) = '"
query += schema_name + "'\n"
query += " ORDER BY SCHEMA_NAME(SCHEMA_ID), NAME"
tables = data_source.get_instance().select_many(query)
query = "SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES "
if schema_name != None:
query += "WHERE TABLE_SCHEMA='" + schema_name + "'"
views = data_source.get_instance().select_many(query)
rtn = tables + views
return rtn
def clean(self):
''' Empty the dbrev schema in preparation for new run.'''
inst = data_source.get_instance()
LOG.debug('cleaning old data')
inst.begin_transaction()
for tbl in self.t_tbl.get():
# LOG.debug('tbl: ' + str(tbl))
self.table_logic.null_pk(tbl)
self.fkc_tbl.delete()
self.ukc_tbl.delete()
self.fk_tbl.delete()
self.uk_tbl.delete()
self.c_tbl.delete()
self.t_tbl.delete()
inst.end_transaction()
def read_unique_keys(schema_name):
''' Get the metadata about the unique keys in schema schema_name
from the connection.
'''
query = "SELECT TC.CONSTRAINT_NAME, TC.TABLE_SCHEMA,\n"
query += " TC.TABLE_NAME, CCU.COLUMN_NAME\n"
query += " FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC\n"
query += " JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU\n"
query += " ON TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME\n"
query += " WHERE\n"
query += " TC.CONSTRAINT_TYPE = 'UNIQUE'\n"
if schema_name != None:
query += " AND TC.TABLE_SCHEMA = '"
query += schema_name + "'\n"
query += " ORDER BY TC.CONSTRAINT_NAME"
rows = data_source.get_instance().select_many(query)
return rows
def read_primary_keys(schema_name):
''' Get the metadata about the primary keys in schema schema_name
from the connection.
'''
query = "SELECT TC.TABLE_SCHEMA, TC.TABLE_NAME, TC.CONSTRAINT_NAME,"
query += " CCU.COLUMN_NAME\n"
query += " FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC\n"
query += " JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU\n"
query += " ON TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME\n"
query += " WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY'\n"
if schema_name != None:
query += " AND TC.TABLE_SCHEMA = '"
query += schema_name + "'\n"
query += " AND CCU.CONSTRAINT_SCHEMA = '"
query += schema_name + "'\n"
query += " ORDER BY TC.TABLE_SCHEMA, TC.TABLE_NAME"
rows = data_source.get_instance().select_many(query)
retval = None
if rows != None:
retval = rows
return retval
def __init__(self):
self.nva_tbl = nv_args_table.NvArgsTable()
self.nvan_tbl = nv_arg_namespaces_table.NvArgNamespacesTable()
self.s_tbl = sessions_table.SessionsTable()
self.cfg = freevolv.apps.dbrev.config.Config.get_instance()
self.dsrc = data_source.get_instance()
