Switch to: V11V10V9V8V7V6V5

Valentina SQL GRAMMAR v5.x

Changes

  • (!!!) register/unregister db/project rules use IDENT for db/project names instead of literals in v4.x;
  • (!!!) DEPRECATED: select_sublist does not allow character_string_literal after AS; Must be used without quotes for single-word name or double quotes for few words name. Parser 5.0 still allow single quotes, but this is deprecated. You have time to correct your queries.
  • CREATE INDEX now works for few columns. But UINT limit of column length still is ignored.
  • For consistency renamed result fields in commands SHOW LINKS, SHOW TRIGGERS. You may need correct your code and/or database objects (stored procedures, triggers) in case you use these commands in your application.
  • SELECT … FOR REPORT now returns TEXT field if text is produced.

New

  • CREATE TYPE AS ENUM
  • DROP TYPE
  • ALTER TYPE
  • CREATE SEQUENSE
  • DROP SEQUENCE
  • SERIAL32 and SERIAL64 pseudo-types of columns
  • ALTER LINK … RENAME
  • ALTER LINK … REFERENTIAL ACTION
  • SHOW CHECKS
  • SHOW CONECTIONS
  • SHOW CONSTRAINTS
  • SHOW INDEXES
  • SHOW LOGS
  • SHOW [VSERVER] LOG
  • SHOW SEQUENCES
  • SHOW TYPES
  • CREATE TRIGGER … ON DATABASE
  • MAIL command
  • SELECT … FOR JSON [v5.5]

Improvements

  • vext_recursive_table rule now can do USING search_condition.
  • vext_recursive_table rule now have WITH COLUMNS optional suffix.
  • SET PROPERTY now can accept bind_value.
  • Most functions now use call_arg, this allows to write “foo( param, NULL, param )”.
  • vext_database_clone now have suffix [WITH vext_set_property] like next_database_create.
  • LINK/UNLINK commands now have suffix [IF NOT EXISTS].
  • CREATE BINARY LINK now will NOT accept SET NULL, SET DEFAULT and NO ACTION actions for ON DELETE. But added NO CASCADE action. See vext_referential_action_for_blink rule.
  • DEFAULT clause of a field in the CREATE TABLE, now has “DEFAULT METHOD('expression')” form.
  • ALTER TABLE … DROP COLUMN has now [IF EXISTS].
  • ALTER TABLE … DROP CONSTRAINT has now [IF EXISTS].
  • SELECT … FOR REPORT … [ENCODE BASE64] – to encode binary format PDF/JPG reports into text result to use, e.g. in emails.

Entry Statements

sql
    :    sql_statement [; sql_statement ]* eof 

sql_statement
    :    sql_schema_statement 
    |    sql_data_statement 
    |    sql_transaction_statement 
    |    valentina_extensions 
    |    valentina_extensions_events
    |    valentina_extensions_database

sql_schema_statement
    :    sql_schema_definition_statement 
    |    sql_schema_manipulation_statement 

sql_data_statement
    :	 direct_select_statement_multiple_rows 
    |    sql_data_change_statement 
    |    with_common_table_expression

sql_transaction_statement
    :	 set_transaction_statement 
    |    commit_statement 
    |    rollback_statement 

valentina_extensions
    :    vext_database
    |    vext_links
    |    vext_print_expr
//  |    vext_set_property           -- MOVED TO sql_data_change_statement rule
//  |    vext_get_property           -- MOVED TO query_primary rule
//  |    vext_show_statement         -- MOVED TO query_primary rule
    |    vext_vserver                -- v4.0
    |    vext_utilities

Schema Definition

sql_schema_definition_statement
    :    function_definition
    |    index_definition
    |    privilege_definition
    |    procedure_definition 
    |    sequence_definition
    |    table_definition
    |    trigger_definition 
    |    type_definition
    |    vext_binary_link_definition
    |    view_definition 
    |    user_definition
vext_binary_link_definition
    :    CREATE [OR REPLACE] BINARY LINK [IF NOT EXISTS] link_name 
           ON TABLES ( table_name , table_name ) 
           AS link_type TO link_type 
           [referential_triggered_action_for_link]
           [OWNER table_name]

link_type
    :    ONE | MANY | UINT

referential_triggered_action_for_link
    :    ON DELETE vext_referential_action_for_link

vext_referential_action_for_link
    :    RESTRICT | NO ACTION | CASCADE | NO CASCADE

Index Definition

index_definition
    :    CREATE [UNIQUE] INDEX index_name ON table_name index_column_list 

Privileges Definition

Added in v4.0

privilege_definition
    :    GRANT privileges TO grantee_list 

privileges
    :    object_privileges ON [object_type] object_name

object_privileges
    :    { ALL | privilege_list }  [PRIVILEGES]		

privilege_list
    :    privilege [, ...]

privilege
    :    NONE | READ | UPDATE | DELETE | ALTER

object_type
    :    DATABASE | PROJECT

