- users-list - monetdb.org

[MonetDB-users] Triggers in MonetDB
by Arthur van Bunningen 17 Jul '05

17 Jul '05

Dear all, First my compliments with this piece of useful and implemented research. Second I was wondering if there is a possibility to have so-called "triggers" in MonetDB such as in for example PostgreSQL [1] or if there are other ways to use MonetDB as an "active database". If anyone has experiences in this field or can point me to the right place to look for this experience I would be very thankful. Kind regards, Arthur van Bunningen [1] http://www.postgresql.org/docs/8.0/static/triggers.html -- Ir. A.H. van Bunningen - PhD Student, Data Management Technology Univ. of Twente, Dept of EEMCS, POBox 217, 7500 AE Enschede, Netherlands Email: bunninge(a)cs.utwente.nl, Phone: +31 53 489 4290, Room: ZI3061 WWW: http://www.cs.utwente.nl/~bunninge

3 4

[MonetDB-users] jdbc related tests fail with a : connection refused
by Arnold Reuser 16 Jul '05

16 Jul '05

Hi all, Currently I've installed MonetDB & SQL on SUSE9.2 Pro from source as it was in CVS on 15 July. The problem I have is that all my jdbc related tests fail with a : Database connect failed: Unable to connect (localhost:48016): Connection refused , MServer with sql option and MapiClient runs just fine. Another example : % java -jar share/MonetDB/lib/MonetDB_JDBC.jar -umonetdb password: <monetdb> Currenlty also fails with the given username, password. Is the documentation or are the tests outdated? Or is there something I should be aware of before I can run the java part correctly. My main focus for MonetDB is also the usage of SQL by using a JDBC connection, therefore I hope this isn't a known newbie problem. cheers, Arnold

2 2

[MonetDB-users] bogus SQL in example + bad Mapi error reporting
by Martijn Faassen 03 Jul '05

03 Jul '05

Hi there, I just spent several hours scratching my head over why a piece of code didn't work. I was experimenting with Mapi and tried the example listed on the top of this page: http://monetdb.cwi.nl/TechDocs/APIs/Mapi/C/index.html This contains the following line: if ((hdl = mapi_query(dbh, "create table emp(name varchar, age int)")) == NULL) die(dbh); Unfortunately, it needs to be 'varchar(20)', not just plain 'varchar'. Just 'varchar' apparently does *not* result in an error, but instead all subsequent insert operations plus the select operation do nothing whatsoever; the fetching returns no results at all. The combination of this very bad error reporting and the mistake in the very first example given on the C API page cost me quite a bit of time, so I'm slightly grumpy. :) The bad example given is not good, but the way the error appears to be completely ignored is worse; this is not good for debuggability. The combination is terrible and not particularly conductive to the uptake of MonetDB. It may be of course that this bug is already fixed in CVS; I'm working off the latest release. Anyway, now that my test code finally works, I can hopefully make some progress hooking up XQuery to a Python API. (this is an experiment, and I realize the existing Python Mapi client code already ought to work, but we'll see..). Regards, Martijn

3 8

[MonetDB-users] mapi and xquery
by Martijn Faassen 03 Jul '05

03 Jul '05

Hi there, I'm trying to figure out how to use mapi to do an xquery. I have been able to shred documents (using MIL), but mapi with xquery is giving me trouble. Looking at the implementation of MapiClient, I see the following piece of code which appears relevant: /* xquery options are passed via the command string (becomes first line of query) */ char *c = command ? command : XQuerydefaultMode; char *t = strstr(c, "timing-") ? "" : mark ? "timing-" : ""; char *d = strstr(c, "debug-") ? "" : trace ? "debug-" : ""; char *m = strstr(c, "-mapi") ? "" : "-mapi"; command = (char*) malloc(strlen(c) + 20); sprintf(command, "%s%s%s%s\n", t, d, c, m); as well as this code: if (command) { /* special code for XQuery: prepend command to query */ hdl = mapi_query_prep(mid); CHECK_RESULT(mid, hdl, buf, break); mapi_query_part(hdl, command, strlen(command)); what exactly is going on here? The logic is rather opaque... Is there some minimal code example somewhere out there that shows how to do an xquery using mapi? Regards, Martijn

2 3

