# Language Extension for Basic Storm

The SQL library also contains a language extension that is usable in Basic Storm. It allows writing SQL statements directly in Basic Storm. The language extension only supports a subset of the full range of SQL queries. On the other hand, since the language extension understands SQL, it is able to bridge some differences between different database engines.

In addition to providing a convenient API, the language extension is also able to type-check queries given that a declaration of the tables in the database is available. This declaration can also be used to perform limited automatic database migrations.

## Data Types

To allow maximum portability, the language extension currently only supports the following data types:

- `INTEGER`/`INT`
- `REAL`
- `TEXT` (behaves like `VARCHAR`)

These data types may optionally be suffixed by a size (e.g. `TEXT(10)`) to specify their size. As mentioned in the database interface, the size of `TEXT` specifies the maximum length, while the size for `INTEGER` and `REAL` specifies the size of the data type in bytes. Note that the behavior for numeric types differ from many databases (they use different integer types instead).

## Column Declarations

The database library differs a bit from SQL in how column declarations behave. This is partially to match what users of Basic Storm would expect, and partially to bridge differences between different databases.

First and foremost, columns are `NOT NULL` by default. Instead, columns that may be `null` are explicitly marked as `ALLOW NULL`. Secondly, primary keys are automatically incremented automatically. This behavior is in line with SQLite, that binds the primary key to a rowid-field that is automatically generated. However, it might be relevant to specify `AUTOINCREMENT` explicitly if the application requires that the primary keys need to be monotonically increasing (this is not guaranteed otherwise, since `AUTOINCREMENT` is not used on SQLite unless explicitly specified).

As such, the following modifiers are supported:

- `PRIMARY KEY`
- `ALLOW NULL`
- `UNIQUE`
- `AUTOINCREMENT` / `AUTO_INCREMENT`
- `DEFAULT <literal>`

It is also possible to specify multiple columns as a primary key by using `PRIMARY KEY(a, b)`.

## Query Syntax

The extension makes SQL queries usable as expressions in Basic Storm. Since there may be multiple database connections active at any one time, it is, however, necessary to specify which database connection to use. This can be done in one of two ways:

- Either by prefixing the query by `WITH <expr>:`, where `<expr>` is an expression that evaluates to the database connection to use.
- Or, the queries can be located (indirectly) inside a `WITH <expr> {}` block. Again, `<expr>` evaluates to the database connection to use. As with normal blocks in Basic Storm, the `WITH <expr> {}` block can also be used as a part of an expression.

The following queries are supported by the extension:

- `CREATE TABLE <name>(<columns>)` or `CREATE TABLE IF NOT EXISTS <name>(<columns>)`

  Creates a table. Columns are declared as described above. This expression evaluates to `void`.

- `CREATE INDEX <name> ON <table>(<columns>)` or `CREAET INDEX ON <table>(<columns>)`

  Creates an index on the specified columns. If the second form is used, a name is generated automatically. Evaluates to `void`.

- `DROP TABLE <table>`

  Discard a table. Evaluates to `void`.

- `INSERT INTO <table>(<columns>) VALUES (<values>)`

  Insert a row in a table. Values may be arbitrary expressions in Basic Storm. The language extension ensures that they are properly escaped. Evaluates to the primary key of the newly inserted row.

- `UPDATE <table> SET <column> = <value> WHERE <predicate>`

  Updates rows in a table. `<value>` may be an arbitrary Basic Storm expression. It is properly escaped. Evaluates to the number of rows updated.

- `DELETE FROM <table> WHERE <predicate>`

  Delete rows from a table. Evaluates to `void`.

- `SELECT <columns> FROM <table> WHERE <predicate>`

  Select rows from a table. `<columns>` may be `*` in which case all columns are selected. The query may also include joins and order by statements. Evaluates to a `Result` that can be used to retrieve the results. This makes it possible to use a `SELECT` statement in a for-loop.

Note that it is possible to use Basic Storm variables anywhere an SQL expression is expected (names of tables and columns can, however, not be parameterized). The language extension takes care to use prepared statements in these cases, so that SQL injections are not possible. It is also possible to embed arbitrary Basic Storm expressions using `${<expr>}`, either to disambiguate variable names, or to pre-compute values.

