def fetch_fields(self, table_name, schema):
cursor = self.connection.cursor()
field_list = list()
fields = cursor.execute("""
select
COLUMN_NAME as name,
t.name as dom,
ORDINAL_POSITION as position,
sc.is_identity as edit,
sc.is_hidden as show_in_grid,
sc.is_computed as autocalculated,
sc.is_nullable as required,
col.DATA_TYPE,
sc.scale,
sc.precision,
sc.max_length
from INFORMATION_SCHEMA.TABLES as tbl
left join INFORMATION_SCHEMA.COLUMNS as col
on col.TABLE_NAME = tbl.TABLE_NAME
left join sys.columns as sc
on sc.object_id = object_id(tbl.table_schema + '.' + tbl.table_name) and sc.NAME = col.COLUMN_NAME
left join sys.types as t
on col.DATA_TYPE = t.name
where tbl.TABLE_NAME = ?;""", (table_name,)).fetchall()
# НЕ РАБОТАЕТ (SQL type - 150 not supported yet)
# prop.value as descript,
# left join sys.extended_properties prop
# on prop.major_id = sc.object_id and prop.minor_id = sc.column_id and prop.NAME = 'MS_Description'
domain_list = list()
for fld in fields:
field = Dbc.Field()
field.name = fld[0]
field.domain = fld[1]
field.position = str(fld[2])
field.edit = fld[3]
field.show_in_grid = fld[4]
field.autocalculated = fld[5]
field.required = fld[6]
field.input = not(field.autocalculated or field.edit)
domain = Dbc.Domain()
domain.char_length = str(fld[10])
domain.precision = str(fld[9])
domain.scale = str(fld[8])
domain.type = TYPES[fld[7]]
domain_list.append(domain)
field.domain = domain
field_list.append(field)
# schema.domains += domain_list
return field_list
def fetch_indices(self, table_id):
cursor = self.connection.cursor()
indices_list = list()
indices_attributes = cursor.execute(
"""\
select id, name, local, kind\
from dbd$indices\
where dbd$indices.table_id = ?""", (table_id, )).fetchall()
for attr_tuple in indices_attributes:
index = Dbc.Index()
if attr_tuple[3] == "fulltext":
index.fulltext, index.uniqueness = True, False
elif attr_tuple[3] == "uniqueness":
index.fulltext, index.uniqueness = False, True
else:
index.fulltext, index.uniqueness = False, False
index.name, index.local = attr_tuple[1:-1]
index.field = cursor.execute(
"""\
select name from dbd$fields \
where dbd$fields.id = (\
select field_id from dbd$index_details\
where dbd$index_details.index_id = ?)""",
(attr_tuple[0], )).fetchone()[0]
indices_list.append(index)
return indices_list
def fetch_indices(self, schema_name, table_name):
cursor = self.connection.cursor()
indices_list = list()
indices = cursor.execute("""
select
ind.name as index_name,
ind.is_unique,
fti.object_id as is_fulltext,
c.name as field_name
from sys.indexes as ind
left join sys.fulltext_indexes as fti
on ind.object_id = fti.object_id
join sys.index_columns as ic
on ind.object_id = ic.object_id and ind.index_id = ic.index_id
join sys.columns as c
on ind.object_id = c.object_id and ic.column_id = c.column_id
where ind.object_id = OBJECT_ID(?);""",
("{schema_name}.{table_name}".format(schema_name=schema_name,
table_name=table_name),)).fetchall()
for ind in indices:
index = Dbc.Index()
index.name = ind[0]
index.uniqueness = ind[1]
index.fulltext = bool(ind[2])
index.field = ind[3]
indices_list.append(index)
return indices_list
def fetch_tables(self, schema):
cursor = self.connection.cursor()
tables_list = list()
tables = cursor.execute("""
select
t.name,
OBJECTPROPERTY(t.object_id, 'HasInsertTrigger') as addition,
OBJECTPROPERTY(t.object_id, 'HasUpdateTrigger') as edition,
t.temporal_type
from sys.tables as t
join sys.schemas as s
on t.schema_id = s.schema_id
where s.name = ?;""", (schema.name,)).fetchall()
for tbl in tables:
table = Dbc.Table()
table.name = tbl[0]
table.add = bool(tbl[1])
table.edit = bool(tbl[2])
table.temporal_mode = bool(tbl[3])
table.indices = self.fetch_indices(schema.name, table.name)
table.constraints = self.fetch_constraints(schema.name, table.name)
table.fields = self.fetch_fields(table.name, schema)
tables_list.append(table)
return tables_list
def get_indices(table):
"""
:param table: Dbc.Table() to find indices in.
:return: indices_list: list of indices in table.
Parse table to find indices in.
"""
if table.nodeName != "table":
raise WrongNodeException("table", table.nodeName)
indices_list = list()
for index in table.getElementsByTagName("index"):
idx = Dbc.Index()
for an, av in index.attributes.items():
if an.lower() == "name":
idx.name = av
elif an.lower() == "field":
idx.field = av
elif an.lower() == "props":
for prop in av.split(", "):
if prop == "fulltext":
idx.fulltext = True
elif prop == "uniqueness":
idx.uniqueness = True
elif prop == "local":
idx.local = True
else:
raise WrongPropertyException(["fulltext", "uniqueness", "local"], prop)
else:
raise WrongAttributeException(["field", "name", "props"], an)
indices_list.append(idx)
return indices_list
def fetch_schema(self):
schema = Dbc.Schema()
schema.name = "dbo"
schema.tables = self.fetch_tables(schema)
# rsd = list()
# for domain in schema.domains:
# if all([domain != dom for dom in rsd]):
# rsd.append(domain)
# schema.domains = rsd
return schema
def get_domains(self):
"""
:return: domain_list: list of domains.
Parse an xml file to find domains in.
"""
domain_list = list()
for domain in self.xml_repr.getElementsByTagName("domain"):
dom = Dbc.Domain()
for an, av in domain.attributes.items():
if an.lower() == "name":
dom.name = av
elif an.lower() == "description":
dom.description = av
elif an.lower() == "type":
dom.type = av
elif an.lower() == "align":
dom.align = av
elif an.lower() == "width":
dom.width = av
elif an.lower() == "props":
for prop in av.split(", "):
if prop == "show_null":
dom.show_null = True
elif prop == "summable":
dom.summable = True
elif prop == "case_sensitive":
dom.case_sensitive = True
elif prop == "show_lead_nulls":
dom.show_lead_nulls = True
elif prop == "thousand_separator":
dom.thousands_separator = True
else:
raise WrongPropertyException(
["show_null", "summable", "case_sensitive", "show_lead_nulls", "thousands_separator"],
prop)
elif an.lower() == "char_length":
dom.char_length = av
elif an.lower() == "length":
dom.length = av
elif an.lower() == "precision":
dom.precision = av
elif an.lower() == "scale":
dom.scale = av
else:
raise WrongAttributeException(
["props", "scale", "length", "char_length", "precision", "width", "align",
"type", "description", "name"], an)
domain_list.append(dom)
return domain_list
def get_fields(table):
"""
:param table: Dbc.Table() to find fields in.
:return: fields_list: list of fields in table.
Parse table to find fields.
"""
if table.nodeName != "table":
raise WrongNodeException("table", table.nodeName)
fields_list = list()
pos = 1
for field in table.getElementsByTagName("field"):
fld = Dbc.Field()
fld.position = str(pos)
for an, av in field.attributes.items():
if an.lower() == "name":
fld.name = av
elif an.lower() == "rname":
fld.rname = av
elif an.lower() == "domain":
fld.domain = av
elif an.lower() == "description":
fld.description = av
elif an.lower() == "props":
for prop in av.split(", "):
if prop == "input":
fld.input = True
elif prop == "edit":
fld.edit = True
elif prop == "show_in_grid":
fld.show_in_grid = True
elif prop == "show_in_details":
fld.show_in_details = True
elif prop == "autocalculated":
fld.autocalculated = True
elif prop == "is_mean":
fld.is_mean = True
elif prop == "required":
fld.required = True
else:
raise WrongPropertyException(["input", "edit", "show_in_grid",
"show_in_details", "is_mean", "autocalculated", "required"],
prop)
else:
raise WrongAttributeException(["name", "rname", "position", "domain", "description", "props"], an)
pos += 1
fields_list.append(fld)
return fields_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":
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 get_tables(self):
"""
:return: tables_list: list of tables in xml file.
Parse a xml file to find tables.
"""
tables_list = list()
for table in self.xml_repr.getElementsByTagName("table"):
tbl = Dbc.Table()
for an, av in table.attributes.items():
if an.lower() == "name":
tbl.name = av
elif an.lower() == "description":
tbl.description = av
elif an.lower() == "props":
for prop in av.split(", "):
if prop == "add":
tbl.add = True
elif prop == "edit":
tbl.edit = True
elif prop == "delete":
tbl.delete = True
elif prop == "temporal_mode":
tbl.temporal_mode = True
else:
raise WrongPropertyException(["delete", "temporal_mode", "edit", "add"], prop)
elif an.lower() == "ht_table_flags":
pass
elif an.lower() == "access_level":
pass
elif an.lower() == "means":
tbl.means = av
else:
raise WrongAttributeException(["props", "access_level", "ht_table_flags", "description", "name"],
an)
tbl.fields = self.get_fields(table)
tbl.indices = self.get_indices(table)
tbl.constraints = self.get_constraints(table)
tables_list.append(tbl)
return tables_list
def get_schema(self):
"""
:return: schema: Dbc.Schema() of data base.
Parse a xml file to obtain schema.
"""
schema = Dbc.Schema()
for an, av in self.xml_repr.documentElement.attributes.items():
if an.lower() == "fulltext_engine":
pass
elif an.lower() == "version":
pass
elif an.lower() == "name":
schema.name = av
elif an.lower() == "description":
pass
else:
raise WrongAttributeException(["fulltext_engine", "version", "name", "description"], an)
schema.domains = self.domain_list
schema.tables = self.tables_list
return schema
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