lwp001/erldb-driver

Linked-in driver of erlang that supports popular RDMBS like mysql, oracle, DB2 and sybase.

Introduction

The db_driver is a high performance database driver based on the Erlang linked-in driver. It uses asynchronous threads to avoid IO block during the database access, the same way in which Erlang asynchronous drivers were implemented. We followed the syntax of Erlydb (erlang_mysql_driver) to design the APIs. For one database access request, the parameters will be passed to driver and processed in asynchronous threads. Then the request will be translated to respective SQL statements for different database types. After that, the work threads will call vendors' C/C++ APIs to execute these SQL statements and return the responses with ei.

Now the driver supports MySQL, Oracle, Sybase, DB2 and Informix.

Design Purpose

We want to support most typical database systems, such as Mysql, Oracle, Sybase, DB2 and Informix, but we don't choose ODBC because of its poor performance.

Installation

In the db_driver directory, execute

$ ./configure [--with-mysql, --with-oracle, --with-sybase, --with-db2 or --with-informix]
$ make
$ sudo make install

Or you can select your drivers in rebar.conf

{drivers, [mysql, oracle]}.

and execute

$ ./rebar compile

Documentation

In the db_driver directory, execute

$ ./make_edoc
$ ./rebar doc

to generate Erlang API.

The Database-Driven Documentation generated by doxygen. If you installed doxygen, you can execute

$ doxygen priv/doc/db.doxyfile

to generated C API.

Open doc/index.html to view the erlang API document.

Connection Parameters

To use db_driver, you need to configure db_driver to make default database connection for database-driven start-up. The connection parametes likes:

PoolId = test,
ConnArgs =  [{driver, mysql},
             {database, "test"},
             {host, "localhost"},
             {user, "root"},
             {password, ""},
             {poolsize, 8}
             ].

where PoolId is the name of your connection instance, the type is atom.

The following is required parameters.

driver::atom()          Database type. Supported mysql, oracle and sybase.
database::string()      Database name.
host::string()          Database host name or IP address.
user:string()           Database user.
password:string()       Database password.
poolsize::integer()     Connection pool size.

The following is optional parameters.

port::integer()             %% Database port. Default is 3306.
default_pool::boolean()     %% Default Connection pool.
error_handler::{Mod, Fun}   %% Callback Function of error handler.

Getting Started

Starts db driver

%% Start db driver.
db_api:start().

%% Connection instance Id.
PoolId = 'test'.

%% Connection args.
ConnArg = [{driver, mysql},
           {database, "test"},
           {host, "localhost"},
           {user, "root"},
           {password, ""},
           {poolsize, 8},
           {default_pool, true}].

%% Add a connection pool.
db_api:add_pool(PoolId, ConnArg).

%% Execute sql string.
db_api:execute_sql("select version()").

%% If you didn't set the default pool flag, you can execute sql like this.
db_api:execute_sql("select version()", [{pool, PoolId}]).

%% Remove a connecttion pool.
db_api:remove_pool(PoolId),

%% Stop db driver.
db_api:stop().

If you set the dafault pool flag in several connection pools, the default pool is the last added pool.

Tests

See test cases in module basic_SUITE, module informix_SUITE and module oracle_SUITE.

Data Type

The following is the mapping of database data type to Erlang data type.

MySQL data type.

BIT             integer()
TINYINT         integer()
BOOL, BOOLEAN   integer()
SMALLINT        integer()
MEDIUMINT       integer()
INT             integer()
INTEGER         integer()
BIGINT          integer()
FLOAT           float()
DOUBLE          float()
FLOAT           float()
DECIMAL         float()
DATE            {date, {Year::integer(), Month::integer(), Day::integer()}}
DATETIME        {datetime,
                    {{Year::integer(), Month::integer(), Day::integer()},
                     {Hour::integer(), Minute::integer(), Second::integer()}}}
TIMESTAMP       {datetime,
                    {{Year::integer(), Month::integer(), Day::integer()},
                     {Hour::integer(), Minute::integer(), Second::integer()}}}
TIME            {time, {Hour::integer(), Minute::integer(), Second::integer()}}
YEAR            integer()
CHAR            integer()
VARCHAR         string()
BINARY          string()
VARBINARY       string()
TINYBLOB        binary()
TINYTEXT        string()
BLOB            binary()
TEXT            string()
MEDIUMBLOB      binary()
MEDIUMTEXT      string()
LONGBLOB        binary()
LONGTEXT        string()

Oracle data type.

STRING          string()
NUMBER          number()
DATE            {datetime,
                    {{Year::integer(), Month::integer(), Day::integer()},
                     {Hour::integer(), Minute::integer(), Second::integer()}}}
TIMESTAMP       {timestamp,
                    {{Year::integer(), Month::integer(), Day::integer()},
                     {Hour::integer(),
                      Minute::integer(),
                      Second::integer(),
                      Microseconds::integer()},
                     {TimeZoneOffsetInHours::integer(),
                      TimeZoneOffsetInMinutes::integer()}}}
TIMESTAMP_Z     {timestamp,
                    {{Year::integer(), Month::integer(), Day::integer()},
                     {Hour::integer(),
                      Minute::integer(),
                      Second::integer(),
                      Microseconds::integer()},
                     {TimeZoneOffsetInHours::integer(),
                      TimeZoneOffsetInMinutes::integer()}}}
