# Database Interface

The generic database interface consists of the following interfaces that allow making queries to databases. The library also provides implementations for SQLite, MariaDB, and MySQL.

The classes in the database interface are not tied to any particular thread. They are, however, not designed to be used by multiple threads concurrently.

## Query Strings

To help users of the library account for differences between different databases, the library provides the `sql.QueryStr` class. The class can be thought of as a regular string, but some parts of the string have semantic information attached to them. For example, the `QueryStr` class knows that a certain part is supposed to be a placeholder, a name, or a type. It is then able to generate the proper escape sequences for the database that is used based on this information (even though SQL is standardized, not all databases follow the standard).

A `QueryStr` is created by using a `sql.QueryStrBuilder`. The builder class has the following members:

- `init()`

  Create.

- `void put(core.Str str)`

  Add a piece of a regular string.

- `void name(core.Str str)`

  Add a quoted name.

- `void placeholder()`

  Add a placeholder for prepared statements.

- `void autoIncrement()`

  Add "autoincrement" keyword.

- `void type(sql.QueryType type)`

  Add a generic type.

- `sql.QueryStr build()`

  Create the query string.

- `void clear()`

  Clear contents.

## Query Types

Different databases support different data types. As such, the library provides the type `sql.QueryType` that provides a generic representation of these types, and aims to provide some resilience against a database modifying a generic type into a more specific one.

The `QueryType` supports three types currently:

- `TEXT`

  Specifies text, either of unlimited size, or with a specified maximum length. If `TEXT` appears without a size, it generally maps to the `TEXT` datatype in databases. If specified with a size (e.g. `TEXT(10)`) it maps to `VARCHAR` or a similar datatype.

- `INTEGER`

  Specifies an integer type. The SQL plugin uses an optional size to specify different sizes of integers (rather than separate types). In particular, `INTEGER` without a size refers to a type that is at least 4 bytes wide. For smaller types, use `INTEGER(2)` for a 2-byte integer, and `INTEGER(8)` for an 8 byte integer. Some databases (e.g. MariaDB and MySQL) support `INTEGER(1)` and `INTEGER(3)` as well. These types are just converted to the next larger type on databases that don't support them, so they can be used there as well.

- `REAL`

  Specifies a floating-point type. Similarly to integers, `REAL(8)` can be used for a double-precision type.

The `QueryType` has the following members:

- `init()`

  Create a type representing "void".

- `sql.QueryType text()`

  Create the type TEXT.

- `sql.QueryType integer()`

  Create the type INTEGER.

- `sql.QueryType real()`

  Create the type REAL.

- `sql.QueryType parse(core.Str from)`

  Parse from string. Returns "void" if the type is unknown. Throws `SQLError` on malformed type specification.

- `sql.QueryType sized(core.Nat size)`

  Create a copy of the type with the specified size.

- `core.Maybe<core.Nat> size()`

  Get the size.

- `core.Bool sameType(sql.QueryType& o)`

  Check if the types are the same, ignoring the size.

- `core.Bool compatible(sql.QueryType& o)`

  Check if we are compatible with the parameter. Compatibility compares the size, but allows any size of `o`, assuming our size is not specified. Also allows 'o' to have a larger size than us.

- `core.Bool any()`

  Is there any type (i.e. not `void`).

- `core.Bool empty()`

  Is this type empty (i.e. `void`).

## Connection

The class `sql.DBConnection` represents a connection to a database. The class itself is abstract, so it is necessary to use the appropriate derived class to connect to a database.

The `DBConnection` class has the following members:

- `sql.Statement prepare(core.Str query)`

  Create a prepared statement. Throws an error if the statement is invalid.

- `sql.Statement prepare(sql.QueryStr query)`

  Create a prepared statement. Throws an error if the statement is invalid.

- `sql.Statement prepare(sql.CachedQuery cached)`

  Create a prepared statement from a CachedQuery. This causes the connection to prepare the query the first time it is seen, and use the cached version for all subsequent calls.

- `void close()`

  Closes the connection to the database. The base implementation finalizes all prepared statements associated with this connection.

- `core.Array<core.Str> tables()`

  Returns all names of tables in SQLite connection in an Array of Str.

- `core.Maybe<sql.Schema> schema(core.Str table)`

  Returns a Schema for a particular table.

### Database Types

The following classes can be created to connect to different databases:

- `sql.SQLite` - SQLite. Supports both in-memory databases and databases in files in the local file system.
- `sql.MariaDB` - MariaDB. Connects to a database as specified in the `Host` object (supports both TCP and UNIX sockets).
- `sql.MySQL` - MySQL. Connects to a database as specified in the `Host` object (supports both TCP and UNIX sockets).

## Statement

The `sql.Statement` class represents a prepared statement, that is a statement that possibly contains placeholders for values. The `Statement` class contains the `bind` function to allow binding values to the placeholders, and allows executing the query. In general, it is a good idea to retain the prepared statement for as long as possible, since this means that the database does not have to re-compile the query every time.

As mentioned above, parameters are bound using the `bind(Nat pos, T value)` overloads. There are also free functions that allow binding `Maybe<T>` for the supported types. Finally, there is `bindNull(Nat pos)` to explicitly set a placeholder to `null`.

Apart from `bind`, the `Statement` class has the following parameters:

- `sql.Statement.Result execute()`

  Execute the prepared statement with the currently bound parameters. Returns an iterator that can be used to retrieve the result. Any subsequent modification of the statement (e.g. modifying bound parameters or calling 'execute' again) invalidates the returned iterator.

