def alterTableAddIndex(database: str, table: str, indexName: str,
columns: list) -> int:
if database not in uni.showDatabases():
return 2
if table not in uni.showTables(database):
return 3
if (len(columns) < 1):
return 4
for col in columns:
if len(uni.extractRangeTable(database, table, col, 0, 1)) == None:
return 1
if uni.createTable(database, table + '_INDEX', 2) != 0:
return 1
if uni.alterAddPK(database, table + '_INDEX', [0]) != 0:
return 1
if uni.insert(database, table + '_INDEX', [indexName, columns]) != 0:
return 1
return 0
def alterDatabaseDecompress(self, database: str) -> int:
if database in u.showDatabases():
try:
lista_tables=u.showTables(database)
for t in lista_tables:
self.alterTableDecompress(database,t)
return 0
except:
return 1
else:
return 2
def alterTableDropFK(database: str, table: str, indexName: str) -> int:
if database not in uni.showDatabases():
return 2
if table not in uni.showTables(database):
return 3
if len(uni.extractRow(database, table, [indexName])) == 0:
return 4
if uni.dropTable(database, table + '_FK') != 0:
return 1
return 0
def alterDatabaseCompress(self, database: str, level: int) -> int:
if database in u.showDatabases():
if level in compression_levels:
try:
lista_tables=u.showTables(database)
for t in lista_tables:
self.alterTableCompress(database,t,level)
return 0
except:
return 1
else:
return 3
else:
return 2
def alterTableAddUnique(database: str, table: str, indexName: str,
columns: list) -> int:
if database not in uni.showDatabases():
return 2
if table not in uni.showTables(database):
return 3
if (len(columns) < 1):
return 4
for col in columns:
if len(uni.extractRangeTable(database, table, col, 0, 1)) == None:
return 1
if len(uni.extractTable(database, table)) == 0:
if uni.createTable(database, table + '_UNIQUE', 2) != 0:
return 1
if uni.alterAddPK(database, table + '_UNIQUE', [0]) != 0:
return 1
if uni.insert(database, table + '_UNIQUE', [indexName, columns]) != 0:
return 1
else:
tuplas = uni.extractTable(database, table)
indiceCol = 0
encontrados = []
while indiceCol < len(columns):
for values in tuplas:
if values[columns[indiceCol]] not in encontrados:
encontrados.append(values[columns[indiceCol]])
else:
return 5
indiceCol += 1
if uni.createTable(database, table + '_UNIQUE', 2) != 0:
return 1
if uni.alterAddPK(database, table + '_UNIQUE', [0]) != 0:
return 1
if uni.insert(database, table + '_UNIQUE', [indexName, columns]) != 0:
return 1
return 0
def alterTableCompress(self, database: str, table: str, level: int) -> int:
#modo=u.getModoBaseDatos(database)
if database in u.showDatabases():
if table in u.showTables(database):
if level in compression_levels:
try:
extract=u.extractTable(database,table)
if len(extract)>0:
#print('Lista mayor a 0 elementos')
#recorrido
for i in range(0,len(extract)):
fila=extract[i]
for j in range(0,len(fila)):
tupla=fila[j]
if type(tupla)==str:
tupla=zlib.compress(bytes(tupla.encode()),level)
elif type(tupla)==bytes:
#tupla=zlib.compress(tupla,level)
tupla=tupla
fila[j]=tupla
extract[i]=fila
u.truncate(database,table)
#print('compressed:',u.alterAddColumn(database,table,'Compressed'))
for element in extract:
#print('ingresa element: '+str(element))
u.insert(database,table,element)
#insert_true=False
return 0
except:
return 1
else:
return 4
else:
return 3
else:
return 2
def alterTableDecompress(self, database: str, table: str) -> int:
codificacion=u.getCodificacionDatabase(database)
if database in u.showDatabases():
if table in u.showTables(database):
try:
extract=u.extractTable(database,table)
if len(extract)>0:
#print('Lista mayor a 0 elementos')
#recorrido
#last_col=-1
for i in range(0,len(extract)):
fila=extract[i]
for j in range(0,len(fila)):
try:
tupla=fila[j]
tupla=zlib.decompress(tupla)
fila[j]=tupla.decode(codificacion)
except:
return 4
extract[i]=fila
#last_col=len(fila)
#print('lastcol: ',last_col)
u.truncate(database,table)
for element in extract:
u.insert(database,table,element)
return 0
except:
return 1
else:
return 3
else:
return 2
def alterTableAddFK(database: str, table: str, indexName: str, columns: list,
tableRef: str, columnsRef: list) -> int:
if database not in uni.showDatabases():
return 2
if table not in uni.showTables(database) or tableRef not in uni.showTables(
database):
return 3
if (len(columns) < 1) or (len(columnsRef) < 1) or (len(columns) !=
len(columnsRef)):
return 4
for col in columns:
if len(uni.extractRangeTable(database, table, col, 0, 1)) == None:
return 1
for refCol in columnsRef:
if len(uni.extractRangeTable(database, table, refCol, 0, 1)) == None:
return 1
if len(uni.extractTable(database, table)) == 0 and len(
uni.extractTable(database, tableRef)) == 0:
if uni.createTable(database, table + '_FK', 3) != 0:
return 1
if uni.alterAddPK(database, table + '_FK', [0]) != 0:
return 1
if uni.insert(database, table + '_FK',
[indexName, tableRef, columnsRef]) != 0:
return 1
elif len(uni.extractTable(database, table)) > 0 and len(
uni.extractTable(database, table)) == 0:
return 5
else:
tableRefValues = uni.extractTable(database, tableRef)
tableValues = uni.extractTable(database, table)
notFoundValues = []
indiceCol = 0
while indiceCol < len(columns):
for values in tableValues:
if values[columns[indiceCol]] not in notFoundValues:
notFoundValues.append(values[columns[indiceCol]])
indiceCol += 1
indiceCol = 0
while indiceCol < len(columnsRef):
for refValues in tableRefValues:
if refValues[columnsRef[indiceCol]] in notFoundValues:
notFoundValues.remove(refValues[columnsRef[indiceCol]])
indiceCol += 1
if len(notFoundValues) == 0:
if uni.createTable(database, table + '_FK', 3) != 0:
return 1
if uni.alterAddPK(database, table + '_FK', [0]) != 0:
return 1
if uni.insert(database, table + '_FK',
[indexName, tableRef, columnsRef]) != 0:
return 1
else:
return 5
return 0