Changeset: 2a04469e4c35 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/2a04469e4c35
Modified Files:
sql/test/Dependencies/Tests/dependency_owner_schema_3.test
sql/test/emptydb/Tests/check.SQL.py
sql/test/emptydb/Tests/check.stable.out
sql/test/emptydb/Tests/check.stable.out.int128
Branch: mangled
Log Message:
Fix two more regressions.
diffs (truncated from 1238 to 300 lines):
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
----
-2274 values hashing to 4f9b4426928c74b12499a7975579a842
+2277 values hashing to 82599f8f3921727fe13dcd0310143f3b
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.SQL.py
b/sql/test/emptydb/Tests/check.SQL.py
--- a/sql/test/emptydb/Tests/check.SQL.py
+++ b/sql/test/emptydb/Tests/check.SQL.py
@@ -72,7 +72,7 @@ sys_akeys = [
('(SELECT id FROM sys.schemas UNION ALL SELECT id FROM sys._tables UNION
ALL SELECT id FROM sys._columns UNION ALL SELECT id FROM sys.functions) as T',
'T.id'),
('(SELECT id FROM sys.schemas UNION ALL SELECT id FROM sys.tables UNION
ALL SELECT id FROM sys.columns UNION ALL SELECT id FROM sys.functions) as T',
'T.id'),
- ('functions f join sys.args a on f.id=a.func_id', 'schema_id, f.name,
func, mod, language, f.type, side_effect, varres, vararg, a.id'),
+ ('functions f join sys.args a on f.id=a.func_id', 'schema_id, f.sqlname,
func, mod, language, f.type, side_effect, varres, vararg, a.id'),
('args', 'func_id, name, inout'),
('types', 'schema_id, systemname, sqlname'),
('objects', 'id, name'),
@@ -237,7 +237,7 @@ sys_notnull = [
('function_types', 'function_type_name'),
('function_types', 'function_type_keyword'),
('functions', 'id'),
- ('functions', 'name'),
+ ('functions', 'sqlname'),
('functions', 'func'),
('functions', 'mod'),
('functions', 'language'),
@@ -388,7 +388,7 @@ with process.client('sql', format='csv',
clt.stdin.write("select E'\\\\dSv ' || s.name || '.' || t.name from
sys._tables t, sys.schemas s where t.schema_id = s.id and t.query is not null
order by s.name, t.name;\n")
- clt.stdin.write("select proj from (select distinct E'\\\\dSf ' || s.name
|| '.\"' || f.name || '\"' as proj from sys.functions f, sys.schemas s where
f.language between 1 and 2 and f.schema_id = s.id and s.name = 'sys') as
proj(proj) order by proj;\n")
+ clt.stdin.write("select proj from (select distinct E'\\\\dSf ' || s.name
|| '.\"' || f.sqlname || '\"' as proj from sys.functions f, sys.schemas s where
f.language between 1 and 2 and f.schema_id = s.id and s.name = 'sys') as
proj(proj) order by proj;\n")
out, err = clt.communicate()
out = re.sub('^"(.*)"$', r'\1', out, flags=re.MULTILINE).replace('"\n',
'\n').replace('\n"', '\n').replace('""', '"').replace(r'\\', '\\')
@@ -448,7 +448,7 @@ select 'sys.table_partitions', t.name, c
select 'sys.range_partitions', t.name, p.expression, r.minimum, r.maximum,
r.with_nulls from sys.range_partitions r left outer join sys._tables t on t.id
= r.table_id left outer join sys.table_partitions p on r.partition_id = p.id;
select 'sys.value_partitions', t.name, p.expression, v.value from
sys.value_partitions v left outer join sys._tables t on t.id = v.table_id left
outer join sys.table_partitions p on v.partition_id = p.id;
-- external functions that don't reference existing MAL function (should be
empty)
-with funcs as (select name, pcre_replace(func, E'--.*\n*', '', '') as func,
schema_id from sys.functions), x (sname, name, modfunc) as (select s.name,
f.name, replace(pcre_replace(f.func, '.*external name (.*);.*', '$1', 'ims'),
'"', '') from funcs f left outer join sys.schemas s on f.schema_id = s.id where
f.func ilike '% external name %') select 'dangling external functions', * from
x where x.modfunc not in (select m.module || '.' || m."function" from
sys.malfunctions() m);
+with funcs as (select sqlname, pcre_replace(func, E'--.*\n*', '', '') as func,
schema_id from sys.functions), x (sname, name, modfunc) as (select s.name,
f.sqlname, replace(pcre_replace(f.func, '.*external name (.*);.*', '$1',
'ims'), '"', '') from funcs f left outer join sys.schemas s on f.schema_id =
s.id where f.func ilike '% external name %') select 'dangling external
functions', * from x where x.modfunc not in (select m.module || '.' ||
m."function" from sys.malfunctions() m);
-- args
'''
# generate a monster query to get all functions with all their
@@ -461,7 +461,7 @@ MAXARGS = 16
# columns of the args table we're interested in
args = ['name', 'type', 'type_digits', 'type_scale', 'inout']
-out += r"select 'sys.functions', s.name, f.name, case f.system when true then
'SYSTEM' else '' end as system,
replace(replace(replace(pcre_replace(pcre_replace(pcre_replace(f.func,
E'--.*\n', '', ''), E'[ \t\n]+', ' ', 'm'), '^ ', '', ''), '( ', '('), ' )',
')'), 'create system ', 'create ') as query, f.mod, fl.language_name,
ft.function_type_name as func_type, f.side_effect, f.varres, f.vararg,
f.semantics"
+out += r"select 'sys.functions', s.name, f.sqlname, case f.system when true
then 'SYSTEM' else '' end as system,
replace(replace(replace(pcre_replace(pcre_replace(pcre_replace(f.func,
E'--.*\n', '', ''), E'[ \t\n]+', ' ', 'm'), '^ ', '', ''), '( ', '('), ' )',
')'), 'create system ', 'create ') as query, f.mod, fl.language_name,
ft.function_type_name as func_type, f.side_effect, f.varres, f.vararg,
f.semantics"
for i in range(0, MAXARGS):
for a in args[:-1]:
out += ", a%d.%s as %s%d" % (i, a, a, i)
@@ -472,7 +472,7 @@ out += " left outer join sys.function_ty
out += " left outer join sys.function_languages fl on f.language =
fl.language_id"
for i in range(0, MAXARGS):
out += " left outer join sys.args a%d on a%d.func_id = f.id and a%d.number
= %d" % (i, i, i, i)
-out += " order by s.name, f.name, query, func_type"
+out += " order by s.name, f.sqlname, query, func_type"
for i in range(0, MAXARGS):
for a in args:
out += ", %s%d nulls first" % (a, i)
@@ -487,19 +487,19 @@ select 'table comments', s.name, t.name,
select 'column comments', s.name, t.name, col.name, c.remark from sys.schemas
s, sys._tables t, sys._columns col, sys.comments c where s.id = t.schema_id and
t.id = col.table_id and col.id = c.id order by s.name, t.name, col.name;
select 'index comments', s.name, t.name, i.name, c.remark from sys.schemas s,
sys._tables t, sys.idxs i, sys.comments c where s.id = t.schema_id and t.id =
i.table_id and i.id = c.id order by s.name, t.name, i.name;
select 'sequence comments', s.name, q.name, c.remark from sys.schemas s,
sys.sequences q, sys.comments c where s.id = q.schema_id and q.id = c.id order
by s.name, q.name;
-select 'function comments', s.name, f.name, c.remark from sys.schemas s,
sys.functions f, sys.comments c where s.id = f.schema_id and f.id = c.id order
by s.name, f.name;
+select 'function comments', s.name, f.sqlname, c.remark from sys.schemas s,
sys.functions f, sys.comments c where s.id = f.schema_id and f.id = c.id order
by s.name, f.sqlname;
-- db_user_info
select 'sys.db_user_info', u.name, u.fullname, s.name from sys.db_user_info u
left outer join sys.schemas s on u.default_schema = s.id order by u.name;
-- dependencies
-select 'function used by function', s1.name, f1.name, s2.name, f2.name,
dt.dependency_type_name from sys.dependencies d left outer join
sys.dependency_types dt on d.depend_type = dt.dependency_type_id, sys.functions
f1, sys.functions f2, sys.schemas s1, sys.schemas s2 where d.id = f1.id and
d.depend_id = f2.id and f1.schema_id = s1.id and f2.schema_id = s2.id order by
s2.name, f2.name, s1.name, f1.name;
-select 'table used by function', s1.name, t.name, s2.name, f.name,
dt.dependency_type_name from sys.dependencies d left outer join
sys.dependency_types dt on d.depend_type = dt.dependency_type_id, sys._tables
t, sys.schemas s1, sys.functions f, sys.schemas s2 where d.id = t.id and
d.depend_id = f.id and t.schema_id = s1.id and f.schema_id = s2.id order by
s2.name, f.name, s1.name, t.name;
-select 'column used by function', s1.name, t.name, c.name, s2.name, f.name,
dt.dependency_type_name from sys.dependencies d left outer join
sys.dependency_types dt on d.depend_type = dt.dependency_type_id, sys._columns
c, sys._tables t, sys.schemas s1, sys.functions f, sys.schemas s2 where d.id =
c.id and d.depend_id = f.id and c.table_id = t.id and t.schema_id = s1.id and
f.schema_id = s2.id order by s2.name, f.name, s1.name, t.name, c.name;
-select 'function used by view', s1.name, f1.name, s2.name, t2.name,
dt.dependency_type_name from sys.dependencies d left outer join
sys.dependency_types dt on d.depend_type = dt.dependency_type_id, sys.schemas
s1, sys.functions f1, sys.schemas s2, sys._tables t2 where d.id = f1.id and
f1.schema_id = s1.id and d.depend_id = t2.id and t2.schema_id = s2.id order by
s2.name, t2.name, s1.name, f1.name;
+select 'function used by function', s1.name, f1.sqlname, s2.name, f2.sqlname,
dt.dependency_type_name from sys.dependencies d left outer join
sys.dependency_types dt on d.depend_type = dt.dependency_type_id, sys.functions
f1, sys.functions f2, sys.schemas s1, sys.schemas s2 where d.id = f1.id and
d.depend_id = f2.id and f1.schema_id = s1.id and f2.schema_id = s2.id order by
s2.name, f2.sqlname, s1.name, f1.sqlname;
+select 'table used by function', s1.name, t.name, s2.name, f.sqlname,
dt.dependency_type_name from sys.dependencies d left outer join
sys.dependency_types dt on d.depend_type = dt.dependency_type_id, sys._tables
t, sys.schemas s1, sys.functions f, sys.schemas s2 where d.id = t.id and
d.depend_id = f.id and t.schema_id = s1.id and f.schema_id = s2.id order by
s2.name, f.sqlname, s1.name, t.name;
+select 'column used by function', s1.name, t.name, c.name, s2.name, f.sqlname,
dt.dependency_type_name from sys.dependencies d left outer join
sys.dependency_types dt on d.depend_type = dt.dependency_type_id, sys._columns
c, sys._tables t, sys.schemas s1, sys.functions f, sys.schemas s2 where d.id =
c.id and d.depend_id = f.id and c.table_id = t.id and t.schema_id = s1.id and
f.schema_id = s2.id order by s2.name, f.sqlname, s1.name, t.name, c.name;
+select 'function used by view', s1.name, f1.sqlname, s2.name, t2.name,
dt.dependency_type_name from sys.dependencies d left outer join
sys.dependency_types dt on d.depend_type = dt.dependency_type_id, sys.schemas
s1, sys.functions f1, sys.schemas s2, sys._tables t2 where d.id = f1.id and
f1.schema_id = s1.id and d.depend_id = t2.id and t2.schema_id = s2.id order by
s2.name, t2.name, s1.name, f1.sqlname;
select 'table used by view', s1.name, t1.name, s2.name, t2.name,
dt.dependency_type_name from sys.dependencies d left outer join
sys.dependency_types dt on d.depend_type = dt.dependency_type_id, sys.schemas
s1, sys._tables t1, sys.schemas s2, sys._tables t2 where d.id = t1.id and
t1.schema_id = s1.id and d.depend_id = t2.id and t2.schema_id = s2.id order by
s2.name, t2.name, s1.name, t1.name;
select 'column used by view', s1.name, t1.name, c1.name, s2.name, t2.name,
dt.dependency_type_name from sys.dependencies d left outer join
sys.dependency_types dt on d.depend_type = dt.dependency_type_id, sys.schemas
s1, sys._tables t1, sys._columns c1, sys.schemas s2, sys._tables t2 where d.id
= c1.id and c1.table_id = t1.id and t1.schema_id = s1.id and d.depend_id =
t2.id and t2.schema_id = s2.id order by s2.name, t2.name, s1.name, t1.name,
c1.name;
select 'column used by key', s1.name, t1.name, c1.name, s2.name, t2.name,
k2.name, dt.dependency_type_name from sys.dependencies d left outer join
sys.dependency_types dt on d.depend_type = dt.dependency_type_id, sys._tables
t1, sys._tables t2, sys.schemas s1, sys.schemas s2, sys._columns c1, sys.keys
k2 where d.id = c1.id and d.depend_id = k2.id and c1.table_id = t1.id and
t1.schema_id = s1.id and k2.table_id = t2.id and t2.schema_id = s2.id order by
s2.name, t2.name, k2.name, s1.name, t1.name, c1.name;
select 'column used by index', s1.name, t1.name, c1.name, s2.name, t2.name,
i2.name, dt.dependency_type_name from sys.dependencies d left outer join
sys.dependency_types dt on d.depend_type = dt.dependency_type_id, sys._tables
t1, sys._tables t2, sys.schemas s1, sys.schemas s2, sys._columns c1, sys.idxs
i2 where d.id = c1.id and d.depend_id = i2.id and c1.table_id = t1.id and
t1.schema_id = s1.id and i2.table_id = t2.id and t2.schema_id = s2.id order by
s2.name, t2.name, i2.name, s1.name, t1.name, c1.name;
-select 'type used by function', t.systemname, t.sqlname, s.name, f.name,
dt.dependency_type_name from sys.dependencies d left outer join
sys.dependency_types dt on d.depend_type = dt.dependency_type_id, sys.types t,
sys.functions f, sys.schemas s where d.id = t.id and d.depend_id = f.id and
f.schema_id = s.id order by s.name, f.name, t.systemname, t.sqlname;
+select 'type used by function', t.systemname, t.sqlname, s.name, f.sqlname,
dt.dependency_type_name from sys.dependencies d left outer join
sys.dependency_types dt on d.depend_type = dt.dependency_type_id, sys.types t,
sys.functions f, sys.schemas s where d.id = t.id and d.depend_id = f.id and
f.schema_id = s.id order by s.name, f.sqlname, t.systemname, t.sqlname;
-- idxs
select 'sys.idxs', t.name, i.name, it.index_type_name from sys.idxs i left
outer join sys._tables t on t.id = i.table_id left outer join sys.index_types
as it on i.type = it.index_type_id order by t.name, i.name;
-- keys
@@ -514,7 +514,7 @@ select 'grant on table', t.name, a.name,
-- columns
select 'grant on column', t.name, c.name, a.name, pc.privilege_code_name,
g.name, p.grantable from sys._tables t, sys._columns c, sys.privileges p left
outer join sys.auths g on p.grantor = g.id left outer join sys.privilege_codes
pc on p.privileges = pc.privilege_code_id, sys.auths a where c.id = p.obj_id
and c.table_id = t.id and p.auth_id = a.id order by t.name, c.name, a.name;
-- functions
-select 'grant on function', s.name, f.name, a.name, pc.privilege_code_name,
g.name, p.grantable from sys.functions f left outer join sys.schemas s on
f.schema_id = s.id, sys.privileges p left outer join sys.auths g on p.grantor =
g.id left outer join sys.privilege_codes pc on p.privileges =
pc.privilege_code_id, sys.auths a where f.id = p.obj_id and p.auth_id = a.id
order by s.name, f.name, a.name;
+select 'grant on function', s.name, f.sqlname, a.name, pc.privilege_code_name,
g.name, p.grantable from sys.functions f left outer join sys.schemas s on
f.schema_id = s.id, sys.privileges p left outer join sys.auths g on p.grantor =
g.id left outer join sys.privilege_codes pc on p.privileges =
pc.privilege_code_id, sys.auths a where f.id = p.obj_id and p.auth_id = a.id
order by s.name, f.sqlname, a.name;
-- sequences
select 'sys.sequences', s.name, q.name, q.start, q.minvalue, q.maxvalue,
q.increment, q.cacheinc, q.cycle from sys.sequences q left outer join
sys.schemas s on q.schema_id = s.id order by s.name, q.name;
-- statistics (expect empty)
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
@@ -981,7 +981,7 @@ CREATE TABLE "sys"."function_languages"
ALTER TABLE "sys"."function_languages" SET READ ONLY;
CREATE TABLE "sys"."function_types" ("function_type_id" SMALLINT NOT NULL,
"function_type_name" VARCHAR(30) NOT NULL, "function_type_keyword" VARCHAR(30)
NOT NULL, CONSTRAINT "function_types_function_type_id_pkey" PRIMARY KEY
("function_type_id"), CONSTRAINT "function_types_function_type_name_unique"
UNIQUE ("function_type_name"));
ALTER TABLE "sys"."function_types" SET READ ONLY;
-CREATE TABLE "sys"."functions" ("id" INTEGER, "name" VARCHAR(256), "func"
VARCHAR(8196), "mod" VARCHAR(8196), "language" INTEGER, "type" INTEGER,
"side_effect" BOOLEAN, "varres" BOOLEAN, "vararg" BOOLEAN, "schema_id" INTEGER,
"system" BOOLEAN, "semantics" BOOLEAN);
+CREATE TABLE "sys"."functions" ("id" INTEGER, "name" VARCHAR(4098), "func"
VARCHAR(8196), "mod" VARCHAR(8196), "language" INTEGER, "type" INTEGER,
"side_effect" BOOLEAN, "varres" BOOLEAN, "vararg" BOOLEAN, "schema_id" INTEGER,
"system" BOOLEAN, "semantics" BOOLEAN, "sqlname" VARCHAR(256));
CREATE TABLE "sys"."idxs" ("id" INTEGER, "table_id" INTEGER, "type" INTEGER,
"name" VARCHAR(1024));
CREATE TABLE "sys"."index_types" ("index_type_id" SMALLINT NOT NULL,
"index_type_name" VARCHAR(25) NOT NULL, CONSTRAINT
"index_types_index_type_id_pkey" PRIMARY KEY ("index_type_id"), CONSTRAINT
"index_types_index_type_name_unique" UNIQUE ("index_type_name"));
ALTER TABLE "sys"."index_types" SET READ ONLY;
@@ -1046,10 +1046,10 @@ create view sys.dependency_views_on_func
create view sys.dependency_views_on_procedures as select v.schema_id as
view_schema_id, v.id as view_id, v.name as view_name, p.id as procedure_id,
p.name as procedure_name, p.type as procedure_type, dep.depend_type as
depend_type from sys.functions as p, sys.tables as v, sys.dependencies as dep
where v.id = dep.id and p.id = dep.depend_id and dep.depend_type = 13 and
p.type = 2 and v.type in (1, 11) order by v.name, v.schema_id, p.name, p.id;
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;
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;
-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,
sys.fqn(s.name, f.name) from sys.functions f, sys.function_types ft,
sys.schemas s where f.type = ft.f
unction_type_id and f.schema_id = s.id) as o(id, tpe, nme) join sys.comments c
on c.id = o.id;
+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,
sys.fqn(s.name, f.sqlname) from sys.functions f, sys.function_types ft,
sys.schemas s where f.type = f
t.function_type_id and f.schema_id = s.id) as o(id, tpe, nme) join
sys.comments c on c.id = o.id;
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);
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;
-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_ret
s_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;
+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_ret
s_all where number = max_number) select f.id o, s.name sch, f.sqlname fun,
case when f.language in (1, 2) then f.func else 'CREATE ' ||
ft.function_type_keyword || ' ' || sys.fqn(s.name, f.sqlname) || '(' ||
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 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;
@@ -1065,7 +1065,7 @@ create view sys.dump_create_roles as sel
create view sys.dump_create_schemas as 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_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' || ifthenelse(ui.schema_path =
'"sys"', '', ' SCHEMA PATH ' || sys.sq(ui.schema_path)) || ';' 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_foreign_keys as select 'ALTER TABLE ' || sys.fqn(fk_s,
fk_t) || ' ADD CONSTRAINT ' || sys.dq(fk) || ' ' || 'FOREIGN KEY(' ||
group_concat(sys.dq(fk_c), ',') ||') ' || 'REFERENCES ' || sys.fqn(pk_s, 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_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.fqn(s.name, 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 wh
ere 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_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.fqn(s.name, f.sqlname) || '(' ||
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.sqlname 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.sqlname, a.name,
g.name, p.grantable;
create view sys.dump_functions as select f.o o, sys.schema_guard(f.sch, f.fun,
f.def) stmt, f.sch schema_name, f.fun function_name from sys.describe_functions
f;
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;
@@ -1079,7 +1079,7 @@ create view sys.dump_triggers as select
create view sys.dump_user_defined_types as select 'CREATE TYPE ' ||
sys.fqn(sch, sql_tpe) || ' EXTERNAL NAME ' || sys.dq(ext_tpe) || ';' stmt, sch
schema_name, sql_tpe type_name from sys.describe_user_defined_types;
create view sys.environment as select * from sys.env();
create view sys.fkeys as select id, table_id, type, name, rkey,
update_action_id, upd.action_name as update_action, delete_action_id,
del.action_name as delete_action from (select id, table_id, type, name, rkey,
cast((("action" >> 8) & 255) as smallint) as update_action_id, cast(("action" &
255) as smallint) as delete_action_id from sys.keys where type = 2 union all
select id, table_id, type, name, rkey, cast((("action" >> 8) & 255) as
smallint) as update_action_id, cast(("action" & 255) as smallint) as
delete_action_id from tmp.keys where type = 2) as fks join sys.fkey_actions upd
on fks.update_action_id = upd.action_id join sys.fkey_actions del on
fks.delete_action_id = del.action_id;
-create view sys.fully_qualified_functions as with fqn(id, tpe, sig, num) as
(select f.id, ft.function_type_keyword, case when a.type is null then s.name ||
'.' || f.name || '()' else 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
where s.id= f.schema_id and f.type = ft.function_type_id) select fqn1.id id,
fqn1.tpe tpe, fqn1.sig nme from fqn fqn1 join (select id, max(num) from fqn
group by id) fqn2(id, num) on fqn1.id = fqn2.id and (fqn1.num = fqn2.num or
fqn1.num is null and fqn2.num is null);
+create view sys.fully_qualified_functions as with fqn(id, tpe, sig, num) as
(select f.id, ft.function_type_keyword, case when a.type is null then s.name ||
'.' || f.sqlname || '()' else s.name || '.' || f.sqlname || '(' ||
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
where s.id= f.schema_id and f.type = ft.function_type_id) select fqn1.id id,
fqn1.tpe tpe, fqn1.sig nme from fqn fqn1 join (select id, max(num) from fqn
group by id) fqn2(id, num) on fqn1.id = fqn2.id and (fqn1.num = fqn2.num or
fqn1.num is null and fqn2.num is null);
create view sys.geometry_columns as select cast(null as varchar(1)) as
f_table_catalog, s.name as f_table_schema, t.name as f_table_name, c.name as
f_geometry_column, cast(has_z(c.type_digits) + has_m(c.type_digits) +2 as
integer) as coord_dimension, c.type_scale as srid, get_type(c.type_digits, 0)
as type from sys.columns c, sys.tables t, sys.schemas s where c.table_id = t.id
and t.schema_id = s.id and c.type in (select sqlname from sys.types where
systemname in ('wkb', 'wkba'));
create view sys.ids (id, name, schema_id, table_id, table_name, obj_type,
sys_table, system) as select id, name, cast(null as int) as schema_id,
cast(null as int) as table_id, cast(null as varchar(124)) as table_name,
'author' as obj_type, 'sys.auths' as sys_table, (name in
('public','sysadmin','monetdb','.snapshot')) as system from sys.auths union all
select id, name, cast(null as int) as schema_id, cast(null as int) as table_id,
cast(null as varchar(124)) as table_name, ifthenelse(system, 'system schema',
'schema'), 'sys.schemas', system from sys.schemas union all select t.id, name,
t.schema_id, t.id as table_id, t.name as table_name,
cast(lower(tt.table_type_name) as varchar(40)), 'sys.tables', t.system from
sys.tables t left outer join sys.table_types tt on t.type = tt.table_type_id
union all select c.id, c.name, t.schema_id, c.table_id, t.name as table_name,
ifthenelse(t.system, 'system column', 'column'), 'sys._columns', t.system from
sys._columns c join sys._tables t on c.tab
le_id = t.id union all select c.id, c.name, t.schema_id, c.table_id, t.name as
table_name, 'column', 'tmp._columns', t.system from tmp._columns c join
tmp._tables t on c.table_id = t.id union all select k.id, k.name, t.schema_id,
k.table_id, t.name as table_name, ifthenelse(t.system, 'system key', 'key'),
'sys.keys', t.system from sys.keys k join sys._tables t on k.table_id = t.id
union all select k.id, k.name, t.schema_id, k.table_id, t.name as table_name,
'key', 'tmp.keys', t.system from tmp.keys k join tmp._tables t on k.table_id =
t.id union all select i.id, i.name, t.schema_id, i.table_id, t.name as
table_name, ifthenelse(t.system, 'system index', 'index'), 'sys.idxs', t.system
from sys.idxs i join sys._tables t on i.table_id = t.id union all select i.id,
i.name, t.schema_id, i.table_id, t.name as table_name, 'index' , 'tmp.idxs',
t.system from tmp.idxs i join tmp._tables t on i.table_id = t.id union all
select g.id, g.name, t.schema_id, g.table_id, t.name as table_name, ifthen
else(t.system, 'system trigger', 'trigger'), 'sys.triggers', t.system from
sys.triggers g join sys._tables t on g.table_id = t.id union all select g.id,
g.name, t.schema_id, g.table_id, t.name as table_name, 'trigger',
'tmp.triggers', t.system from tmp.triggers g join tmp._tables t on g.table_id =
t.id union all select f.id, f.name, f.schema_id, cast(null as int) as table_id,
cast(null as varchar(124)) as table_name, cast(ifthenelse(f.system, 'system ',
'') || lower(ft.function_type_keyword) as varchar(40)), 'sys.functions',
f.system from sys.functions f left outer join sys.function_types ft on f.type =
ft.function_type_id union all select a.id, a.name, f.schema_id, a.func_id as
table_id, f.name as table_name, cast(ifthenelse(f.system, 'system ', '') ||
lower(ft.function_type_keyword) || ' arg' as varchar(44)), 'sys.args', f.system
from sys.args a join sys.functions f on a.func_id = f.id left outer join
sys.function_types ft on f.type = ft.function_type_id union all select id, name,
schema_id, cast(null as int) as table_id, cast(null as varchar(124)) as
table_name, 'sequence', 'sys.sequences', false from sys.sequences union all
select o.id, o.name, pt.schema_id, pt.id, pt.name, 'partition of merge table',
'sys.objects', false from sys.objects o join sys._tables pt on o.sub = pt.id
join sys._tables mt on o.nr = mt.id where mt.type = 3 union all select id,
sqlname, schema_id, cast(null as int) as table_id, cast(null as varchar(124))
as table_name, 'type', 'sys.types', (sqlname in ('inet','json','url','uuid'))
from sys.types where id > 2000 order by id;
create view sys.malfunctions as select * from sys.malfunctions();
@@ -1163,7 +1163,7 @@ create function sys.deltas ("schema" str
create function sys.deltas ("schema" string, "table" string) returns table
("id" int, "segments" bigint, "all" bigint, "inserted" bigint, "updates"
bigint, "deletes" bigint, "level" int) external name "sql"."deltas";
create function sys.deltas ("schema" string, "table" string, "column" string)
returns table ("id" int, "segments" bigint, "all" bigint, "inserted" bigint,
"updates" bigint, "deletes" bigint, "level" int) external name "sql"."deltas";
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, 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 and
t.name = tablename and t.schema_id = s.id and s.name = schemaname order by
c.number; end;
-create function sys.describe_function(schemaname string, functionname string)
returns table(id integer, name string, type string, language string, remark
string) begin return select f.id, f.name, ft.function_type_keyword,
fl.language_keyword, c.remark from sys.functions f 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 left
outer join sys.comments c on f.id = c.id where f.name=functionname and s.name =
schemaname; end;
+create function sys.describe_function(schemaname string, functionname string)
returns table(id integer, name string, type string, language string, remark
string) begin return select f.id, f.sqlname, ft.function_type_keyword,
fl.language_keyword, c.remark from sys.functions f 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 left
outer join sys.comments c on f.id = c.id where f.sqlname=functionname and
s.name = schemaname; end;
create function sys.describe_table(schemaname string, tablename string)
returns table(name string, query string, type string, id integer, remark
string) begin return select t.name, t.query, tt.table_type_name, t.id, c.remark
from sys.schemas s, sys.table_types tt, sys._tables t left outer join
sys.comments c on t.id = c.id where s.name = schemaname and t.schema_id = s.id
and t.name = tablename and t.type = tt.table_type_id; end;
create function sys.describe_type(ctype string, digits integer, tscale
integer) returns string begin return case ctype when 'bigint' then 'BIGINT'
when 'blob' then case digits when 0 then 'BINARY LARGE OBJECT' else 'BINARY
LARGE OBJECT(' || digits || ')' end when 'boolean' then 'BOOLEAN' when 'char'
then case digits when 1 then 'CHARACTER' else 'CHARACTER(' || digits || ')' end
when 'clob' then case digits when 0 then 'CHARACTER LARGE OBJECT' else
'CHARACTER LARGE OBJECT(' || digits || ')' end when 'date' then 'DATE' when
'day_interval' then 'INTERVAL DAY' when ctype = 'decimal' then case when
(digits = 1 and tscale = 0) or digits = 0 then 'DECIMAL' when tscale = 0 then
'DECIMAL(' || digits || ')' when digits = 39 then 'DECIMAL(' || 38 || ',' ||
tscale || ')' when digits = 19 and (select count(*) = 0 from sys.types where
sqlname = 'hugeint') then 'DECIMAL(' || 18 || ',' || tscale || ')' else
'DECIMAL(' || digits || ',' || tscale || ')' end when 'double' then case when
digits = 53 an
d tscale = 0 then 'DOUBLE' when tscale = 0 then 'FLOAT(' || digits || ')' else
'FLOAT(' || digits || ',' || tscale || ')' end when 'geometry' then case digits
when 4 then 'GEOMETRY(POINT' || case tscale when 0 then '' else ',' || tscale
end || ')' when 8 then 'GEOMETRY(LINESTRING' || case tscale when 0 then '' else
',' || tscale end || ')' when 16 then 'GEOMETRY(POLYGON' || case tscale when 0
then '' else ',' || tscale end || ')' when 20 then 'GEOMETRY(MULTIPOINT' ||
case tscale when 0 then '' else ',' || tscale end || ')' when 24 then
'GEOMETRY(MULTILINESTRING' || case tscale when 0 then '' else ',' || tscale end
|| ')' when 28 then 'GEOMETRY(MULTIPOLYGON' || case tscale when 0 then '' else
',' || tscale end || ')' when 32 then 'GEOMETRY(GEOMETRYCOLLECTION' || case
tscale when 0 then '' else ',' || tscale end || ')' else 'GEOMETRY' end when
'hugeint' then 'HUGEINT' when 'int' then 'INTEGER' when 'month_interval' then
case digits when 1 then 'INTERVAL YEAR' when 2 then 'INTERVAL YEA
R TO MONTH' when 3 then 'INTERVAL MONTH' end when 'real' then case when digits
= 24 and tscale = 0 then 'REAL' when tscale = 0 then 'FLOAT(' || digits || ')'
else 'FLOAT(' || digits || ',' || tscale || ')' end when 'sec_interval' then
case digits when 4 then 'INTERVAL DAY' when 5 then 'INTERVAL DAY TO HOUR' when
6 then 'INTERVAL DAY TO MINUTE' when 7 then 'INTERVAL DAY TO SECOND' when 8
then 'INTERVAL HOUR' when 9 then 'INTERVAL HOUR TO MINUTE' when 10 then
'INTERVAL HOUR TO SECOND' when 11 then 'INTERVAL MINUTE' when 12 then 'INTERVAL
MINUTE TO SECOND' when 13 then 'INTERVAL SECOND' end when 'smallint' then
'SMALLINT' when 'time' then case digits when 1 then 'TIME' else 'TIME(' ||
(digits - 1) || ')' end when 'timestamp' then case digits when 7 then
'TIMESTAMP' else 'TIMESTAMP(' || (digits - 1) || ')' end when 'timestamptz'
then case digits when 7 then 'TIMESTAMP' else 'TIMESTAMP(' || (digits - 1) ||
')' end || ' WITH TIME ZONE' when 'timetz' then case digits when 1 then 'TIME'
els
e 'TIME(' || (digits - 1) || ')' end || ' WITH TIME ZONE' when 'tinyint' then
'TINYINT' when 'varchar' then 'CHARACTER VARYING(' || digits || ')' else case
when lower(ctype) = ctype then upper(ctype) else '"' || ctype || '"' end ||
case digits when 0 then '' else '(' || digits || case tscale when 0 then ''
else ',' || tscale end || ')' end end;
end;
@@ -1540,9 +1540,9 @@ select 'sys.table_partitions', t.name, c
select 'sys.range_partitions', t.name, p.expression, r.minimum, r.maximum,
r.with_nulls from sys.range_partitions r left outer join sys._tables t on t.id
= r.table_id left outer join sys.table_partitions p on r.partition_id = p.id;
select 'sys.value_partitions', t.name, p.expression, v.value from
sys.value_partitions v left outer join sys._tables t on t.id = v.table_id left
outer join sys.table_partitions p on v.partition_id = p.id;
-- external functions that don't reference existing MAL function (should be
empty)
-with funcs as (select name, pcre_replace(func, E'--.*\n*', '', '') as func,
schema_id from sys.functions), x (sname, name, modfunc) as (select s.name,
f.name, replace(pcre_replace(f.func, '.*external name (.*);.*', '$1', 'ims'),
'"', '') from funcs f left outer join sys.schemas s on f.schema_id = s.id where
f.func ilike '% external name %') select 'dangling external functions', * from
x where x.modfunc not in (select m.module || '.' || m."function" from
sys.malfunctions() m);
+with funcs as (select sqlname, pcre_replace(func, E'--.*\n*', '', '') as func,
schema_id from sys.functions), x (sname, name, modfunc) as (select s.name,
f.sqlname, replace(pcre_replace(f.func, '.*external name (.*);.*', '$1',
'ims'), '"', '') from funcs f left outer join sys.schemas s on f.schema_id =
s.id where f.func ilike '% external name %') select 'dangling external
functions', * from x where x.modfunc not in (select m.module || '.' ||
m."function" from sys.malfunctions() m);
-- args
-select 'sys.functions', s.name, f.name, case f.system when true then 'SYSTEM'
else '' end as system,
replace(replace(replace(pcre_replace(pcre_replace(pcre_replace(f.func,
E'--.*\n', '', ''), E'[ \t\n]+', ' ', 'm'), '^ ', '', ''), '( ', '('), ' )',
')'), 'create system ', 'create ') as query, f.mod, fl.language_name,
ft.function_type_name as func_type, f.side_effect, f.varres, f.vararg,
f.semantics, a0.name as name0, a0.type as type0, a0.type_digits as
type_digits0, a0.type_scale as type_scale0, case a0.inout when 0 then 'out'
when 1 then 'in' end as inout0, a1.name as name1, a1.type as type1,
a1.type_digits as type_digits1, a1.type_scale as type_scale1, case a1.inout
when 0 then 'out' when 1 then 'in' end as inout1, a2.name as name2, a2.type as
type2, a2.type_digits as type_digits2, a2.type_scale as type_scale2, case
a2.inout when 0 then 'out' when 1 then 'in' end as inout2, a3.name as name3,
a3.type as type3, a3.type_digits as type_digits3, a3.type_scale as type_scale3,
case a3.in
out when 0 then 'out' when 1 then 'in' end as inout3, a4.name as name4,
a4.type as type4, a4.type_digits as type_digits4, a4.type_scale as type_scale4,
case a4.inout when 0 then 'out' when 1 then 'in' end as inout4, a5.name as
name5, a5.type as type5, a5.type_digits as type_digits5, a5.type_scale as
type_scale5, case a5.inout when 0 then 'out' when 1 then 'in' end as inout5,
a6.name as name6, a6.type as type6, a6.type_digits as type_digits6,
a6.type_scale as type_scale6, case a6.inout when 0 then 'out' when 1 then 'in'
end as inout6, a7.name as name7, a7.type as type7, a7.type_digits as
type_digits7, a7.type_scale as type_scale7, case a7.inout when 0 then 'out'
when 1 then 'in' end as inout7, a8.name as name8, a8.type as type8,
a8.type_digits as type_digits8, a8.type_scale as type_scale8, case a8.inout
when 0 then 'out' when 1 then 'in' end as inout8, a9.name as name9, a9.type as
type9, a9.type_digits as type_digits9, a9.type_scale as type_scale9, case
a9.inout when 0 then 'out' whe
n 1 then 'in' end as inout9, a10.name as name10, a10.type as type10,
a10.type_digits as type_digits10, a10.type_scale as type_scale10, case
a10.inout when 0 then 'out' when 1 then 'in' end as inout10, a11.name as
name11, a11.type as type11, a11.type_digits as type_digits11, a11.type_scale as
type_scale11, case a11.inout when 0 then 'out' when 1 then 'in' end as inout11,
a12.name as name12, a12.type as type12, a12.type_digits as type_digits12,
a12.type_scale as type_scale12, case a12.inout when 0 then 'out' when 1 then
'in' end as inout12, a13.name as name13, a13.type as type13, a13.type_digits as
type_digits13, a13.type_scale as type_scale13, case a13.inout when 0 then 'out'
when 1 then 'in' end as inout13, a14.name as name14, a14.type as type14,
a14.type_digits as type_digits14, a14.type_scale as type_scale14, case
a14.inout when 0 then 'out' when 1 then 'in' end as inout14, a15.name as
name15, a15.type as type15, a15.type_digits as type_digits15, a15.type_scale as
type_scale15, ca
se a15.inout when 0 then 'out' when 1 then 'in' end as inout15 from
sys.functions f left outer join sys.schemas s on f.schema_id = s.id left outer
join sys.function_types as ft on f.type = ft.function_type_id left outer join
sys.function_languages fl on f.language = fl.language_id left outer join
sys.args a0 on a0.func_id = f.id and a0.number = 0 left outer join sys.args a1
on a1.func_id = f.id and a1.number = 1 left outer join sys.args a2 on
a2.func_id = f.id and a2.number = 2 left outer join sys.args a3 on a3.func_id =
f.id and a3.number = 3 left outer join sys.args a4 on a4.func_id = f.id and
a4.number = 4 left outer join sys.args a5 on a5.func_id = f.id and a5.number =
5 left outer join sys.args a6 on a6.func_id = f.id and a6.number = 6 left outer
join sys.args a7 on a7.func_id = f.id and a7.number = 7 left outer join
sys.args a8 on a8.func_id = f.id and a8.number = 8 left outer join sys.args a9
on a9.func_id = f.id and a9.number = 9 left outer join sys.args a10 on
a10.func_id =
f.id and a10.number = 10 left outer join sys.args a11 on a11.func_id = f.id
and a11.number = 11 left outer join sys.args a12 on a12.func_id = f.id and
a12.number = 12 left outer join sys.args a13 on a13.func_id = f.id and
a13.number = 13 left outer join sys.args a14 on a14.func_id = f.id and
a14.number = 14 left outer join sys.args a15 on a15.func_id = f.id and
a15.number = 15 order by s.name, f.name, query, func_type, name0 nulls first,
type0 nulls first, type_digits0 nulls first, type_scale0 nulls first, inout0
nulls first, name1 nulls first, type1 nulls first, type_digits1 nulls first,
type_scale1 nulls first, inout1 nulls first, name2 nulls first, type2 nulls
first, type_digits2 nulls first, type_scale2 nulls first, inout2 nulls first,
name3 nulls first, type3 nulls first, type_digits3 nulls first, type_scale3
nulls first, inout3 nulls first, name4 nulls first, type4 nulls first,
type_digits4 nulls first, type_scale4 nulls first, inout4 nulls first, name5
nulls first, type5 nul
ls first, type_digits5 nulls first, type_scale5 nulls first, inout5 nulls
first, name6 nulls first, type6 nulls first, type_digits6 nulls first,
type_scale6 nulls first, inout6 nulls first, name7 nulls first, type7 nulls
first, type_digits7 nulls first, type_scale7 nulls first, inout7 nulls first,
name8 nulls first, type8 nulls first, type_digits8 nulls first, type_scale8
nulls first, inout8 nulls first, name9 nulls first, type9 nulls first,
type_digits9 nulls first, type_scale9 nulls first, inout9 nulls first, name10
nulls first, type10 nulls first, type_digits10 nulls first, type_scale10 nulls
first, inout10 nulls first, name11 nulls first, type11 nulls first,
type_digits11 nulls first, type_scale11 nulls first, inout11 nulls first,
name12 nulls first, type12 nulls first, type_digits12 nulls first, type_scale12
nulls first, inout12 nulls first, name13 nulls first, type13 nulls first,
type_digits13 nulls first, type_scale13 nulls first, inout13 nulls first,
name14 nulls first, type
14 nulls first, type_digits14 nulls first, type_scale14 nulls first, inout14
nulls first, name15 nulls first, type15 nulls first, type_digits15 nulls first,
type_scale15 nulls first, inout15 nulls first;
+select 'sys.functions', s.name, f.sqlname, case f.system when true then
'SYSTEM' else '' end as system,
replace(replace(replace(pcre_replace(pcre_replace(pcre_replace(f.func,
E'--.*\n', '', ''), E'[ \t\n]+', ' ', 'm'), '^ ', '', ''), '( ', '('), ' )',
')'), 'create system ', 'create ') as query, f.mod, fl.language_name,
ft.function_type_name as func_type, f.side_effect, f.varres, f.vararg,
f.semantics, a0.name as name0, a0.type as type0, a0.type_digits as
type_digits0, a0.type_scale as type_scale0, case a0.inout when 0 then 'out'
when 1 then 'in' end as inout0, a1.name as name1, a1.type as type1,
a1.type_digits as type_digits1, a1.type_scale as type_scale1, case a1.inout
when 0 then 'out' when 1 then 'in' end as inout1, a2.name as name2, a2.type as
type2, a2.type_digits as type_digits2, a2.type_scale as type_scale2, case
a2.inout when 0 then 'out' when 1 then 'in' end as inout2, a3.name as name3,
a3.type as type3, a3.type_digits as type_digits3, a3.type_scale as type_scale3,
case a3
.inout when 0 then 'out' when 1 then 'in' end as inout3, a4.name as name4,
a4.type as type4, a4.type_digits as type_digits4, a4.type_scale as type_scale4,
case a4.inout when 0 then 'out' when 1 then 'in' end as inout4, a5.name as
name5, a5.type as type5, a5.type_digits as type_digits5, a5.type_scale as
type_scale5, case a5.inout when 0 then 'out' when 1 then 'in' end as inout5,
a6.name as name6, a6.type as type6, a6.type_digits as type_digits6,
a6.type_scale as type_scale6, case a6.inout when 0 then 'out' when 1 then 'in'
end as inout6, a7.name as name7, a7.type as type7, a7.type_digits as
type_digits7, a7.type_scale as type_scale7, case a7.inout when 0 then 'out'
when 1 then 'in' end as inout7, a8.name as name8, a8.type as type8,
a8.type_digits as type_digits8, a8.type_scale as type_scale8, case a8.inout
when 0 then 'out' when 1 then 'in' end as inout8, a9.name as name9, a9.type as
type9, a9.type_digits as type_digits9, a9.type_scale as type_scale9, case
a9.inout when 0 then 'out'
when 1 then 'in' end as inout9, a10.name as name10, a10.type as type10,
a10.type_digits as type_digits10, a10.type_scale as type_scale10, case
a10.inout when 0 then 'out' when 1 then 'in' end as inout10, a11.name as
name11, a11.type as type11, a11.type_digits as type_digits11, a11.type_scale as
type_scale11, case a11.inout when 0 then 'out' when 1 then 'in' end as inout11,
a12.name as name12, a12.type as type12, a12.type_digits as type_digits12,
a12.type_scale as type_scale12, case a12.inout when 0 then 'out' when 1 then
'in' end as inout12, a13.name as name13, a13.type as type13, a13.type_digits as
type_digits13, a13.type_scale as type_scale13, case a13.inout when 0 then 'out'
when 1 then 'in' end as inout13, a14.name as name14, a14.type as type14,
a14.type_digits as type_digits14, a14.type_scale as type_scale14, case
a14.inout when 0 then 'out' when 1 then 'in' end as inout14, a15.name as
name15, a15.type as type15, a15.type_digits as type_digits15, a15.type_scale as
type_scale15,
case a15.inout when 0 then 'out' when 1 then 'in' end as inout15 from
sys.functions f left outer join sys.schemas s on f.schema_id = s.id left outer
join sys.function_types as ft on f.type = ft.function_type_id left outer join
sys.function_languages fl on f.language = fl.language_id left outer join
sys.args a0 on a0.func_id = f.id and a0.number = 0 left outer join sys.args a1
on a1.func_id = f.id and a1.number = 1 left outer join sys.args a2 on
a2.func_id = f.id and a2.number = 2 left outer join sys.args a3 on a3.func_id =
f.id and a3.number = 3 left outer join sys.args a4 on a4.func_id = f.id and
a4.number = 4 left outer join sys.args a5 on a5.func_id = f.id and a5.number =
5 left outer join sys.args a6 on a6.func_id = f.id and a6.number = 6 left outer
join sys.args a7 on a7.func_id = f.id and a7.number = 7 left outer join
sys.args a8 on a8.func_id = f.id and a8.number = 8 left outer join sys.args a9
on a9.func_id = f.id and a9.number = 9 left outer join sys.args a10 on
a10.func_i
d = f.id and a10.number = 10 left outer join sys.args a11 on a11.func_id =
f.id and a11.number = 11 left outer join sys.args a12 on a12.func_id = f.id and
a12.number = 12 left outer join sys.args a13 on a13.func_id = f.id and
a13.number = 13 left outer join sys.args a14 on a14.func_id = f.id and
a14.number = 14 left outer join sys.args a15 on a15.func_id = f.id and
a15.number = 15 order by s.name, f.sqlname, query, func_type, name0 nulls
first, type0 nulls first, type_digits0 nulls first, type_scale0 nulls first,
inout0 nulls first, name1 nulls first, type1 nulls first, type_digits1 nulls
first, type_scale1 nulls first, inout1 nulls first, name2 nulls first, type2
nulls first, type_digits2 nulls first, type_scale2 nulls first, inout2 nulls
first, name3 nulls first, type3 nulls first, type_digits3 nulls first,
type_scale3 nulls first, inout3 nulls first, name4 nulls first, type4 nulls
first, type_digits4 nulls first, type_scale4 nulls first, inout4 nulls first,
name5 nulls first, typ
e5 nulls first, type_digits5 nulls first, type_scale5 nulls first, inout5
nulls first, name6 nulls first, type6 nulls first, type_digits6 nulls first,
type_scale6 nulls first, inout6 nulls first, name7 nulls first, type7 nulls
first, type_digits7 nulls first, type_scale7 nulls first, inout7 nulls first,
name8 nulls first, type8 nulls first, type_digits8 nulls first, type_scale8
nulls first, inout8 nulls first, name9 nulls first, type9 nulls first,
type_digits9 nulls first, type_scale9 nulls first, inout9 nulls first, name10
nulls first, type10 nulls first, type_digits10 nulls first, type_scale10 nulls
first, inout10 nulls first, name11 nulls first, type11 nulls first,
type_digits11 nulls first, type_scale11 nulls first, inout11 nulls first,
name12 nulls first, type12 nulls first, type_digits12 nulls first, type_scale12
nulls first, inout12 nulls first, name13 nulls first, type13 nulls first,
type_digits13 nulls first, type_scale13 nulls first, inout13 nulls first,
name14 nulls first
, type14 nulls first, type_digits14 nulls first, type_scale14 nulls first,
inout14 nulls first, name15 nulls first, type15 nulls first, type_digits15
nulls first, type_scale15 nulls first, inout15 nulls first;
-- auths
select 'sys.auths', name, grantor from sys.auths;
-- comments
@@ -1551,19 +1551,19 @@ select 'table comments', s.name, t.name,
select 'column comments', s.name, t.name, col.name, c.remark from sys.schemas
s, sys._tables t, sys._columns col, sys.comments c where s.id = t.schema_id and
t.id = col.table_id and col.id = c.id order by s.name, t.name, col.name;
select 'index comments', s.name, t.name, i.name, c.remark from sys.schemas s,
sys._tables t, sys.idxs i, sys.comments c where s.id = t.schema_id and t.id =
i.table_id and i.id = c.id order by s.name, t.name, i.name;
select 'sequence comments', s.name, q.name, c.remark from sys.schemas s,
sys.sequences q, sys.comments c where s.id = q.schema_id and q.id = c.id order
by s.name, q.name;
-select 'function comments', s.name, f.name, c.remark from sys.schemas s,
sys.functions f, sys.comments c where s.id = f.schema_id and f.id = c.id order
by s.name, f.name;
+select 'function comments', s.name, f.sqlname, c.remark from sys.schemas s,
sys.functions f, sys.comments c where s.id = f.schema_id and f.id = c.id order
by s.name, f.sqlname;
-- db_user_info
select 'sys.db_user_info', u.name, u.fullname, s.name from sys.db_user_info u
left outer join sys.schemas s on u.default_schema = s.id order by u.name;
-- dependencies
-select 'function used by function', s1.name, f1.name, s2.name, f2.name,
dt.dependency_type_name from sys.dependencies d left outer join
sys.dependency_types dt on d.depend_type = dt.dependency_type_id, sys.functions
f1, sys.functions f2, sys.schemas s1, sys.schemas s2 where d.id = f1.id and
d.depend_id = f2.id and f1.schema_id = s1.id and f2.schema_id = s2.id order by
s2.name, f2.name, s1.name, f1.name;
-select 'table used by function', s1.name, t.name, s2.name, f.name,
dt.dependency_type_name from sys.dependencies d left outer join
sys.dependency_types dt on d.depend_type = dt.dependency_type_id, sys._tables
t, sys.schemas s1, sys.functions f, sys.schemas s2 where d.id = t.id and
d.depend_id = f.id and t.schema_id = s1.id and f.schema_id = s2.id order by
s2.name, f.name, s1.name, t.name;
-select 'column used by function', s1.name, t.name, c.name, s2.name, f.name,
dt.dependency_type_name from sys.dependencies d left outer join
sys.dependency_types dt on d.depend_type = dt.dependency_type_id, sys._columns
c, sys._tables t, sys.schemas s1, sys.functions f, sys.schemas s2 where d.id =
c.id and d.depend_id = f.id and c.table_id = t.id and t.schema_id = s1.id and
f.schema_id = s2.id order by s2.name, f.name, s1.name, t.name, c.name;
-select 'function used by view', s1.name, f1.name, s2.name, t2.name,
dt.dependency_type_name from sys.dependencies d left outer join
sys.dependency_types dt on d.depend_type = dt.dependency_type_id, sys.schemas
s1, sys.functions f1, sys.schemas s2, sys._tables t2 where d.id = f1.id and
f1.schema_id = s1.id and d.depend_id = t2.id and t2.schema_id = s2.id order by
s2.name, t2.name, s1.name, f1.name;
+select 'function used by function', s1.name, f1.sqlname, s2.name, f2.sqlname,
dt.dependency_type_name from sys.dependencies d left outer join
sys.dependency_types dt on d.depend_type = dt.dependency_type_id, sys.functions
f1, sys.functions f2, sys.schemas s1, sys.schemas s2 where d.id = f1.id and
d.depend_id = f2.id and f1.schema_id = s1.id and f2.schema_id = s2.id order by
s2.name, f2.sqlname, s1.name, f1.sqlname;
+select 'table used by function', s1.name, t.name, s2.name, f.sqlname,
dt.dependency_type_name from sys.dependencies d left outer join
sys.dependency_types dt on d.depend_type = dt.dependency_type_id, sys._tables
t, sys.schemas s1, sys.functions f, sys.schemas s2 where d.id = t.id and
d.depend_id = f.id and t.schema_id = s1.id and f.schema_id = s2.id order by
s2.name, f.sqlname, s1.name, t.name;
+select 'column used by function', s1.name, t.name, c.name, s2.name, f.sqlname,
dt.dependency_type_name from sys.dependencies d left outer join
sys.dependency_types dt on d.depend_type = dt.dependency_type_id, sys._columns
c, sys._tables t, sys.schemas s1, sys.functions f, sys.schemas s2 where d.id =
c.id and d.depend_id = f.id and c.table_id = t.id and t.schema_id = s1.id and
f.schema_id = s2.id order by s2.name, f.sqlname, s1.name, t.name, c.name;
+select 'function used by view', s1.name, f1.sqlname, s2.name, t2.name,
dt.dependency_type_name from sys.dependencies d left outer join
sys.dependency_types dt on d.depend_type = dt.dependency_type_id, sys.schemas
s1, sys.functions f1, sys.schemas s2, sys._tables t2 where d.id = f1.id and
f1.schema_id = s1.id and d.depend_id = t2.id and t2.schema_id = s2.id order by
s2.name, t2.name, s1.name, f1.sqlname;
select 'table used by view', s1.name, t1.name, s2.name, t2.name,
dt.dependency_type_name from sys.dependencies d left outer join
sys.dependency_types dt on d.depend_type = dt.dependency_type_id, sys.schemas
s1, sys._tables t1, sys.schemas s2, sys._tables t2 where d.id = t1.id and
t1.schema_id = s1.id and d.depend_id = t2.id and t2.schema_id = s2.id order by
s2.name, t2.name, s1.name, t1.name;
select 'column used by view', s1.name, t1.name, c1.name, s2.name, t2.name,
dt.dependency_type_name from sys.dependencies d left outer join
sys.dependency_types dt on d.depend_type = dt.dependency_type_id, sys.schemas
s1, sys._tables t1, sys._columns c1, sys.schemas s2, sys._tables t2 where d.id
= c1.id and c1.table_id = t1.id and t1.schema_id = s1.id and d.depend_id =
t2.id and t2.schema_id = s2.id order by s2.name, t2.name, s1.name, t1.name,
c1.name;
select 'column used by key', s1.name, t1.name, c1.name, s2.name, t2.name,
k2.name, dt.dependency_type_name from sys.dependencies d left outer join
sys.dependency_types dt on d.depend_type = dt.dependency_type_id, sys._tables
t1, sys._tables t2, sys.schemas s1, sys.schemas s2, sys._columns c1, sys.keys
k2 where d.id = c1.id and d.depend_id = k2.id and c1.table_id = t1.id and
t1.schema_id = s1.id and k2.table_id = t2.id and t2.schema_id = s2.id order by
s2.name, t2.name, k2.name, s1.name, t1.name, c1.name;
select 'column used by index', s1.name, t1.name, c1.name, s2.name, t2.name,
i2.name, dt.dependency_type_name from sys.dependencies d left outer join
sys.dependency_types dt on d.depend_type = dt.dependency_type_id, sys._tables
t1, sys._tables t2, sys.schemas s1, sys.schemas s2, sys._columns c1, sys.idxs
i2 where d.id = c1.id and d.depend_id = i2.id and c1.table_id = t1.id and
t1.schema_id = s1.id and i2.table_id = t2.id and t2.schema_id = s2.id order by
s2.name, t2.name, i2.name, s1.name, t1.name, c1.name;
-select 'type used by function', t.systemname, t.sqlname, s.name, f.name,
dt.dependency_type_name from sys.dependencies d left outer join
sys.dependency_types dt on d.depend_type = dt.dependency_type_id, sys.types t,
sys.functions f, sys.schemas s where d.id = t.id and d.depend_id = f.id and
f.schema_id = s.id order by s.name, f.name, t.systemname, t.sqlname;
+select 'type used by function', t.systemname, t.sqlname, s.name, f.sqlname,
dt.dependency_type_name from sys.dependencies d left outer join
sys.dependency_types dt on d.depend_type = dt.dependency_type_id, sys.types t,
sys.functions f, sys.schemas s where d.id = t.id and d.depend_id = f.id and
f.schema_id = s.id order by s.name, f.sqlname, t.systemname, t.sqlname;
-- idxs
select 'sys.idxs', t.name, i.name, it.index_type_name from sys.idxs i left
outer join sys._tables t on t.id = i.table_id left outer join sys.index_types
as it on i.type = it.index_type_id order by t.name, i.name;
-- keys
@@ -1578,7 +1578,7 @@ select 'grant on table', t.name, a.name,
-- columns
select 'grant on column', t.name, c.name, a.name, pc.privilege_code_name,
g.name, p.grantable from sys._tables t, sys._columns c, sys.privileges p left
outer join sys.auths g on p.grantor = g.id left outer join sys.privilege_codes
pc on p.privileges = pc.privilege_code_id, sys.auths a where c.id = p.obj_id
and c.table_id = t.id and p.auth_id = a.id order by t.name, c.name, a.name;
-- functions
-select 'grant on function', s.name, f.name, a.name, pc.privilege_code_name,
g.name, p.grantable from sys.functions f left outer join sys.schemas s on
f.schema_id = s.id, sys.privileges p left outer join sys.auths g on p.grantor =
g.id left outer join sys.privilege_codes pc on p.privileges =
pc.privilege_code_id, sys.auths a where f.id = p.obj_id and p.auth_id = a.id
order by s.name, f.name, a.name;
+select 'grant on function', s.name, f.sqlname, a.name, pc.privilege_code_name,
g.name, p.grantable from sys.functions f left outer join sys.schemas s on
f.schema_id = s.id, sys.privileges p left outer join sys.auths g on p.grantor =
g.id left outer join sys.privilege_codes pc on p.privileges =
pc.privilege_code_id, sys.auths a where f.id = p.obj_id and p.auth_id = a.id
order by s.name, f.sqlname, a.name;
-- sequences
select 'sys.sequences', s.name, q.name, q.start, q.minvalue, q.maxvalue,
q.increment, q.cacheinc, q.cycle from sys.sequences q left outer join
sys.schemas s on q.schema_id = s.id order by s.name, q.name;
-- statistics (expect empty)
@@ -1662,7 +1662,7 @@ select 'duplicates in _columns', count(*
select 'duplicates in columns', count(*), table_id, number from sys.columns
group by table_id, number having count(*) > 1;
select 'duplicates in (SELECT id FROM sys.schemas UNION ALL SELECT id FROM
sys._tables UNION ALL SELECT id FROM sys._columns UNION ALL SELECT id FROM
sys.functions) as T', count(*), T.id from (SELECT id FROM sys.schemas UNION ALL
SELECT id FROM sys._tables UNION ALL SELECT id FROM sys._columns UNION ALL
SELECT id FROM sys.functions) as T group by T.id having count(*) > 1;
select 'duplicates in (SELECT id FROM sys.schemas UNION ALL SELECT id FROM
sys.tables UNION ALL SELECT id FROM sys.columns UNION ALL SELECT id FROM
sys.functions) as T', count(*), T.id from (SELECT id FROM sys.schemas UNION ALL
SELECT id FROM sys.tables UNION ALL SELECT id FROM sys.columns UNION ALL SELECT
id FROM sys.functions) as T group by T.id having count(*) > 1;
-select 'duplicates in functions f join sys.args a on f.id=a.func_id',
count(*), schema_id, f.name, func, mod, language, f.type, side_effect, varres,
vararg, a.id from sys.functions f join sys.args a on f.id=a.func_id group by
schema_id, f.name, func, mod, language, f.type, side_effect, varres, vararg,
a.id having count(*) > 1;
+select 'duplicates in functions f join sys.args a on f.id=a.func_id',
count(*), schema_id, f.sqlname, func, mod, language, f.type, side_effect,
varres, vararg, a.id from sys.functions f join sys.args a on f.id=a.func_id
group by schema_id, f.sqlname, func, mod, language, f.type, side_effect,
varres, vararg, a.id having count(*) > 1;
select 'duplicates in args', count(*), func_id, name, inout from sys.args
group by func_id, name, inout having count(*) > 1;
select 'duplicates in types', count(*), schema_id, systemname, sqlname from
sys.types group by schema_id, systemname, sqlname having count(*) > 1;
select 'duplicates in objects', count(*), id, name from sys.objects group by
id, name having count(*) > 1;
@@ -1814,7 +1814,7 @@ select 'null in function_types.function_
select 'null in function_types.function_type_name', function_type_name, * from
sys.function_types where function_type_name is null;
select 'null in function_types.function_type_keyword', function_type_keyword,
* from sys.function_types where function_type_keyword is null;
select 'null in functions.id', id, * from sys.functions where id is null;
-select 'null in functions.name', name, * from sys.functions where name is null;
+select 'null in functions.sqlname', sqlname, * from sys.functions where
sqlname is null;
select 'null in functions.func', func, * from sys.functions where func is null;
select 'null in functions.mod', mod, * from sys.functions where mod is null;
select 'null in functions.language', language, * from sys.functions where
language is null;
@@ -1998,10 +1998,10 @@ select 'null in value_partitions.value',
[ "sys._tables", "sys", "dependency_views_on_procedures", "create
view sys.dependency_views_on_procedures as select v.schema_id as
view_schema_id, v.id as view_id, v.name as view_name, p.id as procedure_id,
p.name as procedure_name, p.type as procedure_type, dep.depend_type as
depend_type from sys.functions as p, sys.tables as v, sys.dependencies as dep
where v.id = dep.id and p.id = dep.depend_id and dep.depend_type = 13 and
p.type = 2 and v.type in (1, 11) order by v.name, v.schema_id, p.name, p.id;",
"VIEW", true, "COMMIT", "WRITABLE" ]
[ "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" ]
[ "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" ]
-[ "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, sys.fqn(s.name, f.name) from
sys.functions f, sys.functi
on_types ft, sys.schemas s where f.type = ft.function_type_id and f.schema_id
= s.id) as o(id, tpe, nme) join sys.comments c on c.id = o.id;", "VIEW", true,
"COMMIT", "WRITABLE" ]
+[ "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, sys.fqn(s.name, f.sqlname)
from sys.functions f, sys.fun
ction_types ft, sys.schemas s where f.type = ft.function_type_id and
f.schema_id = s.id) as o(id, tpe, nme) join sys.comments c on c.id = o.id;",
"VIEW", true, "COMMIT", "WRITABLE" ]
[ "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" ]
[ "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" ]
-[ "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" ]
+[ "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.sqlname fun, case when f.language in
(1, 2) then f.func else 'CREATE ' || ft.function_type_keyword || ' ' ||
sys.fqn(s.name, f.sqlname) || '(' || 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" ]
[ "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" ]
@@ -2017,7 +2017,7 @@ select 'null in value_partitions.value',
[ "sys._tables", "sys", "dump_create_schemas", "create view
sys.dump_create_schemas as 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;", "VIEW", true, "COMMIT",
"WRITABLE" ]
[ "sys._tables", "sys", "dump_create_users", "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' || ifthenelse(ui.schema_path = '\"sys\"',
'', ' SCHEMA PATH ' || sys.sq(ui.schema_path)) || ';' 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';", "VIEW", true, "COMMIT",
"WRITABLE" ]
[ "sys._tables", "sys", "dump_foreign_keys", "create view
sys.dump_foreign_keys as select 'ALTER TABLE ' || sys.fqn(fk_s, fk_t) || ' ADD
CONSTRAINT ' || sys.dq(fk) || ' ' || 'FOREIGN KEY(' ||
group_concat(sys.dq(fk_c), ',') ||') ' || 'REFERENCES ' || sys.fqn(pk_s, 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;", "VIEW", true, "COMMIT", "WRITABLE" ]
-[ "sys._tables", "sys", "dump_function_grants", "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.fqn(s.name, 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;", "VIEW", true, "COMMIT",
"WRITABLE" ]
+[ "sys._tables", "sys", "dump_function_grants", "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.fqn(s.name, f.sqlname) || '(' || 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.sqlname 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.au
ths 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.sqlname, a.name, g.name, p.grantable;", "VIEW", true,
"COMMIT", "WRITABLE" ]
[ "sys._tables", "sys", "dump_functions", "create view
sys.dump_functions as select f.o o, sys.schema_guard(f.sch, f.fun, f.def) stmt,
f.sch schema_name, f.fun function_name from sys.describe_functions f;",
"VIEW", true, "COMMIT", "WRITABLE" ]
[ "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" ]
@@ -2033,7 +2033,7 @@ select 'null in value_partitions.value',
[ "sys._tables", "sys", "environment", "create view sys.environment as
select * from sys.env();", "VIEW", true, "COMMIT", "WRITABLE"
]
[ "sys._tables", "sys", "fkey_actions", NULL, "TABLE", true,
"COMMIT", "READONLY" ]
[ "sys._tables", "sys", "fkeys", "create view sys.fkeys as
select id, table_id, type, name, rkey, update_action_id, upd.action_name as
update_action, delete_action_id, del.action_name as delete_action from (select
id, table_id, type, name, rkey, cast(((\"action\" >> 8) & 255) as smallint) as
update_action_id, cast((\"action\" & 255) as smallint) as delete_action_id from
sys.keys where type = 2 union all select id, table_id, type, name, rkey,
cast(((\"action\" >> 8) & 255) as smallint) as update_action_id,
cast((\"action\" & 255) as smallint) as delete_action_id from tmp.keys where
type = 2) as fks join sys.fkey_actions upd on fks.update_action_id =
upd.action_id join sys.fkey_actions del on fks.delete_action_id =
del.action_id;", "VIEW", true, "COMMIT", "WRITABLE" ]
-[ "sys._tables", "sys", "fully_qualified_functions", "create view
sys.fully_qualified_functions as with fqn(id, tpe, sig, num) as (select f.id,
ft.function_type_keyword, case when a.type is null then s.name || '.' || f.name
|| '()' else 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
where s.id= f.schema_id and f.type = ft.function_type_id) select fqn1.id id,
fqn1.tpe tpe, fqn1.sig nme from fqn fqn1 join (select id, max(num) from fqn
group by id) fqn2(id, num) on fqn1.id = fqn2.id and (fqn1.num = fqn2.num or
fqn1.num is null and fqn2.num is null);", "VIEW", true, "COMMIT",
"WRITABLE" ]
+[ "sys._tables", "sys", "fully_qualified_functions", "create view
sys.fully_qualified_functions as with fqn(id, tpe, sig, num) as (select f.id,
ft.function_type_keyword, case when a.type is null then s.name || '.' ||
f.sqlname || '()' else s.name || '.' || f.sqlname || '(' ||
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
where s.id= f.schema_id and f.type = ft.function_type_id) select fqn1.id id,
fqn1.tpe tpe, fqn1.sig nme from fqn fqn1 join (select id, max(num) from fqn
group by id) fqn2(id, num) on fqn1.id = fqn2.id and (fqn1.num = fqn2.num or
fqn1.num is null and fqn2.num is null);", "VIEW", true, "COMMIT",
"WRITABLE" ]
[ "sys._tables", "sys", "function_languages", NULL, "TABLE",
true, "COMMIT", "READONLY" ]
[ "sys._tables", "sys", "function_types", NULL, "TABLE",
true, "COMMIT", "READONLY" ]
[ "sys._tables", "sys", "functions", NULL, "TABLE", true,
"COMMIT", "WRITABLE" ]
@@ -2165,16 +2165,16 @@ select 'null in value_partitions.value',
[ "sys._columns", "dependencies", "depend_type", "smallint", 16,
0, NULL, true, 2, NULL ]
[ "sys._columns", "dependencies_vw", "id", "int", 32, 0,
NULL, true, 0, NULL ]
[ "sys._columns", "dependencies_vw", "obj_type", "char", 44,
0, NULL, true, 1, NULL ]
-[ "sys._columns", "dependencies_vw", "name", "varchar", 1024,
0, NULL, true, 2, NULL ]
+[ "sys._columns", "dependencies_vw", "name", "varchar", 4098,
0, NULL, true, 2, NULL ]
[ "sys._columns", "dependencies_vw", "used_by_id", "int", 32,
0, NULL, true, 3, NULL ]
[ "sys._columns", "dependencies_vw", "used_by_obj_type", "char",
44, 0, NULL, true, 4, NULL ]
-[ "sys._columns", "dependencies_vw", "used_by_name", "varchar",
1024, 0, NULL, true, 5, NULL ]
+[ "sys._columns", "dependencies_vw", "used_by_name", "varchar",
4098, 0, NULL, true, 5, NULL ]
[ "sys._columns", "dependencies_vw", "depend_type", "smallint",
16, 0, NULL, true, 6, NULL ]
[ "sys._columns", "dependencies_vw", "dependency_type_name",
"varchar", 15, 0, NULL, true, 7, NULL ]
[ "sys._columns", "dependency_args_on_types", "type_id", "int",
32, 0, NULL, true, 0, NULL ]
[ "sys._columns", "dependency_args_on_types", "type_name",
"varchar", 1024, 0, NULL, true, 1, NULL ]
[ "sys._columns", "dependency_args_on_types", "function_id", "int",
32, 0, NULL, true, 2, NULL ]
-[ "sys._columns", "dependency_args_on_types", "function_name",
"varchar", 256, 0, NULL, true, 3, NULL ]
+[ "sys._columns", "dependency_args_on_types", "function_name",
"varchar", 4098, 0, NULL, true, 3, NULL ]
[ "sys._columns", "dependency_args_on_types", "arg_id", "int",
32, 0, NULL, true, 4, NULL ]
[ "sys._columns", "dependency_args_on_types", "arg_name",
"varchar", 256, 0, NULL, true, 5, NULL ]
[ "sys._columns", "dependency_args_on_types", "arg_nr", "int",
32, 0, NULL, true, 6, NULL ]
@@ -2183,7 +2183,7 @@ select 'null in value_partitions.value',
[ "sys._columns", "dependency_columns_on_functions", "column_id",
"int", 32, 0, NULL, true, 1, NULL ]
[ "sys._columns", "dependency_columns_on_functions", "name",
"varchar", 1024, 0, NULL, true, 2, NULL ]
[ "sys._columns", "dependency_columns_on_functions", "function_id",
"int", 32, 0, NULL, true, 3, NULL ]
-[ "sys._columns", "dependency_columns_on_functions",
"function_name", "varchar", 256, 0, NULL, true, 4,
NULL ]
+[ "sys._columns", "dependency_columns_on_functions",
"function_name", "varchar", 4098, 0, NULL, true, 4,
NULL ]
[ "sys._columns", "dependency_columns_on_functions",
"function_type", "int", 32, 0, NULL, true, 5, NULL
]
[ "sys._columns", "dependency_columns_on_functions", "depend_type",
"smallint", 16, 0, NULL, true, 6, NULL ]
[ "sys._columns", "dependency_columns_on_indexes", "column_id",
"int", 32, 0, NULL, true, 0, NULL ]
@@ -2210,7 +2210,7 @@ select 'null in value_partitions.value',
[ "sys._columns", "dependency_columns_on_procedures", "column_id",
"int", 32, 0, NULL, true, 1, NULL ]
[ "sys._columns", "dependency_columns_on_procedures", "column_name",
"varchar", 1024, 0, NULL, true, 2, NULL ]
[ "sys._columns", "dependency_columns_on_procedures", "procedure_id",
"int", 32, 0, NULL, true, 3, NULL ]
-[ "sys._columns", "dependency_columns_on_procedures",
"procedure_name", "varchar", 256, 0, NULL, true, 4,
NULL ]
+[ "sys._columns", "dependency_columns_on_procedures",
"procedure_name", "varchar", 4098, 0, NULL, true, 4,
NULL ]
[ "sys._columns", "dependency_columns_on_procedures",
"procedure_type", "int", 32, 0, NULL, true, 5, NULL
]
[ "sys._columns", "dependency_columns_on_procedures", "depend_type",
"smallint", 16, 0, NULL, true, 6, NULL ]
[ "sys._columns", "dependency_columns_on_triggers",
"table_schema_id", "int", 32, 0, NULL, true, 0, NULL
]
@@ -2240,25 +2240,25 @@ select 'null in value_partitions.value',
[ "sys._columns", "dependency_columns_on_views", "depend_type",
"smallint", 16, 0, NULL, true, 8, NULL ]
[ "sys._columns", "dependency_functions_on_functions", "schema_id",
"int", 32, 0, NULL, true, 0, NULL ]
[ "sys._columns", "dependency_functions_on_functions", "function_id",
"int", 32, 0, NULL, true, 1, NULL ]
-[ "sys._columns", "dependency_functions_on_functions",
"function_name", "varchar", 256, 0, NULL, true, 2,
NULL ]
+[ "sys._columns", "dependency_functions_on_functions",
"function_name", "varchar", 4098, 0, NULL, true, 2,
NULL ]
[ "sys._columns", "dependency_functions_on_functions",
"function_type", "int", 32, 0, NULL, true, 3, NULL
]
[ "sys._columns", "dependency_functions_on_functions",
"used_in_function_schema_id", "int", 32, 0, NULL, true, 4,
NULL ]
[ "sys._columns", "dependency_functions_on_functions",
"used_in_function_id", "int", 32, 0, NULL, true, 5, NULL
]
-[ "sys._columns", "dependency_functions_on_functions",
"used_in_function_name", "varchar", 256, 0, NULL, true,
6, NULL ]
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