- `void finalize()`

  Finalize (dispose of) the statement. It will not be usable again. Finalize is called automatically by the destructor of derived classes, but since finalization by the GC may not happen instantly, calling finalize manually can make resource reclamation quicker.

## Result

When a statement has been executed, it returns a `sql.Statement.Result` class that represents a cursor to the result. The result class fetches the result rows lazily, so that large result sets can be managed without running out of memory. This does, however, mean that it is not possible to modify the parameters bound to the `Statement` while iterating through a result produced by the same `Statement`. It is, however, possible to modify other `Statement`s while consuming the results from a statement (note: this might however cause some databases to cache all results, since the connection can not always be multiplexed).

The `Result` class has the following members:

- `core.Maybe<sql.Row> next()`

  Get the next row.

- `core.Int lastRowId()`

  Get the last inserted row ID.

- `core.Nat changes()`

  Get the number of changes made when INSERT, UPDATE or DELETE was executed.

- `void finalize()`

  Finalize the result prematurely.

It is possible to use a result inside a for-loop in Basic Storm due to the presence of the `next` (and `iter`) members.

Note that since the `Result` is a value, it is not necessary to call `finalize()`. This will be done automatically as soon as the `Result` (and all copies) go out of scope. It is, however, possible to finalize it prematurely in cases where that is more convenient.

## Row

Representation of a single row as a result from a database query.

Behaves similarly to a read-only array, but that may contain different types. The accessor functions that mention specific types throw if the type is incorrect. Type inspection is possible through the generic `at` function that creates a `Variant` (note: the at function is a free function for technical reasons).

Note: The class automatically casts between `Int` and `Long`, as well as `Float` and `Double` as required.

- `core.Nat count()`

  Get number of columns.

- `core.Str getStr(core.Nat index)`

  Get a column as a string.

- `core.Bool getBool(core.Nat index)`

  Get a column as a boolean.

- `core.Int getInt(core.Nat index)`

  Get a column as an integer.

- `core.Long getLong(core.Nat index)`

  Get a column as a long.

- `core.Float getFloat(core.Nat index)`

  Get a column as a float.

- `core.Double getDouble(core.Nat index)`

  Get a column as a double.

- `core.Bool isNull(core.Nat index)`

  Check if a column is null.

- `core.Variant at(sql.Row& row, core.Nat index)`

  Get a column. Creates a Variant, so is slower than using typed accessors.

## Transactions

The SQL library provides the class `sql.Transaction` to create and manage transactions on a database connection. The `Transaction` class is intended to be a resource management class, meaning that the transaction starts when an instance is created, and the transaction ends when it goes out of scope.

The default behavior is to roll back any side effects when the transaction goes out of scope. This means that if an exception causes execution to leave some piece of code (e.g. due to a database constraint error), the transaction is rolled back and the operation can be retried at a later time. To explicitly commit a transaction, call the `commit` member of the `Transaction` class.

The usage is illustrated below:

    void transaction(DBConnection c) {
        // Queries outside of a transaction.

        {
            Transaction t(c);

            // Queries that are a part of the transaction.

            t.commit(); // Commit the transaction
        }

        // Queries outside of a transaction.
    }

The `sql.Transaction` class has the following members:

- `init(sql.DBConnection c)`

  Start a new transaction. The transaction ends by rolling back any changes unless `commit` or `rollback` are called.

- `init(sql.DBConnection c, sql.Transaction.End end)`

  Start a new transaction, specifying how the class should handle behave if neither `commit` nor `rollback` are called.

- `void commit()`

  Commit the transaction now, overriding the default behavior specified in the constructor.

- `void rollback()`

  Rollback the transaction now, overriding the default behavior specified in the constructor.

The syntax extension also provides custom syntax to make it easier to manage transactions.

## Example

As an example, one can use the database library to make a simple query to an SQL database as follows:

    use sql;
    use core:io;

    void main() {
        SQLite db(cwdUrl / "file.db");

        var stmt = db.prepare("SELECT * FROM test WHERE id > ?;");
        stmt.bind(0, 18);

        for (row in stmt.execute()) {
            print(row.getStr(0));
        }
    }

## Connections

When connecting to a server-based database, it is necessary to specify how to connect to the database. The SQL library uses the type `Host` to represent different connection methods. It can be created as follows:

- `Host:local()`

  Use the default connection method to connect to a local database server. The exact semantics varies depending on the database. The benefit of this method is that it is typically does not require authentication since it can rely on the identity of the current system user instead.

- `Host:local(Str)`

  Connect to localhost using a custom name. On UNIX, the string is often the name of a UNIX domain socket.

- `Host:socket(Address)`

  Connect to another database on another machine, reached via the specified `Address`. Note that addresses can be created from the `core:net:toAddress` function or the `core:net:lookupAddress` function. See examples below.

For example, to connect to a MariaDB database on localhost, one can create a connection as illustrated below. The two parameters that are `null` are the username and the password. All of `MariaDB`, `MySQL`, and `PostgreSQL` allows omitting both, which causes them to use the username of the current system user and an empty password.

    MariaDB connection(Host:local(), null, null, "database");

For remote connections, ask the database to use a socket instead. For this, either use `core:net:toAddress` to parse an IPv4 or IPv6 address as follows:

    PostgreSQL connection(Host:socket(core:net:toAddress("192.168.0.1")), "user", "password", "database");

Or, use `core:net:lookupAddress` to resolve a textual address:

    MySQL connection(Host:socket(core:net:lookupAddress("localhost")), "user", "password", "database");