Changeset: a1afb5cdfd97 for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=a1afb5cdfd97
Modified Files:
sql/backends/monet5/sql_scenario.c
sql/scripts/40_json.sql
sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out
sql/test/testdb-upgrade/Tests/upgrade.stable.err
sql/test/testdb-upgrade/Tests/upgrade.stable.out
Branch: default
Log Message:
Implemented upgrade from Feb2013 branch to current branch.
diffs (truncated from 715 to 300 lines):
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
@@ -642,6 +642,91 @@ sql_update_feb2013_sp3(Client c)
return err; /* usually MAL_SUCCEED */
}
+static str
+sql_update_oct2013(Client c)
+{
+ char *buf = GDKmalloc(10240), *err = NULL;
+ size_t bufsize = 10240, pos = 0;
+ char *fullname;
+ FILE *fp1 = NULL, *fp2 = NULL, *fp3 = 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");
+ pos += snprintf(buf+pos, bufsize-pos, "drop procedure
sys.keepQuery;\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);
+ }
+
+ /* new file 26_sysmon.sql */
+ if (fp2) {
+ pos += fread(buf+pos, 1, bufsize-pos, fp2);
+ fclose(fp2);
+ }
+
+ /* 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_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");
+
+ if (schema) {
+ pos += snprintf(buf+pos, bufsize-pos, "set schema \"%s\";\n",
schema);
+ free(schema);
+ }
+
+ assert(pos < 10240);
+
+ printf("Running database upgrade commands:\n%s\n", buf);
+ err = SQLstatementIntern(c, &buf, "update", 1, 0);
+ GDKfree(buf);
+ return err; /* usually MAL_SUCCEED */
+}
str
SQLinitClient(Client c)
@@ -848,6 +933,14 @@ SQLinitClient(Client c)
GDKfree(err);
}
}
+ /* if function sys.querylog_catalog() does not exist, we
+ * need to update */
+ if (!sql_bind_func(m->sa, mvc_bind_schema(m,"sys"),
"querylog_catalog", NULL, NULL, F_FUNC )) {
+ if ((err = sql_update_oct2013(c)) != NULL) {
+ fprintf(stderr, "!%s\n", err);
+ GDKfree(err);
+ }
+ }
}
fflush(stdout);
fflush(stderr);
diff --git a/sql/scripts/40_json.sql b/sql/scripts/40_json.sql
--- a/sql/scripts/40_json.sql
+++ b/sql/scripts/40_json.sql
@@ -21,28 +21,28 @@
create type json external name json;
-- access the top level key by name, return its value
-create function json_filter(js json, name string)
+create function sys.json_filter(js json, name string)
returns json external name json.filter;
-create function json_filter_all(js json, name string)
+create function sys.json_filter_all(js json, name string)
returns json external name json.filterall;
-- a simple path extractor
-create function json_path(js json, e string)
+create function sys.json_path(js json, e string)
returns json external name json.path;
-- test string for JSON compliancy
-create function json_isvalid(js string)
+create function sys.json_isvalid(js string)
returns bool external name json.isvalid;
-create function json_isvalidobject(js string)
+create function sys.json_isvalidobject(js string)
returns bool external name json.isvalidobject;
-create function json_isvalidarray(js string)
+create function sys.json_isvalidarray(js string)
returns bool external name json.isvalidarray;
-- return the number of primary components
-create function json_length(js json)
+create function sys.json_length(js json)
returns integer external name json.length;
-- The remainder awaits the implementation of
@@ -50,26 +50,26 @@ returns integer external name json.lengt
-- unnesting the JSON structure
--- create function json_unnest(js json)
+-- create function sys.json_unnest(js json)
-- returns table( id integer, k string, v string) external name json.unnest;
--- create function json_unnest(js json)
+-- create function sys.json_unnest(js json)
-- returns table( k string, v string) external name json.unnest;
--- create function json_unnest(js json)
+-- create function sys.json_unnest(js json)
-- returns table( v string) external name json.unnest;
--- create function json_nest table( id integer, k string, v string)
+-- create function sys.json_nest table( id integer, k string, v string)
-- returns json external name json.nest;
--- create function json_names(js json)
+-- create function sys.json_names(js json)
-- returns table ( nme string) external name json.names;
--- create function json_values(js json)
+-- create function sys.json_values(js json)
-- returns table ( val string) external name json."values";
-- rendering functions
--- create function json_object(*)
+-- create function sys.json_object(*)
-- returns json external name json.objectrender;
--- create function json_array(*)
+-- create function sys.json_array(*)
-- returns json external name json.arrayrender;
diff --git a/sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out
b/sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out
--- a/sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out
+++ b/sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out
@@ -28,9 +28,247 @@ create aggregate sys.median(val WRD) ret
create aggregate sys.corr(e1 WRD, e2 WRD) returns WRD external name
"aggr"."corr";
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 = 3 and f.schema_id = s.id and s.name = 'sys');
-# 15:38:36 >
-# 15:38:36 > "/usr/bin/python2" "upgrade.SQL.py" "upgrade"
-# 15:38:36 >
+Running database upgrade commands:
+set schema "sys";
+create view sys.tracelog as select * from sys.tracelog();
+drop function sys.clients;
+create view sys.optimizers as select * from sys.optimizers();
+create view sys.environment as select * from sys.environment();
+create view sys.storage as select * from sys.storage();
+create view sys.storagemodel as select * from sys.storagemodel();
+drop procedure sys.resetHistory;
+drop procedure sys.keepCall;
+drop procedure sys.keepQuery;
+drop view sys.queryLog;
+drop table sys.callHistory;
+drop table sys.queryHistory;
+-- The contents of this file are subject to the MonetDB Public License
+-- Version 1.1 (the "License"); you may not use this file except in
+-- compliance with the License. You may obtain a copy of the License at
+-- http://www.monetdb.org/Legal/MonetDBLicense
+--
+-- Software distributed under the License is distributed on an "AS IS"
+-- basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
+-- License for the specific language governing rights and limitations
+-- under the License.
+--
+-- The Original Code is the MonetDB Database System.
+--
+-- The Initial Developer of the Original Code is CWI.
+-- Copyright August 2008-2013 MonetDB B.V.
+-- All Rights Reserved.
+
+-- QUERY HISTORY
+-- The query history mechanism of MonetDB/SQL relies on a few hooks.
+-- The most important one is a global system variable which controls
+-- monitoring of all sessions.
+
+create function sys.querylog_catalog()
+returns table(
+ id oid,
+ owner string,
+ defined timestamp,
+ query string,
+ pipe string,
+ mal int, -- size of MAL plan
+ optimize bigint -- time in usec
+)
+external name sql.querylog_catalog;
+
+-- Each query call is stored in the table calls
+-- At regular intervals the query history table should be cleaned.
+-- This can be done manually on the SQL console, or be integrated
+-- in the keepQuery and keepCall upon need.
+-- The parameters are geared at understanding the resource claims
+-- They reflect the effect of the total workload mix during execution.
+-- The 'cpu' gives the average cpu load percentage over all cores on the
+-- server during execution phase.
+-- increasing cpu load indicates better use of multi-cores.
+-- The 'io' indicate IOs during complete query run.
+-- The 'space' is the total amount of intermediates created in MB.
+-- Reducing the space component improves performance/
+-- All timing in usec and all storage in bytes.
+
+create function sys.querylog_calls()
+returns table(
+ id oid, -- references query plan
+ "start" timestamp, -- time the statement was started
+ "stop" timestamp, -- time the statement was completely finished
+ arguments string, -- actual call structure
+ tuples wrd, -- number of tuples in the result set
+ run bigint, -- time spent (in usec) until the result export
+ ship bigint, -- time spent (in usec) to ship the result set
+ cpu int, -- average cpu load percentage during execution
+ io int, -- percentage time waiting for IO to finish
+ space bigint -- total storage size of intermediates created
(in MB)
+)
+external name sql.querylog_calls;
+
+-- create table views for convenience
+create view sys.querylog_catalog as select * from sys.querylog_catalog();
+create view sys.querylog_calls as select * from sys.querylog_calls();
+create view sys.querylog_history as
+select qd.*, ql."start",ql."stop", ql.arguments, ql.tuples, ql.run, ql.ship,
ql.cpu, ql.space, ql.io
+from sys.querylog_catalog() qd, sys.querylog_calls() ql
+where qd.id = ql.id and qd.owner = user;
+
+update sys._tables
+ set system = true
+ where name in ('querylog_history', 'querylog_calls', 'querylog_catalog')
+ and schema_id = (select id from sys.schemas where name = 'sys');
+
+-- reset history for a particular user
+create procedure sys.querylog_empty()
+external name sql.querylog_empty;
+
+-- manipulate the query logger
+create procedure sys.querylog_enable()
+external name sql.querylog_enable;
+create procedure sys.querylog_enable(threshold smallint)
+external name sql.querylog_enable_threshold;
+create procedure sys.querylog_disable()
+external name sql.querylog_disable;
+-- The contents of this file are subject to the MonetDB Public License
+-- Version 1.1 (the "License"); you may not use this file except in
+-- compliance with the License. You may obtain a copy of the License at
+-- http://www.monetdb.org/Legal/MonetDBLicense
+--
+-- Software distributed under the License is distributed on an "AS IS"
_______________________________________________
checkin-list mailing list
[email protected]
http://mail.monetdb.org/mailman/listinfo/checkin-list
