finnow/finnow-api/source/util/sqlite.d

module util.sqlite;

import std.datetime;

import slf4d;
import handy_httpd.components.optional;
import d2sqlite3;

/**
 * Tries to find a single row from a database.
 * Params:
 *   db = The database to use.
 *   query = The query to execute.
 *   resultMapper = A function to map rows to the desired result type.
 *   args = Arguments for the query.
 * Returns: An optional result.
 */
Optional!T findOne(T, Args...)(Database db, string query, T function(Row) resultMapper, Args args) {
    Statement stmt = db.prepare(query);
    stmt.bindAll(args);
    ResultRange result = stmt.execute();
    if (result.empty) return Optional!T.empty;
    return Optional!T.of(resultMapper(result.front));
}

/**
 * Tries to find a single entity by its id, selecting all properties.
 * Params:
 *   db = The database to use.
 *   table = The table to select from.
 *   resultMapper = A function to map rows to the desired result type.
 *   id = The entity's id.
 * Returns: An optional result.
 */
Optional!T findById(T)(Database db, string table, T function(Row) resultMapper, ulong id) {
    Statement stmt = db.prepare("SELECT * FROM " ~ table ~ " WHERE id = ?");
    stmt.bind(1, id);
    ResultRange result = stmt.execute();
    if (result.empty) return Optional!T.empty;
    return Optional!T.of(resultMapper(result.front));
}

/**
 * Finds a list of records from a database.
 * Params:
 *   db = The database to use.
 *   query = The query to execute.
 *   resultMapper = A function to map rows to the desired result type.
 *   args = Arguments for the query.
 * Returns: A list of results.
 */
T[] findAll(T, Args...)(Database db, string query, T function(Row) resultMapper, Args args) {
    Statement stmt = db.prepare(query);
    stmt.bindAll(args);
    import std.algorithm : map;
    import std.array : array;
    return stmt.execute().map!(r => resultMapper(r)).array;
}

/**
 * Determines if at least one record exists.
 * Params:
 *   db = The database to use.
 *   query = The query to execute.
 *   args = The arguments for the query.
 * Returns: True if at least one record is returned, or false if not.
 */
bool exists(Args...)(Database db, string query, Args args) {
    Statement stmt = db.prepare(query);
    stmt.bindAll(args);
    return !stmt.execute().empty();
}

/**
 * Performs an update (UPDATE/INSERT/DELETE).
 * Params:
 *   db = The database to use.
 *   query = The query to execute.
 *   args = The arguments for the query.
 * Returns: The number of rows that were affected.
 */
int update(Args...)(Database db, string query, Args args) {
    Statement stmt = db.prepare(query);
    stmt.bindAll(args);
    stmt.execute();
    return db.changes();
}

/**
 * Deletes an entity from a table.
 * Params:
 *   db = The database to use.
 *   table = The table to delete from.
 *   id = The id of the entity to delete.
 */
void deleteById(Database db, string table, ulong id) {
    Statement stmt = db.prepare("DELETE FROM " ~ table ~ " WHERE id = ?");
    stmt.bind(1, id);
    stmt.execute();
}

/**
 * Wraps a given delegate block of code in an SQL transaction, so that all
 * operations will be committed at once when done. If an exception is thrown,
 * then the changes will be rolled back.
 * Params:
 *   db = The database to use.
 *   dg = The delegate block of code to run in the transaction.
 * Returns: The return value of the delegate, if the delegate does indeed
 * return something.
 */
T doTransaction(T)(Database db, T delegate() dg) {
    try {
        db.begin();
        static if (is(T : void)) {
            dg();
        } else {
            T result = dg();
        }
        db.commit();
        static if (!is(T : void)) return result;
    } catch (Exception e) {
        error("Rolling back transaction due to exception.", e);
        db.rollback();
        throw e;
    }
}

/**
 * Executes a "SELECT COUNT..." query on a database.
 * Params:
 *   db = The database to use.
 *   query = The query to use, which must return an integer as its sole result.
 *   args = Arguments to provide to the query.
 * Returns: The count returned by the query.
 */
ulong count(Args...)(Database db, string query, Args args) {
    Statement stmt = db.prepare(query);
    stmt.bindAll(args);
    ResultRange result = stmt.execute();
    if (result.empty) return 0;
    return result.front.peek!ulong(0);
}

/**
 * Reads an ISO-8601 UTC timestamp from a result row.
 * Params:
 *   row = The row to read from.
 *   idx = The column index in the row to read.
 * Returns: The timestamp that was read.
 */
SysTime parseISOTimestamp(Row row, size_t idx) {
    return SysTime.fromISOExtString(
        row.peek!(string, PeekMode.slice)(idx),
        UTC()
    );
}

/**
 * Reads a set of bytes from a result row.
 * Params:
 *   row = The row to read from.
 *   idx = The column index in the row to read.
 * Returns: The blob data that was read.
 */
immutable(ubyte[]) parseBlob(Row row, size_t idx) {
    return row.peek!(ubyte[], PeekMode.slice)(idx).idup;
}