ErlyDB 0.7

In the past couple of weeks, I've been hacking away at ErlyDB and the libraries it uses (the Revamped MySQL driver, ErlSQL and Smerl). I added a bunch of features and optimizations and also refactored and/or rewrote most of the original ErlyDB code until I felt the picture looked good.

The last release was v0.1, but I decided to name this release v0.7 because ErlyDB getting pretty close to 1.0 quality. Plus, I just like the number 0.7 :)

Here are the main new features:

Transactions

Each generated module has the function transaction/1, which lets you execute a group of statements in a transaction against the module's driver. Here's an example:


person:transaction(
fun() ->
P = person:new("Menahem"),
P1 = person:save(P),
city:add_person(TelAviv, P1)
end).


Transactions are automatically rolled back if the function throws an error or crashes. For example, this function


person:transaction(
fun() ->
person:delete(P1),
exit(just_kidding)
end).


would cause the delete statement to be rolled back.

Note: all auto-generated CRUD operations are executed in transactions, so you can set your database's auto-commit option to 'false' and nothing will break.

Protection against SQL injection attacks

ErlyDB uses ErlSQL under the hood to automatically quote all string and binary values in SQL expressions. All functions that let the user define a Where condition expect an ErlSQL expression by default. Here are some examples:


person:find({name, '=', "Joe"}).



person:find(
{{name,'=',"Joe"}, 'or', {'not', {age, '<', 26}}}).


You can also use ErlSQL expressions for LIMIT and ORDER BY clauses, such as


person:find({name, like, "Joe%"},
[{order_by, [{age, desc}, country]},
{limit, 3, 4}]).


If you want to use string and/or binary expressions, you can define the {allow_unsafe_statements, true} option when calling erlydb:code_gen/3. This will tell ErlyDB to accept statements such as


person:find("name = 'Joe'").


and


person:find("name LIKE 'Joe%'", "LIMIT 17").


However, this usage is discouraged because it makes you more vulnerable to SQL injection attacks. If you turn this feature on, make sure to escape all your strings using erlsql:encode/1.

Hooks

You can implement in your modules the following functions, which ErlyDB will use as hooks for different operations:


after_fetch/1
before_save/1
after_save/1
before_delete/1
after_delete/1


Fine-grained control on field visibility

By implementing the fields/0 function in your modules, you can specify exactly which database fields ErlyDB will use for the module. For example, if you have the function


fields() -> [name, age, country].


in the module 'person', ErlyDB will only use those fields even if the database table has additional fields (the 'id' field is always assumed to be the primary key).

User-defined table assignment

You can implement the table/0 function to indicate which table ErlyDB should use as the database table for your module. For example, the function


table() -> person.


in the module 'artist' would tell ErlyDB to use the 'person' table in all SQL statements for the module 'artist'.

Multiple modules per table

You can implement the type_field/0 function to indicate which column in the database table holds the module type for each record. In combination with the table/0 function, this lets you store records for multiple modules in a single table. By implementing the fields/0 function, you can also control which fields are exposed for each module.

For example, suppose you have the table 'person' defined as


CREATE TABLE person (
id integer auto_increment primary key,
type char(10),
name varchar(30),
age integer,
country varchar(20),
office integer,
department varchar(30),
genre varchar(30),
instrument varchar(30),
created_on timestamp,
index(type)
)


You can create 3 modules for accessing this table as follows:

person.erl


-module(person).
-export([fields/0, type_field/0]).

fields() -> [name, age, country].

type_field() -> type.


employee.erl


-module(employee).
-export([table/0, fields/0, type_field/0]).

table() -> person.
fields() -> person:fields() ++ [office, department].
type_field() -> type.


musician.erl


-module(musician).
-export([table/0, fields/0, type_field/0]).

table() -> person.
fields() -> person:fields() ++ [genre, instrument].
type_field() -> type.


The created_on field will not be exposed to any of the modules.

Note: The fields/0 function isn't limited to defining supersets. You can define any relation you want, as long is it's expressible in Erlang :)

More auto-generated functions

In addition to the find(Where, Extras) and find_id(Id) functions, ErlyDB now generates find_max(Max, Where, Extras) for getting at most Max records, and find_range(First, Max, Where, Extras) for getting at most Max records starting from offset First. Each function has 4 variants, e.g.


find()
find(Where)
find_with(Extras)
find(Where, Extras)


Functions for finding related records also have such variants. Examples:


developer:projects(D)
developer:projects(D, Where)
developer:projects_with(D, Extras)
developer:projects(D, Where, Extras)

developer:projects_first(D)
developer:projects_first(D, Where)
developer:projects_first_with(D, Extras)
developer:projects_first(D, Where, Extras)

developer:projects_max(D, Max),
developer:projects_max(D, Max, Where),
developer:projects_max_with(D, Max, Extras)
developer:projects_max(D, Max, Where, Extras)

developer:projects_range(D, First, Max),
developer:projects_range(D, First, Max, Where),
developer:projects_range_with(D, First, Max, Extras)
developer:projects_range(D, First, Max, Where, Extras)


Aggregate functions

