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, integer, and real. These types may additionally have a size attached to them.

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.
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.
void close()
Closes the connection to the database.
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 Statements 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.

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) {
        print(row.getStr(0));
    }
}