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

[Clemens Ladisch]

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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[David de Regt]

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 Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Kevin Benson
Sent: Sunday, November 3, 2013 10:55 PM
To: General Discussion of SQLite Database
Subject: 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 '(blah!)'
>
> I get the following error:
> malformed MATCH expression:_[(blah!)]
>
> If I remove either the parenthesis or the exclamation point, or add 
> quotes around the parenthesis, the error disappears.  I've read over 
> all of the FTS docs and I don't see any reason why it shouldn't work 
> (though it should ignore it with the default tokenizer -- but if I 
> have a custom tokenizer, it should support the !, in theory).  Any ideas?
>
> Thanks!
> -David
>


I believe (for backward compatibility
reasons) the SQLITE_ENABLE_FTS3_PARENTHESIS option comes into your 
considerations as described in Compiling and Enabling FTS3 and FTS4 at:
http://www.sqlite.org/fts3.html#section_2 2

--
   --
  --
 --Ô¿Ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Kevin Benson]

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 '(blah!)'
>
> I get the following error:
> malformed MATCH expression:_[(blah!)]
>
> If I remove either the parenthesis or the exclamation point, or add quotes
> around the parenthesis, the error disappears.  I've read over all of the
> FTS docs and I don't see any reason why it shouldn't work (though it should
> ignore it with the default tokenizer -- but if I have a custom tokenizer,
> it should support the !, in theory).  Any ideas?
>
> Thanks!
> -David
>


I believe (for backward compatibility
reasons) the SQLITE_ENABLE_FTS3_PARENTHESIS option comes into your
considerations as described in Compiling and Enabling FTS3 and FTS4 at:
http://www.sqlite.org/fts3.html#section_2 2

--
   --
  --
 --Ô¿Ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Date function accepts only DD for date string

[Navaneeth K N]

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 at the SQLite 
side itself?


--
Cheers,
Navaneeth

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] MATCH with punctuation in parenthesis causing error

[David de Regt]

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 remove either the parenthesis or the exclamation point, or add quotes 
around the parenthesis, the error disappears.  I've read over all of the FTS 
docs and I don't see any reason why it shouldn't work (though it should ignore 
it with the default tokenizer -- but if I have a custom tokenizer, it should 
support the !, in theory).  Any ideas?

Thanks!
-David
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Visual Studio 2013 Issue - GetVersionEx deprecated

[David de Regt]

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 SDK implements. 
 Looking at the SQLite source, it looks like it only uses the GetVersionEx call 
to test for whether LockFileEx is supported on the current OS (whether it's 
NT-based or not).

While not a big deal in theory (that M$ wants to deprecate the call), you may 
want to put in a warning disable around the GetVersion calls, and eventually 
look into a better longer term solution.  As of right now, sqlite doesn't 
compile out of the box on VS2013 without disabling the warning in your project 
file.  You may just want to locally disable the warning around the call for 
now, with something like the following:

#pragma warning(push)
#pragma warning(disable:4996)
... blah blah GetVersionEx() ...
#pragma warning(pop)

...  or just find another method to check if LockFileEx is available (check the 
response from a GetProcAddr, etc.)

In our project we're just disabling 4996 at the project level, so it's not a 
blocking issue, but it may stymie other people, especially anyone picking up 
development with Windows 8.1 now.

Cheers,
-David
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Aris Setyawan]

> 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  wrote:
> 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 each DB's doc, then test it before decide to use
>> it.
>
> Yes, it's different. In LMDB writers never block readers and readers never
> block writers. The original poster was complaining about SELECT taking a
> long
> time and preventing other threads from making progress. That problem doesn't
>
> exist in LMDB. BDB locking *might* be able to avoid this in many cases, if
> there are no hotspots, but is prone to deadlocks which require the calling
> application to retry failed requests.
>
>> LMDB is storage engine optimized for read. Why you don't optimize it's
>> write using cache oblivious data structure, for example LSM tree or
>> create new, like in sophia (sphia.org) key value DB?
>
> 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.
>
> --
>-- Howard Chu
>CTO, Symas Corp.   http://www.symas.com
>Director, Highland Sun http://highlandsun.com/hyc/
>Chief Architect, OpenLDAP  http://www.openldap.org/project/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Howard Chu]

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 each DB's doc, then test it before decide to use
it.


