July 2008 - users-list

[MonetDB-users] [Question]How to change the format of datetime?
by chenhao333333 28 Jul '08

28 Jul '08

Are there any functions like date_format in MonetDB, I want to do something like date_format(zncsrq, '%Y%m%d', that is Mysql's function, in MonetDB. Best regards!

2 1

[MonetDB-users] JESUS LOPEZ LOPEZ - F009939R está ausente de la oficina.
by jesus.lopez＠correo.aeat.es 28 Jul '08

28 Jul '08

Estaré ausente de la oficina desde el 28/07/2008 y no volveré hasta el 08/08/2008. Responderé a su mensaje cuando regrese.

1 0

[MonetDB-users] MonetDB/XQuery XQTS results available?
by Richard Eckart 28 Jul '08

28 Jul '08

Hello everybody, are there any results of the official XML Query Test Suite on MonetDB/ XQuery available or is there an easy way to generate them? Regards, Richard

1 0

[MonetDB-users] Is there Function likes TRUNCATE ?
by nori 25 Jul '08

25 Jul '08

Hi, All. Is there the function such as "TRUNCATE TABLE" in other DB? Regards, Nori. -- Nori tunohiko(a)gmail.com

4 4

[MonetDB-users] Cannot compile MonetDB_4-24
by Stefan Klinger 23 Jul '08

23 Jul '08

Thanks for your help, however I still cannot compile. Actually, I just need to work with most current versions of Pathfinder, and maybe try some other branches of that one as well, until I find out where the fixes end up, and which one works for me. Unortunately I was unable to find a detailed description of which branches of what modules are required to build which other branches of whatever modules I need anyway. If someone would be so kind as to show me how I can achieve this without touching CVS, I would be deeply grateful. Until then, I'll try to follow the CVS trail. On 17 July 2008, Stefan Manegold wrote with possible deletions: > In case you prefer to use the CVS sources, and you do not require the latest > cutting-edge (potentially instable) development version we recommend to > checkout the latest Stable branches. > I ust realize that the respective branch tags are not (yet?) listed on the > MonetDB web site, hence I'll share them here: > > buildtools: MonetDB_1-24 > MonetDB: MonetDB_1-24 > clients: Clients_1-24 > MonetDB4: MonetDB_4-24 > MonetDB5: MonetDB_5-6 > pathfinder: XQuery_0-24 > sql: SQL_2-24 > geom: Geom_0-4 Thank you for this information, but compilation fails for MonetDB4 on the 'make' command with the following error message: /home/sk/opt/MonetDB/include/MonetDB/gdk/gdk_system.h:117: error: expected ‘)’ before ‘*’ token A detailed description of what I did is in the attached 'report' file, which actually is a bash script. The resulting logfile [1] from report 2>&1 | tee logfile is available for download. Kind regards Stefan ____________________ [1] logfile, link valid for one week, 409565 Bytes https://depot.uni-konstanz.de/get/72fwr6 -- Stefan Klinger o/klettern /\/ bis zum send plaintext only - max size 32kB - no spam \ Abfallen http://stefan-klinger.de

4 10

[MonetDB-users] Problems With CREATE TEMPORARY TABLE
by James Leigh 21 Jul '08

21 Jul '08

