We are going to put a lot of restrictions on our database. For now it will:

• support only select and insert
• reside in memory only
• support a single, hard-coded table

our hard-coded table will look like this

insert statements are now going to look like this:

insert 1 cstack foo@bar.com

We are going to start by defining these constants

COLUMN_USERNAME_SIZE = 32
COLUMN_EMAIL_SIZE = 255

ROW_FORMAT = f"<I{COLUMN_USERNAME_SIZE}s{COLUMN_EMAIL_SIZE}s"
ROW_SIZE = struct.calcsize(ROW_FORMAT)

PAGE_SIZE = 4096
TABLE_MAX_PAGES = 100
ROWS_PER_PAGE = PAGE_SIZE // ROW_SIZE
TABLE_MAX_ROWS = ROWS_PER_PAGE * TABLE_MAX_PAGES

<aside> 📖

We will use struct to figure out the byte size here and to unpack and pack rows i.e. convert a tuple into a binary.

</aside>

Then we shall create a table class that store pages in an array. We have preset a max page size that sets the number of rows in a page. Each row is added next to the last page’s end.

We also have a row_slots function that figures out the page_number based on the row number and number of rows in a page. we then need to figure the row_offset for the page number which will be mod of row_num % Rows per page.

Then the byte offset will be row_offset * Row_size . We then return the page the byte_offset for the row

class Table:
    def __init__(self):
        self.num_rows=0
        self.pages=[None] * TABLE_MAX_PAGES

    def row_slots(self,row_num):
        page_num=row_num//ROWS_PER_PAGE
        if self.pages[page_num]==None:
            self.pages[page_num]=bytearray(PAGE_SIZE)

        row_offset = row_num % ROWS_PER_PAGE
        byte_offset = row_offset * ROW_SIZE
        return self.pages[page_num], byte_offset

In the main file

we update the function that prepares the statement.

def prepare_statement(user_input):
    parts=user_input.split()
    print(parts)
    if user_input.startswith("insert"):
        if len(parts)<4:
            return "SYNTAX ERROR",None
        try:
            row_to_insert=(int(parts[1]),parts[2],parts[3])
            return "SUCCESS", {"type": "insert", "data": row_to_insert}
        except ValueError:
            return "SYNTAX_ERROR", None
    if parts[0] == "select":
        return "SUCCESS", {"type": "select"}
    return "UNRECOGNIZED", None

Now for execution our main function must also be updated to execute the insert and select statements respectively