SQL compatibility rules: Difference between revisions

From OpenPetra Wiki
Jump to navigation Jump to search
No edit summary
No edit summary
Line 11: Line 11:
*** [http://www.probusiness.de/projekte/phpgroupware/sql_style_guide.html pro business: SQL Style Guide]
*** [http://www.probusiness.de/projekte/phpgroupware/sql_style_guide.html pro business: SQL Style Guide]
*** [http://web.archive.org/web/20040625110035/http://www.wsl.ch/relics/rauminf/riv/datenbank/general/tinalondon.html Tina London's Guidelines and good practice Guide for developing SQL]
*** [http://web.archive.org/web/20040625110035/http://www.wsl.ch/relics/rauminf/riv/datenbank/general/tinalondon.html Tina London's Guidelines and good practice Guide for developing SQL]
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 [http://bzr.openpetra.org:8088/loggerhead/trunk/annotate/head:/csharp/ICT/Petra/Server/lib/MPartner/code/DataAggregates.PPartnerAddress.cs 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));





Revision as of 13:55, 29 March 2011

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)
  • ORDER BY for queries that are joined with UNION cannot work with aliases for the columns, you have to use the number for the column
    • (e.g. ... ORDER BY 1)
  • boolean variables need to be compared with true or false
    • (e.g. WHERE p_deleted_partner_l = false AND ...)
  • don't use JOIN. Please explicitly write the join inside the WHERE clause.


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)