def set_schema(self):
schema_name = self.data['Schema']
self.schema = Schema(schema_name)
tables = self.data['Tables']
for t in tables:
table = self.set_table(t)
self.schema.add_table(table)
def click(button):
"""
Simple button click, only with GET
:param button: the domId of the button that was clicked
:return:
"""
print(button)
try:
if button == 'downloadSQL':
decomposition_type = request.args['type']
table_name = request.args['table']
new_schema = {}
if decomposition_type == '3NF' or decomposition_type == '3nf':
new_schema = normalized_schemas[table_name].get_nf3_schema()
elif decomposition_type == 'BCNF' or decomposition_type == 'bcnf':
new_schema = normalized_schemas[table_name].get_bcnf_schema()
old_schema = Schema(schema.name)
table_obj = schema.get_table_by_name(table_name)
old_schema.add_table(table_obj)
new_schema.name = old_schema.name
gen = DBGenerateScript(new_schema, old_schema, db_c['username'], db_c['pwd'], db_c['url'], db_c['db'])
file = gen.generate_script()
response = make_response(file)
response.headers["Content-Disposition"] = "attachment; filename="+table_name+"_decomposition.sql"
return response
elif button == 'XMLDownload':
xml_export = XMLExport(schema)
xml_file = xml_export.generate_xml()
response = make_response(xml_file)
response.headers["Content-Disposition"] = "attachment; filename="+schema.get_name+".xml"
return response
return "test"
except Exception as e:
print(e)
def map_tables(self):
mapped = Schema(self.schema)
mapped.import_from_db = True
for table_name in self.inspector.get_table_names(schema=self.schema):
new_table = Table(table_name)
columns = self.inspector.get_columns(table_name, self.schema)
primary_key = self.inspector.get_pk_constraint(table_name, self.schema)
foreign_import = self.inspector.get_foreign_keys(table_name, self.schema)
attributes = []
for c in columns:
attr_new = Attribute(c['name'])
attr_new.set_type(c['type'])
attributes.append(attr_new)
if c['name'] in primary_key['constrained_columns']:
new_table.pk.append(c['name'])
elif c['name'] in foreign_import:
new_table.imported_fk.append(c['name'])
new_table.set_attributes(attributes)
if primary_key['constrained_columns']:
fds = self.construct_fd_from_pk(primary_key['constrained_columns'], attributes)
new_table.fds = fds
for fk in foreign_import:
new_table.imported_fk.append(FKey(fk['constrained_columns'], fk['referred_table'], fk['referred_columns']))
self.connection = self.engine.connect()
result = self.connection.execute("select count(*) as count from " + self.schema + "." + new_table.name)
for row in result:
new_table.db_row_count = row['count']
if self.check_fds_from_data:
fds_detect = FDDetection(self.engine, self.schema, new_table)
fds_detect.setup_table()
fds_data = fds_detect.find_fds()
for fd in fds_data:
new_table.fds.append(fd)
mapped.add_table(new_table)
return mapped
class ManualImport:
def __init__(self, data):
self.data = data
self.schema = {}
self.set_schema()
return
def get_schema(self):
return self.schema
def set_schema(self):
schema_name = self.data['Schema']
self.schema = Schema(schema_name)
tables = self.data['Tables']
for t in tables:
table = self.set_table(t)
self.schema.add_table(table)
@staticmethod
def set_table(table):
name = table['name']
t = Table(name)
fds = table['fds']
attributes = table['attributes']
attributes_list = []
fds_list = []
for attr in attributes:
attrs = attr.replace(')', '')
attrs = attrs.replace('(', '')
attrs = attrs.split(',')
for at in attrs:
attribute = Attribute(at)
attributes_list.append(attribute)
for fd in fds:
lhs_list = []
rhs_list = []
for a in attributes_list:
attr_name = a.get_name
for lhs in fd['lhs']:
if lhs == attr_name:
lhs_list.append(a)
for rhs in fd['rhs']:
if rhs == attr_name:
rhs_list.append(a)
fd_elem = FD(lhs_list, rhs_list)
fds_list.append(fd_elem)
t.set_attributes(attributes_list)
t.set_fds(fds_list)
return t
#test_data = {'Schema': 'Test', 'Tables': [{'fds': [{'lhs': ['attr1'], 'rhs': ['attr2']}, {'lhs': ['attr1'], 'rhs': ['attr3']}, {'lhs': ['attr3'], 'rhs': ['attr2']}], 'name': 'Test1', 'attributes': ['attr1', '(attr2, attr4)', 'attr3']}]}
#mi = ManualImport(test_data)
#schema = mi.get_schema()
#tables = schema.get_tables()
#for t in tables:
#print([a.get_name for a in t.get_attributes])
def init_schema(self):
if self.test:
root = self.fileStructure.getroot()
else:
root = self.fileStructure
for s in root.findall('schema'):
self.schema = Schema(s.get('name'))
self.fileStructure = s
def map_tables(self):
mapped = Schema(self.schema)
mapped.import_from_db = True
for table_name in self.inspector.get_table_names(schema=self.schema):
new_table = Table(table_name)
columns = self.inspector.get_columns(table_name, self.schema)
primary_key = self.inspector.get_pk_constraint(
table_name, self.schema)
foreign_import = self.inspector.get_foreign_keys(
table_name, self.schema)
attributes = []
for c in columns:
attr_new = Attribute(c['name'])
attr_new.set_type(c['type'])
attributes.append(attr_new)
if c['name'] in primary_key['constrained_columns']:
new_table.pk.append(c['name'])
elif c['name'] in foreign_import:
new_table.imported_fk.append(c['name'])
new_table.set_attributes(attributes)
if primary_key['constrained_columns']:
fds = self.construct_fd_from_pk(
primary_key['constrained_columns'], attributes)
new_table.fds = fds
for fk in foreign_import:
new_table.imported_fk.append(
FKey(fk['constrained_columns'], fk['referred_table'],
fk['referred_columns']))
self.connection = self.engine.connect()
result = self.connection.execute("select count(*) as count from " +
self.schema + "." +
new_table.name)
for row in result:
new_table.db_row_count = row['count']
if self.check_fds_from_data:
fds_detect = FDDetection(self.engine, self.schema, new_table)
fds_detect.setup_table()
fds_data = fds_detect.find_fds()
for fd in fds_data:
new_table.fds.append(fd)
mapped.add_table(new_table)
return mapped
def get_nf3_schema(self):
new_schema = Schema('new_' + self.nf.table.get_name)
for t in self.get_new_tables_nf3():
new_schema.add_table(t)
return new_schema
def get_bcnf_schema(self):
new_schema = Schema('new_' + self.nf.table.get_name + '_bcnf')
for t in self.get_new_tables_bcnf():
new_schema.add_table(t)
return new_schema
class XMLImport:
def __init__(self, xml_data, test=False):
self.test = test
self.xml_data = xml_data
self.fileStructure = ''
self.schema = {}
self.tables = []
self.attributes = {}
self.intermediateTables = {}
def readfile(self):
if self.test:
self.fileStructure = elementTree.parse(self.xml_data)
else:
self.fileStructure = elementTree.fromstring(self.xml_data)
def init_objects(self):
self.readfile()
self.init_schema()
self.init_tables()
def get_schema(self):
return self.schema
def init_schema(self):
if self.test:
root = self.fileStructure.getroot()
else:
root = self.fileStructure
for s in root.findall('schema'):
self.schema = Schema(s.get('name'))
self.fileStructure = s
def init_tables(self):
s = self.fileStructure
t = {}
try:
for table in s.findall('.//table'):
table_name = table.get('name')
t['attributes'] = self.init_attributes(table, table_name)
self.attributes[table_name] = []
for attr in t['attributes']:
self.attributes[table_name].append(attr.get_name)
t['fds'] = self.init_fds(table, table_name)
tb = Table(table_name)
tb.set_attributes(t['attributes'])
tb.set_fds(t['fds'])
self.tables.append(tb)
self.schema.add_table(tb)
except Exception as e:
print('init_tables')
print(e)
def init_attributes(self, table, table_name):
attr = set()
try:
for attributes in table.findall('.//attributes'):
for a in attributes.findall('.//attribute'):
attrs = a.text.replace(')', '')
attrs = attrs.replace('(', '')
attrs = attrs.split(',')
for at in attrs:
at = Attribute(at)
attr.add(at)
return attr
except Exception as e:
print('init_attributes ' + str(e))
def check_fds_attributes(self, table, table_name):
attr = []
try:
for attribute in table.findall('.//attribute'):
a = Attribute(attribute.text)
if attribute.text in self.attributes[table_name]:
attr.append(a)
else:
print('All the fds MUST be composed of table attributes!')
return attr
except Exception as e:
print('init_attributes' + str(e))
def handle_intermediate_tables_fds(self, lhs, rhs, table_name):
lhs_list = []
rhs_list = []
add_to_main_table = True
for attribute in lhs.findall('.//attribute'):
a = attribute.text
lhs_list.append(a)
if a not in self.attributes[table_name]:
add_to_main_table = False
for attribute in rhs.findall('.//attribute'):
a = attribute.text
rhs_list.append(a)
if a not in self.attributes[table_name]:
add_to_main_table = False
if not add_to_main_table:
attrs = lhs_list + rhs_list
for inttable in self.intermediateTables[table_name]:
common_elem = list(set(inttable) & set(attrs))
print(common_elem)
if len(common_elem) > 0:
print('table: ' + str(inttable) + ' attrs: ' + str(attrs))
#print(attrs)
#print(str(lhs_list)+'->'+str(rhs_list))
return add_to_main_table
def init_fds(self, table, table_name):
fds = []
try:
for fd in table.findall('.//fd'):
fds.append(
FD(self.check_fds_attributes(fd.find('lhs'), table_name),
self.check_fds_attributes(fd.find('rhs'), table_name)))
if len(fds) == 0:
print(
'No functional dependency found! please add the functional dependencies!'
)
return fds
except Exception as e:
print('get function dependencies exception: ' + str(e))
def treat_attr(self, attr, table_name):
t = attr.split(',')
if len(t) == 1:
return attr
self.getIntermediateTables(t, table_name)
return False
def getIntermediateTables(self, t, table_name):
tables = []
attr = []
added_elem = True
for a in t:
if '(' in a:
a = a.replace('(', '')
if not added_elem:
tables.append(attr)
added_elem = False
attr = [a]
elif ')' in a:
a = a.replace(')', '')
attr.append(a)
tables.append(attr)
attr = []
added_elem = True
else:
added_elem = False
attr.append(a)
self.intermediateTables[table_name] = []
i = 0
for t in tables:
tb = Table(table_name + str(i))
tb_at = []
self.intermediateTables[table_name].append(t)
for a in t:
at = Attribute(a)
tb_at.append(at)
tb.set_attributes(tb_at)
i += 1
self.tables.append(tb)
#test = XMLImport('/Users/mairamachadoladeira/PycharmProjects/adb_project/examples/presentation_example2.xml', True)
#test.init_objects()
#schema = test.get_schema()
#tables = schema.get_tables()
#for t in tables:
#print(t.get_attributes)