Yes, it's different. In LMDB writers never block readers and readers never 
block writers. The original poster was complaining about SELECT taking a long 
time and preventing other threads from making progress. That problem doesn't 
exist in LMDB. BDB locking *might* be able to avoid this in many cases, if 
there are no hotspots, but is prone to deadlocks which require the calling 
application to retry failed requests.



LMDB is storage engine optimized for read. Why you don't optimize it's
write using cache oblivious data structure, for example LSM tree or
create new, like in sophia (sphia.org) key value DB?


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.


--
  -- Howard Chu
  CTO, Symas Corp.   http://www.symas.com
  Director, Highland Sun http://highlandsun.com/hyc/
  Chief Architect, OpenLDAP  http://www.openldap.org/project/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Howard Chu]

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 is better and faster than SQLite ?


Oracle claims that. From what I can see, Oracle is wrong.



On Sun, Nov 3, 2013 at 8:28 PM, Howard Chu  wrote:


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 each DB's doc, then test it before decide to use
it.

LMDB is storage engine optimized for read. Why you don't optimize it's
write using cache oblivious data structure, for example LSM tree or
create new, like in sophia (sphia.org) key value DB?



Because read optimization is what was important to us when I created LMDB.
That's like asking why a hammer isn't a screwdriver.


  On 11/3/13, Howard Chu  wrote:



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
if they will write in the same row/page.

www.oracle.com/technetwork/database/berkeleydb/bdb-
sqlite-comparison-wp-176431.pdf

* You must aware that BDB now have AGPL license.



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.

https://gitorious.org/mdb/sqlightning/



On 11/3/13, Raheel Gupta  wrote:


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 database and soon after
another
thread does "SELECT" on the database (using the same connection) or
vice
versa, the second thread has to wait till the first thread finishes.

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 was wondering if Row Level Locking would be introduced in
journal_mode=delete as its there in InnoDB for MySQL. Atleast for
selects
or inserts Row Level rocking should be possible as neither modify the
existing rows.

journal_mode=wal is a little slower and has its own limitations over
NFS.

OR if there is a mode equivalent to innodb in SQLITE please do let me
know.
I need to do a lot of selects and inserts in my application and hence a
row
level locking is suitable vs table or database level locking.



--
  -- Howard Chu
  CTO, Symas Corp.   http://www.symas.com
  Director, Highland Sun http://highlandsun.com/hyc/
  Chief Architect, OpenLDAP  http://www.openldap.org/project/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update and GROUP BY

[James K. Lowden]

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, 'y');
> INSERT INTO T VALUES('f', 60, 'y');
> INSERT INTO T VALUES('g', null, 'y');
> INSERT INTO T VALUES('h', null, 'z');
> INSERT INTO T VALUES('i', 111, 'z');
> 
> I would like to see where N='c', V as the average for the group (G)
> were this record belongs to (so 'x').

So, you want the average of the non-missing V per G: 

sqlite> select G, avg(V) as avgV from T group by G;
G   avgV  
--  --
x   2.0   
y   70.0  
z   111.0 

and to see that average for each N that belongs to G: 

sqlite> select T.N, A.* from T join (select G, avg(V) as avgV from T
sqlite> group by G) as A on T.G = A.G;
N   G   avgV  
--  --  --
a   x   2.0   
b   x   2.0   
c   x   2.0   
d   y   70.0  
e   y   70.0  
f   y   70.0  
g   y   70.0  
h   z   111.0 
i   z   111.0 

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Yuriy Kaminskiy]

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:
 ...
