Skip to content

Instantly share code, notes, and snippets.

@essen
Last active December 18, 2015 20:39
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save essen/5841844 to your computer and use it in GitHub Desktop.
Save essen/5841844 to your computer and use it in GitHub Desktop.

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

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

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.

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?
@essen
Copy link
Author

essen commented Jul 26, 2013

Isn't single connection just a pool of one?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment