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 checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list