def parse_sql_insert_query(user_query,
database,
user_name,
transaction="None"):
if not validate_sql_insert_query(user_query):
print("Incorrect syntax!")
return False, "NA"
else:
global database_selected
database_selected = database
if database_selected == "None":
print("No database selected!")
return False, "NA"
match = re.search(
"INSERT INTO (.*)[\s\n\r]*[(](.*)[)] VALUES[\s\n\r]*[(](.*)[)];",
user_query, re.IGNORECASE)
table_name = match.group(1).strip()
if not access_check.check_table_access("insert", table_name,
database_selected, user_name):
print(f"User {user_name} cannot insert to {table_name}")
return False, "NA"
if access_check.get_lock_status(
database_selected, table_name) == "N" and transaction == "Y":
pass
elif access_check.get_lock_status(
database_selected,
table_name) == user_name and transaction == "Y":
pass
elif transaction == "None":
pass
else:
print("Table locked by another transaction!!")
return table_name, False
if transaction == "Y":
access_check.lock_table(database, table_name, user_name)
columns = match.group(2).strip()
values = match.group(3).strip()
valid = check_insert_data(table_name, columns, values)
path = "resources/" + database_selected + "/" + table_name + ".csv"
if valid:
with open(path, mode="a", newline='', encoding='utf-8') as f:
writer = csv.writer(f)
writer.writerow(values.split(","))
print(f"Data inserted successfully to table {table_name}")
else:
access_check.unlock_table(database, table_name, user_name)
return False, table_name
return True, table_name
def parse_sql_select_query(user_query,
database_name,
user_name,
transaction="None"):
global database_selected
database_selected = database_name
if database_selected == "None":
print("Database is not selected!")
return False, "NA"
if not validate_sql_select_query(user_query):
print("Invalid query!")
return False, "NA"
user_query = str(user_query).strip()
table_name, where_exists = extract_table_name(user_query)
path = "resources/" + database_selected + "/" + table_name + ".csv"
if not Path(path).is_file():
print(
f"Table {table_name} does not exist in the database {database_selected}"
)
return False, table_name
if not access_check.check_table_access("select", table_name,
database_selected, user_name):
print(f"User {user_name} cannot access {table_name}")
return False, "NA"
if access_check.get_lock_status(database_selected,
table_name) == "N" and transaction == "Y":
pass
elif access_check.get_lock_status(
database_selected, table_name) == user_name and transaction == "Y":
pass
elif transaction == "None":
pass
else:
print("Table locked by another transaction!!")
return False, table_name
dictionary = None
operator = ''
if where_exists:
dictionary, operator = extract_select_condition(user_query)
if dictionary is None:
print("Error!!!")
return False, "NA"
columns_needed = extract_selected_columns(user_query)
select_data(table_name, dictionary, operator, columns_needed)
return True, table_name
def parse_sql_update_query(user_query, database, user_name, transaction="None"):
where_exists, valid = validate_sql_update_query(user_query)
if not valid:
print("Incorrect Update Query Syntax!")
return "NA", False
else:
global database_selected
database_selected = database
if database_selected == "None":
print("No database is selected!")
return "NA", False
if where_exists:
match = re.search("UPDATE (.*) SET (.*) WHERE (.*);", user_query, re.IGNORECASE)
table_name = match.group(1).strip()
setting_info = match.group(2).strip()
conditional_info = match.group(3).strip()
if table_name == "" or setting_info == "" or conditional_info == "":
print("Incorrect Update Query")
return "NA", False
path = "resources/" + database_selected + "/" + table_name + ".csv"
if os.path.isfile(path):
if not access_check.check_table_access("update", table_name, database_selected, user_name):
print(f"User {user_name} cannot access {table_name}")
return "NA", False
if access_check.get_lock_status(database_selected, table_name) == "N" and transaction == "Y":
pass
elif access_check.get_lock_status(database_selected, table_name) == user_name and transaction == "Y":
pass
elif transaction == "None":
pass
else:
print("Table locked by another transaction!!")
return table_name, False
if transaction == "Y":
access_check.lock_table(database, table_name, user_name)
table_df = pd.read_csv(path)
update_dictionary_condition, operator = extract_update_condition(user_query)
setting_column_dictionary = extract_setting_column(setting_info)
if update_dictionary_condition is None or setting_column_dictionary is None:
print("Incorrect syntax in update query!")
access_check.unlock_table(database, table_name, user_name)
return "NA", False
if list(update_dictionary_condition.keys())[0].strip() not in table_df:
print(f"The conditional column in update query does not exist in {table_name} table")
access_check.unlock_table(database, table_name, user_name)
return "NA", False
if list(setting_column_dictionary.keys())[0].strip() not in table_df:
print(
f"The column for which value must be changed in update query does not exist in "
f"{table_name} table")
access_check.unlock_table(database, table_name, user_name)
return "NA", False
valid = check_update_data_with_where(table_name, table_df,
update_dictionary_condition,
setting_column_dictionary)
if not valid:
access_check.unlock_table(database, table_name, user_name)
return "NA", False
else:
update_table(update_dictionary_condition, setting_column_dictionary,
table_name, table_df)
return table_name, True
else:
print(f"The table {table_name} does not exist in database {database_selected}")
return "NA", False
else:
match = re.search("UPDATE (.*) SET (.*);", user_query, re.IGNORECASE)
table_name = match.group(1).strip()
setting_info = match.group(2).strip()
if table_name == "" or setting_info == "":
print("Incorrect Update Query")
return "NA", False
if not access_check.check_table_access("update", table_name, database_selected, user_name):
print(f"User {user_name} cannot update {table_name}")
return "NA", False
path = "resources/" + database_selected + "/" + table_name + ".csv"
if os.path.isfile(path):
if access_check.get_lock_status(database_selected, table_name) == "N" and transaction == "Y":
pass
elif access_check.get_lock_status(database_selected, table_name) == user_name and transaction == "Y":
pass
elif transaction == "None":
pass
else:
print("Table locked by another transaction!!")
return table_name, False
if transaction == "Y":
access_check.lock_table(database, table_name, user_name)
table_df = pd.read_csv(path)
setting_column_dictionary = extract_setting_column(setting_info)
if setting_column_dictionary is None:
print("Incorrect syntax in update query!")
access_check.unlock_table(database, table_name, user_name)
return "NA", False
if list(setting_column_dictionary.keys())[0].strip() not in table_df:
print(
f"The column for which value must be changed in update query does not exist in "
f"{table_name} table")
access_check.unlock_table(database, table_name, user_name)
return "NA", False
valid = check_update_data_without_where(table_name, table_df, setting_column_dictionary)
if not valid:
access_check.unlock_table(database, table_name, user_name)
return "NA", False
else:
update_table_without_where(setting_column_dictionary, table_df, table_name)
return table_name, True
else:
print(f"The table {table_name} does not exist in database {database_selected}")
return "NA", False
print("Data updated successfully")
return table_name, True
def parse_sql_drop_query(user_query, database, user_name):
drop_type, valid = validate_sql_drop_query(user_query)
if not valid:
print("Invalid Query")
return "None", False, "None"
else:
if drop_type == "table":
match = re.search("DROP TABLE (.*);", user_query, re.IGNORECASE)
table_name = match.group(1).strip()
global database_selected
database_selected = database
file = Path("resources/" + database_selected + "/" + table_name +
".csv")
if file.is_file():
if not access_check.check_table_access(
"drop", table_name, database_selected, user_name):
print(f"User {user_name} cannot drop {table_name}")
return "table", False, table_name
print(
f"Are you sure that you want to delete the {table_name} in {database_selected}?"
)
print("All data in the table will be deleted!")
answer = input("Y|y for Yes and N|n for No\n")
if answer.upper() == "Y":
# Check for foreign key using metadata (To be done)
foreign_table, can_drop = can_table_be_dropped(table_name)
if not can_drop:
print(
f"The {table_name} table cannot be deleted because it is referred by {foreign_table} table"
)
else:
# Check for access level
os.remove("resources/" + database_selected + "/" +
table_name + ".csv")
print(f"Table {table_name} successfully deleted!")
os.remove("resources/" + database_selected + "/" +
table_name + "_metadata.csv")
delete_from_relationship_info(table_name)
delete_from_sql_dump(table_name)
else:
print("Drop operation cancelled!")
else:
print(
f"Cannot delete {table_name} which does not exist in {database_selected}"
)
return "table", False, table_name
table_access_path = "resources/" + database_selected + "/table_access_level.csv"
if os.path.isfile(table_access_path):
df = pd.read_csv(table_access_path)
df = df[df["table_name"].str.lower() != table_name]
if df.empty:
with open(table_access_path,
'w',
newline='',
encoding='utf-8') as f:
writer = csv.writer(f)
writer.writerow(['table_name', 'user1', 'user2'])
else:
df.to_csv(table_access_path,
mode='w',
index=False,
header=True)
update_database_info(database_selected, table_name)
return "table", True, table_name
elif drop_type == "database":
match = re.search("DROP DATABASE (.*);", user_query, re.IGNORECASE)
if database == "None":
print("Choose the database before using this query")
return "None", False, "None"
database_name = match.group(1).strip()
directory = Path("resources/" + database_name)
if directory.is_dir():
print(
f"Are you sure that you want to delete the {database_name}?"
)
print("All data in the database will be deleted!")
answer = input("Y|y for Yes and N|n for No\n")
if answer.upper() == "Y":
# Check for access level
shutil.rmtree("resources/" + database_name,
ignore_errors=True)
print(f"Database {database_name} successfully deleted!")
database_selected = "None"
database_access_path = "resources/database_access_level.csv"
if os.path.isfile(database_access_path):
df = pd.read_csv(database_access_path)
df = df[
df["database_name"].str.lower() != database_name]
if df.empty:
with open(database_access_path,
'w',
newline='',
encoding='utf-8') as f:
writer = csv.writer(f)
writer.writerow(
['database_name', 'user1', 'user2'])
else:
df.to_csv(database_access_path,
mode='w',
header=True,
index=False)
update_database_info(database_name, "None")
return "database", True, database_name
else:
print("Drop operation cancelled!")
return "database", True, database_name
else:
print(
f"Cannot delete the {database_name} which does not exist")
return "database", False, database_name
def parse_sql_delete_query(user_query,
database_name,
user_name,
transaction="None"):
where_exists, valid = validate_sql_delete_query(str(user_query))
if not valid:
print("Invalid syntax!")
return "NA", False
else:
global database_selected
database_selected = database_name
if database_selected == "None":
print("Select a database before using queries!")
return "NA", False
if where_exists:
match = re.search("DELETE FROM (.*) WHERE (.*);", user_query,
re.IGNORECASE)
table_name = match.group(1).strip()
if not access_check.check_table_access(
"delete", table_name, database_selected, user_name):
print(f"User {user_name} cannot delete from {table_name}")
return "NA", False
if access_check.get_lock_status(
database_name, table_name) == "N" and transaction == "Y":
pass
elif access_check.get_lock_status(
database_name,
table_name) == user_name and transaction == "Y":
pass
elif transaction == "None":
pass
else:
print("Table locked by another transaction!!")
return table_name, False
if transaction == "Y":
access_check.lock_table(database_name, table_name, user_name)
dictionary, operator = extract_select_condition(user_query)
if dictionary is None:
print("Error!!!")
access_check.unlock_table(database_name, table_name, user_name)
return "NA", False
else:
match = re.search("DELETE FROM (.*);", user_query, re.IGNORECASE)
table_name = match.group(1).strip()
if not access_check.check_table_access(
"delete", table_name, database_selected, user_name):
print(f"User {user_name} cannot delete from {table_name}")
return "NA", False
if access_check.get_lock_status(
database_name, table_name) == "N" and transaction == "Y":
pass
elif access_check.get_lock_status(
database_name,
table_name) == user_name and transaction == "Y":
pass
elif transaction == "None":
pass
else:
print("Table locked by another transaction!!")
return table_name, False
if transaction == "Y":
access_check.lock_table(database_name, table_name, user_name)
dictionary = None
operator = ''
delete_data(table_name, dictionary, operator, where_exists)
return table_name, True