Changeset: 1f2b62a6261b for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=1f2b62a6261b
Added Files:
sql/test/mergetables/Tests/forex.sql
sql/test/mergetables/Tests/forex.stable.err
sql/test/mergetables/Tests/forex.stable.out
Removed Files:
sql/test/mergetables/Tests/forexload.sql
Modified Files:
sql/backends/monet5/sql_scenario.c
sql/scripts/mtime.sql
sql/test/mergetables/Tests/All
sql/test/mergetables/Tests/mergeload.stable.out
sql/test/mergetables/Tests/mergequery.sql
sql/test/mergetables/Tests/mergequery.stable.err
sql/test/mergetables/Tests/mergequery.stable.out
Branch: default
Log Message:
Add epoch functions and mini-forex example
The forex traces show currency pair bid/offer prices.
Organized around a two day history and one current
All glued together in a merge table.
diffs (truncated from 819 to 300 lines):
diff --git a/sql/backends/monet5/sql_scenario.c
b/sql/backends/monet5/sql_scenario.c
--- a/sql/backends/monet5/sql_scenario.c
+++ b/sql/backends/monet5/sql_scenario.c
@@ -1109,6 +1109,22 @@ create aggregate json.tojsonarray( x dou
GDKfree(buf);
return err; /* usually MAL_SUCCEED */
}
+static str
+sql_update_feb2015(Client c)
+{
+ size_t bufsize = 8192*2, pos = 0;
+ char *buf = GDKmalloc(bufsize), *err = NULL;
+
+ pos += snprintf(buf + pos, bufsize - pos, "set schema \"sys\";\n");
+ pos += snprintf(buf+pos, bufsize - pos, "create function epoch(t int)
returns timestamp external name timestamp.epoch;\n");
+ pos += snprintf(buf+pos, bufsize - pos, "create function epoch(t
timestamp) returns int external name timestamp.epoch;\n");
+ pos += snprintf(buf+pos, bufsize - pos, "create function epoch(t
bigint) returns timestamp external name calc.timestamp;\n");
+
+ printf("Running database upgrade commands:\n%s\n", buf);
+ err = SQLstatementIntern(c, &buf, "update", 1, 0);
+ GDKfree(buf);
+ return err; /* usually MAL_SUCCEED */
+}
str
SQLinitClient(Client c)
@@ -1280,6 +1296,14 @@ SQLinitClient(Client c)
GDKfree(err);
}
}
+ /* add missing features needed beyond Oct 2014 */
+ sql_find_subtype(&tp, "timestamp", 0, 0);
+ if ( !sql_bind_func(m->sa, mvc_bind_schema(m, "sys"), "epoch",
&tp, NULL, F_FUNC) ){
+ if ((err = sql_update_feb2015(c)) !=NULL) {
+ fprintf(stderr, "!%s\n", err);
+ GDKfree(err);
+ }
+ }
}
fflush(stdout);
fflush(stderr);
diff --git a/sql/scripts/mtime.sql b/sql/scripts/mtime.sql
--- a/sql/scripts/mtime.sql
+++ b/sql/scripts/mtime.sql
@@ -115,3 +115,11 @@ create function dayofmonth( date ) retur
external name mtime.dayofmonth;
create function week( date ) returns integer
external name mtime.weekofyear;
+
+create function epoch(t timestamp) returns int
+ external name timestamp.epoch;
+create function epoch(t int) returns timestamp
+ external name timestamp.epoch;
+create function epoch(t bigint) returns timestamp
+ external name calc.timestamp;
+
diff --git a/sql/test/mergetables/Tests/All b/sql/test/mergetables/Tests/All
--- a/sql/test/mergetables/Tests/All
+++ b/sql/test/mergetables/Tests/All
@@ -1,8 +1,7 @@
mergeinit
mergeload
mergequery
+forex
#mergedrop corrupts the database
#crash0 crashes the server
-#forex
-
diff --git a/sql/test/mergetables/Tests/forexload.sql
b/sql/test/mergetables/Tests/forex.sql
rename from sql/test/mergetables/Tests/forexload.sql
rename to sql/test/mergetables/Tests/forex.sql
--- a/sql/test/mergetables/Tests/forexload.sql
+++ b/sql/test/mergetables/Tests/forex.sql
@@ -1,71 +1,82 @@
-- sample application based on partitions
-CREATE TABLE day1 ( clk timestamp, currency string, ts timestamp, bid
decimal(12,6), offer decimal(12,6), "open" decimal(12,6), spread decimal(12,6)
);
-CREATE TABLE day1stage ( clk bigint, currency string, ts bigint, bid
decimal(12,6), offer decimal(12,6), "open" decimal(12,6), spread decimal(12,6)
);
+CREATE TABLE day1 ( clk timestamp, currency string, ts timestamp, bid
decimal(12,6), offer decimal(12,6), spread decimal(12,6) );
+CREATE TABLE day1stage ( clk bigint, currency string, ts bigint, bid
decimal(12,6), offer decimal(12,6), spread decimal(12,6) );
COPY 10 RECORDS INTO day1stage FROM stdin USING DELIMITERS '|','\n';
-1413267171000|EUR/USD|1413267158643|1.271810|1.271890|1.271850|0.000080|
-1413267171000|USD/JPY|1413267171225|107.121000|107.127000|107.124000|0.006000|
-1413267171000|GBP/USD|1413267161304|1.606820|1.606930|1.606875|0.000110|
-1413267171000|EUR/GBP|1413267150417|0.791460|0.791570|0.791515|0.000110|
-1413267171000|USD/CHF|1413267158643|0.950420|0.950550|0.950485|0.000130|
-1413267171000|EUR/JPY|1413267171318|136.240000|136.253000|136.246500|0.013000|
-1413267171000|EUR/CHF|1413267158643|1.208760|1.208950|1.208855|0.000190|
-1413267171000|USD/CAD|1413267171318|1.121180|1.121270|1.121225|0.000090|
-1413267171000|AUD/USD|1413267162803|0.878680|0.878780|0.878730|0.000100|
-1413267171000|GBP/JPY|1413267171239|172.126000|172.146000|172.136000|0.020000
+1413267171000|EUR/USD|1413267158643|1.271810|1.271890|0.000080
+1413267171000|USD/JPY|1413267171225|107.121000|107.127000|0.006000
+1413267171000|GBP/USD|1413267161304|1.606820|1.606930|0.000110
+1413267171000|EUR/GBP|1413267150417|0.791460|0.791570|0.000110
+1413267171000|USD/CHF|1413267158643|0.950420|0.950550|0.000130
+1413267171000|EUR/JPY|1413267171318|136.240000|136.253000|0.013000
+1413267171000|EUR/CHF|1413267158643|1.208760|1.208950|0.000190
+1413267171000|USD/CAD|1413267171318|1.121180|1.121270|0.000090
+1413267171000|AUD/USD|1413267162803|0.878680|0.878780|0.000100
+1413267171000|GBP/JPY|1413267171239|172.126000|172.146000|0.020000
-DROP TABLE day1stagel
+INSERT INTO day1
+SELECT epoch(clk), currency, epoch(ts), bid, offer,spread
+FROM day1stage;
+
+DROP TABLE day1stage;
+
+SELECT * from day1;
ALTER TABLE day1 SET READ ONLY;
-CREATE TABLE day2 ( clk timestamp, currency string, ts timestamp, bid
decimal(12,6), offer decimal(12,6), "open" decimal(12,6), spread decimal(12,6)
);
-CREATE TABLE day2stage ( clk bigint, currency string, ts bigint, bid
decimal(12,6), offer decimal(12,6), "open" decimal(12,6), spread decimal(12,6)
);
+CREATE TABLE day2 ( clk timestamp, currency string, ts timestamp, bid
decimal(12,6), offer decimal(12,6), spread decimal(12,6) );
+CREATE TABLE day2stage ( clk bigint, currency string, ts bigint, bid
decimal(12,6), offer decimal(12,6), spread decimal(12,6) );
COPY 10 RECORDS INTO day2stage FROM stdin USING DELIMITERS '|','\n';
-1413267176000|EUR/USD|1413267177168|1.271780|1.271880|1.271830|0.000100
-1413267176000|USD/JPY|1413267177168|107.120000|107.125000|107.122500|0.005000
-1413267176000|GBP/USD|1413267175356|1.606820|1.606950|1.606885|0.000130
-1413267176000|EUR/GBP|1413267175336|0.791440|0.791550|0.791495|0.000110
-1413267176000|USD/CHF|1413267175367|0.950430|0.950560|0.950495|0.000130
-1413267176000|EUR/JPY|1413267177033|136.235000|136.248000|136.241500|0.013000
-1413267176000|EUR/CHF|1413267158643|1.208760|1.208950|1.208855|0.000190
-1413267176000|USD/CAD|1413267173063|1.121150|1.121260|1.121205|0.000110
-1413267176000|AUD/USD|1413267176076|0.878680|0.878760|0.878720|0.000080
-1413267176000|GBP/JPY|1413267176950|172.122000|172.142000|172.132000|0.020000
+1413267176000|EUR/USD|1413267177168|1.271780|1.271880|0.000100
+1413267176000|USD/JPY|1413267177168|107.120000|107.125000|0.005000
+1413267176000|GBP/USD|1413267175356|1.606820|1.606950|0.000130
+1413267176000|EUR/GBP|1413267175336|0.791440|0.791550|0.000110
+1413267176000|USD/CHF|1413267175367|0.950430|0.950560|0.000130
+1413267176000|EUR/JPY|1413267177033|136.235000|136.248000|0.013000
+1413267176000|EUR/CHF|1413267158643|1.208760|1.208950|0.000190
+1413267176000|USD/CAD|1413267173063|1.121150|1.121260|0.000110
+1413267176000|AUD/USD|1413267176076|0.878680|0.878760|0.000080
+1413267176000|GBP/JPY|1413267176950|172.122000|172.142000|0.020000
-DROP TABLE day1stagel
-ALTER TABLE day1 SET READ ONLY;
+INSERT INTO day2
+SELECT epoch(clk), currency, epoch(ts), bid, offer,spread
+FROM day2stage;
+DROP TABLE day2stage;
-CREATE TABLE day3 ( clk timestamp, currency string, ts timestamp, bid
decimal(12,6), offer decimal(12,6), "open" decimal(12,6), spread decimal(12,6)
);
+SELECT * from day1;
+ALTER TABLE day2 SET READ ONLY;
-CREATE MERGE TABLE forex ( clk bigint, currency string, ts bigint, bid
decimal(12,6), offer decimal(12,6), "open" decimal(12,6), spread decimal(12,6)
);
+CREATE TABLE day3 ( clk timestamp, currency string, ts timestamp, bid
decimal(12,6), offer decimal(12,6), spread decimal(12,6) );
+
+CREATE MERGE TABLE forex ( clk bigint, currency string, ts bigint, bid
decimal(12,6), offer decimal(12,6), spread decimal(12,6) );
ALTER TABLE forex ADD TABLE day1;
ALTER TABLE forex ADD TABLE day2;
ALTER TABLE forex ADD TABLE day3;
-SELECT * FROM forex;
+SELECT * FROM forex WHERE currency = 'EUR/USD';
-- update the last part
-INSERT INTO day3 VALUES(1413267181000, 'EUR/USD', 1413267182327, 1.271910,
1.271990, 1.271950, 0.000080);
-INSERT INTO day3 VALUES(1413267181000, 'USD/JPY', 1413267181647,
107.114000,107.121000,107.117500,0.007000);
-INSERT INTO day3 VALUES(1413267181000, 'GBP/USD', 1413267182048, 1.606870,
1.606980, 1.606925, 0.000110);
-INSERT INTO day3 VALUES(1413267181000, 'EUR/GBP', 1413267181968, 0.791490,
0.791600, 0.791545, 0.000110);
-INSERT INTO day3 VALUES(1413267181000, 'USD/CHF', 1413267182041, 0.950350,
0.950460, 0.950405, 0.000110);
-INSERT INTO day3 VALUES(1413267181000, 'EUR/JPY', 1413267182406,
136.241000,136.253000,136.247000,0.012000);
-INSERT INTO day3 VALUES(1413267181000, 'EUR/CHF', 1413267181950, 1.208770,
1.208950, 1.208860, 0.000180);
-INSERT INTO day3 VALUES(1413267181000, 'USD/CAD', 1413267181830, 1.121120,
1.121230, 1.121175, 0.000110);
-INSERT INTO day3 VALUES(1413267181000, 'AUD/USD', 1413267181549, 0.878730,
0.878810, 0.878770, 0.000080);
-INSERT INTO day3 VALUES(1413267181000, 'GBP/JPY', 1413267181618,
172.116000,172.138000,172.127000,0.022000);
+INSERT INTO day3 VALUES( epoch(1413267181000), 'EUR/USD',
epoch(1413267182327), 1.271910, 1.271990, 0.000080);
+INSERT INTO day3 VALUES( epoch(1413267181000), 'USD/JPY',
epoch(1413267181647), 107.114000,107.121000,0.007000);
+INSERT INTO day3 VALUES( epoch(1413267181000), 'GBP/USD',
epoch(1413267182048), 1.606870, 1.606980, 0.000110);
+INSERT INTO day3 VALUES( epoch(1413267181000), 'EUR/GBP',
epoch(1413267181968), 0.791490, 0.791600, 0.000110);
+INSERT INTO day3 VALUES( epoch(1413267181000), 'USD/CHF',
epoch(1413267182041), 0.950350, 0.950460, 0.000110);
+INSERT INTO day3 VALUES( epoch(1413267181000), 'EUR/JPY',
epoch(1413267182406), 136.241000,136.253000,0.012000);
+INSERT INTO day3 VALUES( epoch(1413267181000), 'EUR/CHF',
epoch(1413267181950), 1.208770, 1.208950, 0.000180);
+INSERT INTO day3 VALUES( epoch(1413267181000), 'USD/CAD',
epoch(1413267181830), 1.121120, 1.121230, 0.000110);
+INSERT INTO day3 VALUES( epoch(1413267181000), 'AUD/USD',
epoch(1413267181549), 0.878730, 0.878810, 0.000080);
+INSERT INTO day3 VALUES( epoch(1413267181000), 'GBP/JPY',
epoch(1413267181618), 172.116000,172.138000,0.022000);
-- perform some compound queries
SELECT avg(bid), sum(bid) FROM forex;
-SELECT avg(bid), sum(bid) FROM forex GROUP BY currency;
+SELECT currency, cast(avg(bid) AS DECIMAL(12,6)), sum(bid) FROM forex GROUP BY
currency;
-- drop the first day
ALTER TABLE forex DROP TABLE day1;
-SELECT * FROM forex;
+SELECT * FROM forex WHERE currency = 'EUR/USD';
DROP TABLE forex;
DROP TABLE day1;
diff --git a/sql/test/mergetables/Tests/forex.stable.err
b/sql/test/mergetables/Tests/forex.stable.err
new file mode 100644
--- /dev/null
+++ b/sql/test/mergetables/Tests/forex.stable.err
@@ -0,0 +1,37 @@
+stderr of test 'forex` in directory 'sql/test/mergetables` itself:
+
+
+# 23:00:41 >
+# 23:00:41 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=38230" "--set"
"mapi_usock=/var/tmp/mtest-23198/.s.monetdb.38230" "--set" "monet_prompt="
"--forcemito" "--set" "mal_listing=2"
"--dbpath=/export/scratch1/mk/current//Linux/var/MonetDB/mTests_sql_test_mergetables"
"--set" "mal_listing=0" "--set" "embedded_r=yes"
+# 23:00:41 >
+
+# builtin opt gdk_dbpath =
/export/scratch1/mk/current//Linux/var/monetdb5/dbfarm/demo
+# builtin opt gdk_debug = 0
+# builtin opt gdk_vmtrim = no
+# builtin opt monet_prompt = >
+# builtin opt monet_daemon = no
+# builtin opt mapi_port = 50000
+# builtin opt mapi_open = false
+# builtin opt mapi_autosense = false
+# builtin opt sql_optimizer = default_pipe
+# builtin opt sql_debug = 0
+# cmdline opt gdk_nr_threads = 0
+# cmdline opt mapi_open = true
+# cmdline opt mapi_port = 38230
+# cmdline opt mapi_usock = /var/tmp/mtest-23198/.s.monetdb.38230
+# cmdline opt monet_prompt =
+# cmdline opt mal_listing = 2
+# cmdline opt gdk_dbpath =
/export/scratch1/mk/current//Linux/var/MonetDB/mTests_sql_test_mergetables
+# cmdline opt mal_listing = 0
+# cmdline opt embedded_r = yes
+# cmdline opt gdk_debug = 536870922
+
+# 23:00:42 >
+# 23:00:42 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-23198" "--port=38230"
+# 23:00:42 >
+
+
+# 23:00:42 >
+# 23:00:42 > "Done."
+# 23:00:42 >
+
diff --git a/sql/test/mergetables/Tests/forex.stable.out
b/sql/test/mergetables/Tests/forex.stable.out
new file mode 100644
--- /dev/null
+++ b/sql/test/mergetables/Tests/forex.stable.out
@@ -0,0 +1,162 @@
+stdout of test 'forex` in directory 'sql/test/mergetables` itself:
+
+
+# 23:00:41 >
+# 23:00:41 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=38230" "--set"
"mapi_usock=/var/tmp/mtest-23198/.s.monetdb.38230" "--set" "monet_prompt="
"--forcemito" "--set" "mal_listing=2"
"--dbpath=/export/scratch1/mk/current//Linux/var/MonetDB/mTests_sql_test_mergetables"
"--set" "mal_listing=0" "--set" "embedded_r=yes"
+# 23:00:41 >
+
+# MonetDB 5 server v11.20.0
+# This is an unreleased version
+# Serving database 'mTests_sql_test_mergetables', using 8 threads
+# Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs and 128bit
integers dynamically linked
+# Found 15.590 GiB available main-memory.
+# Copyright (c) 1993-July 2008 CWI.
+# Copyright (c) August 2008-2014 MonetDB B.V., all rights reserved
+# Visit http://www.monetdb.org/ for further information
+# Listening for connection requests on mapi:monetdb://vienna.ins.cwi.nl:38230/
+# Listening for UNIX domain connection requests on
mapi:monetdb:///var/tmp/mtest-23198/.s.monetdb.38230
+# MonetDB/GIS module loaded
+# MonetDB/SQL module loaded
+# MonetDB/R module loaded
+
+Ready.
+
+# 23:00:42 >
+# 23:00:42 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-23198" "--port=38230"
+# 23:00:42 >
+
+#CREATE TABLE day1 ( clk timestamp, currency string, ts timestamp, bid
decimal(12,6), offer decimal(12,6), spread decimal(12,6) );
+#CREATE TABLE day1stage ( clk bigint, currency string, ts bigint, bid
decimal(12,6), offer decimal(12,6), spread decimal(12,6) );
+#COPY 10 RECORDS INTO day1stage FROM stdin USING DELIMITERS '|','\n';
+#1413267171000|EUR/USD|1413267158643|1.271810|1.271890|0.000080
+#1413267171000|USD/JPY|1413267171225|107.121000|107.127000|0.006000
+#1413267171000|GBP/USD|1413267161304|1.606820|1.606930|0.000110
+#1413267171000|EUR/GBP|1413267150417|0.791460|0.791570|0.000110
+#1413267171000|USD/CHF|1413267158643|0.950420|0.950550|0.000130
+#1413267171000|EUR/JPY|1413267171318|136.240000|136.253000|0.013000
+#1413267171000|EUR/CHF|1413267158643|1.208760|1.208950|0.000190
+[ 10 ]
+#INSERT INTO day1
+#SELECT epoch(clk), currency, epoch(ts), bid, offer,spread
+#FROM day1stage;
+[ 10 ]
+#DROP TABLE day1stage;
+#SELECT * from day1;
+% sys.day1, sys.day1, sys.day1, sys.day1, sys.day1,
sys.day1 # table_name
+% clk, currency, ts, bid, offer, spread # name
+% timestamp, clob, timestamp, decimal, decimal, decimal
# type
+% 26, 7, 26, 14, 14, 14 # length
+[ 2014-10-14 06:12:51.000000, "EUR/USD", 2014-10-14 06:12:38.643000,
1.271810, 1.271890, 0.000080 ]
+[ 2014-10-14 06:12:51.000000, "USD/JPY", 2014-10-14 06:12:51.225000,
107.121000, 107.127000, 0.006000 ]
+[ 2014-10-14 06:12:51.000000, "GBP/USD", 2014-10-14 06:12:41.304000,
1.606820, 1.606930, 0.000110 ]
+[ 2014-10-14 06:12:51.000000, "EUR/GBP", 2014-10-14 06:12:30.417000,
0.791460, 0.791570, 0.000110 ]
+[ 2014-10-14 06:12:51.000000, "USD/CHF", 2014-10-14 06:12:38.643000,
0.950420, 0.950550, 0.000130 ]
+[ 2014-10-14 06:12:51.000000, "EUR/JPY", 2014-10-14 06:12:51.318000,
136.240000, 136.253000, 0.013000 ]
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
