XMLDB modifying DML functions

XML database schema > Roadmap > Modifying DML functions

---

Note: This page is a work-in-progress. Feedback and suggested improvements are welcome. Please join the discussion on moodle.org or use the page comments.

Intro/explnation here...

All the work will be performed in one branch, call it MOODLE_17_DB_DML. This point must be performed after Point 1: Splitting datalib.php was finished.

Details

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:

• get_recordset(): Change the order of the params $limitfrom and $limitnum.
• get_recordset_select(): Change the order of the params $limitfrom and $limitnum.
• get_recordset_list(): Change the order of the params $limitfrom and $limitnum.
• get_recordset_sql(): Add the new params $limitfrom and $limitnum. Use SelectLimit() if needed.

• get_records(): Change the order of the params $limitfrom and $limitnum.
• get_records_select(): Change the order of the params $limitfrom and $limitnum.
• get_records_list(): Change the order of the params $limitfrom and $limitnum.
• get_records_sql(): Add the new params $limitfrom and $limitnum.
• get_records_menu(): Add the new params $limitfrom and $limitnum.
• get_records_select_menu(): Add the new params $limitfrom and $limitnum.
• get_records_sql_menu(): Add the new params $limitfrom and $limitnum.

Global changes to be performed include:

• Search and destroy all the occurrences of the LIMIT (uppercase and lowercase) clause.
• Search and destroy all the occurrences of the sql_paging_limit() function call.
• 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:

1. Done!: Create the corresponding sequence each time a table is created with the XMLDB Schema.
2. Done!: Create the corresponding trigger each time a sequence is created.
3. Done!: Modify insert_record() to work with this sequences and triggers properly (similar to PostgreSQL).
4. Done!: Rename the sequence each time one table is renamed.
5. Done!: Drop/Create and trigger each time one table is renamed.
6. 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:

1. Done!: insert_record()
2. Done!: update_record()
3. Done!: set_field()
4. Create some more XMLDB tests to check everything works using the functions above with LOB data

INSTALL things

• 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.

CONCATENATION operator

Each DB seems to have it's 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.

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.

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. ;-)

Timeframe

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