1/*
2 * This Source Code Form is subject to the terms of the Mozilla Public
3 * License, v. 2.0. If a copy of the MPL was not distributed with this
4 * file, You can obtain one at http://mozilla.org/MPL/2.0/.
5 *
6 * Copyright 1997 - July 2008 CWI, August 2008 - 2019 MonetDB B.V.
7 */
8
9/*
10 * SQL upgrade code
11 * N. Nes, M.L. Kersten, S. Mullender
12 */
13#include "monetdb_config.h"
14#include "mal_backend.h"
15#include "sql_execute.h"
16#include "sql_mvc.h"
17#include "mtime.h"
18#include <unistd.h>
19#include "sql_upgrades.h"
20#include "rel_rel.h"
21#include "rel_semantic.h"
22#include "rel_unnest.h"
23#include "rel_optimizer.h"
24
25#include "rel_remote.h"
26#include "mal_authorize.h"
27
28/* this function can be used to recreate the system tables (types,
29 * functions, args) when internal types and/or functions have changed
30 * (i.e. the ones in sql_types.c) */
31static str
32sql_fix_system_tables(Client c, mvc *sql, const char *prev_schema)
33{
34 size_t bufsize = 1000000, pos = 0;
35 char *buf = GDKmalloc(bufsize), *err = NULL;
36 node *n;
37 sql_schema *s;
38
39 if (buf == NULL)
40 throw(SQL, "sql_fix_system_tables", SQLSTATE(HY001) MAL_MALLOC_FAIL);
41 s = mvc_bind_schema(sql, "sys");
42 pos += snprintf(buf + pos, bufsize - pos, "set schema \"sys\";\n");
43
44 pos += snprintf(buf + pos, bufsize - pos,
45 "delete from sys.dependencies where id < 2000;\n");
46
47 /* recreate internal types */
48 pos += snprintf(buf + pos, bufsize - pos,
49 "delete from sys.types where id < 2000;\n");
50 for (n = types->h; n; n = n->next) {
51 sql_type *t = n->data;
52
53 if (t->base.id >= FUNC_OIDS)
54 continue;
55
56 pos += snprintf(buf + pos, bufsize - pos,
57 "insert into sys.types values"
58 " (%d, '%s', '%s', %u, %u, %d, %d, %d);\n",
59 t->base.id, t->base.name, t->sqlname, t->digits,
60 t->scale, t->radix, (int) t->eclass,
61 t->s ? t->s->base.id : s->base.id);
62 }
63
64 /* recreate internal functions */
65 pos += snprintf(buf + pos, bufsize - pos,
66 "delete from sys.functions where id < 2000;\n"
67 "delete from sys.args where func_id not in"
68 " (select id from sys.functions);\n");
69 for (n = funcs->h; n; n = n->next) {
70 sql_func *func = n->data;
71 int number = 0;
72 sql_arg *arg;
73 node *m;
74
75 if (func->base.id >= FUNC_OIDS)
76 continue;
77
78 pos += snprintf(buf + pos, bufsize - pos,
79 "insert into sys.functions values"
80 " (%d, '%s', '%s', '%s',"
81 " %d, %d, %s, %s, %s, %d, %s);\n",
82 func->base.id, func->base.name,
83 func->imp, func->mod, (int) FUNC_LANG_INT,
84 (int) func->type,
85 func->side_effect ? "true" : "false",
86 func->varres ? "true" : "false",
87 func->vararg ? "true" : "false",
88 func->s ? func->s->base.id : s->base.id,
89 func->system ? "true" : "false");
90 if (func->res) {
91 for (m = func->res->h; m; m = m->next, number++) {
92 arg = m->data;
93 pos += snprintf(buf + pos, bufsize - pos,
94 "insert into sys.args"
95 " values"
96 " (%d, %d, 'res_%d',"
97 " '%s', %u, %u, %d,"
98 " %d);\n",
99 store_next_oid(),
100 func->base.id,
101 number,
102 arg->type.type->sqlname,
103 arg->type.digits,
104 arg->type.scale,
105 arg->inout, number);
106 }
107 }
108 for (m = func->ops->h; m; m = m->next, number++) {
109 arg = m->data;
110 if (arg->name)
111 pos += snprintf(buf + pos, bufsize - pos,
112 "insert into sys.args"
113 " values"
114 " (%d, %d, '%s', '%s',"
115 " %u, %u, %d, %d);\n",
116 store_next_oid(),
117 func->base.id,
118 arg->name,
119 arg->type.type->sqlname,
120 arg->type.digits,
121 arg->type.scale,
122 arg->inout, number);
123 else
124 pos += snprintf(buf + pos, bufsize - pos,
125 "insert into sys.args"
126 " values"
127 " (%d, %d, 'arg_%d',"
128 " '%s', %u, %u, %d,"
129 " %d);\n",
130 store_next_oid(),
131 func->base.id,
132 number,
133 arg->type.type->sqlname,
134 arg->type.digits,
135 arg->type.scale,
136 arg->inout, number);
137 }
138 }
139 for (n = aggrs->h; n; n = n->next) {
140 sql_func *aggr = n->data;
141 sql_arg *arg;
142
143 if (aggr->base.id >= FUNC_OIDS)
144 continue;
145
146 pos += snprintf(buf + pos, bufsize - pos,
147 "insert into sys.functions values"
148 " (%d, '%s', '%s', '%s', %d, %d, false,"
149 " %s, %s, %d, %s);\n",
150 aggr->base.id, aggr->base.name, aggr->imp,
151 aggr->mod, (int) FUNC_LANG_INT, (int) aggr->type,
152 aggr->varres ? "true" : "false",
153 aggr->vararg ? "true" : "false",
154 aggr->s ? aggr->s->base.id : s->base.id,
155 aggr->system ? "true" : "false");
156 arg = aggr->res->h->data;
157 pos += snprintf(buf + pos, bufsize - pos,
158 "insert into sys.args values"
159 " (%d, %d, 'res', '%s', %u, %u, %d, 0);\n",
160 store_next_oid(), aggr->base.id,
161 arg->type.type->sqlname, arg->type.digits,
162 arg->type.scale, arg->inout);
163 if (aggr->ops->h) {
164 arg = aggr->ops->h->data;
165 pos += snprintf(buf + pos, bufsize - pos,
166 "insert into sys.args values"
167 " (%d, %d, 'arg', '%s', %u,"
168 " %u, %d, 1);\n",
169 store_next_oid(), aggr->base.id,
170 arg->type.type->sqlname,
171 arg->type.digits, arg->type.scale,
172 arg->inout);
173 }
174 }
175
176 pos += snprintf(buf + pos, bufsize - pos, "set schema \"%s\";\n", prev_schema);
177
178 assert(pos < bufsize);
179 printf("Running database upgrade commands:\n%s\n", buf);
180 err = SQLstatementIntern(c, &buf, "update", true, false, NULL);
181 GDKfree(buf);
182 return err; /* usually MAL_SUCCEED */
183}
184
185#ifdef HAVE_HGE
186static str
187sql_update_hugeint(Client c, mvc *sql, const char *prev_schema, bool *systabfixed)
188{
189 size_t bufsize = 8192, pos = 0;
190 char *buf, *err;
191
192 if (!*systabfixed &&
193 (err = sql_fix_system_tables(c, sql, prev_schema)) != NULL)
194 return err;
195 *systabfixed = true;
196
197 if ((buf = GDKmalloc(bufsize)) == NULL)
198 throw(SQL, "sql_update_hugeint", SQLSTATE(HY001) MAL_MALLOC_FAIL);
199
200 pos += snprintf(buf + pos, bufsize - pos, "set schema \"sys\";\n");
201
202 /* 80_udf_hge.sql */
203 pos += snprintf(buf + pos, bufsize - pos,
204 "create function fuse(one bigint, two bigint)\n"
205 "returns hugeint external name udf.fuse;\n");
206
207 /* 90_generator_hge.sql */
208 pos += snprintf(buf + pos, bufsize - pos,
209 "create function sys.generate_series(first hugeint, \"limit\" hugeint)\n"
210 "returns table (value hugeint)\n"
211 "external name generator.series;\n"
212 "create function sys.generate_series(first hugeint, \"limit\" hugeint, stepsize hugeint)\n"
213 "returns table (value hugeint)\n"
214 "external name generator.series;\n");
215
216 /* 39_analytics_hge.sql */
217 pos += snprintf(buf + pos, bufsize - pos,
218 "create aggregate stddev_samp(val HUGEINT) returns DOUBLE\n"
219 "\texternal name \"aggr\".\"stdev\";\n"
220 "GRANT EXECUTE ON AGGREGATE stddev_samp(HUGEINT) TO PUBLIC;\n"
221 "create aggregate stddev_pop(val HUGEINT) returns DOUBLE\n"
222 "\texternal name \"aggr\".\"stdevp\";\n"
223 "GRANT EXECUTE ON AGGREGATE stddev_pop(HUGEINT) TO PUBLIC;\n"
224 "create aggregate var_samp(val HUGEINT) returns DOUBLE\n"
225 "\texternal name \"aggr\".\"variance\";\n"
226 "GRANT EXECUTE ON AGGREGATE var_samp(HUGEINT) TO PUBLIC;\n"
227 "create aggregate var_pop(val HUGEINT) returns DOUBLE\n"
228 "\texternal name \"aggr\".\"variancep\";\n"
229 "GRANT EXECUTE ON AGGREGATE var_pop(HUGEINT) TO PUBLIC;\n"
230 "create aggregate median(val HUGEINT) returns HUGEINT\n"
231 "\texternal name \"aggr\".\"median\";\n"
232 "GRANT EXECUTE ON AGGREGATE median(HUGEINT) TO PUBLIC;\n"
233 "create aggregate quantile(val HUGEINT, q DOUBLE) returns HUGEINT\n"
234 "\texternal name \"aggr\".\"quantile\";\n"
235 "GRANT EXECUTE ON AGGREGATE quantile(HUGEINT, DOUBLE) TO PUBLIC;\n"
236 "create aggregate corr(e1 HUGEINT, e2 HUGEINT) returns DOUBLE\n"
237 "\texternal name \"aggr\".\"corr\";\n"
238 "GRANT EXECUTE ON AGGREGATE corr(HUGEINT, HUGEINT) TO PUBLIC;\n");
239
240 /* 40_json_hge.sql */
241 pos += snprintf(buf + pos, bufsize - pos,
242 "create function json.filter(js json, name hugeint)\n"
243 "returns json external name json.filter;\n"
244 "GRANT EXECUTE ON FUNCTION json.filter(json, hugeint) TO PUBLIC;\n");
245
246 pos += snprintf(buf + pos, bufsize - pos,
247 "update sys.functions set system = true where name in ('fuse', 'generate_series', 'stddev_samp', 'stddev_pop', 'var_samp', 'var_pop', 'median', 'quantile', 'corr') and schema_id = (select id from sys.schemas where name = 'sys');\n"
248 "update sys.functions set system = true where name = 'filter' and schema_id = (select id from sys.schemas where name = 'json');\n");
249
250 pos += snprintf(buf + pos, bufsize - pos, "set schema \"%s\";\n", prev_schema);
251 assert(pos < bufsize);
252
253 printf("Running database upgrade commands:\n%s\n", buf);
254 err = SQLstatementIntern(c, &buf, "update", true, false, NULL);
255 GDKfree(buf);
256 return err; /* usually MAL_SUCCEED */
257}
258#endif
259
260static str
261sql_update_geom(Client c, mvc *sql, int olddb, const char *prev_schema)
262{
263 size_t bufsize, pos = 0;
264 char *buf, *err = NULL, *geomupgrade;
265 geomsqlfix_fptr fixfunc;
266 node *n;
267 sql_schema *s = mvc_bind_schema(sql, "sys");
268
269 if ((fixfunc = geomsqlfix_get()) == NULL)
270 return NULL;
271
272 geomupgrade = (*fixfunc)(olddb);
273 if (geomupgrade == NULL)
274 throw(SQL, "sql_update_geom", SQLSTATE(HY001) MAL_MALLOC_FAIL);
275 bufsize = strlen(geomupgrade) + 512;
276 buf = GDKmalloc(bufsize);
277 if (buf == NULL) {
278 GDKfree(geomupgrade);
279 throw(SQL, "sql_update_geom", SQLSTATE(HY001) MAL_MALLOC_FAIL);
280 }
281 pos += snprintf(buf + pos, bufsize - pos, "set schema \"sys\";\n");
282 pos += snprintf(buf + pos, bufsize - pos, "%s", geomupgrade);
283 GDKfree(geomupgrade);
284
285 pos += snprintf(buf + pos, bufsize - pos, "delete from sys.types where systemname in ('mbr', 'wkb', 'wkba');\n");
286 for (n = types->h; n; n = n->next) {
287 sql_type *t = n->data;
288
289 if (t->base.id < FUNC_OIDS &&
290 (strcmp(t->base.name, "mbr") == 0 ||
291 strcmp(t->base.name, "wkb") == 0 ||
292 strcmp(t->base.name, "wkba") == 0))
293 pos += snprintf(buf + pos, bufsize - pos, "insert into sys.types values (%d, '%s', '%s', %u, %u, %d, %d, %d);\n",
294 t->base.id, t->base.name, t->sqlname, t->digits, t->scale, t->radix, (int) t->eclass,
295 t->s ? t->s->base.id : s->base.id);
296 }
297
298 pos += snprintf(buf + pos, bufsize - pos, "set schema \"%s\";\n", prev_schema);
299
300 assert(pos < bufsize);
301 printf("Running database upgrade commands:\n%s\n", buf);
302 err = SQLstatementIntern(c, &buf, "update", true, false, NULL);
303 GDKfree(buf);
304 return err; /* usually MAL_SUCCEED */
305}
306
307static str
308sql_update_jul2017(Client c, const char *prev_schema)
309{
310 size_t bufsize = 10000, pos = 0;
311 char *buf = GDKmalloc(bufsize), *err = NULL;
312 char *q1 = "select id from sys.functions where name = 'shpload' and schema_id = (select id from sys.schemas where name = 'sys');\n";
313 res_table *output;
314 BAT *b;
315
316 if( buf == NULL)
317 throw(SQL, "sql_update_jul2017", SQLSTATE(HY001) MAL_MALLOC_FAIL);
318 pos += snprintf(buf + pos, bufsize - pos, "set schema \"sys\";\n");
319
320 pos += snprintf(buf + pos, bufsize - pos,
321 "delete from sys._columns where table_id = (select id from sys._tables where name = 'connections' and schema_id = (select id from sys.schemas where name = 'sys'));\n"
322 "delete from sys._tables where name = 'connections' and schema_id = (select id from sys.schemas where name = 'sys');\n");
323
324 /* 09_like.sql */
325 pos += snprintf(buf + pos, bufsize - pos,
326 "update sys.functions set side_effect = false where name in ('like', 'ilike') and schema_id = (select id from sys.schemas where name = 'sys');\n");
327
328 /* 25_debug.sql */
329 pos += snprintf(buf + pos, bufsize - pos,
330 "drop function sys.malfunctions;\n"
331 "create function sys.malfunctions() returns table(\"module\" string, \"function\" string, \"signature\" string, \"address\" string, \"comment\" string) external name \"manual\".\"functions\";\n"
332 "drop function sys.optimizer_stats();\n"
333 "create function sys.optimizer_stats() "
334 "returns table (optname string, count int, timing bigint) "
335 "external name inspect.optimizer_stats;\n"
336 "update sys.functions set system = true where name in ('malfunctions', 'optimizer_stats') and schema_id = (select id from sys.schemas where name = 'sys');\n");
337
338 /* 46_profiler.sql */
339 pos += snprintf(buf + pos, bufsize - pos,
340 "create function profiler.getlimit() returns integer external name profiler.getlimit;\n"
341 "create procedure profiler.setlimit(lim integer) external name profiler.setlimit;\n"
342 "drop procedure profiler.setpoolsize;\n"
343 "drop procedure profiler.setstream;\n"
344 "update sys.functions set system = true where name in ('getlimit', 'setlimit') and schema_id = (select id from sys.schemas where name = 'profiler');\n");
345
346 /* 51_sys_schema_extensions.sql */
347 pos += snprintf(buf + pos, bufsize - pos,
348 "ALTER TABLE sys.keywords SET READ ONLY;\n"
349 "ALTER TABLE sys.table_types SET READ ONLY;\n"
350 "ALTER TABLE sys.dependency_types SET READ ONLY;\n"
351
352 "CREATE TABLE sys.function_types (\n"
353 "function_type_id SMALLINT NOT NULL PRIMARY KEY,\n"
354 "function_type_name VARCHAR(30) NOT NULL UNIQUE);\n"
355 "INSERT INTO sys.function_types (function_type_id, function_type_name) VALUES\n"
356 "(1, 'Scalar function'), (2, 'Procedure'), (3, 'Aggregate function'), (4, 'Filter function'), (5, 'Function returning a table'),\n"
357 "(6, 'Analytic function'), (7, 'Loader function');\n"
358 "ALTER TABLE sys.function_types SET READ ONLY;\n"
359
360 "CREATE TABLE sys.function_languages (\n"
361 "language_id SMALLINT NOT NULL PRIMARY KEY,\n"
362 "language_name VARCHAR(20) NOT NULL UNIQUE);\n"
363 "INSERT INTO sys.function_languages (language_id, language_name) VALUES\n"
364 "(0, 'Internal C'), (1, 'MAL'), (2, 'SQL'), (3, 'R'), (6, 'Python'), (7, 'Python Mapped'), (8, 'Python2'), (9, 'Python2 Mapped'), (10, 'Python3'), (11, 'Python3 Mapped');\n"
365 "ALTER TABLE sys.function_languages SET READ ONLY;\n"
366
367 "CREATE TABLE sys.key_types (\n"
368 "key_type_id SMALLINT NOT NULL PRIMARY KEY,\n"
369 "key_type_name VARCHAR(15) NOT NULL UNIQUE);\n"
370 "INSERT INTO sys.key_types (key_type_id, key_type_name) VALUES\n"
371 "(0, 'Primary Key'), (1, 'Unique Key'), (2, 'Foreign Key');\n"
372 "ALTER TABLE sys.key_types SET READ ONLY;\n"
373
374 "CREATE TABLE sys.index_types (\n"
375 "index_type_id SMALLINT NOT NULL PRIMARY KEY,\n"
376 "index_type_name VARCHAR(25) NOT NULL UNIQUE);\n"
377 "INSERT INTO sys.index_types (index_type_id, index_type_name) VALUES\n"
378 "(0, 'Hash'), (1, 'Join'), (2, 'Order preserving hash'), (3, 'No-index'), (4, 'Imprint'), (5, 'Ordered');\n"
379 "ALTER TABLE sys.index_types SET READ ONLY;\n"
380
381 "CREATE TABLE sys.privilege_codes (\n"
382 "privilege_code_id INT NOT NULL PRIMARY KEY,\n"
383 "privilege_code_name VARCHAR(30) NOT NULL UNIQUE);\n"
384 "INSERT INTO sys.privilege_codes (privilege_code_id, privilege_code_name) VALUES\n"
385 "(1, 'SELECT'), (2, 'UPDATE'), (4, 'INSERT'), (8, 'DELETE'), (16, 'EXECUTE'), (32, 'GRANT'),\n"
386 "(3, 'SELECT,UPDATE'), (5, 'SELECT,INSERT'), (6, 'INSERT,UPDATE'), (7, 'SELECT,INSERT,UPDATE'),\n"
387 "(9, 'SELECT,DELETE'), (10, 'UPDATE,DELETE'), (11, 'SELECT,UPDATE,DELETE'), (12, 'INSERT,DELETE'),\n"
388 "(13, 'SELECT,INSERT,DELETE'), (14, 'INSERT,UPDATE,DELETE'), (15, 'SELECT,INSERT,UPDATE,DELETE');\n"
389 "ALTER TABLE sys.privilege_codes SET READ ONLY;\n"
390
391 "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");
392
393 /* 75_shp.sql, if shp extension available */
394 err = SQLstatementIntern(c, &q1, "update", true, false, &output);
395 if (err) {
396 GDKfree(buf);
397 return err;
398 }
399 b = BATdescriptor(output->cols[0].b);
400 if (b) {
401 if (BATcount(b) > 0) {
402 pos += snprintf(buf + pos, bufsize - pos,
403 "drop procedure SHPload(integer);\n"
404 "create procedure SHPload(fid integer) external name shp.import;\n"
405 "update sys.functions set system = true where name = 'shpload' and schema_id = (select id from sys.schemas where name = 'sys');\n");
406 }
407 BBPunfix(b->batCacheid);
408 }
409 res_tables_destroy(output);
410
411 pos += snprintf(buf + pos, bufsize - pos, "set schema \"%s\";\n", prev_schema);
412
413 assert(pos < bufsize);
414 printf("Running database upgrade commands:\n%s\n", buf);
415 err = SQLstatementIntern(c, &buf, "update", true, false, NULL);
416 GDKfree(buf);
417 return err; /* usually MAL_SUCCEED */
418}
419
420static str
421sql_update_jul2017_sp2(Client c)
422{
423 char *qry = "select obj_id from sys.privileges where auth_id = 1 and obj_id in (select id from sys._tables where name in ('keywords', 'table_types', 'dependency_types', 'function_types', 'function_languages', 'key_types', 'index_types', 'privilege_codes', 'environment')) and privileges = 1;\n";
424 char *err = NULL;
425 res_table *output;
426 BAT *b;
427
428 err = SQLstatementIntern(c, &qry, "update", true, false, &output);
429 if (err) {
430 return err;
431 }
432
433 b = BATdescriptor(output->cols[0].b);
434 if (b) {
435 if (BATcount(b) < 9) {
436 /* we are missing grants on these system tables, add them */
437 size_t bufsize = 2048, pos = 0;
438 char *buf = GDKmalloc(bufsize);
439
440 if (buf == NULL)
441 throw(SQL, "sql_update_jul2017_sp2", SQLSTATE(HY001) MAL_MALLOC_FAIL);
442
443 /* 51_sys_schema_extensions.sql and 25_debug.sql */
444 pos += snprintf(buf + pos, bufsize - pos,
445 "GRANT SELECT ON sys.keywords TO PUBLIC;\n"
446 "GRANT SELECT ON sys.table_types TO PUBLIC;\n"
447 "GRANT SELECT ON sys.dependency_types TO PUBLIC;\n"
448 "GRANT SELECT ON sys.function_types TO PUBLIC;\n"
449 "GRANT SELECT ON sys.function_languages TO PUBLIC;\n"
450 "GRANT SELECT ON sys.key_types TO PUBLIC;\n"
451 "GRANT SELECT ON sys.index_types TO PUBLIC;\n"
452 "GRANT SELECT ON sys.privilege_codes TO PUBLIC;\n"
453 "GRANT EXECUTE ON FUNCTION sys.environment() TO PUBLIC;\n"
454 "GRANT SELECT ON sys.environment TO PUBLIC;\n"
455 );
456 assert(pos < bufsize);
457 printf("Running database upgrade commands:\n%s\n", buf);
458 err = SQLstatementIntern(c, &buf, "update", true, false, NULL);
459 GDKfree(buf);
460 }
461 BBPunfix(b->batCacheid);
462 }
463 res_tables_destroy(output);
464
465 return err; /* usually NULL */
466}
467
468static str
469sql_update_jul2017_sp3(Client c, mvc *sql, const char *prev_schema, bool *systabfixed)
470{
471 char *err = NULL;
472 sql_schema *sys;
473 sql_table *tab;
474 sql_column *col;
475 oid rid;
476
477 /* if there is no value "sys_update_schemas" in
478 * sys.functions.name, we need to update the sys.functions
479 * table */
480 sys = find_sql_schema(sql->session->tr, "sys");
481 tab = find_sql_table(sys, "functions");
482 col = find_sql_column(tab, "name");
483 rid = table_funcs.column_find_row(sql->session->tr, col, "sys_update_schemas", NULL);
484 if (is_oid_nil(rid) && !*systabfixed) {
485 err = sql_fix_system_tables(c, sql, prev_schema);
486 if (err != NULL)
487 return err;
488 *systabfixed = true;
489 }
490 /* if there is no value "system_update_schemas" in
491 * sys.triggers.name, we need to add the triggers */
492 tab = find_sql_table(sys, "triggers");
493 col = find_sql_column(tab, "name");
494 rid = table_funcs.column_find_row(sql->session->tr, col, "system_update_schemas", NULL);
495 if (is_oid_nil(rid)) {
496 size_t bufsize = 1024, pos = 0;
497 char *buf = GDKmalloc(bufsize);
498 if (buf == NULL)
499 throw(SQL, "sql_update_jul2017_sp3", SQLSTATE(HY001) MAL_MALLOC_FAIL);
500 pos += snprintf(
501 buf + pos,
502 bufsize - pos,
503 "set schema \"sys\";\n"
504 "create trigger system_update_schemas after update on sys.schemas for each statement call sys_update_schemas();\n"
505 "create trigger system_update_tables after update on sys._tables for each statement call sys_update_tables();\n");
506 pos += snprintf(buf + pos, bufsize - pos, "set schema \"%s\";\n", prev_schema);
507 assert(pos < bufsize);
508 printf("Running database upgrade commands:\n%s\n", buf);
509 err = SQLstatementIntern(c, &buf, "update", true, false, NULL);
510 GDKfree(buf);
511 }
512 return err;
513}
514
515static str
516sql_update_mar2018_geom(Client c, sql_table *t, const char *prev_schema)
517{
518 size_t bufsize = 10000, pos = 0;
519 char *buf = GDKmalloc(bufsize), *err = NULL;
520
521 if (buf == NULL)
522 throw(SQL, "sql_update_mar2018_geom", SQLSTATE(HY001) MAL_MALLOC_FAIL);
523 pos += snprintf(buf + pos, bufsize - pos, "set schema \"sys\";\n");
524
525 t->system = 0;
526 pos += snprintf(buf + pos, bufsize - pos,
527 "drop view sys.geometry_columns cascade;\n"
528 "create view sys.geometry_columns as\n"
529 "\tselect cast(null as varchar(1)) as f_table_catalog,\n"
530 "\t\ts.name as f_table_schema,\n"
531 "\t\tt.name as f_table_name,\n"
532 "\t\tc.name as f_geometry_column,\n"
533 "\t\tcast(has_z(c.type_digits) + has_m(c.type_digits) +2 as integer) as coord_dimension,\n"
534 "\t\tc.type_scale as srid,\n"
535 "\t\tget_type(c.type_digits, 0) as type\n"
536 "\tfrom sys.columns c, sys.tables t, sys.schemas s\n"
537 "\twhere c.table_id = t.id and t.schema_id = s.id\n"
538 "\t and c.type in (select sqlname from sys.types where systemname in ('wkb', 'wkba'));\n"
539 "GRANT SELECT ON sys.geometry_columns TO PUBLIC;\n"
540 "update sys._tables set system = true where name = 'geometry_columns' and schema_id in (select id from schemas where name = 'sys');\n");
541
542 pos += snprintf(buf + pos, bufsize - pos, "set schema \"%s\";\n", prev_schema);
543
544 assert(pos < bufsize);
545 printf("Running database upgrade commands:\n%s\n", buf);
546 err = SQLstatementIntern(c, &buf, "update", true, false, NULL);
547 GDKfree(buf);
548 return err; /* usually MAL_SUCCEED */
549}
550
551static str
552sql_update_mar2018(Client c, mvc *sql, const char *prev_schema, bool *systabfixed)
553{
554 size_t bufsize = 30000, pos = 0;
555 char *buf, *err;
556 sql_schema *s;
557 sql_table *t;
558 res_table *output;
559 BAT *b;
560
561 buf = "select id from sys.functions where name = 'quarter' and schema_id = (select id from sys.schemas where name = 'sys');\n";
562 err = SQLstatementIntern(c, &buf, "update", true, false, &output);
563 if (err)
564 return err;
565 b = BATdescriptor(output->cols[0].b);
566 if (b) {
567 if (BATcount(b) == 0 && !*systabfixed) {
568 /* if there is no value "quarter" in
569 * sys.functions.name, we need to update the
570 * sys.functions table */
571 err = sql_fix_system_tables(c, sql, prev_schema);
572 if (err != NULL)
573 return err;
574 *systabfixed = true;
575 }
576 BBPunfix(b->batCacheid);
577 }
578 res_tables_destroy(output);
579
580 buf = GDKmalloc(bufsize);
581 if (buf == NULL)
582 throw(SQL, "sql_update_mar2018", SQLSTATE(HY001) MAL_MALLOC_FAIL);
583 s = mvc_bind_schema(sql, "sys");
584
585 t = mvc_create_table(sql, s, "comments", tt_table, 1, SQL_PERSIST, 0, -1, 0);
586 sql_column *col = mvc_create_column_(sql, t, "id", "int", 32);
587 sql_key *k = sql_trans_create_ukey(sql->session->tr, t, "comments_id_pkey", pkey);
588 k = sql_trans_create_kc(sql->session->tr, k, col);
589 k = sql_trans_key_done(sql->session->tr, k);
590 sql_trans_create_dependency(sql->session->tr, col->base.id, k->idx->base.id, INDEX_DEPENDENCY);
591 col = mvc_create_column_(sql, t, "remark", "varchar", 65000);
592 sql_trans_alter_null(sql->session->tr, col, 0);
593
594 sql_table *privs = mvc_bind_table(sql, s, "privileges");
595 int pub = ROLE_PUBLIC;
596 int p = PRIV_SELECT;
597 int zero = 0;
598 table_funcs.table_insert(sql->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
599
600 pos += snprintf(buf + pos, bufsize - pos, "set schema \"sys\";\n");
601
602 /* 21_dependency_views.sql */
603 pos += snprintf(buf + pos, bufsize - pos,
604"CREATE VIEW sys.ids (id, name, schema_id, table_id, table_name, obj_type, sys_table) AS\n"
605"SELECT id, name, cast(null as int) as schema_id, cast(null as int) as table_id, cast(null as varchar(124)) as table_name, 'author' AS obj_type, 'sys.auths' AS sys_table FROM sys.auths UNION ALL\n"
606"SELECT id, name, cast(null as int) as schema_id, cast(null as int) as table_id, cast(null as varchar(124)) as table_name, 'schema', 'sys.schemas' FROM sys.schemas UNION ALL\n"
607"SELECT id, name, schema_id, id as table_id, name as table_name, case when type = 1 then 'view' else 'table' end, 'sys._tables' FROM sys._tables UNION ALL\n"
608"SELECT id, name, schema_id, id as table_id, name as table_name, case when type = 1 then 'view' else 'table' end, 'tmp._tables' FROM tmp._tables UNION ALL\n"
609"SELECT c.id, c.name, t.schema_id, c.table_id, t.name as table_name, 'column', 'sys._columns' FROM sys._columns c JOIN sys._tables t ON c.table_id = t.id UNION ALL\n"
610"SELECT c.id, c.name, t.schema_id, c.table_id, t.name as table_name, 'column', 'tmp._columns' FROM tmp._columns c JOIN tmp._tables t ON c.table_id = t.id UNION ALL\n"
611"SELECT k.id, k.name, t.schema_id, k.table_id, t.name as table_name, 'key', 'sys.keys' FROM sys.keys k JOIN sys._tables t ON k.table_id = t.id UNION ALL\n"
612"SELECT k.id, k.name, t.schema_id, k.table_id, t.name as table_name, 'key', 'tmp.keys' FROM tmp.keys k JOIN sys._tables t ON k.table_id = t.id UNION ALL\n"
613"SELECT i.id, i.name, t.schema_id, i.table_id, t.name as table_name, 'index', 'sys.idxs' FROM sys.idxs i JOIN sys._tables t ON i.table_id = t.id UNION ALL\n"
614"SELECT i.id, i.name, t.schema_id, i.table_id, t.name as table_name, 'index', 'tmp.idxs' FROM tmp.idxs i JOIN sys._tables t ON i.table_id = t.id UNION ALL\n"
615"SELECT g.id, g.name, t.schema_id, g.table_id, t.name as table_name, 'trigger', 'sys.triggers' FROM sys.triggers g JOIN sys._tables t ON g.table_id = t.id UNION ALL\n"
616"SELECT g.id, g.name, t.schema_id, g.table_id, t.name as table_name, 'trigger', 'tmp.triggers' FROM tmp.triggers g JOIN sys._tables t ON g.table_id = t.id UNION ALL\n"
617"SELECT id, name, schema_id, cast(null as int) as table_id, cast(null as varchar(124)) as table_name, case when type = 2 then 'procedure' else 'function' end, 'sys.functions' FROM sys.functions UNION ALL\n"
618"SELECT a.id, a.name, f.schema_id, cast(null as int) as table_id, cast(null as varchar(124)) as table_name, case when f.type = 2 then 'procedure arg' else 'function arg' end, 'sys.args' FROM sys.args a JOIN sys.functions f ON a.func_id = f.id UNION ALL\n"
619"SELECT id, name, schema_id, cast(null as int) as table_id, cast(null as varchar(124)) as table_name, 'sequence', 'sys.sequences' FROM sys.sequences UNION ALL\n"
620"SELECT id, sqlname, schema_id, cast(null as int) as table_id, cast(null as varchar(124)) as table_name, 'type', 'sys.types' FROM sys.types WHERE id > 2000 /* exclude system types to prevent duplicates with auths.id */\n"
621" ORDER BY id;\n"
622"GRANT SELECT ON sys.ids TO PUBLIC;\n"
623"CREATE VIEW sys.dependencies_vw AS\n"
624"SELECT d.id, i1.obj_type, i1.name,\n"
625" d.depend_id as used_by_id, i2.obj_type as used_by_obj_type, i2.name as used_by_name,\n"
626" d.depend_type, dt.dependency_type_name\n"
627" FROM sys.dependencies d\n"
628" JOIN sys.ids i1 ON d.id = i1.id\n"
629" JOIN sys.ids i2 ON d.depend_id = i2.id\n"
630" JOIN sys.dependency_types dt ON d.depend_type = dt.dependency_type_id\n"
631" ORDER BY id, depend_id;\n"
632"GRANT SELECT ON sys.dependencies_vw TO PUBLIC;\n"
633"CREATE VIEW sys.dependency_owners_on_schemas AS\n"
634"SELECT a.name AS owner_name, s.id AS schema_id, s.name AS schema_name, CAST(1 AS smallint) AS depend_type\n"
635" FROM sys.schemas AS s, sys.auths AS a\n"
636" WHERE s.owner = a.id\n"
637" ORDER BY a.name, s.name;\n"
638"GRANT SELECT ON sys.dependency_owners_on_schemas TO PUBLIC;\n"
639"CREATE VIEW sys.dependency_columns_on_keys AS\n"
640"SELECT t.schema_id AS table_schema_id, t.id AS table_id, t.name AS table_name, c.id AS column_id, c.name AS column_name, k.id AS key_id, k.name AS key_name, CAST(kc.nr +1 AS int) AS key_col_nr, CAST(k.type AS smallint) AS key_type, CAST(4 AS smallint) AS depend_type\n"
641" FROM sys.columns AS c, sys.objects AS kc, sys.keys AS k, sys.tables AS t\n"
642" WHERE k.table_id = c.table_id AND c.table_id = t.id AND kc.id = k.id AND kc.name = c.name\n"
643" AND k.type IN (0, 1)\n"
644" ORDER BY t.schema_id, t.name, c.name, k.type, k.name, kc.nr;\n"
645"GRANT SELECT ON sys.dependency_columns_on_keys TO PUBLIC;\n"
646"CREATE VIEW sys.dependency_tables_on_views AS\n"
647"SELECT t.schema_id AS table_schema_id, t.id AS table_id, t.name AS table_name, v.schema_id AS view_schema_id, v.id AS view_id, v.name AS view_name, dep.depend_type AS depend_type\n"
648" FROM sys.tables AS t, sys.tables AS v, sys.dependencies AS dep\n"
649" WHERE t.id = dep.id AND v.id = dep.depend_id\n"
650" AND dep.depend_type = 5 AND t.type NOT IN (1, 11) AND v.type IN (1, 11)\n"
651" ORDER BY t.schema_id, t.name, v.schema_id, v.name;\n"
652"GRANT SELECT ON sys.dependency_tables_on_views TO PUBLIC;\n"
653"CREATE VIEW sys.dependency_views_on_views AS\n"
654"SELECT v1.schema_id AS view1_schema_id, v1.id AS view1_id, v1.name AS view1_name, v2.schema_id AS view2_schema_id, v2.id AS view2_id, v2.name AS view2_name, dep.depend_type AS depend_type\n"
655" FROM sys.tables AS v1, sys.tables AS v2, sys.dependencies AS dep\n"
656" WHERE v1.id = dep.id AND v2.id = dep.depend_id\n"
657" AND dep.depend_type = 5 AND v1.type IN (1, 11) AND v2.type IN (1, 11)\n"
658" ORDER BY v1.schema_id, v1.name, v2.schema_id, v2.name;\n"
659"GRANT SELECT ON sys.dependency_views_on_views TO PUBLIC;\n"
660"CREATE VIEW sys.dependency_columns_on_views AS\n"
661"SELECT t.schema_id AS table_schema_id, t.id AS table_id, t.name AS table_name, c.id AS column_id, c.name AS column_name, v.schema_id AS view_schema_id, v.id AS view_id, v.name AS view_name, dep.depend_type AS depend_type\n"
662" FROM sys.columns AS c, sys.tables AS v, sys.tables AS t, sys.dependencies AS dep\n"
663" WHERE c.id = dep.id AND v.id = dep.depend_id AND c.table_id = t.id\n"
664" AND dep.depend_type = 5 AND v.type IN (1, 11)\n"
665" ORDER BY t.schema_id, t.name, c.name, v.name;\n"
666"GRANT SELECT ON sys.dependency_columns_on_views TO PUBLIC;\n"
667"CREATE VIEW sys.dependency_functions_on_views AS\n"
668"SELECT f.schema_id AS function_schema_id, f.id AS function_id, f.name AS function_name, v.schema_id AS view_schema_id, v.id AS view_id, v.name AS view_name, dep.depend_type AS depend_type\n"
669" FROM sys.functions AS f, sys.tables AS v, sys.dependencies AS dep\n"
670" WHERE f.id = dep.id AND v.id = dep.depend_id\n"
671" AND dep.depend_type = 5 AND v.type IN (1, 11)\n"
672" ORDER BY f.schema_id, f.name, v.schema_id, v.name;\n"
673"GRANT SELECT ON sys.dependency_functions_on_views TO PUBLIC;\n"
674"CREATE VIEW sys.dependency_schemas_on_users AS\n"
675"SELECT s.id AS schema_id, s.name AS schema_name, u.name AS user_name, CAST(6 AS smallint) AS depend_type\n"
676" FROM sys.users AS u, sys.schemas AS s\n"
677" WHERE u.default_schema = s.id\n"
678" ORDER BY s.name, u.name;\n"
679"GRANT SELECT ON sys.dependency_schemas_on_users TO PUBLIC;\n"
680"CREATE VIEW sys.dependency_tables_on_functions AS\n"
681"SELECT t.schema_id AS table_schema_id, t.id AS table_id, t.name AS table_name, f.name AS function_name, f.type AS function_type, dep.depend_type AS depend_type\n"
682" FROM sys.functions AS f, sys.tables AS t, sys.dependencies AS dep\n"
683" WHERE t.id = dep.id AND f.id = dep.depend_id\n"
684" AND dep.depend_type = 7 AND f.type <> 2 AND t.type NOT IN (1, 11)\n"
685" ORDER BY t.name, t.schema_id, f.name, f.id;\n"
686"GRANT SELECT ON sys.dependency_tables_on_functions TO PUBLIC;\n"
687"CREATE VIEW sys.dependency_views_on_functions AS\n"
688"SELECT v.schema_id AS view_schema_id, v.id AS view_id, v.name AS view_name, f.name AS function_name, f.type AS function_type, dep.depend_type AS depend_type\n"
689" FROM sys.functions AS f, sys.tables AS v, sys.dependencies AS dep\n"
690" WHERE v.id = dep.id AND f.id = dep.depend_id\n"
691" AND dep.depend_type = 7 AND f.type <> 2 AND v.type IN (1, 11)\n"
692" ORDER BY v.name, v.schema_id, f.name, f.id;\n"
693"GRANT SELECT ON sys.dependency_views_on_functions TO PUBLIC;\n"
694"CREATE VIEW sys.dependency_columns_on_functions AS\n"
695"SELECT c.table_id, c.id AS column_id, c.name, f.id AS function_id, f.name AS function_name, f.type AS function_type, dep.depend_type AS depend_type\n"
696" FROM sys.functions AS f, sys.columns AS c, sys.dependencies AS dep\n"
697" WHERE c.id = dep.id AND f.id = dep.depend_id\n"
698" AND dep.depend_type = 7 AND f.type <> 2\n"
699" ORDER BY c.name, c.table_id, f.name, f.id;\n"
700"GRANT SELECT ON sys.dependency_columns_on_functions TO PUBLIC;\n"
701"CREATE VIEW sys.dependency_functions_on_functions AS\n"
702"SELECT f1.schema_id, f1.id AS function_id, f1.name AS function_name, f1.type AS function_type,\n"
703" f2.schema_id AS used_in_function_schema_id, f2.id AS used_in_function_id, f2.name AS used_in_function_name, f2.type AS used_in_function_type, dep.depend_type AS depend_type\n"
704" FROM sys.functions AS f1, sys.functions AS f2, sys.dependencies AS dep\n"
705" WHERE f1.id = dep.id AND f2.id = dep.depend_id\n"
706" AND dep.depend_type = 7 AND f2.type <> 2\n"
707" ORDER BY f1.name, f1.id, f2.name, f2.id;\n"
708"GRANT SELECT ON sys.dependency_functions_on_functions TO PUBLIC;\n"
709"CREATE VIEW sys.dependency_tables_on_triggers AS\n"
710"(SELECT t.schema_id AS table_schema_id, t.id AS table_id, t.name AS table_name, tri.id AS trigger_id, tri.name AS trigger_name, CAST(8 AS smallint) AS depend_type\n"
711" FROM sys.tables AS t, sys.triggers AS tri\n"
712" WHERE tri.table_id = t.id)\n"
713"UNION\n"
714"(SELECT t.schema_id AS table_schema_id, t.id AS table_id, t.name AS table_name, tri.id AS trigger_id, tri.name AS trigger_name, dep.depend_type AS depend_type\n"
715" FROM sys.tables AS t, sys.triggers AS tri, sys.dependencies AS dep\n"
716" WHERE dep.id = t.id AND dep.depend_id = tri.id\n"
717" AND dep.depend_type = 8)\n"
718" ORDER BY table_schema_id, table_name, trigger_name;\n"
719"GRANT SELECT ON sys.dependency_tables_on_triggers TO PUBLIC;\n"
720"CREATE VIEW sys.dependency_columns_on_triggers AS\n"
721"SELECT t.schema_id AS table_schema_id, t.id AS table_id, t.name AS table_name, tri.id AS trigger_id, tri.name AS trigger_name, c.id AS column_id, c.name AS column_name, dep.depend_type AS depend_type\n"
722" FROM sys.tables AS t, sys.columns AS c, sys.triggers AS tri, sys.dependencies AS dep\n"
723" WHERE dep.id = c.id AND dep.depend_id = tri.id AND c.table_id = t.id\n"
724" AND dep.depend_type = 8\n"
725" ORDER BY t.schema_id, t.name, tri.name, c.name;\n"
726"GRANT SELECT ON sys.dependency_columns_on_triggers TO PUBLIC;\n"
727"CREATE VIEW sys.dependency_functions_on_triggers AS\n"
728"SELECT f.schema_id AS function_schema_id, f.id AS function_id, f.name AS function_name, f.type AS function_type,\n"
729" tri.id AS trigger_id, tri.name AS trigger_name, tri.table_id AS trigger_table_id, dep.depend_type AS depend_type\n"
730" FROM sys.functions AS f, sys.triggers AS tri, sys.dependencies AS dep\n"
731" WHERE dep.id = f.id AND dep.depend_id = tri.id\n"
732" AND dep.depend_type = 8\n"
733" ORDER BY f.schema_id, f.name, tri.name;\n"
734"GRANT SELECT ON sys.dependency_functions_on_triggers TO PUBLIC;\n"
735"CREATE VIEW sys.dependency_tables_on_indexes AS\n"
736"SELECT t.schema_id AS table_schema_id, t.id AS table_id, t.name AS table_name, i.id AS index_id, i.name AS index_name, i.type AS index_type, CAST(10 AS smallint) AS depend_type\n"
737" FROM sys.tables AS t, sys.idxs AS i\n"
738" WHERE i.table_id = t.id\n"
739" -- exclude internal system generated and managed indexes for enforcing declarative PKey and Unique constraints\n"
740" AND (i.table_id, i.name) NOT IN (SELECT k.table_id, k.name FROM sys.keys k)\n"
741" ORDER BY t.schema_id, t.name, i.name;\n"
742"GRANT SELECT ON sys.dependency_tables_on_indexes TO PUBLIC;\n"
743"CREATE VIEW sys.dependency_columns_on_indexes AS\n"
744"SELECT c.id AS column_id, c.name AS column_name, t.id AS table_id, t.name AS table_name, t.schema_id, i.id AS index_id, i.name AS index_name, i.type AS index_type, CAST(ic.nr +1 AS INT) AS seq_nr, CAST(10 AS smallint) AS depend_type\n"
745" FROM sys.tables AS t, sys.columns AS c, sys.objects AS ic, sys.idxs AS i\n"
746" WHERE ic.name = c.name AND ic.id = i.id AND c.table_id = i.table_id AND c.table_id = t.id\n"
747" -- exclude internal system generated and managed indexes for enforcing declarative PKey and Unique constraints\n"
748" AND (i.table_id, i.name) NOT IN (SELECT k.table_id, k.name FROM sys.keys k)\n"
749" ORDER BY c.name, t.name, t.schema_id, i.name, ic.nr;\n"
750"GRANT SELECT ON sys.dependency_columns_on_indexes TO PUBLIC;\n"
751"CREATE VIEW sys.dependency_tables_on_foreignkeys AS\n"
752"SELECT t.schema_id AS table_schema_id, t.id AS table_id, t.name AS table_name, fk.name AS fk_name, CAST(k.type AS smallint) AS key_type, CAST(11 AS smallint) AS depend_type\n"
753" FROM sys.tables AS t, sys.keys AS k, sys.keys AS fk\n"
754" WHERE fk.rkey = k.id and k.table_id = t.id\n"
755" ORDER BY t.schema_id, t.name, fk.name;\n"
756"GRANT SELECT ON sys.dependency_tables_on_foreignkeys TO PUBLIC;\n"
757"CREATE VIEW sys.dependency_keys_on_foreignkeys AS\n"
758"SELECT k.table_id AS key_table_id, k.id AS key_id, k.name AS key_name, fk.table_id AS fk_table_id, fk.id AS fk_id, fk.name AS fk_name, CAST(k.type AS smallint) AS key_type, CAST(11 AS smallint) AS depend_type\n"
759" FROM sys.keys AS k, sys.keys AS fk\n"
760" WHERE k.id = fk.rkey\n"
761" ORDER BY k.name, fk.name;\n"
762"GRANT SELECT ON sys.dependency_keys_on_foreignkeys TO PUBLIC;\n"
763"CREATE VIEW sys.dependency_tables_on_procedures AS\n"
764"SELECT t.schema_id AS table_schema_id, t.id AS table_id, t.name AS table_name, p.id AS procedure_id, p.name AS procedure_name, p.type AS procedure_type, dep.depend_type AS depend_type\n"
765" FROM sys.functions AS p, sys.tables AS t, sys.dependencies AS dep\n"
766" WHERE t.id = dep.id AND p.id = dep.depend_id\n"
767" AND dep.depend_type = 13 AND p.type = 2 AND t.type NOT IN (1, 11)\n"
768" ORDER BY t.name, t.schema_id, p.name, p.id;\n"
769"GRANT SELECT ON sys.dependency_tables_on_procedures TO PUBLIC;\n"
770"CREATE VIEW sys.dependency_views_on_procedures AS\n"
771"SELECT v.schema_id AS view_schema_id, v.id AS view_id, v.name AS view_name, p.id AS procedure_id, p.name AS procedure_name, p.type AS procedure_type, dep.depend_type AS depend_type\n"
772" FROM sys.functions AS p, sys.tables AS v, sys.dependencies AS dep\n"
773" WHERE v.id = dep.id AND p.id = dep.depend_id\n"
774" AND dep.depend_type = 13 AND p.type = 2 AND v.type IN (1, 11)\n"
775" ORDER BY v.name, v.schema_id, p.name, p.id;\n"
776"GRANT SELECT ON sys.dependency_views_on_procedures TO PUBLIC;\n"
777"CREATE VIEW sys.dependency_columns_on_procedures AS\n"
778"SELECT c.table_id, c.id AS column_id, c.name AS column_name, p.id AS procedure_id, p.name AS procedure_name, p.type AS procedure_type, dep.depend_type AS depend_type\n"
779" FROM sys.functions AS p, sys.columns AS c, sys.dependencies AS dep\n"
780" WHERE c.id = dep.id AND p.id = dep.depend_id\n"
781" AND dep.depend_type = 13 AND p.type = 2\n"
782" ORDER BY c.name, c.table_id, p.name, p.id;\n"
783"GRANT SELECT ON sys.dependency_columns_on_procedures TO PUBLIC;\n"
784"CREATE VIEW sys.dependency_functions_on_procedures AS\n"
785"SELECT f.schema_id AS function_schema_id, f.id AS function_id, f.name AS function_name, f.type AS function_type,\n"
786" p.schema_id AS procedure_schema_id, p.id AS procedure_id, p.name AS procedure_name, p.type AS procedure_type, dep.depend_type AS depend_type\n"
787" FROM sys.functions AS p, sys.functions AS f, sys.dependencies AS dep\n"
788" WHERE f.id = dep.id AND p.id = dep.depend_id\n"
789" AND dep.depend_type = 13 AND p.type = 2\n"
790" ORDER BY p.name, p.id, f.name, f.id;\n"
791"GRANT SELECT ON sys.dependency_functions_on_procedures TO PUBLIC;\n"
792"CREATE VIEW sys.dependency_columns_on_types AS\n"
793"SELECT t.schema_id AS table_schema_id, t.id AS table_id, t.name AS table_name, dt.id AS type_id, dt.sqlname AS type_name, c.id AS column_id, c.name AS column_name, dep.depend_type AS depend_type\n"
794" FROM sys.tables AS t, sys.columns AS c, sys.types AS dt, sys.dependencies AS dep\n"
795" WHERE dep.id = dt.id AND dep.depend_id = c.id AND c.table_id = t.id\n"
796" AND dep.depend_type = 15\n"
797" ORDER BY dt.sqlname, t.name, c.name, c.id;\n"
798"GRANT SELECT ON sys.dependency_columns_on_types TO PUBLIC;\n"
799"CREATE VIEW sys.dependency_functions_on_types AS\n"
800"SELECT dt.id AS type_id, dt.sqlname AS type_name, f.id AS function_id, f.name AS function_name, f.type AS function_type, dep.depend_type AS depend_type\n"
801" FROM sys.functions AS f, sys.types AS dt, sys.dependencies AS dep\n"
802" WHERE dep.id = dt.id AND dep.depend_id = f.id\n"
803" AND dep.depend_type = 15\n"
804" ORDER BY dt.sqlname, f.name, f.id;\n"
805"GRANT SELECT ON sys.dependency_functions_on_types TO PUBLIC;\n"
806"CREATE VIEW sys.dependency_args_on_types AS\n"
807"SELECT dt.id AS type_id, dt.sqlname AS type_name, f.id AS function_id, f.name AS function_name, a.id AS arg_id, a.name AS arg_name, a.number AS arg_nr, dep.depend_type AS depend_type\n"
808" FROM sys.args AS a, sys.functions AS f, sys.types AS dt, sys.dependencies AS dep\n"
809" WHERE dep.id = dt.id AND dep.depend_id = a.id AND a.func_id = f.id\n"
810" AND dep.depend_type = 15\n"
811" ORDER BY dt.sqlname, f.name, a.number, a.name;\n"
812"GRANT SELECT ON sys.dependency_args_on_types TO PUBLIC;\n"
813"UPDATE sys._tables SET system = true\n"
814" WHERE name IN ('ids', 'dependencies_vw', 'dependency_owners_on_schemas', 'dependency_columns_on_keys',\n"
815" 'dependency_tables_on_views', 'dependency_views_on_views', 'dependency_columns_on_views', 'dependency_functions_on_views',\n"
816" 'dependency_schemas_on_users',\n"
817" 'dependency_tables_on_functions', 'dependency_views_on_functions', 'dependency_columns_on_functions', 'dependency_functions_on_functions',\n"
818" 'dependency_tables_on_triggers', 'dependency_columns_on_triggers', 'dependency_functions_on_triggers',\n"
819" 'dependency_tables_on_indexes', 'dependency_columns_on_indexes',\n"
820" 'dependency_tables_on_foreignkeys', 'dependency_keys_on_foreignkeys',\n"
821" 'dependency_tables_on_procedures', 'dependency_views_on_procedures', 'dependency_columns_on_procedures', 'dependency_functions_on_procedures',\n"
822" 'dependency_columns_on_types', 'dependency_functions_on_types', 'dependency_args_on_types')\n"
823" AND schema_id IN (SELECT id FROM sys.schemas WHERE name = 'sys');\n"
824 );
825
826 /* 25_debug.sql */
827 t = mvc_bind_table(sql, s, "environment");
828 t->system = 0;
829 pos += snprintf(buf + pos, bufsize - pos,
830 "drop view sys.environment cascade;\n"
831 "drop function sys.environment() cascade;\n"
832 "create view sys.environment as select * from sys.env();\n"
833 "GRANT SELECT ON sys.environment TO PUBLIC;\n"
834 "update sys._tables set system = true where system = false and name = 'environment' and schema_id in (select id from sys.schemas where name = 'sys');\n");
835
836 /* 39_analytics.sql, 39_analytics_hge.sql */
837 pos += snprintf(buf + pos, bufsize - pos,
838 "drop aggregate corr(tinyint, tinyint);\n"
839 "drop aggregate corr(smallint, smallint);\n"
840 "drop aggregate corr(integer, integer);\n"
841 "drop aggregate corr(bigint, bigint);\n"
842 "drop aggregate corr(real, real);\n");
843#ifdef HAVE_HGE
844 if (have_hge)
845 pos += snprintf(buf + pos, bufsize - pos,
846 "drop aggregate corr(hugeint, hugeint);\n");
847#endif
848 pos += snprintf(buf + pos, bufsize - pos,
849 "create aggregate corr(e1 TINYINT, e2 TINYINT) returns DOUBLE\n\texternal name \"aggr\".\"corr\";\n"
850 "grant execute on aggregate sys.corr(tinyint, tinyint) to public;\n"
851 "create aggregate corr(e1 SMALLINT, e2 SMALLINT) returns DOUBLE\n\texternal name \"aggr\".\"corr\";\n"
852 "grant execute on aggregate sys.corr(smallint, smallint) to public;\n"
853 "create aggregate corr(e1 INTEGER, e2 INTEGER) returns DOUBLE\n\texternal name \"aggr\".\"corr\";\n"
854 "grant execute on aggregate sys.corr(integer, integer) to public;\n"
855 "create aggregate corr(e1 BIGINT, e2 BIGINT) returns DOUBLE\n\texternal name \"aggr\".\"corr\";\n"
856 "grant execute on aggregate sys.corr(bigint, bigint) to public;\n"
857 "create aggregate corr(e1 REAL, e2 REAL) returns DOUBLE\n\texternal name \"aggr\".\"corr\";\n"
858 "grant execute on aggregate sys.corr(real, real) to public;\n");
859#ifdef HAVE_HGE
860 if (have_hge)
861 pos += snprintf(buf + pos, bufsize - pos,
862 "create aggregate corr(e1 HUGEINT, e2 HUGEINT) returns DOUBLE\n\texternal name \"aggr\".\"corr\";\n"
863 "grant execute on aggregate sys.corr(hugeint, hugeint) to public;\n");
864#endif
865 pos += snprintf(buf + pos, bufsize - pos,
866 "update sys.functions set system = true where name = 'corr' and schema_id = (select id from sys.schemas where name = 'sys');\n");
867
868 /* 51_sys_schema_extensions.sql */
869 t = mvc_bind_table(sql, s, "privilege_codes");
870 t->system = 0;
871 pos += snprintf(buf + pos, bufsize - pos,
872 "CREATE VIEW sys.roles AS SELECT id, name, grantor FROM sys.auths a WHERE a.name NOT IN (SELECT u.name FROM sys.db_users() u);\n"
873 "GRANT SELECT ON sys.roles TO PUBLIC;\n"
874 "CREATE VIEW sys.var_values (var_name, value) AS\n"
875 "SELECT 'cache' AS var_name, convert(cache, varchar(10)) AS value UNION ALL\n"
876 "SELECT 'current_role', current_role UNION ALL\n"
877 "SELECT 'current_schema', current_schema UNION ALL\n"
878 "SELECT 'current_timezone', current_timezone UNION ALL\n"
879 "SELECT 'current_user', current_user UNION ALL\n"
880 "SELECT 'debug', debug UNION ALL\n"
881 "SELECT 'last_id', last_id UNION ALL\n"
882 "SELECT 'optimizer', optimizer UNION ALL\n"
883 "SELECT 'pi', pi() UNION ALL\n"
884 "SELECT 'rowcnt', rowcnt;\n"
885 "GRANT SELECT ON sys.var_values TO PUBLIC;\n"
886 "UPDATE sys._tables SET system = true\n"
887 " WHERE name IN ('roles', 'var_values') AND schema_id IN (SELECT id FROM sys.schemas WHERE name = 'sys');\n"
888 "ALTER TABLE sys.privilege_codes SET READ WRITE;\n"
889 "DROP TABLE sys.privilege_codes;\n"
890 "CREATE TABLE sys.privilege_codes (\n"
891 " privilege_code_id INT NOT NULL PRIMARY KEY,\n"
892 " privilege_code_name VARCHAR(40) NOT NULL UNIQUE);\n"
893 "INSERT INTO sys.privilege_codes (privilege_code_id, privilege_code_name) VALUES\n"
894 " (1, 'SELECT'),\n"
895 " (2, 'UPDATE'),\n"
896 " (4, 'INSERT'),\n"
897 " (8, 'DELETE'),\n"
898 " (16, 'EXECUTE'),\n"
899 " (32, 'GRANT'),\n"
900 " (64, 'TRUNCATE'),\n"
901 " (3, 'SELECT,UPDATE'),\n"
902 " (5, 'SELECT,INSERT'),\n"
903 " (6, 'INSERT,UPDATE'),\n"
904 " (7, 'SELECT,INSERT,UPDATE'),\n"
905 " (9, 'SELECT,DELETE'),\n"
906 " (10, 'UPDATE,DELETE'),\n"
907 " (11, 'SELECT,UPDATE,DELETE'),\n"
908 " (12, 'INSERT,DELETE'),\n"
909 " (13, 'SELECT,INSERT,DELETE'),\n"
910 " (14, 'INSERT,UPDATE,DELETE'),\n"
911 " (15, 'SELECT,INSERT,UPDATE,DELETE'),\n"
912 " (65, 'SELECT,TRUNCATE'),\n"
913 " (66, 'UPDATE,TRUNCATE'),\n"
914 " (68, 'INSERT,TRUNCATE'),\n"
915 " (72, 'DELETE,TRUNCATE'),\n"
916 " (67, 'SELECT,UPDATE,TRUNCATE'),\n"
917 " (69, 'SELECT,INSERT,TRUNCATE'),\n"
918 " (73, 'SELECT,DELETE,TRUNCATE'),\n"
919 " (70, 'INSERT,UPDATE,TRUNCATE'),\n"
920 " (76, 'INSERT,DELETE,TRUNCATE'),\n"
921 " (74, 'UPDATE,DELETE,TRUNCATE'),\n"
922 " (71, 'SELECT,INSERT,UPDATE,TRUNCATE'),\n"
923 " (75, 'SELECT,UPDATE,DELETE,TRUNCATE'),\n"
924 " (77, 'SELECT,INSERT,DELETE,TRUNCATE'),\n"
925 " (78, 'INSERT,UPDATE,DELETE,TRUNCATE'),\n"
926 " (79, 'SELECT,INSERT,UPDATE,DELETE,TRUNCATE');\n"
927 "ALTER TABLE sys.privilege_codes SET READ ONLY;\n"
928 "GRANT SELECT ON sys.privilege_codes TO PUBLIC;\n"
929 "UPDATE sys._tables "
930 "SET system = TRUE "
931 "WHERE name = 'privilege_codes' "
932 "AND schema_id = (SELECT id FROM sys.schemas WHERE name = 'sys');\n"
933 "ALTER TABLE sys.keywords SET READ WRITE;\n"
934 "INSERT INTO sys.keywords VALUES ('COMMENT'), ('CONTINUE'), ('START'), ('TRUNCATE');\n"
935 "ALTER TABLE sys.function_types SET READ WRITE;\n"
936 "ALTER TABLE function_types ADD COLUMN function_type_keyword VARCHAR(30);\n"
937 "UPDATE sys.function_types SET function_type_keyword =\n"
938 " (SELECT kw FROM (VALUES\n"
939 " (1, 'FUNCTION'),\n"
940 " (2, 'PROCEDURE'),\n"
941 " (3, 'AGGREGATE'),\n"
942 " (4, 'FILTER FUNCTION'),\n"
943 " (5, 'FUNCTION'),\n"
944 " (6, 'FUNCTION'),\n"
945 " (7, 'LOADER'))\n"
946 " AS ft (id, kw) WHERE function_type_id = id);\n"
947 "ALTER TABLE sys.function_types ALTER COLUMN function_type_keyword SET NOT NULL;\n"
948 "ALTER TABLE sys.function_languages SET READ WRITE;\n"
949 "ALTER TABLE sys.function_languages ADD COLUMN language_keyword VARCHAR(20);\n"
950 "UPDATE sys.function_languages SET language_keyword =\n"
951 " (SELECT kw FROM (VALUES\n"
952 " (3, 'R'),\n"
953 " (6, 'PYTHON'),\n"
954 " (7, 'PYTHON_MAP'),\n"
955 " (8, 'PYTHON2'),\n"
956 " (9, 'PYTHON2_MAP'),\n"
957 " (10, 'PYTHON3'),\n"
958 " (11, 'PYTHON3_MAP'))\n"
959 " AS ft (id, kw) WHERE language_id = id);\n"
960 "INSERT INTO sys.function_languages VALUES (4, 'C', 'C'), (12, 'C++', 'CPP');\n"
961 );
962
963 /* 60_wlcr.sql */
964 pos += snprintf(buf + pos, bufsize - pos,
965 "create procedure master()\n"
966 "external name wlc.master;\n"
967 "create procedure master(path string)\n"
968 "external name wlc.master;\n"
969 "create procedure stopmaster()\n"
970 "external name wlc.stopmaster;\n"
971 "create procedure masterbeat( duration int)\n"
972 "external name wlc.\"setmasterbeat\";\n"
973 "create function masterClock() returns string\n"
974 "external name wlc.\"getmasterclock\";\n"
975 "create function masterTick() returns bigint\n"
976 "external name wlc.\"getmastertick\";\n"
977 "create procedure replicate()\n"
978 "external name wlr.replicate;\n"
979 "create procedure replicate(pointintime timestamp)\n"
980 "external name wlr.replicate;\n"
981 "create procedure replicate(dbname string)\n"
982 "external name wlr.replicate;\n"
983 "create procedure replicate(dbname string, pointintime timestamp)\n"
984 "external name wlr.replicate;\n"
985 "create procedure replicate(dbname string, id tinyint)\n"
986 "external name wlr.replicate;\n"
987 "create procedure replicate(dbname string, id smallint)\n"
988 "external name wlr.replicate;\n"
989 "create procedure replicate(dbname string, id integer)\n"
990 "external name wlr.replicate;\n"
991 "create procedure replicate(dbname string, id bigint)\n"
992 "external name wlr.replicate;\n"
993 "create procedure replicabeat(duration integer)\n"
994 "external name wlr.\"setreplicabeat\";\n"
995 "create function replicaClock() returns string\n"
996 "external name wlr.\"getreplicaclock\";\n"
997 "create function replicaTick() returns bigint\n"
998 "external name wlr.\"getreplicatick\";\n"
999 "update sys.functions set system = true where name in ('master', 'stopmaster', 'masterbeat', 'masterclock', 'mastertick', 'replicate', 'replicabeat', 'replicaclock', 'replicatick') and schema_id = (select id from sys.schemas where name = 'sys');\n"
1000 );
1001
1002 /* comments */
1003 pos += snprintf(buf + pos, bufsize - pos,
1004 "UPDATE sys._tables\n"
1005 "SET system = true\n"
1006 "WHERE name = 'comments'\n"
1007 "AND schema_id = (SELECT id FROM sys.schemas WHERE name = 'sys');\n"
1008 );
1009
1010 pos += snprintf(buf + pos, bufsize - pos, "set schema \"%s\";\n", prev_schema);
1011
1012 assert(pos < bufsize);
1013 printf("Running database upgrade commands:\n%s\n", buf);
1014 err = SQLstatementIntern(c, &buf, "update", true, false, NULL);
1015 if (err == MAL_SUCCEED) {
1016 pos = snprintf(buf, bufsize, "set schema \"sys\";\n"
1017 "ALTER TABLE sys.keywords SET READ ONLY;\n"
1018 "ALTER TABLE sys.function_types SET READ ONLY;\n"
1019 "ALTER TABLE sys.function_languages SET READ ONLY;\n");
1020 pos += snprintf(buf + pos, bufsize - pos, "set schema \"%s\";\n", prev_schema);
1021 assert(pos < bufsize);
1022 printf("Running database upgrade commands:\n%s\n", buf);
1023 err = SQLstatementIntern(c, &buf, "update", true, false, NULL);
1024 }
1025 GDKfree(buf);
1026 return err; /* usually MAL_SUCCEED */
1027}
1028
1029#ifdef HAVE_NETCDF
1030static str
1031sql_update_mar2018_netcdf(Client c, const char *prev_schema)
1032{
1033 size_t bufsize = 1000, pos = 0;
1034 char *buf = GDKmalloc(bufsize), *err;
1035
1036 if (buf == NULL)
1037 throw(SQL, "sql_update_mar2018_netcdf", SQLSTATE(HY001) MAL_MALLOC_FAIL);
1038
1039 pos += snprintf(buf + pos, bufsize - pos, "set schema sys;\n");
1040
1041 /* 74_netcdf.sql */
1042 pos += snprintf(buf + pos, bufsize - pos,
1043 "grant select on sys.netcdf_files to public;\n"
1044 "grant select on sys.netcdf_dims to public;\n"
1045 "grant select on sys.netcdf_vars to public;\n"
1046 "grant select on sys.netcdf_vardim to public;\n"
1047 "grant select on sys.netcdf_attrs to public;\n"
1048 "grant execute on procedure sys.netcdf_attach(varchar(256)) to public;\n"
1049 "grant execute on procedure sys.netcdf_importvar(integer, varchar(256)) to public;\n");
1050
1051 pos += snprintf(buf + pos, bufsize - pos, "set schema \"%s\";\n", prev_schema);
1052
1053 assert(pos < bufsize);
1054 printf("Running database upgrade commands:\n%s\n", buf);
1055 err = SQLstatementIntern(c, &buf, "update", true, false, NULL);
1056 GDKfree(buf);
1057 return err; /* usually MAL_SUCCEED */
1058}
1059#endif /* HAVE_NETCDF */
1060
1061#ifdef HAVE_SAMTOOLS
1062static str
1063sql_update_mar2018_samtools(Client c, mvc *sql, const char *prev_schema)
1064{
1065 size_t bufsize = 2000, pos = 0;
1066 char *buf, *err;
1067 sql_schema *s = mvc_bind_schema(sql, "bam");
1068
1069 if (s == NULL)
1070 return MAL_SUCCEED;
1071
1072 buf = GDKmalloc(bufsize);
1073 if (buf == NULL)
1074 throw(SQL, "sql_update_mar2018_samtools", SQLSTATE(HY001) MAL_MALLOC_FAIL);
1075
1076 pos += snprintf(buf + pos, bufsize - pos, "set schema sys;\n");
1077
1078 /* 85_bam.sql */
1079 list *l = sa_list(sql->sa);
1080 sql_subtype tpi, tps;
1081 sql_find_subtype(&tpi, "int", 0, 0);
1082 sql_find_subtype(&tps, "clob", 0, 0);
1083 list_append(l, &tpi);
1084 list_append(l, &tps);
1085 list_append(l, &tpi);
1086 list_append(l, &tps);
1087 if (sql_bind_func_(sql->sa, s, "seq_char", l, F_FUNC) == NULL) {
1088 pos += snprintf(buf + pos, bufsize - pos,
1089 "CREATE FUNCTION bam.seq_char(ref_pos INT, alg_seq STRING, alg_pos INT, alg_cigar STRING)\n"
1090 "RETURNS CHAR(1) EXTERNAL NAME bam.seq_char;\n"
1091 "update sys.functions set system = true where name in ('seq_char') and schema_id = (select id from sys.schemas where name = 'bam');\n");
1092 }
1093 sql_find_subtype(&tpi, "smallint", 0, 0);
1094 if (sql_bind_func3(sql->sa, s, "bam_loader_repos", &tps, &tpi, &tpi, F_PROC) != NULL) {
1095 pos += snprintf(buf + pos, bufsize - pos,
1096 "drop procedure bam.bam_loader_repos(string, smallint, smallint);\n"
1097 "drop procedure bam.bam_loader_files(string, smallint, smallint);\n");
1098 }
1099 if (sql_bind_func(sql->sa, s, "bam_loader_repos", &tps, &tpi, F_PROC) == NULL) {
1100 pos += snprintf(buf + pos, bufsize - pos,
1101 "CREATE PROCEDURE bam.bam_loader_repos(bam_repos STRING, dbschema SMALLINT)\n"
1102 "EXTERNAL NAME bam.bam_loader_repos;\n"
1103 "CREATE PROCEDURE bam.bam_loader_files(bam_files STRING, dbschema SMALLINT)\n"
1104 "EXTERNAL NAME bam.bam_loader_files;\n"
1105 "update sys.functions set system = true where name in ('bam_loader_repos', 'bam_loader_files') and schema_id = (select id from sys.schemas where name = 'bam');\n");
1106 }
1107
1108 pos += snprintf(buf + pos, bufsize - pos,
1109 "GRANT SELECT ON bam.files TO PUBLIC;\n"
1110 "GRANT SELECT ON bam.sq TO PUBLIC;\n"
1111 "GRANT SELECT ON bam.rg TO PUBLIC;\n"
1112 "GRANT SELECT ON bam.pg TO PUBLIC;\n"
1113 "GRANT SELECT ON bam.export TO PUBLIC;\n"
1114 "GRANT EXECUTE ON FUNCTION bam.bam_flag(SMALLINT, STRING) TO PUBLIC;\n"
1115 "GRANT EXECUTE ON FUNCTION bam.reverse_seq(STRING) TO PUBLIC;\n"
1116 "GRANT EXECUTE ON FUNCTION bam.reverse_qual(STRING) TO PUBLIC;\n"
1117 "GRANT EXECUTE ON FUNCTION bam.seq_length(STRING) TO PUBLIC;\n"
1118 "GRANT EXECUTE ON FUNCTION bam.seq_char(INT, STRING, INT, STRING) TO PUBLIC;\n"
1119 "GRANT EXECUTE ON PROCEDURE bam.bam_loader_repos(STRING, SMALLINT) TO PUBLIC;\n"
1120 "GRANT EXECUTE ON PROCEDURE bam.bam_loader_files(STRING, SMALLINT) TO PUBLIC;\n"
1121 "GRANT EXECUTE ON PROCEDURE bam.bam_loader_file(STRING, SMALLINT) TO PUBLIC;\n"
1122 "GRANT EXECUTE ON PROCEDURE bam.bam_drop_file(BIGINT, SMALLINT) TO PUBLIC;\n"
1123 "GRANT EXECUTE ON PROCEDURE bam.sam_export(STRING) TO PUBLIC;\n"
1124 "GRANT EXECUTE ON PROCEDURE bam.bam_export(STRING) TO PUBLIC;\n");
1125
1126 pos += snprintf(buf + pos, bufsize - pos, "set schema \"%s\";\n", prev_schema);
1127
1128 assert(pos < bufsize);
1129 printf("Running database upgrade commands:\n%s\n", buf);
1130 err = SQLstatementIntern(c, &buf, "update", true, false, NULL);
1131 GDKfree(buf);
1132 return err; /* usually MAL_SUCCEED */
1133}
1134#endif /* HAVE_SAMTOOLS */
1135
1136static str
1137sql_update_mar2018_sp1(Client c, const char *prev_schema)
1138{
1139 size_t bufsize = 2048, pos = 0;
1140 char *buf = GDKmalloc(bufsize), *err = NULL;
1141
1142 if (buf == NULL)
1143 throw(SQL, "sql_update_mar2018_sp1", SQLSTATE(HY001) MAL_MALLOC_FAIL);
1144 pos += snprintf(buf + pos, bufsize - pos,
1145 "set schema \"sys\";\n"
1146 "drop function sys.dependencies_functions_os_triggers();\n"
1147 "CREATE FUNCTION dependencies_functions_on_triggers()\n"
1148 "RETURNS TABLE (sch varchar(100), usr varchar(100), dep_type varchar(32))\n"
1149 "RETURN TABLE (SELECT f.name, tri.name, 'DEP_TRIGGER' from functions as f, triggers as tri, dependencies as dep where dep.id = f.id AND dep.depend_id =tri.id AND dep.depend_type = 8);\n"
1150 "update sys.functions set system = true where name in ('dependencies_functions_on_triggers') and schema_id = (select id from sys.schemas where name = 'sys');\n");
1151
1152 pos += snprintf(buf + pos, bufsize - pos, "set schema \"%s\";\n", prev_schema);
1153 assert(pos < bufsize);
1154
1155 printf("Running database upgrade commands:\n%s\n", buf);
1156 err = SQLstatementIntern(c, &buf, "update", true, false, NULL);
1157 GDKfree(buf);
1158 return err; /* usually MAL_SUCCEED */
1159}
1160
1161static str
1162sql_update_remote_tables(Client c, mvc *sql, const char *prev_schema)
1163{
1164 res_table *output = NULL;
1165 char* err = MAL_SUCCEED, *buf;
1166 size_t bufsize = 1000, pos = 0;
1167 BAT *tbl = NULL, *uri = NULL;
1168
1169 if ((buf = GDKmalloc(bufsize)) == NULL)
1170 throw(SQL, "sql_update_remote_tables", SQLSTATE(HY001) MAL_MALLOC_FAIL);
1171
1172 /* Create the SQL function needed to dump the remote table credentials */
1173 pos += snprintf(buf + pos, bufsize - pos, "set schema sys;\n");
1174 pos += snprintf(buf + pos, bufsize - pos,
1175 "create function sys.remote_table_credentials (tablename string)"
1176 " returns table (\"uri\" string, \"username\" string, \"hash\" string)"
1177 " external name sql.rt_credentials;\n"
1178 "update sys.functions set system = true where name = 'remote_table_credentials' and schema_id = (select id from sys.schemas where name = 'sys');\n");
1179
1180 pos += snprintf(buf + pos, bufsize - pos, "set schema \"%s\";\n", prev_schema);
1181
1182 assert(pos < bufsize);
1183 printf("Running database upgrade commands:\n%s\n", buf);
1184 err = SQLstatementIntern(c, &buf, "create function", true, false, NULL);
1185 if (err)
1186 goto bailout;
1187
1188 pos = 0;
1189 pos += snprintf(buf + pos, bufsize - pos,
1190 "SELECT concat(concat(scm.name, '.'), tbl.name), tbl.query"
1191 " FROM sys._tables AS tbl JOIN sys.schemas AS scm ON"
1192 " tbl.schema_id=scm.id WHERE tbl.type=5;\n");
1193
1194 assert(pos < bufsize);
1195
1196 err = SQLstatementIntern(c, &buf, "get remote table names", true, false, &output);
1197 if (err)
1198 goto bailout;
1199
1200 /* We executed the query, now process the results */
1201 tbl = BATdescriptor(output->cols[0].b);
1202 uri = BATdescriptor(output->cols[1].b);
1203
1204 if (tbl && uri) {
1205 size_t cnt;
1206 assert(BATcount(tbl) == BATcount(uri));
1207 if ((cnt = BATcount(tbl)) > 0) {
1208 BATiter tbl_it = bat_iterator(tbl);
1209 BATiter uri_it = bat_iterator(uri);
1210 const void *restrict nil = ATOMnilptr(tbl->ttype);
1211 int (*cmp)(const void *, const void *) = ATOMcompare(tbl->ttype);
1212 const char *v;
1213 const char *u;
1214 const char *remote_server_uri;
1215
1216 /* This is probably not correct: offsets? */
1217 for (BUN i = 0; i < cnt; i++) {
1218 v = BUNtvar(tbl_it, i);
1219 u = BUNtvar(uri_it, i);
1220 if (v == NULL || (*cmp)(v, nil) == 0 ||
1221 u == NULL || (*cmp)(u, nil) == 0)
1222 goto bailout;
1223
1224 /* Since the loop might fail, it might be a good idea
1225 * to update the credentials as a second step
1226 */
1227 remote_server_uri = mapiuri_uri((char *)u, sql->sa);
1228 if ((err = AUTHaddRemoteTableCredentials((char *)v, "monetdb", remote_server_uri, "monetdb", "monetdb", false)) != MAL_SUCCEED)
1229 goto bailout;
1230 }
1231 }
1232 } else {
1233 err = createException(SQL, "sql_update_remote_tables", SQLSTATE(HY002) RUNTIME_OBJECT_MISSING);
1234 }
1235
1236bailout:
1237 if (tbl)
1238 BBPunfix(tbl->batCacheid);
1239 if (uri)
1240 BBPunfix(uri->batCacheid);
1241 if (output)
1242 res_table_destroy(output);
1243 GDKfree(buf);
1244 return err; /* usually MAL_SUCCEED */
1245}
1246
1247static str
1248sql_replace_Mar2018_ids_view(Client c, mvc *sql, const char *prev_schema)
1249{
1250 size_t bufsize = 4400, pos = 0;
1251 char *buf = GDKmalloc(bufsize), *err = NULL;
1252 sql_schema *s = mvc_bind_schema(sql, "sys");
1253 sql_table *t = mvc_bind_table(sql, s, "ids");
1254
1255 if (buf == NULL)
1256 throw(SQL, "sql_replace_Mar2018_ids_view", SQLSTATE(HY001) MAL_MALLOC_FAIL);
1257
1258 t->system = 0; /* make it non-system else the drop view will fail */
1259 t = mvc_bind_table(sql, s, "dependencies_vw"); /* dependencies_vw uses view sys.ids so must be removed first */
1260 t->system = 0;
1261
1262 /* 21_dependency_views.sql */
1263 pos += snprintf(buf + pos, bufsize - pos,
1264 "set schema \"sys\";\n"
1265 "DROP VIEW sys.dependencies_vw;\n"
1266 "DROP VIEW sys.ids;\n"
1267
1268 "CREATE VIEW sys.ids (id, name, schema_id, table_id, table_name, obj_type, sys_table) AS\n"
1269 "SELECT id, name, cast(null as int) as schema_id, cast(null as int) as table_id, cast(null as varchar(124)) as table_name, 'author' AS obj_type, 'sys.auths' AS sys_table FROM sys.auths UNION ALL\n"
1270 "SELECT id, name, cast(null as int) as schema_id, cast(null as int) as table_id, cast(null as varchar(124)) as table_name, 'schema', 'sys.schemas' FROM sys.schemas UNION ALL\n"
1271 "SELECT id, name, schema_id, id as table_id, name as table_name, case when type = 1 then 'view' else 'table' end, 'sys._tables' FROM sys._tables UNION ALL\n"
1272 "SELECT id, name, schema_id, id as table_id, name as table_name, case when type = 1 then 'view' else 'table' end, 'tmp._tables' FROM tmp._tables UNION ALL\n"
1273 "SELECT c.id, c.name, t.schema_id, c.table_id, t.name as table_name, 'column', 'sys._columns' FROM sys._columns c JOIN sys._tables t ON c.table_id = t.id UNION ALL\n"
1274 "SELECT c.id, c.name, t.schema_id, c.table_id, t.name as table_name, 'column', 'tmp._columns' FROM tmp._columns c JOIN tmp._tables t ON c.table_id = t.id UNION ALL\n"
1275 "SELECT k.id, k.name, t.schema_id, k.table_id, t.name as table_name, 'key', 'sys.keys' FROM sys.keys k JOIN sys._tables t ON k.table_id = t.id UNION ALL\n"
1276 "SELECT k.id, k.name, t.schema_id, k.table_id, t.name as table_name, 'key', 'tmp.keys' FROM tmp.keys k JOIN tmp._tables t ON k.table_id = t.id UNION ALL\n"
1277 "SELECT i.id, i.name, t.schema_id, i.table_id, t.name as table_name, 'index', 'sys.idxs' FROM sys.idxs i JOIN sys._tables t ON i.table_id = t.id UNION ALL\n"
1278 "SELECT i.id, i.name, t.schema_id, i.table_id, t.name as table_name, 'index', 'tmp.idxs' FROM tmp.idxs i JOIN tmp._tables t ON i.table_id = t.id UNION ALL\n"
1279 "SELECT g.id, g.name, t.schema_id, g.table_id, t.name as table_name, 'trigger', 'sys.triggers' FROM sys.triggers g JOIN sys._tables t ON g.table_id = t.id UNION ALL\n"
1280 "SELECT g.id, g.name, t.schema_id, g.table_id, t.name as table_name, 'trigger', 'tmp.triggers' FROM tmp.triggers g JOIN tmp._tables t ON g.table_id = t.id UNION ALL\n"
1281 "SELECT id, name, schema_id, cast(null as int) as table_id, cast(null as varchar(124)) as table_name, case when type = 2 then 'procedure' else 'function' end, 'sys.functions' FROM sys.functions UNION ALL\n"
1282 "SELECT a.id, a.name, f.schema_id, cast(null as int) as table_id, cast(null as varchar(124)) as table_name, case when f.type = 2 then 'procedure arg' else 'function arg' end, 'sys.args' FROM sys.args a JOIN sys.functions f ON a.func_id = f.id UNION ALL\n"
1283 "SELECT id, name, schema_id, cast(null as int) as table_id, cast(null as varchar(124)) as table_name, 'sequence', 'sys.sequences' FROM sys.sequences UNION ALL\n"
1284 "SELECT id, sqlname, schema_id, cast(null as int) as table_id, cast(null as varchar(124)) as table_name, 'type', 'sys.types' FROM sys.types WHERE id > 2000 /* exclude system types to prevent duplicates with auths.id */\n"
1285 " ORDER BY id;\n"
1286 "GRANT SELECT ON sys.ids TO PUBLIC;\n"
1287
1288 "CREATE VIEW sys.dependencies_vw AS\n"
1289 "SELECT d.id, i1.obj_type, i1.name,\n"
1290 " d.depend_id as used_by_id, i2.obj_type as used_by_obj_type, i2.name as used_by_name,\n"
1291 " d.depend_type, dt.dependency_type_name\n"
1292 " FROM sys.dependencies d\n"
1293 " JOIN sys.ids i1 ON d.id = i1.id\n"
1294 " JOIN sys.ids i2 ON d.depend_id = i2.id\n"
1295 " JOIN sys.dependency_types dt ON d.depend_type = dt.dependency_type_id\n"
1296 " ORDER BY id, depend_id;\n"
1297 "GRANT SELECT ON sys.dependencies_vw TO PUBLIC;\n"
1298
1299 "update sys._tables set system = true where name in ('ids', 'dependencies_vw') and schema_id in (select id from sys.schemas where name = 'sys');\n"
1300 );
1301
1302 pos += snprintf(buf + pos, bufsize - pos, "set schema \"%s\";\n", prev_schema);
1303 assert(pos < bufsize);
1304
1305 printf("Running database upgrade commands:\n%s\n", buf);
1306 err = SQLstatementIntern(c, &buf, "update", true, false, NULL);
1307 GDKfree(buf);
1308 return err; /* usually MAL_SUCCEED */
1309}
1310
1311static str
1312sql_update_gsl(Client c, const char *prev_schema)
1313{
1314 size_t bufsize = 1024, pos = 0;
1315 char *buf = GDKmalloc(bufsize), *err = NULL;
1316
1317 if (buf == NULL)
1318 throw(SQL, "sql_update_gsl", SQLSTATE(HY001) MAL_MALLOC_FAIL);
1319 pos += snprintf(buf + pos, bufsize - pos,
1320 "set schema \"sys\";\n"
1321 "drop function sys.chi2prob(double, double);\n");
1322
1323 pos += snprintf(buf + pos, bufsize - pos, "set schema \"%s\";\n", prev_schema);
1324 assert(pos < bufsize);
1325
1326 printf("Running database upgrade commands:\n%s\n", buf);
1327 err = SQLstatementIntern(c, &buf, "update", true, false, NULL);
1328 GDKfree(buf);
1329 return err; /* usually MAL_SUCCEED */
1330}
1331
1332static str
1333sql_update_aug2018(Client c, mvc *sql, const char *prev_schema)
1334{
1335 size_t bufsize = 1000, pos = 0;
1336 char *buf, *err;
1337
1338 if ((buf = GDKmalloc(bufsize)) == NULL)
1339 throw(SQL, "sql_update_aug2018", SQLSTATE(HY001) MAL_MALLOC_FAIL);
1340
1341 pos += snprintf(buf + pos, bufsize - pos, "set schema sys;\n");
1342 pos += snprintf(buf + pos, bufsize - pos,
1343 "create aggregate sys.group_concat(str string) returns string external name \"aggr\".\"str_group_concat\";\n"
1344 "grant execute on aggregate sys.group_concat(string) to public;\n"
1345 "create aggregate sys.group_concat(str string, sep string) returns string external name \"aggr\".\"str_group_concat\";\n"
1346 "grant execute on aggregate sys.group_concat(string, string) to public;\n"
1347 "update sys.functions set system = true where name in ('group_concat') and schema_id = (select id from sys.schemas where name = 'sys');\n");
1348
1349 pos += snprintf(buf + pos, bufsize - pos, "set schema \"%s\";\n", prev_schema);
1350
1351 assert(pos < bufsize);
1352 printf("Running database upgrade commands:\n%s\n", buf);
1353 err = SQLstatementIntern(c, &buf, "update", true, false, NULL);
1354 if (err)
1355 goto bailout;
1356 err = sql_update_remote_tables(c, sql, prev_schema);
1357
1358 bailout:
1359 GDKfree(buf);
1360 return err; /* usually MAL_SUCCEED */
1361}
1362
1363static str
1364sql_update_aug2018_sp2(Client c, const char *prev_schema)
1365{
1366 size_t bufsize = 1000, pos = 0;
1367 char *buf, *err;
1368 res_table *output;
1369 BAT *b;
1370
1371 if ((buf = GDKmalloc(bufsize)) == NULL)
1372 throw(SQL, "sql_update_aug2018_sp2", SQLSTATE(HY001) MAL_MALLOC_FAIL);
1373
1374 /* required update for changeset 23e1231ada99 */
1375 pos += snprintf(buf + pos, bufsize - pos,
1376 "select id from sys.functions where language <> 0 and not side_effect and type <> 4 and (type = 2 or (language <> 2 and id not in (select func_id from sys.args where inout = 1)));\n");
1377 err = SQLstatementIntern(c, &buf, "update", true, false, &output);
1378 if (err) {
1379 GDKfree(buf);
1380 return err;
1381 }
1382 b = BATdescriptor(output->cols[0].b);
1383 if (b) {
1384 if (BATcount(b) > 0) {
1385 pos = 0;
1386 pos += snprintf(buf + pos, bufsize - pos, "set schema sys;\n");
1387 pos += snprintf(buf + pos, bufsize - pos,
1388 "update sys.functions set side_effect = true where language <> 0 and not side_effect and type <> 4 and (type = 2 or (language <> 2 and id not in (select func_id from sys.args where inout = 1)));\n");
1389
1390 pos += snprintf(buf + pos, bufsize - pos, "set schema \"%s\";\n", prev_schema);
1391
1392 assert(pos < bufsize);
1393 printf("Running database upgrade commands:\n%s\n", buf);
1394 err = SQLstatementIntern(c, &buf, "update", true, false, NULL);
1395 }
1396 BBPunfix(b->batCacheid);
1397 }
1398 res_table_destroy(output);
1399 GDKfree(buf);
1400 return err; /* usually MAL_SUCCEED */
1401}
1402
1403static str
1404sql_drop_functions_dependencies_Xs_on_Ys(Client c, const char *prev_schema)
1405{
1406 size_t bufsize = 1600, pos = 0;
1407 char *err = NULL, *buf = GDKmalloc(bufsize);
1408
1409 if (buf == NULL)
1410 throw(SQL, "sql_drop_functions_dependencies_Xs_on_Ys", SQLSTATE(HY001) MAL_MALLOC_FAIL);
1411
1412 /* remove functions which were created in sql/scripts/21_dependency_functions.sql */
1413 pos += snprintf(buf + pos, bufsize - pos,
1414 "set schema \"sys\";\n"
1415 "DROP FUNCTION dependencies_schemas_on_users();\n"
1416 "DROP FUNCTION dependencies_owners_on_schemas();\n"
1417 "DROP FUNCTION dependencies_tables_on_views();\n"
1418 "DROP FUNCTION dependencies_tables_on_indexes();\n"
1419 "DROP FUNCTION dependencies_tables_on_triggers();\n"
1420 "DROP FUNCTION dependencies_tables_on_foreignKeys();\n"
1421 "DROP FUNCTION dependencies_tables_on_functions();\n"
1422 "DROP FUNCTION dependencies_columns_on_views();\n"
1423 "DROP FUNCTION dependencies_columns_on_keys();\n"
1424 "DROP FUNCTION dependencies_columns_on_indexes();\n"
1425 "DROP FUNCTION dependencies_columns_on_functions();\n"
1426 "DROP FUNCTION dependencies_columns_on_triggers();\n"
1427 "DROP FUNCTION dependencies_views_on_functions();\n"
1428 "DROP FUNCTION dependencies_views_on_triggers();\n"
1429 "DROP FUNCTION dependencies_functions_on_functions();\n"
1430 "DROP FUNCTION dependencies_functions_on_triggers();\n"
1431 "DROP FUNCTION dependencies_keys_on_foreignKeys();\n");
1432
1433 pos += snprintf(buf + pos, bufsize - pos, "set schema \"%s\";\n", prev_schema);
1434 assert(pos < bufsize);
1435
1436 printf("Running database upgrade commands:\n%s\n", buf);
1437 err = SQLstatementIntern(c, &buf, "update", true, false, NULL);
1438 GDKfree(buf);
1439 return err; /* usually MAL_SUCCEED */
1440}
1441
1442static str
1443sql_update_apr2019(Client c, mvc *sql, const char *prev_schema)
1444{
1445 size_t bufsize = 3000, pos = 0;
1446 char *buf, *err;
1447 sql_schema *s = mvc_bind_schema(sql, "sys");
1448 sql_table *t;
1449
1450 if ((buf = GDKmalloc(bufsize)) == NULL)
1451 throw(SQL, "sql_update_apr2019", SQLSTATE(HY001) MAL_MALLOC_FAIL);
1452
1453 pos += snprintf(buf + pos, bufsize - pos, "set schema sys;\n");
1454
1455 /* 15_querylog.sql */
1456 pos += snprintf(buf + pos, bufsize - pos,
1457 "drop procedure sys.querylog_enable(smallint);\n"
1458 "create procedure sys.querylog_enable(threshold integer) external name sql.querylog_enable;\n"
1459 "update sys.functions set system = true where name = 'querylog_enable' and schema_id = (select id from sys.schemas where name = 'sys');\n");
1460
1461 /* 17_temporal.sql */
1462 pos += snprintf(buf + pos, bufsize - pos,
1463 "create function sys.date_trunc(txt string, t timestamp)\n"
1464 "returns timestamp\n"
1465 "external name sql.date_trunc;\n"
1466 "grant execute on function sys.date_trunc(string, timestamp) to public;\n"
1467 "update sys.functions set system = true where schema_id = (select id from sys.schemas where name = 'sys') and name = 'date_trunc' and type = %d;\n", (int) F_FUNC);
1468
1469 /* 22_clients.sql */
1470 pos += snprintf(buf + pos, bufsize - pos,
1471 "create procedure sys.setprinttimeout(\"timeout\" integer)\n"
1472 "external name clients.setprinttimeout;\n"
1473 "update sys.functions set system = true where schema_id = (select id from sys.schemas where name = 'sys') and name = 'setprinttimeout' and type = %d;\n", (int) F_PROC);
1474
1475 /* 26_sysmon.sql */
1476 pos += snprintf(buf + pos, bufsize - pos,
1477 "grant execute on function sys.queue to public;\n"
1478 "grant select on sys.queue to public;\n");
1479
1480 /* 51_sys_schema_extensions.sql */
1481 pos += snprintf(buf + pos, bufsize - pos,
1482 "ALTER TABLE sys.keywords SET READ WRITE;\n"
1483 "INSERT INTO sys.keywords VALUES ('WINDOW');\n"
1484 );
1485 t = mvc_bind_table(sql, s, "var_values");
1486 t->system = 0; /* make it non-system else the drop view will fail */
1487 pos += snprintf(buf + pos, bufsize - pos,
1488 "DROP VIEW sys.var_values;\n"
1489 "CREATE VIEW sys.var_values (var_name, value) AS\n"
1490 "SELECT 'cache' AS var_name, convert(cache, varchar(10)) AS value UNION ALL\n"
1491 "SELECT 'current_role', current_role UNION ALL\n"
1492 "SELECT 'current_schema', current_schema UNION ALL\n"
1493 "SELECT 'current_timezone', current_timezone UNION ALL\n"
1494 "SELECT 'current_user', current_user UNION ALL\n"
1495 "SELECT 'debug', debug UNION ALL\n"
1496 "SELECT 'last_id', last_id UNION ALL\n"
1497 "SELECT 'optimizer', optimizer UNION ALL\n"
1498 "SELECT 'pi', pi() UNION ALL\n"
1499 "SELECT 'rowcnt', rowcnt;\n"
1500 "UPDATE sys._tables SET system = true WHERE name = 'var_values' AND schema_id = (SELECT id FROM sys.schemas WHERE name = 'sys');\n"
1501 "GRANT SELECT ON sys.var_values TO PUBLIC;\n");
1502
1503 /* 99_system.sql */
1504 t = mvc_bind_table(sql, s, "systemfunctions");
1505 t->system = 0;
1506 pos += snprintf(buf + pos, bufsize - pos,
1507 "drop table sys.systemfunctions;\n"
1508 "create view sys.systemfunctions as select id as function_id from sys.functions where system;\n"
1509 "grant select on sys.systemfunctions to public;\n"
1510 "update sys._tables set system = true where name = 'systemfunctions' and schema_id = (select id from sys.schemas where name = 'sys');\n");
1511 /* update type of "query" attribute of tables sys._tables and
1512 * tmp_tables from varchar(2048) to varchar(1048576) */
1513 pos += snprintf(buf + pos, bufsize - pos,
1514 "update sys._columns set type_digits = 1048576 where name = 'query' and table_id in (select id from sys._tables t where t.name = '_tables' and t.schema_id in (select id from sys.schemas s where s.name in ('sys', 'tmp')));\n");
1515 pos += snprintf(buf + pos, bufsize - pos,
1516 "update sys._columns set type_digits = 1048576 where name = 'query' and table_id in (select id from sys._tables t where t.name = 'tables' and t.schema_id in (select id from sys.schemas s where s.name = 'sys'));\n");
1517
1518 pos += snprintf(buf + pos, bufsize - pos, "set schema \"%s\";\n", prev_schema);
1519
1520 assert(pos < bufsize);
1521 printf("Running database upgrade commands:\n%s\n", buf);
1522 err = SQLstatementIntern(c, &buf, "update", true, false, NULL);
1523 if (err == MAL_SUCCEED) {
1524 pos = snprintf(buf, bufsize, "set schema \"sys\";\n"
1525 "ALTER TABLE sys.keywords SET READ ONLY;\n");
1526
1527 pos += snprintf(buf + pos, bufsize - pos, "set schema \"%s\";\n", prev_schema);
1528 assert(pos < bufsize);
1529 printf("Running database upgrade commands:\n%s\n", buf);
1530 err = SQLstatementIntern(c, &buf, "update", true, false, NULL);
1531 }
1532
1533 GDKfree(buf);
1534 return err; /* usually MAL_SUCCEED */
1535}
1536
1537static str
1538sql_update_storagemodel(Client c, mvc *sql, const char *prev_schema)
1539{
1540 size_t bufsize = 20000, pos = 0;
1541 char *buf, *err;
1542 sql_schema *s = mvc_bind_schema(sql, "sys");
1543 sql_table *t;
1544
1545 if ((buf = GDKmalloc(bufsize)) == NULL)
1546 throw(SQL, "sql_update_storagemodel", SQLSTATE(HY001) MAL_MALLOC_FAIL);
1547
1548 /* set views and tables internally to non-system to allow drop commands to succeed without error */
1549 if ((t = mvc_bind_table(sql, s, "storage")) != NULL)
1550 t->system = 0;
1551 if ((t = mvc_bind_table(sql, s, "storagemodel")) != NULL)
1552 t->system = 0;
1553 if ((t = mvc_bind_table(sql, s, "storagemodelinput")) != NULL)
1554 t->system = 0;
1555 if ((t = mvc_bind_table(sql, s, "tablestoragemodel")) != NULL)
1556 t->system = 0;
1557
1558 /* new 75_storagemodel.sql */
1559 pos += snprintf(buf + pos, bufsize - pos,
1560 "set schema sys;\n"
1561 /* drop objects in reverse order of original creation of old 75_storagemodel.sql */
1562 "drop view if exists sys.tablestoragemodel;\n"
1563 "drop view if exists sys.storagemodel cascade;\n"
1564 "drop function if exists sys.storagemodel() cascade;\n"
1565 "drop function if exists sys.imprintsize(bigint, clob) cascade;\n"
1566 "drop function if exists sys.hashsize(boolean, bigint) cascade;\n"
1567 "drop function if exists sys.heapsize(clob, bigint, int) cascade;\n"
1568 "drop function if exists sys.columnsize(clob, bigint, bigint) cascade;\n"
1569 "drop procedure if exists sys.storagemodelinit();\n"
1570 "drop table if exists sys.storagemodelinput cascade;\n"
1571 "drop view if exists sys.\"storage\" cascade;\n"
1572 "drop function if exists sys.\"storage\"(clob, clob, clob) cascade;\n"
1573 "drop function if exists sys.\"storage\"(clob, clob) cascade;\n"
1574 "drop function if exists sys.\"storage\"(clob) cascade;\n"
1575 "drop function if exists sys.\"storage\"() cascade;\n"
1576 "create function sys.\"storage\"()\n"
1577 "returns table (\n"
1578 " \"schema\" varchar(1024),\n"
1579 " \"table\" varchar(1024),\n"
1580 " \"column\" varchar(1024),\n"
1581 " \"type\" varchar(1024),\n"
1582 " \"mode\" varchar(15),\n"
1583 " location varchar(1024),\n"
1584 " \"count\" bigint,\n"
1585 " typewidth int,\n"
1586 " columnsize bigint,\n"
1587 " heapsize bigint,\n"
1588 " hashes bigint,\n"
1589 " phash boolean,\n"
1590 " \"imprints\" bigint,\n"
1591 " sorted boolean,\n"
1592 " revsorted boolean,\n"
1593 " \"unique\" boolean,\n"
1594 " orderidx bigint\n"
1595 ")\n"
1596 "external name sql.\"storage\";\n"
1597 "create view sys.\"storage\" as\n"
1598 "select * from sys.\"storage\"()\n"
1599 " where (\"schema\", \"table\") in (\n"
1600 " SELECT sch.\"name\", tbl.\"name\"\n"
1601 " FROM sys.\"tables\" AS tbl JOIN sys.\"schemas\" AS sch ON tbl.schema_id = sch.id\n"
1602 " WHERE tbl.\"system\" = FALSE)\n"
1603 "order by \"schema\", \"table\", \"column\";\n"
1604 "create view sys.\"tablestorage\" as\n"
1605 "select \"schema\", \"table\",\n"
1606 " max(\"count\") as \"rowcount\",\n"
1607 " count(*) as \"storages\",\n"
1608 " sum(columnsize) as columnsize,\n"
1609 " sum(heapsize) as heapsize,\n"
1610 " sum(hashes) as hashsize,\n"
1611 " sum(\"imprints\") as imprintsize,\n"
1612 " sum(orderidx) as orderidxsize\n"
1613 " from sys.\"storage\"\n"
1614 "group by \"schema\", \"table\"\n"
1615 "order by \"schema\", \"table\";\n"
1616 "create view sys.\"schemastorage\" as\n"
1617 "select \"schema\",\n"
1618 " count(*) as \"storages\",\n"
1619 " sum(columnsize) as columnsize,\n"
1620 " sum(heapsize) as heapsize,\n"
1621 " sum(hashes) as hashsize,\n"
1622 " sum(\"imprints\") as imprintsize,\n"
1623 " sum(orderidx) as orderidxsize\n"
1624 " from sys.\"storage\"\n"
1625 "group by \"schema\"\n"
1626 "order by \"schema\";\n"
1627 "create function sys.\"storage\"(sname varchar(1024))\n"
1628 "returns table (\n"
1629 " \"schema\" varchar(1024),\n"
1630 " \"table\" varchar(1024),\n"
1631 " \"column\" varchar(1024),\n"
1632 " \"type\" varchar(1024),\n"
1633 " \"mode\" varchar(15),\n"
1634 " location varchar(1024),\n"
1635 " \"count\" bigint,\n"
1636 " typewidth int,\n"
1637 " columnsize bigint,\n"
1638 " heapsize bigint,\n"
1639 " hashes bigint,\n"
1640 " phash boolean,\n"
1641 " \"imprints\" bigint,\n"
1642 " sorted boolean,\n"
1643 " revsorted boolean,\n"
1644 " \"unique\" boolean,\n"
1645 " orderidx bigint\n"
1646 ")\n"
1647 "external name sql.\"storage\";\n"
1648 "create function sys.\"storage\"(sname varchar(1024), tname varchar(1024))\n"
1649 "returns table (\n"
1650 " \"schema\" varchar(1024),\n"
1651 " \"table\" varchar(1024),\n"
1652 " \"column\" varchar(1024),\n"
1653 " \"type\" varchar(1024),\n"
1654 " \"mode\" varchar(15),\n"
1655 " location varchar(1024),\n"
1656 " \"count\" bigint,\n"
1657 " typewidth int,\n"
1658 " columnsize bigint,\n"
1659 " heapsize bigint,\n"
1660 " hashes bigint,\n"
1661 " phash boolean,\n"
1662 " \"imprints\" bigint,\n"
1663 " sorted boolean,\n"
1664 " revsorted boolean,\n"
1665 " \"unique\" boolean,\n"
1666 " orderidx bigint\n"
1667 ")\n"
1668 "external name sql.\"storage\";\n"
1669 "create function sys.\"storage\"(sname varchar(1024), tname varchar(1024), cname varchar(1024))\n"
1670 "returns table (\n"
1671 " \"schema\" varchar(1024),\n"
1672 " \"table\" varchar(1024),\n"
1673 " \"column\" varchar(1024),\n"
1674 " \"type\" varchar(1024),\n"
1675 " \"mode\" varchar(15),\n"
1676 " location varchar(1024),\n"
1677 " \"count\" bigint,\n"
1678 " typewidth int,\n"
1679 " columnsize bigint,\n"
1680 " heapsize bigint,\n"
1681 " hashes bigint,\n"
1682 " phash boolean,\n"
1683 " \"imprints\" bigint,\n"
1684 " sorted boolean,\n"
1685 " revsorted boolean,\n"
1686 " \"unique\" boolean,\n"
1687 " orderidx bigint\n"
1688 ")\n"
1689 "external name sql.\"storage\";\n"
1690 "create table sys.storagemodelinput(\n"
1691 " \"schema\" varchar(1024) NOT NULL,\n"
1692 " \"table\" varchar(1024) NOT NULL,\n"
1693 " \"column\" varchar(1024) NOT NULL,\n"
1694 " \"type\" varchar(1024) NOT NULL,\n"
1695 " typewidth int NOT NULL,\n"
1696 " \"count\" bigint NOT NULL,\n"
1697 " \"distinct\" bigint NOT NULL,\n"
1698 " atomwidth int NOT NULL,\n"
1699 " reference boolean NOT NULL DEFAULT FALSE,\n"
1700 " sorted boolean,\n"
1701 " \"unique\" boolean,\n"
1702 " isacolumn boolean NOT NULL DEFAULT TRUE\n"
1703 ");\n"
1704 "create procedure sys.storagemodelinit()\n"
1705 "begin\n"
1706 " delete from sys.storagemodelinput;\n"
1707 " insert into sys.storagemodelinput\n"
1708 " select \"schema\", \"table\", \"column\", \"type\", typewidth, \"count\",\n"
1709 " case when (\"unique\" or \"type\" IN ('varchar', 'char', 'clob', 'json', 'url', 'blob', 'geometry', 'geometrya'))\n"
1710 " then \"count\" else 0 end,\n"
1711 " case when \"count\" > 0 and heapsize >= 8192 and \"type\" in ('varchar', 'char', 'clob', 'json', 'url')\n"
1712 " then cast((heapsize - 8192) / \"count\" as bigint)\n"
1713 " when \"count\" > 0 and heapsize >= 32 and \"type\" in ('blob', 'geometry', 'geometrya')\n"
1714 " then cast((heapsize - 32) / \"count\" as bigint)\n"
1715 " else typewidth end,\n"
1716 " FALSE, case sorted when true then true else false end, \"unique\", TRUE\n"
1717 " from sys.\"storage\";\n"
1718 " update sys.storagemodelinput\n"
1719 " set reference = TRUE\n"
1720 " where (\"schema\", \"table\", \"column\") in (\n"
1721 " SELECT fkschema.\"name\", fktable.\"name\", fkkeycol.\"name\"\n"
1722 " FROM sys.\"keys\" AS fkkey,\n"
1723 " sys.\"objects\" AS fkkeycol,\n"
1724 " sys.\"tables\" AS fktable,\n"
1725 " sys.\"schemas\" AS fkschema\n"
1726 " WHERE fktable.\"id\" = fkkey.\"table_id\"\n"
1727 " AND fkkey.\"id\" = fkkeycol.\"id\"\n"
1728 " AND fkschema.\"id\" = fktable.\"schema_id\"\n"
1729 " AND fkkey.\"rkey\" > -1 );\n"
1730 " update sys.storagemodelinput\n"
1731 " set isacolumn = FALSE\n"
1732 " where (\"schema\", \"table\", \"column\") NOT in (\n"
1733 " SELECT sch.\"name\", tbl.\"name\", col.\"name\"\n"
1734 " FROM sys.\"schemas\" AS sch,\n"
1735 " sys.\"tables\" AS tbl,\n"
1736 " sys.\"columns\" AS col\n"
1737 " WHERE sch.\"id\" = tbl.\"schema_id\"\n"
1738 " AND tbl.\"id\" = col.\"table_id\");\n"
1739 "end;\n"
1740 "create function sys.columnsize(tpe varchar(1024), count bigint)\n"
1741 "returns bigint\n"
1742 "begin\n"
1743 " if tpe in ('tinyint', 'boolean')\n"
1744 " then return count;\n"
1745 " end if;\n"
1746 " if tpe = 'smallint'\n"
1747 " then return 2 * count;\n"
1748 " end if;\n"
1749 " if tpe in ('int', 'real', 'date', 'time', 'timetz', 'sec_interval', 'month_interval')\n"
1750 " then return 4 * count;\n"
1751 " end if;\n"
1752 " if tpe in ('bigint', 'double', 'timestamp', 'timestamptz', 'inet', 'oid')\n"
1753 " then return 8 * count;\n"
1754 " end if;\n"
1755 " if tpe in ('hugeint', 'decimal', 'uuid', 'mbr')\n"
1756 " then return 16 * count;\n"
1757 " end if;\n"
1758 " if tpe in ('varchar', 'char', 'clob', 'json', 'url')\n"
1759 " then return 4 * count;\n"
1760 " end if;\n"
1761 " if tpe in ('blob', 'geometry', 'geometrya')\n"
1762 " then return 8 * count;\n"
1763 " end if;\n"
1764 " return 8 * count;\n"
1765 "end;\n"
1766 "create function sys.heapsize(tpe varchar(1024), count bigint, distincts bigint, avgwidth int)\n"
1767 "returns bigint\n"
1768 "begin\n"
1769 " if tpe in ('varchar', 'char', 'clob', 'json', 'url')\n"
1770 " then return 8192 + ((avgwidth + 8) * distincts);\n"
1771 " end if;\n"
1772 " if tpe in ('blob', 'geometry', 'geometrya')\n"
1773 " then return 32 + (avgwidth * count);\n"
1774 " end if;\n"
1775 " return 0;\n"
1776 "end;\n"
1777 "create function sys.hashsize(b boolean, count bigint)\n"
1778 "returns bigint\n"
1779 "begin\n"
1780 " if b = true\n"
1781 " then return 8 * count;\n"
1782 " end if;\n"
1783 " return 0;\n"
1784 "end;\n"
1785 "create function sys.imprintsize(tpe varchar(1024), count bigint)\n"
1786 "returns bigint\n"
1787 "begin\n"
1788 " if tpe in ('tinyint', 'boolean')\n"
1789 " then return cast(0.2 * count as bigint);\n"
1790 " end if;\n"
1791 " if tpe = 'smallint'\n"
1792 " then return cast(0.4 * count as bigint);\n"
1793 " end if;\n"
1794 " if tpe in ('int', 'real', 'date', 'time', 'timetz', 'sec_interval', 'month_interval')\n"
1795 " then return cast(0.8 * count as bigint);\n"
1796 " end if;\n"
1797 " if tpe in ('bigint', 'double', 'timestamp', 'timestamptz', 'inet', 'oid')\n"
1798 " then return cast(1.6 * count as bigint);\n"
1799 " end if;\n"
1800 " if tpe in ('hugeint', 'decimal', 'uuid', 'mbr')\n"
1801 " then return cast(3.2 * count as bigint);\n"
1802 " end if;\n"
1803 " return 0;\n"
1804 "end;\n"
1805 "create view sys.storagemodel as\n"
1806 "select \"schema\", \"table\", \"column\", \"type\", \"count\",\n"
1807 " sys.columnsize(\"type\", \"count\") as columnsize,\n"
1808 " sys.heapsize(\"type\", \"count\", \"distinct\", \"atomwidth\") as heapsize,\n"
1809 " sys.hashsize(\"reference\", \"count\") as hashsize,\n"
1810 " case when isacolumn then sys.imprintsize(\"type\", \"count\") else 0 end as imprintsize,\n"
1811 " case when (isacolumn and not sorted) then cast(8 * \"count\" as bigint) else 0 end as orderidxsize,\n"
1812 " sorted, \"unique\", isacolumn\n"
1813 " from sys.storagemodelinput\n"
1814 "order by \"schema\", \"table\", \"column\";\n"
1815 "create view sys.tablestoragemodel as\n"
1816 "select \"schema\", \"table\",\n"
1817 " max(\"count\") as \"rowcount\",\n"
1818 " count(*) as \"storages\",\n"
1819 " sum(sys.columnsize(\"type\", \"count\")) as columnsize,\n"
1820 " sum(sys.heapsize(\"type\", \"count\", \"distinct\", \"atomwidth\")) as heapsize,\n"
1821 " sum(sys.hashsize(\"reference\", \"count\")) as hashsize,\n"
1822 " sum(case when isacolumn then sys.imprintsize(\"type\", \"count\") else 0 end) as imprintsize,\n"
1823 " sum(case when (isacolumn and not sorted) then cast(8 * \"count\" as bigint) else 0 end) as orderidxsize\n"
1824 " from sys.storagemodelinput\n"
1825 "group by \"schema\", \"table\"\n"
1826 "order by \"schema\", \"table\";\n"
1827 );
1828 assert(pos < bufsize);
1829
1830 pos += snprintf(buf + pos, bufsize - pos,
1831 "update sys._tables set system = true where schema_id = (select id from sys.schemas where name = 'sys')"
1832 " and name in ('storage', 'tablestorage', 'schemastorage', 'storagemodelinput', 'storagemodel', 'tablestoragemodel');\n");
1833 pos += snprintf(buf + pos, bufsize - pos,
1834 "update sys.functions set system = true where schema_id = (select id from sys.schemas where name = 'sys')"
1835 " and name in ('storage') and type = %d;\n", (int) F_UNION);
1836 pos += snprintf(buf + pos, bufsize - pos,
1837 "update sys.functions set system = true where schema_id = (select id from sys.schemas where name = 'sys')"
1838 " and name in ('storagemodelinit') and type = %d;\n", (int) F_PROC);
1839 pos += snprintf(buf + pos, bufsize - pos,
1840 "update sys.functions set system = true where schema_id = (select id from sys.schemas where name = 'sys')"
1841 " and name in ('columnsize', 'heapsize', 'hashsize', 'imprintsize') and type = %d;\n", (int) F_FUNC);
1842
1843 pos += snprintf(buf + pos, bufsize - pos, "set schema \"%s\";\n", prev_schema);
1844 assert(pos < bufsize);
1845
1846 printf("Running database upgrade commands:\n%s\n", buf);
1847 err = SQLstatementIntern(c, &buf, "update", true, false, NULL);
1848 GDKfree(buf);
1849 return err; /* usually MAL_SUCCEED */
1850}
1851
1852static str
1853sql_update_apr2019_sp1(Client c)
1854{
1855 char *err, *qry = "select c.id from sys.dependency_types dt, sys._columns c, sys.keys k, sys.objects o "
1856 "where k.id = o.id and o.name = c.name and c.table_id = k.table_id and dt.dependency_type_name = 'KEY' and k.type = 1 "
1857 "and not exists (select d.id from sys.dependencies d where d.id = c.id and d.depend_id = k.id and d.depend_type = dt.dependency_type_id);";
1858 res_table *output = NULL;
1859
1860 /* Determine if missing dependency table entry for unique keys
1861 * is required */
1862 err = SQLstatementIntern(c, &qry, "update", true, false, &output);
1863 if (err == NULL) {
1864 BAT *b = BATdescriptor(output->cols[0].b);
1865 if (b) {
1866 if (BATcount(b) > 0) {
1867 /* required update for changeset 23e1231ada99 */
1868 qry = "insert into sys.dependencies (select c.id as id, k.id as depend_id, dt.dependency_type_id as depend_type from sys.dependency_types dt, sys._columns c, sys.keys k, sys.objects o where k.id = o.id and o.name = c.name and c.table_id = k.table_id and dt.dependency_type_name = 'KEY' and k.type = 1 and not exists (select d.id from sys.dependencies d where d.id = c.id and d.depend_id = k.id and d.depend_type = dt.dependency_type_id));\n";
1869 printf("Running database upgrade commands:\n%s\n", qry);
1870 err = SQLstatementIntern(c, &qry, "update", true, false, NULL);
1871 }
1872 BBPunfix(b->batCacheid);
1873 }
1874 }
1875 if (output != NULL)
1876 res_tables_destroy(output);
1877
1878 return err; /* usually MAL_SUCCEED */
1879}
1880
1881static str
1882sql_update_apr2019_sp2(Client c, mvc *sql, const char *prev_schema, bool *systabfixed)
1883{
1884 size_t bufsize = 1000, pos = 0;
1885 char *buf = GDKmalloc(bufsize), *err;
1886
1887 if (buf == NULL)
1888 throw(SQL, "sql_update_apr2019_sp2", SQLSTATE(HY001) MAL_MALLOC_FAIL);
1889
1890 if (!*systabfixed) {
1891 sql_fix_system_tables(c, sql, prev_schema);
1892 *systabfixed = true;
1893 }
1894
1895 pos += snprintf(buf + pos, bufsize - pos, "set schema sys;\n");
1896
1897 /* 11_times.sql */
1898 pos += snprintf(buf + pos, bufsize - pos,
1899 "drop procedure sys.times();\n");
1900
1901 pos += snprintf(buf + pos, bufsize - pos, "set schema \"%s\";\n", prev_schema);
1902
1903 assert(pos < bufsize);
1904 printf("Running database upgrade commands:\n%s\n", buf);
1905 err = SQLstatementIntern(c, &buf, "update", true, false, NULL);
1906 GDKfree(buf);
1907 return err; /* usually MAL_SUCCEED */
1908}
1909
1910#define FLUSH_INSERTS_IF_BUFFERFILLED /* Each new value should add about 20 bytes to the buffer, "flush" when is 200 bytes from being full */ \
1911 if (pos > 7900) { \
1912 pos += snprintf(buf + pos, bufsize - pos, \
1913 ") as t1(c1,c2,c3) where t1.c1 not in (select \"id\" from sys.dependencies where depend_id = t1.c2);\n"); \
1914 assert(pos < bufsize); \
1915 printf("Running database upgrade commands:\n%s\n", buf); \
1916 err = SQLstatementIntern(c, &buf, "update", true, false, NULL); \
1917 if (err) \
1918 goto bailout; \
1919 pos = 0; \
1920 pos += snprintf(buf + pos, bufsize - pos, "insert into sys.dependencies select c1, c2, c3 from (values"); \
1921 ppos = pos; \
1922 first = true; \
1923 }
1924
1925static str
1926sql_update_nov2019_missing_dependencies(Client c, mvc *sql)
1927{
1928 size_t bufsize = 8192, pos = 0, ppos;
1929 char *err = NULL, *buf = GDKmalloc(bufsize);
1930 sql_allocator *old_sa = sql->sa;
1931 bool first = true;
1932
1933 if (buf == NULL)
1934 throw(SQL, "sql_update_nov2019_missing_dependencies", SQLSTATE(HY001) MAL_MALLOC_FAIL);
1935
1936 if (!(sql->sa = sa_create())) {
1937 err = createException(SQL, "sql.catalog", SQLSTATE(HY001) MAL_MALLOC_FAIL);
1938 goto bailout;
1939 }
1940
1941 pos += snprintf(buf + pos, bufsize - pos, "insert into sys.dependencies select c1, c2, c3 from (values");
1942 ppos = pos; /* later check if found updatable database objects */
1943
1944 for (node *n = sql->session->tr->schemas.set->h; n; n = n->next) {
1945 sql_schema *s = (sql_schema*) n->data;
1946
1947 if (s->funcs.set)
1948 for (node *m = s->funcs.set->h; m; m = m->next) {
1949 sql_func *f = (sql_func*) m->data;
1950
1951 if (f->query && f->lang == FUNC_LANG_SQL) {
1952 char *relt;
1953 sql_rel *r = NULL;
1954
1955 if (!(relt = sa_strdup(sql->sa, f->query))) {
1956 err = createException(SQL, "sql.catalog", SQLSTATE(HY001) MAL_MALLOC_FAIL);
1957 goto bailout;
1958 }
1959
1960 r = rel_parse(sql, s, relt, m_deps);
1961 if (r)
1962 r = rel_unnest(sql, r);
1963 if (r)
1964 r = rel_optimizer(sql, r, 0);
1965 if (r) {
1966 list *id_l = rel_dependencies(sql, r);
1967
1968 for (node *o = id_l->h ; o ; o = o->next) {
1969 sqlid next = *(sqlid*) o->data;
1970 if (next != f->base.id) {
1971 pos += snprintf(buf + pos, bufsize - pos, "%s(%d,%d,%d)", first ? "" : ",", next,
1972 f->base.id, (int)(!IS_PROC(f) ? FUNC_DEPENDENCY : PROC_DEPENDENCY));
1973 first = false;
1974 FLUSH_INSERTS_IF_BUFFERFILLED
1975 }
1976 }
1977 } else if (sql->session->status == -1) {
1978 sql->session->status = 0;
1979 sql->errstr[0] = 0;
1980 }
1981 }
1982 }
1983 if (s->tables.set)
1984 for (node *m = s->tables.set->h; m; m = m->next) {
1985 sql_table *t = (sql_table*) m->data;
1986
1987 if (t->query && isView(t)) {
1988 char *relt;
1989 sql_rel *r = NULL;
1990
1991 if (!(relt = sa_strdup(sql->sa, t->query))) {
1992 err = createException(SQL, "sql.catalog", SQLSTATE(HY001) MAL_MALLOC_FAIL);
1993 goto bailout;
1994 }
1995
1996 r = rel_parse(sql, s, relt, m_deps);
1997 if (r)
1998 r = rel_unnest(sql, r);
1999 if (r)
2000 r = rel_optimizer(sql, r, 0);
2001 if (r) {
2002 list *id_l = rel_dependencies(sql, r);
2003
2004 for (node *o = id_l->h ; o ; o = o->next) {
2005 sqlid next = *(sqlid*) o->data;
2006 if (next != t->base.id) {
2007 pos += snprintf(buf + pos, bufsize - pos, "%s(%d,%d,%d)", first ? "" : ",",
2008 next, t->base.id, (int) VIEW_DEPENDENCY);
2009 first = false;
2010 FLUSH_INSERTS_IF_BUFFERFILLED
2011 }
2012 }
2013 }
2014 }
2015 if (t->triggers.set)
2016 for (node *mm = t->triggers.set->h; mm; mm = mm->next) {
2017 sql_trigger *tr = (sql_trigger*) mm->data;
2018 char *relt;
2019 sql_rel *r = NULL;
2020
2021 if (!(relt = sa_strdup(sql->sa, tr->statement))) {
2022 err = createException(SQL, "sql.catalog", SQLSTATE(HY001) MAL_MALLOC_FAIL);
2023 goto bailout;
2024 }
2025
2026 r = rel_parse(sql, s, relt, m_deps);
2027 if (r)
2028 r = rel_unnest(sql, r);
2029 if (r)
2030 r = rel_optimizer(sql, r, 0);
2031 if (r) {
2032 list *id_l = rel_dependencies(sql, r);
2033
2034 for (node *o = id_l->h ; o ; o = o->next) {
2035 sqlid next = *(sqlid*) o->data;
2036 if (next != tr->base.id) {
2037 pos += snprintf(buf + pos, bufsize - pos, "%s(%d,%d,%d)", first ? "" : ",",
2038 next, tr->base.id, (int) TRIGGER_DEPENDENCY);
2039 first = false;
2040 FLUSH_INSERTS_IF_BUFFERFILLED
2041 }
2042 }
2043 }
2044 }
2045 }
2046 }
2047
2048 if (ppos != pos) { /* found updatable functions */
2049 pos += snprintf(buf + pos, bufsize - pos,
2050 ") as t1(c1,c2,c3) where t1.c1 not in (select \"id\" from sys.dependencies where depend_id = t1.c2);\n");
2051
2052 assert(pos < bufsize);
2053 printf("Running database upgrade commands:\n%s\n", buf);
2054 err = SQLstatementIntern(c, &buf, "update", true, false, NULL);
2055 }
2056
2057bailout:
2058 if (sql->sa)
2059 sa_destroy(sql->sa);
2060 sql->sa = old_sa;
2061 GDKfree(buf);
2062 return err;
2063}
2064
2065static str
2066sql_update_nov2019(Client c, mvc *sql, const char *prev_schema, bool *systabfixed)
2067{
2068 size_t bufsize = 16384, pos = 0;
2069 char *err = NULL, *buf = GDKmalloc(bufsize);
2070 res_table *output;
2071 BAT *b;
2072
2073 if (buf == NULL)
2074 throw(SQL, "sql_update_nov2019", SQLSTATE(HY001) MAL_MALLOC_FAIL);
2075
2076 pos += snprintf(buf + pos, bufsize - pos,
2077 "select id from sys.args where func_id in (select id from sys.functions where schema_id = (select id from sys.schemas where name = 'sys') and name = 'second' and func = 'sql_seconds') and number = 0 and type_scale = 3;\n");
2078 err = SQLstatementIntern(c, &buf, "update", 1, 0, &output);
2079 if (err) {
2080 GDKfree(buf);
2081 return err;
2082 }
2083 b = BATdescriptor(output->cols[0].b);
2084 if (b) {
2085 if (BATcount(b) > 0 && !*systabfixed) {
2086 err = sql_fix_system_tables(c, sql, prev_schema);
2087 *systabfixed = true;
2088 }
2089 BBPunfix(b->batCacheid);
2090 }
2091 res_table_destroy(output);
2092 if (err) {
2093 GDKfree(buf);
2094 return err;
2095 }
2096
2097 pos = 0;
2098 pos += snprintf(buf + pos, bufsize - pos,
2099 "set schema \"sys\";\n"
2100 "create function sys.deltas (\"schema\" string)"
2101 " returns table (\"id\" int, \"cleared\" boolean, \"immutable\" bigint, \"inserted\" bigint, \"updates\" bigint, \"deletes\" bigint, \"level\" int)"
2102 " external name \"sql\".\"deltas\";\n"
2103 "create function sys.deltas (\"schema\" string, \"table\" string)"
2104 " returns table (\"id\" int, \"cleared\" boolean, \"immutable\" bigint, \"inserted\" bigint, \"updates\" bigint, \"deletes\" bigint, \"level\" int)"
2105 " external name \"sql\".\"deltas\";\n"
2106 "create function sys.deltas (\"schema\" string, \"table\" string, \"column\" string)"
2107 " returns table (\"id\" int, \"cleared\" boolean, \"immutable\" bigint, \"inserted\" bigint, \"updates\" bigint, \"deletes\" bigint, \"level\" int)"
2108 " external name \"sql\".\"deltas\";\n"
2109 "create aggregate median_avg(val TINYINT) returns DOUBLE\n"
2110 " external name \"aggr\".\"median_avg\";\n"
2111 "GRANT EXECUTE ON AGGREGATE median_avg(TINYINT) TO PUBLIC;\n"
2112 "create aggregate median_avg(val SMALLINT) returns DOUBLE\n"
2113 " external name \"aggr\".\"median_avg\";\n"
2114 "GRANT EXECUTE ON AGGREGATE median_avg(SMALLINT) TO PUBLIC;\n"
2115 "create aggregate median_avg(val INTEGER) returns DOUBLE\n"
2116 " external name \"aggr\".\"median_avg\";\n"
2117 "GRANT EXECUTE ON AGGREGATE median_avg(INTEGER) TO PUBLIC;\n"
2118 "create aggregate median_avg(val BIGINT) returns DOUBLE\n"
2119 " external name \"aggr\".\"median_avg\";\n"
2120 "GRANT EXECUTE ON AGGREGATE median_avg(BIGINT) TO PUBLIC;\n"
2121 "create aggregate median_avg(val DECIMAL) returns DOUBLE\n"
2122 " external name \"aggr\".\"median_avg\";\n"
2123 "GRANT EXECUTE ON AGGREGATE median_avg(DECIMAL) TO PUBLIC;\n"
2124 "create aggregate median_avg(val REAL) returns DOUBLE\n"
2125 " external name \"aggr\".\"median_avg\";\n"
2126 "GRANT EXECUTE ON AGGREGATE median_avg(REAL) TO PUBLIC;\n"
2127 "create aggregate median_avg(val DOUBLE) returns DOUBLE\n"
2128 " external name \"aggr\".\"median_avg\";\n"
2129 "GRANT EXECUTE ON AGGREGATE median_avg(DOUBLE) TO PUBLIC;\n"
2130 "\n"
2131 "create aggregate quantile_avg(val TINYINT, q DOUBLE) returns DOUBLE\n"
2132 " external name \"aggr\".\"quantile_avg\";\n"
2133 "GRANT EXECUTE ON AGGREGATE quantile_avg(TINYINT, DOUBLE) TO PUBLIC;\n"
2134 "create aggregate quantile_avg(val SMALLINT, q DOUBLE) returns DOUBLE\n"
2135 " external name \"aggr\".\"quantile_avg\";\n"
2136 "GRANT EXECUTE ON AGGREGATE quantile_avg(SMALLINT, DOUBLE) TO PUBLIC;\n"
2137 "create aggregate quantile_avg(val INTEGER, q DOUBLE) returns DOUBLE\n"
2138 " external name \"aggr\".\"quantile_avg\";\n"
2139 "GRANT EXECUTE ON AGGREGATE quantile_avg(INTEGER, DOUBLE) TO PUBLIC;\n"
2140 "create aggregate quantile_avg(val BIGINT, q DOUBLE) returns DOUBLE\n"
2141 " external name \"aggr\".\"quantile_avg\";\n"
2142 "GRANT EXECUTE ON AGGREGATE quantile_avg(BIGINT, DOUBLE) TO PUBLIC;\n"
2143 "create aggregate quantile_avg(val DECIMAL, q DOUBLE) returns DOUBLE\n"
2144 " external name \"aggr\".\"quantile_avg\";\n"
2145 "GRANT EXECUTE ON AGGREGATE quantile_avg(DECIMAL, DOUBLE) TO PUBLIC;\n"
2146 "create aggregate quantile_avg(val REAL, q DOUBLE) returns DOUBLE\n"
2147 " external name \"aggr\".\"quantile_avg\";\n"
2148 "GRANT EXECUTE ON AGGREGATE quantile_avg(REAL, DOUBLE) TO PUBLIC;\n"
2149 "create aggregate quantile_avg(val DOUBLE, q DOUBLE) returns DOUBLE\n"
2150 " external name \"aggr\".\"quantile_avg\";\n"
2151 "GRANT EXECUTE ON AGGREGATE quantile_avg(DOUBLE, DOUBLE) TO PUBLIC;\n");
2152#ifdef HAVE_HGE
2153 if (have_hge) {
2154 pos += snprintf(buf + pos, bufsize - pos,
2155 "create aggregate median_avg(val HUGEINT) returns DOUBLE\n"
2156 " external name \"aggr\".\"median_avg\";\n"
2157 "GRANT EXECUTE ON AGGREGATE median_avg(HUGEINT) TO PUBLIC;\n"
2158 "create aggregate quantile_avg(val HUGEINT, q DOUBLE) returns DOUBLE\n"
2159 " external name \"aggr\".\"quantile_avg\";\n"
2160 "GRANT EXECUTE ON AGGREGATE quantile_avg(HUGEINT, DOUBLE) TO PUBLIC;\n");
2161 }
2162#endif
2163 /* 60/61_wlcr signatures migrations */
2164 pos += snprintf(buf + pos, bufsize - pos,
2165 "drop procedure master();\n"
2166 "drop procedure master(string);\n"
2167 "drop procedure stopmaster();\n"
2168 "drop procedure masterbeat(int);\n"
2169 "drop function masterClock();\n"
2170 "drop function masterTick();\n"
2171 "drop procedure replicate();\n"
2172 "drop procedure replicate(timestamp);\n"
2173 "drop procedure replicate(string);\n"
2174 "drop procedure replicate(string, timestamp);\n"
2175 "drop procedure replicate(string, tinyint);\n"
2176 "drop procedure replicate(string, smallint);\n"
2177 "drop procedure replicate(string, integer);\n"
2178 "drop procedure replicate(string, bigint);\n"
2179 "drop procedure replicabeat(integer);\n"
2180 "drop function replicaClock();\n"
2181 "drop function replicaTick();\n"
2182
2183 "create schema wlc;\n"
2184 "create procedure wlc.master()\n"
2185 "external name wlc.master;\n"
2186 "create procedure wlc.master(path string)\n"
2187 "external name wlc.master;\n"
2188 "create procedure wlc.stop()\n"
2189 "external name wlc.stop;\n"
2190 "create procedure wlc.flush()\n"
2191 "external name wlc.flush;\n"
2192 "create procedure wlc.beat( duration int)\n"
2193 "external name wlc.\"setbeat\";\n"
2194 "create function wlc.clock() returns string\n"
2195 "external name wlc.\"getclock\";\n"
2196 "create function wlc.tick() returns bigint\n"
2197 "external name wlc.\"gettick\";\n"
2198
2199 "create schema wlr;\n"
2200 "create procedure wlr.master(dbname string)\n"
2201 "external name wlr.master;\n"
2202 "create procedure wlr.stop()\n"
2203 "external name wlr.stop;\n"
2204 "create procedure wlr.accept()\n"
2205 "external name wlr.accept;\n"
2206 "create procedure wlr.replicate()\n"
2207 "external name wlr.replicate;\n"
2208 "create procedure wlr.replicate(pointintime timestamp)\n"
2209 "external name wlr.replicate;\n"
2210 "create procedure wlr.replicate(id tinyint)\n"
2211 "external name wlr.replicate;\n"
2212 "create procedure wlr.replicate(id smallint)\n"
2213 "external name wlr.replicate;\n"
2214 "create procedure wlr.replicate(id integer)\n"
2215 "external name wlr.replicate;\n"
2216 "create procedure wlr.replicate(id bigint)\n"
2217 "external name wlr.replicate;\n"
2218 "create procedure wlr.beat(duration integer)\n"
2219 "external name wlr.\"setbeat\";\n"
2220 "create function wlr.clock() returns string\n"
2221 "external name wlr.\"getclock\";\n"
2222 "create function wlr.tick() returns bigint\n"
2223 "external name wlr.\"gettick\";\n"
2224 );
2225
2226 pos += snprintf(buf + pos, bufsize - pos,
2227 "update sys.functions set system = true where schema_id = (select id from sys.schemas where name = 'sys')"
2228 " and name in ('deltas') and type = %d;\n", (int) F_UNION);
2229 pos += snprintf(buf + pos, bufsize - pos,
2230 "update sys.functions set system = true where schema_id = (select id from sys.schemas where name = 'sys')"
2231 " and name in ('median_avg', 'quantile_avg') and type = %d;\n", (int) F_AGGR);
2232 pos += snprintf(buf + pos, bufsize - pos,
2233 "update sys.schemas set system = true where name in ('wlc', 'wlr');\n");
2234 pos += snprintf(buf + pos, bufsize - pos,
2235 "update sys.functions set system = true where schema_id = (select id from sys.schemas where name = 'wlc')"
2236 " and name in ('clock', 'tick') and type = %d;\n", (int) F_FUNC);
2237 pos += snprintf(buf + pos, bufsize - pos,
2238 "update sys.functions set system = true where schema_id = (select id from sys.schemas where name = 'wlc')"
2239 " and name in ('master', 'stop', 'flush', 'beat') and type = %d;\n", (int) F_PROC);
2240 pos += snprintf(buf + pos, bufsize - pos,
2241 "update sys.functions set system = true where schema_id = (select id from sys.schemas where name = 'wlr')"
2242 " and name in ('clock', 'tick') and type = %d;\n", (int) F_FUNC);
2243 pos += snprintf(buf + pos, bufsize - pos,
2244 "update sys.functions set system = true where schema_id = (select id from sys.schemas where name = 'wlr')"
2245 " and name in ('master', 'stop', 'accept', 'replicate', 'beat') and type = %d;\n", (int) F_PROC);
2246
2247 /* 39_analytics.sql */
2248 pos += snprintf(buf + pos, bufsize - pos,
2249 "create aggregate stddev_samp(val INTERVAL SECOND) returns DOUBLE\n"
2250 "external name \"aggr\".\"stdev\";\n"
2251 "GRANT EXECUTE ON AGGREGATE stddev_samp(INTERVAL SECOND) TO PUBLIC;\n"
2252 "create aggregate stddev_samp(val INTERVAL MONTH) returns DOUBLE\n"
2253 "external name \"aggr\".\"stdev\";\n"
2254 "GRANT EXECUTE ON AGGREGATE stddev_samp(INTERVAL MONTH) TO PUBLIC;\n"
2255
2256 "create aggregate stddev_pop(val INTERVAL SECOND) returns DOUBLE\n"
2257 "external name \"aggr\".\"stdevp\";\n"
2258 "GRANT EXECUTE ON AGGREGATE stddev_pop(INTERVAL SECOND) TO PUBLIC;\n"
2259 "create aggregate stddev_pop(val INTERVAL MONTH) returns DOUBLE\n"
2260 "external name \"aggr\".\"stdevp\";\n"
2261 "GRANT EXECUTE ON AGGREGATE stddev_pop(INTERVAL MONTH) TO PUBLIC;\n"
2262
2263 "create aggregate var_samp(val INTERVAL SECOND) returns DOUBLE\n"
2264 "external name \"aggr\".\"variance\";\n"
2265 "GRANT EXECUTE ON AGGREGATE var_samp(INTERVAL SECOND) TO PUBLIC;\n"
2266 "create aggregate var_samp(val INTERVAL MONTH) returns DOUBLE\n"
2267 "external name \"aggr\".\"variance\";\n"
2268 "GRANT EXECUTE ON AGGREGATE var_samp(INTERVAL MONTH) TO PUBLIC;\n"
2269
2270 "create aggregate var_pop(val INTERVAL SECOND) returns DOUBLE\n"
2271 "external name \"aggr\".\"variancep\";\n"
2272 "GRANT EXECUTE ON AGGREGATE var_pop(INTERVAL SECOND) TO PUBLIC;\n"
2273 "create aggregate var_pop(val INTERVAL MONTH) returns DOUBLE\n"
2274 "external name \"aggr\".\"variancep\";\n"
2275 "GRANT EXECUTE ON AGGREGATE var_pop(INTERVAL MONTH) TO PUBLIC;\n"
2276
2277 "create aggregate median(val INTERVAL SECOND) returns INTERVAL SECOND\n"
2278 "external name \"aggr\".\"median\";\n"
2279 "GRANT EXECUTE ON AGGREGATE median(INTERVAL SECOND) TO PUBLIC;\n"
2280 "create aggregate median(val INTERVAL MONTH) returns INTERVAL MONTH\n"
2281 "external name \"aggr\".\"median\";\n"
2282 "GRANT EXECUTE ON AGGREGATE median(INTERVAL MONTH) TO PUBLIC;\n"
2283
2284 "create aggregate quantile(val INTERVAL SECOND, q DOUBLE) returns INTERVAL SECOND\n"
2285 "external name \"aggr\".\"quantile\";\n"
2286 "GRANT EXECUTE ON AGGREGATE quantile(INTERVAL SECOND, DOUBLE) TO PUBLIC;\n"
2287 "create aggregate quantile(val INTERVAL MONTH, q DOUBLE) returns INTERVAL MONTH\n"
2288 "external name \"aggr\".\"quantile\";\n"
2289 "GRANT EXECUTE ON AGGREGATE quantile(INTERVAL MONTH, DOUBLE) TO PUBLIC;\n"
2290 );
2291
2292 pos += snprintf(buf + pos, bufsize - pos,
2293 "update sys.functions set system = true where schema_id = (select id from sys.schemas where name = 'sys')"
2294 " and name in ('stddev_samp', 'stddev_pop', 'var_samp', 'var_pop', 'median', 'quantile') and type = %d;\n", (int) F_AGGR);
2295
2296 /* The MAL implementation of functions json.text(string) and json.text(int) do not exist */
2297 pos += snprintf(buf + pos, bufsize - pos,
2298 "drop function json.text(string);\n"
2299 "drop function json.text(int);\n");
2300
2301 /* The first argument to copyfrom is a PTR type */
2302 pos += snprintf(buf + pos, bufsize - pos,
2303 "update \"sys\".\"args\" set \"type\" = 'ptr' where"
2304 " \"func_id\" = (select \"id\" from \"sys\".\"functions\" where \"name\" = 'copyfrom' and \"func\" = 'copy_from' and \"mod\" = 'sql') and \"name\" = 'arg_1';\n");
2305
2306 pos += snprintf(buf + pos, bufsize - pos, "set schema \"%s\";\n", prev_schema);
2307 assert(pos < bufsize);
2308
2309 printf("Running database upgrade commands:\n%s\n", buf);
2310 err = SQLstatementIntern(c, &buf, "update", 1, 0, NULL);
2311 GDKfree(buf);
2312 return err; /* usually MAL_SUCCEED */
2313}
2314
2315static str
2316sql_update_default(Client c, mvc *sql, const char *prev_schema)
2317{
2318 sql_table *t;
2319 size_t bufsize = 8192, pos = 0;
2320 char *err = NULL, *buf = GDKmalloc(bufsize);
2321 sql_schema *sys = mvc_bind_schema(sql, "sys");
2322
2323 if (buf == NULL)
2324 throw(SQL, "sql_update_default", SQLSTATE(HY001) MAL_MALLOC_FAIL);
2325
2326 pos += snprintf(buf + pos, bufsize - pos,
2327 "set schema \"sys\";\n"
2328 "create procedure suspend_log_flushing()\n"
2329 " external name sql.suspend_log_flushing;\n"
2330 "create procedure resume_log_flushing()\n"
2331 " external name sql.resume_log_flushing;\n"
2332 "create procedure hot_snapshot(tarfile string)\n"
2333 " external name sql.hot_snapshot;\n"
2334 "update sys.functions set system = true where schema_id = (select id from sys.schemas where name = 'sys')"
2335 " and name in ('suspend_log_flushing', 'resume_log_flushing', 'hot_snapshot') and type = %d;\n", (int) F_PROC);
2336
2337 /* 16_tracelog */
2338 t = mvc_bind_table(sql, sys, "tracelog");
2339 t->system = 0; /* make it non-system else the drop view will fail */
2340 pos += snprintf(buf + pos, bufsize - pos,
2341 "drop view sys.tracelog;\n"
2342 "drop function sys.tracelog();\n"
2343 "create function sys.tracelog()\n"
2344 " returns table (\n"
2345 " ticks bigint, -- time in microseconds\n"
2346 " stmt string -- actual statement executed\n"
2347 " )\n"
2348 " external name sql.dump_trace;\n"
2349 "create view sys.tracelog as select * from sys.tracelog();\n");
2350
2351 pos += snprintf(buf + pos, bufsize - pos,
2352 "update sys.functions set system = true where schema_id = (select id from sys.schemas where name = 'sys')"
2353 " and name = 'tracelog' and type = %d;\n", (int) F_UNION);
2354 pos += snprintf(buf + pos, bufsize - pos,
2355 "update sys._tables set system = true where schema_id = (select id from sys.schemas where name = 'sys')"
2356 " and name = 'tracelog';\n");
2357
2358 /* 22_clients */
2359 t = mvc_bind_table(sql, sys, "sessions");
2360 t->system = 0; /* make it non-system else the drop view will fail */
2361
2362 pos += snprintf(buf + pos, bufsize - pos,
2363 "drop view sys.sessions;\n"
2364 "drop function sys.sessions;\n"
2365 "create function sys.sessions()\n"
2366 "returns table(\n"
2367 "\"sessionid\" int,\n"
2368 "\"user\" string,\n"
2369 "\"login\" timestamp,\n"
2370 "\"idle\" timestamp,\n"
2371 "\"optimizer\" string,\n"
2372 "\"sessiontimeout\" int,\n"
2373 "\"querytimeout\" int,\n"
2374 "\"workerlimit\" int,\n"
2375 "\"memorylimit\" int)\n"
2376 " external name sql.sessions;\n"
2377 "create view sys.sessions as select * from sys.sessions();\n");
2378
2379 pos += snprintf(buf + pos, bufsize - pos,
2380 "create procedure sys.setoptimizer(\"optimizer\" string)\n"
2381 " external name clients.setoptimizer;\n"
2382 "create procedure sys.setquerytimeout(\"query\" int)\n"
2383 " external name clients.setquerytimeout;\n"
2384 "create procedure sys.setsessiontimeout(\"timeout\" int)\n"
2385 " external name clients.setsessiontimeout;\n"
2386 "create procedure sys.setworkerlimit(\"limit\" int)\n"
2387 " external name clients.setworkerlimit;\n"
2388 "create procedure sys.setmemorylimit(\"limit\" int)\n"
2389 " external name clients.setmemorylimit;\n"
2390 "create procedure sys.setoptimizer(\"sessionid\" int, \"optimizer\" string)\n"
2391 " external name clients.setoptimizer;\n"
2392 "create procedure sys.setquerytimeout(\"sessionid\" int, \"query\" int)\n"
2393 " external name clients.setquerytimeout;\n"
2394 "create procedure sys.setsessiontimeout(\"sessionid\" int, \"query\" int)\n"
2395 " external name clients.setsessiontimeout;\n"
2396 "create procedure sys.setworkerlimit(\"sessionid\" int, \"limit\" int)\n"
2397 " external name clients.setworkerlimit;\n"
2398 "create procedure sys.setmemorylimit(\"sessionid\" int, \"limit\" int)\n"
2399 " external name clients.setmemorylimit;\n"
2400 "create procedure sys.stopsession(\"sessionid\" int)\n"
2401 " external name clients.stopsession;\n");
2402
2403 pos += snprintf(buf + pos, bufsize - pos,
2404 "update sys.functions set system = true where schema_id = (select id from sys.schemas where name = 'sys')"
2405 " and name = 'sessions' and type = %d;\n", (int) F_UNION);
2406 pos += snprintf(buf + pos, bufsize - pos,
2407 "update sys._tables set system = true where schema_id = (select id from sys.schemas where name = 'sys')"
2408 " and name = 'sessions';\n");
2409 pos += snprintf(buf + pos, bufsize - pos,
2410 "update sys.functions set system = true where schema_id = (select id from sys.schemas where name = 'sys')"
2411 " and name in ('setoptimizer', 'setquerytimeout', 'setsessiontimeout', 'setworkerlimit', 'setmemorylimit', 'setoptimizer', 'stopsession') and type = %d;\n", (int) F_PROC);
2412
2413 /* 25_debug */
2414 pos += snprintf(buf + pos, bufsize - pos,
2415 "create function sys.debug(flag string) returns integer\n"
2416 " external name mdb.\"setDebug\";\n"
2417 "create function sys.debugflags()\n"
2418 " returns table(flag string, val bool)\n"
2419 " external name mdb.\"getDebugFlags\";\n");
2420 pos += snprintf(buf + pos, bufsize - pos,
2421 "update sys.functions set system = true where schema_id = (select id from sys.schemas where name = 'sys')"
2422 " and name in ('debug', 'debugflags');\n");
2423
2424 /* 26_sysmon */
2425 t = mvc_bind_table(sql, sys, "queue");
2426 t->system = 0; /* make it non-system else the drop view will fail */
2427
2428 pos += snprintf(buf + pos, bufsize - pos,
2429 "drop view sys.queue;\n"
2430 "drop function sys.queue;\n"
2431 "create function sys.queue()\n"
2432 "returns table(\n"
2433 " tag bigint,\n"
2434 " sessionid int,\n"
2435 " \"user\" string,\n"
2436 " started timestamp,\n"
2437 " status string,\n"
2438 " query string,\n"
2439 " progress int,\n"
2440 " workers int,\n"
2441 " memory int\n"
2442 ")\n"
2443 "external name sql.sysmon_queue;\n"
2444 "grant execute on function sys.queue to public;\n"
2445 "create view sys.queue as select * from sys.queue();\n"
2446 "grant select on sys.queue to public;\n"
2447
2448 "create procedure sys.pause(tag tinyint)\n"
2449 "external name sql.sysmon_pause;\n"
2450 "create procedure sys.resume(tag tinyint)\n"
2451 "external name sql.sysmon_resume;\n"
2452 "create procedure sys.stop(tag tinyint)\n"
2453 "external name sql.sysmon_stop;\n"
2454
2455 "create procedure sys.pause(tag smallint)\n"
2456 "external name sql.sysmon_pause;\n"
2457 "create procedure sys.resume(tag smallint)\n"
2458 "external name sql.sysmon_resume;\n"
2459 "create procedure sys.stop(tag smallint)\n"
2460 "external name sql.sysmon_stop;\n");
2461
2462 pos += snprintf(buf + pos, bufsize - pos,
2463 "update sys.functions set system = true where schema_id = (select id from sys.schemas where name = 'sys')"
2464 " and name = 'queue' and type = %d;\n", (int) F_UNION);
2465 pos += snprintf(buf + pos, bufsize - pos,
2466 "update sys.functions set system = true where schema_id = (select id from sys.schemas where name = 'sys')"
2467 " and name in ('pause', 'resume', 'stop') and type = %d;\n", (int) F_PROC);
2468 pos += snprintf(buf + pos, bufsize - pos,
2469 "update sys._tables set system = true where schema_id = (select id from sys.schemas where name = 'sys')"
2470 " and name = 'queue';\n");
2471
2472 pos += snprintf(buf + pos, bufsize - pos, "set schema \"%s\";\n", prev_schema);
2473 assert(pos < bufsize);
2474
2475 printf("Running database upgrade commands:\n%s\n", buf);
2476 err = SQLstatementIntern(c, &buf, "update", 1, 0, NULL);
2477 GDKfree(buf);
2478 return err; /* usually MAL_SUCCEED */
2479}
2480
2481int
2482SQLupgrades(Client c, mvc *m)
2483{
2484 sql_subtype tp;
2485 sql_subfunc *f;
2486 char *err, *prev_schema = GDKstrdup(stack_get_string(m, "current_schema"));
2487 sql_schema *s = mvc_bind_schema(m, "sys");
2488 sql_table *t;
2489 sql_column *col;
2490 bool systabfixed = false;
2491 int res = 0;
2492
2493 if (!prev_schema) {
2494 fprintf(stderr, "!Allocation failure while running SQL upgrades\n");
2495 res = -1;
2496 }
2497
2498#ifdef HAVE_HGE
2499 if (!res && have_hge) {
2500 sql_find_subtype(&tp, "hugeint", 0, 0);
2501 if (!sql_bind_aggr(m->sa, s, "var_pop", &tp)) {
2502 if ((err = sql_update_hugeint(c, m, prev_schema, &systabfixed)) != NULL) {
2503 fprintf(stderr, "!%s\n", err);
2504 freeException(err);
2505 res = -1;
2506 }
2507 }
2508 }
2509#endif
2510
2511 f = sql_bind_func_(m->sa, s, "env", NULL, F_UNION);
2512 if (!res && f && sql_privilege(m, ROLE_PUBLIC, f->func->base.id, PRIV_EXECUTE, 0) != PRIV_EXECUTE) {
2513 sql_table *privs = find_sql_table(s, "privileges");
2514 int pub = ROLE_PUBLIC, p = PRIV_EXECUTE, zero = 0;
2515
2516 table_funcs.table_insert(m->session->tr, privs, &f->func->base.id, &pub, &p, &zero, &zero);
2517 }
2518
2519 /* If the point type exists, but the geometry type does not
2520 * exist any more at the "sys" schema (i.e., the first part of
2521 * the upgrade has been completed succesfully), then move on
2522 * to the second part */
2523 if (!res && find_sql_type(s, "point") != NULL) {
2524 /* type sys.point exists: this is an old geom-enabled
2525 * database */
2526 if ((err = sql_update_geom(c, m, 1, prev_schema)) != NULL) {
2527 fprintf(stderr, "!%s\n", err);
2528 freeException(err);
2529 res = -1;
2530 }
2531 } else if (!res && geomsqlfix_get() != NULL) {
2532 /* the geom module is loaded... */
2533 sql_find_subtype(&tp, "clob", 0, 0);
2534 if (!sql_bind_func(m->sa, s, "st_wkttosql",
2535 &tp, NULL, F_FUNC)) {
2536 /* ... but the database is not geom-enabled */
2537 if ((err = sql_update_geom(c, m, 0, prev_schema)) != NULL) {
2538 fprintf(stderr, "!%s\n", err);
2539 freeException(err);
2540 res = -1;
2541 }
2542 }
2543 }
2544
2545 if (!res && mvc_bind_table(m, s, "function_languages") == NULL) {
2546 if ((err = sql_update_jul2017(c, prev_schema)) != NULL) {
2547 fprintf(stderr, "!%s\n", err);
2548 freeException(err);
2549 res = -1;
2550 }
2551 }
2552
2553 if (!res && (err = sql_update_jul2017_sp2(c)) != NULL) {
2554 fprintf(stderr, "!%s\n", err);
2555 freeException(err);
2556 res = -1;
2557 }
2558
2559 if (!res && (err = sql_update_jul2017_sp3(c, m, prev_schema, &systabfixed)) != NULL) {
2560 fprintf(stderr, "!%s\n", err);
2561 freeException(err);
2562 res = -1;
2563 }
2564
2565 if (!res && (t = mvc_bind_table(m, s, "geometry_columns")) != NULL &&
2566 (col = mvc_bind_column(m, t, "coord_dimension")) != NULL &&
2567 strcmp(col->type.type->sqlname, "int") != 0) {
2568 if ((err = sql_update_mar2018_geom(c, t, prev_schema)) != NULL) {
2569 fprintf(stderr, "!%s\n", err);
2570 freeException(err);
2571 res = -1;
2572 }
2573 }
2574
2575 if (!res && mvc_bind_schema(m, "wlc") == NULL &&
2576 !sql_bind_func(m->sa, s, "master", NULL, NULL, F_PROC)) {
2577 if ((err = sql_update_mar2018(c, m, prev_schema, &systabfixed)) != NULL) {
2578 fprintf(stderr, "!%s\n", err);
2579 freeException(err);
2580 res = -1;
2581 }
2582#ifdef HAVE_NETCDF
2583 if (mvc_bind_table(m, s, "netcdf_files") != NULL &&
2584 (err = sql_update_mar2018_netcdf(c, prev_schema)) != NULL) {
2585 fprintf(stderr, "!%s\n", err);
2586 freeException(err);
2587 res = -1;
2588 }
2589#endif
2590#ifdef HAVE_SAMTOOLS
2591 if ((err = sql_update_mar2018_samtools(c, m, prev_schema)) != NULL) {
2592 fprintf(stderr, "!%s\n", err);
2593 freeException(err);
2594 res = -1;
2595 }
2596#endif
2597 }
2598
2599 if (!res && sql_bind_func(m->sa, s, "dependencies_functions_os_triggers", NULL, NULL, F_UNION)) {
2600 if ((err = sql_update_mar2018_sp1(c, prev_schema)) != NULL) {
2601 fprintf(stderr, "!%s\n", err);
2602 freeException(err);
2603 res = -1;
2604 }
2605 }
2606
2607 if (!res && mvc_bind_table(m, s, "ids") != NULL) {
2608 /* determine if sys.ids needs to be updated (only the version of Mar2018) */
2609 char * qry = "select id from sys._tables where name = 'ids' and query like '% tmp.keys k join sys._tables% tmp.idxs i join sys._tables% tmp.triggers g join sys._tables% ';";
2610 res_table *output = NULL;
2611 err = SQLstatementIntern(c, &qry, "update", true, false, &output);
2612 if (err) {
2613 fprintf(stderr, "!%s\n", err);
2614 freeException(err);
2615 res = -1;
2616 } else {
2617 BAT *b = BATdescriptor(output->cols[0].b);
2618 if (b) {
2619 if (BATcount(b) > 0) {
2620 /* yes old view definition exists, it needs to be replaced */
2621 if ((err = sql_replace_Mar2018_ids_view(c, m, prev_schema)) != NULL) {
2622 fprintf(stderr, "!%s\n", err);
2623 freeException(err);
2624 res = -1;
2625 }
2626 }
2627 BBPunfix(b->batCacheid);
2628 }
2629 }
2630 if (output != NULL)
2631 res_tables_destroy(output);
2632 }
2633
2634 /* temporarily use variable `err' to check existence of MAL
2635 * module gsl */
2636 if (!res && (((err = getName("gsl")) == NULL || getModule(err) == NULL))) {
2637 /* no MAL module gsl, check for SQL function sys.chi2prob */
2638 sql_find_subtype(&tp, "double", 0, 0);
2639 if (sql_bind_func(m->sa, s, "chi2prob", &tp, &tp, F_FUNC)) {
2640 /* sys.chi2prob exists, but there is no
2641 * implementation */
2642 if ((err = sql_update_gsl(c, prev_schema)) != NULL) {
2643 fprintf(stderr, "!%s\n", err);
2644 freeException(err);
2645 res = -1;
2646 }
2647 }
2648 }
2649
2650 sql_find_subtype(&tp, "clob", 0, 0);
2651 if (!res && sql_bind_aggr(m->sa, s, "group_concat", &tp) == NULL) {
2652 if ((err = sql_update_aug2018(c, m, prev_schema)) != NULL) {
2653 fprintf(stderr, "!%s\n", err);
2654 freeException(err);
2655 res = -1;
2656 }
2657 }
2658
2659 if (!res && sql_bind_func(m->sa, s, "dependencies_schemas_on_users", NULL, NULL, F_UNION)
2660 && sql_bind_func(m->sa, s, "dependencies_owners_on_schemas", NULL, NULL, F_UNION)
2661 && sql_bind_func(m->sa, s, "dependencies_tables_on_views", NULL, NULL, F_UNION)
2662 && sql_bind_func(m->sa, s, "dependencies_tables_on_indexes", NULL, NULL, F_UNION)
2663 && sql_bind_func(m->sa, s, "dependencies_tables_on_triggers", NULL, NULL, F_UNION)
2664 && sql_bind_func(m->sa, s, "dependencies_tables_on_foreignkeys", NULL, NULL, F_UNION)
2665 && sql_bind_func(m->sa, s, "dependencies_tables_on_functions", NULL, NULL, F_UNION)
2666 && sql_bind_func(m->sa, s, "dependencies_columns_on_views", NULL, NULL, F_UNION)
2667 && sql_bind_func(m->sa, s, "dependencies_columns_on_keys", NULL, NULL, F_UNION)
2668 && sql_bind_func(m->sa, s, "dependencies_columns_on_indexes", NULL, NULL, F_UNION)
2669 && sql_bind_func(m->sa, s, "dependencies_columns_on_functions", NULL, NULL, F_UNION)
2670 && sql_bind_func(m->sa, s, "dependencies_columns_on_triggers", NULL, NULL, F_UNION)
2671 && sql_bind_func(m->sa, s, "dependencies_views_on_functions", NULL, NULL, F_UNION)
2672 && sql_bind_func(m->sa, s, "dependencies_views_on_triggers", NULL, NULL, F_UNION)
2673 && sql_bind_func(m->sa, s, "dependencies_functions_on_functions", NULL, NULL, F_UNION)
2674 && sql_bind_func(m->sa, s, "dependencies_functions_on_triggers", NULL, NULL, F_UNION)
2675 && sql_bind_func(m->sa, s, "dependencies_keys_on_foreignkeys", NULL, NULL, F_UNION) ) {
2676 if ((err = sql_drop_functions_dependencies_Xs_on_Ys(c, prev_schema)) != NULL) {
2677 fprintf(stderr, "!%s\n", err);
2678 freeException(err);
2679 res = -1;
2680 }
2681 }
2682
2683 if (!res && (err = sql_update_aug2018_sp2(c, prev_schema)) != NULL) {
2684 fprintf(stderr, "!%s\n", err);
2685 freeException(err);
2686 res = -1;
2687 }
2688
2689 if (!res && (t = mvc_bind_table(m, s, "systemfunctions")) != NULL &&
2690 t->type == tt_table) {
2691 if (!systabfixed &&
2692 (err = sql_fix_system_tables(c, m, prev_schema)) != NULL) {
2693 fprintf(stderr, "!%s\n", err);
2694 freeException(err);
2695 res = -1;
2696 }
2697 systabfixed = true;
2698 if ((err = sql_update_apr2019(c, m, prev_schema)) != NULL) {
2699 fprintf(stderr, "!%s\n", err);
2700 freeException(err);
2701 res = -1;
2702 }
2703 }
2704
2705 /* when function storagemodel() exists and views tablestorage
2706 * and schemastorage don't, then upgrade storagemodel to match
2707 * 75_storagemodel.sql */
2708 if (!res && sql_bind_func(m->sa, s, "storagemodel", NULL, NULL, F_UNION)
2709 && (t = mvc_bind_table(m, s, "tablestorage")) == NULL
2710 && (t = mvc_bind_table(m, s, "schemastorage")) == NULL ) {
2711 if ((err = sql_update_storagemodel(c, m, prev_schema)) != NULL) {
2712 fprintf(stderr, "!%s\n", err);
2713 freeException(err);
2714 res = -1;
2715 }
2716 }
2717
2718 if (!res && (err = sql_update_apr2019_sp1(c)) != NULL) {
2719 fprintf(stderr, "!%s\n", err);
2720 freeException(err);
2721 res = -1;
2722 }
2723
2724 if (!res && sql_bind_func(m->sa, s, "times", NULL, NULL, F_PROC)) {
2725 if (!res && (err = sql_update_apr2019_sp2(c, m, prev_schema, &systabfixed)) != NULL) {
2726 fprintf(stderr, "!%s\n", err);
2727 freeException(err);
2728 res = -1;
2729 }
2730 }
2731
2732 sql_find_subtype(&tp, "string", 0, 0);
2733 if (!res && !sql_bind_func3(m->sa, s, "deltas", &tp, &tp, &tp, F_UNION)) {
2734 if ((err = sql_update_nov2019_missing_dependencies(c, m)) != NULL) {
2735 fprintf(stderr, "!%s\n", err);
2736 freeException(err);
2737 res = -1;
2738 }
2739 if (!systabfixed &&
2740 (err = sql_fix_system_tables(c, m, prev_schema)) != NULL) {
2741 fprintf(stderr, "!%s\n", err);
2742 freeException(err);
2743 res = -1;
2744 }
2745 systabfixed = true;
2746 if ((err = sql_update_nov2019(c, m, prev_schema, &systabfixed)) != NULL) {
2747 fprintf(stderr, "!%s\n", err);
2748 freeException(err);
2749 res = -1;
2750 }
2751 }
2752
2753 if (!res && !sql_bind_func(m->sa, s, "suspend_log_flushing", NULL, NULL, F_PROC)) {
2754 if ((err = sql_update_default(c, m, prev_schema)) != NULL) {
2755 fprintf(stderr, "!%s\n", err);
2756 freeException(err);
2757 res = -1;
2758 }
2759 }
2760
2761 GDKfree(prev_schema);
2762 return res;
2763}
2764