Wednesday, August 30, 2006

Many-to-Many Relations Are Now at an ErlyDB Near You

The title says it all :)

Not only that, I also added more powerful SELECT query capabilities to many-to-one relations.

Here's how it works:

Add these tables to erlydb.sql

create table developer(
id integer auto_increment primary key,
name varchar(30),
country varchar(20));

create table developer_project(
developer_id integer,
project_id integer,
primary key(developer_id, project_id));

This is our modified project.erl file:


relations() ->
[{many_to_one, [language]},
{many_to_many, [developer]}].

This is our new developer.erl files:


relations() ->
[{many_to_many, [project]}].

Following is some code demonstrating the new capabilities. The full module is in the distribution, in test/erlydb_test.erl.

%% fancier project queries
{ok, [Yaws4]} = language:projects_where(
Erlang, "name='Yaws'"),
true = Yaws4 == Yaws1,

{ok, [Yaws5]} = language:projects_with(
Erlang, "limit 1"),
true = Yaws4 == Yaws5,

{ok, [Ejabberd4]} = language:projects(
Erlang, "name like '%e%'", "limit 1"),
true = Ejabberd4 == Ejabberd3,

%% Let's show off some many-to-many features

%% First, add some more projects
[OTP, Mnesia] =
fun(Proj) ->
{ok, P1} = project:save(Proj),
[project:new("OTP", "The Open Telephony Platform", Erlang),
project:new("Mnesia", "A distributed database " ++
"engine written in Erlang", Erlang)]),

%% Next, add some developers
[Joe, Ulf, Klacke] =
fun(Developer) ->
{ok, D1} =
[developer:new("Joe Armstrong", "Sweden"),
developer:new("Ulf Wiger", "Sweden"),
developer:new("Claes (Klacke) Wikstrom", "Sweden")]),

%% Add some developers to our projects
ok = project:add_developer(OTP, Joe),
ok = project:add_developer(OTP, Klacke),
ok = project:add_developer(OTP, Ulf),

%% Add some projects to our developers
ok = developer:add_project(Klacke, Yaws1),
ok = developer:add_project(Klacke, Mnesia),
ok = developer:add_project(Ulf, Mnesia),

%% basic SELECT query
{ok, [Joe, Ulf, Klacke]} =

%% fancier SELECT queries
{ok, [Ulf, Klacke]} =
{ok, [Ulf]} = project:developers_with(Mnesia,
"limit 1"),
{ok, [Klacke]} =
"name like 'Claes%'"),

%% SELECT query, from the other direction
{ok, [Yaws1, OTP, Mnesia]} =

%% Klacke, nothing personal here :)
ok = developer:remove_project(Klacke, Yaws1),
{ok, [OTP, Mnesia]} = developer:projects(Klacke),
ok = developer:remove_project(Klacke, OTP),
{ok, [Mnesia]} = developer:projects(Klacke),
ok = developer:remove_project(Klacke, Mnesia),
{ok, []} = developer:projects(Klacke),

%% I forgot to show you can delete records, too :)
ok = language:delete(Java).

ErlyDB is still in alpha version, so you may have problems. Please let me know if you find any bugs :)

(FYI, I'm moving to Boston tomorrow, so I will probably not be of much help in the next few days.)



Tieying Liu said...

Hey, cool. Welcome to Boston. Let me know if you wanna go get a beer some time.

Take care, man...

Yariv said...

Will do, just shoot me an email :)

pavani said...

MAny to MAny relations