SQL - XQL

By convention, OpenACS stores all its SQL code in .xql files, which facilitates support for OpenACS on multiple databases.  Though this adds an extra burden to the development, please adhere to this convention.

• Keep SQL scripts out of .tcl or .adp files, unless they conform to SQL99.  See details below.
• Assign a name to each db_statement.  see Naming Conventions.
• Database-specific SQL statements, i.e. statements not conforming to SQL99, belongs in a -database.xql file:
  • select content_item__new() => content-item-postgresql.xql
  • select item_id from content_items => content-item.xql
• Do not spread pieces of an SQL statement into multiple places, e.g. inline in Tcl, generic .xql, database-specific .xql.
• If in doubt, be conservative and add SQL scripts into the database-specific file of the database you are working with.
• Format each SQL script properly.  E.g. break each statement into multiple lines as appropriate to make it more readable.  See code-formatting

To make changes to a .xql file take effect, reload the file.

As an exception to the rule about keeping SQL out of Tcl scripts, pieces of an SQL script that is dynamically generated may be placed in a Tcl script if they conform to the SQL 99 standard.  This is often the case for "where_clause" of listbuilder.

Creation of tables in PostgreSQL:

• Do not use the "SERIAL" datatype.  SERIAL is an ugly hack meant to make it easier to port Sybase scripts to PostgreSQL.  We have a standard way of defining integer primary keys and giving values to them: Integer with a sequence.
• When creating a sequence use the CREATE VIEW hack so sequence queries can be shared between PG and Oracle without any extra work.  Otherwise, do the work to make the sequence work both in -oracle.xql as well as -postgresql.xql