XMLDB creating new DDL functions

Moodle 2.0

XMLDB Documentation > Roadmap > XMLDB Creating new DDL functions ----

Important note: All the functions showed in this page are for use in Moodle 2.0 upwards, where we changed the DB layer to support some new features. If you need information for previous Moodle version, take a look to the pre 2.0 page.

Justification

Once the XMLDB Structure has been well defined and we are able to abstract any DB model with it, it's time to build one group of functions that will create the needed DB objects based in the information supplied by such XML structure. All those functions will use the underlying XMLB objects (XMLDBTable, XMLDBField, XMLDBKey, XMLDBIndex) in order to keep the level of abstraction across them.

The objectives to be supplied by these functions are:

Be used by the installation process, replacing the old *.sql usage, to create all the needed DB objects.
Be used by the upgrade process, in the new upgrade.php script, to handle all the DB objects.

Although initially it wasn't completely decided if all those XMLDB objects were going to be used by the DDL functions, or we were going to create a bunch of new functions, completely independent from the XMLDB schema, finally we decided to use the previously created object, mainly because:

If we have some objects representing tables, fields and so on, it hasn't too much sense to build new functions requiring the same type of objects as parameters and not to use them.
Any change in the XMLDB objects will be immediately available to the DDL functions (one new column type...).
Code inside the DDL functions will be pretty simple because all the SQL code generation will be the responsibility of the XMLDB classes themselves.
By using the XMLDB objects, the XMLDB Editor (the tool to edit XMLDB structures easily) could be improved in order to generate automatically the needed PHP code to be used by the upgrade.php scripts. Just select one object (table/field/index/key), one action to perform (create/drop/rename/alter) and, wow!, you will obtain all the PHP code to be pasted in the upgrade script. (coming soon) ;-)

The only drawback we have found is that upgrade code will be a bit longer, because it will need to include all the XMLDB objects creation and definition code. But, in the other hand, it will be an easily readable code.

Implementation

Basic Concepts

All the functions will:

accept one XMLDB objects (the one to be modified) as parameter.
perform all the possible checks before executing any SQL command against DB.
return true/false on success/error (the XMLDB object will contain extra info on error, available with the XMLDBObject->getError() function).
output debug info if enabled.

Handling XMLDB objects

Introduction

Before calling the DDL functions themselves, the proper XMLDB objects must be properly created and contain all the needed info in order to allow the functions to do their work. The basic code structure for any DDL operation should be:

Creation of the XMLDB objects programatically.
Invocation of the DDL function.
Check for errors

Basic example

One short (hehe!) example of these steps could be the following PHP lines, used to create a simple table with one primary key, one foreign key and one index:

 
$field1 = new xmldb_field('id');
$field1->set_attributes(XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, [null, null,] null, null); // [XMLDB_ENUM, null,] Moodle 2.x deprecated  
$field2 = new xmldb_field('name');
$field2->set_attributes(XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, [null, null,] 'default name', 'id'); // [XMLDB_ENUM, null,] Moodle 2.x deprecated 
$field3 = new xmldb_field('course');
$field3->set_attributes(XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, [null, null,] null, 'name'); //  [XMLDB_ENUM, null,] Moodle 2.x deprecated 
$field4 = new xmldb_field('type');
$field4->set_attributes(XMLDB_TYPE_CHAR, '20', null, XMLDB_NOTNULL, null, [XMLDB_ENUM, array('type1', 'type2', 'type3'),] 'type1', 'course'); //  [XMLDB_ENUM, null,] Moodle 2.x deprecated 
$field5 = new xmldb_field('summary');
$field5->set_attributes(XMLDB_TYPE_TEXT, 'medium', null, null, null, [null, null,] null, 'type'); //  [XMLDB_ENUM, null,] Moodle 2.x deprecated 
$key1 = new xmldb_key('primary');
$key1->set_attributes(XMLDB_KEY_PRIMARY, array('id'), null, null);
$key2 = new xmldb_key('foreignkey1');
$key2->set_attributes(XMLDB_KEY_FOREIGN, array('courseid'), 'course', array('id')); 
 
$index1 = new xmldb_index('type');
$index1->set_attributes(XMLDB_INDEX_NOTUNIQUE, array('type'));
 
$table = new xmldb_table('my_first_xmldb_table');
$table->addField($field1);
$table->addField($field2);
$table->addField($field3);
$table->addField($field4);
$table->addField($field5);
 
$table->addKey($key1);
$table->addKey($key2);
 
$table->addIndex($index1);
 
$status = $dbman->create_table($table);

First of all, don't panic!, the code above can be easily reduced to, exactly, 10 lines, but in order to explain it with some detail we have posted here the long version. ;-) Also, don't forget that one of the upcoming features of the use of the XMLDB objects will be the ability of the XMLDB Editor to generate all this PHP code automatically for you.

The example, explained

Lets start with the very two first lines:

$field1 = new xmldb_field('id');
$field1->set_attributes(XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, [null, null,] null, null); //  [XMLDB_ENUM, null,] Moodle 2.x deprecated

