Changeset: 9f856de4d543 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/9f856de4d543
Branch: json-storage
Log Message:
Merge with default
diffs (truncated from 1019 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
@@ -6190,7 +6190,7 @@ sql_update_default(Client c, mvc *sql, s
" 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,\n"
" cast('NO' AS varchar(3)) AS IS_DEFERRABLE,\n"
" cast('NO' AS varchar(3)) AS INITIALLY_DEFERRED,\n"
- " cast('YES' AS varchar(3)) AS ENFORCED,\n"
+ " cast('YES' AS varchar(3)) AS ENFORCED,\n"
" -- MonetDB column extensions\n"
" t.\"schema_id\" AS schema_id,\n"
" t.\"id\" AS table_id,\n"
@@ -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';
@@ -209,7 +210,7 @@ CREATE VIEW INFORMATION_SCHEMA.TABLE_CON
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,
+ cast('YES' AS varchar(3)) AS ENFORCED,
-- MonetDB column extensions
t."schema_id" AS schema_id,
t."id" AS table_id,
@@ -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/server/rel_distribute.c b/sql/server/rel_distribute.c
--- a/sql/server/rel_distribute.c
+++ b/sql/server/rel_distribute.c
@@ -12,6 +12,7 @@
#include "rel_optimizer_private.h"
#include "rel_basetable.h"
#include "rel_exp.h"
+#include "rel_remote.h"
#include "sql_privileges.h"
static int
@@ -219,12 +220,17 @@ rel_rewrite_remote_(visitor *v, sql_rel
case op_basetable: {
sql_table *t = rel->l;
- /* set_remote() */
+ /* when a basetable wraps a sql_table (->l) which is remote we
want to store its remote
+ * uri to the REMOTE property. As the property is pulled up the
tree it can be used in
+ * the case of binary rel operators (see later switch cases) in
order to
+ * 1. resolve properly (same uri) replica tables in the other
subtree (that's why we
+ * call the rewrite_replica)
+ * 2. pull REMOTE over the binary op if the other subtree has a
matching uri remote table
+ */
if (t && isRemote(t) && (p = find_prop(rel->p, PROP_REMOTE)) ==
NULL) {
- char *local_name = sa_strconcat(v->sql->sa,
sa_strconcat(v->sql->sa, t->s->base.name, "."), t->base.name);
p = rel->p = prop_create(v->sql->sa, PROP_REMOTE,
rel->p);
p->id = t->base.id;
- p->value.pval = local_name;
+ p->value.pval = (void *)mapiuri_uri(t->query,
v->sql->sa);
}
} break;
case op_table:
@@ -290,6 +296,7 @@ rel_rewrite_remote_(visitor *v, sql_rel
if (rel->flag&MERGE_LEFT) /* search for any remote tables but
don't propagate over to this relation */
return rel;
+ /* if both subtrees have the REMOTE property with the same uri
then pull it up */
if (l && (pl = find_prop(l->p, PROP_REMOTE)) != NULL &&
r && (pr = find_prop(r->p, PROP_REMOTE)) != NULL &&
strcmp(pl->value.pval, pr->value.pval) == 0) {
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
@@ -78,6 +78,9 @@ DEP_VIEW
ids
DEP_VIEW
_tables
+table_constraints
+DEP_VIEW
+_tables
tables
DEP_VIEW
_tables
@@ -180,6 +183,9 @@ comments
schemata
DEP_VIEW
comments
+sequences
+DEP_VIEW
+comments
tables
DEP_VIEW
comments
@@ -305,6 +311,9 @@ DEP_VIEW
fkey_actions
fkeys
DEP_VIEW
+fkeys
+referential_constraints
+DEP_VIEW
fully_qualified_functions
describe_comments
DEP_VIEW
@@ -428,6 +437,12 @@ DEP_VIEW
keys
ids
DEP_VIEW
+keys
+referential_constraints
+DEP_VIEW
+keys
+table_constraints
+DEP_VIEW
objects
dependency_columns_on_indexes
DEP_VIEW
@@ -558,15 +573,24 @@ schemas
ids
DEP_VIEW
schemas
+referential_constraints
+DEP_VIEW
+schemas
schemata
DEP_VIEW
schemas
+sequences
+DEP_VIEW
+schemas
statistics
DEP_VIEW
schemas
storage
DEP_VIEW
schemas
+table_constraints
+DEP_VIEW
+schemas
tables
DEP_VIEW
schemas
@@ -581,6 +605,9 @@ DEP_VIEW
sequences
ids
DEP_VIEW
+sequences
+sequences
+DEP_VIEW
storage
schemastorage
DEP_VIEW
@@ -687,6 +714,9 @@ tables
ids
DEP_VIEW
tables
+referential_constraints
+DEP_VIEW
+tables
statistics
DEP_VIEW
tables
@@ -820,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
----
-1623 values hashing to 6b21bdd80c4e73ef598244a23a31446a
+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
@@ -444,10 +444,14 @@ select 'null in fkeys.delete_action', de
% .%25, .s, .t, ., ., .t, ., ., . #
table_name
% %25, name, name, query, type, system, commit_action, access, comment
# name
% char, varchar, varchar, clob, varchar,
boolean, char, char, varchar # type
-% 11, 18, 34, 3752, 5, 5, 8, 10, 0 # length
+% 11, 18, 34, 4470, 5, 5, 8, 10, 0 # length
[ "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", "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, 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_nam
e, 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 identit
y_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,
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", "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 ]
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
