Changeset: 62c00fdffc43 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=62c00fdffc43
Modified Files:
        sql/ChangeLog.Mar2018
        sql/backends/monet5/sql_upgrades.c
        sql/scripts/21_dependency_views.sql
        sql/test/emptydb/Tests/check.stable.out
        sql/test/emptydb/Tests/check.stable.out.32bit
        sql/test/emptydb/Tests/check.stable.out.int128
Branch: Mar2018
Log Message:

Corrected the definition of view sys.ids which has been introduced in Mar2018 
release.
For the tmp.keys, tmp.idxs and tmp.triggers query parts it should have been 
joined with tmp._tables instead of sys._tables.
Also added upgrade code.
Note: view sys.dependencies_vw depends on sys.ids, so it needed to be dropped 
first and also recreated later.


diffs (255 lines):

diff --git a/sql/ChangeLog.Mar2018 b/sql/ChangeLog.Mar2018
--- a/sql/ChangeLog.Mar2018
+++ b/sql/ChangeLog.Mar2018
@@ -1,3 +1,6 @@
 # ChangeLog file for sql
 # This file is updated with Maddlog
 
+* Thu May 17 2018 Martin van Dinther <martin.van.dint...@monetdbsolutions.com>
+- Corrected the definition of view: sys.ids.
+
diff --git a/sql/backends/monet5/sql_upgrades.c 
b/sql/backends/monet5/sql_upgrades.c
--- a/sql/backends/monet5/sql_upgrades.c
+++ b/sql/backends/monet5/sql_upgrades.c
@@ -647,7 +647,7 @@ sql_update_jul2017(Client c, mvc *sql)
        res_table *output;
        BAT *b;
 
-       if( buf== NULL)
+       if( buf == NULL)
                throw(SQL, "sql_update_jul2017", SQLSTATE(HY001) 
MAL_MALLOC_FAIL);
        pos += snprintf(buf + pos, bufsize - pos, "set schema \"sys\";\n");
 
@@ -776,7 +776,7 @@ sql_update_jul2017_sp2(Client c)
                        size_t bufsize = 2048, pos = 0;
                        char *buf = GDKmalloc(bufsize);
 
-                       if (buf== NULL)
+                       if (buf == NULL)
                                throw(SQL, "sql_update_jul2017_sp2", 
SQLSTATE(HY001) MAL_MALLOC_FAIL);
 
                        /* 51_sys_schema_extensions.sql and 25_debug.sql */