Hi all, I am having trouble using MonetDB, with dynamic temporary table creation. I have included a simple unit test to demonstrate the problem. These tests work fine on other JDBC SQL servers, but fail with the below exceptions when using MonetDB. Any ideas on how to make new tables available to other connections or keep new tables from interrupting other jdbc statements? Thanks, James ---%<--- java.sql.SQLException: INSERT INTO: no such table 'paints_2' at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2058) at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery(MonetConnection.java:1808) at nl.cwi.monetdb.jdbc.MonetStatement.internalExecute(MonetStatement.java:371) at nl.cwi.monetdb.jdbc.MonetStatement.execute(MonetStatement.java:340) at nl.cwi.monetdb.jdbc.MonetPreparedStatement.<init>(MonetPreparedStatement.java:102) at nl.cwi.monetdb.jdbc.MonetConnection.prepareStatement(MonetConnection.java:625) at nl.cwi.monetdb.jdbc.MonetConnection.prepareStatement(MonetConnection.java:586) at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:248) at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:302) at MonetDBJdbcTest.testDualCreateTable(MonetDBJdbcTest.java:28) ---%<--- java.sql.SQLException: Query did not produce a result set at nl.cwi.monetdb.jdbc.MonetPreparedStatement.executeQuery(MonetPreparedStatement.java:228) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93) at MonetDBJdbcTest.testSelectWhileCreateTable(MonetDBJdbcTest.java:77) ---%<--- import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import junit.framework.TestCase; import org.apache.commons.dbcp.BasicDataSource; public class MonetDBJdbcTest extends TestCase { private BasicDataSource ds1; public void testDualCreateTable() throws Exception { Connection c4 = ds1.getConnection(); c4.setAutoCommit(true); Connection c76 = ds1.getConnection(); c76.setAutoCommit(false); Statement s93 = c76.createStatement(); s93.execute("CREATE TEMPORARY TABLE TRANSACTION_STATEMENTS (\n ctx INTEGER NOT NULL,\n subj INTEGER NOT NULL,\n pred INTEGER NOT NULL,\n obj INTEGER NOT NULL,\n expl BOOL NOT NULL\n)"); s93.close(); Statement s96 = c4.createStatement(); s96.execute("CREATE TABLE paints_2 (\n ctx INTEGER NOT NULL,\n subj INTEGER NOT NULL,\n obj INTEGER NOT NULL,\n expl BOOL NOT NULL\n)"); s96.close(); PreparedStatement ps102 = c76.prepareStatement("INSERT INTO paints_2\nSELECT DISTINCT ctx, subj, obj, expl FROM TRANSACTION_STATEMENTS tr\nWHERE NOT EXISTS (SELECT * FROM paints_2 st\nWHERE st.ctx = tr.ctx AND st.subj = tr.subj AND st.obj = tr.obj AND st.expl = tr.expl)"); ps102.close(); c76.close(); c4.close(); } public void testSelectWhileCreateTable() throws Exception { Connection c2 = ds1.getConnection(); c2.setAutoCommit(true); Statement s7 = c2.createStatement(); s7.execute("CREATE TABLE HASH_VALUES (\n id INTEGER NOT NULL,\n value BIGINT NOT NULL\n)"); s7.close(); PreparedStatement ps72 = c2.prepareStatement("SELECT id, value\nFROM HASH_VALUES\nWHERE value IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); ps72.setLong(1, 157585763729791056l); ps72.setLong(2, 1105705122070497121l); ps72.setLong(3, 940939309403574879l); ps72.setLong(4, 44738479624713631l); ps72.setLong(5, 93693228172326007l); ps72.setLong(6, 909856949769965718l); ps72.setLong(7, 388219017765868455l); ps72.setLong(8, 498326321060763331l); ps72.setLong(9, 917168269028472831l); ps72.setLong(10, 560146884310966732l); ps72.setLong(11, 178986943741094305l); ps72.setNull(12, -5); ps72.setNull(13, -5); ps72.setNull(14, -5); ps72.setNull(15, -5); ResultSet rs73 = ps72.executeQuery(); rs73.next(); rs73.close(); Statement s76 = c2.createStatement(); s76.execute("CREATE TABLE TRIPLES (\n ctx INTEGER NOT NULL,\n subj INTEGER NOT NULL,\n pred INTEGER NOT NULL,\n obj INTEGER NOT NULL,\n expl BOOL NOT NULL\n)"); s76.close(); ps72.setLong(1, 157585763729791056l); ps72.setLong(2, 93693228172326007l); ps72.setLong(3, 44738479624713631l); ps72.setLong(4, 909856949769965718l); ps72.setLong(5, 388219017765868455l); ps72.setLong(6, 917168269028472831l); ps72.setNull(7, -5); ps72.setNull(8, -5); ps72.setNull(9, -5); ps72.setNull(10, -5); ps72.setNull(11, -5); ps72.setNull(12, -5); ps72.setNull(13, -5); ps72.setNull(14, -5); ps72.setNull(15, -5); ResultSet rs88 = ps72.executeQuery(); rs88.close(); ps72.close(); c2.close(); } @Override protected void setUp() throws Exception { Class.forName("nl.cwi.monetdb.jdbc.MonetDriver"); ds1 = new BasicDataSource(); ds1.setUrl("jdbc:monetdb://localhost/jdbc_test"); ds1.setUsername("monetdb"); ds1.setPassword("monetdb"); } @Override protected void tearDown() throws Exception { Connection c106 = ds1.getConnection(); Statement s107 = c106.createStatement(); try { s107.execute("DROP TABLE HASH_VALUES"); s107.execute("DROP TABLE TRIPLES"); } catch (SQLException exc) { // ignore } try { s107.execute("DROP TABLE TRANSACTION_STATEMENTS"); s107.execute("DROP TABLE paints_2"); } catch (SQLException exc) { // ignore } s107.close(); c106.close(); ds1.close(); } }

2 6

Re: [MonetDB-users] [Monetdb-developers] tables/databases simultaneous updates & extended-stored-procedures
by Martin Kersten 19 Jul '08

19 Jul '08

SOL ZADEH wrote: > > Thanks Martin; let me explain better: > > if table update are too costly (slow etc) then could try insertion > instead (having/including the timestamps as an index) > providing the server can cope with very frequent table insertions. > > Is there any benchmark tests in relation to table insertions? you can have a look at the TPCH loading figures. http://monetdb.cwi.nl/projects/monetdb//SQL/Benchmark/TPCH/ I have no hard figures in my head, but the DVD store benchmark we ran 2 years back came out directly after MySQL for high-volume business transactions. http://monetdb.cwi.nl/projects/monetdb/SQL/Benchmark/DVDstore/index.html so it all depends what you consider costly/slow. > > Regarding : > >> Concurrent transactions can freely read a consistent copy of the > database. > does this mean "dirty reads" ? no, only consistent reads > Regards > > > > ----- Original Message ---- > From: Martin Kersten <Martin.Kersten(a)cwi.nl> > To: SOL ZADEH <solzadeh(a)yahoo.com> > Cc: Niels Nes <Niels.Nes(a)cwi.nl>; monetdb-developers(a)lists.sourceforge.net > Sent: Saturday, July 19, 2008 2:22:16 PM > Subject: Re: [Monetdb-developers] tables/databases simultaneous > updates & extended-stored-procedures > > SOL ZADEH wrote: > > > > Thanks Neils for your prompt reply. > > > > Is there a quantified limitation on "insertions" on scenarios I > > mentioned below (multiple tables/database)? > > I.e. Does very frequent insertions on 2 or more tables (on same > > database or other database on the same server) > > in any way effect the performance of the other queries > > (select,insert,update) on the "same" > > or "other" tables (i.e. is parallel processing possible & locking > > performance etc). > > Regards > Trying to interpret you question what it means. > MonetDB does not lock tables or databases during update. Concurrent > transactions can > freely read a consistent copy of the database. > All interference comes from sharing the same CPU, memory and IO channels. > > > > ----- Original Message ---- > > From: Niels Nes <Niels.Nes(a)cwi.nl <mailto:Niels.Nes@cwi.nl>> > > To: SOL ZADEH <solzadeh(a)yahoo.com <mailto:solzadeh@yahoo.com>> > > Cc: monetdb-developers(a)lists.sourceforge.net > <mailto:monetdb-developers@lists.sourceforge.net> > > Sent: Saturday, July 19, 2008 8:49:55 AM > > Subject: Re: [Monetdb-developers] tables/databases simultaneous > > updates & extended-stored-procedures > > > > On Fri, Jul 18, 2008 at 05:13:54PM -0700, SOL ZADEH wrote: > > > Hi > > > > > > > > > I have a couple of questions and really appreciate if any one > > > could answer them or instruct me to the relevant forum topics: > > > > > > > > > 1- what is the limitations (speed, threshold etc) on > > > simultaneous tables /databases update i.e. via multiple odbc > > > connection lets say (also any other faster API to interface > > > with the MonetDB ?) > > MonetDB research concentrated mostly in low update/high query loads. The > > update implementation is still quite capable, but no reall performance > > experiments on updates have been conducted. Next to odbc we have > > mapi and jdbc APIs available. > > > > > > > > > 2- Is there a way to have the triggers to generate > > > real-world (non-db) events i.e. similar to the Extended Stored > > > Procedure in SqlServer or the way Oracle allows you to run Java > > > code inside the stored procedures. > > MonetDB can be extended using C-code and that code could be run within > > the triggers. > > > > Niels > > > > > > Thanks > > > > > > > > ------------------------------------------------------------------------- > > > This SF.Net email is sponsored by the Moblin Your Move Developer's > > challenge > > > Build the coolest Linux based applications with Moblin SDK & win > > great prizes > > > Grand prize is a trip for two to an Open Source event anywhere in > > the world > > > http://moblin-contest.org/redirect.php?banner_id=100&url=/ > <http://moblin-contest.org/redirect.php?banner_id=100&url=/> > > <http://moblin-contest.org/redirect.php?banner_id=100&url=/ > <http://moblin-contest.org/redirect.php?banner_id=100&url=/>> > > > _______________________________________________ > > > Monetdb-developers mailing list > > > Monetdb-developers(a)lists.sourceforge.net > <mailto:Monetdb-developers@lists.sourceforge.net> > > <mailto:Monetdb-developers@lists.sourceforge.net > <mailto:Monetdb-developers@lists.sourceforge.net>> > > > https://lists.sourceforge.net/lists/listinfo/monetdb-developers > > > > > > -- > > > > Niels Nes, Centre for Mathematics and Computer Science (CWI) > > Kruislaan 413, 1098 SJ Amsterdam, The Netherlands > > room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 > > url: http://www.cwi.nl/~niels <http://www.cwi.nl/%7Eniels> > <http://www.cwi.nl/%7Eniels> e-mail: > > Niels.Nes(a)cwi.nl <mailto:Niels.Nes@cwi.nl> <mailto:Niels.Nes@cwi.nl > <mailto:Niels.Nes@cwi.nl>> > > > > ------------------------------------------------------------------------ > > > > > ------------------------------------------------------------------------- > > This SF.Net email is sponsored by the Moblin Your Move Developer's > challenge > > Build the coolest Linux based applications with Moblin SDK & win > great prizes > > Grand prize is a trip for two to an Open Source event anywhere in > the world > > http://moblin-contest.org/redirect.php?banner_id=100&url=/ > <http://moblin-contest.org/redirect.php?banner_id=100&url=/> > > ------------------------------------------------------------------------ > > > > _______________________________________________ > > Monetdb-developers mailing list > > Monetdb-developers(a)lists.sourceforge.net > <mailto:Monetdb-developers@lists.sourceforge.net> > > https://lists.sourceforge.net/lists/listinfo/monetdb-developers > > > >

1 0

Re: [MonetDB-users] Range join question [MonetDB]
by Martin Kersten 18 Jul '08

18 Jul '08

Vasilis Vassalos wrote: > Hello again, > > for the student project I've told you about, we need to perform range > joins, so we tried some on MonetDB. > We're having trouble getting range joins on strings working properly. > > Michalis, my student, has looked into it and reports that > ALGrangejoin (algebra.c) calls BATrangejoin (gdk_rangejoin.c) that > performs the join and returns a BAT > as a result. BATrangejoin has a switch with a case for TYPE_chr that > compares strings You are really delving deep in the sources ; ) An area only meant for the brave at heart. > > When we try to perform a range join on a string attribute (char(n), > even for n=1, varchar(n)) we get > an exception: > !MALException:algebra.rangejoin:GDKerror > !ERROR: BATrangejoin: type not implemented > > The switch statement skips the TYPE_chr case and goes to the default. > For example, > >> sql>create table s1 (id int, v char(10)); >> sql>create table s2 (id int, v char(10)); >> sql>insert into s1 values (1,'s1'); >> sql>insert into s1 values (2,'s2'); >> sql>insert into s1 values (3,'s3'); >> sql>insert into s1 values (4,'s4'); >> sql>insert into s1 values (5,'s5'); >> sql>insert into s1 values (6,'s6'); >> sql>insert into s2 values (1,'s1'); >> sql>insert into s2 values (2,'s2'); >> sql>insert into s2 values (3,'s3'); >> sql>insert into s2 values (4,'s4'); >> sql>insert into s2 values (5,'s5'); >> sql>insert into s2 values (6,'s6'); >> sql>select s1.id from s1, s2 where s1.v between s2.v and s2.v||'c' ; >> !MALException:algebra.rangejoin:GDKerror >> !ERROR: BATrangejoin: type not implemented > This is an instance of the more general theta join. We will have a look into the issue.. It certainly should not have falled back to an error this low in the system. > whereas > >> sql>select s1.id from s1, s2 where s2.id=2 and s1.id between s2.id >> and s2.id+1 ; >> +----+ >> | id | >> +====+ >> | 2 | >> | 3 | >> +----+ > > Thanks! >

1 0

[MonetDB-users] Two questions with XQuery
by Andreas Meinl 17 Jul '08

17 Jul '08

Hi there, I'm currently porting some XQuery scripts to MonetDB/XQuery, and encountered two maybe simple obstacles. The first one is that I can't use fn:collection, but instead always have to apply pf:collection. The online documentation [1] states both alternatives should be practicable. For example in eXist and Sedna I can write: let $a := fn:collection("samples") return $a//id But this does not work for MonetDB/XQuery! Here I have to use: let $a := pf:collection("samples")/child::document-node() return $a//id My second question is that I cannot execute this kind of queries, which runs well in eXist and Sedna: <average> { fn:round(fn:avg( for $doc in fn:collection("samples") return $doc/example/@sum )) } </average> For MonetDB/XQuery I have to pull out pf:collection and add a second return statement: let $col := pf:collection("samples")/child::document-node() return <average> { fn:round(fn:avg( for $doc in $col return $doc/example/@sum )) } </average> So what am I doing wrong? Does MonetDB/XQuery by any chance comply more with any standards than eXist and Sedna do? Or ist fn:collection just not implemented in the current release of MonetDB/XQuery? I would be glad if someone could give me a hint! It's so confusing... Thank you! Regards, Andreas Meinl [1] http://monetdb.cwi.nl/projects/monetdb/XQuery/QuickTour/DOCMGT/ index.html#openall

4 10

[MonetDB-users] Compile: buildtools -> hard, MonetDB -> fails
by Stefan Klinger 17 Jul '08

17 Jul '08

Hello, it took me quite a while to compile the build tools, since the make process failed without any meaningful error messages, but I cannot build Monetdb. I'd expect the configure script to fail with a descriptive error message if required packages (in my case 'libtool', 'flex') are missing. > prefix="${HOME}/opt/MonetDB" > cvs -d:pserver:anonymous@monetdb.cvs.sourceforge.net:/cvsroot/monetdb checkout buildtools [...] > pushd buildtools > ./bootstrap [...] ./bootstrap: line 29: libtoolize: command not found [...] The message about a missing 'libtoolize' is easily overlooked, and configure was running fine anyway: > ./configure --prefix="${prefix}" [...] But the make fails > make [...] /tmp/sk/2/buildtools/mel/mel.yy:331: undefined reference to `mel_yyerror(char const*)' [...] make: *** [all] Error 2 In case you don't know that 'yyerror' hints at flex, you're lost. Well, I figured that out and went on: > make install > popd Then I tried to continue with MonetDB: > cvs -d:pserver:anonymous@monetdb.cvs.sourceforge.net:/cvsroot/monetdb checkout MonetDB > pushd MonetDB > ./bootstrap automake 1.10.1 is 1.7 or newer. Good. autoconf 2.61 is 2.57 or newer. Good. libtool 1.5.26Debian1.5.26-4 is 1.4 or newer. Good. Python 2.5.2 is 2.0.0 or newer. Good. Traceback (most recent call last): File "/home/sk/opt/MonetDB/bin/autogen.py", line 29, in <module> from autogen import am ImportError: No module named autogen Now this leaves me completely clueless... Any help wouldbe appreciated. Kind regards, Stefan -- Stefan Klinger o/klettern /\/ bis zum send plaintext only - max size 32kB - no spam \ Abfallen http://stefan-klinger.de

3 3