Re: [sqlite] Confused about Multiple Indexes

Hi Max Thanks for the reply. On 26/11/2010 7:11 PM, Max Vlasov wrote: > Mohit said that he uses a someone's db, so I can imagine a possibility that > with two indexes ... > CREATE INDEX IDX1 on tx(name ASC); > CREATE INDEX IDX2 on tx(type, name ASC); > ... the creator of database wanted to

Re: [sqlite] Confused about Multiple Indexes

On 26/11/2010 6:34 PM, Swithun Crowe wrote: > Hello > > CREATE INDEX idx ON tx(name ASC, type); > > With the columns in this order (name followed by type), the index will be > used for queries which have either just name, or both name and type in > their WHERE clauses. Swithun, thank you very

Re: [sqlite] How do I get expression from column (smart folder orfilter implementation)

Yuzem wrote: > Thanks for the answer. > I have another problem: > I decided to use a view to do this, I will have to drop/create the view > every time filters is updated but I don't know how to use multiple selects. > I want to do something like this: > > CREATE VIEW

[sqlite] Efficient Paths in Practice Through a 21-bit Codespace (Re: SQLITE 3.7.3 bug report (shell) - output in column mode does not align UTF8-strings correctly)

On Fri, 26 Nov 2010 08:40:42 -0500, Jean-Christophe Deschamps wrote: > At 14:26 26/11/2010, [Samuel Adam ] wrote: > >> N.b., there is a severe bug (pointers calculated based on truncated >> 16-bit >> values above plane-0) in a popular Unicode-properties

Re: [sqlite] How do I get expression from column (smart folder orfilter implementation)

Thanks for the answer. I have another problem: I decided to use a view to do this, I will have to drop/create the view every time filters is updated but I don't know how to use multiple selects. I want to do something like this: CREATE VIEW movies_filters AS SELECT 'rated',movie FROM movies

Re: [sqlite] SQLITE 3.7.3 bug report (shell) - output in column mode does not align UTF8-strings correctly

At 14:26 26/11/2010, you wrote: >N.b., there is a severe bug (pointers calculated based on truncated >16-bit >values above plane-0) in a popular Unicode-properties SQLite extension. >The extension only attempts covering a few high-plane characters—if >memory >serves, three of thhem in array

Re: [sqlite] WAL, durability, and synchronous=NORMAL

Hi, >>> In WAL mode with synchronous=NORMAL, when the user commits >>> a transaction, it is written into the WAL file. No sync >>> until a checkpoint happens. So if the power fails, you might >>> lose all the transactions that have been written into the WAL >>> file. >> Ahha. That explains it.

Re: [sqlite] SQLITE 3.7.3 bug report (shell) - output in column mode does not align UTF8-strings correctly

On Fri, 26 Nov 2010 07:27:02 -0500, Simon Slavin wrote: > On 26 Nov 2010, at 6:52am, Niklas Bäckman wrote: > >> You are right of course. The shell should not count code points, but >> graphemes. >> >> http://unicode.org/faq/char_combmark.html#7 >> [snip] >> Or would it

Re: [sqlite] glob and like not using index in newest sqlite

On Fri, Nov 26, 2010 at 6:23 AM, Spiros Ioannou wrote: > Hello, I recently updated from 3.4.2 to 3.7.3 and the glob and like > operators are now not using the indexes. > My table "dict" has a column named "word" without defined type, containing > utf-8 words. The index

Re: [sqlite] Efficient hash lookup table emulation with SQLite - how?