@@ -861,7 +861,7 @@ sql_update_mar2018_geom(Client c, mvc *s
        char *buf = GDKmalloc(bufsize), *err = NULL;
        char *schema = stack_get_string(sql, "current_schema");
 
-       if (buf== NULL)
+       if (buf == NULL)
                throw(SQL, "sql_update_mar2018_geom", SQLSTATE(HY001) 
MAL_MALLOC_FAIL);
        pos += snprintf(buf + pos, bufsize - pos, "set schema \"sys\";\n");
 
@@ -927,7 +927,7 @@ sql_update_mar2018(Client c, mvc *sql)
 
        schema = stack_get_string(sql, "current_schema");
        buf = GDKmalloc(bufsize);
-       if (buf== NULL)
+       if (buf == NULL)
                throw(SQL, "sql_update_mar2018", SQLSTATE(HY001) 
MAL_MALLOC_FAIL);
        s = mvc_bind_schema(sql, "sys");
 
@@ -1394,7 +1394,7 @@ sql_update_mar2018_netcdf(Client c, mvc 
 
        schema = stack_get_string(sql, "current_schema");
        buf = GDKmalloc(bufsize);
-       if (buf== NULL)
+       if (buf == NULL)
                throw(SQL, "sql_update_mar2018_netcdf", SQLSTATE(HY001) 
MAL_MALLOC_FAIL);
 
        pos += snprintf(buf + pos, bufsize - pos, "set schema sys;\n");
@@ -1435,7 +1435,7 @@ sql_update_mar2018_samtools(Client c, mv
 
        schema = stack_get_string(sql, "current_schema");
        buf = GDKmalloc(bufsize);
-       if (buf== NULL)
+       if (buf == NULL)
                throw(SQL, "sql_update_mar2018_samtools", SQLSTATE(HY001) 
MAL_MALLOC_FAIL);
 
        pos += snprintf(buf + pos, bufsize - pos, "set schema sys;\n");
@@ -1529,6 +1529,76 @@ sql_update_mar2018_sp1(Client c, mvc *sq
        return err;             /* usually MAL_SUCCEED */
 }
 
+static str
+sql_replace_Mar2018_ids_view(Client c, mvc *sql)
+{
+       size_t bufsize = 4400, pos = 0;
+       char *buf = GDKmalloc(bufsize), *err = NULL;
+       char *schema;
+       sql_schema *s;
+       sql_table *t;
+
+       if (buf == NULL)
+               throw(SQL, "sql_replace_Mar2018_ids_view", SQLSTATE(HY001) 
MAL_MALLOC_FAIL);
+
+       schema = stack_get_string(sql, "current_schema");
+       s = mvc_bind_schema(sql, "sys");
+       t = mvc_bind_table(sql, s, "ids");
+       t->system = 0;  /* make it non-system else the drop view will fail */
+       t = mvc_bind_table(sql, s, "dependencies_vw");  /* dependencies_vw uses 
view sys.ids so must be removed first */
+       t->system = 0;
+
+       /* 21_dependency_views.sql */
+       pos += snprintf(buf + pos, bufsize - pos,
+                       "set schema \"sys\";\n"
+                       "DROP VIEW sys.dependencies_vw;\n"
+                       "DROP VIEW sys.ids;\n"
+
+                       "CREATE VIEW sys.ids (id, name, schema_id, table_id, 
table_name, obj_type, sys_table) AS\n"
+                       "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 FROM sys.auths UNION ALL\n"
+                       "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, 
'schema', 'sys.schemas' FROM sys.schemas UNION ALL\n"
+                       "SELECT id, name, schema_id, id as table_id, name as 
table_name, case when type = 1 then 'view' else 'table' end, 'sys._tables' FROM 
sys._tables UNION ALL\n"
+                       "SELECT id, name, schema_id, id as table_id, name as 
table_name, case when type = 1 then 'view' else 'table' end, 'tmp._tables' FROM 
tmp._tables UNION ALL\n"
+                       "SELECT c.id, c.name, t.schema_id, c.table_id, t.name 
as table_name, 'column', 'sys._columns' FROM sys._columns c JOIN sys._tables t 
ON c.table_id = t.id UNION ALL\n"
+                       "SELECT c.id, c.name, t.schema_id, c.table_id, t.name 
as table_name, 'column', 'tmp._columns' FROM tmp._columns c JOIN tmp._tables t 
ON c.table_id = t.id UNION ALL\n"
+                       "SELECT k.id, k.name, t.schema_id, k.table_id, t.name 
as table_name, 'key', 'sys.keys' FROM sys.keys k JOIN sys._tables t ON 
k.table_id = t.id UNION ALL\n"
+                       "SELECT k.id, k.name, t.schema_id, k.table_id, t.name 
as table_name, 'key', 'tmp.keys' FROM tmp.keys k JOIN tmp._tables t ON 
k.table_id = t.id UNION ALL\n"
+                       "SELECT i.id, i.name, t.schema_id, i.table_id, t.name 
as table_name, 'index', 'sys.idxs' FROM sys.idxs i JOIN sys._tables t ON 
i.table_id = t.id UNION ALL\n"
+                       "SELECT i.id, i.name, t.schema_id, i.table_id, t.name 
as table_name, 'index', 'tmp.idxs' FROM tmp.idxs i JOIN tmp._tables t ON 
i.table_id = t.id UNION ALL\n"
+                       "SELECT g.id, g.name, t.schema_id, g.table_id, t.name 
as table_name, 'trigger', 'sys.triggers' FROM sys.triggers g JOIN sys._tables t 
ON g.table_id = t.id UNION ALL\n"
+                       "SELECT g.id, g.name, t.schema_id, g.table_id, t.name 
as table_name, 'trigger', 'tmp.triggers' FROM tmp.triggers g JOIN tmp._tables t 
ON g.table_id = t.id UNION ALL\n"
+                       "SELECT id, name, schema_id, cast(null as int) as 
table_id, cast(null as varchar(124)) as table_name, case when type = 2 then 
'procedure' else 'function' end, 'sys.functions' FROM sys.functions UNION ALL\n"
+                       "SELECT a.id, a.name, f.schema_id, cast(null as int) as 
table_id, cast(null as varchar(124)) as table_name, case when f.type = 2 then 
'procedure arg' else 'function arg' end, 'sys.args' FROM sys.args a JOIN 
sys.functions f ON a.func_id = f.id UNION ALL\n"
+                       "SELECT id, name, schema_id, cast(null as int) as 
table_id, cast(null as varchar(124)) as table_name, 'sequence', 'sys.sequences' 
FROM sys.sequences UNION ALL\n"
+                       "SELECT id, sqlname, schema_id, cast(null as int) as 
table_id, cast(null as varchar(124)) as table_name, 'type', 'sys.types' FROM 
sys.types WHERE id > 2000 /* exclude system types to prevent duplicates with 
auths.id */\n"
+                       " ORDER BY id;\n"
+                       "GRANT SELECT ON sys.ids TO PUBLIC;\n"
+
+                       "CREATE VIEW sys.dependencies_vw AS\n"
+                       "SELECT d.id, i1.obj_type, i1.name,\n"
+                       "       d.depend_id as used_by_id, i2.obj_type as 
used_by_obj_type, i2.name as used_by_name,\n"
+                       "       d.depend_type, dt.dependency_type_name\n"
+                       "  FROM sys.dependencies d\n"
+                       "  JOIN sys.ids i1 ON d.id = i1.id\n"
+                       "  JOIN sys.ids i2 ON d.depend_id = i2.id\n"
+                       "  JOIN sys.dependency_types dt ON d.depend_type = 
dt.dependency_type_id\n"
+                       " ORDER BY id, depend_id;\n"
+                       "GRANT SELECT ON sys.dependencies_vw TO PUBLIC;\n"
+
+                       "update sys._tables set system = true where name in 
('ids', 'dependencies_vw') and schema_id in (select id from sys.schemas where 
name = 'sys');\n"
+                       );
+
+       if (schema)
+               pos += snprintf(buf + pos, bufsize - pos, "set schema 
\"%s\";\n", schema);
+       pos += snprintf(buf + pos, bufsize - pos, "commit;\n");
+       assert(pos < bufsize);
+
+       printf("Running database upgrade commands:\n%s\n", buf);
+       err = SQLstatementIntern(c, &buf, "update", 1, 0, NULL);
+       GDKfree(buf);
+       return err;             /* usually MAL_SUCCEED */
+}
+
 void
 SQLupgrades(Client c, mvc *m)
 {
@@ -1658,4 +1728,29 @@ SQLupgrades(Client c, mvc *m)
                        freeException(err);
                }
        }
+
+       if (mvc_bind_table(m, s, "ids") != NULL) {
+               /* determine if sys.ids needs to be updated (only the version 
of Mar2018) */
+               char * qry = "select id from sys._tables where name = 'ids' and 
query like '% tmp.keys k join sys._tables% tmp.idxs i join sys._tables% 
tmp.triggers g join sys._tables% ';";
+               res_table *output = NULL;
+               err = SQLstatementIntern(c, &qry, "update", 1, 0, &output);
+               if (err) {
+                       fprintf(stderr, "!%s\n", err);
+                       freeException(err);
+               } else {
+                       BAT *b = BATdescriptor(output->cols[0].b);
+                       if (b) {
+                               if (BATcount(b) > 0) {
+                                       /* yes old view definiton exists, it 
needs to be replaced */
+                                       if ((err = 
sql_replace_Mar2018_ids_view(c, m)) != NULL) {
+                                               fprintf(stderr, "!%s\n", err);
+                                               freeException(err);
+                                       }
+                               }
+                               BBPunfix(b->batCacheid);
+                       }
+               }
+               if (output != NULL)
+                       res_tables_destroy(output);
+       }
 }
