Difference between revisions of "DB layer 2.0 examples"

Jump to: navigation, search
m (Mixing query params and IN params)
(According to https://docs.moodle.org/dev/Coding_style#Variables using underscores in variables names is "BAD", so we shouldn't be encouraging it in our docs. As such I've fixed some variable names on this page.)
 
(6 intermediate revisions by 2 users not shown)
Line 1: Line 1:
{{Work in progress}}{{Template:Development:dmllib 2.0}}{{Moodle_2.0}}__NOTOC__
+
{{Template:Development:dmllib 2.0}}{{Moodle_2.0}}__NOTOC__
  
 
=== Dropping one enum from one field ===
 
=== Dropping one enum from one field ===
  
In Moodle 2.0, we have '''discontinued support for ENUM''' (check constraint) in DB columns. See MDL-18577 about that. So, any plugin using enums in Moodle 1.9 will need to drop them as part of the upgrade to Moodle 2.0. To achieve that, as commented in the [[DB layer 2.0 migration docs|migration docs]], the '''drop_enum_from_field()''' method will be used. Here it's one '''real''' example used to drop the enum defined in the forum->type column as part of the upgrade from Moodle 1.9 to 2.0 (just adjust it for your own needs):
+
In Moodle 2.0, we have '''discontinued support for ENUM''' (check constraint) in DB columns. See MDL-18577 about that. So, any plugin using enums in Moodle 1.9 will need to drop them as part of the upgrade to Moodle 2.0. To achieve that, as commented in the [[DB layer 2.0 migration docs#XMLDB/DDL changes|migration docs]], the '''drop_enum_from_field()''' method will be used. Here it's one '''real''' example used to drop the enum defined in the forum->type column as part of the upgrade from Moodle 1.9 to 2.0 (just adjust it for your own needs):
 
<code php>
 
<code php>
 
/// Dropping all enums/check contraints from core. MDL-18577
 
/// Dropping all enums/check contraints from core. MDL-18577
Line 30: Line 30:
 
<code php>
 
<code php>
 
/// Here it's the query param (we are looking for 'general' forums in this example)
 
/// Here it's the query param (we are looking for 'general' forums in this example)
     $query_params = array('general');
+
     $queryparams = array('general');
  
 
/// Here we calculate the IN clause (the list of courses we are going to search)
 
/// Here we calculate the IN clause (the list of courses we are going to search)
 
/// it returns 2 values: the SQL IN clause needed and the params array to be applied
 
/// it returns 2 values: the SQL IN clause needed and the params array to be applied
     list($in_sql, $in_params) = $DB->get_in_or_equal(array(1, 2, 3, 4, 5, 6));
+
     list($insql, $inparams) = $DB->get_in_or_equal(array(1, 2, 3, 4, 5, 6));
  
 
/// Here it's the query we are going to execute, with the IN clause injected in place
 
/// Here it's the query we are going to execute, with the IN clause injected in place
Line 41: Line 41:
 
               JOIN {forum_discussions} d ON d.forum = f.id
 
               JOIN {forum_discussions} d ON d.forum = f.id
 
             WHERE f.type = ?
 
             WHERE f.type = ?
              AND f.course $in_sql
+
                  AND f.course $insql
 
           GROUP BY f.course";
 
           GROUP BY f.course";
  
 
/// Here we merge all params (query ones and IN clause ones,
 
/// Here we merge all params (query ones and IN clause ones,
 
/// respecting the order they are used in the query!)
 
/// respecting the order they are used in the query!)
     $params = array_merge($query_params, $in_params);
+
     $params = array_merge($queryparams, $inparams);
  
 
/// Execute the query, returning the expected count per course
 
/// Execute the query, returning the expected count per course
Line 52: Line 52:
 
</code>
 
</code>
  
Note that '''is really important to respect the order of the params''' when merging them in case you are using question mark placeholders in your query. Alternatively, you can use named params if you want to skip that ordering potential issue, specially if your query is very complex or highly dynamic. Here it's the same example using named params:
+
Note that '''is really important to respect the order of the params''' when merging them in case you are using question mark placeholders in your query. Alternatively, you can use named params if you want to skip that ordering potential issue, specially if your query is very complex or highly dynamic. Here it's the same example using named params (see the 2nd param used in the get_in_or_equal() method call), that's all :
  
 
  <code php>
 
  <code php>
 
/// Here it's the query param (we are looking for 'general' forums in this example)
 
/// Here it's the query param (we are looking for 'general' forums in this example)
     $query_params = array('forum_type' => 'general');
+
     $queryparams = array('forum_type' => 'general');
  
 
/// Here we calculate the IN clause (the list of courses we are going to search)
 
/// Here we calculate the IN clause (the list of courses we are going to search)
 
/// it returns 2 values: the SQL IN clause needed and the params array to be applied
 
/// it returns 2 values: the SQL IN clause needed and the params array to be applied
     list($in_sql, $in_params) = $DB->get_in_or_equal(array(1, 2, 3, 4, 5, 6), SQL_PARAMS_NAMED);
+
     list($insql, $inparams) = $DB->get_in_or_equal(array(1, 2, 3, 4, 5, 6), SQL_PARAMS_NAMED);
  
 
/// Here it's the query we are going to execute, with the IN clause injected in place
 
/// Here it's the query we are going to execute, with the IN clause injected in place
Line 67: Line 67:
 
               JOIN {forum_discussions} d ON d.forum = f.id
 
               JOIN {forum_discussions} d ON d.forum = f.id
 
             WHERE f.type = :forum_type
 
             WHERE f.type = :forum_type
              AND f.course $in_sql
+
                  AND f.course $insql
 
           GROUP BY f.course";
 
           GROUP BY f.course";
  
 
/// Here we merge all params (query ones and IN clause ones,
 
/// Here we merge all params (query ones and IN clause ones,
 
/// as we are using named params, order isn't important)
 
/// as we are using named params, order isn't important)
     $params = array_merge($in_params, $query_params);
+
     $params = array_merge($inparams, $queryparams);
  
 
/// Execute the query, returning the expected count per course
 
/// Execute the query, returning the expected count per course
 
     $records = $DB->get_records_sql($sql, $params);
 
     $records = $DB->get_records_sql($sql, $params);
 
</code>
 
</code>
 +
 +
== See also ==
 +
 +
* [[XMLDB Documentation|XMLDB Documentation]]: where both xmldb and ddl stuff is explained.
 +
* [[Database|Database coding guidelines]]: where some basic rules to follow are defined.
 +
* [[DDL functions|DDL functions]] - Documentation for all the Data Definition Language (DDL) functions available inside Moodle.
 +
* [[DML functions|DML functions]] - Documentation for all the Data Manipulation Language (DML) functions available inside Moodle

Latest revision as of 03:06, 21 February 2012

Moodle 2.0


Dropping one enum from one field

In Moodle 2.0, we have discontinued support for ENUM (check constraint) in DB columns. See MDL-18577 about that. So, any plugin using enums in Moodle 1.9 will need to drop them as part of the upgrade to Moodle 2.0. To achieve that, as commented in the migration docs, the drop_enum_from_field() method will be used. Here it's one real example used to drop the enum defined in the forum->type column as part of the upgrade from Moodle 1.9 to 2.0 (just adjust it for your own needs):

/// Dropping all enums/check contraints from core. MDL-18577
    if ($result && $oldversion < 2009042700) {
 
    /// Changing list of values (enum) of field type on table forum to none
        $table = new xmldb_table('forum');
        $field = new xmldb_field('type', XMLDB_TYPE_CHAR, '20', null, XMLDB_NOTNULL, null, 'general', 'course');
 
    /// Launch change of list of values for field type
        $dbman->drop_enum_from_field($table, $field);
 
    /// forum savepoint reached
        upgrade_mod_savepoint($result, 2009042700, 'forum');
    }

Mixing query params and IN params

As you know, in Moodle 2.0 we are using placeholders for all the parameters passed to any SQL statement (see G6. Also, we use one special function to convert any list of values to the proper IN/EQUAL clause and associated params (see G7).

But what happens when we want to mix both type of params (query params and IN params) in the same query? Here it's one example:

Objective: return the number of forum discussion per course belonging to a given list of courses (IN params) and one type of forum (query param).

/// Here it's the query param (we are looking for 'general' forums in this example)
    $queryparams = array('general');
 
/// Here we calculate the IN clause (the list of courses we are going to search)
/// it returns 2 values: the SQL IN clause needed and the params array to be applied
    list($insql, $inparams) = $DB->get_in_or_equal(array(1, 2, 3, 4, 5, 6));
 
/// Here it's the query we are going to execute, with the IN clause injected in place
    $sql = "SELECT f.course, COUNT(*) count
              FROM {forum} f
              JOIN {forum_discussions} d ON d.forum = f.id
             WHERE f.type = ?
                   AND f.course $insql
          GROUP BY f.course";
 
/// Here we merge all params (query ones and IN clause ones,
/// respecting the order they are used in the query!)
    $params = array_merge($queryparams, $inparams);
 
/// Execute the query, returning the expected count per course
    $records = $DB->get_records_sql($sql, $params);

Note that is really important to respect the order of the params when merging them in case you are using question mark placeholders in your query. Alternatively, you can use named params if you want to skip that ordering potential issue, specially if your query is very complex or highly dynamic. Here it's the same example using named params (see the 2nd param used in the get_in_or_equal() method call), that's all :

/// Here it's the query param (we are looking for 'general' forums in this example)
    $queryparams = array('forum_type' => 'general');
 
/// Here we calculate the IN clause (the list of courses we are going to search)
/// it returns 2 values: the SQL IN clause needed and the params array to be applied
    list($insql, $inparams) = $DB->get_in_or_equal(array(1, 2, 3, 4, 5, 6), SQL_PARAMS_NAMED);
 
/// Here it's the query we are going to execute, with the IN clause injected in place
    $sql = "SELECT f.course, COUNT(*) count
              FROM {forum} f
              JOIN {forum_discussions} d ON d.forum = f.id
             WHERE f.type = :forum_type
                   AND f.course $insql
          GROUP BY f.course";
 
/// Here we merge all params (query ones and IN clause ones,
/// as we are using named params, order isn't important)
    $params = array_merge($inparams, $queryparams);
 
/// Execute the query, returning the expected count per course
    $records = $DB->get_records_sql($sql, $params);

See also

  • XMLDB Documentation: where both xmldb and ddl stuff is explained.
  • Database coding guidelines: where some basic rules to follow are defined.
  • DDL functions - Documentation for all the Data Definition Language (DDL) functions available inside Moodle.
  • DML functions - Documentation for all the Data Manipulation Language (DML) functions available inside Moodle