object_name
    :    IDENT.*

grantee_list
    :    character_string_literal [, ...]

revoke_privilege_statement
    :    REVOKE PRIVILEGES FROM grantee_list	

Procedure/Function Definition

procedure_definition
    :    CREATE [OR REPLACE] PROCEDURE proc_name ( [proc_parameter [, ...]] )
         compound_statement

proc_parameter : [ IN | OUT | INOUT ] param_name data_type
function_definition
    :    CREATE [OR REPLACE] FUNCTION func_name ( [func_parameter [, ...]] )
         RETURNS data_type
         compound_statement

func_parameter : param_name data_type

Compound Statement

compound_statement
    :    BEGIN 
             statement_list 
             [exception_statement]
         END

statement_list
    :    ( statement_in_cs )*

statement_in_cs
    :    compound_statement
    |    control_statement
    |    local_declaration SEMI
    |    raising_error SEMI
    |    routine_statement SEMI

local_declaration
    :    cursor_def
    |    variable_declaration

routine_statement
    :    sql_statement
    |    cursor_open
    |    cursor_close
    |    cursor_fetch
    |    exec_statement
	
control_statement
    :    flow_control_statement
    |    return_statement SEMI
exec_statement
    :    EXECUTE variable_name [USING variable_name_list]
variable_declaration
    :    DECLARE variable_name_list data_type [default_clause]

assign_statement
    :    [SET] variable_name_list { = | := } query_expression
    |    column_reference { = | := } { NULL | expr }
raising_error
    :   {RAISE | RAISEERROR} [exception_error_code [, character_string_literal]]

exception_statement
    :    EXCEPTION
           [ WHEN exception_error_code [ OR exception_error_code ... ] THEN
               statement_list
           ... ]
           [ WHEN OTHERS THEN
               statement_list ]

exception_error_code
    :    UINT_HEX | err_name

Flow Control

flow_control_statement
    :    if_statement
    |    case_statement
    |    while_statement
    |    repeat_statement
    |    loop_statement
    |    iterate_statement
    |    leave_statement

if_statement
    :    IF search_condition THEN statement_list 
         { ELSEIF search_condition THEN statement_list }*
         [ ELSE statement_list ]
         END IF

case_statement
    :    CASE case_operand
         	{ WHEN when_operand THEN statement_list }+
         	[ ELSE statement_list ]
         END CASE 
         
    |    CASE
         	{ WHEN search_condition THEN statement_list }+
         	[ ELSE statement_list ]
         END CASE 

while_statement
    :    WHILE search_condition DO 
             statement_list 
         END WHILE

repeat_statement
    :    REPEAT 
             statement_list 
         UNTIL search_condition 
         END REPEAT

loop_statement
    :    LOOP 
             statement_list 
         END LOOP

iterate_statement
    :    ITERATE 

leave_statement
    :    LEAVE

Cursor Definition

cursor_def
    :    DECLARE cursor_name CURSOR FOR 
         {    variable_name  -- v4.0
         |    direct_select_statement_multiple_rows
         }

cursor_open
    :    OPEN cursor_name
    
cursor_close
    :    CLOSE cursor_name
    
cursor_fetch
    :    FETCH [fetch_direction] [FROM] cursor_name INTO variable_name_list
    
fetch_direction    
    :    NEXT  | FORWARD
    |    PRIOR | BACKWARD
    |    FIRST
    |    LAST
    |    ABSOLUTE count
    |    RELATIVE count

Sequence Definition

sequence_definition
    :    CREATE [TEMPORARY] SEQUENCE sequence_name
         [ INCREMENT [BY] increment ]
         [ MINVALUE minvalue | NO MINVALUE ] 
         [ MAXVALUE maxvalue | NO MAXVALUE ]
         [ START [WITH] start ] 
         [ [NO] CYCLE ]

Table Definition

table_definition
    :    CREATE [OR REPLACE] [ {GLOBAL | LOCAL} TEMPORARY] {[RAM] | [SYSTEM]}
         TABLE [IF NOT EXISTS] table_name
         { table_element_list [ON COMMIT {DELETE | PRESERVE} ROWS ]
         | AS direct_select_statement_multiple_rows 
         }         

table_element_list
    :    ( table_element, ... ) 

table_element
    :    column_definition 
    |    table_constraint_definition 

column_definition
    :    column_name data_type [ default_clause ] [ column_constraint, ... ]

column_constraint
    :    [ CONSTRAINT constraint_name ] 
         {    NOT NULL 
         |    NULL 
         |    INDEXED 
         |    WORDS 
         |    COMPRESSED 
         |    TEMPORARY                                       (v4.0)
         |    { PRIMARY KEY | UNIQUE } 
         |    { AUTOINCREMENT | AUTO_INCREMENT }
         |    IDENTITY  
         |    CHECK ( search_condition ) 
         |    references_specification 
         |    METHOD( STRING_LITERAL )  
         } 

