The Business Rules Engine

Queries

Select

select := <select_clause>
          [ <from_clause>
              [ <where_clause> ]?
	      [ <group_by_clause> [ <havingClause> ]? ]?
	      [ <order_by_clause> ]?
          ]?
select_clause := SELECT [ TOP <integer> [ STARTING AT <integer> ]? ]?
                 [ DISTINCT ]? <select_list>
select_list := <select_item> [ , ... ]*
select_item := [ 
               * |
               [ 
                   [ <alias> . ]? 
                   [
                       * |
                       <column_name> [ [ AS ]? <alias> ]? |
                       <expression> [ [ AS ]? <alias> ]? |
                   ]
               ] ]
expression := <subexpression> [ [ + | - | * | / | % ] <subexpression> ]*
subexpression := [ + | - | ~ ]?
                 [
                     <function> |
                     ( <expression> ) |
                     [ <alias> ]? <column_name< |
                     <constant>
                 ] 
function := <function_name> (
                [
                    <expression> [ , ... ]* |
                    * |
                    [ ALL | DISTINCT ] [ * | <expression> ]
                ]?
            )
constant := NULL | '<string_literal>' | <system_variable>
system_variable := CURRENT_TIMESTAMP | CURRENT_GROUP_NAME | CURRENT_USERNAME 
from_clause := FROM <table_source>
table_source := <sub_table_source> [ <joined_table> ]*
sub_table_source := <table_name> [ [ AS ]? <alias> ]? |
                    ( <joined_tables> )
joined_table := [ 
                    INNER |
                    [ LEFT | RIGHT | FULL ] [ OUTER ]? 
                ]? JOIN <table_source> ON <search_condition>
joined_tables := <sub_table_source> [ <joined_table> ]+
where_clause := WHERE <seach_condition>
search_condition := <sub_search_condition> [ [ AND | OR ] <sub_search_condition> ]*
sub_search_condition := [ NOT ]? [ ( <search_condition> ) | <predicate> ]
predicate := <expression> [
                 <comparison_operator> <expression> |
                 IS [ NOT ]? NULL | 
                 [ NOT ]?[
                     LIKE <expression> |
                     IN ( '<string_literal>' [ , ... ]* )
                 ] | 
                 CONTAINS '<string_literal>'
             ]
comparison_operator := [ = | <> | <= | != | < | >= | > ]
group_by_clause := GROUP BY [ ALL ]? <expression> [ , ... ]*
having_clause := HAVING <search_condition>
order_by_clause := ORDER BY <expression> [ ASC | DESC ]? [ , ... ]*

The SELECT statement is used to return data from the database. Like DML statements, it can only be run when an application and environment are set in the user's session.

A SELECT in Coda is very much like a SELECT in SQL, with a few minor differences.

  • Subselects are not currently supported.
  • All literal values are single-quoted. This includes INTEGERs and FLOATs.
  • All dates are expected to be in UNIX timestamp format. This means seconds since Midnight of 1/1/1970.

Despite the different declaration commands, Coda tables and forms are identical in queries. Joins work the same way between them as would be expected in a SQL database.

The SELECT command can also be used to determine metadata about the application. When the datasource is formatted, a prefix for the schema tables is chosen. For example, if the prefix is "cd_", the schema table FORM_STATUSES would be accessible as CD_FORM_STATUSES.

Below is a list of the schema tables available:

  • TABLES: A list of all tables and forms. They are differentiated by the FORM_FLAG column.
  • TABLE_FIELDS: A list of all table columns and form fields.
  • FORM_STATUSES: A list of all form statuses.
  • FORM_STATUS_RELATIONSHIPS: The "leads to" relationships between form statuses.
  • TRIGGERS: A list of all triggers.
  • INDEXES: A list of all indexes.
  • INDEX_FIELDS: A list of all columns or fields used in indexes.
  • PROCEDURES: A list of all procedures.
  • PROCEDURE_PARAMETERS: A list of all parameters of procedures.
  • CRONS: A list of all crons.
  • CRON_PARAMETERS: A list of all parameters passed in crons.
  • ROLES: A list of all roles.
  • USER_ROLES: Indicates which users have which roles.
  • PERMISSIONS: A list of all user-defined permissions.
  • ROLE_PERMISSIONS: Indicates which roles have which user-defined permissions.
  • ROLE_TABLES: Indicates which roles have what access to which tables.
  • ROLE_FORM_STATUSES: Indicates which roles have what access to which forms at which form statuses.
  • ROLE_PROCEDURES: Indicates which roles have which procedures.
  • CODA_SYSTEM_INFORMATION: Contains metadata about this datasource. This is the only table that isn't prefixed.

While technically possible, it is HIGHLY RECOMMENDED that these tables are NOT inserted into, updated, or deleted from outside of using the appropriate Coda commands. Data corruption is highly likely, and they are subject to change with future releases of the software, breaking compatibility. You've been warned.

SelectObject

selectobject := SELECTOBJECT FROM <table_name> WHERE ID = '<integer>' [ GREEDY ]?

SELECTOBJECT is a convenient way to get all of the data associated with a single form or table row. It returns a single-row resultset containing all of the fields or columns of the row with the provided ID.

If the greedy flag is provided, it returns an additional resultset for each subform or subtable of the queried table, each containing all the rows that relate to the provided ID value.

Raw Select

raw_select := RAW SELECT : <select_statement>

RAW SELECT, like RAW SQL, bypasses CodaServer's preprocessor and sends the provided SELECT statement directly to the underlying database engine.

Some databases have advanced features that cannot be included in Coda's SELECT implementation, as they would break compatibility with other target databases. If it is absolutely necessary to use these features, Coda provides RAW SELECT.