SQL - XQL
OpenACS stores all it's SQL code in .xql files. At least this is the convention. Though this adds an extra burden to the development, please adhere to this standard to make it easy to support OpenACS on multiple databases.
- No SQL code in .tcl or .adp files (unless it is fully portable SQL99; see details below)
- All db_statements need to be named (see Naming Conventions)
- Make sure to use database specific SQL (Non SQL99) code in database specific -database.xql files only
- select content_item__new() => content-item-postgresql.xql
- select item_id from content_items => content-item.xql
- Don't put SQL-code for one statement into multiple places (e.g. inline in Tcl, generic .xql, database-specific .xql)
- If in doubt, be conservative and add it into the database specific file of the database you are developing in.
- Format your SQL strings properly and do not put them all in one line (see: code-formatting)
A small reminder, all changes on an .xql file need to be followed by a reload of this file to become effective
Excemption to the rule of "No SQL code in .tcl" is when you build up your SQL query dynamically, so whenever you only have a partial SQL query, using this in tcl is fine if it is following SQL 99 standard. Usually you will find this in listbuilder, where you add the "where_clause".
Creation of tables in postgres
- Do not use "SERIAL" datatype. SERIAL is an ugly hack in PG meant to make it easier to port Sybase code to PG. We have a standard way of defining integer primary keys and giving values to them: Integer with a sequence
- When creating a sequence you should use the CREATE VIEW hack in PG so sequence queries can be shared between PG and Oracle without any extra work. Otherwise you are required to make the sequence work both in -oracle.xql as well as -postgresql.xql