github.com/voedger/voedger@v0.0.0-20240520144910-273e84102129/design/schemas/syntax.md (about)

     1  # AppSchemas
     2  
     3  ## Contents
     4  - [Common](#common)
     5  - [Workspaces](#workspaces)
     6  - [Data Types](#data-types)
     7  - [DDL Statements](#ddl-statements)
     8      - [Sequences](#sequences)
     9      - [Types](#types)
    10      - [Tables](#tables)
    11      - [Singletones](#singletones)
    12      - [Triggers](#triggers)
    13      - [Commands](#commands)
    14      - [Queries](#queries)
    15      - [Views](#views)
    16      - [ACL](#acl)
    17  - [Notes](#notes)
    18  - [Documentation](#documentation)
    19  - [See Also](#see-also)
    20  
    21  ## Common
    22  ### SchemaFile Syntax:
    23  ```sql
    24  package_statement
    25  
    26  [import_statement [...]]
    27  
    28  [{root_ddl_statement | normal_ddl_statement} [...]]
    29  ```
    30  where:
    31  ```
    32  package_statement = SCHEMA name
    33  import_statement = IMPORT SCHEMA path [AS alias]
    34  root_ddl_statement = 
    35      template_statement
    36  normal_ddl_statement = 
    37      role_statement | workspace_statement | type_statement | 
    38      function_statement | sequence_statement | table_statement | 
    39      comment_statement | tag_statement
    40      
    41  ```
    42  
    43  ## Workspaces
    44  ### Syntax
    45  ```sql
    46  WORKSPACE name (
    47      [ { normal_ddl_statement | workspace_ddl_statement} [...] ]
    48  );
    49  ```
    50  
    51  where:
    52  ```
    53  workspace_ddl_statement = 
    54      grant_statement | command_statement | query_statement | view_statement |
    55      projector_statement | use_table_statement  | rate_statement |
    56      alter_table_statement | alter_query_statement | alter_command_statement
    57  
    58  ```
    59  
    60  ## Data Types
    61  ```sql
    62  data_type = ID | OFFSET | INT | INT32 | INT64 | FLOAT | FLOAT32 | FLOAT64 | QNAME | TEXT | BOOLEAN
    63  -- INT = INT32
    64  -- FLOAT = FLOAT32
    65  ```
    66  
    67  ## DDL Statements
    68  
    69  ### Sequences
    70  Syntax:
    71  ```sql
    72  SEQUENCE sequence_name AS data_type [WITH { optname[=optvalue] [AND ...] }];
    73  ```
    74  
    75  Example:
    76  ```sql
    77  SEQUENCE aricle_numbers as int;
    78  ```
    79  
    80  ### Types
    81  #### Syntax
    82  ```sql
    83  -- Create composite type
    84  TYPE name AS ( attribute [, ... ] ) 
    85      [WITH { optname[=optvalue] [AND ...] }];
    86  
    87  attribute = attribute_name { data_type | type_name }
    88      [NOT NULL] 
    89      [DEFAULT const_value ] 
    90      [REFERENCES table_name] 
    91      [CHECK(condition | 'regexp')]
    92  
    93  -- Create enumeration
    94  TYPE name AS ENUM
    95      ( [ 'label' [, ... ] ] ) 
    96      [WITH { optname[=optvalue] [AND ...] }];
    97  ```
    98  
    99  Example:
   100  ```sql
   101  -- Create composite type
   102  TYPE HasNameAndNumber AS (number int, name text);
   103  
   104  -- Create enumeration
   105  TYPE Reaction AS ENUM ('accept', 'reject', 'smile', 'sorrow');
   106  
   107  
   108  #### Arrays
   109  ???
   110  
   111  -- TYPE Weight AS (brutto int, netto int) CHECK (brutto <= netto);
   112  
   113  ```
   114  
   115  
   116  ### Tables
   117  
   118  #### Principles
   119  - table can inherit from one or more composite types. Table kind (CDOC, WDOC, ODOC) is a mandatory composite type providing 'sys.ID' field and 'sys.IsActive' for CDOC. Table kind is specified for root table only.
   120  - nested tables supported
   121  
   122  #### Syntax
   123  ```sql
   124  TABLE table_name [OF composite_type_name [, ...]] (
   125      { field_expr | inner_table_expr | table_constraint [, ...] }   
   126  ) [WITH { optname[=optvalue] [AND ...] }];
   127  
   128  where:    
   129  field_expr = field_name { data_type | type_name }
   130              [NOT NULL] 
   131              [VERIFIABLE]
   132              [DEFAULT const_value | NEXTVAL('sequence_name')] 
   133              [REFERENCES table_name] 
   134              [CHECK(condition | 'regexp')]
   135  
   136  table_constraint = CHECK(condition) | UNIQUE (field_name (',' field_name)*) 
   137  ```
   138  optname supported:
   139  - Description
   140  - Tags
   141  
   142  Example:
   143  ```sql
   144  TABLE articles OF CDOC, HasNameAndNumber (
   145      article_number  int NOT NULL DEFAULT NEXTVAL('article_numbers') CHECK(article_number>0),
   146      barcode         text NOT NULL,
   147      ean13barcode    text CHECK('^[0-9]{13}$')
   148      
   149      UNIQUE(article_number),
   150  
   151      TABLE article_prices OF IdName (
   152          id_articles     int64 REFERENCES articles,
   153          id_prices       int64 REFERENCES prices,
   154          price           float32 DEFAULT 1.00,
   155          UNIQUE(id_articles, id_prices)
   156      )
   157  ) WITH Description='Information about article';
   158  ```
   159  
   160  #### Limitations
   161  - References 
   162      - CDOC table can only reference to CDOC tables
   163  - Nested tables cannot override table kind
   164  
   165  ### Singletones
   166  #### Principles:
   167  - Singletone is always CDOC table under the hood
   168  - Singletone fields may only refer to other CDoc tables
   169  
   170  ```sql
   171  SINGLETONE table_name [OF composite_type_name [, ...]] (
   172      { field_expr | table_constraint [, ...] }   
   173  ) [WITH { optname[=optvalue] [AND ...] }];
   174  
   175  where:    
   176  field_expr = field_name { data_type | type_name }
   177              [NOT NULL] 
   178              [VERIFIABLE]
   179              [DEFAULT const_value | NEXTVAL('sequence_name')] 
   180              [REFERENCES table_name] 
   181              [CHECK(condition | 'regexp')]
   182  
   183  table_constraint = CHECK(condition) | UNIQUE (field_name (',' field_name)*) 
   184  ```
   185  
   186  ### Triggers
   187  #### Principles
   188  
   189  - BEFORE = validator
   190  - AFTER = projectors
   191  
   192  #### Syntax
   193  ```sql
   194  TRIGGER name { BEFORE|AFTER } 
   195      { event [ OR ...] } 
   196      ON { table_name | command_name }
   197      EXECUTE PROCEDURE function_name 
   198      ENGINE { WASM | BUILTIN }
   199      [WITH { optname[=optvalue] [AND ...] }];
   200  
   201  where:
   202  event = INSERT | UPDATE
   203  ```
   204  
   205  #### Example Validator:
   206  ```sql
   207  TRIGGER ValidateArticle BEFORE INSERT OR UPDATE ON articles EXECUTE PROCEDURE air.ValidateArticle ENGINE WASM;
   208  ```
   209  
   210  ####  Example Projector:
   211  ```sql
   212  TRIGGER AirDashboardProjector AFTER INSERT ON air.PBill 
   213  EXECUTE PROCEDURE air.DashboardProjector ENGINE WASM
   214  WITH HandleErrors=true AND Description='';
   215  ```
   216  
   217  #### Notes
   218  - `WITH HandleErrors=true` - only for `AFTER`, indicates that events with errors must be handled
   219  
   220  ### Commands
   221  
   222  #### Syntax
   223  ```sql
   224  COMMAND name ([[argname] argtype [, ...]]) 
   225      [RETURNS argtype]
   226      ENGINE { WASM | BUILTIN }
   227      [WITH { optname[=optvalue] [AND ...] }];
   228  
   229  argtype = "sys.Json" | data_type | type_name | table_name
   230  ```
   231  
   232  #### Examples
   233  ```sql
   234  COMMAND mycommand(untill.pbill) RETURNS sys.Json ENGINE WASM 
   235  WITH Description='This is my first command' AND Rate='1/HOUR';
   236  ```
   237  
   238  ### QUERIES
   239  #### Syntax
   240  ```sql
   241  QUERY name ([[argname] argtype [, ...]]) 
   242      RETURNS argtype
   243      ENGINE { WASM | BUILTIN }
   244      [WITH { optname[=optvalue] [AND ...] }];
   245  
   246  argtype = "sys.Json" | data_type | type_name | table_name
   247  ```
   248  #### Examples
   249  ```sql
   250  QUERY myquery(untill.pbill) RETURNS sys.Json ENGINE WASM 
   251  WITH Description='This is my first query' AND Rate='100/MINUTE';
   252  ```
   253  
   254  
   255  ### Views
   256  #### Principles
   257  - Views are always "materialized"
   258  - `AS SELECT...` means that a Projector will be created by core which "meterializes" it.
   259  
   260  #### Syntax
   261  ```sql
   262  VIEW name(column_name[, ...]) 
   263      [AS {SELECT ... | RESULT OF projector_name}  ]
   264      [WITH { optname[=optvalue] [AND ...] }];
   265  ```
   266  
   267  #### Examples
   268  ```sql
   269  VIEW channel_messages(is_channel_message, reactions, firstReactors, replies, lastRepliers) 
   270  AS SELECT is_channel_message
   271     ,(SELECT kind, COUNT() FROM reactions GROUP BY kind)
   272     ,(SELECT FIRST(10) author DISTINCT(author) FROM reactions)
   273     ,(SELECT COUNT() FROM threads)
   274     ,(SELECT LAST(3) author DISTINCT(author) FROM messages)
   275  FROM messages 
   276  WHERE messages.is_channel_message = true
   277  ORDER BY messages.id;
   278  
   279  
   280  VIEW XZReports(
   281      Year int32, 
   282      Month int32, 
   283      Day int32, 
   284      Kind int32, 
   285      Number int32, 
   286      XZReportWDocID id,
   287      PRIMARY KEY((Year), Month, Day, Kind, Number)
   288  ) AS RESULT OF UpdateXZReportsView;
   289  ```
   290  
   291  ### ACL
   292  #### Syntax
   293  ```sql
   294  GRANT {{ SELECT | INSERT | UPDATE } | ALL } ON { TABLE [WITH TAG tagname] } TO role_name
   295  GRANT {{ EXECUTE } | ALL } ON { COMMAND | QUERY [WITH TAG tagname] } TO role_name
   296  ```
   297  
   298  ## Notes
   299  - ??? Support for multiline strings
   300  
   301  ## Documentation
   302  - PostgreSQL syntax
   303      - https://www.postgresql.org/docs/current/ddl-basics.html    
   304      - https://www.postgresql.org/docs/current/sql-createtable.html
   305      - https://www.postgresql.org/docs/current/sql-creatematerializedview.html
   306      - https://www.postgresql.org/docs/15/sql-createfunction.html
   307  - [Oracle: Types of SQL Statements](https://docs.oracle.com/cd/B14117_01/server.101/b10759/statements_1001.htm)
   308  - [Cassandra: create table WITH table_options](https://docs.datastax.com/en/cql-oss/3.3/cql/cql_reference/cqlCreateTable.html#table_options)
   309  ## See Also
   310  - https://github.com/heeus/inv-go/blob/master/20220221-parsing/participle/schema.sql
   311  - https://github.com/heeus/heeus-design/blob/main/20220414-slack-wdocs/slack-sql-syntax.md
   312  - https://github.com/heeus/core/blob/ea31af585e5519673be8ff6e489d1afabe6364d8/istructsmem/schema-utils.go#L90
   313  - [API v2](https://dev.heeus.io/launchpad/#!23905)