[MonetDB-users] xquery collection support?
by Martijn Faassen 02 Jul '05

02 Jul '05

Hi there, Another question: does the xquery implementation have collection support? It appears not, as fn:collection doesn't appear to be supported yet, but I'm a newbie to xquery so I may be missing something. I'm interested in XML content management applications, which tend to keep lots (and lots) of smaller XML documents. It'd be interesting to be able to query all of them (or sets of them) using XQuery in one go -- looping through all the stored documents and doing an XQuery on these individually would be suboptimal. Regards, Martijn

2 1

[MonetDB-users] monetdb failing tests on FreeBSD
by Joseph McDonald 09 Jun '05

09 Jun '05

Hi, I just installed MonetDB-4.8.0_rc0 on FreeBSD 5.4-STABLE running on an opteron. To get it to compile I added this to monetdb_config.h: #define __BSD_VISIBLE 1 and I changed /* #define _POSIX_C_SOURCE 200112L */ /* XXX JOE */ #define _POSIX_C_SOURCE 200112 because a freebsd system header already had _POSIX_C_SOURCE defined without the L. when running "gmake check" I get: !ERROR: Testing FAILED SIGNIFICANTLY ! 2 out of 69 tests could not be executed 2 out of 69 tests produced slightly different output 2 out of 69 tests produced SIGNIFICANTLY different output the output is at http://theoden.vpop.net/~joe/mtest/ Any idea what this means and why? I am brand new to monetdb. thanks, -joe

4 7

[MonetDB-users] running 'make check'
by Martijn Faassen 07 Jun '05

07 Jun '05

Hey, Thanks to Niels Nes, my compilation problems were solved. Just for others who may be reading: it was a bogus mel.yy.cc that I generated on an early compile run without bison and flex installed, and this didn't get removed by 'make clean' (something that will be fixed). Anyway, now I ran 'make check', but not very successfully: !ERROR: Testing FAILED SIGNIFICANTLY ! 3 out of 222 tests could not be executed 219 out of 222 tests produced SIGNIFICANTLY different output Basically typically the output I'd see was like this: 14:56:39> tests/suite_06/test_02.milS (<=60,60,60) ... 0.103s (Killed!) test_02.stable.out.FILTERED and test_02.test.out.FILTERED are equal. test_02.stable.err.FILTERED and test_02.test.err.FILTERED differ SIGNIFICANTLY! 14:56:40> tests/suite_08/test_01.milM (<=60,60,60) ... 0.126s (Killed!) test_01.stable.out.FILTERED and test_01.test.out.FILTERED are equal. test_01.stable.err.FILTERED and test_01.test.err.FILTERED differ SIGNIFICANTLY! 14:56:41> tests/suite_08/test_02.milS (<=60,60,60) ... 0.120s (Killed!) test_02.stable.out.FILTERED and test_02.test.out.FILTERED are equal. test_02.stable.err.FILTERED and test_02.test.err.FILTERED differ SIGNIFICANTLY! Looking at the HTML output, one set of unexpected output looks to be option information like this: # builtin opt config = ${prefix}/etc/MonetDB.conf # builtin opt gdk_arch = 32biti686-pc-linux-gnu # builtin opt gdk_version = 4.8.0 # builtin opt monet_pid = 30545 # builtin opt prefix = /home/faassen/tmp/local # builtin opt exec_prefix = ${prefix} # builtin opt gdk_dbname = tst # builtin opt gdk_dbfarm = ${prefix}/var/MonetDB More seriously, I get this: ! /home/faassen/working/monet/MonetDB-4.8.0/src/tools/Tests:: No such file or directory ! All: No such file or directory ! client.milC: No such file or directory ! client.stable.err: No such file or directory ! client.stable.out: No such file or directory ! client.stable.out.64bit: No such file or directory ! client.stable.out.64bit.monet_nostalgic.: No such file or directory ... etc Strangely enough, the directory mentioned does exist: faassen@moon:~$ cd /home/faassen/working/monet/MonetDB-4.8.0/src/tools/Tests faassen@moon:~/working/monet/MonetDB-4.8.0/src/tools/Tests$ pwd /home/faassen/working/monet/MonetDB-4.8.0/src/tools/Tests and those files, at least at a brief glance, do seem to exist too. Again I guess I'm missing something somewhere.. Regards, Martijn

