def process_fk(catalog, table, fk_name, fk_rows):
foreign_key = grt.classes.db_ForeignKey()
if fk_name in cls._connections[connection.__id__]['fk_names']:
while True:
suffix = '_%06d' % random.randint(0, 999999)
if fk_name + suffix not in cls._connections[connection.__id__]['fk_names']:
break
fk_name += suffix
foreign_key.name = fk_name
foreign_key.owner = table
foreign_key.deleteRule = get_action(fk_rows[0].delete_rule)
foreign_key.updateRule = get_action(fk_rows[0].update_rule)
foreign_key.modelOnly = 0
# Find the referenced table:
referenced_schema = find_object_with_name(catalog.schemata, fk_rows[0].pktable_schem) if fk_rows[0].pktable_schem else schema
foreign_key.referencedTable = find_object_with_name(referenced_schema.tables, fk_rows[0].pktable_name) if fk_rows[0].pktable_name else table
for fk_row in fk_rows:
column = find_object_with_name(table.columns, fk_row.fkcolumn_name)
if not column:
grt.send_error('Migration: reverseEngineerTableFKs: Column "%s" not found in table "%s"' % (fk_row.fkcolumn_name, table.name) )
continue
ref_column = find_object_with_name(foreign_key.referencedTable.columns, fk_row.pkcolumn_name)
if not ref_column:
grt.send_error('Migration: reverseEngineerTableFKs: Column "%s" not found in table "%s"' % (fk_row.pkcolumn_name, foreign_key.referencedTable.name) )
continue
foreign_key.columns.append(column)
foreign_key.referencedColumns.append(ref_column)
cls._connections[connection.__id__]['fk_names'][foreign_key.name] = table
table.foreignKeys.append(foreign_key)
def reverseEngineerTableFKs(cls, connection, table):
"""Reverse engineers the foreign keys for the given table."""
schema = table.owner
catalog = schema.owner
if len(table.columns) == 0: # Table must have columns reverse engineered before we can rev eng its foreign keys
grt.send_error('Migration: reverseEngineerTableFKs: Reverse engineering of table %s was attempted but the table has no columns attribute' % table.name)
return 1
query = """SELECT si.index_name, sfk.foreign_table_id, sfk.foreign_index_id, sfk.primary_table_id, sfk.primary_index_id
FROM SYSFKEY sfk
JOIN SYSIDX si ON (sfk.foreign_index_id=si.index_id AND sfk.foreign_table_id=si.table_id)
JOIN SYSTAB st ON sfk.foreign_table_id=st.table_id
JOIN SYSUSER su ON st.creator=su.user_id
WHERE st.table_name='%s' AND su.user_name='%s'
ORDER BY sfk.primary_index_id""" % (table.name, schema.name)
fk_cursor = cls.get_connection(connection).cursor()
for fk_name, this_table_id, this_index_id, other_table_id, other_index_id in fk_cursor.execute(query):
this_column_query = """SELECT stc.column_name
FROM SYSIDXCOL sic
JOIN SYSTABCOL stc ON (sic.table_id=stc.table_id AND sic.column_id=stc.column_id)
WHERE sic.table_id=%d AND sic.index_id=%d
ORDER BY sic.sequence""" % (this_table_id, this_index_id)
other_column_query = """SELECT su.user_name, st.table_name, stc.column_name
FROM SYSIDXCOL sic
JOIN SYSTABCOL stc ON (sic.table_id=stc.table_id AND sic.column_id=stc.column_id)
JOIN SYSTAB st ON stc.table_id=st.table_id
JOIN SYSUSER su ON st.creator=su.user_id
WHERE sic.table_id=%d AND sic.index_id=%d
ORDER BY sic.sequence""" % (other_table_id, other_index_id)
these_columns = cls.execute_query(connection, this_column_query).fetchall()
other_columns = cls.execute_query(connection, other_column_query).fetchall()
foreign_key = grt.classes.db_ForeignKey()
foreign_key.owner = table
foreign_key.name = fk_name
# Find the referenced table:
referenced_schema = find_object_with_name(catalog.schemata, other_columns[0][0]) if other_columns[0][0] else schema
foreign_key.referencedTable = find_object_with_name(referenced_schema.tables, other_columns[0][1]) if other_columns[0][1] else table
for (this_column_name,), (_, _, other_column_name) in zip(these_columns, other_columns):
column = find_object_with_name(table.columns, this_column_name)
if not column:
grt.send_error('Migration: reverseEngineerTableFKs: Column "%s" not found in table "%s"' % (this_column_name, table.name) )
continue
ref_column = find_object_with_name(foreign_key.referencedTable.columns, other_column_name)
if not ref_column:
grt.send_error('Migration: reverseEngineerTableFKs: Column "%s" not found in table "%s"' % (other_column_name, foreign_key.referencedTable.name) )
continue
foreign_key.columns.append(column)
foreign_key.referencedColumns.append(ref_column)
table.foreignKeys.append(foreign_key)
return 0
def process_fk(catalog, table, fk_name, fk_rows):
foreign_key = grt.classes.db_ForeignKey()
if fk_name in cls._connections[connection.__id__]['fk_names']:
while True:
suffix = '_%06d' % random.randint(0, 999999)
if fk_name + suffix not in cls._connections[connection.__id__]['fk_names']:
break
fk_name += suffix
foreign_key.name = fk_name
foreign_key.owner = table
foreign_key.deleteRule = get_action(fk_rows[0].delete_rule)
foreign_key.updateRule = get_action(fk_rows[0].update_rule)
foreign_key.modelOnly = 0
# Find the referenced table:
referenced_schema = find_object_with_name(catalog.schemata, fk_rows[0].pktable_schem) if fk_rows[0].pktable_schem else schema
foreign_key.referencedTable = find_object_with_name(referenced_schema.tables, fk_rows[0].pktable_name) if fk_rows[0].pktable_name else table
for fk_row in fk_rows:
column = find_object_with_name(table.columns, fk_row.fkcolumn_name)
if not column:
grt.send_error('Migration: reverseEngineerTableFKs: Column "%s" not found in table "%s"' % (fk_row.fkcolumn_name, table.name) )
continue
ref_column = find_object_with_name(foreign_key.referencedTable.columns, fk_row.pkcolumn_name)
if not ref_column:
grt.send_error('Migration: reverseEngineerTableFKs: Column "%s" not found in table "%s"' % (fk_row.pkcolumn_name, foreign_key.referencedTable.name) )
continue
foreign_key.columns.append(column)
foreign_key.referencedColumns.append(ref_column)
cls._connections[connection.__id__]['fk_names'][foreign_key.name] = table
table.foreignKeys.append(foreign_key)
def process_fk(catalog, table, fk_name, fk_rows):
foreign_key = grt.classes.db_ForeignKey()
foreign_key.name = fk_name
foreign_key.owner = table
foreign_key.deleteRule = get_action(fk_rows[0].delete_rule)
foreign_key.updateRule = get_action(fk_rows[0].update_rule)
foreign_key.modelOnly = 0
# Find the referenced table:
referenced_schema = find_object_with_name(catalog.schemata, fk_rows[0].pktable_schem) if fk_rows[0].pktable_schem else schema
foreign_key.referencedTable = find_object_with_name(referenced_schema.tables, fk_rows[0].pktable_name) if fk_rows[0].pktable_name else table
for fk_row in fk_rows:
column = find_object_with_name(table.columns, fk_row.fkcolumn_name)
if not column:
grt.send_error('Migration: reverseEngineerTableFKs: Column "%s" not found in table "%s"' % (fk_row.fkcolumn_name, table.name) )
continue
ref_column = find_object_with_name(foreign_key.referencedTable.columns, fk_row.pkcolumn_name)
if not ref_column:
grt.send_error('Migration: reverseEngineerTableFKs: Column "%s" not found in table "%s"' % (fk_row.pkcolumn_name, foreign_key.referencedTable.name) )
continue
foreign_key.columns.append(column)
foreign_key.referencedColumns.append(ref_column)
table.foreignKeys.append(foreign_key)
def process_fk(catalog, table, fk_name, fk_rows):
foreign_key = grt.classes.db_ForeignKey()
if fk_name in cls._connections[connection.__id__]['fk_names']:
while True:
suffix = '_%06d' % random.randint(0, 999999)
if fk_name + suffix not in cls._connections[connection.__id__]['fk_names']:
break
fk_name += suffix
foreign_key.name = fk_name
foreign_key.owner = table
foreign_key.deleteRule = get_delete_action(fk_rows[0].grbit)
foreign_key.updateRule = get_update_action(fk_rows[0].grbit)
foreign_key.modelOnly = 0
# Find the referenced table:
foreign_key.referencedTable = find_object_with_name(catalog.schemata[0].tables, fk_rows[0].szReferencedObject)
if not foreign_key.referencedTable:
grt.send_error('Migration: reverseEngineerTableFKs: Table "%s" not found in schemata "%s"' % (fk_rows[0].szReferencedObject, catalog.schemata[0].name) )
return 1
for fk_row in fk_rows:
column = find_object_with_name(table.columns, fk_row.szColumn)
if not column:
grt.send_error('Migration: reverseEngineerTableFKs: Column "%s" not found in table "%s"' % (fk_row.szColumn, table.name) )
continue
ref_column = find_object_with_name(foreign_key.referencedTable.columns, fk_row.szReferencedColumn)
if not ref_column:
grt.send_error('Migration: reverseEngineerTableFKs: Column "%s" not found in table "%s"' % (fk_row.szReferencedColumn, foreign_key.referencedTable.name) )
continue
foreign_key.columns.append(column)
foreign_key.referencedColumns.append(ref_column)
# Find and delete indexes that are identical to FKs
for index in reversed(table.indices):
if table.primaryKey != index and len(index.columns) == len(foreign_key.columns):
match = True
for i, col in enumerate(index.columns):
if foreign_key.columns[i] != col.referencedColumn:
match = False
break
if match:
grt.send_warning("Migration: reverseEngineerTable: Skipping duplicate index %s from table %s\n" % (col.name, table.name))
table.indices.remove(index)
cls._connections[connection.__id__]['fk_names'][foreign_key.name] = table
table.foreignKeys.append(foreign_key)
def process_fk(catalog, table, fk_name, fk_rows):
foreign_key = grt.classes.db_ForeignKey()
if fk_name in cls._connections[connection.__id__]['fk_names']:
while True:
suffix = '_%06d' % random.randint(0, 999999)
if fk_name + suffix not in cls._connections[connection.__id__]['fk_names']:
break
fk_name += suffix
foreign_key.name = fk_name
foreign_key.owner = table
foreign_key.deleteRule = get_delete_action(fk_rows[0].grbit)
foreign_key.updateRule = get_update_action(fk_rows[0].grbit)
foreign_key.modelOnly = 0
# Find the referenced table:
foreign_key.referencedTable = find_object_with_name(catalog.schemata[0].tables, fk_rows[0].szReferencedObject)
if not foreign_key.referencedTable:
grt.send_error('Migration: reverseEngineerTableFKs: Table "%s" not found in schemata "%s"' % (fk_rows[0].szReferencedObject, catalog.schemata[0].name) )
return 1
for fk_row in fk_rows:
column = find_object_with_name(table.columns, fk_row.szColumn)
if not column:
grt.send_error('Migration: reverseEngineerTableFKs: Column "%s" not found in table "%s"' % (fk_row.szColumn, table.name) )
continue
ref_column = find_object_with_name(foreign_key.referencedTable.columns, fk_row.szReferencedColumn)
if not ref_column:
grt.send_error('Migration: reverseEngineerTableFKs: Column "%s" not found in table "%s"' % (fk_row.szReferencedColumn, foreign_key.referencedTable.name) )
continue
foreign_key.columns.append(column)
foreign_key.referencedColumns.append(ref_column)
# Find and delete indexes that are identical to FKs
for index in reversed(table.indices):
if table.primaryKey != index and len(index.columns) == len(foreign_key.columns):
match = True
for i, col in enumerate(index.columns):
if foreign_key.columns[i] != col.referencedColumn:
match = False
break
if match:
grt.send_warning("Migration: reverseEngineerTable: Skipping duplicate index %s from table %s\n" % (col.name, table.name))
table.indices.remove(index)
cls._connections[connection.__id__]['fk_names'][foreign_key.name] = table
table.foreignKeys.append(foreign_key)
def migrateColumnDefaultValue(self, state, default_value, source_column,
target_column):
target_default_value = default_value
if source_column.simpleType:
source_datatype = source_column.simpleType.name
if source_datatype == 'TIMESTAMP':
if default_value == 'getdate()':
target_default_value = 'CURRENT_TIMESTAMP'
elif source_datatype in ['DATETIME', 'SMALLDATETIME']:
if source_column.defaultValue == 'getdate()':
target_default_value = 'CURRENT_TIMESTAMP'
# Only timestamp supports CURRENT_TIMESTAMP, so force the target type to it
target_column.simpleType = find_object_with_name(
state.targetCatalog.simpleDatatypes, 'TIMESTAMP')
target_column.length = -1
state.addMigrationLogEntry(
0, source_column, target_column,
'Default value is %s, so type was changed from %s to TIMESTAMP'
% (default_value, source_datatype))
if default_value and not default_value.startswith(
"'") and target_default_value == default_value:
# not a string, check for numeric literals
try:
float(default_value)
except:
# not a numeric literal
target_default_value = ''
state.addMigrationLogEntry(
1, source_column, target_column,
'Default value %s is not supported' % default_value)
return target_default_value
def reverseEngineerTablePK(connection, table):
"""Reverse engineers the primary key for the given table."""
schema = table.owner
catalog = schema.owner
execute_query(connection, 'USE %s' % catalog.name)
query = """SELECT sc.name
FROM sysobjects so JOIN syskeys sk ON so.id=sk.id
JOIN syscolumns sc ON sc.id=sk.id AND sc.colid IN (sk.key1, sk.key2, sk.key3, sk.key4, sk.key5, sk.key6, sk.key7, sk.key8)
WHERE so.uid=USER_ID(?) AND sk.id=OBJECT_ID(?) AND sk.type=1"""
if len(table.columns) == 0:
grt.send_error(
'Sybase reverseEngineerTablePK',
"Reverse engineer of table's %s.%s primary key was attempted but the table has no columns attribute"
% (schema.name, table.name))
return 1 # Table must have columns reverse engineered before we can rev eng its primary key
pk_col_names = [
row[0]
for row in execute_query(connection, query, schema.name, table.name)
]
for pk_column in pk_col_names:
column = find_object_with_name(table.columns, pk_column)
if column:
table.addPrimaryKeyColumn(column)
return 0
def migrateColumnDefaultValue(self, state, default_value, source_column, target_column):
target_default_value = default_value
if source_column.simpleType:
source_datatype = source_column.simpleType.name
if source_datatype == 'TIMESTAMP':
if default_value == 'getdate()':
target_default_value = 'CURRENT_TIMESTAMP'
elif source_datatype in ['DATETIME', 'SMALLDATETIME']:
if source_column.defaultValue == 'getdate()':
target_default_value = 'CURRENT_TIMESTAMP'
# Only timestamp supports CURRENT_TIMESTAMP, so force the target type to it
target_column.simpleType = find_object_with_name(state.targetCatalog.simpleDatatypes, 'TIMESTAMP')
state.addMigrationLogEntry(0, source_column, target_column,
'Default value is %s, so type was changed from %s to TIMESTAMP' % (default_value, source_datatype))
if default_value and not default_value.startswith("'") and target_default_value == default_value:
# not a string, check for numeric literals
try:
float(default_value)
except:
# not a numeric literal
target_default_value = ''
state.addMigrationLogEntry(1, source_column, target_column,
'Default value %s is not supported' % default_value)
return target_default_value
def reverseEngineerTablePK(cls, connection, table):
"""Reverse engineers the primary key(s) for the given table."""
schema = table.owner
catalog = schema.owner
if len(table.columns) == 0: # Table must have columns reverse engineered before we can rev eng its primary key(s)
grt.send_error('Migration: reverseEngineerTablePK: Reverse engineering of table %s was attempted but the table has no columns attribute' % table.name)
return 1
# Primary keys and indices come together in the SYSIDX system view, so we'll rev eng them at once:
query = """SELECT st.table_id, si.index_id, si.index_name, si.index_category, si."unique"
FROM SYSIDX si
JOIN SYSTAB st ON si.table_id=st.table_id
JOIN SYSUSER su ON st.creator=su.user_id
WHERE st.table_name='%s' AND su.user_name='%s'
ORDER BY si.index_id""" % (table.name, schema.name)
idx_cursor = cls.get_connection(connection).cursor()
for table_id, index_id, index_name, index_category, index_unique in idx_cursor.execute(query):
index = grt.classes.db_Index()
index.name = index_name
index.isPrimary = 1 if index_category == 1 else 0
index.unique = 1 if index_unique in (1, 2) else 0
if index_category == 1:
index.indexType = 'PRIMARY'
elif index_category == 2:
continue # This is a foreign key, will be handled when reverse engineering them
elif index_category == 3: # Can be a regular index or a unique constraint
if index_unique == 2:
index.indexType = 'UNIQUE'
else:
index.indexType = 'INDEX'
else:
index.indexType = 'FULLTEXT'
# index.hasFilter = False # TODO: Find out if there's a way to determine this
# Get the columns for the index:
idx_cols_query = """SELECT sc.column_name, sic."order"
FROM SYSIDXCOL sic
JOIN SYSTAB st ON sic.table_id=st.table_id
JOIN SYSTABCOL sc ON (sc.column_id = sic.column_id AND sc.table_id = sic.table_id)
WHERE st.table_id=%s AND sic.index_id=%s
ORDER BY sic.sequence""" % (table_id, index_id)
idx_cols_cursor = cls.get_connection(connection).cursor()
for column_name, order in idx_cols_cursor.execute(idx_cols_query):
column = find_object_with_name(table.columns, column_name)
if column:
index_column = grt.classes.db_IndexColumn()
index_column.name = index_name + '.' + column_name
index_column.referencedColumn = column
index_column.descend = 1 if order and order.upper() == 'D' else 0
index.columns.append(index_column)
table.addIndex(index)
if index.isPrimary:
table.primaryKey = index
return 0
def reverseEngineerTablePK(cls, connection, table):
"""Reverse engineers the primary key(s) for the given table."""
schema = table.owner
if len(table.columns) == 0: # Table must have columns reverse engineered before we can rev eng its primary key(s)
grt.send_error('Migration: reverseEngineerTablePK: Reverse engineering of table %s was attempted but the table has no columns attribute' % table.name)
return 1
# Primary keys and indices come together in the SYSIDX system view, so we'll rev eng them at once:
query = """SELECT st.table_id, si.index_id, si.index_name, si.index_category, si."unique"
FROM SYSIDX si
JOIN SYSTAB st ON si.table_id=st.table_id
JOIN SYSUSER su ON st.creator=su.user_id
WHERE st.table_name='%s' AND su.user_name='%s'
ORDER BY si.index_id""" % (table.name, schema.name)
idx_cursor = cls.get_connection(connection).cursor()
for table_id, index_id, index_name, index_category, index_unique in idx_cursor.execute(query):
index = grt.classes.db_Index()
index.name = index_name
index.isPrimary = 1 if index_category == 1 else 0
index.unique = 1 if index_unique in (1, 2) else 0
if index_category == 1:
index.indexType = 'PRIMARY'
elif index_category == 2:
continue # This is a foreign key, will be handled when reverse engineering them
elif index_category == 3: # Can be a regular index or a unique constraint
if index_unique == 2:
index.indexType = 'UNIQUE'
else:
index.indexType = 'INDEX'
else:
index.indexType = 'FULLTEXT'
# index.hasFilter = False # TODO: Find out if there's a way to determine this
# Get the columns for the index:
idx_cols_query = """SELECT sc.column_name, sic."order"
FROM SYSIDXCOL sic
JOIN SYSTAB st ON sic.table_id=st.table_id
JOIN SYSTABCOL sc ON (sc.column_id = sic.column_id AND sc.table_id = sic.table_id)
WHERE st.table_id=%s AND sic.index_id=%s
ORDER BY sic.sequence""" % (table_id, index_id)
idx_cols_cursor = cls.get_connection(connection).cursor()
for column_name, order in idx_cols_cursor.execute(idx_cols_query):
column = find_object_with_name(table.columns, column_name)
if column:
index_column = grt.classes.db_IndexColumn()
index_column.name = index_name + '.' + column_name
index_column.referencedColumn = column
index_column.descend = 1 if order and order.upper() == 'D' else 0
index.columns.append(index_column)
table.addIndex(index)
if index.isPrimary:
table.primaryKey = index
return 0
def reverseEngineerTablePK(cls, connection, table):
"""Reverse engineers the primary key(s) for the given table."""
schema = table.owner
catalog = schema.owner
query = """SELECT tc.CONSTRAINT_NAME, kcu.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu
ON kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
AND kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
AND kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA
AND kcu.TABLE_NAME = tc.TABLE_NAME
WHERE tc.CONSTRAINT_TYPE='PRIMARY KEY' AND tc.TABLE_CATALOG = '%s' AND tc.TABLE_SCHEMA = '%s' AND tc.TABLE_NAME = '%s'
ORDER BY tc.CONSTRAINT_NAME, kcu.ORDINAL_POSITION""" % (
catalog.name,
schema.name,
table.name,
)
if (
len(table.columns) == 0
): # Table must have columns reverse engineered before we can rev eng its primary key(s)
grt.send_error(
"%s reverseEngineerTablePK: Reverse engineer of table %s was attempted but the table has "
"no columns attribute" % (cls.getTargetDBMSName(), table.name)
)
return 1
fk_rows = cls.execute_query(connection, query).fetchall()
if fk_rows:
index = grt.classes.db_Index()
index.name = fk_rows[0][0]
index.isPrimary = 1
index.unique = 1
index.indexType = "PRIMARY"
for _, pk_col in fk_rows:
table_column = find_object_with_name(table.columns, pk_col)
if not table_column:
grt.send_warning(
'%s reverseEngineerTablePK: Could not find column "%s" in table "%s" referenced '
'by primary key constraint "%s". The primary key will not be added.'
% (cls.getTargetDBMSName(), pk_col, table.name, index.name)
)
return 0
index_column = grt.classes.db_IndexColumn()
index_column.name = index.name + "." + pk_col
index_column.referencedColumn = table_column
index.columns.append(index_column)
table.primaryKey = index
table.addIndex(index)
return 0
def reverseEngineerTableIndices(cls, connection, table):
schema = table.owner
if len(table.columns) == 0:
grt.send_error('%s: reverseEngineerTableIndices',
'Reverse engineer of table %s.%s was attempted but the table has no columns attribute' % (cls.getTargetDBMSName(), schema.name, table.name) )
return 1 # Table must have columns reverse engineered before we can rev eng its indices
all_indices_query = """SELECT c2.relname, i.indisunique::int, i.indisclustered::int, i.indnatts, i.indkey
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_namespace n, pg_catalog.pg_index i
WHERE c.oid = i.indrelid AND i.indexrelid = c2.oid AND c.relnamespace = n.oid AND
n.nspname = '%s' AND c.relname = '%s' AND i.indisprimary = False
ORDER BY c2.relname""" % (schema.name, table.name)
index_columns_query = """SELECT a.attname
FROM unnest(ARRAY%r) attrid
JOIN pg_catalog.pg_attribute a ON attrid=a.attnum
JOIN pg_catalog.pg_class c ON c.oid = a.attrelid
JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
WHERE n.nspname = '%s' AND c.relname = '%s'"""
index_rows = cls.execute_query(connection, all_indices_query).fetchall()
for index_name, is_unique, is_clustered, column_count, column_refs in index_rows:
index = grt.classes.db_Index()
index.name = index_name
index.isPrimary = 0
index.unique = is_unique
index.indexType = ('UNIQUE' if is_unique else 'INDEX')
#index.clustered = is_clustered
# Get the columns for the index:
cols = [ int(col) for col in column_refs.split() ]
if column_count != len(cols):
grt.send_warning('%s: reverseEngineerTableIndices' % cls.getTargetDBMSName(),
'Reverse engineer of index %s.%s was attempted but the referenced columns count differs '
'from the number of its referenced columns. Skipping index!' % (schema.name, index_name) )
continue
for (column_name, ) in cls.execute_query(connection, index_columns_query % (cols, schema.name, table.name)):
column = find_object_with_name(table.columns, column_name)
if column:
index_column = grt.classes.db_IndexColumn()
index_column.name = index_name + '.' + column_name
#index_column.descend = is_descending_key
index_column.referencedColumn = column
index.columns.append(index_column)
else:
grt.send_warning('%s: reverseEngineerTableIndices' % cls.getTargetDBMSName(),
'Reverse engineer of index %s.%s was attempted but the referenced column %s '
'could not be found on table %s. Skipping index!' % (schema.name, index_name, column_name, table.name) )
continue
table.addIndex(index)
return 0
def reverseEngineerTableIndices(cls, connection, table):
schema = table.owner
if len(table.columns) == 0:
grt.send_error('%s: reverseEngineerTableIndices',
'Reverse engineer of table %s.%s was attempted but the table has no columns attribute' % (cls.getTargetDBMSName(), schema.name, table.name) )
return 1 # Table must have columns reverse engineered before we can rev eng its indices
all_indices_query = """SELECT c2.relname, i.indisunique::int, i.indisclustered::int, i.indnatts, i.indkey
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_namespace n, pg_catalog.pg_index i
WHERE c.oid = i.indrelid AND i.indexrelid = c2.oid AND c.relnamespace = n.oid AND
n.nspname = '%s' AND c.relname = '%s' AND i.indisprimary = False
ORDER BY c2.relname""" % (schema.name, table.name)
index_columns_query = """SELECT a.attname
FROM unnest(ARRAY%r) attrid
JOIN pg_catalog.pg_attribute a ON attrid=a.attnum
JOIN pg_catalog.pg_class c ON c.oid = a.attrelid
JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
WHERE n.nspname = '%s' AND c.relname = '%s'"""
index_rows = cls.execute_query(connection, all_indices_query).fetchall()
for index_name, is_unique, is_clustered, column_count, column_refs in index_rows:
index = grt.classes.db_Index()
index.name = index_name
index.isPrimary = 0
index.unique = is_unique
index.indexType = ('UNIQUE' if is_unique else 'INDEX')
#index.clustered = is_clustered
# Get the columns for the index:
cols = [ int(col) for col in column_refs.split() ]
if column_count != len(cols):
grt.send_warning('%s: reverseEngineerTableIndices' % cls.getTargetDBMSName(),
'Reverse engineer of index %s.%s was attempted but the referenced columns count differs '
'from the number of its referenced columns. Skipping index!' % (schema.name, index_name) )
continue
for (column_name, ) in cls.execute_query(connection, index_columns_query % (cols, schema.name, table.name)):
column = find_object_with_name(table.columns, column_name)
if column:
index_column = grt.classes.db_IndexColumn()
index_column.name = index_name + '.' + column_name
#index_column.descend = is_descending_key
index_column.referencedColumn = column
index.columns.append(index_column)
else:
grt.send_warning('%s: reverseEngineerTableIndices' % cls.getTargetDBMSName(),
'Reverse engineer of index %s.%s was attempted but the referenced column %s '
'could not be found on table %s. Skipping index!' % (schema.name, index_name, column_name, table.name) )
continue
table.addIndex(index)
return 0
def reverseEngineerTablePK(cls, connection, table):
"""Reverse engineers the primary key for the given table."""
schema = table.owner
catalog = schema.owner
if len(table.columns) == 0: # Table must have columns reverse engineered before we can rev eng its primary key
grt.send_error('Migration: reverseEngineerTablePKAndIndices: Reverse engineer of table %s was attempted but the table has no columns attribute' % table.name)
return 1
# Find the index name associated with the PK:
pk_index_rows = cls.get_connection(connection).cursor().primaryKeys(catalog=catalog.name, schema=schema.name, table=table.name).fetchall()
pk_index_name = pk_index_rows[0].pk_name if pk_index_rows else ''
indices_dict = {} # Map the indices names to their respective columns:
for row in cls.get_connection(connection).cursor().statistics(catalog=catalog.name, schema=schema.name, table=table.name):
if row.type == constant.SQL_TABLE_STAT: # this entry is not an index
continue
indices_dict.setdefault(row.index_name, []).append(row)
if pk_index_name not in indices_dict: # The primary key is not listed in cursor().statistics
from collections import namedtuple
Row = namedtuple('IndexRow', ['table_cat', 'table_schem', 'table_name', 'non_unique', 'index_qualifier',
'index_name', 'type', 'ordinal_position', 'column_name', 'asc_or_desc',
'cardinality', 'pages', 'filter_condition'])
for pk_index_row in pk_index_rows:
row = Row(None, schema.name, table.name, 0, None, pk_index_name, 1, 1, pk_index_row.column_name, 'A', None, None, None)
indices_dict.setdefault(pk_index_name, []).append(row)
for index_name, row_list in list(indices_dict.items()):
index = grt.classes.db_Index()
index.name = index_name
index.isPrimary = 1 if index_name == pk_index_name else 0
index.unique = not row_list[0].non_unique
index.indexType = 'UNIQUE' if index.unique else 'INDEX'
# index.hasFilter = False # TODO: Find out if there's a way to determine this
# Get the columns for the index:
for row in sorted(row_list, key=lambda elem: elem[7]): # Sorted by ordinal_position
column = find_object_with_name(table.columns, row.column_name)
if column:
index_column = grt.classes.db_IndexColumn()
index_column.name = index_name + '.' + row.column_name
index_column.referencedColumn = column
index.columns.append(index_column)
table.addIndex(index)
if index.isPrimary:
table.primaryKey = index
return 0
def reverseEngineerTablePK(cls, connection, table):
"""Reverse engineers the primary key(s) for the given table."""
schema = table.owner
catalog = schema.owner
if len(table.columns) == 0: # Table must have columns reverse engineered before we can rev eng its primary key(s)
grt.send_error('Migration: reverseEngineerTablePKAndIndices: Reverse engineer of table %s was attempted but the table has no columns attribute' % table.name)
return 1
# Find the index name associated with the PK:
pk_index_rows = cls.get_connection(connection).cursor().primaryKeys(catalog=catalog.name, schema=schema.name, table=table.name).fetchall()
pk_index_name = pk_index_rows[0].pk_name if pk_index_rows else ''
indices_dict = {} # Map the indices names to their respective columns:
for row in cls.get_connection(connection).cursor().statistics(catalog=catalog.name, schema=schema.name, table=table.name):
if row.type == constant.SQL_TABLE_STAT: # this entry is not an index
continue
indices_dict.setdefault(row.index_name, []).append(row)
if pk_index_name not in indices_dict: # The primary key is not listed in cursor().statistics
from collections import namedtuple
Row = namedtuple('IndexRow', ['table_cat', 'table_schem', 'table_name', 'non_unique', 'index_qualifier',
'index_name', 'type', 'ordinal_position', 'column_name', 'asc_or_desc',
'cardinality', 'pages', 'filter_condition'])
for pk_index_row in pk_index_rows:
row = Row(None, schema.name, table.name, 0, None, pk_index_name, 1, 1, pk_index_row.column_name, 'A', None, None, None)
indices_dict.setdefault(pk_index_name, []).append(row)
for index_name, row_list in indices_dict.iteritems():
index = grt.classes.db_Index()
index.name = index_name
index.isPrimary = 1 if index_name == pk_index_name else 0
index.unique = not row_list[0].non_unique
index.indexType = 'UNIQUE' if index.unique else 'INDEX'
# index.hasFilter = False # TODO: Find out if there's a way to determine this
# Get the columns for the index:
for row in sorted(row_list, key=lambda elem: elem[7]): # Sorted by ordinal_position
column = find_object_with_name(table.columns, row.column_name)
if column:
index_column = grt.classes.db_IndexColumn()
index_column.name = index_name + '.' + row.column_name
index_column.referencedColumn = column
index.columns.append(index_column)
table.addIndex(index)
if index.isPrimary:
table.primaryKey = index
return 0
def reverseEngineerTablePK(cls, connection, table):
"""Reverse engineers the primary key(s) for the given table."""
schema = table.owner
catalog = schema.owner
query = """SELECT tc.CONSTRAINT_NAME, kcu.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu
ON kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
AND kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
AND kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA
AND kcu.TABLE_NAME = tc.TABLE_NAME
WHERE tc.CONSTRAINT_TYPE='PRIMARY KEY' AND tc.TABLE_CATALOG = '%s' AND tc.TABLE_SCHEMA = '%s' AND tc.TABLE_NAME = '%s'
ORDER BY tc.CONSTRAINT_NAME, kcu.ORDINAL_POSITION""" % (
catalog.name, schema.name, table.name)
if len(
table.columns
) == 0: # Table must have columns reverse engineered before we can rev eng its primary key(s)
grt.send_error(
'%s reverseEngineerTablePK: Reverse engineer of table %s was attempted but the table has '
'no columns attribute' % (cls.getTargetDBMSName(), table.name))
return 1
fk_rows = cls.execute_query(connection, query).fetchall()
if fk_rows:
index = grt.classes.db_Index()
index.name = fk_rows[0][0]
index.isPrimary = 1
index.unique = 1
index.indexType = 'PRIMARY'
for _, pk_col in fk_rows:
table_column = find_object_with_name(table.columns, pk_col)
if not table_column:
grt.send_warning(
'%s reverseEngineerTablePK: Could not find column "%s" in table "%s" referenced '
'by primary key constraint "%s". The primary key will not be added.'
% (cls.getTargetDBMSName(), pk_col, table.name,
index.name))
return 0
index_column = grt.classes.db_IndexColumn()
index_column.name = index.name + '.' + pk_col
index_column.referencedColumn = table_column
index.columns.append(index_column)
table.primaryKey = index
table.addIndex(index)
return 0
def reverseEngineerTablePK(cls, connection, table):
"""Reverse engineers the primary key(s) for the given table."""
if len(table.columns) == 0: # Table must have columns reverse engineered before we can rev eng its primary key(s)
grt.send_error('Migration: reverseEngineerTablePKAndIndices: Reverse engineer of table %s was attempted but the table has no columns attribute' % table.name)
return 1
pk_index_name = 'PrimaryKey'
indices_dict = {} # Map the indices names to their respective columns:
for row in cls.get_connection(connection).cursor().statistics(table=table.name):
if row.type == constant.SQL_TABLE_STAT: # this entry is not an index
continue
indices_dict.setdefault(row.index_name, []).append(row)
for index_name, row_list in indices_dict.iteritems():
index = grt.classes.db_Index()
index.name = index_name
index.isPrimary = 1 if index_name == pk_index_name else 0
index.unique = not row_list[0].non_unique
index.indexType = 'UNIQUE' if index.unique else 'INDEX'
# index.hasFilter = False # TODO: Find out if there's a way to determine this
skip = False
# Get the columns for the index:
for row in sorted(row_list, key=lambda elem: elem[7]): # Sorted by ordinal_position
column = find_object_with_name(table.columns, row.column_name)
if column:
# skip indexes on LONGCHAR columns
if column.simpleType.name in ["LONGCHAR"]:
grt.send_warning("Migration: reverseEngineerTable: Skipping index %s.%s on a %s column\n" % (table.name, column.name, column.simpleType.name))
skip = True
else:
index_column = grt.classes.db_IndexColumn()
index_column.name = index_name + '.' + row.column_name
index_column.referencedColumn = column
index.columns.append(index_column)
if not column.isNotNull and index.isPrimary:
column.isNotNull = 1
grt.send_warning("Migration: reverseEngineerTablePK: column %s.%s was changed to NOT NULL because it's a Primary Key column\n" % (column.owner.name, column.name))
else:
grt.send_warning("Migration: reverseEngineerTablePK: could not find column %s, belonging to key %s. Key will be skipped\n" % (row.column_name, index_name))
skip = True
if not skip:
table.addIndex(index)
if index.isPrimary:
table.primaryKey = index
return 0
def reverseEngineerTablePK(cls, connection, table):
"""Reverse engineers the primary key for the given table."""
if len(table.columns) == 0: # Table must have columns reverse engineered before we can rev eng its primary key
grt.send_error('Migration: reverseEngineerTablePKAndIndices: Reverse engineer of table %s was attempted but the table has no columns attribute' % table.name)
return 1
pk_index_name = 'PrimaryKey'
indices_dict = {} # Map the indices names to their respective columns:
for row in cls.get_connection(connection).cursor().statistics(table=table.name):
if row.type == constant.SQL_TABLE_STAT: # this entry is not an index
continue
indices_dict.setdefault(row.index_name, []).append(row)
for index_name, row_list in list(indices_dict.items()):
index = grt.classes.db_Index()
index.name = index_name
index.isPrimary = 1 if index_name == pk_index_name else 0
index.unique = not row_list[0].non_unique
index.indexType = 'UNIQUE' if index.unique else 'INDEX'
# index.hasFilter = False # TODO: Find out if there's a way to determine this
skip = False
# Get the columns for the index:
for row in sorted(row_list, key=lambda elem: elem[7]): # Sorted by ordinal_position
column = find_object_with_name(table.columns, row.column_name)
if column:
# skip indexes on LONGCHAR columns
if column.simpleType.name in ["LONGCHAR"]:
grt.send_warning("Migration: reverseEngineerTable: Skipping index %s.%s on a %s column\n" % (table.name, column.name, column.simpleType.name))
skip = True
else:
index_column = grt.classes.db_IndexColumn()
index_column.name = index_name + '.' + row.column_name
index_column.referencedColumn = column
index.columns.append(index_column)
if not column.isNotNull and index.isPrimary:
column.isNotNull = 1
grt.send_warning("Migration: reverseEngineerTablePK: column %s.%s was changed to NOT NULL because it's a Primary Key column\n" % (column.owner.name, column.name))
else:
grt.send_warning("Migration: reverseEngineerTablePK: could not find column %s, belonging to key %s. Key will be skipped\n" % (row.column_name, index_name))
skip = True
if not skip:
table.addIndex(index)
if index.isPrimary:
table.primaryKey = index
return 0
def reverseEngineerTriggers(connection, schema):
execute_query(connection, 'USE %s' % schema.owner.name) # catalog
tables_with_triggers_query = """SELECT name, deltrig, instrig, updtrig
FROM sysobjects
WHERE uid = USER_ID(?) AND type='U'
AND(deltrig != 0 OR instrig != 0 OR updtrig != 0)"""
trigger_details_query = """SELECT so.name AS trigger_name, sc.id AS trigger_id, sc.text AS trigger_definition
FROM syscomments sc JOIN sysobjects so ON sc.id=so.id
WHERE sc.id IN (%s)
ORDER BY so.name, sc.colid"""
triggers = {}
for row in execute_query(connection, tables_with_triggers_query, schema.name):
if row[1] != 0:
triggers.setdefault(row[1], [row[0], ''])[1] += ';DELETE'
if row[2] != 0:
triggers.setdefault(row[2], [row[0], ''])[1] += ';INSERT'
if row[3] != 0:
triggers.setdefault(row[3], [row[0], ''])[1] += ';UPDATE'
step = 1.0 / (len(getTriggerNames(connection, schema.owner.name, schema.name)) + 1e-10)
all_triggers = execute_query(connection, trigger_details_query % ', '.join(str(trig_id) for trig_id in triggers)) if triggers else None
trigger_name2id = {}
def register_trigger_name(row):
trigger_name2id[row[0]] = row[1]
if all_triggers:
for idx, trigger_name, trigger_definition in join_multiline_content('trigger_name', 'trigger_definition',
all_triggers, register_trigger_name):
grt.send_progress(idx * step, 'Reverse engineering trigger %s.%s' % (schema.name, trigger_name))
trigger = grt.classes.db_sybase_Trigger()
trigger.name = trigger_name or ''
trigger.sqlDefinition = trigger_definition
trigger.timing = 'AFTER' # All Sybase ASE triggers are fired after the data is changed
# trigger.orientation = 'ROW' # TODO: This needs extra analysis
trigger.enabled = 1 # TODO: Figure out how to tell the actual value
trigger_table, trigger_events = triggers[trigger_name2id[trigger_name]]
trigger.event = trigger_events.strip(';') # It would take values as 'INSERT;UPDATE'
trigger.owner = find_object_with_name(schema.tables, trigger_table)
if trigger.owner:
trigger.owner.triggers.append(trigger)
else:
grt.send_warning('Sybase reverseEngineerTriggers', 'Parent table not found for trigger "%s"' % trigger.name)
grt.send_progress(1.0, 'Finished reverse engineering of triggers for the %s schema.' % schema.name)
return 0
def reverseEngineerTriggers(connection, schema):
execute_query(connection, 'USE %s' % schema.owner.name) # catalog
tables_with_triggers_query = """SELECT name, deltrig, instrig, updtrig
FROM sysobjects
WHERE uid = USER_ID(?) AND type='U'
AND(deltrig != 0 OR instrig != 0 OR updtrig != 0)"""
trigger_details_query = """SELECT so.name AS trigger_name, sc.id AS trigger_id, sc.text AS trigger_definition
FROM syscomments sc JOIN sysobjects so ON sc.id=so.id
WHERE sc.id IN (%s)
ORDER BY so.name, sc.colid"""
triggers = {}
for row in execute_query(connection, tables_with_triggers_query, schema.name):
if row[1] != 0:
triggers.setdefault(row[1], [row[0], ''])[1] += ';DELETE'
if row[2] != 0:
triggers.setdefault(row[2], [row[0], ''])[1] += ';INSERT'
if row[3] != 0:
triggers.setdefault(row[3], [row[0], ''])[1] += ';UPDATE'
step = 1.0 / (len(getTriggerNames(connection, schema.owner.name, schema.name)) + 1e-10)
all_triggers = execute_query(connection, trigger_details_query % ', '.join(str(trig_id) for trig_id in triggers)) if triggers else None
trigger_name2id = {}
def register_trigger_name(row):
trigger_name2id[row[0]] = row[1]
if all_triggers:
for idx, trigger_name, trigger_definition in join_multiline_content('trigger_name', 'trigger_definition',
all_triggers, register_trigger_name):
grt.send_progress(idx * step, 'Reverse engineering trigger %s.%s' % (schema.name, trigger_name))
trigger = grt.classes.db_sybase_Trigger()
trigger.name = trigger_name or ''
trigger.sqlDefinition = trigger_definition
trigger.timing = 'AFTER' # All Sybase ASE triggers are fired after the data is changed
# trigger.orientation = 'ROW' # TODO: This needs extra analysis
trigger.enabled = 1 # TODO: Figure out how to tell the actual value
trigger_table, trigger_events = triggers[trigger_name2id[trigger_name]]
trigger.event = trigger_events.strip(';') # It would take values as 'INSERT;UPDATE'
trigger.owner = find_object_with_name(schema.tables, trigger_table)
if trigger.owner:
trigger.owner.triggers.append(trigger)
else:
grt.send_warning('Sybase reverseEngineerTriggers', 'Parent table not found for trigger "%s"' % trigger.name)
grt.send_progress(1.0, 'Finished reverse engineering of triggers for the %s schema.' % schema.name)
return 0
def reverseEngineerTablePK(cls, connection, table):
"""Reverse engineers the primary key(s) for the given table."""
schema = table.owner
catalog = schema.owner
if len(table.columns) == 0: # Table must have columns reverse engineered before we can rev eng its primary key(s)
grt.send_error('Migration: reverseEngineerTablePKAndIndices: Reverse engineer of table %s was attempted but the table has no columns attribute' % table.name)
return 1
# Find the index name associated with the PK:
pk_index_row = cls.get_connection(connection).cursor().primaryKeys(catalog=catalog.name, schema=schema.name, table=table.name).fetchone()
pk_index_name = pk_index_row.pk_name if pk_index_row else ''
indices_dict = {} # Map the indices names to their respective columns:
for row in cls.get_connection(connection).cursor().statistics(catalog=catalog.name, schema=schema.name, table=table.name):
if row.type == constant.SQL_TABLE_STAT: # this entry is not an index
continue
indices_dict.setdefault(row.index_name, []).append(row)
for index_name, row_list in indices_dict.iteritems():
index = grt.classes.db_Index()
index.name = index_name
index.isPrimary = 1 if index_name == pk_index_name else 0
index.unique = not row_list[0].non_unique
index.indexType = 'UNIQUE' if index.unique else 'INDEX'
# index.hasFilter = False # TODO: Find out if there's a way to determine this
# Get the columns for the index:
for row in sorted(row_list, key=lambda elem: elem[7]): # Sorted by ordinal_position
column = find_object_with_name(table.columns, row.column_name)
if column:
index_column = grt.classes.db_IndexColumn()
index_column.name = index_name + '.' + row.column_name
index_column.referencedColumn = column
index.columns.append(index_column)
table.addIndex(index)
if index.isPrimary:
table.primaryKey = index
return 0
def reverseEngineerTablePK(connection, table):
"""Reverse engineers the primary key(s) for the given table."""
schema = table.owner
catalog = schema.owner
execute_query(connection, 'USE %s' % catalog.name)
query ="""SELECT sc.name
FROM sysobjects so JOIN syskeys sk ON so.id=sk.id
JOIN syscolumns sc ON sc.id=sk.id AND sc.colid IN (sk.key1, sk.key2, sk.key3, sk.key4, sk.key5, sk.key6, sk.key7, sk.key8)
WHERE so.uid=USER_ID(?) AND sk.id=OBJECT_ID(?) AND sk.type=1"""
if len(table.columns) == 0:
grt.send_error('Sybase reverseEngineerTablePK', "Reverse engineer of table's %s.%s primary key was attempted but the table has no columns attribute" % (schema.name, table.name) )
return 1 # Table must have columns reverse engineered before we can rev eng its primary key(s)
pk_col_names = [ row[0] for row in execute_query(connection, query, schema.name, table.name) ]
for pk_column in pk_col_names:
column = find_object_with_name(table.columns, pk_column)
if column:
table.addPrimaryKeyColumn(column)
return 0
def migrateColumnDefaultValue(self, state, default_value, source_column, target_column):
source_table = source_column.owner
source_schema = source_table.owner
target_catalog = target_column.owner.owner.owner
target_default_value = default_value
def raise_on_no_match(re_str, target):
if re.match(re_str, target) is None:
raise ValueError('"%s" does not match the regular expression "%s"' % (target, re_str))
return True
value_validators = [
(["SMALLINT", "INT", "BIGINT"], int),
(["NUMERIC", "DECIMAL", "FLOAT", "REAL", "DOUBLE PRECISION"], float),
(["CHAR", "VARCHAR", "NCHAR", "NVARCHAR", "BLOB", "CLOB", "XML"], lambda _: True),
(["BIT", "BIT VARYING"], lambda val: raise_on_no_match(r"[Bb]?'?[10]+'?", val)), # E.g. B'101001'
(["DATE"], lambda val: raise_on_no_match(r"(\d{4}|\d{2})-\d{1,2}-\d{1,2}", val)),
(["TIME"], lambda val: raise_on_no_match(r"(\d{1,2} )?\d{1,2}:\d{0,2}:\d{0,2}", val)),
(
["TIMESTAMP"],
lambda val: raise_on_no_match(
r"((\d{4}|\d{2})-\d{1,2}-\d{1,2}( (\d{1,2} )?\d{1,2}:\d{0,2}:\d{0,2})?|NULL|NOW\(\))", val.upper()
),
),
(["BOOLEAN"], lambda val: raise_on_no_match(r"(TRUE|FALSE|NULL)", val.upper())),
]
seq_regex = re.compile(r"nextval[(]'(.*?)'::regclass[)]")
source_datatype = None
if source_column.simpleType:
source_datatype = source_column.simpleType.name
if default_value:
match = seq_regex.match(default_value)
if match and target_column.simpleType and target_column.simpleType.group.name == "numeric":
seq = find_object_with_name(source_schema.sequences, match.group(1))
if seq:
pk_cols = (
set(column.referencedColumn.name for column in source_table.primaryKey.columns)
if source_table.primaryKey
else set()
)
if source_column.name in pk_cols: # AUTO_INCREMENT can only be used on columns in a key
try:
start = int(seq.startValue)
except ValueError:
pass
else:
if start > 1:
target_column.owner.nextAutoInc = seq.startValue
target_column.autoIncrement = 1
return ""
if target_column.simpleType.group.name == "numeric":
default_value = default_value.strip("' ")
if default_value:
if "::" in default_value:
default_value = default_value.split("::", 1)[0]
target_default_value = default_value
for value_validator in value_validators:
if source_datatype in value_validator[0]:
try:
value_validator[1](default_value)
except Exception:
target_default_value = ""
state.addMigrationLogEntry(
1,
source_column,
target_column,
"Default value %s is not supported. Removed!" % default_value,
)
else:
target_datatype = target_column.simpleType and target_column.simpleType.name or ""
if source_datatype == "BOOLEAN":
val = default_value.upper()
if val == "TRUE":
target_default_value = "1"
elif val == "FALSE":
target_default_value = "0"
elif source_datatype == "TIMESTAMP" and default_value.upper() == "NOW()":
if (
target_catalog.version.majorNumber,
target_catalog.version.minorNumber,
target_catalog.version.releaseNumber,
) < (5, 6, 5):
if target_datatype == "TIMESTAMP":
target_default_value = (
"CURRENT_TIMESTAMP"
) # now() => CURRENT_TIMESTAMP for TIMESTAMP columns in server v<5.6.5
else:
target_default_value = ""
state.addMigrationLogEntry(
1,
source_column,
target_column,
'Default value now() is not supported for a MySQL column of type "%s".Removed!'
% target_datatype,
)
else: # Server version from 5.6.5 and newer
target_default_value = (
"CURRENT_TIMESTAMP"
) # CURRENT_TIMESTAMP freely allowed for DATETIME & TIMESTAMP columns
return target_default_value
def reverseEngineerTableFKs(connection, table):
"""Reverse engineers the foreign keys for the given table."""
schema = table.owner
catalog = schema.owner
execute_query(connection, 'USE %s' % catalog.name)
query = """SELECT so.name, USER_NAME(so.uid),
COL_NAME(sk.id, key1),
COL_NAME(sk.id, key2),
COL_NAME(sk.id, key3),
COL_NAME(sk.id, key4),
COL_NAME(sk.id, key5),
COL_NAME(sk.id, key6),
COL_NAME(sk.id, key7),
COL_NAME(sk.id, key8),
COL_NAME(sk.depid, depkey1),
COL_NAME(sk.depid, depkey2),
COL_NAME(sk.depid, depkey3),
COL_NAME(sk.depid, depkey4),
COL_NAME(sk.depid, depkey5),
COL_NAME(sk.depid, depkey6),
COL_NAME(sk.depid, depkey7),
COL_NAME(sk.depid, depkey8)
FROM syskeys sk JOIN sysobjects so ON sk.depid = so.id
WHERE sk.type = 2 AND sk.id = OBJECT_ID('%s.%s')""" % (schema.name, table.name)
if len(table.columns) == 0:
grt.send_error('Sybase reverseEngineerTableFKs', 'Reverse engineer of foreign keys for table %s.%s was attempted but the table has no columns attribute' % (schema.name, table.name) )
return 1 # Table must have columns reverse engineered before we can rev eng its foreign keys
table.foreignKeys.remove_all()
for row in execute_query(connection, query):
fk_columns = [col_name for col_name in row[2:10] if col_name]
fk_ref_columns = [col_name for col_name in row[10:] if col_name]
foreign_key = grt.classes.db_sybase_ForeignKey()
foreign_key.name = '%s_%s_%s_fk' % (schema.name, table.name, '_'.join(fk_columns))
foreign_key.owner = table
foreign_key.deleteRule = foreign_key.updateRule = 'RESTRICT'
foreign_key.modelOnly = 0
referenced_schema = find_object_with_name(catalog.schemata, row[1])
if not referenced_schema:
grt.send_error('Sybase reverseEngineerTableFKs', 'Could not find schema "%s" in catalog "%s"' %
(schema.name, catalog.name) )
return 1
foreign_key.referencedTable = find_object_with_name(referenced_schema.tables, row[0])
if not foreign_key.referencedTable:
grt.send_error('Sybase reverseEngineerTableFKs', 'Could not find referenced table "%s" in schema "%s"' %
(row[0], schema.name) )
return 1
for column_name, referenced_column_name in zip(fk_columns, fk_ref_columns):
column = find_object_with_name(table.columns, column_name)
if not column:
grt.send_error('Sybase reverseEngineerTableFKs', 'Could not find column "%s" in table "%s.%s"' %
(column_name, schema.name, table.name) )
return 1
referenced_column = find_object_with_name(foreign_key.referencedTable.columns, referenced_column_name)
if not referenced_column:
grt.send_error('Sybase reverseEngineerTableFKs', 'Could not find column "%s" in table "%s.%s"' %
(referenced_column_name, referenced_schema.name, foreign_key.referencedTable.name) )
return 1
foreign_key.columns.append(column)
foreign_key.referencedColumns.append(referenced_column)
table.foreignKeys.append(foreign_key)
return 0
def reverseEngineerTableIndices(connection, table):
"""Reverse engineers the indices for the given table."""
schema = table.owner
catalog = schema.owner
if len(table.columns) == 0:
grt.send_error('Sybase reverseEngineerTableIndices', 'Reverse engineer of table %s.%s was attempted but the table has no columns attribute' % (schema.name, table.name) )
return 1 # Table must have columns reverse engineered before we can rev eng its indices
execute_query(connection, 'USE %s' % catalog.name)
query = """SELECT INDEX_NAME = A.name,
IS_CLUSTERED = CASE
WHEN ((A.status&16) = 16 OR (A.status2&512) = 512) THEN 1
ELSE 0
END,
IS_PRIMARY = CASE
WHEN ((A.status&0x800) = 0x800) THEN 1
ELSE 0
END,
IS_UNIQUE = CASE
WHEN ((A.status&2) = 2) THEN 1
ELSE 0
END,
IGNORE_DUP = CASE
WHEN ((A.status&4) = 4) THEN 1
ELSE 0
END,
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 1),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 2),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 3),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 4),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 5),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 6),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 7),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 8),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 9),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 10),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 11),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 12),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 13),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 14),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 15),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 16),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 17),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 18),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 19),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 20),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 21),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 22),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 23),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 24),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 25),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 26),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 27),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 28),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 29),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 30),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 31)
FROM sysindexes A, sysobjects B
WHERE A.indid > 0 AND A.indid < 255 AND A.status2 & 2 != 2 AND
B.id = A.id AND B.type = 'U' AND
USER_NAME(B.uid) = ? AND B.name=? ORDER BY 1, 2, 3"""
for index_row in execute_query(connection, query, schema.name, table.name):
index = grt.classes.db_sybase_Index()
index.name = index_row[0]
index.clustered = index_row[1]
index.isPrimary = index_row[2]
index.unique = index_row[3]
index.indexType = 'UNIQUE' if index.unique else 'INDEX'
index.ignoreDuplicateRows = index_row[4]
table.addIndex(index)
# Get the columns for the index:
index_column_names = [colname for colname in index_row[5:] if colname is not None]
for column_name in index_column_names:
column = find_object_with_name(table.columns, column_name)
if column:
index_column = grt.classes.db_sybase_IndexColumn()
index_column.name = index.name + '.' + column_name
index_column.referencedColumn = column
index.columns.append(index_column)
return 0
def reverseEngineerUserDatatypes(connection, catalog):
base_types = dict( (
(34,'IMAGE'),
(35,'TEXT'),
(36,'EXTENDED TYPE'),
(37,'TIMESTAMP'),
(38,'INTN'),
(39,'VARCHAR'),
(45,'BINARY'),
(47,'CHAR'),
(48,'TINYINT'),
(49,'DATE'),
(50,'BIT'),
(51,'TIME'),
(52,'SMALLINT'),
(55,'DECIMAL'),
(56,'INT'),
(58,'SMALLDATETIME'),
(59,'REAL'),
(60,'MONEY'),
(61,'DATETIME'),
(62,'FLOAT'),
(63,'NUMERIC'),
(65,'USMALLINT'),
(66,'UINT'),
(67,'UBIGINT'),
(68,'UINTN'),
(106,'DECIMALN'),
(108,'NUMERICN'),
(109,'FLOATN'),
(110,'MONEYN'),
(111,'DATETIMN'),
(122,'SMALLMONEY'),
(123,'DATEN'),
(135,'UNICHAR'),
(147,'TIMEN'),
(155,'UNIVARCHAR'),
(169,'TEXT_LOCATOR'),
(170,'IMAGE_LOCATOR'),
(171,'UNITEXT_LOCATOR'),
(174,'UNITEXT'),
(187,'BIGDATETIMEN'),
(188,'BIGTIMEN'),
(189,'BIGDATETIME'),
(190,'BIGTIME'),
(191,'BIGINT'),
) )
query = """SELECT name, length, prec, scale, allownulls, type
FROM systypes
WHERE accessrule != NULL"""
execute_query(connection, 'USE %s' % quoteIdentifier(catalog.name))
sybase_rdbms_instance = get_sybase_rdbms_instance()
catalog.userDatatypes.remove_all()
for name, length, precision, scale, is_nullable, base_type in execute_query(connection, query):
datatype = grt.classes.db_sybase_UserDatatype()
datatype.name = name.upper()
datatype.characterMaximumLength = length
datatype.numericPrecision = precision
datatype.numericScale = scale
datatype.isNullable = is_nullable
simple_type = find_object_with_name(sybase_rdbms_instance.simpleDatatypes, base_types[base_type])
if simple_type:
datatype.actualType = simple_type
else:
grt.send_warning('Sybase reverseEngineerUserDatatypes', 'Could not found base type "%s" for user defined type "%s"' % (base_type, name) )
catalog.userDatatypes.append(datatype)
return 0
def migrateColumnDefaultValue(self, state, default_value, source_column,
target_column):
source_table = source_column.owner
source_schema = source_table.owner
target_catalog = target_column.owner.owner.owner
target_default_value = default_value
def raise_on_no_match(re_str, target):
if re.match(re_str, target) is None:
raise ValueError(
'"%s" does not match the regular expression "%s"' %
(target, re_str))
return True
value_validators = [
(['SMALLINT', 'INT', 'BIGINT'], int),
(['NUMERIC', 'DECIMAL', 'FLOAT', 'REAL',
'DOUBLE PRECISION'], float),
(['CHAR', 'VARCHAR', 'NCHAR', 'NVARCHAR', 'BLOB', 'CLOB',
'XML'], lambda _: True),
(['BIT', 'BIT VARYING'
], lambda val: raise_on_no_match(r"[Bb]?'?[10]+'?", val)
), # E.g. B'101001'
(['DATE'], lambda val: raise_on_no_match(
r"(\d{4}|\d{2})-\d{1,2}-\d{1,2}", val)),
(['TIME'], lambda val: raise_on_no_match(
r"(\d{1,2} )?\d{1,2}:\d{0,2}:\d{0,2}", val)),
(['TIMESTAMP'], lambda val: raise_on_no_match(
r"((\d{4}|\d{2})-\d{1,2}-\d{1,2}( (\d{1,2} )?\d{1,2}:\d{0,2}:\d{0,2})?|NULL|NOW\(\))",
val.upper())),
(['BOOLEAN'],
lambda val: raise_on_no_match(r'(TRUE|FALSE|NULL)', val.upper())),
]
seq_regex = re.compile(r"nextval[(]'(.*?)'::regclass[)]")
source_datatype = None
if source_column.simpleType:
source_datatype = source_column.simpleType.name
if default_value:
match = seq_regex.match(default_value)
if match and target_column.simpleType and target_column.simpleType.group.name == 'numeric':
seq = find_object_with_name(source_schema.sequences,
match.group(1))
if seq:
pk_cols = set(
column.referencedColumn.name
for column in source_table.primaryKey.columns
) if source_table.primaryKey else set()
if source_column.name in pk_cols: # AUTO_INCREMENT can only be used on columns in a key
try:
start = int(seq.startValue)
except ValueError:
pass
else:
if start > 1:
target_column.owner.nextAutoInc = seq.startValue
target_column.autoIncrement = 1
return ''
if target_column.simpleType.group.name == 'numeric':
default_value = default_value.strip("' ")
if default_value:
if '::' in default_value:
default_value = default_value.split('::', 1)[0]
target_default_value = default_value
for value_validator in value_validators:
if source_datatype in value_validator[0]:
try:
value_validator[1](default_value)
except Exception:
target_default_value = ''
state.addMigrationLogEntry(
1, source_column, target_column,
'Default value %s is not supported. Removed!' %
default_value)
else:
target_datatype = target_column.simpleType and target_column.simpleType.name or ''
if source_datatype == 'BOOLEAN':
val = default_value.upper()
if val == 'TRUE':
target_default_value = '1'
elif val == 'FALSE':
target_default_value = '0'
elif source_datatype == 'TIMESTAMP' and default_value.upper(
) == 'NOW()':
if (target_catalog.version.majorNumber,
target_catalog.version.minorNumber,
target_catalog.version.releaseNumber) < (
5, 6, 5):
if target_datatype == 'TIMESTAMP':
target_default_value = 'CURRENT_TIMESTAMP' # now() => CURRENT_TIMESTAMP for TIMESTAMP columns in server v<5.6.5
else:
target_default_value = ''
state.addMigrationLogEntry(
1, source_column, target_column,
'Default value now() is not supported for a MySQL column of type "%s".Removed!'
% target_datatype)
else: # Server version from 5.6.5 and newer
target_default_value = 'CURRENT_TIMESTAMP' # CURRENT_TIMESTAMP freely allowed for DATETIME & TIMESTAMP columns
return target_default_value
def reverseEngineerTableFKs(cls, connection, table):
"""Reverse engineers the foreign keys for the given table."""
catalog = table.owner.owner
schema = table.owner
query = """SELECT kcu1.COLUMN_NAME,
rc.CONSTRAINT_NAME, kcu2.TABLE_SCHEMA, kcu2.TABLE_NAME,
kcu2.COLUMN_NAME, rc.UPDATE_RULE, rc.DELETE_RULE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
ON rc.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu1
ON kcu1.CONSTRAINT_CATALOG = rc.CONSTRAINT_CATALOG
AND kcu1.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
AND kcu1.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu2
ON kcu2.CONSTRAINT_CATALOG = rc.UNIQUE_CONSTRAINT_CATALOG
AND kcu2.CONSTRAINT_SCHEMA = rc.UNIQUE_CONSTRAINT_SCHEMA
AND kcu2.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY' AND kcu1.ORDINAL_POSITION = kcu2.ORDINAL_POSITION
AND kcu1.TABLE_CATALOG = ?
AND kcu1.TABLE_SCHEMA = ?
AND kcu1.TABLE_NAME = ?
ORDER BY kcu1.CONSTRAINT_NAME, kcu1.ORDINAL_POSITION"""
if len(table.columns) == 0:
grt.send_error('%s reverseEngineerTableFKs: Reverse engineering of table '
'%s was attempted but the table has no columns attribute' % (cls.getTargetDBMSName(), table.name))
return 1 # Table must have columns reverse engineered before we can rev eng its foreign keys
cursor = cls.execute_query(connection, query, catalog.name, schema.name, table.name)
current_fk = None
table.foreignKeys.remove_all()
for col_name, fk_name, ref_schema, ref_table, ref_col, upd_rule, del_rule in cursor:
if not current_fk or fk_name != current_fk.name:
if current_fk:
table.foreignKeys.append(current_fk)
foreign_key = grt.classes.db_ForeignKey()
foreign_key.name = fk_name
foreign_key.owner = table
foreign_key.deleteRule = del_rule.upper()
foreign_key.updateRule = upd_rule.upper()
foreign_key.modelOnly = 0
referenced_schema = find_object_with_name(catalog.schemata, ref_schema)
if not referenced_schema:
grt.send_warning('%s reverseEngineerTableFKs: Could not find referenced schema "%s" '
'for foreign key constraint "%s"' % (cls.getTargetDBMSName(), ref_schema, fk_name))
continue
referenced_table = find_object_with_name(referenced_schema.tables, ref_table)
if not referenced_table:
grt.send_warning('%s reverseEngineerTableFKs: Could not find referenced table "%s.%s" '
'for foreign key constraint "%s"' % (cls.getTargetDBMSName(), ref_schema, ref_table, fk_name))
continue
if len(referenced_table.columns) == 0:
grt.send_error('%s reverseEngineerTableFKs: Reverse engineering of table '
'%s was attempted but the table has no columns attribute' % (cls.getTargetDBMSName(), referenced_table.name))
return 1 # Table must have columns reverse engineered before we can rev eng its foreign keys
foreign_key.referencedTable = referenced_table
current_fk = foreign_key
column = find_object_with_name(table.columns, col_name)
if not column:
grt.send_warning('%s reverseEngineerTableFKs: Could not find column "%s.%s.%s" '
'for foreign key constraint "%s"' % (cls.getTargetDBMSName(), schema.name, table.name, col_name, fk_name))
continue
current_fk.columns.append(column)
referenced_column = find_object_with_name(current_fk.referencedTable.columns, ref_col)
if not referenced_column:
grt.send_warning('%s reverseEngineerTableFKs: Could not find referenced column "%s.%s.%s" '
'for foreign key constraint "%s"' % (cls.getTargetDBMSName(), ref_schema, ref_table, ref_col, fk_name))
continue
current_fk.referencedColumns.append(referenced_column)
# Store the last fk:
if current_fk:
table.foreignKeys.append(current_fk)
return 0
def reverseEngineerUserDatatypes(connection, catalog):
base_types = dict((
(34, 'IMAGE'),
(35, 'TEXT'),
(36, 'EXTENDED TYPE'),
(37, 'TIMESTAMP'),
(38, 'INTN'),
(39, 'VARCHAR'),
(45, 'BINARY'),
(47, 'CHAR'),
(48, 'TINYINT'),
(49, 'DATE'),
(50, 'BIT'),
(51, 'TIME'),
(52, 'SMALLINT'),
(55, 'DECIMAL'),
(56, 'INT'),
(58, 'SMALLDATETIME'),
(59, 'REAL'),
(60, 'MONEY'),
(61, 'DATETIME'),
(62, 'FLOAT'),
(63, 'NUMERIC'),
(65, 'USMALLINT'),
(66, 'UINT'),
(67, 'UBIGINT'),
(68, 'UINTN'),
(106, 'DECIMALN'),
(108, 'NUMERICN'),
(109, 'FLOATN'),
(110, 'MONEYN'),
(111, 'DATETIMN'),
(122, 'SMALLMONEY'),
(123, 'DATEN'),
(135, 'UNICHAR'),
(147, 'TIMEN'),
(155, 'UNIVARCHAR'),
(169, 'TEXT_LOCATOR'),
(170, 'IMAGE_LOCATOR'),
(171, 'UNITEXT_LOCATOR'),
(174, 'UNITEXT'),
(187, 'BIGDATETIMEN'),
(188, 'BIGTIMEN'),
(189, 'BIGDATETIME'),
(190, 'BIGTIME'),
(191, 'BIGINT'),
))
query = """SELECT name, length, prec, scale, allownulls, type
FROM systypes
WHERE accessrule != NULL"""
execute_query(connection, 'USE %s' % quoteIdentifier(catalog.name))
sybase_rdbms_instance = get_sybase_rdbms_instance()
catalog.userDatatypes.remove_all()
for name, length, precision, scale, is_nullable, base_type in execute_query(
connection, query):
datatype = grt.classes.db_sybase_UserDatatype()
datatype.name = name.upper()
datatype.characterMaximumLength = length
datatype.numericPrecision = precision
datatype.numericScale = scale
datatype.isNullable = is_nullable
simple_type = find_object_with_name(
sybase_rdbms_instance.simpleDatatypes, base_types[base_type])
if simple_type:
datatype.actualType = simple_type
else:
grt.send_warning(
'Sybase reverseEngineerUserDatatypes',
'Could not found base type "%s" for user defined type "%s"' %
(base_type, name))
catalog.userDatatypes.append(datatype)
return 0
def reverseEngineerTableIndices(connection, table):
"""Reverse engineers the indices for the given table."""
schema = table.owner
catalog = schema.owner
if len(table.columns) == 0:
grt.send_error(
'Sybase reverseEngineerTableIndices',
'Reverse engineer of table %s.%s was attempted but the table has no columns attribute'
% (schema.name, table.name))
return 1 # Table must have columns reverse engineered before we can rev eng its indices
execute_query(connection, 'USE %s' % catalog.name)
query = """SELECT INDEX_NAME = A.name,
IS_CLUSTERED = CASE
WHEN ((A.status&16) = 16 OR (A.status2&512) = 512) THEN 1
ELSE 0
END,
IS_PRIMARY = CASE
WHEN ((A.status&0x800) = 0x800) THEN 1
ELSE 0
END,
IS_UNIQUE = CASE
WHEN ((A.status&2) = 2) THEN 1
ELSE 0
END,
IGNORE_DUP = CASE
WHEN ((A.status&4) = 4) THEN 1
ELSE 0
END,
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 1),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 2),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 3),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 4),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 5),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 6),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 7),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 8),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 9),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 10),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 11),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 12),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 13),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 14),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 15),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 16),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 17),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 18),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 19),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 20),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 21),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 22),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 23),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 24),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 25),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 26),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 27),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 28),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 29),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 30),
INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 31)
FROM sysindexes A, sysobjects B
WHERE A.indid > 0 AND A.indid < 255 AND A.status2 & 2 != 2 AND
B.id = A.id AND B.type = 'U' AND
USER_NAME(B.uid) = ? AND B.name=? ORDER BY 1, 2, 3"""
for index_row in execute_query(connection, query, schema.name, table.name):
index = grt.classes.db_sybase_Index()
index.name = index_row[0]
index.clustered = index_row[1]
index.isPrimary = index_row[2]
index.unique = index_row[3]
index.indexType = 'UNIQUE' if index.unique else 'INDEX'
index.ignoreDuplicateRows = index_row[4]
table.addIndex(index)
# Get the columns for the index:
index_column_names = [
colname for colname in index_row[5:] if colname is not None
]
for column_name in index_column_names:
column = find_object_with_name(table.columns, column_name)
if column:
index_column = grt.classes.db_sybase_IndexColumn()
index_column.name = index.name + '.' + column_name
index_column.referencedColumn = column
index.columns.append(index_column)
return 0
def reverseEngineerTableFKs(connection, table):
"""Reverse engineers the foreign keys for the given table."""
schema = table.owner
catalog = schema.owner
execute_query(connection, 'USE %s' % catalog.name)
query = """SELECT so.name, USER_NAME(so.uid),
COL_NAME(sk.id, key1),
COL_NAME(sk.id, key2),
COL_NAME(sk.id, key3),
COL_NAME(sk.id, key4),
COL_NAME(sk.id, key5),
COL_NAME(sk.id, key6),
COL_NAME(sk.id, key7),
COL_NAME(sk.id, key8),
COL_NAME(sk.depid, depkey1),
COL_NAME(sk.depid, depkey2),
COL_NAME(sk.depid, depkey3),
COL_NAME(sk.depid, depkey4),
COL_NAME(sk.depid, depkey5),
COL_NAME(sk.depid, depkey6),
COL_NAME(sk.depid, depkey7),
COL_NAME(sk.depid, depkey8)
FROM syskeys sk JOIN sysobjects so ON sk.depid = so.id
WHERE sk.type = 2 AND sk.id = OBJECT_ID('%s.%s')""" % (schema.name, table.name)
if len(table.columns) == 0:
grt.send_error(
'Sybase reverseEngineerTableFKs',
'Reverse engineer of foreign keys for table %s.%s was attempted but the table has no columns attribute'
% (schema.name, table.name))
return 1 # Table must have columns reverse engineered before we can rev eng its foreign keys
table.foreignKeys.remove_all()
for row in execute_query(connection, query):
fk_columns = [col_name for col_name in row[2:10] if col_name]
fk_ref_columns = [col_name for col_name in row[10:] if col_name]
foreign_key = grt.classes.db_sybase_ForeignKey()
foreign_key.name = '%s_%s_%s_fk' % (schema.name, table.name,
'_'.join(fk_columns))
foreign_key.owner = table
foreign_key.deleteRule = foreign_key.updateRule = 'RESTRICT'
foreign_key.modelOnly = 0
referenced_schema = find_object_with_name(catalog.schemata, row[1])
if not referenced_schema:
grt.send_error(
'Sybase reverseEngineerTableFKs',
'Could not find schema "%s" in catalog "%s"' %
(schema.name, catalog.name))
return 1
foreign_key.referencedTable = find_object_with_name(
referenced_schema.tables, row[0])
if not foreign_key.referencedTable:
grt.send_error(
'Sybase reverseEngineerTableFKs',
'Could not find referenced table "%s" in schema "%s"' %
(row[0], schema.name))
return 1
for column_name, referenced_column_name in zip(fk_columns,
fk_ref_columns):
column = find_object_with_name(table.columns, column_name)
if not column:
grt.send_error(
'Sybase reverseEngineerTableFKs',
'Could not find column "%s" in table "%s.%s"' %
(column_name, schema.name, table.name))
return 1
referenced_column = find_object_with_name(
foreign_key.referencedTable.columns, referenced_column_name)
if not referenced_column:
grt.send_error(
'Sybase reverseEngineerTableFKs',
'Could not find column "%s" in table "%s.%s"' %
(referenced_column_name, referenced_schema.name,
foreign_key.referencedTable.name))
return 1
foreign_key.columns.append(column)
foreign_key.referencedColumns.append(referenced_column)
table.foreignKeys.append(foreign_key)
return 0
def reverseEngineer(cls, connection, catalog_name, schemata_list, context):
grt.send_progress(0, "Reverse engineering catalog information")
cls.check_interruption()
catalog = cls.reverseEngineerCatalog(connection, catalog_name)
# calculate total workload 1st
grt.send_progress(0.1, 'Preparing...')
table_count_per_schema = {}
view_count_per_schema = {}
routine_count_per_schema = {}
trigger_count_per_schema = {}
total_count_per_schema = {}
get_tables = context.get("reverseEngineerTables", True)
get_triggers = context.get("reverseEngineerTriggers", True)
get_views = context.get("reverseEngineerViews", True)
get_routines = context.get("reverseEngineerRoutines", True)
# 10% of the progress is for preparation
total = 1e-10 # total should not be zero to avoid DivisionByZero exceptions
i = 0.0
accumulated_progress = 0.1
for schema_name in schemata_list:
cls.check_interruption()
table_count_per_schema[schema_name] = len(cls.getTableNames(connection, catalog_name, schema_name)) if get_tables else 0
view_count_per_schema[schema_name] = len(cls.getViewNames(connection, catalog_name, schema_name)) if get_views else 0
cls.check_interruption()
routine_count_per_schema[schema_name] = len(cls.getProcedureNames(connection, catalog_name, schema_name)) + len(cls.getFunctionNames(connection, catalog_name, schema_name)) if get_routines else 0
trigger_count_per_schema[schema_name] = len(cls.getTriggerNames(connection, catalog_name, schema_name)) if get_triggers else 0
total_count_per_schema[schema_name] = (table_count_per_schema[schema_name] + view_count_per_schema[schema_name] +
routine_count_per_schema[schema_name] + trigger_count_per_schema[schema_name] + 1e-10)
total += total_count_per_schema[schema_name]
grt.send_progress(accumulated_progress + 0.1 * (i / (len(schemata_list) + 1e-10) ), "Gathered stats for %s" % schema_name)
i += 1.0
# Now take 60% in the first pass of reverse engineering:
accumulated_progress = 0.2
for schema_name in schemata_list:
schema_progress_share = 0.6 * (total_count_per_schema.get(schema_name, 0.0) / total)
schema = find_object_with_name(catalog.schemata, schema_name)
if schema:
# Reverse engineer tables:
step_progress_share = schema_progress_share * (table_count_per_schema[schema_name] / (total_count_per_schema[schema_name] + 1e-10))
if get_tables:
cls.check_interruption()
grt.send_info('Reverse engineering tables from %s' % schema_name)
grt.begin_progress_step(accumulated_progress, accumulated_progress + step_progress_share)
# Remove previous first pass marks that may exist if the user goes back and attempt rev eng again:
progress_flags = cls._connections[connection.__id__].setdefault('_rev_eng_progress_flags', set())
progress_flags.discard('%s_tables_first_pass' % schema_name)
cls.reverseEngineerTables(connection, schema)
grt.end_progress_step()
accumulated_progress += step_progress_share
grt.send_progress(accumulated_progress, 'First pass of table reverse engineering for schema %s completed!' % schema_name)
# Reverse engineer views:
step_progress_share = schema_progress_share * (view_count_per_schema[schema_name] / (total_count_per_schema[schema_name] + 1e-10))
if get_views:
cls.check_interruption()
grt.send_info('Reverse engineering views from %s' % schema_name)
grt.begin_progress_step(accumulated_progress, accumulated_progress + step_progress_share)
cls.reverseEngineerViews(connection, schema)
grt.end_progress_step()
accumulated_progress += step_progress_share
grt.send_progress(accumulated_progress, 'Reverse engineering of views for schema %s completed!' % schema_name)
# Reverse engineer routines:
step_progress_share = schema_progress_share * (routine_count_per_schema[schema_name] / (total_count_per_schema[schema_name] + 1e-10))
if get_routines:
cls.check_interruption()
grt.send_info('Reverse engineering routines from %s' % schema_name)
grt.begin_progress_step(accumulated_progress, accumulated_progress + step_progress_share)
grt.begin_progress_step(0.0, 0.5)
cls.reverseEngineerProcedures(connection, schema)
cls.check_interruption()
grt.end_progress_step()
grt.begin_progress_step(0.5, 1.0)
reverseEngineerFunctions(connection, schema)
grt.end_progress_step()
grt.end_progress_step()
accumulated_progress += step_progress_share
grt.send_progress(accumulated_progress, 'Reverse engineering of routines for schema %s completed!' % schema_name)
# Reverse engineer triggers:
step_progress_share = schema_progress_share * (trigger_count_per_schema[schema_name] / (total_count_per_schema[schema_name] + 1e-10))
if get_triggers:
cls.check_interruption()
grt.send_info('Reverse engineering triggers from %s' % schema_name)
grt.begin_progress_step(accumulated_progress, accumulated_progress + step_progress_share)
cls.reverseEngineerTriggers(connection, schema)
grt.end_progress_step()
accumulated_progress = 0.8
grt.send_progress(accumulated_progress, 'Reverse engineering of triggers for schema %s completed!' % schema_name)
else: # No schema with the given name was found
grt.send_warning('The schema %s was not found in the catalog %s. Skipping it.' % (schema_name, catalog_name) )
# Now the second pass for reverse engineering tables:
if get_tables:
total_tables = sum(table_count_per_schema[schema.name] for schema in catalog.schemata if schema.name in schemata_list)
for schema in catalog.schemata:
if schema.name not in schemata_list:
continue
cls.check_interruption()
step_progress_share = 0.2 * (table_count_per_schema[schema.name] / (total_tables + 1e-10))
grt.send_info('Reverse engineering foreign keys for tables in schema %s' % schema.name)
grt.begin_progress_step(accumulated_progress, accumulated_progress + step_progress_share)
cls.reverseEngineerTables(connection, schema)
grt.end_progress_step()
accumulated_progress += step_progress_share
grt.send_progress(accumulated_progress, 'Second pass of table reverse engineering for schema %s completed!' % schema_name)
grt.send_progress(1.0, 'Reverse engineering completed!')
return catalog
def reverseEngineer(cls, connection, catalog_name, schemata_list, context):
grt.send_progress(0, "Reverse engineering catalog information")
cls.check_interruption()
catalog = cls.reverseEngineerCatalog(connection, catalog_name)
# calculate total workload 1st
grt.send_progress(0.1, 'Preparing...')
table_count_per_schema = {}
view_count_per_schema = {}
routine_count_per_schema = {}
trigger_count_per_schema = {}
total_count_per_schema = {}
get_tables = context.get("reverseEngineerTables", True)
get_triggers = context.get("reverseEngineerTriggers", True)
get_views = context.get("reverseEngineerViews", True)
get_routines = context.get("reverseEngineerRoutines", True)
# 10% of the progress is for preparation
total = 1e-10 # total should not be zero to avoid DivisionByZero exceptions
i = 0.0
accumulated_progress = 0.1
for schema_name in schemata_list:
cls.check_interruption()
table_count_per_schema[schema_name] = len(cls.getTableNames(connection, catalog_name, schema_name)) if get_tables else 0
view_count_per_schema[schema_name] = len(cls.getViewNames(connection, catalog_name, schema_name)) if get_views else 0
cls.check_interruption()
routine_count_per_schema[schema_name] = len(cls.getProcedureNames(connection, catalog_name, schema_name)) + len(cls.getFunctionNames(connection, catalog_name, schema_name)) if get_routines else 0
trigger_count_per_schema[schema_name] = len(cls.getTriggerNames(connection, catalog_name, schema_name)) if get_triggers else 0
total_count_per_schema[schema_name] = (table_count_per_schema[schema_name] + view_count_per_schema[schema_name] +
routine_count_per_schema[schema_name] + trigger_count_per_schema[schema_name] + 1e-10)
total += total_count_per_schema[schema_name]
grt.send_progress(accumulated_progress + 0.1 * (i / (len(schemata_list) + 1e-10) ), "Gathered stats for %s" % schema_name)
i += 1.0
# Now take 60% in the first pass of reverse engineering:
accumulated_progress = 0.2
for schema_name in schemata_list:
schema_progress_share = 0.6 * (total_count_per_schema.get(schema_name, 0.0) / total)
schema = find_object_with_name(catalog.schemata, schema_name)
if schema:
# Reverse engineer tables:
step_progress_share = schema_progress_share * (table_count_per_schema[schema_name] / (total_count_per_schema[schema_name] + 1e-10))
if get_tables:
cls.check_interruption()
grt.send_info('Reverse engineering tables from %s' % schema_name)
grt.begin_progress_step(accumulated_progress, accumulated_progress + step_progress_share)
# Remove previous first pass marks that may exist if the user goes back and attempt rev eng again:
progress_flags = cls._connections[connection.__id__].setdefault('_rev_eng_progress_flags', set())
progress_flags.discard('%s_tables_first_pass' % schema_name)
cls.reverseEngineerTables(connection, schema)
grt.end_progress_step()
accumulated_progress += step_progress_share
grt.send_progress(accumulated_progress, 'First pass of table reverse engineering for schema %s completed!' % schema_name)
# Reverse engineer views:
step_progress_share = schema_progress_share * (view_count_per_schema[schema_name] / (total_count_per_schema[schema_name] + 1e-10))
if get_views:
cls.check_interruption()
grt.send_info('Reverse engineering views from %s' % schema_name)
grt.begin_progress_step(accumulated_progress, accumulated_progress + step_progress_share)
cls.reverseEngineerViews(connection, schema)
grt.end_progress_step()
accumulated_progress += step_progress_share
grt.send_progress(accumulated_progress, 'Reverse engineering of views for schema %s completed!' % schema_name)
# Reverse engineer routines:
step_progress_share = schema_progress_share * (routine_count_per_schema[schema_name] / (total_count_per_schema[schema_name] + 1e-10))
if get_routines:
cls.check_interruption()
grt.send_info('Reverse engineering routines from %s' % schema_name)
grt.begin_progress_step(accumulated_progress, accumulated_progress + step_progress_share)
grt.begin_progress_step(0.0, 0.5)
cls.reverseEngineerProcedures(connection, schema)
cls.check_interruption()
grt.end_progress_step()
grt.begin_progress_step(0.5, 1.0)
reverseEngineerFunctions(connection, schema)
grt.end_progress_step()
grt.end_progress_step()
accumulated_progress += step_progress_share
grt.send_progress(accumulated_progress, 'Reverse engineering of routines for schema %s completed!' % schema_name)
# Reverse engineer triggers:
step_progress_share = schema_progress_share * (trigger_count_per_schema[schema_name] / (total_count_per_schema[schema_name] + 1e-10))
if get_triggers:
cls.check_interruption()
grt.send_info('Reverse engineering triggers from %s' % schema_name)
grt.begin_progress_step(accumulated_progress, accumulated_progress + step_progress_share)
cls.reverseEngineerTriggers(connection, schema)
grt.end_progress_step()
accumulated_progress = 0.8
grt.send_progress(accumulated_progress, 'Reverse engineering of triggers for schema %s completed!' % schema_name)
else: # No schema with the given name was found
grt.send_warning('The schema %s was not found in the catalog %s. Skipping it.' % (schema_name, catalog_name) )
# Now the second pass for reverse engineering tables:
if get_tables:
total_tables = sum(table_count_per_schema[schema.name] for schema in catalog.schemata if schema.name in schemata_list)
for schema in catalog.schemata:
if schema.name not in schemata_list:
continue
cls.check_interruption()
step_progress_share = 0.2 * (table_count_per_schema[schema.name] / (total_tables + 1e-10))
grt.send_info('Reverse engineering foreign keys for tables in schema %s' % schema.name)
grt.begin_progress_step(accumulated_progress, accumulated_progress + step_progress_share)
cls.reverseEngineerTables(connection, schema)
grt.end_progress_step()
accumulated_progress += step_progress_share
grt.send_progress(accumulated_progress, 'Second pass of table reverse engineering for schema %s completed!' % schema_name)
grt.send_progress(1.0, 'Reverse engineering completed!')
return catalog