sqlite> CREATE TABLE t (key TEXT PRIMARY KEY, count INTEGER); sqlite> INSERT OR REPLACE INTO t VALUES("key1",coalesce((SELECT count FROM t WHERE key="key1"),0)+1); sqlite> SELECT * FROM t; key1|1 sqlite> INSERT OR REPLACE INTO t VALUES("key1",coalesce((SELECT count FROM t WHERE

Re: [sqlite] Efficient hash lookup table emulation with SQLite - how?

On 25 Nov 2010, at 2:07pm, Alexei Alexandrov wrote: > * There is a trace file which contains a big number of some objects. Each > object has a number of fields which constitute its primary key (PK). > * The objects are loaded into a table which has a number of PK columns (mapped > from the

[sqlite] glob and like not using index in newest sqlite

Hello, I recently updated from 3.4.2 to 3.7.3 and the glob and like operators are now not using the indexes. My table "dict" has a column named "word" without defined type, containing utf-8 words. The index is: CREATE INDEX wordidx on dicts (word); explain query plan SELECT * from dicts where

Re: [sqlite] WAL, durability, and synchronous=NORMAL

On 11/25/2010 09:04 PM, Simon Slavin wrote: > > On 25 Nov 2010, at 2:00pm, Dan Kennedy wrote: > >> In WAL mode with synchronous=NORMAL, when the user commits >> a transaction, it is written into the WAL file. No sync >> until a checkpoint happens. So if the power fails, you might >> lose all the

[sqlite] Efficient hash lookup table emulation with SQLite - how?

Hi, I'm trying to solve efficiently the following task with SQlite: * There is a trace file which contains a big number of some objects. Each object has a number of fields which constitute its primary key (PK). * The objects are loaded into a table which has a number of PK columns (mapped from

Re: [sqlite] SQLITE 3.7.3 bug report (shell) - output in column mode does not align UTF8-strings correctly

On 26 Nov 2010, at 6:52am, Niklas Bäckman wrote: > You are right of course. The shell should not count code points, but > graphemes. > > http://unicode.org/faq/char_combmark.html#7 > > I guess that this probably falls out of the "lite" scope of SQLITE though? There is absolutely no way

Re: [sqlite] [ERROR] Failed compound query with simple expr in ORDER BY

On 26-11-10 13:02, Drake Wilson wrote: > Quoth luuk34, on 2010-11-26 12:49:53 +0100: >> The extra column seems to work, >> but i thought this should work too? > I would imagine so, at first glance. > >> But the ORDER is wrong... > How? The example you provided seems properly

Re: [sqlite] [ERROR] Failed compound query with simple expr in ORDER BY

Quoth luuk34 , on 2010-11-26 12:49:53 +0100: > The extra column seems to work, > but i thought this should work too? I would imagine so, at first glance. > But the ORDER is wrong... How? The example you provided seems properly sorted. > sqlite> SELECT a,b FROM ( > ...>

Re: [sqlite] [ERROR] Failed compound query with simple expr in ORDER BY

On 26-11-10 12:37, Drake Wilson wrote: > Quoth Waldemar Derr, on 2010-11-26 12:24:27 +0100: >> --Don't working: (Error: 1st ORDER BY term does not match any column in the >> result set.) >> >> SELECT * FROM OrderTest WHERE Price< 200 >> UNION >> SELECT * FROM OrderTest WHERE

Re: [sqlite] [ERROR] Failed compound query with simple expr in ORDER BY

Quoth Waldemar Derr , on 2010-11-26 12:24:27 +0100: > --Don't working: (Error: 1st ORDER BY term does not match any column in the > result set.) > > SELECT * FROM OrderTest WHERE Price < 200 > UNION > SELECT * FROM OrderTest WHERE Price > 500 > ORDER BY Price IS 0, Price; >From

[sqlite] [ERROR] Failed compound query with simple expr in ORDER BY

Hello all readers, following a complete example for reproduce this behaviour (SQLite 3.7.3): CREATE TABLE IF NOT EXISTS OrderTest (ID AUTOINC, Price FLOAT); REPLACE INTO OrderTest VALUES (1, 50); REPLACE INTO OrderTest VALUES (1, 50); REPLACE INTO OrderTest VALUES (2, 75); REPLACE INTO

Re: [sqlite] Confused about Multiple Indexes

On Fri, Nov 26, 2010 at 1:34 PM, Swithun Crowe < swit...@swithun.servebeer.com> wrote: > Hello > > MS> The second index should be: > MS> CREATE INDEX IDX2 on tx(type, name ASC); > > MS> What I had meant to ask was whether there is any benefit in having two > MS> indexes when one of the indexes is

Re: [sqlite] Confused about Multiple Indexes

Hello MS> The second index should be: MS> CREATE INDEX IDX2 on tx(type, name ASC); MS> What I had meant to ask was whether there is any benefit in having two MS> indexes when one of the indexes is exactly within the other. MS> IDX1 is index on 'name ASC' while IDX2 is an index on 'type, name

Re: [sqlite] Assertion failure in SQLite 3.7.3 (new vs. 3.6.23.1)

On 25 Nov 2010, at 14:06, Dan Kennedy wrote: > On 11/25/2010 03:45 PM, Philip Graham Willoughby wrote: >> Hi all, >> >> I'm noticing a new failure with SQLite 3.7.3 as compared to the previous >> version I was using, 3.6.23.1. > > Are you able to share the database and the query that causes >

Re: [sqlite] Confused about Multiple Indexes

Hi Swithun Thank you for your reply. I'm sorry I was simplifying the schema when I sent it out. On 26/11/2010 5:35 PM, Swithun Crowe wrote: > Hello > > MS> CREATE TABLE tx (name TEXT, type INTEGER, seq INTEGER, seq_record TEXT, > MS> ...); > > MS> CREATE INDEX IDX1 on tx(name ASC); > MS>

Re: [sqlite] Confused about Multiple Indexes

Hello MS> CREATE TABLE tx (name TEXT, type INTEGER, seq INTEGER, seq_record TEXT, MS> ...); MS> CREATE INDEX IDX1 on tx(name ASC); MS> CREATE INDEX IDX2 on tx(type, search_name ASC); The two indexes cover different columns, so they do different things. The indexes you need depend on the

Re: [sqlite] import and convert table to schema?

Wouter Overmeire writes: [...] > If I compare the speed of doing this for one file to an import statement in sqlite itself there is a big > difference, '.import' is much faster [...] Use transactions like this (will be much faster): BEGIN TRANSACTION; INSERT ; INSERT ;

[sqlite] import and convert table to schema?

So far I haven`t found a solution for the following problem. I have a number of txt files with tab spaced data. Each file`s row looks like: ... In one file N is the same for all rows, but between files N can have different values. So each row represents an array of real values of length N,