ErlyDB generates functions for getting aggregate data about records from a module. The functions currently supported are 'count', 'max', 'min', 'avg', 'sum' and 'stddev' (it's easy to add more functions to this list by changing aggregate_functions/0 in erlydb.erl). For example, you can make calls such as


person:max(age).
employee:count('distinct name').
season:avg(temperature).
city:sum(population, {country,'=',"USA"}).


to get aggregate values from the database. The 'count' function has a special version that takes no arguments.


person:count().


is equivalent to


person:count('*').


In addition, ErlyDB lets you query aggregate data about related records (both in one_to_many and many_to_many relations). Examples:


developer:sum_of_projects(Joe, language).
apple:avg_of_oranges(MyApple, color,
{{state,'=',"ripe"},'and',{size, '>', 3}}).


The special form of 'count' also exists for related records:


language:count_of_developers(Erlang).


Better error handling

In the last version of ErlyDB, functions returned {ok, Result} or {error, Err}. In version 0.7, I changed it so functions return Result or they crash by calling exit(Err). This makes it easier to execute a group of statements and let the enclosing function trap all the errors (it also follows the Mnesia API more closely).

Multiple drivers, multiple databases in one session

The last version of ErlyDB supported only one driver per session. In version 0.7, each module can have a different driver. In addition, each module can have a list of driver options defined when calling erlydb:code_gen/3. All generated functions pass these options to the driver. For example, the MySQL driver accepts the {pool_id, Val} option, which defines against which connection pool ErlyDB should execute SQL statements. This allows you to work with multiple MySQL databases in one ErlyDB session.

Efficient SQL generation

In the last version, ErlyDB created SQL statements by string concatenation. In version 0.7, ErlyDB uses ErlSQL to generate statements as iolists of binaries, which are more efficient than strings because they consume less memory.

Many internal improvements

I rewrote or refactored most of the code from the alpha version. I also optimized the code generation process so it's much faster now.

That's it :)

You can get the distribution from branches/v0.7 in the subversion repository.

Note: ErlyDB requires the Revamped MySQL Driver v0.9.5 or above, which you can get from the Subversion repository under branches/v0.9.5. This driver is based on the original MySQL driver from the YXA project with a improvements I made such as support of transactions, prepared statements, binary queries and more efficient connection pooling. Once the changes I made to this driver are integrated back into the YXA source repository, the Revamped driver will no longer be supported. This is to ensure that the Erlang community doesn't have to deal with multiple forks of the same driver.

Please let me know if you find any bugs or if you have any suggestions.

Mnesia? MySQL? It's The Same Transaction!

I have a confession: I didn't like my last attempt at adding transaction support to the Revamped MySQL Driver. Don't get me wrong -- it's better to have some transaction support in the driver than none, but the approach I took had a couple of shortcomings. One was minor and one major.

The minor shortcoming was that the transaction handling added a non-trivial level of complexity to the API due to its introduction of a few new functions such as new_transaction(), add_statement(), add_execute() and commit(). This made the MySQL transaction API much less elegant than the Mnesia transaction API, which, in its simplest form, boils down to a single function -- mnesia:transaction/1.

As an example, compare


T = mysql:new_transaction(pool_id),
T1 = mysql:add_query(T,
<<"INSERT INTO person(name) VALUES ('bob')">>),
Res = mysql:commit(T1).


to


mnesia:transaction(fun() -> mnesia:write(Bob) end).


Even ignoring the ugliness of the SQL statement, it's easy to see that the Mnesia example is much clearer.

My attempt at simplifying the MySQL transaction handling by making it possible to write the above example as


mysql:transaction(pool_id, fun(T) ->
add_query(T,
<<"INSERT INTO person(name) VALUES ('bob')">>)
end)


was in improvement, but it still added more complexity to the driver's API than the Mnesia's single transaction function.

Putting aesthetic concerns aside for a second, having a transaction API that's different from Mnesia's would make my life hard when implementing different ErlyDB drivers that must conform to the same transaction specifications. Having a consistent approach to transactions in all drivers was quite important for ErlyDB.

The bigger shortcoming with the original transaction interface in the MySQL driver was that although it was possible to execute a sequence of INSERT, UPDATE and DELETE statements in a transaction, it was impossible to execute SELECT statements in a transaction and actually do something with the results. For example, it was impossible to do the equivalent of


mysql:fetch(p1, <<"BEGIN">>),
mysql:fetch(p1,
<<"INSERT INTO person(name) VALUES ('bob')">>),
Res = mysql:fetch(p1,
<<"SELECT last_insert_id()">>),
[[Id]] = mysql:get_result_rows(Res),
mysql:fetch(p1,
["UPDATE person SET name='Jane' WHERE id=",
integer_to_list(Id)]),
mysql:fetch(p1, <<"COMMIT">>).


Even worse, if you had more than one connection in the connection pool, there was no way at all to implement the above transaction because the connection pooling algorithm would execute each statement in a different connection!

Ouch.

I basically ran into 2 problems: in Erlang, functions are stateless. There was no way for mysql:fetch to know that it's part of a transaction unless it was given a parameter that would inform it of this fact. (This parameter could either be a transaction record, as in the original approach, or a continuation, letting the user implement transactions in continuation passing style.) In addition, the transaction function executed in the client process, but each SQL statement was executed in the process of a connection chosen by the dispatcher. The only way to execute a group of statements in one connection is to send them to the dispatcher in a batch.

The first problem caused the API complexity; the second problem made using results from SELECT statements inside a transaction impossible.

Fortunately, I was able to find work-arounds to both problems.

To tackle the first issue, I dug deep into my memory of obscure Erlang documentation I had read. In the Advanced Topics section of the Erlang course is a short section on the process dictionary. In Erlang, each process has a mutable data structure called the process dictionary. The process dictionary allows you to 'cheat' by storing in it values and later retrieving them in other functions without passing those values as explicit parameters. This feature is generally discouraged because it makes your code less readable and it potentially introduces obscure bugs. (It's interesting how in most programming languages, mutable data is a fact of life, whereas functional languages such as Erlang and Haskell teach you to avoid it as much as possible :) ). I have never used the process dictionary before, and I will do my best to refrain from using it again, but for this specific feature, it was very useful.

Detour: It is actually possible to emulate the process dictionary in a "clean" way using an Erlang process that maintains state for all other processes. To get and set the state for the current process, you would make calls such as


State = gen_server:call(state_server,
{get_state, self()}).


and


gen_server:cast(state_server,
{set_state, self(), State})


(gen_server:call is blocking and get_server:cast is non-blocking).

The main disadvantages with this approach is that it involves a higher overhead due to message passing and that the state server needs to monitor other processes using erlang:monitor in order to garbage-collect state data belonging to processes that have died or crashed.

Back to the MySQL driver: With the process dictionary, mysql:fetch and mysql:execute could know that they were called inside a transaction, which made it possible to remove the mysql:add_query and mysql:add_execute functions. However, I still faced the problem that each call to mysql:fetch and mysql:execute would go to a different connection because of the connection pooling mechanism.

Thankfully, Erlang let me solve this problem quite elegantly: send the transaction function to the connection's process via the dispatcher, execute the function in the connection's process, and then send the result of the function to the calling process!

(For veteran Erlangers, this is probably far from groundbreaking, but for me, the notion of sending a function to different process that would execute it and send back the result is pretty cool. This is another small way in which Erlang has changed the way I think about programming.)

This is the solution I implemented in the MySQL driver. When you call mysql:transaction(ConnPool, Fun), the Fun parameter is sent to the dispatcher, which picks the next connection from the connection pool and then sends the Fun to the connection's process. The connection process puts a flag in the process dictionary marking the existence of a transaction context, and then executes the Fun. Calls to mysql:fetch and mysql:execute inside the Fun pick up the transaction context, and instead of sending their SQL statements to the dispatcher, they call mysql_conn functions directly. This ensures that all statements in a transaction are executed in the same connection.

With this new approach, the MySQL driver now lets you write transaction handling code in the style of the Mnesia API. The new way of writing the above example is:


mysql:prepare(insert_developer,
<<"INSERT INTO developer(name) VALUES (?)">>),
mysql:transaction(conn_pool,
fun() ->
mysql:execute(insert_developer, ["Bob"]),
{data, Res} =
mysql:fetch(<<"SELECT last_insert_id()">>),
[[Id]] = mysql:get_result_rows(Res),
mysql:fetch(
[<<"UPDATE developer SET name='Jane' WHERE id=">>,
integer_to_list(Id)])
end).


Two notes about this example:

• The PoolId parameter is no longer necessary in calls to mysql:fetch and mysql:execute when they are called inside a transaction. For consistency, you can keep the parameter in the calls -- it'll just be ignored.


• The last call to mysql:fetch demonstrates the use of iolists, which are more efficient than string concatenation in certain cases, such as when you're sending the result to a socket.

I made another change to the MySQL driver besides the transaction API change: inspired by Joe Armstrong's last article about transactional memory, I added versioning to prepared statements. In the last version, when a prepared statement changed, the dispatcher would scan all connections and the ones that have marked it as prepared would be updated. This solution is fine as long as there aren't too many database connections and the set of prepared statements is stable, but it doesn't scale too well.

In the latest version of the driver, I made it so when mysql:execute sends a request to the dispatcher, the dispatcher checks what the latest version of the statement is in its memory and then forwards to the request to execute the prepared statement with the given version to a connection from the connection pool. If the connection hasn't prepared the statement or if the prepared version of the statement in the connection is lower than the one in the dispatcher's request, the connection would request the latest version of the statement from the dispatcher. This ensures that all connections execute the latest version of each statement while keeping communications to a minimum.

(Things actually get more complicated when a call to mysql:execute is made in a transaction, but I'll spare you such messy details :) )

I put the new version for the driver in branches/v0.9. The old version is in branches/v0.8. Please get the latest version, give it a test drive and let me know if you find any problems (I tested it myself and it looks pretty stable).

Now that the MySQL driver looks solid, I can finally go back to ErlyDB :)

Update: I forgot to mention that transactions are automatically rolled back if the Fun returns or throws {error, Err}. For instance,


mysql:transaction(conn_pool1,
fun() ->
mysql:fetch(<<"DELETE FROM customer">>,
throw({error, just_kidding})
end).


would have no impact on the data because cause the transaction will be rolled back.

Revamped MySQL Driver Update

I made a number of changes to the Revamped MySQL Driver code. These are the major items:

• I rewrote much of the prepared statement handling logic. I improved it by adding versioning to prepared statements. I also pushed much of the logic to mysql_conn.erl.


• I simplified the transaction API in the MySQL driver to make it similar to the Mnesia transaction API. I will write a more complete article describing the reasoning as well as how to use the new API tomorrow or the day after. For now, you can read the documentation in the code or look at the test/mysql_test.erl.

The new code is in trunk. The old code is in braches/v0.8.

More details are coming.

New ErlSQL Feature: Lisp-style Operator Expansion

When you first write a library, you really don't know how useful it can be. You often discover new ways of enhancing it and making it more powerful only when you start using it. This has been the case with almost every library I wrote: Smerl, ErlyDB, and now ErlSQL.

While I was using ErlSQL to hack ErlyDB's internals, I realized what a pain it is to use the SQL way of writing repeated expressions that use the same binary operator, e.g. {{{a,'=',b}, 'and', {c,'=',d}}, 'and', {e,'=',f}}. Lisp handles such situations much more elegantly than SQL (I know, it's hard to believe that anything could be more elegant than SQL, but what do you know :) ). In Lisp, you would write (and (= a b) (= c d) (= e f)), which spares you having to write the 'AND' operator for each new element in the list.

(Please forgive me if I made any egregious Lisp syntax errors in this example. I haven't touched Lisp since college and my knowledge of it is very rusty :) )

The Lisp way is often much more concise than the SQL way, so I implemented a Lisp-style operator expansion feature in ErlSQL.

Here are a few examples of how to use this feature:


{select,'*',{from,foo},
{where,{a,'=',{'+',[1,2,3]}}}} ->
"SELECT * FROM foo WHERE (a = 1 + 2 + 3)"

{select,'*',{from,foo}
{where,{'=',[{'+',[a,b,c]},{'+',[d,e,f]}]}}} ->
"SELECT * FROM foo WHERE a + b + c = d + e + f"

{select,'*',{from,foo},
{where,
{'and',[{a,'=',b},{c,'=',d},{e,'=',f}]}}} ->
"SELECT * FROM foo WHERE (a = b) AND (c = d) AND (e = f)"


Any expression which is a tuple where the first item is an operator and the second item is a list will be expanded by ErlSQL in a similar fashion.

Introducing ErlSQL: Easy Expression and Generation of SQL Statements in Erlang

A couple of days ago, I started refactoring some of the code generating SQL statements in ErlyDB. My intention was to modularlize the SQL generation in ErlyDB to make it more flexible and reusable. As I added more and more functions, I suddently found myself with a new domain specific embedded language for expressing SQL statements in Erlang on my hands :) I named this language ErlSQL (ESQL in short).

I created small library for generating literal SQL statements from ESQL expressions. Given that this capability can be useful outside of ErlyDB (for instance, if you are using the Revamped MySQL driver or the Postgres driver from Jungerl directly), I decided to create a new project for this library. ErlSQL now lives at http://code.google.com/p/erlsql.

ErlSQL's main benefits are:

• Easy dynamic generation of SQL queries from Erlang for application developers.


• Prevention of most, if not all, SQL injection attacks by assuring that all string values are properly escaped.


• Integration with higher level libraries such as ErlyDB

ErlSQL covers a large subset of the SQL language and its extensions, including most CREATE, UPDATE, DELETE and SELECT queries. ESQL supports field and table aliases ("foo AS bar"), complex WHERE expressions, ORDER BY and LIMIT clauses, nested queries, function calls, unions, aggregate expressions (using GROUP BY and HAVING clauses), and more.

ErlSQL will gain more capabilities over time. If it's missing a feature you need urgently, it should be fairly straightforward for you to add it youself.

erlsql.erl only has 2 exported functions: sql/1 and sql/2. sql/1 takes an ESQL expression and returns an iolist (a tree of strings and binaries that you can send directly to a socket). sql/2 takes an additional boolean paramemter indicating if the result should be converted to a single binary (via iolist_to_binary/1) or not.

Here are some examples of ESQL expressions and their corresponding SQL queries:


{insert,project,[{foo,5},{baz,"bob"}]} ->
"INSERT INTO project(foo,baz) VALUES (5,'bob')"

{insert,project,[foo,bar,baz],[[a,b,c],[d,e,f]]} ->
"INSERT INTO project(foo,bar,baz) VALUES
('a','b','c'),('d','e','f')"

{insert,project,[foo,bar,baz],[{a,b,c},{d,e,f}]} ->
"INSERT INTO project(foo,bar,baz) VALUES
('a','b','c'),('d','e','f')"

{update,project,[{foo,5},{bar,6},{baz,"hello"}]} ->
"UPDATE project SET foo=5,bar=6,baz='hello'"

{update,project,[{started_on,{2000,21,3}}],{name,like,"blob"}} ->
"UPDATE project SET started_on='2000213'
WHERE (name LIKE 'blob')"

{delete,project} ->
"DELETE FROM project"

{delete,project,{a,'=',5}} ->
"DELETE FROM project WHERE (a = 5)"

{delete,developer,{'not',{{name,like,"%Paul%"},
'or',{name,like,"%Gerber%"}}}} ->
"DELETE FROM developer
WHERE NOT ((name LIKE '%Paul%') OR (name LIKE '%Gerber%'))"

{select,["foo"]} ->
"SELECT 'foo'"

{select,["foo","bar"]} ->
"SELECT 'foo','bar'"

{select,{1,'+',1}} ->
"SELECT (1 + 1)"

{select,{foo,as,bar},{from,{baz,as,blub}}} ->
"SELECT foo AS bar FROM baz AS blub"

{select,name,{from,developer},
{where,{country,'=',"quoted ' \" string"}}} ->
"SELECT name FROM developer
WHERE (country = 'quoted \\' \\\" string')"

{select,[{{p,name},as,name},{{p,age},as,age},{project,'*'}],
{from,[{person,as,p},project]}} ->
"SELECT p.name AS name,p.age AS age,project.*
FROM person AS p,project"

{select,{call,count,name},{from,developer}} ->
"SELECT count(name) FROM developer"

{{select,name,{from,person}},
union,
{select,name,{from,project}}} ->
"(SELECT name FROM person) UNION "
"(SELECT name FROM project)"

{select,distinct,name,{from,person},{limit,5}} ->
"SELECT DISTINCT name FROM person LIMIT 5"

{select,[name,age],{from,person},{order_by,[{name,desc},age]}} ->
"SELECT name,age FROM person ORDER BY name DESC,age"

{select,[{call,count,name},age],{from,developer},{group_by,age}} ->
"SELECT count(name),age FROM developer GROUP BY age"

{select,[{call,count,name},age,country],
{from,developer},
{group_by,[age,country],having,{age,'>',20}}} ->
"SELECT count(name),age,country
FROM developer GROUP BY age,country HAVING (age > 20)"

{select,'*',{from,developer},{where,{name,in,["Paul","Frank"]}}} ->
"SELECT * FROM developer WHERE name IN ('Paul','Frank')"

{select,name,
{from,developer},
{where,{name,in,
{select,distinct,name,{from,gymnist}}}}} ->
"SELECT name FROM developer WHERE name IN
(SELECT DISTINCT name FROM gymnist)"

{select,name,
{from,developer},
{where,{name,in,
{{select,distinct,name,{from,gymnist}},
union,
{select,name,
{from,dancer},
{where,{{name,like,"Mikhail%"},
'or',
{country,'=',"Russia"}}}},
{where,{name,like,"M%"}},
[{order_by,{name,desc}},{limit,5,10}]}}}} ->
"SELECT name FROM developer
WHERE name IN (
(SELECT DISTINCT name FROM gymnist)
UNION
(SELECT name FROM dancer
WHERE ((name LIKE 'Mikhail%') OR (country = 'Russia')))
WHERE (name LIKE 'M%') ORDER BY name DESC LIMIT 5,10)"


Making ErlSQL was another lesson to me of the great expressiveness gained by the combination of of tuples, lists and pattern matching. It would be quite frustrating for me to use a language that doesn't have such semantics if I were ever forced to do so :)

In conclusion: if it looks like SQL and it acts like SQL, it must be... Erlang! :)

Update: There seems to be some confusion regarding the utility of ErlSQL, so I'll take another shot at explaining it. Many applications and libraries generate SQL queries programatically based on certain rules. ErlyDB is such library. Given a domain model and a set of relations, ErlyDB generates SQL queries for interacting with the data for this domain. The current implementation of ErlyDB (v0.1) does this by string concatenation, which is rather inefficient and error prone. ErlSQL facilitates writing SQL generation code in ErlyDB in a safe and efficient manner and by interacting directly with the semantics of the SQL language in Erlang. This is primarily why I made ErlSQL: I wanted to make SQL generation in ErlyDB more robust and elegant than string concatenation.

The second motivation is that for certain applications, the statements that ErlyDB generates automatically are not sufficient. Application developers often need to add their own WHERE framents and sometimes even write full queries to support the application requirements. I wanted to give developers the tools to write dynamic queries in a manner that's resilient to SQL injection attacks but without losing the full flexibility and familiarity of the SQL language and without stepping too far outside of Erlang.

Plus, it's neat to have the IDE indent your clauses and balance all your paretheses :)

Another note: Ulf Wiger pointed out to me on the mailing list that using Dialyzer, it may be possible to check the validity of the ErlSQL expressions in compile time. I haven't tried it, though.

Erlang MySQL Driver, Reloaded

In my quest to make ErlyDB a top-notch database abstraction tool (since I'm planning on using it myself, I wouldn't want it to be any less :) ), I have spent the past few days improving the Yxa MySQL driver for Erlang that I obtained from Process One to bring it up to par with ErlyDB's needs. I ended up rewriting much of the driver's internals as well as adding a bunch of features and optimizations.

