def process(self, instruction): typeChecker = TypeChecker() nombreTabla = self._table_name.alias noCols = self.numberOfColumns(self._column_list) resTab = typeChecker.createTable(nombreTabla, noCols, 0, 0) # TODO add line and column # Si devuelve None es porque ya existe la tabla if resTab == None: return # Agrega las propiedades que agrupan a varias columnas self.generetaExtraProp() # Genera las tablas ya con todas sus propiedades self.generateColumns(nombreTabla, typeChecker) # Si tiene inherits la manoseamos if self._inherits_from != None: self.addInherits(nombreTabla, self._inherits_from) # Si ocurrio algun error en todo el proceso mato la tabla if self._can_create_flag == False: typeChecker.deleteTable(nombreTabla, 0, 0) return # Verifico si tiene llave primaria la tabla o si le meto una escondida # if self.existsPK(nombreTabla) == 0: # self.generateHiddenPK(nombreTabla) # Agrego llaves primarias a la base de datos si no hubo clavo con la tabla self.addPKToDB(nombreTabla)
def agregarCheck(self, conditionColumn, tablaId): typeChecker = TypeChecker() bandera = False tableToAlter = typeChecker.searchTable(SymbolTable().useDatabase, tablaId) # L (L|D)* whatColumnIs = re.search('[a-zA-z]([a-zA-z]|[0-9])*', conditionColumn.alias) if whatColumnIs != None: whatColumnIs = whatColumnIs.group(0) for columna in tableToAlter.columns: if (columna._name == whatColumnIs): bandera = True columna._check = conditionColumn.alias break if not bandera: desc = f": Undefined column in alter check ()" ErrorController().add(26, 'Execution', desc, 0, 0) return bandera = False else: desc = f": column not given in check()" ErrorController().add(26, 'Execution', desc, 0, 0) return typeChecker.writeFile()
def agregarNuevoTipo(self, tablaAMod, colAMod, nuevoTipo): typeChecker = TypeChecker() bandera = False tableToAlter = typeChecker.searchTable(SymbolTable().useDatabase, tablaAMod) tipoFinal = { '_tipoColumna': str(nuevoTipo._tipoColumna), '_paramOne': nuevoTipo._paramOne, '_paramTwo': nuevoTipo._paramTwo } for columna in tableToAlter.columns: if (columna._name == colAMod): bandera = True columna._dataType = tipoFinal break if not bandera: desc = f": Undefined column in alter type ()" ErrorController().add(26, 'Execution', desc, 0, 0) return bandera = False typeChecker.writeFile() pass
def process(self, instrucction): # Obteniendo tabla de la cual voy a borrar database_id = SymbolTable().useDatabase table_tp = TypeChecker().searchTable(database_id, self.table) table_cont = DataController().extractTable(self.table, self.line, self.column) headers = TypeChecker().searchColumnHeadings(table_tp) table_delete = pd.DataFrame(table_cont) if self.params == None: pk_col_name = TypeChecker().searchColPrimaryKey(table_tp) if pk_col_name == []: # NO HAY LLAVE PRIMARIA pk_list = table_delete.index.tolist() print(pk_list) for pk in pk_list: DataController().delete(self.table, pk, self.line, self.column) else: table_delete.columns = headers list_pks = [] for col in pk_col_name: list_pks.append(col.name) pk_list = table_delete[list_pks].values.tolist() print(pk_list) for pk in pk_list: DataController().delete(self.table, pk, self.line, self.column) else: for option in self.params: if isinstance(option, Where): table_delete.columns = headers storage_columns(table_cont, headers, self.line, self.column) storage_table(table_cont, headers, self.table, self.line, self.column) table_result = option.process( instrucction, table_delete, self.table) pk_col_name = TypeChecker().searchColPrimaryKey(table_tp) if pk_col_name == []: # NO HAY LLAVE PRIMARIA pk_list = table_result.index.to_list() print(pk_list) for pk in pk_list: DataController().delete(self.table, pk, self.line, self.column) else: table_result.columns = headers list_pks = [] for col in pk_col_name: list_pks.append(col.name) pk_list = table_result[list_pks].values.tolist() print(pk_list) for pk in pk_list: DataController().delete(self.table, pk, self.line, self.column) break return None
def process(self, instrucction): typeChecker = TypeChecker() database = typeChecker.searchDatabase(self._database_name) if self._if_exists and not database: return typeChecker.deleteDatabase(self._database_name, self._noLine, self._noColumn)
def __init__(self): self._typeChecker = TypeChecker() self._data = '' self.generateReport() report = open('typeChecker.dot', 'w') report.write(self._data) report.close() os.system('dot -Tpdf typeChecker.dot -o typeChecker.pdf')
def process(self, instruction): typeChecker = TypeChecker() nombreTabla = self._table_name.value noCols = self.numberOfColumns(self._column_list) typeChecker.createTable(nombreTabla, noCols, 0, 0) #TODO add line and column # Agrega las propiedades que agrupan a varias columnas self.generetaExtraProp() # Genera las tablas ya con todas sus propiedades self.generateColumns(nombreTabla, typeChecker)
def existsPK(self, tableCreated): indicesPrimarios = 0 typeChecker = TypeChecker() tablaToExtract = typeChecker.searchTable(SymbolTable().useDatabase, tableCreated) for colExt in tablaToExtract._colums: if colExt._primaryKey == True: indicesPrimarios += 1 return indicesPrimarios
def process(self, instrucction): typeChecker = TypeChecker() database = None # TODO Obtener desde la tabla de simbolos if not database: desc = f": Database not selected" ErrorController().addExecutionError(4, 'Execution', desc, self._noLine, self._noColumn) return typeChecker.deleteTable(database, self._table_name, self._noLine, self._noColumn)
def process(self, instrucction): typeChecker = TypeChecker() database = typeChecker.searchDatabase(self._dbActual) if not database: desc = f": Database {self._dbActual} does not exist" ErrorController().add(35, 'Execution', desc, self._noLine, self._noColumn) return SymbolTable().useDatabase = database DataWindow().consoleText('Query returned successfully: USE DATABASE')
def generateHiddenPK(self, nombreTabla): typeChecker = TypeChecker() tipoEscondido = { '_tipoColumna': 'HIDDEN', '_paramOne': None, '_paramTwo': None } columnaEscondida = Column('HIDDEN', tipoEscondido) columnaEscondida._primaryKey = True tableToInsert = typeChecker.searchTable(SymbolTable().useDatabase, nombreTabla) typeChecker.createColumnTable(tableToInsert, columnaEscondida, 0, 0) print('### SE HA GENERADO UNA LLAVE PRIMARIA ESCONDIDA')
def addPKToDB(self, tableCreated): indicesPrimarios = [] typeChecker = TypeChecker() tablaToExtract = typeChecker.searchTable(SymbolTable().useDatabase, tableCreated) for colExt in tablaToExtract._colums: if colExt._primaryKey == True: indicesPrimarios.append(colExt._number) if len(indicesPrimarios) == 0: pass else: DataController().alterAddPK(tableCreated, indicesPrimarios, 0, 0)
def extractTable(self, name: str, noLine, noColumn) -> list: """ Method to get a list of records from a table :param name: The name of table :param noLine: The instruction line :param noColumn: The instruction column :return: Returns a list of records """ database = SymbolTable().useDatabase if not database: desc = f": Database not selected" ErrorController().add(4, 'Execution', desc, noLine, noColumn) return None if not TypeChecker().searchTable(database, name): desc = f": Table {name} does not exist" ErrorController().add(27, 'Execution', desc, noLine, noColumn) return None data = data_mode.mode(database.mode).extractTable( database.name.lower(), name.lower()) if data == None: ErrorController().add(34, 'Execution', '', noLine, noColumn) return None return data
def extractRow(self, name: str, columns: list, noLine, noColumn) -> list: """ Method to extract a record from a table :param name: The name of table :param columns: List with number of columns of primary keys :param noLine: The instruction line :param noColumn: The instruction column :return: Returns nothing """ database = SymbolTable().useDatabase if not database: desc = f": Database not selected" ErrorController().add(4, 'Execution', desc, noLine, noColumn) return None if not TypeChecker().searchTable(database, name): desc = f": Table {name} does not exist" ErrorController().add(27, 'Execution', desc, noLine, noColumn) return None data = data_mode.mode(database.mode).extractRow( database.name.lower(), name.lower(), columns) if data == None: ErrorController().add(34, 'Execution', '', noLine, noColumn) return None return data
def process(self, instruction): #Jalando Base de Datos print("EJECUTANDO INSERT") database_id = SymbolTable().useDatabase if not database_id: desc = f": Database not selected" ErrorController().addExecutionError(4, 'Execution', desc, 0, 1)#manejar linea y columna return None #Base de datos existe --> Obtener tabla table_tp = TypeChecker().searchTable(database_id, self.table.value) if not table_tp: desc = f": Table does not exists" ErrorController().addExecutionError(4, 'Execution', desc, 0, 1)#manejar linea y columna return None # Obtenida la tabla ---> TODO: VALIDAR TIPOS # for column in table_tp.columns: # if column. if self.arr_columns == None: #Solo nos dieron los valores, tienen que venir todos ---> Espino ya valida longitud? vals_insert = [] for column in self.arr_values: val = column.process(instruction) vals_insert.append(val.value) print(vals_insert) DataController().insert(self.table.value, vals_insert,0,1) else: if len(self.arr_columns) == len(self.arr_values): pass else: print("Error Datos incompletos")
def addForeignKey(self, listaCols, nombreTabla, listaTablasCols): # the len of the cols must be de same if len(listaCols) != len(listaTablasCols): desc = f": cant of params in foreign() != " ErrorController().add(36, 'Execution', desc, 0, 0) self._can_create_flag = False return typeChecker = TypeChecker() existForeingTable = typeChecker.searchTable(SymbolTable().useDatabase, nombreTabla) # validate if the foreign table exists if existForeingTable == None: desc = f": Undefined table in foreign key ()" ErrorController().add(27, 'Execution', desc, 0, 0) self._can_create_flag = False return # validate if the columns exists in the foreign table for coli in listaTablasCols: if typeChecker.searchColumn(existForeingTable, coli) == None: desc = f": Undefined col in table in foreign key ()" ErrorController().add(26, 'Execution', desc, 0, 0) self._can_create_flag = False return bandera = False for x in range(0, len(listaCols)): for columna in self._column_list: if isinstance(columna, CreateCol): if (columna._column_name == listaCols[x]): if columna._properties != None: bandera = True columna._properties[0]['fk_references_to'] = { '_refTable': nombreTabla, '_refColumn': listaTablasCols[x] } break if not bandera: desc = f": Undefined column in foreign key ()" ErrorController().add(26, 'Execution', desc, 0, 0) self._can_create_flag = False return bandera = False
def agregarCol(self, columna, nombreTabla): typeChecker = TypeChecker() tipoFinal = { '_tipoColumna': str(columna._type_column._tipoColumna), '_paramOne': columna._type_column._paramOne, '_paramTwo': columna._type_column._paramTwo } columnaFinal = Column(columna._column_name, tipoFinal) tableToInsert = typeChecker.searchTable(SymbolTable().useDatabase, nombreTabla) validateCol = typeChecker.createColumnTable(tableToInsert, columnaFinal, 0, 0) # if return None an error ocurrio if validateCol == None: return
def eliminarColumna(self, nombreTabla, nombreColumna): typeChecker = TypeChecker() bandera = False tableToAlter = typeChecker.searchTable(SymbolTable().useDatabase, nombreTabla) for columna in tableToAlter.columns: if (columna._name == nombreColumna): bandera = True # typeChecker.deleteColumn(tableToAlter,columna,0,0) break if not bandera: desc = f": Undefined column in alter drop column ()" ErrorController().add(26, 'Execution', desc, 0, 0) return bandera = False pass
def agregarNotNull(self, tablaAMod, colAMod): typeChecker = TypeChecker() bandera = False tableToAlter = typeChecker.searchTable(SymbolTable().useDatabase, tablaAMod) for columna in tableToAlter.columns: if (columna._name == colAMod): bandera = True columna._notNull = True break if not bandera: desc = f": Undefined column in alter not null ()" ErrorController().add(26, 'Execution', desc, 0, 0) return bandera = False typeChecker.writeFile()
def process(self, instrucction): typeChecker = TypeChecker() bandera = False tableToAlter = typeChecker.searchTable(SymbolTable().useDatabase, instrucction) for columna in tableToAlter.columns: if (columna._name == self._oldName): bandera = True columna._name = self._newName break if not bandera: desc = f": Undefined column in rename column ()" ErrorController().add(26, 'Execution', desc, 0, 0) return bandera = False typeChecker.writeFile()
def searchTableIndex(self, tabla, linea, column): database_id = SymbolTable().useDatabase lista = [] if not database_id: desc = f": Database not selected" ErrorController().add(4, 'Execution', desc, linea, column) #manejar linea y columna return False #Base de datos existe --> Obtener tabla table_tp = TypeChecker().searchTable(database_id, tabla) if not table_tp: desc = f": Table does not exists" ErrorController().add(4, 'Execution', desc, linea, column) #manejar linea y columna return False table_cont = DataController().extractTable(tabla, linea, column) headers = TypeChecker().searchColumnHeadings(table_tp) return True
def process(self, instruction): # Obtenida la tabla ---> TODO: VALIDAR TIPOS # for column in table_tp.columns: # if column. if self.arr_columns == None: #Solo nos dieron los valores, tienen que venir todos ---> Espino ya valida longitud? vals_insert = [] for column in self.arr_values: val = column.process(instruction) vals_insert.append(val.value) # print(vals_insert) DataController().insert(self.table.value, vals_insert,0,1) # Enviar numero de fila y columna else: if len(self.arr_columns) == len(self.arr_values): columns_insert = [] vals_insert = [] for column in self.arr_columns: id_col = column.alias columns_insert.append(id_col) for column in self.arr_values: val = column.process(instruction) print("val") print(val) vals_insert.append(val.value) #Pidiendo tabla database_id = SymbolTable().useDatabase table_tp = TypeChecker().searchTable(database_id, self.table.id) table_cont = DataController().extractTable(self.table.id,0,0)#TODO: MANEJAR BIEN COLUMNA Y FILA headers = TypeChecker().searchColumnHeadings(table_tp) table_delete = pd.DataFrame(table_cont) table_delete.columns = headers #Ordenar ordered_vals = ordenarDatos(headers, columns_insert, vals_insert) #Insertar print(ordered_vals) # DataController().insert(self.table.value, ordered_vals,0,1) # Enviar numero de fila y columna else: print("Error Datos incompletos") return None
def validateValues(self, array_values:[]): database_id = SymbolTable().useDatabase table_tp = TypeChecker().searchTable(database_id, self.table.alias) headers = TypeChecker().searchColumnHeadings(table_tp) if len(headers) != len(array_values): desc = "Error Datos incompletos" ErrorController().add(28, 'Execution', desc, self.line, self.column) return False checker = CreateTB(None, None, None) dic = dict(zip(headers, array_values)) for index, name_col in enumerate(headers): column = TypeChecker().searchColumn(table_tp, name_col).__dict__ is_correct = checker.validateType(column['_dataType'], array_values[index], False) if not is_correct: desc = f'Valor no valido para la columna {name_col}' ErrorController().add(9, 'Execution', desc, self.line, self.column) return False if not realizeCheck(column, dic, self.line, self.column): return False return True
def addInherits(self, nameChildTable, nameParentTable): typeChecker = TypeChecker() tablaPadre = typeChecker.searchTable(SymbolTable().useDatabase, nameParentTable) tablaHija = typeChecker.searchTable(SymbolTable().useDatabase, nameChildTable) # La tabla de la que hereda no existe if tablaPadre == None: desc = f": parent table dont exists" ErrorController().add(27, 'Execution', desc, 0, 0) self._can_create_flag = False return for colPar in tablaPadre._colums: # Vamos a insertar en la hija validarCol = typeChecker.createColumnTable(tablaHija, colPar, 0, 0) # Si es una columna repetida entonces no puede crear la tabla if validarCol == None: self._can_create_flag = False return
def process(self, instrucction): #Obteniendo tabla de la cual voy a borrar database_id = SymbolTable().useDatabase table_tp = TypeChecker().searchTable(database_id, self.table) table_cont = DataController().extractTable(self.table,0,0) headers = TypeChecker().searchColumnHeadings(table_tp) table_delete = pd.DataFrame(table_cont) table_delete.columns = headers if self.params == None: #BORRAR TODOS LOS REGISTROS DE LA TABLA pk_col_name = TypeChecker().searchColPrimaryKey(table_tp).name pk_list = table_delete[[pk_col_name]].to_numpy() print(pk_list) for pk in pk_list: DataController().delete(self.table, pk, 0,0) else: for option in self.params: if isinstance(option, Where): table_delete.query(option.condition.alias) break return None
def alterAddPK(self, name: str, columns: list, noLine, noColumn): """ Method to define primary keys to a database :param name: The name of table :param columns: List with number of columns :param noLine: The instruction line :param noColumn: The instruction column :return: Returns nothing """ database = SymbolTable().useDatabase if not database: desc = f": Database not selected" ErrorController().add(4, 'Execution', desc, noLine, noColumn) return table = TypeChecker().searchTable(database, name) if not table: desc = f": Table {name} does not exist" ErrorController().add(27, 'Execution', desc, noLine, noColumn) return dbStatement = data_mode.mode(database.mode).alterAddPK( database.name.lower(), name.lower(), columns) if dbStatement == 0: for col in table.columns: if col.number in columns: col.primaryKey = True DataWindow().consoleText( 'Query returned successfully: Alter Table add PK') elif dbStatement == 1: ErrorController().add(34, 'Execution', '', noLine, noColumn) elif dbStatement == 2: desc = f": Database {database.name} does not exist" ErrorController().add(35, 'Execution', desc, noLine, noColumn) elif dbStatement == 3: desc = f": Table {name} does not exist" ErrorController().add(27, 'Execution', desc, noLine, noColumn) elif dbStatement == 4: desc = f": Multiple primary keys for table {database.name} are not allowed" ErrorController().add(36, 'Execution', desc, noLine, noColumn) elif dbStatement == 5: desc = f": Column of relation {name} does not exist" ErrorController().add(26, 'Execution', desc, noLine, noColumn)
def process(self, instruction): typeChecker = TypeChecker() print('') print('VAMOS A MODIFICAR LA SIGUIENTE TABLA') print(self._tablaAModificar) existTable = typeChecker.searchTable(SymbolTable().useDatabase, self._tablaAModificar) # validate if the table to alter exists if existTable == None: desc = f": Undefined table in alter table" ErrorController().add(27, 'Execution', desc, 0, 0) return print('y estos son los cambios') # Cambio puede ser un add (column,check,constraint_unique,foreign_key) # un alter # un drop # un rename for cambio in self._listaCambios: cambio.process(self._tablaAModificar)
def process(self, instruction): #Obteniendo tabla de la cual voy a hacer el update database_id = SymbolTable().useDatabase table_tp = TypeChecker().searchTable(database_id, self.table) table_cont = DataController().extractTable(self.table,0,0) headers = TypeChecker().searchColumnHeadings(table_tp) table_update = pd.DataFrame(table_cont) table_update.columns = headers tuplas = [] #t[0] = nombre columna, t[1] = valor a cambiar for column in self.arr_columns_vals: tuplas.append(column.process(instruction)) d = {} for t in tuplas: if not t[0] in headers: print("Columna no existe --- ERROR") break else: d[ headers.index(t[0]) ] = t[1].value print("DICTIONARY") print(d) return None if self.params == None: #CAMBIAR TODOS LOS REGISTROS DE LA TABLA pk_col_name = TypeChecker().searchColPrimaryKey(table_tp).name pk_list = table_update[[pk_col_name]].to_numpy() print(pk_list) for pk in pk_list: DataController().update(self.table.value, d, pk, 0 , 0) else: for option in self.params: if isinstance(option, Where): table_update.query(option.condition.alias) break
class TypeCheckerReport(object): def __init__(self): self._typeChecker = TypeChecker() self._data = '' self.generateReport() report = open('typeChecker.dot', 'w') report.write(self._data) report.close() os.system('dot -Tpng typeChecker.dot -o typeChecker.png') def generateReport(self): self._data = 'digraph {\n\ttbl [\n\tshape=plaintext\n\tlabel=<' self._data += '\n\t\t<table border=\'0\' cellborder=\'1\' color=\'#324960\' cellspacing=\'0\'>' self.generateDatabases() self._data += '\n\t\t</table>\n\t>];\n}' def generateDatabases(self): databases = self._typeChecker.getList() for db in databases: self._data += '\n\t\t\t<tr>\n\t\t\t\t<td bgcolor="#324960" colspan=\'2\'>' self._data += f"\n\t\t\t\t\t<font color=\"white\">DATABASE: {db.name}</font>" self._data += '\n\t\t\t\t</td>\n\t\t\t</tr>' self.generateTables(db) self._data += '\n\t\t\t<tr>\n\t\t\t\t<td colspan=\'2\' SIDES="T"> </td>\n\t\t\t</tr>' def generateTables(self, database): for tb in database.tables: self._data += '\n\t\t\t<tr>\n\t\t\t\t<td bgcolor="#4fc3a1" colspan=\'2\' SIDES="LR">' self._data += f"\n\t\t\t\t\t<font color=\"white\">TABLE: {tb.name}</font>" self._data += '\n\t\t\t\t</td>\n\t\t\t</tr>' self.generateColumns(tb) def generateColumns(self, table): for col in table.columns: self.dataCol('Name', col.name) self.dataCol('Data Type', col.dataType) self.dataCol('Length', col.length) self.dataCol('Not Null', col.notNull) #self.dataCol('Primary Key', col.primaryKey) self._data += f"\n\t\t\t<tr>\n\t\t\t\t<td SIDES=\"LB\" ALIGN=\"RIGHT\"><b>Primary Key: </b></td>" self._data += f"\n\t\t\t\t<td SIDES=\"RB\" ALIGN=\"LEFT\">{col.primaryKey}</td>\n\t\t\t</tr>" def dataCol(self, name, data): self._data += f"\n\t\t\t<tr>\n\t\t\t\t<td SIDES=\"L\" ALIGN=\"RIGHT\"><b>{name}:</b></td>" self._data += f"\n\t\t\t\t<td SIDES=\"R\" ALIGN=\"LEFT\">{data}</td>\n\t\t\t</tr>"
def process(self, instrucction): typeChecker = TypeChecker() database = typeChecker.searchDatabase(self._properties['id']) if database: if self._properties['if_not_exists']: return if self._replace: typeChecker.deleteDatabase(database.name, self._noLine, self._noColumn) # TODO Verificar permisos y modo database = Database(self._properties['id']) typeChecker.createDatabase(database, self._noLine, self._noColumn)