XMLDB defining an XML structure

XML database schema > Roadmap > Defining one XML structure

Justification

Before Moodle 1.6, all the DB install and upgrade was developed twice (once to handle MySQL installations and another to handle PostgreSQL installations). This approach, although working, has caused some headaches in the past, mainly because it was really difficult to keep both lines of development 100% on sync. Some developers do they work against one RDBMS and it was complex to develop to the other one (two test environments, skills on both databases, slower development cycle...). And all this was happening with only two supported RDBMS!

One of the main objectives of Moodle 1.7 is to extend the the number of supported RDBMS to other flavours (more exactly, to Oracle and MSSQL). And the old approach (one line of development for each DB) could become an absolute nightmare.

Because of this we have planned to build one structure to define all the DB objects used by Moodle. This structure will provide the necessary level of abstraction to be shared by all the RDBMS systems, so the "multiple lines of development" explained in the previous paragraph will be out forever, giving us one robust and well defined way to handle DB objects independently of the exact RDBMS being used.

Implementation

Initially all our best wishes were to use the AdoDB XML Schema. As Moodle is using ADOdb libraries to communicate with databases it sounded like the natural approach to solve the problem. But, finally, two reasons prevented us to use it:

Although working, it seems to be one feature in progress, with important changes/evolutions arriving at the time of write this document.
Its lack of support for "prefixes" (one Moodle key feature, to allow multiple instances to run in the same server), would force us to create some awful tricks to generate the objects.

So, finally, we decided to build our own XML files, with everything we need to define every object present in the DB.

The XMLDB editor

Although the XML is pretty simple to read (and to write), one of the major drawbacks was its easy and error prune adoption by the developers. Also some problems with versioning systems getting crazy with XML files (thanks ML!) pointed us to the requirement to use one high-density format (it means, physically long lines) in our XML files.

After some intense thoughts we decided to build one specialised editor for our XML format. This editor should be easy to use and provide support for all the objects present one Moodle DB. And it's done (and will support future enhancements easily, we hope).

The XMLDB Editor makes the edition of tables/fields/keys/indexes practically a trivial task, allowing the developer to spend the time coding and improving things instead of fighting against XML files and the errors caused by manual editing (of course, the developer is free to use such extra-time as desired, beers, dance, books, music...) ;-)

All the new install.xml files, present under each db directory in Moodle can be edited (and we recommend it) with just some clicks and keystrokes. Those install.xml will contain all the info needed to generate the specific objects needed for each RDBMS supported. Obviously, such files, are the neutral replacement for all the *.sql files used until now.

Installation

The editor is pretty easy to install, mainly because it runs under Moodle and it's a cool framework to rapid-development. To start working with it all you have to do is:

Download if from http://download.moodle.org/modules/xmldb.zip (it's also under cvs://contrib/xmldb).
Unzip it and copy the whole "xmldb" dir under the "admin" directory of your development server.
Copy the "xmldb/lang/en_utf8/xmldb.php" file to the "lang/en_utf8" dir in order to view some strings properly.
Login to your server and, under the Admin Interface, you'll see a new link pointing to the "XMLDB Editor".

One important note is that, to be able to handle files properly, the web server needs write access to all those "db" directories where the "install.xml" files reside.

That's all!

Use

We really think the XMLDB Editor is pretty easy to use, so here you won't see a complete guide to use it. We highly recommend you to play with it for a while, viewing how it works and how it modifies the install.xml files.

It's organised in a top-botton structure, where you start loading (or creating) a new XMLDB file. Then, you can edit such file and its's general structure will be showed. This structure have two type of elements, tables and statements and the XMLDB Editor allows you to add, edit, delete, and move them easily. Also, for initial creation of tables, one small but effective reverse-enginery tool has been developed (only under MySQL) allowing you to retrofit any table from the DB to the XMLDB Editor.

While editing tables you will see their fields, keys and indexes and you'll be able to handle all them easily. Note that some fields can be no-editable. It uses to be because they are being used in some way (part of one key or index) and the idea is to warn you about that.

Fields can be edited and you can specify their name, type, length, decimals, null-ability, defaults and so one. Exactly the same for both keys and indexes.

While editing statements, you must thing about them like "collections of sentences". Once you select the type (only inserts are allowed for now) and table you are interested you'll be able to introduce the exact values easily, being able to duplicate them easily to gain some speed if you have a lot of sentences in your development. Sentences can be edited and deleted easily too.

One interesting feature is that all the XMLDB Editor pages allow you to enter one comment about the item being modified (table, index, key, field, statement...). Use it at your entire needs, sure it helps other developers to understand a bit more the DB model.

Please, don't forget to read and understand the next section, when we talk about some important norms to create and handle our XMLDB files.

Conventions

Structure (assignment)
Example and DTD
Future Improvements

Documentation