def get_foreign_keys(self, table, schema=None):
cursor = self.execute_sql(
f'PRAGMA "{schema or "main"}".foreign_key_list("{table}")')
return [
ForeignKeyMetadata(row[3], row[2], row[4], table)
for row in cursor.fetchall()
]
def stash_foreign_keys(self):
where, params = self.where_table_parm('kcu.table_schema', 'kcu.table_name')
query = (
"SELECT DISTINCT "
"kcu.table_schema, kcu.table_name, kcu.column_name, ccu.table_schema, ccu.table_name, ccu.column_name "
"FROM information_schema.table_constraints AS tc "
"JOIN information_schema.key_column_usage AS kcu "
"ON tc.constraint_name = kcu.constraint_name "
"AND tc.constraint_schema = kcu.constraint_schema "
"JOIN information_schema.constraint_column_usage AS ccu "
"ON ccu.constraint_name = tc.constraint_name "
"AND ccu.constraint_schema = tc.constraint_schema "
f"WHERE tc.constraint_type = 'FOREIGN KEY' AND {where}"
)
cursor = self.db.execute_sql(query, params)
fetch = cursor.fetchall()
foreign_keys = {}
for schema, g1 in groupby(fetch, key=itemgetter(0)):
foreign_keys[schema] = {}
for table, g2 in groupby(g1, key=itemgetter(1)):
foreign_keys[schema][table] = [
ForeignKeyMetadata(fc, pt, pc, ft)
for (fs, ft, fc, ps, pt, pc) in list(g2)
]
return foreign_keys
async def get_foreign_keys(self, table, schema=None):
query = """
SELECT column_name, referenced_table_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_name = %s
AND table_schema = DATABASE()
AND referenced_table_name IS NOT NULL
AND referenced_column_name IS NOT NULL"""
cursor = await self.execute_sql(query, (table, ))
return [
ForeignKeyMetadata(column, dest_table, dest_column, table)
for column, dest_table, dest_column in cursor.fetchall()
]
def get_foreign_keys(self, table, schema=None):
res = self.execute_sql(
'''SELECT
COL_NAME(fc.parent_object_id, fc.parent_column_id),
OBJECT_NAME (f.referenced_object_id),
COL_NAME(fc.referenced_object_id, fc.referenced_column_id)
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID=fc.constraint_object_id
WHERE OBJECT_NAME(f.parent_object_id)=?''', (table, ))
return [
ForeignKeyMetadata(col_name, ref_table, ref_col, table)
for col_name, ref_table, ref_col in res.fetchall()
]
def get_foreign_keys(self, table, schema='public'):
sql = """
SELECT
kcu.column_name, ccu.table_name, ccu.column_name
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON (tc.constraint_name = kcu.constraint_name AND
tc.constraint_schema = kcu.constraint_schema)
JOIN information_schema.constraint_column_usage AS ccu
ON (ccu.constraint_name = tc.constraint_name AND
ccu.constraint_schema = tc.constraint_schema)
WHERE
tc.constraint_type = 'FOREIGN KEY' AND
tc.table_name = %s AND
tc.table_schema = %s"""
cursor = yield self.execute_sql(sql, (table, schema))
raise gen.Return([ForeignKeyMetadata(row[0], row[1], row[2], table)
for row in cursor.fetchall()])
def get_foreign_keys(self, table, schema='dbo'):
return [
ForeignKeyMetadata(name, dest_tab, dest_col, table)
for name, *_, dest_tab, dest_col, con in self.md_cache(
table, schema) if con == 'FOREIGN KEY'
]