From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from smtpng1.m.smailru.net (smtpng1.m.smailru.net [94.100.181.251]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by dev.tarantool.org (Postfix) with ESMTPS id E9250430D5A for ; Thu, 7 Nov 2019 13:36:23 +0300 (MSK) From: imeevma@tarantool.org Date: Thu, 7 Nov 2019 13:36:23 +0300 Message-Id: <12ed4be2e7e433fdca58a43fc3b937eb9a54f52f.1573121685.git.imeevma@gmail.com> In-Reply-To: References: Subject: [Tarantool-patches] [PATCH v3 3/5] sql: introduce SET statement List-Id: Tarantool development patches List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , To: v.shpilevoy@tarantool.org Cc: tarantool-patches@dev.tarantool.org Due to the removal of sql_compound_select_limit from the session options, this patch has been modified. New patch: >From 12ed4be2e7e433fdca58a43fc3b937eb9a54f52f Mon Sep 17 00:00:00 2001 From: Mergen Imeev Date: Wed, 16 Oct 2019 16:43:10 +0300 Subject: [PATCH] sql: introduce SET statement This patch creates an SQL SET statement. This statement replaces pragmas that can modify SQL settings. List of pragmas that will have the corresponding option in SET: 'defer_foreign_keys' 'full_column_names' 'recursive_triggers' 'reverse_unordered_selects' 'sql_compound_select_limit' 'sql_default_engine' 'parser_trace' 'select_trace' 'sql_trace' 'vdbe_addoptrace' 'vdbe_debug' 'vdbe_eqp' 'vdbe_listing' 'vdbe_trace' 'where_trace' All these pragmas along with the pragmas 'short_column_names' and 'count_changes' will be removed in the next patch. Part of #4511 @TarantoolBot document Title: SQL SET statement SQL SET statement is used to change SQL settings. To change the value of an SQL parameter, use the following syntax: SET = ; Currently available SQL settings: 'sql_defer_foreign_keys' 'sql_full_column_names' 'sql_recursive_triggers' 'sql_reverse_unordered_selects' 'sql_compound_select_limit' 'sql_default_engine' In addition, SQL debugging settings can also be changed using this statement in the debug build: 'sql_parser_trace' 'sql_select_trace' 'sql_trace' 'sql_vdbe_addoptrace' 'sql_vdbe_debug' 'sql_vdbe_eqp' 'sql_vdbe_listing' 'sql_vdbe_trace' 'sql_where_trace' Example of usage: SET full_column_names = true; SET sql_compound_select_limit = 10; SET sql_default_engine = 'memtx'; diff --git a/src/box/sql/build.c b/src/box/sql/build.c index ce87b88..77f8dd4 100644 --- a/src/box/sql/build.c +++ b/src/box/sql/build.c @@ -3275,6 +3275,21 @@ enum { SQL_SESSION_OPTION_max, }; + +/** + * Identifiers of all SQL global options that can be viewed. The + * identifier of the option is equal to its place in the sorted + * list of global options, which starts at 0. + * + * It is IMPORTANT that these options are sorted by name. If this + * is not the case, the result returned by the _vsession_settings + * space iterator will not be sorted properly. + */ +enum { + SQL_GLOBAL_OPTION_COMPOUND_SELECT_LIMIT = 0, + SQL_GLOBAL_OPTION_max, +}; + /** * A local structure that allows to establish a connection between * the name of the parameter, its field type and mask, if it have @@ -3329,6 +3344,17 @@ static struct sql_option_metadata sql_session_opts[] = { #endif }; +/** + * Variable that contains names of the SQL global options, their + * field types and mask if they have one or 0 if don't have. + * + * It is IMPORTANT that these options sorted by name. + */ +static struct sql_option_metadata sql_global_opts[] = { + /** SQL_GLOBAL_OPTION_COMPOUND_SELECT_LIMIT */ + {"sql_compound_select_limit", FIELD_TYPE_INTEGER, 0}, +}; + uint32_t sql_session_opt_id_max() { @@ -3382,3 +3408,82 @@ sql_session_opt_tuple(struct tuple_format *format, int option_id, *result = tuple; return 0; } + +static void +sql_set_session_option(struct Parse *parse_context, int id, struct Expr *value) +{ + struct session *session = current_session(); + struct sql_option_metadata *option = &sql_session_opts[id]; + if (value->type != option->field_type) { + diag_set(ClientError, ER_INCONSISTENT_TYPES, + field_type_strs[option->field_type], + field_type_strs[value->type]); + parse_context->is_aborted = true; + return; + } + if (value->type == FIELD_TYPE_BOOLEAN) { + bool is_set = value->op == TK_TRUE; + if (is_set) + session->sql_flags |= option->mask; + else + session->sql_flags &= ~option->mask; +#ifndef NDEBUG + if (id == SQL_SESSION_OPTION_PARSER_TRACE) { + if (is_set) + sqlParserTrace(stdout, "parser: "); + else + sqlParserTrace(NULL, NULL); + } +#endif + } else { + assert(id == SQL_SESSION_OPTION_DEFAULT_ENGINE); + enum sql_storage_engine engine = + STR2ENUM(sql_storage_engine, value->u.zToken); + if (engine == sql_storage_engine_MAX) { + parse_context->is_aborted = true; + diag_set(ClientError, ER_NO_SUCH_ENGINE, + value->u.zToken); + return; + } + current_session()->sql_default_engine = engine; + } +} + +static void +sql_set_global_option(struct Parse *parse_context, int id, struct Expr *value) +{ + (void)id; + assert(id == SQL_GLOBAL_OPTION_COMPOUND_SELECT_LIMIT); + int limit = value->u.iValue; + if (sql_limit(sql_get(), SQL_LIMIT_COMPOUND_SELECT, limit) < 0) + parse_context->is_aborted = true; +} + +void +sql_set_settings(struct Parse *parse_context, struct Token *name, + struct Expr *value) +{ + int option_id; + char *name_str = sql_name_from_token(sql_get(), name); + if (name_str == NULL) { + parse_context->is_aborted = true; + return; + } + /* Try to find the option among the session options. */ + for (option_id = 0; option_id < SQL_SESSION_OPTION_max; ++option_id) { + if (strcasecmp(sql_session_opts[option_id].name, name_str) == 0) + break; + } + if (option_id < SQL_SESSION_OPTION_max) + return sql_set_session_option(parse_context, option_id, value); + /* Try to find the option among the global options. */ + for (option_id = 0; option_id < SQL_GLOBAL_OPTION_max; ++option_id) { + if (strcasecmp(sql_global_opts[option_id].name, name_str) == 0) + break; + } + if (option_id < SQL_GLOBAL_OPTION_max) + return sql_set_global_option(parse_context, option_id, value); + diag_set(ClientError, ER_SQL_PARSER_GENERIC, "Setting is not found"); + parse_context->is_aborted = true; + return; +} diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y index 1d0c95f..d0702d7 100644 --- a/src/box/sql/parse.y +++ b/src/box/sql/parse.y @@ -1539,6 +1539,11 @@ cmd ::= DROP INDEX ifexists(E) nm(X) ON fullname(Y). { sql_drop_index(pParse); } +///////////////////////////// The SET command //////////////////////////////// +cmd ::= SET nm(X) EQ term(Y). { + sql_set_settings(pParse,&X,Y.pExpr); +} + ///////////////////////////// The PRAGMA command ///////////////////////////// // cmd ::= PRAGMA nm(X). { diff --git a/src/box/sql/sqlInt.h b/src/box/sql/sqlInt.h index 2594b73..8a2e370 100644 --- a/src/box/sql/sqlInt.h +++ b/src/box/sql/sqlInt.h @@ -4467,4 +4467,18 @@ int sql_fieldno_by_name(struct Parse *parse_context, struct Expr *field_name, uint32_t *fieldno); +/** + * Set new value for SQL setting. + * + * @param parse_context Parsing context. + * @param name Name of SQL setting to change. + * @param value New values of SQL setting. + * + * @retval 0 on success. + * @retval -1 on error. + */ +void +sql_set_settings(struct Parse *parse_context, struct Token *name, + struct Expr *value); + #endif /* sqlINT_H */ diff --git a/test/sql/sql-debug.result b/test/sql/sql-debug.result index 2dba684..632293a 100644 --- a/test/sql/sql-debug.result +++ b/test/sql/sql-debug.result @@ -54,3 +54,131 @@ box.execute('PRAGMA') - ['vdbe_trace', 0] - ['where_trace', 0] ... +-- +-- gh-4511: make sure that SET works. +-- +box.execute('SELECT "name" FROM "_vsession_settings";') +--- +- metadata: + - name: name + type: string + rows: + - ['sql_default_engine'] + - ['sql_defer_foreign_keys'] + - ['sql_full_column_names'] + - ['sql_parser_trace'] + - ['sql_recursive_triggers'] + - ['sql_reverse_unordered_selects'] + - ['sql_select_trace'] + - ['sql_trace'] + - ['sql_vdbe_addoptrace'] + - ['sql_vdbe_debug'] + - ['sql_vdbe_eqp'] + - ['sql_vdbe_listing'] + - ['sql_vdbe_trace'] + - ['sql_where_trace'] +... +engine = box.space._vsession_settings:get{'sql_default_engine'}.value +--- +... +order = box.space._vsession_settings:get{'sql_reverse_unordered_selects'}.value +--- +... +box.execute('SET sql_default_engine = 1;') +--- +- null +- 'Inconsistent types: expected string got integer' +... +box.execute("SET sql_default_engine = 'some_engine';") +--- +- null +- Space engine 'some_engine' does not exist +... +box.execute("SET engine = 'vinyl';") +--- +- null +- Setting is not found +... +box.execute("SET sql_defer_foreign_keys = 'vinyl';") +--- +- null +- 'Inconsistent types: expected boolean got string' +... +engine == box.space._vsession_settings:get{'sql_default_engine'}.value +--- +- true +... +order == box.space._vsession_settings:get{'sql_reverse_unordered_selects'}.value +--- +- true +... +box.execute("SET sql_default_engine = 'vinyl';") +--- +- row_count: 0 +... +box.execute("SET sql_reverse_unordered_selects = true;") +--- +- row_count: 0 +... +box.execute('SELECT * FROM "_vsession_settings";') +--- +- metadata: + - name: name + type: string + - name: value + type: any + rows: + - ['sql_where_trace', false] + - ['sql_vdbe_trace', false] + - ['sql_vdbe_listing', false] + - ['sql_vdbe_eqp', false] + - ['sql_vdbe_debug', false] + - ['sql_vdbe_addoptrace', false] + - ['sql_trace', false] + - ['sql_select_trace', false] + - ['sql_reverse_unordered_selects', true] + - ['sql_recursive_triggers', true] + - ['sql_parser_trace', false] + - ['sql_full_column_names', false] + - ['sql_defer_foreign_keys', false] + - ['sql_default_engine', 'vinyl'] +... +box.execute("SET sql_default_engine = 'memtx';") +--- +- row_count: 0 +... +box.execute("SET sql_reverse_unordered_selects = false;") +--- +- row_count: 0 +... +box.execute('SELECT * FROM "_vsession_settings";') +--- +- metadata: + - name: name + type: string + - name: value + type: any + rows: + - ['sql_default_engine', 'memtx'] + - ['sql_defer_foreign_keys', false] + - ['sql_full_column_names', false] + - ['sql_parser_trace', false] + - ['sql_recursive_triggers', true] + - ['sql_reverse_unordered_selects', false] + - ['sql_select_trace', false] + - ['sql_trace', false] + - ['sql_vdbe_addoptrace', false] + - ['sql_vdbe_debug', false] + - ['sql_vdbe_eqp', false] + - ['sql_vdbe_listing', false] + - ['sql_vdbe_trace', false] + - ['sql_where_trace', false] +... +box.execute("SET sql_default_engine = '"..engine.."';") +--- +- row_count: 0 +... +box.execute("SET sql_reverse_unordered_selects = "..tostring(order)..";") +--- +- row_count: 0 +... diff --git a/test/sql/sql-debug.test.lua b/test/sql/sql-debug.test.lua index edd0ef4..83746f0 100644 --- a/test/sql/sql-debug.test.lua +++ b/test/sql/sql-debug.test.lua @@ -15,3 +15,29 @@ box.execute('PRAGMA parser_trace = '.. result[1][1]) -- Make PRAGMA command return the result as a result set. -- box.execute('PRAGMA') + +-- +-- gh-4511: make sure that SET works. +-- +box.execute('SELECT "name" FROM "_vsession_settings";') + +engine = box.space._vsession_settings:get{'sql_default_engine'}.value +order = box.space._vsession_settings:get{'sql_reverse_unordered_selects'}.value + +box.execute('SET sql_default_engine = 1;') +box.execute("SET sql_default_engine = 'some_engine';") +box.execute("SET engine = 'vinyl';") +box.execute("SET sql_defer_foreign_keys = 'vinyl';") +engine == box.space._vsession_settings:get{'sql_default_engine'}.value +order == box.space._vsession_settings:get{'sql_reverse_unordered_selects'}.value + +box.execute("SET sql_default_engine = 'vinyl';") +box.execute("SET sql_reverse_unordered_selects = true;") +box.execute('SELECT * FROM "_vsession_settings";') + +box.execute("SET sql_default_engine = 'memtx';") +box.execute("SET sql_reverse_unordered_selects = false;") +box.execute('SELECT * FROM "_vsession_settings";') + +box.execute("SET sql_default_engine = '"..engine.."';") +box.execute("SET sql_reverse_unordered_selects = "..tostring(order)..";") -- 2.7.4