Page Comparison

Totara supports PostgreSQL, MySQL and MSSQL. Here are some things to consider to ensure support across all databases.

...

In addition the get_records* and get_recordset* functions provide arguments for OFFSET/LIMIT which should be used instead of including them in the query:

Correct code

$first10users = $DB->get_records_sql("SELECT * FROM {user} ORDER BY id", null, 0, 10);

Incorrect code

$first10users = $DB->get_records_sql("SELECT * FROM {user} ORDER BY id LIMIT 0,10");

...

• The second argument to sql_like() is either ‘?’ or ‘:named’ only. No % symbols etc, put them in the parameter.

• Always escape any variables in the parameter with $DB->sql_like_escape().

For example do this:

Correct code

$field = 'fullname';
$word_to_match = 'banana';
$like_sql = $DB->sql_like($field, '?');
$params = ['%' . $DB->sql_like_escape($word_to_match) . '%'];
$DB->get_records_sql("SELECT * FROM {table} WHERE $like_sql", $params);

instead of this:

Incorrect code

$field = 'fullname';
$word_to_match = 'banana';
$like_sql = $DB->sql_like($field, '%?%');
$params = array($word_to_match);
$DB->get_records_sql("SELECT * FROM {table} WHERE $like_sql", $params);

...

If you develop with postgres, make sure you test in another db, because postgres will let you get away with things like this:

Correct code

SELECT * FROM table WHERE course = 3ORDER BY id; // no space between 3 and ORDER BY

Incorrect code

SELECT COUNT (DISTINCT field) FROM table; // space between count and open bracket

...

PostgreSQL doesn’t support the use of double quotes when quoting a fixed string.

So use single quotes:

Correct code

SELECT 'mystring';

instead of doing this:

Incorrect code

SELECT "mystring";

Fixed values in GROUP BY

PostgreSQL doesn’t support the use of fixed values in a GROUP BY statement:

Incorrect code

SELECT 'mystring', COUNT(id) FROM {user} GROUP BY 'mystring';

...

MySQL doesn’t require you specify them all, PostgreSQL does.

Correct code

SELECT u.firstname,u.lastname,count(u.id) FROM {user} u GROUP BY u.firstname, u.lastname;

Incorrect code

SELECT u.firstname,u.lastname,count(u.id) FROM {user} u GROUP BY u.firstname;

...

The field name used for calculated fields can be different, so always use a field alias so you can be sure how to refer to the field. So use:

Correct code

SELECT firstname, MIN(id) AS minid FROM {user} GROUP BY firstname;

instead of:

Incorrect code

SELECT firstname, MIN(id) FROM {user} GROUP BY firstname;

...

This is not supported in mysql:

Incorrect code

SELECT DISTINCT(table.*) FROM …

If you exclude the brackets it is okay in mysql:

Correct code

SELECT DISTINCT table.* FROM …

...

This is not supported in mysql:

Incorrect code

UPDATE table … FROM (SELECT …);

Use the following format for complex UPDATEs

Correct code

UPDATE {primary_table}
SET field = (SELECT otheralias.otherfield
               FROM {other_table} otheralias
              WHERE {primary_table}.id = otheralias.fk)
WHERE somefield = something
AND EXISTS (SELECT 1
              FROM {anothertable} anotheralias
             WHERE {primary_table}.id = anotheralias.fk);

...

Prior to Totara LMS 2.6, MSSQL didn’t support comparison of TEXT fields (Oracle has similar restrictions). Because comparison is used by ORDER BY, DISTINCT and GROUP BY you couldn't use any of these operations on TEXT fields directly:

Incorrect code

SELECT id FROM {course} WHERE summary = :summarystring;

Incorrect code

SELECT DISTINCT(summary) FROM {course};

Incorrect code

SELECT id FROM {course} ORDER BY summary;

Incorrect code

SELECT summary, MIN(startdate) FROM {course} GROUP BY summary;

Incorrect code

SELECT fullname, COUNT(summary) FROM {course} GROUP BY fullname;

If you really needed to compare them you can use sql_compare_text() or sql_order_by_text():

Correct code

$sql = "SELECT id FROM {course} WHERE " . $DB->sql_compare_text('summary') . " = :summarystring";

...

Oracle only supports a maximum of 1000 items in an IN() clause. If your IN clause is likely to be very large you are probably better off using a JOIN instead (which will probably also be faster).

For example, do:

Correct code

$sql = "SELECT id FROM {course} WHERE " . $DB->sql_compare_text('summary') . " = :summarystring";

instead of:

Incorrect code

SELECT firstname, lastname FROM {user} u
    WHERE id IN (SELECT userid FROM {course_completion} WHERE course = 4);

...

Postgres expects that field is of the same type as defaultvalue. MySQL will happily accept field of type int and string in the defaultvalue.

Correct code

SELECT COALESCE(integerfield, 0);

Correct code

SELECT COALESCE(varcharfield, "''");

Incorrect code

SELECT COALESCE(integerfield, "''");

...

Recent versions of Moodle require Postgres 9+ and don't support Oracle so technically either approach is supported. However due to previous support for postgres < 8.4 and Oracle, existing queries all use the same convention, so we should stick with it for consistency. This is to use the AS keyword to alias columns, but not tables/subqueries. e.g.:

Correct code

SELECT id AS userid FROM {user} u;

Incorrect code

SELECT id userid FROM {user};

Incorrect code

SELECT id FROM {user} AS u;

...