Changeset: 52825f2634e7 for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=52825f2634e7
Modified Files:
clients/Tests/SQL-dump.stable.out
clients/Tests/SQL-dump.stable.out.int128
Branch: Jul2015
Log Message:
Approved.
diffs (166 lines):
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
@@ -469,13 +469,13 @@ create function "abbrev" (p inet) return
external name inet."abbrev";
create function alpha(pdec double, pradius double)
returns double external name sql.alpha;
-create procedure analyze(minmax int, "sample" bigint)
+create procedure sys.analyze(minmax int, "sample" bigint)
external name sql.analyze;
-create procedure analyze(minmax int, "sample" bigint, sch string)
+create procedure sys.analyze(minmax int, "sample" bigint, sch string)
external name sql.analyze;
-create procedure analyze(minmax int, "sample" bigint, sch string, tbl string)
+create procedure sys.analyze(minmax int, "sample" bigint, sch string, tbl
string)
external name sql.analyze;
-create procedure analyze(minmax int, "sample" bigint, sch string, tbl string,
col string)
+create procedure sys.analyze(minmax int, "sample" bigint, sch string, tbl
string, col string)
external name sql.analyze;
-- ogc Spatial Analysis methods
@@ -1167,7 +1167,22 @@ external name sql.sysmon_stop;
-- For strings we take a sample to determine their average length.
create function sys."storage"()
-returns table ("schema" string, "table" string, "column" string, "type"
string, "mode" string, location string, "count" bigint, typewidth int,
columnsize bigint, heapsize bigint, hashes bigint, phash boolean, imprints
bigint, sorted boolean)
+returns table (
+ "schema" string,
+ "table" string,
+ "column" string,
+ "type" string,
+ "mode" string,
+ location string,
+ "count" bigint,
+ typewidth int,
+ columnsize bigint,
+ heapsize bigint,
+ hashes bigint,
+ phash boolean,
+ imprints bigint,
+ sorted boolean
+)
external name sql."storage";
create function sys.storagemodel()
returns table (
@@ -6994,10 +7009,10 @@ 5509 "querylog_disable" "create procedur
5511 "tracelog" "-- This Source Code Form is subject to the terms of
the Mozilla Public\n-- License, v. 2.0. If a copy of the MPL was not
distributed with this\n-- file, You can obtain one at
http://mozilla.org/MPL/2.0/.\n--\n-- Copyright 2008-2015 MonetDB B.V.\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 in 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-- actual statement
executed\n\t)\n\texterna
l name sql.dump_trace;" "sql" 1 5 false false false
2000
5541 "profiler_openstream" "create procedure profiler_openstream(host
string, port int) external name profiler.""openStream"";" "profiler" 1
2 true false false 2000
5545 "profiler_stethoscope" "create procedure profiler_stethoscope(ticks
int) external name profiler.stethoscope;" "profiler" 1 2 true
false false 2000
-5548 "epoch" "-- This Source Code Form is subject to the terms of the
Mozilla Public\n-- License, v. 2.0. If a copy of the MPL was not distributed
with this\n-- file, You can obtain one at http://mozilla.org/MPL/2.0/.\n--\n--
Copyright 2008-2015 MonetDB B.V.\n\n-- assume milliseconds when converted to
TIMESTAMP\ncreate function ""epoch""(sec bigint) returns timestamp\n\texternal
name timestamp.""epoch"";" "timestamp" 1 1 false false
false 2000
-5552 "epoch" "create function ""epoch""(sec int) returns
timestamp\n\texternal name timestamp.""epoch"";" "timestamp" 1 1
false false false 2000
-5556 "epoch" "create function ""epoch""(ts timestamp) returns
int\n\texternal name timestamp.""epoch"";" "timestamp" 1 1
false false false 2000
-5560 "epoch" "create function ""epoch""(ts timestamp with time zone) returns
int\n\texternal name timestamp.""epoch"";" "timestamp" 1 1
false false false 2000
+5548 "epoch" "-- This Source Code Form is subject to the terms of the
Mozilla Public\n-- License, v. 2.0. If a copy of the MPL was not distributed
with this\n-- file, You can obtain one at http://mozilla.org/MPL/2.0/.\n--\n--
Copyright 2008-2015 MonetDB B.V.\n\n-- assume milliseconds when converted to
TIMESTAMP\ncreate function sys.""epoch""(sec bigint) returns
timestamp\n\texternal name timestamp.""epoch"";" "timestamp" 1
1 false false false 2000
+5552 "epoch" "create function sys.""epoch""(sec int) returns
timestamp\n\texternal name timestamp.""epoch"";" "timestamp" 1
1 false false false 2000
+5556 "epoch" "create function sys.""epoch""(ts timestamp) returns
int\n\texternal name timestamp.""epoch"";" "timestamp" 1 1
false false false 2000
+5560 "epoch" "create function sys.""epoch""(ts timestamp with time zone)
returns int\n\texternal name timestamp.""epoch"";" "timestamp" 1 1
false false false 2000
5564 "shrink" "-- This Source Code Form is subject to the terms of
the Mozilla Public\n-- License, v. 2.0. If a copy of the MPL was not
distributed with this\n-- file, You can obtain one at
http://mozilla.org/MPL/2.0/.\n--\n-- Copyright 2008-2015 MonetDB B.V.\n\n--
Vacuum a relational table should be done with care.\n-- For, the oid's are used
in join-indices.\n\n-- Vacuum of tables may improve IO performance and disk
footprint.\n-- The foreign key constraints should be dropped before\n-- and
re-established after the cluster operation.\n\ncreate procedure shrink(sys
string, tab string)\n\texternal name sql.shrink;" "sql" 1 2
true false false 2000
5568 "reuse" "create procedure reuse(sys string, tab string)\n\texternal
name sql.reuse;" "sql" 1 2 true false false 2000
5572 "vacuum" "create procedure vacuum(sys string, tab
string)\n\texternal name sql.vacuum;" "sql" 1 2 true false
false 2000
@@ -7192,17 +7207,17 @@ 6482 "fitsload" "create procedure fitslo
6485 "listdirpat" "create procedure listdirpat(dirname string,pat string)
external name fits.listdirpattern;" "fits" 1 2 true false
false 2000
6521 "netcdf_attach" "-- gr_name is ""GLOBAL"" or ""ROOT"" for classic
NetCDF files\n-- used for groups in HDF5 files\n-- global attributes have
obj_name=""""\n\n-- create function netcdfvar (fname varchar(256))
\n--\treturns int external name netcdf.test;\n\ncreate procedure
netcdf_attach(fname varchar(256))\n external name netcdf.attach;"
"netcdf" 1 2 true false false 2000
6524 "netcdf_importvar" "create procedure netcdf_importvar(fid integer,
varnname varchar(256))\n external name netcdf.importvariable;"
"netcdf" 1 2 true false false 2000
-6528 "storage" "-- This Source Code Form is subject to the terms of
the Mozilla Public\n-- License, v. 2.0. If a copy of the MPL was not
distributed with this\n-- file, You can obtain one at
http://mozilla.org/MPL/2.0/.\n--\n-- Copyright 2008-2015 MonetDB B.V.\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, phash boolean, imprints bigint, sorted boolean)\nexternal name
sql.""storage"";" "sql" 1 5 false false false 2000
+6528 "storage" "-- This Source Code Form is subject to the terms of
the Mozilla Public\n-- License, v. 2.0. If a copy of the MPL was not
distributed with this\n-- file, You can obtain one at
http://mozilla.org/MPL/2.0/.\n--\n-- Copyright 2008-2015 MonetDB B.V.\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 (\n\t""schema"" strin
g,\n\t""table"" string,\n\t""column"" string,\n\t""type"" string,\n\t""mode""
string,\n\tlocation string,\n\t""count"" bigint,\n\ttypewidth
int,\n\tcolumnsize bigint,\n\theapsize bigint,\n\thashes bigint,\n\tphash
boolean,\n\timprints bigint,\n\tsorted boolean\n)\nexternal name
sql.""storage"";" "sql" 1 5 false false false 2000
6572 "storagemodelinit" "-- this table can be adjusted to reflect the
anticipated final database size\n\n-- The model input can be derived from the
current database using\ncreate procedure
sys.storagemodelinit()\nbegin\n\tdelete from sys.storagemodelinput;\n\n\tinsert
into sys.storagemodelinput\n\tselect x.""schema"", x.""table"", x.""column"",
x.""type"", x.typewidth, x.count, 0, x.typewidth, false, x.sorted from
sys.""storage""() x;\n\n\tupdate sys.storagemodelinput\n\tset reference =
true\n\twhere concat(concat(""schema"",""table""), ""column"") in (\n\t\tselect
concat( concat(""fkschema"".""name"", ""fktable"".""name""),
""fkkeycol"".""name"" )\n\t\tfrom\t""sys"".""keys"" as
""fkkey"",\n\t\t\t\t""sys"".""objects"" as
""fkkeycol"",\n\t\t\t\t""sys"".""tables"" as
""fktable"",\n\t\t\t\t""sys"".""schemas"" as ""fkschema""\n\t\twhere
""fktable"".""id"" = ""fkkey"".""table_id""\n\t\t\tand ""fkkey"".""id"" =
""fkkeycol"".""id""\n\t\t\tand ""fkschema"".""id"" = ""fktable"".""schem
a_id""\n\t\t\tand ""fkkey"".""rkey"" > -1);\n\n\tupdate
sys.storagemodelinput\n\tset ""distinct"" = ""count"" -- assume all
distinct\n\twhere ""type"" = 'varchar' or ""type""='clob';\nend;" "user" 2
2 true false false 2000
6574 "columnsize" "-- 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' then return 4 * i;\n\twhen nme = 'bigint' then return
8 * i;\n\twhen nme = 'hugeint' 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;" "user" 2
1 false false false 2
000
6580 "heapsize" "create function sys.heapsize(tpe string, i bigint, w
int)\nreturns bigint\nbegin\n\tif tpe <> 'varchar' and tpe <>
'clob'\n\tthen\n\t\treturn 0;\n\tend if;\n\treturn 10240 + i * w;\nend;"
"user" 2 1 false false false 2000
6586 "hashsize" "create function sys.hashsize(b boolean, i
bigint)\nreturns bigint\nbegin\n\t-- assume non-compound keys\n\tif b =
true\n\tthen\n\t\treturn 8 * i;\n\tend if;\n\treturn 0;\nend;" "user" 2
1 false false false 2000
6591 "imprintsize" "create function sys.imprintsize(i bigint, nme
string)\nreturns bigint\nbegin\n\tif nme = 'boolean'\n\t\tor nme =
'tinyint'\n\t\tor nme = 'smallint'\n\t\tor nme = 'int'\n\t\tor nme =
'bigint'\n\t\tor nme = 'hugeint'\n\t\tor nme = 'decimal'\n\t\tor nme =
'date'\n\t\tor nme = 'timestamp'\n\t\tor nme = 'real'\n\t\tor nme =
'double'\n\tthen\n\t\treturn cast( i * 0.12 as bigint);\n\tend if ;\n\treturn
0;\nend;" "user" 2 1 false false false 2000
6596 "storagemodel" "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"" bigint,\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;" "user" 2 5 false false
false 2000
-6643 "analyze" "create procedure analyze(minmax int, ""sample""
bigint)\nexternal name sql.analyze;" "sql" 1 2 true false
false 2000
-6647 "analyze" "create procedure analyze(minmax int, ""sample""
bigint, sch string)\nexternal name sql.analyze;" "sql" 1 2
true false false 2000
-6652 "analyze" "create procedure analyze(minmax int, ""sample""
bigint, sch string, tbl string)\nexternal name sql.analyze;" "sql" 1
2 true false false 2000
-6658 "analyze" "create procedure analyze(minmax int, ""sample""
bigint, sch string, tbl string, col string)\nexternal name sql.analyze;"
"sql" 1 2 true false false 2000
+6643 "analyze" "create procedure sys.analyze(minmax int, ""sample""
bigint)\nexternal name sql.analyze;" "sql" 1 2 true
false false 2000
+6647 "analyze" "create procedure sys.analyze(minmax int, ""sample""
bigint, sch string)\nexternal name sql.analyze;" "sql" 1 2 true
false false 2000
+6652 "analyze" "create procedure sys.analyze(minmax int, ""sample""
bigint, sch string, tbl string)\nexternal name sql.analyze;" "sql" 1
2 true false false 2000
+6658 "analyze" "create procedure sys.analyze(minmax int, ""sample""
bigint, sch string, tbl string, col string)\nexternal name sql.analyze;"
"sql" 1 2 true false false 2000
6665 "reverse" "-- This Source Code Form is subject to the terms of
the Mozilla Public\n-- License, v. 2.0. If a copy of the MPL was not
distributed with this\n-- file, You can obtain one at
http://mozilla.org/MPL/2.0/.\n--\n-- Copyright 2008-2015 MonetDB B.V.\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;" "udf" 1
1 false false false 2000
6669 "fuse" "-- 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;" "udf" 1 1 false false false
2000
6674 "fuse" "-- fuse two (2-byte) smallint values into one (4-byte) integer
value\ncreate function fuse(one smallint, two smallint)\nreturns integer
external name udf.fuse;" "udf" 1 1 false false false
2000
diff --git a/clients/Tests/SQL-dump.stable.out.int128
b/clients/Tests/SQL-dump.stable.out.int128
--- a/clients/Tests/SQL-dump.stable.out.int128
+++ b/clients/Tests/SQL-dump.stable.out.int128
@@ -465,13 +465,13 @@ create function "abbrev" (p inet) return
external name inet."abbrev";
create function alpha(pdec double, pradius double)
returns double external name sql.alpha;
-create procedure analyze(minmax int, "sample" bigint)
+create procedure sys.analyze(minmax int, "sample" bigint)
external name sql.analyze;
-create procedure analyze(minmax int, "sample" bigint, sch string)
+create procedure sys.analyze(minmax int, "sample" bigint, sch string)
external name sql.analyze;
-create procedure analyze(minmax int, "sample" bigint, sch string, tbl string)
+create procedure sys.analyze(minmax int, "sample" bigint, sch string, tbl
string)
external name sql.analyze;
-create procedure analyze(minmax int, "sample" bigint, sch string, tbl string,
col string)
+create procedure sys.analyze(minmax int, "sample" bigint, sch string, tbl
string, col string)
external name sql.analyze;
-- ogc Spatial Analysis methods
@@ -1204,7 +1204,22 @@ external name sql.sysmon_stop;
-- For strings we take a sample to determine their average length.
create function sys."storage"()
-returns table ("schema" string, "table" string, "column" string, "type"
string, "mode" string, location string, "count" bigint, typewidth int,
columnsize bigint, heapsize bigint, hashes bigint, phash boolean, imprints
bigint, sorted boolean)
+returns table (
+ "schema" string,
+ "table" string,
+ "column" string,
+ "type" string,
+ "mode" string,
+ location string,
+ "count" bigint,
+ typewidth int,
+ columnsize bigint,
+ heapsize bigint,
+ hashes bigint,
+ phash boolean,
+ imprints bigint,
+ sorted boolean
+)
external name sql."storage";
create function sys.storagemodel()
returns table (
@@ -7674,10 +7689,10 @@ 5962 "querylog_disable" "create procedur
5964 "tracelog" "-- This Source Code Form is subject to the terms of
the Mozilla Public\n-- License, v. 2.0. If a copy of the MPL was not
distributed with this\n-- file, You can obtain one at
http://mozilla.org/MPL/2.0/.\n--\n-- Copyright 2008-2015 MonetDB B.V.\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 in 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-- actual statement
executed\n\t)\n\texterna
l name sql.dump_trace;" "sql" 1 5 false false false
2000
5994 "profiler_openstream" "create procedure profiler_openstream(host
string, port int) external name profiler.""openStream"";" "profiler" 1
2 true false false 2000
5998 "profiler_stethoscope" "create procedure profiler_stethoscope(ticks
int) external name profiler.stethoscope;" "profiler" 1 2 true
false false 2000
-6001 "epoch" "-- This Source Code Form is subject to the terms of the
Mozilla Public\n-- License, v. 2.0. If a copy of the MPL was not distributed
with this\n-- file, You can obtain one at http://mozilla.org/MPL/2.0/.\n--\n--
Copyright 2008-2015 MonetDB B.V.\n\n-- assume milliseconds when converted to
TIMESTAMP\ncreate function ""epoch""(sec bigint) returns timestamp\n\texternal
name timestamp.""epoch"";" "timestamp" 1 1 false false
false 2000
-6005 "epoch" "create function ""epoch""(sec int) returns
timestamp\n\texternal name timestamp.""epoch"";" "timestamp" 1 1
false false false 2000
-6009 "epoch" "create function ""epoch""(ts timestamp) returns
int\n\texternal name timestamp.""epoch"";" "timestamp" 1 1
false false false 2000
-6013 "epoch" "create function ""epoch""(ts timestamp with time zone) returns
int\n\texternal name timestamp.""epoch"";" "timestamp" 1 1
false false false 2000
+6001 "epoch" "-- This Source Code Form is subject to the terms of the
Mozilla Public\n-- License, v. 2.0. If a copy of the MPL was not distributed
with this\n-- file, You can obtain one at http://mozilla.org/MPL/2.0/.\n--\n--
Copyright 2008-2015 MonetDB B.V.\n\n-- assume milliseconds when converted to
TIMESTAMP\ncreate function sys.""epoch""(sec bigint) returns
timestamp\n\texternal name timestamp.""epoch"";" "timestamp" 1
1 false false false 2000
+6005 "epoch" "create function sys.""epoch""(sec int) returns
timestamp\n\texternal name timestamp.""epoch"";" "timestamp" 1
1 false false false 2000
+6009 "epoch" "create function sys.""epoch""(ts timestamp) returns
int\n\texternal name timestamp.""epoch"";" "timestamp" 1 1
false false false 2000
+6013 "epoch" "create function sys.""epoch""(ts timestamp with time zone)
returns int\n\texternal name timestamp.""epoch"";" "timestamp" 1 1
false false false 2000
6017 "shrink" "-- This Source Code Form is subject to the terms of
the Mozilla Public\n-- License, v. 2.0. If a copy of the MPL was not
distributed with this\n-- file, You can obtain one at
http://mozilla.org/MPL/2.0/.\n--\n-- Copyright 2008-2015 MonetDB B.V.\n\n--
Vacuum a relational table should be done with care.\n-- For, the oid's are used
in join-indices.\n\n-- Vacuum of tables may improve IO performance and disk
footprint.\n-- The foreign key constraints should be dropped before\n-- and
re-established after the cluster operation.\n\ncreate procedure shrink(sys
string, tab string)\n\texternal name sql.shrink;" "sql" 1 2
true false false 2000
6021 "reuse" "create procedure reuse(sys string, tab string)\n\texternal
name sql.reuse;" "sql" 1 2 true false false 2000
6025 "vacuum" "create procedure vacuum(sys string, tab
string)\n\texternal name sql.vacuum;" "sql" 1 2 true false
false 2000
@@ -7880,17 +7895,17 @@ 6970 "fitsload" "create procedure fitslo
6973 "listdirpat" "create procedure listdirpat(dirname string,pat string)
external name fits.listdirpattern;" "fits" 1 2 true false
false 2000
7009 "netcdf_attach" "-- gr_name is ""GLOBAL"" or ""ROOT"" for classic
NetCDF files\n-- used for groups in HDF5 files\n-- global attributes have
obj_name=""""\n\n-- create function netcdfvar (fname varchar(256))
\n--\treturns int external name netcdf.test;\n\ncreate procedure
netcdf_attach(fname varchar(256))\n external name netcdf.attach;"
"netcdf" 1 2 true false false 2000
7012 "netcdf_importvar" "create procedure netcdf_importvar(fid integer,
varnname varchar(256))\n external name netcdf.importvariable;"
"netcdf" 1 2 true false false 2000
-7016 "storage" "-- This Source Code Form is subject to the terms of
the Mozilla Public\n-- License, v. 2.0. If a copy of the MPL was not
distributed with this\n-- file, You can obtain one at
http://mozilla.org/MPL/2.0/.\n--\n-- Copyright 2008-2015 MonetDB B.V.\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, phash boolean, imprints bigint, sorted boolean)\nexternal name
sql.""storage"";" "sql" 1 5 false false false 2000
+7016 "storage" "-- This Source Code Form is subject to the terms of
the Mozilla Public\n-- License, v. 2.0. If a copy of the MPL was not
distributed with this\n-- file, You can obtain one at
http://mozilla.org/MPL/2.0/.\n--\n-- Copyright 2008-2015 MonetDB B.V.\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 (\n\t""schema"" strin
g,\n\t""table"" string,\n\t""column"" string,\n\t""type"" string,\n\t""mode""
string,\n\tlocation string,\n\t""count"" bigint,\n\ttypewidth
int,\n\tcolumnsize bigint,\n\theapsize bigint,\n\thashes bigint,\n\tphash
boolean,\n\timprints bigint,\n\tsorted boolean\n)\nexternal name
sql.""storage"";" "sql" 1 5 false false false 2000
7060 "storagemodelinit" "-- this table can be adjusted to reflect the
anticipated final database size\n\n-- The model input can be derived from the
current database using\ncreate procedure
sys.storagemodelinit()\nbegin\n\tdelete from sys.storagemodelinput;\n\n\tinsert
into sys.storagemodelinput\n\tselect x.""schema"", x.""table"", x.""column"",
x.""type"", x.typewidth, x.count, 0, x.typewidth, false, x.sorted from
sys.""storage""() x;\n\n\tupdate sys.storagemodelinput\n\tset reference =
true\n\twhere concat(concat(""schema"",""table""), ""column"") in (\n\t\tselect
concat( concat(""fkschema"".""name"", ""fktable"".""name""),
""fkkeycol"".""name"" )\n\t\tfrom\t""sys"".""keys"" as
""fkkey"",\n\t\t\t\t""sys"".""objects"" as
""fkkeycol"",\n\t\t\t\t""sys"".""tables"" as
""fktable"",\n\t\t\t\t""sys"".""schemas"" as ""fkschema""\n\t\twhere
""fktable"".""id"" = ""fkkey"".""table_id""\n\t\t\tand ""fkkey"".""id"" =
""fkkeycol"".""id""\n\t\t\tand ""fkschema"".""id"" = ""fktable"".""schem
a_id""\n\t\t\tand ""fkkey"".""rkey"" > -1);\n\n\tupdate
sys.storagemodelinput\n\tset ""distinct"" = ""count"" -- assume all
distinct\n\twhere ""type"" = 'varchar' or ""type""='clob';\nend;" "user" 2
2 true false false 2000
7062 "columnsize" "-- 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' then return 4 * i;\n\twhen nme = 'bigint' then return
8 * i;\n\twhen nme = 'hugeint' 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;" "user" 2
1 false false false 2
000
7068 "heapsize" "create function sys.heapsize(tpe string, i bigint, w
int)\nreturns bigint\nbegin\n\tif tpe <> 'varchar' and tpe <>
'clob'\n\tthen\n\t\treturn 0;\n\tend if;\n\treturn 10240 + i * w;\nend;"
"user" 2 1 false false false 2000
7074 "hashsize" "create function sys.hashsize(b boolean, i
bigint)\nreturns bigint\nbegin\n\t-- assume non-compound keys\n\tif b =
true\n\tthen\n\t\treturn 8 * i;\n\tend if;\n\treturn 0;\nend;" "user" 2
1 false false false 2000
7079 "imprintsize" "create function sys.imprintsize(i bigint, nme
string)\nreturns bigint\nbegin\n\tif nme = 'boolean'\n\t\tor nme =
'tinyint'\n\t\tor nme = 'smallint'\n\t\tor nme = 'int'\n\t\tor nme =
'bigint'\n\t\tor nme = 'hugeint'\n\t\tor nme = 'decimal'\n\t\tor nme =
'date'\n\t\tor nme = 'timestamp'\n\t\tor nme = 'real'\n\t\tor nme =
'double'\n\tthen\n\t\treturn cast( i * 0.12 as bigint);\n\tend if ;\n\treturn
0;\nend;" "user" 2 1 false false false 2000
7084 "storagemodel" "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"" bigint,\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;" "user" 2 5 false false
false 2000
-7131 "analyze" "create procedure analyze(minmax int, ""sample""
bigint)\nexternal name sql.analyze;" "sql" 1 2 true false
false 2000
-7135 "analyze" "create procedure analyze(minmax int, ""sample""
bigint, sch string)\nexternal name sql.analyze;" "sql" 1 2
true false false 2000
-7140 "analyze" "create procedure analyze(minmax int, ""sample""
bigint, sch string, tbl string)\nexternal name sql.analyze;" "sql" 1
2 true false false 2000
-7146 "analyze" "create procedure analyze(minmax int, ""sample""
bigint, sch string, tbl string, col string)\nexternal name sql.analyze;"
"sql" 1 2 true false false 2000
+7131 "analyze" "create procedure sys.analyze(minmax int, ""sample""
bigint)\nexternal name sql.analyze;" "sql" 1 2 true
false false 2000
+7135 "analyze" "create procedure sys.analyze(minmax int, ""sample""
bigint, sch string)\nexternal name sql.analyze;" "sql" 1 2 true
false false 2000
+7140 "analyze" "create procedure sys.analyze(minmax int, ""sample""
bigint, sch string, tbl string)\nexternal name sql.analyze;" "sql" 1
2 true false false 2000
+7146 "analyze" "create procedure sys.analyze(minmax int, ""sample""
bigint, sch string, tbl string, col string)\nexternal name sql.analyze;"
"sql" 1 2 true false false 2000
7153 "reverse" "-- This Source Code Form is subject to the terms of
the Mozilla Public\n-- License, v. 2.0. If a copy of the MPL was not
distributed with this\n-- file, You can obtain one at
http://mozilla.org/MPL/2.0/.\n--\n-- Copyright 2008-2015 MonetDB B.V.\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;" "udf" 1
1 false false false 2000
7157 "fuse" "-- 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;" "udf" 1 1 false false false
2000
7162 "fuse" "-- fuse two (2-byte) smallint values into one (4-byte) integer
value\ncreate function fuse(one smallint, two smallint)\nreturns integer
external name udf.fuse;" "udf" 1 1 false false false
2000
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
