Previous Up Next

Chapter 5  DBForge

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:

5.2  Principle

The tool is based on the following problem : The creation of functions executing SQL queries To solve these problems, we want to To do so, we must have the following information about each colum of each table : The list of tables with the above information for each column is what we call the database schema.

We can note that: 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.

5.3.1  Column id

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 : 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.

5.3.4  Saving

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:

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
...

Previous Up Next