def graphAVL(database: str, table: str):
try:
Handler.init_DirReports()
avl = Handler.tableinstance(database, table)
grafo = TreeGraph(avl)
grafo.export()
return 'tmp/grafo-avl.png'
except:
return None
def graphTuple(database: str, table: str, index):
try:
Handler.init_DirReports()
avl = Handler.tableinstance(database, table)
tupla = avl.search(index)
fname = 'DataAccessLayer/imaging/tupla.png'
tuples = open("DataAccessLayer/imaging/tupla.dot", "w")
temporal = 'digraph tables{\ngraph[bgcolor="#0f1319"] \nnode[style="filled",color="black", ' \
'fillcolor="#45c2c5",shape="box",fontcolor="black",fontname="Century Gothic", fontsize=18];\n'
c = 0
for registro in tupla:
temporal += "node" + str(c) + "[label=\"" + str(registro).replace(' ', '') + "\"];\n"
c += 1
temporal += "}"
tuples.write(temporal)
tuples.close()
os.system("dot -Tpng DataAccessLayer/imaging/tupla.dot -o " + fname)
os.remove('DataAccessLayer/imaging/tupla.dot')
return fname
except:
return None
def getIndexes(database: str, table: str):
avl_temp = Handler.tableinstance(database, table)
return avl_temp.indexes()
class DatabaseModule:
def __init__(self):
self.handler = Handler()
self.databases = self.handler.rootinstance()
def createDatabase(self, database: str) -> int:
try:
if not isinstance(database, str) or self.handler.invalid(database):
raise
self.databases = self.handler.rootinstance()
for i in self.databases:
if database.upper() == i.name.upper():
return 2
self.databases.append(Database(database))
self.handler.rootupdate(self.databases)
return 0
except:
return 1
def showDatabases(self) -> list:
temporal = []
self.databases = self.handler.rootinstance()
for i in self.databases:
temporal.append(i.name)
return temporal
def alterDatabase(self, databaseOld: str, databaseNew: str) -> int:
try:
if not isinstance(databaseOld, str) or not isinstance(databaseNew, str) or self.handler.invalid(
databaseNew):
raise
index = -1
self.databases = self.handler.rootinstance()
for i in self.databases:
if databaseOld.upper() == i.name.upper():
index = self.databases.index(i)
tables_temp = self.handler.findCoincidences(databaseOld, i.tablesName)
break
if index != -1:
for i in self.databases:
if databaseNew.upper() == i.name.upper():
return 3
for j in tables_temp:
avl_temp = self.handler.tableinstance(databaseOld, j)
avl_temp.database = databaseNew
self.handler.rename(databaseOld + '_' + j + '.tbl',
databaseNew + '_' + j + '.tbl')
self.handler.tableupdate(avl_temp)
self.databases[index].name = databaseNew
self.handler.rootupdate(self.databases)
return 0
return 2
except:
return 1
def dropDatabase(self, database: str) -> int:
try:
if not isinstance(database, str):
raise
index = -1
self.databases = self.handler.rootinstance()
for i in range(len(self.databases)):
if database.upper() == self.databases[i].name.upper():
index = i
tables_temp = self.handler.findCoincidences(database, self.databases[i].tablesName)
break
if index != -1:
for j in tables_temp:
self.handler.delete(database + '_' + j + '.tbl')
self.databases.pop(index)
self.handler.rootupdate(self.databases)
return 0
return 2
except:
return 1
def dropAll(self):
self.handler.reset()
class TupleModule:
def __init__(self):
self.handler = Handler()
self.dbs = None
def insert(self, database: str, table: str, register: list) -> int:
try:
if not isinstance(database, str) or not isinstance(
table, str) or not isinstance(register, list):
raise
filtro = False
self.dbs = self.handler.rootinstance()
for db in self.dbs:
if db.name.upper() == database.upper():
if self.handler.exists(database, table):
filtro = True
break
else:
return 3
if filtro:
avl = self.handler.tableinstance(database, table)
if len(register) != avl.numberColumns:
return 5
if not len(avl.pklist) == 0:
auxPk = ""
for key in avl.pklist:
auxPk += "-" + str(register[key])
auxPk = auxPk[1:]
if avl.search(auxPk):
return 4
else:
avl.add(auxPk, register)
else:
index = avl.hidden
while True:
if avl.search(str(index)):
index += 1
continue
avl.add(str(index), register)
break
avl.hidden = index
self.handler.tableupdate(avl)
return 0
else:
return 2
except:
return 1
def loadCSV(self, file: str, database: str, table: str) -> list:
try:
if not isinstance(database, str) or not isinstance(
table, str) or not file.endswith(".csv"):
raise
reader = self.handler.readcsv(file)
self.dbs = self.handler.rootinstance()
for db in self.dbs:
if db.name.upper() == database.upper():
if self.handler.exists(database, table):
result = []
avl = self.handler.tableinstance(database, table)
for fila in reader:
result.append(self.__insert(avl, fila))
self.handler.tableupdate(avl)
return result
else:
return []
return []
except:
return []
def extractRow(self, database: str, table: str, columns: list) -> list:
try:
if not isinstance(database, str) or not isinstance(
table, str) or not isinstance(columns, list):
raise
self.dbs = self.handler.rootinstance()
for db in self.dbs:
if db.name.upper() == database.upper():
if self.handler.exists(database, table):
avl = self.handler.tableinstance(database, table)
node = avl.search(self.__concat(columns))
if node:
return node
return []
else:
return []
return []
except:
return []
def update(self, database: str, table: str, register: dict,
columns: list) -> int:
try:
if not isinstance(database, str) or not isinstance(
table, str) or not isinstance(columns, list):
raise
self.dbs = self.handler.rootinstance()
existeDB = False
for db in self.dbs:
if db.name.upper() == database.upper():
existeDB = True
break
if self.handler.exists(database, table) and existeDB:
avl = self.handler.tableinstance(database, table)
auxStr = ""
for key in columns:
auxStr += "-" + str(key)
auxStr = auxStr[1:]
avltmp = avl.search(auxStr)
if avltmp:
if len(register) <= avl.numberColumns:
simple = True
for key in register:
if key in avl.pklist:
simple = False
content = avltmp
oldcontent = avltmp[:]
for key in register:
content[key] = register[key]
if simple:
avl.update(auxStr, content)
self.handler.tableupdate(avl)
else:
tmp = []
for key in avl.pklist:
tmp.append(oldcontent[key])
self.delete(database, table, tmp)
self.insert(database, table, content)
return 0
else:
return 1
else:
return 4
elif existeDB is False:
return 2
else:
return 3
except:
return 1
def delete(self, database: str, table: str, columns: list) -> int:
try:
if not isinstance(database, str) or not isinstance(
table, str) or not isinstance(columns, list):
raise
self.dbs = self.handler.rootinstance()
for db in self.dbs:
if db.name.upper() == database.upper():
if self.handler.exists(database, table):
avl = self.handler.tableinstance(database, table)
pk = self.__concat(columns)
if avl.search(pk):
avl.delete(pk)
self.handler.tableupdate(avl)
return 0
return 4
else:
return 3
return 2
except:
return 1
def truncate(self, database: str, table: str) -> int:
try:
if not isinstance(database, str) or not isinstance(table, str):
raise
self.dbs = self.handler.rootinstance()
for base in self.dbs:
if base.name.upper() == database.upper():
if self.handler.exists(database, table):
avl = self.handler.tableinstance(database, table)
newAvl = AVLTree(database, table, avl.numberColumns,
avl.pklist)
self.handler.tableupdate(newAvl)
return 0
else:
return 3
return 2
except:
return 1
@staticmethod
def __concat(keys) -> str:
res = ""
for pk in keys:
res += "-" + str(pk)
res = res[1:]
return res
@staticmethod
def __insert(avl, register: list) -> int:
try:
if not isinstance(register, list):
raise
if len(register) != avl.numberColumns:
return 5
if not len(avl.pklist) == 0:
auxPk = ""
for key in avl.pklist:
auxPk += "-" + str(register[key])
auxPk = auxPk[1:]
if avl.search(auxPk):
return 4
else:
avl.add(auxPk, register)
else:
index = avl.hidden
while True:
if avl.search(str(index)):
index += 1
continue
avl.add(str(index), register)
break
avl.hidden = index
return 0
except:
return 1
class TableModule:
def __init__(self):
self.handler = Handler()
self.dbs = None
def createTable(self, database: str, table: str,
numberColumns: int) -> int:
try:
if not isinstance(database, str) or not isinstance(table, str) or (
not isinstance(numberColumns, int) or numberColumns < 0):
raise
self.dbs = self.handler.rootinstance()
if not self.handler.exists(database, table):
for i in self.dbs:
if database.upper() == i.name.upper():
if self.handler.invalid(table):
raise
i.tablesName.append(table)
self.handler.rootupdate(self.dbs)
self.handler.tableupdate(
AVLTree(database, table, numberColumns))
return 0
return 2
return 3
except:
return 1
def showTables(self, database: str) -> list:
try:
if not isinstance(database, str):
raise
self.dbs = self.handler.rootinstance()
for i in self.dbs:
if database.upper() == i.name.upper():
return i.tablesName
return None
except:
return None
def extractTable(self, database: str, table: str) -> list:
try:
if not isinstance(database, str) or not isinstance(table, str):
raise
if self.handler.exists(database, table):
avl = self.handler.tableinstance(database, table)
return avl.tolist()
else:
return None
except:
return None
def extractRangeTable(self, database: str, table: str, columnNumber: int,
lower: any, upper: any) -> list:
try:
if not isinstance(database, str) or not isinstance(
table, str) or not isinstance(columnNumber, int):
raise
if self.handler.exists(database, table):
avl = self.handler.tableinstance(database, table)
return avl.range(columnNumber, lower, upper)
else:
return None
except:
return None
def alterAddPK(self, database: str, table: str, columns: list) -> int:
try:
if not isinstance(database, str) or not isinstance(table, str) or not isinstance(columns, list) or \
len(columns) == 0:
raise
self.dbs = self.handler.rootinstance()
for db in self.dbs:
if database.upper() == db.name.upper():
if self.handler.exists(database, table):
avl = self.handler.tableinstance(database, table)
if len(avl.pklist) == 0:
for i in columns:
if not isinstance(i, int):
raise
if i not in range(avl.numberColumns):
return 5
if avl.root is None:
avl.pklist = columns
else:
used = []
nodes = avl.tolist()
for tpl in nodes:
pk = ""
for col in columns:
pk += "-" + str(tpl[col])
pk = pk[1:]
if pk in used:
return 1
used.append(pk)
newavl = AVLTree(database, table,
avl.numberColumns, columns)
c = 0
for tpl in nodes:
newavl.add(used[c], tpl)
c += 1
avl = newavl
self.handler.tableupdate(avl)
return 0
else:
return 4
else:
return 3
return 2
except:
return 1
def alterDropPK(self, database: str, table: str) -> int:
try:
if not isinstance(database, str) or not isinstance(table, str):
raise
self.dbs = self.handler.rootinstance()
for i in self.dbs:
if database.upper() == i.name.upper():
if self.handler.exists(database, table):
avl = self.handler.tableinstance(database, table)
if len(avl.pklist) != 0:
avl.pklist = []
self.handler.tableupdate(avl)
return 0
else:
return 4
else:
return 3
return 2
except:
return 1
# region Phase 2
def alterAddFK(self, database: str, table: str,
references: dict) -> int: # para fase 2
pass
def alterAddIndex(self, database: str, table: str,
references: dict) -> int: # para fase 2
pass
# endregion
def alterTable(self, database: str, tableOld: str, tableNew: str) -> int:
try:
if not isinstance(database, str) or not isinstance(tableOld, str) or not isinstance(tableNew, str) or \
self.handler.invalid(tableNew):
raise
self.dbs = self.handler.rootinstance()
for db in self.dbs:
if database.upper() == db.name.upper():
if self.handler.exists(database, tableOld):
if not self.handler.exists(database, tableNew):
for j in range(len(db.tablesName)):
if db.tablesName[j].upper() == tableOld.upper(
):
db.tablesName[j] = tableNew
self.handler.rootupdate(self.dbs)
avl = self.handler.tableinstance(
database, tableOld)
avl.name = tableNew
self.handler.rename(
database + '_' + tableOld + '.tbl',
database + '_' + tableNew + '.tbl')
self.handler.tableupdate(avl)
return 0
else:
return 4
else:
return 3
return 2
except:
return 1
def alterAddColumn(self, database: str, table: str, default: any) -> int:
try:
if not isinstance(database, str) or not isinstance(table, str):
raise
self.dbs = self.handler.rootinstance()
for db in self.dbs:
if database.upper() == db.name.upper():
if self.handler.exists(database, table):
avl = self.handler.tableinstance(database, table)
avl.numberColumns += 1
avl.massiveupdate("add", default)
self.handler.tableupdate(avl)
return 0
else:
return 3
return 2
except:
return 1
def alterDropColumn(self, database: str, table: str,
columnNumber: int) -> int:
try:
if not isinstance(database, str) or not isinstance(
table, str) or not isinstance(columnNumber, int):
raise
self.dbs = self.handler.rootinstance()
for db in self.dbs:
if database.upper() == db.name.upper():
if self.handler.exists(database, table):
avl = self.handler.tableinstance(database, table)
if columnNumber not in range(avl.numberColumns):
return 5
elif avl.numberColumns == 1 or columnNumber in avl.pklist:
return 4
else:
avl.numberColumns -= 1
avl.massiveupdate("drop", int(columnNumber))
c = 0
for key in avl.pklist:
if key > columnNumber:
key -= 1
avl.pklist[c] = key
c += 1
self.handler.tableupdate(avl)
return 0
else:
return 3
return 2
except:
return 1
def dropTable(self, database: str, table: str) -> int:
try:
if not isinstance(database, str) or not isinstance(table, str):
raise
self.dbs = self.handler.rootinstance()
for i in self.dbs:
if database.upper() == i.name.upper():
if self.handler.exists(database, table):
i.tablesName.remove(table)
self.handler.rootupdate(self.dbs)
self.handler.delete(database + '_' + table + '.tbl')
return 0
else:
return 3
return 2
except:
return 1