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 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 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.")
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
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()
