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

The roadmap

Extending DB support to new RDBMS implies a lot of changes in current code, plus the creation of some new libraries/artifacts. In this chapter, the main points of the whole process are related and explained briefly. Please note that points are numbered and should be performed in that order (when parallel work is possible it's pointed).

1. Splitting datalib.php: Currently such file includes SQL DDL (table_column()...), SQL DML (select, insert...) and some functions that aren't related directly with ADOdb (category_parent_visible, print_object()...). With more and more functions coming to support new RDBMS (DDL mainly), it could be a good idea to split the file into 3 parts, one to handle DDL (creation/alter/drop.. - could be called ddllib.php, only used on install/upgrade/migration), other to handle DML (where all the current insert, select, update.. functions will go - could continue being datalib.php) and other to store all those non-ADOdb related functions (or move them to their proper lib - course/lib.php, user/lib.php...).
2. Modifying DML functions: Some changes have to be performed to extend support for other RDBMS in all this functions, like changes to support LIMIT clauses (that are emulated both under Oracle and MSSQL), insert_record() changes, handling of CLOB/BLOB (large text/binary objects...) and so on. While all this changes are performed in datalib.php, code across Moodle must be modified to perform the correct function calls. This point must be performed after point 1 was finished. This is really a big point!
3. Defining one XML structure and handling it: The new XML structure used to define any DB (used at installation) should be as complex as needed, defining all the info required to transform it into usable DDL statements against any RDBMS. Validation and some sort of edition/inverse engineering will be present. This point can be performed in parallel with 1 & 2.
4. Creating new DDL functions: In order to be able to install/upgrade any DB component of Moodle, we need to suppot a bunch of new functions to CREATE/DROP/ALTER table, indexes, constraints, sequences, fields. All this functions should be cross-compatible and generate the proper SQL for each RDBMS. They will reside in the new ddllib.php (point 1) and will be included only in the installation/upgrade processes. This point must be performed after point 3 was finished.
5. Altering the installation/upgrade process: While compatibility with the old method will be present and any module/block/format/question... could use that approach, 1.7 should use (for the whole official distro) a new approach. This will be based in the version number (like the current one) and will execute any action defined in one new XML file (replacing current *.sql files) and one new upgrade.php file (replacing current *php files). Both MySQL and PostgreSQL installations will be able to run both upgrade systems (first, the old one, then the new one) but new RDBMS only will work with the new one. In any case, the old one will become deprecated and will disappear in 2.0. This point must be performed after point 4 was finished.
6. Documenting: All the libraries above plus the new install/upgrade system must be completely documented to allow developers one easy migration from the old approach. Things like field types, forbidden words an so on must be well defined in order to allow the quick adoption and better usage of all the system. This point can be performed in parallel with points 1-5.

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! :-( UPDATED: We also could hack calls to getAssoc() to add the id field back. Memory gain is really big! And places requiring FETCH_NUM shouldn't be really legion....uhm...
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 ? Proposal to create a bunch of functions to create, alter and drop everything! Handle special DB casuistic there (this will help to have more readable upgrade scripts).
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).
10. Under Oracle a similar mechanism to the used in postgresql in insert_record() could be needed: get sequence nextval, insert record... when the id is needed. Simple.
11. Important Oracle problems when inserting CLOB/BLOB data. Under Oracle the two steps (INSERT empty_b/clob() and UPDATE are needed). This will force us to review ALL the updates against current MEDIUMTEXT, TEXT, BYTEA columns to use the 2 steps approach, using the technique described in: http://phplens.com/adodb/reference.functions.updateblob.html and http://phplens.com/adodb/reference.functions.updateclob.html. Needs testing agains SQL*Server (because it isn't explicitly supported). We could implement some wrapper modifications in our central insert/update datalib functions to support this transparently (I hope), adding some parameter or detecting it dynamically (getting metadata for columns, detecting dbtype...). But all the hard-coded INSERT and UPDATE statements should disappear from Moodle code, using datalib functions always.
12. Alleviate datalib.php from some functions currently present that aren't part of the "wrapper-over-adodb" objective, moving them to their own library (module, course...). With this we should end with one smaller and well-defined (and documented) datalib.
13. Problems user Oracle (and SQL*Server) with NOT NULL fields and attempts to insert values. Not allowed! We should re-examine all those fields across DB and change their status to NULLABLE. Analyse why a lot of them were changed recently in MySQL!!
14. Regular expressions. Problems with Oracle (not available until 10g) and MSSQL (not available). Oracle 10g implements them using directly and one package existed since ages (Oracle 8i?) to handle them (owa_pattern). MSSQL can execute them by installing some stored procedures. Oh, oh, problems with some (a few) queries...

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