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")