table_constraint_definition
    :    [ CONSTRAINT constraint_name ] 
            {   { UNIQUE [ index_name ]  |  PRIMARY KEY } index_column_list 
            |    FOREIGN KEY ( column_name_list ) references_specification  
            |    CHECK ( search_condition )
            |    INDEX [ index_name ] ( column_name [(UINT)] , ... ) 
            }

default_clause
    :    DEFAULT default_option

default_option
    :    NULL
    |    USER | CURRENT_USER | SESSION_USER | SYSTEM_USER 
    |    METHOD( STRING_LITERAL )
    |    literal 

references_specification
    :    REFERENCES table_name [ ( column_name_list ) ]
         [ MATCH {FULL | PARTIAL} ] 
         [ ON DELETE referential_action ]
         [ ON UPDATE referential_action ]

referential_action
    :    CASCADE | SET NULL | SET DEFAULT | NO ACTION | RESTRICT

Column Types Definition

data_type
    :    character_string_type 
    |    numeric_type 
    |    datetime_type 
    |    blob_type 
    |    objectptr_type

character_string_type
    :    { CHARACTER | CHAR } [VARYING] [ ( max_size ) ] 
    |    { VARCHAR | STRING } ( max_size ) 

numeric_type
    :    exact_numeric_type 
    |    approximate_numeric_type 

datetime_type
    :    DATE 
    |    { DATETIME | TIMESTAMP } [ ( timestamp_precision ) ] [ WITH TIME ZONE ] 
    |    TIME [ ( time_precision ) ] [ WITH TIME ZONE ]            

blob_type
    :    { FIXEDBINARY | VARBINARY } ( max_size )
    |    { BLOB | PICTURE } ( segment_size ) 
    |    TEXT ( segment_size ) 

objectptr_type
    :    OBJECTPTR

exact_numeric_type
    :    
    {   BOOLEAN 
        | BYTE     |  UCHAR 
        | SHORT    | USHORT | SMALLINT
        | MEDIUM   | UMEDIUM  
        | LONG     | ULONG  | INTEGER | INT
        | LLONG    | ULLONG  
        | SERIAL32 | SERIAL64
    }    
    |   { NUMERIC | DECIMAL | DEC }  [ ( precision [, scale ] ) ] 

approximate_numeric_type
    :    FLOAT  [ ( precision [, scale ] ) ] 
    |    DOUBLE [ ( precision [, scale ] ) ]
    |    REAL  

Trigger Definition

trigger_definition
    :    CREATE [OR REPLACE] TRIGGER [IF NOT EXISTS] trigger_name
         trigger_action_time 
         { trigger_dml_event_clause | trigger_db_event_clause | trigger_ddl_event_clause }
         compound_statement      

trigger_action_time 
    :    BEFORE | AFTER | INSTEAD OF
trigger_dml_event_clause
    :    trigger_dml_event_list ON table_or_view_name
         [REFERENCING [NEW [AS] new_row_name] [OLD [AS] old_row_name] ]
         FOR EACH { ROW [WHEN condition] | STATEMENT }   

trigger_dml_event_list
    :    trigger_dml_event [ {OR|,} trigger_dml_event ]* 
       
trigger_dml_event 
    :    DELETE | INSERT | UPDATE [OF column_list]
trigger_db_event_clause
    :    trigger_db_event_list ON DATABASE
         
trigger_db_event_list
    :    trigger_db_event [ {OR|,} trigger_db_event ]* 
       
trigger_db_event 
    :    STARTUP | SHUTDOWN | LOGON | LOGOFF 
trigger_ddl_event_clause
    :    trigger_ddl_event_list ON DATABASE
         
trigger_db_event_list
    :    trigger_ddl_event [ {OR|,} trigger_ddl_event ]* 
       
trigger_ddl_event 
    :    ALTER | CREATE | DROP | GRANT | DDL 

Type Definition

user_defined_type_definition
    :    CREATE [OR REPLACE] TYPE [IF NOT EXISTS] type_name 
             AS {ENUM | ENUM8 | ENUM16} character_string_literal_list [, locale_character_string_literal_lists]

character_string_literal_list
    :    ( [character_string_literal, ...] )

locale_character_string_literal_lists
    :    locale_character_string_literal_list [, ...]

locale_character_string_literal_list
    :    locale_name COLON character_string_literal_list

User Definition

Added in v4.0.

user_definition
    :    CREATE USER single_user_def [,...] 

single_user_def
    :    user_identifier [IDENTIFIED BY [PASSWORD] character_string_literal]  [with_option]

with_option
    :    WITH ADMIN OPTION

View Definition

view_definition
    : CREATE [OR REPLACE] VIEW view_name [ (column_name_list) ] AS query_expression [ WITH CHECK OPTION ]

Schema Manipulation

