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 (excemption detailed 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
• 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.
• A proper format for select statements is
  select ....
  from ....
  where clause
      and clause
      and clause
• A proper format for insert statements (and note the indention and overflow of attributes)
  insert into table_name
    (c1, c2, c3 ...
     cx, cy, cz)
  values
    (v1, v2, v3 ...
     vx, vy, vz)
• A proper format for update statements:
  update table
  set c1=v1,
       c2=v2,
       ...
  where ...
  
  

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