I created a Google Code project for this revamped driver, where I will make any future changes and bug fixes. You can access it at http://code.google.com/p/erlang-mysql-driver/.

(Btw, I even resisted getting pulled into a prolonged blog debate about Erlang's merits as a web development language as opposed to Ruby. Astonishingly, it looks like my last attempt at brainwashing all developers into becoming Erlang devotees has produced mixed results! I guess it's time to think about the next escalation in the propaganda war :) )

Here's a summary of my changes:

• Added support for transactions


• Added support for prepared statements (you can execute prepared statement in and outside of a transaction)


• Added support for binary queries (this is the preferred way of sending queries because it's more efficient than using strings)


• Rewrote the connection pooling mechanism to make it more efficient.


• Numbers and date/time values in result sets are automatically converted to their Erlang equivalents. Strings are treated as binaries.


• Logging statements can now be instantiated lazily based on the desired logging level. This significantly reduces the overhead of logging, especially when debug level logging is disabled.


• Other internal modifications.

Following is a short tutorial illustrating how to use the new features.

Create a MySQL database called "test" and the run the following script:


create table developer(
id integer auto_increment primary key,
name varchar(30),
country varchar(20),
created_on timestamp)
type=InnoDB;


(Setting the table type to InnoDB in necessary for transaction support.)

Here's some Erlang code showing how to use some of the new APIs.


%% Start the MySQL dispatcher and create the first connection
%% to the database. 'p1' is the connection pool identifier.
mysql:start_link(p1, "localhost", "root", "password", "test"),

%% Add 2 more connections to the connection pool
mysql:connect(p1, "localhost", undefined, "root",
"password", "test", true),
mysql:connect(p1, "localhost", undefined, "root",
"password", "test", true),

mysql:fetch(p1, <<"DELETE FROM developer">>),

mysql:fetch(p1, <<"INSERT INTO developer(name, country) "
"VALUES "
"('Claes (Klacke) Wikstrom', 'Sweden'),"
"('Ulf Wiger', 'USA')">>),

%% Execute a query (using a binary)
Result1 = mysql:fetch(p1, <<"SELECT * FROM developer">>),
io:format("Result1: ~p~n", [Result1]),

%% Register a prepared statement
mysql:prepare(update_developer_country,
<<"UPDATE developer SET country=? where name like ?">>),

%% Execute the prepared statement
mysql:execute(p1, update_developer_country, [<<"Sweden">>,
<<"%Wiger">>]),

Result2 = mysql:fetch(p1, <<"SELECT * FROM developer">>),
io:format("Result2: ~p~n", [Result2]),

%% Make some statements
S1 = <<"INSERT INTO developer(name, country) VALUES "
"('Joe Armstrong', 'USA')">>,

S2 = <<"DELETE FROM developer WHERE name like 'Claes%'">>,

%% Create a transaction
T1 = mysql:new_transaction(p1),
T2 = mysql:add_query(T1, S1),

%% You can execute prepared statements inside transactions
T3 = mysql:add_execute(T2, update_developer_country,
[<<"Sweden">>, <<"%Armstrong">>]),

T4 = mysql:add_query(T3, S2),
mysql:commit(T4),

Result3 = mysql:fetch(p1, <<"SELECT * FROM developer">>),
io:format("Result2: ~p~n", [Result3]),

%% Another way of doing a transaction
S3 = <<"DELETE FROM developer WHERE country='USA'">>,
mysql:transaction(p1, fun(T) ->
mysql:add_queries(T, [S1,S2,S3])
end),

Result4 = mysql:fetch(p1, <<"SELECT * FROM developer">>),
io:format("Result2: ~p~n", [Result4]),

%% Transactions are automatically rolled back if
%% any of their queries results in an error.
mysql:transaction(
p1, fun(T) ->
mysql:add_queries(T, [<<"DELETE FROM developer">>,
<<"bad bad query">>])
end),

Result5 = mysql:fetch(p1, <<"SELECT * FROM developer">>),
io:format("Result2: ~p~n", [Result5]),

ok.


Additional documentation is in mysql.erl.

In the next few days, I am planning to integrate these new features into ErlyDB. From some research I've done, I've learned that using prepared statements significantly lowers the load on the database. Given that the database is often a scalability bottleneck in web applications, I think it's important for ErlyDB to use prepared statements as much as possible.

I have done some testing, but given the large number of potentially destabilizing changes, I don't recommend this driver for production use at the moment. You can test it yourself to get comfortable with the code's stability or wait a month or two until it's more hardened.

Some notes about the driver's internals

When initialized, the driver spawns an Erlang process of type gen_server, called mysql_dispatcher, which maintains connection pools to MySQL databases. Each connection has an Erlang process. When the process crashes (i.e. the connection is lost), the dispatcher (by default) tries to reconnect to the database from a new process.

Erlang client processes communicate with the MySQL dispatcher using standard gen_server calls. This structure allows a large number of Erlang processes in a single VM to share relatively few database connections.

Prepared statements are actually prepared lazily per connection. When the user executes a previously defined prepared statement against a connection in which the statement hasn't been prepared, the dispatcher will issue the PREPARE call for the connection before executing the statement. If the execute call is part of a transaction, the dispatcher will issue the PREPARE call before the transaction begins.

If any statement inside a transaction results in an error, the driver automatically tries to roll back the transaction.

Where Are My Readers From?

Here's a geo map overlay view taken from Google Analytics for my blog's last 500 visits.

As you can see, Erlang isn't just popular in Sweden :)

Ranked by number of visitors, the top 10 countries are USA (1312 visits), UK (188 visits), Germany (151 visits), Canada (135 visits), China (120 visits), Sweden (106 visits), france (94 visits), Australia (81 visits), Brazil (75 visits) and Netherlands (63 visits).

Where are you from?

Update: I made the high-res map downloadable.

Boston, End of Week One

This week flew by pretty fast, and unfortunately I haven't accomplished the improvements I had planned for ErlyDB. Next week, I will shift gears and start cranking out that ErlyDB code I have to produce.

So what did I do this week? I did some of blogging and emailing. I took a lot of walks around the Boston to familiarize myself with this town. I also took a bunch of pictures, most of which I put on flickr.

Here's my favorite picture from my walk earlier today in Boston Common. It's a beautiful statue of George Washington.

(This great man wasn't thinking outside the thread -- he was thinking outside the Empire :) )

On the coding side, I spent a good deal of time revving the haXe remoting adapter I contributed to Yaws (my favorite web server :) ). Nicolas Cannasse, haXe's amazingly talented creator, has recently changed the haXe remoting protocol to improve the performace of the caching algorithm used to handle references to strings. In the previous implementation, the serialization of each string was preceded by a linear traversal of all previously serialized strings in search for a match. If a match was found, the index in the cache would be serialized rather than the string itself. This would lower the bandwidth cost of serializing the same string multiple times. Nicolas's improvement was to change the string comparisons to hash table lookups, which is far more efficient for messages with numerous strings.

My task was to make the same changes on the Erlang side. Unfortunately, I haven't touched the haXe remoting code in a while, so my knowledge of it was a bit rusty. The code is also rather complex, partly because it's written in Continuation Passing Style, which makes the parser capable of processing a message in chunks (also called stream-parsing) but also harder to comprehend. (The haXe remoting parser is based on the JSON RPC parser, which I hacked quite substantially.)

After some pretty frustrating debugging (both and haXe have changed since I last played with them), I finally got the code to a ready-to-ship state today. With a CVS commit (or two), the job was done.

I have no idea how many people are actually using this feature, but as both Yaws and haXe grow in popularity, I'm sure others will find it quite useful. haXe is rapidly becoming a great language for all sorts of GUI development, and combining it with a Yaws backend can make a pretty powerful combination (do I see haXe + Yaws powered massively multiplayer online Flash games on the horizon? :) ).

I'm happy that Klacke, Yaws's creator, was willing to let me add the haXe remoting feature to Yaws. It's a rather "exotic" feature, especially because I doubt many Erlangers use haXe. However, Klacke was interested in haXe and when I gave him the Erlang haXe remoting code, he made me a Yaws committer and gave me the green light to add it to the Yaws codebase (with the condition that I also write the documentation :) ).

Maybe at some point down the road I will offer Klacke another feature or two to add to Yaws :)

