Quick links: Content - sections - sub sections
EN FR

In order to use jDao, you have to write an XML file which describes the mapping, i.e, which properties of a jDao record correspond to which field in a SQL table.

Creating a file automatically

A DAO file can be generated by the jelix script on the command line, from an existing table.


 php cmd.php createdao modulename daoname tablename sequencename

For example, if you want to create a "product" dao from the table "shop_product" in the "myshop" module, you will enter:


 php cmd.php createdao myshop product shop_product

In the daos directory of the myshop module, you will have then a product.dao.xml file which contain the mapping description. The table name is optional if you want to use the table name for the dao name, and if you don't have a sequence. You can indicate the name of the sequence used to auto increment the primary key (oracle databases and pgsql).

The automatic generation of a dao file helps to create quickly, but you will have often to modify this files to customize the mapping, to add method declaration etc..

Details on the XML format


<dao xmlns="http://jelix.org/ns/dao/1.0">
   <datasources>
     datasources section
   </datasources>
   <record>
      properties section
   </record>
   <factory>
      methods section
   </factory>
</dao>

There are three sections, although the <factory> section is optional. This section is described in an other chapter.

  • <datasources> : defines the tables on which the object will be mapped.
  • <record> : defines the mapping itself.

Simple mapping

Table declaration

We call simple mapping, a mapping where a record is mapped to a single table. To declare the table, we use the tag <primarytable> with the following attributes:

  • name: alias given to the table and that will be used in SQL queries
  • realname (optional): real name of the table in the database. If this attribute is not specified, it takes the same value as the attribute name. In this case name must contain the real name of the table.
  • primarykey indicates the primary key. You can specify multiple fields, separated by a space or a comma.

  <datasources>
      <primarytable name="p" realname="products" primarykey="id_product" />
  </datasources>

It says here that the record will be based on the table products, which has the alias p, and which contains the primary key id_product.

There is always a single table "primary" in a DAO (hence a single tag <primarytable>). You will see that you can specify other tables (foreign tables) farther.

Then we should declare the mapping between the record fields and the object properties.

Properties declaration

The <record> section declares the properties of an object record. Each property corresponds to a field in the primary table, or one of those foreign tables as you will see later. Of course, you are not obliged to declare a property for all existing fields. We can make several DAO working on the same table but which are intended for different uses. For example make a specific DAO to recover slight registration lists (so the mapping is made on the essential properties), and an other to manage full datas (so the mapping is made on all fields).

The <record> section must contain one or more <property> element. Here is the <property> element with possible attributes:


  <property 
      name="simplificated name" 
      fieldname="filed name" 
      datatype="" required="true/false" minlength="" maxlength="" regexp="" 
      sequence="sequence name" autoincrement="true/false"
      updatepattern="" insertpattern="" selectpattern=""
      default="" 
   />

The attribute name is the name of the property of the object.

The attribute fieldname is the field name on which the property is mapped. If name and fieldname are equals, we can leave fieldname.

The attributes datatype, required, minlength, maxlength and regexp are constraints. This allows the check() method to verify the values of properties (before storage for instance).

The attribute default allows you to specify a default value which will be stored in the property.

The attribute datatype indicates the type of the field. Here is the list of recognized types. You can see that there are many more types than in previous jelix version:

  • varchar, varchar2, nvarchar2, character, character varying, char, nchar, name, longvarchar, string (deprecated),
  • int, integer, tinyint, smallint, mediumint, bigint,
  • serial, bigserial, autoincrement, bigautoincrement,
  • double, double precision, float, real, number, binary_float, binary_double, money,
  • numeric, decimal, dec,
  • date, time, datetime, timestamp, utimestamp, year, interval
  • boolean, bool, bit
  • tinytext, text, mediumtext, longtext, long, clob, nclob
  • tinyblob, blob, mediumblob, longblob, bfile,
  • varbinary, bytea, binary, raw, long raw
  • enum, set, xmltype, point, line, lsed, box, path, polygon, circle, cidr, inet, macaddr, bit varyong, arrays, complex types

Of course, some database doesn't recognize all of these types. Don't worry, jDao will use the corresponding type in the selected database. For example, bytea is a postgresql type. If you use a mysql database, jDao will use longblob.

Values of fields will be converted in the corresponding PHP type, most of time, in a string.

For autoincremented fields, you can indicate serial or autoincrement as a type. But for some case, it is better to indicate one of integer types, and to set a autoincrement attribute to true. On some databases, an auto incremented field can be associated with a sequence. Then the attribute sequence should contain the sequence name.

The attributes updatepattern, insertpattern and selectpattern lets you specify a pattern to be applied during the update, the insert or the read of the field value. This pattern should really be a SQL expression, and can contain the string "%s", which will be replaced by the value or the name of the field. Default values of patterns is "%s". If it indicates an empty value, this corresponds to a null operation (so the field is not readed, inserted or updated). On primary keys you can use insertpattern and selectpattern , but not updatepattern.

Example 1

For a field which contains an updated date, we can do:


  <property name="date_update" datatype="datetime" insertpattern="NOW()" updatepattern="NOW()" />

So each time there is an insert or an update, the inserted value will be the current date.

Example 2

It may also have a property that does not correspond directly to a field, but that is the result of a SQL expression. In this case, you must disable the inserting and updating.


   <property Name="identite" datatype="string" selectpattern="CONCAT(name,' ',firstname)" insertpattern="" updatepattern="" />

Carefull about the content of selectpattern:

  • Expression must use fields of a single table. If a dao is based on multiple tables (for example, A and B, see next section), it is not possible to indicate both fields from the table A and the table B in the same selectpattern.
  • If the expression uses some fields of the table B (a foreign table), then corresponding properties should be declared for this table, with the table attribute on the <property> element, with the name or alias of the table B, as a value.

Mapping on several tables

We can declare a table, but also additionnal tables which are linked to the main table by joins. It is useful when you want to retrieve simultaneously a record and informations of other tables. For example, if you want to retrieve a product of the "products" table, and at the same time the name of its category from the table "category", you should also declared the table "category". Note that you can modify only data which come from the main table when you want to update or insert a record.

To declare such foreign tables, which are logically related to the main table by foreign keys, you should use:

  • <foreigntable> to indicate a foreign table linked by a normal join.
  • <optionalforeigntable> to indicate a foreign table linked by an outer join.

Example


   <primarytable name="p" realname="products" primarykey="id_product" />
   <foreigntable name="cat" realname="categories" primarykey="id_cat" onforeignkey="id_cat" />
   <optionalforeigntable name="man" realname="manufacturers" primarykey="id" onforeignkey="id_manufacturer" />

As for tag <primarytable>, there are attributes name, realname and primarykey. There is also an additional attribute, <onforeignkey>, which indicates the name of the field in the primary table, which is the foreign key on the table in question. Thus, with the above example, jDao generate requests for type SELECT terms FROM and WHERE:


 FROM products as p left join manufacturers as man on (p.id_manufacturer = man.id),  categories as cat
 WHERE cat.id_cat = p.id_cat

You can add <property> element to map properties to field of foreign tables, like any other property for the main table. The only difference is that you have to add an attribute table which indicates the alias of the table in which the field belongs to.


  <property 
      name="category_label" 
      fieldname="label" 
      table="cat"
   />