sql_schema_manipulation_statement
    :    alter_table_statement 
    |    alter_trigger_statement
    |    alter_type_statement
    |    alter_view_statement
    |    vext_alter_link_statement

    |    drop_index_statement
    |    drop_procedure_statement
    |    drop_sequence_statement
    |    drop_table_statement 
    |    drop_trigger_statement
    |    drop_type_statement
    |    drop_view_statement
    |    drop_user_statement
    |    vext_drop_link_statement

    |    revoke_privilege_statement

ALTER Statements

alter_table_statement
    :    ALTER TABLE table_name alter_table_action [ , alter_table_action ... ]

alter_table_action
    :    ADD [COLUMN]   column_definition  
    |    ADD [COLUMN] ( column_definition [, column_definition ... ] )

    |    ADD   table_constraint_definition 
    |    ADD ( table_constraint_definition [, table_constraint_definition ... ] ) 

    |    ALTER [COLUMN] column_name alter_column_action
    |    DROP  [COLUMN] [IF EXISTS] column_name [drop_behavior]

    |    DROP PRIMARY KEY  
    |    DROP INDEX index_name 
    |    DROP CONSTRAINT [IF EXISTS] IDENT 

    |    CHANGE [COLUMN] old_col_name column_definition 
    |    MODIFY [COLUMN] column_definition 
    |    RENAME [AS] table_name 

alter_column_action
    :    SET default_clause 
    |    DROP DEFAULT 
    |    RENAME [AS] column_name
alter_view_statement
    :    ALTER VIEW view_name [ (column_name_list) ] AS query_expression [WITH CHECK OPTION]
alter_trigger_statement
    :    ALTER TRIGGER trigger_name { ENABLE | DISABLE }
alter_type_statement
    :    ALTER TYPE type_name alter_type_action

alter_type_action
    :    RENAME {TO | AS} type_name

    |    ADD VALUE  character_string_literal [, locale_name COLON character_string_literal ... ]
    |    ADD VALUES 
         { character_string_literal_list [, locale_character_string_literal_lists]
         | locale_character_string_literal_lists
         }

    |    CHANGE VALUE [locale_name COLON] character_string_literal TO character_string_literal

    |    DROP VALUES locale_name
alter_link_statement
    :    ALTER LINK link_name alter_link_action

alter_link_action
    :    RENAME [AS] link_name
    |    ALTER referential_triggered_action_for_link

DROP Statements

drop_behavior
    :    CASCADE | RESTRICT

drop_index_statement
    :    DROP INDEX index_name ON table_name 

vext_drop_link_statement
    :    DROP LINK [IF EXISTS] link_name 

drop_procedure_statement
    :    DROP PROCEDURE [IF EXISTS] proc_name

drop_sequence_statement
    :    DROP SEQUENCE [IF EXISTS] sequence_name

drop_table_statement
    :    DROP TABLE [IF EXISTS] table_name [ drop_behavior ] 

drop_trigger_statement
    :    DROP TRIGGER [IF EXISTS] trigger_name 

drop_type_statement
    :    DROP TYPE [IF EXISTS] type_name 

drop_view_statement
    :    DROP VIEW [IF EXISTS] view_name 

drop_user_statement
    :    DROP USER user_identifier [,...]

Data Manipulation

sql_data_change_statement
    :    insert_statement
    |    delete_statement_searched
    |    update_statement_searched
    |    call_statement
    |    assign_statement
    |    vext_set_property
    |    vext_link_records
    |    vext_unlink_records
insert_statement
    :    INSERT [INTO] table_name insert_columns_and_source
    |    INSERT INTO LINK link_name VALUES ( UINT, UINT )

insert_columns_and_source
    :    DEFAULT VALUES
    |    ( column_name_list )
    |    query_expression

delete_statement_searched
    :    DELETE FROM table_name [ WHERE search_condition ]

update_statement_searched
    :    UPDATE table_name SET set_clause [, set_clause ...]  
	     [ WHERE search_condition ]

set_clause
    :    column_name = { DEFAULT | NULL | expr }
call_statement
    :    CALL proc_name ( call_arg [, ...] ) 

call_arg
    :    expr | NULL
vext_link_records
    :    LINK  RECORD[S] list_of_records OF table_name WITH 
         [EACH]RECORD[S] list_of_records OF table_name 
         [USING link_name]
         [IF NOT EXISTS]             // this suffix added into v5.0

vext_unlink_records
    :    UNLINK RECORD[S] v_list_of_records   OF table_name FROM 
          [EACH]RECORD[S] [v_list_of_records] OF table_name 
          [USING link_name]          // v4.7, improved 
          [IF  EXISTS]               // this suffix added into v5.0

v_list_of_records
    :    ( v_link_value [, v_link_value ...] )
    |    subquery 

v_link_value
    :    UINT
    |    dynamic_parameter_specification 
    |    column_reference                                                -- v5.0, allows triggers begin Link RECORD(recID) OF T1... end
                                                                         -- also (@var)

Queries

direct_select_statement_multiple_rows
    :    query_expression 
             [ORDER BY sort_specification_list] 
             [vext_select_limit] 
             [vext_for_xml]
             [vext_for_json]
             [vext_for_report]

