Development:DB layer 2.0 migration docs

Introduction

Much of the following documentation will not make much sense unless you first read the XMLDB documentation. Please read it first if you would like to join the effort to convert Moodle's code to the new dmllib.

Also, it's recommended to read the whole DDL and DML documentation before starting with the migration. That will allow you to have some initial knowledge about the new architecture.

This article defines all the changes that need to be performed in Moodle 1.9 code in order to make it run properly under the new Moodle 2.0 DB layer. The changes below are grouped into 2 main blocks, XMLDB and DML, to keep them organised. Additional minor changes may be required in the DDL code, but won't be documented here.

Although the order of changes shown on this page isn't mandatory at all, it can be interesting to follow the migration progress to be able to understand and learn a bit more about all them. That way, you'll end up knowing not only what to change but how and why those changes are required.

Each change will be as simple as possible, representing one easy rule to follow to adapt the code. When anything becomes too complex to be explained as one simple rule, it will contain one link to the examples page.

For any problem in the migration of code, it's recommended to use the Databases forum at moodle.org. Also if you find a bug in the process, please report it in the Moodle Tracker, so that developers will be able to fix it ASAP.

The Glossary module was used as the basis for many of the examples below.

And finally, feel free to complete/fix the list below with the changes you find in the progress of migration; that will certainly help many developers. Thanks!

check_db_syntax: One helper script

Before you start migrating your code to Moodle 2.0, it's recommended you install and run the check_db_syntax.php script. Simply copy it to the main folder of your plugin and execute it (from command line or via web). It will show you a list of old DB usages that need to be transformed following the information below in this article.

If you think that something is missing in the script or have any ideas to improve it, feel free to do that yourself, commenting about it in MDL-15237. Thanks!

Also, this (perl-compatible - works in Eclipse, hopefully elsewhere) regex:

(?<!->)(?<!function )(?<!\$)\b(?:(?:count|delete|get|insert|update)_record(?!s_csv)|[gs]et_field|record_exists|execute_sql)|\$CFG->prefix|rs_(?:fetch|close)|(add|strip)slashes(?!_js)

will find most of the things in your code that need attention, with few false positives.

XMLDB/DDL changes

Some comments

• When changing DB structures it's highly recommended to use the XMLDB Editor (Admin->Development->XMLDB Editor). It is a safe way to edit install.xml files and to get correct PHP code to be used in upgrade.php scripts.
• If you have some doubts about the list of changes below, it's highly recommended to take a look at some code in core modules, blocks... whatever you need. They are a good reference.
• The most noticeable change (from a migration perspective) in the DDL stuff is that, starting with Moodle 2.0, we have decided to drop support for enum (check constraint) fields completely. See MDL-18577 and this discussion. That implies changes in different parts of the DB stuff (XML files, function parameters, dropping existing enums...) and are commented with more detail below.

The changes

