MoodleDocs - User contributions [en]

Security

2008-12-11T20:01:14Z

Skodak: /* Firewalls */ rpd fix proposed by Vicente

All web application software is highly complex, and every application has security issues that are found from time to time, usually involving some combination of input that the programmers did not anticipate. The Moodle project takes security seriously, and is continuously improving Moodle to close such holes as we find them.

==Introduction==
*This page contains important security measures for your Moodle installation.
*You should report security problems to the [http://tracker.moodle.org/secure/CreateIssue!default.jspa Moodle tracker] (and mark it as a security issue!) so that developers can see it and inform registered Moodle sites about fixes as soon as possible.
*You should not post actual exploits in the forums or elsewhere on the web (to protect Moodle admins who have not upgraded yet).

==Simple security measures==
*The best security strategy is a good backup! But you don't have a good backup unless you are able to restore it. Test your restoration procedures!
*Load only software or services you will use
*Perform regular updates
*Model your security after the layers of clothing you wear on a cold winter day

==Basic recommendations==
*Update Moodle regularly on each release
:Published security holes draw crackers attention after release. The older the version, the more vulnerabilities it is likely to contain.
*Disable register globals
:This will help prevent against possible XSS problems in third-party scripts.
*Use strong passwords for admin and teachers
:Choosing "difficult" passwords is a basic security practice to protect against "brute force" cracking of accounts.
*Only give teacher accounts to trusted users. Avoid creating public sandboxes with free teacher accounts on production servers.
:Teacher accounts have much freer permissions and it is easier to create situations where data can be abused or stolen.
*Separate your systems as much as possible
:Another basic security technique is to use different passwords on different systems, use different machines for different services and so on. This will prevent damage being widespread even if one account or one server is compromised.

==Run regular updates==
*Use auto update systems
*Windows Update
*Linux: up2date, yum, apt-get
:Consider automating updates with a script scheduled via cron
*Mac OSX update system
*Stay current with php, apache, and moodle

==Use mailing lists to stay updated==
*CERT - http://www.us-cert.gov/cas/signup.html
*PHP - http://www.php.net/mailing-lists.php - sign up for Announcements list
*MySQL - http://lists.mysql.com - sign up for MySQL Announcements

==Firewalls==
*Security experts recommend a dual firewall
:Differing hardware/software combinations
*Disabling unused services is often as effective as a firewall
:Use netstat -a to review open network ports
*Not a guarantee of protection
*Allow ports
:80, 443(ssl), and 9111 (for chat),
:Remote admin: ssh 22, or rdp 3389

==Password policy==

{{Moodle 1.9}}In Moodle 1.9 onwards, a password policy may be set up via ''Administration > Security > [[Site policies]]''.

There is a check box to determine if password complexity should be enforced or not, the option to set the minimum length of the password, the minimum number of digits, the minimum number of lowercase characters, the minimum number of uppercase characters and the minimum number of non alphanumeric characters.

If a user enters a password that does not meet those requirements, they are given an error message indicating the nature of the problem with the entered password.

Enforcing password complexity along with requiring users to change their initial password go a long way in helping ensure that users choose and are in fact using "good passwords".

==Be prepared for the worst==
*Have backups ready
*Practice recovery procedures ahead of time
*Use a rootkit detector on a regular basis
**Linux/MacOSX - http://www.chkrootkit.org/
**Windows - http://www.sysinternals.com/Utilities/RootkitRevealer.html

==Moodle security alerts==
*Register your site with Moodle.org
:Registered users receive email alerts
*Security alerts also posted online
*Web - http://moodle.org/security
*RSS feed - http://moodle.org/rss/file.php/1/1/forum/996/rss.xml

==Miscellaneous considerations==
These are all things you might consider that impact your overall security:
*Use the secure forms setting
*Set the mysql root user password
*Turn off mysql network access
*Use SSL, httpslogins=yes
*Use good passwords - set up a password policy in ''Administration > Security > [[Site policies]]'' (in Moodle 1.9 onwards)
*Do not enable the ''opentogoogle'' setting (in ''Administration > Security > [[Site policies]]'')
*Disable guest access by hiding the guest login button (in ''Administration > Users > [[Authentication]]'')
*Place enrollment keys on all courses
*Disable the enrolment key hint in the [[Internal enrolment]] settings (via ''Administration > Courses > [[Enrolment plugins|Enrolments]]'') (in Moodle 1.9.3 onwards) or remove it by editing the enrolmentkeyhint string in ''moodle.php'' (via ''Administration > Language > [[Language editing]]'') and changing
That enrolment key was incorrect, please try again. (Here's a hint - it starts with '$a')
:to
That enrolment key was incorrect, please try again. Contact your instructor if you need assistance.

==Most secure/paranoid file permissions==

'''Note''': The following information applies to Linux/Unix based installations only, as MS Windows permission system is quite different.

Depending on your server setup there are two different scenarios:
# You are running Moodle on your own dedicated server.
# You are running Moodle on a shared hosting environment.

In the sections below, you are required to use the web service user account and group to set the permissions, so you need to know them. This can vary quite a bit from server to server but if this feature has not been disabled in your server, you can go to http://your.moodle.site/admin/phpinfo.php (logging in as admin), and then search for the line that reads 'User/Group', inside the 'apache' table. For example, I get 'www-data' for the user account and 'www-data' for the group too.

=== Running Moodle on a dedicated server ===
Assuming you are running Moodle on a sealed server (i.e. no user logins allowed on the machine) and that root takes care of the modifications to both moodle code and moodle config (config.php), then this are the most tight permissions I can think of:

1. moodledata directory and all of its contents (and subdirectories, includes sessions):
owner: apache user (apache, httpd, www-data, whatever; see above)
group: apache group (apache, httpd, www-data, whatever; see above)
permissions: 700 on directories, 600 on files

2. moodle directory and all of its contents and subdirectories (including config.php):
owner: root
group: root
permissions: 755 on directories, 644 on files.

If you allow local logins for regular users, then 2. should be:
owner: root
group: apache group (apache, httpd, www-data, whatever; see above)
permissions: 750 on directories, 640 on files.

Think of these permissions as the most paranoid ones. You can be secure enough with less tighter permissions, both in moodledata and moodle directories (and subdirectories).

=== Running Moodle on a shared hosting environment ===
If you are running Moodle on a shared hosting environment, then above permissions are probably wrong. If you set 700 as the permission for directories (and 600 for files), you are probably denying the web service user account access to your directories and files.

If you want to tighten your permissions as much as possible, you will need to know:

# the user account and the group the web service is running under (see above).
# the owner of the directories/files of both moodledata and the moodle directory (this should normally be your client user account), and the group of the directories/files. You can usually get this information from the file manager of your hosting control panel. Go to the moodle folder and pick any directory or file and try to view/change the permissions, owner and group of that file. That would normally show the current permissions, owner and group. Do the same for the moodledata directory.

Then, depending on the following scenarios you should use a different set of permissions (listed from more secure to less secure) for your moodledata directory:

# if the web service account and the owner of the directories/files is the same, you should use 700 for directories and 600 for files.
# if the web service group and the group of the directories/files is the same, you should use 770 for directories and 660 for the files.
# if none of the above, you will need to use 777 for directories and 666 for files, which is less secure but it is your only option. 707 and 606 would be more secure, but it might or might not work, depending on your particular setup.

In fact, you just need to set moodledata the permissions specified above, as all the directories and files inside are created by the web service itself, and will have the right permissions.

Regarding the moodle directory, as long as the web service user account can read the files plus read and execute the directories, that should be enough. There is no need to grant write permission to the web service account/group on any of the files or subdirectories. The only drawback is that you will need to create the config.php by hand during the installation process, as Moodle will not be able to create it. But that should not be a big problem.

==See also==

*[[Security FAQ]]
Using Moodle forum discussions:
*[http://moodle.org/mod/forum/discuss.php?d=39404 Guide to Securing your Moodle Server]
*[http://moodle.org/mod/forum/discuss.php?d=93561 How to secure Moodle website from hacking] including recommendations on emergency recovery

[[Category:Security]]

[[es:Seguridad]]
[[fr:Sécurité]]

Development:Gradebook improvements

2008-12-08T11:04:18Z

Skodak: /* Effectiveness of grading interface */

Same aspects of gradebook were not fully solved in 1.9.x, this page tries to summarize potential improvements

==Aggregation of hidden grades==
Overlook problem partially solve after the database freeze - workaround was to recalculate the grades on the fly so that users that can the hidden grades are ignored. This approach is expensive and can not be used in conditional activities, grade exports, etc.

Solution is to add new flag into grade categories - ''aggregate hidden grades''. Having two values for each grade would be too complex, selective agrragation should solve most of the problems and should be reasonably backwards compatible.

==Final grading in activities==

==Effectiveness of grading interface==

==Tracking of submission dates==

Development:Gradebook improvements

2008-12-08T09:56:27Z

Skodak: New page: Same aspects of gradebook were not fully solved in 1.9.x, this page tries to summarize potential improvements ==Aggregation of hidden grades== Overlook problem partially solve after the d...

Capabilities/moodle/user:viewuseractivitiesreport

2008-12-01T19:22:50Z

Skodak:

*This allows a user to view this user's activity reports and grades (user context only)

====Legacy Role Default Settings====
{| border="1" cellspacing="0" cellpadding="2"
|-
! Legacy Role !! Inherit !! Allow !! Prevent !! Prohibit
|-
| Administrator || X || - || - || -
|-
| Course Creator || X || - || - || -
|-
| Teacher || X || - || - || -
|-
| Non-editing Teacher || X || - || - || -
|-
| Student || X || - || - || -
|-
| Guest || X || - || - || -
|-
| Authenticated User || X || - || - || -
|}

Note: originally there were some defaults for legacy roles, but they were removed because this capability can be used only in user or system context.

==Example usage==

*[[Parent role]]

[[Category:Capabilities|User]]

[[eu:Gaitasunak/moodle/user:viewuseractivitiesreport]]
[[fr:Capabilities/moodle/user:viewuseractivitiesreport]]
[[ja:ケイパビリティ/moodle/user:viewuseractivitiesreport]]

Capabilities/moodle/user:viewuseractivitiesreport

2008-12-01T19:22:16Z

Skodak: fixed definition and added explanation why defaults do not make sense see MDL-17485

*This allows a user to view this user's activity reports and grades (user context only)

====Legacy Role Default Settings====
{| border="1" cellspacing="0" cellpadding="2"
|-
! Legacy Role !! Inherit !! Allow !! Prevent !! Prohibit
|-
| Administrator || X || - || - || -
|-
| Course Creator || X || - || - || -
|-
| Teacher || X || - || - || -
|-
| Non-editing Teacher || X || - || - || -
|-
| Student || X || - || - || -
|-
| Guest || X || - || - || -
|-
| Authenticated User || X || - || - || -
|}

Note: originally there were some defaults for legacy roles, but they were removed because this capability can be used only in user context.

==Example usage==

*[[Parent role]]

[[Category:Capabilities|User]]

[[eu:Gaitasunak/moodle/user:viewuseractivitiesreport]]
[[fr:Capabilities/moodle/user:viewuseractivitiesreport]]
[[ja:ケイパビリティ/moodle/user:viewuseractivitiesreport]]

Development:Coding

2008-11-27T22:38:34Z

Skodak: /* General rules */

Any collaborative project needs consistency and stability to stay strong.

These '''coding guidelines''' are to provide a goal for all Moodle code to strive to. It's true that some of the older existing code falls short in a few areas, but it will all be fixed eventually. All new code definitely must adhere to these standards as closely as possible.

==General rules==

# All '''code''' files should use the '''.php''' extension.
# All '''template''' files should use the '''.html''' extension.
# All text files should use '''Unix-style text format''' (most text editors have this as an option).
# All php tags must be 'full' tags like '''<?php ?>''' ... not 'short' tags like <? ?>.
# All existing '''copyright notices must be retained'''. You can add your own if necessary.
# Each file should '''include (require_once) the main config.php''' file.
# Any '''other include/require should use an absolute path''' beginning with $CFG->dirroot or $CFG->libdir, not relative includes, which [http://uk.php.net/manual/en/function.include.php sometimes behave strangely under PHP].
# Includes should be done at the top of files or inside functions/methods, do not include/require in the middle of file in global scope
# Each file should '''check that the user is authenticated correctly''', using require_login() and has_capability() or require_capability().
# All '''access to databases should use the functions in ''lib/dmllib.php''''' whenever possible - this allows compatibility across a wide range of databases. You should find that almost anything is possible using these functions. If you must write SQL code then make sure it is: cross-platform; restricted to specific functions within your code (usually a lib.php file); and clearly marked.
# '''Don't create new global variables'''. Use only the Moodle-standard $CFG, $SESSION, $THEME, $SITE, $COURSE and $USER global variables.
# '''All variables should be initialised''' or at least tested for existence using isset() or empty() before they are used.
# '''All strings should be translatable''' - create new texts in the "lang/en_utf8" files with concise English lowercase names and retrieve them from your code using get_string() or print_string(). Never delete strings to ensure backwards compatibility of the language packs is maintained.
# '''Don't use p() and s() functions''' to print language strings. Those functions are not designed to print html with tags. Use echo() instead.
# All '''errors should be printed using print_error()''' to maximise translation and help for users (it automatically links to the docs wiki).
# All '''help files should be translatable''' - create new texts in the "lang/en_utf8/help" directory and call them using helpbutton(). If you need to update a help file:
#* with a minor change, where an old translation of the file would still make sense, then it's OK to make the change but you should notify translation AT moodle DOT org.
#* for a major change you should create a new file by adding an incrementing number (eg filename2.html) so that translators can easily see it's a new version of the file. Obviously the new code and the help index files should also be modified to point to the newest versions.
# Incoming data from the browser (sent via '''GET or POST) automatically has magic_quotes applied''' (regardless of the PHP settings) so that you can safely insert it straight into the database. '''All other raw data (from files, or from databases) must be escaped with addslashes()''' before inserting it into the database. Because this is so often done incorrectly, there is more explanation on this issue of adding and stripping slashes on a [[Developer:Slashes|separate page]].
# VERY IMPORTANT: Within Moodle, '''All texts should be printed using the format_text() function''', especially those that have come from users. This ensures that text is filtered and cleaned correctly. More information can be found on the page about [[Development:Output_functions|output functions]].
# User actions should be logged using the [[Development:Logs|add_to_log()]] function. These logs are used for [[Settings#Show_activity_reports|activity reports]] and [[Logs]].
# '''Always create HTML links relative to the full site root''', i.e. link to ''$CFG->wwwroot/mod/blonk/view.php?id=99'' rather than just ''view.php?id=99''. This means that your code will work if called by a script in another folder, among other things.
# Modules should '''store config variables using ''set_config('varname', $value, 'mod/modulename')''''' where the last parameter is the path to the module directory.
# '''Respect decimal separators specified''' in language pack - do not use number_format() directly, instead use format_float() and unformat_float() when displaying or processing floating point numbers (mandatory in 2.0)

==Coding style==

I know it can be a little annoying to change your style if you're used to something else, but balance that annoyance against the annoyance of all the people trying later on to make sense of Moodle code with mixed styles. There are obviously many good points for and against any style that people use, but the current style just is, so please stick to it.

1. Indenting should be consistently 4 spaces. Don't use tabs AT ALL.

2. Variable names should always be easy-to-read, meaningful lowercase English words. If you really need more than one word then run them together, but keep them short as possible. Use plural names for arrays of objects.

GOOD: $quiz
GOOD: $errorstring
GOOD: $assignments (for an array of objects)
GOOD: $i (but only in little loops)

BAD: $Quiz
BAD: $aReallyLongVariableNameWithoutAGoodReason
BAD: $error_string

3. Constants should always be in upper case, and always start with the name of the module. They should have words separated by underscores.

define("FORUM_MODE_FLATOLDEST", 1);
4. Function names should be simple English lowercase words, and start with the name of the module to avoid conflicts between modules. Words should be separated by underscores. Parameters should always have sensible defaults if possible. Note there is no space between the function name and the following (brackets).

function forum_set_display_mode($mode=0) {
global $USER, $CFG;

if ($mode) {
$USER->mode = $mode;
} else if (empty($USER->mode)) {
$USER->mode = $CFG->forum_displaymode;
}
}

The same applies to naming classes and their methods. Use lowercase words separated by underscores.

class some_custom_class {
function class_method() {
...
}
}

Note there are some exceptions in the code using camelcase: class SomeCustomClass {function classMethod(){...}}, but these are usually there because of compatibility with thirds party libraries (e.g. formslib).

5. Blocks must always be enclosed in curly braces (even if there is only one line). Moodle uses this style:

if ($quiz->attempts) {
if ($numattempts > $quiz->attempts) {
error($strtoomanyattempts, "view.php?id=$cm->id");
}
}

6. Strings should be defined using single quotes where possible, so that [http://php.net/types.string less memory is used].

$var = 'some text without any variables';
$var = 'with special characters like a new line '."\n";
$var = 'a very, very long string with a '.$single.' variable in it';
$var = "some $text with $many variables $within it";

7. Comments should be added as much as is practical, to explain the code flow and the purpose of functions and variables.

* Every function (and class) should use the popular [http://www.phpdoc.org/ phpDoc format]. This allows code documentation to be generated automatically.
* Inline comments should use the // style, laid out neatly so that it fits among the code and lines up with it.

/**
* The description should be first, with asterisks laid out exactly
* like this example. If you want to refer to a another function,
* do it like this: {@link clean_param()}. Then, add descriptions
* for each parameter as follows.
*
* @param int $postid The PHP type is followed by the variable name
* @param array $scale The PHP type is followed by the variable name
* @param array $ratings The PHP type is followed by the variable name
* @return mixed
*/
function forum_get_ratings_mean($postid, $scale, $ratings=NULL) {
if (!$ratings) {
$ratings = array(); // Initialize the empty array
if ($rates = get_records("forum_ratings", "post", $postid)) {
// Process each rating in turn
foreach ($rates as $rate) {
....etc

8. Space should be used liberally - don't be afraid to spread things out a little to gain some clarity. Generally, there should be one space between brackets and normal statements, but no space between brackets and variables or functions:

foreach ($objects as $key => $thing) {
process($thing);
}

if ($x == $y) {
$a = $b;
} else if ($x == $z) {
$a = $c;
} else {
$a = $d;
}

9. When making a COPY of an object, always use the php5 clone() function (otherwise you may end up with just a reference to the first object). Moodle will make sure this works consistently on php4 too.

BAD: $b = $a;
GOOD: $b = clone($a);

If the thing you want to copy is not an object, but may contain objects (eg an array of objects) then use fullclone() instead.

==Database structures==

To help you create tables that meet these guidelines, we recommend you use the built in [[Development:XMLDB_defining_an_XML_structure#The_XMLDB_editor|database definition (XMLDB) editor]].

# Every table must have an auto-incrementing id field (INT10) as primary index. (see [[IdColumnReasons]])
# The main table containing instances of each module must have the same name as the module (eg widget) and contain the following minimum fields:
#* id - as described above
#* course - the id of the course that each instance belongs to
#* name - the full name of each instance of the module
# Other tables associated with a module that contain information about 'things' should be named widget_things (note the plural).
# Table and column names should avoid using [[Database reserved words|reserved words in any database]]. Please check them before creation. Table names may be up to 28 characters long, and Column names up to 30 characters.
# Column names should be always lowercase, simple and short, following the same rules as for variable names.
# Where possible, columns that contain a reference to the id field of another table (eg widget) should be called widgetid. (Note that this convention is newish and not followed in some older tables)
# Boolean fields should be implemented as small integer fields (eg INT4) containing 0 or 1, to allow for later expansion of values if necessary.
# Most tables should have a timemodified field (INT10) which is updated with a current timestamp obtained with the PHP time() function.
# Always define a default value for each field (and make it sensible)
# Each table name should start with the database prefix ($CFG->prefix). In a lot of cases, this is taken care of for you automatically. Also, under Postgres, the name of every index must start with the prefix too.
# In order to guarantee [[Development:XMLDB problems#Table and column aliases - the AS keyword|cross-db compatibility]] follow these simple rules about the use of the '''AS''' keyword (only if you need table/colum aliases, of course):
#* '''Don't use''' the '''AS''' keyword for '''table aliases'''.
#* '''Do use''' the '''AS''' keyword for '''column aliases'''.
# '''Never''' create UNIQUE KEYs (constraints) at all. Instead use UNIQUE INDEXes. In the future, if we decide to add referential integrity to Moodle and we need UNIQUE KEYs they will be used, but not now. Please note that the XMLDB editor allows you to specify both XMLDB-only UNIQUE and FOREIGN constraints (and that's good, in order to have the XML well defined) but only underlying INDEXes will be generated.
# Those XMLDB-only UNIQUE KEYs (read previous point) only must be defined if such field/fields '''are going to be the target''' for some (XMLDB-only too) FOREIGN KEY. Else, create them as simple UNIQUE INDEXes.
# Tables associated '''with one block''' must follow this convention with their names: '''$CFG->prefix + "block_" + name_of_the_block + anything_else'''. For example, assuming that $CFG->prefix is 'mdl_', all the tables for the block "rss_client" must start by 'mdl_block_rss_client' (being possible to add more words at the end, i.e. 'mdl_block_rss_client_anothertable'...). This rule will be 100% enforced with Moodle 2.0, giving time to developers until then. See [http://tracker.moodle.org/browse/MDL-6786 Task 6786] for more info about this.
# '''Never''' make database changes in the STABLE branches. If we did, then users upgrading from one stable version to the next would have duplicate changes occurring, which may cause serious errors.
# When refering to integer variable in SQL queries, do not surround the value in quotes. For example, get_records_select('question', "category=$catid") is right. get_records_select('question', "category='$catid'") is wrong. It hides bugs where $catid is undefined. ([http://moodle.org/mod/forum/discuss.php?d=80629 This thread explains].)

==Security issues (and handling form and URL data)==

# Do not rely on 'register_globals'. Every variable must be properly initialised in every code file. It must be obvious where the variable came from.
# Initialise all arrays and objects, even if empty. $a = array() or $obj = new stdClass();.
# Do not use the optional_variable() function (this function is now deprecated). Use the optional_param() function instead. Pick the correct PARAM_XXXX value for the data type you expect.
# Do not use the require_variable() function (this function is now deprecated). Use the required_param() function instead. Pick the correct PARAM_XXXX value for the data type you expect.
# Use data_submitted(), with care. Data must still be cleaned before use.
# Do not use $_GET, $_POST or $_REQUEST. Use the appropriate required_param() or optional_param() appropriate to your need.
# Do not check for an action using something like if (isset($_GET['something'])). Use, e.g., $something = optional_param( 'something',-1,PARAM_INT ) and then perform proper test for it being in its expected range of values e.g., if ($something>=0) {....
# Wherever possible group all your required_param(), optional_param() and other variables initialisation at the beginning of each file to make them easy to find.
# Use 'sesskey' mechanism to protect form handling routines from attack. Basic example of use: when form is generated, include <input type="hidden" name="sesskey" value="<?php echo sesskey(); ?>" />. When you process the form check with if (!confirm_sesskey()) { print_error('confirmsesskeybad');}.
# All filenames must be 'cleaned' using the clean_filename() function, if this has not been done already by appropriate use of required_param() or optional_param()
# Any data read from the database must have [[Developer:Slashes|addslashes()]] applied to it before it can be written back. A whole object of data can be hit at once with addslashes_object().
# addslashes_js() must be used for escaping of javascript strings
# Wherever possible, data to be stored in the database must come from POST data (from a form with method="POST") as opposed to GET data (ie, data from the URL line).
# Do not use data from $_SERVER if you can avoid it. This has portability issues.
# If it hasn't been done somewhere else, make sure all data written to the database has been through the clean_param() function using the appropriate PARAM_XXXX for the datatype.
# If you write custom SQL code, make very sure it is correct. In particular watch out for missing quotes around values. Possible SQL 'injection' exploit.
# Check all data (particularly that written to the database) in every file it is used. Do not expect or rely on it being done somewhere else.
# Blocks of code to be included should contain a definite PHP structure (e.g, a class declaration, function definition(s) etc.) - straight blocks of code promote uninitialised variable usage.
# If you need to use shell_exec() (or any other shell invoking function), make sure you clean parameters first with escapeshellcmd()/escapeshellarg (otherwise, we open up for shell injection attacks).

==Use of JavaScript within Moodle==

On a separate page, see [[Development:JavaScript guidelines]].

[[Category:Developer|Coding]]

[[es:Manual de Estilo de Código]]
[[ja:コーディング]]
[[zh:代码指南]]
[[pl:Kodowanie]]
[[pt:manual_de_codigo]]

Development:DB layer 2.0 migration docs

2008-11-27T18:26:02Z

Skodak: /* The tin changes */

{{Template:Development:dmllib 2.0}}{{Moodle_2.0}}
== Introduction ==
Much of the following documentation will not make much sense unless you first read [[Development: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.

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 ([[Development: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.

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 [[Development: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.

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!

__TOC__

== 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.

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!

== 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):
<code php>
function xmldb_glossary_upgrade($oldversion=0) {

global $CFG, $THEME, $DB;
</code>
* 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):
<code php>
$dbman = $DB->get_manager(); /// loads ddl manager and xmldb classes
</code>
* 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()).
<code php>
Example:

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

// New syntax
global $DB;
$sql = "WHERE id = 1";
$DB->get_record_select($sql);
</code>
::Note: for some functions, the $params array is not the second function parameter. For example, set_field:'''
<code php>
// Old syntax
set_field('user', 'firstname', 'Peter', 'id', 1);

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

* 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.
<code php>
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);
</code>
::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:''
<code php>
global $DB;
$user = $DB->get_record("user", array("firstname" => "Peter", "lastname" => "Cantrophus") );
</code>

* 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();
<code php>
Example:

// Old syntax
while($result = rs_fetch_next_record($rs)) {
...
}
rs_close();

// New syntax
foreach ($rs as $result) {
...
}
$rs->close();
</code>

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

<code php>
Example:

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

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

* 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.

<code php>
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);
</code>

* 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.

<code php>
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);
</code>

=== 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.
<code php>
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));
</code>

* T2: Originally DML functions were returning ''false'' if error occurred - ''dml_exception'' is thrown now instead.
<code php>
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);
</code>

== See also ==

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

Development:DB layer 2.0 migration docs

2008-11-27T18:11:33Z

Skodak: /* The tin changes */

{{Template:Development:dmllib 2.0}}{{Moodle_2.0}}
== Introduction ==
Much of the following documentation will not make much sense unless you first read [[Development: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.

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 ([[Development: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.

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 [[Development: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.

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!

__TOC__

== 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.

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!

== 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):
<code php>
function xmldb_glossary_upgrade($oldversion=0) {

global $CFG, $THEME, $DB;
</code>
* 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):
<code php>
$dbman = $DB->get_manager(); /// loads ddl manager and xmldb classes
</code>
* 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()).
<code php>
Example:

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

// New syntax
global $DB;
$sql = "WHERE id = 1";
$DB->get_record_select($sql);
</code>
::Note: for some functions, the $params array is not the second function parameter. For example, set_field:'''
<code php>
// Old syntax
set_field('user', 'firstname', 'Peter', 'id', 1);

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

* 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.
<code php>
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);
</code>
::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:''
<code php>
global $DB;
$user = $DB->get_record("user", array("firstname" => "Peter", "lastname" => "Cantrophus") );
</code>

* 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();
<code php>
Example:

// Old syntax
while($result = rs_fetch_next_record($rs)) {
...
}
rs_close();

// New syntax
foreach ($rs as $result) {
...
}
$rs->close();
</code>

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

<code php>
Example:

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

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

* 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.

<code php>
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);
</code>

* 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.

<code php>
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);
</code>

=== 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.
<code php>
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));
</code>

* T2: Originally DML functions were returning ''false'' if error occurred - exceptions are thrown now instead.
<code php>
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);
</code>

== See also ==

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

Development:DB layer 2.0 migration docs

2008-11-27T18:11:05Z

Skodak: /* The tin changes */

{{Template:Development:dmllib 2.0}}{{Moodle_2.0}}
== Introduction ==
Much of the following documentation will not make much sense unless you first read [[Development: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.

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 ([[Development: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.

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 [[Development: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.

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!

__TOC__

== 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.

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!

== 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):
<code php>
function xmldb_glossary_upgrade($oldversion=0) {

global $CFG, $THEME, $DB;
</code>
* 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):
<code php>
$dbman = $DB->get_manager(); /// loads ddl manager and xmldb classes
</code>
* 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()).
<code php>
Example:

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

// New syntax
global $DB;
$sql = "WHERE id = 1";
$DB->get_record_select($sql);
</code>
::Note: for some functions, the $params array is not the second function parameter. For example, set_field:'''
<code php>
// Old syntax
set_field('user', 'firstname', 'Peter', 'id', 1);

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

* 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.
<code php>
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);
</code>
::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:''
<code php>
global $DB;
$user = $DB->get_record("user", array("firstname" => "Peter", "lastname" => "Cantrophus") );
</code>

* 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();
<code php>
Example:

// Old syntax
while($result = rs_fetch_next_record($rs)) {
...
}
rs_close();

// New syntax
foreach ($rs as $result) {
...
}
$rs->close();
</code>

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

<code php>
Example:

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

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

* 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.

<code php>
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);
</code>

* 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.

<code php>
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);
</code>

=== 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.
<code php>
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));
</code>

* T2: Originally DML functions were returning ''false'' if error occurred - exceptions are thrown now instead.
<code php>
Example:

// Old syntax
$record = new object();
$record->course = 5;
if (!$id = insert_record('sometable', $record)) {
error('can not insert new reocrd');
}

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

== See also ==

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

Development:DB layer 2.0 migration docs

2008-11-27T18:06:33Z

Skodak: /* The tin changes */

{{Template:Development:dmllib 2.0}}{{Moodle_2.0}}
== Introduction ==
Much of the following documentation will not make much sense unless you first read [[Development: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.

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 ([[Development: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.

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 [[Development: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.

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!

__TOC__

== 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.

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!

== 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):
<code php>
function xmldb_glossary_upgrade($oldversion=0) {

global $CFG, $THEME, $DB;
</code>
* 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):
<code php>
$dbman = $DB->get_manager(); /// loads ddl manager and xmldb classes
</code>
* 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()).
<code php>
Example:

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

// New syntax
global $DB;
$sql = "WHERE id = 1";
$DB->get_record_select($sql);
</code>
::Note: for some functions, the $params array is not the second function parameter. For example, set_field:'''
<code php>
// Old syntax
set_field('user', 'firstname', 'Peter', 'id', 1);

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

* 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.
<code php>
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);
</code>
::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:''
<code php>
global $DB;
$user = $DB->get_record("user", array("firstname" => "Peter", "lastname" => "Cantrophus") );
</code>

* 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();
<code php>
Example:

// Old syntax
while($result = rs_fetch_next_record($rs)) {
...
}
rs_close();

// New syntax
foreach ($rs as $result) {
...
}
$rs->close();
</code>

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

<code php>
Example:

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

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

* 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.

<code php>
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);
</code>

* 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.

<code php>
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);
</code>

=== 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.
<code php>
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));
</code>

* T2: Originally DML functions were returning ''false'' if error occurred - exceptions are thrown now instead.
<code php>
Example:

// Old syntax
$record = new object();
$record->course = 5;
if (!$id = insert_record('sometable', $record)) {
error('can not insert new reocrd');
}

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

== See also ==

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

Development:DB layer 2.0 migration docs

2008-11-27T17:58:14Z

Skodak:

{{Template:Development:dmllib 2.0}}{{Moodle_2.0}}
== Introduction ==
Much of the following documentation will not make much sense unless you first read [[Development: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.

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 ([[Development: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.

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 [[Development: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.

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!

__TOC__

== 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.

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!

== 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):
<code php>
function xmldb_glossary_upgrade($oldversion=0) {

global $CFG, $THEME, $DB;
</code>
* 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):
<code php>
$dbman = $DB->get_manager(); /// loads ddl manager and xmldb classes
</code>
* 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()).
<code php>
Example:

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

// New syntax
global $DB;
$sql = "WHERE id = 1";
$DB->get_record_select($sql);
</code>
::Note: for some functions, the $params array is not the second function parameter. For example, set_field:'''
<code php>
// Old syntax
set_field('user', 'firstname', 'Peter', 'id', 1);

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

* 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.
<code php>
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);
</code>
::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:''
<code php>
global $DB;
$user = $DB->get_record("user", array("firstname" => "Peter", "lastname" => "Cantrophus") );
</code>

* 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();
<code php>
Example:

// Old syntax
while($result = rs_fetch_next_record($rs)) {
...
}
rs_close();

// New syntax
foreach ($rs as $result) {
...
}
$rs->close();
</code>

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

<code php>
Example:

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

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

* 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.

<code php>
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);
</code>

* 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.

<code php>
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);
</code>

=== 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.
<code php>
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));
</code>

== See also ==

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

Development:DB layer 2.0 migration docs

2008-11-27T17:55:04Z

Skodak: /* The golden changes */

{{Template:Development:dmllib 2.0}}{{Moodle_2.0}}
== Introduction ==
Much of the following documentation will not make much sense unless you first read [[Development: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.

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 ([[Development: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.

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 [[Development: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.

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!

__TOC__

== 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.

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!

== 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):
<code php>
function xmldb_glossary_upgrade($oldversion=0) {

global $CFG, $THEME, $DB;
</code>
* 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):
<code php>
$dbman = $DB->get_manager(); /// loads ddl manager and xmldb classes
</code>
* 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()).
<code php>
Example:

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

// New syntax
global $DB;
$sql = "WHERE id = 1";
$DB->get_record_select($sql);
</code>
::Note: for some functions, the $params array is not the second function parameter. For example, set_field:'''
<code php>
// Old syntax
set_field('user', 'firstname', 'Peter', 'id', 1);

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

* 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.
<code php>
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);
</code>
::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:''
<code php>
global $DB;
$user = $DB->get_record("user", array("firstname" => "Peter", "lastname" => "Cantrophus") );
</code>

* 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();
<code php>
Example:

// Old syntax
while($result = rs_fetch_next_record($rs)) {
...
}
rs_close();

// New syntax
foreach ($rs as $result) {
...
}
$rs->close();
</code>

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

<code php>
Example:

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

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

* 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.

<code php>
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);
</code>

* 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.

<code php>
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);
</code>

=== 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.
<code php>
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));
</code>

=== The iron changes ===

== See also ==

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

Development:DB layer 2.0 migration docs

2008-11-27T17:45:42Z

Skodak: /* See also */

{{Template:Development:dmllib 2.0}}{{Moodle_2.0}}
== Introduction ==
Much of the following documentation will not make much sense unless you first read [[Development: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.

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 ([[Development: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.

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 [[Development: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.

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!

__TOC__

== 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.

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!

== 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):
<code php>
function xmldb_glossary_upgrade($oldversion=0) {

global $CFG, $THEME, $DB;
</code>
* 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):
<code php>
$dbman = $DB->get_manager(); /// loads ddl manager and xmldb classes
</code>
* 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()).
<code php>
Example:

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

// New syntax
global $DB;
$sql = "WHERE id = 1";
$DB->get_record_select($sql);
</code>
::Note: for some functions, the $params array is not the second function parameter. For example, set_field:'''
<code php>
// Old syntax
set_field('user', 'firstname', 'Peter', 'id', 1);

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

* 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.
<code php>
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);
</code>
::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:''
<code php>
global $DB;
$user = $DB->get_record("user", array("firstname" => "Peter", "lastname" => "Cantrophus") );
</code>

* 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();
<code php>
Example:

// Old syntax
while($result = rs_fetch_next_record($rs)) {
...
}
rs_close();

// New syntax
foreach ($rs as $result) {
...
}
$rs->close();
</code>

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

<code php>
Example:

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

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

* 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.

<code php>
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);
</code>

* 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.

<code php>
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);
</code>

=== The iron changes ===

== See also ==

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

File:Ddl exceptions.png

2008-11-27T17:33:15Z

Skodak: Hierarchy of DDL exceptions

Hierarchy of DDL exceptions

Development:DDL exceptions

2008-11-27T17:32:57Z

Skodak: /* Overview */

{{Moodle_2.0}}In previous versions problems during upgrade were ignored by default.

==Overview==
[[Image:Ddl_exceptions.png]]

Defined in lib/ddllib.php for now.

===ddl_exception===
Base class for DDL related exceptions

===ddl_table_missing_exception===
Table specified in function parameter does not exist.

===ddl_field_missing_exception===
Field specified in function parameter does not exist.

===ddl_change_structure_exception===
General exception thrown during execution of sql that changes database structure.

==See also==

* [[Development:Exceptions|Exceptions]]: General guidelines for using of exceptions in Moodle 2.0
* [[Development:DML functions|DML functions]]: Where all the functions used to handle DB data. ([[wikipedia:Data_Manipulation_Language|DML]]) are defined.
* [[Development:DDL functions|DDL functions]]: Where all the functions used to handle DB objects ([[wikipedia:Data_Definition_Language|DDL]]) are defined.

Development:DDL exceptions

2008-11-27T17:32:30Z

Skodak:

{{Moodle_2.0}}In previous versions problems during upgrade were ignored by default.

==Overview==
[[Image:Ddl_exceptions]]

Defined in lib/ddllib.php for now.

===ddl_exception===
Base class for DDL related exceptions

===ddl_table_missing_exception===
Table specified in function parameter does not exist.

===ddl_field_missing_exception===
Field specified in function parameter does not exist.

===ddl_change_structure_exception===
General exception thrown during execution of sql that changes database structure.

==See also==

* [[Development:Exceptions|Exceptions]]: General guidelines for using of exceptions in Moodle 2.0
* [[Development:DML functions|DML functions]]: Where all the functions used to handle DB data. ([[wikipedia:Data_Manipulation_Language|DML]]) are defined.
* [[Development:DDL functions|DDL functions]]: Where all the functions used to handle DB objects ([[wikipedia:Data_Definition_Language|DDL]]) are defined.

Development:DML exceptions

2008-11-27T17:23:19Z

Skodak: /* Overview */

{{Moodle_2.0}}In previous versions problems in database calls were ignored by default.

==Overview==
[[Image:Dml_exceptions.png]]

Defined in lib/dmllib.php for now.

===dml_connection_exception===
Thrown when can not connect to database for any reason.

===dml_read_exception===
Problem occurred during reading from database. Originally indicated be returning ''false'' - this value was often confused with ''false'' return value meaning ''not found''.

===dml_read_exception===
Problem occurred during writing to database. Originally indicated be returning ''false''.

==See also==

* [[Development:Exceptions|Exceptions]]: General guidelines for using of exceptions in Moodle 2.0
* [[Development:DML functions|DML functions]]: Where all the functions used to handle DB data. ([[wikipedia:Data_Manipulation_Language|DML]]) are defined.
* [[Development:DDL functions|DDL functions]]: Where all the functions used to handle DB objects ([[wikipedia:Data_Definition_Language|DDL]]) are defined.

Development:DML exceptions

2008-11-27T17:21:59Z

Skodak: /* Overview */

{{Moodle_2.0}}In previous versions problems in database calls were ignored by default.

==Overview==
[[Image:Dml_exceptions.png]]

===dml_connection_exception===
Thrown when can not connect to database for any reason.

===dml_read_exception===
Problem occurred during reading from database. Originally indicated be returning ''false'' - this value was often confused with ''false'' return value meaning ''not found''.

===dml_read_exception===
Problem occurred during writing to database. Originally indicated be returning ''false''.

==See also==

* [[Development:Exceptions|Exceptions]]: General guidelines for using of exceptions in Moodle 2.0
* [[Development:DML functions|DML functions]]: Where all the functions used to handle DB data. ([[wikipedia:Data_Manipulation_Language|DML]]) are defined.
* [[Development:DDL functions|DDL functions]]: Where all the functions used to handle DB objects ([[wikipedia:Data_Definition_Language|DDL]]) are defined.

Development:DML exceptions

2008-11-27T17:18:26Z

Skodak:

{{Moodle_2.0}}In previous versions problems in database calls were ignored by default.

==Overview==
[[Image:Dml_exceptions.png]]

===connection_exception===

===dml_read_exception===

===dml_read_exception===

==See also==

* [[Development:Exceptions|Exceptions]]: General guidelines for using of exceptions in Moodle 2.0
* [[Development:DML functions|DML functions]]: Where all the functions used to handle DB data. ([[wikipedia:Data_Manipulation_Language|DML]]) are defined.
* [[Development:DDL functions|DDL functions]]: Where all the functions used to handle DB objects ([[wikipedia:Data_Definition_Language|DDL]]) are defined.

File:Dml exceptions.png

2008-11-27T17:17:34Z

Skodak: Hierarchy of DML exceptions

Hierarchy of DML exceptions

Development:DML exceptions

2008-11-27T17:17:12Z

Skodak: /* Overview */

{{Moodle_2.0}}In previous versions problems in database calls were ignored by default.

==Overview==
[[Image:Dml_exceptions.png]]

TODO: add more info here, explanation and same examples.

==See also==

* [[Development:Exceptions|Exceptions]]: General guidelines for using of exceptions in Moodle 2.0
* [[Development:DML functions|DML functions]]: Where all the functions used to handle DB data. ([[wikipedia:Data_Manipulation_Language|DML]]) are defined.
* [[Development:DDL functions|DDL functions]]: Where all the functions used to handle DB objects ([[wikipedia:Data_Definition_Language|DDL]]) are defined.

Development:DML exceptions

2008-11-27T17:17:03Z

Skodak:

{{Moodle_2.0}}In previous versions problems in database calls were ignored by default.

==Overview==
[[[Image:Dml_exceptions.png]]]

TODO: add more info here, explanation and same examples.

==See also==

* [[Development:Exceptions|Exceptions]]: General guidelines for using of exceptions in Moodle 2.0
* [[Development:DML functions|DML functions]]: Where all the functions used to handle DB data. ([[wikipedia:Data_Manipulation_Language|DML]]) are defined.
* [[Development:DDL functions|DDL functions]]: Where all the functions used to handle DB objects ([[wikipedia:Data_Definition_Language|DDL]]) are defined.

Development:Exceptions

2008-11-27T17:03:34Z

Skodak: /* See also */

{{Moodle_2.0}}Exceptions are a common feature in all modern programming languages. They help us to handle exceptional problems that may appear unexpectedly during code execution.

==Overview==
Please keep in mind that exceptions are for something really ''exceptional'', normal code execution flow should not depend on them.

Examples of suitable use are:
* problem writing to directory moodledata directory - this is expected to be always writable
* wrong function/method parameters - coding error which must be fixed by a developer
* inserting data into non-existent database table

Incorrect uses:
* using exceptions instead of break in order to terminate loops
* returning results from functions

==Major benefits==
===Less code===
Many IFs can be eliminated.

===Return values are not mixed with error flags===
In previous versions database functions were returning false if something went wrong during execution. Sometimes the ''false'' value also meant no value found.

===Recovery after error===
Previously if something went wrong we either returned false and printed debug info or terminated execution with error(), print_error() or die(). Exceptions allow us to wrap potentially problematic code in try-catch block and recover from exceptions.

Example of suitable use could be admin/cron.php. There were several problems with module scripts terminating and preventing execution of other parts of cron script. Now we can wrap all calls to cron functions in try-catch block and recover from problems in individual activities or 3rd party code.

==Exception hierarchy==
[[Image:Exceptions.png]]

===moodle_exception===
Unknown type of problem, also used by print_error() and error() functions. Please try to find some more suitable exception type if possible.

Defined in lib/setuplib.php for now.

===coding_exception===
General exception indicating incorrect use of function, bogus parameters, etc. Most probably a typo or developer did not understand the inline docs. Use especially in library functions when you think that the developer should be notified. The hint in constructor is not expected to be localised.

Defined in lib/setuplib.php for now.

===ddl_exception===
Base class for all exceptions that may be thrown during manipulation of database structure. See linked page for details.

===dml_exception===
Base class for all exceptions that may be thrown during normal database access. See linked page for details.

===file_exception===
Base class for file storage related problems.

==See also==

* [http://www.php.net/manual/en/language.exceptions.php PHP Manual - Exceptions]
* [[Development:DML exceptions|DML exceptions]]: Use of exceptions in DML layer
* [[Development:DDL exceptions|DDL exceptions]]: Use of exceptions in DDL layer
* [[Development:File storage exceptions|File exceptions]]: Use of exceptions in file storage layer

Development:Exceptions

2008-11-27T17:02:13Z

Skodak: /* coding_exception */

Development:Exceptions

2008-11-27T16:58:30Z

Skodak: /* moodle_exception */

{{Moodle_2.0}}Exceptions are a common feature in all modern programming languages. They help us to handle exceptional problems that may appear unexpectedly during code execution.

==Overview==
Please keep in mind that exceptions are for something really ''exceptional'', normal code execution flow should not depend on them.

Examples of suitable use are:
* problem writing to directory moodledata directory - this is expected to be always writable
* wrong function/method parameters - coding error which must be fixed by a developer
* inserting data into non-existent database table

Incorrect uses:
* using exceptions instead of break in order to terminate loops
* returning results from functions

==Major benefits==
===Less code===
Many IFs can be eliminated.

===Return values are not mixed with error flags===
In previous versions database functions were returning false if something went wrong during execution. Sometimes the ''false'' value also meant no value found.

===Recovery after error===
Previously if something went wrong we either returned false and printed debug info or terminated execution with error(), print_error() or die(). Exceptions allow us to wrap potentially problematic code in try-catch block and recover from exceptions.

Example of suitable use could be admin/cron.php. There were several problems with module scripts terminating and preventing execution of other parts of cron script. Now we can wrap all calls to cron functions in try-catch block and recover from problems in individual activities or 3rd party code.

==Exception hierarchy==
[[Image:Exceptions.png]]

===moodle_exception===
Unknown type of problem, also used by print_error() and error() functions. Please try to find some more suitable exception type if possible.

Defined in lib/setuplib.php for now.

===coding_exception===
General exception indicating incorrect use of function, bogus parameters, etc. Most probably a typo or developer did not understand the inline docs.

Defined in lib/setuplib.php for now.

===ddl_exception===
Base class for all exceptions that may be thrown during manipulation of database structure. See linked page for details.

===dml_exception===
Base class for all exceptions that may be thrown during normal database access. See linked page for details.

===file_exception===
Base class for file storage related problems.

==See also==

* [http://www.php.net/manual/en/language.exceptions.php PHP Manual - Exceptions]
* [[Development:DML exceptions|DML exceptions]]: Use of exceptions in DML layer
* [[Development:DDL exceptions|DDL exceptions]]: Use of exceptions in DDL layer

Development:Exceptions

2008-11-27T16:58:10Z

Skodak: /* moodle_exception */

{{Moodle_2.0}}Exceptions are a common feature in all modern programming languages. They help us to handle exceptional problems that may appear unexpectedly during code execution.

==Overview==
Please keep in mind that exceptions are for something really ''exceptional'', normal code execution flow should not depend on them.

Examples of suitable use are:
* problem writing to directory moodledata directory - this is expected to be always writable
* wrong function/method parameters - coding error which must be fixed by a developer
* inserting data into non-existent database table

Incorrect uses:
* using exceptions instead of break in order to terminate loops
* returning results from functions

==Major benefits==
===Less code===
Many IFs can be eliminated.

===Return values are not mixed with error flags===
In previous versions database functions were returning false if something went wrong during execution. Sometimes the ''false'' value also meant no value found.

===Recovery after error===
Previously if something went wrong we either returned false and printed debug info or terminated execution with error(), print_error() or die(). Exceptions allow us to wrap potentially problematic code in try-catch block and recover from exceptions.

Example of suitable use could be admin/cron.php. There were several problems with module scripts terminating and preventing execution of other parts of cron script. Now we can wrap all calls to cron functions in try-catch block and recover from problems in individual activities or 3rd party code.

==Exception hierarchy==
[[Image:Exceptions.png]]

===moodle_exception===
Unknown type of problem, also used by print_error() and error() functions. Please try to find some more suitable exception type if possible.

===coding_exception===
General exception indicating incorrect use of function, bogus parameters, etc. Most probably a typo or developer did not understand the inline docs.

Defined in lib/setuplib.php for now.

===ddl_exception===
Base class for all exceptions that may be thrown during manipulation of database structure. See linked page for details.

===dml_exception===
Base class for all exceptions that may be thrown during normal database access. See linked page for details.

===file_exception===
Base class for file storage related problems.

==See also==

* [http://www.php.net/manual/en/language.exceptions.php PHP Manual - Exceptions]
* [[Development:DML exceptions|DML exceptions]]: Use of exceptions in DML layer
* [[Development:DDL exceptions|DDL exceptions]]: Use of exceptions in DDL layer

Development:Exceptions

2008-11-27T16:57:58Z

Skodak: /* =coding_exception */

{{Moodle_2.0}}Exceptions are a common feature in all modern programming languages. They help us to handle exceptional problems that may appear unexpectedly during code execution.

==Overview==
Please keep in mind that exceptions are for something really ''exceptional'', normal code execution flow should not depend on them.

Examples of suitable use are:
* problem writing to directory moodledata directory - this is expected to be always writable
* wrong function/method parameters - coding error which must be fixed by a developer
* inserting data into non-existent database table

Incorrect uses:
* using exceptions instead of break in order to terminate loops
* returning results from functions

==Major benefits==
===Less code===
Many IFs can be eliminated.

===Return values are not mixed with error flags===
In previous versions database functions were returning false if something went wrong during execution. Sometimes the ''false'' value also meant no value found.

===Recovery after error===
Previously if something went wrong we either returned false and printed debug info or terminated execution with error(), print_error() or die(). Exceptions allow us to wrap potentially problematic code in try-catch block and recover from exceptions.

Example of suitable use could be admin/cron.php. There were several problems with module scripts terminating and preventing execution of other parts of cron script. Now we can wrap all calls to cron functions in try-catch block and recover from problems in individual activities or 3rd party code.

==Exception hierarchy==
[[Image:Exceptions.png]]

==moodle_exception==
Unknown type of problem, also used by print_error() and error() functions. Please try to find some more suitable exception type if possible.

===coding_exception===
General exception indicating incorrect use of function, bogus parameters, etc. Most probably a typo or developer did not understand the inline docs.

Defined in lib/setuplib.php for now.

===ddl_exception===
Base class for all exceptions that may be thrown during manipulation of database structure. See linked page for details.

===dml_exception===
Base class for all exceptions that may be thrown during normal database access. See linked page for details.

===file_exception===
Base class for file storage related problems.

==See also==

* [http://www.php.net/manual/en/language.exceptions.php PHP Manual - Exceptions]
* [[Development:DML exceptions|DML exceptions]]: Use of exceptions in DML layer
* [[Development:DDL exceptions|DDL exceptions]]: Use of exceptions in DDL layer

Development:Exceptions

2008-11-27T16:57:44Z

Skodak: /* Exception hierarchy */

{{Moodle_2.0}}Exceptions are a common feature in all modern programming languages. They help us to handle exceptional problems that may appear unexpectedly during code execution.

==Overview==
Please keep in mind that exceptions are for something really ''exceptional'', normal code execution flow should not depend on them.

Examples of suitable use are:
* problem writing to directory moodledata directory - this is expected to be always writable
* wrong function/method parameters - coding error which must be fixed by a developer
* inserting data into non-existent database table

Incorrect uses:
* using exceptions instead of break in order to terminate loops
* returning results from functions

==Major benefits==
===Less code===
Many IFs can be eliminated.

===Return values are not mixed with error flags===
In previous versions database functions were returning false if something went wrong during execution. Sometimes the ''false'' value also meant no value found.

===Recovery after error===
Previously if something went wrong we either returned false and printed debug info or terminated execution with error(), print_error() or die(). Exceptions allow us to wrap potentially problematic code in try-catch block and recover from exceptions.

Example of suitable use could be admin/cron.php. There were several problems with module scripts terminating and preventing execution of other parts of cron script. Now we can wrap all calls to cron functions in try-catch block and recover from problems in individual activities or 3rd party code.

==Exception hierarchy==
[[Image:Exceptions.png]]

==moodle_exception==
Unknown type of problem, also used by print_error() and error() functions. Please try to find some more suitable exception type if possible.

===coding_exception==
General exception indicating incorrect use of function, bogus parameters, etc. Most probably a typo or developer did not understand the inline docs.

Defined in lib/setuplib.php for now.

===ddl_exception===
Base class for all exceptions that may be thrown during manipulation of database structure. See linked page for details.

===dml_exception===
Base class for all exceptions that may be thrown during normal database access. See linked page for details.

===file_exception===
Base class for file storage related problems.

==See also==

* [http://www.php.net/manual/en/language.exceptions.php PHP Manual - Exceptions]
* [[Development:DML exceptions|DML exceptions]]: Use of exceptions in DML layer
* [[Development:DDL exceptions|DDL exceptions]]: Use of exceptions in DDL layer

Development:Exceptions

2008-11-27T16:52:11Z

Skodak:

File:Exceptions.png

2008-11-27T16:51:19Z

Skodak: Top exceptions hierarchy

Top exceptions hierarchy

Development:Exceptions

2008-11-27T15:42:22Z

Skodak: exceptions info

Development:Exceptions

2008-11-27T15:15:06Z

Skodak: /* See also */

{{Moodle_2.0}}

TODO: add more info here, explanation and same examples.

==See also==

* [http://www.php.net/manual/en/language.exceptions.php PHP Manual - Exceptions]
* [[Development:DML exceptions|DML exceptions]]: Use of exceptions in DML layer
* [[Development:DDL exceptions|DDL exceptions]]: Use of exceptions in DDL layer

Development:DDL exceptions

2008-11-27T15:08:24Z

Skodak: New page: {{Moodle_2.0}}In previous versions problems during upgrade were ignored by default. TODO: add more info here, explanation and same examples. ==See also== * [[Development:Exceptions|Ex...

{{Moodle_2.0}}In previous versions problems during upgrade were ignored by default.

TODO: add more info here, explanation and same examples.

==See also==

* [[Development:Exceptions|Exceptions]]: General guidelines for using of exceptions in Moodle 2.0
* [[Development:DML functions|DML functions]]: Where all the functions used to handle DB data. ([[wikipedia:Data_Manipulation_Language|DML]]) are defined.
* [[Development:DDL functions|DDL functions]]: Where all the functions used to handle DB objects ([[wikipedia:Data_Definition_Language|DDL]]) are defined.

Development:Exceptions

2008-11-27T15:07:09Z

Skodak:

{{Moodle_2.0}}

TODO: add more info here, explanation and same examples.

==See also==

* [[Development:DML exceptions|DML exceptions]]: Use of exceptions in DML layer
* [[Development:DDL exceptions|DDL exceptions]]: Use of exceptions in DDL layer

Development:Exceptions

2008-11-27T15:06:30Z

Skodak: New page: {{Moodle_2.0}} TODO: add more info here, explanation and same examples. ==See also== * DML Exceptions: Use of exceptions in DML layer * [[Development:DD...

{{Moodle_2.0}}

TODO: add more info here, explanation and same examples.

==See also==

* [[Development:DML Exceptions|DML Exceptions]]: Use of exceptions in DML layer
* [[Development:DDL Exceptions|DDL Exceptions]]: Use of exceptions in DDL layer

Development:DML exceptions

2008-11-27T15:05:39Z

Skodak: /* See also */

{{Moodle_2.0}}In previous versions problems in database calls were ignored by default.

TODO: add more info here, explanation and same examples.

==See also==

* [[Development:Exceptions|Exceptions]]: General guidelines for using of exceptions in Moodle 2.0
* [[Development:DML functions|DML functions]]: Where all the functions used to handle DB data. ([[wikipedia:Data_Manipulation_Language|DML]]) are defined.
* [[Development:DDL functions|DDL functions]]: Where all the functions used to handle DB objects ([[wikipedia:Data_Definition_Language|DDL]]) are defined.

Development:DML exceptions

2008-11-27T15:05:17Z

Skodak: /* See also */

{{Moodle_2.0}}In previous versions problems in database calls were ignored by default.

TODO: add more info here, explanation and same examples.

==See also==

* [[Development:Exceptions|Exceptions]]: General guidlines for using of exceptions in Moodle 2.0
* [[Development:DML functions|DML functions]]: Where all the functions used to handle DB data. ([[wikipedia:Data_Manipulation_Language|DML]]) are defined.
* [[Development:DDL functions|DDL functions]]: Where all the functions used to handle DB objects ([[wikipedia:Data_Definition_Language|DDL]]) are defined.

Development:DTL functions

2008-11-24T23:37:14Z

Skodak: New page: {{Moodle_2.0}}New feature that enables transferring, import and export of sql data from one server to another. Useful especially when moving data to another type of database. This is the ...

{{Moodle_2.0}}New feature that enables transferring, import and export of sql data from one server to another. Useful especially when moving data to another type of database.

This is the result of GSOC 2OO8 project by Andrei Bautu.

==TODO==
* add more info here
* add links to pages and docs created by Andrei

==See also==
* [[Development:DML functions|DML functions]]: Where all the functions used to handle DB data ([[wikipedia:Data_Manipulation_Language|DML]]) are defined.
* [[Development:DDL functions|DDL functions]]: Where all the functions used to handle DB objects ([[wikipedia:Data_Definition_Language|DDL]]) are defined.

Development:DDL functions

2008-11-24T23:30:58Z

Skodak: /* See also */

{{Moodle_2.0}}In this page you'll access to the available functions under Moodle to be able to handle DB structures (tables, fields, indexes...).

The objective is to have a well-defined group of functions able to handle all the DB structure (DDL statements) using one neutral description, being able to execute the correct SQL statements required by each RDBMS. All these functions are used '''[[Development:Installing and upgrading plugin database tables|exclusively by the installation and upgrade processes]]'''.

In this page you'll see a complete list of such functions, with some explanations, tricks and examples of their use. If you are interested, it's also highly recommendable to take a look to the [[Development:DML functions|DML functions page]] where everything about how to handle DB data (select, insert, update, delete i.e. DML statements) is defined.

Of course, feel free to clarify, complete and add more info to all this documentation. It will be welcome, absolutely!

==Main info==

'''Important note:''' All the functions showed in this page are for use in '''Moodle 2.0 upwards''', where we changed the [[Development:DB layer 2.0|DB layer]] to support some new features. If you need information for previous Moodle version, take a look to the [[Development:DDL functions - pre 2.0|DDL functions - pre 2.0]] page.

* All the function calls in this page are public methods of the '''database manager''', accessible from the $DB global object. So you'll need to "import" it within your '''upgrade.php''' main function with something like:

<code php>
function xmldb_xxxx_upgrade {

global $DB;

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

/// Your upgrade code goes here
}

</code>

* Once more, the use of these functions is '''restricted''' to the upgrade processes and it shouldn't be used ever out from there.
* All the $table, $field, $index parameters are, always, XMLDB objects. Don't forget to read carefully the complete documentation about [[Development:XMLDB creating new DDL functions|creating new DDL functions]] before playing with these functions. Everything is explained there, with one general example and some really useful tricks to improve the use of all the functions detailed below.
* If you want real examples of the usage of these functions it's 100% interesting to examine the '''upgrade.php''' scripts that are responsible for Moodle upgrading since the 1.7 release.
* Also, it's a '''very good idea''' (highly recommended!) to use the [[XMLDB editor|XMLDB Editor]] itself to generate automatically the desired PHP code. Just play with it!

==The functions==

===Handling tables===
<code php>
/// To detect if one table exists:
$dbman->table_exists($table)
/// To create one table:
$dbman->create_table($table, $continue=true, $feedback=true)
/// To drop one table:
$dbman->drop_table($table, $continue=true, $feedback=true)
/// To rename one table:
$dbman->rename_table($table, $newname, $continue=true, $feedback=true)
</code>

===Handling fields===
<code php>
/// To detect if one field exists:
$dbman->field_exists($table, $field)
/// To create one field:
$dbman->add_field($table, $field, $continue=true, $feedback=true)
/// To drop one field:
$dbman->drop_field($table, $field, $continue=true, $feedback=true)
/// To change the type of one field:
$dbman->change_field_type($table, $field, $continue=true, $feedback=true)
/// To change the precision of one field:
$dbman->change_field_precision($table, $field, $continue=true, $feedback=true)
/// To change the signed/unsigned status of one field:
$dbman->change_field_unsigned($table, $field, $continue=true, $feedback=true)
/// To make one field nullable or not:
$dbman->change_field_notnull($table, $field, $continue=true, $feedback=true)
/// To change the enum (check constraint) of one field:
$dbman->change_field_enum($table, $field, $continue=true, $feedback=true)
/// To change the default value of one field:
$dbman->change_field_default($table, $field, $continue=true, $feedback=true)
/// To rename one field:
$dbman->rename_field($table, $field, $newname, $continue=true, $feedback=true)
</code>

===Handling indexes===
<code php>
/// To detect if one index exists:
$dbman->index_exists($table, $index)
/// To return the name of one index in DB:
$dbman->find_index_name($table, $index)
/// To add one index:
$dbman->add_index($table, $index, $continue=true, $feedback=true)
/// To drop one index:
$dbman->drop_index($table, $index, $continue=true, $feedback=true)
</code>

==Some considerations==
# All the $table, $field, $index parameters are, always, XMLDB objects.
# All the $newtablename, $newfieldname parameters are, always, simple strings.
# All the ***_exists() functions return boolean true/false.
# Any problem in the execution of the functions will throw one Exception and the upgrade process will die.
# It's recommendable to use the [[XMLDB editor|XMLDB Editor]] to generate the PHP code automatically (did I say this before? :-P )

==See also==

* [[Development:XMLDB Documentation|XMLDB Documentation]]: Main page of the whole XMLDB documentation, where all the process is defined and all the related information resides.
* [[XMLDB Defining one XML structure]]: Where you will know a bit more about the underlying XML structure used to define the DB objects, that is used continuously by the functions described in this page.
* [[Development:Installing and upgrading plugin database tables|Installing and upgrading plugin DB tables]]
* [[Development:DML functions|DML functions]]: Where all the functions used to handle DB data ([[wikipedia:Data_Manipulation_Language|DML]]) are defined.
* [[Development:DDL functions - pre 2.0|DDL functions - pre 2.0]]: '''(deprecated!)''' For information valid before Moodle 2.0.
* [[Development:DB layer 2.0 migration docs|DB layer 2.0 migration docs]]: Information about how to modify your code to work with the new Moodle 2.0 DB layer.
* [[Development:DTL functions|DTL functions]]: Exporting, importing and moving of data stored in sql databases

[[Category:DB]]
[[Category:XMLDB]]

Development:DML functions

2008-11-24T23:30:08Z

Skodak: DTL info

{{Moodle_2.0}}In this page you'll access to the available functions under Moodle to be able to access to DB data. You should use '''exclusively''' these functions in order to retrieve or modify DB contents because these functions provide an high level of abstraction and guarantee that your DB manipulation will work against different RDBMS.

Where possible, tricks and examples will be documented here in order to make developers' lives a bit easier. Of course, feel free to clarify, complete and add more info to all this documentation. It will be welcome, absolutely!

== Main info ==

'''Important note:''' All the functions showed in this page are for use in '''Moodle 2.0 upwards''', where we changed the [[Development:DB layer 2.0|DB layer]] to support some new features. If you need information for previous Moodle version, take a look to the [[Development:DML functions - pre 2.0|DML functions - pre 2.0]] page.

* All the function calls in this page are public methods of the $DB global object. So you'll need to "import" it within your functions (not needed in global scripts) with one simple:
<code php>global $DB;</code>
* All the $table parameters in the functions are mean to be the table name '''without'' prefixes.
<code php>$user = $DB->get_record('user', array('id'=>'1');</code>
* When using the xxx_sql() functions, table names must be enclosed between curly braces.
<code php>$user = $DB->get_record_sql('SELECT * FROM {user} WHERE id = ?', array(1));</code>
* All the $conditions parameters in the functions are arrays of fieldname=>fieldvalue elements.
<code php>$user = $DB->get_record('user', array('firstname'=>'Martin', 'lastname'=>'Dougiamas');</code>
* All the $params parameters in the functions are arrays of values used to fill placeholders in SQL statements. Both question mark and named placeholders can be used.
<code php>
/// Question mark placeholders:
$DB->get_record_sql('SELECT * FROM {user} WHERE firstname = ? AND lastname = ?',
array('Martin', 'Dougiamas'));

/// Named placeholders:
$DB->get_record_sql('SELECT * FROM {user} WHERE firstname = :firstname AND lastname = :lastname',
array('firstname'=>'Martin', 'lastname'=>'Dougiamas'));
</code>

== The functions ==

===Seeing how many records match a given criteria===
<code php>
o $DB->count_records($table, array $conditions=null)
/// Count the records in a table where all the given conditions met.
o $DB->count_records_select($table, $select, array $params=null, $countitem="COUNT('x')")
/// Count the records in a table which match a particular WHERE clause.
o $DB->count_records_sql($sql, array $params=null)
/// Get the result of a SQL SELECT COUNT(...) query.
</code>

===Seeing if one record exists===
<code php>
o $DB->record_exists($table, array $conditions=null)
/// Test whether a record exists in a table where all the given conditions met.
o $DB->record_exists_select($table, $select, array $params=null)
/// Test whether any records exists in a table which match a particular WHERE clause.
o $DB->record_exists_sql($sql, array $params=null)
/// Test whether a SQL SELECT statement returns any records.
</code>

===Retrieving a single record===
<code php>
o $DB->get_record($table, array $conditions, $fields='*', $ignoremultiple=false)
/// Get a single database record as an object where all the given conditions met.
o $DB->get_record_select($table, $select, array $params=null, $fields='*', $ignoremultiple=false)
/// Get a single database record as an object which match a particular WHERE clause.
o $DB->get_record_select($table, $select, array $params=null, $fields='*', $ignoremultiple=false)
/// Get a single database record as an object using a SQL statement.
</code>

===Getting an array of records===
<code php>
o $DB->get_records($table, array $conditions=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0)
/// Get a number of records as an array of objects where all the given conditions met.
o $DB->get_records_select($table, $select, array $params=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0)
/// Get a number of records as an array of objects which match a particular WHERE clause.
o $DB->get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0)
/// Get a number of records as an array of objects using a SQL statement.

o $DB->get_records_menu($table, array $conditions=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0)
/// Get the first two columns from a number of records as an associative array where all the given conditions met.
o $DB->get_records_select_menu($table, $select, array $params=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0)
/// Get the first two columns from a number of records as an associative array which match a particular WHERE clause.
o $DB->get_records_sql_menu($sql, array $params=null, $limitfrom=0, $limitnum=0)
/// Get the first two columns from a number of records as an associative array using a SQL statement.

o $DB->get_records_list($table, $field='', $values='', $sort='', $fields='*', $limitfrom='', $limitnum='')
/// Get a number of records as an array of objects where one field match one list of values.
</code>

===Getting a particular field value from one record===
<code php>
o $DB->get_field($table, $return, array $conditions)
/// Get a single field value from a table record where all the given conditions met.
o $DB->get_field_select($table, $return, $select, array $params=null)
/// Get a single field value from a table record which match a particular WHERE clause.
o $DB->get_field_sql($sql, array $params=null)
/// Get a single field value (first field) using a SQL statement.
</code>

===Getting a particular field value from various records===

<code php>
o $DB->get_fieldset_select($table, $return, $select, array $params=null)
/// Selects records and return values of chosen field as an array which match a particular WHERE clause.
o $DB->get_fieldset_sql($sql, array $params=null)
/// Selects records and return values (first field) as an array using a SQL statement.
</code>

===Setting a particular field in the database===
<code php>
o $DB->set_field($table, $newfield, $newvalue, array $conditions=null)
/// Set a single field in every table record where all the given conditions met.
o $DB->set_field_select($table, $newfield, $newvalue, $select, array $params=null)
/// Set a single field in every table record which match a particular WHERE clause.
</code>

===Deleting Records===
<code php>
o $DB->delete_records($table, array $conditions=null)
/// Delete the records from a table where all the given conditions met.
o $DB->delete_records_select($table, $select, array $params=null)
/// Delete one or more records from a table which match a particular WHERE clause.
</code>

===Inserting Records===
<code php>
o $DB->insert_record($table, $dataobject, $returnid=true, $bulk=false)
/// Insert a record into a table and return the "id" field if required.
</code>

===Updating Records===
<code php>
o $DB->update_record($table, $dataobject, $bulk=false)
/// Update a record in a table.
</code>

===Using Recordsets===

While the number of records to be retrieved from DB is high, the '''get_records_xxx()''' functions above are far from optimal, because they use to load all the records in memory at the same time. Under those circumstances, it's highly recommended to use this '''get_recordset_xxx()''' functions instead, using one nice mechanism to iterate over all the target records saving a lot of memory.

Only one thing is '''absolutely important'''. Don't forget to close the record sets after using them! (that will freed a lot of resources in the RDBMS).

Here it's the general way to iterate over records using the '''get_recordset_xxx()''' functions:

<code php>
if ($rs = $DB->get_recordset(....) {
foreach ($rs as $record) {
/// Do whatever you want with this record
}
$rs->close(); /// Don't forget to close the recordset!
}
</code>

And this is the list of available functions (100% paired with the get_records_xxx() above):
<code php>
o $DB->get_recordset($table, array $conditions=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0)
/// Get a number of records as a moodle_recordset where all the given conditions met.
o $DB->get_recordset_select($table, $select, array $params=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0)
/// Get a number of records as a moodle_recordset which match a particular WHERE clause.
o $DB->get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0);
/// Get a number of records as a moodle_recordset using a SQL statement.

o $DB->get_recordset_list($table, $field='', $values='', $sort='', $fields='*', $limitfrom='', $limitnum='')
/// Get a number of records as a moodle_recordset where one field match one list of values.
</code>

===Helper Functions===

In order have real cross-db compatibility, there are some helper functions used to build SQL fragments based on the DB Moodle is running. Using them we'll avoid conditional queries here and there, having those "incompatibilities" fixed once and for ever.
<code php>
o $DB->sql_bitand($int1, $int2)
/// Returns the SQL text to be used in order to perform one bitwise AND
/// operation between 2 integers.
o $DB->sql_bitnot($int1)
/// Returns the SQL text to be used in order to perform one bitwise NOT
/// operation with 1 integer.
o $DB->sql_bitor($int1, $int2)
/// Returns the SQL text to be used in order to perform one bitwise OR
/// operation between 2 integers.
o $DB->sql_bitxor($int1, $int2)
/// Returns the SQL text to be used in order to perform one bitwise XOR
/// operation between 2 integers.

o $DB->sql_null_from_clause()
/// Returns the FROM clause required by some DBs in all SELECT statements.

o $DB->sql_ceil($fieldname)
/// Returns the correct CEIL expression applied to fieldname.
o $DB->sql_substr()
/// Returns the proper substr() function for each DB.
o $DB->sql_ilike()
/// Returns the proper SQL to do LIKE in a case-insensitive way.

o $DB->sql_cast_char2int($fieldname, $text=false)
/// Returns the SQL to be used in order to CAST one CHAR column to INTEGER.
o $DB->sql_cast_char2real($fieldname, $text=false)
/// Returns the SQL to be used in order to CAST one CHAR column to REAL number.

o $DB->sql_compare_text($fieldname, $numchars=32)
/// Returns the SQL text to be used to compare one TEXT (clob) column.
/// with one VARCHAR column.
o $DB->sql_order_by_text($fieldname, $numchars=32)
/// Returns the SQL text to be used to order by one TEXT (clob) column.

o $DB->sql_concat()
/// Returns the proper SQL to do CONCAT between the elements passed.
o $DB->sql_concat_join($separator="' '", $elements=array())
/// Returns the proper SQL to do CONCAT between the elements passed using one separator.
o $DB->sql_fullname($first='firstname', $last='lastname')
/// Returns the proper SQL to concatenate $firstname and $lastname.

o $DB->sql_isempty($tablename, $fieldname, $nullablefield, $textfield)
/// Returns the proper SQL to know if one field is empty.
o $DB->sql_isnotempty($tablename, $fieldname, $nullablefield, $textfield)
/// Returns the proper SQL to know if one field is not empty.
o $DB->sql_empty()
/// Returns the empty string char used by every supported DB.
</code>

==See also==

* [[Development:DML exceptions|DML exceptions]]: New DML code is throwing exceptions instead of returning false if anything goes wrong
* [[Development:DML drivers|DML drivers]]: Database drivers for new DML layer
* [[Development:DML functions - pre 2.0|DML functions - pre 2.0]]: '''(deprecated!)''' For information valid before Moodle 2.0.
* [[Development:DDL functions|DDL functions]]: Where all the functions used to handle DB objects ([[wikipedia:Data_Definition_Language|DDL]]) are defined.
* [[Development:DB layer 2.0 examples|DB layer 2.0 examples]]: To see some code examples using various DML functions.
* [[Development:DB layer 2.0 migration docs|DB layer 2.0 migration docs]]: Information about how to modify your code to work with the new Moodle 2.0 DB layer.
* [[Development:DTL functions|DTL functions]]: Exporting, importing and moving of data stored in sql databases

[[Category:DB]]
[[Category:XMLDB]]

Developer meeting November 2008

2008-11-24T23:25:34Z

Skodak: /* Moodle 2.0 */

[[Developer meetings]] > November 2008 meeting

*Date: 23:00 UTC on Monday, 24 November 2008 - see the [http://moodle.org/calendar/view.php?view=day&course=5&cal_d=25&cal_m=11&cal_y=2008#event_4805 Moodle Developer Meeting calendar entry] for conversion into your time zone
*Location: [http://elluminate.remote-learner.net/join_meeting.html?meetingId=1176914355734 Elluminate Moodle Developers Meeting Room] - login using your actual name as login name and password ''moodle''

==Moodle 2.0==

* Overview and timeline - Martin D
* [[Development:Roles_administration_improvements_for_Moodle_2.0|New roles interfaces demo]], as an example of good JavaScript practices - Tim
* [[Development:Enrolment_plugins_2.0|Enrolment plugins proposal]]? - Tim
* Database related changes: [[Development:DML functions|DML]], [[Development:DDL functions|DDL]], DTL and new [[Development:DML drivers|native database drivers]] - skodak
* Privacy project MDL-17205 - skodak
* [[Development:Web_services|New web services system]] - Jerome
* Repository Plugins & new chat module - Dongsheng
* [https://docs.moodle.org/en/Feedback_link_for_all_elements Feedback link for all elements] If there's time, and I get up again before midnight - Jeff Forssell

==Other news==

*Google Highly Open Participation Contest (GHOP) (Helen)
*GSOC Mentor Summit - a few words about the summit (DanP & maybe Anthony/David Horat if they have anything to add)
*[[Events Subscriptions / Notifications sub-system]] - concept stage (Mike C.)

*Anything else? Please add yours.

==See also==
*[[Roadmap]]

[[Category:Developer]]

Developer meeting November 2008

2008-11-24T23:24:29Z

Skodak: /* Moodle 2.0 */

[[Developer meetings]] > November 2008 meeting

*Date: 23:00 UTC on Monday, 24 November 2008 - see the [http://moodle.org/calendar/view.php?view=day&course=5&cal_d=25&cal_m=11&cal_y=2008#event_4805 Moodle Developer Meeting calendar entry] for conversion into your time zone
*Location: [http://elluminate.remote-learner.net/join_meeting.html?meetingId=1176914355734 Elluminate Moodle Developers Meeting Room] - login using your actual name as login name and password ''moodle''

==Moodle 2.0==

* Overview and timeline - Martin D
* [[Development:Roles_administration_improvements_for_Moodle_2.0|New roles interfaces demo]], as an example of good JavaScript practices - Tim
* [[Development:Enrolment_plugins_2.0|Enrolment plugins proposal]]? - Tim
* Database related changes: [[Development:DML functions|DML]], [[Development:DDL functions|DDL]], DTL and new [[Development:DML drivers|native database drivers]] - skodak
* Privacy project - skodak
* [[Development:Web_services|New web services system]] - Jerome
* Repository Plugins & new chat module - Dongsheng
* [https://docs.moodle.org/en/Feedback_link_for_all_elements Feedback link for all elements] If there's time, and I get up again before midnight - Jeff Forssell

==Other news==

*Google Highly Open Participation Contest (GHOP) (Helen)
*GSOC Mentor Summit - a few words about the summit (DanP & maybe Anthony/David Horat if they have anything to add)
*Events Subscriptions / Notifications sub-system - concept stage (Mike C.)

*Anything else? Please add yours.

==See also==
*[[Roadmap]]

[[Category:Developer]]

Development:DML functions

2008-11-24T23:20:05Z

Skodak: /* See also */

Development:DML drivers

2008-11-24T23:18:13Z

Skodak:

{{Moodle_2.0}}Previous versions were using adodb abstraction partially encapsulated by old DML api. The database drivers are now fully separated from the rest of code and it is even possible to create new native drivers that do not rely on adodb abstraction anymore.

At present there are two sample native drivers - mysqli and pgsql. The benefits are:
* more optimised and probably faster
* consume less memory
* better possibility to improve logging, debugging, profiling, etc.
* less code, easier to fix and maintain
* and more

Please note that native drivers are now used by default in HEAD because we need to get as much testing as possible ;-)

==TODO==
* add more info here
* add separate docs pages for each driver - describe all options there

==See also==
* [[Development:DML functions|DML functions]]: Where all the functions used to handle DB data ([[wikipedia:Data_Manipulation_Language|DML]]) are defined.
* [[Development:DML exceptions|DML exceptions]]: New DML code is throwing exceptions instead of returning false if anything goes wrong

Development:DML drivers

2008-11-24T23:17:28Z

Skodak:

{{Moodle_2.0}}Previous versions were using adodb abstraction partially encapsulated by old DML api. The database drivers are now fully separated from the rest of code and it is even possible to create new native drivers that do not rely on adodb abstraction anymore.

At present there are two sample native drivers - mysqli and pgsql. The benefits are:
* more optimised and probably faster
* consume less memory
* better possibility to improve logging, debugging, profiling
* smaller code & easier to fix and maintain
* and more

Please note that native drivers are now used by default in HEAD because we need to get as much testing as possible ;-)

==TODO==
* add more info here
* add separate docs pages for each driver - describe all options there

==See also==
* [[Development:DML functions|DML functions]]: Where all the functions used to handle DB data ([[wikipedia:Data_Manipulation_Language|DML]]) are defined.
* [[Development:DML exceptions|DML exceptions]]: New DML code is throwing exceptions instead of returning false if anything goes wrong

Development:DML drivers

2008-11-24T23:17:01Z

Skodak:

{{Moodle_2.0}}Previous versions were using adodb abstraction partially encapsulated by old DML api. The database drivers are now fully separated from the rest of code and it is even possible to create new native drivers that do not rely on adodb abstraction anymore.

At present there are two sample native drivers - mysqli and pgsql. The benefits are:
* more optimised and probably faster
* consume less memory
* better possibility to improve logging, debugging, profiling
* smaller code & easier to fix and maintain
* and more

Please note that native drivers are now used by default in HEAD because we need to get as much testing as possible.

==TODO==
* add more info here
* add separate docs pages for each driver - describe all options there

==See also==
* [[Development:DML functions|DML functions]]: Where all the functions used to handle DB data ([[wikipedia:Data_Manipulation_Language|DML]]) are defined.
* [[Development:DML exceptions|DML exceptions]]: New DML code is throwing exceptions instead of returning false if anything goes wrong

Development:DML drivers

2008-11-24T23:16:42Z

Skodak:

{{Moodle_2.0}}Previous versions were using adodb abstraction partially encapsulated by old DML api. The database drivers are now fully separated from the rest of code and it is even possible to create new native drivers that do not rely on adodb abstraction anymore.

At present there are two sample native drivers - mysqli and pgsql. The benefits are:
* more optimised and probably faster
* consume less memory
* better possibility to improve logging, debugging, profiling
* smaller code & easier to fix and maintain
* and more

Please note that native drivers are now used by default in HEAD because we need to get as much testing as possible.

==TODO==
* add more info here
* add separate docs pages for each driver - describe all options there

==See also==

* [[Development:DML functions|DML functions]]: Where all the functions used to handle DB data ([[wikipedia:Data_Manipulation_Language|DML]]) are defined.
* [[Development:DML exceptions|DML exceptions]]: New DML code is throwing exceptions instead of returning false if anything goes wrong

Development:DML drivers

2008-11-24T23:13:41Z

Skodak:

{{Moodle_2.0}}Previous versions were using adodb abstraction partially encapsulated by old DML api. The database drivers are now fully separated from the rest of code and it is even possible to create new native drivers that do not rely on adodb abstraction anymore.

At present there are two sample native drivers - mysql and pgsql. The benefits are:
* more optimised and probably faster
* consume less memory
* better possibility to improve logging, debugging, profiling
* smaller code & easier to fix and maintain
* and more

TODO: add more info here

==See also==

* [[Development:DML functions|DML functions]]: Where all the functions used to handle DB data ([[wikipedia:Data_Manipulation_Language|DML]]) are defined.
* [[Development:DML exceptions|DML exceptions]]: New DML code is throwing exceptions instead of returning false if anything goes wrong

Development:DML drivers

2008-11-24T23:06:22Z

Skodak:

{{Moodle_2.0}}Previous versions were using adodb abstraction partially encaplulated by old DML api. The database drivers are now fully separated from the rest of code and it is even possible to create new native drivers that do not rely on adodb abstraction anymore.

TODO: add more info here and explanation.

==See also==

* [[Development:DML functions|DML functions]]: Where all the functions used to handle DB data ([[wikipedia:Data_Manipulation_Language|DML]]) are defined.
* [[Development:DML exceptions|DML exceptions]]: New DML code is throwing exceptions instead of returning false if anything goes wrong

Development:DML drivers

2008-11-24T23:04:38Z

Skodak: New page: {{Moodle_2.0}}Previous versions were using adodb abstraction partially encaplulated by old DML api. The database drivers are now fully separated from the rest of code and it is even possib...

{{Moodle_2.0}}Previous versions were using adodb abstraction partially encaplulated by old DML api. The database drivers are now fully separated from the rest of code and it is even possible to create new native drivers that do not rely on adodb abstraction anymore.

TODO: add more info here, explanation and same examples.

==See also==

* [[Development:DML functions|DML functions]]: Where all the functions used to handle DB data ([[wikipedia:Data_Manipulation_Language|DML]]) are defined.
* [[Development:DML exceptions|DML exceptions]]: New DML code is throwing exceptions instead of returning false if anything goes wrong