Changeset: 8bbc5fc77364 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/8bbc5fc77364
Modified Files:
clients/mapiclient/dump.c
sql/backends/monet5/sql_upgrades.c
sql/scripts/52_describe.sql
sql/scripts/76_dump.sql
sql/test/Dependencies/Tests/dependency_owner_schema_3.test
sql/test/Tests/comment-dump.SQL.py
sql/test/emptydb/Tests/check.stable.out
sql/test/emptydb/Tests/check.stable.out.int128
sql/test/miscellaneous/Tests/sequences.test
sql/test/sql_dump/Tests/dump.test
sql/test/testdb-previous-upgrade-chain-hge/Tests/dump.stable.out
sql/test/testdb-previous-upgrade-chain/Tests/dump.stable.out
sql/test/testdb-previous-upgrade-hge/Tests/dump.stable.out
sql/test/testdb-previous-upgrade/Tests/dump.stable.out
sql/test/testdb-upgrade-chain/Tests/dump.stable.out
sql/test/testdb-upgrade/Tests/dump.stable.out
sql/test/testdb/Tests/dump-nogeom.stable.out
sql/test/testdb/Tests/dump.stable.out
Branch: Jan2022
Log Message:
Fixes to dump sequence default values.
diffs (truncated from 549 to 300 lines):
diff --git a/clients/mapiclient/dump.c b/clients/mapiclient/dump.c
--- a/clients/mapiclient/dump.c
+++ b/clients/mapiclient/dump.c
@@ -2389,17 +2389,16 @@ dump_database(Mapi mid, stream *toConsol
"WHERE sch.id = seq.schema_id "
"ORDER BY sch.name, seq.name";
const char *sequences2 =
- "SELECT s.name, "
- "seq.name, "
- "peak_next_value_for(s.name, seq.name), "
- "seq.\"minvalue\", "
- "seq.\"maxvalue\", "
- "seq.\"increment\", "
- "seq.\"cycle\" "
- "FROM sys.sequences seq, "
- "sys.schemas s "
- "WHERE s.id = seq.schema_id "
- "ORDER BY s.name, seq.name";
+ "SELECT "
+ "sch, "
+ "seq, "
+ "rs, "
+ "rmi, "
+ "rma, "
+ "inc, "
+ "cycle "
+ "FROM sys.describe_sequences "
+ "ORDER BY sch, seq";
/* we must dump tables, views, functions/procedures and triggers in
order of creation since they can refer to each other */
const char *tables_views_functions_triggers =
"with vft (sname, name, id, query, remark, type) AS ("
@@ -2874,6 +2873,8 @@ dump_database(Mapi mid, stream *toConsol
if (sname != NULL && strcmp(schema, sname) != 0)
continue;
+ // sleep(7);
+
mnstr_printf(toConsole,
"ALTER SEQUENCE ");
dquoted_print(toConsole, schema, ".");
@@ -2881,9 +2882,9 @@ dump_database(Mapi mid, stream *toConsol
mnstr_printf(toConsole, " RESTART WITH %s", restart);
if (strcmp(increment, "1") != 0)
mnstr_printf(toConsole, " INCREMENT BY %s",
increment);
- if (strcmp(minvalue, "0") != 0)
+ if (minvalue)
mnstr_printf(toConsole, " MINVALUE %s",
minvalue);
- if (strcmp(maxvalue, "0") != 0)
+ if (maxvalue)
mnstr_printf(toConsole, " MAXVALUE %s",
maxvalue);
mnstr_printf(toConsole, " %sCYCLE;\n", strcmp(cycle,
"true") == 0 ? "" : "NO ");
if (mnstr_errnr(toConsole)) {
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
@@ -2774,7 +2774,27 @@ sql_update_jul2021(Client c, mvc *sql, c
" s.name as sch,\n"
" seq.name as seq,\n"
" seq.\"start\" s,\n"
- "
sys.peak_next_value_for(s.name, seq.name) AS rs,\n"
+ " sys.get_value_for(s.name,
seq.name) AS rs,\n"
+ " CASE WHEN seq.\"minvalue\" =
-9223372036854775807 AND seq.\"increment\" > 0 AND seq.\"start\" = 1 THEN TRUE
ELSE FALSE END nomin,\n"
+ " CASE WHEN seq.\"maxvalue\" =
9223372036854775807 AND seq.\"increment\" < 0 AND seq.\"start\" = -1 THEN TRUE
ELSE FALSE END nomax,\n"
+ " CASE\n"
+ " WHEN seq.\"minvalue\"
= 0 AND seq.\"increment\" > 0 THEN NULL\n"
+ " WHEN seq.\"minvalue\" <>
-9223372036854775807 THEN seq.\"minvalue\"\n"
+ " ELSE\n"
+ " CASE\n"
+ " WHEN
seq.\"increment\" < 0 THEN NULL\n"
+ " ELSE CASE WHEN
seq.\"start\" = 1 THEN NULL ELSE seq.\"minvalue\" END\n"
+ " END\n"
+ " END rmi,\n"
+ " CASE \n"
+ " WHEN seq.\"maxvalue\" = 0
AND seq.\"increment\" < 0 THEN NULL\n"
+ " WHEN seq.\"maxvalue\" <>
9223372036854775807 THEN seq.\"maxvalue\"\n"
+ " ELSE\n"
+ " CASE\n"
+ " WHEN
seq.\"increment\" > 0 THEN NULL\n"
+ " ELSE CASE WHEN
seq.\"start\" = -1 THEN NULL ELSE seq.\"maxvalue\" END\n"
+ " END\n"
+ " END rma,\n"
" seq.\"minvalue\" mi,\n"
" seq.\"maxvalue\" ma,\n"
" seq.\"increment\" inc,\n"
@@ -2934,8 +2954,16 @@ sql_update_jul2021(Client c, mvc *sql, c
" 'CREATE SEQUENCE ' ||
sys.FQN(sch, seq) || ' AS BIGINT ' ||\n"
" CASE WHEN \"s\" <> 0 THEN
'START WITH ' || \"rs\" ELSE '' END ||\n"
" CASE WHEN \"inc\" <> 1 THEN '
INCREMENT BY ' || \"inc\" ELSE '' END ||\n"
- " CASE WHEN \"mi\" <> 0 THEN '
MINVALUE ' || \"mi\" ELSE '' END ||\n"
- " CASE WHEN \"ma\" <> 0 THEN '
MAXVALUE ' || \"ma\" ELSE '' END ||\n"
+ " CASE\n"
+ " WHEN nomin THEN ' NO
MINVALUE'\n"
+ " WHEN rmi IS NOT NULL THEN
' MINVALUE ' || rmi\n"
+ " ELSE ''\n"
+ " END ||\n"
+ " CASE\n"
+ " WHEN nomax THEN ' NO
MAXVALUE'\n"
+ " WHEN rma IS NOT NULL THEN
' MAXVALUE ' || rma\n"
+ " ELSE ''\n"
+ " END ||\n"
" CASE WHEN \"cache\" <> 1 THEN
' CACHE ' || \"cache\" ELSE '' END ||\n"
" CASE WHEN \"cycle\" THEN '
CYCLE' ELSE '' END || ';' stmt\n"
" FROM sys.describe_sequences;\n"
@@ -3745,7 +3773,20 @@ sql_update_jan2022(Client c, mvc *sql, c
" JOIN sys.schemas s ON
f.schema_id = s.id\n"
" JOIN sys.function_types
ft ON f.type = ft.function_type_id\n"
" LEFT OUTER JOIN
sys.function_languages fl ON f.language = fl.language_id\n"
- " WHERE s.name <> 'tmp' AND NOT
f.system;\n");
+ " WHERE s.name <> 'tmp' AND NOT
f.system;\n"
+ "GRANT SELECT ON
sys.describe_constraints TO PUBLIC;\n"
+ "GRANT SELECT ON sys.describe_indices
TO PUBLIC;\n"
+ "GRANT SELECT ON
sys.describe_column_defaults TO PUBLIC;\n"
+ "GRANT SELECT ON
sys.describe_foreign_keys TO PUBLIC;\n"
+ "GRANT SELECT ON sys.describe_tables TO
PUBLIC;\n"
+ "GRANT SELECT ON sys.describe_triggers
TO PUBLIC;\n"
+ "GRANT SELECT ON sys.describe_comments
TO PUBLIC;\n"
+ "GRANT SELECT ON
sys.fully_qualified_functions TO PUBLIC;\n"
+ "GRANT SELECT ON
sys.describe_privileges TO PUBLIC;\n"
+ "GRANT SELECT ON
sys.describe_user_defined_types TO PUBLIC;\n"
+ "GRANT SELECT ON
sys.describe_partition_tables TO PUBLIC;\n"
+ "GRANT SELECT ON sys.describe_sequences
TO PUBLIC;\n"
+ "GRANT SELECT ON sys.describe_functions
TO PUBLIC;\n");
pos += snprintf(buf + pos, bufsize - pos,
"update sys.functions set system = true
where system <> true and name in ('sq', 'fqn',
'get_merge_table_partition_expressions', 'get_remote_table_expressions',
'schema_guard') and schema_id = 2000 and type = %d;\n", F_FUNC);
pos += snprintf(buf + pos, bufsize - pos,
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
@@ -488,10 +488,30 @@ CREATE VIEW sys.describe_partition_table
CREATE VIEW sys.describe_sequences AS
SELECT
- s.name as sch,
- seq.name as seq,
+ s.name sch,
+ seq.name seq,
seq."start" s,
- peak_next_value_for(s.name, seq.name) AS rs,
+ peak_next_value_for(s.name, seq.name) rs,
+ CASE WHEN seq."minvalue" = -9223372036854775807 AND
seq."increment" > 0 AND seq."start" = 1 THEN TRUE ELSE FALSE END nomin,
+ CASE WHEN seq."maxvalue" = 9223372036854775807 AND
seq."increment" < 0 AND seq."start" = -1 THEN TRUE ELSE FALSE END nomax,
+ CASE
+ WHEN seq."minvalue" = 0 AND seq."increment" > 0 THEN
NULL
+ WHEN seq."minvalue" <> -9223372036854775807 THEN
seq."minvalue"
+ ELSE
+ CASE
+ WHEN seq."increment" < 0 THEN NULL
+ ELSE CASE WHEN seq."start" = 1 THEN
NULL ELSE seq."maxvalue" END
+ END
+ END rmi,
+ CASE
+ WHEN seq."maxvalue" = 0 AND seq."increment" < 0 THEN
NULL
+ WHEN seq."maxvalue" <> 9223372036854775807 THEN
seq."maxvalue"
+ ELSE
+ CASE
+ WHEN seq."increment" > 0 THEN NULL
+ ELSE CASE WHEN seq."start" = -1 THEN
NULL ELSE seq."maxvalue" END
+ END
+ END rma,
seq."minvalue" mi,
seq."maxvalue" ma,
seq."increment" inc,
@@ -576,3 +596,17 @@ BEGIN
LEFT OUTER JOIN sys.comments c ON f.id = c.id
WHERE f.name=functionName AND s.name = schemaName;
END;
+
+GRANT SELECT ON sys.describe_constraints TO PUBLIC;
+GRANT SELECT ON sys.describe_indices TO PUBLIC;
+GRANT SELECT ON sys.describe_column_defaults TO PUBLIC;
+GRANT SELECT ON sys.describe_foreign_keys TO PUBLIC;
+GRANT SELECT ON sys.describe_tables TO PUBLIC;
+GRANT SELECT ON sys.describe_triggers TO PUBLIC;
+GRANT SELECT ON sys.describe_comments TO PUBLIC;
+GRANT SELECT ON sys.fully_qualified_functions TO PUBLIC;
+GRANT SELECT ON sys.describe_privileges TO PUBLIC;
+GRANT SELECT ON sys.describe_user_defined_types TO PUBLIC;
+GRANT SELECT ON sys.describe_partition_tables TO PUBLIC;
+GRANT SELECT ON sys.describe_sequences TO PUBLIC;
+GRANT SELECT ON sys.describe_functions TO PUBLIC;
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
@@ -182,12 +182,21 @@ CREATE VIEW sys.dump_partition_tables AS
CREATE VIEW sys.dump_sequences AS
SELECT
'CREATE SEQUENCE ' || sys.FQN(sch, seq) || ' AS BIGINT ' ||
- CASE WHEN "s" <> 0 THEN 'START WITH ' || "rs" ELSE '' END ||
- CASE WHEN "inc" <> 1 THEN ' INCREMENT BY ' || "inc" ELSE '' END ||
- CASE WHEN "mi" <> 0 THEN ' MINVALUE ' || "mi" ELSE '' END ||
- CASE WHEN "ma" <> 0 THEN ' MAXVALUE ' || "ma" ELSE '' END ||
- CASE WHEN "cache" <> 1 THEN ' CACHE ' || "cache" ELSE '' END ||
- CASE WHEN "cycle" THEN ' CYCLE' ELSE '' END || ';' stmt,
+ CASE WHEN "s" <> 0 THEN 'START WITH ' || "rs" ELSE '' END ||
+ CASE WHEN "inc" <> 1 THEN ' INCREMENT BY ' || "inc" ELSE '' END ||
+ CASE
+ WHEN nomin THEN ' NO MINVALUE'
+ WHEN rmi IS NOT NULL THEN ' MINVALUE ' || rmi
+ ELSE ''
+ END ||
+ CASE
+ WHEN nomax THEN ' NO MAXVALUE'
+ WHEN rma IS NOT NULL THEN ' MAXVALUE ' || rma
+ ELSE ''
+ END ||
+ CASE WHEN "cache" <> 1 THEN ' CACHE ' || "cache" ELSE '' END ||
+ CASE WHEN "cycle" THEN ' CYCLE' ELSE '' END ||
+ ';' stmt,
sch schema_name,
seq seqname
FROM sys.describe_sequences;
diff --git a/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
b/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
--- a/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
+++ b/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
@@ -126,7 +126,7 @@ DEP_FUNC
query TTT rowsort
SELECT c.name, v.name, 'DEP_VIEW' from sys.columns as c, sys.tables as v,
sys.dependencies as dep where c.id = dep.id AND v.id = dep.depend_id AND
dep.depend_type = 5 AND v.type in (1, 11, 21, 31) order by c.name, v.name
----
-2253 values hashing to 2bd737c6d7fa6090b81d699ef4684862
+2259 values hashing to 601d3047954820aa935c1cb583bed2f1
query TTT rowsort
SELECT c.name, k.name, 'DEP_KEY' from sys.columns as c, sys.objects as kc,
sys.keys as k where kc."name" = c.name AND kc.id = k.id AND k.table_id =
c.table_id AND k.rkey = -1 order by c.name, k.name
diff --git a/sql/test/Tests/comment-dump.SQL.py
b/sql/test/Tests/comment-dump.SQL.py
--- a/sql/test/Tests/comment-dump.SQL.py
+++ b/sql/test/Tests/comment-dump.SQL.py
@@ -42,7 +42,7 @@ create function f(i int, j int, k int) r
create function f(i int, j int, k int, l int) returns int begin return 45; end;
create procedure g() begin delete from tab where false; end;
COMMENT ON PROCEDURE "foo"."g"() IS 'proc';
-ALTER SEQUENCE "foo"."counter" RESTART WITH 1 MAXVALUE 9223372036854775807 NO
CYCLE;
+ALTER SEQUENCE "foo"."counter" RESTART WITH 1 NO CYCLE;
SET SCHEMA "sys";
COMMIT;
'''
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
@@ -1044,7 +1044,7 @@ create view sys.describe_functions as wi
create view sys.describe_indices as with it (id, idx) as (values (0, 'INDEX'),
(4, 'IMPRINTS INDEX'), (5, 'ORDERED INDEX')) select i.name ind, s.name sch,
t.name tbl, c.name col, it.idx tpe from sys.idxs as i left join sys.keys as k
on i.name = k.name, sys.objects as kc, sys._columns as c, sys.schemas s,
sys._tables as t, it where i.table_id = t.id and i.id = kc.id and kc.name =
c.name and t.id = c.table_id and t.schema_id = s.id and k.type is null and
i.type = it.id order by i.name, kc.nr;
create view sys.describe_partition_tables as select m_sch, m_tbl, p_sch,
p_tbl, case when p_raw_type is null then 'READ ONLY' when (p_raw_type =
'VALUES' and pvalues is null) or (p_raw_type = 'RANGE' and minimum is null and
maximum is null and with_nulls) then 'FOR NULLS' else p_raw_type end as tpe,
pvalues, minimum, maximum, with_nulls from (with tp("type", table_id) as
(select ifthenelse((table_partitions."type" & 2) = 2, 'VALUES', 'RANGE'),
table_partitions.table_id from sys.table_partitions), subq(m_tid, p_mid,
"type", m_sch, m_tbl, p_sch, p_tbl) as (select m_t.id, p_m.id, m_t."type",
m_s.name, m_t.name, p_s.name, p_m.name from sys.schemas m_s, sys._tables m_t,
sys.dependencies d, sys.schemas p_s, sys._tables p_m where m_t."type" in (3, 6)
and m_t.schema_id = m_s.id and m_s.name <> 'tmp' and m_t.system = false and
m_t.id = d.depend_id and d.id = p_m.id and p_m.schema_id = p_s.id order by
m_t.id, p_m.id) select subq.m_sch, subq.m_tbl, subq.p_sch, subq.p_tbl,
tp."type" as p_raw_ty
pe, case when tp."type" = 'VALUES' then (select group_concat(vp.value, ',')
from sys.value_partitions vp where vp.table_id = subq.p_mid) else null end as
pvalues, case when tp."type" = 'RANGE' then (select minimum from
sys.range_partitions rp where rp.table_id = subq.p_mid) else null end as
minimum, case when tp."type" = 'RANGE' then (select maximum from
sys.range_partitions rp where rp.table_id = subq.p_mid) else null end as
maximum, case when tp."type" = 'VALUES' then exists(select vp.value from
sys.value_partitions vp where vp.table_id = subq.p_mid and vp.value is null)
else (select rp.with_nulls from sys.range_partitions rp where rp.table_id =
subq.p_mid) end as with_nulls from subq left outer join tp on subq.m_tid =
tp.table_id) as tmp_pi;
create view sys.describe_privileges as select case when o.tpe is null and
pc.privilege_code_name = 'SELECT' then 'COPY FROM' when o.tpe is null and
pc.privilege_code_name = 'UPDATE' then 'COPY INTO' else o.nme end o_nme,
coalesce(o.tpe, 'GLOBAL') o_tpe, pc.privilege_code_name p_nme, a.name a_nme,
g.name g_nme, p.grantable grantable from sys.privileges p left join (select
t.id, s.name || '.' || t.name , 'TABLE' from sys.schemas s, sys.tables t where
s.id = t.schema_id union all select c.id, s.name || '.' || t.name || '.' ||
c.name, 'COLUMN' from sys.schemas s, sys.tables t, sys.columns c where s.id =
t.schema_id and t.id = c.table_id union all select f.id, f.nme, f.tpe from
sys.fully_qualified_functions f) o(id, nme, tpe) on o.id = p.obj_id,
sys.privilege_codes pc, auths a, auths g where p.privileges =
pc.privilege_code_id and p.auth_id = a.id and p.grantor = g.id;
-create view sys.describe_sequences as select s.name as sch, seq.name as seq,
seq."start" s, peak_next_value_for(s.name, seq.name) as rs, seq."minvalue" mi,
seq."maxvalue" ma, seq."increment" inc, seq."cacheinc" cache, seq."cycle" cycle
from sys.sequences seq, sys.schemas s where s.id = seq.schema_id and s.name <>
'tmp' order by s.name, seq.name;
+create view sys.describe_sequences as select s.name sch, seq.name seq,
seq."start" s, peak_next_value_for(s.name, seq.name) rs, case when
seq."minvalue" = -9223372036854775807 and seq."increment" > 0 and seq."start" =
1 then true else false end nomin, case when seq."maxvalue" =
9223372036854775807 and seq."increment" < 0 and seq."start" = -1 then true else
false end nomax, case when seq."minvalue" = 0 and seq."increment" > 0 then null
when seq."minvalue" <> -9223372036854775807 then seq."minvalue" else case when
seq."increment" < 0 then null else case when seq."start" = 1 then null else
seq."maxvalue" end end end rmi, case when seq."maxvalue" = 0 and
seq."increment" < 0 then null when seq."maxvalue" <> 9223372036854775807 then
seq."maxvalue" else case when seq."increment" > 0 then null else case when
seq."start" = -1 then null else seq."maxvalue" end end end rma, seq."minvalue"
mi, seq."maxvalue" ma, seq."increment" inc, seq."cacheinc" cache, seq."cycle"
cycle from sys.sequences seq
, sys.schemas s where s.id = seq.schema_id and s.name <> 'tmp' order by
s.name, seq.name;
create view sys.describe_tables as select t.id o, s.name sch, t.name tab,
ts.table_type_name typ, (select ' (' || group_concat(sys.dq(c.name) || ' ' ||
sys.describe_type(c.type, c.type_digits, c.type_scale) || ifthenelse(c."null" =
'false', ' NOT NULL', '') , ', ') || ')' from sys._columns c where c.table_id =
t.id) col, case ts.table_type_name when 'REMOTE TABLE' then
sys.get_remote_table_expressions(s.name, t.name) when 'MERGE TABLE' then
sys.get_merge_table_partition_expressions(t.id) when 'VIEW' then
sys.schema_guard(s.name, t.name, t.query) else '' end opt from sys.schemas s,
sys.table_types ts, sys.tables t where ts.table_type_name in ('TABLE', 'VIEW',
'MERGE TABLE', 'REMOTE TABLE', 'REPLICA TABLE') and t.system = false and s.id =
t.schema_id and ts.table_type_id = t.type and s.name <> 'tmp';
create view sys.describe_triggers as select s.name sch, t.name tab, tr.name
tri, tr.statement def from sys.schemas s, sys.tables t, sys.triggers tr where
s.id = t.schema_id and t.id = tr.table_id and not t.system;
create view sys.describe_user_defined_types as select s.name sch, t.sqlname
sql_tpe, t.systemname ext_tpe from sys.types t join sys.schemas s on
t.schema_id = s.id where t.eclass = 18 and ((s.name = 'sys' and t.sqlname not
in ('geometrya', 'mbr', 'url', 'inet', 'json', 'uuid', 'xml')) or (s.name <>
'sys'));
@@ -1061,7 +1061,7 @@ create view sys.dump_functions as select
create view sys.dump_grant_user_privileges as 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_indices as select 'CREATE ' || tpe || ' ' || sys.dq(ind)
|| ' ON ' || sys.fqn(sch, 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_partition_tables as select 'ALTER TABLE ' ||
sys.fqn(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 '' end || case when tpe in ('VALUES', 'RANGE') and with_nulls then ' WITH
NULL VALUES' else '' end || ';' stmt, m_sch merge_schema_name, m_tbl
merge_table_name, p_sch partition_schema_name, p_tbl partition_table_name from
sys.describe_partition_tables;
-create view sys.dump_sequences as select 'CREATE SEQUENCE ' || sys.fqn(sch,
seq) || ' AS BIGINT ' || case when "s" <> 0 then 'START WITH ' || "rs" else ''
end || case when "inc" <> 1 then ' INCREMENT BY ' || "inc" else '' end || case
when "mi" <> 0 then ' MINVALUE ' || "mi" else '' end || case when "ma" <> 0
then ' MAXVALUE ' || "ma" else '' end || case when "cache" <> 1 then ' CACHE '
|| "cache" else '' end || case when "cycle" then ' CYCLE' else '' end || ';'
stmt, sch schema_name, seq seqname from sys.describe_sequences;
+create view sys.dump_sequences as select 'CREATE SEQUENCE ' || sys.fqn(sch,
seq) || ' AS BIGINT ' || case when "s" <> 0 then 'START WITH ' || "rs" else ''
end || case when "inc" <> 1 then ' INCREMENT BY ' || "inc" else '' end || case
when nomin then ' NO MINVALUE' when rmi is not null then ' MINVALUE ' || rmi
else '' end || case when nomax then ' NO MAXVALUE' when rma is not null then '
MAXVALUE ' || rma else '' end || case when "cache" <> 1 then ' CACHE ' ||
"cache" else '' end || case when "cycle" then ' CYCLE' else '' end || ';' stmt,
sch schema_name, seq seqname from sys.describe_sequences;
create view sys.dump_start_sequences as select 'UPDATE sys.sequences seq SET
start = ' || s || ' WHERE name = ' || sys.sq(seq) || ' AND schema_id = (SELECT
s.id FROM sys.schemas s WHERE s.name = ' || sys.sq(sch) || ');' stmt, sch
schema_name, seq sequence_name from sys.describe_sequences;
create view sys.dump_table_constraint_type as select 'ALTER TABLE ' ||
sys.fqn(sch, 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.fqn(sname, 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;
@@ -1990,7 +1990,7 @@ select 'null in value_partitions.value',
[ "sys._tables", "sys", "describe_indices", "create view
sys.describe_indices as with it (id, idx) as (values (0, 'INDEX'), (4,
'IMPRINTS INDEX'), (5, 'ORDERED INDEX')) select i.name ind, s.name sch, t.name
tbl, c.name col, it.idx tpe from sys.idxs as i left join sys.keys as k on
i.name = k.name, sys.objects as kc, sys._columns as c, sys.schemas s,
sys._tables as t, it where i.table_id = t.id and i.id = kc.id and kc.name =
c.name and t.id = c.table_id and t.schema_id = s.id and k.type is null and
i.type = it.id order by i.name, kc.nr;", "VIEW", true, "COMMIT",
"WRITABLE" ]
[ "sys._tables", "sys", "describe_partition_tables", "create view
sys.describe_partition_tables as select m_sch, m_tbl, p_sch, p_tbl, case when
p_raw_type is null then 'READ ONLY' when (p_raw_type = 'VALUES' and pvalues is
null) or (p_raw_type = 'RANGE' and minimum is null and maximum is null and
with_nulls) then 'FOR NULLS' else p_raw_type end as tpe, pvalues, minimum,
maximum, with_nulls from (with tp(\"type\", table_id) as (select
ifthenelse((table_partitions.\"type\" & 2) = 2, 'VALUES', 'RANGE'),
table_partitions.table_id from sys.table_partitions), subq(m_tid, p_mid,
\"type\", m_sch, m_tbl, p_sch, p_tbl) as (select m_t.id, p_m.id, m_t.\"type\",
m_s.name, m_t.name, p_s.name, p_m.name from sys.schemas m_s, sys._tables m_t,
sys.dependencies d, sys.schemas p_s, sys._tables p_m where m_t.\"type\" in (3,
6) and m_t.schema_id = m_s.id and m_s.name <> 'tmp' and m_t.system = false and
m_t.id = d.depend_id and d.id = p_m.id and p_m.schema_id = p_s.id order by
m_t.id, p_m.id) select subq.
m_sch, subq.m_tbl, subq.p_sch, subq.p_tbl, tp.\"type\" as p_raw_type, case
when tp.\"type\" = 'VALUES' then (select group_concat(vp.value, ',') from
sys.value_partitions vp where vp.table_id = subq.p_mid) else null end as
pvalues, case when tp.\"type\" = 'RANGE' then (select minimum from
sys.range_partitions rp where rp.table_id = subq.p_mid) else null end as
minimum, case when tp.\"type\" = 'RANGE' then (select maximum from
sys.range_partitions rp where rp.table_id = subq.p_mid) else null end as
maximum, case when tp.\"type\" = 'VALUES' then exists(select vp.value from
sys.value_partitions vp where vp.table_id = subq.p_mid and vp.value is null)
else (select rp.with_nulls from sys.range_partitions rp where rp.table_id =
subq.p_mid) end as with_nulls from subq left outer join tp on subq.m_tid =
tp.table_id) as tmp_pi;", "VIEW", true, "COMMIT", "WRITABLE" ]
[ "sys._tables", "sys", "describe_privileges", "create view
sys.describe_privileges as select case when o.tpe is null and
pc.privilege_code_name = 'SELECT' then 'COPY FROM' when o.tpe is null and
pc.privilege_code_name = 'UPDATE' then 'COPY INTO' else o.nme end o_nme,
coalesce(o.tpe, 'GLOBAL') o_tpe, pc.privilege_code_name p_nme, a.name a_nme,
g.name g_nme, p.grantable grantable from sys.privileges p left join (select
t.id, s.name || '.' || t.name , 'TABLE' from sys.schemas s, sys.tables t where
s.id = t.schema_id union all select c.id, s.name || '.' || t.name || '.' ||
c.name, 'COLUMN' from sys.schemas s, sys.tables t, sys.columns c where s.id =
t.schema_id and t.id = c.table_id union all select f.id, f.nme, f.tpe from
sys.fully_qualified_functions f) o(id, nme, tpe) on o.id = p.obj_id,
sys.privilege_codes pc, auths a, auths g where p.privileges =
pc.privilege_code_id and p.auth_id = a.id and p.grantor = g.id;", "VIEW", true,
"COMMIT", "WRITABLE" ]
-[ "sys._tables", "sys", "describe_sequences", "create view
sys.describe_sequences as select s.name as sch, seq.name as seq, seq.\"start\"
s, peak_next_value_for(s.name, seq.name) as rs, seq.\"minvalue\" mi,
seq.\"maxvalue\" ma, seq.\"increment\" inc, seq.\"cacheinc\" cache,
seq.\"cycle\" cycle from sys.sequences seq, sys.schemas s where s.id =
seq.schema_id and s.name <> 'tmp' order by s.name, seq.name;", "VIEW",
true, "COMMIT", "WRITABLE" ]
+[ "sys._tables", "sys", "describe_sequences", "create view
sys.describe_sequences as select s.name sch, seq.name seq, seq.\"start\" s,
peak_next_value_for(s.name, seq.name) rs, case when seq.\"minvalue\" =
-9223372036854775807 and seq.\"increment\" > 0 and seq.\"start\" = 1 then true
else false end nomin, case when seq.\"maxvalue\" = 9223372036854775807 and
seq.\"increment\" < 0 and seq.\"start\" = -1 then true else false end nomax,
case when seq.\"minvalue\" = 0 and seq.\"increment\" > 0 then null when
seq.\"minvalue\" <> -9223372036854775807 then seq.\"minvalue\" else case when
seq.\"increment\" < 0 then null else case when seq.\"start\" = 1 then null else
seq.\"maxvalue\" end end end rmi, case when seq.\"maxvalue\" = 0 and
seq.\"increment\" < 0 then null when seq.\"maxvalue\" <> 9223372036854775807
then seq.\"maxvalue\" else case when seq.\"increment\" > 0 then null else case
when seq.\"start\" = -1 then null else seq.\"maxvalue\" end end end rma,
seq.\"minvalue\" mi, seq.\"maxva
lue\" ma, seq.\"increment\" inc, seq.\"cacheinc\" cache, seq.\"cycle\" cycle
from sys.sequences seq, sys.schemas s where s.id = seq.schema_id and s.name <>
'tmp' order by s.name, seq.name;", "VIEW", true, "COMMIT", "WRITABLE"
]
[ "sys._tables", "sys", "describe_tables", "create view
sys.describe_tables as select t.id o, s.name sch, t.name tab,
ts.table_type_name typ, (select ' (' || group_concat(sys.dq(c.name) || ' ' ||
sys.describe_type(c.type, c.type_digits, c.type_scale) || ifthenelse(c.\"null\"
= 'false', ' NOT NULL', '') , ', ') || ')' from sys._columns c where c.table_id
= t.id) col, case ts.table_type_name when 'REMOTE TABLE' then
sys.get_remote_table_expressions(s.name, t.name) when 'MERGE TABLE' then
sys.get_merge_table_partition_expressions(t.id) when 'VIEW' then
sys.schema_guard(s.name, t.name, t.query) else '' end opt from sys.schemas s,
sys.table_types ts, sys.tables t where ts.table_type_name in ('TABLE', 'VIEW',
'MERGE TABLE', 'REMOTE TABLE', 'REPLICA TABLE') and t.system = false and s.id =
t.schema_id and ts.table_type_id = t.type and s.name <> 'tmp';", "VIEW", true,
"COMMIT", "WRITABLE" ]
[ "sys._tables", "sys", "describe_triggers", "create view
sys.describe_triggers as select s.name sch, t.name tab, tr.name tri,
tr.statement def from sys.schemas s, sys.tables t, sys.triggers tr where s.id =
t.schema_id and t.id = tr.table_id and not t.system;", "VIEW", true,
"COMMIT", "WRITABLE" ]
[ "sys._tables", "sys", "describe_user_defined_types", "create view
sys.describe_user_defined_types as select s.name sch, t.sqlname sql_tpe,
t.systemname ext_tpe from sys.types t join sys.schemas s on t.schema_id = s.id
where t.eclass = 18 and ((s.name = 'sys' and t.sqlname not in ('geometrya',
'mbr', 'url', 'inet', 'json', 'uuid', 'xml')) or (s.name <> 'sys'));", "VIEW",
true, "COMMIT", "WRITABLE" ]
@@ -2007,7 +2007,7 @@ select 'null in value_partitions.value',
[ "sys._tables", "sys", "dump_grant_user_privileges", "create view
sys.dump_grant_user_privileges as 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;", "VIEW", true,
"COMMIT", "WRITABLE" ]
[ "sys._tables", "sys", "dump_indices", "create view sys.dump_indices
as select 'CREATE ' || tpe || ' ' || sys.dq(ind) || ' ON ' || sys.fqn(sch, tbl)
|| '(' || group_concat(col) || ');' stmt, sch schema_name, tbl table_name, ind
index_name from sys.describe_indices group by ind, tpe, sch, tbl;",
"VIEW", true, "COMMIT", "WRITABLE" ]
[ "sys._tables", "sys", "dump_partition_tables", "create view
sys.dump_partition_tables as select 'ALTER TABLE ' || sys.fqn(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 '' end || case when tpe
in ('VALUES', 'RANGE') and with_nulls then ' WITH NULL VALUES' else '' end ||
';' stmt, m_sch merge_schema_name, m_tbl merge_table_name, p_sch
partition_schema_name, p_tbl partition_table_name from
sys.describe_partition_tables;", "VIEW", true, "COMMIT", "WRITABLE"
]
-[ "sys._tables", "sys", "dump_sequences", "create view
sys.dump_sequences as select 'CREATE SEQUENCE ' || sys.fqn(sch, seq) || ' AS
BIGINT ' || case when \"s\" <> 0 then 'START WITH ' || \"rs\" else '' end ||
case when \"inc\" <> 1 then ' INCREMENT BY ' || \"inc\" else '' end || case
when \"mi\" <> 0 then ' MINVALUE ' || \"mi\" else '' end || case when \"ma\" <>
0 then ' MAXVALUE ' || \"ma\" else '' end || case when \"cache\" <> 1 then '
CACHE ' || \"cache\" else '' end || case when \"cycle\" then ' CYCLE' else ''
end || ';' stmt, sch schema_name, seq seqname from sys.describe_sequences;",
"VIEW", true, "COMMIT", "WRITABLE" ]
+[ "sys._tables", "sys", "dump_sequences", "create view
sys.dump_sequences as select 'CREATE SEQUENCE ' || sys.fqn(sch, seq) || ' AS
BIGINT ' || case when \"s\" <> 0 then 'START WITH ' || \"rs\" else '' end ||
case when \"inc\" <> 1 then ' INCREMENT BY ' || \"inc\" else '' end || case
when nomin then ' NO MINVALUE' when rmi is not null then ' MINVALUE ' || rmi
else '' end || case when nomax then ' NO MAXVALUE' when rma is not null then '
MAXVALUE ' || rma else '' end || case when \"cache\" <> 1 then ' CACHE ' ||
\"cache\" else '' end || case when \"cycle\" then ' CYCLE' else '' end || ';'
stmt, sch schema_name, seq seqname from sys.describe_sequences;", "VIEW",
true, "COMMIT", "WRITABLE" ]
[ "sys._tables", "sys", "dump_start_sequences", "create view
sys.dump_start_sequences as select 'UPDATE sys.sequences seq SET start = ' || s
|| ' WHERE name = ' || sys.sq(seq) || ' AND schema_id = (SELECT s.id FROM
sys.schemas s WHERE s.name = ' || sys.sq(sch) || ');' stmt, sch schema_name,
seq sequence_name from sys.describe_sequences;", "VIEW", true, "COMMIT",
"WRITABLE" ]
[ "sys._tables", "sys", "dump_statements", NULL, "TABLE",
true, "COMMIT", "WRITABLE" ]
[ "sys._tables", "sys", "dump_table_constraint_type", "create view
sys.dump_table_constraint_type as select 'ALTER TABLE ' || sys.fqn(sch, 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;", "VIEW", true, "COMMIT", "WRITABLE" ]
@@ -2390,11 +2390,15 @@ select 'null in value_partitions.value',
[ "sys._columns", "describe_sequences", "seq", "varchar", 256,
0, NULL, true, 1, NULL ]
[ "sys._columns", "describe_sequences", "s", "bigint", 64,
0, NULL, true, 2, NULL ]
[ "sys._columns", "describe_sequences", "rs", "bigint", 64,
0, NULL, true, 3, NULL ]
-[ "sys._columns", "describe_sequences", "mi", "bigint", 64,
0, NULL, true, 4, NULL ]
-[ "sys._columns", "describe_sequences", "ma", "bigint", 64,
0, NULL, true, 5, NULL ]
-[ "sys._columns", "describe_sequences", "inc", "bigint", 64,
0, NULL, true, 6, NULL ]
-[ "sys._columns", "describe_sequences", "cache", "bigint",
64, 0, NULL, true, 7, NULL ]
-[ "sys._columns", "describe_sequences", "cycle", "boolean",
1, 0, NULL, true, 8, NULL ]
+[ "sys._columns", "describe_sequences", "nomin", "boolean",
1, 0, NULL, true, 4, NULL ]
+[ "sys._columns", "describe_sequences", "nomax", "boolean",
1, 0, NULL, true, 5, NULL ]
+[ "sys._columns", "describe_sequences", "rmi", "bigint", 64,
0, NULL, true, 6, NULL ]
+[ "sys._columns", "describe_sequences", "rma", "bigint", 64,
0, NULL, true, 7, NULL ]
+[ "sys._columns", "describe_sequences", "mi", "bigint", 64,
0, NULL, true, 8, NULL ]
+[ "sys._columns", "describe_sequences", "ma", "bigint", 64,
0, NULL, true, 9, NULL ]
+[ "sys._columns", "describe_sequences", "inc", "bigint", 64,
0, NULL, true, 10, NULL ]
+[ "sys._columns", "describe_sequences", "cache", "bigint",
64, 0, NULL, true, 11, NULL ]
+[ "sys._columns", "describe_sequences", "cycle", "boolean",
1, 0, NULL, true, 12, NULL ]
[ "sys._columns", "describe_tables", "o", "int", 32, 0,
NULL, true, 0, NULL ]
[ "sys._columns", "describe_tables", "sch", "varchar", 1024,
0, NULL, true, 1, NULL ]
[ "sys._columns", "describe_tables", "tab", "varchar", 1024,
0, NULL, true, 2, NULL ]
@@ -5339,8 +5343,10 @@ select 'null in value_partitions.value',
[ "column used by view", "sys", "describe_sequences", "cache",
"sys", "dump_sequences", "VIEW" ]
[ "column used by view", "sys", "describe_sequences", "cycle",
"sys", "dump_sequences", "VIEW" ]
[ "column used by view", "sys", "describe_sequences", "inc", "sys",
"dump_sequences", "VIEW" ]
-[ "column used by view", "sys", "describe_sequences", "ma", "sys",
"dump_sequences", "VIEW" ]
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