Now I can finally go back to the fun stuff: ErlyDB.

The Best Erlang Propaganda You'll Ever See

Back in the old days (about 1 month ago), when my blog had about 10 visitors a day (most of whom came to download the XCode plugin for haXe I created), I fully enjoyed the unbridled freedom the blogging medium has given me in my writings about Erlang. My readers (reader?) were all interested in Erlang. Every statement I made about Erlang's strengths was taken as obvious. Justifying my arguments was as easy as saying 'duh.'

Times have changed.

My blog now gets over 1000 page views a day, and some days, when it's featured on reddit.com, it reaches over 5000 page views. With this deluge of new readers, I have suddenly found myself having to contend with an unfamiliar challenge: the Skeptics.

Many of my readers are very intelligent, experienced programmers. Some of them are knowledgeable about if not experts in just about every computer language you can imagine (well, maybe short of Brainf*ck). Erlang is different from many languages, and therefore many of my visitors write very good comments pointing out aspects of Erlang that they don't like or where they think Erlang falls short of other languages.

Like every language, Erlang has some shortcomings. Erlang is a tool, not a panacea. Erlang happens to be a very good tool for building scalable, fault-tolerant, distributed systems, which is primarily why I am so interested in it, but it's not a silver bullet. Depending on your needs, other languages are arguably better than Erlang: Perl for regexps and string processing, OCaml for raw performance, Haskell for its purity and type system, Ruby/Python/PHP for scripting and (some) database-driven webapps, Java for abundance of libraries (and programmers), C# if you're coding for the .NET platform, Prolog for logic programming, C/C++ for desktop programming, etc.

