[Tarantool-patches] [PATCH v3 3/5] sql: introduce SET statement
imeevma at tarantool.org
imeevma at tarantool.org
Thu Nov 7 13:36:23 MSK 2019
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 <imeevma at gmail.com>
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 <name of the setting> = <value of the setting>;
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
More information about the Tarantool-patches
mailing list