diff --git a/sql/scripts/21_dependency_views.sql 
b/sql/scripts/21_dependency_views.sql
--- a/sql/scripts/21_dependency_views.sql
+++ b/sql/scripts/21_dependency_views.sql
@@ -17,11 +17,11 @@ SELECT id, name, schema_id, id as table_
 SELECT c.id, c.name, t.schema_id, c.table_id, t.name as table_name, 'column', 
'sys._columns' FROM sys._columns c JOIN sys._tables t ON c.table_id = t.id 
UNION ALL
 SELECT c.id, c.name, t.schema_id, c.table_id, t.name as table_name, 'column', 
'tmp._columns' 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, 'key', 
'sys.keys' 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' FROM tmp.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' 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, 'index', 
'sys.idxs' 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' FROM tmp.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' 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, 'trigger', 
'sys.triggers' 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' FROM tmp.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' FROM tmp.triggers g JOIN tmp._tables t ON g.table_id = t.id 
UNION ALL
 SELECT id, name, schema_id, cast(null as int) as table_id, cast(null as 
varchar(124)) as table_name, case when type = 2 then 'procedure' else 
'function' end, 'sys.functions' FROM sys.functions UNION ALL
 SELECT a.id, a.name, f.schema_id, cast(null as int) as table_id, cast(null as 
varchar(124)) as table_name, case when f.type = 2 then 'procedure arg' else 
'function arg' end, 'sys.args' FROM sys.args a JOIN sys.functions f ON 
a.func_id = f.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' FROM sys.sequences 
UNION ALL
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
@@ -924,7 +924,7 @@ create view sys.dependency_views_on_proc
 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.environment as select * from sys.env();
 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) 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 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, 'schema', 'sys.schemas' from sys.schemas union all 