Still, I don't like dealing with skeptics.

You see, I'm at the helm of a well-greased Erlang hype machine, and I'm on a take-no-prisioners mission to recruit every programmer with an internet connection into the cult of Erlang. If you're a programmer, I won't rest until you think in Erlang; talk in Erlang; dream in Erlang. When I'm done with you, your life will be a list; your being will be a tuple; you will communicate to your family and coworkers by asynchronous message passing; your actions will be functions; your consciousness will run on pattern matching.

You will forget your objects. Your shared memory will become Mnesia.

To achieve this nefarious goal of mine, I decided it's time to bring out the big guns.

This is no fun and games anymore. This is war for the hearts and minds of programmers from faraway paradigms. I'm here at my command and control center, and I mean business.

Enough talk. It's time for action.

Without further ado, I present to you my secret weapon, as well as the best Erlang propaganda you'll ever see: The Erlang Movie.

(Try as hard as you can, I know you won't be able to resist hitting that "Play" button.)

...

Now that you've seen the Erlang Movie, we can resume rational discourse regarding the merits of this language. The only condition is that you have to blindingly accept everything I say. You also have to first run around the streets shouting at the top of your lungs "I've seen the light! It's called 'Erlang'!"

Deal? :)

...

Now back to serious writing: please don't take anything I've said here seriously!!!

