Section: DAO file
| ^ jDao: relational object mapping | Instantiation and use of a dao factory and record » |
− Table of content
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 queriesrealname(optional): real name of the table in the database. If this attribute is not specified, it takes the same value as the attributename. In this casenamemust contain the real name of the table.primarykeyindicates 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,intervalboolean,bool,bittinytext,text,mediumtext,longtext,long,clob,nclobtinyblob,blob,mediumblob,longblob,bfile,varbinary,bytea,binary,raw,long rawenum,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
tableattribute 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"
/>