select id, name, schema_id, id as table_id, name as table_name, case when type 
= 1 then 'view' else 'table' end, 'sys._tables' from sys._tables union all 
select id, name, schema_id, id as table_id, name as table_name, case when type 
= 1 then 'view' else 'table' end, 'tmp._tables' from tmp._tables union all 
select c.id, c.name, t.schema_id, c.table_id, t.name as table_name, 'column', 
'sys._columns' from sys._columns c join sys._tables t on c.table_id = t.id 
union all select c.id, c.name, t.schema_id, c.table_id, t.name as table_name, 
'co
 lumn', 'tmp._columns' 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, 'key', 'sys.keys' 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' from tmp.keys k join sys._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, 'index', 'sys.idxs' 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' from tmp.idxs i join sys._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, 'trigger', 'sys.triggers' 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' from tmp.triggers g 
join sys._table
 s t on g.table_id = t.id union all select id, name, schema_id, cast(null as 
int) as table_id, cast(null as varchar(124)) as table_name, case when type = 2 
then 'procedure' else 'function' end, 'sys.functions' from sys.functions union 
all select a.id, a.name, f.schema_id, cast(null as int) as table_id, cast(null 
as varchar(124)) as table_name, case when f.type = 2 then 'procedure arg' else 
'function arg' end, 'sys.args' from sys.args a join sys.functions f on 
a.func_id = f.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' 
from sys.sequences 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' from 
sys.types where id > 2000 order by id;
+create view sys.ids (id, name, schema_id, table_id, table_name, obj_type, 
sys_table) 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 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, 'schema', 'sys.schemas' from sys.schemas union all 
select id, name, schema_id, id as table_id, name as table_name, case when type 
= 1 then 'view' else 'table' end, 'sys._tables' from sys._tables union all 
select id, name, schema_id, id as table_id, name as table_name, case when type 
= 1 then 'view' else 'table' end, 'tmp._tables' from tmp._tables union all 
select c.id, c.name, t.schema_id, c.table_id, t.name as table_name, 'column', 
'sys._columns' from sys._columns c join sys._tables t on c.table_id = t.id 
union all select c.id, c.name, t.schema_id, c.table_id, t.name as table_name, 
'co
 lumn', 'tmp._columns' 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, 'key', 'sys.keys' 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' 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, 'index', 'sys.idxs' 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' 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, 'trigger', 'sys.triggers' 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' from tmp.triggers g 
join tmp._table
 s t on g.table_id = t.id union all select id, name, schema_id, cast(null as 
int) as table_id, cast(null as varchar(124)) as table_name, case when type = 2 
then 'procedure' else 'function' end, 'sys.functions' from sys.functions union 
all select a.id, a.name, f.schema_id, cast(null as int) as table_id, cast(null 
as varchar(124)) as table_name, case when f.type = 2 then 'procedure arg' else 
'function arg' end, 'sys.args' from sys.args a join sys.functions f on 
a.func_id = f.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' 
from sys.sequences 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' from 
sys.types where id > 2000 order by id;
 create view sys.optimizers as select * from sys.optimizers();
 create view sys.querylog_calls as select * from sys.querylog_calls();
 create view sys.querylog_catalog as select * from sys.querylog_catalog();
