DB layer 2.0 migration docs

Revision as of 00:24, 27 June 2008 by Eloy Lafuente (stronk7) (talk | contribs) (XMLDB/DDL changes)

Jump to: navigation, search

Note: This page is a work-in-progress. Feedback and suggested improvements are welcome. Please join the discussion on moodle.org or use the page comments.


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 find something is missing in the script or have any idea to improve it, feel free to do so yourself or comment it in MDL-15237. Thanks!

XMLDB/DDL changes

Some comments

  • When changing DB structures it's highly recommended to use the XMLDB Editor (Admin->Misc->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 changes

  • No changes are required in install.xml files at all (that's good news!).
  • 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

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()).

  • Wherever old functions are used (get_record*, get_field*, set_field, insert_record, update_record), 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()).
 // Old syntax
 $sql = "WHERE id = 1";
 // New syntax
 global $DB;
 $sql = "WHERE id = 1";

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

 // Old syntax
 set_field('user', 'firstname', 'Eloy', 'id', 1);
 // New syntax
 global $DB;
 $DB->set_field('user', 'firstname', 'Eloy', array('id' => 1));
  • 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.
 // Old syntax:
 $user = get_record("user", "firstname", "Eloy", "lastname", "Lafuente");
 // New syntax:
 global $DB;
 $conditions = array("firstname" => "Eloy", "lastname" => "Lafuente");
 $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" => "Eloy", "lastname" => "Lafuente") );
  • 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();
 // Old syntax
 while($result = rs_fetch_next_record($rs)) {
 // New syntax
 foreach ($rs as $result) {
  • All uses of addslashes() must be removed. They are no longer needed
  • Placeholders must be used for table names. Instead of {$CFG->prefix}tablename, use {tablename}.
 // Old syntax
 $sql = "SELECT * FROM {$CFG->prefix}user";
 // New syntax
 $sql = "SELECT * FROM {user}";
  • 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.
 // Old syntax
 $sql = "SELECT id, firstname FROM {$CFG->prefix}user WHERE firstname = 'Eloy' AND lastname = 'Lafuente'";
 $user = get_record_sql($sql);
 // New syntax: ordered params
 global $DB;
 $params = array('Eloy', 'Lafuente');
 $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' => 'Eloy', 'lastname' => 'Lafuente');
 $sql = "SELECT id, firstname FROM {user} WHERE firstname = :firstname AND lastname = :lastname";
 $user = $DB->get_record_sql($sql, $params);
  • 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.
 // 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

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.