Changeset: ebb910254f5e for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/ebb910254f5e
Modified Files:
clients/mapiclient/dump.c
sql/server/sql_parser.y
sql/server/sql_scan.c
sql/test/testdb/Tests/dump-nogeom.stable.out
sql/test/testdb/Tests/dump.stable.out
sql/test/testdb/Tests/load.test
Branch: Sep2022
Log Message:
Implemented parsing and dumping of extra user information.
MAX_MEMORY, MAX_WORKERS, OPTIMIZER. Also added dump test.
diffs (257 lines):
diff --git a/clients/mapiclient/dump.c b/clients/mapiclient/dump.c
--- a/clients/mapiclient/dump.c
+++ b/clients/mapiclient/dump.c
@@ -331,6 +331,42 @@ bailout:
}
static bool
+has_schema_max_memory(Mapi mid)
+{
+ MapiHdl hdl;
+ bool ret;
+ static int answer = -1;
+
+ if (answer >= 0)
+ return answer;
+
+ if ((hdl = mapi_query(mid, "select id from sys._columns where table_id
= (select id from sys._tables where name = 'db_user_info' and schema_id =
(select id from sys.schemas where name = 'sys')) and name = 'max_memory'")) ==
NULL ||
+ mapi_error(mid))
+ goto bailout;
+ ret = mapi_get_row_count(hdl) == 1;
+ while ((mapi_fetch_row(hdl)) != 0) {
+ if (mapi_error(mid))
+ goto bailout;
+ }
+ if (mapi_error(mid))
+ goto bailout;
+ mapi_close_handle(hdl);
+ answer = ret;
+ return ret;
+
+bailout:
+ if (hdl) {
+ if (mapi_result_error(hdl))
+ mapi_explain_result(hdl, stderr);
+ else
+ mapi_explain_query(hdl, stderr);
+ mapi_close_handle(hdl);
+ } else
+ mapi_explain(mid, stderr);
+ return false;
+}
+
+static bool
has_table_partitions(Mapi mid)
{
MapiHdl hdl;
@@ -2534,11 +2570,28 @@ dump_database(Mapi mid, stream *toConsol
"ORDER BY s.name, t.sqlname";
const char *users =
has_schema_path(mid) ?
+ has_schema_max_memory(mid) ?
"SELECT ui.name, "
"ui.fullname, "
"sys.password_hash(ui.name), "
"s.name, "
- "ui.schema_path "
+ "ui.schema_path, "
+ "ui.max_memory, "
+ "ui.max_workers, "
+ "ui.optimizer, "
+ "au.name "
+ "FROM sys.db_user_info ui LEFT OUTER JOIN sys.auths au on
ui.default_role = au.id, "
+ "sys.schemas s "
+ "WHERE ui.default_schema = s.id "
+ "AND ui.name <> 'monetdb' "
+ "AND ui.name <> '.snapshot' "
+ "ORDER BY ui.name" :
+ "SELECT ui.name, "
+ "ui.fullname, "
+ "sys.password_hash(ui.name), "
+ "s.name, "
+ "ui.schema_path, "
+ "0, 0, 'default_pipe', cast(null as clob) "
"FROM sys.db_user_info ui, "
"sys.schemas s "
"WHERE ui.default_schema = s.id "
@@ -2549,7 +2602,8 @@ dump_database(Mapi mid, stream *toConsol
"ui.fullname, "
"sys.password_hash(ui.name), "
"s.name, "
- "cast(null as clob) "
+ "cast(null as clob), "
+ "0, 0, 'default_pipe', cast(null as clob) "
"FROM sys.db_user_info ui, "
"sys.schemas s "
"WHERE ui.default_schema = s.id "
@@ -2824,6 +2878,10 @@ dump_database(Mapi mid, stream *toConsol
const char *pwhash = mapi_fetch_field(hdl, 2);
const char *sname = mapi_fetch_field(hdl, 3);
const char *spath = mapi_fetch_field(hdl, 4);
+ const char *mmemory = mapi_fetch_field(hdl, 5);
+ const char *mworkers = mapi_fetch_field(hdl, 6);
+ const char *optimizer = mapi_fetch_field(hdl, 7);
+ const char *defrole = mapi_fetch_field(hdl, 8);
mnstr_printf(toConsole, "CREATE USER ");
dquoted_print(toConsole, uname, " ");
@@ -2837,6 +2895,20 @@ dump_database(Mapi mid, stream *toConsol
mnstr_printf(toConsole, " SCHEMA PATH ");
squoted_print(toConsole, spath, '\'', false);
}
+ if (mmemory && strcmp(mmemory, "0") != 0) {
+ mnstr_printf(toConsole, " MAX_MEMORY %s",
mmemory);
+ }
+ if (mworkers && strcmp(mworkers, "0") != 0) {
+ mnstr_printf(toConsole, " MAX_WORKERS %s",
mworkers);
+ }
+ if (optimizer && strcmp(optimizer, "default_pipe") !=
0) {
+ mnstr_printf(toConsole, " OPTIMIZER ");
+ squoted_print(toConsole, optimizer, '\'',
false);
+ }
+ if (defrole && strcmp(defrole, uname) != 0) {
+ mnstr_printf(toConsole, " DEFAULT ROLE ");
+ dquoted_print(toConsole, defrole, NULL);
+ }
mnstr_printf(toConsole, ";\n");
}
if (mapi_error(mid))
diff --git a/sql/server/sql_parser.y b/sql/server/sql_parser.y
--- a/sql/server/sql_parser.y
+++ b/sql/server/sql_parser.y
@@ -1492,10 +1492,10 @@ role_def:
append_string(l, $8);
append_list(l, $9);
append_int(l, $4);
- append_lng(l, $10);
- append_int(l, $11);
+ append_lng(l, $10);
+ append_int(l, $11);
append_string(l, $12);
- append_string(l, $13);
+ append_string(l, $13);
$$ = _symbol_create_list( SQL_CREATE_USER, l ); }
;
@@ -5474,17 +5474,19 @@ non_reserved_word:
| ACTION { $$ = sa_strdup(SA, "action"); }
| ANALYZE { $$ = sa_strdup(SA, "analyze"); }
| AUTO_COMMIT { $$ = sa_strdup(SA, "auto_commit"); }
-| BIG { $$ = sa_strdup(SA, "big"); }
+| BIG { $$ = sa_strdup(SA, "big"); }
| CACHE { $$ = sa_strdup(SA, "cache"); }
| CENTURY { $$ = sa_strdup(SA, "century"); }
| CLIENT { $$ = sa_strdup(SA, "client"); }
| COMMENT { $$ = sa_strdup(SA, "comment"); }
| DATA { $$ = sa_strdup(SA, "data"); }
+| SQL_DEBUG { $$ = sa_strdup(SA, "debug"); }
| DECADE { $$ = sa_strdup(SA, "decade"); }
-| ENDIAN { $$ = sa_strdup(SA, "endian"); }
+| DIAGNOSTICS { $$ = sa_strdup(SA, "diagnostics"); }
+| DOW { $$ = sa_strdup(SA, "dow"); }
+| DOY { $$ = sa_strdup(SA, "doy"); }
+| ENDIAN { $$ = sa_strdup(SA, "endian"); }
| EPOCH { $$ = sa_strdup(SA, "epoch"); }
-| SQL_DEBUG { $$ = sa_strdup(SA, "debug"); }
-| DIAGNOSTICS { $$ = sa_strdup(SA, "diagnostics"); }
| SQL_EXPLAIN { $$ = sa_strdup(SA, "explain"); }
| FIRST { $$ = sa_strdup(SA, "first"); }
| GEOMETRY { $$ = sa_strdup(SA, "geometry"); }
@@ -5493,14 +5495,17 @@ non_reserved_word:
| KEY { $$ = sa_strdup(SA, "key"); }
| LAST { $$ = sa_strdup(SA, "last"); }
| LEVEL { $$ = sa_strdup(SA, "level"); }
-| LITTLE { $$ = sa_strdup(SA, "little"); }
+| LITTLE { $$ = sa_strdup(SA, "little"); }
+| MAX_MEMORY { $$ = sa_strdup(SA, "max_memory"); }
| MAXVALUE { $$ = sa_strdup(SA, "maxvalue"); }
-| MINMAX { $$ = sa_strdup(SA, "MinMax"); }
+| MAX_WORKERS { $$ = sa_strdup(SA, "max_workers"); }
+| MINMAX { $$ = sa_strdup(SA, "minmax"); }
| MINVALUE { $$ = sa_strdup(SA, "minvalue"); }
| sqlNAME { $$ = sa_strdup(SA, "name"); }
-| NATIVE { $$ = sa_strdup(SA, "native"); }
+| NATIVE { $$ = sa_strdup(SA, "native"); }
| NULLS { $$ = sa_strdup(SA, "nulls"); }
| OBJECT { $$ = sa_strdup(SA, "object"); }
+| OPTIMIZER { $$ = sa_strdup(SA, "optimizer"); }
| OPTIONS { $$ = sa_strdup(SA, "options"); }
| PASSWORD { $$ = sa_strdup(SA, "password"); }
| PATH { $$ = sa_strdup(SA, "path"); }
@@ -5518,13 +5523,11 @@ non_reserved_word:
| STORAGE { $$ = sa_strdup(SA, "storage"); }
| TEMP { $$ = sa_strdup(SA, "temp"); }
| TEMPORARY { $$ = sa_strdup(SA, "temporary"); }
-| UNLOGGED { $$ = sa_strdup(SA, "unlogged"); }
| sqlTEXT { $$ = sa_strdup(SA, "text"); }
| SQL_TRACE { $$ = sa_strdup(SA, "trace"); }
| TYPE { $$ = sa_strdup(SA, "type"); }
+| UNLOGGED { $$ = sa_strdup(SA, "unlogged"); }
| WEEK { $$ = sa_strdup(SA, "week"); }
-| DOW { $$ = sa_strdup(SA, "dow"); }
-| DOY { $$ = sa_strdup(SA, "doy"); }
| ZONE { $$ = sa_strdup(SA, "zone"); }
/* SQL/XML non reserved words */
diff --git a/sql/server/sql_scan.c b/sql/server/sql_scan.c
--- a/sql/server/sql_scan.c
+++ b/sql/server/sql_scan.c
@@ -278,6 +278,9 @@ scanner_init_keywords(void)
failed += keywords_insert("SESSION_USER", SESSION_USER);
failed += keywords_insert("CURRENT_SCHEMA", CURRENT_SCHEMA);
failed += keywords_insert("SESSION", sqlSESSION);
+ failed += keywords_insert("MAX_MEMORY", MAX_MEMORY);
+ failed += keywords_insert("MAX_WORKERS", MAX_WORKERS);
+ failed += keywords_insert("OPTIMIZER", OPTIMIZER);
failed += keywords_insert("RIGHT", RIGHT);
failed += keywords_insert("SCHEMA", SCHEMA);
diff --git a/sql/test/testdb/Tests/dump-nogeom.stable.out
b/sql/test/testdb/Tests/dump-nogeom.stable.out
--- a/sql/test/testdb/Tests/dump-nogeom.stable.out
+++ b/sql/test/testdb/Tests/dump-nogeom.stable.out
@@ -1,9 +1,12 @@
START TRANSACTION;
SET SCHEMA "sys";
-CREATE USER "testuser" WITH ENCRYPTED PASSWORD
'e9e633097ab9ceb3e48ec3f70ee2beba41d05d5420efee5da85f97d97005727587fda33ef4ff2322088f4c79e8133cc9cd9f3512f4d3a303cbdb5bc585415a00'
NAME 'Test User' SCHEMA "sys";
+CREATE USER "testuser" WITH ENCRYPTED PASSWORD
'e9e633097ab9ceb3e48ec3f70ee2beba41d05d5420efee5da85f97d97005727587fda33ef4ff2322088f4c79e8133cc9cd9f3512f4d3a303cbdb5bc585415a00'
NAME 'Test User' SCHEMA "sys" MAX_MEMORY 1000000000 MAX_WORKERS 5 OPTIMIZER
'minimal_pipe' DEFAULT ROLE "monetdb";
+CREATE USER "testuser2" WITH ENCRYPTED PASSWORD
'e9e633097ab9ceb3e48ec3f70ee2beba41d05d5420efee5da85f97d97005727587fda33ef4ff2322088f4c79e8133cc9cd9f3512f4d3a303cbdb5bc585415a00'
NAME 'Second Test User' SCHEMA "sys";
CREATE SCHEMA "testschema" AUTHORIZATION "testuser";
COMMENT ON SCHEMA "testschema" IS 'a schema used for testing';
+CREATE SCHEMA "testuser2" AUTHORIZATION "testuser2";
ALTER USER "testuser" SET SCHEMA "testschema";
+ALTER USER "testuser2" SET SCHEMA "testuser2";
GRANT COPY FROM TO "testuser";
CREATE SEQUENCE "testschema"."selfref_seq" AS INTEGER;
COMMENT ON SEQUENCE "testschema"."selfref_seq" IS 'sequence number for selfref
table';
diff --git a/sql/test/testdb/Tests/dump.stable.out
b/sql/test/testdb/Tests/dump.stable.out
--- a/sql/test/testdb/Tests/dump.stable.out
+++ b/sql/test/testdb/Tests/dump.stable.out
@@ -1,9 +1,12 @@
START TRANSACTION;
SET SCHEMA "sys";
-CREATE USER "testuser" WITH ENCRYPTED PASSWORD
'e9e633097ab9ceb3e48ec3f70ee2beba41d05d5420efee5da85f97d97005727587fda33ef4ff2322088f4c79e8133cc9cd9f3512f4d3a303cbdb5bc585415a00'
NAME 'Test User' SCHEMA "sys";
+CREATE USER "testuser" WITH ENCRYPTED PASSWORD
'e9e633097ab9ceb3e48ec3f70ee2beba41d05d5420efee5da85f97d97005727587fda33ef4ff2322088f4c79e8133cc9cd9f3512f4d3a303cbdb5bc585415a00'
NAME 'Test User' SCHEMA "sys" MAX_MEMORY 1000000000 MAX_WORKERS 5 OPTIMIZER
'minimal_pipe' DEFAULT ROLE "monetdb";
+CREATE USER "testuser2" WITH ENCRYPTED PASSWORD
'e9e633097ab9ceb3e48ec3f70ee2beba41d05d5420efee5da85f97d97005727587fda33ef4ff2322088f4c79e8133cc9cd9f3512f4d3a303cbdb5bc585415a00'
NAME 'Second Test User' SCHEMA "sys";
CREATE SCHEMA "testschema" AUTHORIZATION "testuser";
COMMENT ON SCHEMA "testschema" IS 'a schema used for testing';
+CREATE SCHEMA "testuser2" AUTHORIZATION "testuser2";
ALTER USER "testuser" SET SCHEMA "testschema";
+ALTER USER "testuser2" SET SCHEMA "testuser2";
GRANT COPY FROM TO "testuser";
CREATE SEQUENCE "testschema"."selfref_seq" AS INTEGER;
COMMENT ON SEQUENCE "testschema"."selfref_seq" IS 'sequence number for selfref
table';
diff --git a/sql/test/testdb/Tests/load.test b/sql/test/testdb/Tests/load.test
--- a/sql/test/testdb/Tests/load.test
+++ b/sql/test/testdb/Tests/load.test
@@ -2,7 +2,10 @@ statement ok
START TRANSACTION
statement ok
-CREATE USER "testuser" WITH ENCRYPTED PASSWORD
'e9e633097ab9ceb3e48ec3f70ee2beba41d05d5420efee5da85f97d97005727587fda33ef4ff2322088f4c79e8133cc9cd9f3512f4d3a303cbdb5bc585415a00'
NAME 'Test User' SCHEMA "sys"
+CREATE USER "testuser" WITH ENCRYPTED PASSWORD
'e9e633097ab9ceb3e48ec3f70ee2beba41d05d5420efee5da85f97d97005727587fda33ef4ff2322088f4c79e8133cc9cd9f3512f4d3a303cbdb5bc585415a00'
NAME 'Test User' SCHEMA "sys" MAX_MEMORY 1000000000 MAX_WORKERS 5 OPTIMIZER
'minimal_pipe' DEFAULT ROLE "monetdb"
+
+statement ok
+CREATE USER "testuser2" WITH ENCRYPTED PASSWORD
'e9e633097ab9ceb3e48ec3f70ee2beba41d05d5420efee5da85f97d97005727587fda33ef4ff2322088f4c79e8133cc9cd9f3512f4d3a303cbdb5bc585415a00'
NAME 'Second Test User'
statement ok
CREATE SCHEMA "testschema" AUTHORIZATION "testuser"
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
