Switch to: V12V11V10V9V8V7V6V5

Valentina SQL GRAMMAR v7.x

Changes

New

  • VARIANT type.
  • type_init_literals clause – to support inserting of typed values into new VARIANT field.
  • KeyValue object:
    • CERATE KEYVALUE
    • DROP KEYVALUE
    • SHOW KEYVALUES
    • KEYVALUE DELETE
    • KEYVALUE GET
    • KEYVALUE SET
    • KEYVALUE UPDATE
    • KEYVALUE UPSERT

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_channels               -- v6.0   
    |    vext_database
    |    vext_links
    |    vext_mail
    |    vext_print_expr
    |    vext_report_stmt            -- v6.2
//  |    vext_get_property           -- MOVED TO query_primary rule
//  |    vext_set_property           -- MOVED TO sql_data_change_statement 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_keyvalue_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_method] index_column_list 

index_method                              -- v5.6
    :   USING {DEFAULT | HASH}

KeyValue Definition

vext_keyvalue_definition
    :    CREATE [OR REPLACE] 
         KEYVALUE [IF NOT EXISTS] keyvalue_name
         [    WITH KEY ( vext_key_parts )
         |    FOR TABLES
         |    FOR TABLE table_name
         |    FOR LINK link_name
         ]
vext_key_parts
    :    vext_key_part, ...
 
vext_key_part
    :    BYTE | SHORT | USHORT | LONG | ULONG | LLONG | ULLONG | VARBINARY

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	

grantor
    : 	 CURRENT_USER
    |    character_string_literal

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 
         |    HASH                                            -- v5.6
         |    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 ] 
         {    table_constraint_unique_definition
         |    table_constraint_referential_definition  
         |    table_constraint_check_definition
         |    table_constraint_index_definition 
         }

table_constraint_unique_definition
    :    { UNIQUE [ index_name ]  |  PRIMARY KEY } [ index_method ] index_column_list 

table_constraint_referential_definition
    :    FOREIGN KEY ( column_name_list ) references_specification

table_constraint_check_definition
    :    CHECK ( search_condition )

table_constraint_index_definition
    :    INDEX [ index_name ] [ index_method ] index_column_list
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
        | MONEY                                                          -- v6.0
        | VARIANT                                                        -- v7.0 
    }    
    |   { 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
    :    ADD [COLUMN]   column_definition  
    |    ADD [COLUMN] ( column_definition, ... ] )

    |    ADD   table_constraint_definition 
    |    ADD ( 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 

drop_keyvalue_statement
    :    DROP KEYVALUE keyvalue_name [OF TABLE table_name | OF LINK link_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
    |    merge_statement
    |    call_statement
    |    assign_statement
    |    vext_truncate_statement
    |    vext_set_property
    |    vext_link_records
    |    vext_unlink_records
    |    vext_keyvalue_action                                                     -- v7.0

Tables

insert_statement
    :    INSERT [INTO] table_name insert_columns_and_source 
            [ON DUPLICATE KEY SET set_clause, ... ]                               -- v6.0

insert_columns_and_source
    :    DEFAULT VALUES
    |    ( column_name_list )
    |    query_expression
delete_statement_searched
    :    DELETE FROM table_name [ WHERE search_condition ]

vext_truncate_statement
    :    TRUNCATE [TABLE] table_name                                              -- v6.0
update_statement_searched
    :    UPDATE table_name SET set_clause, ...  
	     [ WHERE search_condition ]

set_clause
    :    column_name = { DEFAULT | NULL | expr | VALUE(column_name) | VALUES(column_name) }
merge_statement                                                                  -- v6.0
    :    MERGE [INTO] target_table_name [[AS] table_alias]  
                USING source_table_name [[AS] table_alias]
               join_specification
               {merge_when_clause}+

merge_when_clause
    :    WHEN MATCHED THEN merge_matched
    |    WHEN NOT MATCHED BY SOURCE THEN merge_matched
    |    WHEN NOT MATCHED [BY TARGET] THEN merge_not_matched

merge_matched
    :    UPDATE SET set_clause
    |    DELETE                        (non SQL standard)

merge_not_matched
    :    INSERT ( column_name_list )
         {    VALUES ( values_list )
         |    DEFAULT VALUES
         } 
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]                                                 -- 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 
          [IF  EXISTS]                                                   -- v5.0

v_list_of_records
    :    ( v_link_value, ... )
    |    subquery 

v_link_value
    :    LAST                                                            -- v7.4.4, same as last_recid_of_table(tbl_name)
    |    UINT
    |    dynamic_parameter_specification 
    |    column_reference                                                -- v5.0, allows triggers begin Link RECORD(recID) OF T1... end
                                                                         -- also (@var)

KeyValue

-- Default KeyValue or KeyValue WITH KEY
vext_keyvalue_action
    :    KEYVALUE keyvalue_name
         {    SET    ( vext_keyvalue_list )
         |    UPDATE ( vext_keyvalue_list )
         |    UPSERT ( vext_keyvalue_list )
         }
-- KeyValue FOR TABLE or FOR LINK
vext_keyvalue_action
    :    KEYVALUE keyvalue_name {OF TABLE table_name | OF LINK link_name}
         {    SET    ( vext_keyvalue_list )
         |    UPDATE ( vext_keyvalue_list )
         |    UPSERT ( vext_keyvalue_list )
         }
         [FOR RECORD {UINT | vext_link_list_of_records} ]
vext_keyvalue_action
    :    KEYVALUE keyvalue_name [OF TABLE table_name | OF LINK link_name]
         {    DELETE [LIKE] ( vext_key_list )
         |    GET    [LIKE] ( vext_key_list )
         }
vext_keyvalue_list
    :    vext_keyvalue, ...

vext_keyvalue
    :    vext_key COLON vext_value

vext_key_list
    :    vext_key, ...

vext_key
    :    character_string_literal
    |    variable_name
    |    dynamic_parameter_specification

vext_value
    :    character_string_literal
    |    variable_name
    |    dynamic_parameter_specification
    |    TRUE
    |    FALSE

Queries

direct_select_statement_multiple_rows
    :    query_expression 
             [vext_for_xml]
             [vext_for_json]
             [vext_for_report]

query_expression
    :    query_core [order_by_clause] [vext_select_limit]

query_core
    :    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, 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, ...  
            [ WHERE search_condition ]
            [ GROUP BY grouping_column_reference_list [WITH ROLLUP] ]
            [ HAVING search_condition ]

grouping_column_reference_list
    :    select_expr_ref, ... 
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 
    |    ON 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 )
         )
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, ... ]                   -- v5.0

