The basic executor

Let's build a very naïve executor to be able to execute our SQL statements

The executor is one of the fundamental components of a database.

Our first version on the basic executor will be… basic. But will get the work done.

The executor will get quite sophisticated as we develop it in the future.

The code can be found here.

The interface

There are two parts of the interface:

1. The input for the executor, which will come directly from the parser. In the future there will be a few layers in between the parser and the executor. If you need to be reminded of this, check the parser discussion

2. The output. For now this will be really crude. No error reporting, no status on CREATE and INSERT. We will get a list of results from SELECT

Here is the output interface:

class SomeResultBase(BaseModel):
    pass

class SomeNone(SomeResultBase):
    pass

class SomeSelectResult(SomeResultBase):
    column_names: list[str]
    rows: list[list[str]]

SomeResult = SomeNone | SomeSelectResult

SomeNone is used for CREATE and INSERT, SomeSelectResult for SELECT.

The implementation

The implementation is just a handful of lines.

We'll store all tables as CSV files in a local folder called DB:

from pathlib import Path
DATABASE_PATH = Path.cwd() / "DB"

CREATE TABLE

CREATE TABLE is as simple as

import csv

def create_table(table_definition: SomeCreateTable) -> None:
    with open(DATABASE_PATH / f"{table_definition.name}.csv", "w") as f:
        writer = csv.writer(f, delimiter="\t")
        writer.writerow(map(lambda col: col.name, table_definition.columns))

Our table is just a CSV file. We don’t even save the column type 😜.

INSERT INTO

We simply insert a row in the CSV file:

def insert_into(insert_definition: SomeInsertInto) -> None:
    with open(DATABASE_PATH / f"{insert_definition.table_name}.csv", "a") as f:
        writer = csv.writer(f, delimiter="\t")
        writer.writerow(insert_definition.values)

SELECT *

For SELECT we return all the rows, save the header:

def select(select_definition: SomeSelect) -> SomeSelectResult:
    with open(DATABASE_PATH / f"{select_definition.table_name}.csv", "r") as f:
        reader = csv.reader(f, delimiter="\t")
        header = next(reader)  # get the header
        rows = [row for row in reader]
        return SomeSelectResult(
            column_names=header,
            rows=rows,
        )

This is a really simple executor. You can expect this to become way more sophisticated over time. “Soon we’ll track types, enforce schemas, and start optimizing SELECTs.