Changeset: b557019bea76 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=b557019bea76
Modified Files:
sql/backends/monet5/sql.c
sql/backends/monet5/sql_upgrades.c
sql/include/sql_catalog.h
sql/server/rel_updates.c
sql/server/sql_mvc.c
sql/server/sql_privileges.c
sql/storage/sql_catalog.c
Branch: nospare
Log Message:
merged with default
diffs (truncated from 14766 to 300 lines):
diff --git a/clients/Tests/MAL-signatures.stable.out
b/clients/Tests/MAL-signatures.stable.out
--- a/clients/Tests/MAL-signatures.stable.out
+++ b/clients/Tests/MAL-signatures.stable.out
@@ -9513,7 +9513,7 @@ stdout of test 'MAL-signatures` in direc
[ "sqlcatalog", "alter_seq", "pattern sqlcatalog.alter_seq(X_1:str,
X_2:str, X_3:ptr, X_4:lng):void ", "SQLalter_seq;", "" ]
[ "sqlcatalog", "alter_set_table", "pattern
sqlcatalog.alter_set_table(X_1:str, X_2:str, X_3:int):void ",
"SQLalter_set_table;", "" ]
[ "sqlcatalog", "alter_table", "pattern
sqlcatalog.alter_table(X_1:str, X_2:str, X_3:ptr, X_4:int):void ",
"SQLalter_table;", "" ]
-[ "sqlcatalog", "alter_user", "pattern sqlcatalog.alter_user(X_1:str,
X_2:str, X_3:int, X_4:str, X_5:str):void ", "SQLalter_user;", ""
]
+[ "sqlcatalog", "alter_user", "pattern sqlcatalog.alter_user(X_1:str,
X_2:str, X_3:int, X_4:str, X_5:str, X_6:str):void ", "SQLalter_user;",
"" ]
[ "sqlcatalog", "comment_on", "pattern sqlcatalog.comment_on(X_1:int,
X_2:str):void ", "SQLcomment_on;", "" ]
[ "sqlcatalog", "create_function", "pattern
sqlcatalog.create_function(X_1:str, X_2:str, X_3:ptr):void ",
"SQLcreate_function;", "" ]
[ "sqlcatalog", "create_role", "pattern
sqlcatalog.create_role(X_1:str, X_2:str, X_3:int):void ",
"SQLcreate_role;", "" ]
@@ -9522,7 +9522,7 @@ stdout of test 'MAL-signatures` in direc
[ "sqlcatalog", "create_table", "pattern
sqlcatalog.create_table(X_1:str, X_2:str, X_3:ptr, X_4:int):void ",
"SQLcreate_table;", "" ]
[ "sqlcatalog", "create_trigger", "pattern
sqlcatalog.create_trigger(X_1:str, X_2:str, X_3:str, X_4:int, X_5:int, X_6:int,
X_7:str, X_8:str, X_9:str, X_10:str):void ", "SQLcreate_trigger;", "" ]
[ "sqlcatalog", "create_type", "pattern
sqlcatalog.create_type(X_1:str, X_2:str, X_3:str):void ",
"SQLcreate_type;", "" ]
-[ "sqlcatalog", "create_user", "pattern
sqlcatalog.create_user(X_1:str, X_2:str, X_3:int, X_4:str, X_5:str):void ",
"SQLcreate_user;", "" ]
+[ "sqlcatalog", "create_user", "pattern
sqlcatalog.create_user(X_1:str, X_2:str, X_3:int, X_4:str, X_5:str,
X_6:str):void ", "SQLcreate_user;", "" ]
[ "sqlcatalog", "create_view", "pattern
sqlcatalog.create_view(X_1:str, X_2:str, X_3:ptr, X_4:int):void ",
"SQLcreate_view;", "" ]
[ "sqlcatalog", "drop_constraint", "pattern
sqlcatalog.drop_constraint(X_1:str, X_2:str, X_3:int, X_4:int):void ",
"SQLdrop_constraint;", "" ]
[ "sqlcatalog", "drop_function", "pattern
sqlcatalog.drop_function(X_1:str, X_2:str, X_3:int, X_4:int, X_5:int):void ",
"SQLdrop_function;", "" ]
diff --git a/clients/Tests/MAL-signatures.stable.out.int128
b/clients/Tests/MAL-signatures.stable.out.int128
--- a/clients/Tests/MAL-signatures.stable.out.int128
+++ b/clients/Tests/MAL-signatures.stable.out.int128
@@ -12836,7 +12836,7 @@ stdout of test 'MAL-signatures` in direc
[ "sqlcatalog", "alter_seq", "pattern sqlcatalog.alter_seq(X_1:str,
X_2:str, X_3:ptr, X_4:lng):void ", "SQLalter_seq;", "" ]
[ "sqlcatalog", "alter_set_table", "pattern
sqlcatalog.alter_set_table(X_1:str, X_2:str, X_3:int):void ",
"SQLalter_set_table;", "" ]
[ "sqlcatalog", "alter_table", "pattern
sqlcatalog.alter_table(X_1:str, X_2:str, X_3:ptr, X_4:int):void ",
"SQLalter_table;", "" ]
-[ "sqlcatalog", "alter_user", "pattern sqlcatalog.alter_user(X_1:str,
X_2:str, X_3:int, X_4:str, X_5:str):void ", "SQLalter_user;", ""
]
+[ "sqlcatalog", "alter_user", "pattern sqlcatalog.alter_user(X_1:str,
X_2:str, X_3:int, X_4:str, X_5:str, X_6:str):void ", "SQLalter_user;",
"" ]
[ "sqlcatalog", "comment_on", "pattern sqlcatalog.comment_on(X_1:int,
X_2:str):void ", "SQLcomment_on;", "" ]
[ "sqlcatalog", "create_function", "pattern
sqlcatalog.create_function(X_1:str, X_2:str, X_3:ptr):void ",
"SQLcreate_function;", "" ]
[ "sqlcatalog", "create_role", "pattern
sqlcatalog.create_role(X_1:str, X_2:str, X_3:int):void ",
"SQLcreate_role;", "" ]
@@ -12845,7 +12845,7 @@ stdout of test 'MAL-signatures` in direc
[ "sqlcatalog", "create_table", "pattern
sqlcatalog.create_table(X_1:str, X_2:str, X_3:ptr, X_4:int):void ",
"SQLcreate_table;", "" ]
[ "sqlcatalog", "create_trigger", "pattern
sqlcatalog.create_trigger(X_1:str, X_2:str, X_3:str, X_4:int, X_5:int, X_6:int,
X_7:str, X_8:str, X_9:str, X_10:str):void ", "SQLcreate_trigger;", "" ]
[ "sqlcatalog", "create_type", "pattern
sqlcatalog.create_type(X_1:str, X_2:str, X_3:str):void ",
"SQLcreate_type;", "" ]
-[ "sqlcatalog", "create_user", "pattern
sqlcatalog.create_user(X_1:str, X_2:str, X_3:int, X_4:str, X_5:str):void ",
"SQLcreate_user;", "" ]
+[ "sqlcatalog", "create_user", "pattern
sqlcatalog.create_user(X_1:str, X_2:str, X_3:int, X_4:str, X_5:str,
X_6:str):void ", "SQLcreate_user;", "" ]
[ "sqlcatalog", "create_view", "pattern
sqlcatalog.create_view(X_1:str, X_2:str, X_3:ptr, X_4:int):void ",
"SQLcreate_view;", "" ]
[ "sqlcatalog", "drop_constraint", "pattern
sqlcatalog.drop_constraint(X_1:str, X_2:str, X_3:int, X_4:int):void ",
"SQLdrop_constraint;", "" ]
[ "sqlcatalog", "drop_function", "pattern
sqlcatalog.drop_function(X_1:str, X_2:str, X_3:int, X_4:int, X_5:int):void ",
"SQLdrop_function;", "" ]
diff --git a/clients/mapiclient/dump.c b/clients/mapiclient/dump.c
--- a/clients/mapiclient/dump.c
+++ b/clients/mapiclient/dump.c
@@ -2196,7 +2196,8 @@ dump_database(Mapi mid, stream *toConsol
"SELECT ui.name, "
"ui.fullname, "
"password_hash(ui.name), "
- "s.name "
+ "s.name, "
+ "ui.schema_path "
"FROM sys.db_user_info ui, "
"sys.schemas s "
"WHERE ui.default_schema = s.id "
@@ -2439,6 +2440,7 @@ dump_database(Mapi mid, stream *toConsol
const char *fullname = mapi_fetch_field(hdl, 1);
const char *pwhash = mapi_fetch_field(hdl, 2);
const char *sname = mapi_fetch_field(hdl, 3);
+ const char *spath = mapi_fetch_field(hdl, 4);
mnstr_printf(toConsole, "CREATE USER ");
dquoted_print(toConsole, uname, " ");
@@ -2447,7 +2449,8 @@ dump_database(Mapi mid, stream *toConsol
mnstr_printf(toConsole, " NAME ");
squoted_print(toConsole, fullname, '\'', false);
mnstr_printf(toConsole, " SCHEMA ");
- dquoted_print(toConsole, describe ? sname : "sys",
";\n");
+ dquoted_print(toConsole, describe ? sname : "sys", " ");
+ mnstr_printf(toConsole, "SCHEMA PATH '%s';\n", spath);
}
if (mapi_error(mid))
goto bailout;
diff --git a/clients/mapiclient/mhelp.c b/clients/mapiclient/mhelp.c
--- a/clients/mapiclient/mhelp.c
+++ b/clients/mapiclient/mhelp.c
@@ -82,7 +82,8 @@ SQLhelp sqlhelp1[] = {
"ALTER USER ident RENAME TO ident\n"
"ALTER USER SET [ENCRYPTED | UNENCRYPTED] PASSWORD string USING OLD
PASSWORD string\n"
"ALTER USER ident WITH [ENCRYPTED | UNENCRYPTED] PASSWORD string\n"
- "ALTER USER ident [ WITH [ENCRYPTED | UNENCRYPTED] PASSWORD string ]
SET SCHEMA ident",
+ "ALTER USER ident [WITH [ENCRYPTED | UNENCRYPTED] PASSWORD string] SET
SCHEMA ident\n"
+ "ALTER USER ident [WITH [ENCRYPTED | UNENCRYPTED] PASSWORD string]
SCHEMA PATH string",
"ident",
"See also https://www.monetdb.org/Documentation/SQLreference/Users"},
{"ANALYZE",
@@ -229,7 +230,7 @@ SQLhelp sqlhelp1[] = {
NULL},
{"CREATE USER",
"Create a new database user",
- "CREATE USER ident WITH [ENCRYPTED | UNENCRYPTED] PASSWORD string NAME
string SCHEMA ident",
+ "CREATE USER ident WITH [ENCRYPTED | UNENCRYPTED] PASSWORD string NAME
string SCHEMA ident [SCHEMA PATH string]",
"ident",
"See also https://www.monetdb.org/Documentation/SQLreference/Users"},
{"CREATE VIEW",
diff --git a/documentation/source/developers_handbook.rst
b/documentation/source/developers_handbook.rst
--- a/documentation/source/developers_handbook.rst
+++ b/documentation/source/developers_handbook.rst
@@ -69,20 +69,35 @@ directory where they reside::
[$src_root/sql/test/json/Tests]$ Mtest.py .
-Adding a new test
+Adding sqllogic test
-----------------
-Summarizing the above discussion, to add a new test, you need to write the test
-itself, create the stable output and error files, and finally add the test to
the
-``All`` index. This will make ensure that the test will be picked up by
-``Mtest.py`` as part of its group.
+See `<https://www.sqlite.org/sqllogictest/doc/trunk/about.wiki/>`_ for detail
information
+on how to structure sqllogic test if you desire to make one by hand. We have
extended the
+sqllogic protocol a bit further::
+
+ skipif <system>
+ onlyif <system>
-To create the correct stable output you can use the ``Mapprove.py`` utility.
-First create empty ``.stable.{out,err}`` files and run the test using
-``Mtest.py``. Check the output and if it is correct, run ``Mapprove.py`` with
-the same arguments. This will add the correct contents to the
-``.stable.{out,err}`` files. Commit the changes to the VCS and the test can now
-be used by other developers and the nightly testing infrastructure.
+ statement (ok|ok rowcount|error) [arg]
+ query (I|T|R)+ (nosort|rowsort|valuesort|python)? [arg]
+ I: integer; T: text (string); R: real (decimal)
+ nosort: do not sort
+ rowsort: sort rows
+ valuesort: sort individual values
+ python some.python.function: run data through function (MonetDB
extension)
+ hash-threshold number
+ halt
+
+Alternatively ``.sql`` scripts can be converted to sqllogic tests (.test) with
``Mconvert.py``.
+For example::
+
+ $Mconvert.py --auto <module>/Tests <convert_me>.sql
+
+All new tests need to be placed in the appropriate test folder and their name
respectively in the
+index ``All`` file.
Python tests API
----------------
+
+See many of the examples in ``sql/test/Users/Tests``.
diff --git a/sql/ChangeLog.scoping2 b/sql/ChangeLog.scoping2
new file mode 100644
--- /dev/null
+++ b/sql/ChangeLog.scoping2
@@ -0,0 +1,27 @@
+# ChangeLog file for sql
+# This file is updated with Maddlog
+
+* Mon Nov 30 2020 Pedro Ferreira <pedro.ferre...@monetdbsolutions.com>
+- Added 'schema path' property to user, specifying a list of schemas
+ to be searched on to find SQL objects such as tables and
+ functions. The scoping rules have been updated to support this feature
+ and it now finds SQL objects in the following order:
+ 1. On occasions with multiple tables (e.g. add foreign key constraint,
+ add table to a merge table), the child will be searched on the
+ parent's schema.
+ 2. For tables only, declared tables on the stack.
+ 3. 'tmp' schema if not listed on the 'schema path'.
+ 4. Session's current schema.
+ 5. Each schema from the 'schema path' in order.
+ 6. 'sys' schema if not listed on the 'schema path'.
+ Whenever the full path is specified, ie "schema"."object", no search will
+ be made besides on the explicit schema.
+- To update the schema path ALTER USER x SCHEMA PATH y; statement was added.
+ [SCHEMA PATH string] syntax was added to the CREATE USER statement.
+ The schema path must be a single string where each schema must be between
+ double quotes and separated with a single comma, e.g. '"sch1","sch2"'
+ For every created user, if the schema path is not given, '"sys"' will be
+ the default schema path.
+- Changes in the schema path won't be reflected on currently connected users,
+ therefore they have to re-connect to see the change. Non existent schemas
+ on the path will be ignored.
diff --git a/sql/backends/monet5/generator/Tests/crash.Bug-3609.stable.err
b/sql/backends/monet5/generator/Tests/crash.Bug-3609.stable.err
--- a/sql/backends/monet5/generator/Tests/crash.Bug-3609.stable.err
+++ b/sql/backends/monet5/generator/Tests/crash.Bug-3609.stable.err
@@ -32,7 +32,7 @@ stderr of test 'crash.Bug-3609` in direc
MAPI = (monetdb) /var/tmp/mtest-30092/.s.monetdb.31340
QUERY = select generate_series(0,2,1);
-ERROR = !SELECT: no such operator 'generate_series'
+ERROR = !SELECT: no such operator 'generate_series'(tinyint, tinyint, tinyint)
CODE = 42000
diff --git a/sql/backends/monet5/rel_bin.c b/sql/backends/monet5/rel_bin.c
--- a/sql/backends/monet5/rel_bin.c
+++ b/sql/backends/monet5/rel_bin.c
@@ -41,22 +41,22 @@ stmt_selectnil( backend *be, stmt *col)
}
static stmt *
-sql_unop_(backend *be, sql_schema *s, const char *fname, stmt *rs)
+sql_unop_(backend *be, const char *fname, stmt *rs)
{
mvc *sql = be->mvc;
sql_subtype *rt = NULL;
sql_subfunc *f = NULL;
- if (!s)
- s = sql->session->schema;
rt = tail_type(rs);
- f = sql_bind_func(sql->sa, s, fname, rt, NULL, F_FUNC);
+ f = sql_bind_func(sql, "sys", fname, rt, NULL, F_FUNC);
/* try to find the function without a type, and convert
* the value to the type needed by this function!
*/
- if (!f && (f = sql_find_func(sql->sa, s, fname, 1, F_FUNC, NULL)) !=
NULL) {
+ if (!f && (f = sql_find_func(sql, "sys", fname, 1, F_FUNC, NULL)) !=
NULL) {
sql_arg *a = f->func->ops->h->data;
+ sql->session->status = 0;
+ sql->errstr[0] = '\0';
rs = check_types(be, &a->type, rs, type_equal);
if (!rs)
f = NULL;
@@ -72,7 +72,7 @@ sql_unop_(backend *be, sql_schema *s, co
} else if (rs) {
char *type = tail_type(rs)->type->sqlname;
- return sql_error(sql, 02, SQLSTATE(42000) "SELECT: no such
unary operator '%s(%s)'", fname, type);
+ return sql_error(sql, ERR_NOTFOUND, SQLSTATE(42000) "SELECT: no
such unary operator '%s(%s)'", fname, type);
}
return NULL;
}
@@ -377,10 +377,10 @@ handle_in_exps(backend *be, sql_exp *ce,
if (c->nrcols == 0 || (depth && !reduce)) {
sql_subtype *bt = sql_bind_localtype("bit");
sql_subfunc *cmp = (in)
- ?sql_bind_func(sql->sa, sql->session->schema, "=",
tail_type(c), tail_type(c), F_FUNC)
- :sql_bind_func(sql->sa, sql->session->schema, "<>",
tail_type(c), tail_type(c), F_FUNC);
- sql_subfunc *a = (in)?sql_bind_func(sql->sa,
sql->session->schema, "or", bt, bt, F_FUNC)
- :sql_bind_func(sql->sa,
sql->session->schema, "and", bt, bt, F_FUNC);
+ ?sql_bind_func(sql, "sys", "=", tail_type(c),
tail_type(c), F_FUNC)
+ :sql_bind_func(sql, "sys", "<>", tail_type(c),
tail_type(c), F_FUNC);
+ sql_subfunc *a = (in)?sql_bind_func(sql, "sys", "or", bt, bt,
F_FUNC)
+ :sql_bind_func(sql, "sys", "and", bt, bt,
F_FUNC);
for( n = nl->h; n; n = n->next) {
sql_exp *e = n->data;
@@ -547,7 +547,7 @@ exp_bin_or(backend *be, sql_exp *e, stmt
return s;
if (!sin && sel1 && sel1->nrcols == 0 && s->nrcols == 0) {
- sql_subfunc *f = sql_bind_func(be->mvc->sa,
be->mvc->session->schema, anti?"or":"and", bt, bt, F_FUNC);
+ sql_subfunc *f = sql_bind_func(be->mvc, "sys",
anti?"or":"and", bt, bt, F_FUNC);
assert(f);
s = stmt_binop(be, sel1, s, f);
} else if (sel1 && (sel1->nrcols == 0 || s->nrcols == 0)) {
@@ -574,7 +574,7 @@ exp_bin_or(backend *be, sql_exp *e, stmt
return s;
if (!sin && sel2 && sel2->nrcols == 0 && s->nrcols == 0) {
- sql_subfunc *f = sql_bind_func(be->mvc->sa,
be->mvc->session->schema, anti?"or":"and", bt, bt, F_FUNC);
+ sql_subfunc *f = sql_bind_func(be->mvc, "sys",
anti?"or":"and", bt, bt, F_FUNC);
assert(f);
s = stmt_binop(be, sel2, s, f);
} else if (sel2 && (sel2->nrcols == 0 || s->nrcols == 0)) {
@@ -589,7 +589,7 @@ exp_bin_or(backend *be, sql_exp *e, stmt
sel2 = s;
}
if (sel1->nrcols == 0 && sel2->nrcols == 0) {
- sql_subfunc *f = sql_bind_func(be->mvc->sa,
be->mvc->session->schema, anti?"and":"or", bt, bt, F_FUNC);
+ sql_subfunc *f = sql_bind_func(be->mvc, "sys", anti?"and":"or",
bt, bt, F_FUNC);
assert(f);
return stmt_binop(be, sel1, sel2, f);
}
@@ -617,9 +617,9 @@ exp2bin_case(backend *be, sql_exp *fe, s
int next_cond = 1, single_value = (fe->card <= CARD_ATOM && (!left ||
!left->nrcols));
char name[16], *nme = NULL;
sql_subtype *bt = sql_bind_localtype("bit");
- sql_subfunc *not = sql_bind_func(be->mvc->sa, be->mvc->session->schema,
"not", bt, NULL, F_FUNC);
- sql_subfunc *or = sql_bind_func(be->mvc->sa, NULL, "or", bt, bt,
F_FUNC);
- sql_subfunc *and = sql_bind_func(be->mvc->sa, be->mvc->session->schema,
"and", bt, bt, F_FUNC);
+ sql_subfunc *not = sql_bind_func(be->mvc, "sys", "not", bt, NULL,
F_FUNC);
+ sql_subfunc *or = sql_bind_func(be->mvc, "sys", "or", bt, bt, F_FUNC);
+ sql_subfunc *and = sql_bind_func(be->mvc, "sys", "and", bt, bt, F_FUNC);
if (single_value) {
/* var_x = nil; */
@@ -721,7 +721,7 @@ exp2bin_case(backend *be, sql_exp *fe, s
if (en->next) {
cond = stmt_unop(be, cond, not);
- sql_subfunc *isnull =
sql_bind_func(be->mvc->sa, be->mvc->session->schema, "isnull", bt, NULL,
F_FUNC);
+ sql_subfunc *isnull =
sql_bind_func(be->mvc, "sys", "isnull", bt, NULL, F_FUNC);
cond = stmt_binop(be, cond,
stmt_unop(be, cond, isnull), or);
if (ocond)
cond = stmt_binop(be, ocond,
cond, and);
@@ -749,8 +749,8 @@ exp2bin_coalesce(backend *be, sql_exp *f
int single_value = (fe->card <= CARD_ATOM && (!left || !left->nrcols));
char name[16], *nme = NULL;
sql_subtype *bt = sql_bind_localtype("bit");
- sql_subfunc *and = sql_bind_func(be->mvc->sa, NULL, "and", bt, bt,
F_FUNC);
- sql_subfunc *not = sql_bind_func(be->mvc->sa, be->mvc->session->schema,
"not", bt, NULL, F_FUNC);
+ sql_subfunc *and = sql_bind_func(be->mvc, "sys", "and", bt, bt, F_FUNC);
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
