def distinct(table: Table, column):
""" returns a filtered table where all rows are distinct """
newTable = Table(table.name, table.schema)
columnIndex = table.keyToPosDict[column]
valueSet = set()
for row in table:
val = row[columnIndex]
if val in valueSet:
continue
valueSet.add(val)
newTable.append(row)
return newTable
def where(table: Table, column, symbol, constant):
"""
tuple = (column_name, operator, constant)
SLECT * IS NOT NULL
SELECT * WHERE id > 4;
>, <,=, !=, IS NOT, IS.
returns a filtered table
"""
constant = None if constant == "NULL" else constant
newTable = Table(table.name, table.schema)
func = operatorDictionary[symbol]
columnIndex = table.keyToPosDict[column]
# for row in table: # suspicious
# row.cleanRow(table.schema) # suspicious
for row in table:
if row[columnIndex] is None and constant is not None:
continue
if func(row[columnIndex], constant):
newTable.append(row)
return newTable
def leftOuterJoin(self, tokens):
"""
"SELECT students.name, students.grade, classes.course, classes.instructor
FROM students LEFT OUTER JOIN classes
ON students.class = classes.course
WHERE students.grade > 60
ORDER BY classes.instructor, students.name, students.grade;"
"""
whereArr = []
if "WHERE" in tokens:
tokens = list(tokens)
whereIndex = tokens.index("WHERE")
orderIndex = tokens.index("ORDER")
whereArr = tokens[whereIndex:orderIndex]
del tokens[whereIndex:orderIndex]
tokens = deque(tokens)
# PARSING STRING FIRST
fields, leftTableName, rightTableName, leftJoinCol, rightJoinCol, orderByColumns = leftJoinParse(
tokens)
# Make deep copies of left and right tables
leftTable: Table = deepcopy(self.db[leftTableName])
rightTable: Table = deepcopy(self.db[rightTableName])
newTableName = "joinedTable"
newSchema = OrderedDict()
for key, value in leftTable.schema.items():
newKeyName = newTableName + "." + key
newSchema[newKeyName] = value
for key, value in rightTable.schema.items():
newKeyName = newTableName + "." + key
newSchema[newKeyName] = value
newTable = Table(newTableName, newSchema)
self.db[newTableName] = newTable
lkeyToPosDict, rkeyToPosDict = leftTable.keyToPosDict, rightTable.keyToPosDict
rightNones = len(rightTable[0]) * [None]
# populate new table
for lrow in leftTable:
row = Row()
if lrow.getVal(leftJoinCol, lkeyToPosDict) is None:
row.extend(lrow + rightNones)
newTable.append(row)
continue
for rrow in rightTable:
if lrow.getVal(leftJoinCol, lkeyToPosDict) == rrow.getVal(
rightJoinCol, rkeyToPosDict):
row.extend(lrow + rrow)
newTable.append(row) # needs fixing, maybe?
break # there might be multiple tables with same matching id
else:
row.extend(lrow + rightNones)
newTable.append(row)
continue
for i, field in enumerate(fields):
fields[i] = addTableName(newTableName, field)
for i, field in enumerate(orderByColumns):
orderByColumns[i] = addTableName(newTableName, field)
fieldsString = ", ".join(fields)
orderString = ", ".join(orderByColumns)
whereString = ""
if whereArr:
whereArr[1] = addTableName(newTableName, whereArr[1])
whereArr = [str(i) for i in whereArr] # maybe switch none to NULL
whereString = " ".join(whereArr)
query = " SELECT {} FROM {} {} ORDER BY {};".format(
fieldsString, newTableName, whereString, orderString)
result = self.executeHelper(query)
# delete the table we created
self.db.pop(newTableName)
return result