Changeset: aecea48fd3aa for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=aecea48fd3aa
Modified Files:
sql/backends/monet5/UDF/Tests/udf-fuse.stable.out
sql/backends/monet5/UDF/Tests/udf-reverse.stable.out
sql/jdbc/tests/Tests/Test_Dobjects.stable.out
sql/jdbc/tests/Tests/Test_JdbcClient.stable.out
sql/scripts/15_querylog.sql
sql/test/BugDay_2005-10-06_2.8/Tests/MapiClient-dump.SF-905851.stable.out
sql/test/BugTracker/Tests/cardinality_violation.SF-1240701.stable.err
sql/test/BugTracker/Tests/multi-column-constraint.SF-1964587.stable.out
sql/test/Tests/systemfunctions.stable.out
Branch: default
Log Message:
approving tests after querlog / sysmon changes
also made querylog_history a system view.
diffs (210 lines):
diff --git a/sql/backends/monet5/UDF/Tests/udf-fuse.stable.out
b/sql/backends/monet5/UDF/Tests/udf-fuse.stable.out
--- a/sql/backends/monet5/UDF/Tests/udf-fuse.stable.out
+++ b/sql/backends/monet5/UDF/Tests/udf-fuse.stable.out
@@ -44,7 +44,7 @@ end s0_1;
% .explain # table_name
% mal # name
% clob # type
-% 67 # length
+% 78 # length
function user.s1_1{autoCommit=true}(A0:sht,A1:sht):void;
X_4 := udf.fuse(A0,A1);
sql.exportValue(1,".","fuse_single_value","int",32,0,6,X_4,"");
@@ -59,7 +59,7 @@ end s1_1;
% .explain # table_name
% mal # name
% clob # type
-% 70 # length
+% 84 # length
function user.s2_1{autoCommit=true}(A0:int,A1:int):void;
X_4 := udf.fuse(A0,A1);
sql.exportValue(1,".","fuse_single_value","bigint",64,0,6,X_4,"");
@@ -92,7 +92,7 @@ end s2_1;
% .explain # table_name
% mal # name
% clob # type
-% 60 # length
+% 86 # length
function user.s5_1{autoCommit=true}():void;
X_2 := sql.mvc();
X_3:bat[:oid,:oid] := sql.tid(X_2,"sys","udf_fuse");
@@ -116,7 +116,7 @@ end s5_1;
% .explain # table_name
% mal # name
% clob # type
-% 57 # length
+% 86 # length
function user.s6_1{autoCommit=true}():void;
X_2 := sql.mvc();
X_3:bat[:oid,:oid] := sql.tid(X_2,"sys","udf_fuse");
@@ -140,7 +140,7 @@ end s6_1;
% .explain # table_name
% mal # name
% clob # type
-% 58 # length
+% 86 # length
function user.s7_1{autoCommit=true}():void;
X_2 := sql.mvc();
X_3:bat[:oid,:oid] := sql.tid(X_2,"sys","udf_fuse");
diff --git a/sql/backends/monet5/UDF/Tests/udf-reverse.stable.out
b/sql/backends/monet5/UDF/Tests/udf-reverse.stable.out
--- a/sql/backends/monet5/UDF/Tests/udf-reverse.stable.out
+++ b/sql/backends/monet5/UDF/Tests/udf-reverse.stable.out
@@ -48,7 +48,7 @@ Ready.
% .explain # table_name
% mal # name
% clob # type
-% 70 # length
+% 85 # length
function user.s0_1{autoCommit=true}(A0:str):void;
X_3 := udf.reverse(A0);
sql.exportValue(1,".","reverse_single_value","clob",0,0,4,X_3,"");
@@ -81,7 +81,7 @@ end s0_1;
% .explain # table_name
% mal # name
% clob # type
-% 60 # length
+% 91 # length
function user.s3_1{autoCommit=true}():void;
X_2 := sql.mvc();
X_3:bat[:oid,:oid] := sql.tid(X_2,"sys","udf_reverse");
diff --git a/sql/jdbc/tests/Tests/Test_Dobjects.stable.out
b/sql/jdbc/tests/Tests/Test_Dobjects.stable.out
--- a/sql/jdbc/tests/Tests/Test_Dobjects.stable.out
+++ b/sql/jdbc/tests/Tests/Test_Dobjects.stable.out
@@ -54,7 +54,6 @@ TABLE_TYPE
mTests_sql_jdbc_tests
Resultset with 3 columns
TABLE_SCHEM TABLE_CATALOG TABLE_CAT
-querylog mTests_sql_jdbc_tests mTests_sql_jdbc_tests
sys mTests_sql_jdbc_tests mTests_sql_jdbc_tests
tmp mTests_sql_jdbc_tests mTests_sql_jdbc_tests
Resultset with 3 columns
@@ -88,6 +87,10 @@ mTests_sql_jdbc_tests tmp keys SYSTEM TA
mTests_sql_jdbc_tests tmp objects SYSTEM TABLE null null null
null rowid SYSTEM
mTests_sql_jdbc_tests tmp triggers SYSTEM TABLE null null
null null rowid SYSTEM
mTests_sql_jdbc_tests sys columns SYSTEM VIEW SELECT * FROM (SELECT
p.* FROM "sys"."_columns" AS p UNION ALL SELECT t.* FROM "tmp"."_columns" AS t)
AS columns; null null null rowid SYSTEM
+mTests_sql_jdbc_tests sys querylog_history SYSTEM VIEW 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; null null null rowid
SYSTEM
mTests_sql_jdbc_tests sys tables SYSTEM VIEW 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;
null null null rowid SYSTEM
mTests_sql_jdbc_tests sys tablestoragemodel SYSTEM VIEW -- A
summary of the table storage requirement is is available as a table view.
-- The auxillary column denotes the maximum space if all non-sorted columns
@@ -100,10 +103,6 @@ as select "schema","table",max(count) as
sum(case when sorted = false then 8 * count else 0 end) as auxillary
from sys.storagemodel() group by "schema","table"; null null null
rowid SYSTEM
mTests_sql_jdbc_tests sys users SYSTEM VIEW SELECT u."name" AS
"name", ui."fullname", ui."default_schema" FROM db_users() AS u LEFT JOIN
"sys"."db_user_info" AS ui ON u."name" = ui."name" ; null null null
rowid SYSTEM
-mTests_sql_jdbc_tests querylog history VIEW create view
querylog.history as
-select qd.*, ql."start",ql."stop", ql.arguments, ql.tuples, ql.run, ql.ship,
ql.cpu, ql.space, ql.io
-from querylog.catalog() qd, querylog.calls() ql
-where qd.id = ql.id and qd.owner = user; null null null rowid
SYSTEM
# 09:57:01 >
# 09:57:01 > "Done."
diff --git a/sql/jdbc/tests/Tests/Test_JdbcClient.stable.out
b/sql/jdbc/tests/Tests/Test_JdbcClient.stable.out
--- a/sql/jdbc/tests/Tests/Test_JdbcClient.stable.out
+++ b/sql/jdbc/tests/Tests/Test_JdbcClient.stable.out
@@ -179,14 +179,6 @@ CREATE INDEX "triples_subject_object_idx
CREATE INDEX "triples_subject_predicate_idx" ON "triples" ("subject",
"predicate");
-CREATE SCHEMA "querylog";
-
-SET SCHEMA "querylog";
-
-CREATE VIEW "history" AS select qd.*, ql."start",ql."stop", ql.arguments,
ql.tuples, ql.run, ql.ship, ql.cpu, ql.space, ql.io
-from querylog.catalog() qd, querylog.calls() ql
-where qd.id = ql.id and qd.owner = user;
-
COMMIT;
# 20:01:25 >
diff --git a/sql/scripts/15_querylog.sql b/sql/scripts/15_querylog.sql
--- a/sql/scripts/15_querylog.sql
+++ b/sql/scripts/15_querylog.sql
@@ -64,6 +64,10 @@ 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 = 'querylog_history'
+ and schema_id = (select id from sys.schemas where name = 'sys');
-- reset history for a particular user
create procedure sys.querylog_reset()
diff --git
a/sql/test/BugDay_2005-10-06_2.8/Tests/MapiClient-dump.SF-905851.stable.out
b/sql/test/BugDay_2005-10-06_2.8/Tests/MapiClient-dump.SF-905851.stable.out
--- a/sql/test/BugDay_2005-10-06_2.8/Tests/MapiClient-dump.SF-905851.stable.out
+++ b/sql/test/BugDay_2005-10-06_2.8/Tests/MapiClient-dump.SF-905851.stable.out
@@ -113,12 +113,6 @@ Ready.
# 14:58:14 >
START TRANSACTION;
-CREATE SCHEMA "querylog" AUTHORIZATION "monetdb";
-SET SCHEMA "querylog";
-create view querylog.history as
-select qd.*, ql."start",ql."stop", ql.arguments, ql.tuples, ql.run, ql.ship,
ql.cpu, ql.space, ql.io
-from querylog.catalog() qd, querylog.calls() ql
-where qd.id = ql.id and qd.owner = user;
SET SCHEMA "sys";
CREATE TABLE "sys"."allnewtriples" (
"id" INTEGER NOT NULL,
diff --git
a/sql/test/BugTracker/Tests/cardinality_violation.SF-1240701.stable.err
b/sql/test/BugTracker/Tests/cardinality_violation.SF-1240701.stable.err
--- a/sql/test/BugTracker/Tests/cardinality_violation.SF-1240701.stable.err
+++ b/sql/test/BugTracker/Tests/cardinality_violation.SF-1240701.stable.err
@@ -80,12 +80,12 @@ stderr of test 'cardinality_violation.SF
# 21:00:43 > mclient -lsql -umonetdb -Pmonetdb --host=alf --port=38808
# 21:00:43 >
-MAPI = (monetdb) /var/tmp/mtest-29744/.s.monetdb.36963
+MAPI = (monetdb) /var/tmp/mtest-3806/.s.monetdb.38077
QUERY = select * from env() as env where name = ( select 'prefix' from env()
as env );
ERROR = !cardinality violation (21>1)
-MAPI = (monetdb) /var/tmp/mtest-29744/.s.monetdb.36963
+MAPI = (monetdb) /var/tmp/mtest-3806/.s.monetdb.38077
QUERY = select * from columns where name = (select columns.name from _tables,
columns where _tables.id = columns.table_id);
-ERROR = !cardinality violation (328>1)
+ERROR = !cardinality violation (336>1)
# 21:00:43 >
diff --git
a/sql/test/BugTracker/Tests/multi-column-constraint.SF-1964587.stable.out
b/sql/test/BugTracker/Tests/multi-column-constraint.SF-1964587.stable.out
--- a/sql/test/BugTracker/Tests/multi-column-constraint.SF-1964587.stable.out
+++ b/sql/test/BugTracker/Tests/multi-column-constraint.SF-1964587.stable.out
@@ -26,12 +26,6 @@ Ready.
# 16:17:36 >
START TRANSACTION;
-CREATE SCHEMA "querylog" AUTHORIZATION "monetdb";
-SET SCHEMA "querylog";
-create view querylog.history as
-select qd.*, ql."start",ql."stop", ql.arguments, ql.tuples, ql.run, ql.ship,
ql.cpu, ql.space, ql.io
-from querylog.catalog() qd, querylog.calls() ql
-where qd.id = ql.id and qd.owner = user;
SET SCHEMA "sys";
CREATE TABLE "sys"."test_property" (
"subject" INTEGER,
diff --git a/sql/test/Tests/systemfunctions.stable.out
b/sql/test/Tests/systemfunctions.stable.out
--- a/sql/test/Tests/systemfunctions.stable.out
+++ b/sql/test/Tests/systemfunctions.stable.out
@@ -49,10 +49,7 @@ Ready.
% sys.s, sys.f, sys.a, sys.a, sys. # table_name
% schema, function, argno, argtype, definition # name
% varchar, varchar, int, varchar, varchar # type
-% 8, 35, 1, 15, 1582 # length
-[ "querylog", "calls", 0, "table", "-- Each query call is
stored in the table calls\n-- At regular intervals the query history table
should be cleaned.\n-- This can be done manually on the SQL console, or be
integrated\n-- in the keepQuery and keepCall upon need.\n-- The parameters are
geared at understanding the resource claims\n-- They reflect the effect of the
total workload mix during execution.\n-- The 'cpu' gives the average cpu load
percentage over all cores on the \n-- server during execution phase. \n--
increasing cpu load indicates better use of multi-cores.\n-- The 'io' indicate
IOs during complete query run.\n-- The 'space' is the total amount of
intermediates created in MB.\n-- Reducing the space component improves
performance/\n-- All timing in usec and all storage in bytes.\n\ncreate
function querylog.calls()\nreturns table(\n\tid oid,\t\t\t\t -- references
query plan\n\t\"start\" timestamp,\t-- time the statement was
started\n\t\"stop\" timestamp,\t-- time th
e statement was completely finished\n\targuments string,\t-- actual call
structure\n\ttuples wrd,\t\t\t-- number of tuples in the result set\n\trun
bigint,\t\t-- time spent (in usec) until the result export\n\tship
bigint,\t\t-- time spent (in usec) to ship the result set\n\tcpu int, \t\t--
average cpu load percentage during execution\n\tio int,\t\t\t-- percentage time
waiting for IO to finish \n\tspace bigint\t\t-- total storage size of
intermediates created (in MB)\n)\nexternal name sql.querylog_calls;" ]
-[ "querylog", "catalog", 0, "table", "create function
querylog.catalog()\nreturns table(\n\tid oid,\n\towner string,\n\tdefined
timestamp,\n\tquery string,\n\tpipe string,\n\tmal int,\t\t\t-- size of MAL
plan\n\toptimize bigint \t-- time in usec\n)\nexternal name
sql.querylog_catalog;" ]
-[ "querylog", "init", 0, "smallint", "create procedure
querylog.init(threshold smallint)\nexternal name sql.querylog_init_threshold;"
]
+% 3, 35, 1, 15, 1582 # length
[ "sys", "Intersect", 0, "boolean", "create function
\"Intersect\"(a geometry, b geometry) returns boolean external name
geom.\"Intersect\";" ]
[ "sys", "Intersect", 1, "geometry", "" ]
[ "sys", "Intersect", 2, "geometry", "" ]
@@ -326,6 +323,9 @@ Ready.
[ "sys", "polygonfromtext", 2, "smallint", "" ]
[ "sys", "querycache", 0, "table", "-- SQL QUERY CACHE\n--
The SQL query cache returns a table with the query plans kept\n\ncreate
function querycache() \n\treturns table (query string, count int) \n\texternal
name sql.dump_cache;" ]
[ "sys", "querylog", 0, "clob", "-- Trace the SQL input\ncreate
procedure querylog(filename string) \n\texternal name sql.logfile;" ]
+[ "sys", "querylog_calls", 0, "table", "-- Each query
call is stored in the table calls\n-- At regular intervals the query history
table should be cleaned.\n-- This can be done manually on the SQL console, or
be integrated\n-- in the keepQuery and keepCall upon need.\n-- The parameters
are geared at understanding the resource claims\n-- They reflect the effect of
the total workload mix during execution.\n-- The 'cpu' gives the average cpu
load percentage over all cores on the \n-- server during execution phase. \n--
increasing cpu load indicates better use of multi-cores.\n-- The 'io' indicate
IOs during complete query run.\n-- The 'space' is the total amount of
intermediates created in MB.\n-- Reducing the space component improves
performance/\n-- All timing in usec and all storage in bytes.\n\ncreate
function sys.querylog_calls()\nreturns table(\n\tid oid,\t\t\t\t -- references
query plan\n\t\"start\" timestamp,\t-- time the statement was
started\n\t\"stop\" timestamp,\t--
time the statement was completely finished\n\targuments string,\t-- actual
call structure\n\ttuples wrd,\t\t\t-- number of tuples in the result set\n\trun
bigint,\t\t-- time spent (in usec) until the result export\n\tship
bigint,\t\t-- time spent (in usec) to ship the result set\n\tcpu int, \t\t--
average cpu load percentage during execution\n\tio int,\t\t\t-- percentage time
waiting for IO to finish \n\tspace bigint\t\t-- total storage size of
intermediates created (in MB)\n)\nexternal name sql.querylog_calls;" ]
+[ "sys", "querylog_catalog", 0, "table", "-- The
contents of this file are subject to the MonetDB Public License\n-- Version 1.1
(the \"License\"); you may not use this file except in\n-- compliance with the
License. You may obtain a copy of the License at\n--
http://www.monetdb.org/Legal/MonetDBLicense\n--\n-- Software distributed under
the License is distributed on an \"AS IS\"\n-- basis, WITHOUT WARRANTY OF ANY
KIND, either express or implied. See the\n-- License for the specific language
governing rights and limitations\n-- under the License.\n--\n-- The Original
Code is the MonetDB Database System.\n--\n-- The Initial Developer of the
Original Code is CWI.\n-- Copyright August 2008-2013 MonetDB B.V.\n-- All
Rights Reserved.\n\n-- QUERY HISTORY\n-- The query history mechanism of
MonetDB/SQL relies on a few hooks.\n-- The most important one is a global
system variable which controls\n-- monitoring of all sessions. \n\ncreate
function sys.querylog_catalog()\nreturns table
(\n\tid oid,\n\towner string,\n\tdefined timestamp,\n\tquery string,\n\tpipe
string,\n\tmal int,\t\t\t-- size of MAL plan\n\toptimize bigint \t-- time in
usec\n)\nexternal name sql.querylog_catalog;" ]
+[ "sys", "querylog_init", 0, "smallint", "create
procedure sys.querylog_init(threshold smallint)\nexternal name
sql.querylog_init_threshold;" ]
[ "sys", "queue", 0, "table", "-- The contents of
this file are subject to the MonetDB Public License\n-- Version 1.1 (the
\"License\"); you may not use this file except in\n-- compliance with the
License. You may obtain a copy of the License at\n--
http://www.monetdb.org/Legal/MonetDBLicense\n--\n-- Software distributed under
the License is distributed on an \"AS IS\"\n-- basis, WITHOUT WARRANTY OF ANY
KIND, either express or implied. See the\n-- License for the specific language
governing rights and limitations\n-- under the License.\n--\n-- The Original
Code is the MonetDB Database System.\n--\n-- The Initial Developer of the
Original Code is CWI.\n-- Copyright August 2008-2013 MonetDB B.V.\n-- All
Rights Reserved.\n\n-- System monitoring\n\n-- show status of all active SQL
queries.\ncreate function sys.queue()\nreturns table(\n\tqtag
bigint,\n\t\"user\" string,\n\tstarted timestamp,\n\testimate
timestamp,\n\tprogress int,\n\tstatus string,\n\ttag oid,\n\tquery string\n)\
nexternal name sql.sysmon_queue;" ]
[ "sys", "radians", 0, "double", "create function
radians(d double) \nreturns double\n\treturn d*pi()/180;" ]
[ "sys", "radians", 1, "double", "" ]
_______________________________________________
checkin-list mailing list
[email protected]
http://mail.monetdb.org/mailman/listinfo/checkin-list
