Data Model

Data Model

SQLToy has a very simple internal data model. We have no persistence so we do not have to worry about writing anything to disk; everything is stored in memory.

Our database is stored in a variable called database. It is initialized like:

function initSQLToy() {
  database = {
    tables: {}
  }
}

database has one property: tables. This stores all the tables in the database indexed by name. You can see how a table is created in the CREATE_TABLE method:

function CREATE_TABLE(name) {
  database.tables[name] = {
    name,
    rows: []
  }

  return database.tables[name];
}

This function takes a name of a table and adds a new empty table to the database.tables[] array. Note that we do not do any error checking here. If the table name already exists we overwrite it. This is in keeping with the SQLToy philosophy of simplicity. The purpose of SQLToy is not to make a database which is actually useful! The purpose is to teach SQL.

In the same vein we do not create a schema for our table. In a real database CREATE TABLE takes a list of columns and types. This way the database can typecheck the values being inserted into the table. We forgo all that as well. We want to keep the focus on the SQL operations without worrying about error handling.

The object which is inserted into the tables array is our new table. This object has two proerties: name and rows. The name is the name of the table. This is used for output and for creating column names when doing joins.

The second property is an array of rows. Each row is represented by an object. We can see how they are created in INSERT_INTO:

function INSERT_INTO(tableName, r) {
  let rows;
  if (Array.isArray(r)) {
    rows = r;
  } else {
    rows = [r];
  }
  const table = database.tables[tableName];
  for (const row of rows) {
    table.rows = [...table.rows, row];
  }
}

This function accepts a tableName and a second argument which can be either a single row object or an array of row objects.

Rows are just objects which have the column name as the key and the value for the column as the value. For example:

{
    id: 1,
    name: 'Josh',
    department_id: 1,
}

All told a table