◄ prev contents next ►

⚠

The web version only has simple instructions since chapter 04, while the full book has detailed explanations and background info.

0305: Execute SQL

Execute Parsed SQL

Some relational database interfaces were implemented earlier:

func (db *DB) Select(schema *Schema, row Row) (ok bool, err error)
func (db *DB) Insert(schema *Schema, row Row) (updated bool, err error)
func (db *DB) Update(schema *Schema, row Row) (updated bool, err error)
func (db *DB) Delete(schema *Schema, row Row) (deleted bool, err error)

Now SQL is parsed into StmtXXX types, so these 2 parts can be connected:

type SQLResult struct {
    Updated int
    Header  []string
    Values  []Row
}

func (db *DB) ExecStmt(stmt interface{}) (r SQLResult, err error) {
    switch ptr := stmt.(type) {
    case *StmtCreatTable:
        err = db.execCreateTable(ptr)
    case *StmtSelect:
        r.Header = ptr.cols
        r.Values, err = db.execSelect(ptr)
    case *StmtInsert:
        r.Updated, err = db.execInsert(ptr)
    case *StmtUpdate:
        r.Updated, err = db.execUpdate(ptr)
    case *StmtDelete:
        r.Updated, err = db.execDelete(ptr)
    default:
        panic("unreachable")
    }
    return
}

SQLResult returns the result of a statement:

SELECT returns Header and Values. It is a slice because it will return multiple rows later.
Other update statements return Updated, the number of affected rows. For now it can only be 0 or 1.

Store Table Schemas

All operations depend on schemas, so first implement execCreateTable(). Serialize the table schema and store it under one key. DB adds a map cache, keyed by table name:

type DB struct {
    KV     KV
    tables map[string]Schema
}

func (db *DB) GetSchema(table string) (Schema, error) {
    schema, ok := db.tables[table]
    if !ok {
        val, ok, err := db.KV.Get([]byte("@schema_" + table))
        if err == nil && ok {
            err = json.Unmarshal(val, &schema)
        }
        if err != nil {
            return Schema{}, err
        }
        if !ok {
            return Schema{}, errors.New("table is not found")
        }
        db.tables[table] = schema
    }
    return schema, nil
}

Implement execCreateTable():

Convert StmtCreatTable to Schema.
Store Schema under the key @schema_ + table name.
Add it to the DB.tables map.

func (db *DB) execCreateTable(stmt *StmtCreatTable) (err error)

Execute SELECT

It is better to split this into small helpers, which can be reused by other statements:

lookupColumns(): check column names (select a,b), return indices in schema.Cols.
makePKey(): check that WHERE matches the primary key, return a Row with the key filled.
subsetRow(): return only the columns in select a,b.

func (db *DB) execSelect(stmt *StmtSelect) ([]Row, error) {
    schema, ok := db.tables[stmt.table]
    if !ok {
        return nil, errors.New("table is not found")
    }
    indices, err := lookupColumns(schema.Cols, stmt.cols)
    if err != nil {
        return nil, err
    }

    row, err := makePKey(&schema, stmt.keys)
    if err != nil {
        return nil, err
    }
    if ok, err = db.Select(&schema, row); err != nil {
        return nil, err
    }
    if !ok {
        return nil, nil
    }

    row = subsetRow(row, indices)
    return []Row{row}, nil
}

Execute INSERT, UPDATE, DELETE

Implement the other SQL statements by calling DB.Insert(), DB.Update(), and DB.Delete():

func (db *DB) execInsert(stmt *StmtInsert) (count int, err error)
func (db *DB) execUpdate(stmt *StmtUpdate) (count int, err error)
func (db *DB) execDelete(stmt *StmtDelete) (count int, err error)

Requirements:

If KV is updated, return count = 1, otherwise return 0.
Check that StmtXXX matches the Schema.

For UPDATE, we do no support updating the primary keys. UPDATE only changes the V part of KV. Updating a primary key would mean deleting the old key and inserting a new one, which is rarely needed.

ⓘ

CodeCrafters.io has similar courses in many programming languages, including build your own Redis, SQLite, Docker, etc. It’s worth checking out.

◄ prev contents next ►