def describe(database, query):
pt = desc.parse(query)
pt.database = database
common_methods.validatetable(pt)
metadata = Metadata(database, pt.table)
columns = ["Column", "Type", "Length", "PK", "FK"]
ptable = PrettyTable(columns)
for col in metadata.columns.keys():
data = []
data.append(col)
data.append(metadata.columns[col][META.COLUMNS_TYPE])
data.append(metadata.columns[col][META.COLUMNS_LENGTH])
if col in metadata.primarykeys:
data.append("YES")
else:
data.append("-")
if col in metadata.foreignkeys.keys():
fk = metadata.foreignkeys[col][META.KEYS_FOREIGN_REF_TABLE]
fk += "(" + metadata.foreignkeys[col][
META.KEYS_FOREIGN_REF_COLUMN] + ")"
data.append(fk)
else:
data.append("-")
ptable.add_row(data)
print(ptable)
def validate(parsetree):
validator.validatetable(parsetree)
validator.validatecolumns(parsetree)
validator.validatecondition(parsetree)
# try:
# pt = select.parse("select firstname from EMP where id = 1")
# validate(pt)
# except Exception as e:
# print(e)
def validate(parsetree):
validator.validatetable(parsetree)
validatedata(parsetree)
def __getcolumns(database, query):
columns = re.search(r'\(.*\)', query).group()
columns = columns[1:-1].strip().split(",")
columns = list(map(lambda x: x.strip(), columns))
metadata = {"columns": [], "keys": {"primary": [], "foreign": []}}
columns_names = []
columnlist = []
for e in columns:
pair = list(map(lambda x: x.strip(), e.split()))
if pair[0] == "PRIMARY":
primarykey = re.search(r'\(.*\)', e).group()
primarykey = primarykey[1:-1].split(",")
primarykey = list(map(lambda x: x.strip(), primarykey))
for key in primarykey:
if key not in columns_names:
raise Exception("Primary Key {} is Unknown".format(key))
metadata["keys"]["primary"].append(primarykey[0])
elif pair[0] == "FOREIGN":
parts = e.split()
parts = list(map(lambda x: x.strip(), parts))
col = parts[2][1:-1]
if col not in columns_names:
raise Exception("Foreign Key {} is Unknown".format(col))
ref_table = parts[4][:parts[4].find("(")]
ref_col = parts[4][parts[4].find("(") + 1:-1]
parsetree = ParseTree()
parsetree.table = ref_table
parsetree.columns = [ref_col]
parsetree.database = database
common_methods.validatetable(parsetree)
common_methods.validatecolumns(parsetree)
foreign_key = {
"name": col,
"ref_table": ref_table,
"ref_column": ref_col
}
metadata["keys"]["foreign"].append(foreign_key)
elif "INT" in pair[1]:
column = {}
column["name"] = pair[0]
datatype = "INT"
length = re.search(r'\(.*\)', e).group()
length = int(length[1:-1])
column["type"] = datatype
column["length"] = int(length)
columnlist.append(column)
columns_names.append(pair[0])
elif "VARCHAR" in pair[1]:
column = {}
column["name"] = pair[0]
datatype = "VARCHAR"
length = re.search(r'\(.*\)', e).group()
length = int(length[1:-1])
column["type"] = datatype
column["length"] = int(length)
columnlist.append(column)
columns_names.append(pair[0])
elif "DOUBLE" in pair[1]:
column = {}
column["name"] = pair[0]
datatype = "DOUBLE"
length = re.search(r'\(.*\)', e).group()
length = int(length[1:-1])
column["type"] = datatype
column["length"] = int(length)
columnlist.append(column)
columns_names.append(pair[0])
else:
raise Exception("Error in Create Query")
metadata["columns"] = columnlist
return metadata
def validate(parsetree):
validator.validatetable(parsetree)
validatedata(parsetree)
validator.validatecondition(parsetree)
def validate(parsetree):
validator.validatetable(parsetree)
if parsetree.condition is not None:
validator.validatecondition(parsetree)