Details
-
Type:
Sub-task
-
Status:
Closed
-
Priority:
Minor
-
Resolution: Fixed
-
Affects Version/s: 2.0
-
Fix Version/s: 2.0
-
Component/s: Database SQL/XMLDB
-
Labels:None
-
Database:MySQL, PostgreSQL, Microsoft SQL, Oracle
-
Affected Branches:MOODLE_20_STABLE
-
Fixed Branches:MOODLE_20_STABLE
Description
Expected behaviour to get cross-db support is to automatically cast empty strings to 0 in the following functions:
- insert_record
- update_record
- set_field
Rationale: DBs are idiot:
- MSSQL allows empties on int fields and saves them as 0, but not in decimal fields no matter of the nullability of the field)
- Oracle allows empties on int fields and saves them as NULL and only in NOT NULL fields. Same for decimal fields.
- MySQL doesn't allow empties in int/decimal fields (strict mode). Error.
- PG doesn't allow empties in int/decimal fields). Error.
So I guess we only can move to the "perform casting" alternative. Is the only that will work cross-db. The other (not perform casting) won't work as far as MSSQL and Oracle sometimes accept those empty strings.
Activity
- All
- Comments
- History
- Activity
- Source
- Test Sessions
Done,
implemented the normalise_value() function in mysql and postgresql drives (was already in mssql and oracle) that performs all the default "normalisation" like casting empty strings to cero when working with numeric columns.
Tests passing now.
Resolving as fixed.
Petr, my only concern is if we should, in moodle_database.php:
/**
* Normalise values based in RDBMS dependencies (booleans, LOBs...)
*
* @param database_column_info $column column metadata corresponding with the value we are going to normalise
* @param mixed $value value we are going to normalise
* @return mixed the normalised value
*/
private abstract function normalise_value($column, $value);
in order to "force" drivers implementators to have such good habit.
Ciao 
/**
* Normalise values based in RDBMS dependencies (booleans, LOBs...)
*
* @param database_column_info $column column metadata corresponding with the value we are going to normalise
* @param mixed $value value we are going to normalise
* @return mixed the normalised value
*/
private abstract function normalise_value($column, $value);
private + abstract? looks weird, I doubt you would be able to call it from moodel_database if defined in native drivers. What other use would it have?
I did not test it, it might actually work in this weird PHP OOP model
That's a good point, oki, declaring it as protected abstract. Ciao
Test now are checking the expected behaviour (cast empty to 0). These need fix:
MySQL: insert/update/set_field
PostgreSQL: set_field
coming soon...ciao