Changeset: 633e0dff08fd for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=633e0dff08fd
Modified Files:
sql/backends/monet5/sql_upgrades.c
sql/scripts/51_sys_schema_extension.sql
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/emptydb/Tests/check.stable.out
sql/test/emptydb/Tests/check.stable.out.32bit
sql/test/emptydb/Tests/check.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:
Add a language keyword column to the function_languages table.
And stick the upgrade code in a more logical place.
diffs (truncated from 1470 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
@@ -1280,6 +1280,34 @@ sql_update_mar2018(Client c, mvc *sql)
"SET system = TRUE "
"WHERE name = 'privilege_codes' "
"AND schema_id = (SELECT id FROM sys.schemas WHERE name
= 'sys');\n"
+ "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);\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"
+ " AS ft (id, kw) WHERE function_type_id = id);\n"
+ "ALTER TABLE sys.function_types ALTER COLUMN
function_type_keyword SET NOT NULL;\n"
+ "ALTER TABLE sys.function_languages SET READ WRITE;\n"
+ "ALTER TABLE sys.function_languages ADD COLUMN
language_keyword VARCHAR(20);\n"
+ "UPDATE sys.function_languages SET language_keyword =\n"
+ " (SELECT kw FROM (VALUES\n"
+ " (3, 'R'),\n"
+ " (6, 'PYTHON'),\n"
+ " (7, 'PYTHON_MAP'),\n"
+ " (8, 'PYTHON2'),\n"
+ " (9, 'PYTHON2_MAP'),\n"
+ " (10, 'PYTHON3'),\n"
+ " (11, 'PYTHON3_MAP'))\n"
+ " AS ft (id, kw) WHERE language_id = id);\n"
+ "INSERT INTO sys.function_languages VALUES (4, 'C',
'C'), (12, 'C++', 'CPP');\n"
);
/* 60_wlcr.sql */
@@ -1329,21 +1357,6 @@ sql_update_mar2018(Client c, mvc *sql)
"AND schema_id = (SELECT id FROM sys.schemas WHERE name
= 'sys');\n"
"DELETE FROM sys.systemfunctions WHERE function_id IS
NULL;\n"
"ALTER TABLE sys.systemfunctions ALTER COLUMN
function_id SET NOT NULL;\n"
- "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);\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"
- " 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");
@@ -1359,7 +1372,8 @@ sql_update_mar2018(Client c, mvc *sql)
schema = stack_get_string(sql, "current_schema");
pos = snprintf(buf, bufsize, "set schema \"sys\";\n"
"ALTER TABLE sys.keywords SET READ ONLY;\n"
- "ALTER TABLE sys.function_types SET READ
ONLY;\n");
+ "ALTER TABLE sys.function_types SET READ ONLY;\n"
+ "ALTER TABLE sys.function_languages SET READ
ONLY;\n");
if (schema)
pos += snprintf(buf + pos, bufsize - pos, "set schema
\"%s\";\n", schema);
pos += snprintf(buf + pos, bufsize - pos, "commit;\n");
diff --git a/sql/scripts/51_sys_schema_extension.sql
b/sql/scripts/51_sys_schema_extension.sql
--- a/sql/scripts/51_sys_schema_extension.sql
+++ b/sql/scripts/51_sys_schema_extension.sql
@@ -290,24 +290,29 @@ GRANT SELECT ON sys.function_types TO PU
CREATE TABLE sys.function_languages (
- language_id SMALLINT NOT NULL PRIMARY KEY,
- language_name VARCHAR(20) NOT NULL UNIQUE);
+ language_id SMALLINT NOT NULL PRIMARY KEY,
+ language_name VARCHAR(20) NOT NULL UNIQUE,
+ language_keyword VARCHAR(20));
-- Values taken from sql/include/sql_catalog.h see: #define
-- FUNC_LANG_INT 0, FUNC_LANG_MAL 1, FUNC_LANG_SQL 2, FUNC_LANG_R 3,
--- FUNC_LANG_PY 6, FUNC_LANG_MAP_PY 7, FUNC_LANG_PY2 8,
--- FUNC_LANG_MAP_PY2 9, FUNC_LANG_PY3 10, FUNC_LANG_MAP_PY3 11.
-INSERT INTO sys.function_languages (language_id, language_name) VALUES
- (0, 'Internal C'),
- (1, 'MAL'),
- (2, 'SQL'),
- (3, 'R'),
- (6, 'Python'),
- (7, 'Python Mapped'),
- (8, 'Python2'),
- (9, 'Python2 Mapped'),
- (10, 'Python3'),
- (11, 'Python3 Mapped');
+-- FUNC_LANG_C 4, FUNC_LANG_PY 6, FUNC_LANG_MAP_PY 7, FUNC_LANG_PY2 8,
+-- FUNC_LANG_MAP_PY2 9, FUNC_LANG_PY3 10, FUNC_LANG_MAP_PY3 11,
+-- FUNC_LANG_CPP 12.
+INSERT INTO sys.function_languages (language_id, language_name,
language_keyword) VALUES
+ (0, 'Internal C', NULL),
+ (1, 'MAL', NULL),
+ (2, 'SQL', NULL),
+ (3, 'R', 'R'),
+ (4, 'C', 'C'),
+-- (5, 'J', 'J'), -- Javascript? not yet available for use
+ (6, 'Python', 'PYTHON'),
+ (7, 'Python Mapped', 'PYTHON_MAP'),
+ (8, 'Python2', 'PYTHON2'),
+ (9, 'Python2 Mapped', 'PYTHON2_MAP'),
+ (10, 'Python3', 'PYTHON3'),
+ (11, 'Python3 Mapped', 'PYTHON3_MAP'),
+ (12, 'C++', 'CPP');
ALTER TABLE sys.function_languages SET READ ONLY;
GRANT SELECT ON sys.function_languages TO PUBLIC;
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
@@ -5632,6 +5632,34 @@ INSERT INTO sys.privilege_codes (privile
ALTER TABLE sys.privilege_codes SET READ ONLY;
GRANT SELECT ON sys.privilege_codes TO PUBLIC;
UPDATE sys._tables SET system = TRUE WHERE name = 'privilege_codes' AND
schema_id = (SELECT id FROM sys.schemas WHERE name = 'sys');
+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);
+UPDATE sys.function_types SET function_type_keyword =
+ (SELECT kw FROM (VALUES
+ (1, 'FUNCTION'),
+ (2, 'PROCEDURE'),
+ (3, 'AGGREGATE'),
+ (4, 'FILTER FUNCTION'),
+ (5, 'FUNCTION'),
+ (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;
+ALTER TABLE sys.function_languages SET READ WRITE;
+ALTER TABLE sys.function_languages ADD COLUMN language_keyword VARCHAR(20);
+UPDATE sys.function_languages SET language_keyword =
+ (SELECT kw FROM (VALUES
+ (3, 'R'),
+ (6, 'PYTHON'),
+ (7, 'PYTHON_MAP'),
+ (8, 'PYTHON2'),
+ (9, 'PYTHON2_MAP'),
+ (10, 'PYTHON3'),
+ (11, 'PYTHON3_MAP'))
+ AS ft (id, kw) WHERE language_id = id);
+INSERT INTO sys.function_languages VALUES (4, 'C', 'C'), (12, 'C++', 'CPP');
create procedure master()
external name wlc.master;
create procedure master(path string)
@@ -5673,21 +5701,6 @@ WHERE name = 'comments'
AND schema_id = (SELECT id FROM sys.schemas WHERE name = 'sys');
DELETE FROM sys.systemfunctions WHERE function_id IS NULL;
ALTER TABLE sys.systemfunctions ALTER COLUMN function_id SET NOT NULL;
-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);
-UPDATE sys.function_types SET function_type_keyword =
- (SELECT kw FROM (VALUES
- (1, 'FUNCTION'),
- (2, 'PROCEDURE'),
- (3, 'AGGREGATE'),
- (4, 'FILTER FUNCTION'),
- (5, 'FUNCTION'),
- (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;
@@ -5696,6 +5709,7 @@ Running database upgrade commands:
set schema "sys";
ALTER TABLE sys.keywords SET READ ONLY;
ALTER TABLE sys.function_types SET READ ONLY;
+ALTER TABLE sys.function_languages SET READ ONLY;
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
@@ -5627,6 +5627,34 @@ INSERT INTO sys.privilege_codes (privile
ALTER TABLE sys.privilege_codes SET READ ONLY;
GRANT SELECT ON sys.privilege_codes TO PUBLIC;
UPDATE sys._tables SET system = TRUE WHERE name = 'privilege_codes' AND
schema_id = (SELECT id FROM sys.schemas WHERE name = 'sys');
+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);
+UPDATE sys.function_types SET function_type_keyword =
+ (SELECT kw FROM (VALUES
+ (1, 'FUNCTION'),
+ (2, 'PROCEDURE'),
+ (3, 'AGGREGATE'),
+ (4, 'FILTER FUNCTION'),
+ (5, 'FUNCTION'),
+ (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;
+ALTER TABLE sys.function_languages SET READ WRITE;
+ALTER TABLE sys.function_languages ADD COLUMN language_keyword VARCHAR(20);
+UPDATE sys.function_languages SET language_keyword =
+ (SELECT kw FROM (VALUES
+ (3, 'R'),
+ (6, 'PYTHON'),
+ (7, 'PYTHON_MAP'),
+ (8, 'PYTHON2'),
+ (9, 'PYTHON2_MAP'),
+ (10, 'PYTHON3'),
+ (11, 'PYTHON3_MAP'))
+ AS ft (id, kw) WHERE language_id = id);
+INSERT INTO sys.function_languages VALUES (4, 'C', 'C'), (12, 'C++', 'CPP');
create procedure master()
external name wlc.master;
create procedure master(path string)
@@ -5668,21 +5696,6 @@ WHERE name = 'comments'
AND schema_id = (SELECT id FROM sys.schemas WHERE name = 'sys');
DELETE FROM sys.systemfunctions WHERE function_id IS NULL;
ALTER TABLE sys.systemfunctions ALTER COLUMN function_id SET NOT NULL;
-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);
-UPDATE sys.function_types SET function_type_keyword =
- (SELECT kw FROM (VALUES
- (1, 'FUNCTION'),
- (2, 'PROCEDURE'),
- (3, 'AGGREGATE'),
- (4, 'FILTER FUNCTION'),
- (5, 'FUNCTION'),
- (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;
@@ -5691,6 +5704,7 @@ Running database upgrade commands:
set schema "sys";
ALTER TABLE sys.keywords SET READ ONLY;
ALTER TABLE sys.function_types SET READ ONLY;
+ALTER TABLE sys.function_languages SET READ ONLY;
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
@@ -4919,6 +4919,34 @@ INSERT INTO sys.privilege_codes (privile
ALTER TABLE sys.privilege_codes SET READ ONLY;
GRANT SELECT ON sys.privilege_codes TO PUBLIC;
UPDATE sys._tables SET system = TRUE WHERE name = 'privilege_codes' AND
schema_id = (SELECT id FROM sys.schemas WHERE name = 'sys');
+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);
+UPDATE sys.function_types SET function_type_keyword =
+ (SELECT kw FROM (VALUES
+ (1, 'FUNCTION'),
+ (2, 'PROCEDURE'),
+ (3, 'AGGREGATE'),
+ (4, 'FILTER FUNCTION'),
+ (5, 'FUNCTION'),
+ (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;
+ALTER TABLE sys.function_languages SET READ WRITE;
+ALTER TABLE sys.function_languages ADD COLUMN language_keyword VARCHAR(20);
+UPDATE sys.function_languages SET language_keyword =
+ (SELECT kw FROM (VALUES
+ (3, 'R'),
+ (6, 'PYTHON'),
+ (7, 'PYTHON_MAP'),
+ (8, 'PYTHON2'),
+ (9, 'PYTHON2_MAP'),
+ (10, 'PYTHON3'),
+ (11, 'PYTHON3_MAP'))
+ AS ft (id, kw) WHERE language_id = id);
+INSERT INTO sys.function_languages VALUES (4, 'C', 'C'), (12, 'C++', 'CPP');
create procedure master()
external name wlc.master;
create procedure master(path string)
@@ -4960,21 +4988,6 @@ WHERE name = 'comments'
AND schema_id = (SELECT id FROM sys.schemas WHERE name = 'sys');
DELETE FROM sys.systemfunctions WHERE function_id IS NULL;
ALTER TABLE sys.systemfunctions ALTER COLUMN function_id SET NOT NULL;
-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);
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
