ORM Development Model
The Ada Database Objects is an Object Relational Mapping for the Ada05 programming language. It allows to map database objects into Ada records and access database content easily. The library supports PostgreSQL, MySQL, SQLite as databases. Most of the concepts developed for ADO come from the Java Hibernate ORM.
The ORM uses either an XML mapping file, a YAML file or an UML model, a code generator and a runtime library for the implementation. It provides a database driver for PostgreSQL, MySQL and SQLite. The ORM helps your application by providing a mapping of your database tables directly in the target programming language: Ada05 in our case. The development process is the following:
ORM Development Model
This document describes how to build the library and how you can use the different features to simplify and help you access databases from your Ada application.
This chapter explains how to build and install the library.
Before building ADO, you will need:
First get, build and install the XML/Ada and then get, build and install the Ada Utility Library.
The PostgreSQL, MySQL and SQLite development headers and runtime are necessary for building the ADO driver. The configure script will use them to enable the ADO drivers. The configure script will fail if it does not find any database driver.
MySQL Development installation
sudo apt-get install libmysqlclient-devMariaDB Development installation
sudo apt-get install mariadb-client libmariadb-client-lgpl-devSQLite Development installation
sudo apt-get install libsqlite3-devPostgreSQL Development installation
sudo apt-get install postgresql-client libpq-devIt is recommended to use msys2 available at https://www.msys2.org/ and use the pacman command to install the required packages.
pacman -S git
pacman -S make
pacman -S unzip
pacman -S base-devel --needed
pacman -S mingw-w64-x86_64-sqlite3For Windows, the installation is a little bit more complex and manual. You may either download the files from MySQL and SQLite download sites or you may use the files provided by Ada Database Objects in the win32 directory.
For Windows 32-bit, extract the files:
cd win32 && unzip sqlite-dll-win32-x86-3290000.zipFor Windows 64-bit, extract the files:
cd win32 && unzip sqlite-dll-win64-x64-3290000.zipIf your GNAT 2019 compiler is installed in C:/GNAT/2019, you may install the liblzma, MySQL and SQLite libraries by using msys cp with:
cp win32/*.dll C:/GNAT/2019/bin
cp win32/*.dll C:/GNAT/2019/lib
cp win32/*.lib C:/GNAT/2019/lib
cp win32/*.a C:/GNAT/2019/libThe library uses the configure script to detect the build environment, check which databases are available and configure everything before building. If some component is missing, the configure script will report an error. The configure script provides several standard options and you may use:
--prefix=DIR to control the installation directory,--with-mysql=PATH to control the path where mysql_config is installed,--with-ada-util=PATH to control the installation path of Ada Utility Library,--enable-mysql to enable the support for MySQL,--enable-postgresql to enable the support for PostgreSQL,--enable-sqlite to enable the support for SQLite,--enable-shared to enable the build of shared libraries,--disable-static to disable the build of static libraries,--enable-distrib to build for a distribution and strip symbols,--disable-distrib to build with debugging support,--enable-coverage to build with code coverage support (-fprofile-arcs -ftest-coverage),--help to get a detailed list of supported options.In most cases you will configure with the following command:
./configureAfter configuration is successful, you can build the library by running:
makeAfter building, it is good practice to run the unit tests before installing the library. The unit tests are built and executed using:
make testAnd unit tests are executed by running the bin/ado_harness test program. A configuration file is necessary to control the test parameters including the test database to be used. To run the tests with a MySQL database, use the following command:
bin/ado_harness -config test-mysql.propertiesand with a SQLite database, use the following command:
bin/ado_harness -config test-sqlite.propertiesThe installation is done by running the install target:
make installIf you want to install on a specific place, you can change the prefix and indicate the installation direction as follows:
make install prefix=/optTo use the library in an Ada project, add the following line at the beginning of your GNAT project file:
with "ado";
with "ado_all";It is possible to use only a specific database driver, in that case your GNAT project file could be defined as follows:
with "ado";
with "ado_mysql";
with "ado_sqlite";
with "ado_postgresql";where the ado_mysql, ado_sqlite and ado_postgresql are optional and included according to your needs.
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:
The tutorial application is a simple user management database which has only one table.
The first step is to design the data model. You have the choice with:
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 statusThese 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.
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 dbThe 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:
create-name-driver.DROP statements to erase the database tables. The file name uses the pattern drop-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.
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.
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;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_RefAfter 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 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.
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);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;The ADO.Sessions package defines the control and management of database sessions. The database session is represented by the Session or Master_Session types. It provides operation to create a database statement that can be executed. The Session type is used to represent read-only database sessions. It provides operations to query the database but it does not allow to update or delete content. The Master_Session type extends the Session type to provide write access and it provides operations to get update or delete statements. The differentiation between the two sessions is provided for the support of database replications with databases such as MySQL.
Database drivers provide operations to access the database. These operations are specific to the database type and the ADO.Drivers package among others provide an abstraction that allows to make the different databases look like they have almost the same interface.
A database driver exists for SQLite, MySQL and PostgreSQL. The driver is either statically linked to the application or it can be loaded dynamically if it was built as a shared library. For a dynamic load, the driver shared library name must be prefixed by libada_ado_. For example, for a mysql driver, the shared library name is libada_ado_mysql.so.
| Driver name | Database |
|---|---|
| mysql | MySQL, MariaDB |
| sqlite | SQLite |
| postgresql | PostgreSQL |
The database drivers are initialized automatically but in some cases, you may want to control some database driver configuration parameter. In that case, the initialization must be done only once before creating a session factory and getting a database connection. The initialization can be made using a property file which contains the configuration for the database drivers and the database connection properties. For such initialization, you will have to call one of the Initialize operation from the ADO.Drivers package.
ADO.Drivers.Initialize ("db.properties");The set of configuration properties can be set programatically and passed to the Initialize operation.
Config : Util.Properties.Manager;
...
Config.Set ("ado.database", "sqlite:///mydatabase.db");
Config.Set ("ado.queries.path", ".;db");
ADO.Drivers.Initialize (Config);Once initialized, a configuration property can be retrieved by using the Get_Config operation.
URI : constant String := ADO.Drivers.Get_Config ("ado.database");Dynamic loading of database drivers is disabled by default for security reasons and it can be enabled by setting the following property in the configuration file:
ado.drivers.load=trueDynamic loading is triggered when a database connection string refers to a database driver which is not known.
The MySQL database driver can be initialize explicitly by using the ado_mysql GNAT project and calling the initialization procedure.
ADO.Mysql.Initialize ("db.properties");The set of configuration properties can be set programatically and passed to the Initialize operation.
Config : Util.Properties.Manager;
...
Config.Set ("ado.database", "mysql://localhost:3306/ado_test");
Config.Set ("ado.queries.path", ".;db");
ADO.Mysql.Initialize (Config);The MySQL database driver supports the following properties:
| Name | Description |
|---|---|
| user | The user name to connect to the server |
| password | The user password to connect to the server |
| socket | The optional Unix socket path for a Unix socket base connection |
| encoding | The encoding to be used for the connection (ex: UTF-8) |
The SQLite database driver can be initialize explicitly by using the ado_mysql GNAT project and calling the initialization procedure.
ADO.Sqlite.Initialize ("db.properties");The set of configuration properties can be set programatically and passed to the Initialize operation.
Config : Util.Properties.Manager;
...
Config.Set ("ado.database", "sqlite:///regtests.db?synchronous=OFF&encoding=UTF-8");
Config.Set ("ado.queries.path", ".;db");
ADO.Sqlite.Initialize (Config);The SQLite database driver will pass all the properties as SQLite pragma allowing the configuration of the SQLite database.
The PostgreSQL database driver can be initialize explicitly by using the ado_mysql GNAT project and calling the initialization procedure.
ADO.Postgresql.Initialize ("db.properties");The set of configuration properties can be set programatically and passed to the Initialize operation.
Config : Util.Properties.Manager;
...
Config.Set ("ado.database", "postgresql://localhost:5432/ado_test?user=ado&password=ado");
Config.Set ("ado.queries.path", ".;db");
ADO.Postgresql.Initialize (Config);The PostgreSQL database driver supports the following properties:
| Name | Description |
|---|---|
| user | The user name to connect to the server |
| password | The user password to connect to the server |
The database connection string is an URI that specifies the database driver to use as well as the information for the database driver to connect to the database. The driver connection is a string of the form:
driver://[host][:port]/[database][?property1][=value1]...The database connection string is passed to the session factory that maintains connections to the database (see ADO.Sessions.Factory).
The session factory is the entry point to obtain a database session. The ADO.Sessions.Factory package defines the factory for creating sessions.
with ADO.Sessions.Factory;
...
Sess_Factory : ADO.Sessions.Factory;The session factory can be initialized by using the Create operation and by giving a URI string that identifies the driver and the information to connect to the database. The session factory is created only once when the application starts.
ADO.Sessions.Factory.Create (Sess_Factory, "mysql://localhost:3306/ado_test?user=test");Having a session factory, one can get a database by using the Get_Session or Get_Master_Session function. Each time this operation is called, a new session is returned. The session is released when the session variable is finalized.
DB : ADO.Sessions.Session := Sess_Factory.Get_Session;The session factory is also responsible for maintaining some data that is shared by all the database connections. This includes:
the sequence generators used to allocate unique identifiers for database tables,
the entity cache,
some application specific global cache.
The ADO cache manager allows to create and maintain cache of values and use the cache from the SQL expander to replace cached values before evaluating the SQL. The SQL expander identifies constructs as follows:
$cache_name[entry-name]and look for the cache identified by cache_name and then replace the cache entry registered with the name entry-name.
The cache manager is represented by the Cache_Manager type and the database session contains one cache manager. Applications may use their own cache in that case they will declare their cache as follows:
M : ADO.Caches.Cache_Manager;A cache group is identified by a unique name and is represented by the Cache_Type base class. The cache group instance is registered in the cache manager by using the Add_Cache operation.
The ADO.Statements package provides high level operations to construct database statements and execute them. They allow to represent SQL statements (prepared or not) and provide support to execute them and retreive their result. The SQL statements are represented by several Ada types depending on their behavior:
The Statement type represents the base type for all the SQL statements.
The Query_Statement type is intended to be used for database query statements and provides additional operations to retrieve results.
The Update_Statement type targets the database update statements and it provides specific operations to update fields. The Insert_Statement extends the Update_Statement type and is intended for database insertion.
The Delete_Statement type is intended to be used to remove elements from the database.
The database statements are created by using the database session and by providing the SQL or the named query to be used.
Query parameters are represented by the Parameter type which can represent almost all database types including boolean, numbers, strings, dates and blob. Parameters are put in a list represented by the Abstract_List or List types.
A parameter is added by using either the Bind_Param or the Add_Param operation. The Bind_Param operation allows to specify either the parameter name or its position. The Add_Param operation adds the parameter at end of the list and uses the last position. In most cases, it is easier to bind a parameter with a name as follows:
Query.Bind_Param ("name", "Joe");and the SQL can use the following construct:
SELECT * FROM user WHERE name = :nameWhen the Add_Param is used, the parameter is not associated with any name but it as a position index. Setting a parameter is easier:
Query.Add_Param ("Joe");but the SQL cannot make any reference to names and must use the ? construct:
SELECT * FROM user WHERE name = ?The parameter expander is a mechanism that allows to replace or inject values in the SQL query by looking at an operation provided by the Expander interface. Such expander is useful to replace parameters that are global to a session or to an application.
The database query statement is represented by the Query_Statement type. The Create_Statement operation is provided on the Session type and it gets the SQL to execute as parameter. For example:
Stmt : ADO.Statements.Query_Statement := Session.Create_Statement
("SELECT * FROM user WHERE name = :name");After the creation of the query statement, the parameters for the SQL query are provided by using either the Bind_Param or Add_Param procedures as follows:
Stmt.Bind_Param ("name", name);Once all the parameters are defined, the query statement is executed by calling the Execute procedure:
Stmt.Execute;Several operations are provided to retrieve the result. First, the Has_Elements function will indicate whether some database rows are available in the result. It is then possible to retrieve each row and proceed to the next one by calling the Next procedure. The number of rows is also returned by the Get_Row_Count function. A simple loop to iterate over the query result looks like:
while Stmt.Has_Elements loop
Id := Stmt.Get_Identifier (1);
...
Stmt.Next;
end loop;Ada Database Objects provides a small framework which helps in using complex SQL queries in an application by using named queries. The benefit of the framework are the following:
The SQL query result are directly mapped in Ada records,
It is easy to change or tune an SQL query without re-building the application,
The SQL query can be easily tuned for a given database.
The database query framework uses an XML query file:
The XML query file defines a mapping that represents the result of SQL queries,
The XML mapping is used by Dynamo code generator to generate an Ada record,
The XML query file also defines a set of SQL queries, each query being identified by a unique name,
The XML query file is read by the application to obtain the SQL query associated with a query name,
The application uses the List procedure generated by Dynamo.
The XML query file uses the query-mapping root element. It should define at most one class mapping and several query definitions. The class definition should come first before any query definition.
<query-mapping>
<class>...</class>
<query>...</query>
</query-mapping>The XML query mapping is very close to the database XML table mapping. The difference is that there is no need to specify any table name nor any SQL type. The XML query mapping is used to build an Ada record that correspond to query results. Unlike the database table mapping, the Ada record will not be tagged and its definition will expose all the record members directly.
The following XML query mapping:
<query-mapping>
<class name='Samples.Model.User_Info'>
<property name="name" type="String">
<comment>the user name</comment>
</property>
<property name="email" type="String">
<comment>the email address</comment>
</property>
</class>
</query-mapping>will generate the following Ada record and it will instantiate the Ada container Vectors generic to provide a support for vectors of the record:
package Samples.Model is
type User_Info is record
Name : Unbounded_String;
Email : Unbounded_String;
end record;
package User_Info_Vectors is
new Ada.Containers.Vectors (Index_Type => Natural,
Element_Type => User_Info,
"=" => "=");
subtype User_Info_Vector is User_Info_Vectors.Vector;
end Samples.Model;A List operation is also generated and can be used to execute an SQL query and have the result mapped in the record.
The same query mapping can be used by different queries.
After writing or updating a query mapping, it is necessary to launch the Dynamo code generator to generate the corresponding Ada model.
The XML query file defines a list of SQL queries that the application can use. Each query is associated with a unique name. The application will use that name to identify the SQL query to execute. For each query, the file also describes the SQL query pattern that must be used for the query execution.
<query-mapping>
<query name='user-list' class='Samples.Model.User_Info'>
<sql driver='mysql'>
SELECT u.name, u.email FROM user AS u
</sql>
<sql driver='sqlite'>
...
</sql>
<sql-count driver='mysql'>
SELECT COUNT(*) FROM user AS u
</sql-count>
</query>
</query-mapping>The query contains basically two SQL patterns. The sql element represents the main SQL pattern. This is the SQL that is used by the List operation. In some cases, the result set returned by the query is limited to return only a maximum number of rows. This is often use in paginated lists.
The sql-count element represents an SQL query to indicate the total number of elements if the SQL query was not limited.
The sql and sql-count XML element can have an optional driver attribute. When defined, the attribute indicates the database driver name that is specific to the query. When empty or not defined, the SQL is not specific to a database driver.
For each query, the Dynamo code generator generates a query definition instance which can be used in the Ada code to be able to use the query. Such instance is static and readonly and serves as a reference when using the query. For the above query, the Dynamo code generator generates:
package Samples.User.Model is
Query_User_List : constant ADO.Queries.Query_Definition_Access;
private
...
end Samples.User.Model;When a new query is added, the Dynamo code generator must be launched to update the generated Ada code.
In order to use a named query, it is necessary to create a query context instance and initialize it. The query context holds the information about the query definition as well as the parameters to execute the query. It provides a Set_Query and Set_Count_Query operation that allows to configure the named query to be executed. It also provides all the Bind_Param and Add_Param operations to allow giving the query parameters.
with ADO.Sessions;
with ADO.Queries;
...
Session : ADO.Sessions.Session := Factory.Get_Session;
Query : ADO.Queries.Context;
Users : Samples.User.Model.User_Info_Vector;
...
Query.Set_Query (Samples.User.Model.Query_User_List);
Samples.User.Model.List (Users, Session, Query);To use the sql-count part of the query, you will use the Set_Count_Query with the same query definition. You will then create a query statement from the named query context and run the query. Since the query is expected to contain exactly one row, you can use the Get_Result_Integer to get the first row and column result. For example:
Query.Set_Count_Query (Samples.User.Model.Query_User_List);
...
Stmt : ADO.Statements.Query_Statement
:= Session.Create_Statement (Query);
...
Stmt.Execute;
...
Count : Natural := Stmt.Get_Result_Integer;You may also use the ADO.Datasets.Get_Count operation which simplifies these steps in:
Query.Set_Count_Query (Samples.User.Model.Query_User_List);
...
Count : Natural := ADO.Datasets.Get_Count (Session, Query);A big benefit when using ADO is the model mapping with the Ada and SQL code generator.
The model describes the database tables, their columns and relations with each others. It is then used to generate the Ada implementation which provides operations to create, update and delete records from the database and map them in Ada transparently.
The model can be defined in:
This chapter focuses on the YAML description.
In YAML, the type definition follows the pattern below:
<table-type-name>:
type: entity
table: <table-name>
description: <description>
hasList: true|false
indexes:
id:
fields:
oneToOne:
oneToMany:The table-type-name represents the Ada type name with the full package specification. The code generator will add the _Ref prefix to the Ada type name to define the final type with reference counting. A private type is also generated with the _Impl prefix.
The YAML fields have the following meanings:
| Field | Description |
|---|---|
| type | Must be 'entity' to describe a database table |
| table | The name of the database table. This must be a valid SQL name |
| description | A comment description for the table and type definition |
| hasList | When true, a List operation is also generated for the type |
| indexes | Defines the indexes for the table |
| id | Defines the primary keys for the table |
| fields | Defines the simple columns for the table |
| oneToOne | Defines the one to one table relations |
| oneToMany | Defines the one to many table relations |
Simple columns are represented within the fields section.
<table-type-name>:
fields:
<member-name>:
type: <type>
length: <length>
description: <description>
column: <column-name>
not-null: true|false
unique: true|false
readonly: true|false
version: falseThe YAML fields have the following meanings:
| Field | Description |
|---|---|
| type | The column type. This type maps to an Ada type and an SQL type |
| length | For variable length columns, this is the maximum length of the column |
| description | A comment description for the table and type definition |
| column | The database table column name |
| not-null | When true, indicates that the column cannot be null |
| unique | When true, indicates that the column must be unique in the table rows |
| readonly | When true, the column cannot be updated. The Save operation will ignore updated. |
| version | Must be 'false' for simple columns |
The type column describes the type of the column using a string that is agnostic of the Ada and SQL languages. The mapping of the type to SQL depends on the database. The not-null definition has an impact on the Ada type since when the column can be null, a special Ada type is required to represent that null value.
The ADO.Nullable_X types are all represented by the following record:
type Nullable_X is record
Value : X := <default-value>;
Is_Null : Boolean := True;
end record;The Is_Null boolean member must be checked to see if the value is null or not. The comparison operation (=) ignores the Value comparison when one of the record to compare has Is_Null set.
| Type | not-null | SQL | Ada |
|---|---|---|---|
| boolean | true | TINYINT | Boolean |
| false | TINYINT | ADO.Nullable_Boolean | |
| byte | true | TINYINT | - |
| false | TINYINT | - | |
| integer | true | INTEGER | Integer |
| false | INTEGER | ADO.Nullable_Integer | |
| long | true | BIGINT | Long_Long_Integer |
| false | BIGINT | ADO.Nullable_Long_Integer | |
| identifier | BIGINT | ADO.Identifier | |
| entity_type | true | INTEGER | ADO.Entity_Type |
| false | INTEGER | ADO.Nullable_Entity_Type | |
| string | true | VARCHAR(N) | Unbounded_String |
| false | VARCHAR(N) | ADO.Nullable_String | |
| date | true | DATE | Ada.Calendar.Time |
| false | DATE | ADO.Nullable_Time | |
| time | true | DATETIME | Ada.Calendar.Time |
| false | DATETIME | ADO.Nullable_Time | |
| blob | BLOB | ADO.Blob_Ref |
The identifier type is used to represent a foreign key mapped to a BIGINT in the database. It is always represented by the Ada type ADO.Identifier and the null value is represented by the special value ADO.NO_IDENTIFIER.
The blob type is represented by an Ada stream array held by a reference counted object. The reference can be null.
The entity_type type allows to uniquely identify the type of a database entity. Each database table is associated with an entity_type unique value. Such value is created statically when the database schema is created and populated in the database. The entity_type values are maintained in the entity_type ADO database table.
Primary keys are used to uniquely identify a row within a table. For the ADO framework, only the identifier and string primary types are supported.
<table-type-name>:
id:
<member-name>:
type: {identifier|string}
length: <length>
description: <description>
column: <column-name>
not-null: true
unique: true
version: false
generator:
strategy: {none|auto|sequence}The generator section describes how the primary key is generated.
| Strategy | description |
|---|---|
| none | the primary key is managed by the application |
| auto | use the database auto increment support |
| sequence | use the ADO sequence generator |
A one to many relation is described by the following YAML description:
<table-type-name>:
oneToMany:
<member-name>:
type: <model-type>
description: <description>
column: <column-name>
not-null: true|false
readonly| true|falseThis represents the foreign key and this YAML description is to be put in the table that holds it.
The type definition describes the type of object at the end of the relation. This can be the identifier type which means the relation will not be strongly typed and mapped to the ADO.Identifier type. But it can be the table type name used for another table definition. In that case, the code generator will generate a getter and setter that will use the object reference instance.
Circular dependencies are allowed within the same Ada package. That is, two tables can reference each other as long as they are defined in the same Ada package. A relation can use a reference of a type declared in another YAML description from another Ada package. In that case, with clauses are generated to import them.
Optimistic locking is a mechanism that allows updating the same database record from several transactions without having to take a strong row lock that would block transactions. By having a version column that is incremented after each change, it is possible to detect that the database row was modified when we want to update it. When this happens, the optimistic lock exception ADO.Objects.LAZY_LOCK is raised and it is the responsibility of the application to handle the failure by retrying the update.
For the optimistic locking to work, a special integer based column must be declared.
<table-type-name>:
fields:
<member-name>:
type: <type>
description: <description>
column: <column-name>
not-null: true
unique: false
version: trueThe generated Ada code gives access to the version value but it does not allow its modification. The version column is incremented only by the Save procedure and only if at least one field of the record was modified (otherwise the Save has no effect). The version number starts with the value 1. ## Objects When a database table is mapped into an Ada object, the application holds a reference to that object through the Object_Ref type. The Object_Ref tagged type is the root type of any database record reference. Reference counting is used so that the object can be stored, shared and the memory management is handled automatically. It defines generic operations to be able to:
load the database record and map it to the Ada object,
save the Ada object into the database either by inserting or updating it,
delete the database record.
The Dynamo code generator will generate a specific tagged type for each database table that is mapped. These tagged type will inherit from the Object_Ref and will implement the required abstract operations. For each of them, the code generator will generate the Get_X and Set_X operation for each column mapped in Ada.
Before the Object_Ref is a reference, it does not hold the database record itself. The ADO.Objects.Object_Record tagged record is used for that and it defines the root type for the model representation. The type provides operations to modify a data field of the record while tracking its changes so that when the Save operation is called, only the data fields that have been modified are updated in the database. An application will not use nor access the Object_Record. The Dynamo code generator generates a private type to make sure it is only accessed through the reference.
Several predicate operations are available to help applications check the validity of an object reference:
| Function | Description |
|---|---|
| Is_Null | When returning True, it indicates the reference is NULL. |
| Is_Loaded | When returning True, it indicates the object was loaded from the database. |
| Is_Inserted | When returning True, it indicates the object was inserted in the database. |
| Is_Modified | When returning True, it indicates the object was modified and must be saved. |
Let's assume we have a User_Ref mapped record, an instance of the reference would be declared as follows:
with Samples.User.Model;
...
User : Samples.User.Model.User_Ref;After this declaration, the reference is null and the following assumption is true:
User.Is_Null and not User.Is_Loaded and not User.Is_InsertedIf we set a data field such as the name, an object is allocated and the reference is no longer null.
User.Set_Name ("Ada Lovelace");After this statement, the following assumption is true:
not User.Is_Null and not User.Is_Loaded and not User.Is_InsertedWith this, it is therefore possible to identify that this object is not yet saved in the database. After calling the Save procedure, a primary key is allocated and the following assumption becomes true:
not User.Is_Null and not User.Is_Loaded and User.Is_InsertedThree operations are generated by the Dynamo code generator to help in loading a object from the database: two Load procedures and a Find procedure. The Load procedures are able to load an object by using its primary key. Two forms of Load are provided: one that raises the ADO.Objects.NOT_FOUND exception and another that returns an additional Found boolean parameter. Within the application, if the database row is expected to exist, the first form should be used. In other cases, when the application expects that the database record may not exist, the second form is easier and avoids raising and handling an exception for a common case.
User.Load (Session, 1234);The Find procedure allows to retrieve a database record by specifying a filter. The filter object is represented by the ADO.SQL.Query tagged record. A simple query filter is declared as follows:
Filter : ADO.SQL.Query;The filter is an SQL fragment that is inserted within the WHERE clause to find the object record. The filter can use parameters that are configured by using the Bind_Param or Add_Param operations. For example, to find a user from its name, the following filter could be set:
Filter.Set_Filter ("name = :name");
Filter.Bind_Param ("name", "Ada Lovelace");Once the query filter is initialized and configured with its parameters, the Find procedure can be called:
Found : Boolean;
...
User.Find (Session, Filter, Found);The Find procedure does not raise an exception if the database record is not found. Instead, it returns a boolean status in the Found output parameter. The Find procedure will execute an SQL SELECT statement with a WHERE clause to retrieve the database record. The Found output parameter is set when the query returns exactly one row.
To modify an object, applications will use one of the Set_X operation generated for each mapped column. The ADO runtime will keep track of which data fields are modified. The Save procedure must be called to update the database record. When calling it, an SQL UPDATE statement is generated to update the modified data fields.
User.Set_Status (1);
User.Save (Session);Deleting objects is made by using the Delete operation.
User.Delete (Session);Sometimes you may want to delete an object without having to load it first. This is possible by delete an object without loading it. For this, set the primary key on the object and call the Delete operation:
User.Set_Id (42);
User.Delete (Session);The sequence generator is responsible for creating unique ID's across all database objects.
Each table can be associated with a sequence generator. The sequence factory is shared by several sessions and the implementation is thread-safe.
The HiLoGenerator implements a simple High Low sequence generator by using sequences that avoid to access the database.
Example:
F : Factory;
Id : Identifier;
...
Allocate (Manager => F, Name => "user", Id => Id);The HiLo sequence generator. This sequence generator uses a database table sequence to allocate blocks of identifiers for a given sequence name. The sequence table contains one row for each sequence. It keeps track of the next available sequence identifier (in the `value column).
To allocate a sequence block, the HiLo generator obtains the next available sequence identified and updates it by adding the sequence block size. The HiLo sequence generator will allocate the identifiers until the block is full after which a new block will be allocated.
The ADO runtime uses the logging framework provided by Ada Utility Library. By default, logging messages are disabled and the logging framework has a negligeable impact on performance (less than 1 us per log).
You can customize the logging framework so that you activate logs according to your needs. In the full mode, the ADO runtime will report the SQL statements which are executed.
To control the logging, add or update the following definitions in a property file:
log4j.rootCategory=DEBUG,console,result
log4j.appender.console=Console
log4j.appender.console.level=WARN
log4j.appender.console.layout=level-message
log4j.appender.result=File
log4j.appender.result.File=test.log
# Logger configuration
log4j.logger.ADO=INFO,result
log4j.logger.ADO.Sessions=WARN
log4j.logger.ADO.Statements=DEBUGThe logging framework is configured by using the Util.Log.Logging.Initialize operation:
Util.Log.Loggers.Initialize ("config.properties");which can be executed from any place (but the best place is during the application start).
You can also configure the logger in Ada by using the following code:
with Util.Properties;
...
Log_Config : Util.Properties.Manager;
...
Log_Config.Set ("log4j.rootCategory", "DEBUG,console");
Log_Config.Set ("log4j.appender.console", "Console");
Log_Config.Set ("log4j.appender.console.level", "ERROR");
Log_Config.Set ("log4j.appender.console.layout", "level-message");
Log_Config.Set ("log4j.logger.Util", "FATAL");
Log_Config.Set ("log4j.logger.ADO", "ERROR");
Log_Config.Set ("log4j.logger.ADO.Statements", "DEBUG");
Util.Log.Loggers.Initialize (Log_Config); The ADO runtime has several loggers, each of them can be activated separately. The following loggers are interesting:
| Logger name | Description |
|---|---|
| ADO.Drivers | Database drivers and connection to servers |
| ADO.Sessions | Database session management |
| ADO.Statements | SQL statements execution |
| ADO.Queries | Named queries identification and retreival |
Some exceptions are raised when there is a serious problem. The problem could be of different nature:
The ADO.Sessions.Connection_Error exception is raised when the connection string used to access the database is incorrect. The connection string could be improperly formatted, a database driver may not be found, the database server may not be reachable.
The ADO.Sessions.Session_Error exception is raised when the Session object is used while it is not initialized or the connection was closed programatically.
The ADO.Queries.Query_Error exception is raised when a named query cannot be found. In that case, the SQL that corresponds to the query cannot be executed.
The ADO.Statements.SQL_Error exception is raised when the execution of an SQL query fails. This is an indication that the SQL statement is invalid and was rejected by the database.
The ADO.Statements.Invalid_Column exception is raised after the execution of an SQL query when the application tries to access the result. It is raised when the program tries to retrieve a column value that does not exist.
The ADO.Statements.Invalid_Type exception is also raised after the execution of an SQL query when the value of a column cannot be converted to the Ada type. It occurs if a column contains a string while the application tries to get the column as an integer or date. Similarly, if a column is null and the returned Ada type does not support the nullable concept, this exception will be raised.
The ADO.Statements.Invalid_Statement exception is raised when you try to use and execute a Statement object which is not initialized.
The object layer provided by ADO raises specific exceptions.
The ADO.Objects.NOT_FOUND exception is raised by the generated Load procedure when an object cannot be found in the database.
The ADO.Objects.INSERT_ERROR exception is raised by the generated Save procedure executed the SQL INSERT statement and its execution failed.
The ADO.Objects.UPDATE_ERROR exception is raised by the generated Save procedure executed the SQL UPDATE statement and its execution failed.
The ADO.Objects.LAZY_LOCK exception is raised by the generated Save procedure executed the SQL UPDATE statement failed and the version of the object was changed.