Skip to content

quick-sql-grammar.md

Latest commit

 

History

History
667 lines (493 loc) · 22.2 KB

quick-sql-grammar.md

File metadata and controls

667 lines (493 loc) · 22.2 KB

Quick SQL Grammar

Table of Contents

Comments

A comment can appear between any keywords, parameters, or punctuation marks in a statement. You can include a comment in a statement in two ways:

  • Begin the comment with a slash and an asterisk (/). Proceed with the text of the comment. This text can span multiple lines. End the comment with an asterisk and a slash (/). The opening and terminating characters need not be separated from the text by a space or a line break.
  • Begin the comment with -- (two hyphens). Proceed with the text of the comment. This text cannot extend to a new line. End the comment with a line break.

Datatypes

Type DB Type
boolean, bool BOOLEAN
num, number NUMBER
int, integer INTEGER
d, date DATE
ts, timestamp TIMESTAMP
tstz, tswtz, timestamp with local time zone TIMESTAMP WITH LOCAL TIMEZONE
char, vc, varchar, varchar2, string VARCHAR2(4000)
vcNNN,vc(NNN) VARCHAR2(NNN)
vc32k VARCHAR2(32767)
geometry, sdo_geometry SDO_GEOMETRY
vect, vector VECTOR(,,*)
vectNNN,vect(NNN) VECTOR(NNN,,)
vcNk VARCHAR2(N*1024)
clob CLOB
blob BLOB
json CLOB CHECK (<COLUMN_NAME> IS JSON)
file Adds a BLOB column and _FILENAME, _CHARSET, _MIMETYPE, _LASTUPD columns that enhance the ability for file download via a browser

Table Directives

Directive Description
/api Generate PL/SQL package API to query, insert, update, and delete data within a table. Adds Oracle auditing, by default AUDIT ALL ON <TABLE NAME>.
/audit Adds Oracle auditing, by default AUDIT ALL ON <TABLE NAME>.
/auditcols, /audit cols, /audit columns Automatically adds an UPDATED, UPDATED_BY, INSERTED, and INSERTED_BY columns and the trigger logic to set column values.
/check table level constraint
/colprefix Prefix all columns of a given table with this value. Automatically adds an underscore if not provided.
/compress, /compressed Table will be created compressed.
/flashback, /fda Enable Flashback Data Archive on the table. Optionally specify an archive name, e.g. /flashback myarchive.
/insert NN Generate NN SQL INSERT statement(s) with random data, for example: /INSERT 20. (Maximum = 1000)
/rest Generate REST enablement of the table using Oracle REST Data Services (ORDS)
/unique, /uk Generate table level unique constraint
/pk Generate primary key constraint (on table level it is usually a composite key)
{annotations} Oracle SQL annotations on the table. See Annotations. A DESCRIPTION annotation automatically generates a COMMENT ON TABLE statement.

Star/Snowflake schema relationship direction indicators

In star (and snowflake) database model tables are organized into a hierarchy where the table at the top (fact) is in many-to-one relationship to children (dimension tables). Consequently, each dimension table is equipped with primary key, which is referenced via foreign key constraint in the fact table. For example, given the Sales fact table together with Products and Customers dimension tables, the star database model is expressed in QSQL as:

sales
  quantity
  > products
    name
  > customers 
    first name

The more-than symbol > hints the many-to-one relationship between the table cardinalities. The opposite -- one-to-many relationship -- is denoted by the < prefix, which is default, and is usually omitted from QSQL schema definition.

Sales-Product-Customer Example

Column Directives

