29,033
edits
Changes
m
→SQL Coding rules
Line 281:
Line 281:
</syntaxhighlight>
</syntaxhighlight>
−*Into SQL requests, you must quote fields except the fields that contain amounts which must be stored as double or real type. Quotes on numbers may result in saving as a different value. For example 412.62 in an insert will be saved as value 412.61999512 into database (due to implicit conversion string to numeric) if the target field has type double(24,8). Only PHP see value 412.61999512. Other tools will see 412.62 giving a sense that there is no problem. But it's PHP that has the good vision. There is really a wrong value into database. By removing quotes on numbers, no problem occurs.
+*Into SQL requests, you must quote fields except the fields that contain amounts which must be stored as double or real type. Quotes on numbers may result in saving as a different value. For example 412.62 in an insert will be saved as value 412.61999512 into database (due to implicit conversion string to numeric) if the target field has type double(24,8). Only PHP see value 412.61999512. Other tools will see 412.62 letting think that there is no problem. But it's PHP that has the good vision. There is really a wrong value into database. By removing quotes on numbers, no problem occurs.
Example:
Example:
Line 289:
Line 289:
</syntaxhighlight>
</syntaxhighlight>
−Note, problem of float numbers is same problem on all langauges and not only when inserting data into database. It occurs also with any language when you work on "real" numbers, so numbers must be, as soon as they are affected, cleaned with function price2num with second parameter defined to :
+Note, problem of float numbers is same problem on all languages and not only when inserting data into database. It occurs also with any language when you work on "real" numbers, so numbers must be, as soon as they are affected into variable, cleaned with function price2num with second parameter defined to :
'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)
Line 321:
Line 321:
This statement should not be used: it is not handled the same way by different databases.
This statement should not be used: it is not handled the same way by different databases.
Also, using WITH ROLLUP breaks the purity of the returned data. The intermediate subtotal generation aggregation performed by this statement can easily be done using PHP and helps keep the returned data array clean (not corrupted by artificially inserted data).
Also, using WITH ROLLUP breaks the purity of the returned data. The intermediate subtotal generation aggregation performed by this statement can easily be done using PHP and helps keep the returned data array clean (not corrupted by artificially inserted data).
+
+* The sql function GROUP_CONCAT can't be used. It is not portable and ask the database to make subrequests for line into main table, this can be done easier by doing a subrequest inside the PHP loop when building the output of each line.
* Use $db->ifsql for SQL IF
* Use $db->ifsql for SQL IF
Line 328:
Line 330:
* No DELETE CASCADE and ON UPDATE CASCADE
* No DELETE CASCADE and ON UPDATE CASCADE
−Such SQL instructions are forbidden because they bypass the business rules of the application bringing a lot of troubles out of control of the developers. For example, if there is a delete cascade between table A and B, when the application will execute the code to remove a record in A, the children into table B will also be removed. If there was a Dolibarr PHP trigger (for example bring by an external module) on the deletion of record of B (for example to validate the deletion or to execute a complementary action), the DELETE CASCADE will be executed without having the Dolibarr PHP trigger executed, missing the validation or actions of the PHP trigger of the module. All business rules must be implemented on the same side (so the server PHP side), this is the reason why business rules implemented on Database side are not allowed (same conclusion than the next point about Database triggers).
+Such SQL instructions are forbidden because they bypass the business rules of the application bringing a lot of troubles out of control of the developers. For example, if there is a delete cascade between table A and B, when the application will execute the code to remove a record in A, the children into table B will also be removed (this is the goal). But if there was a Dolibarr PHP trigger (for example provided by an external module) on the deletion of record of B (for example to validate the deletion or to execute a complementary action), the DELETE CASCADE will be executed without having the Dolibarr PHP trigger on B deletion executed, missing the validation or actions of the PHP trigger of the module. All business rules must be implemented on the same side (so the server PHP side), this is the reason why business rules implemented on Database side (like with delete cascade) are not allowed (same conclusion than the next point about Database triggers).
−Note that external modules can use them for links between Dolibarr tables and its module tables but a better recommanded solution is to use the trigger to implement the deletion of its table when a record of a parent official table is deleted.
+Note that an external module can use them for links between Dolibarr tables and its module tables but a better recommended solution is to use the trigger to implement the deletion of its tables when a record of a parent official table is deleted.
==Using Database triggers==
==Using Database triggers==