DB layer 2.0 migration docs

Revision as of 15:16, 5 May 2009 by Eloy Lafuente (stronk7) (talk | contribs) (link to drop_enum_from_field() example)

Jump to: navigation, search

Moodle 2.0


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 start 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 new Moodle 2.0 DB layer. Changes below are grouped into 2 main blocks (xmldb and dml) to have them organised. Additional minor changes may be required in the ddl code, but won't be documented here.

Although the order of changes showed in this page isn't mandatory at all, it can be interesting to follow it in 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 become 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 any bug in the process, please report it in the Moodle Tracker, that way 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 start migrating your code to Moodle 2.0, it's recommended to 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). I'll show you the 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 idea 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|udpate)_record(?!s_csv)|[gs]et_field|record_exists|execute_sql)|\$CFG->prefix|rs_(?:fetch|close)|addslashes(?!_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

  • No changes are required in install.xml files at all (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 since 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 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 (parameters are the same, no change with them required)
  • All XMLDBField instances in your upgrade code must be replaced by xmldb_field (no change in parameters)
  • 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() (no change in parameters)
  • 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() (no change in parameters)
  • All the DDL functions used in upgrade code, must be transformed as detailed below (it's only about to add "$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
   check_constraint_exists ==> $dbman->check_constraint_exists
   find_sequence_name ==> $dbman->find_sequence_name
   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
   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
  • 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). One good time to clean-up a bit your upgrade code ;-). Don't forget to take a look to the XMLDB Editor and to core modules to see how upgrade.php files look like 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 two 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).

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.
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);
  • 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);

The iron changes

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 functions - DDL exceptions information.
  • DML functions - DML exceptions information.