def queryAll(UserQuery, workingDB): selLower = dbutils.inputCleaner("SELECT * FROM ", UserQuery) selection = dbutils.inputCleaner("select * from ", selLower) if workingDB != None: if dbutils.tableExistenceCheck(selection, workingDB): f = open(f'{workingDB}/{selection}.txt', 'r') print(f.read()) f.close() else: print(f"Could not query table {selection} because it does not exist.") else: print("Please specify which database to use.")
def updateTuple(UserQuery, workingDB): tInput = dbutils.inputCleaner("update ", UserQuery) tName = tInput.split()[0] # Grabs table name setColumn = tInput.split()[2] # Gets "set" column setRecord = tInput.split()[4] #.replace("'", "") # Gets "set" record whereColumn = tInput.split()[6] # Gets "where" column whereRecord = tInput.split()[8] #.replace("'", "") # Gets "where" record if (workingDB != None): if dbutils.tableExistenceCheck(tName, workingDB) == 1: filename = workingDB + '/' + tName + '.txt' # No way to modify middle of file, so we recreate it f = open(filename, 'r') tempFile = f.readlines() f.close() count = 0 mods = 0 setColumnNum = 0 whereColumnNum = 0 for line in tempFile: if (count == 0): # Headers columnList = line.split() del columnList[1::3] setColumnNum = columnList.index(setColumn) whereColumnNum = columnList.index(whereColumn) if (count > 0): # Values tupleDetails = line.split() if (tupleDetails[whereColumnNum] == whereRecord): # Update data, Add newline if last column in row if ((setColumnNum + 2) > len(tupleDetails)): tupleDetails[setColumnNum] = f'{setRecord}\n' # Update data else: tupleDetails[setColumnNum] = setRecord tempFile[count] = ' '.join(tupleDetails) mods += 1 count += 1 # Overwriting the file os.system(f'truncate -s 0 {workingDB}/{tName}.txt') f = open(filename, 'w') for line in tempFile: f.write(line) f.close() print(f"{mods} record(s) modified in {tName}.") else: print( f"Could not update values in {tName} because it does not exist." ) else: print("Please specify which database to use.")
def insertTuple(UserQuery, workingDB): tInput = dbutils.inputCleaner("insert into ", UserQuery) tName = tInput.split()[0] # Grabs table name tRest = tInput.replace(tName, "").replace(" values", "")#.replace('\t', "").replace(" ", "") tAttrs0 = tRest[1:] # Leaves only string with attributes tAttrs1 = tAttrs0[:-1] # See above tAttrs = tAttrs1.split(",") # Creates list from attributes if (workingDB != None): if dbutils.tableExistenceCheck(tName, workingDB) == 1: filename = workingDB + '/' + tName + '.txt' f = open(filename, 'a') f.write('\n') f.write(" | ".join(tAttrs)) # Writes list to file with pipe delimiter f.close() print(f"1 new record inserted into {tName}.") else: print(f"Could not add values to {tName} because it does not exist.") else: print("Please specify which database to use.")
def insertTuple(UserQuery, workingDB, isLocked, u, c): tInput = dbutils.inputCleaner("insert into ", UserQuery) tName = tInput.split()[0] # Grabs table name tRest = tInput.replace(tName, "").replace(" values", "")#.replace('\t', "").replace(" ", "") tAttrs0 = tRest[1:] # Leaves only string with attributes tAttrs1 = tAttrs0[:-1] # See above tAttrs = tAttrs1.split(",") # Creates list from attributes tAttrs[0] = tAttrs[0].replace("(", "") def appendToFile(): f = open(filename, 'a') f.write('\n') f.write(" | ".join(tAttrs)) # Writes list to file with pipe delimiter f.close() if (workingDB != None): if dbutils.tableExistenceCheck(tName, workingDB) == 1: if isLocked == 0: if u: os.system(f"cp {workingDB}/{tName}.txt {workingDB}/{tName}.new.txt") filename = workingDB + '/' + tName + '.new.txt' appendToFile() c.append(f"rm {workingDB}/{tName}.txt") c.append(f"mv {workingDB}/{tName}.new.txt {workingDB}/{tName}.txt") else: filename = workingDB + '/' + tName + '.txt' appendToFile() print(f"1 new record inserted into {tName}.") else: print(f"Table {tName} is locked!") else: print(f"Could not add values to {tName} because it does not exist.") else: print("Please specify which database to use.")
def commandProcessing(): global workingDB global userMadeLock global isLocked #global CommandsToExecuteOnCommit if (';' not in UserQuery and UserQuery.upper() != ".EXIT"): # Invalid command print("Commands must end with ';'") # Creates database elif ("CREATE DATABASE" in UserQuery.upper()): dbName = dbutils.inputCleaner("CREATE DATABASE ", UserQuery) if dbutils.databaseExistenceCheck(dbName) == 0: os.system(f'mkdir {dbName}') print(f"Created database {dbName}.") else: print( f"Could not create database {dbName} because it already exists." ) # Deletes database elif ("DROP DATABASE" in UserQuery.upper()): dbName = dbutils.inputCleaner("DROP DATABASE ", UserQuery) if dbutils.databaseExistenceCheck(dbName): os.system(f'rm -r {dbName}') print(f"Removed database {dbName}.") else: print( f"Could not remove database {dbName} because it does not exist." ) # Sets currently active database elif ("USE" in UserQuery.upper()): workingDB = dbutils.inputCleaner("USE ", UserQuery) #os.system('cd ' + workingDB) if dbutils.databaseExistenceCheck(workingDB): print(f"Using database {workingDB}.") else: print( f"Could not use database {workingDB} because it does not exist." ) # Creates a table with specified name and attributes elif ("CREATE TABLE" in UserQuery.upper()): # Splits input into separate strings tInput = dbutils.inputCleaner("CREATE TABLE ", UserQuery).replace("create table ", "") tName = tInput.split()[0] # Grabs table name tRest = tInput.replace(tName, "") tAttrs0 = tRest[2:] # Leaves only string with attributes tAttrs1 = tAttrs0[:-1] # See above tAttrs = tAttrs1.split(",") # Creates list from attributes if (workingDB != None): if dbutils.tableExistenceCheck(tName, workingDB) == 0: os.system(f'touch {workingDB}/{tName}.txt') filename = workingDB + '/' + tName + '.txt' f = open(filename, 'w') f.write(" |".join( tAttrs)) # Writes list to file with pipe delimiter f.close() print(f"Created table {tName}.") else: print( f"Could not create table {tName} because it already exists." ) else: print("Please specify which database to use.") # Deletes table elif ("DROP TABLE" in UserQuery.upper()): tName = dbutils.inputCleaner("DROP TABLE ", UserQuery) if (workingDB != None): if dbutils.tableExistenceCheck(tName, workingDB): if isLocked == 0: if userMadeLock: CommandsToExecuteOnCommit.append( f'rm {workingDB}/{tName}.txt') else: os.system(f'rm {workingDB}/{tName}.txt') print(f"Removed table {tName} from database {workingDB}.") else: print(f"Table {tName} is locked!") else: print( f"Could not remove table {tName} because it does not exist." ) else: print("Please specify which database to use.") # Returns table elements as specified elif ("SELECT" in UserQuery.upper()): if ("SELECT *" in UserQuery.upper()): if ("." in UserQuery.upper()): joinutils.joinTableOpener(UserQuery, workingDB) else: queryutils.queryAll(UserQuery, workingDB) else: queryutils.querySpecific(UserQuery, workingDB) # Modifies table by adding attribute elif ("ALTER TABLE" in UserQuery.upper()): alter = dbutils.inputCleaner("ALTER TABLE ", UserQuery) tName = alter.split()[0] # Grabs table name alterCmd = alter.split()[1] # Grabs command (ADD, etc) alterRest1 = alter.replace(tName, "") alterRest2 = alterRest1.replace( alterCmd, "") # Left with attributes, currently only supports one newAttr = alterRest2[2:] # May have issue with leading parentheses if workingDB != None: if dbutils.tableExistenceCheck(tName, workingDB): if isLocked == 0: f = open(f'{workingDB}/{tName}.txt', 'a') f.write(f" | {newAttr}" ) # Appends attribute to file with pipe delimiter f.close() print(f"Modified table {tName}.") else: print(f"Table {tName} is locked!") else: print( f"Could not modify table {tName} because it does not exist." ) else: print("Please specify which database to use.") elif ("INSERT INTO" in UserQuery.upper()): tableutils.insertTuple(UserQuery, workingDB, isLocked, userMadeLock, CommandsToExecuteOnCommit) elif ("UPDATE" in UserQuery.upper()): tableutils.updateTuple(UserQuery, workingDB, isLocked, userMadeLock, CommandsToExecuteOnCommit) elif ("DELETE FROM" in UserQuery.upper()): tableutils.deleteTuple(UserQuery, workingDB, isLocked, userMadeLock, CommandsToExecuteOnCommit) elif ("BEGIN TRANSACTION" in UserQuery.upper()): userMadeLock = dbutils.makeLock(workingDB) print("Transaction start.") elif ("COMMIT" in UserQuery.upper()): if userMadeLock: dbutils.releaseLock(workingDB, CommandsToExecuteOnCommit) print("Transaction committed.") else: print("Transaction aborted.") userMadeLock = 0 # Testing purposes, deletes databases to start fresh elif ("DEL" in UserQuery): os.system('rm -r CS457_PA4') elif (".EXIT" != UserQuery.upper()): print("I don't know what you want me to do.")
def deleteTuple(UserQuery, workingDB): tInput = dbutils.inputCleaner("delete from ", UserQuery) tName = tInput.split()[0] # Grabs table name whereColumn = tInput.split()[2] # Gets "where" column whereRecord = tInput.split()[4]#.replace("'", "") # Gets "where" record operand = dbutils.getOperand(tInput.split()[3]) if (workingDB != None): if dbutils.tableExistenceCheck(tName, workingDB) == 1: filename = workingDB + '/' + tName + '.txt' # No way to modify middle of file, so we recreate it f = open(filename, 'r') tempFile = f.readlines() f.close() count = 0 mods = 0 whereColumnNum = 0 for line in tempFile: if (count == 0): # Headers columnList = line.split() del columnList[1::3] whereColumnNum = columnList.index(whereColumn) if (count > 0): # Values tupleDetails = line.split() # Finds selected rows and deletes them def deleteTupleHelper(mods): if (operand == 0): # Equality # The type checking here handles strings and numbers separately # Ex. 150 or 150.00 would not find 150.00 or 150, respectively if (type(tupleDetails[whereColumnNum]) is str): if (tupleDetails[whereColumnNum] == whereRecord): tempFile[count] = None mods += 1 elif (type(tupleDetails[whereColumnNum]) is not str): if (float(tupleDetails[whereColumnNum]) == float(whereRecord)): tempFile[count] = None mods += 1 elif (operand == 1): # Greater than if (float(tupleDetails[whereColumnNum]) > float(whereRecord)): tempFile[count] = None mods += 1 elif (operand == -1): # Less than if (float(tupleDetails[whereColumnNum]) < float(whereRecord)): tempFile[count] = None mods += 1 #TODO # Add != action return mods mods = deleteTupleHelper(mods) count += 1 # Overwrites the file os.system(f'truncate -s 0 {workingDB}/{tName}.txt') f = open(filename, 'w') for line in tempFile: if (line != None): f.write(line) f.close() print(f"{mods} record(s) removed in {tName}.") else: print(f"Could not remove values in {tName} because it does not exist.") else: print("Please specify which database to use.")
def querySpecific(UserQuery, workingDB): selLower = dbutils.inputCleaner("SELECT ", UserQuery) selection = dbutils.inputCleaner("select ", selLower) # Gathering list of variables selectColumns = selection.replace(",", "").split() selectColumns = selectColumns[:selectColumns.index("from")] # Table name tName = selection.split()[len(selectColumns)+1] # Gathering what to filter by whereColumn = selection.split()[len(selectColumns)+3] whereRecord = selection.split()[len(selectColumns)+5] operand = dbutils.getOperand(selection.split()[len(selectColumns)+4]) if workingDB != None: if dbutils.tableExistenceCheck(tName, workingDB): f = open(f'{workingDB}/{tName}.txt', 'r') tempFile = f.readlines() f.close() selectColumnNums = [] columnNameString = "" listToReturn = [] count = 0 for line in tempFile: if (count == 0): # Headers # Finding the indexes of select and where columns columnList = line.split() columnListWithTypes = columnList.copy() del columnListWithTypes[2::3] del columnList[1::3] columnCount = 0 # If variable is found in table, record its index for word in columnList: if word in selectColumns: selectColumnNums.append(columnCount) if (word == whereColumn): whereColumnNum = columnCount columnCount += 1 # Creating a custom table header for the selected columns for index in selectColumnNums: columnNameString += f"{columnListWithTypes[index]} {columnListWithTypes[index+1]} | " queryHeader = columnNameString[:-3] listToReturn.append(queryHeader) if (count > 0): # Values tupleDetails = line.split() # Determines what to do with each row def querySpecificHelper(): # Creates the row output def queryStringMaker(): queryString = "" for index in selectColumnNums: queryString += f"{tupleDetails[index]} | " queryResult = queryString[:-3] listToReturn.append(queryResult) if (operand == 0): # Equality # The type checking here handles strings and numbers separately # Ex. 150 or 150.00 would not find 150.00 or 150, respectively if (type(tupleDetails[whereColumnNum]) is str): if (tupleDetails[whereColumnNum] == whereRecord): queryStringMaker() elif (type(tupleDetails[whereColumnNum]) is not str): if (float(tupleDetails[whereColumnNum]) == float(whereRecord)): queryStringMaker() elif (operand == 1): # Greater than if (float(tupleDetails[whereColumnNum]) > float(whereRecord)): queryStringMaker() elif (operand == -1): # Less than if (float(tupleDetails[whereColumnNum]) < float(whereRecord)): queryStringMaker() elif (operand == -3): # Inequality if (type(tupleDetails[whereColumnNum]) is str): if (tupleDetails[whereColumnNum] != whereRecord): queryStringMaker() elif (type(tupleDetails[whereColumnNum]) is not str): if (float(tupleDetails[whereColumnNum]) != float(whereRecord)): queryStringMaker() querySpecificHelper() count += 1 for line in listToReturn: # Prints table print(line) else: print(f"Could not query table {tName} because it does not exist.") else: print("Please specify which database to use.")
import tableutils import queryutils import joinutils workingDB = None UserQuery = None TableList = [None] while (UserQuery != ".EXIT"): UserQuery = input("nickQL> ") if (';' not in UserQuery and UserQuery != ".EXIT"): # Invalid command print("Commands must end with ';'") # Creates database elif ("CREATE DATABASE" in UserQuery.upper()): dbName = dbutils.inputCleaner("CREATE DATABASE ", UserQuery) if dbutils.databaseExistenceCheck(dbName) == 0: os.system(f'mkdir {dbName}') print(f"Created database {dbName}.") else: print( f"Could not create database {dbName} because it already exists." ) # Deletes database elif ("DROP DATABASE" in UserQuery.upper()): dbName = dbutils.inputCleaner("DROP DATABASE ", UserQuery) if dbutils.databaseExistenceCheck(dbName): os.system(f'rm -r {dbName}') print(f"Removed database {dbName}.") else:
def joinTableOpener(UserQuery, workingDB): joinType = 0 exitFlag = 0 if ('LEFT OUTER JOIN' in UserQuery.upper()): joinType = 1 # Removing unnecessary words from command selLower = dbutils.inputCleaner("SELECT * FROM ", UserQuery) selection = dbutils.inputCleaner("select * from ", selLower) selection = selection.replace("inner join", "").replace("left outer join", "") commandWords = selection.replace(",", "").split() # Grabbing values from commands table1Name = commandWords[0] table2Name = commandWords[2] comparisonOperator = dbutils.getOperand(commandWords[6]) # Importing tables into lists Table_1 = [] Table_2 = [] Table_Join = [] TableListNames = [Table_1, Table_2] ActualTableNames = [table1Name, table2Name] if workingDB != None: for x in range(0, 2): if dbutils.tableExistenceCheck(ActualTableNames[x], workingDB): f = open(f'{workingDB}/{ActualTableNames[x]}.txt', 'r') for line in f: TableListNames[x].append( line) #Turning tables into list of lines f.close() else: print( f"Could not query table {ActualTableNames[x]} because it does not exist." ) exitFlag = 1 else: print("Please specify which database to use.") if (exitFlag == 0): # Finding the index of columns to search table1Column = Table_1[0].index(commandWords[5].split(".")[1]) table2Column = Table_2[0].index(commandWords[7].split(".")[1]) # Performs comparisons on given data def joinOperandFunction(t1, t2): if (comparisonOperator == 0): #Equality if (type(Table_2[t2].split("|")[table2Column]) is str): if (Table_2[t2].split("|")[table2Column] == Table_1[t1].split("|")[table1Column]): Table_Join.append(f'{Table_1[t1]} | {Table_2[t2]}') else: if (float(Table_2[t2].split("|")[table2Column]) == float( Table_1[t1].split("|")[table1Column])): Table_Join.append(f'{Table_1[t1]} | {Table_2[t2]}') elif (comparisonOperator == 1): #Greater than if (Table_2[t2].split("|")[table2Column] > Table_1[t1].split("|")[table1Column]): Table_Join.append(f'{Table_1[t1]} | {Table_2[t2]}') elif (comparisonOperator == -1): #Less than if (Table_2[t2].split("|")[table2Column] < Table_1[t1].split("|")[table1Column]): Table_Join.append(f'{Table_1[t1]} | {Table_2[t2]}') elif (comparisonOperator == -3): #Inequality if (Table_2[t2].split("|")[table2Column] != Table_1[t1].split("|")[table1Column]): Table_Join.append(f'{Table_1[t1]} | {Table_2[t2]}') # Join function. Nested for loops to iterate through tables. def join(): Table_1[0] = Table_1[0].rstrip('\n') Table_2[0] = Table_2[0].rstrip('\n') Table_Join.append(f"{Table_1[0]} | {Table_2[0]}") for t1 in range(1, len(Table_1)): Table_1[t1] = Table_1[t1].rstrip("\n") for t2 in range(1, len(Table_2)): Table_2[t2] = Table_2[t2].rstrip('\n') joinOperandFunction(t1, t2) # Left outer join. If program cannot find Table 1 value in the joined Table, we add it with null info for Table 2's values. if (joinType == 1): if (Table_1[t1].split("|")[table1Column] not in Table_Join[-1].split("|")[table1Column]): Table_Join.append(f"{Table_1[t1]} | |") for myTuple in Table_Join: print(myTuple) join()
import dbutils import tableutils import queryutils workingDB = None UserQuery = None TableList = [None] while (UserQuery != ".EXIT"): UserQuery = input("nickQL> ") if (';' not in UserQuery and UserQuery != ".EXIT"): # Invalid command print("Commands must end with ';'") # Creates database elif ("CREATE DATABASE" in UserQuery.upper()): dbName = dbutils.inputCleaner("CREATE DATABASE ", UserQuery) if dbutils.databaseExistenceCheck(dbName) == 0: os.system(f'mkdir {dbName}') print(f"Created database {dbName}.") else: print( f"Could not create database {dbName} because it already exists." ) # Deletes database elif ("DROP DATABASE" in UserQuery.upper()): dbName = dbutils.inputCleaner("DROP DATABASE ", UserQuery) if dbutils.databaseExistenceCheck(dbName): os.system(f'rm -r {dbName}') print(f"Removed database {dbName}.") else: