Note:

If you want to create a new page for developers, you should create it on the Moodle Developer Resource site.

DB layer 2.0 migration docs: Difference between revisions

From MoodleDocs
m (Get rid of databases forum link. [https://moodle.org/mod/forum/discuss.php?d=217775])
(40 intermediate revisions by 10 users not shown)
Line 3: Line 3:
Much of the following documentation will not make much sense unless you first read [[XMLDB_Documentation|the XMLDB documentation]]. Please read it first if you would like to join the effort to convert Moodle's code to the new dmllib.
Much of the following documentation will not make much sense unless you first read [[XMLDB_Documentation|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 [[wikipedia:Data_Definition_Language|DDL]] and [[wikipedia:Data_Manipulation_Language|DML]] documentation before start with the migration. That will allow you to have some initial knowledge about the new architecture.
Also, it's recommended to read the whole [[wikipedia:Data_Definition_Language|DDL]] and [[wikipedia:Data_Manipulation_Language|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 new Moodle 2.0 DB layer. Changes below are grouped into 2 main blocks ([[XMLDB_Documentation|xmldb]] and [[wikipedia:Data_Manipulation_Language|dml]]) to have them organised. Additional minor changes may be required in the [[wikipedia:Data_Definition_Language|ddl]] code, but won't be documented here.
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_Documentation|XMLDB]] and [[wikipedia:Data_Manipulation_Language|DML]], to keep them organised. Additional minor changes may be required in the [[wikipedia:Data_Definition_Language|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.
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 become too complex to be explained as one simple rule, it will contain one link to the [[dmllib_2.0_examples|examples page]].
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 [[dmllib_2.0_examples|examples page]].


For any problem in the migration of code, it's recommended to use the [http://moodle.org/mod/forum/view.php?id=45 Databases forum] at [http://moodle.org moodle.org]. Also if you find any bug in the process, please report it in the [http://tracker.moodle.org/browse/MDL-14679 Moodle Tracker], that way developers will be able to fix it ASAP.
For any problem in the migration of code, it's recommended to use the [http://moodle.org/mod/forum/view.php?id=55 General developer forum] at [http://moodle.org moodle.org]. Also if you find a bug in the process, please report it in the [http://tracker.moodle.org/browse/MDL-14679 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.
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!
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!


__TOC__
__TOC__
Line 21: Line 21:
== check_db_syntax: One helper script ==
== check_db_syntax: One helper script ==


Before start migrating your code to Moodle 2.0, it's recommended to install and run the [http://cvs.moodle.org/contrib/tools/check_db_syntax/ 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.
Before you start migrating your code to Moodle 2.0, it's recommended you install and run the [http://download.moodle.org/download.php/direct/tools/check_db_syntax.zip/ 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 idea to improve it, feel free to do that yourself, commenting about it in MDL-15237. Thanks!
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 ==
== XMLDB/DDL changes ==
Line 29: Line 35:
=== Some comments ===
=== 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.
* When changing DB structures it's highly recommended to use the [[XMLDB editor|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.
* 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 [http://moodle.org/mod/forum/discuss.php?d=118852 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 ===
=== 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|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 '''[[DB layer 2.0 examples#Dropping one enum from one field|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


* 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 the Glossary module):
* 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):
<code php>
<code php>
function xmldb_glossary_upgrade($oldversion=0) {
function xmldb_glossary_upgrade($oldversion=0) {
Line 41: Line 56:
     global $CFG, $THEME, $DB;
     global $CFG, $THEME, $DB;
</code>
</code>
* All upgrade.php scripts, must NOT have '''$db''' (lowercase) in the globals declaration. Delete it if present.
* 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):
* After the global declaration in the points above, this line must be present (we'll need it later):
<code php>
<code php>
$dbman = $DB->get_manager(); /// loads ddl manager and xmldb classes
$dbman = $DB->get_manager(); /// loads ddl manager and xmldb classes
</code>
</code>
* All XMLDBTable instances in your upgrade code must be replaced by xmldb_table (parameters are the same, no change with them required)
* 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 (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 '''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 '''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 '''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 '''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 '''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 '''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 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:
* 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
** table_exists ==> $dbman->table_exists
    field_exists ==> $dbman->field_exists
** field_exists ==> $dbman->field_exists
    index_exists ==> $dbman->index_exists
** index_exists ==> $dbman->index_exists
    find_index_name ==> $dbman->find_index_name
** find_index_name ==> $dbman->find_index_name
    find_check_constraint_name ==> $dbman->find_check_constraint_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
** check_constraint_exists ==> $dbman->check_constraint_exists ('''DEPRECATED in 2.0. OUT in 2.1''')
    find_sequence_name ==> $dbman->find_sequence_name
** find_sequence_name ==> '''OUT in 2.0''', see MDL-20349
    create_table ==> $dbman->create_table
** create_table ==> $dbman->create_table
    drop_table ==> $dbman->drop_table
** drop_table ==> $dbman->drop_table
    rename_table ==> $dbman->rename_table
** rename_table ==> $dbman->rename_table
    add_field ==> $dbman->add_field
** add_field ==> $dbman->add_field
    drop_field ==> $dbman->drop field
** drop_field ==> $dbman->drop field
    rename_field ==> $dbman->rename_field
** rename_field ==> $dbman->rename_field
    change_field_type ==> $dbman->change_field_type
** change_field_type ==> $dbman->change_field_type
    change_field_precision => $dbman->change_field_precision
** change_field_precision => $dbman->change_field_precision
    change_field_unsigned ==> $dbman->change_field_unsigned
** change_field_unsigned ==> $dbman->change_field_unsigned
    change_field_notnull ==> $dbman->change_field_notnull
** change_field_notnull ==> $dbman->change_field_notnull
    change_field_enum ==> $dbman->change_field_enum
** change_field_enum ==> $dbman->change_field_enum ('''OUT in 2.0''', see '''[[DB layer 2.0 examples#Dropping one enum from one field|drop_enum_from_field()]]''' to get rid of remaining ENUMs in code).
    change_field_default ==> $dbman->change_field_default
** change_field_default ==> $dbman->change_field_default
    add_key ==> $dbman->add_key
** add_key ==> $dbman->add_key
    drop_key ==> $dbman->drop_key
** drop_key ==> $dbman->drop_key
    add_index ==> $dbman->add_index
** add_index ==> $dbman->add_index
    drop_index ==> $dbman->drop_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.
* 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:
<code php>/** 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');
}</code>
 
* 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|XMLDB Editor]] and the core modules to see how [http://cvs.moodle.org/moodle/lib/db/upgrade.php?view=markup upgrade.php] files should look in Moodle 2.0.


== DML changes ==
== DML changes ==
Line 91: Line 126:
* 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!
* 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).
* 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 ===
=== The golden changes ===
Line 104: Line 139:
// Old syntax
// Old syntax
$sql = "WHERE id = 1";
$sql = "WHERE id = 1";
get_record_select($sql);
get_record_select('user', $sql);
    
    
// New syntax
// New syntax
global $DB;
global $DB;
$sql = "WHERE id = 1";
$sql = "WHERE id = 1";
$DB->get_record_select($sql);
$DB->get_record_select('user', $sql);
</code>
</code>
::Note: for some functions, the $params array is not the second function parameter. For example, set_field:'''
::Note: for some functions, the $params array is not the second function parameter. For example, set_field:'''
Line 172: Line 207:
* <span id="g6"><b>G6</b></span>: 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.
* <span id="g6"><b>G6</b></span>: 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.
** 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.
** 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.


<code php>
<code php>
Line 193: Line 228:
$user = $DB->get_record_sql($sql, $params);
$user = $DB->get_record_sql($sql, $params);
</code>
</code>
To obtain a value for the LIKE operator, include any % signs into the parameter string. For example, code that was previously <tt>"LIKE '$value%'"</tt> becomes <tt>"LIKE ?"</tt> with the parameter <tt>$value.'%'</tt>.


* <span id="g7"><b>G7</b></span>: 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.
* <span id="g7"><b>G7</b></span>: 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.
Line 203: Line 240:
$gis = implode(',', $depends_on);
$gis = implode(',', $depends_on);
$sql = "SELECT *
$sql = "SELECT *
        FROM {$CFG->prefix}grade_items
          FROM {$CFG->prefix}grade_items
        WHERE id IN ($gis)";
        WHERE id IN ($gis)";
$items = $DB->get_records_sql($sql);
$items = $DB->get_records_sql($sql);
    
    
Line 212: Line 249:
list($usql, $params) = $DB->get_in_or_equal($depends_on);
list($usql, $params) = $DB->get_in_or_equal($depends_on);
$sql = "SELECT *
$sql = "SELECT *
        FROM {grade_items}
          FROM {grade_items}
        WHERE id $usql";
        WHERE id $usql";
$items = $DB->get_records_sql($sql, $params);
$items = $DB->get_records_sql($sql, $params);
</code>
</code>
'''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 ===
=== The iron changes ===


*<span id="I1"><b>I1</b></span>: 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).
<code php>
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 ... ..."
</code>


=== The tin changes ===
=== The tin changes ===
Line 262: Line 313:
* [[DDL functions|DDL functions]] - Documentation for all the Data Definition Language (DDL) functions available inside Moodle.
* [[DDL functions|DDL functions]] - Documentation for all the Data Definition Language (DDL) functions available inside Moodle.
* [[DML functions|DML functions]] - Documentation for all the Data Manipulation Language (DML) functions available inside Moodle.
* [[DML functions|DML functions]] - Documentation for all the Data Manipulation Language (DML) functions available inside Moodle.
* [[DDL exceptions|DDL functions]] - DDL exceptions information.
* [[DDL exceptions|DDL exceptions]] - DDL exceptions information.
* [[DML exceptions|DML functions]] - DML exceptions information.
* [[DML exceptions|DML exceptions]] - DML exceptions information.

Revision as of 15:47, 14 December 2013

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 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 General developer 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('user', $sql);

// New syntax global $DB; $sql = "WHERE id = 1"; $DB->get_record_select('user', $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.