@@ -1429,7 +1429,7 @@ drop function pcre_replace(string, strin
 [ "sys",       "function_types",       NULL,   "TABLE",        true,   
"COMMIT",       "READONLY"      ]
 [ "sys",       "functions",    NULL,   "TABLE",        true,   "COMMIT",       
"WRITABLE"      ]
 [ "sys",       "geometry_columns",     "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'));",        
"VIEW", true,   "COMMIT",       "WRITABLE"      ]
-[ "sys",       "ids",  "create view sys.ids (id, name, schema_id, table_id, 
table_name, obj_type, sys_table) 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 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, 'schema', 'sys.schemas' 
from sys.schemas union all select id, name, schema_id, id as table_id, name as 
table_name, case when type = 1 then 'view' else 'table' end, 'sys._tables' from 
sys._tables union all select id, name, schema_id, id as table_id, name as 
table_name, case when type = 1 then 'view' else 'table' end, 'tmp._tables' from 
tmp._tables union all select c.id, c.name, t.schema_id, c.table_id, t.name as 
table_name, 'column', 'sys._columns' from sys._columns c join sys._tables t on 
c.table_id = t.id union all select c.id, c.name, t.schema_id, c.table_id, 
t.name a
 s table_name, 'column', 'tmp._columns' 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, 'key', 'sys.keys' 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' from tmp.keys k join sys._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, 'index', 'sys.idxs' 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' from tmp.idxs i join sys._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, 'trigger', 'sys.triggers' 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' from tmp.triggers 
 g join sys._tables t on g.table_id = t.id union all select id, name, 
schema_id, cast(null as int) as table_id, cast(null as varchar(124)) as 
table_name, case when type = 2 then 'procedure' else 'function' end, 
'sys.functions' from sys.functions union all select a.id, a.name, f.schema_id, 
cast(null as int) as table_id, cast(null as varchar(124)) as table_name, case 
when f.type = 2 then 'procedure arg' else 'function arg' end, 'sys.args' from 
sys.args a join sys.functions f on a.func_id = f.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' from sys.sequences 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' from sys.types where id > 2000 order by 
id;",     "VIEW", true,   "COMMIT",       "WRITABLE"      ]
+[ "sys",       "ids",  "create view sys.ids (id, name, schema_id, table_id, 
table_name, obj_type, sys_table) 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 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, 'schema', 'sys.schemas' 
from sys.schemas union all select id, name, schema_id, id as table_id, name as 
table_name, case when type = 1 then 'view' else 'table' end, 'sys._tables' from 
sys._tables union all select id, name, schema_id, id as table_id, name as 
table_name, case when type = 1 then 'view' else 'table' end, 'tmp._tables' from 
tmp._tables union all select c.id, c.name, t.schema_id, c.table_id, t.name as 
table_name, 'column', 'sys._columns' from sys._columns c join sys._tables t on 
c.table_id = t.id union all select c.id, c.name, t.schema_id, c.table_id, 
t.name a
 s table_name, 'column', 'tmp._columns' 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, 'key', 'sys.keys' 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' 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, 'index', 'sys.idxs' 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' 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, 'trigger', 'sys.triggers' 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' from tmp.triggers 
 g join tmp._tables t on g.table_id = t.id union all select id, name, 
schema_id, cast(null as int) as table_id, cast(null as varchar(124)) as 
table_name, case when type = 2 then 'procedure' else 'function' end, 
'sys.functions' from sys.functions union all select a.id, a.name, f.schema_id, 
cast(null as int) as table_id, cast(null as varchar(124)) as table_name, case 
when f.type = 2 then 'procedure arg' else 'function arg' end, 'sys.args' from 
sys.args a join sys.functions f on a.func_id = f.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' from sys.sequences 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' from sys.types where id > 2000 order by 
id;",     "VIEW", true,   "COMMIT",       "WRITABLE"      ]
 [ "sys",       "idxs", NULL,   "TABLE",        true,   "COMMIT",       
"WRITABLE"      ]
 [ "sys",       "index_types",  NULL,   "TABLE",        true,   "COMMIT",       
"READONLY"      ]
 [ "sys",       "key_types",    NULL,   "TABLE",        true,   "COMMIT",       
"READONLY"      ]
diff --git a/sql/test/emptydb/Tests/check.stable.out.32bit 
b/sql/test/emptydb/Tests/check.stable.out.32bit
--- a/sql/test/emptydb/Tests/check.stable.out.32bit
+++ b/sql/test/emptydb/Tests/check.stable.out.32bit
@@ -928,7 +928,7 @@ create view sys.dependency_views_on_proc
 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.environment as select * from sys.env();
 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) 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 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, 'schema', 'sys.schemas' from sys.schemas union all 
select id, name, schema_id, id as table_id, name as table_name, case when type 
= 1 then 'view' else 'table' end, 'sys._tables' from sys._tables union all 
select id, name, schema_id, id as table_id, name as table_name, case when type 
= 1 then 'view' else 'table' end, 'tmp._tables' from tmp._tables union all 
select c.id, c.name, t.schema_id, c.table_id, t.name as table_name, 'column', 
'sys._columns' from sys._columns c join sys._tables t on c.table_id = t.id 
union all select c.id, c.name, t.schema_id, c.table_id, t.name as table_name, 
'co
 lumn', 'tmp._columns' 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, 'key', 'sys.keys' 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' from tmp.keys k join sys._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, 'index', 'sys.idxs' 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' from tmp.idxs i join sys._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, 'trigger', 'sys.triggers' 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' from tmp.triggers g 