query_expression
    :    query_term  [ {UNION | EXCEPT} [ ALL ] query_term ]*  

query_term
    :    query_primary  [ INTERSECT [ ALL ] query_primary ]*

query_primary
    :    VALUES table_value_constructor_list 
    |    TABLE table_name 
    |    LINK link_name                       // vext, added in v4.7
    |    vext_get_property
    |    vext_show_statement
    |    vext_recursive_table
    |    table_reference 
    |    SELECT [ALL | DISTINCT] select_list [INTO variable_name_list] [table_expression] 

table_expression
    :   FROM table_reference [, table_reference ]*  
            [ WHERE search_condition ]
            [ GROUP BY grouping_column_reference_list [WITH ROLLUP] ]
            [ HAVING search_condition ]
vext_recursive_table         -- v4.1
    :    {ANCESTORS | BROTHERS | DESCENDANTS } OF vext_root_objects 
         [{TO|ON} LEVEL uint_or_var] 
         USING ( link_name | search_condition )
         [WITH {ROOT | ROOTS}]
         [WITH {COLUMN | COLUMNS} vext_pseudo_column, ... ]          // added in v5.0

vext_root_objects
    :    UINT
    |    variable_name
    |    ( search_condition )

vext_pseudo_column
    :  {LEVEL | IS_LEAF} [[AS] IDENT]

uint_or_var
    :    UINT
    |    variable_name
table_reference
    :    non_join_table [[NATURAL][join_type] JOIN non_join_table [join_specification]]*
    |    pivoted_table

non_join_table
    :	table_name [ [ AS ] IDENT ]
    |	subquery   [ [ AS ] IDENT ]

subquery
    :    ( query_expression )

join_type
    :    { LEFT | RIGHT | FULL } [ OUTER ] 
    |    INNER 

join_specification
    :    ON ( search_condition | link_name [TO (ParentDirection | ChildDirection)] ) 
    |    USING ( column_name_list )
pivoted_table
    :    table_reference PIVOT pivot_clause AS table_alias
 
pivot_clause
    :    (    aggregate_function ( value_column )
              FOR column_name 
              IN ( column_list )
         )
table_value_constructor_list
    :    row_expr, ... 

row_expr
    :    row_list_element
    |    ( row_list_element, ... ) 
              
row_list_element
    :    expr | NULL | DEFAULT 

Predicates

search_condition
    :	boolean_term [ OR boolean_term ]*

boolean_term
    :	boolean_factor [ and boolean_factor ]* 

boolean_factor
    :	[ NOT ] boolean_test 

boolean_test
    :	boolean_primary [ IS [ NOT ] truth_value ] 
                
boolean_primary
    :	row_expr comp_op row_expr ) 
    |	row_expr [ NOT ] BETWEEN row_expr and row_expr
    |	row_expr [ NOT ] IN in_predicate_value
    |	row_expr IS [ NOT ] NULL
    |	row_expr OVERLAPS row_expr
    |	row_expr MATCH [ UNIQUE ] [PARTIAL | FULL ] subquery 
    |	row_expr comp_op { ALL | SOME | ANY} subquery 

    |	EXISTS subquery 
    |	UNIQUE subquery 

    |	expr [ NOT ] LIKE expr [ ESCAPE expr ] 
    |	expr [ NOT ] REGEX expr 

    |	( search_condition )

in_predicate_value
    :	subquery 
    |	( expr, ... )  

comp_op
    :	=  |  {}  |  {  |  }  |  {=  |  }=  |  *= |  =*  |  *=*

truth_value
    :	TRUE | FALSE | UNKNOWN

Expressions

expr
    :	term [ {  +  |  -  |  ||  } term ]*

term
    :	factor ( ( * | / | % ) factor )* 

factor
    :	[ + | - ] primary 

primary
    :	IDENT ( [ expr [, expr ]* ] ) 
    |	value_expression_primary 
    |	numeric_value_function 
    |	string_value_function 

value_expression_primary
    :	unsigned_value_specification 
    |	truth_value 
    |	column_reference 
    |	set_function_specification 
    |	case_expression 
    |	cast_specification 
    |	subquery 
    |	( expr ) 
cast_specification
    :	CAST ( {expr | NULL} AS data_type ) 
case_expression
    :	case_abbreviation 
    |	case_specification 

case_abbreviation
    :	NULLIF ( expr , expr ) 
    |	COALESCE ( expr [, expr]* ) 

case_specification
    :	CASE { simple_case | searched_case } END 

simple_case
    :	expr ( WHEN expr THEN result  )+ [ ELSE result ] 

searched_case
    :	{ WHEN search_condition THEN result }+  [ ELSE result ] 

result
    :	expr | NULL 