Directive Description
/idx, /index, /indexed Creates a non unique index
/unique, /uk Creates a unique constraint
/check Creates a check constraint with comma or white space delimited values e.g. /check Yes, No
/constant When generating data set this column to a constant value. For example /constant NYC.
/default Adds default value if the column is null
/domain Use an Oracle SQL Domain as the column type (23ai+). The domain name becomes the column's type, e.g. /domain email_d.
/values Comma separated list of values to use when generating data. For example /values 1, 2, 3, 4 or /values Yes, No.
/upper Forces column values to upper case
/lower Forces column values to lower case
/nn, /not null Adds a not null constraint on the column
/between Adds a between check constraint on the column, for example /between 1 and 100
/references, /reference, /fk Foreign key references e.g. /references table_name. Note you can reference tables that are not part of your model.
/cascade on delete cascade
/setnull on delete set null
/pk Identifies column as the primary key of the table. It is recommended not manually specify primary keys and let this app create primary key columns automatically.
/trans, /translation, /translations Marks a column for multi-lingual translation support. Generates a shared language table, a <table>_trans table with translated column variants, and a <table>_resolved view that joins them using sys_context for the current language. See the transcontext setting.
--, [comments] Enclose comments using square brackets or using dash dash syntax
{annotations} Oracle SQL annotations on the column. See Annotations. A DESCRIPTION annotation automatically generates a COMMENT ON COLUMN statement.

Views

View Syntax

view [view_name] [table name] [table name]...

Ensure the view name contains no spaces, ensure the table names contain no spaces. Delimit table names by a space or comma.

View Example

dept 
  dname 
  loc 
  emp 
    ename 
    job 
    
view dept_emp emp dept

This syntax restricts views to conjunctive queries (i.e. containing equijoin predicates) only.

Duality View Syntax (23ai+)

dv [view_name] [root_table] [nested_table]...

JSON Relational Duality Views expose relational data as JSON documents. The first table is the root; subsequent tables are nested as child or parent objects based on their foreign key relationships.

Duality View Example

# settings = {db: "23ai"}
departments
    name
    employees
        first_name
        last_name
        salary num

dv dept_emp_dv departments employees

Generates:

create or replace json relational duality view dept_emp_dv as
departments @insert @update @delete
{
    _id       : id,
    name      : name,
    employees : employees @insert @update @delete
    [{
        _id        : id,
        first_name : first_name,
        last_name  : last_name,
        salary     : salary
    }]
};

Annotations

Oracle SQL annotations can be added to tables, columns, and views using curly braces {...}. Annotations are included in the generated DDL as ANNOTATIONS (...) clauses.

Annotation Syntax

table_name {Key 'value', AnotherKey "value", FlagKey}
    column_name {Key 'value'}
  • Key-value annotations: Key 'value' or Key "value" (single or double quotes)
  • Flag annotations (no value): FlagKey
  • Multiple annotations are comma-separated

DESCRIPTION Annotation

When an annotation has the key DESCRIPTION, a COMMENT ON statement is automatically generated from its value. This applies to both tables and columns. If a node has both a DESCRIPTION annotation and an explicit comment ([...] or --), the DESCRIPTION annotation takes precedence.

departments {DESCRIPTION 'Main HR departments table', Classification 'HR'}
    name {DESCRIPTION 'The department name'}

Generates:

create table departments (
    ...
)
annotations (DESCRIPTION 'Main HR departments table', Classification 'HR');

comment on table departments is 'Main HR departments table';
comment on column departments.name is 'The department name';

Annotation Examples

-- Table-level annotation
departments {UI_Display 'Departments', Classification 'HR'}
    name

-- Column-level annotation
departments
    name {UI_Display 'Department Name'}

-- Annotations combined with directives
departments
    name /nn {Format 'text'}

-- View annotation
view dept_v dept {UI_Display 'Department View'}

AI Enrichment (aienrichment + db >= 26)

When the aienrichment setting is yes and db is 26ai or higher, Quick SQL additionally generates a PL/SQL block that populates the METADATA_ANNOTATIONS AI enrichment layer. This enables Oracle 26ai features such as METADATA_ANNOTATIONS_USAGE, METADATA_ANNOTATIONS_GROUPS, and METADATA_ANNOTATIONS_GROUP_MEM views.

For key-value annotations, metadata_annotations.set() calls are generated:

  • Table annotations — metadata_annotations.set(label, value, 'TABLE_NAME')
  • Column annotations — metadata_annotations.set(label, value, 'TABLE.COLUMN', 'TABLE COLUMN')
  • View annotations — metadata_annotations.set(label, value, 'VIEW_NAME', 'VIEW')

