Changeset: 66b62dd188ff for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/66b62dd188ff
Modified Files:
sql/backends/monet5/sql_upgrades.c
Branch: literal_features
Log Message:
merged with default
diffs (truncated from 1085 to 300 lines):
diff --git a/clients/Tests/exports.stable.out b/clients/Tests/exports.stable.out
--- a/clients/Tests/exports.stable.out
+++ b/clients/Tests/exports.stable.out
@@ -1145,7 +1145,6 @@ InstrPtr pushArgumentId(MalBlkPtr mb, In
InstrPtr pushBit(MalBlkPtr mb, InstrPtr q, bit val);
InstrPtr pushBte(MalBlkPtr mb, InstrPtr q, bte val);
InstrPtr pushDbl(MalBlkPtr mb, InstrPtr q, dbl val);
-InstrPtr pushEmptyBAT(MalBlkPtr mb, InstrPtr q, int tpe);
InstrPtr pushEndInstruction(MalBlkPtr mb);
InstrPtr pushFlt(MalBlkPtr mb, InstrPtr q, flt val);
InstrPtr pushHge(MalBlkPtr mb, InstrPtr q, hge val);
diff --git a/monetdb5/mal/mal_builder.c b/monetdb5/mal/mal_builder.c
--- a/monetdb5/mal/mal_builder.c
+++ b/monetdb5/mal/mal_builder.c
@@ -698,20 +698,6 @@ pushZero(MalBlkPtr mb, InstrPtr q, int t
}
InstrPtr
-pushEmptyBAT(MalBlkPtr mb, InstrPtr q, int tpe)
-{
- if (q == NULL || mb->errors)
- return q;
- setModuleId(q, getName("bat"));
- setFunctionId(q, getName("new"));
-
- q = pushArgument(mb, q, newTypeVariable(mb, TYPE_void));
- q = pushArgument(mb, q, newTypeVariable(mb, getBatType(tpe)));
- q = pushZero(mb, q, TYPE_lng);
- return q;
-}
-
-InstrPtr
pushValue(MalBlkPtr mb, InstrPtr q, ValPtr vr)
{
int _t;
diff --git a/monetdb5/mal/mal_builder.h b/monetdb5/mal/mal_builder.h
--- a/monetdb5/mal/mal_builder.h
+++ b/monetdb5/mal/mal_builder.h
@@ -50,7 +50,6 @@ mal_export InstrPtr pushNil(MalBlkPtr mb
mal_export InstrPtr pushType(MalBlkPtr mb, InstrPtr q, int tpe);
mal_export InstrPtr pushNilType(MalBlkPtr mb, InstrPtr q, char *tpe);
mal_export InstrPtr pushZero(MalBlkPtr mb, InstrPtr q, int tpe);
-mal_export InstrPtr pushEmptyBAT(MalBlkPtr mb, InstrPtr q, int tpe);
mal_export InstrPtr pushValue(MalBlkPtr mb, InstrPtr q, ValPtr cst);
mal_export int getIntConstant(MalBlkPtr mb, int val);
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
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
