31,016
edits
Language and development rules: Difference between revisions
Jump to navigation
Jump to search
→SQL rules
| Line 96: | Line 96: | ||
* 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. | * 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 == | == Primary keys == | ||
| Line 130: | Line 122: | ||
''idx_societe_user_creat'' is a performance index on table llx_societe for field user_creat | ''idx_societe_user_creat'' is a performance index on table llx_societe for field user_creat | ||
== | == DDL file format == | ||
Files that contain definition of the database structure (DDL files) must be 2 per tables: | |||
< | * Each table is defined by its own file whose name is <tt>llx_''mytable''.sql</tt> | ||
A comment will be added on each field to explain its usage. | |||
</ | * All foreign keys, performance indexes or other constraints will be defined into another file called <tt>llx_''mytable''.key.sql</tt> | ||
Those files must be stored into directory '''install/mysql/tables''' for all standard files or '''mymodule/tables''' for tables provided by an external module. | |||
Examples | |||
=== File of table llx_mytable.sql === | === File of table llx_mytable.sql === | ||
| Line 203: | Line 196: | ||
== SQL Coding rules == | == SQL Coding rules == | ||
* Alias usage/Fields naming | |||
When doing select, we can use alias to simplify writing/reading of requests: | |||
<source lang="sql"> | |||
select chp1, chpxxx2 as chp2 from table2 as t1, table2 as t2 where t1.chpx = t2.chpy | |||
</source> | |||
However, we must not used alias for update request as they are not compatible with Mysql 3.1. | |||
* All SELECT * are forbidden ! Any SELECT must define complete list of fields to get. This avoid confusion. Example: | * All SELECT * are forbidden ! Any SELECT must define complete list of fields to get. This avoid confusion. Example: | ||
<source lang="sql"> | <source lang="sql"> | ||
| Line 222: | Line 222: | ||
== Mysql specificities == | == Mysql specificities == | ||
* Tables must be declared with format InnoDB. | * Tables must be declared with format InnoDB. | ||
This format support foreign keys and their restrictions, and transactions integrity is also supported. This guarantee that Dolibarr events keep all data with correct values between tables even even transaction modify different tables. | This format support foreign keys and their restrictions, and transactions integrity is also supported. This guarantee that Dolibarr events keep all data with correct values between tables even even transaction modify different tables. | ||