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:
Casting between types
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:
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:instead of this:
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: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:instead of doing this:
Fixed values in GROUP BY
PostgreSQL doesn’t support the use of fixed values in a GROUP BY statement: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.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:instead of:
Don’t use DISTINCT() with table wildcards
This is not supported in mysql:If you exclude the brackets it is okay in mysql:
Don’t use UPDATE FROM SELECT
This is not supported in mysql:Use the following format for complex UPDATEsThe 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:
If you really needed to compare them you can use sql_compare_text() or sql_order_by_text():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:instead of: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.
Using the AS keyword
Different databases have different support for how AS should be used:
field AS alias
table AS alias
Not supported before 8.4
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.:
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