def 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, full_cascading_delete, expression\
from dbd$constraints where dbd$constraints.table_id = ?""",
(table_id, )).fetchall()
for a in constraints_attributes:
constraint = dbc.Constraint()
i, tbl, constraint.name, constraint.kind, constraint.reference, constraint.unique_key_id, \
constraint.has_value_edit, constraint.cascading_delete,\
constraint.full_cascading_delete,constraint.expression = a
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 = ?)""",
(a[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):
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
constraints_list.append(const)
return constraints_list
def create_constraints_unique(self, table):
cursor = self.con.cursor()
cursor.execute("select K.CONSTRAINT_NAME, K.COLUMN_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE K "
"INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS T ON K.CONSTRAINT_NAME=T.CONSTRAINT_NAME "
"WHERE T.CONSTRAINT_TYPE='UNIQUE' AND T.TABLE_NAME='"+table.name+"'")
constraints = cursor.fetchall()
for constraint in constraints:
c = dbc.Constraint()
c.name = constraint[0]
c.items = constraint[1]
c.kind = "UNIQUE"
table.constraints.append(c)
cursor.close()
def create_constraints_check(self, table):
cursor = self.con.cursor()
cursor.execute("select K.CONSTRAINT_NAME, K.CHECK_CLAUSE from INFORMATION_SCHEMA.CHECK_CONSTRAINTS K "
"INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS T ON K.CONSTRAINT_NAME=T.CONSTRAINT_NAME "
"WHERE T.CONSTRAINT_TYPE='CHECK' AND T.TABLE_NAME='"+table.name+"'")
constraints = cursor.fetchall()
for constraint in constraints:
c = dbc.Constraint()
c.name = constraint[0]
c.expression = constraint[1]
c.kind = "CHECK"
table.constraints.append(c)
cursor.close()
def create_constraints_foreign(self, table):
cursor = self.con.cursor()
cursor.execute("SELECT fk.name, c1.name, OBJECT_NAME(fk.referenced_object_id), c2.name "
"FROM sys.foreign_keys fk INNER JOIN "
"sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id INNER JOIN "
"sys.columns c1 ON fkc.parent_column_id = c1.column_id AND fkc.parent_object_id = c1.object_id "
"INNER JOIN sys.columns c2 ON fkc.referenced_column_id = c2.column_id "
"AND fkc.referenced_object_id = c2.object_id "
"WHERE OBJECT_NAME(fk.parent_object_id)='"+table.name+"'")
constraints = cursor.fetchall()
for constraint in constraints:
c = dbc.Constraint()
c.name = constraint[0]
c.items = constraint[1]
c.reference = constraint[2]
c.ref_field = constraint[3]
c.kind = "FOREIGN"
table.constraints.append(c)
cursor.close()