MoodleDocs - User contributions [en]

Local plugins

2011-04-07T20:51:06Z

Tdjones: /* Other /local/ customisation files */

{{Infobox Project
|name = Local customisations
|state = Implemented
|tracker = MDL-17376, MDL-16438
|discussion = http://moodle.org/mod/forum/discuss.php?d=126017 http://moodle.org/mod/forum/discuss.php?d=86903
|assignee = [[User:Petr Škoda (škoďák)|Petr Škoda (škoďák)]], some parts were originally proposed and implemented in 1.9 by Penny Leach
}}
{{Moodle 2.0}}

The recommended way to add new functionality to Moodle is to create a new standard plugin (module, block, auth, enrol, etc.).The /local/ plugins are mostly suitable for things that do not fit standard plugins.

== Custom /local/ plugins ==

Local plugins are used in cases when no standard plugin fits, examples are:
* event consumers communicating with external systems
* custom definitions of web services and external functions
* applications that extend moodle at the system level (hub server, amos server, etc.)
* new database tables used in core hacks (discouraged)
* new capability definitions used in core hacks
* custom admin settings
* extending the navigation block with custom menus [http://moodle.org/mod/forum/discuss.php?d=170325&parent=753095]

=== Standard plugin features: ===
* /local/xxx/version.php - version of script (must be incremented after changes)
* /local/xxx/db/install.xml - executed during install (new version.php found)
* /local/xxx/db/install.php - executed right after install.xml
* /local/xxx/db/uninstall.php - executed during uninstallation
* /local/xxx/db/upgrade.php - executed after version.php change
* /local/xxx/db/access.php - definition of capabilities
* /local/xxx/db/events.php - event handlers and subscripts
* /local/xxx/db/messages.php - messaging registration
* /local/xxx/db/external.php - web services and external functions descriptions
* /local/xxx/lang/en/local_pluginname.php - language file

The ''xxx'' is used instead of your local plugin name, plugins of the same type are installed/upgraded in alphabetical order.

=== List of differences from normal plugins: ===
* always executed last during install/upgrade - guaranteed by order of plugins in <code>get_plugin_types()</code>
* are expected to use event handlers - events are intended for communication core-->plugins only, local plugins are the best candidates for event handlers
* can add admin settings to any settings page - loaded last when constructing admin tree
* do not need to have any UI - other plugins are usually visible somewhere
* some extra hooks (not implemented yet)

== /local/xxx/db/messages.php ==
Example File Structure:
<code php>
<?php

/**
* Defines message providers (types of messages being sent)
*
* @package mod-forum
* @copyright 1999 onwards Martin Dougiamas http://moodle.com
* @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
*/

$messageproviders = array (

/// Ordinary single forum posts
'posts' => array (
)

);
</code>

== Other /local/ customisation files==

===Customised site defaults===

Different default site settings can be stored in file /local/defaults.php.
These new defaults are used during installation, upgrade and later are
displayed as default values in admin settings. This means that the content
of the defaults files is usually updated BEFORE installation or upgrade.

These customised defaults are useful especially when using CLI tools
for installation and upgrade.

Sample /local/defaults.php file content:
<code php>
<?php
$defaults['moodle']['forcelogin'] = 1; // new default for $CFG->forcelogin
$defaults['scorm']['maxgrade'] = 20; // default for get_config('scorm', 'maxgrade')
$defaults['moodlecourse']['numsections'] = 11;
$defaults['moodle']['hiddenuserfields'] = array('city', 'country');
</code>
First bracket contains string from column plugin of config_plugins table.
Second bracket is the name of setting. In the admin settings UI the plugin and
name of setting is separated by "|".

The values usually correspond to the raw string in config table, with the exception
of comma separated lists that are usually entered as real arrays.

Please note that not all settings are converted to admin_tree,
they are mostly intended to be set directly in config.php.

=== 2.0 pre-upgrade script===

You can use /local/upgrade_pre20.php script for any code that needs to
be executed before the main upgrade to 2.0. Most probably this will
be used for undoing of old hacks that would otherwise break normal
2.0 upgrade.

This file is just included directly, there does not need to be any
function inside. If the execution stops the script is executed again
during the next upgrade. The first execution of lib/db/upgrade.php
increments the version number and the pre upgrade script is not
executed any more.

== Customisations outside of /local/ directory==

=== Forced settings===

Sometimes it is useful to force some settings and prevent any changes of these settings via the standard admin UI. It is possible to hardcode these settings in config.php.

In case of course settings it is very simply, the values are assigned directly to $CFG properties. In case of plugins the values are specified in a multidimensional array in $CFG->force_plugin_settings.

Sample code in config.php
<code php>
$CFG->allowobjectembed = 0;
$CFG->forced_plugin_settings = array('page'=>array('displayoptions'=>5, 'requiremodintro'=>1), 'folder'=>'requiremodintro'=>1);
</code>

=== Local language customisations===

Moodle supports other type of local customisation of standard language
packs. If you want to create your own language pack based on another
language create new dataroot directory with "_local" suffix, for example
following file with content changes string "Login" to "Sign in":
moodledata/lang/en_local
<code php>
<?php
$string['login'] = 'Sign in';
</code>
See also https://docs.moodle.org/en/Language_editing

=== Custom script injection===

Very old customisation option that allows you to modify scripts by injecting
code right after the require 'config.php' call.

This setting is enabled by manually setting $CFG->customscripts variable
in config.php script. The value is expected to be full path to directory
with the same structure as dirroot. Please note this hack only affects
files that actually include the config.php!

; Examples:
* disable one specific moodle page without code modification
* alter page parameters on the fly

=== Direct code modifications===
This is usually the last resort, if possible do not do it. And if you still do it use some version control system (preferably git).

=== Direct database modifications===
Very strongly discouraged! Sometimes field lengths may be modified without side effects. Adding or removing of db fields will most probably cause major problems during future upgrades. New database tables should be added only from plugins.

== Local customisations in previous versions ==
Previous versions include only partial support for customisations in /local/ directory.

=== List of local customisations in 1.9.x: ===
* /local/cron.php - custom cron jobs
* /local/settings.php - custom admin settings
* /local/db/upgrade.php - general modifications
* /local/lang/* - custom strings
* /local/lib.php - local_delete_course()

=== Migration from old 1.9.x /local/: ===
* <code>local/*</code> needs to be copied to new directory
* <code>local/xxxx/db/install.php</code> is intended for first installation, originally everything was in upgrade.php
* events are used instead of hooks
* upgrade code needs to migrate old settings, events, etc. directly in core db tables - such as change component strings and capability names from db/install.php or manually before/after upgrade

==See also==

* [http://moodle.org/mod/forum/discuss.php?d=170325&parent=753095 Extending navigation block]
* Using Moodle [http://moodle.org/mod/forum/discuss.php?d=86903 Local Customisations] forum discussion
* http://cvs.moodle.org/moodle/local/readme.txt?view=markup
* [[Local customisation (Moodle 1.9)]]

[[Category:Local customisation]]

Data manipulation API

2011-03-29T16:40:12Z

Tdjones: /* Inserting Records */

{{Moodle_2.0}}This page describes the functions available to access data in the Moodle database. You should '''exclusively''' use these functions in order to retrieve or modify database content because these functions provide a high level of abstraction and guarantee that your database manipulation will work against different RDBMSes.

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 information to this documentation. It will be welcome, absolutely!

== Main info ==

'''Important note:''' All the functions shown on this page are for use in '''Moodle 2.0 upwards''', where we changed the [[DB layer 2.0|DB layer]] to support some new features. If you need information for previous Moodle version, take a look to the [[DML functions - pre 2.0|DML functions - pre 2.0]] page. For a detailed reference of changes, see the [[DB layer 2.0 migration docs|migration docs]].

* All the function calls on 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 meant 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 the question mark and named placeholders can be used. Note that named params '''must be unique''' even if the value passed is the same.
<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>

===Getting 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_sql($sql, array $params=null)
/// 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, array $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>
====Example(s)====
<code php>
$record = new stdClass();
$record->name = 'overview';
$record->displayorder = '10000';
$DB->insert_record('quiz_report', $record);
</code>

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

===Using Recordsets===

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

Only one thing is '''absolutely important''': Don't forget to close the recordsets after using them! (This will free up a lot of resources in the RDBMS).

Here is 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 matches one list of values.
</code>

===Delegated transactions===

* Please note some databases do not support transactions (such as the MyISAM MySQL database engine), however all server administrators are strongly encouraged to migrate to databases that support transactions (such as the InnoDB MySQL database engine).
* Previous versions supported only one level of transaction. Since Moodle 2.0, the DML layer emulates delegated transactions that allow nesting of transactions.
* Transactions should not be used much in Moodle core; they are intended for various plugins such as web services, enrol and auth plugins.
* Some subsystems (such as messaging) do not support transactions because is it is not possible to rollback in external systems.

A transaction is started by:
<code php>
$transaction = $DB->start_delegated_transaction();
</code>

and finished by:
<code php>
$transaction->allow_commit();
</code>

Usually a transaction is rolled back when an exception is thrown. <code>$transaction->rollback($ex);</code> must be used very carefully because it might break compatibility with databases that do not support transactions. Transactions cannot be used as part of expected code flow; they can be used only as an emergency protection of data consistency.

See more details in [[DB layer 2.0 delegated transactions]] or MDL-20625.

===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 and have 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_ilike()
/// Returns the proper SQL to do LIKE in a case-insensitive way.
o $DB->sql_length($fieldname)
/// Returns the SQL text to be used to calculate the length in characters of one expression.
o $DB->sql_modulo($int1, $int2)
/// Returns the SQL text to be used in order to calculate module - remainder after division
o $DB->sql_position($needle, $haystack)
/// Returns the SQL for returning searching one string for the location of another.
/// Note: If using placeholders BOTH in $needle and $haystack, they MUST be named placeholders.
o $DB->sql_substr($expr, $start, $length=false)
/// Returns the proper substr() SQL text used to extract substrings from DB.
/// Note: This fuction has changed in Moodle 2.0 and now at least 2 params are mandatory.
/// Note: Now it returns the whole SQL text to be used instead of only the function name.

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

* [[DML exceptions|DML exceptions]]: New DML code is throwing exceptions instead of returning false if anything goes wrong
* [[DML drivers|DML drivers]]: Database drivers for new DML layer
* [[DML functions - pre 2.0|DML functions - pre 2.0]]: '''(deprecated!)''' For information valid before Moodle 2.0.
* [[DDL functions|DDL functions]]: Where all the functions used to handle DB objects ([[wikipedia:Data_Definition_Language|DDL]]) are defined.
* [[DB layer 2.0 examples|DB layer 2.0 examples]]: To see some code examples using various DML functions.
* [[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.
* [[DTL functions|DTL functions]]: Exporting, importing and moving of data stored in SQL databases

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