Update: quick poll -- how many of you have watched the whole thing? :)

First Day in Boston on the "Job"

Yesterday was my first day as a resident Bostonian as well as a semi-unemployed Erlanger.

I must say that although my original impression of Boston wasn't very enthusiastic, I think this town will grow on me.

Boston is a college town in the truest sense of the word. In New York, everyone looks like he/she came from a different planet: people dress different, look different, speak different languages, and seeking different goals. In Boston, on the other hand, you feel like almost everyone is a student. I live somewhere between Boston College and Boston University, which makes the impression even stronger.

Because of its abundance of colleges, Boston feels like a very young town. In my advanced age of 26, I can't help but feel like I'm a TA or something :)

My new "office" is the Boston Public Library on Copley Square:

(You're probably wondering what that blue cow is doing there. Apparently, the cows are all over Boston. Check out the rest of the set for more cow pictures.)

Sometimes I don't get why many startups spend their money on renting office space when you can get a comfortable, quiet environment with good internet connectivity for free at the public library. Maybe it's that VC money thing :)

The Boston Public Library actually wins in the tech department over the New York Public Library for providing free Wi-Fi in the entire building, not just one room.

I didn't get much coding done yesterday, but today I'm hoping to finish implementing transaction handling in ErlyDB. It might take longer, though, so don't get your hopes up too high :)