In addition to the standard queries mentioned above, the language extension provides a few additional constructs for convenience:

- `SELECT ONE <columns> FROM <table> WHERE <predicate>`

  Like `SELECT`, but only returns the first row as a `Maybe<Row>`. Useful when looking up data based on the primary key, for example. Like `SELECT`, supports `JOIN`, and `ORDER BY` as well.

- `COUNT FROM <table> WHERE <predicate>`

  Like `SELECT COUNT(*)` in standard SQL. As such, returns the number of matching rows a `SELECT` statement would have returned. Supports `JOIN`, but not `ORDER BY` (as it would be meaningless).

To illustrate how it can be used, consider the following example:

    use sql;

    void main() {
        SQLite db(); // Create an in-memory database.

        // Create a table to work with.
        WITH db: CREATE TABLE test(id INTEGER PRIMARY KEY, name TEXT);

        // We don't want to specify WITH db every time, so we use a block:
        WITH db {
            // Insert some data.
            Str value = "test2";
            INSERT INTO test VALUES (1, "test");
            INSERT INTO test VALUES (2, value);

            // Inspect the data:
            for (row in SELECT * FROM test WHERE id < 1) {
                print(row.toS());
            }
        }
    }

## Typesafe Queries

If a table declaration is provided, it is possible to create a typed connection that the database library can use to type-check SQL queries. As we shall see, this also makes it more convenient to extract data from SQL queries.

A database declaration is declared at top-level in a file. It consists of a number of table- and index declarations:

    use sql;

    DATABASE PetDB {
        TABLE person(
            id INTEGER PRIMARY KEY,
            name TEXT
        );

        TABLE pet(
            id INTEGER PRIMARY KEY,
            name TEXT,
            owner INTEGER
        );

        INDEX ON pet(owner);
    }

This declares a database with two tables, `persons` and `pet`, and an index on the `owner` column of `pet`. In Basic Storm, the database `PetDB` will appear as a type that represents the typed connection. This typed connection can be used instead of a `DBConnection` when using the language extension. It can be created as follows:

    SQLite untyped();     // Regular, untyped connection to an in-memory database.
    PetDB typed(untyped); // Create a typed version that uses the untyped connection.

When the typed connection is created, it will inspect the current state of the database (using `tables` and `schema`) and verifies that the database contains the expected tables and indices. If this is not the case, it automatically creates any missing tables, and attempts to automatically migrate any existing tables. The extent of the automatic migrations are, however, quite limited so to not accidentally destroy data. For example, the migration will happily create new tables, modify column attributes, and insert new columns that may either be `null`, or that have default values. It will, however, not attempt to remove columns as that may cause data loss.

Due to the automatic migration above, it is not possible to use `CREATE TABLE`, `DROP TABLE`, or `CREATE INDEX`, on a typed connection as that would violate the assumptions made by the library.

The type safety does, however, mean that the library can typecheck queries, and provide convenient access to elements. For example, consider the following function that inserts elements in the database:

    use sql;

    void insert(PetDB typed) {
        // The library knows that this is fine, since `id` is autoincrement.
        WITH typed: INSERT INTO person(name) VALUES ("Filip");

        // This is, however, not allowed since the types do not match:
        WITH typed: INSERT INTO person(name) VALUES (10);
    }

Similarly, for select statements, the library generates custom types that represent the result rows:

    use sql;

    Str? findPerson(PetDB typed, Nat personId) {
        if (row = WITH typed: SELECT ONE name FROM person WHERE id == personId) {
            // Note: We access the column by name here:
            return row.name;
        } else {
            return null;
        }
    }

This also works with joins. For example, to retrieve all pets for a particular person, one can do:

    use sql;

    // Note: Using "name" here is not ideal since it might not be unique,
    // but it illustrates how joins work:
    Array<Str> findPets(PetDB typed, Str name) {
        Array<Str> out;
        var result = WITH typed: SELECT * FROM person
            JOIN pet ON pet.owner == person.id
            WHERE person.name == name;
        for (row in result) {
            // Again, note that we access columns by name.
            out << row.pet.name;
        }
    }