TIMESTAMP_LZ    {timestamp,
                    {{Year::integer(), Month::integer(), Day::integer()},
                     {Hour::integer(),
                      Minute::integer(),
                      Second::integer(),
                      Microseconds::integer()},
                     {TimeZoneOffsetInHours::integer(),
                      TimeZoneOffsetInMinutes::integer()}}}
BINARY          binary()
CLOB            string()
NCLOB           string()
BLOB            binary()
INTERVAL_YM     {interval_ym, {Year::integer(), Month::integer()}}}
INTERVAL_DS     {interval_ds,
                    {Day::integer(),
                     Hour::integer(),
                     Minute::integer(),
                     Second::integer(),
                     FractionalSecondComponent::integer()}}

Sybase data type.

VARBINARY       binary()
BIT             integer()
CHAR            string()
VARCHAR         string()
UNICHAR         list()
UNIVARCHAR      list()
DATE            {date, {Year::integer(), Month::integer(), Day::integer()}}
TIME            {time,
                    {Hour::integer(),
                     Minute::integer(),
                     Second::integer(),
                     Millisecond::integer()}}
DATETIME        {datetime,
                    {{Year::integer(), Month::integer(), Day::integer()},
                     {Hour::integer(),
                      Minute::integer(),
                      Second::integer(),
                      Millisecond::integer()}}}
SMALLDATETIME   {smalldatetime,
                    {{Year::integer(), Month::integer(), Day::integer()},
                     {Hour::integer(), Minute::integer()}}}
TINYINT         integer()
SMALLINT        integer()
INT             integer()
BIGINT          integer()
DECIMAL         {number, string()}
NUMERIC         {number, string()}
FLOAT           float()
REAL            float()
MONEY           {number, string()}
SMALLMONEY      float()

Query Description

Supported Keyword

The following keywords are supported (mainly used in the select):

table: 'join', 'left, join', 'right, join', 'inner, join'.
fields: 'as', '+', '-', '*', '/', '.', 'function'.
where: 'and', 'or', 'not', 'like', 'between', 'in', '+', '-', '*', '/', '=', '!=', '>', '>=', '<', '<=', '.', 'function'.
extras: 'order', 'asc', 'desc', 'limit', 'group', 'having', 'and', 'or', 'not', 'like', 'between', 'in', '+', '-', '*', '/', '=', '!=', '>', '>=', '<', '<=', '.', 'function'.

Insert Field Type Description

insert function description:

insert(Table::atom(), [{Field::atom(), Value::term()}]).

Create table test1, "f + Data Types" as the field name, the sample insert statement is as follows:

db_api:insert(test1, [
        {fbit, 1},
        {ftinyint, 1},
        {fsmallint, 200},
        {fmediunint, 1323},
        {fint, 38524},
        {fbigint, 2233434},
        {ffloat, 238954.345},
        {fdouble, 335623.276212},
        {fdecimal, 45656.12},
        {fdate, {date, {2010, 3, 24}}},
        {fdatetime, {datetime, {{2010, 3, 24}, {11, 19, 30}}}},
        {ftimestamp, {datetime, {{2010, 3, 1}, {2, 10, 30}}}},
        {ftime, {time, {11, 45, 22}}},
        {fyear, 2010},
        {fchar, 97},
        {fvarchar, "test varchar"},
        {fbinary, "fjdsgjnkdgbdf dfdfg\r\n isfdk"},
        {fvarbinary, "dkjsor klsjfsdfj"},
        {ftinytext, "dsfgd"},
        {ftext, ";ljkdf"},
        {fmediumtext, "dfuyejksf"},
        {flongtext, "indtvdf"},
        {ftinyblob, <<"hg">>},
        {fblob, <<34,56,0,54,75>>},
        {fmediumblob, <<97,98,99,100>>},
        {flongblob, <<"sdfidsigyrertkjhejkrgweur3[5940766%^#$^&(;lgf khjfgh">>}
]).

Where Clause Syntax Description

Comparison Operators

{field, '=', WhereExpr}
{field, '!=', WhereExpr}
{field, '>=', WhereExpr}
{field, '<=', WhereExpr}

'+', '-', '*', '/'

{WhereExpr1, '+', WhereExpr2}
{WhereExpr1, '-', WhereExpr2}
{WhereExpr1, '*', WhereExpr2}
{WhereExpr1, '/', WhereExpr2}

AND

{'and', [WhereExpr]}

OR

{WhereExpr, 'or', WhereExpr}

NOT

{'not', WhereExpr}

LINK

{WhereExpr, 'like', WhereExpr}

BETWEEN

{WhereExpr, 'between', {WhereExpr1, WhereExpr2}}

IN

{WhereExpr1, 'in', [WhereExpr2]}

AS

{WhereExpr1, 'as', WhereExpr2}

Example:

db_api:select(test1,
    {'and', [{fint, '>', 10}, {fdate, '=', {date, {2010, 3, 24}}}]})

Option Syntax Description

pool

Database connection pool.

{pool, pool_name}

fields

select list of fields returned.

{fields, [id, name]}

distinct

Mysql syntax similar to the distinct , whether to remove duplicate records. The default is false.

{distinct, true}

extras

Expand list.

{extras, [{order, name}, {limit, 1}, {group, name}]}

order

Ordering the results.

{order, Field}
{order, {Field, asc}}
{order, {Field, desc}}
{order, [{Field1, asc}, {Field2, desc}]}

limit

Limit the number of records.

{limit, 10}
{limit, 10, 20}

group

The result set grouping.

{group, name}
{group, [id, name]}

having

The result set grouping condition.

{having, {fint, '>', 5}}