XMLDB creating new DDL functions - pre 2.0

Revision as of 22:02, 13 August 2006 by Eloy Lafuente (stronk7) (talk | contribs) (The example, explained)

Jump to: navigation, search

XML database schema > Roadmap > XMLDB Creating new DDL functions


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.


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


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:

  1. Creation of the XMLDB objects programatically.
  2. Invocation of the DDL function.
  3. 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 XMLDBField('id');
$field1->setAttributes(XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null, null);
$field2 = new XMLDBField('name');
$field2->setAttributes(XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null, null, 'default name', 'id');
$field3 = new XMLDBField('course');
$field3->setAttributes(XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, null, 'name');
$field4 = new XMLDBField('type');
$field4->setAttributes(XMLDB_TYPE_CHAR, '20', null, XMLDB_NOTNULL, null, XMLDB_ENUM, array('type1', 'type2', 'type3'), 'type1', 'course');
$field5 = new XMLDBField('summary');
$field5->setAttributes(XMLDB_TYPE_TEXT, 'medium', null, null, null, null, null, null, 'type');

$key1 = newXMLDBKey('primary');
$key1->setAttributes(XMLDB_KEY_PRIMARY, array('id'), null, null);
$key2 = newXMLDBKey('foreignkey1');
$key2->setAttributes(XMLDB_KEY_FOREIGN, array('course'), 'course', array('id)); 

$index1 = newXMLDBIndex('type');
$index1->setAttributes(XMLDB_INDEX_NOTUNIQUE, array('type'));

$table = new XMLDBTable('my_first_xmldb_table');



$status = 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 XMLDBField('id');
$field1->setAttributes(XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null, null);

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:

  1. 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.
  2. 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.
  3. Unsigned: To specify if the numeric field will be unsigned, we'll use the XMLDB_UNSIGNED constant. If signed, we'll pass null.
  4. Not Null: To specify if the field will be not null, we'll use the XMLDB_NOTNULL constant. If nullable, we'll pass null.
  5. 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.
  6. 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.
  7. 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.
  8. Default value: If the field has some meaningful default value to be defined this parameter will contain it. Else, null.
  9. 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.

Reduced 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 XMLDBTable('my_first_xmldb_table');

$table->addFieldInfo('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
$table->addFieldInfo('name', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null, null, 'default name');
$table->addFieldInfo('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, null);
$table->addFieldInfo('type', XMLDB_TYPE_CHAR, '20', null, XMLDB_NOTNULL, null, XMLDB_ENUM, array('type1', 'type2', 'type3'), 'type1');
$table->addFieldInfo('summary', XMLDB_TYPE_TEXT, 'medium', null, null, null, null, null, null);

$table->addKeyInfo('primary', XMLDB_KEY_PRIMARY, array('id'), null, null);
$table->addKeyInfo('foreignkey1', XMLDB_KEY_FOREIGN, array('course'), 'course', array('id)); 

$table->addIndexInfo('type', XMLDB_INDEX_NOTUNIQUE, array('type'));

$status = create_table($table);

Better, isn't it? ;-)

Integration with the XMLDB Editor

See also

  • DDL functions: To get access to the updated documentation about the functions available to modify the DB objects.
  • XML structure: To know a bit more about the internal XML structure used to describe all the DB objects using one neutral language.
  • List of functions to create: The list of functions to be created from scratch. Used to follow the progress and its status.