Language and development rules: Difference between revisions

Jump to navigation Jump to search

Language and development rules (view source)

Revision as of 10:24, 7 October 2012

6,156 bytes added ,  14 years ago
→‎SQL rules
VisualWikitext
Line 72: Line 72:


= SQL rules =
= SQL rules =
== Table strutures ==
* Structure of tables.
* Structure of tables.
When you create a new table, it is recommanded to use same conventions than other Dolibarr tables. This means the following fields:
When you create a new table, it is recommanded to use same conventions than other Dolibarr tables. This means the following fields:
Line 82: Line 85:
  - fk_user_modif integer that is the id of user making change
  - fk_user_modif integer that is the id of user making change


* Type of fields:
Well, to be compatible with any accurancy required by any country on amounts, and with any database syntax, we will use the following type into database:
- 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+tie
- date for a field date
* 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.
'''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.
== 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 ==
A foreign key name must start with prefix <tt>fk_</tt> followed by the table name linked (this is required to avoid duplicate names in project that are not allowed even if on different tables by some DBMS like Postgresql) then the name of child field (this is to allow to have several foreign keys on same table).
Example:
''fk_facture_fourn_fk_soc''  is a foreign key on table llx_facture_fourn for field fk_soc in this table (that link to 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
== 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.
== Examples ==
=== File of table llx_mytable.sql ===
<source lang="sql">
-- ===========================================================================
-- Copyright (C) 2008 Author <email@author.com>
--
-- This program is free software; you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation; either version 2 of the License, or
-- (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
--
-- $Id: llx_mytable.sql,v 1.23 2005/10/08 15:59:49 eldy Exp $
-- ===========================================================================
create table llx_mytable
(
  rowid      integer AUTO_INCREMENT PRIMARY KEY,
  field_one  integer,
  field_two  integer NOT NULL,
  fk_field    integer,
  field_date  datetime,
  tms        timestamp
)type=innodb;
</source>
=== File for keys/indexs on llx_mytable.key.sql ===
<source lang="sql">
-- ===========================================================================
-- Copyright (C) 2008 Author <email@author.com>
--
-- This program is free software; you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation; either version 2 of the License, or
-- (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
--
-- $Id: llx_matable.sql,v 1.23 2005/10/08 15:59:49 eldy Exp $
-- ===========================================================================
ALTER TABLE llx_matable ADD UNIQUE uk_matable_field(field_one, field_two);
ALTER TABLE llx_matable ADD CONSTRAINT fk_matable_fk_field FOREIGN KEY (fk_field) REFERENCES llx_matablepere (rowid);
</source>
== SQL Coding rules ==
* 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 98: Line 219:
'MU' (for unit prices), 'MT' (for total prices) or 'MS' (otherwise) depending on usage of number. (see function documentation)
'MU' (for unit prices), 'MT' (for total prices) or 'MS' (otherwise) depending on usage of number. (see function documentation)


* Type of fields:
* Functions NOW or SYSDATE are forbidden inside SQL requests. If you must use the current date into a field, value must come from the PHP and not from the database engine. This is to avoid a better portability of code and a correct management of TimeZone.
Well, to be compatible with any accurency required by any country on amounts, and with any database syntax, we will use the following type into database:
 
- double(24,8) for any amount
== Mysql specificities ==
- double(6,3) for any vat rate
 
- real for a quantity
* Tables must be declared with format InnoDB.
- varchar for a string (alos if length is 1, the type char is more and more deprecated)
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.
- timestamp for a field date+time that is automatically updated
- datetime for a field date+tie
- date for a field date


* Dolibarr must works even if Mysql option '''strict''' is active.
* Dolibarr must works even if Mysql option '''strict''' is active.
Line 114: Line 232:
</source>
</source>


* Functions NOW or SYSDATE are forbidden inside SQL requests. If you must use the current date into a field, value must come from the PHP and not from the database engine. This is to avoid a better portability of code and a correct management of TimeZone.
== PostgeSQL specificities ==
Only Mysql SQL files must be maintained.
Those files are converted "on the fly" by the database Dolibarr driver.


= HTML norms =
= HTML norms =
Retrieved from "https://wiki.dolibarr.org/index.php/Special:MobileDiff/27724"

Navigation menu