Re: [sqlite] SQL query on sort order

Kishor: Collation is okay for case insensitivity but since here we have digits and special chars I think it may not be helpful. Plz correct me if I am wrong. Puneet Kishor-2 wrote: > > > > Harish CS wrote: >> Hi, >> We have a problem with a sql query. >> In a table, a column called "name"

Re: [sqlite] SQL query on sort order

Pavel Ivanov: Thank you very much. We used the query. -Harish Pavel Ivanov-2 wrote: > > If you want to do that completely in SQL without using collations you > can do something like this: > > select name, > case when substr(name, 1, 1) between 'A' and 'Z' or >

Re: [sqlite] Low-load methods of finding previous or next row

On Tue, Dec 14, 2010 at 05:09:04PM +, Simon Slavin wrote: > I recently found out that when you use LIMIT in SQLite the engine > still processes all applicable records even if it only has to return > the number you asked for. I suspect that this makes something I used > to do inefficient. So

Re: [sqlite] Low-load methods of finding previous or next row

On Wed, Dec 15, 2010 at 03:56:06AM -0600, Nicolas Williams wrote: > SELECT * FROM toy > WHERE > a >= (SELECT a FROM toy WHERE id = 6) OR > (a = (SELECT a FROM toy WHERE id = 6) AND > b <= (SELECT b FROM toy WHERE id = 6)) OR > (a = (SELECT a FROM toy WHERE id =

Re: [sqlite] Low-load methods of finding previous or next row

On Wed, Dec 15, 2010 at 04:29:42AM -0600, Nicolas Williams wrote: > The ORed terms are optimized as a UNION, with each sub-query using the > index. That's three index operations per column that you order by. Not > bad. s/three/one/ ___ sqlite-users

Re: [sqlite] Low-load methods of finding previous or next row

And if you use parametrized queries then you get this query plan: 0|0|TABLE toy VIA MULTI-INDEX UNION 0|0|TABLE toy WITH INDEX toy_abc 0|0|TABLE toy WITH INDEX toy_abc 0|0|TABLE toy WITH INDEX toy_abc The ORed terms are optimized as a UNION, with each sub-query using the index. That's three

Re: [sqlite] Lemon behavior

On Tue, Dec 07, 2010 at 08:09:53PM +0100, Begelman, Jamie wrote: > I'm using Lemon for a non-sqlite related project and it is exiting with an > assertion failure that I would like to understand. I have extracted the > following small set of productions from a larger grammar. The "list" >

Re: [sqlite] SQL query on sort order

Hello Pavel, Thanks. The substr() compares the first character only. For example, if the data is [CAT=$, CAT1$], it has to be sorted as [CAT1$, CAT=$] because when '=' and '1' are compared, '1' has to come first. Thanks for any suggestions. -Harish Pavel Ivanov-2 wrote: > > If you want to

Re: [sqlite] SQL query on sort order

Harish CS wrote: > Collation is okay for case insensitivity but since here we have digits and > special chars I think it may not be helpful. Plz correct me if I am wrong. You can implement a custom collation that defines any order of your choice. See

[sqlite] assert crash in wal

Hi, I wrote a little program that insert in a loop rows in to the DB, and in another thread run wal_checkpoint. After few minutes (6-7) I get (consistently) the following assert error: sqlite_test: ..//src/wal.c:1364: walMerge: Assertion `iLeft>=nLeft || aContent[aLeft[iLeft]]>dbpage' failed.

Re: [sqlite] assert crash in wal

Try to add: -DSQLITE_THREADSAFE =1 to your compilation options. -- Marco Bambini http://www.sqlabs.com On Dec 15, 2010, at 2:34 PM, Yoni Londner wrote: > Hi, > > I wrote a little program that insert in a loop rows in to the DB, and in > another thread run wal_checkpoint. > After few

Re: [sqlite] assert crash in wal

Isn't that the default? from sqliteInt.h: #if !defined(SQLITE_THREADSAFE) #if defined(THREADSAFE) # define SQLITE_THREADSAFE THREADSAFE #else # define SQLITE_THREADSAFE 1 /* IMP: R-07272-22309 */ #endif #endif Anyway, I added the define and it still happening. Yoni. On 15/12/2010 3:39 PM, Marco

Re: [sqlite] SQL query on sort order

>From the first post I've got the impression that only first character matters for you. When such sort order should persist over all characters you can't do it with simple query. Only the custom collation can help you. Pavel On Wed, Dec 15, 2010 at 7:29 AM, Harish CS wrote:

Re: [sqlite] WAL index in memory - multiple connections

On Mon, Dec 13, 2010 at 12:29:20PM +0200, Yoni Londner wrote: > Hi, > > In general I think that SQLite should have a in memory VFS, which is OS > independent. A laudable goal, where mmap is not available. > > I am going to implement proc_exclusive now, and would love to get any >

Re: [sqlite] assert crash in wal

On Wed, Dec 15, 2010 at 8:34 AM, Yoni Londner wrote: > Hi, > > I wrote a little program that insert in a loop rows in to the DB, and in > another thread run wal_checkpoint. > After few minutes (6-7) I get (consistently) the following assert error: > > sqlite_test:

Re: [sqlite] assert crash in wal

On Wed, Dec 15, 2010 at 11:49 AM, Richard Hipp wrote: > > > On Wed, Dec 15, 2010 at 8:34 AM, Yoni Londner wrote: > >> Hi, >> >> I wrote a little program that insert in a loop rows in to the DB, and in >> another thread run wal_checkpoint. >> After few

Re: [sqlite] assert crash in wal

From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp >The underlying error here is that you are attempting to use threads in the first place. You should never do that. Threads are evil and should be avoided wherever possible. Use separate

[sqlite] GROUP BY malfunction?

Hi there... I usually use mysql as a database engine, but sqlite has few unique features that came handy in my last project, so... here I am :-) Everything went well until yesterday, when I stumbled upon some strange behavior... Example database: +++--+-+ | ID |

Re: [sqlite] assert crash in wal

On Wed, Dec 15, 2010 at 11:14 PM, Doug wrote: > . And getting concurrency with processes means you introduce the > complexities of interprocess communication/synchronization which is much > easier to handle with threads in the same process. > > Doug, nothing stops you from

Re: [sqlite] GROUP BY malfunction?

On 12/15/2010 1:34 PM, _ Robal _ wrote: > What I'm trying to do is to get the whole rows with unique lineID's > and biggest date value... kinda like versioning. > > but sqlite keeps returning just single row, no matter how many rows > should be returned. > > The statement I use goes as

[sqlite] Weird CASCADE behavior: bug?

When I run the following piece of SQL in an empty database, I get a "no such table: main.table_e" error on the second "DROP TABLE" statement: CREATE TABLE table_e ( eid TEXT PRIMARY KEY ); CREATE TABLE table_t ( tid TEXT PRIMARY KEY, value TEXT ); CREATE TABLE table_b ( --

Re: [sqlite] assert crash in wal

Hi Richard, > > Perhaps I speak too soon. I'm still getting the error even after changing > this to SQLITE_CONFIG_SERIALIZED. I am continuing to investigate > Note that I used two sql connections (one for each thread) Yoni. ___ sqlite-users

Re: [sqlite] pragma vs select for introspection

On 15/12/10 02:47, Darren Duncan wrote: > Wols Lists wrote: >> On 15/12/10 00:18, Darren Duncan wrote: >> The point I'm making is that a list doesn't contain any ordering *data* >> - it's inherent in the fact of a list. A list is an abstract concept. In >> Pick, I can store a data structure that

Re: [sqlite] pragma vs select for introspection

Wols, I'm just acknowledging that I've read this message, but don't feel the need to say anything more in response, as we appear to have reached a point of clear-enough mutual understanding. I suggest that if you want to further discuss anything related that you start a new message, off of the

Re: [sqlite] assert crash in wal

On Wed, Dec 15, 2010 at 6:18 PM, Yoni Londner wrote: > Hi Richard, > > > > > Perhaps I speak too soon. I'm still getting the error even after > changing > > this to SQLITE_CONFIG_SERIALIZED. I am continuing to investigate > > > > Note that I used two sql connections

Re: [sqlite] assert crash in wal

The patch at http://www.sqlite.org/src/ci/cf86affcb7 should fix this problem. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GROUP BY malfunction?

Hi there... Thanks for your reply! > Works for me, I get two rows. Not the ones you expect though: nothing says > that the > engine must choose the first (or last, or any particular) row to represent > the group. After trying so many invalid constructions that was the only one that was giving

Re: [sqlite] GROUP BY malfunction?

On 12/15/2010 9:49 PM, _ Robal _ wrote: > I tried all of them... none working I'm afraid... Seems to me like it > can't access external > query fields from sub-query. Giving me always error - 'l1.lineID' is > unknown column... Must be a limitation of SQLite v2. It should work with v3. v2 is

Re: [sqlite] assert crash in wal

That's was fast! I tried it, and it seems to work correctly now. Thanks! :-) Yoni. On 16/12/2010 4:08 AM, Richard Hipp wrote: > The patch at http://www.sqlite.org/src/ci/cf86affcb7 should fix this > problem. ___ sqlite-users mailing list

Re: [sqlite] assert crash in wal

> The underlying error here is that you are attempting to use threads in the > first place. You should never do that. Threads are evil and should be > avoided wherever possible. Use separate processes for concurrency. Threads > in application programs always result in subtle bugs (such as

Re: [sqlite] WAL index in memory - multiple connections

> While I'm not a SQLite developer, I have to say I think you're going > down the wrong path. Are you sure the WAL index is your bottleneck? > I think it is unlikely. I think you are right, and I stooped working on this issue. > From your previous descriptions of your application, you might be