Table of Contents
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
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_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
Links
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