Another Interesting Erlang Day on Reddit

Reddit readers seem to be very interested in Erlang. I think this is for a good reason: even if you don't like Erlang or you don't think it's a very good language for your needs, you probably would at least agree that Erlang is interesting.

Erlang is a strange creature. It's a functional, dynamically typed language with built-in concurrency semantics. It was created with a very specific purpose: to build large-scale fault-tolerant distributed systems. No other language, at least to my knowledge, has been designed so specifically to tackle this goal. Judging by the success of Ericsson's AXD301 switch and ejabberd, Erlang has delivered on its intended use.

Although it has commerical origins, Erlang is now open source, and it's making headway into the web development world. A language's web development capabilities seem to be one of the most attractive features for the larger developer community. How many people would become Ruby developers if it weren't for Rails? I don't think very many. Although Erlang isn't as far along as Ruby is in the web development department, I think it will close many of these gaps in the coming months. (I also think that the Erlang web stack will have a nice feature or two that will be very hard to build into Rails :) )

Today, I saw 4 Erlang-related articles on programmin.reddit.com: earlier this morning, my article OO, FP, Erlang, and Me was somewhere in the bottom half of the links. It may have been higher at some point -- I don't know. At number 2 (and it used to be at number 1) is Joe Armstrong's Why I Don't Like Shared Memory. At number 9) is Ask Reddit: is Erlang hype the next Ruby hype? I seriously don't think that Erlang's "hype" is anywhere close to Ruby's hype. Erlang's "hype" is basically my blog, wagerlabs.com and now Joe Armstrong's blog. I don't think we are trying to "hype" Erlang as much as raise awareness to the unique capabilities of this seriously under-hyped language, but other people may see it differently. At number 19) is David Bergman's Erlang: The Best or Worst of Two Worlds?. It may have something to do with my mentioning this article on my blog, but maybe it was discovered by the poster independently.

There's definitely a growing interest in Erlang, but until Erlang has a more complete web development stack and until one or two companies will have built a killer web app using Erlang, there will always be doubt about Erlang's true merits for pragmatic developers.

I think that many of these doubts will clear in the next few months.

By the way, saying that Erlang doesn't power any killer webapps is inaccurate: AFAIK, Meebo uses ejabberd for their instant messaging backend. If Meebo also used Yaws as their web server (I don't think they do, but I don't know for certain), they would probably have a simpler architecture.

I like simple :)

Interesting Take on Erlang by David Bergman

My friend and former collegue, David Bergman, has an interesting take on Erlang in his blog: Erlang: the Best or Worst of Two Worlds?

In this article, David discusses Erlang's mertis as a "practical" functional language for building large-scale systems. He also points out a couple of Erlang's aspects that he doesn't like: dynamic typing and lack of currying. Clearly, the first item is the more important.

I'm a big fan of dynamic typing, less from a theortical and more from a practical point of view. I think dynamic typing speeds up development because you can easly execute statements in the shell without previously defining your types. Without dynamic typing, I also don't think I could have created Smerl and ErlyDB -- at least not so easily. At the very least, they would have required the developer to define in compile time the "types" that would be generated in runtime. That's more effort than I would like to make as an ErlyDB user. Still, David brings up some very good points about static typing and I'm not sure that I disagree with him 100% :)

David is the smartest developer (among other things) I know personally and he's the main culprit behind getting me interested in functional languages. His blog is very good. I recommend it to everyone.

Goodbye, New York. Hello, Boston!

This past weekend, after a ~3 year tenure in Manhattan, NYC, I moved to Boston.

It was a crazy weekend that included endless shopping, moving boxes and furniture, painting walls, getting 2 parking tickets, and even attending my friend's wedding. (Guess where? In New York, of course! :) ).

I'm exhausted, but I made it.

Now I need to figure out my life out here.

Paul Graham thinks that nerds don't like New York. At least in my case, he's way off the mark. I love New York. I think it's one of the best cities on earth. No city is as exciting and fascinating as New York.

I will miss New York a lot.

Maybe Paul Graham is right: maybe I'm not a real nerd. Maybe all this Erlang stuff is just a facade, and deep down in my soul I really want to be a hip hop dancer or something :)

Nah... I really like coding. I enjoy this stuff. I sometimes work crazy hours just because I like it. When I don't work crazy hours, I often write code when I get home from work. Not always, though. I have a life outside of my MacBook.

So why Boston, of all places?

I didn't move here because I love Boston. I know Boston pretty well, and although I find it nice enough, I've never fallen in love with it. I definitely don't like it as much as I like New York.

The main reason I moved here is because my girlfriend of two years has been living here for a year now, and I wanted to live closer to her.

The second reason I moved to Boston is that wanted to be able to live cheaply for a while so I can live off my savings while I work on a fun project I want to build. I'm 26 years old, and I feel that this is a good time to do it. I don't have a lot of money, but I also don't have a wife, kids, college bills or mortgage payments. I don't even have a car (I'm from New York, remember? :) ). My savings will carry me for a few months. After that, maybe I'll have to find a real job or a consulting gig or something.

If that happens, I'll cross my fingers and hope that I can get a gig writing Erlang code.

I really enjoy working with Erlang much more than other languages for some reason. I don't even know if it's worth it anymore trying to come up with an objective-sounding list of justifications rather than just saying that I like how it feels.

Even if my project doesn't take wings, my investment in it will certainly bear fruit as I am planning on releasing all the framework-level code I write as open source. ErlyDB will see many improvements, but I will also probably release contributions to higher levels of the Erlang web development stack.

I hope that in a number of months, few people will question that Erlang is a great language for building cutting-edge web applications.

We'll see how all this plays out. I'll keep you posted on my progress.

OO, FP, Erlang, and Me

Drop the anti-OO rehtoric, it just makes you look stupid. For your information, objects are cheap to create in most languages. For example, .Net only need 12 bytes of overhead. Had you not starting attacking OO, I would have been more interested in what you have to say about Erlang and databases.

-- blog comment

I have gone through a string of languages before I seriously got into Erlang. I started learned programming in BASIC and Pascal in my early teens, and then I moved on to C/C++ in high-school. In college, most classes were taught in Java, although we had occasional excursions into C/C++, Scheme and Prolog. Professionaly, I have worked on web applications with Perl, PHP and Java. At my last gig, I have created a pretty large (for 1 developer, I would think) codebase in C: about 34,000 lines of code. I was a network application with a lot of threading and IO logic. It also interacted with a backend system written in Java.

I usually try to stay on top of the latest trends. When Ruby on Rails started getting a lot of buzz, it struck me as a nice alternative to the popular tools of the day, so I studied it pretty thoroughly and then I used it to make a prototype for a website. (I ended up dropping that prototype at the point when I realized I was hopelessly hooked on Erlang :) )

