class Engine: database = Loader.get_database() @classmethod def execute(cls, statement_data): if statement_data['command'] == 'SELECT': table_names = statement_data['table_ids'] cls.joined_table = cls.process_tables(table_names) if statement_data['has_where']: cls.filtered_table = cls.filter_by_where( statement_data['where_clause']) else: cls.filtered_table = cls.joined_table if statement_data['has_function']: Engine.aggregate_function(statement_data, cls.filtered_table) else: column_names = statement_data['column_ids'] selected_columns = cls.process_columns(column_names) cls.project(selected_columns) else: raise NotImplementedError('Command not implemented') @classmethod def project(cls, columns): print '%s' % ', '.join(map(str, columns)) rows = cls.filtered_table.get_rows() for row in rows: projected_row_data = [] cells = row.get_cells() for cell in cells: cell_column = cell.get_column() if cell_column.get_name() in columns: projected_row_data.append(cell.get_value()) print '%s' % ', '.join(map(str, projected_row_data)) @classmethod def process_columns(cls, column_names): selected_columns = [] if column_names[0] == '*': # handle Wildcard columns = cls.filtered_table.get_columns() for column in columns: selected_columns.append(str(column.get_name())) else: for column_name in column_names: if not '.' in column_name: columns = cls.filtered_table.get_columns() for column in columns: proper_column_name = column.get_name() half_column_name = proper_column_name.split('.') if column_name == half_column_name[1]: column_name = proper_column_name selected_columns.append(str(column_name)) return selected_columns @classmethod def process_tables(cls, table_names): tables = [] column_names = [] for table_name in table_names: table = cls.database.get_table_by_name(table_name) tables.append(table) columns = table.get_columns() for column in columns: column_name = column.get_name() column_names.append(table_name + '.' + column_name) cls.database.create_table('joined_table', column_names) joined_table = cls.database.get_table_by_name('joined_table') all_rows = [] Engine.join(0, tables, [], all_rows) # generate rows for row in all_rows: joined_table.insert_values(row) # insert rows return joined_table @staticmethod def join(table_ind, tables, row_data, all_rows): if table_ind == len(tables): all_rows.append(row_data) else: table = tables[table_ind] for row in table.get_rows(): new_row = row_data + row.get_row_data() Engine.join(table_ind + 1, tables, new_row, all_rows) @classmethod def filter_by_where(cls, where_clause): if len(where_clause) < 3 or (len(where_clause) > 3 and len(where_clause) < 7) or ( len(where_clause) > 7): raise SyntaxError('Invalid WHERE clause') column_names = [ column.get_name() for column in cls.joined_table.get_columns() ] cls.database.create_table('filtered_table', column_names) filtered_table = cls.database.get_table_by_name('filtered_table') and_clause = False or_clause = False try: if where_clause[3].upper() == 'AND': and_clause = True elif where_clause[3].upper() == 'OR': or_clause = True else: raise SyntaxError('Invalid WHERE clause') except IndexError: pass rows = cls.joined_table.get_rows() for row_number, row in enumerate(rows): bool_1 = cls.compare(where_clause[0], where_clause[2], row_number) bool_2 = True if and_clause or or_clause: bool_2 = cls.compare(where_clause[4], where_clause[6], row_number) if and_clause: if bool_1 and bool_2: filtered_table.insert_values(row.get_row_data()) elif or_clause: if bool_1 or bool_2: filtered_table.insert_values(row.get_row_data()) else: if bool_1: filtered_table.insert_values(row.get_row_data()) return filtered_table @classmethod def compare(cls, lhs, rhs, row_number): lhs_value = cls.get_value_from_argument(lhs, row_number) rhs_value = cls.get_value_from_argument(rhs, row_number) return lhs_value == rhs_value @classmethod def get_value_from_argument(cls, argument, row_number): column_argument = None if '.' in argument: column_argument = cls.joined_table.get_column_by_name(argument) else: columns = cls.joined_table.get_columns() for column in columns: proper_column_name = column.get_name() column_name = proper_column_name.split('.') if argument == column_name[1]: column_argument = column break if column_argument is not None: cell = column_argument.get_cell_by_index(row_number) return cell.get_value() return argument @staticmethod def aggregate_function(statement_data, table): function_column_name = statement_data['function_column'] if not '.' in function_column_name: columns = table.get_columns() for column in columns: proper_column_name = column.get_name() column_name = proper_column_name.split('.') if function_column_name == column_name[1]: function_column_name = proper_column_name function_column = table.get_column_by_name(function_column_name) cells = function_column.get_cells() if statement_data['function'] == 'sum': ret = 0 for cell in cells: ret += int(cell.get_value()) print 'sum(' + function_column_name + ')' elif statement_data['function'] == 'avg': ret = 0 for cell in cells: ret += int(cell.get_value()) ret = float(ret) / len(cells) print 'avg(' + function_column_name + ')' elif statement_data['function'] == 'min': ret = sys.maxint for cell in cells: value = int(cell.get_value()) if ret > value: ret = value print 'min(' + function_column_name + ')' elif statement_data['function'] == 'max': ret = -sys.maxint - 1 for cell in cells: value = int(cell.get_value()) if ret < value: ret = value print 'max(' + function_column_name + ')' else: raise NotImplementedError( 'Aggregate function not implemented or invalid') print ret
from loader import Loader from parser import Parser from engine import Engine database = Loader.get_database() if __name__ == '__main__': while True: query = raw_input('> ') if query == 'Quit': break try: statement_data = Parser.process_query(query, database) Engine.execute(statement_data) except SyntaxError as error: print 'SyntaxError: ' + str(error) except NotImplementedError as error: print 'NotImplementedError: ' + str(error) except IndexError as error: