gmane.comp.db.sqlite.general

Re: query optimization for inner table join

Jos van den Oever — 2008-12-01T16:36:47

2008/12/1 Igor Tandetnik public.gmane.org>: Thank you very much, Igor. I would have not thought of that. This is a nicely predictable single linear scan. Still not awfully fast, but it will have to do. Using an index on map(n,m) seems faster. This may be because the m values are in the index and there is no need to access the table. Cheers, Jos _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Missing TCL/TEA source code

Ribeiro, Glauber — 2008-12-01T16:23:50

I think the source code for SQLite with TCL for version 3.6.6.2 is missing from sqlite.org. Thanks, glauber _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: SQLite aggregate functions by Tcl

Alexey Pechnikov — 2008-12-01T16:22:04

Hello! В сообщении от Monday 01 December 2008 18:16:04 D. Richard Hipp написал(а): Do you think that this feature is not better way? May be aggregate operations is more preferable doing in application layer? Best regards, Alexey. _______________________________________________ sqlite-users mailing list sqlite-users< at >sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: Journal files

D. Richard Hipp — 2008-12-01T15:55:52

On Dec 1, 2008, at 10:50 AM, Eric Minbiole wrote: And in particular http://www.sqlite.org/pragma.html#pragma_journal_mode D. Richard Hipp drh-X1OJI8nnyKUAvxtiuMwx3w< at >public.gmane.org _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: Journal files

Eric Minbiole — 2008-12-01T15:50:59

http://www.sqlite.org/pragma.html _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Journal files

Stephen Abbamonte — 2008-12-01T15:46:21

Hello all, I am looking for a way to completely turn off the creation of journal files. Any help is much appreciated. Thanks. _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: SQLite aggregate functions by Tcl

D. Richard Hipp — 2008-12-01T15:16:04

On Dec 1, 2008, at 9:56 AM, Alexey Pechnikov wrote: The current TCL interface for SQLite does not provide the ability to add aggregate functions written in TCL. So in that sense, it is not possible. However, the TCL interface could be extended to add this capability. D. Richard Hipp drh-X1OJI8nnyKUAvxtiuMwx3w< at >public.gmane.org _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

SQLite aggregate functions by Tcl

Alexey Pechnikov — 2008-12-01T14:56:26

Hello! Is it possible? Best regards, Alexey. _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: Arranging of ids in Sqlite3

Alexey Pechnikov — 2008-12-01T14:23:30

Hello! В сообщении от Wednesday 26 November 2008 13:57:02 Nikhil Kansal написал(а): You can get rowid for last inserted row as select last_insert_rowid(); Best regards, Alexey. _______________________________________________ sqlite-users mailing list sqlite-users< at >sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: query optimization for inner table join

Igor Tandetnik — 2008-12-01T13:19:36

"Jos van den Oever" public.gmane.org> wrote in message news:c2dbc4260812010126o8eb189ax9bdafdf662fea04a-JsoAwUIsXosN+BqQ9rBEUg< at >public.gmane.org Try this: select n from map group by n having count(case when m=3 then 1 else null end) != 0 and count(case when m=5 then 1 else null end) != 0 and count(case when m=7 then 1 else null end) = 0; Having an index on map(n) should speed it up. Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: Why must one write a mini SQL parser to read thecolumnnames?

Ben Harper — 2008-12-01T13:10:24

Thanks, that's perfect! On Mon, Dec 1, 2008 at 3:06 PM, Igor Tandetnik public.gmane.org> wrote: _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: Why must one write a mini SQL parser to read thecolumnnames?

Igor Tandetnik — 2008-12-01T13:06:46

"Ben Harper" public.gmane.org> wrote in message news:6def3e7b0812010502x32fb4d1ap96a2a0a1f278ff9d-JsoAwUIsXosN+BqQ9rBEUg< at >public.gmane.org Have you looked at PRAGMA table_info? Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Why must one write a mini SQL parser to read the columnnames?

Ben Harper — 2008-12-01T13:02:31

To the best of my findings, it seems to me that one needs to write a mini SQL parser in order to read the 'sql' field from sqlite_master, in order to discover the fields in an Sqlite table. Is this really a necessary design? Would it not be better if sqlite3_table_column_metadata had a mode that could enumerate the columns in a table? Or am I missing something obvious? _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Partial search with fts

Rael Bauer — 2008-12-01T11:48:01

Previously someone advised that I use the "*" char to achieve partial search results with fts. eg ver* will match version. This works ok, but only for end parts of a word. Is there anyway to get partial matches for beginning or middle parts of a word? e.g. *sion - to match version or *si* to match version Thanks Rael _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: please help with NULL and NOTHING?

Dan — 2008-12-01T11:18:10

If you are sure there is at most one entry in bilance1 where the account and year match then you could do this: SELECT coalesce( (SELECT dbs from bilance1 where account='13100' and pYear=?), 0 ) AS summadeb; On Dec 1, 2008, at 3:26 PM, aivars wrote: _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

query optimization for inner table join

Jos van den Oever — 2008-12-01T09:26:54

