Changeset: d37a870d10af for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=d37a870d10af
Modified Files:
clients/Tests/SQL-dump.sql
clients/Tests/SQL-dump.stable.out
clients/Tests/SQL-dump_nogeom.stable.out
sql/backends/monet5/sql_scenario.c
Branch: Jan2014
Log Message:
Fixed up upgrade from Feb2013 database.
diffs (truncated from 351 to 300 lines):
diff --git a/clients/Tests/SQL-dump.sql b/clients/Tests/SQL-dump.sql
--- a/clients/Tests/SQL-dump.sql
+++ b/clients/Tests/SQL-dump.sql
@@ -42,6 +42,7 @@
\dSv sys.querylog_catalog
\dSv sys.querylog_history
\dSv sys.queue
+\dSv sys.sessions
\dSv sys.storage
\dSv sys.storagemodel
\dSv sys.tables
@@ -49,6 +50,7 @@
\dSv sys.tracelog
\dSv sys.users
+-- select distinct name from sys.functions where sql = true order by name;
\dSf sys.Intersect
\dSf sys.Union
\dSf sys.abbrev
@@ -165,6 +167,7 @@
\dSf sys.pointfromtext
\dSf sys.polyfromtext
\dSf sys.polygonfromtext
+\dSf sys.quantile
\dSf sys.querycache
\dSf sys.querylog
\dSf sys.querylog_calls
@@ -178,9 +181,12 @@
\dSf sys.resume
\dSf sys.reuse
\dSf sys.reverse
+\dSf sys.sessions
\dSf sys.setmasklen
+\dSf sys.setsession
\dSf sys.settimeout
\dSf sys.shrink
+\dSf sys.shutdown
\dSf sys.srid
\dSf sys.stddev_pop
\dSf sys.stddev_samp
diff --git a/clients/Tests/SQL-dump.stable.out
b/clients/Tests/SQL-dump.stable.out
--- a/clients/Tests/SQL-dump.stable.out
+++ b/clients/Tests/SQL-dump.stable.out
@@ -423,6 +423,7 @@ select qd.*, ql."start",ql."stop", ql.ar
from sys.querylog_catalog() qd, sys.querylog_calls() ql
where qd.id = ql.id and qd.owner = user;
create view sys.queue as select * from sys.queue();
+create view sys.sessions as select * from sys.sessions();
create view sys.storage as select * from sys.storage();
create view sys.storagemodel as select * from sys.storagemodel();
SELECT * FROM (SELECT p.*, 0 AS "temporary" FROM "sys"."_tables" AS p UNION
ALL SELECT t.*, 1 AS "temporary" FROM "tmp"."_tables" AS t) AS tables where
tables.type <> 2;
@@ -984,6 +985,28 @@ create function pointfromtext(wkt string
create function polyfromtext(wkt string, srid smallint) returns polygon
external name geom."PolyFromText";
-- alias
create function polygonfromtext(wkt string, srid smallint) returns polygon
external name geom."PolyFromText";
+create aggregate quantile(val tinyint, q double) returns tinyint
+ external name "aggr"."quantile";
+create aggregate quantile(val smallint, q double) returns smallint
+ external name "aggr"."quantile";
+create aggregate quantile(val integer, q double) returns integer
+ external name "aggr"."quantile";
+create aggregate quantile(val wrd, q double) returns wrd
+ external name "aggr"."quantile";
+create aggregate quantile(val bigint, q double) returns bigint
+ external name "aggr"."quantile";
+create aggregate quantile(val decimal, q double) returns decimal
+ external name "aggr"."quantile";
+create aggregate quantile(val real, q double) returns real
+ external name "aggr"."quantile";
+create aggregate quantile(val double, q double) returns double
+ external name "aggr"."quantile";
+create aggregate quantile(val date, q double) returns date
+ external name "aggr"."quantile";
+create aggregate quantile(val time, q double) returns time
+ external name "aggr"."quantile";
+create aggregate quantile(val timestamp, q double) returns timestamp
+ external name "aggr"."quantile";
-- SQL QUERY CACHE
-- The SQL query cache returns a table with the query plans kept
@@ -1130,8 +1153,13 @@ All Rights Reserved.
-- Reverse a string
create function reverse(src string)
returns string external name udf.reverse;
+create function sys.sessions()
+returns table("user" string, "login" timestamp, "sessiontimeout" bigint,
"lastcommand" timestamp, "querytimeout" bigint, "active" bool)
+external name sql.sessions;
create function "setmasklen" (p inet, mask int) returns inet
external name inet."setmasklen";
+create procedure sys.setsession("timeout" bigint)
+ external name sql.setsession;
-- control the query and session time out
create procedure sys.settimeout("query" bigint)
external name sql.settimeout;
@@ -1162,6 +1190,10 @@ create procedure sys.settimeout("query"
create procedure shrink(sys string, tab string)
external name sql.shrink;
+create procedure sys.shutdown(delay tinyint)
+external name sql.shutdown;
+create procedure sys.shutdown(delay tinyint, force bool)
+external name sql.shutdown;
create function srid(g geometry) returns integer external name geom."SRID";
create aggregate stddev_pop(val tinyint) returns double
external name "aggr"."stdevp";
diff --git a/clients/Tests/SQL-dump_nogeom.stable.out
b/clients/Tests/SQL-dump_nogeom.stable.out
--- a/clients/Tests/SQL-dump_nogeom.stable.out
+++ b/clients/Tests/SQL-dump_nogeom.stable.out
@@ -383,6 +383,7 @@ select qd.*, ql."start",ql."stop", ql.ar
from sys.querylog_catalog() qd, sys.querylog_calls() ql
where qd.id = ql.id and qd.owner = user;
create view sys.queue as select * from sys.queue();
+create view sys.sessions as select * from sys.sessions();
create view sys.storage as select * from sys.storage();
create view sys.storagemodel as select * from sys.storagemodel();
SELECT * FROM (SELECT p.*, 0 AS "temporary" FROM "sys"."_tables" AS p UNION
ALL SELECT t.*, 1 AS "temporary" FROM "tmp"."_tables" AS t) AS tables where
tables.type <> 2;
@@ -897,6 +898,28 @@ create procedure sys.pause(tag int)
external name sql.sysmon_pause;
create procedure sys.pause(tag bigint)
external name sql.sysmon_pause;
+create aggregate quantile(val tinyint, q double) returns tinyint
+ external name "aggr"."quantile";
+create aggregate quantile(val smallint, q double) returns smallint
+ external name "aggr"."quantile";
+create aggregate quantile(val integer, q double) returns integer
+ external name "aggr"."quantile";
+create aggregate quantile(val wrd, q double) returns wrd
+ external name "aggr"."quantile";
+create aggregate quantile(val bigint, q double) returns bigint
+ external name "aggr"."quantile";
+create aggregate quantile(val decimal, q double) returns decimal
+ external name "aggr"."quantile";
+create aggregate quantile(val real, q double) returns real
+ external name "aggr"."quantile";
+create aggregate quantile(val double, q double) returns double
+ external name "aggr"."quantile";
+create aggregate quantile(val date, q double) returns date
+ external name "aggr"."quantile";
+create aggregate quantile(val time, q double) returns time
+ external name "aggr"."quantile";
+create aggregate quantile(val timestamp, q double) returns timestamp
+ external name "aggr"."quantile";
-- SQL QUERY CACHE
-- The SQL query cache returns a table with the query plans kept
@@ -1042,8 +1065,13 @@ All Rights Reserved.
-- Reverse a string
create function reverse(src string)
returns string external name udf.reverse;
+create function sys.sessions()
+returns table("user" string, "login" timestamp, "sessiontimeout" bigint,
"lastcommand" timestamp, "querytimeout" bigint, "active" bool)
+external name sql.sessions;
create function "setmasklen" (p inet, mask int) returns inet
external name inet."setmasklen";
+create procedure sys.setsession("timeout" bigint)
+ external name sql.setsession;
-- control the query and session time out
create procedure sys.settimeout("query" bigint)
external name sql.settimeout;
@@ -1074,6 +1102,10 @@ create procedure sys.settimeout("query"
create procedure shrink(sys string, tab string)
external name sql.shrink;
+create procedure sys.shutdown(delay tinyint)
+external name sql.shutdown;
+create procedure sys.shutdown(delay tinyint, force bool)
+external name sql.shutdown;
create aggregate stddev_pop(val tinyint) returns double
external name "aggr"."stdevp";
create aggregate stddev_pop(val smallint) returns double
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
@@ -654,48 +654,18 @@ sql_update_feb2013_sp3(Client c)
static str
sql_update_jan2014(Client c)
{
- size_t bufsize = 12800, pos = 0;
+ size_t bufsize = 15000, pos = 0;
char *buf = GDKmalloc(bufsize), *err = NULL;
char *fullname;
- FILE *fp1 = NULL, *fp2 = NULL, *fp3 = NULL;
+ FILE *fp = NULL;
ValRecord *schvar = stack_get_var(((backend *) c->sqlcontext)->mvc,
"current_schema");
char *schema = NULL;
if (schvar)
schema = strdup(schvar->val.sval);
- snprintf(buf, bufsize, "createdb%c15_querylog", DIR_SEP);
- if ((fullname = MSP_locate_sqlscript(buf, 1)) != NULL) {
- fp1 = fopen(fullname, "r");
- GDKfree(fullname);
- }
- snprintf(buf, bufsize, "createdb%c26_sysmon", DIR_SEP);
- if ((fullname = MSP_locate_sqlscript(buf, 1)) != NULL) {
- fp2 = fopen(fullname, "r");
- GDKfree(fullname);
- }
- snprintf(buf, bufsize, "createdb%c40_json", DIR_SEP);
- if ((fullname = MSP_locate_sqlscript(buf, 1)) != NULL) {
- fp3 = fopen(fullname, "r");
- GDKfree(fullname);
- }
-
pos += snprintf(buf + pos, bufsize - pos, "set schema \"sys\";\n");
- /* new entry in 16_tracelog.sql */
- pos += snprintf(buf + pos, bufsize - pos, "create view sys.tracelog as
select * from sys.tracelog();\n");
-
- /* deleted entry from 22_clients.sql */
- pos += snprintf(buf + pos, bufsize - pos, "drop function
sys.clients;\n");
-
- /* added entry in 25_debug.sql */
- pos += snprintf(buf + pos, bufsize - pos, "create view sys.optimizers
as select * from sys.optimizers();\n");
- pos += snprintf(buf + pos, bufsize - pos, "create view sys.environment
as select * from sys.environment();\n");
-
- /* added entry in 75_storagemodel.sql */
- pos += snprintf(buf + pos, bufsize - pos, "create view sys.storage as
select * from sys.storage();\n");
- pos += snprintf(buf + pos, bufsize - pos, "create view sys.storagemodel
as select * from sys.storagemodel();\n");
-
/* replaced 15_history.sql by 15_querylog.sql */
pos += snprintf(buf + pos, bufsize - pos, "drop procedure
sys.resetHistory;\n");
pos += snprintf(buf + pos, bufsize - pos, "drop procedure
sys.keepCall;\n");
@@ -721,36 +691,50 @@ sql_update_jan2014(Client c)
}
}
}
- pos += snprintf(buf + pos, bufsize - pos, "update sys._tables set
system = false where name in ('querylog','callhistory','queryhistory') and
schema_id = (select id from sys.schemas where name = 'sys');\n");
+ pos += snprintf(buf + pos, bufsize - pos, "update sys._tables set
system = false where name in ('callhistory','queryhistory','querylog') and
schema_id = (select id from sys.schemas where name = 'sys');\n");
pos += snprintf(buf + pos, bufsize - pos, "drop view sys.queryLog;\n");
pos += snprintf(buf + pos, bufsize - pos, "drop table
sys.callHistory;\n");
pos += snprintf(buf + pos, bufsize - pos, "drop table
sys.queryHistory;\n");
- if (fp1) {
- pos += fread(buf + pos, 1, bufsize - pos, fp1);
- fclose(fp1);
+ snprintf(buf + pos, bufsize - pos, "createdb%c15_querylog", DIR_SEP);
+ if ((fullname = MSP_locate_sqlscript(buf + pos, 1)) != NULL) {
+ fp = fopen(fullname, "r");
+ GDKfree(fullname);
+ }
+ if (fp) {
+ pos += fread(buf + pos, 1, bufsize - pos, fp);
+ fclose(fp);
}
+ /* new entry in 16_tracelog.sql */
+ pos += snprintf(buf + pos, bufsize - pos, "create view sys.tracelog as
select * from sys.tracelog();\n");
+
+ /* deleted entry from and new entries in 22_clients.sql */
+ pos += snprintf(buf + pos, bufsize - pos, "drop function
sys.clients;\n");
+ pos += snprintf(buf + pos, bufsize - pos, "create function
sys.sessions() returns table(\"user\" string, \"login\" timestamp,
\"sessiontimeout\" bigint, \"lastcommand\" timestamp, \"querytimeout\" bigint,
\"active\" bool) external name sql.sessions;\n");
+ pos += snprintf(buf + pos, bufsize - pos, "create view sys.sessions as
select * from sys.sessions();\n");
+ pos += snprintf(buf + pos, bufsize - pos, "create procedure
sys.shutdown(delay tinyint) external name sql.shutdown;\n");
+ pos += snprintf(buf + pos, bufsize - pos, "create procedure
sys.shutdown(delay tinyint, force bool) external name sql.shutdown;\n");
+ pos += snprintf(buf + pos, bufsize - pos, "create procedure
sys.settimeout(\"query\" bigint) external name sql.settimeout;\n");
+ pos += snprintf(buf + pos, bufsize - pos, "create procedure
sys.settimeout(\"query\" bigint, \"session\" bigint) external name
sql.settimeout;\n");
+ pos += snprintf(buf + pos, bufsize - pos, "create procedure
sys.setsession(\"timeout\" bigint) external name sql.setsession;\n");
+
+ /* added entry in 25_debug.sql */
+ pos += snprintf(buf + pos, bufsize - pos, "create view sys.optimizers
as select * from sys.optimizers();\n");
+ pos += snprintf(buf + pos, bufsize - pos, "create view sys.environment
as select * from sys.environment();\n");
+
/* new file 26_sysmon.sql */
- if (fp2) {
- pos += fread(buf + pos, 1, bufsize - pos, fp2);
- fclose(fp2);
+ snprintf(buf + pos, bufsize - pos, "createdb%c26_sysmon", DIR_SEP);
+ if ((fullname = MSP_locate_sqlscript(buf + pos, 1)) != NULL) {
+ fp = fopen(fullname, "r");
+ GDKfree(fullname);
+ }
+ if (fp) {
+ pos += fread(buf + pos, 1, bufsize - pos, fp);
+ fclose(fp);
}
- /* new file 40_json.sql */
- if (fp3) {
- pos += fread(buf + pos, 1, bufsize - pos, fp3);
- fclose(fp3);
- }
-
- pos += snprintf(buf + pos, bufsize - pos,
- "insert into sys.systemfunctions (select f.id from
sys.functions f, sys.schemas s where f.name in ('querylog_catalog',
'querylog_calls', 'queue', 'json_filter', 'json_filter_all', 'json_path',
'json_text', 'json_isvalid', 'json_isvalidobject', 'json_isvalidarray',
'json_length') 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 in ('querylog_empty',
'querylog_enable', 'querylog_disable', 'pause', 'resume', 'sysmon_resume',
'stop') and f.type = %d and f.schema_id = s.id and s.name = 'sys');\n",
- F_PROC);
- pos += snprintf(buf + pos, bufsize - pos, "update sys._tables set
system = true where name in ('tracelog', 'optimizers', 'environment',
'storage', 'storagemodel') and schema_id = (select id from sys.schemas where
name = 'sys');\n");
-
- /* new entries in 39_analytics.sql for quantiles and one previously
missing median */
+ /* new entries in 39_analytics.sql for quantiles and one
+ * previously missing median */
pos += snprintf(buf + pos, bufsize - pos, "create aggregate
quantile(val TINYINT, q DOUBLE) returns TINYINT external name
\"aggr\".\"quantile\";\n");
pos += snprintf(buf + pos, bufsize - pos, "create aggregate
quantile(val SMALLINT, q DOUBLE) returns SMALLINT external name
\"aggr\".\"quantile\";\n");
pos += snprintf(buf + pos, bufsize - pos, "create aggregate
quantile(val INTEGER, q DOUBLE) returns INTEGER external name
\"aggr\".\"quantile\";\n");
@@ -762,10 +746,52 @@ sql_update_jan2014(Client c)
pos += snprintf(buf + pos, bufsize - pos, "create aggregate
quantile(val DATE, q DOUBLE) returns DATE external name
\"aggr\".\"quantile\";\n");
pos += snprintf(buf + pos, bufsize - pos, "create aggregate
quantile(val TIME, q DOUBLE) returns TIME external name
\"aggr\".\"quantile\";\n");
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
