Changeset: 1129290efb9c for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/1129290efb9c
Modified Files:
sql/scripts/52_describe.sql
sql/test/Dependencies/Tests/dependency_owner_schema_3.test
sql/test/emptydb/Tests/check.stable.out
sql/test/emptydb/Tests/check.stable.out.32bit
sql/test/emptydb/Tests/check.stable.out.int128
sql/test/sql_dump/Tests/dump.test
Branch: Jun2023
Log Message:
add order by on describe_constraints
diffs (103 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
@@ -171,7 +171,8 @@ CREATE VIEW sys.describe_constraints AS
AND k.table_id = t.id
AND s.id = t.schema_id
AND t.system = FALSE
- AND k.type in (0, 1);
+ AND k.type in (0, 1)
+ ORDER BY k.name, kc.nr;
CREATE VIEW sys.describe_indices AS
WITH it (id, idx) AS (VALUES (0, 'INDEX'), (4, 'IMPRINTS INDEX'), (5,
'ORDERED INDEX')) --UNIQUE INDEX wraps to INDEX.
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
@@ -129,7 +129,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
----
-2289 values hashing to e640ec7c86f357c8f9135af496fc3b2b
+2292 values hashing to 4ee99a9b4d0f0c8db6376eb0be833f01
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/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
@@ -482,7 +482,7 @@ select 'null in fkeys.delete_action', de
[ "sys._tables", "sys", "dependency_views_on_views", "create view
sys.dependency_views_on_views as select v1.schema_id as view1_schema_id, v1.id
as view1_id, v1.name as view1_name, v2.schema_id as view2_schema_id, v2.id as
view2_id, v2.name as view2_name, dep.depend_type as depend_type from sys.tables
as v1, sys.tables as v2, sys.dependencies as dep where v1.id = dep.id and v2.id
= dep.depend_id and dep.depend_type = 5 and v1.type in (1, 11) and v2.type in
(1, 11) order by v1.schema_id, v1.name, v2.schema_id, v2.name;", "VIEW",
true, "COMMIT", "WRITABLE", NULL ]
[ "sys._tables", "sys", "describe_column_defaults", "create view
sys.describe_column_defaults as select s.name sch, t.name tbl, c.name col,
c.\"default\" def from sys.schemas s, sys.tables t, sys.columns c where s.id =
t.schema_id and t.id = c.table_id and s.name <> 'tmp' and not t.system and
c.\"default\" is not null;", "VIEW", true, "COMMIT", "WRITABLE",
NULL ]
[ "sys._tables", "sys", "describe_comments", "create view
sys.describe_comments as select o.id id, o.tpe tpe, o.nme fqn, c.remark rem
from (select id, 'SCHEMA', sys.dq(name) from sys.schemas union all select t.id,
ifthenelse(ts.table_type_name = 'VIEW', 'VIEW', 'TABLE'), sys.fqn(s.name,
t.name) from sys.schemas s join sys.tables t on s.id = t.schema_id join
sys.table_types ts on t.type = ts.table_type_id where s.name <> 'tmp' union all
select c.id, 'COLUMN', sys.fqn(s.name, t.name) || '.' || sys.dq(c.name) from
sys.columns c, sys.tables t, sys.schemas s where c.table_id = t.id and
t.schema_id = s.id union all select idx.id, 'INDEX', sys.fqn(s.name, idx.name)
from sys.idxs idx, sys._tables t, sys.schemas s where idx.table_id = t.id and
t.schema_id = s.id union all select seq.id, 'SEQUENCE', sys.fqn(s.name,
seq.name) from sys.sequences seq, sys.schemas s where seq.schema_id = s.id
union all select f.id, ft.function_type_keyword, qf.nme from sys.functions f,
sys.function_types ft, sys.
schemas s, sys.fully_qualified_functions qf where f.type = ft.function_type_id
and f.schema_id = s.id and qf.id = f.id) as o(id, tpe, nme) join sys.comments c
on c.id = o.id;", "VIEW", true, "COMMIT", "WRITABLE", NULL
]
-[ "sys._tables", "sys", "describe_constraints", "create view
sys.describe_constraints as select s.name sch, t.name tbl, kc.name col, k.name
con, case k.type when 0 then 'PRIMARY KEY' when 1 then 'UNIQUE' end tpe from
sys.schemas s, sys._tables t, sys.objects kc, sys.keys k where kc.id = k.id and
k.table_id = t.id and s.id = t.schema_id and t.system = false and k.type in (0,
1);", "VIEW", true, "COMMIT", "WRITABLE", NULL ]
+[ "sys._tables", "sys", "describe_constraints", "create view
sys.describe_constraints as select s.name sch, t.name tbl, kc.name col, k.name
con, case k.type when 0 then 'PRIMARY KEY' when 1 then 'UNIQUE' end tpe from
sys.schemas s, sys._tables t, sys.objects kc, sys.keys k where kc.id = k.id and
k.table_id = t.id and s.id = t.schema_id and t.system = false and k.type in (0,
1) order by k.name, kc.nr;", "VIEW", true, "COMMIT", "WRITABLE",
NULL ]
[ "sys._tables", "sys", "describe_foreign_keys", "create view
sys.describe_foreign_keys as with action_type (id, act) as (values (0, 'NO
ACTION'), (1, 'CASCADE'), (2, 'RESTRICT'), (3, 'SET NULL'), (4, 'SET DEFAULT'))
select fs.name fk_s, fkt.name fk_t, fkkc.name fk_c, fkkc.nr o, fkk.name fk,
ps.name pk_s, pkt.name pk_t, pkkc.name pk_c, ou.act on_update, od.act on_delete
from sys._tables fkt, sys.objects fkkc, sys.keys fkk, sys._tables pkt,
sys.objects pkkc, sys.keys pkk, sys.schemas ps, sys.schemas fs, action_type ou,
action_type od where fkt.id = fkk.table_id and pkt.id = pkk.table_id and fkk.id
= fkkc.id and pkk.id = pkkc.id and fkk.rkey = pkk.id and fkkc.nr = pkkc.nr and
pkt.schema_id = ps.id and fkt.schema_id = fs.id and (fkk.\"action\" & 255) =
od.id and ((fkk.\"action\" >> 8) & 255) = ou.id order by fkk.name, fkkc.nr;",
"VIEW", true, "COMMIT", "WRITABLE", NULL ]
[ "sys._tables", "sys", "describe_functions", "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, 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;", "VIEW", true,
"COMMIT", "WRITABLE", NULL ]
[ "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", NULL ]
@@ -3455,6 +3455,7 @@ select 'null in fkeys.delete_action', de
[ "column used by view", "sys", "keys", "type", "sys",
"describe_constraints", "VIEW" ]
[ "column used by view", "sys", "objects", "id", "sys",
"describe_constraints", "VIEW" ]
[ "column used by view", "sys", "objects", "name", "sys",
"describe_constraints", "VIEW" ]
+[ "column used by view", "sys", "objects", "nr", "sys",
"describe_constraints", "VIEW" ]
[ "column used by view", "sys", "schemas", "id", "sys",
"describe_constraints", "VIEW" ]
[ "column used by view", "sys", "schemas", "name", "sys",
"describe_constraints", "VIEW" ]
[ "column used by view", "sys", "_tables", "id", "sys",
"describe_foreign_keys", "VIEW" ]
diff --git a/sql/test/emptydb/Tests/check.stable.out.32bit
b/sql/test/emptydb/Tests/check.stable.out.32bit
--- a/sql/test/emptydb/Tests/check.stable.out.32bit
+++ b/sql/test/emptydb/Tests/check.stable.out.32bit
@@ -482,7 +482,7 @@ select 'null in fkeys.delete_action', de
[ "sys._tables", "sys", "dependency_views_on_views", "create view
sys.dependency_views_on_views as select v1.schema_id as view1_schema_id, v1.id
as view1_id, v1.name as view1_name, v2.schema_id as view2_schema_id, v2.id as
view2_id, v2.name as view2_name, dep.depend_type as depend_type from sys.tables
as v1, sys.tables as v2, sys.dependencies as dep where v1.id = dep.id and v2.id
= dep.depend_id and dep.depend_type = 5 and v1.type in (1, 11) and v2.type in
(1, 11) order by v1.schema_id, v1.name, v2.schema_id, v2.name;", "VIEW",
true, "COMMIT", "WRITABLE", NULL ]
[ "sys._tables", "sys", "describe_column_defaults", "create view
sys.describe_column_defaults as select s.name sch, t.name tbl, c.name col,
c.\"default\" def from sys.schemas s, sys.tables t, sys.columns c where s.id =
t.schema_id and t.id = c.table_id and s.name <> 'tmp' and not t.system and
c.\"default\" is not null;", "VIEW", true, "COMMIT", "WRITABLE",
NULL ]
[ "sys._tables", "sys", "describe_comments", "create view
sys.describe_comments as select o.id id, o.tpe tpe, o.nme fqn, c.remark rem
from (select id, 'SCHEMA', sys.dq(name) from sys.schemas union all select t.id,
ifthenelse(ts.table_type_name = 'VIEW', 'VIEW', 'TABLE'), sys.fqn(s.name,
t.name) from sys.schemas s join sys.tables t on s.id = t.schema_id join
sys.table_types ts on t.type = ts.table_type_id where s.name <> 'tmp' union all
select c.id, 'COLUMN', sys.fqn(s.name, t.name) || '.' || sys.dq(c.name) from
sys.columns c, sys.tables t, sys.schemas s where c.table_id = t.id and
t.schema_id = s.id union all select idx.id, 'INDEX', sys.fqn(s.name, idx.name)
from sys.idxs idx, sys._tables t, sys.schemas s where idx.table_id = t.id and
t.schema_id = s.id union all select seq.id, 'SEQUENCE', sys.fqn(s.name,
seq.name) from sys.sequences seq, sys.schemas s where seq.schema_id = s.id
union all select f.id, ft.function_type_keyword, qf.nme from sys.functions f,
sys.function_types ft, sys.
schemas s, sys.fully_qualified_functions qf where f.type = ft.function_type_id
and f.schema_id = s.id and qf.id = f.id) as o(id, tpe, nme) join sys.comments c
on c.id = o.id;", "VIEW", true, "COMMIT", "WRITABLE", NULL
]
-[ "sys._tables", "sys", "describe_constraints", "create view
sys.describe_constraints as select s.name sch, t.name tbl, kc.name col, k.name
con, case k.type when 0 then 'PRIMARY KEY' when 1 then 'UNIQUE' end tpe from
sys.schemas s, sys._tables t, sys.objects kc, sys.keys k where kc.id = k.id and
k.table_id = t.id and s.id = t.schema_id and t.system = false and k.type in (0,
1);", "VIEW", true, "COMMIT", "WRITABLE", NULL ]
+[ "sys._tables", "sys", "describe_constraints", "create view
sys.describe_constraints as select s.name sch, t.name tbl, kc.name col, k.name
con, case k.type when 0 then 'PRIMARY KEY' when 1 then 'UNIQUE' end tpe from
sys.schemas s, sys._tables t, sys.objects kc, sys.keys k where kc.id = k.id and
k.table_id = t.id and s.id = t.schema_id and t.system = false and k.type in (0,
1) order by k.name, kc.nr;", "VIEW", true, "COMMIT", "WRITABLE",
NULL ]
[ "sys._tables", "sys", "describe_foreign_keys", "create view
sys.describe_foreign_keys as with action_type (id, act) as (values (0, 'NO
ACTION'), (1, 'CASCADE'), (2, 'RESTRICT'), (3, 'SET NULL'), (4, 'SET DEFAULT'))
select fs.name fk_s, fkt.name fk_t, fkkc.name fk_c, fkkc.nr o, fkk.name fk,
ps.name pk_s, pkt.name pk_t, pkkc.name pk_c, ou.act on_update, od.act on_delete
from sys._tables fkt, sys.objects fkkc, sys.keys fkk, sys._tables pkt,
sys.objects pkkc, sys.keys pkk, sys.schemas ps, sys.schemas fs, action_type ou,
action_type od where fkt.id = fkk.table_id and pkt.id = pkk.table_id and fkk.id
= fkkc.id and pkk.id = pkkc.id and fkk.rkey = pkk.id and fkkc.nr = pkkc.nr and
pkt.schema_id = ps.id and fkt.schema_id = fs.id and (fkk.\"action\" & 255) =
od.id and ((fkk.\"action\" >> 8) & 255) = ou.id order by fkk.name, fkkc.nr;",
"VIEW", true, "COMMIT", "WRITABLE", NULL ]
[ "sys._tables", "sys", "describe_functions", "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, 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;", "VIEW", true,
"COMMIT", "WRITABLE", NULL ]
[ "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", NULL ]
@@ -3455,6 +3455,7 @@ select 'null in fkeys.delete_action', de
[ "column used by view", "sys", "keys", "type", "sys",
"describe_constraints", "VIEW" ]
[ "column used by view", "sys", "objects", "id", "sys",
"describe_constraints", "VIEW" ]
[ "column used by view", "sys", "objects", "name", "sys",
"describe_constraints", "VIEW" ]
+[ "column used by view", "sys", "objects", "nr", "sys",
"describe_constraints", "VIEW" ]
[ "column used by view", "sys", "schemas", "id", "sys",
"describe_constraints", "VIEW" ]
[ "column used by view", "sys", "schemas", "name", "sys",
"describe_constraints", "VIEW" ]
[ "column used by view", "sys", "_tables", "id", "sys",
"describe_foreign_keys", "VIEW" ]
diff --git a/sql/test/emptydb/Tests/check.stable.out.int128
b/sql/test/emptydb/Tests/check.stable.out.int128
--- a/sql/test/emptydb/Tests/check.stable.out.int128
+++ b/sql/test/emptydb/Tests/check.stable.out.int128
@@ -482,7 +482,7 @@ select 'null in fkeys.delete_action', de
[ "sys._tables", "sys", "dependency_views_on_views", "create view
sys.dependency_views_on_views as select v1.schema_id as view1_schema_id, v1.id
as view1_id, v1.name as view1_name, v2.schema_id as view2_schema_id, v2.id as
view2_id, v2.name as view2_name, dep.depend_type as depend_type from sys.tables
as v1, sys.tables as v2, sys.dependencies as dep where v1.id = dep.id and v2.id
= dep.depend_id and dep.depend_type = 5 and v1.type in (1, 11) and v2.type in
(1, 11) order by v1.schema_id, v1.name, v2.schema_id, v2.name;", "VIEW",
true, "COMMIT", "WRITABLE", NULL ]
[ "sys._tables", "sys", "describe_column_defaults", "create view
sys.describe_column_defaults as select s.name sch, t.name tbl, c.name col,
c.\"default\" def from sys.schemas s, sys.tables t, sys.columns c where s.id =
t.schema_id and t.id = c.table_id and s.name <> 'tmp' and not t.system and
c.\"default\" is not null;", "VIEW", true, "COMMIT", "WRITABLE",
NULL ]
[ "sys._tables", "sys", "describe_comments", "create view
sys.describe_comments as select o.id id, o.tpe tpe, o.nme fqn, c.remark rem
from (select id, 'SCHEMA', sys.dq(name) from sys.schemas union all select t.id,
ifthenelse(ts.table_type_name = 'VIEW', 'VIEW', 'TABLE'), sys.fqn(s.name,
t.name) from sys.schemas s join sys.tables t on s.id = t.schema_id join
sys.table_types ts on t.type = ts.table_type_id where s.name <> 'tmp' union all
select c.id, 'COLUMN', sys.fqn(s.name, t.name) || '.' || sys.dq(c.name) from
sys.columns c, sys.tables t, sys.schemas s where c.table_id = t.id and
t.schema_id = s.id union all select idx.id, 'INDEX', sys.fqn(s.name, idx.name)
from sys.idxs idx, sys._tables t, sys.schemas s where idx.table_id = t.id and
t.schema_id = s.id union all select seq.id, 'SEQUENCE', sys.fqn(s.name,
seq.name) from sys.sequences seq, sys.schemas s where seq.schema_id = s.id
union all select f.id, ft.function_type_keyword, qf.nme from sys.functions f,
sys.function_types ft, sys.
schemas s, sys.fully_qualified_functions qf where f.type = ft.function_type_id
and f.schema_id = s.id and qf.id = f.id) as o(id, tpe, nme) join sys.comments c
on c.id = o.id;", "VIEW", true, "COMMIT", "WRITABLE", NULL
]
-[ "sys._tables", "sys", "describe_constraints", "create view
sys.describe_constraints as select s.name sch, t.name tbl, kc.name col, k.name
con, case k.type when 0 then 'PRIMARY KEY' when 1 then 'UNIQUE' end tpe from
sys.schemas s, sys._tables t, sys.objects kc, sys.keys k where kc.id = k.id and
k.table_id = t.id and s.id = t.schema_id and t.system = false and k.type in (0,
1);", "VIEW", true, "COMMIT", "WRITABLE", NULL ]
+[ "sys._tables", "sys", "describe_constraints", "create view
sys.describe_constraints as select s.name sch, t.name tbl, kc.name col, k.name
con, case k.type when 0 then 'PRIMARY KEY' when 1 then 'UNIQUE' end tpe from
sys.schemas s, sys._tables t, sys.objects kc, sys.keys k where kc.id = k.id and
k.table_id = t.id and s.id = t.schema_id and t.system = false and k.type in (0,
1) order by k.name, kc.nr;", "VIEW", true, "COMMIT", "WRITABLE",
NULL ]
[ "sys._tables", "sys", "describe_foreign_keys", "create view
sys.describe_foreign_keys as with action_type (id, act) as (values (0, 'NO
ACTION'), (1, 'CASCADE'), (2, 'RESTRICT'), (3, 'SET NULL'), (4, 'SET DEFAULT'))
select fs.name fk_s, fkt.name fk_t, fkkc.name fk_c, fkkc.nr o, fkk.name fk,
ps.name pk_s, pkt.name pk_t, pkkc.name pk_c, ou.act on_update, od.act on_delete
from sys._tables fkt, sys.objects fkkc, sys.keys fkk, sys._tables pkt,
sys.objects pkkc, sys.keys pkk, sys.schemas ps, sys.schemas fs, action_type ou,
action_type od where fkt.id = fkk.table_id and pkt.id = pkk.table_id and fkk.id
= fkkc.id and pkk.id = pkkc.id and fkk.rkey = pkk.id and fkkc.nr = pkkc.nr and
pkt.schema_id = ps.id and fkt.schema_id = fs.id and (fkk.\"action\" & 255) =
od.id and ((fkk.\"action\" >> 8) & 255) = ou.id order by fkk.name, fkkc.nr;",
"VIEW", true, "COMMIT", "WRITABLE", NULL ]
[ "sys._tables", "sys", "describe_functions", "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, 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;", "VIEW", true,
"COMMIT", "WRITABLE", NULL ]
[ "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", NULL ]
@@ -3566,6 +3566,7 @@ select 'null in fkeys.delete_action', de
[ "column used by view", "sys", "keys", "type", "sys",
"describe_constraints", "VIEW" ]
[ "column used by view", "sys", "objects", "id", "sys",
"describe_constraints", "VIEW" ]
[ "column used by view", "sys", "objects", "name", "sys",
"describe_constraints", "VIEW" ]
+[ "column used by view", "sys", "objects", "nr", "sys",
"describe_constraints", "VIEW" ]
[ "column used by view", "sys", "schemas", "id", "sys",
"describe_constraints", "VIEW" ]
[ "column used by view", "sys", "schemas", "name", "sys",
"describe_constraints", "VIEW" ]
[ "column used by view", "sys", "_tables", "id", "sys",
"describe_foreign_keys", "VIEW" ]
diff --git a/sql/test/sql_dump/Tests/dump.test
b/sql/test/sql_dump/Tests/dump.test
--- a/sql/test/sql_dump/Tests/dump.test
+++ b/sql/test/sql_dump/Tests/dump.test
@@ -352,12 +352,12 @@ ALTER SEQUENCE "sys"."seq7" RESTART WITH
ALTER SEQUENCE "sys"."seq8" RESTART WITH -5 INCREMENT BY -1 MINVALUE -10
MAXVALUE -1 NO CYCLE;
ALTER SEQUENCE "sys"."seq9" RESTART WITH 10 MINVALUE 10 MAXVALUE 10 NO CYCLE;
ALTER TABLE "sys"."yoyo" ALTER COLUMN "tsz" SET DEFAULT 'BLABOLO';
+ALTER TABLE "sbar"."bar" ADD CONSTRAINT "bar_pk" PRIMARY KEY ("bi", "bs");
ALTER TABLE "sys"."bolo" ADD CONSTRAINT "cpk" PRIMARY KEY ("s", "v");
+ALTER TABLE "sfoo"."foo" ADD CONSTRAINT "foo_pk" PRIMARY KEY ("fi", "fs");
ALTER TABLE "sys"."rolo" ADD CONSTRAINT "rolo_v_pkey" PRIMARY KEY ("v");
ALTER TABLE "sys"."ungolo" ADD CONSTRAINT "ungolo_x_y_unique" UNIQUE ("x",
"y");
ALTER TABLE "sys"."ungolo" ADD CONSTRAINT "ungolo_z_unique" UNIQUE ("z");
-ALTER TABLE "sfoo"."foo" ADD CONSTRAINT "foo_pk" PRIMARY KEY ("fi", "fs");
-ALTER TABLE "sbar"."bar" ADD CONSTRAINT "bar_pk" PRIMARY KEY ("bi", "bs");
CREATE INDEX "ind1" ON "sys"."ungolo"(x,y);
CREATE IMPRINTS INDEX "ind2" ON "sys"."ungolo"(y,z);
CREATE ORDERED INDEX "ind3" ON "sys"."ungolo"(x,z);
_______________________________________________
checkin-list mailing list -- checkin-list@monetdb.org
To unsubscribe send an email to checkin-list-le...@monetdb.org
