====== 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
==== Binary Link 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
==== 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
==== 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