Changeset: 475a02095846 for MonetDB URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=475a02095846 Modified Files: sql/test/Tests/systemfunctions.stable.out sql/test/Tests/systemfunctions.stable.out.int128 sql/test/Tests/systemfunctions_bam.stable.out sql/test/Tests/systemfunctions_bam.stable.out.int128 sql/test/Tests/systemfunctions_gsl.stable.out sql/test/Tests/systemfunctions_gsl.stable.out.int128 sql/test/Tests/systemfunctions_nogeom.stable.out sql/test/Tests/systemfunctions_nogeom.stable.out.int128 Branch: default Log Message:
Merge with Oct2014 branch. diffs (truncated from 879 to 300 lines): 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 @@ -58,7 +58,7 @@ Ready. % sys.L3, sys.L3, sys.L3, sys.L3, sys.L3 # table_name % schema, function, argno, argtype, definition # name % varchar, varchar, int, varchar, varchar # type -% 4, 35, 2, 15, 1662 # length +% 4, 35, 2, 15, 1681 # length [ "json", "filter", 0, "json", "-- access the top level key by name, return its value\ncreate function json.filter(js json, pathexpr string)\nreturns json external name json.filter;" ] [ "json", "filter", 1, "json", "" ] [ "json", "filter", 2, "clob", "" ] @@ -158,10 +158,6 @@ Ready. [ "sys", "buffer", 0, "geometry", "create function buffer(a geometry, distance float) returns geometry external name geom.\"Buffer\";" ] [ "sys", "buffer", 1, "geometry", "" ] [ "sys", "buffer", 2, "double", "" ] -[ "sys", "cluster1", 0, "clob", "-- 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-- Portions created by CWI are Copyright (C) 1997-July 2008 CWI.\n-- Copyright August 2008-2015 MonetDB B.V.\n-- All Rights Reserved.\n\n-- Clustering a relational table should be done with care.\n-- For, the oid's are used in join-indices.\n\n-- Clustering of tables may improve IO performance\n-- The foreign key constraints should b e dropped before\n-- and re-established after the cluster operation.\n\ncreate procedure cluster1(sys string, tab string)\n\texternal name sql.cluster1;" ] -[ "sys", "cluster1", 1, "clob", "" ] -[ "sys", "cluster2", 0, "clob", "create procedure cluster2(sys string, tab string)\n\texternal name sql.cluster2;" ] -[ "sys", "cluster2", 1, "clob", "" ] [ "sys", "columnsize", 0, "bigint", "-- The predicted storage footprint of the complete database\n-- determines the amount of diskspace needed for persistent storage\n-- and the upperbound when all possible index structures are created.\n-- The storage requirement for foreign key joins is split amongst the participants.\n\ncreate function sys.columnsize(nme string, i bigint, d bigint)\nreturns bigint\nbegin\n\tcase\n\twhen nme = 'boolean' then return i;\n\twhen nme = 'char' then return 2*i;\n\twhen nme = 'smallint' then return 2 * i;\n\twhen nme = 'int'\t then return 4 * i;\n\twhen nme = 'bigint'\t then return 8 * i;\n\twhen nme = 'hugeint'\t then return 16 * i;\n\twhen nme = 'timestamp' then return 8 * i;\n\twhen nme = 'varchar' then\n\t\tcase\n\t\twhen cast(d as bigint) << 8 then return i;\n\t\twhen cast(d as bigint) << 16 then return 2 * i;\n\t\twhen cast(d as bigint) << 32 then return 4 * i;\n\t\telse return 8 * i;\n\t\tend case;\n\telse return 8 * i;\n\tend case;\nend;" ] [ "sys", "columnsize", 1, "clob", "" ] [ "sys", "columnsize", 2, "bigint", "" ] @@ -399,6 +395,12 @@ Ready. [ "sys", "isempty", 1, "geometry", "" ] [ "sys", "issimple", 0, "boolean", "create function issimple(g geometry) returns boolean external name geom.\"IsSimple\";" ] [ "sys", "issimple", 1, "geometry", "" ] +[ "sys", "left_shift", 0, "boolean", "create function \"left_shift\"(i1 inet, i2 inet) returns boolean\n\texternal name inet.\"<<\";" ] +[ "sys", "left_shift", 1, "inet", "" ] +[ "sys", "left_shift", 2, "inet", "" ] +[ "sys", "left_shift_assign", 0, "boolean", "create function \"left_shift_assign\"(i1 inet, i2 inet) returns boolean\n\texternal name inet.\"<<=\";" ] +[ "sys", "left_shift_assign", 1, "inet", "" ] +[ "sys", "left_shift_assign", 2, "inet", "" ] [ "sys", "length", 0, "double", "create function length(g geometry) returns float external name geom.\"Length\";" ] [ "sys", "length", 1, "geometry", "" ] [ "sys", "like", 0, "clob", "-- 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-- Portions created by CWI are Copyright (C) 1997-July 2008 CWI.\n-- Copyright August 2008-2015 MonetDB B.V.\n-- All Rights Reserved.\n\ncreate filter function \"like\"(val string, pat string, esc string) external name algebra.\"like\";" ] @@ -502,6 +504,9 @@ Ready. [ "sys", "polygonfromtext", 0, "polygon", "-- alias\ncreate function polygonfromtext(wkt string, srid smallint) returns polygon external name geom.\"PolyFromText\";" ] [ "sys", "polygonfromtext", 1, "clob", "" ] [ "sys", "polygonfromtext", 2, "smallint", "" ] +[ "sys", "profiler_openstream", 0, "clob", "create procedure profiler_openstream(host string, port int) external name profiler.\"openStream\";" ] +[ "sys", "profiler_openstream", 1, "int", "" ] +[ "sys", "profiler_stethoscope", 0, "int", "create procedure profiler_stethoscope(ticks int) external name profiler.stethoscope;" ] [ "sys", "quantile", 0, "tinyint", "create aggregate quantile(val tinyint, q double) returns tinyint\n \texternal name \"aggr\".\"quantile\";" ] [ "sys", "quantile", 1, "tinyint", "" ] [ "sys", "quantile", 2, "double", "" ] @@ -538,7 +543,7 @@ Ready. [ "sys", "querycache", 0, "clob", "-- SQL QUERY CACHE\n-- The SQL query cache returns a table with the query plans kept\n\ncreate function sys.querycache() \n\treturns table (query string, count int) \n\texternal name sql.dump_cache;" ] [ "sys", "querycache", 1, "int", "" ] [ "sys", "querylog", 0, "clob", "-- Trace the SQL input\ncreate procedure sys.querylog(filename string) \n\texternal name sql.logfile;" ] -[ "sys", "querylog_calls", 0, "oid", "-- 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_calls", 0, "oid", "-- 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)\nexternal name sql.querylog_calls;" ] [ "sys", "querylog_calls", 1, "timestamp", "" ] [ "sys", "querylog_calls", 2, "timestamp", "" ] [ "sys", "querylog_calls", 3, "clob", "" ] @@ -547,14 +552,14 @@ Ready. [ "sys", "querylog_calls", 6, "bigint", "" ] [ "sys", "querylog_calls", 7, "int", "" ] [ "sys", "querylog_calls", 8, "int", "" ] -[ "sys", "querylog_calls", 9, "bigint", "" ] -[ "sys", "querylog_catalog", 0, "oid", "-- 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-- Portions created by CWI are Copyright (C) 1997-July 2008 CWI.\n-- Copyright August 2008-2015 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_catalog", 0, "oid", "-- 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-- Portions created by CWI are Copyright (C) 1997-July 2008 CWI.\n-- Copyright August 2008-2015 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\t\"plan\" string,\t\t-- Name of MAL plan\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_catalog", 1, "clob", "" ] [ "sys", "querylog_catalog", 2, "timestamp", "" ] [ "sys", "querylog_catalog", 3, "clob", "" ] [ "sys", "querylog_catalog", 4, "clob", "" ] -[ "sys", "querylog_catalog", 5, "int", "" ] -[ "sys", "querylog_catalog", 6, "bigint", "" ] +[ "sys", "querylog_catalog", 5, "clob", "" ] +[ "sys", "querylog_catalog", 6, "int", "" ] +[ "sys", "querylog_catalog", 7, "bigint", "" ] [ "sys", "querylog_enable", 0, "smallint", "create procedure sys.querylog_enable(threshold smallint)\nexternal name sql.querylog_enable_threshold;" ] [ "sys", "queue", 0, "bigint", "-- 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-- Portions created by CWI are Copyright (C) 1997-July 2008 CWI.\n-- Copyright August 2008-2015 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\tprog ress int,\n\tstatus string,\n\ttag oid,\n\tquery string\n)\nexternal name sql.sysmon_queue;" ] [ "sys", "queue", 1, "clob", "" ] @@ -576,6 +581,12 @@ Ready. [ "sys", "reuse", 1, "clob", "" ] [ "sys", "reverse", 0, "clob", "-- 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-- Portions created by CWI are Copyright (C) 1997-July 2008 CWI.\n-- Copyright August 2008-2015 MonetDB B.V.\n-- All Rights Reserved.\n\n-- add function signatures to SQL catalog\n\n\n-- Reverse a string\ncreate function reverse(src string)\nreturns string external name udf.reverse;" ] [ "sys", "reverse", 1, "clob", "" ] +[ "sys", "right_shift", 0, "boolean", "create function \"right_shift\"(i1 inet, i2 inet) returns boolean\n\texternal name inet.\">>\";" ] +[ "sys", "right_shift", 1, "inet", "" ] +[ "sys", "right_shift", 2, "inet", "" ] +[ "sys", "right_shift_assign", 0, "boolean", "create function \"right_shift_assign\"(i1 inet, i2 inet) returns boolean\n\texternal name inet.\">>=\";" ] +[ "sys", "right_shift_assign", 1, "inet", "" ] +[ "sys", "right_shift_assign", 2, "inet", "" ] [ "sys", "sessions", 0, "clob", "create function sys.sessions()\nreturns table(\"user\" string, \"login\" timestamp, \"sessiontimeout\" bigint, \"lastcommand\" timestamp, \"querytimeout\" bigint, \"active\" bool)\nexternal name sql.sessions;" ] [ "sys", "sessions", 1, "timestamp", "" ] [ "sys", "sessions", 2, "bigint", "" ] @@ -638,18 +649,19 @@ Ready. [ "sys", "stddev_samp", 1, "timestamp", "" ] [ "sys", "stop", 0, "int", "create procedure sys.stop(tag int)\nexternal name sql.sysmon_stop;" ] [ "sys", "stop", 0, "bigint", "create procedure sys.stop(tag bigint)\nexternal name sql.sysmon_stop;" ] -[ "sys", "storage", 0, "clob", "-- 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-- Portions created by CWI are Copyright (C) 1997-July 2008 CWI.\n-- Copyright August 2008-2015 MonetDB B.V.\n-- All Rights Reserved.\n\n-- Author M.Kersten\n-- This script gives the database administrator insight in the actual\n-- footprint of the persistent tables and the maximum playground used\n-- when indices are introduced upon them.\n-- By chancing the storagemodelinput table directly, the footprint for\n-- yet to be loaded databases can be assessed.\n\n-- The actual storage footprint of an existing database can be\n-- obtained by the table procuding function storage()\n-- It represents the actual state of affairs, i.e. storage on disk\n-- of columns and foreign key indices, and possible temporary hash indices.\n-- For strings we take a sample to determine their average length.\n\ncreate function sys.storage()\nreturns table (\"schema\" string, \"table\" string, \"column\" string, \"type\" string, location string, \"count\" bigint, typewidth int, columnsize bigint, heapsize bigint, hashes bigint, imprints bigint, sorted boolean)\nexternal name sql.storage;" ] +[ "sys", "storage", 0, "clob", "-- 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-- Portions created by CWI are Copyright (C) 1997-July 2008 CWI.\n-- Copyright August 2008-2015 MonetDB B.V.\n-- All Rights Reserved.\n\n-- Author M.Kersten\n-- This script gives the database administrator insight in the actual\n-- footprint of the persistent tables and the maximum playground used\n-- when indices are introduced upon them.\n-- By chancing the storagemodelinput table directly, the footprint for\n-- yet to be loaded databases can be assessed.\n\n-- The actual storage footprint of an existing database can be\n-- obtained by the table procuding function storage()\n-- It represents the actual state of affairs, i.e. storage on disk\n-- of columns and foreign key indices, and possible temporary hash indices.\n-- For strings we take a sample to determine their average length.\n\ncreate function sys.\"storage\"()\nreturns table (\"schema\" string, \"table\" string, \"column\" string, \"type\" string, \"mode\" string, location string, \"count\" bigint, typewidth int, columnsize bigint, heapsize bigint, hashes bigint, imprints bigint, sorted boolean)\nexternal name sql.\"storage\";" ] [ "sys", "storage", 1, "clob", "" ] [ "sys", "storage", 2, "clob", "" ] [ "sys", "storage", 3, "clob", "" ] [ "sys", "storage", 4, "clob", "" ] -[ "sys", "storage", 5, "bigint", "" ] -[ "sys", "storage", 6, "int", "" ] -[ "sys", "storage", 7, "bigint", "" ] +[ "sys", "storage", 5, "clob", "" ] +[ "sys", "storage", 6, "bigint", "" ] +[ "sys", "storage", 7, "int", "" ] [ "sys", "storage", 8, "bigint", "" ] [ "sys", "storage", 9, "bigint", "" ] [ "sys", "storage", 10, "bigint", "" ] -[ "sys", "storage", 11, "boolean", "" ] +[ "sys", "storage", 11, "bigint", "" ] +[ "sys", "storage", 12, "boolean", "" ] [ "sys", "storagemodel", 0, "clob", "create function sys.storagemodel()\nreturns table (\n\t\"schema\" string,\n\t\"table\" string,\n\t\"column\" string,\n\t\"type\" string,\n\t\"count\"\tbigint,\n\tcolumnsize bigint,\n\theapsize bigint,\n\thashes bigint,\n\timprints bigint,\n\tsorted boolean)\nbegin\n\treturn select i.\"schema\", i.\"table\", i.\"column\", i.\"type\", i.\"count\",\n\tcolumnsize(i.\"type\", i.count, i.\"distinct\"),\n\theapsize(i.\"type\", i.\"distinct\", i.\"atomwidth\"),\n\thashsize(i.\"reference\", i.\"count\"),\n\timprintsize(i.\"count\",i.\"type\"),\n\ti.sorted\n\tfrom sys.storagemodelinput i;\nend;" ] [ "sys", "storagemodel", 1, "clob", "" ] [ "sys", "storagemodel", 2, "clob", "" ] @@ -671,18 +683,19 @@ Ready. [ "sys", "touches", 0, "boolean", "create function touches(a geometry, b geometry) returns boolean external name geom.\"Touches\";" ] [ "sys", "touches", 1, "geometry", "" ] [ "sys", "touches", 2, "geometry", "" ] -[ "sys", "tracelog", 0, "int", "-- 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-- Portions created by CWI are Copyright (C) 1997-July 2008 CWI.\n-- Copyright August 2008-2015 MonetDB B.V.\n-- All Rights Reserved.\n\n-- make the offline tracing table available for inspection\ncreate function sys.tracelog() \n\treturns table (\n\t\tevent integer,\t\t-- event counter\n\t\tclk varchar(20), \t-- wallclock, no mtime i n kernel\n\t\tpc varchar(50), \t-- module.function[nr]\n\t\tthread int, \t\t-- thread identifier\n\t\t\"user\" int, \t\t-- user identifier\n\t\tticks bigint, \t\t-- time in microseconds\n\t\treads bigint, \t\t-- number of blocks read\n\t\twrites bigint, \t-- number of blocks written\n\t\trbytes bigint,\t\t-- amount of bytes touched\n\t\twbytes bigint,\t\t-- amount of bytes written\n\t\ttype string,\t\t-- return types\n\t\tstmt string\t\t\t-- actual statement executed\n\t)\n\texternal name sql.dump_trace;" ] +[ "sys", "tracelog", 0, "int", "-- 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-- Portions created by CWI are Copyright (C) 1997-July 2008 CWI.\n-- Copyright August 2008-2015 MonetDB B.V.\n-- All Rights Reserved.\n\n-- make the offline tracing table available for inspection\ncreate function sys.tracelog() \n\treturns table (\n\t\tevent integer,\t\t-- event counter\n\t\tclk varchar(20), \t-- wallclock, no mtime i n kernel\n\t\tpc varchar(50), \t-- module.function[nr]\n\t\tthread int, \t\t-- thread identifier\n\t\tticks bigint, \t\t-- time in microseconds\n\t\trrsmb bigint, \t\t-- resident memory in MB\n\t\tvmmb bigint, \t\t-- virtual size in MB\n\t\treads bigint, \t\t-- number of blocks read\n\t\twrites bigint, \t\t-- number of blocks written\n\t\tminflt bigint, \t\t-- minor page faults\n\t\tmajflt bigint, \t\t-- major page faults\n\t\tnvcsw bigint, \t\t-- non-volantary conext switch\n\t\tstmt string\t\t\t-- actual statement executed\n\t)\n\texternal name sql.dump_trace;" ] [ "sys", "tracelog", 1, "varchar", "" ] [ "sys", "tracelog", 2, "varchar", "" ] [ "sys", "tracelog", 3, "int", "" ] -[ "sys", "tracelog", 4, "int", "" ] +[ "sys", "tracelog", 4, "bigint", "" ] [ "sys", "tracelog", 5, "bigint", "" ] [ "sys", "tracelog", 6, "bigint", "" ] [ "sys", "tracelog", 7, "bigint", "" ] [ "sys", "tracelog", 8, "bigint", "" ] [ "sys", "tracelog", 9, "bigint", "" ] -[ "sys", "tracelog", 10, "clob", "" ] -[ "sys", "tracelog", 11, "clob", "" ] +[ "sys", "tracelog", 10, "bigint", "" ] +[ "sys", "tracelog", 11, "bigint", "" ] +[ "sys", "tracelog", 12, "clob", "" ] [ "sys", "uuid", 0, "uuid", "-- generate a new uuid\ncreate function sys.uuid()\nreturns uuid external name uuid.\"new\";" ] [ "sys", "vacuum", 0, "clob", "create procedure vacuum(sys string, tab string)\n\texternal name sql.vacuum;" ] [ "sys", "vacuum", 1, "clob", "" ] diff --git a/sql/test/Tests/systemfunctions.stable.out.int128 b/sql/test/Tests/systemfunctions.stable.out.int128 --- a/sql/test/Tests/systemfunctions.stable.out.int128 +++ b/sql/test/Tests/systemfunctions.stable.out.int128 @@ -58,7 +58,7 @@ Ready. % sys.L3, sys.L3, sys.L3, sys.L3, sys.L3 # table_name % schema, function, argno, argtype, definition # name % varchar, varchar, int, varchar, varchar # type -% 4, 35, 2, 15, 1662 # length +% 4, 35, 2, 15, 1681 # length [ "json", "filter", 0, "json", "-- access the top level key by name, return its value\ncreate function json.filter(js json, pathexpr string)\nreturns json external name json.filter;" ] [ "json", "filter", 1, "json", "" ] [ "json", "filter", 2, "clob", "" ] @@ -161,10 +161,6 @@ Ready. [ "sys", "buffer", 0, "geometry", "create function buffer(a geometry, distance float) returns geometry external name geom.\"Buffer\";" ] [ "sys", "buffer", 1, "geometry", "" ] [ "sys", "buffer", 2, "double", "" ] -[ "sys", "cluster1", 0, "clob", "-- 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-- Portions created by CWI are Copyright (C) 1997-July 2008 CWI.\n-- Copyright August 2008-2015 MonetDB B.V.\n-- All Rights Reserved.\n\n-- Clustering a relational table should be done with care.\n-- For, the oid's are used in join-indices.\n\n-- Clustering of tables may improve IO performance\n-- The foreign key constraints should b e dropped before\n-- and re-established after the cluster operation.\n\ncreate procedure cluster1(sys string, tab string)\n\texternal name sql.cluster1;" ] -[ "sys", "cluster1", 1, "clob", "" ] -[ "sys", "cluster2", 0, "clob", "create procedure cluster2(sys string, tab string)\n\texternal name sql.cluster2;" ] -[ "sys", "cluster2", 1, "clob", "" ] [ "sys", "columnsize", 0, "bigint", "-- The predicted storage footprint of the complete database\n-- determines the amount of diskspace needed for persistent storage\n-- and the upperbound when all possible index structures are created.\n-- The storage requirement for foreign key joins is split amongst the participants.\n\ncreate function sys.columnsize(nme string, i bigint, d bigint)\nreturns bigint\nbegin\n\tcase\n\twhen nme = 'boolean' then return i;\n\twhen nme = 'char' then return 2*i;\n\twhen nme = 'smallint' then return 2 * i;\n\twhen nme = 'int'\t then return 4 * i;\n\twhen nme = 'bigint'\t then return 8 * i;\n\twhen nme = 'hugeint'\t then return 16 * i;\n\twhen nme = 'timestamp' then return 8 * i;\n\twhen nme = 'varchar' then\n\t\tcase\n\t\twhen cast(d as bigint) << 8 then return i;\n\t\twhen cast(d as bigint) << 16 then return 2 * i;\n\t\twhen cast(d as bigint) << 32 then return 4 * i;\n\t\telse return 8 * i;\n\t\tend case;\n\telse return 8 * i;\n\tend case;\nend;" ] [ "sys", "columnsize", 1, "clob", "" ] [ "sys", "columnsize", 2, "bigint", "" ] @@ -286,8 +282,6 @@ Ready. [ "sys", "equals", 2, "geometry", "" ] [ "sys", "evalalgebra", 0, "clob", "create procedure sys.evalalgebra( ra_stmt string, opt bool)\n\texternal name sql.\"evalAlgebra\";" ] [ "sys", "evalalgebra", 1, "boolean", "" ] -[ "sys", "fitsattach", 0, "clob", "create procedure fitsattach(fname string) external name fits.attach;" ] -[ "sys", "fitsload", 0, "clob", "create procedure fitsload(tname string) external name fits.load;" ] [ "sys", "fuse", 0, "smallint", "-- fuse two (1-byte) tinyint values into one (2-byte) smallint value\ncreate function fuse(one tinyint, two tinyint)\nreturns smallint external name udf.fuse;" ] [ "sys", "fuse", 1, "tinyint", "" ] [ "sys", "fuse", 2, "tinyint", "" ] @@ -417,6 +411,12 @@ Ready. [ "sys", "isempty", 1, "geometry", "" ] [ "sys", "issimple", 0, "boolean", "create function issimple(g geometry) returns boolean external name geom.\"IsSimple\";" ] [ "sys", "issimple", 1, "geometry", "" ] +[ "sys", "left_shift", 0, "boolean", "create function \"left_shift\"(i1 inet, i2 inet) returns boolean\n\texternal name inet.\"<<\";" ] +[ "sys", "left_shift", 1, "inet", "" ] +[ "sys", "left_shift", 2, "inet", "" ] +[ "sys", "left_shift_assign", 0, "boolean", "create function \"left_shift_assign\"(i1 inet, i2 inet) returns boolean\n\texternal name inet.\"<<=\";" ] +[ "sys", "left_shift_assign", 1, "inet", "" ] +[ "sys", "left_shift_assign", 2, "inet", "" ] [ "sys", "length", 0, "double", "create function length(g geometry) returns float external name geom.\"Length\";" ] [ "sys", "length", 1, "geometry", "" ] [ "sys", "like", 0, "clob", "-- 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-- Portions created by CWI are Copyright (C) 1997-July 2008 CWI.\n-- Copyright August 2008-2015 MonetDB B.V.\n-- All Rights Reserved.\n\ncreate filter function \"like\"(val string, pat string, esc string) external name algebra.\"like\";" ] @@ -427,9 +427,6 @@ Ready. [ "sys", "linefromtext", 0, "linestring", "create function linefromtext(wkt string, srid smallint) returns linestring external name geom.\"LineFromText\";" ] [ "sys", "linefromtext", 1, "clob", "" ] [ "sys", "linefromtext", 2, "smallint", "" ] -[ "sys", "listdir", 0, "clob", "\n\ncreate procedure listdir(dirname string) external name fits.listdir;" ] -[ "sys", "listdirpat", 0, "clob", "create procedure listdirpat(dirname string,pat string) external name fits.listdirpattern;" ] -[ "sys", "listdirpat", 1, "clob", "" ] [ "sys", "masklen", 0, "int", "create function \"masklen\" (p inet) returns int\n\texternal name inet.\"masklen\";" ] [ "sys", "masklen", 1, "inet", "" ] [ "sys", "mbr", 0, "mbr", "-- currently we only use mbr instead of\n-- Envelope():Geometry\n-- as that returns Geometry objects, and we prefer the explicit mbr's\n-- minimum bounding rectangle (mbr)\ncreate function mbr (g geometry) returns mbr external name geom.mbr;" ] @@ -525,6 +522,9 @@ Ready. [ "sys", "polygonfromtext", 0, "polygon", "-- alias\ncreate function polygonfromtext(wkt string, srid smallint) returns polygon external name geom.\"PolyFromText\";" ] [ "sys", "polygonfromtext", 1, "clob", "" ] [ "sys", "polygonfromtext", 2, "smallint", "" ] +[ "sys", "profiler_openstream", 0, "clob", "create procedure profiler_openstream(host string, port int) external name profiler.\"openStream\";" ] +[ "sys", "profiler_openstream", 1, "int", "" ] +[ "sys", "profiler_stethoscope", 0, "int", "create procedure profiler_stethoscope(ticks int) external name profiler.stethoscope;" ] [ "sys", "quantile", 0, "tinyint", "create aggregate quantile(val tinyint, q double) returns tinyint\n \texternal name \"aggr\".\"quantile\";" ] [ "sys", "quantile", 1, "tinyint", "" ] [ "sys", "quantile", 2, "double", "" ] @@ -564,7 +564,7 @@ Ready. [ "sys", "querycache", 0, "clob", "-- SQL QUERY CACHE\n-- The SQL query cache returns a table with the query plans kept\n\ncreate function sys.querycache() \n\treturns table (query string, count int) \n\texternal name sql.dump_cache;" ] [ "sys", "querycache", 1, "int", "" ] [ "sys", "querylog", 0, "clob", "-- Trace the SQL input\ncreate procedure sys.querylog(filename string) \n\texternal name sql.logfile;" ] -[ "sys", "querylog_calls", 0, "oid", "-- 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_calls", 0, "oid", "-- 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)\nexternal name sql.querylog_calls;" ] [ "sys", "querylog_calls", 1, "timestamp", "" ] [ "sys", "querylog_calls", 2, "timestamp", "" ] [ "sys", "querylog_calls", 3, "clob", "" ] @@ -573,14 +573,14 @@ Ready. [ "sys", "querylog_calls", 6, "bigint", "" ] [ "sys", "querylog_calls", 7, "int", "" ] [ "sys", "querylog_calls", 8, "int", "" ] -[ "sys", "querylog_calls", 9, "bigint", "" ] -[ "sys", "querylog_catalog", 0, "oid", "-- 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-- Portions created by CWI are Copyright (C) 1997-July 2008 CWI.\n-- Copyright August 2008-2015 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_catalog", 0, "oid", "-- 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-- Portions created by CWI are Copyright (C) 1997-July 2008 CWI.\n-- Copyright August 2008-2015 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\t\"plan\" string,\t\t-- Name of MAL plan\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_catalog", 1, "clob", "" ] [ "sys", "querylog_catalog", 2, "timestamp", "" ] [ "sys", "querylog_catalog", 3, "clob", "" ] [ "sys", "querylog_catalog", 4, "clob", "" ] -[ "sys", "querylog_catalog", 5, "int", "" ] -[ "sys", "querylog_catalog", 6, "bigint", "" ] +[ "sys", "querylog_catalog", 5, "clob", "" ] +[ "sys", "querylog_catalog", 6, "int", "" ] +[ "sys", "querylog_catalog", 7, "bigint", "" ] [ "sys", "querylog_enable", 0, "smallint", "create procedure sys.querylog_enable(threshold smallint)\nexternal name sql.querylog_enable_threshold;" ] [ "sys", "queue", 0, "bigint", "-- 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-- Portions created by CWI are Copyright (C) 1997-July 2008 CWI.\n-- Copyright August 2008-2015 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\tprog ress int,\n\tstatus string,\n\ttag oid,\n\tquery string\n)\nexternal name sql.sysmon_queue;" ] [ "sys", "queue", 1, "clob", "" ] @@ -602,6 +602,12 @@ Ready. [ "sys", "reuse", 1, "clob", "" ] [ "sys", "reverse", 0, "clob", "-- 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-- Portions created by CWI are Copyright (C) 1997-July 2008 CWI.\n-- Copyright August 2008-2015 MonetDB B.V.\n-- All Rights Reserved.\n\n-- add function signatures to SQL catalog\n\n\n-- Reverse a string\ncreate function reverse(src string)\nreturns string external name udf.reverse;" ] [ "sys", "reverse", 1, "clob", "" ] +[ "sys", "right_shift", 0, "boolean", "create function \"right_shift\"(i1 inet, i2 inet) returns boolean\n\texternal name inet.\">>\";" ] +[ "sys", "right_shift", 1, "inet", "" ] +[ "sys", "right_shift", 2, "inet", "" ] +[ "sys", "right_shift_assign", 0, "boolean", "create function \"right_shift_assign\"(i1 inet, i2 inet) returns boolean\n\texternal name inet.\">>=\";" ] +[ "sys", "right_shift_assign", 1, "inet", "" ] +[ "sys", "right_shift_assign", 2, "inet", "" ] [ "sys", "sessions", 0, "clob", "create function sys.sessions()\nreturns table(\"user\" string, \"login\" timestamp, \"sessiontimeout\" bigint, \"lastcommand\" timestamp, \"querytimeout\" bigint, \"active\" bool)\nexternal name sql.sessions;" ] [ "sys", "sessions", 1, "timestamp", "" ] [ "sys", "sessions", 2, "bigint", "" ] @@ -668,18 +674,19 @@ Ready. [ "sys", "stddev_samp", 1, "hugeint", "" ] [ "sys", "stop", 0, "int", "create procedure sys.stop(tag int)\nexternal name sql.sysmon_stop;" ] [ "sys", "stop", 0, "bigint", "create procedure sys.stop(tag bigint)\nexternal name sql.sysmon_stop;" ] -[ "sys", "storage", 0, "clob", "-- 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-- Portions created by CWI are Copyright (C) 1997-July 2008 CWI.\n-- Copyright August 2008-2015 MonetDB B.V.\n-- All Rights Reserved.\n\n-- Author M.Kersten\n-- This script gives the database administrator insight in the actual\n-- footprint of the persistent tables and the maximum playground used\n-- when indices are introduced upon them.\n-- By chancing the storagemodelinput table directly, the footprint for\n-- yet to be loaded databases can be assessed.\n\n-- The actual storage footprint of an existing database can be\n-- obtained by the table procuding function storage()\n-- It represents the actual state of affairs, i.e. storage on disk\n-- of columns and foreign key indices, and possible temporary hash indices.\n-- For strings we take a sample to determine their average length.\n\ncreate function sys.storage()\nreturns table (\"schema\" string, \"table\" string, \"column\" string, \"type\" string, location string, \"count\" bigint, typewidth int, columnsize bigint, heapsize bigint, hashes bigint, imprints bigint, sorted boolean)\nexternal name sql.storage;" ] +[ "sys", "storage", 0, "clob", "-- 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-- Portions created by CWI are Copyright (C) 1997-July 2008 CWI.\n-- Copyright August 2008-2015 MonetDB B.V.\n-- All Rights Reserved.\n\n-- Author M.Kersten\n-- This script gives the database administrator insight in the actual\n-- footprint of the persistent tables and the maximum playground used\n-- when indices are introduced upon them.\n-- By chancing the storagemodelinput table directly, the footprint for\n-- yet to be loaded databases can be assessed.\n\n-- The actual storage footprint of an existing database can be\n-- obtained by the table procuding function storage()\n-- It represents the actual state of affairs, i.e. storage on disk\n-- of columns and foreign key indices, and possible temporary hash indices.\n-- For strings we take a sample to determine their average length.\n\ncreate function sys.\"storage\"()\nreturns table (\"schema\" string, \"table\" string, \"column\" string, \"type\" string, \"mode\" string, location string, \"count\" bigint, typewidth int, columnsize bigint, heapsize bigint, hashes bigint, imprints bigint, sorted boolean)\nexternal name sql.\"storage\";" ] [ "sys", "storage", 1, "clob", "" ] [ "sys", "storage", 2, "clob", "" ] [ "sys", "storage", 3, "clob", "" ] [ "sys", "storage", 4, "clob", "" ] -[ "sys", "storage", 5, "bigint", "" ] -[ "sys", "storage", 6, "int", "" ] -[ "sys", "storage", 7, "bigint", "" ] +[ "sys", "storage", 5, "clob", "" ] +[ "sys", "storage", 6, "bigint", "" ] +[ "sys", "storage", 7, "int", "" ] [ "sys", "storage", 8, "bigint", "" ] [ "sys", "storage", 9, "bigint", "" ] [ "sys", "storage", 10, "bigint", "" ] -[ "sys", "storage", 11, "boolean", "" ] +[ "sys", "storage", 11, "bigint", "" ] +[ "sys", "storage", 12, "boolean", "" ] [ "sys", "storagemodel", 0, "clob", "create function sys.storagemodel()\nreturns table (\n\t\"schema\" string,\n\t\"table\" string,\n\t\"column\" string,\n\t\"type\" string,\n\t\"count\"\tbigint,\n\tcolumnsize bigint,\n\theapsize bigint,\n\thashes bigint,\n\timprints bigint,\n\tsorted boolean)\nbegin\n\treturn select i.\"schema\", i.\"table\", i.\"column\", i.\"type\", i.\"count\",\n\tcolumnsize(i.\"type\", i.count, i.\"distinct\"),\n\theapsize(i.\"type\", i.\"distinct\", i.\"atomwidth\"),\n\thashsize(i.\"reference\", i.\"count\"),\n\timprintsize(i.\"count\",i.\"type\"),\n\ti.sorted\n\tfrom sys.storagemodelinput i;\nend;" ] [ "sys", "storagemodel", 1, "clob", "" ] [ "sys", "storagemodel", 2, "clob", "" ] @@ -701,18 +708,19 @@ Ready. [ "sys", "touches", 0, "boolean", "create function touches(a geometry, b geometry) returns boolean external name geom.\"Touches\";" ] [ "sys", "touches", 1, "geometry", "" ] [ "sys", "touches", 2, "geometry", "" ] -[ "sys", "tracelog", 0, "int", "-- 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-- Portions created by CWI are Copyright (C) 1997-July 2008 CWI.\n-- Copyright August 2008-2015 MonetDB B.V.\n-- All Rights Reserved.\n\n-- make the offline tracing table available for inspection\ncreate function sys.tracelog() \n\treturns table (\n\t\tevent integer,\t\t-- event counter\n\t\tclk varchar(20), \t-- wallclock, no mtime i n kernel\n\t\tpc varchar(50), \t-- module.function[nr]\n\t\tthread int, \t\t-- thread identifier\n\t\t\"user\" int, \t\t-- user identifier\n\t\tticks bigint, \t\t-- time in microseconds\n\t\treads bigint, \t\t-- number of blocks read\n\t\twrites bigint, \t-- number of blocks written\n\t\trbytes bigint,\t\t-- amount of bytes touched\n\t\twbytes bigint,\t\t-- amount of bytes written\n\t\ttype string,\t\t-- return types\n\t\tstmt string\t\t\t-- actual statement executed\n\t)\n\texternal name sql.dump_trace;" ] +[ "sys", "tracelog", 0, "int", "-- 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-- Portions created by CWI are Copyright (C) 1997-July 2008 CWI.\n-- Copyright August 2008-2015 MonetDB B.V.\n-- All Rights Reserved.\n\n-- make the offline tracing table available for inspection\ncreate function sys.tracelog() \n\treturns table (\n\t\tevent integer,\t\t-- event counter\n\t\tclk varchar(20), \t-- wallclock, no mtime i n kernel\n\t\tpc varchar(50), \t-- module.function[nr]\n\t\tthread int, \t\t-- thread identifier\n\t\tticks bigint, \t\t-- time in microseconds\n\t\trrsmb bigint, \t\t-- resident memory in MB\n\t\tvmmb bigint, \t\t-- virtual size in MB\n\t\treads bigint, \t\t-- number of blocks read\n\t\twrites bigint, \t\t-- number of blocks written\n\t\tminflt bigint, \t\t-- minor page faults\n\t\tmajflt bigint, \t\t-- major page faults\n\t\tnvcsw bigint, \t\t-- non-volantary conext switch\n\t\tstmt string\t\t\t-- actual statement executed\n\t)\n\texternal name sql.dump_trace;" ] [ "sys", "tracelog", 1, "varchar", "" ] [ "sys", "tracelog", 2, "varchar", "" ] [ "sys", "tracelog", 3, "int", "" ] -[ "sys", "tracelog", 4, "int", "" ] +[ "sys", "tracelog", 4, "bigint", "" ] [ "sys", "tracelog", 5, "bigint", "" ] [ "sys", "tracelog", 6, "bigint", "" ] [ "sys", "tracelog", 7, "bigint", "" ] [ "sys", "tracelog", 8, "bigint", "" ] [ "sys", "tracelog", 9, "bigint", "" ] -[ "sys", "tracelog", 10, "clob", "" ] -[ "sys", "tracelog", 11, "clob", "" ] +[ "sys", "tracelog", 10, "bigint", "" ] +[ "sys", "tracelog", 11, "bigint", "" ] +[ "sys", "tracelog", 12, "clob", "" ] [ "sys", "uuid", 0, "uuid", "-- generate a new uuid\ncreate function sys.uuid()\nreturns uuid external name uuid.\"new\";" ] [ "sys", "vacuum", 0, "clob", "create procedure vacuum(sys string, tab string)\n\texternal name sql.vacuum;" ] [ "sys", "vacuum", 1, "clob", "" ] diff --git a/sql/test/Tests/systemfunctions_bam.stable.out b/sql/test/Tests/systemfunctions_bam.stable.out --- a/sql/test/Tests/systemfunctions_bam.stable.out +++ b/sql/test/Tests/systemfunctions_bam.stable.out @@ -300,8 +300,6 @@ Ready. [ "sys", "equals", 2, "geometry", "" ] [ "sys", "evalalgebra", 0, "clob", "create procedure sys.evalalgebra( ra_stmt string, opt bool)\n\texternal name sql.\"evalAlgebra\";" ] [ "sys", "evalalgebra", 1, "boolean", "" ] -[ "sys", "fitsattach", 0, "clob", "create procedure fitsattach(fname string) external name fits.attach;" ] -[ "sys", "fitsload", 0, "clob", "create procedure fitsload(tname string) external name fits.load;" ] _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list
