Database compatibility

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.

Do not use any of the words listed for table names, columns or table or field aliases.

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:

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

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

Use sql_like() correctly

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:

$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:

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

Lenient use of whitespace PostgreSQL

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

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

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

This kind of error can happen when you build SQL from strings and forget to include whitespace.

Quote fixed strings with single quotes

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

So use single quotes:

SELECT 'mystring';

instead of doing this:

SELECT "mystring";

Fixed values in GROUP BY

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

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

You will need to refactor your query to avoid grouping by a fixed value.

Specifying all non-aggregated fields in GROUP BY

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

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

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

This often comes up when grouping by all fields in a table via table.*.

Use field aliases on all complex/calculated fields

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:

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

instead of:

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

Don’t use DISTINCT() with table wildcards

This is not supported in mysql:

SELECT DISTINCT(table.*) FROM …

If you exclude the brackets it is okay in mysql:

SELECT DISTINCT table.* FROM …

Don’t use UPDATE FROM SELECT

This is not supported in mysql:

UPDATE table … FROM (SELECT …);

Use the following format for complex UPDATEs

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

MySQL has a 1000 byte limit on indexes. If you do a unique index across multiple VARCHAR fields this can quickly fill up as unicode characters use 3 bytes per character.

Probably best avoided anyway as it will be slow.

Avoid comparing, sorting, grouping or DISTINCTing TEXT field types

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:

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

SELECT DISTINCT(summary) FROM {course};

SELECT id FROM {course} ORDER BY summary;

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

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():

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

Also be aware that they are just truncating the TEXT fields to a fix length and casting to VARCHAR type so you are not really comparing the whole field when you do this.

Be aware when using table wildcards (table.*) since this may include TEXT fields.

This has been fixed in Totara LMS 2.6 and above, in those versions you can treat TEXT fields normally.

Avoid using boolean types

Don’t use TRUE or FALSE as they aren’t supported by MSSQL. If required use 1=1 or 1=0 instead.

Drop indexes and keys prior to dropping fields

MSSQL won’t allow you to drop a table field if it has any indexes or keys on it. You need to drop any indexes or keys first, before you drop the field.

Avoid using IN() for very large sets

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:

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

instead of:

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

If you do need to work around it you can use sql_sequence() in totara/core/utils.php which will split large in clauses into IN (…) OR IN (…) OR …

COALESCE params must be of the same type

COALESCE(<field>, <defaultvalue>) - Returns field, unless it equals Null, in which case it returns defaultvalue.

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

SELECT COALESCE(integerfield, 0);

SELECT COALESCE(varcharfield, "''");

SELECT COALESCE(integerfield, "''");

Using the AS keyword

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

SELECT id AS userid FROM {user} u;

SELECT id userid FROM {user};

SELECT id FROM {user} AS u;

Huge recordsets

As of Totara 13 you can use get_huge_recordset_xxx() methods for huge recordsets.

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