Changeset: 9e73a7950c5b for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/9e73a7950c5b
Modified Files:
sql/backends/monet5/sql_upgrades.c
sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out
sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.ppc64.int128
sql/test/emptydb-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade/Tests/upgrade.stable.out
sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
sql/test/emptydb/Tests/check.SQL.py
sql/test/emptydb/Tests/check.stable.out
sql/test/emptydb/Tests/check.stable.out.32bit
sql/test/emptydb/Tests/check.stable.out.int128
sql/test/testdb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade-chain/Tests/upgrade.stable.out
sql/test/testdb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade/Tests/upgrade.stable.out
sql/test/testdb-previous-upgrade/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out
sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade/Tests/upgrade.stable.out
sql/test/testdb-upgrade/Tests/upgrade.stable.out.int128
Branch: default
Log Message:
Fix and approve upgrade and check code.
diffs (truncated from 10149 to 300 lines):
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
@@ -5927,9 +5927,7 @@ sql_update_default(Client c, mvc *sql, s
const char *cmds =
"CREATE SCHEMA INFORMATION_SCHEMA;\n"
"COMMENT ON SCHEMA INFORMATION_SCHEMA IS 'ISO/IEC 9075-11
SQL/Schemata';\n"
- "\n"
"update sys.schemas set system = true where name =
'information_schema';\n"
- "\n"
"CREATE VIEW INFORMATION_SCHEMA.CHARACTER_SETS AS SELECT\n"
" cast(NULL AS varchar(1)) AS CHARACTER_SET_CATALOG,\n"
" cast(NULL AS varchar(1)) AS CHARACTER_SET_SCHEMA,\n"
@@ -5939,9 +5937,7 @@ sql_update_default(Client c, mvc *sql, s
" cast(NULL AS varchar(1)) AS DEFAULT_COLLATE_CATALOG,\n"
" cast(NULL AS varchar(1)) AS DEFAULT_COLLATE_SCHEMA,\n"
" cast(NULL AS varchar(1)) AS DEFAULT_COLLATE_NAME;\n"
- "\n"
"GRANT SELECT ON TABLE INFORMATION_SCHEMA.CHARACTER_SETS TO
PUBLIC WITH GRANT OPTION;\n"
- "\n"
"CREATE VIEW INFORMATION_SCHEMA.SCHEMATA AS SELECT\n"
" cast(NULL AS varchar(1)) AS CATALOG_NAME,\n"
" s.\"name\" AS SCHEMA_NAME,\n"
@@ -5958,9 +5954,7 @@ sql_update_default(Client c, mvc *sql, s
" INNER JOIN sys.\"auths\" a ON s.\"owner\" = a.\"id\"\n"
" LEFT OUTER JOIN sys.\"comments\" cm ON s.\"id\" = cm.\"id\"\n"
" ORDER BY s.\"name\";\n"
- "\n"
"GRANT SELECT ON TABLE INFORMATION_SCHEMA.SCHEMATA TO PUBLIC
WITH GRANT OPTION;\n"
- "\n"
"CREATE VIEW INFORMATION_SCHEMA.TABLES AS SELECT\n"
" cast(NULL AS varchar(1)) AS TABLE_CATALOG,\n"
" s.\"name\" AS TABLE_SCHEMA,\n"
@@ -5989,9 +5983,7 @@ sql_update_default(Client c, mvc *sql, s
" LEFT OUTER JOIN sys.\"comments\" cm ON t.\"id\" = cm.\"id\"\n"
" LEFT OUTER JOIN (SELECT DISTINCT \"schema\", \"table\",
\"count\" FROM sys.\"statistics\"()) st ON (s.\"name\" = st.\"schema\" AND
t.\"name\" = st.\"table\")\n"
" ORDER BY s.\"name\", t.\"name\";\n"
- "\n"
"GRANT SELECT ON TABLE INFORMATION_SCHEMA.TABLES TO PUBLIC WITH
GRANT OPTION;\n"
- "\n"
"CREATE VIEW INFORMATION_SCHEMA.VIEWS AS SELECT\n"
" cast(NULL AS varchar(1)) AS TABLE_CATALOG,\n"
" s.\"name\" AS TABLE_SCHEMA,\n"
@@ -6006,7 +5998,7 @@ sql_update_default(Client c, mvc *sql, s
" -- MonetDB column extensions\n"
" t.\"schema_id\" AS schema_id,\n"
" t.\"id\" AS table_id,\n"
- " cast(sys.ifthenelse(t.\"system\", t.\"type\" + 10 /* system
view */, t.\"type\") AS smallint) AS table_type_id,\n"
+ " cast(sys.ifthenelse(t.\"system\", t.\"type\" + 10 ,
t.\"type\") AS smallint) AS table_type_id,\n"
" t.\"system\" AS is_system,\n"
" cm.\"remark\" AS comments\n"
" FROM sys.\"_tables\" t\n"
@@ -6014,9 +6006,7 @@ sql_update_default(Client c, mvc *sql, s
" LEFT OUTER JOIN sys.\"comments\" cm ON t.\"id\" = cm.\"id\"\n"
" WHERE t.\"type\" = 1\n"
" ORDER BY s.\"name\", t.\"name\";\n"
- "\n"
"GRANT SELECT ON TABLE INFORMATION_SCHEMA.VIEWS TO PUBLIC WITH
GRANT OPTION;\n"
- "\n"
"CREATE VIEW INFORMATION_SCHEMA.COLUMNS AS SELECT\n"
" cast(NULL AS varchar(1)) AS TABLE_CATALOG,\n"
" s.\"name\" AS TABLE_SCHEMA,\n"
@@ -6028,9 +6018,9 @@ sql_update_default(Client c, mvc *sql, s
" c.\"type\" AS DATA_TYPE,\n"
" cast(sys.ifthenelse(c.\"type\" IN
('varchar','clob','char','json','url','xml'), c.\"type_digits\", NULL) AS int)
AS CHARACTER_MAXIMUM_LENGTH,\n"
" cast(sys.ifthenelse(c.\"type\" IN
('varchar','clob','char','json','url','xml'), c.\"type_digits\" * 3, NULL) AS
int) AS CHARACTER_OCTET_LENGTH,\n"
- " cast(sys.ifthenelse(c.\"type\" IN
('int','bigint','smallint','tinyint','hugeint','float','real','double','decimal','numeric','oid'),
c.\"type_digits\", NULL) AS int) AS NUMERIC_PRECISION,\n"
- " cast(sys.ifthenelse(c.\"type\" IN
('int','bigint','smallint','tinyint','hugeint','float','real','double','oid'),
2, sys.ifthenelse(c.\"type\" IN ('decimal','numeric'), 10, NULL)) AS int) AS
NUMERIC_PRECISION_RADIX,\n"
- " cast(sys.ifthenelse(c.\"type\" IN
('int','bigint','smallint','tinyint','hugeint','float','real','double','decimal','numeric','oid'),
c.\"type_scale\", NULL) AS int) AS NUMERIC_SCALE,\n"
+ " cast(sys.ifthenelse(c.\"type\" IN
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
c.\"type_digits\", NULL) AS int) AS NUMERIC_PRECISION,\n"
+ " cast(sys.ifthenelse(c.\"type\" IN
('int','smallint','tinyint','bigint','hugeint','float','real','double','oid'),
2, sys.ifthenelse(c.\"type\" IN ('decimal','numeric'), 10, NULL)) AS int) AS
NUMERIC_PRECISION_RADIX,\n"
+ " cast(sys.ifthenelse(c.\"type\" IN
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
c.\"type_scale\", NULL) AS int) AS NUMERIC_SCALE,\n"
" cast(sys.ifthenelse(c.\"type\" IN
('date','timestamp','timestamptz','time','timetz'), c.\"type_scale\" -1, NULL)
AS int) AS DATETIME_PRECISION,\n"
" cast(CASE c.\"type\" WHEN 'day_interval' THEN 'interval day'
WHEN 'month_interval' THEN 'interval month' WHEN 'sec_interval' THEN 'interval
second' ELSE NULL END AS varchar(40)) AS INTERVAL_TYPE,\n"
" cast(sys.ifthenelse(c.\"type\" IN
('day_interval','month_interval','sec_interval'), c.\"type_scale\" -1, NULL) AS
int) AS INTERVAL_PRECISION,\n"
@@ -6079,7 +6069,6 @@ sql_update_default(Client c, mvc *sql, s
" INNER JOIN sys.\"schemas\" s ON t.\"schema_id\" = s.\"id\"\n"
" LEFT OUTER JOIN sys.\"comments\" cm ON c.\"id\" = cm.\"id\"\n"
" ORDER BY s.\"name\", t.\"name\", c.\"number\";\n"
- "\n"
"GRANT SELECT ON TABLE INFORMATION_SCHEMA.COLUMNS TO PUBLIC
WITH GRANT OPTION;\n"
"\n"
"update sys._tables set system = true where system <> true\n"
diff --git
a/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
---
a/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
+++
b/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
@@ -447,3 +447,155 @@ commit;
Running database upgrade commands:
alter table sys.function_languages set read only;
+Running database upgrade commands:
+CREATE SCHEMA INFORMATION_SCHEMA;
+COMMENT ON SCHEMA INFORMATION_SCHEMA IS 'ISO/IEC 9075-11 SQL/Schemata';
+update sys.schemas set system = true where name = 'information_schema';
+CREATE VIEW INFORMATION_SCHEMA.CHARACTER_SETS AS SELECT
+ cast(NULL AS varchar(1)) AS CHARACTER_SET_CATALOG,
+ cast(NULL AS varchar(1)) AS CHARACTER_SET_SCHEMA,
+ cast('UTF-8' AS varchar(16)) AS CHARACTER_SET_NAME,
+ cast('ISO/IEC 10646:2021' AS varchar(20)) AS CHARACTER_REPERTOIRE,
+ cast('UTF-8' AS varchar(16)) AS FORM_OF_USE,
+ cast(NULL AS varchar(1)) AS DEFAULT_COLLATE_CATALOG,
+ cast(NULL AS varchar(1)) AS DEFAULT_COLLATE_SCHEMA,
+ cast(NULL AS varchar(1)) AS DEFAULT_COLLATE_NAME;
+GRANT SELECT ON TABLE INFORMATION_SCHEMA.CHARACTER_SETS TO PUBLIC WITH GRANT
OPTION;
+CREATE VIEW INFORMATION_SCHEMA.SCHEMATA AS SELECT
+ cast(NULL AS varchar(1)) AS CATALOG_NAME,
+ s."name" AS SCHEMA_NAME,
+ a."name" AS SCHEMA_OWNER,
+ cast(NULL AS varchar(1)) AS DEFAULT_CHARACTER_SET_CATALOG,
+ cast(NULL AS varchar(1)) AS DEFAULT_CHARACTER_SET_SCHEMA,
+ cast('UTF-8' AS varchar(16)) AS DEFAULT_CHARACTER_SET_NAME,
+ cast(NULL AS varchar(1)) AS SQL_PATH,
+ -- MonetDB column extensions
+ s."id" AS schema_id,
+ s."system" AS is_system,
+ cm."remark" AS comments
+ FROM sys."schemas" s
+ INNER JOIN sys."auths" a ON s."owner" = a."id"
+ LEFT OUTER JOIN sys."comments" cm ON s."id" = cm."id"
+ ORDER BY s."name";
+GRANT SELECT ON TABLE INFORMATION_SCHEMA.SCHEMATA TO PUBLIC WITH GRANT OPTION;
+CREATE VIEW INFORMATION_SCHEMA.TABLES AS SELECT
+ cast(NULL AS varchar(1)) AS TABLE_CATALOG,
+ s."name" AS TABLE_SCHEMA,
+ t."name" AS TABLE_NAME,
+ tt."table_type_name" AS TABLE_TYPE,
+ cast(NULL AS varchar(1)) AS SELF_REFERENCING_COLUMN_NAME,
+ cast(NULL AS varchar(1)) AS REFERENCE_GENERATION,
+ cast(NULL AS varchar(1)) AS USER_DEFINED_TYPE_CATALOG,
+ cast(NULL AS varchar(1)) AS USER_DEFINED_TYPE_SCHEMA,
+ cast(NULL AS varchar(1)) AS USER_DEFINED_TYPE_NAME,
+ cast(sys.ifthenelse((t."type" IN (0, 3, 7, 20, 30) AND t."access" IN (0,
2)), 'YES', 'NO') AS varchar(3)) AS IS_INSERTABLE_INTO,
+ cast('NO' AS varchar(3)) AS IS_TYPED,
+ cast((CASE t."commit_action" WHEN 1 THEN 'DELETE' WHEN 2 THEN 'PRESERVE'
WHEN 3 THEN 'DROP' ELSE NULL END) AS varchar(10)) AS COMMIT_ACTION,
+ -- MonetDB column extensions
+ t."schema_id" AS schema_id,
+ t."id" AS table_id,
+ t."type" AS table_type_id,
+ st."count" AS row_count,
+ t."system" AS is_system,
+ sys.ifthenelse(t."type" IN (1, 11), TRUE, FALSE) AS is_view,
+ t."query" AS query_def,
+ cm."remark" AS comments
+ FROM sys."tables" t
+ INNER JOIN sys."schemas" s ON t."schema_id" = s."id"
+ INNER JOIN sys."table_types" tt ON t."type" = tt."table_type_id"
+ LEFT OUTER JOIN sys."comments" cm ON t."id" = cm."id"
+ LEFT OUTER JOIN (SELECT DISTINCT "schema", "table", "count" FROM
sys."statistics"()) st ON (s."name" = st."schema" AND t."name" = st."table")
+ ORDER BY s."name", t."name";
+GRANT SELECT ON TABLE INFORMATION_SCHEMA.TABLES TO PUBLIC WITH GRANT OPTION;
+CREATE VIEW INFORMATION_SCHEMA.VIEWS AS SELECT
+ cast(NULL AS varchar(1)) AS TABLE_CATALOG,
+ s."name" AS TABLE_SCHEMA,
+ t."name" AS TABLE_NAME,
+ t."query" AS VIEW_DEFINITION,
+ cast('NONE' AS varchar(10)) AS CHECK_OPTION,
+ cast('NO' AS varchar(3)) AS IS_UPDATABLE,
+ cast('NO' AS varchar(3)) AS INSERTABLE_INTO,
+ cast('NO' AS varchar(3)) AS IS_TRIGGER_UPDATABLE,
+ cast('NO' AS varchar(3)) AS IS_TRIGGER_DELETABLE,
+ cast('NO' AS varchar(3)) AS IS_TRIGGER_INSERTABLE_INTO,
+ -- MonetDB column extensions
+ t."schema_id" AS schema_id,
+ t."id" AS table_id,
+ cast(sys.ifthenelse(t."system", t."type" + 10 , t."type") AS smallint) AS
table_type_id,
+ t."system" AS is_system,
+ cm."remark" AS comments
+ FROM sys."_tables" t
+ INNER JOIN sys."schemas" s ON t."schema_id" = s."id"
+ LEFT OUTER JOIN sys."comments" cm ON t."id" = cm."id"
+ WHERE t."type" = 1
+ ORDER BY s."name", t."name";
+GRANT SELECT ON TABLE INFORMATION_SCHEMA.VIEWS TO PUBLIC WITH GRANT OPTION;
+CREATE VIEW INFORMATION_SCHEMA.COLUMNS AS SELECT
+ cast(NULL AS varchar(1)) AS TABLE_CATALOG,
+ s."name" AS TABLE_SCHEMA,
+ t."name" AS TABLE_NAME,
+ c."name" AS COLUMN_NAME,
+ cast(c."number" +1 AS int) AS ORDINAL_POSITION,
+ c."default" AS COLUMN_DEFAULT,
+ cast(sys.ifthenelse(c."null", 'YES', 'NO') AS varchar(3)) AS IS_NULLABLE,
+ c."type" AS DATA_TYPE,
+ cast(sys.ifthenelse(c."type" IN
('varchar','clob','char','json','url','xml'), c."type_digits", NULL) AS int) AS
CHARACTER_MAXIMUM_LENGTH,
+ cast(sys.ifthenelse(c."type" IN
('varchar','clob','char','json','url','xml'), c."type_digits" * 3, NULL) AS
int) AS CHARACTER_OCTET_LENGTH,
+ cast(sys.ifthenelse(c."type" IN
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
c."type_digits", NULL) AS int) AS NUMERIC_PRECISION,
+ cast(sys.ifthenelse(c."type" IN
('int','smallint','tinyint','bigint','hugeint','float','real','double','oid'),
2, sys.ifthenelse(c."type" IN ('decimal','numeric'), 10, NULL)) AS int) AS
NUMERIC_PRECISION_RADIX,
+ cast(sys.ifthenelse(c."type" IN
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
c."type_scale", NULL) AS int) AS NUMERIC_SCALE,
+ cast(sys.ifthenelse(c."type" IN
('date','timestamp','timestamptz','time','timetz'), c."type_scale" -1, NULL) AS
int) AS DATETIME_PRECISION,
+ cast(CASE c."type" WHEN 'day_interval' THEN 'interval day' WHEN
'month_interval' THEN 'interval month' WHEN 'sec_interval' THEN 'interval
second' ELSE NULL END AS varchar(40)) AS INTERVAL_TYPE,
+ cast(sys.ifthenelse(c."type" IN
('day_interval','month_interval','sec_interval'), c."type_scale" -1, NULL) AS
int) AS INTERVAL_PRECISION,
+ cast(NULL AS varchar(1)) AS CHARACTER_SET_CATALOG,
+ cast(NULL AS varchar(1)) AS CHARACTER_SET_SCHEMA,
+ cast(sys.ifthenelse(c."type" IN
('varchar','clob','char','json','url','xml'), 'UTF-8', NULL) AS varchar(16)) AS
CHARACTER_SET_NAME,
+ cast(NULL AS varchar(1)) AS COLLATION_CATALOG,
+ cast(NULL AS varchar(1)) AS COLLATION_SCHEMA,
+ cast(NULL AS varchar(1)) AS COLLATION_NAME,
+ cast(NULL AS varchar(1)) AS DOMAIN_CATALOG,
+ cast(NULL AS varchar(1)) AS DOMAIN_SCHEMA,
+ cast(NULL AS varchar(1)) AS DOMAIN_NAME,
+ cast(NULL AS varchar(1)) AS UDT_CATALOG,
+ cast(NULL AS varchar(1)) AS UDT_SCHEMA,
+ cast(NULL AS varchar(1)) AS UDT_NAME,
+ cast(NULL AS varchar(1)) AS SCOPE_CATALOG,
+ cast(NULL AS varchar(1)) AS SCOPE_SCHEMA,
+ cast(NULL AS varchar(1)) AS SCOPE_NAME,
+ cast(NULL AS int) AS MAXIMUM_CARDINALITY,
+ cast(NULL AS varchar(1)) AS DTD_IDENTIFIER,
+ cast('NO' AS varchar(3)) AS IS_SELF_REFERENCING,
+ cast(CASE WHEN c."default" LIKE 'next value for %' THEN 'YES' ELSE 'NO' END
AS varchar(3)) AS IS_IDENTITY,
+ cast(NULL AS varchar(10)) AS IDENTITY_GENERATION,
+ cast(NULL AS int) AS IDENTITY_START,
+ cast(NULL AS int) AS IDENTITY_INCREMENT,
+ cast(NULL AS int) AS IDENTITY_MAXIMUM,
+ cast(NULL AS int) AS IDENTITY_MINIMUM,
+ cast(NULL AS varchar(3)) AS IDENTITY_CYCLE,
+ cast('NO' AS varchar(3)) AS IS_GENERATED,
+ cast(NULL AS varchar(1)) AS GENERATION_EXPRESSION,
+ cast('NO' AS varchar(3)) AS IS_SYSTEM_TIME_PERIOD_START,
+ cast('NO' AS varchar(3)) AS IS_SYSTEM_TIME_PERIOD_END,
+ cast('NO' AS varchar(3)) AS SYSTEM_TIME_PERIOD_TIMESTAMP_GENERATION,
+ cast(sys.ifthenelse(t."type" IN (0,3,7,20,30), 'YES', 'NO') AS varchar(3))
AS IS_UPDATABLE,
+ cast(NULL AS varchar(1)) AS DECLARED_DATA_TYPE,
+ cast(NULL AS int) AS DECLARED_NUMERIC_PRECISION,
+ cast(NULL AS int) AS DECLARED_NUMERIC_SCALE,
+ -- MonetDB column extensions
+ t."schema_id" AS schema_id,
+ c."table_id" AS table_id,
+ c."id" AS column_id,
+ t."system" AS is_system,
+ cm."remark" AS comments
+ FROM sys."columns" c
+ INNER JOIN sys."tables" t ON c."table_id" = t."id"
+ INNER JOIN sys."schemas" s ON t."schema_id" = s."id"
+ LEFT OUTER JOIN sys."comments" cm ON c."id" = cm."id"
+ ORDER BY s."name", t."name", c."number";
+GRANT SELECT ON TABLE INFORMATION_SCHEMA.COLUMNS TO PUBLIC WITH GRANT OPTION;
+
+update sys._tables set system = true where system <> true
+ and schema_id = (select s.id from sys.schemas s where s.name =
'information_schema')
+ and name in ('character_sets','schemata','tables','views','columns');
+commit;
+
diff --git a/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
b/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
--- a/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
+++ b/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
@@ -447,3 +447,155 @@ commit;
Running database upgrade commands:
alter table sys.function_languages set read only;
+Running database upgrade commands:
+CREATE SCHEMA INFORMATION_SCHEMA;
+COMMENT ON SCHEMA INFORMATION_SCHEMA IS 'ISO/IEC 9075-11 SQL/Schemata';
+update sys.schemas set system = true where name = 'information_schema';
+CREATE VIEW INFORMATION_SCHEMA.CHARACTER_SETS AS SELECT
+ cast(NULL AS varchar(1)) AS CHARACTER_SET_CATALOG,
+ cast(NULL AS varchar(1)) AS CHARACTER_SET_SCHEMA,
+ cast('UTF-8' AS varchar(16)) AS CHARACTER_SET_NAME,
+ cast('ISO/IEC 10646:2021' AS varchar(20)) AS CHARACTER_REPERTOIRE,
+ cast('UTF-8' AS varchar(16)) AS FORM_OF_USE,
+ cast(NULL AS varchar(1)) AS DEFAULT_COLLATE_CATALOG,
+ cast(NULL AS varchar(1)) AS DEFAULT_COLLATE_SCHEMA,
+ cast(NULL AS varchar(1)) AS DEFAULT_COLLATE_NAME;
+GRANT SELECT ON TABLE INFORMATION_SCHEMA.CHARACTER_SETS TO PUBLIC WITH GRANT
OPTION;
+CREATE VIEW INFORMATION_SCHEMA.SCHEMATA AS SELECT
+ cast(NULL AS varchar(1)) AS CATALOG_NAME,
+ s."name" AS SCHEMA_NAME,
+ a."name" AS SCHEMA_OWNER,
+ cast(NULL AS varchar(1)) AS DEFAULT_CHARACTER_SET_CATALOG,
+ cast(NULL AS varchar(1)) AS DEFAULT_CHARACTER_SET_SCHEMA,
+ cast('UTF-8' AS varchar(16)) AS DEFAULT_CHARACTER_SET_NAME,
+ cast(NULL AS varchar(1)) AS SQL_PATH,
+ -- MonetDB column extensions
+ s."id" AS schema_id,
+ s."system" AS is_system,
+ cm."remark" AS comments
+ FROM sys."schemas" s
+ INNER JOIN sys."auths" a ON s."owner" = a."id"
+ LEFT OUTER JOIN sys."comments" cm ON s."id" = cm."id"
+ ORDER BY s."name";
+GRANT SELECT ON TABLE INFORMATION_SCHEMA.SCHEMATA TO PUBLIC WITH GRANT OPTION;
+CREATE VIEW INFORMATION_SCHEMA.TABLES AS SELECT
+ cast(NULL AS varchar(1)) AS TABLE_CATALOG,
+ s."name" AS TABLE_SCHEMA,
+ t."name" AS TABLE_NAME,
+ tt."table_type_name" AS TABLE_TYPE,
+ cast(NULL AS varchar(1)) AS SELF_REFERENCING_COLUMN_NAME,
+ cast(NULL AS varchar(1)) AS REFERENCE_GENERATION,
+ cast(NULL AS varchar(1)) AS USER_DEFINED_TYPE_CATALOG,
+ cast(NULL AS varchar(1)) AS USER_DEFINED_TYPE_SCHEMA,
+ cast(NULL AS varchar(1)) AS USER_DEFINED_TYPE_NAME,
+ cast(sys.ifthenelse((t."type" IN (0, 3, 7, 20, 30) AND t."access" IN (0,
2)), 'YES', 'NO') AS varchar(3)) AS IS_INSERTABLE_INTO,
+ cast('NO' AS varchar(3)) AS IS_TYPED,
+ cast((CASE t."commit_action" WHEN 1 THEN 'DELETE' WHEN 2 THEN 'PRESERVE'
WHEN 3 THEN 'DROP' ELSE NULL END) AS varchar(10)) AS COMMIT_ACTION,
+ -- MonetDB column extensions
+ t."schema_id" AS schema_id,
+ t."id" AS table_id,
+ t."type" AS table_type_id,
+ st."count" AS row_count,
+ t."system" AS is_system,
+ sys.ifthenelse(t."type" IN (1, 11), TRUE, FALSE) AS is_view,
_______________________________________________
checkin-list mailing list -- checkin-list@monetdb.org
To unsubscribe send an email to checkin-list-le...@monetdb.org
