5.1 Introduction
DBforge is a tool for generating OCaml code to access databases. This code
is generated from a schema, described through a graphical user interface.
We explain the use of DBForge as in a tutorial, through a complete example.
We assume that at least one of the following librairies is installed:
-
OCamlODBC 2.5 (or superior) :
http://www.maxence-g.net/Tools/ocamlodbc/ocamlodbc.html,
- OCaml-MySQL 0.1.1 (or superior) :
http://raevnos.pennmush.org/code/ocaml.html,
- Postgres 20010808 (or superior) :
http://www.eleves.ens.fr:8080/home/frisch/soft.
5.2 Principle
The tool is based on the following problem :
The creation of functions executing SQL queries
-
is repetitive:
translation of parameters into strings and concatenation of strings
to create the query, execution of the query, test on the return value,
translation of the returned data (for select queries) into
the correct OCaml type,
- is error prone:
an error in the concatenation of strings to create the query
(for example a missing blank) or in the concatenated strings
will cause an error at run time,
- does not allow a high maintainability: indeed, when the database
schema is modified, one must verify each query is still correct
against the new database schema.
To solve these problems, we want to
-
automatically generate the functions executing basic queries on each table,
- allow the user to define complex queries, using the SQL language ;
the OCaml functions executing these queries being automatically
generated, we want to guarantee at compile time that execution
of these queries will not fail at run time
(because of errors like syntax errors or referencing a non-existent
column of a table),
- automatically verify and invalidate some queries when the database schema
is modified.
To do so, we must have the following information about each colum of each table :
-
its name,
- its type definition for the target DBMS,
- some attributes like whether the column is nullable, whether it's a key
for this table,
- its OCaml type in the source code we want to generate,
- the functions to use to translate DBMS (resp. OCaml) values into OCaml
(resp. DBMS) values.
The list of tables with the above information for each column is what
we call the database schema.
We can note that:
-
The definition of the column and the translation functions are DBMS dependant.
- The OCaml type, column name and some attributes are DBMS independant.
So we can have the same interface for accessing different underlying DBMS.
The application using this inteface can then be used for each DBMS
(if it does not use some DBMS specific functionalities, for example
the AUTO_INCREMENT attribute of MySQL).
The DBForge tool can be used to describe such a schema.
It does not allow yet the definition of complex queries and the verification
of these queries against the schema.
As an example, we will build a table with several columns, then use the
generated code to performs some actions on this table in a database.
5.3 Description of the schema
The description of the schema is made with a graphical editor. A schema
is stored in a file. The following command launches the editor on the schema
in file base.sch:
dbforge.gui base.sch
The edition window appears. From this window you can edit the tables
and columns, and define complex queries which will be encapsulated in
functions. (the definition of complex queries is not implemented yet).
Add a table with the menu Table/Add. Enter "foo" for the table name
and click on the "Ok" button. The table is then added to our schema. It does not
contain any column yet: as you can see when you select the table, the list
of columns remains empty.
We'll add three columns to our table. The first will be an integer used
as unique identifier, the second will be a float, and the third will be
a string.
Select the table "foo" and use the menu Columns/Add. The column edition
window appears. This window contains 4 tabs. The first ("Common") is used to
give information common to all DBMS. Each of the other tabs correspond to
a target library handled by DBForge: "ODBC" for OCamlODBC,
"MySQL" for OCaml-Mysql, et "PostgreSQL" for Postgres.
In the "Common" tab, type the following values in the fields :
-
"Name": id. This is the name of the column and also
of a field in a record type in the generated OCaml code. So this name
must a valid OCaml identifier.
- "OCaml type": int. This is the OCaml type used to represent
the values of the column. If the column is ``nullable'' (see below), and
that the given type is t, then the type of the values for this column
will be t option, else it will be t. You can give any OCaml type,
but it must be valid, and known at compile time.
- "Nullable": not activated. We indicate here whether the value
of a record for this column can be NULL. If it can be NULL,
the associated type in the generated OCaml code will be t option
instead of t, with t the type given in the field "OCaml type".
- "Index": activated. We indicate that the table must contain an index
on this column. (not yet implemented in the code generation).
In each of the remaining tabs, you must indicate the SQL type of the column,
whether it's a key (primary key or just a key), and give translations
functions to convert SQL values (as strings) to and from OCaml values.
Some functions are available in the combo box of each function field,
but you can put your own instead.
At last, you can give a default SQL value, which will usually appear in the
DEFAULT clause of the column definition.
Here are the values to put in the fields of each tab :
|
"ODBC" |
"MySQL" |
"PostgreSQL" |
"SQL type" |
integer |
INT |
INT8 |
"Optional key type" |
primary key |
primary key |
primary key |
"sql ® OCaml" |
int_of_string |
Mysql.int2ml |
int_of_string |
"OCaml ® sql" |
string_of_int |
Mysql.ml2int |
string_of_int |
"Default SQL value" |
|
|
|
Once this is done, click on the "Ok" to add the column to the table ``foo''.
5.3.2 Colonne length
In this column we want to store a float. This column is not a key and we may
have no value (that is we can have the NULL value).
Here are the values to fill the fields in "Common" with:
Field |
Value |
"Name" |
length |
"OCaml type" |
float |
"nullable" |
activated |
"index" |
not activated |
And in the other tabs:
|
"ODBC" |
"MySQL" |
"PostgreSQL" |
"SQL type" |
double |
DOUBLE |
FLOAT8 |
"Optional key type" |
|
|
|
"sql ® OCaml" |
float_of_string |
Mysql.float2ml |
float_of_string |
"OCaml ® sql" |
string_of_float |
Mysql.ml2float |
string_of_float |
"Default SQL value" |
|
|
|
5.3.3 Colonne comment
In this column we want to store a comment text. This column is not a key
and we may have no value (that is we can have the NULL value).
Here are the values to fill the fields in "Common" with:
Field |
Value |
"Name" |
comment |
"OCaml type" |
string |
"nullable" |
activated |
"index" |
not activated |
And in the other tabs:
|
"ODBC" |
"MySQL" |
"PostgreSQL" |
"SQL type" |
varchar(255) |
TEXT |
TEXT |
"Optional key type" |
|
|
|
"sql ® OCaml" |
string_of_sqlstring |
Mysql.str2ml |
string_of_sqlstring |
"OCaml ® sql" |
sqlstring_of_string |
Mysql.ml2str |
sqlstring_of_string |
"Default SQL value" |
|
|
|
Note: For the ``ODBC'' tab, we put vachar(255) as SQL type, which is far more
restrictive than the TEXT SQL type of the two other tabs, but this way we
ensure the portability of the application because this type is standard.
However, if we know that the underlying DBMS that the application will use through ODBC
supports another SQL type, we can use it. To do so, we can use the field ``Other'' with
the type to use, for example TEXT.
Now that we have defined our schema, we can save it with the menu File/Save. then
quit with the menu File/Quit.
5.4 Code generation
The code generation is performed with one of the following commands:
-
dbforge -odbc base.sch: generates code which uses OCamlODBC in base.ml and base.mli,
- dbforge -mysql base.sch: generates code which uses OCaml-MySQL in base.ml and base.mli,
- dbforge -pg base.sch: generates code which uses Postgres in base.ml and base.mli.
5.5 Compilation
To compile the generated code, use the following commands, where DBDIR is the directory
where the library (OCamlODBC, OCaml-MySQL or Postgres) you want to use is :
ocamlc -c -I DBDIR base.mli
ocamlc -c -I DBDIR base.ml
You can also generate the documentation using ocamldoc :
mkdir htmldoc
ocamldoc -I DBDIR -html -d htmldoc base.mli
Then the documentation is in htmldoc/index.html.
You may have to add -I flags if you referenced other modules in the code in your
schema description (OCaml types and translation functions).
5.6 Using the generated code
Type the following code in the file main.ml :
module B = Base
module F = B.Foo
let d = B.connect Sys.argv.(1) Sys.argv.(2)
let _ =
try F.create d
with Failure s -> prerr_endline s
let _ = F.delete d ()
let cpt = ref 0
let _ =
while (!cpt <= 1000) do
(
if !cpt mod 2 = 0 then
F.insert d ~id: !cpt ~length: None ()
else
F.insert d ~id: !cpt ~length: (Some (float_of_int !cpt))
~comment: (Some (string_of_int !cpt)) ();
incr cpt
)
done
let l = F.select d ()
let _ = List.iter (fun t ->
print_int t.F.id;
print_string "#";
(match t.F.length with
None -> print_string "NULL"
| Some f -> Printf.printf "%f" f);
print_string "#";
(match t.F.comment with
None -> print_string "NULL"
| Some text -> print_string text);
print_newline ()) l
let _ = F.drop d
The code above connects to a database, creates the table "foo",
deletes all records in it, fills it, reads it, prints the retrieved records
and destroys the table.
To compile this example :
ocamlc -o example.x -I DBDIR ocamlodbc.cma base.cmo main.ml
This command works for OCamlODBC. For OCaml-MySQL, you must replace
ocamlodbc.cma by mysqlstatic.cma, et by postgres.cma for
the Postgres library.
Some other compilation options may be needed (-ccopt -l..., ...).
Next, launch the example this way (if your DBMS is well configured, launched,
and if the file ~/.odbc.ini is correct (for the OCamlODBC library)) :
./example.x <user> <database name>
The program should print a thousand records this way:
...
969#969.000000#969
970#NULL#NULL
971#971.000000#971
972#NULL#NULL
973#973.000000#973
974#NULL#NULL
975#975.000000#975
976#NULL#NULL
977#977.000000#977
978#NULL#NULL
979#979.000000#979
980#NULL#NULL
...