Section: DAO file
| ^ jDao: relational object mapping | Instantiation and use of a dao factory and record » |
− Table of content
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 jelix.php createdao modulename daoname tablename
For example, if you want to create a "product" dao from the table "shop_product" in the "myshop" module, you will enter:
php jelix.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 automatic generation of a dao file helps to create quickly, but you will have often to modify this files to personnalize 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 page.
- 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 = a 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 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 be the real name of the table.
- primarykey indicates the primary key. You can specify multiple keys, 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 ¶
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).
record section must contain one or more tags <property>:
<property
name="simplificated name"
fieldname="filed name"
datatype="" required="true/false" minlength="" maxlength="" regexp=""
sequence="sequence name"
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 (since jelix 1.0RC1) allows you to specify a default value which will be stored in the property.
The attribute datatype can be set to:
- string
- int
- integer
- autoincrement
- double / float
- numeric / bigautoincrement
- date
- time
- datetime
- boolean (jelix 1.0)
On some databases, an autoincrement 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).
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(nom, '',prenom)" 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.
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"
/>
