May 2008 - users-list

[MonetDB-users] mclient command line history question
by Drew Sky 12 May '08

12 May '08

Hello, I am having trouble using the command line history feature of mclient. After I run a query at the command line, I can't re-use the same query by pressing the up-arrow. From documentation, it seems like I should be able to do this. Is this a feature I should have compiled in, when building from source code? When I press the up-arrow after a query I get sql>^[[A I've tried adding the --history and --interactive options but it doesn't help. I've also tried changing the terminal setting to other modes (I'm using linux) but this doesn't help either. Can anyone help me out? Thanks, Andrew ____________________________________________________________________________________ Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ

2 1

[MonetDB-users] php5 and windows
by Michiel Kamermans 08 May '08

08 May '08

Hi all, I've been hearing interesting things about MonetDB so I figured I'd see how it performed with the data that my website uses (several million cross-referencing records of Japanese/English dictionary data) by installing it for use with PHP5... but I ran into a problem come actual PHP interfacing. Which steps must be taken to get PHP5 to properly interface with MonetDB on a windows machine? The online documentation (http://homepages.cwi.nl/~mk/MonetDB/The-PHP-Library.html) has a wonderful, if slightly useless, "TODO" for the windows instruction section, and the self-help approach of simply placing the php_monetdb.dll file in PHP's extensions directory and adding an "extension=php_monetdb.dll" to the PHP.ini file leads to PHP throwing up a terminal error when it then tries to run. Any help is appreciated. - Mike Kamermans

1 0

[MonetDB-users] Limit statements
by Henrik Vendelbo 07 May '08

07 May '08

The section on sorting and limit 3.1.7.2 Select lists, distinct, sorting, limit Is very brief and doesn't seem to describe limit/sort, does this mean that LIMIT/OFFSET is supported or that ROW_NUMBER should be used? Henrik

2 1

[MonetDB-users] MonetDB/XQuery not coming back
by Richard Eckart 06 May '08

06 May '08

Hi there, I am looking again into MonetDB/XQuery. A colleague of mine ran a little test on MonetDB/XQuery and made the experience that when starting the server and importing some documents, a query is possible. But when killing the server (running as deamon - killed with CTRL-C), restarting it the same query never comes back. He tried this under FreeBSD and I am experiencing the same problem now on Mac OS X 10.4.11. I turned on debug logging (level 255) but did not see anything that would help me. The only thing I really did see were lines like this while the client was hanging and the CPU running at 100% load. # batstamp(-1418) = -292 # batstamp(1663) = -84581 # batstamp(1662) = -84582 # batstamp(1662) = -84583 # batstamp(-1659) = -84584 # batstamp(1662) = -84585 # batstamp(1449) = 0 # batstamp(1633) = 0 Killing the client the server would still be running producing these lines with the numbers keeping on sinking (getting more negative). After killing the server (CTRL-C) and restarting it I can browse the database using the web interface, but whenever I run the query it goes bye-bye. The query that ran successfully directly after import but not after restart is this: > declare namespace leveler="urn:xmlns:sfb441:leveler"; > > let $lexical := > pf:documents()[@collection eq 'tuebadz' and > fn:starts-with(., 'Corpus-Lexical')]/fn:string(text > ()) > > let $verbs := ( > for $doc in $lexical > return fn:doc($doc)//tok[fn:starts-with(fn:string(pos/ > @leveler:text), "V")] > ) > > return element results { > attribute matches { count($verbs) }, > for $verb in $verbs/fn:string(orth) > order by $verb > return element verb { $verb } > } If I slightly modify the query removing /fn:string(orth), the query runs well. I played around a little and whenever I try to access the "orth" element in any way it goes bye-bye. The "tok" elements have another child called "pos" which I can access just fine. The difference is that "orth" contains a text node while "pos" is an empty tag. Example: > <tok id="s_7_n_15"> > <orth>seien</orth> > <pos xmlns:leveler="urn:xmlns:sfb441:leveler" leveler:text="VAFIN" > func="HD" /> > </tok> Do you have any ideas how to fix/handle this problem? Thank you very much. Cheers, Richard

1 0

[MonetDB-users] Tutorial for database scripting language MIL?
by jz＠uva 06 May '08

06 May '08

Hi all, Does someone have a tutorial for the database scripting language MIL? I am currently checking pftijah.mx, but I am wondering whether there is a better option than trying to reverse engineer things. Thanks, junte

3 2

[MonetDB-users] MySQLError ?
by Henrik Vendelbo 06 May '08

06 May '08

I was having a look at the __init__.py module header for MonetSQLdb, and saw the following: __all__ = [ 'BINARY', 'Binary', 'Connect', 'Connection', 'DATE', 'Date', 'Time', 'Timestamp', 'DateFromTicks', 'TimeFromTicks', 'TimestampFromTicks', 'DataError', 'DatabaseError', 'Error', 'FIELD_TYPE', 'IntegrityError', 'InterfaceError', 'InternalError', 'MySQLError', 'NULL', 'NUMBER', 'NotSupportedError', 'DBAPISet', What is the reference to MySQLError doing in the code? I suppose it is just ignored by the interpreter but surely it is a mistake. Warning, OperationalError, and ProgrammingError are not exported by the module. Henrik

2 1

[MonetDB-users] Fwd: [ monetdb-Bugs-1957794 ] How to debug on Windows OS?
by Lefteris 05 May '08

05 May '08

Please read carefully the following comments to avoid opening a third bug with the same question. thank you ---------- Forwarded message ---------- From: SourceForge.net <noreply(a)sourceforge.net> Date: Mon, May 5, 2008 at 10:30 AM Subject: [ monetdb-Bugs-1957794 ] How to debug on Windows OS? To: noreply(a)sourceforge.net Bugs item #1957794, was opened at 2008-05-05 09:50 Message generated for change (Comment added) made by lsidir You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=482468&aid=1957794&group_… Please note that this message will contain a full copy of the comment thread, including the initial issue submission, for this request, not just the latest update. Category: None Group: None >Status: Closed >Resolution: Rejected Priority: 5 Private: No Submitted By: lxwandy (lxwandy) Assigned to: Nobody/Anonymous (nobody) Summary: How to debug on Windows OS? Initial Comment: Hi.. I want to know about the method of debugging MonetDB on Windows OS. After compiering MonetDB use "namke" , how can I debug it. Is special debug tool needed? ---------------------------------------------------------------------- >Comment By: Lefteris Sidirourgos (lsidir) Date: 2008-05-05 10:30 Message: Logged In: YES user_id=1856546 Originator: NO Hi, this question will be answered if you post it to the monetdb-user mailing list. See http://monetdb.cwi.nl/projects/monetdb/Development/MailChannels/index.html about our mailing lists. This is the bugtracker, a tool to submit bugs rather than questions. ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=482468&aid=1957794&group_…

1 0

[MonetDB-users] Django Backend: SQL column types
by Henrik Vendelbo 04 May '08

04 May '08

Hi guys, I am jumping straight into the deep end with Monet. My first task is to implement a backend for Django written in Django. I need to map the field types to SQL column types, and that is for the most part easy, yet a few questions: 1) Is 'integer' accepted, or do I have to use 'int' 2) How do I specify a floating point with specified max_digits and decimal_places? I didn't see support for numeric in the docs. 3) How do I specify unsigned integers? unsigned or check expression? Is there a text field, or should I use varchar/clob ? These are my definitions as they stand so far: DATA_TYPES = { 'AutoField': 'serial', 'BooleanField': 'boolean', 'CharField': 'varchar(%(maxlength)s)', 'CommaSeparatedIntegerField': 'varchar(%(maxlength)s)', 'DateField': 'date', 'DateTimeField': 'timestamp', 'FileField': 'varchar(100)', 'FilePathField': 'varchar(100)', 'FloatField': 'numeric(%(max_digits)s, %(decimal_places)s)', 'ImageField': 'varchar(100)', 'IntegerField': 'int', 'IPAddressField': 'char(15)', 'ManyToManyField': None, 'NullBooleanField': 'bool', 'OneToOneField': 'int', 'PhoneNumberField': 'varchar(20)', 'PositiveIntegerField': 'integer unsigned', # TODO how should this be handled??? 'PositiveSmallIntegerField': 'smallint unsigned', # TODO how should this be handled??? 'SlugField': 'varchar(%(maxlength)s)', 'SmallIntegerField': 'smallint', 'TextField': 'text', 'TimeField': 'time', 'USStateField': 'varchar(2)', } Thanks, Henrik

3 2

[MonetDB-users] Performance issues with queries in SQL
by Nils Grimsmo 01 May '08

01 May '08

I have tables with around 12 million rows and large primary keys. I find the performance for various queries to be rather strange. * Why is Q2 (see below) so much slower than Q1? It is actually faster for me to use Q1 and do the rest of the matching myself. * Why is Q3 so immensely much faster than Q1? I understand that the first part of the primary key is selective in Q3. Do I understand right if Q1 scans until "type" matches, then scans until "name" matches, then scans until "value" matches? No indexes are put on disk, right? Then the matching for Q3 should be scan until "value" matches, which shouldn't be more than at most three times faster, right (if three columns are read instead of one for Q1)? I do Q1 twice to look for cash effects, but none are seen... Could some columns from the table "value" be cached and kept, without giving room for those from "type_name_value"? * Q4 has the same performance as Q1, as expected. * Why isn't Q5 faster than Q6? 82% of the rows have type=3. Fewer should be scanned to find that "!!foo" doesn't exist than for "~~foo", as "!" has a low ASCII value, while "~" is high. (See COUNT queries at the bottom.) Klem fra Nils Q1: SELECT * FROM type_name_value WHERE type = 3 AND name = 'author' AND value = 'Jurgen Annevelink' Q2: SELECT * FROM type_name_value WHERE type = 3 AND name = 'author' AND value = 'Jurgen Annevelink' AND doc = 1 AND nenc = '1.1.1.1' AND pos = 4 Q3: SELECT * FROM value WHERE value = 'Jurgen Annevelink' AND type = 3 AND name = 'author' Q4: SELECT * FROM type_name_value WHERE type = 3 AND name = 'author' AND value = '__bar' Q5: SELECT * FROM type_name_value WHERE type = 3 AND name = '!!foo' AND value = '__bar' Q6: SELECT * FROM type_name_value WHERE type = 3 AND name = '~~foo' AND value = '__bar' Q7: SELECT * FROM type_name_value WHERE type = 3000 AND name = '__foo' AND value = '__bar' See runs below. Number of rows SELECT COUNT(type) FROM type_name_value % nilsgri.type_name_value # table_name % count_no_nil_type # name % int # type % 8 # length [ 12165109 ] Query time: 0.066192 SELECT COUNT(type) FROM value % nilsgri.value # table_name % count_no_nil_type # name % int # type % 8 # length [ 12165109 ] Query time: 0.058111 The PRIMARY KEY of type_name_value is (type,name,value,doc,nenc,pos) The PRIMARY KEY of value is (value,doc,nenc,pos) Q1: Search 3 first, match 3 first. SELECT * FROM type_name_value WHERE type = 3 AND name = 'author' AND value = 'Jurgen Annevelink' % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 6, 17, 1, 12, 8, 3, 5 # length [ 3, "author", "Jurgen Annevelink", 1, "1.1.1.1", 4, 5, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.23579.2.1", 724888, 56, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.2487.3.1", 81996, 5, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.42.2.1", 1071, 5, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.533986.2.1", 17294725, 56, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.576139.3.1", 18745432, 56, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.944085.1.1", 30104242, 107, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.99173.2.1", 3191470, 56, true ] Query time: 1.643438 Q1: Repeat to check for disk cache effects. SELECT * FROM type_name_value WHERE type = 3 AND name = 'author' AND value = 'Jurgen Annevelink' % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 6, 17, 1, 12, 8, 3, 5 # length [ 3, "author", "Jurgen Annevelink", 1, "1.1.1.1", 4, 5, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.23579.2.1", 724888, 56, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.2487.3.1", 81996, 5, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.42.2.1", 1071, 5, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.533986.2.1", 17294725, 56, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.576139.3.1", 18745432, 56, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.944085.1.1", 30104242, 107, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.99173.2.1", 3191470, 56, true ] Query time: 1.615900 Q2: Search entire, match entire. SELECT * FROM type_name_value WHERE type = 3 AND name = 'author' AND value = 'Jurgen Annevelink' AND doc = 1 AND nenc = '1.1.1.1' AND pos = 4 % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 6, 17, 1, 7, 1, 1, 5 # length [ 3, "author", "Jurgen Annevelink", 1, "1.1.1.1", 4, 5, true ] Query time: 11.073827 Q3: Use table value instead. SELECT * FROM value WHERE value = 'Jurgen Annevelink' AND type = 3 AND name = 'author' % nilsgri.value, nilsgri.value, nilsgri.value, nilsgri.value, nilsgri.value, nilsgri.value, nilsgri.value, nilsgri.value # table_name % value, doc, nenc, pos, type, name, pathid, complete # name % clob, int, varchar, int, int, varchar, int, boolean # type % 17, 1, 12, 8, 1, 6, 3, 5 # length [ "Jurgen Annevelink", 1, "1.1.1.1", 4, 3, "author", 5, true ] [ "Jurgen Annevelink", 1, "1.23579.2.1", 724888, 3, "author", 56, true ] [ "Jurgen Annevelink", 1, "1.2487.3.1", 81996, 3, "author", 5, true ] [ "Jurgen Annevelink", 1, "1.42.2.1", 1071, 3, "author", 5, true ] [ "Jurgen Annevelink", 1, "1.533986.2.1", 17294725, 3, "author", 56, true ] [ "Jurgen Annevelink", 1, "1.576139.3.1", 18745432, 3, "author", 56, true ] [ "Jurgen Annevelink", 1, "1.944085.1.1", 30104242, 3, "author", 107, true ] [ "Jurgen Annevelink", 1, "1.99173.2.1", 3191470, 3, "author", 56, true ] Query time: 0.000477 Q4: Search 3 first, match 2 first. SELECT * FROM type_name_value WHERE type = 3 AND name = 'author' AND value = '__bar' % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 0, 0, 1, 0, 1, 1, 5 # length Query time: 1.617259 Q5: Search 3 first, match 1 first. '!' early in alphabet. SELECT * FROM type_name_value WHERE type = 3 AND name = '!!foo' AND value = '__bar' % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 0, 0, 1, 0, 1, 1, 5 # length Query time: 0.530727 Q6: Search 3 first, match 1 first. '~' late in alphabet. SELECT * FROM type_name_value WHERE type = 3 AND name = '~~foo' AND value = '__bar' % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 0, 0, 1, 0, 1, 1, 5 # length Query time: 0.534592 Q7: Search 3 first, match 0 first. SELECT * FROM type_name_value WHERE type = 3000 AND name = '__foo' AND value = '__bar' % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 0, 0, 1, 0, 1, 1, 5 # length Query time: 0.000407 Number of rows with type and name mathcing. SELECT COUNT(type) FROM type_name_value WHERE type = 3 AND name = 'author' % nilsgri.type_name_value # table_name % count_no_nil_type # name % int # type % 7 # length [ 2730959 ] Query time: 0.683863 Number of rows with type mathcing. SELECT COUNT(type) FROM type_name_value WHERE type = 3 % nilsgri.type_name_value # table_name % count_no_nil_type # name % int # type % 8 # length [ 10020569 ] Query time: 0.032573 SELECT COUNT(type) FROM type_name_value WHERE type = 3 AND name < '!!foo' % nilsgri.type_name_value # table_name % count_no_nil_type # name % int # type % 1 # length [ 0 ] Query time: 0.161186 SELECT COUNT(type) FROM type_name_value WHERE type = 3 AND name < '~~foo' % nilsgri.type_name_value # table_name % count_no_nil_type # name % int # type % 8 # length [ 10020569 ] Query time: 0.341732 Number of rows with type mathcing. real 0m18.372s user 0m0.005s sys 0m0.002s

2 3