For GROUP annotations, metadata_annotations.create_group() and metadata_annotations.add_to_group() calls are generated.

Flag annotations (without a value) are skipped since the package requires a value argument.

Settings

You can enter inline settings to explicitly set SQL syntax generation options. Alternatively, you can click Settings at the top of the right pane to declaratively set the generation options.

Entering settings directly into the Quick SQL Shorthand pane ensures the same SQL generation options are utilized even if you download the script and later paste it back. For example, enter the following to prefix all table names with TEST and generate for schema OBE:

# settings = { prefix: "test", schema: "OBE" }

Alternatively, enter each setting on a separate line for the same result:

# prefix: "test"

# schema: "OBE"

Note: The settings must start on a new line and begin with # settings = to enter multiple settings, or # to enter a single setting per line. All values are case insensitive. Brackets, spaces, and commas can be added for clarity but are ignored. To have all settings generated use:

# verbose: true

The available settings are listed in the below sections.

apex

Possible values: true, false
Default value: false

This setting controls the syntax generated to support audit columns. Specifically if audit columns are enabled triggers are generated to maintain the user creating a row and the user last updating a row. When enabled the following function is used:

coalesce(sys_context('APEX$SESSION','APP_USER'),user)

When not enabled the following function is used:

user

api

Possible Values: true, false
Default Value: false

Generate PL/SQL APIs on all tables for create, insert, update, delete and query.

auditcols

Possible Values: true, false
Default Value: false

Adds an additional created, created_by, updated and updated_by columns to every table created.

boolean

Possible Values: yn, native
Default Value: inferred from #db

Set boolean to legacy char(1) or new 23c native boolean value. This setting has priority over db:23c, so that the user can override db seting (which influences other functionality)

compress

Possible Values: true, false
Default Value: false

When enabled creates all tables compressed.

transcontext

Default Value: sys_context('APP_CTX','LANG')

The SQL expression used in the _resolved view to determine the current language when using /trans column directives. For example, to use a different application context:

# transcontext: "sys_context('MY_CTX','LANGUAGE')"

createdByCol

Default Value: created_by

When Audit Columns are enabled the default column used to track the user who created a row is CREATED_BY. Use this setting to override default audit column name.

createdCol

Default Value: created

When Audit Columns are enabled the default column used to track the user who created a row is CREATED. Use this setting to override default audit column name.

date

Possible Values: date, timestamp, timestamp with timezone, TSWTZ, timestamp with local time zone, TSWLTZ
Default Value: date

By default all DATE columns created using the Oracle DATE datatype. Use this setting to override this default.

db

Possible Values: 11g, 12c, 19c, 21c, 23c, 26ai Default Value: 19c

Specifies the database version the syntax should be compatible with. The version string is reduced to major version number. Therefore, 23, 23c, 23ai, and 23.1.1 are all legitimate values equivalent to 23.

drop

Possible Values: true, false
Default Value: false

Include SQL commands to drop each database object created.

language

Possible Values: EN, DE, KO, JA
Default Value: EN

Generate data used for insert statements using this language.

longVC

Possible Values: true, false
Default Value: false

Allow longer identifiers to be used for database object names. Longer identifiers allow the maximum length a VARCHAR2 column datatype will be 32767 characters. When not set the maximum length of a VARCHAR2 column datatype will be 4000 characters.

ondelete

Possible Values: cascade, restrict, set null Default Value: cascade

This setting controls how foreign key ON DELETE settings.

overrideSettings

Possible Values: true, false
Default Value: false

When enabled all application settings set via the user interface console are ignored and only settings set in the script will be used.

PK

Possible Values: guid, seq, identity, none
Default Value: identity

Determines how the primary key will be set. Primary keys can be set using SYS_GUID, identity column or sequence.

prefix

Database object prefix. An underscore will be appended if not provided.

prefixPKwithTname

Possible Values: true, false
Default Value: false

Prefix primary key database table columns with name of table. For example the primary key of the EMPLOYEE table would be EMPLOYEE_ID. Without setting the name of implicitly created primary key columns will be ID.

genPK

Possible Values: true, false
Default Value: true

Automatically generate an ID primary key column for each table.

