Tutorial
This small tutorial explains how an application can access a database (PostgreSQL, MySQL or SQLite) to store its data by using the Ada Database Objects framework. The framework has several similarities with the excellent Hibernate Java framework.
The ADO framework is composed of:
- A code generator provided by Dynamo,
- A core runtime,
- A set of database drivers (PostgreSQL, MySQL, SQLite).
The tutorial application is a simple user management database which has only one table.
Defining the data model
The first step is to design the data model. You have the choice with:
- Using an UML modeling tool such as ArgoUML,
- Writing an XML file following the Hibernate description,
- Writing a YAML description according to the Doctrine mapping.
In all cases, the model describes the data table as well as how the different
columns are mapped to an Ada type. The model can also describe the relations between
tables. XML and YAML data model files should be stored in the db
directory.
Let's define a mapping for a simple user
table and save it in db/user.hbm.xml
:
<?xml version="1.0" encoding="UTF-8"?>
<hibernate-mapping default-cascade="none">
<class name="Samples.User.Model.User"
table="user" dynamic-insert="true" dynamic-update="true">
<comment>Record representing a user</comment>
<id name="id" type="ADO.Identifier" unsaved-value="0">
<comment>the user identifier</comment>
<column name="id" not-null="true" unique="true" sql-type="BIGINT"/>
<generator class="sequence"/>
</id>
<version name="version" type="int" column="object_version" not-null="true"/>
<property name="name" type="String">
<comment>the user name</comment>
<column name="name" not-null="true" unique="false" sql-type="VARCHAR(256)"/>
</property>
<property name="email" type="String" unique='true'>
<comment>the user email</comment>
<column name="email" not-null="true" unique="false" sql-type="VARCHAR(256)"/>
</property>
<property name="date" type="String">
<comment>the user registration date</comment>
<column name="date" not-null="true" unique="false" sql-type="VARCHAR(256)"/>
</property>
<property name="description" type="String">
<comment>the user description</comment>
<column name="description" not-null="true" unique="false" sql-type="VARCHAR(256)"/>
</property>
<property name="status" type="Integer">
<comment>the user status</comment>
<column name="status" not-null="true" unique="false" sql-type="Integer"/>
</property>
</class>
</hibernate-mapping>
The YAML description is sometimes easier to understand and write
and the content could be saved in the db/users.yaml
file.
Samples.User.Model.User:
type: entity
table: user
description: Record representing a user
hasList: true
indexes:
id:
id:
type: identifier
column: id
not-null: true
unique: true
description: the user identifier
fields:
version:
type: integer
column: object_version
not-null: true
version: true
unique: false
description:
name:
type: string
length: 255
column: name
not-null: true
unique: false
description: the user name
email:
type: string
length: 255
column: email
not-null: true
unique: false
description: the user email
date:
type: string
length: 255
column: date
not-null: true
unique: false
description: the user registration date
description:
type: string
length: 255
column: description
not-null: true
unique: false
description: the user description
status:
type: integer
column: status
not-null: true
unique: false
description: the user status
These XML and YAML mapping indicate that the database table user
is represented by the User
tagged record declared in the Samples.User.Model
package. The table contains a name
,
description
, email
and a date
column members
which are a string. It also has a status
column which is an integer.
The table primary key is represented by the id
column. The version
column is
a special column used by the optimistic locking.
Generating the Ada model and SQL schema
The Dynamo code generator is then used to generate the package and Ada records that represent our data model. The generator also generates the database SQL schema so that tables can be created easily in the database.
dynamo generate db
The generator will build the package specification and body for
Samples.User.Model
package. The files are created in src/model
to make it
clear that these files are model files that are generated. The database table user
is represented
by the Ada tagged record User_Ref
. The record members are not visible and to access the attributes
it is necessary to use getter or setter operations.
The SQL files are generated for every supported database in the db/mysql
,
db/sqlite
and db/postgresql
directories. The generator generates two SQL files
in each directory:
- A first SQL file that allows to create the tables in the database.
The file name uses the pattern
create-
name-driver. - A second SQL file that contains
DROP
statements to erase the database tables. The file name uses the patterndrop-
name-driver.
When you modify the UML, XML or YAML model files, you should generate again
the Ada and SQL files. Even though these files can be generated, it is
recommended to store these generated files in a versioning systems such
as git
because this helps significantly in tracking changes in the data model.
Getting a Database Connection
To access the database, we will need a database connection. These connections are obtained from a factory and they are represented by a session object.
The session factory is the entry point to obtain a database session.
with ADO.Sessions;
with ADO.Sessions.Factory;
...
Factory : ADO.Sessions.Factory.Session_Factory;
The factory can be initialized by giving a URI string that identifies the driver and the information to connect to the database. Once created, the factory returns a session object to connect to that database. To connect to another database, another factory is necessary.
To get access to a MySQL database, the factory could be initialized as follows:
ADO.Sessions.Factory.Create (Factory, "mysql://localhost:3306/ado_test?user=test");
And to use an SQLite database, you could use:
ADO.Sessions.Factory.Create (Factory, "sqlite:///tests.db");
For a PostgreSQL database, the factory would look like:
ADO.Sessions.Factory.Create (Factory, "postgresql://localhost:5432/ado_test?user=test");
Factory initialization is done once when an application starts. The same factory object can be used by multiple tasks.
Opening a Session
The session is created by using the Get_Session
or the Get_Master_Session
function of the factory. Both function return a session object
associated with a database connection. The Get_Session
will return
a Session
object which is intended to provide read-only access to the
database. The Get_Master_session
returns a Master_Session
object
which provides a read-write access to the database.
In a typical MySQL Master/Slave replication, the Master_Session
will refer
to a connection to the MySQL master while the Session
will refer to a slave.
With an SQLite database, both sessions will in fact share the same SQLite internal
connection.
To load or save the user object in the database, we need a Master_Session
database connection:
with ADO.Sessions;
...
Session : ADO.Sessions.Master_Session := Factory.Get_Master_Session;
Creating a database record
To create our first database record, we will declare a variable that will represent the new database record.
The User_Ref
represents a reference to such record.
with Samples.User.Model;
...
User : Samples.User.Model.User_Ref
After this declaration, the variable does not refer to any database record but we can still set some fields:
User.Set_Name ("Harry");
User.Set_Age (17);
To save the object in the database, we just need to call the Save
operation. To save the object, we need
a database session that is capable of updating and inserting new rows. If the object does not yet have a primary key,
and if the primary key allocation mode is set to hilo
, the ADO runtime will allocate a new unique primary key
before inserting the new row.
User.Save (Session);
The primary key can be obtained after the first Save
with the following operation:
Id : ADO.Identifier := User.Get_Id;
Loading a database record
Loading a database record is quite easy and the ADO framework proposes two mechanisms. First, let's declare our user variable:
Harry : User_Ref;
Then we can load the user record from the primary key identifier (assuming the identifier is ''23''):
Harry.Load (Session, 23);
If the user cannot be found, the Load
operation will raise the NOT_FOUND
exception.
In many cases, we may not know the primary key but a search on one or several columns may be necessary.
For this, we can create a query filter and use the Find
operation. To use a query filter, we need
first to declare a Query
object:
with ADO.SQL;
...
Query : ADO.SQL.Query;
On the query object, we have to define the filter which represents the condition and set the possible parameters used by the filter.
Query.Bind_Param (1, "Harry");
Query.Set_Filter ("name = ?");
Once the query is defined and initialized, we can find the database record:
Found : Boolean;
...
User.Find (Session, Query, Found);
Unlike the Load
operation, Find
does not raise an exception but instead returns a boolean value
telling whether the record was found or not. The database query (and filter) has to return exactly one
record to consider the object as found.
Getting a list of objects
When several records have to be read, it is necessary to use the List
operation together with
a vector object.
Users : User_Vector;
The List
operation gets the vector object, the database session
and the query filter. If the vector contained some elements, they
are removed and replaced by the query result.
List (Users, Session, Query);
Running SQL queries
Sometimes it is necessary to execute SQL queries to be able to get the result without having it
to be mapped to an Ada record. For this, we are going to use the ADO.Statements
with ADO.Statements;
...
Statement : ADO.Statements.Query_Statement := Session.Create_Statement ("SELECT COUNT(*) FROM user");
and then execute it and retrieve the result.
Statement.Execute;
if not Statement.Has_Elements then
Put_Line ("SQL count() failed")
else
Put_Line (Integer'Image (Statement.Get_Integer (0)));
end if;