Changeset: 81f2d6ee26ab for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=81f2d6ee26ab
Modified Files:
sql/backends/monet5/sql_upgrades.c
sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.powerpc64
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.32bit
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.powerpc64
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.powerpc64.int128
sql/test/emptydb-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade/Tests/upgrade.stable.out
sql/test/emptydb-upgrade/Tests/upgrade.stable.out.32bit
sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out
sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.32bit
sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade/Tests/upgrade.stable.out
sql/test/testdb-upgrade/Tests/upgrade.stable.out.32bit
sql/test/testdb-upgrade/Tests/upgrade.stable.out.int128
Branch: Mar2018
Log Message:
Make upgraded table sys.function_types look like new.
I.e., no DEFAULT '' for function_type_keyword.
diffs (truncated from 410 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
@@ -1323,17 +1323,18 @@ sql_update_mar2018(Client c, mvc *sql)
"ALTER TABLE sys.keywords SET READ WRITE;\n"
"INSERT INTO sys.keywords VALUES ('COMMENT'),
('CONTINUE'), ('START'), ('TRUNCATE');\n"
"ALTER TABLE sys.function_types SET READ WRITE;\n"
- "ALTER TABLE function_types ADD COLUMN
function_type_keyword VARCHAR(30) NOT NULL DEFAULT '';\n"
+ "ALTER TABLE function_types ADD COLUMN
function_type_keyword VARCHAR(30);\n"
"UPDATE sys.function_types SET function_type_keyword
=\n"
" (SELECT kw FROM (VALUES\n"
- " (1, \'FUNCTION\'),\n"
- " (2, \'PROCEDURE\'),\n"
- " (3, \'AGGREGATE\'),\n"
- " (4, \'FILTER FUNCTION\'),\n"
- " (5, \'FUNCTION\'),\n"
- " (6, \'FUNCTION\'),\n"
- " (7, \'LOADER\'))\n"
+ " (1, 'FUNCTION'),\n"
+ " (2, 'PROCEDURE'),\n"
+ " (3, 'AGGREGATE'),\n"
+ " (4, 'FILTER FUNCTION'),\n"
+ " (5, 'FUNCTION'),\n"
+ " (6, 'FUNCTION'),\n"
+ " (7, 'LOADER'))\n"
" AS ft (id, kw) WHERE function_type_id = id);\n"
+ "ALTER TABLE sys.function_types ALTER COLUMN
function_type_keyword SET NOT NULL;\n"
);
pos += snprintf(buf + pos, bufsize - pos,
"delete from sys.systemfunctions where function_id not
in (select id from sys.functions);\n");
diff --git a/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
@@ -5675,7 +5675,7 @@ ALTER TABLE sys.systemfunctions ALTER CO
ALTER TABLE sys.keywords SET READ WRITE;
INSERT INTO sys.keywords VALUES ('COMMENT'), ('CONTINUE'), ('START'),
('TRUNCATE');
ALTER TABLE sys.function_types SET READ WRITE;
-ALTER TABLE function_types ADD COLUMN function_type_keyword VARCHAR(30) NOT
NULL DEFAULT '';
+ALTER TABLE function_types ADD COLUMN function_type_keyword VARCHAR(30);
UPDATE sys.function_types SET function_type_keyword =
(SELECT kw FROM (VALUES
(1, 'FUNCTION'),
@@ -5686,6 +5686,7 @@ UPDATE sys.function_types SET function_t
(6, 'FUNCTION'),
(7, 'LOADER'))
AS ft (id, kw) WHERE function_type_id = id);
+ALTER TABLE sys.function_types ALTER COLUMN function_type_keyword SET NOT NULL;
delete from sys.systemfunctions where function_id not in (select id from
sys.functions);
set schema "sys";
commit;
diff --git
a/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.powerpc64
b/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.powerpc64
--- a/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.powerpc64
+++ b/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.powerpc64
@@ -5670,7 +5670,7 @@ ALTER TABLE sys.systemfunctions ALTER CO
ALTER TABLE sys.keywords SET READ WRITE;
INSERT INTO sys.keywords VALUES ('COMMENT'), ('CONTINUE'), ('START'),
('TRUNCATE');
ALTER TABLE sys.function_types SET READ WRITE;
-ALTER TABLE function_types ADD COLUMN function_type_keyword VARCHAR(30) NOT
NULL DEFAULT '';
+ALTER TABLE function_types ADD COLUMN function_type_keyword VARCHAR(30);
UPDATE sys.function_types SET function_type_keyword =
(SELECT kw FROM (VALUES
(1, 'FUNCTION'),
@@ -5681,6 +5681,7 @@ UPDATE sys.function_types SET function_t
(6, 'FUNCTION'),
(7, 'LOADER'))
AS ft (id, kw) WHERE function_type_id = id);
+ALTER TABLE sys.function_types ALTER COLUMN function_type_keyword SET NOT NULL;
delete from sys.systemfunctions where function_id not in (select id from
sys.functions);
set schema "sys";
commit;
diff --git a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
--- a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
+++ b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
@@ -4962,7 +4962,7 @@ ALTER TABLE sys.systemfunctions ALTER CO
ALTER TABLE sys.keywords SET READ WRITE;
INSERT INTO sys.keywords VALUES ('COMMENT'), ('CONTINUE'), ('START'),
('TRUNCATE');
ALTER TABLE sys.function_types SET READ WRITE;
-ALTER TABLE function_types ADD COLUMN function_type_keyword VARCHAR(30) NOT
NULL DEFAULT '';
+ALTER TABLE function_types ADD COLUMN function_type_keyword VARCHAR(30);
UPDATE sys.function_types SET function_type_keyword =
(SELECT kw FROM (VALUES
(1, 'FUNCTION'),
@@ -4973,6 +4973,7 @@ UPDATE sys.function_types SET function_t
(6, 'FUNCTION'),
(7, 'LOADER'))
AS ft (id, kw) WHERE function_type_id = id);
+ALTER TABLE sys.function_types ALTER COLUMN function_type_keyword SET NOT NULL;
delete from sys.systemfunctions where function_id not in (select id from
sys.functions);
set schema "sys";
commit;
diff --git a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.32bit
b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.32bit
--- a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.32bit
+++ b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.32bit
@@ -4962,7 +4962,7 @@ ALTER TABLE sys.systemfunctions ALTER CO
ALTER TABLE sys.keywords SET READ WRITE;
INSERT INTO sys.keywords VALUES ('COMMENT'), ('CONTINUE'), ('START'),
('TRUNCATE');
ALTER TABLE sys.function_types SET READ WRITE;
-ALTER TABLE function_types ADD COLUMN function_type_keyword VARCHAR(30) NOT
NULL DEFAULT '';
+ALTER TABLE function_types ADD COLUMN function_type_keyword VARCHAR(30);
UPDATE sys.function_types SET function_type_keyword =
(SELECT kw FROM (VALUES
(1, 'FUNCTION'),
@@ -4973,6 +4973,7 @@ UPDATE sys.function_types SET function_t
(6, 'FUNCTION'),
(7, 'LOADER'))
AS ft (id, kw) WHERE function_type_id = id);
+ALTER TABLE sys.function_types ALTER COLUMN function_type_keyword SET NOT NULL;
delete from sys.systemfunctions where function_id not in (select id from
sys.functions);
set schema "sys";
commit;
diff --git a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
@@ -5725,7 +5725,7 @@ ALTER TABLE sys.systemfunctions ALTER CO
ALTER TABLE sys.keywords SET READ WRITE;
INSERT INTO sys.keywords VALUES ('COMMENT'), ('CONTINUE'), ('START'),
('TRUNCATE');
ALTER TABLE sys.function_types SET READ WRITE;
-ALTER TABLE function_types ADD COLUMN function_type_keyword VARCHAR(30) NOT
NULL DEFAULT '';
+ALTER TABLE function_types ADD COLUMN function_type_keyword VARCHAR(30);
UPDATE sys.function_types SET function_type_keyword =
(SELECT kw FROM (VALUES
(1, 'FUNCTION'),
@@ -5736,6 +5736,7 @@ UPDATE sys.function_types SET function_t
(6, 'FUNCTION'),
(7, 'LOADER'))
AS ft (id, kw) WHERE function_type_id = id);
+ALTER TABLE sys.function_types ALTER COLUMN function_type_keyword SET NOT NULL;
delete from sys.systemfunctions where function_id not in (select id from
sys.functions);
set schema "sys";
commit;
diff --git a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.powerpc64
b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.powerpc64
--- a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.powerpc64
+++ b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.powerpc64
@@ -4962,7 +4962,7 @@ ALTER TABLE sys.systemfunctions ALTER CO
ALTER TABLE sys.keywords SET READ WRITE;
INSERT INTO sys.keywords VALUES ('COMMENT'), ('CONTINUE'), ('START'),
('TRUNCATE');
ALTER TABLE sys.function_types SET READ WRITE;
-ALTER TABLE function_types ADD COLUMN function_type_keyword VARCHAR(30) NOT
NULL DEFAULT '';
+ALTER TABLE function_types ADD COLUMN function_type_keyword VARCHAR(30);
UPDATE sys.function_types SET function_type_keyword =
(SELECT kw FROM (VALUES
(1, 'FUNCTION'),
@@ -4973,6 +4973,7 @@ UPDATE sys.function_types SET function_t
(6, 'FUNCTION'),
(7, 'LOADER'))
AS ft (id, kw) WHERE function_type_id = id);
+ALTER TABLE sys.function_types ALTER COLUMN function_type_keyword SET NOT NULL;
delete from sys.systemfunctions where function_id not in (select id from
sys.functions);
set schema "sys";
commit;
diff --git
a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.powerpc64.int128
b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.powerpc64.int128
--- a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.powerpc64.int128
+++ b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.powerpc64.int128
@@ -5725,7 +5725,7 @@ ALTER TABLE sys.systemfunctions ALTER CO
ALTER TABLE sys.keywords SET READ WRITE;
INSERT INTO sys.keywords VALUES ('COMMENT'), ('CONTINUE'), ('START'),
('TRUNCATE');
ALTER TABLE sys.function_types SET READ WRITE;
-ALTER TABLE function_types ADD COLUMN function_type_keyword VARCHAR(30) NOT
NULL DEFAULT '';
+ALTER TABLE function_types ADD COLUMN function_type_keyword VARCHAR(30);
UPDATE sys.function_types SET function_type_keyword =
(SELECT kw FROM (VALUES
(1, 'FUNCTION'),
@@ -5736,6 +5736,7 @@ UPDATE sys.function_types SET function_t
(6, 'FUNCTION'),
(7, 'LOADER'))
AS ft (id, kw) WHERE function_type_id = id);
+ALTER TABLE sys.function_types ALTER COLUMN function_type_keyword SET NOT NULL;
delete from sys.systemfunctions where function_id not in (select id from
sys.functions);
set schema "sys";
commit;
diff --git a/sql/test/emptydb-upgrade-hge/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-upgrade-hge/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-upgrade-hge/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-upgrade-hge/Tests/upgrade.stable.out.int128
@@ -5675,7 +5675,7 @@ ALTER TABLE sys.systemfunctions ALTER CO
ALTER TABLE sys.keywords SET READ WRITE;
INSERT INTO sys.keywords VALUES ('COMMENT'), ('CONTINUE'), ('START'),
('TRUNCATE');
ALTER TABLE sys.function_types SET READ WRITE;
-ALTER TABLE function_types ADD COLUMN function_type_keyword VARCHAR(30) NOT
NULL DEFAULT '';
+ALTER TABLE function_types ADD COLUMN function_type_keyword VARCHAR(30);
UPDATE sys.function_types SET function_type_keyword =
(SELECT kw FROM (VALUES
(1, 'FUNCTION'),
@@ -5686,6 +5686,7 @@ UPDATE sys.function_types SET function_t
(6, 'FUNCTION'),
(7, 'LOADER'))
AS ft (id, kw) WHERE function_type_id = id);
+ALTER TABLE sys.function_types ALTER COLUMN function_type_keyword SET NOT NULL;
delete from sys.systemfunctions where function_id not in (select id from
sys.functions);
set schema "sys";
commit;
diff --git a/sql/test/emptydb-upgrade/Tests/upgrade.stable.out
b/sql/test/emptydb-upgrade/Tests/upgrade.stable.out
--- a/sql/test/emptydb-upgrade/Tests/upgrade.stable.out
+++ b/sql/test/emptydb-upgrade/Tests/upgrade.stable.out
@@ -4962,7 +4962,7 @@ ALTER TABLE sys.systemfunctions ALTER CO
ALTER TABLE sys.keywords SET READ WRITE;
INSERT INTO sys.keywords VALUES ('COMMENT'), ('CONTINUE'), ('START'),
('TRUNCATE');
ALTER TABLE sys.function_types SET READ WRITE;
-ALTER TABLE function_types ADD COLUMN function_type_keyword VARCHAR(30) NOT
NULL DEFAULT '';
+ALTER TABLE function_types ADD COLUMN function_type_keyword VARCHAR(30);
UPDATE sys.function_types SET function_type_keyword =
(SELECT kw FROM (VALUES
(1, 'FUNCTION'),
@@ -4973,6 +4973,7 @@ UPDATE sys.function_types SET function_t
(6, 'FUNCTION'),
(7, 'LOADER'))
AS ft (id, kw) WHERE function_type_id = id);
+ALTER TABLE sys.function_types ALTER COLUMN function_type_keyword SET NOT NULL;
delete from sys.systemfunctions where function_id not in (select id from
sys.functions);
set schema "sys";
commit;
diff --git a/sql/test/emptydb-upgrade/Tests/upgrade.stable.out.32bit
b/sql/test/emptydb-upgrade/Tests/upgrade.stable.out.32bit
--- a/sql/test/emptydb-upgrade/Tests/upgrade.stable.out.32bit
+++ b/sql/test/emptydb-upgrade/Tests/upgrade.stable.out.32bit
@@ -4962,7 +4962,7 @@ ALTER TABLE sys.systemfunctions ALTER CO
ALTER TABLE sys.keywords SET READ WRITE;
INSERT INTO sys.keywords VALUES ('COMMENT'), ('CONTINUE'), ('START'),
('TRUNCATE');
ALTER TABLE sys.function_types SET READ WRITE;
-ALTER TABLE function_types ADD COLUMN function_type_keyword VARCHAR(30) NOT
NULL DEFAULT '';
+ALTER TABLE function_types ADD COLUMN function_type_keyword VARCHAR(30);
UPDATE sys.function_types SET function_type_keyword =
(SELECT kw FROM (VALUES
(1, 'FUNCTION'),
@@ -4973,6 +4973,7 @@ UPDATE sys.function_types SET function_t
(6, 'FUNCTION'),
(7, 'LOADER'))
AS ft (id, kw) WHERE function_type_id = id);
+ALTER TABLE sys.function_types ALTER COLUMN function_type_keyword SET NOT NULL;
delete from sys.systemfunctions where function_id not in (select id from
sys.functions);
set schema "sys";
commit;
diff --git a/sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
@@ -5725,7 +5725,7 @@ ALTER TABLE sys.systemfunctions ALTER CO
ALTER TABLE sys.keywords SET READ WRITE;
INSERT INTO sys.keywords VALUES ('COMMENT'), ('CONTINUE'), ('START'),
('TRUNCATE');
ALTER TABLE sys.function_types SET READ WRITE;
-ALTER TABLE function_types ADD COLUMN function_type_keyword VARCHAR(30) NOT
NULL DEFAULT '';
+ALTER TABLE function_types ADD COLUMN function_type_keyword VARCHAR(30);
UPDATE sys.function_types SET function_type_keyword =
(SELECT kw FROM (VALUES
(1, 'FUNCTION'),
@@ -5736,6 +5736,7 @@ UPDATE sys.function_types SET function_t
(6, 'FUNCTION'),
(7, 'LOADER'))
AS ft (id, kw) WHERE function_type_id = id);
+ALTER TABLE sys.function_types ALTER COLUMN function_type_keyword SET NOT NULL;
delete from sys.systemfunctions where function_id not in (select id from
sys.functions);
set schema "sys";
commit;
diff --git a/sql/test/testdb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
b/sql/test/testdb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
--- a/sql/test/testdb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
+++ b/sql/test/testdb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
@@ -5673,7 +5673,7 @@ ALTER TABLE sys.systemfunctions ALTER CO
ALTER TABLE sys.keywords SET READ WRITE;
INSERT INTO sys.keywords VALUES ('COMMENT'), ('CONTINUE'), ('START'),
('TRUNCATE');
ALTER TABLE sys.function_types SET READ WRITE;
-ALTER TABLE function_types ADD COLUMN function_type_keyword VARCHAR(30) NOT
NULL DEFAULT '';
+ALTER TABLE function_types ADD COLUMN function_type_keyword VARCHAR(30);
UPDATE sys.function_types SET function_type_keyword =
(SELECT kw FROM (VALUES
(1, 'FUNCTION'),
@@ -5684,6 +5684,7 @@ UPDATE sys.function_types SET function_t
(6, 'FUNCTION'),
(7, 'LOADER'))
AS ft (id, kw) WHERE function_type_id = id);
+ALTER TABLE sys.function_types ALTER COLUMN function_type_keyword SET NOT NULL;
delete from sys.systemfunctions where function_id not in (select id from
sys.functions);
set schema "testschema";
commit;
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
@@ -4957,7 +4957,7 @@ ALTER TABLE sys.systemfunctions ALTER CO
ALTER TABLE sys.keywords SET READ WRITE;
INSERT INTO sys.keywords VALUES ('COMMENT'), ('CONTINUE'), ('START'),
('TRUNCATE');
ALTER TABLE sys.function_types SET READ WRITE;
-ALTER TABLE function_types ADD COLUMN function_type_keyword VARCHAR(30) NOT
NULL DEFAULT '';
+ALTER TABLE function_types ADD COLUMN function_type_keyword VARCHAR(30);
UPDATE sys.function_types SET function_type_keyword =
(SELECT kw FROM (VALUES
(1, 'FUNCTION'),
@@ -4968,6 +4968,7 @@ UPDATE sys.function_types SET function_t
(6, 'FUNCTION'),
(7, 'LOADER'))
AS ft (id, kw) WHERE function_type_id = id);
+ALTER TABLE sys.function_types ALTER COLUMN function_type_keyword SET NOT NULL;
delete from sys.systemfunctions where function_id not in (select id from
sys.functions);
set schema "testschema";
commit;
diff --git a/sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.32bit
b/sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.32bit
--- a/sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.32bit
+++ b/sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.32bit
@@ -4957,7 +4957,7 @@ ALTER TABLE sys.systemfunctions ALTER CO
ALTER TABLE sys.keywords SET READ WRITE;
INSERT INTO sys.keywords VALUES ('COMMENT'), ('CONTINUE'), ('START'),
('TRUNCATE');
ALTER TABLE sys.function_types SET READ WRITE;
-ALTER TABLE function_types ADD COLUMN function_type_keyword VARCHAR(30) NOT
NULL DEFAULT '';
+ALTER TABLE function_types ADD COLUMN function_type_keyword VARCHAR(30);
UPDATE sys.function_types SET function_type_keyword =
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