resetsettings

Resets all application settings to default values. When included all application settings currently active for your session will be ignored.

rowkey

Possible Values: true, false
Default Value: false

For each table created add a ROW_KEY column that generates an alphanumeric identifier. Values of the ROW_KEY column will be set by generated database table trigger logic.

tenantID

Possible Values: true, false
Default Value: false

For each table add a TENANT_ID column to support multi-tenant applications. The value of this column is simply added to the table, maintaining this value will need to be provided by the developer.

rowVersion

Possible Values: true, false
Default Value: false

For each table generated add a ROW_VERSION column that increments by 1 for each update. When enabled database table trigger logic will be generated to increment row versions on update.

schema

Prefix object names with a schema name. The default is no schema prefix for object names.

semantics

Possible Values: char, byte

You can choose between:

  • No column semantics:

    varchar2(4000)

  • Byte semantics:

    varchar2(4000 byte)

  • Char semantics

    varchar2(4000 char)

updatedByCol

Default Value: updated_by

When enabling audit columns use this setting to override default audit column name.

updatedCol

Default Value: updated

When enabling audit columns use this setting to override default audit column name.

verbose

Possible Values: true, false
Default Value: false

Show all settings, not just settings that are different from the default.

Document

The database defined via QuickSQL is populated with the data generated by chancejs. If QSQL code has been generated from json document, then the document is kept under the #document section, and is used to populate the database with genuine data. See the Car Racing Example.

Grammar

quicksql::= stmt+

stmt::= tree
      | view
      | '#' individual_setting
      |  'settings' '=' '{' individual_setting ( ',' individual_setting )* '}'
      |  'document' '=' JSON

view::= 'view' view_name table_name+ annotation?
       | view_name '=' table_name+
       | 'dv' view_name table_name+

view_name::= identifier
table_name::= identifier
column_name::= identifier

tree::= node+

node::= tableNode | columnNode

tableNode::= indentation relationship? tableName tableDirective* annotation?
columnNode::= indentation columnName columnDirective* datatype* annotation?

indentation::= INDENT | DEDENT | SAMELEVEL

relationship::= '>' | '<'

tableDirective::= '/'
       ('api'
      |'audit'|'auditcols'|'audit cols'|'audit columns'
      |'colprefix'
      |'compress'|'compressed'
      |'flashback'|'fda'
      |'insert' integer
      |'rest'
      |'unique' | 'uk'
      |'pk'
      |'check'
      |'cascade'
      )

columnDirective::= '/'
      ('idx'|'index'|'indexed'
      |'unique'|'uk'
      |'check'
      |'constant'
      |'default'
      |'values'
      |'upper'
      |'lower'
      |'nn'|'not null'
      |'between'
      |'references'|'reference'
      |'cascade'|'setnull'
      |'fk'
      |'pk'
      |'domain'
      |'trans'|'translation'|'translations'
      )

annotation::= '{' annotationEntry ( ',' annotationEntry )* '}'

annotationEntry::= identifier ( string_literal | double_quoted_literal )
                  | identifier

datatype::=
       'num'|'number'
       |'int'|'integer'
       |'d'|'date'
       |'ts'|'timestamp'
       |'tstz'|'tswtz'|'timestamp' 'with' 'local' 'time' 'zonechar'
       |'vc'|'varchar'|'varchar2'|'string'
       |'vc' integer | 'vc' '(' integer ')'
       | 'vc32k'
       | 'clob'|'blob'|'jsonfile'
       | 'geometry'|'sdo_geometry'

individual_setting::=
      ( 'apex'|'api'|'audit'
      |'cols'|'compress'|'createdbycol'|'createdcol'
      |'date'|'db'|'drop'
      |'language'|'longvc'
      |'ondelete'|'overridesettings'
      |'pk'|'prefix'|'prefixpkwithtname'
      |'genpk'
      |'resetsettings'|'rowkey'
      |'tenantid'|'rowversion'
      |'schema'|'semantics'
      |'transcontext'
      |'updatedbycol'|'updatedcolverbose' ) ':' (string_literal| 'true' | 'false')

Syntax Railroad Diagram