vext_root_objects
    :    UINT
    |    variable_name
    |    ( search_condition )

vext_pseudo_column
    :    {LEVEL | IS_LEAF} [[AS] IDENT]
table_value_constructor_list
    :    row_expr, ... 

row_expr
    :    row_list_element
    |    ( row_list_element, ... ) 
              
row_list_element
    :    expr | NULL | DEFAULT 
order_by_clause
    :    [ORDER BY sort_specification_list] 

sort_specification_list
    :    sort_specification, ...

sort_specification
    :    select_expr_ref [ordering_specification] [null_order]

select_expr_ref
    :    column_reference
    |    UINT

ordering_specification
    :    ASC | DESC

null_order
    :    NULLS (FIRST | LAST)
uint_or_var
    :    UINT
    |    variable_name

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, ...] ) 
    |	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, ... ) 

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 COMITTED 
    | 	READ UNCOMITTED
    |	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

Links

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

Notifications

vext_channels
    : SUBSCRIBE   CHANNEL
    | UNSUBSCRIBE CHANNEL
    | NOTIFY CHANNEL expr WITH expr

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

Mail Server

vext_mail                                                             -- v5.0
    :    __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          uint_or_var,
        __TIMEOUT       uint_or_var,                                  -- v5.8, parameter added
        [__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, ...

Report Server

vext_report_stmt                                        -- v6.2
    :    REPORT project_name.report_name
             FROM data_source
             USING data_query
             [AS {PDF | HTML | JPG}]
             [PAGES StartPage TO EndPage] 
             [ENCODE BASE64]                           

data_source : character_string_literal_or_var
data_query  : character_string_literal_or_var

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 report_format]
             [PAGES start_page TO end_page] 
             [ENCODE BASE64]                            -- v5.0

report_format
    : {PDF | HTML | JPG}

start_page : uint_or_var
end_page   : uint_or_var

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 KEYVALUES   {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 GRANTS     [{FROM|OF} grantor]
    |    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_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_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 
    |    type_init_literals

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
type_init_literals                            -- v7.0
    :	"bit"		STRING_LITERAL
    |	"tinyint"	STRING_LITERAL
    |	"smallint"	STRING_LITERAL
    |	"mediumint"	STRING_LITERAL
    |	"integer"	STRING_LITERAL
    |	"int"		STRING_LITERAL
    |	"bigint"	STRING_LITERAL
    |	"boolean"	STRING_LITERAL
    |	"byte"		STRING_LITERAL
    |	"uchar"		STRING_LITERAL
    |	"short"		STRING_LITERAL
    |	"ushort"	STRING_LITERAL
    |	"medium"	STRING_LITERAL
    |	"umedium"	STRING_LITERAL
    |	"long"		STRING_LITERAL
    |	"ulong"		STRING_LITERAL
    |	"llong"		STRING_LITERAL
    |	"ullong"	STRING_LITERAL
    |	"float"		STRING_LITERAL
    |	"double"	STRING_LITERAL
    |	"fixedbinary"   STRING_LITERAL
    |	"varbinary"	STRING_LITERAL
    |	"blob"		STRING_LITERAL
    |	"picture"	STRING_LITERAL
    |	"tinytext"	STRING_LITERAL
    |	"text"		STRING_LITERAL
    |	"mediumtext"    STRING_LITERAL
    |	"longtext"	STRING_LITERAL

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

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

keyvalue_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:         character_string_literal
log_name:            character_string_literal
path_name:           character_string_literal