set_function_specification
    :	COUNT ( * ) 
    |	{ AVG | MAX | MIN | SUM | COUNT } ( [ALL | DISTINCT] expr )  
    |	{ STDDEV |  STDDEV_POP }          ( [ALL | DISTINCT] expr )
    |	{ CORR | COVAR }                  ( expr )  
    |	{ BIT_AND | BIT_OR | BIT_XOR }    ( expr )

numeric_value_function
    :	POSITION ( expr IN expr ) 
    |	length_expression 

length_expression
    :	{CHAR_LENGTH | CHARARACTER_LENGTH} ( expr ) 
    |	OCTET_LENGTH ( expr )  
    |	BIT_LENGTH ( expr )
 
string_value_function
    :	SUBSTRING ( expr FROM expr [ FOR expr ] ) 
    |	{UPPER | LOWER} ( expr ) 
    |	 TRIM ( [LEADING | TRAILING | BOTH] expr [ FROM expr ]  )

datetime_value_function
    :	CURRENT_DATE 
    |	CURRENT_TIME  [ ( time_precision ) ] 	 
    |	CURRENT_TIMESTAMP [ ( timestamp_precision ) ] 

Transactions

set_transaction_statement
    :	set transaction transaction_mode, ...  

commit_statement
    :	commit [ work ] 

rollback_statement
    :	rollback [work] 

transaction_mode
    :	isolation level level_of_isolation 
    |	transaction_access_mode 

level_of_isolation
    :	read committed 
    | 	READ uncommitted 
    |	repeatable read 
    |	serializable

transaction_access_mode
    :	read only | READ write 

Valentina Extensions

Database

vext_database
    :    vext_database_backup
    |    vext_database_clone
    |    vext_database_create
    |    vext_database_drop
    |    vext_database_use

vext_database_backup
    :    BACKUP [DATABASE] [TO path_name] [WITH vext_diagnose]
	
vext_database_clone
    :    CLONE DATABASE [DATA | STRUCTURE] TO path_name [WITH vext_set_property]

vext_database_create
    :    CREATE DATABASE [IF NOT EXISTS] db_name [WITH vext_set_property]

vext_database_drop
    :    DROP DATABASE [IF EXISTS] db_name

vext_database_use
    :    USE [DATABASE] db_name
vext_links
    :    vext_copy_links
//  |    vext_create_binary_link     -- MOVED TO sql_schema_definition_statement.
//  |    vext_drop_link              -- MOVED TO sql_schema_manipulation_statement.
//  |    vext_link_records           -- MOVED TO sql_data_change_statement rule.
//  |    vext_unlink_records         -- MOVED TO sql_data_change_statement rule.

vext_copy_links
    : COPY LINKS FROM link1_name TO link2_name
    | COPY LINKS FROM link1_name TO link2_name AND link3_name
    | COPY LINKS FROM link1_name AND link2_name TO link3_name

Properties

vext_get_property
    :    GET PROPERTY property_name_list OF DATABASE
    |    GET PROPERTY property_name_list OF TABLE table_name 
    |    GET PROPERTY property_name_list OF FIELD column_refernce 
    |    GET PROPERTY property_name_list OF LINK link_name 
    |    GET PROPERTY property_name_list OF SERVER 
    |    GET PROPERTY property_name_list OF CONNECTION
    |    GET PROPERTY property_name_list 

vext_set_property
    :    SET PROPERTY property_name_list OF DATABASE TO property_value_list
    |    SET PROPERTY property_name_list OF TABLE table_name TO property_value_list
    |    SET PROPERTY property_name_list OF FIELD column_refernce TO property_value_list
    |    SET PROPERTY property_name_list OF LINK link_name TO property_value_list
    |    SET PROPERTY property_name_list OF SERVER TO property_value_list
    |    SET PROPERTY property_name_list OF CONNECTION TO property_value_list
    |    SET PROPERTY property_name_list TO property_value_list

show_properties
    |    SHOW PROPERTIES 
    |    SHOW PROPERTIES OF DATABASE
    |    SHOW PROPERTIES OF TABLE table_name
    |    SHOW PROPERTIES OF FIELD column_refernce
    |    SHOW PROPERTIES OF LINK link_refernce
    |    SHOW PROPERTIES OF SERVER
    |    SHOW PROPERTIES OF CONNECTION

property_name_list
    :    property_name, ...

property_value_list
    :    property_value, ...

property_value
    :    character_string_literal
    |    signed_numeric_literal
    |    variable_name 
    |    dynamic_parameter_specification 
    |    TRUE
    |    FALSE
    |    NULL

SELECT Command

SELECT ... LIMIT

vext_select_limit
    :    LIMIT UINT [, UINT]         -- LIMIT [offset,] rows
    |    LIMIT UINT OFFSET UINT      -- LIMIT rows OFFSET offset 

SELECT ... FOR JSON

vext_for_json                                           -- v5.5
    :    FOR JSON

SELECT ... FOR REPORT

vext_for_report                                         -- v4.0
    :    FOR REPORT project_name.report_name
             [AS {PDF | HTML | JPG}]
             [PAGES StartPage TO EndPage] 
             [ENCODE BASE64]                            -- v5.0

