XMLDB Modifying DML functions
This point must be performed after Point 1: Splitting datalib.php was finished.
- 1 Details
- 1.1 LIMIT x, y clause
- 1.2 INSERT records (Oracle)
- 1.3 INSERT of clob/blob data (Oracle)
- 1.4 INSTALL things
- 1.5 CONCATENATION operator
- 1.6 DISTINCT clause on TEXT/BINARY
- 1.7 Review all the "AS" uses in table aliases
- 1.8 Introduce new upgrade.php scripts everywhere and deprecate the old ones
- 1.9 Review all the LCASE() and UCASE() uses in SQL statements
- 1.10 Review all the LEFT(), RIGHT(), SUBSTR() and SUBSTRING() uses in SQL statements
- 1.11 PREVENT all the REGEXP SQL statements under MSSQL and Oracle
- 1.12 Review all $CFG->dbtype uses
- 2 Timeframe
This is a long process in which a lot of scripts become affected although critical changes are, exclusively in the DML functions. The rest is a repetitive work, changing how such functions are used in hundreds of places and testing them carefully. Documentation of the new final DML library must be ready as soon as possible to allow 3rd part developers to update their contributions.
Following, there is one list of changes to be performed in the DML library with their implications in the rest of the code (note: as the number of changes isn't too much big, move each item to one section!)
LIMIT x, y clause
We need to change all the uses of the LIMIT offset, num (or vice-versa) clause (both Oracle and MSSQL doesn't support such clause) to use the cross-db compatible SelectLimit() function. To do it, all the get_recordXXX and get_recordsetXXX functions in dmllib.php must be changed to support two more parameters $sqlnum and $sqlfrom, to specify the number of records to retrieve and the number of records to skip. This change in the library, although important, will be backwards compatible with the old Moodle code using hand-written LIMIT clasuses.
Once changed, all Moodle core should be transformed from the old LIMIT x,y uses to the new functions with the new parameters.
Also, the sql_paging_limit() will be deprecated ASAP (2.0?).
Functions to be changed are:
- Done!: get_recordset(): Change the order of the params $limitfrom and $limitnum.
- Done!: get_recordset_select(): Change the order of the params $limitfrom and $limitnum.
- Done!: get_recordset_list(): Change the order of the params $limitfrom and $limitnum.
- Done!: get_recordset_sql(): Add the new params $limitfrom and $limitnum. Use SelectLimit() if needed.
- Done!: get_records(): Change the order of the params $limitfrom and $limitnum.
- Done!: get_records_select(): Change the order of the params $limitfrom and $limitnum.
- Done!: get_records_list(): Change the order of the params $limitfrom and $limitnum.
- Done!: get_records_sql(): Add the new params $limitfrom and $limitnum.
- Done!: get_records_menu(): Add the new params $limitfrom and $limitnum.
- Done!: get_records_select_menu(): Add the new params $limitfrom and $limitnum.
- Done!: get_records_sql_menu(): Add the new params $limitfrom and $limitnum.
Global changes to be performed include:
- Done!: Search and destroy all the occurrences of the LIMIT (uppercase and lowercase) clause.
- Done!: Search and destroy all the occurrences of the sql_paging_limit() function call.
- Done!: Deprecate the sql_paging_limit() function.
INSERT records (Oracle)
Oracle lacks support for auto-numeric fields, so one sequence must be created for each table, plus one trigger to simulate auto-numeric fields behaviour. This implies:
- Done!: Create the corresponding sequence each time a table is created with the XMLDB Schema.
- Done!: Create the corresponding trigger each time a sequence is created.
- Done!: Modify insert_record() to work with this sequences and triggers properly (similar to PostgreSQL).
- Done!: Rename the sequence each time one table is renamed.
- Done!: Drop/Create and trigger each time one table is renamed.
- Done!: Drop the sequence and trigger each time one table is droppped.
INSERT of clob/blob data (Oracle)
Oracle doesn't supports direct injection of clob/blob (text/binary) data to DB. Instead, a two phase transaction must be performed, first inserting the record using empty_clob() to create the container and then, updating the record with real contents with the 'UpdateXlob() functions.
This has the counterpart that we must know what fields are clob/blob, perhaps asking Metadata functions (and caching results). Then, follow the above approach for each lob column in the insert.
Note that nothing is specified in the ADOdb documentation about updating clob/blobs so they should be supported directly by the UpdateXlob() functions without problems (needs to be checked with real data).
Code to modify is:
- Done!: insert_record()
- Done!: update_record()
- Done!: set_field()
- Done!: Create some more XMLDB tests to check everything works using the functions above with LOB data
- Done!: Allow the installer to select both MSSQL and Oracle.
- Done!: Check magic_quotes_sybase must be set for MSSQL and Oracle (php.ini mandatory! or PGC data won't be properly slashed - note that this req. will be out once we disable PGC for ever!). Also, modify stripslashes_safe() and some other related functions. Note: One alternative solution is to disable magic_quotes_gpc completely (although it's the opposite to Moodle install requirements). With this, Moodle itself will quote everything with the proper character, that has been correctly defined before the quoting job starts. I'm running my servers with this configuration, because it supports to jump/run different DB without changes in php.ini + web server restarts.
- Done!: Check Oracle DB is Unicode and set $CFG->unicodedb (and config record) properly
- Done!: Assume MSSQL is always Unicode, using the proper TDS or ODBTP driver with conversion to UTF-8.
Each DB seems to have its own concatenation syntax. We must provide one central function sql_concat(), accepting up to X strings in order to build the proper SQL sentence. MySQL uses the CONCAT() function, PostgreSQL and Oracle the "||" (two pipes) chars (the SQL-Standard) and MSSQL the "+" (plus) sign.
- Done!: Use one proper sql_concat() function to support any DB (wrapper over ADOdb)
- Done!: Look for harcoded uses of the concat stuff
DISTINCT clause on TEXT/BINARY
Both MSSQL and Oracle doesn't support the DISTINCT clause on TEXT and BINARY fields. When present it must be erased (the DISTINCT or the field, depending of the logic) from the query and, if necessary, create one PHP iteration to retrieve such TEXT info.
Note that in a lot of situations, the DISTINCT clause can be omitted inside Moodle because all the get_records_XXX() functions return one associative array that, by the first field in the query, guarantees that all the records will be different.
- Done!: Review all the DISTINCT uses containing TEXT/BINARY columns, limiting the fields retrieved OR simply deleting the DISTINCT if it isn't needed (first field of query = id)
Review all the "AS" uses in table aliases
As deduced in XMLDB problems, it's mandatory to review all the current uses of the "AS" keyword in table aliases (Oracle forbids it).
Also, all the column aliases should use the keyword (AS) because PostgreSQL requires it, although this should be ok since ages because PG uses to work properly since then. ;-)
- Done!: Search and destroy all the "AS" uses in table aliases.
Introduce new upgrade.php scripts everywhere and deprecate the old ones
Starting with Moodle 1.7, only the new XMLDB-based upgrade.php scripts will be used in core. So these points must be addressed:
- Done!: Create all the new upgrade.php basically empty (template) files.
- Done!: Mark as deprecated all the old scripts, encouranging hackers to leave them unmodified.
Review all the LCASE() and UCASE() uses in SQL statements
- Done!: Both lower() can upper() are the standard SQL functions for this to work, so replace them everywhere.
- Done!: Deprecate db_uppercase() and db_lowercase() as they aren't used at all in Moodle Core.
Review all the LEFT(), RIGHT(), SUBSTR() and SUBSTRING() uses in SQL statements
- Done!: Create one function sql_substr() to get the correct function name for each DB .
- Done!: Replace Left() and Right() SQL calls to use sql_substr() result as function name.
- Done!: Replace Substr() and Substring() SQL calls to use sql_substr() result as function name.
PREVENT all the REGEXP SQL statements under MSSQL and Oracle
Only Oracle 10gR2 and MSSQL via external function seem to support REGEXP in SQL. Prevent them (replacing by simpler LIKEs if possible( in the next places:
- Done!: lib/datalib.php (trimmed +/- chars, so REGEXP wont be used)
- Done!: lib/searchlib.php (transformed TOKEN searches into STRING searches, trimmed +/- chars, so REGEXP wont be used)
- Done!: message/lib.php (trimmed +/- chars, so REGEXP wont be used)
- Done!: mod/forum/lib.php (hidden the searchfullwords field in complete formulary)
- Done!: mod/glossary/lib.php (trimmed +/- chars, so REGEXP wont be used)
- Done!: mod/glossary/sql.php (trimmed +/- chars, so REGEXP wont be used)
Review all $CFG->dbtype uses
Done!: Look for all the places using $CFG->dbtype to see what happens with new RDBMS. Extend support to them.
One year, more or less, will be enough to have one Alfa version available! Eloy Lafuente (stronk7) 07:19, 18 August 2006 (WST) (written in one moment of desperation)
15 days of work