In my last gig, the Java backend code initially interacted with a database by making SQL calls directly via JDBC, but then we switched to Hibernate, 'The' Java ORM framework. After the switch, we saw a significant performance hit and actually considered switching back to JDBC. For several reasons, we eventually decided to take the performance hit and stick with Hibernate anyway.

Benchmarks often produce misleading results regarding the behavior of real system, so you should take the following with a grain of salt, but polepoes.org has ran some benchmarks comparing Java ORM frameworks to JDBC, and in their summary, they state, "The use of O-R mapping technology like Hibernate or JDO O-R mappers has a strong negative impact on performance. If you can't compensate by throwing hardware at your app, you may have to avoid O-R mappers, if performance is important to you."

I'm not an expert on Hibernate internals and I can't state precisely why it would add such an overhead, but, as I said, it most probably has to do with the heavyweight nature of objects. By 'heavyweight' I mean all not just the physical size of the object in memory, but the full context associated with the object. In OO languages that maintain runtime type information such as Java and C# (as opposed to C++) those 12 bytes of overhead that the commenter has mentioned hold a pointer to a class structure. That structure has lists of fields and methods, as well as pointers to its superclass and maybe also to the interfaces it implements. The same structure repeats iteself up the inheritance chain. ORM frameworks have to examine this complex structure, usually by reflection, in order to figure out how to map instance variables to database fields.

(Hibernate actually performs bytecode instrumentation to lower the overhead associated with reflection. I'm not sure what the speed gains are, though. Regardless, we did notice that Hibernate adds overhead to performance.)

In dynamic OO languages such as Ruby, objects have an even higher overhead than in Java and C# because every object has its own hash table that holds its methods, and each method call requires a hash table lookup. That's (partly) why Ruby is slower than Java and C#.

By the way, I'm not trying to suggest that ORM is "bad" or that Hibernate is a bad product. If I used an OO language to build a web application, I would probably use an ORM framework. If I used Java, I would probably use Hibernate because it's mature and feature-rich. Developer productivity is generally much more valuable than the cost of buying a few extra servers.

Back to my programming history: although I have tasted some functional programming in college, it has been in an academic setting. In the "real-world," I have rarely heard of companies using functional languages. Mainstream programming-related websites such as oreillynet.com and devx.com hardly ever talk about any functional languages. Try to search for a Lisp, Haskell or Erlang job on craigslist or monster.com and you'll likely get zero results. All this gave me the impression that functional languages are more for academics than for people who build real systems.

The general story I and many other programmers of my generation have been told is as follows (I'm simplifying, of course): at first there was C, then C++ came and made C better by adding object orientation. Then Java came and made programmers lives much better by adding garbage collection, single inheritence and platform independence.

We also learned that the LAMP stack and with its various scripting languages is an alternative for developers who want to get things done cheap and also quick-and dirty.

When I first discovered Erlang, I wasn't thrilled. "Why bother with some obscure language designed for the telcom industry? Nobody probably uses it besides Ericsson and that jabber server -- ejabberd -- so why should I? Anyway, I already have Java, which makes my life easy," I thought. The look of the website -- especially the documentation area -- didn't add to my enthusiasm, either. It felt too old-school.

However, as much as I initially disliked it, Erlang kept pulling me back. The fact that it was designed for building distributed systems was too alluring. I've done my part in building such systems in C and Java and I know how hard it is. Race conditions, deadlocks, RPC protocols, nodes crashing, monitoring difficulties, logging, resource leaks, obscure bugs, etc, are all major pains in the neck. They are much worse when you're using languages that weren't designed to address those issues.

I have suffered the pains, and I wanted to find a better way.

That's why I kept on gravitating back to the Erlang documentation site, doing the tutorials, experimenting, reading code, asking questions and educating myself. The more I learned about Erlang, the less I missed the Java/C/C++ ways of doing things. After a while, I realized what a great language it was. Everything just fit together perfectly -- message passing, distributed programming, faul-tolerance, high-availability, pattern matching, dynamic typing, rapid development, functional semantics, a distributed database...

When I finally felt that I 'got' Erlang, never once did I miss a single OO construct. In fact, I started regarding OO as a burden. In OO languages, every class, every method, every field has so much context around it. To understand where piece of code fits into the big picture, you have to read through the documentation for all the classes and interfaces to which it belongs. Also, OO languages often encourage if not force you to wedge your types into some inhertiance chain in order to reuse code, which results in ugly inhertiance relations that are inflexible at best.

Functional languages like Erlang have the opposite philosophy. They try to minimize the context surrounding a piece of logic-- a function. All you have to know is what parameters a function takes and what it returns. There is no implicit link between the function and the data it uses. With single-assignment, you don't even have to worry about functions mangling the values of your variables. When you're coding in Erlang as opposed to Java, you think less about the context of your functions and more about what they do and how to compose them together to solve your problem.

That's what a good programming language is about: it should let you express the solution to your problem as concisely and easily as possible so you can move on to the next problem. It should also make it easy to compose multiple components into a larger whole.

When the Erlang lightbulb went off in my head, I actually started feeling some resentment towards the rest of the software world, which is dominated by OO thinking. "Why didn't anybody tell me about this earlier? Why isn't Erlang featured in the mainstream software press? Why is Ruby on Rails on Oreilly's radar but Erlang isn't? Why is everybody chasing OO features while overlooking this great language? Why did I spend so much time working with much worse languages? Erlang is twenty years old. It has been open sourced in 1998. How could the software community ignore it so thoroughly?"

This is partly I occasionally take jabs at the OO world. Part of me feels that it has mislead me and kept me away from a beautiful tool that would have made my life much better had I known about it sooner.

And much more fun :)