The Business Rules Engine

Metadata Commands

Introduction

Coda has a number of commands to help users get information about the server and the applications they are running. This information (metadata) also helps developers create highly-tuned, dynamic front-end environments that change as needed to reflect the current state of the applications they present.

Show (Server Objects)

show := SHOW [
            USERS 
                [ IN GROUP <group_name> ]? 
                [ FOR APPLICATION <application_name> . <environment_name> ]? |
            GROUPS 
                [ FOR APPLICATION <application_name> . <environment_name> ]? |
            TYPES |
            DATASOURCES |
            SESSIONS |
            APPLICATIONS
                [ FOR GROUP <group_name> ]? |
            SERVER PERMISSIONS FOR USER <username> |
            APPLICATION PERMISSIONS FOR USER <username>
                [ IN GROUP <group_name> ]? |
            SYS INFO
        ] [ <where_clause> ]? [ <order_by_clause> ]?

The SHOW commands return a recordset containing all of the relevant objects that meet the search criteria.

WHERE and ORDER BY clauses can be used to give some additional flexibility in showing the objects. To specify fields for theses clauses, the correct table aliases must be used. Most object types have the USERS table aliased as C for the creating user and M for the modifying user, and in all cases the primary table (for example, GROUPS in the SHOW GROUPS command) is aliased as OBJ. The exceptions are as follows:

  • SESSIONS: No M or C alias, U refers to the user referred to by the session.
  • SERVER PERMISSIONS: No M or C alias, U refers to the user.
  • APPLICATION PERMISSIONS: No M or C alias, U refers to the user and G to the group under which they have the permissions.
  • SYS INFO: No M or C alias.

Show (Application Objects)

show := SHOW [
            APP INFO |
            TABLES |
            FORMS |
            PROCEDURES |
            TRIGGERS FOR [ TABLE | FORM ] <table_name> |
            INDEXES |
            CRONS |
            ROLES 
                [ FOR USER <username> [ IN GROUP <group_name> ]? ]? |
            PERMISSIONS
                [
                    FOR USER <username> [ IN GROUP <group_name> ]? |
                    FOR ROLE <role_name>
                ]? |
            TABLE PERMISSIONS 
                [
                    FOR USER <username> [ IN GROUP <group_name> ]? |
                    FOR ROLE <role_name>
                ]? |
            FORM PERMISSIONS 
                [
                    FOR USER <username> [ IN GROUP <group_name> ]? |
                    FOR ROLE <role_name>
                ]? |
            PROCEDURE PERMISSIONS
                [
                    FOR USER <username> [ IN GROUP <group_name> ]? |
                    FOR ROLE <role_name>
                ]? 
        ] [ <where_clause> ]? [ <order_by_clause> ]?

The SHOW commands return a recordset containing all of the relevant objects that meet the search criteria.

WHERE and ORDER BY clauses can be used to give some additional flexibility in showing the objects. To specify fields for theses clauses, the correct table aliases must be used. In almost all cases the primary table (for example, TABLES in the SHOW TABLES command) is aliased as OBJ. The additional aliases are as follows:

  • TABLES: P refers to the parent table, if one is present.
  • FORMS: P refers to the parent form, if one is present.
  • TRIGGERS:
    • TABLE: P refers to the parent table of the trigger.
    • FORM: P refers to the parent form of the trigger, and FS to the status that fires it.
  • INDEXES: P refers to the parent form or table.
  • CRONS: P refers to the procedure called by the cron.
  • TABLE PERMISSIONS: T refers to the table, and R to the role.
  • FORM PERMISSIONS: F refers to the form, S to the status, and R to the role.
  • PROCEDURE PERMISSIONS: P refers to the procedure, and R to the role.

Describe (Server Objects)

describe := DESCRIBE [
                USER <username> |	
                GROUP <group_name> |
                TYPE <type_name> |
                DATASOURCE <datasource_name> |
                APPLICATION <application_name>
            ]

Returns a resultset with all of the data for a particular database object.

Describe (Application Objects)

describe := DESCRIBE [
                 TABLE <table_name> [ COLUMNS ]? |
                 FORM <form_name> [ FIELDS | STATUSES | STATUS RELATIONSHIPS ]? |
                 INDEX <type_name> [ COLUMNS ]? |
                 PROCEDURE <procedure_name> [ PARAMETERS ]? |
                 TRIGGER <table_form_name> [ BEFORE | AFTER ] <operation> |
                 CRON <cron_name> [ PARAMETERS ]?
            ]

Returns a resultset with all of the data for a particular database object. These DESCRIBE commands require an application and environment to be set in the session.

Some objects have optional parameters that return different data. These are described below:

  • TABLE: COLUMNS returns the table's columns
  • FORM: FIELDS returns the form's fields, STATUSES its statuses, and STATUS RELATIONSHIPS the "leads to" relationships of the statuses
  • INDEX: COLUMNS returns the indexes' columns
  • PROCEDURE: PARAMETERS returns the procedure's parameters
  • CRON: PARAMETERS returns the cron's parameters

SysSelect

sysselect := <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 ]? [ , ... ]*

Lets a user run SELECT statements directly against the system database. This requires the QUERY_SYSTEM_TABLES permission.

The following tables are available:

  • USERS: Contains all users
  • GROUPS: Contains all groups
  • USER_GROUPS: Indicates group membership
  • DATASOURCES: Contains all datasources
  • DATASOURCE_OPTIONS: Contains extended datasource options
  • APPLICATIONS: Contains all applications
  • GROUP_APPLICATIONS: Indicates which groups have access to which applications
  • SERVER_PERMISSIONS: Contains a list of available server permissions
  • APPLICATION_PERMISSIONS: Contains a list of available application permissions
  • USER_SERVER_PERMISSIONS: Indicates which users have which server permissions
  • USER_APPLICATION_PERMISSIONS: Indications which users have application permissions
  • TRANSACTIONS: Contains the schema transaction history for the various applications
  • TYPES: Contains all types
  • SESSIONS: Contains all CodaServer sessions
  • CLUSTER: Contains a list of all CodaServer instances in this cluster
  • CODA_SYSTEM_INFORMATION: Contains the metadata for this Coda datasource