ADO_Queries

Database Queries

Introduction

Ada Database Objects provides a small framework which helps in using complex SQL queries in an application. 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 [http://code.google.com/p/ada-gen Dynamo] 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 [http://code.google.com/p/ada-gen Dynamo].

XML Query File and Mapping

XML Query File

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>

SQL Result Mapping

The XML query mapping is very close to the database table mapping. The difference is that there is no need to specify and 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:

package Samples.Model is
   type User_Info is record
     Name  : Unbounded_String;
     Email : Unbounded_String;
   end record;
end Samples.Model;

The same query mapping can be used by different queries.

SQL Queries

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 name='xxx' class='Samples.Model.User_Info'>
   <sql driver='mysql'>
     select u.name, u.email from user
   </sql>
   <sql driver='sqlite'>
      ...
   </sql>
   <sql-count driver='mysql'>
      select count(*) from user u
   </sql-count>
</query>

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.

---

Generated by Dynamo from ado-queries.ads