Changeset: 263e070a8a9c for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=263e070a8a9c
Modified Files:
sql/backends/monet5/sql_scenario.c
sql/test/testdb-upgrade-chain/Tests/dump.stable.out
sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out
Branch: Oct2014
Log Message:
Fix upgrade (again): maintain "system" status of functions and views.
diffs (80 lines):
diff --git a/sql/backends/monet5/sql_scenario.c
b/sql/backends/monet5/sql_scenario.c
--- a/sql/backends/monet5/sql_scenario.c
+++ b/sql/backends/monet5/sql_scenario.c
@@ -1328,9 +1328,7 @@ sql_update_oct2014_sp1(Client c)
if (schvar)
schema = strdup(schvar->val.sval);
- pos += snprintf(buf + pos, bufsize - pos, "set schema \"sys\";\n");
-
- pos += snprintf(buf + pos, bufsize - pos, "\n\
+ pos += snprintf(buf + pos, bufsize - pos, "set schema \"sys\";\n\
create table upgradeOct2014_views as (select s.name, t.query from
tables t, schemas s where s.id = t.schema_id and t.id in (select d.depend_id
from dependencies d where d.id in (select f.id from functions f where f.func
ilike '%%returns table%%' and f.name not in ('env', 'var', 'db_users') and
f.language <> 0) and d.depend_type = 5)) with data;\n\
create table upgradeOct2014 as select s.name, f.func, f.id from
functions f, schemas s where s.id = f.schema_id and f.func ilike '%%returns
table%%' and f.name not in ('env', 'var', 'db_users') and f.language <> 0 order
by f.id with data;\n\
create table upgradeOct2014_changes (c bigint);\n\
@@ -1345,6 +1343,9 @@ sql_update_oct2014_sp1(Client c)
\n\
create function create_view_upgrade_oct2014( sname string, f string )
returns int external name sql.create_view_upgrade_oct2014;\n\
insert into upgradeOct2014_changes select
create_view_upgrade_oct2014(name, query) from upgradeOct2014_views;\n\
+ update _tables set system = true where name in ('tables', 'columns',
'users', 'querylog_catalog', 'querylog_calls', 'querylog_history', 'tracelog',
'sessions', 'optimizers', 'environment', 'queue', 'storage', 'storagemodel',
'tablestoragemodel') and schema_id = (select id from schemas where name =
'sys');\n\
+ insert into systemfunctions (select id from functions where name in
('bbp', 'db_users', 'dependencies_columns_on_functions',
'dependencies_columns_on_indexes', 'dependencies_columns_on_keys',
'dependencies_columns_on_triggers', 'dependencies_columns_on_views',
'dependencies_functions_on_functions', 'dependencies_functions_os_triggers',
'dependencies_keys_on_foreignkeys', 'dependencies_owners_on_schemas',
'dependencies_schemas_on_users', 'dependencies_tables_on_foreignkeys',
'dependencies_tables_on_functions', 'dependencies_tables_on_indexes',
'dependencies_tables_on_triggers', 'dependencies_tables_on_views',
'dependencies_views_on_functions', 'dependencies_views_on_triggers', 'env',
'environment', 'generate_series', 'optimizers', 'optimizer_stats',
'querycache', 'querylog_calls', 'querylog_catalog', 'queue', 'sessions',
'storage', 'storagemodel', 'tojsonarray', 'tracelog', 'var') and schema_id =
(select id from schemas where name = 'sys') and id not in (select function_id
from syst
emfunctions));\n\
+ delete from systemfunctions where function_id not in (select id from
functions);\n\
drop function create_view_upgrade_oct2014;\n\
\n\
drop table upgradeOct2014_views;\n\
diff --git a/sql/test/testdb-upgrade-chain/Tests/dump.stable.out
b/sql/test/testdb-upgrade-chain/Tests/dump.stable.out
--- a/sql/test/testdb-upgrade-chain/Tests/dump.stable.out
+++ b/sql/test/testdb-upgrade-chain/Tests/dump.stable.out
@@ -101198,19 +101198,6 @@ 1 0
1 1
create view keytest3 as select t2.key1, t2.key2, t1.key3 from keytest1 t1,
keytest2 t2 where t1.key1 = t2.key1 and t1.key2 = t2.key2;
create trigger keytesttrigger after update on keytest1 referencing new row as
new_row for each row insert into keytest2 values (new_row.key1, new_row.key2);
-create function keyjoin()
-returns table (
- key1 integer,
- key2 integer,
- key3 integer
-)
-begin
- return table (
- select keytest2.key1, keytest2.key2, keytest1.key3
- from keytest1, keytest2
- where keytest1.key1 = keytest2.key1 and keytest1.key2 = keytest2.key2
- );
-end;
CREATE TABLE "testschema"."selfref" (
"id" INTEGER NOT NULL DEFAULT next value for
"testschema"."selfref_seq",
"parentid" INTEGER,
@@ -101253,6 +101240,19 @@ CREATE TABLE "testschema"."geomtest" (
COPY 2 RECORDS INTO "testschema"."geomtest" FROM stdin USING DELIMITERS
'\t','\n','"';
POINT (10.5000000000000000 12.3000000000000007) LINESTRING
(10.0000000000000000 10.0000000000000000, 20.0000000000000000
20.0000000000000000, 30.0000000000000000 40.0000000000000000) LINESTRING
(10.0000000000000000 10.0000000000000000, 20.0000000000000000
20.0000000000000000, 30.0000000000000000 40.0000000000000000) POLYGON
((10.0000000000000000 10.0000000000000000, 10.0000000000000000
20.0000000000000000, 20.0000000000000000 20.0000000000000000,
20.0000000000000000 15.0000000000000000, 10.0000000000000000
10.0000000000000000)) POLYGON ((10.0000000000000000 10.0000000000000000,
10.0000000000000000 20.0000000000000000, 20.0000000000000000
20.0000000000000000, 20.0000000000000000 15.0000000000000000,
10.0000000000000000 10.0000000000000000)) MULTIPOINT (20.0000000000000000
80.0000000000000000, 110.0000000000000000 160.0000000000000000,
20.0000000000000000 160.0000000000000000) MULTILINESTRING
((0.0000000000000000 0.0000000000000000, 0.0000000000000000
80.0000000000000000, 60.000000000
0000000 80.0000000000000000, 60.0000000000000000 0.0000000000000000,
0.0000000000000000 0.0000000000000000)) MULTILINESTRING ((0.0000000000000000
0.0000000000000000, 0.0000000000000000 80.0000000000000000, 60.0000000000000000
80.0000000000000000, 60.0000000000000000 0.0000000000000000, 0.0000000000000000
0.0000000000000000)) MULTIPOLYGON (((140.0000000000000000
110.0000000000000000, 260.0000000000000000 110.0000000000000000,
170.0000000000000000 20.0000000000000000, 50.0000000000000000
20.0000000000000000, 140.0000000000000000 110.0000000000000000)),
((300.0000000000000000 270.0000000000000000, 420.0000000000000000
270.0000000000000000, 340.0000000000000000 190.0000000000000000,
220.0000000000000000 190.0000000000000000, 300.0000000000000000
270.0000000000000000))) MULTIPOLYGON (((140.0000000000000000
110.0000000000000000, 260.0000000000000000 110.0000000000000000,
170.0000000000000000 20.0000000000000000, 50.0000000000000000
20.0000000000000000, 140.0000000000000000 110.00000000000
00000)), ((300.0000000000000000 270.0000000000000000, 420.0000000000000000
270.0000000000000000, 340.0000000000000000 190.0000000000000000,
220.0000000000000000 190.0000000000000000, 300.0000000000000000
270.0000000000000000))) POLYGON ((10.0000000000000000 10.0000000000000000,
10.0000000000000000 20.0000000000000000, 20.0000000000000000
20.0000000000000000, 20.0000000000000000 15.0000000000000000,
10.0000000000000000 10.0000000000000000)) GEOMETRYCOLLECTION (POLYGON
((0.0000000000000000 0.0000000000000000, 0.0000000000000000
100.0000000000000000, 100.0000000000000000 100.0000000000000000,
100.0000000000000000 0.0000000000000000, 0.0000000000000000
0.0000000000000000)), LINESTRING (10.0000000000000000 10.0000000000000000,
20.0000000000000000 20.0000000000000000, 30.0000000000000000
40.0000000000000000)) BOX (10.000000 10.000000, 20.000000 20.000000)
NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
NULL NULL NULL
+create function keyjoin()
+returns table (
+ key1 integer,
+ key2 integer,
+ key3 integer
+)
+begin
+ return table (
+ select keytest2.key1, keytest2.key2, keytest1.key3
+ from keytest1, keytest2
+ where keytest1.key1 = keytest2.key1 and keytest1.key2 = keytest2.key2
+ );
+end;
SET SCHEMA "sys";
ALTER TABLE "testschema"."keytest2" ADD CONSTRAINT "keytest2_key1_key2_fkey"
FOREIGN KEY ("key1", "key2") REFERENCES "testschema"."keytest1" ("key1",
"key2");
ALTER TABLE "testschema"."selfref" ADD CONSTRAINT "selfref_parentid_fkey"
FOREIGN KEY ("parentid") REFERENCES "testschema"."selfref" ("id");
diff --git a/sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out
b/sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out
--- a/sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out
+++ b/sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out
@@ -36,6 +36,9 @@ set schema "sys";
create function create_view_upgrade_oct2014( sname string, f string )
returns int external name sql.create_view_upgrade_oct2014;
insert into upgradeOct2014_changes select
create_view_upgrade_oct2014(name, query) from upgradeOct2014_views;
+ update _tables set system = true where name in ('tables', 'columns',
'users', 'querylog_catalog', 'querylog_calls', 'querylog_history', 'tracelog',
'sessions', 'optimizers', 'environment', 'queue', 'storage', 'storagemodel',
'tablestoragemodel') and schema_id = (select id from schemas where name =
'sys');
+ insert into systemfunctions (select id from functions where name in
('bbp', 'db_users', 'dependencies_columns_on_functions',
'dependencies_columns_on_indexes', 'dependencies_columns_on_keys',
'dependencies_columns_on_triggers', 'dependencies_columns_on_views',
'dependencies_functions_on_functions', 'dependencies_functions_os_triggers',
'dependencies_keys_on_foreignkeys', 'dependencies_owners_on_schemas',
'dependencies_schemas_on_users', 'dependencies_tables_on_foreignkeys',
'dependencies_tables_on_functions', 'dependencies_tables_on_indexes',
'dependencies_tables_on_triggers', 'dependencies_tables_on_views',
'dependencies_views_on_functions', 'dependencies_views_on_triggers', 'env',
'environment', 'generate_series', 'optimizers', 'optimizer_stats',
'querycache', 'querylog_calls', 'querylog_catalog', 'queue', 'sessions',
'storage', 'storagemodel', 'tojsonarray', 'tracelog', 'var') and schema_id =
(select id from schemas where name = 'sys') and id not in (select function_id
from syst
emfunctions));
+ delete from systemfunctions where function_id not in (select id from
functions);
drop function create_view_upgrade_oct2014;
drop table upgradeOct2014_views;
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
