def LFU_delete(mysql, view_name):
result_view_list = LFU_get_view_table(mysql)
for ele in result_view_list:
if ele[0] == view_name:
db_query(mysql, 'DROP VIEW \'{}\';'.format(view_name))
return 1
return 0
def LFU_create_view_table(mysql):
exist_result, error = db_query(
mysql, 'show tables like \'view_count_reserved\';', None)
if len(exist_result) == 0:
result, error = db_query(
mysql,
'CREATE TABLE `view_count_reserved`(`view_name` VARCHAR(100) NOT NULL,`count` INT UNSIGNED,PRIMARY KEY (`view_name`));',
None)
return 0 # empty view table return 0
else:
return 1 # view table exists return 1
def LFU_envict(mysql, result_view_list):
least_key = None
least_count = float('inf')
for ele in result_view_list:
if ele[1] < least_count:
print('count is ', ele[1])
least_key = ele[0]
least_count = ele[1]
print(least_key)
if least_key != None:
db_query(
mysql,
'DELETE FROM view_count_reserved WHERE view_name = \'{}\';'.format(
least_key), None)
def get_join(mysql, tables, columns, renames, jointype, match,
returned_view_name):
table = [x.strip() for x in tables.split(',')]
if len(tables) <= 1:
raise PredictableJoinTableNotEnoughException()
if jointype != "full":
matches = [x.strip() for x in match.split(',')]
if (matches[0] == "") or (len(matches) != (len(table) - 1)):
message = "Number of column mismatched with number of table"
return 412, message, None, None
if returned_view_name != "":
command = "CREATE VIEW " + returned_view_name + " AS SELECT "
else:
message = "View name missed"
return 412, message, None, None
if columns == "":
command += "* from "
else:
column = [x.strip() for x in columns.split(',')]
names = [x.strip() for x in renames.split(',')]
if len(names) != len(column):
message = "Number of names and columns mismatched"
return 412, message, None, None
for count in range(0, len(column)):
command += column[count] + " AS " + names[count] + ", "
command = command[:-2] + " from "
command += table[0] + " "
if jointype == "inner":
for count in range(1, len(table)):
command += "INNER JOIN " + table[count] + " ON " + matches[count -
1] + " "
elif jointype == "partial":
for count in range(1, len(table)):
command += "LEFT JOIN " + table[count] + " ON " + matches[count -
1] + " "
elif jointype == "full":
if len(table) != 2:
message = "Only two tables can be full join at one time"
return 412, message, None, None
command += "CROSS JOIN " + table[1] + " "
else:
message = "Incorrect join type"
return 412, message, None, None
command = command[:-1] + ";"
data, error = db_query(mysql, command, None)
if error is not None:
message = "Input incorrect! " + error
return 412, message, None, error
message = "Join between tables is created successfully. New view \'{}\' is saved.".format(
returned_view_name)
LFU_increment(returned_view_name, mysql)
return 201, message, data, None
def check_exist(col_name, table, mysql):
is_exist, error = db_query(
mysql, 'SHOW COLUMNS FROM `{}` LIKE \'{}\';'.format(table, col_name))
if len(is_exist) == 0:
return False
else:
return True
def get_tabledata(table, column, page, filter, sort_by, mysql):
command = "SELECT "
if column:
columns = column.split(",")
for c in columns:
command += c + ","
command = command[:-1] + " "
else:
command += "* "
command += "FROM " + table
if (filter):
command += " WHERE " + filter
if (sort_by):
command += " ORDER BY "
sort_by = sort_by.split(",")
for sort in sort_by:
command += sort + ","
command = command[:-1]
command += " LIMIT 250 OFFSET " + str((int(page) - 1) * 250)
columns, error = db_query(mysql, "SHOW columns FROM " + table)
data, error = db_query(mysql, command)
if column is None:
row_headers = [x[0] for x in columns]
else:
row_headers = column.split(",")
json_data = []
for row in data:
json_data.append(dict(zip(row_headers, row)))
# print(json.dumps(json_data))
if (error == "FAILED_TO_CONNECT"):
return 401, None, None, "Please connect to a database using the /connect endpoint."
elif (error):
return 400, None, None, error
else:
return 200, "Success", json_data, None
def get_metadata(table_name, mysql, current_database=os.getenv("MYSQL_DB")):
resultlist = []
error = ""
if table_name == 'TABLE':
result, error = db_query(
mysql, 'SHOW FULL TABLES IN {};'.format(current_database))
for item in result:
if item[1] == "VIEW" or item[0] == "view_count_reserved":
continue
temp = {"Tables": item[0],
"Table_type": item[1]
}
resultlist.append(temp)
message = "Success! Get all table info from " + current_database + "."
elif table_name == 'VIEW':
result, error = db_query(
mysql, 'SHOW FULL TABLES IN {} WHERE TABLE_TYPE LIKE \'VIEW\';'.format(current_database))
for item in result:
temp = {"Views": item[0],
"Table_type": item[1]
}
resultlist.append(temp)
message = "Success! Get all view info from " + current_database + "."
else:
result, error = db_query(
mysql, 'DESCRIBE `{}`;'.format(table_name))
if error is not None:
return 400, "Table does not exist in the database.", None, None
for item in result:
temp = {"Field": item[0],
"Type": item[1],
"Null": item[2],
"Key": item[3]
}
resultlist.append(temp)
message = "Success! Get all column info from " + current_database + "."
data = resultlist
status = 200
if error is not None:
status = 400
message = "Failed. Error: " + error
return status, message, data, error
def delete_table(table_name, mysql):
# check whether it input is a VIEW
check_type_result, error = db_query(mysql, 'SHOW FULL TABLES like \'{}\''.format(table_name))
if len(check_type_result) > 0 and check_type_result[0][1] == 'VIEW':
LFU_delete(mysql, table_name)
result, error = db_query(mysql, "DROP VIEW " + table_name)
if (error == "FAILED_TO_CONNECT"):
return 401, None, None, "Please connect to a database using the /connect endpoint."
elif (error):
return 400, None, None, error
else:
return 200, "View {} is deleted.".format(table_name), result, None
else:
result, error = db_query(mysql, "DROP TABLE " + table_name)
if (error == "FAILED_TO_CONNECT"):
return 401, None, None, "Please connect to a database using the /connect endpoint."
elif (error):
return 400, None, None, error
else:
return 200, "Table {} is deleted.".format(table_name), result, None
def update_tabledata(table, column, value, condition, mysql):
# Try to parse the table variable in order to detect exception.
tables = table.split(",")
if len(tables) == 0:
raise PredictableInvalidArgumentException("1")
elif len(tables) > 1:
raise PredictableInvalidArgumentException("6")
# Parse the list of columns from string into array.
columns = column.split(",")
# Now, check the duplication in columns.
for elem in columns:
if columns.count(elem) > 1:
raise PredictableDuplicateColumnException(elem)
# Parse the list of values from string into array.
values = value.split(",")
# Now, check the relation between columns and values.
if len(values) != len(columns):
raise PredictableColumnNumberMismatchException(elem)
# Now, we can start to communicate with the database.
command = "UPDATE " + table + " "
command = command + "SET "
x = 0
for elem in columns:
command = command + elem + " = " + values[x] + ", "
x += 1
command = command[:-2] + " "
if len(condition) != 0:
command = command + "Where " + condition + ";"
else:
command = command + ";"
print(command)
data, error = db_query(mysql, command)
if error != None:
status = 412
message = "Incorrect Input! " + error
return status, message, None, error
else:
status = 201
message = "Table " + table + " is updated."
return status, message, None, None
def get_metadata_(table_name, mysql):
resultlist = []
error = ""
result, error = db_query(
mysql, 'DESCRIBE `{}`;'.format(table_name))
if error is not None:
return 400, "Table does not exist in the database.", None, None
for item in result:
temp = {"Field": item[0],
"Type": item[1],
"Null": item[2],
"Key": item[3]
}
resultlist.append(temp)
message = "Success! Get all column info from."
data = resultlist
status = 200
if error is not None:
status = 400
message = "Failed. Error: " + error
return status, message, data, error
def delete_tabledata(table, condition, mysql):
# Try to parse the table variable in order to detect exception.
tables = table.split(",")
if len(tables) == 0:
raise PredictableInvalidArgumentException("1")
elif len(tables) > 1:
raise PredictableInvalidArgumentException("6")
command = "DELETE FROM " + table + " "
command = command + "WHERE " + condition + ";"
print(command)
data, error = db_query(mysql, command)
if error != None:
status = 412
message = "Incorrect Input! " + error
return status, message, None, error
else:
status = 201
message = "Row is deleted."
return status, message, None, None
def update_table(table, columns, operation, mysql):
if operation != "insert" and operation != "drop":
return 400, None, None, "Invalid Operation"
command = "ALTER TABLE " + table
split_columns = columns.split(",")
for column_name in split_columns:
if operation == "insert":
command += " ADD " + column_name + " VARCHAR(200),"
else:
command += " DROP COLUMN " + column_name + ","
command = command[:-1]
result, error = db_query(mysql, command)
message = "Successfully inserted the columns: " + columns + "."
if operation == "drop":
message = "Successfully dropped the columns: " + columns + "."
if (error == "FAILED_TO_CONNECT"):
return 401, None, None, "Please connect to a database using the /connect endpoint."
elif (error):
return 400, None, None, error
else:
return 200, message, result, None
def LFU_increment(view, mysql):
if LFU_create_view_table(mysql) == 0:
db_query(
mysql,
'INSERT INTO view_count_reserved (view_name, count) VALUES ("{}", 1);'
.format(view), None)
else:
result_view_list = LFU_get_view_table(mysql)
for ele in result_view_list:
if ele[0] == view:
db_query(
mysql,
'UPDATE view_count_reserved SET count = count + 1 WHERE view_name = \'{}\';'
.format(view), None)
return
if len(result_view_list) == VIEW_LIMIT:
LFU_envict(mysql, result_view_list)
db_query(
mysql,
'INSERT INTO view_count_reserved (view_name, count) VALUES ("{}", 1);'
.format(view), None)
def LFU_get_view_table(mysql):
result, error = db_query(mysql, 'SELECT * FROM view_count_reserved;', None)
return result
def LFU_reset(mysql):
db_query(mysql, 'DROP TABLE view_count_reserved;', None)
def get_union(mysql, table_name_A, columns_A, table_name_B, columns_B,
returned_view_name):
# check whether table contains in the db
table_name_A = table_name_A.strip()
table_name_B = table_name_B.strip()
A_exist, error = db_query(mysql,
'SHOW TABLES LIKE \'{}\';'.format(table_name_A))
B_exist, error = db_query(mysql,
'SHOW TABLES LIKE \'{}\';'.format(table_name_B))
if len(A_exist) == 0 or len(B_exist) == 0:
message = "Table you entered does not exist. Please check again."
return 400, message, None, None
data = []
# if lists are both empty, select all
if columns_A == None and columns_B == None:
data, error = db_query(
mysql, 'SELECT * FROM {} UNION SELECT * FROM {};'.format(
table_name_A, table_name_B))
if error != None:
message = "Some error occurs " + error
return 400, message, None, error
if returned_view_name != None:
message = "Union between two tables is created successfully. New view \'{}\' is saved.".format(
returned_view_name)
db_query(
mysql,
'CREATE VIEW {} AS SELECT * FROM (SELECT * FROM {} UNION SELECT * FROM {}) AS temp;'
.format(returned_view_name, table_name_A, table_name_B))
LFU_increment(returned_view_name, mysql)
else:
message = "Union between two tables is created successfully. No view is saved."
elif columns_A == None or columns_B == None:
message = "Number of columns does not match. One of them is None."
return 402, message, None, None
else:
# check the number of column match or not
col_list_A = columns_A.split(',')
col_list_B = columns_B.split(',')
if len(col_list_A) != len(col_list_B):
message = "Number of columns does not match. A has {} columns and B has {} columns.".format(
len(col_list_A), len(col_list_B))
return 402, message, None, None
print("jindao else")
# check whether table contains those cols
col_str_A = ""
for index in range(len(col_list_A)):
current = col_list_A[index].strip()
if check_exist(current, table_name_A, mysql) == False:
message = "One of columns you entered does not exist in table A. Please check again."
return 401, message, None, None
if index == len(col_list_A) - 1:
col_str_A = col_str_A + current
else:
col_str_A = col_str_A + current + ","
col_str_B = ""
for index in range(len(col_list_B)):
current = col_list_B[index].strip()
if check_exist(current, table_name_B, mysql) == False:
message = "One of columns you entered does not exist in table A. Please check again."
return 401, message, None, None
if index == len(col_list_B) - 1:
col_str_B = col_str_B + current
else:
col_str_B = col_str_B + current + ","
data, error = db_query(
mysql, 'SELECT {} FROM {} UNION SELECT {} FROM {};'.format(
col_str_A, table_name_A, col_str_B, table_name_B))
if error is not None:
message = "Some error occurs " + error
return 400, message, None, error
if returned_view_name != None:
result, error = db_query(
mysql,
'CREATE VIEW {} AS SELECT * FROM (SELECT {} FROM {} UNION SELECT {} FROM {}) AS temp;'
.format(returned_view_name, col_str_A, table_name_A, col_str_B,
table_name_B))
LFU_increment(returned_view_name, mysql)
if error != None:
message = "Some error occurs " + error
else:
message = "Union between two tables is created successfully. New view \'{}\' is saved.".format(
returned_view_name)
else:
message = "Union between two tables is created successfully. No view is saved."
return 200, message, data, None