def filter_computed_columns(table, columns):
columns_str = ','.join(f"'{column}'" for column in columns)
query = (
f"SELECT *, TYPE_NAME(system_type_id) as type_name FROM sys.columns "
f"WHERE name in ({columns_str}) AND object_id = OBJECT_ID('{table}') "
"AND is_computed = 1")
return [column['name'] for column in db.query(query)]
def get_tables():
table_names: Iterator[str] = (table['TABLE_NAME']
for table in db.query('sp_tables')
if table['TABLE_TYPE'] == 'TABLE')
tables = [
table for table in table_names if not table.startswith('_')
and not table[0].islower() and table not in REDUNDANT_TABLES
]
return tables
def find_tables_fks(tables):
table_fks = defaultdict(list)
for table in tables:
fks = db.query(f'sp_fkeys {table}')
for fk in fks:
fk_table = fk['FKTABLE_NAME']
fk_column = fk['FKCOLUMN_NAME']
table_fks[fk_table].append(fk_column)
return table_fks
def get_computed_column_formula(table, column):
query = f"SELECT * FROM sys.computed_columns WHERE name = '{column}' and object_id = OBJECT_ID('{table}')"
computed_columns = db.query(query)
computed_column = computed_columns.first()
formula: str = computed_column['definition']
name = computed_column['name']
if formula.startswith('(left') and name.startswith('__'):
related_computed_column = name.strip('_')
result_formula = f"(CONVERT(VARCHAR(32), HASHBYTES('MD5', {related_computed_column}), 2))"
else:
result_formula = formula
return result_formula
def identify_columns_types(table, columns):
def _format_column_type(info):
if info['is_computed']:
formula = get_computed_column_formula(table, info['name'])
return f'as {formula}'
elif info['type_name'] in CHAR_TYPES:
return F"{info['type_name']}({info['max_length']})"
else:
return info['type_name']
columns_str = ','.join(f"'{column}'" for column in columns)
query = (
f"SELECT *, TYPE_NAME(system_type_id) as type_name FROM sys.columns "
f"WHERE name in ({columns_str}) AND object_id = OBJECT_ID('{table}')"
)
infos = db.query(query)
return {
info['name']: _format_column_type(info)
for info in infos
}
def drop_index(index):
*_, table = index['name'].split('_')
index_name = index['name']
if index['is_unique_constraint']:
return f'ALTER TABLE dbo.{table} DROP CONSTRAINT {index_name};'
else:
return f"DROP INDEX {index_name} ON {table};"
if __name__ == '__main__':
with open('data/0_tables.json') as f:
tables = json.load(f)
indexes = db.query(
'select * from sys.indexes where name is not null and is_primary_key = 0 order by name;'
)
table_indexes = [
index for index in indexes if is_table_index(index, tables)
]
drop_indexes_str = '\nGO\n'.join(map(drop_index, table_indexes))
with open(f'sql/2_drop_constraints [{DATABASE}].sql', 'w') as f:
print(f'''use {DATABASE};
GO
sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all'
GO
ALTER TABLE dbo.Properties DROP CONSTRAINT DF_Properties_TableName;
GO
ALTER TABLE dbo.HeadTabl DROP CONSTRAINT DF_HeadTabl_Expert;
def list_related_tables(table):
return [
(fk['FKTABLE_NAME'], fk['FKCOLUMN_NAME'])
for fk in db.query(f"exec sp_fkeys '{table}'").all()
]
def get_text_columns(table):
return [
column['COLUMN_NAME']
for column in db.query(f'exec sp_columns {table}')
if column['TYPE_NAME'] in TEXT_TYPES
]
context_builder = partial(build_context_for_table,
invariant_with_suffix=invariant_with_suffix)
contexts = list(map(context_builder, tables_with_properties))
return contexts
if __name__ == '__main__':
with open('data/1_tables_with_types.json') as f:
tables_with_types = json.load(f)
HAS_RU_COLUMNS_tables = [
table for table, type_ in tables_with_types.items() if table not in
['Bibliogr', 'LastModified', 'Properties', 'HeadTabl', 'SingTabl']
and type_ == 'HAS_RU_COLUMNS'
]
table_properties = {
property_['TableName']: property_
for property_ in db.query(PROPERTIES_QUERY)
}
contexts = _build_contexts(HAS_RU_COLUMNS_tables, table_properties)
NO_RU_COLUMNS_tables = {'Wavepure', 'SistTabl', 'LitrTabl'}
contexts.extend(
_build_contexts(NO_RU_COLUMNS_tables,
table_properties,
invariant_with_suffix=False))
with open('data/page_contexts.json', 'w', encoding='utf-8') as f:
json.dump(contexts, f)
def get_rows(table):
return db.query(f'SELECT * FROM {table}')
def get_columns(table):
return [
column['COLUMN_NAME'] for column in db.query(f'sp_columns {table}')
]
def find_table_pks(table):
return [pk['COLUMN_NAME'] for pk in db.query(f'sp_pkeys {table}').all()]