def commit(self): for table in self.accessed_tables: self.accessed_tables[table].save() self.unlocktables() print("Transaction Commited") logger.get_event_logger().info("Transaction Commited") pass
def execute(database, query, transaction=None): try: parsetree = parsedrop.parse(query) parsetree.database = database validatedrop.validate(parsetree) lock = helper.typeoflock(database, parsetree.table) if lock != None: raise Exception("Table {} is locked by a transaction".format( parsetree.table)) logger.get_event_logger().warning( "Table {} is locked by a transaction".format(parsetree.table)) tablename = parsetree.table directory = database ext = ".json" file = directory + "/" + tablename + ext if os.path.exists(file): os.remove(file) file = directory + "/" + tablename + "_meta" + ext os.remove(file) sqldump.updating_sql_dump(database, tablename) print(f"The {tablename} has been successfully dropped") logger.get_event_logger().info( f"The {tablename} has been successfully dropped") except Exception as e: print(e)
def validatecolumnvaluepair(parsetree): metadata = Metadata(parsetree.database, parsetree.table) for pair in parsetree.columnvaluepair: column = pair.key if not metadata.hascolumn(column): raise Exception("Column '{}' does not exist in table '{}'".format( column, parsetree.table)) logger.get_event_logger().warning( "Column '{}' does not exist in table '{}'".format( column, parsetree.table))
def validatetable(parsetree): directory = parsetree.database ext = ".json" file = directory + "/" + parsetree.table + ext try: open(file, 'r') except: raise Exception("Table Not Found: " + parsetree.table) logger.get_event_logger().warning("Table Not Found: " + parsetree.table)
def execute(database, query, transaction=None): try: parsetree = parseselect.parse(query) parsetree.database = database validateselect.validate(parsetree) lock = helper.typeoflock(database, parsetree.table) if transaction == None: if lock == constants.EXCLUSIVE: raise Exception("Table {} is locked by a transaction".format( parsetree.table)) logger.get_event_logger().warning( "Table {} is locked by a transaction".format( parsetree.table)) table = Table(database, parsetree.table, parsetree.columns) else: if parsetree.table in transaction.accessed_tables.keys(): table = transaction.accessed_tables[parsetree.table] else: if lock == None or lock == constants.SHARED: table = Table(database, parsetree.table, parsetree.columns) helper.locktable(database, parsetree.table, constants.SHARED) transaction.accessed_tables[parsetree.table] = table else: raise Exception( "Table {} is locked by a transaction".format( parsetree.table)) logger.get_event_logger().warning( "Table {} is locked by a transaction".format( parsetree.table)) if parsetree.condition is not None: column = list(parsetree.condition.keys())[0] value = parsetree.condition[column] table = table.filter(column, value, parsetree.conditiontype) table.columns = parsetree.columns if parsetree.columns is None: columns = table.metadata.columns else: columns = parsetree.columns ptable = PrettyTable(columns) for row in table.iterator(): data = [] for column in columns: data.append(row.data[column]) ptable.add_row(data) print(ptable) except Exception as e: print(e)
def validatecolumns(parsetree): if len(parsetree.columns) == 1 and parsetree.columns[0] == "*": parsetree.columns = None return metadata = Metadata(parsetree.database, parsetree.table) for column in parsetree.columns: if not metadata.hascolumn(column): raise Exception("Column '{}' does not exist in table '{}'".format( column, parsetree.table)) logger.get_event_logger().warning( "Column '{}' does not exist in table '{}'".format( column, parsetree.table))
def validatelength(metadata, column, value): allowedlength = metadata.columnlength(column) givenlength = len(str(value)) if metadata.columntype(column) == constants.Metadata.DOUBLE: givenlength = len(str(value).split(".")[0]) if givenlength > allowedlength: raise Exception( "Length Exceeded: {}\nAllowed: {}\nProvided: {}".format( column, allowedlength, givenlength)) logger.get_event_logger().warning( "Length Exceeded: {}\nAllowed: {}\nProvided: {}".format( column, allowedlength, givenlength))
def validatedata(parsetree): metadata = Metadata(parsetree.database, parsetree.table) for column in parsetree.columnvaluepair.keys(): if not metadata.hascolumn(column): raise Exception("Column '{}' does not exist in table '{}'".format( column, parsetree.table)) logger.get_event_logger().warning( "Column '{}' does not exist in table '{}'".format( column, parsetree.table)) validator.checkdatatype(metadata, column, parsetree.columnvaluepair[column]) validator.transformvalue(metadata, parsetree.columnvaluepair, column) validator.validatelength(metadata, column, parsetree.columnvaluepair[column])
def validatedata(parsetree): metadata = Metadata(parsetree.database,parsetree.table) actualcolumns = metadata.columns.keys() providedcolumns = parsetree.columnvaluepair.keys() if len(actualcolumns)!= len(providedcolumns): raise Exception("Column count does not match with actual columns") logger.get_event_logger().warning("Column count does not match with actual columns") for column in parsetree.columnvaluepair.keys(): if not metadata.hascolumn(column): raise Exception("Column '{}' does not exist in table '{}'".format(column,parsetree.table)) logger.get_event_logger().warning("Column '{}' does not exist in table '{}'".format(column,parsetree.table)) validator.checkdatatype(metadata,column,parsetree.columnvaluepair[column]) validator.transformvalue(metadata,parsetree.columnvaluepair,column) validator.validatelength(metadata,column,parsetree.columnvaluepair[column])
def generating_erd(database): table_attributes = [] erd_file = database + "/erd.txt" file = open(erd_file, "w") try: for filename in os.listdir(database): if filename.find("meta") != -1: name = re.search('(.*?)_meta.json', filename) file.write( f"\nThe name of the table is : {name.group(1).strip()}") with open(database + "/" + filename) as f: file_content = json.load(f) columns = file_content['columns'] for column in columns: table_attributes.append(column['name']) file.write( f"\nThe Attributes of the table are : {table_attributes}") keys = file_content['keys'] primary_key = keys['primary'] file.write(f"\nThe Primary key is : {primary_key}") foreign_key_details = keys['foreign'] for foreign_key_detail in foreign_key_details: foreign_key_name = foreign_key_detail['name'] foreign_key_table = foreign_key_detail['ref_table'] file.write( f"\nThe Foreign key is : {foreign_key_name}. It is the primary key for the table : {foreign_key_table}" ) if foreign_key_name in primary_key: file.write( f"\nThe Cardinality between the \"{name.group(1).strip()}\" table and \"{foreign_key_table}\" table is : 1 to 1" ) else: file.write( f"\nThe Cardinality between the \"{name.group(1).strip()}\" table and \"{foreign_key_table}\" table is : 1 to Many" ) file.write("\n") file.write(" -" * 35) file.close() logger.get_event_logger().info(f"Generated ERD") return except Exception as e: print(e)
def validatecondition(parsetree): if parsetree.condition is None: return metadata = Metadata(parsetree.database, parsetree.table) column = list(parsetree.condition.keys())[0] value = parsetree.condition[column] if not metadata.hascolumn(column): raise Exception("Column '{}' does not exist in table '{}'".format( column, parsetree.table)) logger.get_event_logger().warning( "Column '{}' does not exist in table '{}'".format( column, parsetree.table)) checkdatatype(metadata, column, value) transformvalue(metadata, parsetree.condition, column) transformcomparator(parsetree)
def execute(query, user): try: database = use.parse(query) if os.path.exists(ROOT_DIRECTORY + "/" + database): if not authentication.hasaccess(user, database): print("Does not have access to the database") return None print("Database Selected: '{}'".format(database)) logger.get_general_logger().info( "Database Selected: '{}'".format(database)) logger.get_event_logger().info( "Database Selected: '{}'".format(database)) database_val = ROOT_DIRECTORY + "/" + database logger.get_general_logger().info( f"The total number of tables in the database are {counter.count_tables_in_database(database_val)}" ) return ROOT_DIRECTORY + "/" + database print("Database does not exist '{}'".format(database)) return None except Exception as e: print(e) return None
def execute(database, query, transaction=None): try: parsetree = parsedelete.parse(query) parsetree.database = database validatedelete.validate(parsetree) lock = helper.typeoflock(database, parsetree.table) if transaction==None: if lock !=None: logger.get_event_logger().warning("Table {} is locked by a transaction".format(parsetree.table)) raise Exception("Table {} is locked by a transaction".format(parsetree.table)) table = Table(database, parsetree.table) else: if parsetree.table in transaction.accessed_tables.keys(): table = transaction.accessed_tables[parsetree.table] else: if lock ==None: table = Table(database, parsetree.table, parsetree.columns) helper.locktable(database, parsetree.table, constants.SHARED) transaction.accessed_tables[parsetree.table] = table else: logger.get_event_logger().warning("Table {} is locked by a transaction".format(parsetree.table)) raise Exception("Table {} is locked by a transaction".format(parsetree.table)) records_before_deletion = len(table.iterator()) if parsetree.condition is not None: column = list(parsetree.condition.keys())[0] value = parsetree.condition[column] table.delete(column, value, parsetree.conditiontype) table.columns = parsetree.columns records_after_deletion = len(table.iterator()) total_records_deleted = records_before_deletion - records_after_deletion print(f"{total_records_deleted} records have been successfully deleted") logger.get_event_logger().info(f"{total_records_deleted} records have been successfully deleted") else: table.deletetable() print(f"{parsetree.table} table has been successfully deleted") logger.get_event_logger().info(f"{parsetree.table} table has been successfully deleted") if transaction==None: table.save() except Exception as e: print(e)
def checkdatatype(metadata, column, value): if value == "NULL": return datatype = metadata.columntype(column) if datatype == META.VARCHAR: if value[0] != value[-1] or (value[0] != "'" and value[0] != '"'): raise Exception("Invalid data '{}' for column '{}'".format( value, column)) logger.get_event_logger().warning( "Invalid data '{}' for column '{}'".format(value, column)) if datatype == META.INT: if value.find(".") != -1: raise Exception( "For column: {}\nExpecting: {}\nProvided: {}".format( column, datatype, value)) logger.get_event_logger().warning( "For column: {}\nExpecting: {}\nProvided: {}".format( column, datatype, value)) try: int(value) except: raise Exception( "For column: {}\nExpecting: {}\nProvided: {}".format( column, datatype, value)) logger.get_event_logger().warning( "For column: {}\nExpecting: {}\nProvided: {}".format( column, datatype, value)) if datatype == META.DOUBLE: try: float(value) except: raise Exception( "For column: {}\nExpecting: {}\nProvided: {}".format( column, datatype, value)) logger.get_event_logger().warning( "For column: {}\nExpecting: {}\nProvided: {}".format( column, datatype, value))
def execute(database, query, transaction=None): try: parsetree = update.parse(query) parsetree.database = database validateupdate.validate(parsetree) lock = helper.typeoflock(database, parsetree.table) if transaction == None: if lock != None: raise Exception("Table {} is locked by a transaction".format( parsetree.table)) logger.get_event_logger().warning( "Table {} is locked by a transaction".format( parsetree.table)) table = Table(database, parsetree.table) else: if parsetree.table in transaction.accessed_tables.keys(): table = transaction.accessed_tables[parsetree.table] else: if lock != None: raise Exception( "Table {} is locked by a transaction".format( parsetree.table)) logger.get_event_logger().warning( "Table {} is locked by a transaction".format( parsetree.table)) table = Table(database, parsetree.table, parsetree.columns) helper.locktable(database, parsetree.table, constants.EXCLUSIVE) transaction.accessed_tables[parsetree.table] = table column = list(parsetree.condition.keys())[0] value = parsetree.condition[column] rows = table.update(parsetree.columnvaluepair, column, value, parsetree.conditiontype) print("{} Rows Updated".format(rows)) logger.get_event_logger().info("{} Rows Updated".format(rows)) if transaction == None: table.save() except Exception as e: print(e)
def execute(database, query, transaction=None): try: parsetree = parseinsert.parse(query) parsetree.database = database validateinsert.validate(parsetree) record = Record() record.data = parsetree.columnvaluepair lock = helper.typeoflock(database, parsetree.table) if transaction == None: if lock != None: raise Exception("Table {} is locked by a transaction".format( parsetree.table)) logger.get_event_logger().warning( "Table {} is locked by a transaction".format( parsetree.table)) t = Table(database, parsetree.table) t.insert(record) t.save() else: if parsetree.table in transaction.accessed_tables.keys(): table = transaction.accessed_tables[parsetree.table] else: if lock != None: raise Exception( "Table {} is locked by a transaction".format( parsetree.table)) logger.get_event_logger().warning( "Table {} is locked by a transaction".format( parsetree.table)) table = Table(database, parsetree.table, parsetree.columns) helper.locktable(database, parsetree.table, constants.EXCLUSIVE) transaction.accessed_tables[parsetree.table] = table table.insert(record) print("1 Row inserted successfully") logger.get_event_logger().info("1 Row inserted successfully") except Exception as e: print(e)
def handle_queries(): global user database = None active_transaction = None while True: query = str(input(">> ")) operation = bqo.findoperation(query) if operation == Operation.EXIT: break if "CREATE DATABASE" in query.upper(): create.execute(database, query, user) continue elif operation == Operation.GRANT: grant.execute(query, user) continue elif operation == Operation.REVOKE: revoke.execute(query, user) continue elif operation == Operation.SHW_DTBS: infoqueries.showdatabases() continue if database is None and operation is not Operation.USE: print("Database not selected\n") continue if "GENERATE ERD" in query.upper(): erd.generating_erd(database) continue if active_transaction != None: active_transaction.execute(query, operation) if operation == Operation.COMMIT or operation == Operation.ROLLBACK: active_transaction = None continue logger.get_event_logger().info(f"The query entered is : {query}") start_time = time.time() if operation == Operation.SELECT: select.execute(database, query) elif operation == Operation.INSERT: insert.execute(database, query) elif operation == Operation.UPDATE: update.execute(database, query) elif operation == Operation.DELETE: delete.execute(database, query) elif operation == Operation.DROP: drop.execute(database, query) elif operation == Operation.CREATE: create.execute(database, query, user) elif operation == Operation.USE: database = use.execute(query, user) elif operation == Operation.SHW_TBLS: infoqueries.showtables(database) elif operation == Operation.SHW_DTBS: infoqueries.showdatabases() elif operation == Operation.DESC: infoqueries.describe(database, query) elif operation == Operation.STRT_TRNAS: active_transaction = Transaction(database) print("Transaction Started") logger.get_event_logger().info(f"Transaction started") elif operation == Operation.COMMIT: print("No active Transaction") logger.get_event_logger().info(f"No active transaction") elif operation == Operation.ROLLBACK: print("No active Transaction") logger.get_event_logger().info(f"No active transaction") else: print("Invalid Query") logger.get_event_logger().error(f"The query entered is invalid") end_time = time.time() total_time = end_time - start_time logger.get_general_logger().info( f"The total execution time of the query \"{query}\" is : {total_time}" ) print()
def execute(self, query, operation): # return if operation == Operation.SELECT: select.execute(self.database, query, self) elif operation == Operation.INSERT: insert.execute(self.database, query, self) elif operation == Operation.UPDATE: update.execute(self.database, query, self) pass elif operation == Operation.DELETE: delete.execute(self.database, query, self) pass elif operation == Operation.DROP: print("Cannot delete resource during transaction") logger.get_event_logger().warning( "Cannot delete resource during transaction") pass elif operation == Operation.CREATE: print("Cannot create resource during transaction") logger.get_event_logger().warning( "Cannot create resource during transaction") pass elif operation == Operation.USE: print("Cannot change database during transaction") logger.get_event_logger().warning( "Cannot change database during transaction") elif operation == Operation.GRANT: print("Cannot change permissions during transaction") logger.get_event_logger().warning( "Cannot change permissions during transaction") elif operation == Operation.REVOKE: print("Cannot change permissions during transaction") logger.get_event_logger().warning( "Cannot change permissions during transaction") elif operation == Operation.EXIT: print("Please commit or rollback transaction") logger.get_event_logger().warning( "Please commit or rollback transaction") elif operation == Operation.STRT_TRNAS: print("A transaction is already active") logger.get_event_logger().warning( "A transaction is already active") elif operation == Operation.COMMIT: self.commit() elif operation == Operation.ROLLBACK: self.rollback() else: print("Invalid Query") logger.get_event_logger().error("Invalid Query") print()
def rollback(self): self.unlocktables() print("Transaction Rolled back") logger.get_event_logger().info("Transaction Rolled back")