Changeset: 183e9e27b7f2 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/183e9e27b7f2
Modified Files:
sql/scripts/52_describe.sql
sql/scripts/76_dump.sql
Branch: default
Log Message:
Various improvements to the dump code.
Add columns to helper views so that they can be selected on;
fix dumping of functions, especially in languages other than SQL;
fix dumping of grants by issuing GRANT statements.
We're not there yet...
diffs (truncated from 647 to 300 lines):
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
@@ -145,7 +145,7 @@ BEGIN
END;
END;
-CREATE FUNCTION sys.SQ (s STRING) RETURNS STRING BEGIN RETURN ' ''' ||
sys.replace(s,'''','''''') || ''' '; END;
+CREATE FUNCTION sys.SQ (s STRING) RETURNS STRING BEGIN RETURN '''' ||
sys.replace(s,'''','''''') || ''''; END;
CREATE FUNCTION sys.DQ (s STRING) RETURNS STRING BEGIN RETURN '"' ||
sys.replace(s,'"','""') || '"'; END; --TODO: Figure out why this breaks with
the space
CREATE FUNCTION sys.FQN(s STRING, t STRING) RETURNS STRING BEGIN RETURN
sys.DQ(s) || '.' || sys.DQ(t); END;
CREATE FUNCTION sys.ALTER_TABLE(s STRING, t STRING) RETURNS STRING BEGIN
RETURN 'ALTER TABLE ' || sys.FQN(s, t) || ' '; END;
@@ -514,12 +514,54 @@ CREATE VIEW sys.describe_sequences AS
ORDER BY s.name, seq.name;
CREATE VIEW sys.describe_functions AS
+ WITH func_args_all(func_id, number, max_number, func_arg) AS
+ (
+ SELECT
+ func_id,
+ number,
+ max(number) OVER (PARTITION BY func_id ORDER BY number
DESC),
+ group_concat(sys.dq(name) || ' ' ||
sys.describe_type(type, type_digits, type_scale),', ') OVER (PARTITION BY
func_id ORDER BY number)
+ FROM sys.args
+ WHERE inout = 1
+ ),
+ func_args(func_id, func_arg) AS
+ (
+ SELECT func_id, func_arg
+ FROM func_args_all
+ WHERE number = max_number
+ ),
+ func_rets_all(func_id, number, max_number, func_ret, func_ret_type) AS
+ (
+ SELECT
+ func_id,
+ number,
+ max(number) OVER (PARTITION BY func_id ORDER BY number
DESC),
+ group_concat(sys.dq(name) || ' ' ||
sys.describe_type(type, type_digits, type_scale),', ') OVER (PARTITION BY
func_id ORDER BY number),
+ group_concat(sys.describe_type(type, type_digits,
type_scale),', ') OVER (PARTITION BY func_id ORDER BY number)
+ FROM sys.args
+ WHERE inout = 0
+ ),
+ func_rets(func_id, func_ret, func_ret_type) AS
+ (
+ SELECT
+ func_id,
+ func_ret,
+ func_ret_type
+ FROM func_rets_all
+ WHERE number = max_number
+ )
SELECT
f.id o,
s.name sch,
f.name fun,
- f.func def
- FROM sys.functions f JOIN sys.schemas s ON f.schema_id = s.id WHERE
s.name <> 'tmp' AND NOT f.system;
+ CASE WHEN f.language IN (1, 2) THEN f.func ELSE 'CREATE ' ||
ft.function_type_keyword || ' ' || sys.FQN(s.name, f.name) || '(' ||
coalesce(fa.func_arg, '') || ')' || CASE WHEN f.type = 5 THEN ' RETURNS TABLE
(' || coalesce(fr.func_ret, '') || ')' WHEN f.type IN (1,3) THEN ' RETURNS ' ||
fr.func_ret_type ELSE '' END || CASE WHEN fl.language_keyword IS NULL THEN ''
ELSE ' LANGUAGE ' || fl.language_keyword END || ' ' || f.func END def
+ FROM sys.functions f
+ LEFT OUTER JOIN func_args fa ON fa.func_id = f.id
+ LEFT OUTER JOIN func_rets fr ON fr.func_id = f.id
+ JOIN sys.schemas s ON f.schema_id = s.id
+ JOIN sys.function_types ft ON f.type = ft.function_type_id
+ LEFT OUTER JOIN sys.function_languages fl ON f.language =
fl.language_id
+ WHERE s.name <> 'tmp' AND NOT f.system;
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)
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
@@ -5,145 +5,239 @@
-- Copyright 1997 - July 2008 CWI, August 2008 - 2021 MonetDB B.V.
CREATE VIEW sys.dump_create_roles AS
- SELECT
- 'CREATE ROLE ' || sys.dq(name) || ';' stmt FROM sys.auths
- WHERE name NOT IN (SELECT name FROM sys.db_user_info)
- AND grantor <> 0;
+ SELECT
+ 'CREATE ROLE ' || sys.dq(name) || ';' stmt,
+ name user_name
+ FROM sys.auths
+ WHERE name NOT IN (SELECT name FROM sys.db_user_info)
+ AND grantor <> 0;
CREATE VIEW sys.dump_create_users AS
- SELECT
- 'CREATE USER ' || sys.dq(ui.name) || ' WITH ENCRYPTED
PASSWORD ' ||
- sys.sq(sys.password_hash(ui.name)) ||
- ' NAME ' || sys.sq(ui.fullname) || ' SCHEMA sys;' stmt
- FROM sys.db_user_info ui, sys.schemas s
- WHERE ui.default_schema = s.id
- AND ui.name <> 'monetdb'
- AND ui.name <> '.snapshot';
+ SELECT
+ 'CREATE USER ' || sys.dq(ui.name) || ' WITH ENCRYPTED PASSWORD ' ||
+ sys.sq(sys.password_hash(ui.name)) ||
+ ' NAME ' || sys.sq(ui.fullname) || ' SCHEMA sys;' stmt,
+ ui.name user_name
+ FROM sys.db_user_info ui, sys.schemas s
+ WHERE ui.default_schema = s.id
+ AND ui.name <> 'monetdb'
+ AND ui.name <> '.snapshot';
CREATE VIEW sys.dump_create_schemas AS
- SELECT
- 'CREATE SCHEMA ' || sys.dq(s.name) || ifthenelse(a.name <>
'sysadmin', ' AUTHORIZATION ' || a.name, ' ') || ';' stmt
- FROM sys.schemas s, sys.auths a
- WHERE s.authorization = a.id AND s.system = FALSE;
+ SELECT
+ 'CREATE SCHEMA ' || sys.dq(s.name) || ifthenelse(a.name <> 'sysadmin', '
AUTHORIZATION ' || sys.dq(a.name), ' ') || ';' stmt,
+ s.name schema_name
+ FROM sys.schemas s, sys.auths a
+ WHERE s.authorization = a.id AND s.system = FALSE;
CREATE VIEW sys.dump_add_schemas_to_users AS
- SELECT
- 'ALTER USER ' || sys.dq(ui.name) || ' SET SCHEMA ' ||
sys.dq(s.name) || ';' stmt
- FROM sys.db_user_info ui, sys.schemas s
- WHERE ui.default_schema = s.id
- AND ui.name <> 'monetdb'
- AND ui.name <> '.snapshot'
- AND s.name <> 'sys';
+ SELECT
+ 'ALTER USER ' || sys.dq(ui.name) || ' SET SCHEMA ' || sys.dq(s.name) ||
';' stmt,
+ s.name schema_name,
+ ui.name user_name
+ FROM sys.db_user_info ui, sys.schemas s
+ WHERE ui.default_schema = s.id
+ AND ui.name <> 'monetdb'
+ AND ui.name <> '.snapshot'
+ AND s.name <> 'sys';
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
- WHERE a1.id = ur.login_id AND a2.id = ur.role_id;
+ SELECT
+ 'GRANT ' || sys.dq(a2.name) || ' ' || ifthenelse(a1.name = 'public',
'PUBLIC', sys.dq(a1.name)) || ';' stmt,
+ a2.name grantee,
+ a1.name grantor
+ FROM sys.auths a1, sys.auths a2, sys.user_role ur
+ WHERE a1.id = ur.login_id AND a2.id = ur.role_id;
CREATE VIEW sys.dump_table_constraint_type AS
- SELECT
- 'ALTER TABLE ' || sys.DQ(sch) || '.' || sys.DQ(tbl) ||
- ' ADD CONSTRAINT ' || sys.DQ(con) || ' '||
- tpe || ' (' || GROUP_CONCAT(sys.DQ(col), ', ') || ');' stmt
- FROM sys.describe_constraints GROUP BY sch, tbl, con, tpe;
+ SELECT
+ 'ALTER TABLE ' || sys.DQ(sch) || '.' || sys.DQ(tbl) ||
+ ' ADD CONSTRAINT ' || sys.DQ(con) || ' '||
+ tpe || ' (' || GROUP_CONCAT(sys.DQ(col), ', ') || ');' stmt,
+ sch schema_name,
+ tbl table_name,
+ con constraint_name
+ FROM sys.describe_constraints GROUP BY sch, tbl, con, tpe;
+
+CREATE VIEW sys.dump_table_grants AS
+ WITH table_grants (sname, tname, grantee, grants, grantor, grantable)
+ AS (SELECT s.name, t.name, a.name, sum(p.privileges), g.name, p.grantable
+ FROM sys.schemas s, sys.tables t, sys.auths a, sys.privileges p,
sys.auths g
+ WHERE p.obj_id = t.id AND p.auth_id = a.id AND t.schema_id = s.id AND
t.system = FALSE AND p.grantor = g.id GROUP BY s.name, t.name, a.name, g.name,
p.grantable
+ ORDER BY s.name, t.name, a.name, g.name, p.grantable)
+ SELECT
+ 'GRANT ' || pc.privilege_code_name || ' ON TABLE ' || sys.dq(sname)
+ || '.' || sys.dq(tname) || ' TO ' || ifthenelse(grantee = 'public',
'PUBLIC', sys.dq(grantee))
+ || CASE WHEN grantable = 1 THEN ' WITH GRANT OPTION' ELSE '' END || ';'
stmt,
+ sname schema_name,
+ tname table_name,
+ grantee
+ FROM table_grants LEFT OUTER JOIN sys.privilege_codes pc ON grants =
pc.privilege_code_id;
+
+CREATE VIEW sys.dump_column_grants AS
+ SELECT
+ 'GRANT ' || pc.privilege_code_name || '(' || sys.dq(c.name) || ') ON '
+ || sys.dq(s.name) || '.' || sys.dq(t.name) || ' TO ' ||
ifthenelse(a.name = 'public', 'PUBLIC', sys.dq(a.name))
+ || CASE WHEN p.grantable = 1 THEN ' WITH GRANT OPTION' ELSE '' END ||
';' stmt,
+ s.name schema_name,
+ t.name table_name,
+ c.name column_name,
+ a.name grantee
+ FROM sys.schemas s,
+ sys.tables t,
+ sys.columns c,
+ sys.auths a,
+ sys.privileges p,
+ sys.auths g,
+ sys.privilege_codes pc
+ WHERE p.obj_id = c.id
+ AND c.table_id = t.id
+ AND p.auth_id = a.id
+ AND t.schema_id = s.id
+ AND NOT t.system
+ AND p.grantor = g.id
+ AND p.privileges = pc.privilege_code_id
+ ORDER BY s.name, t.name, c.name, a.name, g.name, p.grantable;
+
+CREATE VIEW sys.dump_function_grants AS
+ WITH func_args_all(func_id, number, max_number, func_arg) AS
+ (SELECT a.func_id,
+ a.number,
+ max(a.number) OVER (PARTITION BY a.func_id ORDER BY a.number DESC),
+ group_concat(sys.describe_type(a.type, a.type_digits, a.type_scale),
', ') OVER (PARTITION BY a.func_id ORDER BY a.number)
+ FROM sys.args a
+ WHERE a.inout = 1),
+ func_args(func_id, func_arg) AS
+ (SELECT func_id, func_arg FROM func_args_all WHERE number = max_number)
+ SELECT
+ 'GRANT ' || pc.privilege_code_name || ' ON '
+ || ft.function_type_keyword || ' '
+ || sys.dq(s.name) || '.' || sys.dq(f.name) || '(' ||
coalesce(fa.func_arg, '') || ') TO '
+ || ifthenelse(a.name = 'public', 'PUBLIC', sys.dq(a.name))
+ || CASE WHEN p.grantable = 1 THEN ' WITH GRANT OPTION' ELSE '' END ||
';' stmt,
+ s.name schema_name,
+ f.name function_name,
+ a.name grantee
+ FROM sys.schemas s,
+ sys.functions f LEFT OUTER JOIN func_args fa ON f.id = fa.func_id,
+ sys.auths a,
+ sys.privileges p,
+ sys.auths g,
+ sys.function_types ft,
+ sys.privilege_codes pc
+ WHERE s.id = f.schema_id
+ AND f.id = p.obj_id
+ AND p.auth_id = a.id
+ AND p.grantor = g.id
+ AND p.privileges = pc.privilege_code_id
+ AND f.type = ft.function_type_id
+ AND NOT f.system
+ ORDER BY s.name, f.name, a.name, g.name, p.grantable;
CREATE VIEW sys.dump_indices AS
- SELECT
- 'CREATE ' || tpe || ' ' ||
- sys.DQ(ind) || ' ON ' || sys.DQ(sch) || '.' || sys.DQ(tbl) ||
- '(' || GROUP_CONCAT(col) || ');' stmt
- FROM sys.describe_indices GROUP BY ind, tpe, sch, tbl;
+ SELECT
+ 'CREATE ' || tpe || ' ' ||
+ sys.DQ(ind) || ' ON ' || sys.DQ(sch) || '.' || sys.DQ(tbl) ||
+ '(' || GROUP_CONCAT(col) || ');' stmt,
+ sch schema_name,
+ tbl table_name,
+ ind index_name
+ FROM sys.describe_indices GROUP BY ind, tpe, sch, tbl;
CREATE VIEW sys.dump_column_defaults AS
- SELECT 'ALTER TABLE ' || sys.FQN(sch, tbl) || ' ALTER COLUMN ' ||
sys.DQ(col) || ' SET DEFAULT ' || def || ';' stmt
- FROM sys.describe_column_defaults;
+ SELECT 'ALTER TABLE ' || sys.FQN(sch, tbl) || ' ALTER COLUMN ' ||
sys.DQ(col) || ' SET DEFAULT ' || def || ';' stmt,
+ sch schema_name,
+ tbl table_name,
+ col column_name
+ FROM sys.describe_column_defaults;
CREATE VIEW sys.dump_foreign_keys AS
- SELECT
- 'ALTER TABLE ' || sys.DQ(fk_s) || '.'|| sys.DQ(fk_t) || ' ADD
CONSTRAINT ' || sys.DQ(fk) || ' ' ||
- 'FOREIGN KEY(' || GROUP_CONCAT(sys.DQ(fk_c), ',') ||') ' ||
- 'REFERENCES ' || sys.DQ(pk_s) || '.' || sys.DQ(pk_t) || '(' ||
GROUP_CONCAT(sys.DQ(pk_c), ',') || ') ' ||
- 'ON DELETE ' || on_delete || ' ON UPDATE ' || on_update ||
- ';' stmt
- FROM sys.describe_foreign_keys GROUP BY fk_s, fk_t, pk_s, pk_t, fk,
on_delete, on_update;
+ SELECT
+ 'ALTER TABLE ' || sys.DQ(fk_s) || '.'|| sys.DQ(fk_t) || ' ADD CONSTRAINT '
|| sys.DQ(fk) || ' ' ||
+ 'FOREIGN KEY(' || GROUP_CONCAT(sys.DQ(fk_c), ',') ||') ' ||
+ 'REFERENCES ' || sys.DQ(pk_s) || '.' || sys.DQ(pk_t) || '(' ||
GROUP_CONCAT(sys.DQ(pk_c), ',') || ') ' ||
+ 'ON DELETE ' || on_delete || ' ON UPDATE ' || on_update ||
+ ';' stmt,
+ fk_s foreign_schema_name,
+ fk_t foreign_table_name,
+ pk_s primary_schema_name,
+ pk_t primary_table_name,
+ fk key_name
+ FROM sys.describe_foreign_keys GROUP BY fk_s, fk_t, pk_s, pk_t, fk,
on_delete, on_update;
CREATE VIEW sys.dump_partition_tables AS
- SELECT
- 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')
- WHEN tpe = 'FOR NULLS' THEN ' AS PARTITION FOR NULL
VALUES'
- ELSE '' --'READ ONLY'
- END ||
- CASE WHEN tpe in ('VALUES', 'RANGE') AND with_nulls THEN ' WITH
NULL VALUES' ELSE '' END ||
- ';' stmt
- FROM sys.describe_partition_tables;
+ SELECT
+ 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')
+ WHEN tpe = 'FOR NULLS' THEN ' AS PARTITION FOR NULL VALUES'
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
