XMLDB defining an XML structure

XMLDB Documentation > Roadmap > Defining one XML structure

Justification

Moodle supports a number of Database Engines, including MySQL, MariaDB, Postgresql, OCI, and MS SQL Server. Each of these has a slightly different format for some of their table creation statements.

XMLDB has been created as a standardised format to describe the structure of the database in a human-readable format which the Moodle installer can turn into DDL commands to create the database structure.

The Moodle XMLDB editor must be used to correctly define the XMLDB structure for all tables within Moodle. It is also able to convert the XMLDB structure into upgrade scripts which can be copied and pasted into Moodle upgrade.php files.

The XMLDB editor

Main article

Although the XML is pretty simple to read (and to write), one of the major drawbacks was its easy and error-prone 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 addition 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.

Launching

Just login to your server as an administrator and, under the Development tab of the Administration Block, 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 (and to the files themselves, of course). ;-)

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-bottom structure, where you start loading (or creating) a new XMLDB file. Then, you can edit such file and its general structure will be showed. This structure has one type of elements, tables and the XMLDB Editor allows you to add, edit, delete, and move them easily. Additionally it is possible to turn an existing table (under MySQL and MariaDB) into an XMLDB structure, allowing you to retrofit any table from the DB to XMLDB Editor.

Note: If you can't click on the create links.... you must first create the /db folder (as shown in the list, but it may not really exist) and then make sure it is writeable by the webserver

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.

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, etc). 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, where we talk about some important guidelines to create and handle XMLDB files.

Conventions

Apart of the Database Structures guidelines, some more conventions should be followed:

About names:
1. All lowercase names (tables, indexes, keys and fields).
2. Table names and field names must use only a-z, 0-9 and _ chars. Table names can be at most 53 characters long (28 before Moodle 4.3); column names at most 63 characters long (30 before Moodle 4.3).
3. Key and index names under the XMLDB Files must be formed by concatenating the name of the fields present in the key/index with the '"-" (minus) character.
4. Primary key always must be named "primary" (this is one exception to the previous convention).
5. It's highly recommended to avoid reserved words completely. We know we have some of them now but they should be completely out for next releases.
About NULLS
1. Avoid creating all the fields as NOT NULL with the silly default value '' (empty string). The underlying code used to create tables will handle it properly but the XMLDB structure must be REAL. Read more in the Problems Page.
About FOREIGN KEYS
1. Under the tables of every XMLDB file, you must define the existing Foreign Keys (FK) properly. This will allow everybody to know a bit better the structure, allow to evolve to a better constrained system in the future and will provide the underlying code with the needed info to create the proper indexes.
2. Note that, if you define any field combination as FK you won't have to create any index on that fields, the code will do it automatically!
3. Respect Convention 1.3
About UNIQUE KEYS
1. Declare any fields as UNIQUE KEY (UK) only if they are going to be used as target for one FK. Create unique indexes instead.
2. Respect Convention 1.3

One example: the assignment module

Here we are going to examine the current implementation of the XMLDB Schema for the assignment module (a simple one). It has been completely generated with the XMLDB Editor but it's nice to know a bit more about the XML internals.

As you can see the structure is pretty simple:

XMLDB
- TABLES, one or more, each one with
  - FIELDS
  - KEYS
  - INDEXES
  - SENTENCES

First of all you should note that all the elements contain the PREVIOUS and NEXT attributes. They allow us to keep everything ordered although it isn't meaningful at all from the RDBMS perspective. Also the COMMENT field is present everywhere to be used as desired.

The TABLE element

We can ignore the TABLE element, as it's simply one container for the internals (FIELDS, KEYS and INDEXES). Let's go to examine them a bit more:

The FIELD element

It maps with one field in the DB (obviously). For each field you can define its name, type (from a list of neutral types), length, decimals (for some types), notnull (true/false), unsigned (true/false), sequence (if it's autonumeric or serial, true/false) and default (to assign a default value).

So, in our example, we have two tables, assignment and assignment_submissions, each one with its own fields, defining all the information related above. Please note that naming conventions are followed.

The KEY element

Here is where all the PRIMARY KEYS (PK), UNIQUE KEYS (UK) and FOREIGN KEYS (FK) will be defined. For each key we define its name, type, fields (that belongs to it) and optionally (if the key is one FK) the target reftable and reffields.

In our example, the assignment table has one (mandatory!) PK (called, "primary", rules are rules) built with the "id" field.

The other table, the "assignment_submissions" one, also has its PK (called "primary" once more) and one FK, with the field "assignment" pointing to the field "id" of the table "assignment". Note that the FK follows the name conventions and its name is, simply, the name of the fields being part of it ("assignment"). Also, the FK has as target to one PK of the same module.

Finally, note that there isn't any index created for all these keys. Moodle will generate them automatically when the table is created. All the keys will have their corresponding index. Point. ;-)

The INDEX element

Where all the indexes will be defined. For each index you can define its name, unique (true/false) and the fields (as a comma-separated string) that it comprises. Please note that naming conventions are followed.

Also, some "obvious index", like the one based in the "assignment" field of the "assignment_submissions" table doesn't exist. Yes, you know why: Because such column has been defined as a FK and the index will be automatically created (see previous section).

DTD and XML schema

Not sure if this will be usable for somebody but here you can find one automatically generated DTD for the XMLDB files. Also one automatically generated XML Schema is available. Any improvement/fix to them will be welcome!

Documentation