def RunQuery():
query = input("Input a Query: ")
select_index = query.find('SELECT')
from_index = query.find('FROM')
where_index = query.find('WHERE')
if select_index == -1 or from_index == -1 or where_index == -1:
return 'Input Wrong!'
display_list = query[select_index + 6:from_index - 1].strip().split(',')
target_database = query[from_index + 5:where_index - 1].strip()
condition = query[where_index + 5:]
less_index = condition.find('(')
greater_index = condition.find(')')
if less_index == -1 and greater_index == -1:
condition_list = [
item.strip() for item in condition.strip().split(',')
]
else:
new_condition = condition[:less_index] + condition[
less_index + 1:greater_index] + condition[greater_index + 1:]
condition_list = [
item.strip() for item in new_condition.strip().split(',')
]
col_name, operator, literal = condition_list[0], condition_list[
1], condition_list[2]
col_list = [item for item in display_list]
col_list.append(col_name)
env = SliceEnv()
sliceDB = env.open(target_database)
result = sliceDB.run_query(col_list, col_name, operator, literal)
for item in result:
item_str = ''
for each in item:
item_str += str(each) + ' '
print(item_str)
def DeleteRecord():
env = SliceEnv()
db_name = input("Input the table's name(For example:CustDB): ")
sliceDB = env.open(db_name)
index_col = sliceDB.db_index_col
key_value = input("The key of the table " + db_name + " is " + index_col +
", input the key value: ")
value_list = sliceDB.delete(key_value)
print(value_list)
env.close(db_name)
def queryDatabase(sliceQuery):
# open the proper database
queryEnv = SliceEnv.SliceEnv()
queryDB = queryEnv.openDB(sliceQuery.databaseName)
# check if given columns exist
columnsToBeDisplayed = sliceQuery.displayedColumns
elements = len(columnsToBeDisplayed
) # get number of columns that will be displayed.
elemSoFar = 0
for element in columnsToBeDisplayed:
for schemaElement in queryDB.schemaElements:
if element == schemaElement.columnName:
elemSoFar = elemSoFar + 1
if (elemSoFar !=
elements): # if they are equal then all the columns exist.
print(
"One or more of the columns especified do not exist on the given DB. The query can not be executed."
)
return
# Make sure the given literal has the same type as the column to which it will be compared agaisnt.
queryCondition = sliceQuery.condition
"""
for element in queryDB.schemaElements:
if element.columnName == queryCondition.column:
if element.dataType == type(queryCondition.literal):
boolComp=True
break
if boolComp == False:
print("Column data type and literal don't match. Can not execute query.")
return
"""
boolComp = False
# Execute the query
for element in queryDB.rows:
dictionary = element.schemaElementsDict
if queryCondition.equalityOperator == 1:
if dictionary[queryCondition.column] == queryCondition.literal:
for i in columnsToBeDisplayed:
print(i, ":", dictionary[i], " ", end='')
boolComp = True
elif queryCondition.equalityOperator == 2:
if dictionary[queryCondition.column] > queryCondition.literal:
for i in columnsToBeDisplayed:
print(i, ":", dictionary[i], " ", end=' ')
boolComp = True
elif queryCondition.equalityOperator == 3:
if dictionary[queryCondition.column] < queryCondition.literal:
for i in columnsToBeDisplayed:
print(i, ":", dictionary[i], " ", end='')
boolComp = True
if boolComp == True:
print()
boolComp = False
def DisplayJoin():
env = SliceEnv()
left_name = input("Input the first database's name(For example:CustDB): ")
right_name = input(
"Input the second database's name(For example:SalesDB): ")
leftDB = env.open(left_name)
rightDB = env.open(right_name)
rightDB.join(leftDB)
env.close(left_name)
env.close(right_name)
print('Join OK!')
def CreateDB():
db_name = input("Input the table name: ")
count = int(input("a count of the number of fields:"))
db_schema = []
for i in range(count):
field,field_type = input("the name and type for each column(column_name|COLUMN_TYPE):").split('|')
db_schema.append((SliceField(field,type_dic[field_type])))
db_index_col = input("Input an index column name, if an index is required: ")
env = SliceEnv()
env.createDB(db_name, db_schema, db_index_col)
file = open(db_name+'.slc','w')
file.close()
print('Create OK!')
return SLICEDBS
def UpdateRecord():
env = SliceEnv()
db_name = input("Input the table's name(For example:CustDB): ")
sliceDB = env.open(db_name)
sliceRecord = sliceDB.createRecord()
fields = []
for item in sliceDB.db_schema:
fields.append(item.get_field_name())
for field in fields:
value = input('Input "' + field + '" value:')
# print(value)
if value:
sliceRecord.setString(field, value)
sliceDB.set(sliceRecord)
env.close(db_name)
print('Update OK')
def AddRecord():
env = SliceEnv()
db_name = input("Input the database's name(For example:CustDB):")
sliceDB = env.open(db_name)
sliceRecord = sliceDB.createRecord()
while 1:
field = input(
'Input the field name(For example:cust),Press Enter to quit input: '
)
if field:
value = input("Input the value: ")
sliceRecord.setString(field, value)
else:
break
sliceDB.set(sliceRecord)
env.close(db_name)
print('Add OK')
def Bulkload():
env = SliceEnv()
db_name = input("Input the database's name(For example:CustDB): ")
sliceDB = env.open(db_name)
bulkfile = input("Input the bulk file's name(For example:CustDB): ")
sliceDB.load(bulkfile)
def join(self, dataBase, joinColumn):
# check if both databases contain the joinColumn and if they have the same dataType.
boolCheck = self.checkJoinCompability(dataBase, joinColumn)
if (boolCheck == False): # can't perform join so just return.
return
repeatedNames = list(
) # will hold an array of names already used in the local database(Table)
schemaElements = []
# mark all columns with the same name.
for selfElement in self.schemaElements:
for otherElement in dataBase.schemaElements:
if selfElement.columnName == otherElement.columnName: # if another column with the same name has been found in the second table mark it, so it can be changed later.
repeatedNames.append(selfElement.columnName)
# create the new schema for the join
for element in self.schemaElements: # Local Database
schemaElements.append(
SliceModule.SliceModule(element.columnName, element.dataType))
for element in dataBase.schemaElements: # other Database
if element.columnName == joinColumn: # don't add another joinColumn to the new database
continue
elif element.columnName in repeatedNames: # if the current column Name already exists, change it
newName = dataBase.databaseName + "_" + element.columnName
schemaElements.append(
SliceModule.SliceModule(newName, element.dataType))
else: # the column is unique so add as is.
schemaElements.append(
SliceModule.SliceModule(element.columnName,
element.dataType))
# create the new join database.
env = SliceEnv.SliceEnv()
newDataBaseName = self.databaseName + dataBase.databaseName
env.createDB(
newDataBaseName,
schemaElements) # Create the new database without an indexColumn.
# open the new DataBase
newSliceDB = env.openDB(newDataBaseName)
# Join the two databases and store the corresponding data in the newly created database.
for element in self.rows:
dict1 = element.schemaElementsDict
for otherElement in dataBase.rows:
dict2 = otherElement.schemaElementsDict
if dict1[joinColumn] == dict2[
joinColumn]: # if the joinColumn values from both databases are the same then create a new record for the new database.
# create new record for the join database
newSliceRecord = newSliceDB.createRecord()
for key in dict1.keys(
): # copy values from the local database.
newSliceRecord.setElement(key, dict1[key])
for key in dict2.keys(
): # copy only necessary values from other database.
if key != joinColumn: # Don't copy the joinClomun value from the other database since we already have the one from the local database and that column no longer exists.
if key in repeatedNames: # if the name from the new database was changed, then use the correct column name.
newKey = dataBase.databaseName + "_" + key
newSliceRecord.setElement(newKey, dict2[key])
else: # the name is still the same.
newSliceRecord.setElement(key, dict2[key])
newSliceDB.set(
newSliceRecord
) # save the new SliceRecord in join database.
newSliceDB.writeDataBase() # write new database to disk.
def main(argv=None):
menu = """
Please enter the character in front of the method you would like to use(Enter 'help' to display the menu again and q to quit the program.):
a:Create database (table name, count, column name/column_type pairs, index)
b:Update Record (database name, data values)
c:Add record (database name, data values)
d:Delete Record (database name, key value)
e:Bulk load (database name, upload file name)
f:Display Join (database 1, database 2, join column)
g:Run Query (table name, display_columns, condition)
h:Report 1 (nothing required)
i:Report 2 (nothing required)
j:Print Databases (nothing required)
k:print Records (database name)
q:Exit (nothing required)
"""
print(menu)
schemaElements = [SliceModule.SliceModule("cust", SliceModule.DataType.Int),
SliceModule.SliceModule("name", SliceModule.DataType.String),
SliceModule.SliceModule("age", SliceModule.DataType.Int),
SliceModule.SliceModule("phone", SliceModule.DataType.String),
SliceModule.SliceModule("address", SliceModule.DataType.String)]
env = SliceEnv.SliceEnv()
env.createDB("CustDB", schemaElements, "cust")
sliceDB = env.openDB("CustDB")
sliceRecord = sliceDB.createRecord();
sliceRecord.setElement("name", "Joe Smith")
sliceRecord.setElement("cust", 1)
sliceRecord.setElement("age", 43)
sliceRecord.setElement("address", "Montreal")
sliceDB.set(sliceRecord)
schemaElements = [SliceModule.SliceModule("order", SliceModule.DataType.Int),
SliceModule.SliceModule("cust", SliceModule.DataType.Int),
SliceModule.SliceModule("date", SliceModule.DataType.String),
SliceModule.SliceModule("total", SliceModule.DataType.Double)]
envOther = SliceEnv.SliceEnv()
envOther.createDB("SalesDB", schemaElements, "order")
userInput = ""
env = None
sliceDB = None
databaseName = ""
sliceRecord = None
env = SliceEnv.SliceEnv()
while (userInput != "q"):
try:
userInput = input("input> ").strip() # remove spaces.
# Process options
if userInput == 'q': # Exit
pass
elif userInput == 'help': # Help
print(menu)
elif userInput == 'a':
print("Please enter the name of the database:")
databaseName = input("> ").strip() # remove spaces.
print("Please enter the number of columnName|columnType pairs:")
count = input("> ").strip() # remove spaces.
count = int(count)
schemaElements = list()
for index in range(count):
print("Please enter columnName|columnType pairs:")
pair = input("> ").strip() # remove spaces.
schemaElements.append(str(pair))
print("Please enter the index column(Choose From ", schemaElements, " ):")
indexColumn = input("> ").strip() # remove spaces.
schemaElements = createDataBase(databaseName, schemaElements, indexColumn)
if indexColumn == "":
env.createDB(databaseName, schemaElements)
else:
env.createDB(databaseName, schemaElements, indexColumn)
sliceDB = env.openDB(databaseName)
sliceDB.writeDataBase() # write new database to disk.
elif userInput == 'b':
print("Please enter the name of the database you would like to update a SliceRecord:")
databaseName = input("> ").strip() # remove spaces.
sliceDB = env.openDB(databaseName)
if sliceDB is None:
pass
else:
sliceRecord = sliceDB.createRecord();
schemaElements = sliceDB.getschemaElements
for element in schemaElements:
print("Enter a Value for:", element.columnName, " Data Type:", element.dataType)
value = input("> ").strip() # remove spaces.
if value == "" and element.dataType == 1:
value = -1
if value == "" and element.dataType == 2:
value = -0.0
if element.dataType == 1: # The expected type is Integer, so convert current field to integer.
value = int(value)
elif element.dataType == 2: # The expected type is float, so convert current field to float.
value = float(value)
elif element.dataType == 3: # The expected type is string, don't convert since it's already a string..
value = str(value)
sliceRecord.setElement(element.columnName, value)
sliceDB.set(sliceRecord)
sliceDB.writeDataBase()
elif userInput == 'c':
print("Please enter the name of the database you would like to add a SliceRecord:")
databaseName = input("> ").strip() # remove spaces.
sliceDB = env.openDB(databaseName)
if sliceDB is None:
pass
else:
sliceRecord = sliceDB.createRecord();
schemaElements = sliceDB.getschemaElements
for element in schemaElements:
print("Enter a Value for:", element.columnName, " Data Type:", element.dataType)
value = input("> ").strip() # remove spaces.
if value == "" and element.dataType == 1:
value = -1
if value == "" and element.dataType == 2:
value = -0.0
if element.dataType == 1: # The expected type is Integer, so convert current field to integer.
value = int(value)
elif element.dataType == 2: # The expected type is float, so convert current field to float.
value = float(value)
elif element.dataType == 3: # The expected type is string, don't convert since it's already a string..
value = str(value)
sliceRecord.setElement(element.columnName, value)
sliceDB.set(sliceRecord)
print("About to write to database.")
sliceDB.writeDataBase()
elif userInput == 'd':
print("Please enter the name of the database you would like to delete a SliceRecord:")
databaseName = input("> ").strip() # remove spaces.
sliceDB = env.openDB(databaseName)
if sliceDB is None:
pass
else:
dataType = None
print("Please enter key Value of the value you wish to remove:")
value = input("> ").strip() # remove spaces.
schemaElements = sliceDB.getschemaElements
indexColumn = sliceDB.getindexColumn
if indexColumn == "":
print("Can't perform delete operation since the index Column is empty.")
pass
else:
for element in schemaElements:
if element.columnName == indexColumn:
dataType = element.dataType
if dataType == 1: # The expected type is Integer, so convert current field to integer.
value = int(value)
elif dataType == 2: # The expected type is float, so convert current field to float.
value = float(value)
elif dataType == 3: # The expected type is string, don't convert since it's already a string..
value = str(value)
# Delete record
sliceDB.deleteRecord(value)
sliceDB.writeDataBase()
elif userInput == 'e':
print("Please enter the name of the database:")
databaseName = input("> ").strip() # remove spaces.
sliceDB = env.openDB(databaseName)
if sliceDB is None:
pass
else:
print("Please enter the path for the file:")
pathName = input("> ").strip() # remove spaces.
sliceDB.load(pathName)
sliceDB.writeDataBase()
elif userInput == 'f':
print("Please enter the name of the local DataBase:")
databaseName = input("> ").strip() # remove spaces.
sliceDB = env.openDB(databaseName)
if sliceDB is None:
pass
else:
print("Please enter the name of the other DataBase:")
databaseName2 = input("> ").strip() # remove spaces.
otherSliceDB = env.openDB(databaseName2)
if otherSliceDB is None:
pass
else:
print("Please enter the column name for the join to take place:")
joinColumn = input("> ").strip() # remove spaces.
sliceDB.join(otherSliceDB, joinColumn)
elif userInput == 'g':
print("Please enter the name of the Data Base you wish to perform queries on:")
databaseName = input("> ").strip() # remove spaces.
sliceDB = env.openDB(databaseName)
if sliceDB is None:
pass
else:
print("Please enter the columns you would like to be displayed:")
columns = input("> ").strip() # remove spaces.
print("Please enter the condition:")
conditions = input("> ").strip() # remove spaces.
elementsCondition = conditions.split("|")
if elementsCondition[1] == "EQ":
elementsCondition[1] = QueryCondition.SliceOP.EQ
elif elementsCondition[1] == "GT":
elementsCondition[1] = QueryCondition.SliceOP.GT
elif elementsCondition[1] == "LT":
elementsCondition[1] = QueryCondition.SliceOP.LT
schemaElements = sliceDB.getschemaElements
dataType = None
for element in schemaElements:
if element.columnName == elementsCondition[0]:
dataType = element.dataType
if dataType == 1:
elementsCondition[2] = int(elementsCondition[2])
if dataType == 2:
elementsCondition[2] = float(elementsCondition[2])
if dataType == 3:
elementsCondition[2] = str(elementsCondition[2])
condition = QueryCondition.QueryCondition(elementsCondition[0], elementsCondition[1],
elementsCondition[2])
columnsArray = columns.split("|")
query = SliceQuery.SliceQuery(columnsArray, databaseName, condition)
SliceDB.SliceDB.queryDatabase(query);
elif userInput == 'h':
os.system("report1.exe")
elif userInput == 'i':
os.system("report2.exe")
elif userInput == 'j':
dataBases = env.getDataBases
print("Databases:")
for key in dataBases.keys():
print(" ", key)
elif userInput == 'k':
print("Please enter the name of the database you wish to display content:")
databaseName = input("> ").strip() # remove spaces.
sliceDB = env.openDB(databaseName)
if sliceDB is None:
pass
else:
rowsPrint = sliceDB.getRows
for element in rowsPrint:
print(element.schemaElementsDict)
except Exception as err:
print(err)
pass