Changeset: bd62c72ed60a for MonetDB URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=bd62c72ed60a Added Files: ChangeLog.sqlextra Modified Files: sql/backends/monet5/sql_upgrades.c sql/scripts/51_sys_schema_extension.sql Branch: sqlextra Log Message:
Handle upgrades properly diffs (125 lines): diff --git a/ChangeLog.sqlextra b/ChangeLog.sqlextra new file mode 100644 --- /dev/null +++ b/ChangeLog.sqlextra @@ -0,0 +1,14 @@ +# ChangeLog file for MonetDB-sqlextra +# This file is updated with Maddlog + +* Wed Aug 30 2017 Pedro Ferreira <[email protected]> +- A column default value can be used in a UPDATE statement: UPDATE tname + SET cname = DEFAULT, and INSERT statements: INSERT INTO tname VALUES + (..., DEFAULT, ...) +- Added TRUNCATE statements into the SQL catalog, conforming to the SQL + standard. In a TRUNCATE statement a 'CONTINUE IDENTITY' or 'RESTART + IDENTITY' clause can be passed mrestart or not, being the former the + default one. The options 'RESTRICT' and 'CASCADE' tell if the table has + foreign key references, the referencing tables should be truncated as + well. This happens on 'CASCADE' and 'RESTRICT' is the default behavior. + 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 @@ -764,16 +764,10 @@ sql_update_jul2017(Client c, mvc *sql) "privilege_code_id INT NOT NULL PRIMARY KEY,\n" "privilege_code_name VARCHAR(30) NOT NULL UNIQUE);\n" "INSERT INTO sys.privilege_codes (privilege_code_id, privilege_code_name) VALUES\n" - "(1, 'SELECT'), (2, 'UPDATE'), (4, 'INSERT'), (8, 'DELETE'), (16, 'EXECUTE'), (32, 'GRANT'), (64, 'TRUNCATE'),\n" + "(1, 'SELECT'), (2, 'UPDATE'), (4, 'INSERT'), (8, 'DELETE'), (16, 'EXECUTE'), (32, 'GRANT'),\n" "(3, 'SELECT,UPDATE'), (5, 'SELECT,INSERT'), (6, 'INSERT,UPDATE'), (7, 'SELECT,INSERT,UPDATE'),\n" "(9, 'SELECT,DELETE'), (10, 'UPDATE,DELETE'), (11, 'SELECT,UPDATE,DELETE'), (12, 'INSERT,DELETE'),\n" - "(13, 'SELECT,INSERT,DELETE'), (14, 'INSERT,UPDATE,DELETE'), (15, 'SELECT,INSERT,UPDATE,DELETE'),\n" - "(65, 'SELECT,TRUNCATE'), (66, 'UPDATE,TRUNCATE'), (68, 'INSERT,TRUNCATE'), (72, 'DELETE,TRUNCATE'),\n" - "(67, 'SELECT,UPDATE,TRUNCATE'), (69, 'SELECT,INSERT,TRUNCATE'), (73, 'SELECT,DELETE,TRUNCATE'),\n" - "(70, 'INSERT,UPDATE,TRUNCATE'), (76, 'INSERT,DELETE,TRUNCATE'), (74, 'UPDATE,DELETE,TRUNCATE'),\n" - "(71, 'SELECT,INSERT,UPDATE,TRUNCATE'), (75, 'SELECT,UPDATE,DELETE,TRUNCATE'),\n" - "(77, 'SELECT,INSERT,DELETE,TRUNCATE'), (78, 'INSERT,UPDATE,DELETE,TRUNCATE'),\n" - "(79, 'SELECT,INSERT,UPDATE,DELETE,TRUNCATE');\n" + "(13, 'SELECT,INSERT,DELETE'), (14, 'INSERT,UPDATE,DELETE'), (15, 'SELECT,INSERT,UPDATE,DELETE');\n" "ALTER TABLE sys.privilege_codes SET READ ONLY;\n" "update sys._tables set system = true where name in ('function_languages', 'function_types', 'index_types', 'key_types', 'privilege_codes') and schema_id = (select id from sys.schemas where name = 'sys');\n"); @@ -809,6 +803,55 @@ sql_update_jul2017(Client c, mvc *sql) return err; /* usually MAL_SUCCEED */ } +static str +sql_extra_upgrade(Client c, mvc *sql) +{ + size_t bufsize = 10000, pos = 0; + char *buf, *err = NULL, *schema = stack_get_string(sql, "current_schema"); + char *q1 = "select privilege_code_id from sys.privilege_codes where privilege_code_id = 64;\n"; + res_table *output; + BAT *b; + int upgrade = 0; + + err = SQLstatementIntern(c, &q1, "update", 1, 0, &output); + + b = BATdescriptor(output->cols[0].b); + if (b) { + if (BATcount(b) > 0) { + upgrade = 1; + } + BBPunfix(b->batCacheid); + } + res_tables_destroy(output); + + if(upgrade) { + if((buf = GDKmalloc(bufsize)) == NULL) + throw(SQL, "sql_default", MAL_MALLOC_FAIL); + + pos += snprintf(buf + pos, bufsize - pos, + "ALTER TABLE sys.privilege_codes SET READ WRITE;\n" + "INSERT INTO sys.privilege_codes (privilege_code_id, privilege_code_name) VALUES\n" + "(64, 'TRUNCATE'),\n" + "(65, 'SELECT,TRUNCATE'), (66, 'UPDATE,TRUNCATE'), (68, 'INSERT,TRUNCATE'), (72, 'DELETE,TRUNCATE'),\n" + "(67, 'SELECT,UPDATE,TRUNCATE'), (69, 'SELECT,INSERT,TRUNCATE'), (73, 'SELECT,DELETE,TRUNCATE'),\n" + "(70, 'INSERT,UPDATE,TRUNCATE'), (76, 'INSERT,DELETE,TRUNCATE'), (74, 'UPDATE,DELETE,TRUNCATE'),\n" + "(71, 'SELECT,INSERT,UPDATE,TRUNCATE'), (75, 'SELECT,UPDATE,DELETE,TRUNCATE'),\n" + "(77, 'SELECT,INSERT,DELETE,TRUNCATE'), (78, 'INSERT,UPDATE,DELETE,TRUNCATE'),\n" + "(79, 'SELECT,INSERT,UPDATE,DELETE,TRUNCATE');\n" + "ALTER TABLE sys.privilege_codes SET READ ONLY;\n"); + + if (schema) + pos += snprintf(buf + pos, bufsize - pos, "set schema \"%s\";\n", schema); + + assert(pos < bufsize); + + printf("Running database upgrade commands:\n%s\n", buf); + err = SQLstatementIntern(c, &buf, "update", 1, 0, NULL); + GDKfree(buf); + } + return err; +} + void SQLupgrades(Client c, mvc *m) { @@ -896,4 +939,9 @@ SQLupgrades(Client c, mvc *m) freeException(err); } } + + if ((err = sql_extra_upgrade(c, m)) != NULL) { + fprintf(stderr, "!%s\n", err); + freeException(err); + } } 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 @@ -116,12 +116,13 @@ CREATE TABLE sys.privilege_codes ( -- Values taken from sql/include/sql_catalog.h see: #define PRIV_SELECT 1, PRIV_UPDATE 2, PRIV_INSERT 4, PRIV_DELETE 8, PRIV_EXECUTE 16, PRIV_GRANT 32, PRIV_TRUNCATE 64 INSERT INTO sys.privilege_codes (privilege_code_id, privilege_code_name) VALUES - (1, 'SELECT'), (2, 'UPDATE'), (4, 'INSERT'), (8, 'DELETE'), (16, 'EXECUTE'), (32, 'GRANT'), (64, 'TRUNCATE'), + (1, 'SELECT'), (2, 'UPDATE'), (4, 'INSERT'), (8, 'DELETE'), (16, 'EXECUTE'), (32, 'GRANT'), -- next are combined privileges applicable only to tables and columns (3, 'SELECT,UPDATE'), (5, 'SELECT,INSERT'), (6, 'INSERT,UPDATE'), (7, 'SELECT,INSERT,UPDATE'), (9, 'SELECT,DELETE'), (10, 'UPDATE,DELETE'), (11, 'SELECT,UPDATE,DELETE'), (12, 'INSERT,DELETE'), (13, 'SELECT,INSERT,DELETE'), (14, 'INSERT,UPDATE,DELETE'), (15, 'SELECT,INSERT,UPDATE,DELETE'), + (64, 'TRUNCATE'), (65, 'SELECT,TRUNCATE'), (66, 'UPDATE,TRUNCATE'), (68, 'INSERT,TRUNCATE'), (72, 'DELETE,TRUNCATE'), (67, 'SELECT,UPDATE,TRUNCATE'), (69, 'SELECT,INSERT,TRUNCATE'), (73, 'SELECT,DELETE,TRUNCATE'), (70, 'INSERT,UPDATE,TRUNCATE'), (76, 'INSERT,DELETE,TRUNCATE'), (74, 'UPDATE,DELETE,TRUNCATE'), _______________________________________________ checkin-list mailing list [email protected] https://www.monetdb.org/mailman/listinfo/checkin-list
