mekao

This library will help you to construct SQL queries. It have no other facilities: no pools, no caching, no RDBMS specific code. Main assumption is that records are used to represent DB data.

ToC

1. Thesis
2. Basic usage
3. Install
4. Records
5. Selectors
6. Specs

Thesis

SQL is complex language. There are variety of weird cases when you will be not satisfied with query, generated by any tool. Sophisticated tools aimed to hide this complexity and eventually became as complex as SQL itself and even more: they substitute well defined SQL by vague DSL, add caching layers, do fancy type conversions.

Goals of this library:

• to cover most basic cases;
• to give you an ability to adjust generated query;
• to be embeddable into other libraries;
• to be vendor agnostic(generated SQL is good for any RDBMS, but could be tuned to match yours).

Usage

Suppose that we have table books in our SQL db:

Column	Type	Attributes
id	int	Primary key, read only
isbn	varchar
title	varchar
author	varchar
created	timestamp	Read-only

To begin to use mekao you'll need couple of things:

• make a record with same fields as in SQL table you are interested in;
• describe table in terms of mekao;
• write some general settings for all queries (like how exactly LIMIT queries are constructed for your RDBMS dialect, or how placeholders are looks like for you DB driver).

make a record

-record(book, {id, isbn, title, author, created}).

describe a table

-include_lib("mekao/include/mekao.hrl").

-define(TABLE_BOOKS, #mekao_table{
    name    =  <<"books">>,
    columns = [
        #mekao_column{name = <<"id">>, type = int, key = true, ro = true},
        #mekao_column{name = <<"isbn">>, type = varchar},
        #mekao_column{name = <<"title">>, type = varchar},
        #mekao_column{name = <<"author">>, type = varchar},
        #mekao_column{name = <<"created">>, type = datetime, ro = true}
    ]
}).

Pay attention: each field in #mekao_table{} must be at the same position as corresponding field in #book{} (like title column have 3rd position in both records).

write general settings

-define(S, #mekao_settings{
    %% our placeholders will look like:
    %% ... WHERE id = $1 AND isbn = $2
    placeholder = fun (_, Pos, _) -> [$$ | integer_to_list(Pos)] end
}).

glimpse of usage

fetch_book(SelectBook) ->
    {ok, #mekao_query{
        body = Q, types = Types, values = Vals
    }} = mekao:select(SelectBook, ?TABLE_BOOKS, ?S),
    {iolist_to_binary(Q), Types, Vals}.

update_book(SetBook, WhereBook) ->
    {ok, #mekao_query{
        body = Q, types = Types, values = Vals
    }} = mekao:update(SetBook, WhereBook, ?TABLE_BOOKS, ?S),
    {iolist_to_binary(Q), Types, Vals}.

%%... snip ...

{<<"SELECT id, isbn, title, author, created FROM books WHERE id = $1">>,
    [int], [1]
} = fetch_book(#book{id = 1, _ = '$skip'}),

{<<"SELECT id, isbn, title, author, created FROM books"
    " WHERE author LIKE $1">>, [varchar], [<<"%Joe%">>]
} = fetch_book(
    #book{author = {'$predicate', like, <<"%Joe%">>}, _ = '$skip'}
),

{<<"UPDATE books SET author = $1 WHERE id IS NULL">>,
    [varchar], [<<"Joe">>]
} = update_book(
    #book{author = <<"Joe">>, _ = '$skip'}, %% SET clause
    #book{id = undefined, _ = '$skip'}  %% WHERE clause
),

%%... snip ...

You definitely noticed '$skip' atom. When you construct record like this, every other field will have '$skip' as a value:

1> #book{id = 1, _ = '$skip'}.
#book{id = 1, isbn = '$skip', title = '$skip',
      author = '$skip', created = '$skip'}

This instructs mekao that you don't want to include other fields in query.

You may wonder about iolist_to_binary/1 trick. All queries generated by mekao have a type iodata(). This means there could be mixed strings, binaries, chars, nested lists of strings and so on. Some drivers do accept iodata(), others do not. This made in the sake of performance, it is up to application to convert this to any acceptable form.

Placeholders $1 and $2 were generated with help of user-defined #mekao_settings.placeholder function.

If you want to extend resulted query use mekao:prepare_* set of queries instead.

For more examples please see test/mekao_tests.erl.

Install

Add this to rebar.config

{deps, [
    {mekao, {git, "git://github.com/ddosia/mekao.git", {branch, "v0"}}}
]}.

Alternatively use hex.

Project follows SemVer versioning conventions. Backward incompatible changes will result in a new branch, named after MAJOR version, i.e. v0, v1, v2 and so on. Make sure that your project depends on particular branch and not on master.

Records

• #mekao_settings{}
• #mekao_table{}
• #mekao_column{}

Selectors

Selectors is a way to adjust WHERE clause. When you pass record to mekao each field may contain regular value, or special predicate term.

SQL	predicate
=	{'$predicate', '=', term()}
<>	{'$predicate', '<>', term()}
>	{'$predicate', '>', term()}
>=	{'$predicate', '>=', term()}
<	{'$predicate', '<', term()}
<=	{'$predicate', '<=', term()}
LIKE	{'$predicate', like, term()}
BETWEEN	{'$predicate', 'between', term(), term()}
IN	{'$predicate', in, [term(), ...]}
NOT	{'$predicate', not, predicate()}

Example:

DT1 = {{2013, 1, 1}, {0, 0, 0}},
DT2 = {{2014, 1, 1}, {0, 0, 0}},

mekao:select(
    #book{
        created = {'$predicate', between, DT1, DT2},
        _       = '$skip'
    }, ?TABLE_BOOKS, ?S
).
%% SELECT id, isbn, title, author, created FROM books
%% WHERE created BETWEEN $1 AND $2

see mekao:selector() type spec.