join sys._table
 s t on g.table_id = t.id union all select id, name, schema_id, cast(null as 
int) as table_id, cast(null as varchar(124)) as table_name, case when type = 2 
then 'procedure' else 'function' end, 'sys.functions' from sys.functions union 
all select a.id, a.name, f.schema_id, cast(null as int) as table_id, cast(null 
as varchar(124)) as table_name, case when f.type = 2 then 'procedure arg' else 
'function arg' end, 'sys.args' from sys.args a join sys.functions f on 
a.func_id = f.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' 
from sys.sequences 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' from 
sys.types where id > 2000 order by id;
+create view sys.ids (id, name, schema_id, table_id, table_name, obj_type, 
sys_table) 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 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, 'schema', 'sys.schemas' from sys.schemas union all 
select id, name, schema_id, id as table_id, name as table_name, case when type 
= 1 then 'view' else 'table' end, 'sys._tables' from sys._tables union all 
select id, name, schema_id, id as table_id, name as table_name, case when type 
= 1 then 'view' else 'table' end, 'tmp._tables' from tmp._tables union all 
select c.id, c.name, t.schema_id, c.table_id, t.name as table_name, 'column', 
'sys._columns' from sys._columns c join sys._tables t on c.table_id = t.id 
union all select c.id, c.name, t.schema_id, c.table_id, t.name as table_name, 
'co
 lumn', 'tmp._columns' 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, 'key', 'sys.keys' 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' 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, 'index', 'sys.idxs' 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' 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, 'trigger', 'sys.triggers' 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' from tmp.triggers g 
join tmp._table
 s t on g.table_id = t.id union all select id, name, schema_id, cast(null as 
int) as table_id, cast(null as varchar(124)) as table_name, case when type = 2 
then 'procedure' else 'function' end, 'sys.functions' from sys.functions union 
all select a.id, a.name, f.schema_id, cast(null as int) as table_id, cast(null 
as varchar(124)) as table_name, case when f.type = 2 then 'procedure arg' else 
'function arg' end, 'sys.args' from sys.args a join sys.functions f on 
a.func_id = f.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' 
from sys.sequences 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' from 
sys.types where id > 2000 order by id;
 create view sys.optimizers as select * from sys.optimizers();
 create view sys.querylog_calls as select * from sys.querylog_calls();
 create view sys.querylog_catalog as select * from sys.querylog_catalog();
@@ -1433,7 +1433,7 @@ drop function pcre_replace(string, strin
 [ "sys",       "function_types",       NULL,   "TABLE",        true,   
"COMMIT",       "READONLY"      ]
 [ "sys",       "functions",    NULL,   "TABLE",        true,   "COMMIT",       
"WRITABLE"      ]
 [ "sys",       "geometry_columns",     "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'));",        
"VIEW", true,   "COMMIT",       "WRITABLE"      ]
-[ "sys",       "ids",  "create view sys.ids (id, name, schema_id, table_id, 
table_name, obj_type, sys_table) 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 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, 'schema', 'sys.schemas' 
from sys.schemas union all select id, name, schema_id, id as table_id, name as 
table_name, case when type = 1 then 'view' else 'table' end, 'sys._tables' from 
sys._tables union all select id, name, schema_id, id as table_id, name as 
table_name, case when type = 1 then 'view' else 'table' end, 'tmp._tables' from 
tmp._tables union all select c.id, c.name, t.schema_id, c.table_id, t.name as 
table_name, 'column', 'sys._columns' from sys._columns c join sys._tables t on 
c.table_id = t.id union all select c.id, c.name, t.schema_id, c.table_id, 
t.name a
 s table_name, 'column', 'tmp._columns' 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, 'key', 'sys.keys' 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' from tmp.keys k join sys._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, 'index', 'sys.idxs' 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' from tmp.idxs i join sys._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, 'trigger', 'sys.triggers' 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' from tmp.triggers 
 g join sys._tables t on g.table_id = t.id union all select id, name, 
schema_id, cast(null as int) as table_id, cast(null as varchar(124)) as 
table_name, case when type = 2 then 'procedure' else 'function' end, 
'sys.functions' from sys.functions union all select a.id, a.name, f.schema_id, 
cast(null as int) as table_id, cast(null as varchar(124)) as table_name, case 
when f.type = 2 then 'procedure arg' else 'function arg' end, 'sys.args' from 
sys.args a join sys.functions f on a.func_id = f.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' from sys.sequences 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' from sys.types where id > 2000 order by 
id;",     "VIEW", true,   "COMMIT",       "WRITABLE"      ]
+[ "sys",       "ids",  "create view sys.ids (id, name, schema_id, table_id, 
table_name, obj_type, sys_table) 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 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, 'schema', 'sys.schemas' 
from sys.schemas union all select id, name, schema_id, id as table_id, name as 
table_name, case when type = 1 then 'view' else 'table' end, 'sys._tables' from 
sys._tables union all select id, name, schema_id, id as table_id, name as 
table_name, case when type = 1 then 'view' else 'table' end, 'tmp._tables' from 
tmp._tables union all select c.id, c.name, t.schema_id, c.table_id, t.name as 
table_name, 'column', 'sys._columns' from sys._columns c join sys._tables t on 
c.table_id = t.id union all select c.id, c.name, t.schema_id, c.table_id, 
t.name a
 s table_name, 'column', 'tmp._columns' 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, 'key', 'sys.keys' 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' 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, 'index', 'sys.idxs' 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' 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, 'trigger', 'sys.triggers' 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' from tmp.triggers 
 g join tmp._tables t on g.table_id = t.id union all select id, name, 
schema_id, cast(null as int) as table_id, cast(null as varchar(124)) as 
table_name, case when type = 2 then 'procedure' else 'function' end, 
'sys.functions' from sys.functions union all select a.id, a.name, f.schema_id, 
cast(null as int) as table_id, cast(null as varchar(124)) as table_name, case 
when f.type = 2 then 'procedure arg' else 'function arg' end, 'sys.args' from 
sys.args a join sys.functions f on a.func_id = f.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' from sys.sequences 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' from sys.types where id > 2000 order by 
id;",     "VIEW", true,   "COMMIT",       "WRITABLE"      ]
 [ "sys",       "idxs", NULL,   "TABLE",        true,   "COMMIT",       
"WRITABLE"      ]
 [ "sys",       "index_types",  NULL,   "TABLE",        true,   "COMMIT",       
"READONLY"      ]
 [ "sys",       "key_types",    NULL,   "TABLE",        true,   "COMMIT",       
"READONLY"      ]
diff --git a/sql/test/emptydb/Tests/check.stable.out.int128 
b/sql/test/emptydb/Tests/check.stable.out.int128
--- a/sql/test/emptydb/Tests/check.stable.out.int128
+++ b/sql/test/emptydb/Tests/check.stable.out.int128
@@ -923,7 +923,7 @@ create view sys.dependency_views_on_proc
 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.environment as select * from sys.env();
 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) 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 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, 'schema', 'sys.schemas' from sys.schemas union all 
