Copyright © Yariv Sadan 2006-2007
Authors: Yariv Sadan (yarivsblog@gmail.com) [web site: http://yarivsblog.com].
ErlyDB: The Erlang Twist on Database Abstraction.
Introduction
Primary and Foreign Key Conventions
ErlyDB is a database abstraction layer generator for Erlang. ErlyDB combines database metadata and user-provided metadata to generate functions that let you perform common data access operations in an intuitive manner. It also provides a single API for working with different database engines (although currently, only MySQL is supported), letting you write portable data access code.
ErlyDB is designed to work with relational schemas, supporting both
one-to-many and many-to-many relations. For more details on how to
define relations between modules, see erlydb_base:relations/0.
By using erlsql under the hood for SQL statement generation, ErlyDB
provides a simple and effective mechanism for protection against
SQL injection attacks. (It's possible to use ErlyDB in 'unsafe' mode,
which lets you write SQL statement snippets as strings, but this isn't
recommended.) Many of the functions that ErlyDB generates let you extend
the automatically generated queries by passing WHERE
conditions and/or extras (e.g. LIMIT, ORDER BY) clauses, expressed as
ErlSQL snippets, as parameters.
ErlyDB uses the module erlydb_base as a generic template for database access modules. During code generation, ErlyDB calls smerl:extend(erlydb_base, Module), and then performs different manipulations on the functions in the resulting module in order to specialize them for the specific model.
To learn about the functions that ErlyDB generates and how to implement functions that provide ErlyDB extra database metadata prior to code generation, refer to the documentation for erlydb_base.
You can find sample code illustrating how to use many of ErlyDB's features in the test/erlydb directory.
Prior to ErlyWeb 0.4, ErlyDB assumed that all tables have an identity primary key field named 'id'. From ErlyWeb 0.4, ErlyDB lets users define arbitrary primary key fields for their tables. ErlyDB figures out which fields are the primary key fields automatically by querying the database' metadata.
ErlyDB currently relies on a naming convention to map primary key field names to foreign key field names in related tables. Foreign key field names are constructed as follows: [TableName]_[FieldName]. For example, if the 'person' table had primary key fields named 'name' and 'age', then related tables would have the foreign key fields 'person_name' and 'person_age', referencing the 'name' and 'age' fields of the 'person' table.
Important: Starting from ErlyWeb 0.4, when a module defines a different
table name (by overriding the erlydb_base:table/0 function),
the table name is used in foreign key field names, not the module name.
In one-to-many/many-to-one relations, the foreign key fields for the 'one' table exist in the 'many' table. In many_to_many relations, all foreign key fields for both modules exist in a separate table named [Table1]_[Table2], where Table1 < Table2 by alphabetical ordering.
Starting from v0.4, ErlyDB has special logic to handle the case where a
module has a
many-to-many relation to itself. In such a case, the relation table
would be called [TableName]_[TableName], and its fields would be the
table's primary key corresponding foreign key fields,
first with the postfix "1", and
then with the postfix "2". For example, if the 'person' module defined
the relation {many_to_many, [person]} (and the table name were 'person',
i.e., the default), then there should exist a
'person_person' relation table with the following fields: person_name1,
person_name2, person_age1, and person_age2.
(In addition to using a different foreign key naming convention, ErlyDB uses different query construction rules when working with self-referencing many-to-many relations.)
In a future version, ErlyDB may allow users to customize the foreign key field names as well as many_to_many relation table names.driver() = atom() | {Driver::atom(), DriverOptions::proplist()} | {Driver::atom(), DriverOptions::proplist(), [pool()]}
pool() = atom() | {default, PoolId::atom()}
| code_gen/2 | Equivalent to code_gen(Modules, Drivers, []). |
| code_gen/3 | Equivalent to code_gen(Modules, Drivers, Options, []). |
| code_gen/4 | Generate code for the list of modules using the provided drivers. |
| start/1 | Start an ErlyDB session for the driver using the driver's default options. |
| start/2 | Start an ErlyDB sessions for the driver using the list of user-defined options. |
Equivalent to code_gen(Modules, Drivers, []).
Equivalent to code_gen(Modules, Drivers, Options, []).
code_gen(Modules::[Module::atom() | string()], Driver::[driver()] | driver(), Options::[term()], IncludePaths::[IncludePath::string()]) -> ok | {error, Err}
Generate code for the list of modules using the provided drivers.
If you're using ErlyWeb, you shouldn't need to call this function directly.
Instead, refer to erlyweb:compile/2.
In ErlyWeb 0.7, the signature for this function has changed. ErlyDB used to support only a single driver with a single connection pool in a session. As of ErlyWeb 0.7, ErlyDB supports multiple drivers in a session, and multiple connection pools for each driver.
The 'Modules' parameter is a list of files or modules for which to generate ErlyDB code. If a list item is an atom, ErlyDB assumes it's a module that has been loaded into the VM or that resides in the VM's code path. In either case, the module's source code should be discoverable either through Erlang's path conventions or because the module was compiled with debug_info.
If a list item is a string, ErlyDB treats it as a file name (relative or absolute) and attempts to read it from disk.
The 'Drivers' parameter is either a single element or a list of
elements of the form
Driver::atom(),
{Driver::atom(), DriverOptions::proplist()}, or
{Driver::atom(), DriverOptions::proplist(), Pools::pool()}.
The first element in the Drivers list is the default driver that ErlyDB will use for all modules that don't override the driver option.
'Driver' can be mysql, psql or mnesia. 'Options' is a list of
driver-specific options. For a list of available options, refer to
the driver's documentation.
'DriverOptions' is a property list that contains driver-specific options (e.g. '{allow_unsafe_statements, Bool}'). For more information refer to the driver's documentation.
'Pools' is a list of available connection pools for the driver.
Note that the driver must be started and the pools must be connected
before code_gen/2 is called. Each item in 'Pools' is an atom indicating
the pool id, or a tuple of the form {PoolId, default}, which indicates
that this pool will be used as the default pool for the driver.
If you don't provide a {PoolId, default} pool option, ErlyDB will use
the driver-defined default pool id if it exists (you can obtain it by
calling Mod:get_default_pool_id(), where 'Mod' is the driver's
module, e.g. 'erlydb_mysql').
'Options' is a list of options that are used for all modules. This may include global driver options as well as options that are passed to compile:file/2. For more information, refer to this function's documentation in the OTP documentation.
Additional include paths that will be used to search for header files when compiling the modules.
Generate code for "musician.erl" using the MySQL driver. Only the default pool is enabled.
code_gen(["musician.erl"], mysql).
Use the previous settings but allow unsafe SQL statements, and compile with debug_info:
code_gen(["musician.erl"],
{mysql, [{allow_unsafe_statements, true}]},
[debug_info]).
Generate code for the modules using the MySQL driver with two additional
pools, 'pool1' and 'pool2'. The default pool is remains erlydb_mysql:
code_gen(["musician.erl", "instrument.erl"],
{mysql, [], [pool1, pool2]}).
Similar to the previous setting, but allow unsafe statement and use
pool2 as the default pool name:
code_gen(["src/musician.erl", "src/instrument.erl"],
{mysql, [{allow_unsafe_statements, true}],
[{pool1, {pool2, default}}]})
Generate code for the modules using both the MySQL and Postgres driver. The MySQL driver has 2 pools enabled: mysql_pool1 and mysql_pool2, which is the default. The Postgres driver has a single default pool, pg_pool1. The MySQL driver allows unsafe statements:
code_gen(["src/musician.erl", "src/instrument.erl", "src/song.erl"],
[{mysql, [{allow_unsafe_statements, true}],
[{mysql_pool1, {mysql_pool2, default}}]},
{psql, [], [{pg_pool1, default}]}])
To specify which connection pool ErlyDB should for a specific module, add the following line to the module's source code:
-erlydb_options([{driver, Driver}, {pool_id, PoolId}]).
The 'driver' option tells ErlyDB to use a non-default driver for the
module. The 'pool_id' option tells ErlyDB to use a non-default pool id
for the module. Neither option is required -- you can specify only
a 'driver' option or only a 'pool_id' option.
start(Driver::atom()) -> ok | {error, Err}
Start an ErlyDB session for the driver using the driver's default options. This only works for some drivers. For more details, refer to the driver's documentation.
start(Driver::atom(), Options::proplist()) -> ok | {error, Err}
Start an ErlyDB sessions for the driver using the list of user-defined options. For information on which options are available for a driver, refer to the driver's documentation.