Changeset: 3b1ad0c7f65c for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/3b1ad0c7f65c
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.int128
sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade/Tests/upgrade.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.int128
sql/test/testdb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade/Tests/upgrade.stable.out.int128
testing/listexports.py.in
Branch: Sep2022
Log Message:
Add CASCADE to all DROP queries.
This will make that the upgrade proceeds at the cost of possibly
dropping user functions/views.
diffs (truncated from 1211 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
@@ -327,23 +327,23 @@ sql_drop_functions_dependencies_Xs_on_Ys
/* remove functions which were created in
sql/scripts/21_dependency_functions.sql */
pos += snprintf(buf + pos, bufsize - pos,
- "DROP FUNCTION dependencies_schemas_on_users();\n"
- "DROP FUNCTION dependencies_owners_on_schemas();\n"
- "DROP FUNCTION dependencies_tables_on_views();\n"
- "DROP FUNCTION dependencies_tables_on_indexes();\n"
- "DROP FUNCTION dependencies_tables_on_triggers();\n"
- "DROP FUNCTION dependencies_tables_on_foreignKeys();\n"
- "DROP FUNCTION dependencies_tables_on_functions();\n"
- "DROP FUNCTION dependencies_columns_on_views();\n"
- "DROP FUNCTION dependencies_columns_on_keys();\n"
- "DROP FUNCTION dependencies_columns_on_indexes();\n"
- "DROP FUNCTION dependencies_columns_on_functions();\n"
- "DROP FUNCTION dependencies_columns_on_triggers();\n"
- "DROP FUNCTION dependencies_views_on_functions();\n"
- "DROP FUNCTION dependencies_views_on_triggers();\n"
- "DROP FUNCTION dependencies_functions_on_functions();\n"
- "DROP FUNCTION dependencies_functions_on_triggers();\n"
- "DROP FUNCTION dependencies_keys_on_foreignKeys();\n");
+ "DROP FUNCTION dependencies_schemas_on_users()
CASCADE;\n"
+ "DROP FUNCTION dependencies_owners_on_schemas()
CASCADE;\n"
+ "DROP FUNCTION dependencies_tables_on_views()
CASCADE;\n"
+ "DROP FUNCTION dependencies_tables_on_indexes()
CASCADE;\n"
+ "DROP FUNCTION dependencies_tables_on_triggers()
CASCADE;\n"
+ "DROP FUNCTION dependencies_tables_on_foreignKeys()
CASCADE;\n"
+ "DROP FUNCTION dependencies_tables_on_functions()
CASCADE;\n"
+ "DROP FUNCTION dependencies_columns_on_views()
CASCADE;\n"
+ "DROP FUNCTION dependencies_columns_on_keys()
CASCADE;\n"
+ "DROP FUNCTION dependencies_columns_on_indexes()
CASCADE;\n"
+ "DROP FUNCTION dependencies_columns_on_functions()
CASCADE;\n"
+ "DROP FUNCTION dependencies_columns_on_triggers()
CASCADE;\n"
+ "DROP FUNCTION dependencies_views_on_functions()
CASCADE;\n"
+ "DROP FUNCTION dependencies_views_on_triggers()
CASCADE;\n"
+ "DROP FUNCTION dependencies_functions_on_functions()
CASCADE;\n"
+ "DROP FUNCTION dependencies_functions_on_triggers()
CASCADE;\n"
+ "DROP FUNCTION dependencies_keys_on_foreignKeys()
CASCADE;\n");
assert(pos < bufsize);
@@ -404,7 +404,7 @@ sql_update_storagemodel(Client c, mvc *s
}
pos += snprintf(buf + pos, bufsize - pos,
- "drop procedure if exists sys.storagemodelinit();\n"
+ "drop procedure if exists sys.storagemodelinit() cascade;\n"
"drop table if exists sys.storagemodelinput cascade;\n"
"drop view if exists sys.\"storage\" cascade;\n");
@@ -926,23 +926,23 @@ sql_update_nov2019(Client c, mvc *sql)
#endif
/* 60/61_wlcr signatures migrations */
pos += snprintf(buf + pos, bufsize - pos,
- "drop procedure master();\n"
- "drop procedure master(string);\n"
- "drop procedure stopmaster();\n"
- "drop procedure masterbeat(int);\n"
- "drop function masterClock();\n"
- "drop function masterTick();\n"
- "drop procedure replicate();\n"
- "drop procedure replicate(timestamp);\n"
- "drop procedure replicate(string);\n"
- "drop procedure replicate(string, timestamp);\n"
- "drop procedure replicate(string, tinyint);\n"
- "drop procedure replicate(string, smallint);\n"
- "drop procedure replicate(string, integer);\n"
- "drop procedure replicate(string, bigint);\n"
- "drop procedure replicabeat(integer);\n"
- "drop function replicaClock();\n"
- "drop function replicaTick();\n"
+ "drop procedure master() cascade;\n"
+ "drop procedure master(string) cascade;\n"
+ "drop procedure stopmaster() cascade;\n"
+ "drop procedure masterbeat(int) cascade;\n"
+ "drop function masterClock() cascade;\n"
+ "drop function masterTick() cascade;\n"
+ "drop procedure replicate() cascade;\n"
+ "drop procedure replicate(timestamp) cascade;\n"
+ "drop procedure replicate(string) cascade;\n"
+ "drop procedure replicate(string, timestamp) cascade;\n"
+ "drop procedure replicate(string, tinyint) cascade;\n"
+ "drop procedure replicate(string, smallint) cascade;\n"
+ "drop procedure replicate(string, integer) cascade;\n"
+ "drop procedure replicate(string, bigint) cascade;\n"
+ "drop procedure replicabeat(integer) cascade;\n"
+ "drop function replicaClock() cascade;\n"
+ "drop function replicaTick() cascade;\n"
"create schema wlc;\n"
"create procedure wlc.master()\n"
@@ -1059,8 +1059,8 @@ sql_update_nov2019(Client c, mvc *sql)
/* The MAL implementation of functions json.text(string) and
json.text(int) do not exist */
pos += snprintf(buf + pos, bufsize - pos,
- "drop function json.text(string);\n"
- "drop function json.text(int);\n");
+ "drop function json.text(string) cascade;\n"
+ "drop function json.text(int) cascade;\n");
/* The first argument to copyfrom is a PTR type */
pos += snprintf(buf + pos, bufsize - pos,
@@ -1130,7 +1130,7 @@ sql_update_jun2020(Client c, mvc *sql)
/* 12_url */
pos += snprintf(buf + pos, bufsize - pos,
- "drop function isaURL(url);\n"
+ "drop function isaURL(url) cascade;\n"
"CREATE function isaURL(theUrl string) RETURNS BOOL\n"
" EXTERNAL NAME url.\"isaURL\";\n"
"GRANT EXECUTE ON FUNCTION isaURL(string) TO PUBLIC;\n"
@@ -1139,10 +1139,10 @@ sql_update_jun2020(Client c, mvc *sql)
/* 13_date.sql */
pos += snprintf(buf + pos, bufsize - pos,
- "drop function str_to_time(string, string);\n"
- "drop function time_to_str(time, string);\n"
- "drop function str_to_timestamp(string, string);\n"
- "drop function timestamp_to_str(timestamp, string);\n"
+ "drop function str_to_time(string, string) cascade;\n"
+ "drop function time_to_str(time, string) cascade;\n"
+ "drop function str_to_timestamp(string, string)
cascade;\n"
+ "drop function timestamp_to_str(timestamp, string)
cascade;\n"
"create function str_to_time(s string, format string)
returns time with time zone\n"
" external name mtime.\"str_to_time\";\n"
"create function time_to_str(d time with time zone,
format string) returns string\n"
@@ -1163,8 +1163,8 @@ sql_update_jun2020(Client c, mvc *sql)
t = mvc_bind_table(sql, sys, "tracelog");
t->system = 0; /* make it non-system else the drop view will fail */
pos += snprintf(buf + pos, bufsize - pos,
- "drop view sys.tracelog;\n"
- "drop function sys.tracelog();\n"
+ "drop view sys.tracelog cascade;\n"
+ "drop function sys.tracelog() cascade;\n"
"create function sys.tracelog()\n"
" returns table (\n"
" ticks bigint, -- time in microseconds\n"
@@ -1180,10 +1180,10 @@ sql_update_jun2020(Client c, mvc *sql)
/* 17_temporal.sql */
pos += snprintf(buf + pos, bufsize - pos,
- "drop function sys.epoch(bigint);\n"
- "drop function sys.epoch(int);\n"
- "drop function sys.epoch(timestamp);\n"
- "drop function sys.epoch(timestamp with time zone);\n"
+ "drop function sys.epoch(bigint) cascade;\n"
+ "drop function sys.epoch(int) cascade;\n"
+ "drop function sys.epoch(timestamp) cascade;\n"
+ "drop function sys.epoch(timestamp with time zone)
cascade;\n"
"create function sys.epoch(sec BIGINT) returns
TIMESTAMP WITH TIME ZONE\n"
" external name mtime.epoch;\n"
"create function sys.epoch(sec INT) returns TIMESTAMP
WITH TIME ZONE\n"
@@ -1206,8 +1206,8 @@ sql_update_jun2020(Client c, mvc *sql)
t->system = 0; /* make it non-system else the drop view will fail */
pos += snprintf(buf + pos, bufsize - pos,
- "drop view sys.sessions;\n"
- "drop function sys.sessions;\n"
+ "drop view sys.sessions cascade;\n"
+ "drop function sys.sessions cascade;\n"
"create function sys.sessions()\n"
"returns table(\n"
"\"sessionid\" int,\n"
@@ -1321,8 +1321,8 @@ sql_update_jun2020(Client c, mvc *sql)
t->system = 0; /* make it non-system else the drop view will fail */
pos += snprintf(buf + pos, bufsize - pos,
- "drop view sys.queue;\n"
- "drop function sys.queue;\n"
+ "drop view sys.queue cascade;\n"
+ "drop function sys.queue cascade;\n"
"create function sys.queue()\n"
"returns table(\n"
"\"tag\" bigint,\n"
@@ -1339,9 +1339,9 @@ sql_update_jun2020(Client c, mvc *sql)
"create view sys.queue as select * from sys.queue();\n"
"grant select on sys.queue to public;\n"
- "drop procedure sys.pause(int);\n"
- "drop procedure sys.resume(int);\n"
- "drop procedure sys.stop(int);\n"
+ "drop procedure sys.pause(int) cascade;\n"
+ "drop procedure sys.resume(int) cascade;\n"
+ "drop procedure sys.stop(int) cascade;\n"
"grant execute on procedure sys.pause(bigint) to
public;\n"
"grant execute on procedure sys.resume(bigint) to
public;\n"
@@ -1593,18 +1593,18 @@ sql_update_jun2020(Client c, mvc *sql)
" and schema_id = (select id from sys.schemas where
name = 'sys') and type in (%d, %d);\n", (int) F_ANALYTIC, (int) F_AGGR);
pos += snprintf(buf + pos, bufsize - pos,
- "DROP AGGREGATE stddev_samp(date);\n"
- "DROP AGGREGATE stddev_samp(time);\n"
- "DROP AGGREGATE stddev_samp(timestamp);\n"
- "DROP AGGREGATE stddev_pop(date);\n"
- "DROP AGGREGATE stddev_pop(time);\n"
- "DROP AGGREGATE stddev_pop(timestamp);\n"
- "DROP AGGREGATE var_samp(date);\n"
- "DROP AGGREGATE var_samp(time);\n"
- "DROP AGGREGATE var_samp(timestamp);\n"
- "DROP AGGREGATE var_pop(date);\n"
- "DROP AGGREGATE var_pop(time);\n"
- "DROP AGGREGATE var_pop(timestamp);\n");
+ "DROP AGGREGATE stddev_samp(date) CASCADE;\n"
+ "DROP AGGREGATE stddev_samp(time) CASCADE;\n"
+ "DROP AGGREGATE stddev_samp(timestamp) CASCADE;\n"
+ "DROP AGGREGATE stddev_pop(date) CASCADE;\n"
+ "DROP AGGREGATE stddev_pop(time) CASCADE;\n"
+ "DROP AGGREGATE stddev_pop(timestamp) CASCADE;\n"
+ "DROP AGGREGATE var_samp(date) CASCADE;\n"
+ "DROP AGGREGATE var_samp(time) CASCADE;\n"
+ "DROP AGGREGATE var_samp(timestamp) CASCADE;\n"
+ "DROP AGGREGATE var_pop(date) CASCADE;\n"
+ "DROP AGGREGATE var_pop(time) CASCADE;\n"
+ "DROP AGGREGATE var_pop(timestamp) CASCADE;\n");
/* 51_sys_schema_extensions */
pos += snprintf(buf + pos, bufsize - pos,
@@ -1731,27 +1731,27 @@ sql_update_jun2020_bam(Client c, mvc *m)
pos += snprintf(buf + pos, bufsize - pos,
"update sys.schemas set system = false where name =
'bam';\n"
"update sys._tables set system = false where schema_id
in (select id from sys.schemas where name = 'bam');\n"
- "drop procedure bam.bam_loader_repos;\n"
- "drop procedure bam.bam_loader_files;\n"
- "drop procedure bam.bam_loader_file;\n"
- "drop procedure bam.bam_drop_file;\n"
- "drop function bam.bam_flag;\n"
- "drop function bam.reverse_seq;\n"
- "drop function bam.reverse_qual;\n"
- "drop function bam.seq_length;\n"
- "drop function bam.seq_char;\n"
- "drop procedure bam.sam_export;\n"
- "drop procedure bam.bam_export;\n");
+ "drop procedure bam.bam_loader_repos cascade;\n"
+ "drop procedure bam.bam_loader_files cascade;\n"
+ "drop procedure bam.bam_loader_file cascade;\n"
+ "drop procedure bam.bam_drop_file cascade;\n"
+ "drop function bam.bam_flag cascade;\n"
+ "drop function bam.reverse_seq cascade;\n"
+ "drop function bam.reverse_qual cascade;\n"
+ "drop function bam.seq_length cascade;\n"
+ "drop function bam.seq_char cascade;\n"
+ "drop procedure bam.sam_export cascade;\n"
+ "drop procedure bam.bam_export cascade;\n");
if (b) {
if (BATcount(b) > 0 && *(lng *) Tloc(b, 0) == 0) {
/* tables in bam schema are empty: drop them */
pos += snprintf(buf + pos, bufsize - pos,
- "drop table bam.sq;\n"
- "drop table bam.rg;\n"
- "drop table bam.pg;\n"
- "drop table bam.export;\n"
- "drop table bam.files;\n"
- "drop schema bam;\n");
+ "drop table bam.sq cascade;\n"
+ "drop table bam.rg cascade;\n"
+ "drop table bam.pg cascade;\n"
+ "drop table bam.export cascade;\n"
+ "drop table bam.files cascade;\n"
+ "drop schema bam cascade;\n");
}
BBPunfix(b->batCacheid);
}
@@ -1824,9 +1824,9 @@ static str
sql_update_oscar_lidar(Client c)
{
char *query =
- "drop procedure sys.lidarattach(string);\n"
- "drop procedure sys.lidarload(string);\n"
- "drop procedure sys.lidarexport(string, string, string);\n";
+ "drop procedure sys.lidarattach(string) cascade;\n"
+ "drop procedure sys.lidarload(string) cascade;\n"
+ "drop procedure sys.lidarexport(string, string, string)
cascade;\n";
printf("Running database upgrade commands:\n%s\n", query);
return SQLstatementIntern(c, query, "update", true, false, NULL);
}
@@ -1874,8 +1874,8 @@ sql_update_oscar(Client c, mvc *sql)
t->system = 0; /* make it non-system else the drop view
will fail */
pos += snprintf(buf + pos, bufsize - pos,
- "drop view sys.queue;\n"
- "drop function sys.queue;\n"
+ "drop view sys.queue cascade;\n"
+ "drop function sys.queue cascade;\n"
"create function sys.queue()\n"
"returns table(\n"
"\"tag\" bigint,\n"
@@ -1891,9 +1891,9 @@ sql_update_oscar(Client c, mvc *sql)
"grant execute on function sys.queue to
public;\n"
"create view sys.queue as select * from
sys.queue();\n"
"grant select on sys.queue to public;\n"
- "drop procedure sys.pause(bigint);\n"
- "drop procedure sys.resume(bigint);\n"
- "drop procedure sys.stop(bigint);\n"
+ "drop procedure sys.pause(bigint)
cascade;\n"
+ "drop procedure sys.resume(bigint)
cascade;\n"
+ "drop procedure sys.stop(bigint)
cascade;\n"
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
