XMLDB problems

XML database schema > XMLDB Problems

---

Every section on this page will show important problems present in the adoption of the new database layer planned for 1.7. Each section will define the problem, its impact and, when available, its solution. Every section will include one line containing its status ('Open', 'Decided, 'Work in progress', 'Finished', 'Tested' and 'Closed').

Of course, fell free to use the Bug Tracker to follow the daily evolution of each one of these problems by adding one link inside each section pointing to the proper bug.

Prior to change the status from 'Decided' to 'Work in progress' the corresponding page of the roadmap must be modified, including all the actions/steps needed to solve the problem. Obviously, at the end, the expected status for each section is 'Closed'.

Regular expressions

(Status: Open - Severity: Critical - Bug: not defined)

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

SQL Limit performance

(Status: Open - Severity: Not critical - Bug: not defined)

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!

Naming conventions

(Status: Open - Severity: Not critical - Bug: not defined)

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? Can we use different naming schemes for each DB. PostgreSQL one seems more formal, is it the way for Oracle and SQL*Server.

NOT NULL fields using a DEFAULT '' clause

(Status: Open - Severity: Critical - Bug: 6218 )

Under Moodle 1.6 there are tons of char/varchar/text/blob columns that are defined as NOT NULL and contains one DEFAULT clause with the value '' (empty string).

There are two important problems with this type of definitions:

1. They haven't to much sense from a design perspective. If one field is defined as NOT NULL it shouldn't contain empty values.
2. Oracle and MSSQL consider empty strings as NULL values the DEFAULT application will crash in both RDBS.

The proper way to solve this situation should be to annihilate all those inconsistent/wrong combinations from the whole DB schema (NOT NULL + DEFAULT ''), but it can be really hard and risky to do it now (Moodle 1.6-1.7). So here it's the proposed solution:

1. Don't modify 1.6 at all. Everything should continue working properly.
2. In the XMLDB files, for 1.7, continue defining all those fields with the current form (i.e.: NOT NULL DEFAULT ).
3. In the XMLDB generator (create table), avoid the NOT NULL and DEFAULT clause for all those fields for Oracle and, potentially MSSQL. Create all those fields as normal nullable fields without default value.
4. Add some check in the installation script to look for that MySQL modes being problematic.

With this approach we'll have MySQL and PostgreSQL servers running exactly without modification in 1.7 while MSSQL and Oracle versions would be more correct. It's possible that some parts of the code need some modification (if they were relying in that wrong default values) but they will be a small number, sure.

After 1.7, we should start to modify MySQL and PostgreSQL implementation to use the same (correct) field definition (nullable columns) with more time to change/test/fix everything.