SELECT ... FOR XML

vext_for_xml                                            -- v4.0
    :    FOR XML {RAW | AUTO} 
         [,] {ELMENT | ELEMENTS}
         [ [WITH | CDATA] CDATA ]

SHOW Commands

vext_show_statement
    :    SHOW DATABASES 
    |    SHOW PROJECTS

    |    SHOW EVENTS      {FROM|OF} db_name
    |    SHOW PROCEDURES [{FROM|OF} db_name] 
    |    SHOW SEQUENCES  [{FROM|OF} db_name] 
    |    SHOW TABLES     [{FROM|OF} db_name] 
    |    SHOW TYPES      [{FROM|OF} db_name] 

    |    SHOW COLUMNS     {FROM|OF} table_name  [{FROM|OF} db_name]
    |    SHOW LINKS      [[{FROM|OF} table_name] {FROM|OF} db_name] 
    |    SHOW TRIGGERS   [[{FROM|OF} table_name] {FROM|OF} db_name] 

    |    SHOW CONSTRAINTS {FROM|OF} table_name [{FROM|OF} db_name]
    |    SHOW CHECKS      {FROM|OF} table_name [{FROM|OF} db_name]
    |    SHOW INDEXES     {FROM|OF} table_name [{FROM|OF} db_name]

    |    SHOW CONNECTIONS
    |    SHOW USERS

    |    SHOW LOGS
    |    SHOW [VSERVER] LOG size [,offset [FROM {START|END}]] [FOR log_name]

    |    show_properties
    |    show_status_statement 

show_status_statement
    :    SHOW STATUS
    |    SHOW STATUS {OF|FOR} SERVER
    |    SHOW STATUS {OF|FOR} DATABASE [db_name]
    |    SHOW STATUS {OF|FOR} {TABLE | LINK | INDEX} [name]
    |    SHOW STATUS {OF|FOR} {FIELD | COLUMN} tbl_name.fld_name

Utility Commands

vext_utilities
    :    vext_compact
    |    vext_defragment 
    |    vext_diagnose 
    |    vext_load_data
    |    vext_mail
    |    vext_repair 
    |    vext_reindex 
vext_compact
    :    COMPACT 
vext_defragment
    :    DEFRAGMENT
vext_diagnose
    :    DIAGNOSE DATABASE [ verbose_level ]
    |    DIAGNOSE TABLE table_name_list [ verbose_level ]
    |    DIAGNOSE FIELD table_dot_column_list [ verbose_level ]

verbose_level
    :    [VERBOSE] { NONE | LOW | NORMAL | HIGH | VERYHIGH }
vext_load_data                                         -- v4.0
    :    LOAD DATA [LOCAL] INFILE path
              [REPLACE | IGNORE]
              INTO TABLE table_name
              [CHARACTER SET encoding_name]  
              [ {FIELDS | COLUMNS} TERMINATED BY string_literal]
              [ LINES TERMINATED BY string_literal]
              [ IGNORE UINT LINES ]
              [ vext_col_name_or_user_var, ... ]

vext_col_name_or_user_var                             -- v4.0
    :    column_name
    |    variable_name
    |    NULL
vext_mail                                            -- v5.0 - EXPERIMENTAL
    :    __MAIL
         __FROM         character_string_literal_or_var 
         __TO           character_string_literal_or_var	 
         __SUBJECT      character_string_literal_or_var
         __BODY         character_string_literal_or_var
        [__ATTACH       vext_attach_list]
            __SMTP      character_string_literal_or_var
            __PORT      character_string_literal_or_var
            [__USER     character_string_literal_or_var,
             __PASSWORD character_string_literal_or_var]
            [__SSL      truth_value_or_var]

vext_attach_list
    :    character_string_literal_or_var AS character_string_literal_or_var , ...
vext_reindex
    :    REINDEX DATABASE
    |    REINDEX TABLE table_name_list 
    |    REINDEX FIELD table_dot_column_list 
vext_repair
    :    REPAIR DATABASE
    |    REPAIR TABLE table_name_list 
    |    REPAIR FIELD table_dot_column_list 
vext_print_expr
    :    PRINT expr

Valentina Server

vext_vserver                         -- v4.0
    :    vext_vsrv_admin
    |    vext_vsrv_events

Admin

vext_vserver_admin                   -- v4.0
    :    vext_vsrv_register_db
    |    vext_vsrv_unregister_db
    |    vext_vsrv_register_project
    |    vext_vsrv_unregister_project
    |    vext_vsrv_drop_connections

vext_vsrv_register_db                -- v4.0
    :    REGISTER DATABASE db_name [path_name]

vext_vsrv_unregister_db              -- v4.0
    :    UNREGISTER DATABASE db_name

vext_vsrv_register_project           -- v4.0
    :    REGISTER PROJECT project_name [path_name]