• STATEMENTS section was replaced by db/install.php code and db/log.php
• Few other changes are required in install.xml files (that's good news!). Although you must know these:
  • The ENUM and ENUMVALUES attributes present in your install.xml files will be completely ignored both by the DLL generation stuff and by the XMLDB Editor.
  • In Moodle 2.1, those attributes (ENUM and ENUMVALUES) will be 100% forbidden, so it's highly recommended to erase them beginning now (Moodle 2.0).
  • The XMLDB Editor will detect them when loading any install.xml file and will suggest you to fix that easily with one 1-click® option.
  • No matter if you have fixed that with the 1-click® option when loading the files... the XMLDB Editor will save any edited install.xml files without those attributes.
  • If your Moodle 1.9.x code has some enum defined in the database, you will need to create one upgrade block (in db/upgrade.php) to drop it (the enum, not the field! ;-) as part of the migration from Moodle 1.9 to 2.0 by using the new drop_enum_from_field() method.
  • Of course, if you don't use/like the XMLDB Editor, you can erase them manually with something like this:

perl -p -e 's/ENUM(VALUES)?=".*?" //g' < install.xml > install.xml.new

• All upgrade.php scripts, within the main xxxx_upgrade function must have $DB (uppercase) in the globals declaration (along with others if needed). Example (from the Glossary module):

function xmldb_glossary_upgrade($oldversion=0) {

   global $CFG, $THEME, $DB;

• All upgrade.php scripts must NOT have $db (lowercase) in the globals declaration. Delete it if present.
• After the global declaration in the points above, this line must be present (we'll need it later):

$dbman = $DB->get_manager(); /// loads ddl manager and xmldb classes

• All XMLDBTable instances in your upgrade code must be replaced by xmldb_table (no change in parameters)
• All XMLDBField instances in your upgrade code must be replaced by xmldb_field (with change in params, both $enum and $enumvalues are out from function declaration!)
• All XMLDBIndex instances in your upgrade code must be replaced by xmldb_index (no change in parameters)
• All XMLDBKey instances in your upgrade code must be replaced by xmldb_key (no change in parameters)
• All the addFieldInfo() methods must be replaced by add_field() (with change in params, both $enum and $enumvalues (the 7th and 8th parameters) are out from function declaration!)
• All the addIndexInfo() methods must be replaced by add_index() (no change in parameters)
• All the addKeyInfo() methods must be replaced by add_key() (no change in parameters)
• All the setAttributes() methods must be replaced by set_attributes() (with change in params, both $enum and $enumvalues (the 6th and 7th parameters) are out from function declaration!)
• All the DDL functions used in your upgrade code must be transformed as detailed below (it's simply about adding "$dbman->" - without the quotes - before each function call). No changes in parameters are required:
  • table_exists ==> $dbman->table_exists
  • field_exists ==> $dbman->field_exists
  • index_exists ==> $dbman->index_exists
  • find_index_name ==> $dbman->find_index_name
  • find_check_constraint_name ==> $dbman->find_check_constraint_name (DEPRECATED in 2.0. OUT in 2.1)
  • check_constraint_exists ==> $dbman->check_constraint_exists (DEPRECATED in 2.0. OUT in 2.1)
  • find_sequence_name ==> OUT in 2.0, see MDL-20349
  • create_table ==> $dbman->create_table
  • drop_table ==> $dbman->drop_table
  • rename_table ==> $dbman->rename_table
  • add_field ==> $dbman->add_field
  • drop_field ==> $dbman->drop field
  • rename_field ==> $dbman->rename_field
  • change_field_type ==> $dbman->change_field_type
  • change_field_precision => $dbman->change_field_precision
  • change_field_unsigned ==> $dbman->change_field_unsigned
  • change_field_notnull ==> $dbman->change_field_notnull
  • change_field_enum ==> $dbman->change_field_enum (OUT in 2.0, see drop_enum_from_field() to get rid of remaining ENUMs in code).
  • change_field_default ==> $dbman->change_field_default
  • add_key ==> $dbman->add_key
  • drop_key ==> $dbman->drop_key
  • add_index ==> $dbman->add_index
  • drop_index ==> $dbman->drop_index
• The DDL functions that change things (create/drop/rename/add)_(table/field/index) no longer return boolean. Instead, they throw an exception if they fail. So your upgrade.php no longer needs to use a $result variable, and instead should use the upgrade_mod_savepoint() function:

/** old way **/ if ($result && $oldversion < 2008061000) {

   $table = new xmldb_table('facetoface_submissions');
   $field = new xmldb_field('notificationtype');
   $field->set_attributes(XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0', 'timemodified');

   $result = $result && $dbman->add_field($table, $field);

}

/** new way **/ if ($oldversion < 2008061000) {

   $table = new xmldb_table('facetoface_submissions');
   $field = new xmldb_field('notificationtype');
   $field->set_attributes(XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0', 'timemodified');

   $dbman->add_field($table, $field);
   upgrade_mod_savepoint(true, 2008061000, 'facetoface');

}

• Finally, and not less important, your code (module, block... plugin) must guarantee that it can be upgraded ONLY from Moodle 1.9, so any previous upgrade code can be safely deleted. Moodle 2.0 requires Moodle 1.9 to be upgraded, so everybody will run the 1.9 => 2.0 upgrade (with other paths like 1.8 => 2.0 not being possible). This is a good time to clean-up your upgrade code a bit ;-). Don't forget to take a look at the XMLDB Editor and the core modules to see how upgrade.php files should look in Moodle 2.0.

DML changes

Some comments

• The ENTIRE CODEBASE requires an update of ALL database query function calls. Expect most moodle files to be affected by this change.

• This is the more complex part to migrate to have the code working under Moodle 2.0, not because of the complexity of the changes themselves (90% of them will be really easy), but because you'll need to triple-check everything works as expected after changes.

• Along the changes below, you'll find links to some examples that will try to make things easier. Anyway, if you are blocked at any point, please ask in forums or tracker (see links at the beginning of the page). Sure it has a good enough solution.

• Once more it's highly recommended to take a look to Moodle core code, searching for similar examples. Of course, new meaningful examples are welcome, and also any clarification in the list of changes below. Feel free to do it, this is a wiki!

• Finally, one more explanation: The changes below have been split into three sections. First one, (called "The golden changes") are modifications that must be applied to ALL the transformations defined in the second section ("The iron changes"). Sure you'll understand that after reading them (it's basically a matter of not repeating the golden ones within each iron one, just imagine they are everywhere). Finally other changes details can be found in the "The tin changes" section.

The golden changes

PLEASE read the API before going crazy with search & replace! You can find all the new methods in lib/dml/moodle_database.php. This is essential because some method signatures have changed (params are different), and some method names have even changed (execute_sql() is now execute()).

Each of the golden changes below is given one short name (G1, G2, G3...) for further reference in the rest of the documentation. Let's go:

• G1: Wherever old functions are used (get_record*, get_field*, set_field, insert_record, update_record, count_records*, delete_records, record_exists), the global $DB must be used as the object on which these functions are called (e.g. get_record_select() becomes $DB->get_record_select()).

Example:

// Old syntax $sql = "WHERE id = 1"; get_record_select($sql);

// New syntax global $DB; $sql = "WHERE id = 1"; $DB->get_record_select($sql);

Note: for some functions, the $params array is not the second function parameter. For example, set_field:

// Old syntax set_field('user', 'firstname', 'Peter', 'id', 1);

// New syntax global $DB; $DB->set_field('user', 'firstname', 'Peter', array('id' => 1));

• G2: All uses of addslashes() must be removed. They are no longer needed

• G3: All the functions that used to accept a list of string params in the form "param1, value1, param2, value2" now need to be given an array of key=>value pairs as a replacement for these params. Other params remain as before. Check the new API for any exceptions.

Example:

// Old syntax: $user = get_record("user", "firstname", "Peter", "lastname", "Cantrophus");

// New syntax: global $DB; $conditions = array("firstname" => "Peter", "lastname" => "Cantrophus"); $user = $DB->get_record("user", $conditions);

Note: The example above has been written out in full for clarity. You can use the array() directly within the function call, without using a temporary variable, if you prefer:

global $DB; $user = $DB->get_record("user", array("firstname" => "Peter", "lastname" => "Cantrophus") );

• G4: rs_fetch_next_record($rs) is deprecated, in favour of the simple foreach($rs as $var). Calls to rs_close() must be replaced by $rs->close();

Example:

// Old syntax while($result = rs_fetch_next_record($rs)) {

   ...

} rs_close();

// New syntax foreach ($rs as $result) {

   ...

} $rs->close();

• G5: Placeholders must be used for table names. Instead of {$CFG->prefix}tablename, use {tablename}.

Example:

// Old syntax $sql = "SELECT * FROM {$CFG->prefix}user";

// New syntax $sql = "SELECT * FROM {user}";

• G6: When PHP variables are used in SQL queries, they must be replaced by parameters. You have the choice between two approaches: ordered parameters, or named parameters.
  • Ordered parameters use a simple array of values, which are given to the DML function as $params. The values in the SQL code are simply question marks (?) replacing the values. They are replaced by the DML code one by one, substituting each question mark (?) with the next value in the $params array.
  • Named parameters use an associative array of name => value pairs as the $params array. The values in the SQL code are replaced with a colon (:) followed by the key associated with the value, in the $params array. Note that named params must be unique, no matter if the value passed is the same.

Examples:

// Old syntax $sql = "SELECT id, firstname FROM {$CFG->prefix}user WHERE firstname = 'Peter' AND lastname = 'Cantrophus'"; $user = get_record_sql($sql);

// New syntax: ordered params global $DB; $params = array('Peter', 'Cantrophus'); $sql = "SELECT id, firstname FROM {user} WHERE firstname = ? AND lastname = ?"; $user = $DB->get_record_sql($sql, $params);

// New syntax: named params global $DB; $params = array('firstname' => 'Peter', 'lastname' => 'Cantrophus'); $sql = "SELECT id, firstname FROM {user} WHERE firstname = :firstname AND lastname = :lastname"; $user = $DB->get_record_sql($sql, $params);

To obtain a value for the LIKE operator, include any % signs into the parameter string. For example, code that was previously "LIKE '$value%'" becomes "LIKE ?" with the parameter $value.'%'.

• G7: Replacement of the IN(...) syntax: We no longer hard-code this in our SQL queries, we use a function which determines whether the IN() syntax is needed, or, if there is only one value to compare, the equal (=) sign can be used.

Example:

// Old syntax: $depends_on = array(1, 43, 553); $gis = implode(',', $depends_on); $sql = "SELECT *

         FROM {$CFG->prefix}grade_items
        WHERE id IN ($gis)";

$items = $DB->get_records_sql($sql);

// new syntax global $DB; $depends_on = array(1, 43, 553); list($usql, $params) = $DB->get_in_or_equal($depends_on); $sql = "SELECT *

         FROM {grade_items}
        WHERE id $usql";

$items = $DB->get_records_sql($sql, $params);

Note: to combine the last two, do the G7 IN SQL stuff first to generate the params array, then do something like

 $params['firstname'] = 'peter';

to add the stuff for G6

The iron changes

• I1: Originally the sql_substr() function was used without parameters and it returned only the name of the "substring" function to be used under each DB. In Moodle 2.0 and upwards, it has 3 parameters (2 being mandatory) and it returns the complete SQL text to be used when handling substrings. Note that positions in this function are 1-based (first char has index 1).

Example:

// Old syntax $records = get_records_sql("SELECT " . sql_substr() . "(firstname, 1, 20)" . " FROM ... ..."

// New syntax $records = $DB->get_records_sql("SELECT " . $DB->sql_substr('firstname', 1, 20) . " FROM ... ..."

The tin changes

List of minor changes

• T1: Originally get_records() and similar functions were returning false if no records found. All these methods are now always returning arrays, empty array in case of no records found. Please note that get_record() still returns false if specified record not found.

Example:

// Old syntax if (!$posts = get_records('forum_posts', 'parent', 666)) {

   $posts = array()

}

// New syntax global $DB; $posts = $DB->get_records('forum_posts', array('parent'=>666));

• T2: Originally DML functions were returning false if error occurred - dml_exception is thrown now instead.

Example:

// Old syntax $record = new object(); $record->course = 5; if (!$id = insert_record('sometable', $record)) {

  error('can not insert new record');

}

// New syntax global $DB; $record = new object(); $record->course = 5; $id = $DB->insert_record('sometable', $record);

See also

• XMLDB Documentation: where both xmldb and ddl stuff is explained.
• DDL functions - Documentation for all the Data Definition Language (DDL) functions available inside Moodle.
• DML functions - Documentation for all the Data Manipulation Language (DML) functions available inside Moodle.
• DDL exceptions - DDL exceptions information.
• DML exceptions - DML exceptions information.