Page Comparison

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

If you want to test a query across multiple databases, you can use a site like http://sqlfiddle.com/

Database reserved words

Each database has a list of words that are reserved for internal use. Because Totara supports multiple databases the list of reserved words is quite long:

http://docs.moodle.org/dev/XMLDB_reserved_words

Some common reserved words include: key, numeric, plan, date, file, mod, public and user.

...

We recommend building your schema files (install.xml, upgrade.php) using the [http://docs.moodle.org/dev/XMLDB_editor XMLDB editor] since this will ensure valid syntax and provides a built in reserved word checking tool.

Using cross-database functions provided by Totara

Some commonly used SQL syntax is not cross-database compatible. Moodle provides a set of functions (functions prefixed with sql_ in lib/dml/moodle_database.php) which return the appropriate SQL for each database.

Use the $DB->sql_* methods for calculating:

• Length

• Substring

• Position

• Casting between types

• like

• max

• concat

• isempty/isnotempty

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 syntax for sql_like can be easy to get wrong. Make sure:

• 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);

The important things to note are:

• the updated table doesn’t use an alias (line 1)

• the field changed (line 2) and the field in the where (line 5) don’t need the table name prefix

• if the updated table is referenced inside a subquery, prefix the field with the full table name (lines 4 and 8)

Be careful about multi-column indexes on VARCHAR fields

...

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, "''");

...

Different databases have different support for how AS should be used:

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;

...

Previously get_recordset* methods were expected to use client-side buffers for the whole result - MySQL/ariaDB and PostgreSQL always did it, MS SQL Server does it in some cases already too. New get_huge_recordset* methods use small client-side buffers or server-side cursors to allow iteration of results that would not fit into memory. get_huge_recordset_xxx() methods make the intent more clear and will not pollute autocompletion of current get_record* methods. Database table export fallback was switched to huge record sets.

There are two important limitations of huge recordsets:

• MS SQL Server does not like modification of referenced db tables while iterating - this is fatal for temporary tables and sometimes regular tables too

• MS SQL Server does not like transactions while iterating record sets