The Business Rules Engine

Data Manipulation Language

Introduction

DML, or Data Manipulation Language, consists of the commands used to manipulate data within the CodaServer databases. For tables, this includes INSERT, UPDATE, and DELETE. For forms, this includes UPDATE and the form-specific status verbs that are defined for the form. Additionally, procedures can modify data through the EXEC PROCEDURE statement, and the RAW SQL statement can be used to run native SQL commands on the target databases.

All DML commands require a connection with the particular application and environment that is being changed. This connection can be created with the SET APPLICATION command.

Insert

insert := INSERT INTO <table_name> [
              ( <column_name> [ , ... ]* ) VALUES ( <value> [ , ... ]* ) [ , ... ]* |
              SET <column_name> = <value> [ , ... ]
          ]
value := NULL | CURRENT_TIMESTAMP | CURRENT_USERNAME | CURRENT_GROUP_NAME |
         '<string_literal>' | <array_literal>

Inserts a new row into a table. Coda supports standard SQL-style inserts, multirow SQL-style inserts (by specifying multiple VALUES blocks), and UPDATE-style inserts with a SET clause.

Upon a successful INSERT, CodaServer returns a resultset containing the IDs of all newly created rows.

As of this writing, CodaServer does not support expressions or subselects in INSERT statements, only literal values or system variables.

Update

update := UPDATE <table_name> SET
          <column_name> = <value> [ , ... ] [ <where_clause> ]?
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>'
             ]
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 
comparison_operator := [ = | <> | <= | != | < | >= | > ]

Updates rows in either a table or form. In the case of the form, the updates do NOT change the status of the form.

As of this writing, Coda does not support expressions or subselects in the SET clause of UPDATE statements, only literal values or system variables.

If no WHERE clause is specified, EVERY ROW IN THE TABLE WILL BE UPDATED. Be careful.

Delete

delete := DELETE FROM <table_name>
          [ <where_clause> ]?
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>'
             ]
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 
comparison_operator := [ = | <> | <= | != | < | >= | > ]

Deletes rows from a table. If the table has the SOFT DELETE flag set, it sets the ACTIVE_FLAG column to false instead of performing a hard delete on the row.

If no WHERE clause is specified, EVERY ROW IN THE TABLE WILL BE DELETED. Be careful.

Form Status Verbs

status_verb := <status_verb> <table_name> SET
          <column_name> = <value> [ , ... ]  [ AS NEW FORM | [ <where_clause> ]? ]?
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>'
             ]
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 
comparison_operator := [ = | <> | <= | != | < | >= | > ]

Form status verbs are the mechanism by which forms are moved from one status to another. They are best thought of as smart UPDATE statements that respect business rules. As an example, let's use a simple "order" process .

order workflow

If we were to issue the command

PAY orders SET paid_date = CURRENT_TIMESTAMP

all of the orders in either the Placed, Backordered, or Partially Shipped statuses would be moved to Paid. If we only wanted to move a particular status to Paid, we might issue the command

PAY orders SET paid_date = CURRENT_TIMESTAMP WHERE status_id = '3'

where the status_id specified is that of the status we wish to include. Of course, the SET clause can have any number of columns in it, and any valid WHERE clause could be used to narrow down the number of rows to be updated. Very often, a single ID will be specified, since most forms are processed one-at-a-time.

A special case for form status verbs is that of the "initial status." In the example above, Built is an initial status in that it is the means by which an order enters the process. If we wished to build a new order we would simply type

BUILD orders SET order_number = 'CO123', address = '123', ... AS NEW FORM

with the ... replaced by the rest of the fields we wish to set. Since we are creating a new order, a WHERE clause is inappropriate. If we decided to make Placed an initial status as well (for our sales people entering orders from a phone call, for instance) it would be possible to use the AS NEW FORM syntax with the PLACE verb as well. The initial status flag doesn't prevent a status from occurring elsewhere in a process.

AS NEW FORM calls return a resultset containing the IDs of all newly-created rows.

Exec Procedure

exec_procedure := EXEC PROCEDURE <procedure_name> 
                  [ ( [ '<string_literal>' | <array_literal> ] [ , ... ]* ) ]?

Executes the specified procedure with the given parameters.

Executing a procedure always returns a result, and how you access this result depends on the environment you are working in. The command-line CodaClient has a FETCH command for this purpose.

Raw SQL

raw_sql := RAW SQL : <sql_statement>

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

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