Development:XML database schema

Note: This article is a work in progress. Please use the page comments for any recommendations/suggestions for improvement.

The goal of these pages is to document, explain and follow the evolution of the integration between Moodle and other RDBS, using the ADOdb XML Schema (AXMLS), built over the ADOdb Data Dictionary, that comes with the ADOdb library, used by Moodle to perform all DB operations since the beginning.

Preliminary tests

This test involves the creation of one minimal DB structure (2-3 tables) testing all these features with the XML schema:

1. Table creation.
2. Field creation:
   • names (don't forget reserved words!).
   • types
   • length considerations
   • autonumeric (serial or sequence) fields.
   • default values
   • unsigned support
   • null/not null decission!
3. PK creation and naming
4. INDEX and UNIQUE INDEX creation an naming
5. FK creation and naming?
6. Sequences naming

Todo list

1. We need to change all the uses of the LIMIT offset, num clause to use the cross-db compatible SelectLimit() function. Two alternatives seem possible:
   1. Create a new set of get_records_limit() (and get_recordset_limit() ?) functions, allowing to specify the offset, num parameters.
   2. Modify the current get_records() (and get_recordset()) functions to allow two more optional parameters (offset, num)
2. Analyse the impact of such SelectLimit() calls under SQL*Server, Oracle... because it's emulated by ADOdb on those DBs, because their lack of support for the LIMIT clause. As the offset parameter grows, ADOdb must iterate over more records to get the desired window and it could be a problem under long sets of records!
3. What to do with all the previously created DB objects (indexes, unique indexes, sequences...) if their naming schema doesn't fit with the implemented by ADOdb. Drop/recreate everything? Leave it unmodified?
4. Currently, both under MySQL and PostgreSQL, ADODB_FETCH_BOTH (default) is used so all the information is, practically, duplicated. In other side, SQL*Server, by default, uses ADODB_FETCH_NUM (although it can be changed to ADODB_FETCH_ASSOC). Proposal, after connection, change ALWAYS to ADODB_FETCH_ASSOC. It's supported by all the DB (while ADODB_FETCH_BOTH isn't!) and it will save us near 50% memory (and speed?) for record arrays! If there was some places using ADODB_FETCH_NUM structures inside Moodle (I really thing they aren't used) we must update them. UPDATED: it seems that the id field is lost in conversion to ADODB_FETCH_ASSOC so, perhaps it wouldn't be a good idea to force this mode! :-(
5. Force ADODB_ASSOC_CASE to 0 (lowercase). Some DB could break this and until now, both mysql and postgresql are working fine with lowercased field names.
6. Reserver words. What to do? One horrible example: "USER": http://www.petefreitag.com/tools/sql_reserved_words_checker/?word=user
   1. Try to quote them.
   2. Change them completely under Moodle.
7. Need to pre-parse the XML database schema to add prefix to all tables/indexes/constraints before parsing and executing!
8. Analise how upgrades are going to succeed. Everything inside one unique upgrade.php file, ok, DML should be php, but DDL, how to handle it ?
9. MSSQL escape quotes by adding one more quote to the quote character, instead of standard addslashses. This can be solved by using qstr() (so a lot of addslashes() calls should be modified) or by setting magic_quotes_sybase when running against SQL*server (then addslashes() will escape only the quote char). Independent of the solution, stripslashes_safe() must be modified to strip the correct characters based on $CFG->dbtype (because the magic_quotes_sybase setting modifies what's quoted by magic_quotes_gpc too).

See also

• XMLDB preliminary links - A collection of links about general info, searched and analysed at the initial stages of the project
• XMLDB column types - Some links about column types inside every RDBMS and their characteristics
• XMLDB reserved words - A collection of reserver words inside each RDBMS