30,997
edits
Language and development rules: Difference between revisions
Jump to navigation
Jump to search
m
→SQL Coding rules
Dolichicken (talk | contribs) (In English, the meaning of "eventually" is more like "at the end". This is different to German (and probably French), where the meaning of "eventuell" is more like "maybe".) |
Tag: 2017 source edit |
||
| Line 292: | Line 292: | ||
'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) | ||
* | * Date functions NOW, SYSDATE or DATEDIFF 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 for better portability of code and correct management of TimeZone. | ||
For example, don't do: | For example, don't do: | ||
| Line 314: | Line 314: | ||
An other advantage of this rule, is that request benefits of index because we are making a compare of a field with a fixed value. When using datediff, you make an operation on field before comparison, this means database can't use the index on field, resulting on very bad performance compared to solution without the datediff. | An other advantage of this rule, is that request benefits of index because we are making a compare of a field with a fixed value. When using datediff, you make an operation on field before comparison, this means database can't use the index on field, resulting on very bad performance compared to solution without the datediff. | ||
*Use $db->ifsql for SQL IF | * The statement WITH ROLLUP is prohibited: | ||
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). | |||
* Use $db->ifsql for SQL IF | |||
Do not include the IF into your forged SQL request. But use instead $db->ifsql() method to you will make a SQL IF that is compatible with all SQL databases. | Do not include the IF into your forged SQL request. But use instead $db->ifsql() method to you will make a SQL IF that is compatible with all SQL databases. | ||
*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. 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. So all business rules must be implemented on the same side (the server PHP side), this is the reason why business rules implemented on Database are not allowed (same situation than the next point on Database triggers) | Such SQL instructions are forbidden because they bypass the business rules of the application. 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. So all business rules must be implemented on the same side (the server PHP side), this is the reason why business rules implemented on Database are not allowed (same situation than the next point on Database triggers) | ||