select id, name, schema_id, id as table_id, name as table_name, case when type 
= 1 then 'view' else 'table' end, 'sys._tables' from sys._tables union all 
select id, name, schema_id, id as table_id, name as table_name, case when type 
= 1 then 'view' else 'table' end, 'tmp._tables' from tmp._tables union all 
select c.id, c.name, t.schema_id, c.table_id, t.name as table_name, 'column', 
'sys._columns' from sys._columns c join sys._tables t on c.table_id = t.id 
union all select c.id, c.name, t.schema_id, c.table_id, t.name as table_name, 
'co
 lumn', 'tmp._columns' 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, 'key', 'sys.keys' 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' from tmp.keys k join sys._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, 'index', 'sys.idxs' 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' from tmp.idxs i join sys._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, 'trigger', 'sys.triggers' 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' from tmp.triggers g 
join sys._table
 s t on g.table_id = t.id union all select id, name, schema_id, cast(null as 
int) as table_id, cast(null as varchar(124)) as table_name, case when type = 2 
then 'procedure' else 'function' end, 'sys.functions' from sys.functions union 
all select a.id, a.name, f.schema_id, cast(null as int) as table_id, cast(null 
as varchar(124)) as table_name, case when f.type = 2 then 'procedure arg' else 
'function arg' end, 'sys.args' from sys.args a join sys.functions f on 
a.func_id = f.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' 
from sys.sequences 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' from 
sys.types where id > 2000 order by id;
+create view sys.ids (id, name, schema_id, table_id, table_name, obj_type, 
sys_table) 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 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, 'schema', 'sys.schemas' from sys.schemas union all 
select id, name, schema_id, id as table_id, name as table_name, case when type 
= 1 then 'view' else 'table' end, 'sys._tables' from sys._tables union all 
select id, name, schema_id, id as table_id, name as table_name, case when type 
= 1 then 'view' else 'table' end, 'tmp._tables' from tmp._tables union all 
select c.id, c.name, t.schema_id, c.table_id, t.name as table_name, 'column', 
'sys._columns' from sys._columns c join sys._tables t on c.table_id = t.id 
union all select c.id, c.name, t.schema_id, c.table_id, t.name as table_name, 
'co
 lumn', 'tmp._columns' 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, 'key', 'sys.keys' 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' 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, 'index', 'sys.idxs' 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' 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, 'trigger', 'sys.triggers' 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' from tmp.triggers g 
