def fetch_constraints(self, table_id):
cursor = self.connection.cursor()
constraints_list = list()
constraints_attributes = cursor.execute(
"""\
select id, table_id, name, constraint_type, reference, unique_key_id, has_value_edit, cascading_delete, expression\
from dbd$constraints\
where dbd$constraints.table_id = ?""", (table_id, )).fetchall()
for attr_tuple in constraints_attributes:
constraint = Dbc.Constraint()
_, tbl_id, constraint.name, constraint.kind, constraint.reference, constraint.unique_key_id, \
constraint.has_value_edit, constraint.cascading_delete, constraint.expression = attr_tuple
constraint.has_value_edit, constraint.cascading_delete = map(
bool, [constraint.has_value_edit, constraint.cascading_delete])
constraint.items = cursor.execute(
"""\
select name from dbd$fields\
where dbd$fields.id = (\
select field_id from dbd$constraint_details\
where dbd$constraint_details.constraint_id = ?)""",
(attr_tuple[0], )).fetchone()[0]
constraint.reference = None if constraint.kind == "PRIMARY" else cursor.execute(
"""\
select name from dbd$tables where dbd$tables.id = ?""",
(constraint.reference, )).fetchone()[0]
constraints_list.append(constraint)
return constraints_list
def get_constraints(table):
"""
:param table: Dbc.Table() to find constraints in.
:return: constraints_list: list of constraints in table.
Parse table to find constraints.
"""
if table.nodeName != "table":
raise WrongNodeException("table", table.nodeName)
constraints_list = list()
for constraint in table.getElementsByTagName("constraint"):
const = Dbc.Constraint()
for an, av in constraint.attributes.items():
if an.lower() == "name":
const.name = av
elif an.lower() == "kind":
const.kind = av
elif an.lower() == "items":
const.items = av
elif an.lower() == "unique_key_id":
const.unique_key_id = av
elif an.lower() == "reference":
const.reference = av
elif an.lower() == "expression":
const.expression = av
elif an.lower() == "props":
for prop in av.split(", "):
if prop == "has_value_edit":
const.has_value_edit = True
elif prop == "cascading_delete":
const.cascading_delete = True
# elif prop == "full_cascading_delete":
# const.full_cascading_delete = True
elif prop == "full_cascading_delete":
pass
else:
raise WrongPropertyException([
"has_value_edit", "cascading_delete",
"full_cascading_delete"
], prop)
else:
raise WrongAttributeException([
"name", "constraint_type", "unique_key_id",
"expression"
"kind", "items", "reference_type", "reference", "props"
], an)
constraints_list.append(const)
return constraints_list
def fetch_constraints(self, schema_name, table_name):
cursor = self.connection.cursor()
get_primary_keys = cursor.execute(
"""
select
kc.name,
KCU.COLUMN_NAME as items,
kc.unique_index_id as unique_key_index
from sys.tables as t
join sys.key_constraints as kc
on t.object_id = kc.parent_object_id
join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as KCU
on KCU.CONSTRAINT_NAME = kc.name
where t.object_id = object_id(?);""",
(".".join([schema_name, table_name]))).fetchall()
primary_keys = list()
if get_primary_keys:
for key in get_primary_keys:
constraint = Dbc.Constraint()
constraint.name = key[0]
constraint.kind = "PRIMARY"
constraint.items = key[1]
constraint.unique_key_id = str(key[2])
primary_keys.append(constraint)
get_foreign_keys = cursor.execute(
"""
select
fk.name,
ac.name,
tt.name,
fk.delete_referential_action
from sys.tables as t
join sys.all_columns as ac
on t.object_id = ac.object_id
join sys.foreign_key_columns as fkc
on ac.column_id = fkc.parent_column_id and t.object_id = fkc.parent_object_id
join sys.foreign_keys as fk
on fkc.constraint_object_id = fk.object_id
join sys.tables as tt
on tt.object_id = fk.referenced_object_id
where t.object_id = object_id(?);""",
(".".join([schema_name, table_name]))).fetchall()
foreign_keys = list()
if get_foreign_keys:
for key in get_foreign_keys:
constraint = Dbc.Constraint()
constraint.name = key[0]
constraint.kind = "FOREIGN"
constraint.items = key[1]
constraint.reference = key[2]
constraint.cascading_delete = bool(key[3])
foreign_keys.append(constraint)
get_check_constraints = cursor.execute(
"""
select
cc.name,
ac.name as items,
cc.definition as expression
from sys.tables as t
join sys.all_columns as ac
on t.object_id = ac.object_id
join sys.check_constraints as cc
on ac.column_id = cc.parent_column_id and t.object_id = cc.parent_object_id
where t.object_id = object_id(?);""",
(".".join([schema_name, table_name]))).fetchall()
check_constraints = list()
if get_check_constraints:
for key in get_check_constraints:
constraint = Dbc.Constraint()
constraint.name = key[0]
constraint.kind = "CHECK"
constraint.items = key[1]
constraint.expression = key[2]
check_constraints.append(constraint)
return primary_keys + foreign_keys + check_constraints