bank_api.md

Bank API

Interface

Most functions can also return {error, unsupported} if the feature isn't supported in that database. For example, transactions or pubsub functionality.

start_pool(Ref, NbWorkers, PoolOpts) -> ok
stop_pool(Ref) -> ok

Start and stop a pool of workers. These functions cannot be used from inside a worker.

restart_pool(Ref, PoolOpts) -> ok

Restarts the pool, one worker at a time. This operation returns immediately and is performed asynchronously. Workers are restarted one by one, by first removing the worker from the ets table, sending a message telling it to stop, waiting for it to finish processing its requests until it can stop safely, and then starting a new worker to replace it.

The goal of this operation is to allow safe reconnection with new options while making sure we don't get any query ignored or failed, and also making sure we don't overflow the server with new connections (which is why we do one by one). This operation should take a few minutes at most.

query(Ref, Query) -> bank:result()
query(Ref, Query, Params) -> bank:result()
query(Ref, Query, Params, Opts) -> bank:result() | {async, AsyncRef}

Execute a query.

prepare(Ref, Stmt, Query) -> ok
prepare(Ref, Stmt, Query, Opts) -> ok
unprepare(Ref, Stmt) -> ok
unprepare(Ref, Stmt, Opts) -> ok

Prepare or forget a statement. These functions can only be used from inside a worker. This also means that they cannot be ran asynchronously.

execute(Ref, Stmt, Params) -> bank:result()
execute(Ref, Stmt, Params, Opts) -> bank:result() | {async, AsyncRef}

Execute a prepared statement.

batch(Ref, Fun) -> any()
batch(Ref, Fun, Opts) -> any() | {async, AsyncRef}

Execute a batch fun from in a worker context.

transaction(Ref, Fun) -> any()
transaction(Ref, Fun, Opts) -> any() | {async, AsyncRef}

Execute a batch fun inside a transaction in a worker context.

sub(Ref, Channel) -> ok
sub(Ref, Channel, Opts) -> ok | {async, AsyncRef}

Subscribe to Channel if the server supports notifications. This function cannot be used from inside a worker.

unsub(Ref, Channel) -> ok
unsub(Ref, Channel, Opts) -> ok | {async, AsyncRef}

Unsubscribe from Channel. This function cannot be used from inside a worker.

pub(Ref, Channel, Message) -> ok
pub(Ref, Channel, Message, Opts) -> ok | {async, AsyncRef}

Publish to Channel.

Pool options

{driver, BankDriver, DriverOpts}

Driver to be used. Example: {driver, bank_pgsql, {tcp, Host, Port, [{username, Username}, {password, Password}]}}.

{connect_retry, Time = 5000}

Time before reconnecting when the connection fails. Bank never gives up reconnecting.

{keepalive, Time = 10000}

Interval between pings.

{onconnect, Fun}

Fun that will be executed on connect.

Query options

These options are common to all kinds of queries.

async

Whether to return immediately. A ref AsyncRef is returned to identify the result of the query that will be sent as a message.

{timeout, Time = infinity}

Timeout before giving up on the query.

Asynchronous messages

• {bank, AsyncRef, ok, AffectedRows, InsertID}
• {bank, AsyncRef, rows, fin | nofin, Rows}: there might be more than one such message sent, fin indicates the last message
• {bank, AsyncRef, error, Reason, String}
• {bank, AsyncRef, notify, Message}

bank_driver_api.md

Bank Driver API

Drivers are required to not construct queries dynamically, to avoid issues such as SQL injections.

Drivers that don't support prepared statements, transactions or pubsub functionality MUST refuse to run them with {error, unsupported}.

Drivers for databases that don't include support for one-off queries with parameters can simulate it by creating a prepared statement and removing it right after.

The driver API is entirely asynchronous. Most functions of the API are used to manage the connection or send data to it, with one function used to translate the messages sent by the server. The state is responsible of making sure things happen in the right order if that is a requirement of the protocol.

connect(DriverOpts) -> State

Connect to a database.

close(State)

Close the connection to the database.

ping(State)

Ping the server.

query(Query, Params, State)

Execute a query.

prepare(Stmt, Query, State)

Prepare a statement.

unprepare(Stmt, State)

Forget a prepared statement.

execute(Stmt, Params, State)

Execute a prepared statement.

begin_transaction(State)

Start a transaction block.

commit_transaction(State)

Commit a transaction and close the transaction block.

rollback_transaction(State)

Rollback a transaction and close the transaction block.

fetch_all(State)

Fetch all rows returned by a previous query or statement. This function is only used from inside the worker when running batch jobs or transactions and are blocking the worker anyway.

parse(Data, State) - see below

Parse the data and return one of the following:

• more
• {ok, AffectedRows, InsertID}
• {rows, fin | nofin, Rows}
• {error, Reason, String}
• {notify, Message}

Driver options

Connection methods:

• {file, Filename, ConnOpts}
• {tcp, Host, Port, ConnOpts}
• {ssl, Host, Port, ConnOpts}
• {unix_socket, Path, ConnOpts}

ConnOpts may include:

• username as binary()
• password as binary()
• charset as atom() with a list common to all drivers, although not all of them might accept all charsets. Defaults to utf8.

bank_schema_api.md

Bank Schema API

This module is meant to make easier database schema manipulation across drivers. It can retrieve or modify a schema using a similar syntax and data types.

Introduction.md

List of databases we may be looking to support

• MariaDB
• Microsoft SQL Server
• MySQL
• Oracle Database
• PostgreSQL
• SQLite
• VoltDB

