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_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 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 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":
# schema.fulltext_engine = av
# elif an.lower() == "version":
# schema.version = av
# elif an.lower() == "name":
# schema.name = av
# elif an.lower() == "description":
# schema.description = av
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_domains(self):
cursor = self.connection.cursor()
domain_list = list()
domain_attributes = cursor.execute("""\
select name, description, data_type_id, align, width, length, precision, show_null, summable, case_sensitive, \
show_lead_nulls, thousands_separator, char_length, scale from dbd$domains"""
).fetchall()
for attr_tuple in domain_attributes:
domain = Dbc.Domain()
domain.name, domain.description, domain.type, domain.align, domain.width, domain.length, \
domain.precision, domain.show_null, domain.summable, domain.case_sensitive, domain.show_lead_nulls, \
domain.thousands_separator, domain.char_length, domain.scale = attr_tuple
domain.show_null, domain.show_lead_nulls, domain.thousands_separator, domain.summable, \
domain.case_sensitive = map(bool, [domain.show_null, domain.show_lead_nulls, domain.thousands_separator,
domain.summable, domain.case_sensitive])
# domain.char_length, domain.length, domain.scale, domain.precision, domain.width = \
# map(str, [domain.char_length, domain.length, domain.scale, domain.precision, domain.width])
domain.char_length = str(
domain.char_length) if domain.char_length else None
domain.length = str(domain.length) if domain.length else None
domain.scale = str(domain.scale) if domain.scale else None
domain.precision = str(
domain.precision) if domain.precision else None
domain.width = str(domain.width) if domain.width else None
domain.type = cursor.execute(
"""select type_id from dbd$data_types where dbd$data_types.id = ?""",
(domain.type, )).fetchone()[0]
domain_list.append(domain)
return domain_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_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_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_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 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_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 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":
# tbl.ht_table_flags = av
# elif an.lower() == "access_level":
# tbl.access_level = av
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 fetch_schema(self):
schema = Dbc.Schema()
cursor = self.connection.cursor()
# sid, name = cursor.execute("""select id, name from dbd$schemas where name = ?""",
# (self.schema_name,)).fetchone()
# if name is None:
# raise SchemaNameError("Can't find schema with {} name".format(self.schema_name))
# else:
# schema.name = name
schema.domains = self.fetch_domains()
schema.tables = self.fetch_tables()
self.connection.commit()
self.connection.close()
return schema
def fetch_tables(self):
cursor = self.connection.cursor()
tables_list = list()
tables_attributes = cursor.execute("""\
select id, name, description, can_add, can_edit, can_delete, temporal_mode, means from dbd$tables"""
).fetchall()
for attr_tuple in tables_attributes:
table = Dbc.Table()
tid, table.name, table.description, table.add, table.edit, table.delete, table.temporal_mode, \
table.means = attr_tuple
table.add, table.edit, table.delete = map(
bool, [table.add, table.edit, table.delete])
table.fields = self.fetch_fields(tid)
table.constraints = self.fetch_constraints(tid)
table.indices = self.fetch_indices(tid)
tables_list.append(table)
return tables_list
def fetch_fields(self, table_id):
cursor = self.connection.cursor()
field_list = list()
filed_attributes = cursor.execute(
"""\
select name, russian_short_name, description, domain_id, can_input, can_edit, \
show_in_grid, show_in_details, is_mean, autocalculated, required from dbd$fields\
where dbd$fields.table_id = ?""", (table_id, )).fetchall()
for attr_tuple in filed_attributes:
field = Dbc.Field()
field.name, field.rname, field.description, field.domain, field.input, field.edit, \
field.show_in_grid, field.show_in_details, field.is_mean, field.autocalculated, \
field.required = attr_tuple
field.input, field.edit, field.show_in_grid, field.show_in_details, field.is_mean, field.autocalculated, \
field.required = map(bool, [field.input, field.edit, field.show_in_grid, field.show_in_details,
field.is_mean, field.autocalculated, field.required])
field.domain = cursor.execute(
"""select name from dbd$domains where dbd$domains.id = ?""",
(field.domain, )).fetchone()[0]
field_list.append(field)
return field_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_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