SQL compatibility rules

The following pitfalls exist when trying to support several databases. Please stick to these rules!

• test for a null value: use IS NULL.
  • (e.g. ... OR p_date_good_until_d IS NULL)
  • SQL Null handling is identical between SQLite & Postgres, including the usage of CASE. See: SQL Null Handling for a useful comparison.
  • Useful functions for handling null values in queries include:
  • COALESCE(value [, ...]) - return the first non-null value in a long list
  • e.g. SELECT COALESCE(description, short_description, '(none)')
  • And its inverse function:
  • NULLIF(value1, value2) - returns a null value if value1 equals value2; otherwise it returns value1
  • e.g. SELECT COALESCE(NULLIF(a_field, ' '), another_field) FROM table_name; - In other words: if column 'a_field' is empty or null then column 'another_field' is returned
• boolean variables need to be compared with true or false
  • (e.g. WHERE p_deleted_partner_l = false AND ...)
• Include ELSE with CASE
  • Example: SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;
  • If no ELSE is present in the CASE, the result will be null, so it is best to always include the ELSE clause of a CASE.
  • In terms of checking for nulls you need to be careful with CASE:
  • CASE WHEN null THEN 1 ELSE 0 END, always returns 0 even if the case is null.
  • To check for null: CASE WHEN (myField IS NULL) THEN 1 ELSE 0 END will return 1 when myField is null
• don't use JOIN. Please explicitly write the join inside the WHERE clause.
  • see the remarks in:
    • pro business: SQL Style Guide
    • Tina London's Guidelines and good practice Guide for developing SQL

Not all ANSI SQL Features are implemented In SQLite To see SQL implementation view: SQL Implementation in SQLite

Unsupported features of SQL include:

• RIGHT and FULL OUTER JOIN - LEFT OUTER JOIN is implemented, but not RIGHT OUTER JOIN or FULL OUTER JOIN.
• Complete ALTER TABLE support - Only the RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are supported. Other kinds of ALTER TABLE operations such as DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT, and so forth are omitted.
• Complete trigger support - FOR EACH ROW triggers are supported but not FOR EACH STATEMENT triggers.
• Writing to VIEWs - VIEWs in SQLite are read-only. You may not execute a DELETE, INSERT, or UPDATE statement on a view. But you can create a trigger that fires on an attempt to DELETE, INSERT, or UPDATE a view and do what you need in the body of the trigger.
• GRANT and REVOKE - Since SQLite reads and writes an ordinary disk file, the only access permissions that can be applied are the normal file access permissions of the underlying operating system. The GRANT and REVOKE commands commonly found on client/server RDBMSes are not implemented because they would be meaningless for an embedded database engine.

Please note that we reference all Table names with PUB_ before the name, eg. PUB_a_account. This is necessary, so that we can check for permissions and other things. For an example see this snippet from DataAggregates.PPartnerAddress.cs:

1952             OtherLocations = Convert.ToInt16(DBAccess.GDBAccessObj.ExecuteScalar(
1953                "SELECT COUNT(*) " + "FROM PUB_" + PPartnerLocationTable.GetTableDBName() + ' ' +
1954                "WHERE " + PPartnerLocationTable.GetPartnerKeyDBName() + " = ? " +
1955		     "AND " + PPartnerLocationTable.GetLocationKeyDBName() + " NOT IN " +
1956	             "(" + LocationKeyInString + ")", AReadTransaction, false, ParametersArray));

The Report Generator is able to convert the following pitfalls. You only need to know about them, when you are debugging.

• Strings are written in single quotes.
  • (e.g. WHERE p_status_code_c = 'ACTIVE')
• Dates need to be written in Amercian format, i.e. 'mm/dd/yyyy'.
  • Using the notation {#date_variable#) works from the reports, if you don't need a static date
• boolean comparisons: "true" needs to be written as "1", "false" is "0".
• The tables have the prefix "pub." instead of "pub_"

To get the days of year of a specific date in the sql statement use DAYOFYEAR(). This will return a string from 000 to 366. DAYOFYEAR() will be converted to the correct function for the different databases. For example DAYOFYEAR(person.p_date_of_birth_d) will be converted to:

• SqLite: strftime( %j, person.p_date_of_birth_d)
• PostgreSql: to_char(person.p_date_of_birth_d, 'DDD')
• MySql: DATE_FORMAT(person.p_date_of_birth_d, %j)