4 9

[MonetDB-users] MonetDB 4.8.0 compile problem
by Martijn Faassen 03 Jun '05

03 Jun '05

Hi there, I'm trying to get MonetDB compiled on my debian (unstable) box, but I keep running into the same snag. It's probably something stupid, but I cannot seem to solve it. The full error log is in the end, but much of it seems to point to a missing yacc/bison during linking: mel.o(.text+0x6f4): In function `main': /home/faassen/working/monet/MonetDB-4.8.0/src/mel/mel.mx:400: undefined reference to `yyin' mel.tab.o(.text+0x126): In function `yyparse': /home/faassen/working/monet/MonetDB-4.8.0/src/mel/mel.yy:53: undefined reference to `yylineno' I have bison and flex installed, and configure picks it up, but this doesn't seem to help any... Does anyone have any clue on what I am doing wrong here, and how to fix it? Regards, Martijn /bin/sh ../../libtool --tag=CXX --mode=link g++ -g -O2 -ansi -Wall -W -o mel FileInfo.o atomops.o mil.o prelude.o bat_arg.o epilogue.o var_arg.o proto.o builtin.o glue.o mel.o mel.tab.o mel.yy.o symbol.o command.o html.o module.o symtable.o any_arg.o debug.o iterator.o object.o type_arg.o atom.o depend.o operator.o use.o atom_arg.o dependency.o link.o ops.o g++ -g -O2 -ansi -Wall -W -o mel FileInfo.o atomops.o mil.o prelude.o bat_arg.o epilogue.o var_arg.o proto.o builtin.o glue.o mel.o mel.tab.o mel.yy.o symbol.o command.o html.o module.o symtable.o any_arg.o debug.o iterator.o object.o type_arg.o atom.o depend.o operator.o use.o atom_arg.o dependency.o link.o ops.o mel.yy.o(.text+0x0): In function `main': /home/faassen/working/monet/MonetDB-4.8.0/src/mel/mel.yy.cc:2: multiple definition of `main' mel.o(.text+0x670):/home/faassen/working/monet/MonetDB-4.8.0/src/mel/mel.mx:388: first defined here /usr/bin/ld: Warning: size of symbol `main' changed from 1247 in mel.o to 15 in mel.yy.o mel.o(.text+0x6f4): In function `main': /home/faassen/working/monet/MonetDB-4.8.0/src/mel/mel.mx:400: undefined reference to `yyin' mel.tab.o(.text+0x126): In function `yyparse': /home/faassen/working/monet/MonetDB-4.8.0/src/mel/mel.yy:53: undefined reference to `yylineno' mel.tab.o(.text+0x138):/home/faassen/working/monet/MonetDB-4.8.0/src/mel/mel.yy:54: undefined reference to `yyin' mel.tab.o(.text+0x1e0):/home/faassen/working/monet/MonetDB-4.8.0/src/mel/mel.yy:61: undefined reference to `yylineno' mel.tab.o(.text+0x265):/home/faassen/working/monet/MonetDB-4.8.0/src/mel/mel.yy:64: undefined reference to `close_and_change(void*, _IO_FILE*)' mel.tab.o(.text+0x621):/home/faassen/working/monet/MonetDB-4.8.0/src/mel/mel.yy:124: undefined reference to `get_cur_buffer()' mel.tab.o(.text+0x637):/home/faassen/working/monet/MonetDB-4.8.0/src/mel/mel.yy:125: undefined reference to `yylineno' mel.tab.o(.text+0x691):/home/faassen/working/monet/MonetDB-4.8.0/src/mel/mel.yy:129: undefined reference to `new_buffer(_IO_FILE*)' mel.tab.o(.text+0x99f):/home/faassen/working/monet/MonetDB-4.8.0/src/mel/mel.yy:181: undefined reference to `get_signature(char*)' mel.tab.o(.text+0x9c0):/home/faassen/working/monet/MonetDB-4.8.0/src/mel/mel.yy:182: undefined reference to `script' mel.tab.o(.text+0x15dc): In function `yyparse': /home/faassen/working/monet/MonetDB-4.8.0/src/mel/y.tab.c:1168: undefined reference to `yylex()' mel.tab.o(.text+0x176b): In function `mel_yyerror(char const*)': /home/faassen/working/monet/MonetDB-4.8.0/src/mel/mel.yy:464: undefined reference to `yylineno' mel.tab.o(.text+0x178c): In function `mel_yyerror(char const*)': /usr/include/c++/3.3/ostream:193: undefined reference to `yylineno' mel.tab.o(.text+0x17da): In function `mel_yyerror(char const*)': /home/faassen/working/monet/MonetDB-4.8.0/src/mel/mel.yy:468: undefined reference to `yytext' mel.tab.o(.text+0x17f3):/home/faassen/working/monet/MonetDB-4.8.0/src/mel/mel.yy:472: undefined reference to `yytext' mel.tab.o(.text+0x1876): In function `mel_yyerror(char const*)': /usr/include/c++/3.3/ostream:193: undefined reference to `yytext' mel.tab.o(.text+0x195b): In function `semerror()': /home/faassen/working/monet/MonetDB-4.8.0/src/mel/mel.yy:494: undefined reference to `yylineno' mel.tab.o(.text+0x197c): In function `semerror()': /usr/include/c++/3.3/ostream:193: undefined reference to `yylineno' collect2: ld returned 1 exit status make[4]: *** [mel] Error 1 make[4]: Leaving directory `/home/faassen/working/monet/MonetDB-4.8.0/src/mel' make[3]: *** [all] Error 2 make[3]: Leaving directory `/home/faassen/working/monet/MonetDB-4.8.0/src/mel' make[2]: *** [all-recursive] Error 1 make[2]: Leaving directory `/home/faassen/working/monet/MonetDB-4.8.0/src' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/home/faassen/working/monet/MonetDB-4.8.0' make: *** [all] Error 2

2 3

[MonetDB-users] Converting a unix timestamp (bigint) to timestamp/date?
by Arjen van der Meijden 24 May '05

24 May '05

Hi List, I'm trying to have an easy way to convert some fields that where initialy (in MySQL) stored as unsigned integers. I converted them using a dump/restore to MonetDB's bigint. Currently this works: CAST("1970-01-01 01:00:00" as timestamp) + (CAST(RecordTimestamp AS STRING) || 'LL') Appearantly bigint (long) is incompatible with sec_interval (long)? But afaik this should be possible in SQL as well: CAST("1970-01-01 01:00:00" as timestamp) + INTERVAL RecordTimestamp SECONDS And of course in MySQL there is FROM_UNIXTIME(RecordTimestamp), in PostgreSQL there is: timestamp 'epoch' + RecordTimestamp * interval '1 seconds' Converting it back is also quite a bit of work: SELECT ((CAST(current_timestamp as timestamp) - CAST("1970-01-01 00:00:00" as timestamp)) / 1000); That seems to work to get the current time's epoch... Why aren't timestamptz and timestamp compatible, btw? In MySQL there is UNIX_TIMESTAMP(RecordTimestamp), in PostgreSQL EXTRACT(epoch FROM current_timestamp)::bigint I was also looking for a way to format the date/timestamp output, is that possible? Casting it to a SQL-date is rather easy of course, but other formatting will very likely become a big mess if EXTRACT where to be used. Since my client-language is php, I either need preformatted output or a unix timestamp for ease of use. In SQL it is often better to work with dates/timestamps, rather than the not really correct unix timestamp arithmetics (add 86400 seconds to get the next day and so on). Did I overlook some easy to use extentions to SQL, or do I really need to use the constructs I already found? Best regards, Arjen van der Meijden

3 2

[MonetDB-users] Bug with average of decimals when an empty table is returned
by Arjen van der Meijden 24 May '05

24 May '05

Hi list, I noticed a weird result from AVG when doing one of my queries; -1.7976931348623158e+307 Appearantly it happens with this minimal testcase: monetdb-> create table emptyone (field decimal(3,1)); Operation successful monetdb-> select avg(field) from emptyone; +------------------------+ | scale_down_avg_field | +========================+ | -1.7976931348623158E307 | +------------------------+ 1 row The result should afaik be NULL, not this value. With a decimal(3) it works correctly by the way. The same behaviour is seen with numeric(3,1) vs numeric(3). Best regards, Arjen

2 4