A few final remarks on named access to columns:

- The type of the column corresponds to the one declared in the table declaration. If it might be null, it is wrapped in `Maybe<T>`.
- If names contain `.`, then the entire sub-table may be `null` in the case of `LEFT`, `RIGHT` or `OUTER` joins. This means that it is necessary to check entire sub-tables for `null` in these cases. If column aliases (the `AS` keyword) are used, this might lead to excessive `null`-checking, since it is not possible to express the constraint that column `a` is null exactly when column `b` is null in the Storm type system without creating a nested structure.

## SQL Extensions

The language extension supports the following extensions to the SQL standard. They are supported by all supported databases, regardless of whether the underlying database supports the extension or not. Note that the same is not true if the low-level interface is used.

- Abbreviating `INSERT INTO` as `INSERT`.
- Abbreviating `DELETE FROM` as `DELETE`.
- `INSERT ... RETURNING x`

  This causes the insert statement to produce the inserted columns as specified by `x` (or `*` for all of them). This extension is supported natively by all databases except for MySQL. For MySQL, the query is emulated as a transaction of two statements, which may have a performance penalty. Note that this extension is only available for typed connections.

- `UPDATE ... RETURNING x`

  This causes the update statement to produce all rows that were updated by the query. As above, `x` specifies the columns to return (or `*` for all). This extension is supported natively by all databases except MySQL and MariaDB. For those databases, the functionality is emulated using two queries in a transaction, which may impact performance. Note that this extension is only available for typed connections.

- `DELETE ... RETURNING x`

  This causes the delete statement to produce all rows that were removed by the query. As above, `x` specifies the columns to return (or `*` for all). This extension is supported natively by all databases except MySQL. For those databases, the functionality is emulated using two queries in a transaction, which may impact performance.

## Transactions

The syntax extension also provides custom syntax for managing transactions. This is done using a `TRANSACTION` block as in the example below. As with any other queries, the transaction block may either be prefixed with a `WITH <expr>:` to indicate which connection to use, or can be placed inside a `WITH <expr> {}` block.

    use sql;

    void transaction(DBConnection c) {
        WITH c {
            INSERT INTO test VALUES (1, "test");

            TRANSACTION {
                INSERT INTO test VALUES (2, "test2");
                INSERT INTO test VALUES (3, "test3");
            }

            // ...
        }
    }

In the example above, inserting values 2 and 3 are done as a part of a transaction, meaning that it is not possible to observe the row with ID 2 without also observing the row with ID 3. The transaction is automatically committed at the end of the transaction block. As with `sql.Transaction`, the transaction is automatically rolled back if an exception is thrown from the contents of the transaction block.

The syntax extension also provides the statements `COMMIT;` and `ROLLBACK;` to abort a transaction prematurely by rolling back or committing the transaction. For example, we can abort the transaction midway by using `ROLLBACK` in an if-statement to roll back the transaction if `condition` is false. Similarly to `break` in loops, execution will not continue past `ROLLBACK;` in the code below, so the row with id 3 will never be inserted if `condition` is true.

    use sql;

    void transaction(DBConnection c) {
        WITH c {
            TRANSACTION {
                INSERT INTO test VALUES (2, "test2");
                if (condition) {
                    ROLLBACK;
                }
                INSERT INTO test VALUES (3, "test3");
            }

            // ...
        }
    }

As with many other things in Basic Storm, `TRANSACTION` blocks can be used as expressions, and they return the value of the last expression in the block. Furthermore, `COMMIT` and `ROLLBACK` optionally accept an expression that will be "returned" from the `TRANSACTION` block (much like `return`). To illustrate this, we can extend the example above to evaluate to a value that we store in a value and print:

    use sql;

    void transaction(DBConnection c) {
        WITH c {
            Str result = TRANSACTION {
                INSERT INTO test VALUES (2, "test2");
                if (condition) {
                    ROLLBACK "Failure...";
                }
                INSERT INTO test VALUES (3, "test3");

                "OK";
            };

            print("Transaction done: ${result}");
        }
    }