oceandrift.db.dbal.driver

User’s Guide

…or well, almost: Different database implementations will still use different SQL dialects for their queries.

For safety and simplicity reasons, the interface is built around the concept of so-called Prepared Statements. db.prepare returns a Prepared Statement compiled from the provided SQL code.

In those cases where you only want to modify the schema (DDL), talk to the DB server or similar and you don’t expect to retrieve an actual result, db.execute does the job.

Examples

Basic usage

// connection setup (driver-specific!)
DatabaseDriver db = new SQLite3();

// disconnect on exit
// alternative: you could also call .close() manually later instead
scope(exit) db.close();

// establish a fresh database connection
db.connect();

// use .connected to check whether there is an active connection
if (!db.connected) {
    writeln("Disconnected :(");
}

Accessing data

through prepared Statements

// prepare a new statement
Statement stmt = db.prepare("SELECT * FROM mytable WHERE id >= ? AND id < ?");

// cleanup on exit
scope(exit) stmt.close();

// set // dynamic parameters are 0-indexed, first param is zero (not one!)
stmt.bind(0, 200);
stmt.bind(1, 400);

// execute the prepared statement
stmt.execute();

// is there a result?
if (!stmt.empty) {
    // - yes
    Row row1 = stmt.front;
    writeln(row1);
}
else {
    // - no
    writeln("No data found");
}

// advance to the next row
stmt.popFront();

const s = (!stmt.empty) ? "a" : "no";
writeln("There is ", s, " 2nd row");

// bind'n'execute helper
stmt.executeWith(30, 90);

// loop over the result (Statement is an InputRange)
foreach(Row row; stmt) {
    const firstColumn = row[0];
    writeln("Entry found: ", firstColumn);
}

Transactions

// begin a new database transaction (assuming we’re connected to a transactional database!)
db.transactionStart();

// […] do stuff here

if (saveChanges)
    db.transactionCommit(); // either commit the current transaction…
else
    db.transactionRollback(); // …or roll it back

Prepared Statements

Most database access in oceandrift happens through Prepared Statements.

Prepared Statements are compiled before execution and allow the use of dynamic parameters (see: Stattement.bind). This implies there’s no need for manual quoting or escaping with regard to dynamic parameters. In such a prepared statement actual values are substituted by “placeholders” (usually in form of a question mark ? character).

Unlike in many other database libraries there’s no “vanilla” .query function. This also means there’s no need for a function to escape SQL string literals.

Driver Developer’s Guide

In order to add support for another database implementation/engine/whatsoever, create a new driver struct or class matching the DatabaseDriverSpec interface. Said interface only exists for documentation purposes. There is no need to actually have your driver class implement (“inherit from”) DatabaseDriverSpec. It might become private in the future anyway.

Connection setup should happen via its constructor, but do not establish a connection yet; that’s what the DatabaseDriverSpec.connect function/method is for.

Provide the current connection status via a DatabaseDriverSpec.connected function/method.

Clean connection shutdown happens through a call to DatabaseDriverSpec.close. Disconnect the underlying database connection there. Beware a user may call DatabaseDriver.connect later of course.

If the underlying database supports toggling “auto commit” mode, this functionality should be provided via DatabaseDriverSpec.autoCommit. There’s also a corresponding getter method.

Manual transaction control is done through:

• DatabaseDriverSpec.transactionStart

  – starts/begins a new transaction

  might commit a currently active transaction, just stick to your database’s active defaults; usually done by executing a SQL statement like BEGIN TRANSACTION;

• DatabaseDriverSpec.transactionCommit

  – commit/save the current transaction

  usually done by executing a SQL statement like COMMIT;

• DatabaseDriverSpec.transactionRollback

  – rollback/abort the current transaction

  usually done by executing a SQL statement like ROLLBACK;

For non-transactional databases you probably want to throw an Exception here.

DatabaseDriverSpec.execute is a method that takes any SQL statement that (usually) shouldn’t return any data (e.g. CREATE TABLE …) and immediatly executes it. Indicate errors by throwing an appropriate Exception. In other database libraries such functions would often return the number of affected rows or similar, oceandrift currently defines this function as a void one; might be subject to change.

DatabaseDriverSpec.prepare: compile the passed SQL statement and return a Prepared Statement. Preparation (compilation) errors should be indicated by throwing an appropriate Exception; You must not return null.

Prepared Statements (Driver)

Prepared Statements are represented by instances of Statement. Create a class that implements said interface and wrap your database specific statement implementation.

Dynamic Parameters (Driver)

Dynamic parameters are set via the Statement.bind overloads. If the underlying database clients expects zero(0)-indexed indices, substract 1 from the passed index number. For datatypes your database engine doesn’t support, convert them to alternatives where appropriate. Perhaps string can act as a last resort catch-all solution.

Statement Exeuction (Driver)

When Statement.execute is called, execute the prepared statement with the currently bound parameters. Make the first result row available as .front if available (otherwise .empty should evaluate to true).

Statements form an InputRange.

• Statement.front

  – each range element represents an individual row

• Statement.popFront advances the cursor to the next row.
• Statement.empty indicates whether .front has no further row loaded. For empty result sets this should be immediatly set to return true.

Regarding such query results:

• Convert column values to a compatible type of the DBValue tagged algebraic (“union”)
• Row is just a struct that wraps an array of DBValues. (This approach leads to better readable error messages than an plain alias.)

Similar to DatabaseDriverSpec there’s a Statement.close method. Finalize/close the underlying prepared statement and do any necessary cleanup. If possible, code this with the assumption that your users could forget to call it – because they eventually will.