Changeset: eee140ec9b94 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/eee140ec9b94
Modified Files:
sql/ChangeLog
sql/backends/monet5/sql_upgrades.c
sql/scripts/91_information_schema.sql
sql/test/Dependencies/Tests/dependency_owner_schema_3.test
sql/test/emptydb/Tests/check.stable.out
sql/test/emptydb/Tests/check.stable.out.32bit
sql/test/emptydb/Tests/check.stable.out.int128
Branch: default
Log Message:
Add system view information_schema.referential_constraints
diffs (truncated from 435 to 300 lines):
diff --git a/sql/ChangeLog b/sql/ChangeLog
--- a/sql/ChangeLog
+++ b/sql/ChangeLog
@@ -10,14 +10,16 @@
information_schema.character_sets
information_schema.check_constraints
information_schema.table_constraints
+ information_schema.referential_constraints
information_schema.sequences
Most views have been extended (after the standard columns) with MonetDB
specific information columns such as schema_id, table_id, column_id, etc.
- This simplifies joins with any sys.* tables/views when needed.
+ This simplifies filtering and joins with system tables/views in sys schema
+ when needed.
Note: MonetDB does NOT support catalog qualifiers in object names, so all the
- _CATALOG columns in these information_schema views will allways contain NULL.
+ CATALOG columns in these information_schema views will allways contain NULL.
* Mon Aug 21 2023 Niels Nes <[email protected]>
- Added support for generated column syntax:
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
@@ -6204,6 +6204,34 @@ sql_update_default(Client c, mvc *sql, s
" ORDER BY s.\"name\", t.\"name\", k.\"name\";\n"
"GRANT SELECT ON TABLE INFORMATION_SCHEMA.TABLE_CONSTRAINTS TO
PUBLIC WITH GRANT OPTION;\n"
+ "CREATE VIEW INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS
SELECT\n"
+ " cast(NULL AS varchar(1)) AS CONSTRAINT_CATALOG,\n"
+ " s.\"name\" AS CONSTRAINT_SCHEMA,\n"
+ " fk.\"name\" AS CONSTRAINT_NAME,\n"
+ " cast(NULL AS varchar(1)) AS UNIQUE_CONSTRAINT_CATALOG,\n"
+ " uks.\"name\" AS UNIQUE_CONSTRAINT_SCHEMA,\n"
+ " uk.\"name\" AS UNIQUE_CONSTRAINT_NAME,\n"
+ " cast('FULL' AS varchar(7)) AS MATCH_OPTION,\n"
+ " fk.\"update_action\" AS UPDATE_RULE,\n"
+ " fk.\"delete_action\" AS DELETE_RULE,\n"
+ " -- MonetDB column extensions\n"
+ " t.\"schema_id\" AS fk_schema_id,\n"
+ " t.\"id\" AS fk_table_id,\n"
+ " t.\"name\" AS fk_table_name,\n"
+ " fk.\"id\" AS fk_key_id,\n"
+ " ukt.\"schema_id\" AS uc_schema_id,\n"
+ " uk.\"table_id\" AS uc_table_id,\n"
+ " ukt.\"name\" AS uc_table_name,\n"
+ " uk.\"id\" AS uc_key_id\n"
+ " FROM sys.\"fkeys\" fk\n"
+ " INNER JOIN sys.\"tables\" t ON t.\"id\" = fk.\"table_id\"\n"
+ " INNER JOIN sys.\"schemas\" s ON s.\"id\" = t.\"schema_id\"\n"
+ " LEFT OUTER JOIN sys.\"keys\" uk ON uk.\"id\" = fk.\"rkey\"\n"
+ " LEFT OUTER JOIN sys.\"tables\" ukt ON ukt.\"id\" =
uk.\"table_id\"\n"
+ " LEFT OUTER JOIN sys.\"schemas\" uks ON uks.\"id\" =
ukt.\"schema_id\"\n"
+ " ORDER BY s.\"name\", t.\"name\", fk.\"name\";\n"
+ "GRANT SELECT ON TABLE
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS TO PUBLIC WITH GRANT OPTION;\n"
+
"CREATE VIEW INFORMATION_SCHEMA.SEQUENCES AS SELECT\n"
" cast(NULL AS varchar(1)) AS SEQUENCE_CATALOG,\n"
" s.\"name\" AS SEQUENCE_SCHEMA,\n"
@@ -6234,7 +6262,7 @@ sql_update_default(Client c, mvc *sql, s
"\n"
"update sys._tables set system = true where system <> true\n"
" and schema_id = (select s.id from sys.schemas s where s.name
= 'information_schema')\n"
- " and name in
('character_sets','check_constraints','columns','schemata','sequences','table_constraints','tables','views');\n";
+ " and name in
('character_sets','check_constraints','columns','schemata','sequences','referential_constraints','table_constraints','tables','views');\n";
printf("Running database upgrade commands:\n%s\n", cmds);
fflush(stdout);
err = SQLstatementIntern(c, cmds, "update", true, false, NULL);
diff --git a/sql/scripts/91_information_schema.sql
b/sql/scripts/91_information_schema.sql
--- a/sql/scripts/91_information_schema.sql
+++ b/sql/scripts/91_information_schema.sql
@@ -9,11 +9,12 @@
-- ISO/IEC SQL/Schemata (as defined in ISO_9075_11_Schemata_2011_E.pdf)
-- defines INFORMATION_SCHEMA schema and standardised views
--
--- NOTE 1: The views have been extended with MonetDB specific information
--- columns such as schema_id, table_id, column_id, is_system, etc.
--- This eases joins with any sys.* tables/views.
--- NOTE 2: MonetDB does NOT support catalog qualifiers in object names, so
+-- NOTE 1: MonetDB does NOT support catalog qualifiers in object names, so
-- all the *CATALOG* columns in next views will allways contain NULL.
+-- NOTE 2: Most views have been extended (after the standard columns) with
+-- MonetDB specific information columns such as schema_id, table_id,
+-- column_id, is_system, etc. This simplifies filtering and joins with
+-- system tables/views in sys or tmp schemas when needed.
CREATE SCHEMA INFORMATION_SCHEMA;
COMMENT ON SCHEMA INFORMATION_SCHEMA IS 'ISO/IEC 9075-11 SQL/Schemata';
@@ -223,6 +224,38 @@ CREATE VIEW INFORMATION_SCHEMA.TABLE_CON
GRANT SELECT ON TABLE INFORMATION_SCHEMA.TABLE_CONSTRAINTS TO PUBLIC WITH
GRANT OPTION;
+-- The view REFERENTIAL_CONSTRAINTS contains all referential (foreign key)
constraints in the current database.
+-- Only those constraints are shown for which the current user has write
access to the referencing table
+-- (by way of being the owner or having some privilege other than SELECT).
+CREATE VIEW INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS SELECT
+ cast(NULL AS varchar(1)) AS CONSTRAINT_CATALOG,
+ s."name" AS CONSTRAINT_SCHEMA,
+ fk."name" AS CONSTRAINT_NAME,
+ cast(NULL AS varchar(1)) AS UNIQUE_CONSTRAINT_CATALOG,
+ uks."name" AS UNIQUE_CONSTRAINT_SCHEMA,
+ uk."name" AS UNIQUE_CONSTRAINT_NAME,
+ cast('FULL' AS varchar(7)) AS MATCH_OPTION,
+ fk."update_action" AS UPDATE_RULE,
+ fk."delete_action" AS DELETE_RULE,
+ -- MonetDB column extensions
+ t."schema_id" AS fk_schema_id,
+ t."id" AS fk_table_id,
+ t."name" AS fk_table_name,
+ fk."id" AS fk_key_id,
+ ukt."schema_id" AS uc_schema_id,
+ uk."table_id" AS uc_table_id,
+ ukt."name" AS uc_table_name,
+ uk."id" AS uc_key_id
+ FROM sys."fkeys" fk
+ INNER JOIN sys."tables" t ON t."id" = fk."table_id"
+ INNER JOIN sys."schemas" s ON s."id" = t."schema_id"
+ LEFT OUTER JOIN sys."keys" uk ON uk."id" = fk."rkey"
+ LEFT OUTER JOIN sys."tables" ukt ON ukt."id" = uk."table_id"
+ LEFT OUTER JOIN sys."schemas" uks ON uks."id" = ukt."schema_id"
+ ORDER BY s."name", t."name", fk."name";
+
+GRANT SELECT ON TABLE INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS TO PUBLIC
WITH GRANT OPTION;
+
-- The view SEQUENCES contains all sequences defined in the current database.
-- Only those sequences are shown that the current user has access to
-- (by way of being the owner or having some privilege).
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
@@ -311,6 +311,9 @@ DEP_VIEW
fkey_actions
fkeys
DEP_VIEW
+fkeys
+referential_constraints
+DEP_VIEW
fully_qualified_functions
describe_comments
DEP_VIEW
@@ -435,6 +438,9 @@ keys
ids
DEP_VIEW
keys
+referential_constraints
+DEP_VIEW
+keys
table_constraints
DEP_VIEW
objects
@@ -567,6 +573,9 @@ schemas
ids
DEP_VIEW
schemas
+referential_constraints
+DEP_VIEW
+schemas
schemata
DEP_VIEW
schemas
@@ -705,6 +714,9 @@ tables
ids
DEP_VIEW
tables
+referential_constraints
+DEP_VIEW
+tables
statistics
DEP_VIEW
tables
@@ -838,7 +850,7 @@ DEP_FUNC
query TTT rowsort
SELECT distinct 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
----
-1677 values hashing to f5071174850041be0fdcc4ca508a68f3
+1698 values hashing to 805e3895a1ec9ab7f2ecd4a1520099b4
query TTT rowsort
SELECT c.name, k.name, 'DEP_KEY' from sys.columns as c, sys.objects as kc,
sys.keys as k where kc."name" = c.name AND kc.id = k.id AND k.table_id =
c.table_id AND k.rkey = -1 order by c.name, k.name
diff --git a/sql/test/emptydb/Tests/check.stable.out
b/sql/test/emptydb/Tests/check.stable.out
--- a/sql/test/emptydb/Tests/check.stable.out
+++ b/sql/test/emptydb/Tests/check.stable.out
@@ -448,6 +448,7 @@ select 'null in fkeys.delete_action', de
[ "sys._tables", "information_schema", "character_sets", "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;", "VIEW", true, "COMMIT", "WRITABLE", NULL
]
[ "sys._tables", "information_schema", "check_constraints", "create
view information_schema.check_constraints as select cast(null as varchar(1)) as
constraint_catalog, cast(null as varchar(1024)) as constraint_schema, cast(null
as varchar(1024)) as constraint_name, cast(null as varchar(1024)) as
check_clause where 1=0;", "VIEW", true, "COMMIT", "WRITABLE", NULL
]
[ "sys._tables", "information_schema", "columns", "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, case c.\"type\" when 'day_interval' then 'interval day' when
'month_interval' then 'interval month' when 'sec_interval' then 'interval
second' else c.\"type\" end 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 (case c.\"type_digits\" when 1 then
'interval year' when 2 then 'interval year to month' when 3 then 'interval
month' else null end) when 'sec_interval' then (case c.\"type_digits\" 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' else null end) else null end as varchar(40)) as
interval_type, cast(case c.\"type\" when 'day_interval' then 0 when
'month_interval' then 0 when 'sec_interval' then
(sys.ifthenelse(c.\"type_digits\" in (7, 10, 12, 13),
sys.ifthenelse(c.\"type_scale\" > 0, c.\"type_scale\", 3), 0)) else null end 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, c
ast(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_timestam
p_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, 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\";", "VIEW", true, "COMMIT", "WRITABLE",
NULL ]
+[ "sys._tables", "information_schema", "referential_constraints",
"create view information_schema.referential_constraints as select cast(null as
varchar(1)) as constraint_catalog, s.\"name\" as constraint_schema, fk.\"name\"
as constraint_name, cast(null as varchar(1)) as unique_constraint_catalog,
uks.\"name\" as unique_constraint_schema, uk.\"name\" as
unique_constraint_name, cast('FULL' as varchar(7)) as match_option,
fk.\"update_action\" as update_rule, fk.\"delete_action\" as delete_rule,
t.\"schema_id\" as fk_schema_id, t.\"id\" as fk_table_id, t.\"name\" as
fk_table_name, fk.\"id\" as fk_key_id, ukt.\"schema_id\" as uc_schema_id,
uk.\"table_id\" as uc_table_id, ukt.\"name\" as uc_table_name, uk.\"id\" as
uc_key_id from sys.\"fkeys\" fk inner join sys.\"tables\" t on t.\"id\" =
fk.\"table_id\" inner join sys.\"schemas\" s on s.\"id\" = t.\"schema_id\" left
outer join sys.\"keys\" uk on uk.\"id\" = fk.\"rkey\" left outer join
sys.\"tables\" ukt on ukt.\"id\" = uk.\"table_id\" l
eft outer join sys.\"schemas\" uks on uks.\"id\" = ukt.\"schema_id\" order by
s.\"name\", t.\"name\", fk.\"name\";", "VIEW", true, "COMMIT",
"WRITABLE", NULL ]
[ "sys._tables", "information_schema", "schemata", "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, 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\";",
"VIEW", true, "COMMIT", "WRITABLE", NULL ]
[ "sys._tables", "information_schema", "sequences", "create view
information_schema.sequences as select cast(null as varchar(1)) as
sequence_catalog, s.\"name\" as sequence_schema, sq.\"name\" as sequence_name,
cast('bigint' as varchar(16)) as data_type, cast(64 as smallint) as
numeric_precision, cast(2 as smallint) as numeric_precision_radix, cast(0 as
smallint) as numeric_scale, sq.\"start\" as start_value, sq.\"minvalue\" as
minimum_value, sq.\"maxvalue\" as maximum_value, sq.\"increment\" as increment,
cast(sys.ifthenelse(sq.\"cycle\", 'YES', 'NO') as varchar(3)) as cycle_option,
cast(null as varchar(16)) as declared_data_type, cast(null as smallint) as
declared_numeric_precision, cast(null as smallint) as declared_numeric_scale,
sq.\"schema_id\" as schema_id, sq.\"id\" as sequence_id,
get_value_for(s.\"name\", sq.\"name\") as current_value, sq.\"cacheinc\" as
cacheinc, cm.\"remark\" as comments from sys.\"sequences\" sq inner join
sys.\"schemas\" s on sq.\"schema_id\" = s.\"id\
" left outer join sys.\"comments\" cm on sq.\"id\" = cm.\"id\" order by
s.\"name\", sq.\"name\";", "VIEW", true, "COMMIT", "WRITABLE",
NULL ]
[ "sys._tables", "information_schema", "table_constraints", "create
view information_schema.table_constraints as select cast(null as varchar(1)) as
constraint_catalog, s.\"name\" as constraint_schema, k.\"name\" as
constraint_name, cast(null as varchar(1)) as table_catalog, s.\"name\" as
table_schema, t.\"name\" as table_name, cast(case k.\"type\" when 0 then
'PRIMARY KEY' when 1 then 'UNIQUE' when 2 then 'FOREIGN KEY' else null end as
varchar(16)) as constraint_type, cast('NO' as varchar(3)) as is_deferrable,
cast('NO' as varchar(3)) as initially_deferred, cast('YES' as varchar(3)) as
enforced, t.\"schema_id\" as schema_id, t.\"id\" as table_id, k.\"id\" as
key_id, k.\"type\" as key_type, t.\"system\" as is_system from (select
sk.\"id\", sk.\"table_id\", sk.\"name\", sk.\"type\" from sys.\"keys\" sk union
all select tk.\"id\", tk.\"table_id\", tk.\"name\", tk.\"type\" from
tmp.\"keys\" tk) k inner join (select st.\"id\", st.\"schema_id\", st.\"name\",
st.\"system\" from sys.\"_tabl
es\" st union all select tt.\"id\", tt.\"schema_id\", tt.\"name\",
tt.\"system\" from tmp.\"_tables\" tt) t on k.\"table_id\" = t.\"id\" inner
join sys.\"schemas\" s on t.\"schema_id\" = s.\"id\" order by s.\"name\",
t.\"name\", k.\"name\";", "VIEW", true, "COMMIT", "WRITABLE",
NULL ]
@@ -655,6 +656,23 @@ select 'null in fkeys.delete_action', de
[ "sys._columns", "information_schema", "columns", "column_id",
"int", 32, 0, NULL, true, 52, NULL, NULL ]
[ "sys._columns", "information_schema", "columns", "is_system",
"boolean", 1, 0, NULL, true, 53, NULL, NULL ]
[ "sys._columns", "information_schema", "columns", "comments",
"varchar", 65000, 0, NULL, true, 54, NULL, NULL ]
+[ "sys._columns", "information_schema", "referential_constraints",
"constraint_catalog", "varchar", 1, 0, NULL, true, 0,
NULL, NULL ]
+[ "sys._columns", "information_schema", "referential_constraints",
"constraint_schema", "varchar", 1024, 0, NULL, true, 1,
NULL, NULL ]
+[ "sys._columns", "information_schema", "referential_constraints",
"constraint_name", "varchar", 1024, 0, NULL, true, 2,
NULL, NULL ]
+[ "sys._columns", "information_schema", "referential_constraints",
"unique_constraint_catalog", "varchar", 1, 0, NULL, true,
3, NULL, NULL ]
+[ "sys._columns", "information_schema", "referential_constraints",
"unique_constraint_schema", "varchar", 1024, 0, NULL, true,
4, NULL, NULL ]
+[ "sys._columns", "information_schema", "referential_constraints",
"unique_constraint_name", "varchar", 1024, 0, NULL, true,
5, NULL, NULL ]
+[ "sys._columns", "information_schema", "referential_constraints",
"match_option", "varchar", 7, 0, NULL, true, 6, NULL,
NULL ]
+[ "sys._columns", "information_schema", "referential_constraints",
"update_rule", "varchar", 15, 0, NULL, true, 7, NULL,
NULL ]
+[ "sys._columns", "information_schema", "referential_constraints",
"delete_rule", "varchar", 15, 0, NULL, true, 8, NULL,
NULL ]
+[ "sys._columns", "information_schema", "referential_constraints",
"fk_schema_id", "int", 32, 0, NULL, true, 9, NULL, NULL
]
+[ "sys._columns", "information_schema", "referential_constraints",
"fk_table_id", "int", 32, 0, NULL, true, 10, NULL, NULL
]
+[ "sys._columns", "information_schema", "referential_constraints",
"fk_table_name", "varchar", 1024, 0, NULL, true, 11,
NULL, NULL ]
+[ "sys._columns", "information_schema", "referential_constraints",
"fk_key_id", "int", 32, 0, NULL, true, 12, NULL, NULL
]
+[ "sys._columns", "information_schema", "referential_constraints",
"uc_schema_id", "int", 32, 0, NULL, true, 13, NULL, NULL
]
+[ "sys._columns", "information_schema", "referential_constraints",
"uc_table_id", "int", 32, 0, NULL, true, 14, NULL, NULL
]
+[ "sys._columns", "information_schema", "referential_constraints",
"uc_table_name", "varchar", 1024, 0, NULL, true, 15,
NULL, NULL ]
+[ "sys._columns", "information_schema", "referential_constraints",
"uc_key_id", "int", 32, 0, NULL, true, 16, NULL, NULL
]
[ "sys._columns", "information_schema", "schemata", "catalog_name",
"varchar", 1, 0, NULL, true, 0, NULL, NULL ]
[ "sys._columns", "information_schema", "schemata", "schema_name",
"varchar", 1024, 0, NULL, true, 1, NULL, NULL ]
[ "sys._columns", "information_schema", "schemata", "schema_owner",
"varchar", 1024, 0, NULL, true, 2, NULL, NULL ]
@@ -3104,6 +3122,10 @@ select 'null in fkeys.delete_action', de
[ "table used by view", "sys", "comments", "information_schema",
"columns", "VIEW" ]
[ "table used by view", "sys", "schemas", "information_schema",
"columns", "VIEW" ]
[ "table used by view", "sys", "tables", "information_schema",
"columns", "VIEW" ]
+[ "table used by view", "sys", "fkeys", "information_schema",
"referential_constraints", "VIEW" ]
+[ "table used by view", "sys", "keys", "information_schema",
"referential_constraints", "VIEW" ]
+[ "table used by view", "sys", "schemas", "information_schema",
"referential_constraints", "VIEW" ]
+[ "table used by view", "sys", "tables", "information_schema",
"referential_constraints", "VIEW" ]
[ "table used by view", "sys", "auths", "information_schema",
"schemata", "VIEW" ]
[ "table used by view", "sys", "comments", "information_schema",
"schemata", "VIEW" ]
[ "table used by view", "sys", "schemas", "information_schema",
"schemata", "VIEW" ]
@@ -3367,6 +3389,20 @@ select 'null in fkeys.delete_action', de
[ "column used by view", "sys", "tables", "schema_id",
"information_schema", "columns", "VIEW" ]
[ "column used by view", "sys", "tables", "system",
"information_schema", "columns", "VIEW" ]
[ "column used by view", "sys", "tables", "type",
"information_schema", "columns", "VIEW" ]
+[ "column used by view", "sys", "fkeys", "delete_action",
"information_schema", "referential_constraints", "VIEW" ]
+[ "column used by view", "sys", "fkeys", "id",
"information_schema", "referential_constraints", "VIEW" ]
+[ "column used by view", "sys", "fkeys", "name",
"information_schema", "referential_constraints", "VIEW" ]
+[ "column used by view", "sys", "fkeys", "rkey",
"information_schema", "referential_constraints", "VIEW" ]
+[ "column used by view", "sys", "fkeys", "table_id",
"information_schema", "referential_constraints", "VIEW" ]
+[ "column used by view", "sys", "fkeys", "update_action",
"information_schema", "referential_constraints", "VIEW" ]
+[ "column used by view", "sys", "keys", "id", "information_schema",
"referential_constraints", "VIEW" ]
+[ "column used by view", "sys", "keys", "name", "information_schema",
"referential_constraints", "VIEW" ]
+[ "column used by view", "sys", "keys", "table_id",
"information_schema", "referential_constraints", "VIEW" ]
+[ "column used by view", "sys", "schemas", "id",
"information_schema", "referential_constraints", "VIEW" ]
+[ "column used by view", "sys", "schemas", "name",
"information_schema", "referential_constraints", "VIEW" ]
+[ "column used by view", "sys", "tables", "id",
"information_schema", "referential_constraints", "VIEW" ]
+[ "column used by view", "sys", "tables", "name",
"information_schema", "referential_constraints", "VIEW" ]
+[ "column used by view", "sys", "tables", "schema_id",
"information_schema", "referential_constraints", "VIEW" ]
[ "column used by view", "sys", "auths", "id",
"information_schema", "schemata", "VIEW" ]
[ "column used by view", "sys", "auths", "name",
"information_schema", "schemata", "VIEW" ]
[ "column used by view", "sys", "comments", "id",
"information_schema", "schemata", "VIEW" ]
@@ -4403,8 +4439,8 @@ select 'null in fkeys.delete_action', de
[ "grant on table", "auths", "public", "SELECT", NULL,
0 ]
[ "grant on table", "character_sets", "public", "SELECT",
"monetdb", 1 ]
[ "grant on table", "check_constraints", "public", "SELECT",
"monetdb", 1 ]
+[ "grant on table", "columns", "public", "SELECT",
"monetdb", 1 ]
[ "grant on table", "columns", "public", "SELECT", NULL,
0 ]
-[ "grant on table", "columns", "public", "SELECT",
"monetdb", 1 ]
[ "grant on table", "comments", "public", "SELECT", NULL,
0 ]
[ "grant on table", "compinfo", "public", "SELECT",
"monetdb", 0 ]
[ "grant on table", "db_user_info", "monetdb", "SELECT", NULL,
0 ]
@@ -4475,6 +4511,7 @@ select 'null in fkeys.delete_action', de
[ "grant on table", "privileges", "public", "SELECT", NULL,
0 ]
[ "grant on table", "queue", "public", "SELECT",
"monetdb", 0 ]
[ "grant on table", "range_partitions", "public", "SELECT",
NULL, 0 ]
+[ "grant on table", "referential_constraints", "public",
"SELECT", "monetdb", 1 ]
[ "grant on table", "roles", "public", "SELECT",
"monetdb", 0 ]
[ "grant on table", "schemas", "public", "SELECT", NULL,
0 ]
[ "grant on table", "schemata", "public", "SELECT",
"monetdb", 1 ]
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
@@ -448,6 +448,7 @@ select 'null in fkeys.delete_action', de
[ "sys._tables", "information_schema", "character_sets", "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;", "VIEW", true, "COMMIT", "WRITABLE", NULL
]
[ "sys._tables", "information_schema", "check_constraints", "create
view information_schema.check_constraints as select cast(null as varchar(1)) as
constraint_catalog, cast(null as varchar(1024)) as constraint_schema, cast(null
as varchar(1024)) as constraint_name, cast(null as varchar(1024)) as
check_clause where 1=0;", "VIEW", true, "COMMIT", "WRITABLE", NULL
]
[ "sys._tables", "information_schema", "columns", "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, case c.\"type\" when 'day_interval' then 'interval day' when
'month_interval' then 'interval month' when 'sec_interval' then 'interval
second' else c.\"type\" end 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 (case c.\"type_digits\" when 1 then
'interval year' when 2 then 'interval year to month' when 3 then 'interval
month' else null end) when 'sec_interval' then (case c.\"type_digits\" 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' else null end) else null end as varchar(40)) as
interval_type, cast(case c.\"type\" when 'day_interval' then 0 when
'month_interval' then 0 when 'sec_interval' then
(sys.ifthenelse(c.\"type_digits\" in (7, 10, 12, 13),
sys.ifthenelse(c.\"type_scale\" > 0, c.\"type_scale\", 3), 0)) else null end 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, c
ast(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_timestam
p_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, 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\";", "VIEW", true, "COMMIT", "WRITABLE",
NULL ]
+[ "sys._tables", "information_schema", "referential_constraints",
"create view information_schema.referential_constraints as select cast(null as
varchar(1)) as constraint_catalog, s.\"name\" as constraint_schema, fk.\"name\"
as constraint_name, cast(null as varchar(1)) as unique_constraint_catalog,
uks.\"name\" as unique_constraint_schema, uk.\"name\" as
unique_constraint_name, cast('FULL' as varchar(7)) as match_option,
fk.\"update_action\" as update_rule, fk.\"delete_action\" as delete_rule,
t.\"schema_id\" as fk_schema_id, t.\"id\" as fk_table_id, t.\"name\" as
fk_table_name, fk.\"id\" as fk_key_id, ukt.\"schema_id\" as uc_schema_id,
uk.\"table_id\" as uc_table_id, ukt.\"name\" as uc_table_name, uk.\"id\" as
uc_key_id from sys.\"fkeys\" fk inner join sys.\"tables\" t on t.\"id\" =
fk.\"table_id\" inner join sys.\"schemas\" s on s.\"id\" = t.\"schema_id\" left
outer join sys.\"keys\" uk on uk.\"id\" = fk.\"rkey\" left outer join
sys.\"tables\" ukt on ukt.\"id\" = uk.\"table_id\" l
eft outer join sys.\"schemas\" uks on uks.\"id\" = ukt.\"schema_id\" order by
s.\"name\", t.\"name\", fk.\"name\";", "VIEW", true, "COMMIT",
"WRITABLE", NULL ]
[ "sys._tables", "information_schema", "schemata", "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, 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\";",
"VIEW", true, "COMMIT", "WRITABLE", NULL ]
[ "sys._tables", "information_schema", "sequences", "create view
information_schema.sequences as select cast(null as varchar(1)) as
sequence_catalog, s.\"name\" as sequence_schema, sq.\"name\" as sequence_name,
cast('bigint' as varchar(16)) as data_type, cast(64 as smallint) as
numeric_precision, cast(2 as smallint) as numeric_precision_radix, cast(0 as
smallint) as numeric_scale, sq.\"start\" as start_value, sq.\"minvalue\" as
minimum_value, sq.\"maxvalue\" as maximum_value, sq.\"increment\" as increment,
cast(sys.ifthenelse(sq.\"cycle\", 'YES', 'NO') as varchar(3)) as cycle_option,
cast(null as varchar(16)) as declared_data_type, cast(null as smallint) as
declared_numeric_precision, cast(null as smallint) as declared_numeric_scale,
sq.\"schema_id\" as schema_id, sq.\"id\" as sequence_id,
get_value_for(s.\"name\", sq.\"name\") as current_value, sq.\"cacheinc\" as
cacheinc, cm.\"remark\" as comments from sys.\"sequences\" sq inner join
sys.\"schemas\" s on sq.\"schema_id\" = s.\"id\
" left outer join sys.\"comments\" cm on sq.\"id\" = cm.\"id\" order by
s.\"name\", sq.\"name\";", "VIEW", true, "COMMIT", "WRITABLE",
NULL ]
[ "sys._tables", "information_schema", "table_constraints", "create
view information_schema.table_constraints as select cast(null as varchar(1)) as
constraint_catalog, s.\"name\" as constraint_schema, k.\"name\" as
constraint_name, cast(null as varchar(1)) as table_catalog, s.\"name\" as
table_schema, t.\"name\" as table_name, cast(case k.\"type\" when 0 then
'PRIMARY KEY' when 1 then 'UNIQUE' when 2 then 'FOREIGN KEY' else null end as
varchar(16)) as constraint_type, cast('NO' as varchar(3)) as is_deferrable,
cast('NO' as varchar(3)) as initially_deferred, cast('YES' as varchar(3)) as
enforced, t.\"schema_id\" as schema_id, t.\"id\" as table_id, k.\"id\" as
key_id, k.\"type\" as key_type, t.\"system\" as is_system from (select
sk.\"id\", sk.\"table_id\", sk.\"name\", sk.\"type\" from sys.\"keys\" sk union
all select tk.\"id\", tk.\"table_id\", tk.\"name\", tk.\"type\" from
tmp.\"keys\" tk) k inner join (select st.\"id\", st.\"schema_id\", st.\"name\",
st.\"system\" from sys.\"_tabl
es\" st union all select tt.\"id\", tt.\"schema_id\", tt.\"name\",
tt.\"system\" from tmp.\"_tables\" tt) t on k.\"table_id\" = t.\"id\" inner
join sys.\"schemas\" s on t.\"schema_id\" = s.\"id\" order by s.\"name\",
t.\"name\", k.\"name\";", "VIEW", true, "COMMIT", "WRITABLE",
NULL ]
@@ -653,6 +654,23 @@ select 'null in fkeys.delete_action', de
[ "sys._columns", "information_schema", "columns", "column_id",
"int", 32, 0, NULL, true, 52, NULL, NULL ]
[ "sys._columns", "information_schema", "columns", "is_system",
"boolean", 1, 0, NULL, true, 53, NULL, NULL ]
[ "sys._columns", "information_schema", "columns", "comments",
"varchar", 65000, 0, NULL, true, 54, NULL, NULL ]
+[ "sys._columns", "information_schema", "referential_constraints",
"constraint_catalog", "varchar", 1, 0, NULL, true, 0,
NULL, NULL ]
+[ "sys._columns", "information_schema", "referential_constraints",
"constraint_schema", "varchar", 1024, 0, NULL, true, 1,
NULL, NULL ]
+[ "sys._columns", "information_schema", "referential_constraints",
"constraint_name", "varchar", 1024, 0, NULL, true, 2,
NULL, NULL ]
+[ "sys._columns", "information_schema", "referential_constraints",
"unique_constraint_catalog", "varchar", 1, 0, NULL, true,
3, NULL, NULL ]
+[ "sys._columns", "information_schema", "referential_constraints",
"unique_constraint_schema", "varchar", 1024, 0, NULL, true,
4, NULL, NULL ]
+[ "sys._columns", "information_schema", "referential_constraints",
"unique_constraint_name", "varchar", 1024, 0, NULL, true,
5, NULL, NULL ]
+[ "sys._columns", "information_schema", "referential_constraints",
"match_option", "varchar", 7, 0, NULL, true, 6, NULL,
NULL ]
+[ "sys._columns", "information_schema", "referential_constraints",
"update_rule", "varchar", 15, 0, NULL, true, 7, NULL,
NULL ]
+[ "sys._columns", "information_schema", "referential_constraints",
"delete_rule", "varchar", 15, 0, NULL, true, 8, NULL,
NULL ]
+[ "sys._columns", "information_schema", "referential_constraints",
"fk_schema_id", "int", 32, 0, NULL, true, 9, NULL, NULL
]
+[ "sys._columns", "information_schema", "referential_constraints",
"fk_table_id", "int", 32, 0, NULL, true, 10, NULL, NULL
]
+[ "sys._columns", "information_schema", "referential_constraints",
"fk_table_name", "varchar", 1024, 0, NULL, true, 11,
NULL, NULL ]
+[ "sys._columns", "information_schema", "referential_constraints",
"fk_key_id", "int", 32, 0, NULL, true, 12, NULL, NULL
]
+[ "sys._columns", "information_schema", "referential_constraints",
"uc_schema_id", "int", 32, 0, NULL, true, 13, NULL, NULL
]
+[ "sys._columns", "information_schema", "referential_constraints",
"uc_table_id", "int", 32, 0, NULL, true, 14, NULL, NULL
]
+[ "sys._columns", "information_schema", "referential_constraints",
"uc_table_name", "varchar", 1024, 0, NULL, true, 15,
NULL, NULL ]
+[ "sys._columns", "information_schema", "referential_constraints",
"uc_key_id", "int", 32, 0, NULL, true, 16, NULL, NULL
]
[ "sys._columns", "information_schema", "schemata", "catalog_name",
"varchar", 1, 0, NULL, true, 0, NULL, NULL ]
[ "sys._columns", "information_schema", "schemata", "schema_name",
"varchar", 1024, 0, NULL, true, 1, NULL, NULL ]
[ "sys._columns", "information_schema", "schemata", "schema_owner",
"varchar", 1024, 0, NULL, true, 2, NULL, NULL ]
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
