Changeset: 06685aae3a6b for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=06685aae3a6b
Modified Files:
sql/backends/monet5/sql_upgrades.c
sql/scripts/52_describe.sql
sql/scripts/76_dump.sql
sql/test/emptydb/Tests/check.stable.out
sql/test/emptydb/Tests/check.stable.out.int128
Branch: default
Log Message:
More sys and fix type (it's "privilege", not "priviledge").
diffs (truncated from 786 to 300 lines):
diff --git a/sql/backends/monet5/sql_upgrades.c
b/sql/backends/monet5/sql_upgrades.c
--- a/sql/backends/monet5/sql_upgrades.c
+++ b/sql/backends/monet5/sql_upgrades.c
@@ -2678,11 +2678,11 @@ sql_update_default(Client c, mvc *sql, c
" WHERE c.table_id = t.id)
col,\n"
" CASE\n"
" WHEN ts.table_type_name =
'REMOTE TABLE' THEN\n"
- "
get_remote_table_expressions(s.name, t.name)\n"
+ "
sys.get_remote_table_expressions(s.name, t.name)\n"
" WHEN ts.table_type_name =
'MERGE TABLE' THEN\n"
- "
get_merge_table_partition_expressions(t.id)\n"
+ "
sys.get_merge_table_partition_expressions(t.id)\n"
" WHEN ts.table_type_name =
'VIEW' THEN\n"
- " schema_guard(s.name,
t.name, t.query)\n"
+ "
sys.schema_guard(s.name, t.name, t.query)\n"
" ELSE\n"
" ''\n"
" END opt\n"
@@ -2745,7 +2745,7 @@ sql_update_default(Client c, mvc *sql, c
" CASE WHEN a.type IS NULL
THEN\n"
" s.name || '.' ||
f.name || '()'\n"
" ELSE\n"
- " s.name || '.' ||
f.name || '(' || group_concat(describe_type(a.type, a.type_digits,
a.type_scale), ',') OVER (PARTITION BY f.id ORDER BY a.number) || ')'\n"
+ " s.name || '.' ||
f.name || '(' || group_concat(sys.describe_type(a.type, a.type_digits,
a.type_scale), ',') OVER (PARTITION BY f.id ORDER BY a.number) || ')'\n"
" END,\n"
" a.number\n"
" FROM sys.schemas s,
sys.function_types ft, sys.functions f LEFT JOIN sys.args a ON f.id =
a.func_id\n"
@@ -2906,7 +2906,7 @@ sql_update_default(Client c, mvc *sql, c
"CREATE FUNCTION
sys.describe_columns(schemaName string, tableName string)\n"
" RETURNS TABLE(name string, type
string, digits integer, scale integer, Nulls boolean, cDefault string, number
integer, sqltype string, remark string)\n"
"BEGIN\n"
- " RETURN SELECT c.name, c.\"type\",
c.type_digits, c.type_scale, c.\"null\", c.\"default\", c.number,
describe_type(c.\"type\", c.type_digits, c.type_scale), com.remark\n"
+ " RETURN SELECT c.name, c.\"type\",
c.type_digits, c.type_scale, c.\"null\", c.\"default\", c.number,
sys.describe_type(c.\"type\", c.type_digits, c.type_scale), com.remark\n"
" FROM sys._tables t,
sys.schemas s, sys._columns c\n"
" LEFT OUTER JOIN sys.comments
com ON c.id = com.id\n"
" WHERE c.table_id = t.id\n"
@@ -2961,7 +2961,7 @@ sql_update_default(Client c, mvc *sql, c
" AND ui.name <> '.snapshot'\n"
" AND s.name <> 'sys';\n"
"\n"
- "CREATE VIEW
sys.dump_grant_user_priviledges AS\n"
+ "CREATE VIEW
sys.dump_grant_user_privileges AS\n"
" SELECT\n"
" 'GRANT ' || sys.dq(a2.name) ||
' ' || ifthenelse(a1.name = 'public', 'PUBLIC', sys.dq(a1.name)) || ';' stmt\n"
" FROM sys.auths a1, sys.auths a2,
sys.user_role ur\n"
@@ -2996,7 +2996,7 @@ sql_update_default(Client c, mvc *sql, c
"\n"
"CREATE VIEW sys.dump_partition_tables
AS\n"
" SELECT\n"
- " ALTER_TABLE(m_sch, m_tbl) || '
ADD TABLE ' || sys.FQN(p_sch, p_tbl) ||\n"
+ " sys.ALTER_TABLE(m_sch, m_tbl)
|| ' ADD TABLE ' || sys.FQN(p_sch, p_tbl) ||\n"
" CASE \n"
" WHEN tpe = 'VALUES' THEN '
AS PARTITION IN (' || pvalues || ')'\n"
" WHEN tpe = 'RANGE' THEN '
AS PARTITION FROM ' || ifthenelse(minimum IS NOT NULL, sys.SQ(minimum), 'RANGE
MINVALUE') || ' TO ' || ifthenelse(maximum IS NOT NULL, sys.SQ(maximum), 'RANGE
MAXVALUE')\n"
@@ -3026,7 +3026,7 @@ sql_update_default(Client c, mvc *sql, c
" FROM sys.describe_sequences;\n"
"\n"
"CREATE VIEW sys.dump_functions AS\n"
- " SELECT f.o o, schema_guard(f.sch,
f.fun, f.def) stmt FROM sys.describe_functions f;\n"
+ " SELECT f.o o,
sys.schema_guard(f.sch, f.fun, f.def) stmt FROM sys.describe_functions f;\n"
"\n"
"CREATE VIEW sys.dump_tables AS\n"
" SELECT\n"
@@ -3040,7 +3040,7 @@ sql_update_default(Client c, mvc *sql, c
" FROM sys.describe_tables t;\n"
"\n"
"CREATE VIEW sys.dump_triggers AS\n"
- " SELECT schema_guard(sch, tab, def)
stmt FROM sys.describe_triggers;\n"
+ " SELECT sys.schema_guard(sch, tab,
def) stmt FROM sys.describe_triggers;\n"
"\n"
"CREATE VIEW sys.dump_comments AS\n"
" SELECT 'COMMENT ON ' || c.tpe || '
' || c.fqn || ' IS ' || sys.SQ(c.rem) || ';' stmt FROM sys.describe_comments
c;\n"
@@ -3080,7 +3080,7 @@ sql_update_default(Client c, mvc *sql, c
" RETURN\n"
" CASE\n"
" WHEN (t = 'varchar' OR t
='char' OR t = 'clob' OR t = 'json' OR t = 'geometry' OR t = 'url') THEN\n"
- " 'CASE WHEN ' ||
sys.DQ(s) || ' IS NULL THEN ''null'' ELSE ' || 'esc(' || sys.DQ(s) || ')' || '
END'\n"
+ " 'CASE WHEN ' ||
sys.DQ(s) || ' IS NULL THEN ''null'' ELSE ' || 'sys.esc(' || sys.DQ(s) || ')'
|| ' END'\n"
" ELSE\n"
" 'CASE WHEN ' ||
sys.DQ(s) || ' IS NULL THEN ''null'' ELSE CAST(' || sys.DQ(s) || ' AS STRING)
END'\n"
" END;\n"
@@ -3107,7 +3107,7 @@ sql_update_default(Client c, mvc *sql, c
" SET COPY_INTO_STMT = 'COPY
' || _cnt || ' RECORDS INTO ' || sys.FQN(sch, tbl) || '(' || sys.DQ(cname);\n"
"\n"
" DECLARE SELECT_DATA_STMT
STRING;\n"
- " SET SELECT_DATA_STMT =
'SELECT (SELECT COUNT(*) FROM sys.dump_statements) + RANK() OVER(), ' ||
prepare_esc(cname, ctype);\n"
+ " SET SELECT_DATA_STMT =
'SELECT (SELECT COUNT(*) FROM sys.dump_statements) + RANK() OVER(), ' ||
sys.prepare_esc(cname, ctype);\n"
"\n"
" DECLARE M INT;\n"
" SET M = (SELECT MAX(c.id)
FROM sys.columns c, sys.tables t WHERE c.table_id = t.id AND t.name = tbl);\n"
@@ -3117,7 +3117,7 @@ sql_update_default(Client c, mvc *sql, c
" SET cname = (SELECT
c.name FROM sys.columns c WHERE c.id = k);\n"
" SET ctype = (SELECT
c.type FROM sys.columns c WHERE c.id = k);\n"
" SET COPY_INTO_STMT =
(COPY_INTO_STMT || ', ' || sys.DQ(cname));\n"
- " SET SELECT_DATA_STMT =
SELECT_DATA_STMT || '|| ''|'' || ' || prepare_esc(cname, ctype);\n"
+ " SET SELECT_DATA_STMT =
SELECT_DATA_STMT || '|| ''|'' || ' || sys.prepare_esc(cname, ctype);\n"
" END WHILE;\n"
"\n"
" SET COPY_INTO_STMT =
(COPY_INTO_STMT || ') FROM STDIN USING DELIMITERS ''|'',E''\\\\n'',''\"'';');\n"
@@ -3145,13 +3145,13 @@ sql_update_default(Client c, mvc *sql, c
" WHILE i < M DO\n"
" set sch = (SELECT s.name
FROM sys.tables t, sys.schemas s WHERE s.id = t.schema_id AND t.id = i);\n"
" set tbl = (SELECT t.name
FROM sys.tables t, sys.schemas s WHERE s.id = t.schema_id AND t.id = i);\n"
- " CALL _dump_table_data(sch,
tbl);\n"
+ " CALL
sys._dump_table_data(sch, tbl);\n"
" SET i = (SELECT MIN(t.id)
FROM sys.tables t, sys.table_types ts WHERE t.type = ts.table_type_id AND
ts.table_type_name = 'TABLE' AND NOT t.system AND t.id > i);\n"
" END WHILE;\n"
"\n"
" set sch = (SELECT s.name FROM
sys.tables t, sys.schemas s WHERE s.id = t.schema_id AND t.id = i);\n"
" set tbl = (SELECT t.name FROM
sys.tables t, sys.schemas s WHERE s.id = t.schema_id AND t.id = i);\n"
- " CALL _dump_table_data(sch,
tbl);\n"
+ " CALL sys._dump_table_data(sch,
tbl);\n"
" END IF;\n"
"END;\n"
"\n"
@@ -3168,7 +3168,7 @@ sql_update_default(Client c, mvc *sql, c
" INSERT INTO sys.dump_statements
SELECT (SELECT COUNT(*) FROM sys.dump_statements) + RANK() OVER(), stmt FROM
sys.dump_create_schemas;\n"
" INSERT INTO sys.dump_statements
SELECT (SELECT COUNT(*) FROM sys.dump_statements) + RANK() OVER(), stmt FROM
sys.dump_user_defined_types;\n"
" INSERT INTO sys.dump_statements
SELECT (SELECT COUNT(*) FROM sys.dump_statements) + RANK() OVER(), stmt FROM
sys.dump_add_schemas_to_users;\n"
- " INSERT INTO sys.dump_statements
SELECT (SELECT COUNT(*) FROM sys.dump_statements) + RANK() OVER(), stmt FROM
sys.dump_grant_user_priviledges;\n"
+ " INSERT INTO sys.dump_statements
SELECT (SELECT COUNT(*) FROM sys.dump_statements) + RANK() OVER(), stmt FROM
sys.dump_grant_user_privileges;\n"
" INSERT INTO sys.dump_statements
SELECT (SELECT COUNT(*) FROM sys.dump_statements) + RANK() OVER(), stmt FROM
sys.dump_sequences;\n"
" INSERT INTO sys.dump_statements
SELECT (SELECT COUNT(*) FROM sys.dump_statements) + RANK() OVER(), stmt FROM
sys.dump_start_sequences;\n"
"\n"
@@ -3193,7 +3193,7 @@ sql_update_default(Client c, mvc *sql, c
" INSERT INTO sys.dump_statements
SELECT (SELECT COUNT(*) FROM sys.dump_statements) + RANK() OVER(), stmt FROM
sys.dump_privileges;\n"
"\n"
" IF NOT DESCRIBE THEN\n"
- " CALL dump_table_data();\n"
+ " CALL sys.dump_table_data();\n"
" END IF;\n"
"\n"
" INSERT INTO sys.dump_statements
VALUES ( (SELECT COUNT(*) FROM sys.dump_statements) + 1, 'COMMIT;');\n"
@@ -3269,7 +3269,7 @@ sql_update_default(Client c, mvc *sql, c
" name =
'dump_create_users' OR\n"
" name =
'dump_create_schemas' OR\n"
" name =
'dump_add_schemas_to_users' OR\n"
- " name =
'dump_grant_user_priviledges' OR\n"
+ " name =
'dump_grant_user_privileges' OR\n"
" name =
'dump_table_constraint_type' OR\n"
" name = 'dump_indices'
OR\n"
" name =
'dump_column_defaults' OR\n"
diff --git a/sql/scripts/52_describe.sql b/sql/scripts/52_describe.sql
--- a/sql/scripts/52_describe.sql
+++ b/sql/scripts/52_describe.sql
@@ -296,11 +296,11 @@ CREATE VIEW sys.describe_tables AS
WHERE c.table_id = t.id) col,
CASE
WHEN ts.table_type_name = 'REMOTE TABLE' THEN
- get_remote_table_expressions(s.name, t.name)
+ sys.get_remote_table_expressions(s.name, t.name)
WHEN ts.table_type_name = 'MERGE TABLE' THEN
- get_merge_table_partition_expressions(t.id)
+ sys.get_merge_table_partition_expressions(t.id)
WHEN ts.table_type_name = 'VIEW' THEN
- schema_guard(s.name, t.name, t.query)
+ sys.schema_guard(s.name, t.name, t.query)
ELSE
''
END opt
@@ -363,7 +363,7 @@ CREATE VIEW sys.fully_qualified_function
CASE WHEN a.type IS NULL THEN
s.name || '.' || f.name || '()'
ELSE
- s.name || '.' || f.name || '(' ||
group_concat(describe_type(a.type, a.type_digits, a.type_scale), ',') OVER
(PARTITION BY f.id ORDER BY a.number) || ')'
+ s.name || '.' || f.name || '(' ||
group_concat(sys.describe_type(a.type, a.type_digits, a.type_scale), ',') OVER
(PARTITION BY f.id ORDER BY a.number) || ')'
END,
a.number
FROM sys.schemas s, sys.function_types ft, sys.functions f LEFT
JOIN sys.args a ON f.id = a.func_id
@@ -524,7 +524,7 @@ CREATE VIEW sys.describe_functions AS
CREATE FUNCTION sys.describe_columns(schemaName string, tableName string)
RETURNS TABLE(name string, type string, digits integer, scale integer,
Nulls boolean, cDefault string, number integer, sqltype string, remark string)
BEGIN
- RETURN SELECT c.name, c."type", c.type_digits, c.type_scale, c."null",
c."default", c.number, describe_type(c."type", c.type_digits, c.type_scale),
com.remark
+ RETURN SELECT c.name, c."type", c.type_digits, c.type_scale, c."null",
c."default", c.number, sys.describe_type(c."type", c.type_digits,
c.type_scale), com.remark
FROM sys._tables t, sys.schemas s, sys._columns c
LEFT OUTER JOIN sys.comments com ON c.id = com.id
WHERE c.table_id = t.id
diff --git a/sql/scripts/76_dump.sql b/sql/scripts/76_dump.sql
--- a/sql/scripts/76_dump.sql
+++ b/sql/scripts/76_dump.sql
@@ -29,7 +29,7 @@ CREATE VIEW sys.dump_add_schemas_to_user
AND ui.name <> '.snapshot'
AND s.name <> 'sys';
-CREATE VIEW sys.dump_grant_user_priviledges AS
+CREATE VIEW sys.dump_grant_user_privileges AS
SELECT
'GRANT ' || sys.dq(a2.name) || ' ' || ifthenelse(a1.name =
'public', 'PUBLIC', sys.dq(a1.name)) || ';' stmt
FROM sys.auths a1, sys.auths a2, sys.user_role ur
@@ -64,7 +64,7 @@ CREATE VIEW sys.dump_foreign_keys AS
CREATE VIEW sys.dump_partition_tables AS
SELECT
- ALTER_TABLE(m_sch, m_tbl) || ' ADD TABLE ' || sys.FQN(p_sch,
p_tbl) ||
+ sys.ALTER_TABLE(m_sch, m_tbl) || ' ADD TABLE ' ||
sys.FQN(p_sch, p_tbl) ||
CASE
WHEN tpe = 'VALUES' THEN ' AS PARTITION IN (' ||
pvalues || ')'
WHEN tpe = 'RANGE' THEN ' AS PARTITION FROM ' ||
ifthenelse(minimum IS NOT NULL, sys.SQ(minimum), 'RANGE MINVALUE') || ' TO ' ||
ifthenelse(maximum IS NOT NULL, sys.SQ(maximum), 'RANGE MAXVALUE')
@@ -94,7 +94,7 @@ CREATE VIEW sys.dump_start_sequences AS
FROM sys.describe_sequences;
CREATE VIEW sys.dump_functions AS
- SELECT f.o o, schema_guard(f.sch, f.fun, f.def) stmt FROM
sys.describe_functions f;
+ SELECT f.o o, sys.schema_guard(f.sch, f.fun, f.def) stmt FROM
sys.describe_functions f;
CREATE VIEW sys.dump_tables AS
SELECT
@@ -108,7 +108,7 @@ CREATE VIEW sys.dump_tables AS
FROM sys.describe_tables t;
CREATE VIEW sys.dump_triggers AS
- SELECT schema_guard(sch, tab, def) stmt FROM sys.describe_triggers;
+ SELECT sys.schema_guard(sch, tab, def) stmt FROM sys.describe_triggers;
CREATE VIEW sys.dump_comments AS
SELECT 'COMMENT ON ' || c.tpe || ' ' || c.fqn || ' IS ' ||
sys.SQ(c.rem) || ';' stmt FROM sys.describe_comments c;
@@ -148,7 +148,7 @@ BEGIN
RETURN
CASE
WHEN (t = 'varchar' OR t ='char' OR t = 'clob' OR t = 'json' OR t
= 'geometry' OR t = 'url') THEN
- 'CASE WHEN ' || sys.DQ(s) || ' IS NULL THEN ''null'' ELSE ' ||
'esc(' || sys.DQ(s) || ')' || ' END'
+ 'CASE WHEN ' || sys.DQ(s) || ' IS NULL THEN ''null'' ELSE ' ||
'sys.esc(' || sys.DQ(s) || ')' || ' END'
ELSE
'CASE WHEN ' || sys.DQ(s) || ' IS NULL THEN ''null'' ELSE
CAST(' || sys.DQ(s) || ' AS STRING) END'
END;
@@ -180,7 +180,7 @@ CREATE PROCEDURE sys._dump_table_data(sc
SET COPY_INTO_STMT = 'COPY ' || _cnt || ' RECORDS INTO
' || sys.FQN(sch, tbl) || '(' || sys.DQ(cname);
DECLARE SELECT_DATA_STMT STRING;
- SET SELECT_DATA_STMT = 'SELECT (SELECT COUNT(*) FROM
sys.dump_statements) + RANK() OVER(), ' || prepare_esc(cname, ctype);
+ SET SELECT_DATA_STMT = 'SELECT (SELECT COUNT(*) FROM
sys.dump_statements) + RANK() OVER(), ' || sys.prepare_esc(cname, ctype);
DECLARE M INT;
SET M = (SELECT MAX(c.id) FROM sys.columns c,
sys.tables t WHERE c.table_id = t.id AND t.name = tbl);
@@ -190,7 +190,7 @@ CREATE PROCEDURE sys._dump_table_data(sc
SET cname = (SELECT c.name FROM sys.columns c
WHERE c.id = k);
SET ctype = (SELECT c.type FROM sys.columns c
WHERE c.id = k);
SET COPY_INTO_STMT = (COPY_INTO_STMT || ', ' ||
sys.DQ(cname));
- SET SELECT_DATA_STMT = SELECT_DATA_STMT || '||
''|'' || ' || prepare_esc(cname, ctype);
+ SET SELECT_DATA_STMT = SELECT_DATA_STMT || '||
''|'' || ' || sys.prepare_esc(cname, ctype);
END WHILE;
SET COPY_INTO_STMT = (COPY_INTO_STMT || ') FROM STDIN
USING DELIMITERS ''|'',E''\\n'',''"'';');
@@ -218,13 +218,13 @@ CREATE PROCEDURE sys.dump_table_data() B
WHILE i < M DO
set sch = (SELECT s.name FROM sys.tables t, sys.schemas
s WHERE s.id = t.schema_id AND t.id = i);
set tbl = (SELECT t.name FROM sys.tables t, sys.schemas
s WHERE s.id = t.schema_id AND t.id = i);
- CALL _dump_table_data(sch, tbl);
+ CALL sys._dump_table_data(sch, tbl);
SET i = (SELECT MIN(t.id) FROM sys.tables t,
sys.table_types ts WHERE t.type = ts.table_type_id AND ts.table_type_name =
'TABLE' AND NOT t.system AND t.id > i);
END WHILE;
set sch = (SELECT s.name FROM sys.tables t, sys.schemas s WHERE
s.id = t.schema_id AND t.id = i);
set tbl = (SELECT t.name FROM sys.tables t, sys.schemas s WHERE
s.id = t.schema_id AND t.id = i);
- CALL _dump_table_data(sch, tbl);
+ CALL sys._dump_table_data(sch, tbl);
END IF;
END;
@@ -241,7 +241,7 @@ BEGIN
INSERT INTO sys.dump_statements SELECT (SELECT COUNT(*) FROM
sys.dump_statements) + RANK() OVER(), stmt FROM sys.dump_create_schemas;
INSERT INTO sys.dump_statements SELECT (SELECT COUNT(*) FROM
sys.dump_statements) + RANK() OVER(), stmt FROM sys.dump_user_defined_types;
INSERT INTO sys.dump_statements SELECT (SELECT COUNT(*) FROM
sys.dump_statements) + RANK() OVER(), stmt FROM sys.dump_add_schemas_to_users;
- INSERT INTO sys.dump_statements SELECT (SELECT COUNT(*) FROM
sys.dump_statements) + RANK() OVER(), stmt FROM sys.dump_grant_user_priviledges;
+ INSERT INTO sys.dump_statements SELECT (SELECT COUNT(*) FROM
sys.dump_statements) + RANK() OVER(), stmt FROM sys.dump_grant_user_privileges;
INSERT INTO sys.dump_statements SELECT (SELECT COUNT(*) FROM
sys.dump_statements) + RANK() OVER(), stmt FROM sys.dump_sequences;
INSERT INTO sys.dump_statements SELECT (SELECT COUNT(*) FROM
sys.dump_statements) + RANK() OVER(), stmt FROM sys.dump_start_sequences;
@@ -266,7 +266,7 @@ BEGIN
INSERT INTO sys.dump_statements SELECT (SELECT COUNT(*) FROM
sys.dump_statements) + RANK() OVER(), stmt FROM sys.dump_privileges;
IF NOT DESCRIBE THEN
- CALL dump_table_data();
+ CALL sys.dump_table_data();
END IF;
--TODO Improve performance of dump_table_data.
--TODO loaders ,procedures, window and filter sys.functions.
diff --git a/sql/test/emptydb/Tests/check.stable.out
b/sql/test/emptydb/Tests/check.stable.out
--- a/sql/test/emptydb/Tests/check.stable.out
+++ b/sql/test/emptydb/Tests/check.stable.out
@@ -106,7 +106,7 @@
\dSv sys.dump_create_users
\dSv sys.dump_foreign_keys
\dSv sys.dump_functions
-\dSv sys.dump_grant_user_priviledges
+\dSv sys.dump_grant_user_privileges
\dSv sys.dump_indices
\dSv sys.dump_partition_tables
\dSv sys.dump_privileges
@@ -469,7 +469,7 @@ SYSTEM VIEW sys.dump_create_schemas
SYSTEM VIEW sys.dump_create_users
SYSTEM VIEW sys.dump_foreign_keys
SYSTEM VIEW sys.dump_functions
-SYSTEM VIEW sys.dump_grant_user_priviledges
+SYSTEM VIEW sys.dump_grant_user_privileges
SYSTEM VIEW sys.dump_indices
SYSTEM VIEW sys.dump_partition_tables
SYSTEM VIEW sys.dump_privileges
@@ -1033,7 +1033,7 @@ create view sys.describe_indices as with
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
