Changeset: 87c7e0f8e5a3 for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=87c7e0f8e5a3
Added Files:
sql/backends/monet5/sql_upgrades.c
sql/backends/monet5/sql_upgrades.h
Modified Files:
sql/backends/monet5/Makefile.ag
sql/backends/monet5/sql_scenario.c
sql/backends/monet5/sql_scenario.h
Branch: default
Log Message:
Separate out upgrade instructions
diffs (truncated from 2156 to 300 lines):
diff --git a/sql/backends/monet5/Makefile.ag b/sql/backends/monet5/Makefile.ag
--- a/sql/backends/monet5/Makefile.ag
+++ b/sql/backends/monet5/Makefile.ag
@@ -41,6 +41,7 @@ lib__sql = {
mal_backend.c mal_backend.h \
sql_user.c sql_user.h \
sql_scenario.c sql_scenario.h \
+ sql_upgrades.c sql_upgrades.h \
rel_bin.c rel_bin.h \
sql_statement.c sql_statement.h \
sql_statistics.c sql_statistics.h \
diff --git a/sql/backends/monet5/sql_scenario.c
b/sql/backends/monet5/sql_scenario.c
--- a/sql/backends/monet5/sql_scenario.c
+++ b/sql/backends/monet5/sql_scenario.c
@@ -60,6 +60,7 @@
#include "opt_prelude.h"
#include "opt_pipes.h"
#include <unistd.h>
+#include "sql_upgrades.h"
static int SQLinitialized = 0;
static int SQLnewcatalog = 0;
@@ -365,7 +366,7 @@ handle_error(mvc *m, stream *out, int ps
return go;
}
-static int
+int
SQLautocommit(Client c, mvc *m)
{
if (m->session->auto_commit && m->session->active) {
@@ -379,7 +380,7 @@ SQLautocommit(Client c, mvc *m)
return TRUE;
}
-static void
+void
SQLtrans(mvc *m)
{
m->caching = m->cache;
@@ -387,957 +388,6 @@ SQLtrans(mvc *m)
mvc_trans(m);
}
-static str
-sql_update_feb2013(Client c)
-{
- char *buf = GDKmalloc(4096), *err = NULL;
- size_t bufsize = 4096, pos = 0;
-
- /* sys.stddev_samp functions */
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.stddev_samp(val TINYINT) returns DOUBLE external name
\"aggr\".\"stdev\";\n");
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.stddev_samp(val SMALLINT) returns DOUBLE external name
\"aggr\".\"stdev\";\n");
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.stddev_samp(val INTEGER) returns DOUBLE external name
\"aggr\".\"stdev\";\n");
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.stddev_samp(val BIGINT) returns DOUBLE external name
\"aggr\".\"stdev\";\n");
-#ifdef HAVE_HGE
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.stddev_samp(val HUGEINT) returns DOUBLE external name
\"aggr\".\"stdev\";\n");
-#endif
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.stddev_samp(val REAL) returns DOUBLE external name \"aggr\".\"stdev\";\n");
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.stddev_samp(val DOUBLE) returns DOUBLE external name
\"aggr\".\"stdev\";\n");
-
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.stddev_samp(val DATE) returns DOUBLE external name \"aggr\".\"stdev\";\n");
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.stddev_samp(val TIME) returns DOUBLE external name \"aggr\".\"stdev\";\n");
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.stddev_samp(val TIMESTAMP) returns DOUBLE external name
\"aggr\".\"stdev\";\n");
-
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.stddev_pop(val TINYINT) returns DOUBLE external name
\"aggr\".\"stdevp\";\n");
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.stddev_pop(val SMALLINT) returns DOUBLE external name
\"aggr\".\"stdevp\";\n");
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.stddev_pop(val INTEGER) returns DOUBLE external name
\"aggr\".\"stdevp\";\n");
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.stddev_pop(val BIGINT) returns DOUBLE external name
\"aggr\".\"stdevp\";\n");
-#ifdef HAVE_HGE
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.stddev_pop(val HUGEINT) returns DOUBLE external name
\"aggr\".\"stdevp\";\n");
-#endif
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.stddev_pop(val REAL) returns DOUBLE external name \"aggr\".\"stdevp\";\n");
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.stddev_pop(val DOUBLE) returns DOUBLE external name
\"aggr\".\"stdevp\";\n");
-
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.stddev_pop(val DATE) returns DOUBLE external name \"aggr\".\"stdevp\";\n");
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.stddev_pop(val TIME) returns DOUBLE external name \"aggr\".\"stdevp\";\n");
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.stddev_pop(val TIMESTAMP) returns DOUBLE external name
\"aggr\".\"stdevp\";\n");
-
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.var_samp(val TINYINT) returns DOUBLE external name
\"aggr\".\"variance\";\n");
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.var_samp(val SMALLINT) returns DOUBLE external name
\"aggr\".\"variance\";\n");
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.var_samp(val INTEGER) returns DOUBLE external name
\"aggr\".\"variance\";\n");
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.var_samp(val BIGINT) returns DOUBLE external name
\"aggr\".\"variance\";\n");
-#ifdef HAVE_HGE
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.var_samp(val HUGEINT) returns DOUBLE external name
\"aggr\".\"variance\";\n");
-#endif
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.var_samp(val REAL) returns DOUBLE external name \"aggr\".\"variance\";\n");
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.var_samp(val DOUBLE) returns DOUBLE external name
\"aggr\".\"variance\";\n");
-
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.var_samp(val DATE) returns DOUBLE external name \"aggr\".\"variance\";\n");
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.var_samp(val TIME) returns DOUBLE external name \"aggr\".\"variance\";\n");
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.var_samp(val TIMESTAMP) returns DOUBLE external name
\"aggr\".\"variance\";\n");
-
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.var_pop(val TINYINT) returns DOUBLE external name
\"aggr\".\"variancep\";\n");
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.var_pop(val SMALLINT) returns DOUBLE external name
\"aggr\".\"variancep\";\n");
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.var_pop(val INTEGER) returns DOUBLE external name
\"aggr\".\"variancep\";\n");
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.var_pop(val BIGINT) returns DOUBLE external name
\"aggr\".\"variancep\";\n");
-#ifdef HAVE_HGE
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.var_pop(val HUGEINT) returns DOUBLE external name
\"aggr\".\"variancep\";\n");
-#endif
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.var_pop(val REAL) returns DOUBLE external name \"aggr\".\"variancep\";\n");
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.var_pop(val DOUBLE) returns DOUBLE external name
\"aggr\".\"variancep\";\n");
-
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.var_pop(val DATE) returns DOUBLE external name \"aggr\".\"variancep\";\n");
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.var_pop(val TIME) returns DOUBLE external name \"aggr\".\"variancep\";\n");
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.var_pop(val TIMESTAMP) returns DOUBLE external name
\"aggr\".\"variancep\";\n");
-
- pos += snprintf(buf + pos, bufsize - pos,
- "insert into sys.systemfunctions (select f.id from
sys.functions f, sys.schemas s where f.name in ('stddev_samp', 'stddev_pop',
'var_samp', 'var_pop') and f.type = %d and f.schema_id = s.id and s.name =
'sys');\n", F_AGGR);
-
- assert(pos < 4096);
-
- printf("Running database upgrade commands:\n%s\n", buf);
- err = SQLstatementIntern(c, &buf, "update", 1, 0, NULL);
- GDKfree(buf);
- return err; /* usually MAL_SUCCEED */
-}
-
-
-static str
-sql_update_feb2013_sp1(Client c)
-{
- size_t bufsize = 10240, pos = 0;
- char *buf = GDKmalloc(bufsize), *err = NULL;
-
- /* sys.stddev functions */
- pos += snprintf(buf + pos, bufsize - pos, "drop filter function
sys.\"like\"(string, string, string);\n");
- pos += snprintf(buf + pos, bufsize - pos, "drop filter function
sys.\"ilike\"(string, string, string);\n");
- pos += snprintf(buf + pos, bufsize - pos, "create filter function
sys.\"like\"(val string, pat string, esc string) external name
algebra.\"like\";\n");
- pos += snprintf(buf + pos, bufsize - pos, "create filter function
sys.\"ilike\"(val string, pat string, esc string) external name
algebra.\"ilike\";\n");
- pos += snprintf(buf + pos, bufsize - pos, "create filter function
sys.\"like\"(val string, pat string) external name algebra.\"like\";\n");
- pos += snprintf(buf + pos, bufsize - pos, "create filter function
sys.\"ilike\"(val string, pat string) external name algebra.\"ilike\";\n");
-
- pos += snprintf(buf + pos, bufsize - pos, "drop function
sys.storage;\n");
-
- /* 75_storagemodel.sql */
- pos += snprintf(buf + pos, bufsize - pos, "\
-create function sys.storage()\n\
-returns table (\"schema\" string, \"table\" string, \"column\" string,
\"type\" string, location string, \"count\" bigint, typewidth int, columnsize
bigint, heapsize bigint, indices bigint, sorted boolean)\n\
-external name sql.storage;\n\
-\n\
-create table sys.storagemodelinput(\n\
- \"schema\" string,\n\
- \"table\" string,\n\
- \"column\" string,\n\
- \"type\" string,\n\
- \"typewidth\" int,\n\
- \"count\" bigint,\n\
- \"distinct\" bigint,\n\
- \"atomwidth\" int,\n\
- \"reference\" boolean,\n\
- \"sorted\" boolean\n\
-);\n\
-update sys._tables\n\
- set system = true\n\
- where name = 'storagemodelinput'\n\
- and schema_id = (select id from sys.schemas where name =
'sys');\n\
-\n\
-create procedure sys.storagemodelinit()\n\
-begin\n\
- delete from sys.storagemodelinput;\n\
- insert into sys.storagemodelinput\n\
- select X.\"schema\", X.\"table\", X.\"column\", X.\"type\",
X.typewidth, X.count, 0, X.typewidth, false, X.sorted from sys.storage() X;\n\
- update sys.storagemodelinput\n\
- set reference = true\n\
- where concat(concat(\"schema\",\"table\"), \"column\") in (\n\
- SELECT concat( concat(\"fkschema\".\"name\",
\"fktable\".\"name\"), \"fkkeycol\".\"name\" )\n\
- FROM \"sys\".\"keys\" AS \"fkkey\",\n\
- \"sys\".\"objects\" AS \"fkkeycol\",\n\
- \"sys\".\"tables\" AS \"fktable\",\n\
- \"sys\".\"schemas\" AS \"fkschema\"\n\
- WHERE \"fktable\".\"id\" = \"fkkey\".\"table_id\"\n\
- AND \"fkkey\".\"id\" = \"fkkeycol\".\"id\"\n\
- AND \"fkschema\".\"id\" = \"fktable\".\"schema_id\"\n\
- AND \"fkkey\".\"rkey\" > -1);\n\
- update sys.storagemodelinput\n\
- set \"distinct\" = \"count\"\n\
- where \"type\" = 'varchar' or \"type\"='clob';\n\
-end;\n\
-\n\
-create function sys.columnsize(nme string, i bigint, d bigint)\n\
-returns bigint\n\
-begin\n\
- case\n\
- when nme = 'boolean' then return i;\n\
- when nme = 'char' then return 2*i;\n\
- when nme = 'smallint' then return 2 * i;\n\
- when nme = 'int' then return 4 * i;\n\
- when nme = 'bigint' then return 8 * i;\n\
- when nme = 'timestamp' then return 8 * i;\n\
- when nme = 'varchar' then\n\
- case\n\
- when cast(d as bigint) << 8 then return i;\n\
- when cast(d as bigint) << 16 then return 2 * i;\n\
- when cast(d as bigint) << 32 then return 4 * i;\n\
- else return 8 * i;\n\
- end case;\n\
- else return 8 * i;\n\
- end case;\n\
-end;\n\
-\n\
-create function sys.heapsize(tpe string, i bigint, w int)\n\
-returns bigint\n\
-begin\n\
- if tpe <> 'varchar' and tpe <> 'clob'\n\
- then\n\
- return 0;\n\
- end if;\n\
- return 10240 + i * w;\n\
-end;\n\
-\n\
-create function sys.indexsize(b boolean, i bigint)\n\
-returns bigint\n\
-begin\n\
-\n\
- if b = true\n\
- then\n\
- return 8 * i;\n\
- end if;\n\
- return 0;\n\
-end;\n\
-\n\
-create function sys.storagemodel()\n\
-returns table (\n\
- \"schema\" string,\n\
- \"table\" string,\n\
- \"column\" string,\n\
- \"type\" string,\n\
- \"count\" bigint,\n\
- columnsize bigint,\n\
- heapsize bigint,\n\
- indices bigint,\n\
- sorted boolean)\n\
-begin\n\
- return select I.\"schema\", I.\"table\", I.\"column\", I.\"type\",
I.\"count\",\n\
- columnsize(I.\"type\", I.count, I.\"distinct\"),\n\
- heapsize(I.\"type\", I.\"distinct\", I.\"atomwidth\"),\n\
- indexsize(I.\"reference\", I.\"count\"),\n\
- I.sorted\n\
- from sys.storagemodelinput I;\n\
-end;\n\
-\n\
-create view sys.tablestoragemodel\n\
-as select \"schema\",\"table\",max(count) as \"count\",\n\
- sum(columnsize) as columnsize,\n\
- sum(heapsize) as heapsize,\n\
- sum(indices) as indices,\n\
- sum(case when sorted = false then 8 * count else 0 end) as auxillary\n\
-from sys.storagemodel() group by \"schema\",\"table\";\n\
-update sys._tables\n\
- set system = true\n\
- where name = 'tablestoragemodel'\n\
- and schema_id = (select id from sys.schemas where name =
'sys');\n");
-
- pos += snprintf(buf + pos, bufsize - pos, "insert into
sys.systemfunctions (select f.id from sys.functions f, sys.schemas s where
f.name in ('like', 'ilike') and f.type = %d and f.schema_id = s.id and s.name =
'sys');\n", F_FILT);
- pos += snprintf(buf + pos, bufsize - pos,
- "insert into sys.systemfunctions (select f.id from
sys.functions f, sys.schemas s where f.name in ('storage', 'columnsize',
'heapsize', 'indexsize', 'storagemodel') and f.type = %d and f.schema_id = s.id
and s.name = 'sys');\n",
- F_FUNC);
- pos += snprintf(buf + pos, bufsize - pos, "insert into
sys.systemfunctions (select f.id from sys.functions f, sys.schemas s where
f.name = 'storagemodelinit' and f.type = %d and f.schema_id = s.id and s.name =
'sys');\n", F_PROC);
-
- assert(pos < bufsize);
-
- printf("Running database upgrade commands:\n%s\n", buf);
- err = SQLstatementIntern(c, &buf, "update", 1, 0, NULL);
- GDKfree(buf);
- return err; /* usually MAL_SUCCEED */
-}
-
-static str
-sql_update_feb2013_sp3(Client c)
-{
- size_t bufsize = 4096, pos = 0;
- char *buf = GDKmalloc(bufsize), *err = NULL;
-
- /* aggregates on type WRD */
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.stddev_samp(val WRD) returns DOUBLE external name \"aggr\".\"stdev\";\n");
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.stddev_pop(val WRD) returns DOUBLE external name \"aggr\".\"stdevp\";\n");
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.var_samp(val WRD) returns DOUBLE external name \"aggr\".\"variance\";\n");
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.var_pop(val WRD) returns DOUBLE external name \"aggr\".\"variancep\";\n");
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate
sys.median(val WRD) returns WRD external name \"aggr\".\"median\";\n");
- pos += snprintf(buf + pos, bufsize - pos, "create aggregate sys.corr(e1
WRD, e2 WRD) returns WRD external name \"aggr\".\"corr\";\n");
-
- pos += snprintf(buf + pos, bufsize - pos,
- "insert into sys.systemfunctions (select f.id from
sys.functions f, sys.schemas s where f.name in ('stddev_samp', 'stddev_pop',
'var_samp', 'var_pop', 'median', 'corr') and f.type = %d and f.schema_id = s.id
and s.name = 'sys');\n",
- F_AGGR);
-
- assert(pos < 4096);
-
- printf("Running database upgrade commands:\n%s\n", buf);
- err = SQLstatementIntern(c, &buf, "update", 1, 0, NULL);
- GDKfree(buf);
- return err; /* usually MAL_SUCCEED */
-}
-
-static str
-sql_update_jan2014(Client c)
-{
- size_t bufsize = 25000, pos = 0;
- char *buf = GDKmalloc(bufsize), *err = NULL;
- ValRecord *schvar = stack_get_var(((backend *) c->sqlcontext)->mvc,
"current_schema");
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