vext_vsrv_unregister_project         -- v4.0
    :    UNREGISTER PROJECT project_name

vext_vsrv_drop_connections           -- v4.0
    :    DROP CONNECTIONS ([FOR] | [OF]) DATABASE db_name

Events

vext_vsrv_events
     :    vext_vsrv_event_definition_statement
     |    vext_vsrv_drop_event_statement
     |    vext_vsrv_alter_event_statement

vext_vsrv_event_definition_statement
    :    CREATE EVENT [IF NOT EXISTS] event_name FOR [DATABASE] db_name
         ON SCHEDULE vext_vsrv_shedule_definition
         [ ON COMPLETION [NOT] PRESERVE ]
         [ ENABLED | DISABLED ]
         [ COMMENT literal ]
         DO sql_statement

vext_vsrv_drop_event_statement
    :    DROP EVENT [ IF EXISTS ] event_name

vext_vsrv_alter_event_statement
    :    ALTER EVENT event_name
         [ ON SCHEDULE vext_vsrv_shedule_definition ]
         [ RENAME TO event_name2 ]
         [ ON COMPLETION [NOT] PRESERVE ]
         [ COMMENT 'comment' ]
         [ ENABLED | DISABLED ]
          DO sql_statement ]

vext_vsrv_shedule_definition
    :    AT timestamp [+ INTERVAL interval_value ]
    |    EVERY interval_value [STARTS timestamp] [ENDS timestamp]

vext_vsrv_shedule_definition
    :    AT timestamp [+ INTERVAL interval_value ]
    |    EVERY interval_value
    |    EVERY interval_value STARTS timestamp
    |    EVERY interval_value ENDS timestamp
    |    EVERY interval_value STARTS timestamp ENDS timestamp

interval_value
    :    integer_value time_keyword

time_keyword
    :    YEAR | MONTH | WEEK | DAY | HOUR | MINUTE | SECOND

Literals

literal
    :    signed_numeric_literal 
    |    general_literal 

unsigned_literal
    :    UINT 
    |    general_literal 

signed_numeric_literal
    :    [+|-] UINT 

general_literal
    :    character_string_literal 
    |    hex_string_literal 
    |    datetime_literal 

character_string_literal
    :    STRING_LITERAL [ (SPACE | COMMENT) STRING_LITERAL, ... ]

hex_string_literal
    :    x character_string_literal

datetime_literal
    :    DATE      STRING_LITERAL 
    |    TIME      STRING_LITERAL
    |    DATETIME  STRING_LITERAL
    |    TIMESPAMP STRING_LITERAL
character_string_literal_or_var               -- v5.0
    :    character_string_literal
    |    variable_name

uint_or_var
    :    UINT
    |    variable_name

truth_value_or_var
    :    truth_value
    |    variable_name

Unsigned Values

unsigned_value_specification
    :	unsigned_literal
    |	general_value_specification

general_value_specification
    :    dynamic_parameter_specification
    |    USER
    |    CURRENT_USER
    |    SESSION_USER
    |    SYSTEM_USER
    |    VALUE
	
dynamic_parameter_specification
    :    COLUMN UINT 
    |    QUESTION	

Lists

column_name_list
    :	column_name, ... 

column_reference
    :    column_name
    |    table_name.column_name
    |    column_name-}column_name
    |    table_name.column_name-}column_name

grouping_column_reference_list
    :    column_reference, ... 

index_column_list
    :    ( column_name [ (UINT) ]   [, column_name [ (UINT) ] ]  ) 

table_dot_column
    :    table_name.column_name

table_dot_column_list
    :    table_dot_column, ... 

table_name_list
    :    table_name, ... 

select_list
    :    select_sublist, ... 

select_sublist
    :    table_name.* 
    |    table_name.** 
    |    * 
    |    ** 
    |    expr [ [AS] IDENT ] 
    |    NULL [ [AS] IDENT ] 

variable_name_list
    :    variable_name, ... 

Names

collation_name:      IDENT
column_name:         IDENT
constraint_name:     IDENT
correlation_name:    IDENT
correlation_name:    IDENT
cursor_name:         IDENT

db_name:             IDENT

err_name:            IDENT
event_name:          IDENT
expression_name:     IDENT

field_name:          IDENT
func_name:           IDENT

index_name:          IDENT

label_name:          IDENT
link_name:           IDENT
local_table_name:    IDENT

method_name:         IDENT

new_row_name:        IDENT

old_row_name:        IDENT

project_name:        IDENT
property_name:       IDENT

sequence_name:       IDENT

table_name:          IDENT
table_alias:         IDENT
table_or_view_name:  IDENT
trigger_name:        IDENT

user_identifier:     IDENT

Helper Names

max_size:            UINT
precision:           UINT
scale:               UINT
segment_size:        UINT
time_precision:      UINT
timestamp_precision: UINT	


locale_name          STRING_LITERAL
log_name             STRING_LITERAL
path_name            STRING_LITERAL