In the first line we create a new field, called "id" (that will be the name of the column in the table, so do forget to follow the XMLDB naming conventions and the coding guidelines.

In the second line we define all the attributes of the field with a simple function call. The list of parameters is:

Column Type: Can be one of these: XMLDB_TYPE_INTEGER, XMLDB_TYPE_NUMBER, XMLDB_TYPE_CHAR, XMLDB_TYPE_TEXT, XMLDB_TYPE_BINARY. Note that XMLDB internals support other column types but they are only for compatibility with some special tables. Moodle shouldn't use anything out from the list above.
Column precision: It's length for INTEGERS and CHARS, two comma-separated numbers (total length and number of decimal positions) for NUMBERS and one of this: "small", "medium", "big, for both TEXTS and BINARIES.
Unsigned: To specify if the numeric field will be unsigned, we'll use the XMLDB_UNSIGNED constant. If signed, we'll pass null.
Not Null: To specify if the field will be not null, we'll use the XMLDB_NOTNULL constant. If nullable, we'll pass null.
Sequence: To specify if the field will be a sequence (or auto-numeric, or auto-incremented, or whatever you call it), we'll use the XMLDB_SEQUENCE constant. If not, we'll pass null.
Enum: To specify if the field only will contain a limited number of possible values, we'll use the XMLDB_ENUM constant. If not, we'll pass null. // Moodle 2.0 deprecated
Enum values: If the field has been defined as XMLDB_ENUM, this paramenter will have one array containing all the possible values for the field. Else, null. // Moodle 2.0 deprecated
Default value: If the field has some meaningful default value to be defined this parameter will contain it. Else, null.
After-field: If you want to enforce the field to be created after some other exact column in the DB, this parameter will allow you to do so. Note that, if not specified, every added field is created after the latest one so, in this example we could not define it at all. We'll use null once again if undefined.

So, the lines of code above are creating one Field, named "id", that will be an integer of 10 digits, not null and auto-numeric. Let's see the next fields:

$field2 = new xmldb_field('name');
$field2->set_attributes(XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, [null, null,] 'default name', 'id'); //  [XMLDB_ENUM, Enum values,] Moodle 2.x deprecated

Here we are creating one new field, called "name", defined as one char with maximum length of 255cc, not null, with one default value ("default name") and to be created after the "id" field (note again that, to build tables, this order isn't useful at all and the order of adding the fields to the table will be used).

Lets examine the next one:

$field3 = new xmldb_field('course');
$field3->set_attributes(XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, [null, null,] null, 'name'); //  [XMLDB_ENUM, Enum values,] Moodle 2.x deprecated

Another new field, called "course", integer with a maximum length of 10, unsigned and not null (forget the 'name' value, to define after-field info as we said some lines above).

Another one field:

$field4 = new xmldb_field('type');
$field4->set_attributes(XMLDB_TYPE_CHAR, '20', null, XMLDB_NOTNULL, null, [XMLDB_ENUM, array('type1', 'type2', 'type3'),] 'type1', 'course'); //  [XMLDB_ENUM, Enum values,] Moodle 2.x deprecated

This field will be called "type", char(20), not null, will have three possible values ("type1", "type2" and "type3") and its default value will be "type1".

And the final field in our example:

$field5 = new xmldb_field('summary');
$field5->set_attributes(XMLDB_TYPE_TEXT, 'medium', null, null, null, [null, null,] null, 'type');

That will be a field name "summary" of type text with a "medium" length.

Also note that it isn't mandatory to specify all those "null" parameters and, starting from the right, we can safely avoid them, so the last field we have seen could be (if we ignore, once more, the after-field parameter:

$field5->set_attributes(XMLDB_TYPE_TEXT, 'medium');

Now, we'll analyse the creation of keys. The first key specified in our example is:

$key1 = new xmldb_key('primary');
$key1->set_atrributes(XMLDB_KEY_PRIMARY, array('id'), null, null);

With the first line, we create an index named "primary". It's important to note that, whereas field names specified in the previous code is important because it's going to be the name of the fields in the DB, the names specified here aren't important at all, mainly, because of XMLDB key and index naming specs, so the keys and indexes created will be automatically named following those rules. Anyway, in the XMLDB files we use to call "primary" to the primary key and to concatenate the name of the fields in the key/index separated by "-" as their official name. But here it isn't important.

The second line completely defines the key. Attributes are:

Key Type: Can be one of these: XMLDB_KEY_PRIMARY, XMLDB_KEY_UNIQUE and XMLDB_KEY_FOREIGN that are the basic constraint types for our relational DB model. It's important to note that all the keys above will have one index created for them automatically so, when you define any Key, you haven't to define any index with the same fields at all. XMLDB will do it for you. More yet, for now, Foreign Keys won't be enforced (just the underlying indexes will be created) because before enabling them a lot of changes need to be performed within Moodle processes. But we want to have all those relations defined since the beginning of XMLDB. It will allow us to move quickly to a pure relational model.
List of fields: An array containing the names of the fields that will be part of the key (and the underlying index).
Reference table: Exclusively for Foreign Keys, the table where the fields defined in the previous parameter are pointing to.
Reference fields: Exclusively for Foreign Keys, the list of fields in the reference table that must match with the list of fields in the own table. Please note that, those "reference fields" must be defined as primary or unique key in the reference table. Relational rules, you know. So avoid pointing to fields not satisfying this condition completely!

With all this info, we know that we are going to create one primary key with the field "id". Remember that XMLDB will set the correct name for that key.

The second index in our example says:

$key2 = new xmldb_key('foreignkey1');
$key2->set_attributes(XMLDB_KEY_FOREIGN, array('courseid'), 'course', array('id'));

Here we are creating one more key, of type foreign key, with the field "course" pointing to the "id" field of the "course" table. Once more, the name isn't important at all.

And finally, we arrive to the indexes. Don't forget that all the previously defined keys will generate one underlying index with the fields specified so, in this sections we only have to define some other combinations of fields that are used often by SQL statements. By providing the correct indexes we'll get big speed improvements. But they must be correct. It's absolutely wrong to "index everything" without knowing what is being done. Don't forget it!

In our example we have this lines of code:

$index1 = new xmldb_index('type');
$index1->set_attributes(XMLDB_INDEX_NOTUNIQUE, array('type'));

In the first line we create the new index object. Remember, that the name provided here isn't the name of the index in the RDBMS, XMLDB key and index naming will define it automatically.

In the second line we specify:

Index Type: that can be XMLDB_INDEX_NOTUNIQUE (that is pretty equivalent to null, the default) or XMLDB_INDEX_UNIQUE depending if the list of fields defined in the previous parameter allows duplicate tuples or no.
List of fields: An array containing the names of the fields that will be part of the index.

So, in the example, we are creating one non unique index with the field called "type".

With this we've seen all the individual objects that conforms one table structure. Now we are going all these object to our new table so code says:

$table = new xmldb_table('my_first_xmldb_table');
$table->addField($field1);
$table->addField($field2);
$table->addField($field3);
$table->addField($field4);
$table->addField($field5);

$table->addKey($key1);
$table->addKey($key2);

$table->addIndex($index1);

With this lines we are simply creating one new table, called "my_first_xmldb_table", whose name must follow the XMLDB naming conventions and the coding guidelines. Then all the previously defined fields, keys and indexes are added to the table. Point.

And the final line of our code is:

$status = $dbman->create_table($table);

With this line, we'll create the DB table, with all the fields/keys/indexes specified with all the specs defined in the previous lines, with the correct prefix, proper object naming and particularities for each different RDBMS. This function call will return true/false, depending if the execution of the needed SQL commands has ended Ok or no.

Wow, at last, we end!!

Reduced (and recommended) example

All the lines in the example above have been really good to explain how to create any XMLDBField, XMLDBKey, XMLDBIndex and XMLDBTable structures from PHP code but we must recognise that it's a bit awful in terms of readability and length. So, for the creation of tables, we can use the following code, that is a complete replacement for the previous one:

$table = new xmldb_table('my_first_xmldb_table');
 
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
$table->add_field('name', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null, null, 'default name');
$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, null);
$table->add_field('type', XMLDB_TYPE_CHAR, '20', null, XMLDB_NOTNULL, null, XMLDB_ENUM, array('type1', 'type2', 'type3'), 'type1');
$table->add_field('summary', XMLDB_TYPE_TEXT, 'medium', null, null, null, null, null, null);
 
$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'), null, null);
$table->add_key('foreignkey1', XMLDB_KEY_FOREIGN, array('courseid'), 'course', array('id')); 
 
$table->add_index('type', XMLDB_INDEX_NOTUNIQUE, array('type'));
 
$status = $dbman->create_table($table);

Better, isn't it? ;-)

All the documentation in the previous complete example continues being 100% valid here. Just note that, when using this syntax, we have one more field, exactly the first one, that must be used to specify the name of the field/key/index being added. The rest is exactly the same!

Integration with the XMLDB Editor

Now you should know a bit more about how to create tables via PHP under the new XMLDB schema. It was really important to know about it because, in the process, you've seen how to create fields, keys and indexes, plus some extra notions about the whole thing.

Also, the creation of tables is, with difference, the most difficult operation (in terms of PHP generated) that you will find under all the new DDL functions. Other operations like rename, drop, alter will be really easier to use. Trying to help you a bit in the PHP generation, if you use the XMLDB Editor to design and create your DB structures, you will be able to obtain all the code needed to perform different DDL actions automatically.

Just go to the table/field/key/index you are modifying and, after performing the desired changes, press the "PHP Code" link and it will show you one new page with all (practically) the options available. Just select the desired one and the required PHP will appear in seconds

Such code can be directly used by your upgrade scripts (by modifying some minor bits here and there) improving the DB experience and reducing the risk of errors a lot.

Also, it's highly recommended to use such utility to understand better how the PHP code works, like an online tutor. Enjoy it!

Documentation