> [In fact, you can move out out loop not only *whole* constant WHERE, but 
> also
> all constant AND terms of WHERE, like this:
> SELECT * FROM t WHERE const1 AND notconst AND const2 ->
> SELECT * FROM (SELECT * FROM t WHERE notconst) WHERE const1 AND const2
> I'll take a shot on that later.]
 Here it goes.

 Prerequisite: previous patch.
 Passes quick regression test (make test).
 Possible problem: short-circuits evaluation. Should not be a problem, IMO, 
 as only
 constants references? Please verify.
>>> Ping.
>> Ping.
> Ping.
> For convenience all 3 patches collected below (needed no change for 3.7.11).

Ping. Over 2 years passed since this patch series was first posted.
Updated patch series for 3.8.1 below.
-- 

The author or authors of this code dedicate any and all copyright interest
in this code to the public domain. We make this dedication for the benefit
of the public at large and to the detriment of our heirs and successors.
We intend this dedication to be an overt act of relinquishment in perpetuity
of all present and future rights to this code under copyright law.

Signed-off-by: Yuriy M. Kaminskiy 

Part 1: Move whereSplit() to unbreak constant condition elimination.
Test case:
   CREATE TABLE t (i, j, k);
   EXPLAIN SELECT * FROM t WHERE 11

Index: sqlite3-3.8.1/src/where.c
===
--- sqlite3-3.8.1.orig/src/where.c  2013-10-17 22:57:30.0 +0400
+++ sqlite3-3.8.1/src/where.c   2013-11-03 22:44:41.0 +0400
@@ -5727,7 +5727,6 @@ WhereInfo *sqlite3WhereBegin(
   initMaskSet(pMaskSet);
   whereClauseInit(&pWInfo->sWC, pWInfo);
   sqlite3ExprCodeConstants(pParse, pWhere);
-  whereSplit(&pWInfo->sWC, pWhere, TK_AND);
   sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto */

   /* Special case: a WHERE clause that is constant.  Evaluate the
@@ -5738,6 +5737,8 @@ WhereInfo *sqlite3WhereBegin(
 pWhere = 0;
   }

+  whereSplit(&pWInfo->sWC, pWhere, TK_AND);
+
   /* Special case: No FROM clause
   */
   if( nTabList==0 ){
===

Part 2: optimize "WHERE const AND notconst" too
Test case:
   EXPLAIN SELECT * FROM t WHERE 11 AND 12 AND i AND 13 AND j AND 14;

Index: sqlite3-3.8.1/src/where.c
===
--- sqlite3-3.8.1.orig/src/where.c  2013-11-03 23:27:05.0 +0400
+++ sqlite3-3.8.1/src/where.c   2013-11-03 23:27:59.0 +0400
@@ -5739,6 +5739,24 @@ WhereInfo *sqlite3WhereBegin(

   whereSplit(&pWInfo->sWC, pWhere, TK_AND);

+  {
+/* Move const in "WHERE const AND notconst" out of internal loop */
+int i, j;
+WhereClause * const pWC = &pWInfo->sWC;
+
+for(j=i=0; inTerm; i++){
+  if( nTabList==0 || sqlite3ExprIsConstantNotJoin(pWC->a[i].pExpr) ){
+sqlite3ExprIfFalse(pParse, pWC->a[i].pExpr, pWInfo->iBreak,
SQLITE_JUMPIFNULL);
+continue;
+  }
+  if( j!=i )
+pWC->a[j]=pWC->a[i];
+  j++;
+}
+pWC->nTerm -= i-j;
+/*if (i != j) memset(&(pWC->a[j]), 0, (i-j)*sizeof(pWC->a[0]));*/
+  }
+
   /* Special case: No FROM clause
   */
   if( nTabList==0 ){
===

Part 3: Remove now-redundant sqlite3ExprIsConstantNotJoin call.
There are minor change:
   EXPLAIN SELECT * FROM t WHERE 1 AND 2

Index: sqlite3-3.8.1/src/where.c
===
--- sqlite3-3.8.1.orig/src/where.c  2013-11-03 22:54:44.0 +0400
+++ sqlite3-3.8.1/src/where.c   2013-11-03 22:56:18.0 +0400
@@ -5727,20 +5727,13 @@ WhereInfo *sqlite3WhereBegin(
   initMaskSet(pMaskSet);
   whereClauseInit(&pWInfo->sWC, pWInfo);
   sqlite3ExprCodeConstants(pParse, pWhere);
-  sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto */
-
-  /* Special case: a WHERE clause that is constant.  Evaluate the
-  ** expression and either jump over all of the code or fall thru.
-  */
-  if( pWhere && (nTabList==0 || sqlite3ExprIsConstantNotJoin(pWhere)) ){
-sqlite3ExprIfFalse(pParse, pWhere, pWInfo->iBreak, SQLITE_JUMPIFNULL);
-pWhere = 0;
-  }
-
   whereSplit(&pWInfo->sWC, pWhere, TK_AND);
+  sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto */

   {
-/* Move const in "WHERE const AND notconst" out of internal loop */
+/* Special case: AND subterm of WHERE clause that is constant. Evaluate the
+ ** expression and either jump over all of the code or fall thru.
+ */
 int i, j;
 WhereClause * const pWC = &pWInfo->sWC;

_

Re: [sqlite] Row Level Locking as in InnoDB

[Raheel Gupta]

@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 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 each DB's doc, then test it before decide to use
>> it.
>>
>> LMDB is storage engine optimized for read. Why you don't optimize it's
>> write using cache oblivious data structure, for example LSM tree or
>> create new, like in sophia (sphia.org) key value DB?
>>
>
> Because read optimization is what was important to us when I created LMDB.
> That's like asking why a hammer isn't a screwdriver.
>
>
>  On 11/3/13, Howard Chu  wrote:
>>
>>> 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
 if they will write in the same row/page.

 www.oracle.com/technetwork/database/berkeleydb/bdb-
 sqlite-comparison-wp-176431.pdf

 * You must aware that BDB now have AGPL license.

>>>
>>> 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.
>>>
>>> https://gitorious.org/mdb/sqlightning/
>>>
>>>
 On 11/3/13, Raheel Gupta  wrote:

> 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 database and soon after
> another
> thread does "SELECT" on the database (using the same connection) or
> vice
> versa, the second thread has to wait till the first thread finishes.
>
> 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 was wondering if Row Level Locking would be introduced in
> journal_mode=delete as its there in InnoDB for MySQL. Atleast for
> selects
> or inserts Row Level rocking should be possible as neither modify the
> existing rows.
>
> journal_mode=wal is a little slower and has its own limitations over
> NFS.
>
> OR if there is a mode equivalent to innodb in SQLITE please do let me
> know.
> I need to do a lot of selects and inserts in my application and hence a
> row
> level locking is suitable vs table or database level locking.
>

>
> --
>   -- Howard Chu
>   CTO, Symas Corp.   http://www.symas.com
>   Director, Highland Sun http://highlandsun.com/hyc/
>   Chief Architect, OpenLDAP  http://www.openldap.org/project/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Howard Chu]

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 each DB's doc, then test it before decide to use
it.

LMDB is storage engine optimized for read. Why you don't optimize it's
write using cache oblivious data structure, for example LSM tree or
create new, like in sophia (sphia.org) key value DB?


Because read optimization is what was important to us when I created LMDB. 
That's like asking why a hammer isn't a screwdriver.



On 11/3/13, Howard Chu  wrote:

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
if they will write in the same row/page.

www.oracle.com/technetwork/database/berkeleydb/bdb-sqlite-comparison-wp-176431.pdf

* You must aware that BDB now have AGPL license.


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.

https://gitorious.org/mdb/sqlightning/



On 11/3/13, Raheel Gupta  wrote:

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 database and soon after
another
thread does "SELECT" on the database (using the same connection) or vice
versa, the second thread has to wait till the first thread finishes.

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 was wondering if Row Level Locking would be introduced in
journal_mode=delete as its there in InnoDB for MySQL. Atleast for
selects
or inserts Row Level rocking should be possible as neither modify the
existing rows.

journal_mode=wal is a little slower and has its own limitations over
NFS.

OR if there is a mode equivalent to innodb in SQLITE please do let me
know.
I need to do a lot of selects and inserts in my application and hence a
row
level locking is suitable vs table or database level locking.



--
  -- Howard Chu
  CTO, Symas Corp.   http://www.symas.com
  Director, Highland Sun http://highlandsun.com/hyc/
  Chief Architect, OpenLDAP  http://www.openldap.org/project/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Aris Setyawan]

> 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 test it before decide to use
it.

LMDB is storage engine optimized for read. Why you don't optimize it's
write using cache oblivious data structure, for example LSM tree or
create new, like in sophia (sphia.org) key value DB?

On 11/3/13, Howard Chu  wrote:
> 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
>> if they will write in the same row/page.
>>
>> www.oracle.com/technetwork/database/berkeleydb/bdb-sqlite-comparison-wp-176431.pdf
>>
>> * You must aware that BDB now have AGPL license.
>
> 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.
>
> https://gitorious.org/mdb/sqlightning/
>
>>
>> On 11/3/13, Raheel Gupta  wrote:
>>> 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 database and soon after
>>> another
>>> thread does "SELECT" on the database (using the same connection) or vice
>>> versa, the second thread has to wait till the first thread finishes.
>>>
>>> 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 was wondering if Row Level Locking would be introduced in
>>> journal_mode=delete as its there in InnoDB for MySQL. Atleast for
>>> selects
>>> or inserts Row Level rocking should be possible as neither modify the
>>> existing rows.
>>>
>>> journal_mode=wal is a little slower and has its own limitations over
>>> NFS.
>>>
>>> OR if there is a mode equivalent to innodb in SQLITE please do let me
>>> know.
>>> I need to do a lot of selects and inserts in my application and hence a
>>> row
>>> level locking is suitable vs table or database level locking.
>
>
> --
>-- Howard Chu
>CTO, Symas Corp.   http://www.symas.com
>Director, Highland Sun http://highlandsun.com/hyc/
>Chief Architect, OpenLDAP  http://www.openldap.org/project/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Howard Chu]

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
if they will write in the same row/page.

www.oracle.com/technetwork/database/berkeleydb/bdb-sqlite-comparison-wp-176431.pdf

* You must aware that BDB now have AGPL license.


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.


https://gitorious.org/mdb/sqlightning/



On 11/3/13, Raheel Gupta  wrote:

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 database and soon after another
thread does "SELECT" on the database (using the same connection) or vice
versa, the second thread has to wait till the first thread finishes.

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 was wondering if Row Level Locking would be introduced in
journal_mode=delete as its there in InnoDB for MySQL. Atleast for selects
or inserts Row Level rocking should be possible as neither modify the
existing rows.

journal_mode=wal is a little slower and has its own limitations over NFS.

OR if there is a mode equivalent to innodb in SQLITE please do let me know.
I need to do a lot of selects and inserts in my application and hence a row
level locking is suitable vs table or database level locking.



--
  -- Howard Chu
  CTO, Symas Corp.   http://www.symas.com
  Director, Highland Sun http://highlandsun.com/hyc/
  Chief Architect, OpenLDAP  http://www.openldap.org/project/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Aris Setyawan]

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 the same row/page.

www.oracle.com/technetwork/database/berkeleydb/bdb-sqlite-comparison-wp-176431.pdf

* You must aware that BDB now have AGPL license.

On 11/3/13, Raheel Gupta  wrote:
> 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 database and soon after another
> thread does "SELECT" on the database (using the same connection) or vice
> versa, the second thread has to wait till the first thread finishes.
>
> 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 was wondering if Row Level Locking would be introduced in
> journal_mode=delete as its there in InnoDB for MySQL. Atleast for selects
> or inserts Row Level rocking should be possible as neither modify the
> existing rows.
>
> journal_mode=wal is a little slower and has its own limitations over NFS.
>
> OR if there is a mode equivalent to innodb in SQLITE please do let me know.
> I need to do a lot of selects and inserts in my application and hence a row
> level locking is suitable vs table or database level locking.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Simon Slavin]

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 specifically for your WHERE and ORDER 
BY clauses.  And that you have set an appropriate SQLite time-out so you don't 
have to handle time-outs in your own programming.

> I was wondering if Row Level Locking would be introduced in
> journal_mode=delete as its there in InnoDB for MySQL. Atleast for selects
> or inserts Row Level rocking should be possible as neither modify the
> existing rows.

It /might/ be possible to introduce table-level locking, but row-level locking 
is actually very complicated.  Not only do you have to lock that row of the 
table data, you have to lock that chunk in all the indexes.  You also have to 
make sure that no operation makes changes to any of the indexes which might 
cause that row to be skipped or duplicated in a scan, which means that locking 
a row really means locking a range of rows in certain indexes.  There are also 
opportunities for deadlock which need to be dealt with, introducing lots of 
extra programming.

SQLite gets a lot of its speed because it doesn't have to do complicated 
checking whenever it does things, it just tests to see whether the entire 
database is locked.  Having to continually check for locking on each row would 
make it far slower.  There's a good chance that having to check for locking in 
every _step() rather than just once per SELECT would actually give you slower 
results than you currently have.

> journal_mode=wal is a little slower and has its own limitations over NFS.

NFS access is not suitable for SQLite because locking under NFS is 
untrustworthy.  Please see



If you really need concurrent multi-computer access then your project may be 
more suited to a DBMS with server/client architecture.  Those are inherently 
better for NFS because they don't use the NFS for database transactions: the 
database is actually accessed only from software running on a computer which 
accesses the file locally.  See the last section of



Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Row Level Locking as in InnoDB

[Raheel Gupta]

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 database and soon after another
thread does "SELECT" on the database (using the same connection) or vice
versa, the second thread has to wait till the first thread finishes.

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 was wondering if Row Level Locking would be introduced in
journal_mode=delete as its there in InnoDB for MySQL. Atleast for selects
or inserts Row Level rocking should be possible as neither modify the
existing rows.

journal_mode=wal is a little slower and has its own limitations over NFS.

OR if there is a mode equivalent to innodb in SQLITE please do let me know.
I need to do a lot of selects and inserts in my application and hence a row
level locking is suitable vs table or database level locking.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Second ORDER BY statement

[Simon Slavin]

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 column in the 
table.

CREATE TABLE ... (..., Language TEXT COLLATE NOCASE, ...)

Then all collations of that column are handled NOCASE and you don't have to 
keep specifying it in your code or even indexes.  Note that this does not 
change the actual values which are stored in the table: you still get back out 
the exact form of text you put in.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature Request: Binding Arrays

[Simon Slavin]

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 JOINs.  It can do lots of rearranging 
of a JOIN to optimize all aspects of it, including noticing when one index is 
useful for combinations of WHERE, JOIN, and ORDER BY.  You can make it even 
better by doing an ANALYZE when the pattern of your data changes.  Sub-SELECTs 
aren't quite as good as JOINs but SQLite still understands them very well.

Custom functions are great for custom uses, but all SQLite knows is that you 
have a function.  It can't do any optimization using them.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Second ORDER BY statement

[Clemens Ladisch]

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
> SELECT *
> FROM table1
> WHERE Language ="" COLLATE NOCASE
> LIMIT 100

You sort them the same way you sort the other records:

SELECT * FROM
(SELECT *
 FROM table1
 WHERE Language != "" COLLATE NOCASE
 ORDER BY Language COLLATE NOCASE)
UNION ALL
SELECT * FROM
(SELECT *
 FROM table1
 WHERE Language = "" COLLATE NOCASE
 ORDER BY Artist COLLATE NOCASE)
LIMIT 100


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature Request: Binding Arrays

[Dominique Devienne]

On Sat, Nov 2, 2013 at 4:59 AM, Olaf Schmidt  wrote:

> Am 31.10.2013 14:09, schrieb Dominique Devienne:
>
> [Userdefined functions in conjunction with fast Exists-checks
> in "Userland" - vs. SQLites built-in indexing in case of In (List)]
>
> [...] With a function based approach, you are *always* full-scanning the
>> whole
>> "real" table, no matter the cardinality of the InList operand [...]
>>
>> Of course that's speculation on my part, versus your timed
>> experimentation,
>> so could well be that I'm wrong. And I'll need to look into this
>> eventually.
>>
>>
> You're not wrong - although the UDF-timings in my previous post are
> correct - it is true that they will remain (relatively) constant
>

Thanks for confirming with hard-experimentations my guesswork.


> [...] what came as a surprise
> was the kind of "inverse-lookup" the SQLite-optimizer apparently
> performs, when an index exists on the "real" table which provides
> the Column-value to compare against the "In"-list.
>

Yes, SQLite probes several plans for most index combinations, and the order
you write your SQL statement and its joins matters little if at all AFAIK.
(disclaimer: non-authoritative guesswork again).


> With only an index on the real table (on the compare-value-column):
> 0.4msec (100 items in the compare-list)
> 1.9msec (1000 items in the compare-list)
> 26msec (1 items in the compare-list)
> 116msec (4 items in the compare-list)
>

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. It's very tempting for a C/C++ developer with little
SQL experience to write a C++ UDF as a WHERE filtering predicate, STL-style
(most of our tables are virtual tables over native C++ containers, so often
you can write such UDFs in our app), but as the tables they you end up
full-scanning get large, it doesn't scale well against an index-path plan
if there's one possible.


> Maybe all these values provide an insight also for others - in what
> "regions" the SQLite-In-List functionality (roughly) operates
> timing-wise (under somewhat idealized conditions).


Once again, thanks for the detailed and thorough analysis Olaf. Cheers, --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users