join tmp._table
 s t on g.table_id = t.id union all select id, name, schema_id, cast(null as 
int) as table_id, cast(null as varchar(124)) as table_name, case when type = 2 
then 'procedure' else 'function' end, 'sys.functions' from sys.functions union 
all select a.id, a.name, f.schema_id, cast(null as int) as table_id, cast(null 
as varchar(124)) as table_name, case when f.type = 2 then 'procedure arg' else 
'function arg' end, 'sys.args' from sys.args a join sys.functions f on 
a.func_id = f.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' 
from sys.sequences 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' from 
sys.types where id > 2000 order by id;
 create view sys.optimizers as select * from sys.optimizers();
 create view sys.querylog_calls as select * from sys.querylog_calls();
 create view sys.querylog_catalog as select * from sys.querylog_catalog();
@@ -1438,7 +1438,7 @@ drop function pcre_replace(string, strin
 [ "sys",       "function_types",       NULL,   "TABLE",        true,   
"COMMIT",       "READONLY"      ]
 [ "sys",       "functions",    NULL,   "TABLE",        true,   "COMMIT",       
"WRITABLE"      ]
 [ "sys",       "geometry_columns",     "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'));",        
"VIEW", true,   "COMMIT",       "WRITABLE"      ]
-[ "sys",       "ids",  "create view sys.ids (id, name, schema_id, table_id, 
table_name, obj_type, sys_table) 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 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, 'schema', 'sys.schemas' 
from sys.schemas union all select id, name, schema_id, id as table_id, name as 
table_name, case when type = 1 then 'view' else 'table' end, 'sys._tables' from 
sys._tables union all select id, name, schema_id, id as table_id, name as 
table_name, case when type = 1 then 'view' else 'table' end, 'tmp._tables' from 
tmp._tables union all select c.id, c.name, t.schema_id, c.table_id, t.name as 
table_name, 'column', 'sys._columns' from sys._columns c join sys._tables t on 
c.table_id = t.id union all select c.id, c.name, t.schema_id, c.table_id, 
t.name a
 s table_name, 'column', 'tmp._columns' 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, 'key', 'sys.keys' 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' from tmp.keys k join sys._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, 'index', 'sys.idxs' 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' from tmp.idxs i join sys._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, 'trigger', 'sys.triggers' 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' from tmp.triggers 
 g join sys._tables t on g.table_id = t.id union all select id, name, 
schema_id, cast(null as int) as table_id, cast(null as varchar(124)) as 
table_name, case when type = 2 then 'procedure' else 'function' end, 
'sys.functions' from sys.functions union all select a.id, a.name, f.schema_id, 
cast(null as int) as table_id, cast(null as varchar(124)) as table_name, case 
when f.type = 2 then 'procedure arg' else 'function arg' end, 'sys.args' from 
sys.args a join sys.functions f on a.func_id = f.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' from sys.sequences 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' from sys.types where id > 2000 order by 
id;",     "VIEW", true,   "COMMIT",       "WRITABLE"      ]
+[ "sys",       "ids",  "create view sys.ids (id, name, schema_id, table_id, 
table_name, obj_type, sys_table) 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 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, 'schema', 'sys.schemas' 
from sys.schemas union all select id, name, schema_id, id as table_id, name as 
table_name, case when type = 1 then 'view' else 'table' end, 'sys._tables' from 
sys._tables union all select id, name, schema_id, id as table_id, name as 
table_name, case when type = 1 then 'view' else 'table' end, 'tmp._tables' from 
tmp._tables union all select c.id, c.name, t.schema_id, c.table_id, t.name as 
table_name, 'column', 'sys._columns' from sys._columns c join sys._tables t on 
c.table_id = t.id union all select c.id, c.name, t.schema_id, c.table_id, 
t.name a
 s table_name, 'column', 'tmp._columns' 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, 'key', 'sys.keys' 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' 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, 'index', 'sys.idxs' 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' 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, 'trigger', 'sys.triggers' 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' from tmp.triggers 
 g join tmp._tables t on g.table_id = t.id union all select id, name, 
schema_id, cast(null as int) as table_id, cast(null as varchar(124)) as 
table_name, case when type = 2 then 'procedure' else 'function' end, 
'sys.functions' from sys.functions union all select a.id, a.name, f.schema_id, 
cast(null as int) as table_id, cast(null as varchar(124)) as table_name, case 
when f.type = 2 then 'procedure arg' else 'function arg' end, 'sys.args' from 
sys.args a join sys.functions f on a.func_id = f.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' from sys.sequences 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' from sys.types where id > 2000 order by 
id;",     "VIEW", true,   "COMMIT",       "WRITABLE"      ]
 [ "sys",       "idxs", NULL,   "TABLE",        true,   "COMMIT",       
"WRITABLE"      ]
 [ "sys",       "index_types",  NULL,   "TABLE",        true,   "COMMIT",       
"READONLY"      ]
 [ "sys",       "key_types",    NULL,   "TABLE",        true,   "COMMIT",       
"READONLY"      ]
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list

Reply via email to