31,003
edits
Language and development rules: Difference between revisions
Jump to navigation
Jump to search
→SQL rules
m (→PHP) |
|||
| Line 86: | Line 86: | ||
= SQL rules = | = SQL rules = | ||
== DDL file format == | == DDL file format == | ||
| Line 217: | Line 157: | ||
ALTER TABLE llx_mytable ADD CONSTRAINT fk_mytable_fk_field FOREIGN KEY (fk_field) REFERENCES llx_matablepere (rowid); | ALTER TABLE llx_mytable ADD CONSTRAINT fk_mytable_fk_field FOREIGN KEY (fk_field) REFERENCES llx_matablepere (rowid); | ||
</source> | </source> | ||
== Table and fields structures == | |||
* Structure of tables. | |||
When you create a new table, it is recommended to use the same conventions as other Dolibarr tables. This means the following fields: | |||
- rowid INTEGER AUTO_INCREMENT PRIMARY KEY that is technical id of record | |||
- entity INTEGER default 1 that is id for the multicompany feature | |||
- date_creation datetime that is the creation date | |||
- tms timestamp that will contain date of last modification (the database manages this field automatically, no need to manage it by the code, just create the field) | |||
- import_key that will contains the import code YYYYMMDDHHMMSS if you make mass import. | |||
Eventually | |||
- fk_user_creat integer that is the id of user making creation | |||
- fk_user_modif integer that is the id of user making change | |||
- fk_user_valid integer that is the id of user validating record | |||
- fk_soc integer that is the ID of the Third party (if applicable) | |||
Note field: | |||
- note_private text for private comment of the object | |||
- note_pubic text for public comment of the object | |||
or | |||
- note text if there is no need to have private and public | |||
* Type of fields: | |||
Well, to be compatible with any accuracy required by any country on amounts, and with any database syntax, we will use the following type into database: | |||
- integer for an any of foreign key | |||
- double(24,8) for any amount | |||
- double(6,3) for any vat rate | |||
- real for a quantity | |||
- varchar for a string (also if length is 1, the type char is more and more deprecated) | |||
- timestamp for a field date+time that is automatically updated | |||
- datetime for a field date+time | |||
- date for a field date | |||
- text or medium text for large fields (no index allowed on such fields) | |||
All business rules must be on same place, into PHP code, not into client, neither into database, that's why type enum is also not allowed. | |||
Other type are not allowed for compatibility reasons. | |||
* All tables has a prefix to avoid name conflicts with other projects. In current version this prefix is fixed ans can't be modified. Its value is <tt>llx_</tt>. In a future version this value should be modified during installation process. | |||
== Primary keys == | |||
The primary key of a table must be called <tt>rowid</tt>. | |||
Some old tables does not use this rule and use a primary key called <tt>id</tt> (ie [[Table llx_c_actioncomm]]), but the reason is history and this should not happens anymore. | |||
== Foreign keys == | |||
Name of a foreign key must start with the prefix <tt>fk_</tt> followed by the table name (i.e. name of the referencing table) (this is required to avoid duplicate names in project that are not allowed even if in different tables by some DBMS like Postgresql) then the field name (i.e. name of the referencing field) (this is to allow to have several foreign keys in same table). | |||
Example: | |||
''fk_facture_fourn_fk_soc'' is a foreign key in the table llx_facture_fourn for the field fk_soc in this table (that references the rowid field in another table) | |||
Note: If you develop your own external module, it must have no foreign keys that point to Dolibarr standard tables. This will break standard dolibarr upgrades, repair, backup and restore tools and may also break standard features. | |||
== Alternative keys == | |||
Sometimes, we need another unique keys than primary key. We can add in this case an alternate unique key. When we need this, we can create an alternate unique key. Such an index is called by a name that start by prefix <tt>uk_</tt> followed by an underscore, then the the table name (this is required to avoid duplicate names of unique keys that may create problems for some DBMS like Postgresql) and then another string to define the key (this is to allow to have several unique keys on same table). | |||
Example: | |||
''uk_societe_code_client'' is a unique key on table llx_societe on field code_client. | |||
== Index performance == | |||
Some fields are often used as search or order criteria, or for joins. In such case, we need to set a performance index on field to increase performances. Such indexes are named with a prefix <tt>idx_</tt> then the table name and then the field name. | |||
Example: | |||
''idx_societe_user_creat'' is a performance index on table llx_societe for field user_creat | |||
== SQL Coding rules == | == SQL Coding rules == | ||