Hi all, I've trouble optimizing for an N:M mapping table. The schema of the table is this: CREATE TABLE map (n INTEGER NOT NULL, m INTEGER NOT NULL); I want to retrieve a list of n filtered on the presence of certain values of m, e.g. give me all n for which there is an m = 3 and m = 5, but no m = 7. A naive query would look like this: SELECT a.n FROM map a, map b, map c WHERE a.n = b.n AND a.n = c.n AND a.m = 3 AND b.m = 5 AND c.id not in (select id from map where c.m = 7); This can be slow, even for the more simple case with only positive selection: SELECT a.n FROM map a, map b WHERE a.n = b.n AND a.m = 3 AND b.m = 5; And this variation does not make it a lot faster: SELECT n FROM map WHERE m = 3 INTERSECT SELECT n FROM map where m = 5; There are about a million entries in the table map and want to increase to about 10 million. The current indexes are CREATE INDEX map_n ON map(n); CREATE INDEX map_m ON map(n,m); Is there a cleverer way of doing these queries? The fraction of n's that has a particular m can be anywhere between 0 and 1. Cheers, Jos _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: please help with NULL and NOTHING?

Mohd Radzi Ibrahim — 2008-12-01T09:07:03

You should handle this in your client program. Even in MS SQL or Oracle, it will not return any resultset. If you were to use left join, you may get it as NULL for any missing links. rgd, Radzi. ----- Original Message ----- From: "aivars" public.gmane.org> To: public.gmane.org> Sent: Monday, December 01, 2008 4:26 PM Subject: [sqlite] please help with NULL and NOTHING? _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

please help with NULL and NOTHING?

aivars — 2008-12-01T08:26:30

Hello, The simple query is like this: SELECT dbs as summadeb from bilance1 where account='13100' and pYear=?; Account number 13100 is not present in the table bilance1 when pYear=2005 and it should be like this and therefore dbs is also not present. Other years account number 13100 is present and query works OK. When the query is run with a parameter pYear='2005' it returns nothing - the resultset is empty or nothing? (I am doing it from python25) Even if I change the query to : SELECT coalesce(dbs,0) as summadeb from bilance1 where account='13100' and pYear='2005'; it still returns NOTHING, not 0. The same happens also on MS SQLServer 2005 so I think it should be like that according to sql standards. If there is no account number 13100 in the table then the result is NOTHING, not NULL or 0. Strictly speaking I think also the value of 0 is not correct in this case but I would like to have it. It seams that coalesce can handle NULL not NOTHING. My question is: Is there an SQL way to handle above query to return 0 or should I handle this in client program (python)? (presently I get TypeError: 'NoneType' object is unsubscriptable) Using sqlite 3.6.2, python2.5 and Windows XP Thanks in advance Aivars _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: Select Limit issues

Igor Tandetnik — 2008-12-01T04:13:00

"Webmaster" public.gmane.org> wrote in message news:002a01c95368$eb234070$6501a8c0< at >Netactix I meant that you would put an actual number in place of :X. Or else, that you would figure out how to work with parameterized queries in whatever environment you use SQLite with. Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: Select Limit issues

P Kishor — 2008-12-01T04:04:22

This kind of problem, in my opinion, is more suited for the application rather than SQL. Even if you get this to work, there are several issues... what happens if your dataset changes? what happens if there are not enough Whites or Blues before or after the first Red? What happens if a White or a Blue gets intermingled/interchanged with each other? How do you maintain the order of the list that you get back? In your original data, Field1 is definitely not the ordering column... see the following rows 1218.90,White,12:00 18516.40,Blue,12:00 1255.33,White,12:00 927.46,White,12:00 etc. etc. That said, see http://www.perlmonks.org/?node_id=726949 for a couple of three solutions more suitably belonging in the application domain rather than the SQL domain. On 11/30/08, Webmaster public.gmane.org> wrote:

Re: Select Limit issues

Webmaster — 2008-12-01T03:57:17

Ok, Did that but now it doesn't run? select * from ( select * from table1 where Field2='Red' order by Field1 limit 1 offset :X) union all select * from ( select * from table1 where Field2='White' and Field1 < (select Field1 from table1 where Field2='Red' order by Field1 limit 1) order by Field1 desc limit 4 ) union all select * from ( select * from table1 where Field2='Blue' and Field1 < (select Field1 from table1 where Field2='Red' order by Field1 limit 1) order by Field1 desc limit 1 ) union all select * from ( select * from table1 where Field2='White' and Field1 > (select Field1 from table1 where Field2='Red' order by Field1 limit 1) order by Field1 limit 4 ) union all select * from ( select * from table1 where Field2='Blue' and Field1 > (select Field1 from table1 where Field2='Red' order by Field1 limit 1) order by Field1 limit 1 ) order by Field1; I take it I am missing something here? -----Original Message----- From: sqlite-users-bounces-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org [mailto:sqlite-users-bounces-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org] On Behalf Of Igor Tandetnik Sent: Sunday, November 30, 2008 9:15 PM To: sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org Subject: Re: [sqlite] Select Limit issues "Webmaster" public.gmane.org> wrote in message news:000c01c9532d$c4919a00$6501a8c0< at >Netactix Replace all subqueries that look like this: (select Field1 from table1 where Field2='Red' order by Field1 limit 1) with something like this: (select Field1 from table1 where Field2='Red' order by Field1 limit 1 offset :X) and vary X in the loop, starting from 0, until you get an empty resultset. Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users