Re: [sqlite] Date function accepts only DD for date string

Navaneeth K N wrote: > select date('2013-11-04') -> Works well > select date('2013-11-4') -> Not working > > Is there a way to make the second form working? Only by inserting a zero into the string (which isn't easy with the built-in SQL functions). Regards, Clemens

Re: [sqlite] MATCH with punctuation in parenthesis causing error

I realize that the query is being parsed with the enhanced query syntax since I added parenthesis (and have that compile flag enabled), but why does the exclamation point at the end cause an error? It seems like it should be just ignored, given the default tokenizer. -Original

Re: [sqlite] MATCH with punctuation in parenthesis causing error

On Mon, Nov 4, 2013 at 1:38 AM, David de Regt wrote: > I've worked around this issue a separate way, but I'd like to understand > what went wrong in the first place here. I have an FTS3 table, and if I > query with the following: > > SELECT * FROM table WHERE keywords MATCH

[sqlite] Date function accepts only DD for date string

Hello, select date('2013-11-04') -> Works well select date('2013-11-4') -> Not working Is there a way to make the second form working? Currently, I handle this at the application side. If month/day is less than 10, then prefix it with 0. But I'm wondering is there a better way to do this

[sqlite] MATCH with punctuation in parenthesis causing error

I've worked around this issue a separate way, but I'd like to understand what went wrong in the first place here. I have an FTS3 table, and if I query with the following: SELECT * FROM table WHERE keywords MATCH '(blah!)' I get the following error: malformed MATCH expression:_[(blah!)] If I

[sqlite] Visual Studio 2013 Issue - GetVersionEx deprecated

A small issue has arisen that the local powers may want to be aware of. In Visual Studio 2013, which uses the Windows 8.1 Platform SDK, they've marked GetVersionEx as deprecated, trying to supercede it through to VerifyVersionInfo and some other hardcoded macros based on that call that the new

Re: [sqlite] Row Level Locking as in InnoDB

> I just looked, sophia is nothing special. See these microbench results. > http://pastebin.com/cFK1JsCN > > LMDB's codebase is still smaller and faster. Nothing else touches LMDB's > read > speed. Focus to the write number. You are using SSD or HDD? On 11/4/13, Howard Chu

Re: [sqlite] Row Level Locking as in InnoDB

Aris Setyawan wrote: SQLightning replaces the SQLite backend with Symas' LMDB, which also uses MVCC and thus supports high concurrency. It is also many times faster than BerkeleyDB and vanilla SQLite. Your MVCC is different compared to InnoDB or BDB locking. Every one should carefully read

Re: [sqlite] Row Level Locking as in InnoDB

Raheel Gupta wrote: @Howard I had tested your code earlier but it didnt seem to be stable and getting it to run was a task. Also I learnt that it is a "in-memory" database. False. LMDB is a memory-mapped disk database, that is not the same as an in-memory database. @Aris are you saying BDB

Re: [sqlite] Update and GROUP BY

On Sat, 2 Nov 2013 18:06:30 +0100 Gert Van Assche wrote: > CREATE TABLE T (N, V, G); > INSERT INTO T VALUES('a', 1, 'x'); > INSERT INTO T VALUES('b', 3, 'x'); > INSERT INTO T VALUES('c', null, 'x'); > INSERT INTO T VALUES('d', 80, 'y'); > INSERT INTO T VALUES('e', null,

Re: [sqlite] [patch 2/2] move "const" out of loop in "WHERE const AND expr"

On 2012/04/08 Yuriy Kaminskiy wrote: > On 2011/10/23, Yuriy Kaminskiy wrote: >> Yuriy Kaminskiy wrote: >>> Yuriy Kaminskiy wrote: Yuriy Kaminskiy wrote: > When WHERE condition is constant, there are no need to evaluate and check > it for > each row. It works, but only partially:

Re: [sqlite] Row Level Locking as in InnoDB

@Howard I had tested your code earlier but it didnt seem to be stable and getting it to run was a task. Also I learnt that it is a "in-memory" database. @Aris are you saying BDB is better and faster than SQLite ? On Sun, Nov 3, 2013 at 8:28 PM, Howard Chu wrote: > Aris

Re: [sqlite] Row Level Locking as in InnoDB

Aris Setyawan wrote: SQLightning replaces the SQLite backend with Symas' LMDB, which also uses MVCC and thus supports high concurrency. It is also many times faster than BerkeleyDB and vanilla SQLite. Your MVCC is different compared to InnoDB or BDB locking. Every one should carefully read

Re: [sqlite] Row Level Locking as in InnoDB

> SQLightning replaces the SQLite backend with Symas' LMDB, which also uses > MVCC > and thus supports high concurrency. It is also many times faster than > BerkeleyDB and vanilla SQLite. Your MVCC is different compared to InnoDB or BDB locking. Every one should carefully read each DB's doc, then

Re: [sqlite] Row Level Locking as in InnoDB

Aris Setyawan wrote: SQLite do not use row level locking, but db level locking, so it was the right behavior the second thread was blocked. For innodb like in SQLite, Oracle have SQLite compatible API, but use BDB backend. Since BDB use MVCC (row/page level locking), your threads only blocked

Re: [sqlite] Row Level Locking as in InnoDB

SQLite do not use row level locking, but db level locking, so it was the right behavior the second thread was blocked. For innodb like in SQLite, Oracle have SQLite compatible API, but use BDB backend. Since BDB use MVCC (row/page level locking), your threads only blocked if they will write in

Re: [sqlite] Row Level Locking as in InnoDB

On 3 Nov 2013, at 1:24pm, Raheel Gupta wrote: > In order to avoid this, I had to use journal_mode=wal so that two threads > dont have to wait when they both are doing SELECTs which might be taking > 3-5 seconds to process. I assume you have designed your indexes

[sqlite] Row Level Locking as in InnoDB

Hi, I have been using SQLite for one project of mine and I will be storing TBs of Data. Now there will be a lot of selections in this database and I have come across one problem with SQLite. In journal_mode=delete the selection is database locked. When one thread does a "TRANSACTION" on the

Re: [sqlite] Second ORDER BY statement

On 3 Nov 2013, at 3:24am, SongbookDB wrote: > WHERE Language !="" COLLATE NOCASE > ORDER BY Language COLLATE NOCASE) By the way, if every time you refer to your Language column you want it colated NOCASE, it's far more efficient to do it when you define the

Re: [sqlite] Feature Request: Binding Arrays

On 3 Nov 2013, at 9:07am, Dominique Devienne wrote: > This is consistent with findings we've seen in our own software, where > rewriting queries to use joins instead of custom SQL functions sped up some > queries considerably. The SQLite engine completely understands

Re: [sqlite] Second ORDER BY statement

SongbookDB wrote: > I'd now like to order the Language = "" rows by another column, "Artist", > but cannot crack how to restructure the query to accommodate this. > > SELECT * FROM > (SELECT * > FROM table1 > WHERE Language !="" COLLATE NOCASE > ORDER BY Language COLLATE NOCASE) > UNION ALL >