Changeset: 4b5fbd801145 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/4b5fbd801145
Branch: Mar2025
Log Message:
merged
diffs (truncated from 429 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
@@ -4768,93 +4768,101 @@ sql_update_mar2025_sp1(Client c, mvc *sq
const char query1[] = "select table_type_name from sys.table_types
where table_type_name = 'LOCAL TEMPORARY VIEW';";
err = SQLstatementIntern(c, query1, "update", true, false, &output);
if (err == MAL_SUCCEED && (b = BBPquickdesc(output->cols[0].b)) &&
BATcount(b) == 0) {
- const char stmt2[] =
+ const char stmt2a[] =
"ALTER TABLE sys.table_types SET READ WRITE;\n"
- "INSERT INTO sys.table_types VALUES (31, 'LOCAL
TEMPORARY VIEW');\n";
- printf("Running database upgrade commands:\n%s\n", stmt2);
+ "COMMIT;\n";
+ printf("Running database upgrade commands:\n%s\n", stmt2a);
fflush(stdout);
- err = SQLstatementIntern(c, stmt2, "update", true, false, NULL);
+ err = SQLstatementIntern(c, stmt2a, "update", true, false,
NULL);
if (err == MAL_SUCCEED) {
- const char stmt3[] = "ALTER TABLE sys.table_types SET
READ ONLY;\n";
- printf("Running database upgrade commands:\n%s\n",
stmt3);
+ const char stmt2b[] =
+ "INSERT INTO sys.table_types VALUES (31, 'LOCAL
TEMPORARY VIEW');\n"
+ "COMMIT;\n";
+ printf("Running database upgrade commands:\n%s\n",
stmt2b);
fflush(stdout);
- err = SQLstatementIntern(c, stmt3, "update", true,
false, NULL);
- }
+ err = SQLstatementIntern(c, stmt2b, "update", true,
false, NULL);
+ if (err == MAL_SUCCEED) {
+ const char stmt3[] = "ALTER TABLE
sys.table_types SET READ ONLY;\n";
+ printf("Running database upgrade
commands:\n%s\n", stmt3);
+ fflush(stdout);
+ err = SQLstatementIntern(c, stmt3, "update",
true, false, NULL);
+ }
- /* 91_information_schema.sql */
- /* correct definitions of views: information_schema.tables and
information_schema.views */
- if (err == MAL_SUCCEED) {
- const char query[] =
- "DROP VIEW information_schema.views CASCADE;\n"
- "DROP VIEW information_schema.tables CASCADE;\n"
+ /* 91_information_schema.sql */
+ /* correct definitions of views:
information_schema.tables and information_schema.views */
+ if (err == MAL_SUCCEED) {
+ const char query[] =
+ "DROP VIEW information_schema.views
CASCADE;\n"
+ "DROP VIEW information_schema.tables
CASCADE;\n"
- "CREATE VIEW INFORMATION_SCHEMA.TABLES AS
SELECT\n"
- " cast(NULL AS varchar(1)) AS TABLE_CATALOG,\n"
- " s.\"name\" AS TABLE_SCHEMA,\n"
- " t.\"name\" AS TABLE_NAME,\n"
- " tt.\"table_type_name\" AS TABLE_TYPE,\n"
- " cast(NULL AS varchar(1)) AS
SELF_REFERENCING_COLUMN_NAME,\n"
- " cast(NULL AS varchar(1)) AS
REFERENCE_GENERATION,\n"
- " cast(NULL AS varchar(1)) AS
USER_DEFINED_TYPE_CATALOG,\n"
- " cast(NULL AS varchar(1)) AS
USER_DEFINED_TYPE_SCHEMA,\n"
- " cast(NULL AS varchar(1)) AS
USER_DEFINED_TYPE_NAME,\n"
- " 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,\n"
- " cast('NO' AS varchar(3)) AS IS_TYPED,\n"
- " 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,\n"
- " -- MonetDB column extensions\n"
- " t.\"schema_id\" AS schema_id,\n"
- " t.\"id\" AS table_id,\n"
- " t.\"type\" AS table_type_id,\n"
- " st.\"count\" AS row_count,\n"
- " t.\"system\" AS is_system,\n"
- " sys.ifthenelse(t.\"type\" IN (1, 11, 21,
31), TRUE, FALSE) AS is_view,\n"
- " t.\"query\" AS query_def,\n"
- " cm.\"remark\" AS comments\n"
- " FROM sys.\"tables\" t\n"
- " INNER JOIN sys.\"schemas\" s ON
t.\"schema_id\" = s.\"id\"\n"
- " INNER JOIN sys.\"table_types\" tt ON
t.\"type\" = tt.\"table_type_id\"\n"
- " 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"
- "GRANT SELECT ON TABLE
INFORMATION_SCHEMA.TABLES TO PUBLIC WITH GRANT OPTION;\n"
+ "CREATE VIEW INFORMATION_SCHEMA.TABLES
AS SELECT\n"
+ " cast(NULL AS varchar(1)) AS
TABLE_CATALOG,\n"
+ " s.\"name\" AS TABLE_SCHEMA,\n"
+ " t.\"name\" AS TABLE_NAME,\n"
+ " tt.\"table_type_name\" AS
TABLE_TYPE,\n"
+ " cast(NULL AS varchar(1)) AS
SELF_REFERENCING_COLUMN_NAME,\n"
+ " cast(NULL AS varchar(1)) AS
REFERENCE_GENERATION,\n"
+ " cast(NULL AS varchar(1)) AS
USER_DEFINED_TYPE_CATALOG,\n"
+ " cast(NULL AS varchar(1)) AS
USER_DEFINED_TYPE_SCHEMA,\n"
+ " cast(NULL AS varchar(1)) AS
USER_DEFINED_TYPE_NAME,\n"
+ " 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,\n"
+ " cast('NO' AS varchar(3)) AS
IS_TYPED,\n"
+ " 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,\n"
+ " -- MonetDB column extensions\n"
+ " t.\"schema_id\" AS schema_id,\n"
+ " t.\"id\" AS table_id,\n"
+ " t.\"type\" AS table_type_id,\n"
+ " st.\"count\" AS row_count,\n"
+ " t.\"system\" AS is_system,\n"
+ " sys.ifthenelse(t.\"type\" IN (1, 11,
21, 31), TRUE, FALSE) AS is_view,\n"
+ " t.\"query\" AS query_def,\n"
+ " cm.\"remark\" AS comments\n"
+ " FROM sys.\"tables\" t\n"
+ " INNER JOIN sys.\"schemas\" s ON
t.\"schema_id\" = s.\"id\"\n"
+ " INNER JOIN sys.\"table_types\" tt ON
t.\"type\" = tt.\"table_type_id\"\n"
+ " 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"
+ "GRANT SELECT ON TABLE
INFORMATION_SCHEMA.TABLES TO PUBLIC WITH GRANT OPTION;\n"
- "CREATE VIEW INFORMATION_SCHEMA.VIEWS AS
SELECT\n"
- " cast(NULL AS varchar(1)) AS TABLE_CATALOG,\n"
- " s.\"name\" AS TABLE_SCHEMA,\n"
- " t.\"name\" AS TABLE_NAME,\n"
- " t.\"query\" AS VIEW_DEFINITION,\n"
- " cast('NONE' AS varchar(10)) AS
CHECK_OPTION,\n"
- " cast('NO' AS varchar(3)) AS IS_UPDATABLE,\n"
- " cast('NO' AS varchar(3)) AS
INSERTABLE_INTO,\n"
- " cast('NO' AS varchar(3)) AS
IS_TRIGGER_UPDATABLE,\n"
- " cast('NO' AS varchar(3)) AS
IS_TRIGGER_DELETABLE,\n"
- " cast('NO' AS varchar(3)) AS
IS_TRIGGER_INSERTABLE_INTO,\n"
- " -- MonetDB column extensions\n"
- " t.\"schema_id\" AS schema_id,\n"
- " t.\"id\" AS table_id,\n"
- " t.\"type\" AS table_type_id,\n"
- " t.\"system\" AS is_system,\n"
- " cm.\"remark\" AS comments\n"
- " FROM sys.\"tables\" t\n"
- " INNER JOIN sys.\"schemas\" s ON
t.\"schema_id\" = s.\"id\"\n"
- " LEFT OUTER JOIN sys.\"comments\" cm ON
t.\"id\" = cm.\"id\"\n"
- " WHERE t.\"type\" IN (1, 11, 21, 31)\n"
- " ORDER BY s.\"name\", t.\"name\";\n"
- "GRANT SELECT ON TABLE INFORMATION_SCHEMA.VIEWS
TO PUBLIC WITH GRANT OPTION;\n"
- "\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 ('tables','views');\n";
+ "CREATE VIEW INFORMATION_SCHEMA.VIEWS
AS SELECT\n"
+ " cast(NULL AS varchar(1)) AS
TABLE_CATALOG,\n"
+ " s.\"name\" AS TABLE_SCHEMA,\n"
+ " t.\"name\" AS TABLE_NAME,\n"
+ " t.\"query\" AS VIEW_DEFINITION,\n"
+ " cast('NONE' AS varchar(10)) AS
CHECK_OPTION,\n"
+ " cast('NO' AS varchar(3)) AS
IS_UPDATABLE,\n"
+ " cast('NO' AS varchar(3)) AS
INSERTABLE_INTO,\n"
+ " cast('NO' AS varchar(3)) AS
IS_TRIGGER_UPDATABLE,\n"
+ " cast('NO' AS varchar(3)) AS
IS_TRIGGER_DELETABLE,\n"
+ " cast('NO' AS varchar(3)) AS
IS_TRIGGER_INSERTABLE_INTO,\n"
+ " -- MonetDB column extensions\n"
+ " t.\"schema_id\" AS schema_id,\n"
+ " t.\"id\" AS table_id,\n"
+ " t.\"type\" AS table_type_id,\n"
+ " t.\"system\" AS is_system,\n"
+ " cm.\"remark\" AS comments\n"
+ " FROM sys.\"tables\" t\n"
+ " INNER JOIN sys.\"schemas\" s ON
t.\"schema_id\" = s.\"id\"\n"
+ " LEFT OUTER JOIN sys.\"comments\" cm
ON t.\"id\" = cm.\"id\"\n"
+ " WHERE t.\"type\" IN (1, 11, 21, 31)\n"
+ " ORDER BY s.\"name\", t.\"name\";\n"
+ "GRANT SELECT ON TABLE
INFORMATION_SCHEMA.VIEWS TO PUBLIC WITH GRANT OPTION;\n"
+ "\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 ('tables','views');\n";
- sql_schema *infoschema = mvc_bind_schema(sql,
"information_schema");
- sql_table *t;
- if ((t = mvc_bind_table(sql, infoschema, "tables")) !=
NULL)
- t->system = 0; /* make it non-system else the
drop view will fail */
- if ((t = mvc_bind_table(sql, infoschema, "views")) !=
NULL)
- t->system = 0;
- printf("Running database upgrade commands:\n%s\n",
query);
- fflush(stdout);
- err = SQLstatementIntern(c, query, "update", true,
false, NULL);
+ sql_schema *infoschema = mvc_bind_schema(sql,
"information_schema");
+ sql_table *t;
+ if ((t = mvc_bind_table(sql, infoschema,
"tables")) != NULL)
+ t->system = 0; /* make it non-system
else the drop view will fail */
+ if ((t = mvc_bind_table(sql, infoschema,
"views")) != NULL)
+ t->system = 0;
+ printf("Running database upgrade
commands:\n%s\n", query);
+ fflush(stdout);
+ err = SQLstatementIntern(c, query, "update",
true, false, NULL);
+ }
}
}
if (output != NULL) {
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
@@ -297,7 +297,11 @@ delete from sys.privileges where (obj_id
Running database upgrade commands:
ALTER TABLE sys.table_types SET READ WRITE;
+COMMIT;
+
+Running database upgrade commands:
INSERT INTO sys.table_types VALUES (31, 'LOCAL TEMPORARY VIEW');
+COMMIT;
Running database upgrade commands:
ALTER TABLE sys.table_types SET READ ONLY;
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
@@ -297,7 +297,11 @@ delete from sys.privileges where (obj_id
Running database upgrade commands:
ALTER TABLE sys.table_types SET READ WRITE;
+COMMIT;
+
+Running database upgrade commands:
INSERT INTO sys.table_types VALUES (31, 'LOCAL TEMPORARY VIEW');
+COMMIT;
Running database upgrade commands:
ALTER TABLE sys.table_types SET READ ONLY;
diff --git
a/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
@@ -380,7 +380,11 @@ delete from sys.privileges where (obj_id
Running database upgrade commands:
ALTER TABLE sys.table_types SET READ WRITE;
+COMMIT;
+
+Running database upgrade commands:
INSERT INTO sys.table_types VALUES (31, 'LOCAL TEMPORARY VIEW');
+COMMIT;
Running database upgrade commands:
ALTER TABLE sys.table_types SET READ ONLY;
diff --git
a/sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
@@ -297,7 +297,11 @@ delete from sys.privileges where (obj_id
Running database upgrade commands:
ALTER TABLE sys.table_types SET READ WRITE;
+COMMIT;
+
+Running database upgrade commands:
INSERT INTO sys.table_types VALUES (31, 'LOCAL TEMPORARY VIEW');
+COMMIT;
Running database upgrade commands:
ALTER TABLE sys.table_types SET READ ONLY;
diff --git a/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out
b/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out
--- a/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out
+++ b/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out
@@ -297,7 +297,11 @@ delete from sys.privileges where (obj_id
Running database upgrade commands:
ALTER TABLE sys.table_types SET READ WRITE;
+COMMIT;
+
+Running database upgrade commands:
INSERT INTO sys.table_types VALUES (31, 'LOCAL TEMPORARY VIEW');
+COMMIT;
Running database upgrade commands:
ALTER TABLE sys.table_types SET READ ONLY;
diff --git a/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
@@ -380,7 +380,11 @@ delete from sys.privileges where (obj_id
Running database upgrade commands:
ALTER TABLE sys.table_types SET READ WRITE;
+COMMIT;
+
+Running database upgrade commands:
INSERT INTO sys.table_types VALUES (31, 'LOCAL TEMPORARY VIEW');
+COMMIT;
Running database upgrade commands:
ALTER TABLE sys.table_types SET READ ONLY;
diff --git
a/sql/test/testdb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
b/sql/test/testdb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
--- a/sql/test/testdb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
+++ b/sql/test/testdb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
@@ -297,7 +297,11 @@ delete from sys.privileges where (obj_id
Running database upgrade commands:
ALTER TABLE sys.table_types SET READ WRITE;
+COMMIT;
+
+Running database upgrade commands:
INSERT INTO sys.table_types VALUES (31, 'LOCAL TEMPORARY VIEW');
+COMMIT;
Running database upgrade commands:
ALTER TABLE sys.table_types SET READ ONLY;
diff --git a/sql/test/testdb-previous-upgrade-chain/Tests/upgrade.stable.out
b/sql/test/testdb-previous-upgrade-chain/Tests/upgrade.stable.out
--- a/sql/test/testdb-previous-upgrade-chain/Tests/upgrade.stable.out
+++ b/sql/test/testdb-previous-upgrade-chain/Tests/upgrade.stable.out
@@ -297,7 +297,11 @@ delete from sys.privileges where (obj_id
Running database upgrade commands:
ALTER TABLE sys.table_types SET READ WRITE;
+COMMIT;
+
+Running database upgrade commands:
INSERT INTO sys.table_types VALUES (31, 'LOCAL TEMPORARY VIEW');
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
