def IsComplete(self, sql):
""" TODO: Description
"""
# Clean the SQL query from comments and such
sqln = self.CleanSQL(sql)
m = self.re_complete.match(sql)
if m:
return rs.SQLComplete(sqln)
ret = rs.SQLIncomplete(sqln)
return ret
def TryCreateTable(self, name, fields):
""" TODO: Description
"""
# Check if all types exist, if one doesn't raise an error
fbuild = {}
for field in fields.keys():
if not fields[field][0] in typeTable:
return err.TYPENOTFOUNDERROR(fields[field][0])
fbuild[field] = fields[field][0]
self.name = name
self.fields = fbuild # Fields & types
self.rows = []
self.caster = TypeCaster()
# Set constraints
self.primary = "ID"
self.setConstraints(fields)
self.primaryIndex = {
} # Elke insert krijgt de primary key hier + de index ervan in de rows
return res.DBResponse(type="SUCCESS",
operation="CREATE TABLE",
table=name)
def try_CreateTable(self, sql):
""" Try to determine whether or not the statement is a CREATE TABLE statement
"""
ret = rs.SQLNone
regex_result = self.re_table.search(sql)
if regex_result:
# TODO: Improve upon this splitting
# TODO: Create the constraints array/dictionary
fields = regex_result.group(2).split(
",") # Break fields up in field constraint tuples
fields = [f.split() for f in fields]
# Register the constraints and turn it into a dict
fdict = {}
for f in fields:
fdict[f[0]] = f[1:]
ret = rs.SQLResponse(
type="CREATE TABLE",
table=regex_result.group(
1), # Table name is the first group in the match
fields=fdict # The dictionary of field TYPE pairs
)
return ret
def try_insert(self, sql):
""" Try to insert items into the database
input:
sql: string the SQL query
"""
out = rs.SQLNone
res = self.re_insert.search(sql) # table, [field], [value]
if res:
# Split the fields & values into keyvalue pairs
fields = [
self.clean_name.sub("", f) for f in res.group(2).split(",")
]
values = [
self.clean_field.sub("", f) for f in res.group(3).split(",")
]
# TODO: Is this correct?
for i in range(len(values)):
if values[i][0] == " ":
values[i] = values[i][1:]
# Combine cleaned fields into pairs
kvpairs = zip(fields, values)
# Put everything into a SQLResponse
out = rs.SQLResponse(
type="INSERT", # INSERT
table=res.group(1), # Table
fields=kvpairs # [(Field,Value)]
)
return out
def try_select(self, sql):
""" Try to see whether or not the query is a selection query and break it up in parts
"""
ret = rs.SQLNone
# See whether or not it is a SELECT query
res = self.re_selectNew.search(sql)
if not res:
return ret
# Check for a WHERE clause
wh = self.re_whereNew.search(res.group(3))
conditions = []
if wh:
conditions.append(wh.groups())
ret = rs.SQLResponse(
type="SELECT", # Type
table=self.re_clean.sub("", res.group(2)), # What table
fields=self.re_clean.sub("", res.group(1)).split(",") # Fieldnames
)
ret["conditions"] = conditions
return ret
def ImportSQL(self, filestream):
""" Import a database from a SQL file
input:
filestream : file : A python filestream to write to
returns:
Not sure yet
"""
lines = filestream.read().split("\n")
for i in lines:
ret = self.InputSQL(i)
if ret["type"] == "ERROR":
return ret
res = DBResponse.DBResponse("IMPORT COMPLETE")
return res
def ExportAsSQL(self, filestream, encoding = "LINUX"):
""" Exports the database as a list of SQL statements to an active filestream
input:
filestream : file : A python filestream to write to
returns:
DBResponse : An acknowledge or an error, depending on if everything went right
"""
nl = "\r\n"
if encoding == "LINUX":
nl = "\n"
elif encoding == "WINDOWS":
nl = "\r\n"
# Create DB:
filestream.write("--Create database:" + nl)
filestream.write("--CREATE DATABASE " + str(self.name) + ";" + nl)
# Create Tables:
filestream.write(nl + "--Create tables:" + nl)
for name in self.tablenames:
filestream.write("CREATE TABLE " + str(name) + " (" +nl)
# Creating all fields of the tables
for i, field in enumerate(self.tables[name].fields.keys()):
filestream.write(" " + str(field) + " " +
str(self.tables[name].fields[field]) +
("," +nl if i+1 < len(self.tables[name].fields.keys()) else nl))
filestream.write(");" + nl)
# The data of each table:
filestream.write(nl + "--Fill in the data of each table:" + nl)
for table in self.tablenames: # Export per table:
for row in self.tables[table].rows: # Export each row
fields = row.keys() # Not each row has a value for all the keys
values = "', '".join(map(str,[row[key] for key in fields ]))
line = "INSERT INTO " +table+" ('"+ "', '".join(map(str, fields)) +"')" + nl +" VALUES ('"+values+"');"
filestream.write(line + "" + nl)
return DBResponse.DBResponse("EXPORT COMPLETE")
print
def Query(self, query):
""" Execute a query
input:
query : string : A SQL query to be executed
returns:
DBResponse : The effect of the query
"""
# Interpret SQL
# Currently hardcoded to just accept insertion queries
ret = DBResponse.DBResponse("None")
res = self.sqlinterpreter.TryDecodeSQL(query)
if res["type"] == "ERROR":
return res
elif res["type"] == "INCOMPLETE": # For multiline SQL queries
self.lastcommand += res["query"]
elif res["type"] == "INSERT":
ret = self.insert(
tbl = res["table"],
kvpairs = res["fields"] # Fields::[(field, value)]
)
elif res["type"] == "SELECT":
ret = self.select(
table = res["table"],
fields = res["fields"],
conditions = res["conditions"]
)
elif res["type"] == "CREATE TABLE":
ret = self.create_table(
name = res["table"],
fields = res["fields"]
)
return ret
def setConstraints(self, constraints):
""" Fills in a list of lambda functions for each of the fields
input:
TODO
returns:
DBResponse : The response of filling in the constraints; can be a type error
"""
self.consts = {}
for field in constraints.keys():
funcs = []
# Check the type consrtaint
if not constraints[field][0] in typeTable:
return err.TYPENOTFOUNDERROR(constraints[field][0])
# Set the type constraint
# type, cast = self.caster.TypeCast(value)
print constraints[field][0]
f = None
if constraints[field][0] == "TEXT":
f = lambda x: True
else:
f = lambda x: self.caster.TypeCast(x)[0] == constraints[field][
0]
funcs.append(f)
# The other constraitns
for c in constraints[field][1:]:
# primary:
funcs.append(lambda x: not x in self.primaryIndex.keys())
continue
self.consts[field] = funcs
return res.Response("ACC")
def Insert(self, kvpairs):
""" Check for uniqueness in the primary key and types, then finally add the type-cast value to the db
"""
row = {}
# Check whether the table has the key and ifso insert into row
for key, value in kvpairs:
if not self.fields.has_key(key): # Check if key exists
return err.KEYNOTFOUNDERROR(key, self.name)
# Check for primary uniqueness
if key == self.primary:
if value in self.self.primaryIndex.keys():
# TODO: Raise proper error
return err.ERROR(
text="Tried to insert not-unique primary key!")
# Check types and typecast
if not self.fields[key] == "TEXT":
type, cast = self.caster.TypeCast(value)
if type == self.fields[key]: # Check for correct type
row[key] = cast
else:
return err.TYPEERROR(key, self.fields[key], type)
else:
row[key] = value
# Now fill up the missed keys in the row
for key in self.fields.keys():
if not row.has_key(key):
row[key] = self.Defaults(key)
# Finally add the row to the database
self.rows.append(row)
return res.DBResponse("SUCCESS", operation="INSERT", table=self.name)