TODO

• An API still needs to be defined for modifying the schema of the database.
• Question: do we want to always connect to a database?

Just to be clear. Driver options would be the implementation specific options of a Bank driver? So usually this would be a [proplists:proplist()]. Looks good to me, but let me think about it for a while.

We want some driver options to be normalized across all drivers, if not common. For example, how to connect, the charset used, and such.

The driver options can be partially consolidated though there's some differences when using something like sqlite for example.

There's a pretty complete open source oci implementation in C here http://orclib.sourceforge.net/

Other SQL Frameworks

Java JDBC

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.Properties;

/**
 * Simple Example for JDBC and JavaSE 7
 */
public class JdbcExample {

    private static Logger log = LoggerFactory.getLogger(JdbcExample.class);

    public static void main(String[] args) throws Exception {

        // Register Driver with DriverManager
        Class.forName("com.mysql.jdbc.Driver").newInstance();

        /**
         * The scheme of the URL is used by the DriverManager
         * to distingush which loaded Driver will be used.
         * The URL itself is parsed by the Driver.
         *
         * A valid PostgreSQL URL for org.postgresql.Driver would look like:
         *
         * jdbc:postgresql:database
         * jdbc:postgresql://host/database
         * jdbc:postgresql://host:5432/database
         *
         * With PostgreSQL it is possible to supply connection properties
         * as url parameters.
         *
         * A valid MySQL and MariaDB URL for com.mysql.jdbc.Driver would look like:
         *
         * jdbc:mysql:///database
         * jdbc:mysql://host/database
         * jdbc:mysql://host:3306/database
         *
         * With MySQL it is possible to supply connection properties
         * as url parameters.
         *
         * A valid SQLite URL for org.sqlite.JDBC would look like:
         *
         * jdbc:sqlite:/home/me/my_database/database.db
         *
         * A valid VoltDB URL for org.voltdb.jdbc.Driver would look like:
         *
         * jdbc:voltdb://host:21212
         */
        String jdbcUrl = "jdbc:mysql:///test";

        /**
         * A lot of connection properties can be set here,
         * which are all dependant on which jdbc driver
         * you use.
         */
        Properties props = new Properties();
        props.setProperty("user", "user");
        props.setProperty("password", "secret");

        String insertSql = "INSERT INTO test (name) VALUES (?)";
        String selectSql = "SELECT id, name FROM test";

        /**
         * use try with resource, so the AutoClosable resource
         * gets safely closed after the try block is finished.
         */
        try (Connection con = DriverManager.getConnection(jdbcUrl, props)) {
            con.setAutoCommit(false);

            try (PreparedStatement ps = con.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS)) {
                ps.setString(1, "Fritz Tester");
                int affectedRows = ps.executeUpdate();
                try (ResultSet generatedKeys = ps.getGeneratedKeys()) {
                    if (generatedKeys.next()) {
                        log.info("Affected Rows: {}; Id: {}", affectedRows, generatedKeys.getInt(1));
                    }
                }
                con.commit();
            }
            try (
                    PreparedStatement ps = con.prepareStatement(selectSql);
                    ResultSet rs = ps.executeQuery()
            ) {
                while (rs.next()) {
                    log.info("id: {}, name: {}", rs.getInt(1), rs.getString(2));
                }
            }
        }

    }

}

There is no out of the box connection pooling for JDBC. Connection pooling is usually provided by an JEE application server like: TomEE, Glassfish or JBOSS. There are also Servlet Containers like Tomcat which provide connection pooling for resources.

The JDBC API also covers database meta-data in a uniform way.

Perl DBI

The same as the above but implemented in Perl using DBI

#!/usr/bin/perl -w

use DBI;
use strict;

# The driver is selected by DBI from the url. The installed drivers are searched
# for in the DBD package. Parameters are dependent on the DBD driver.
#
# PostgreSQL:
#
# dbi:Pg:dbname=test;host=localhost;port=5432
# dbi:Pg:dbname=test
#
# MySQL and MariaDB:
#
# dbi:mysql:test;host=localhost;port=3306
# dbi:mysql:test
#
# SQLite
#
# dbi:SQLite:dbname=/home/me/database.db
#
# VoltDB:
#
# I haven't found a driver.
#
my $dbh = DBI->connect("dbi:mysql:test;host=localhost;port=3306", "user", "secret",
        { AutoCommit => 0}) || die "Unable to connect to database: ${!}\n";
my $sth_insert = $dbh->prepare("INSERT INTO test (name) VALUES (?)");
my $sth_select = $dbh->prepare("SELECT id, name FROM test");

$sth_insert->bind_param(1, "Fritz Tester");
$sth_insert->execute;
printf("id: %s\n", $dbh->last_insert_id("test", "public", "test", "id"));
$dbh->commit;

$sth_select->execute;
while (my @row = $sth_select->fetchrow_array) {
        printf("%2d: %s\n", $row[0], $row[1]);
}

$dbh->disconnect;

There's also support for meta-data in DBI. No built in pooling is provided, but usually there are no long running processes in Perl, so there's no real need for it. Prepared statements can not be unprepared.

Haskell HDBC

I haven't tried to implement the above in Haskell. HDBC has drivers for MySQL, PostgreSQL, SQLite and ODBC.

@matthias-endler I worked a lot.

I like the API including the pool in this case. You almost never just want a single connection but... it would be nice to see a single connection api as well. That would be easy enough to create given the drivers have the same API.

The pool part in erlang is actually a little more interesting due to the lack of true shared memory besides ets.

Isn't single connection just a pool of one?