Re: [sqlite] High performance and concurrency

[Simon Slavin]

On 1 Mar 2018, at 7:24am, Shevek  wrote:

> What I think is happening is that either a pthread mutex or a database lock 
> is serializing the accesses, so each thread blocks the others.

What journal mode are you using ?



If it's not WAL, try WAL.  If it's currently WAL, try DELETE.  Once you've 
changed it see if this changes how your program behaves.

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

[sqlite] High performance and concurrency

[Shevek]

Hi,

I would like to have truly concurrent access to an sqlite database, that 
is, the ability for multiple connections to read from the database 
simultaneously. I'm using Java with xerial's sqlite-jdbc, customized to 
let me mmap the entire database into RAM, and with additional debugging 
symbols for perf. The database is about 30Gb, fully read-only, and the 
connections are opened as such.


What I think is happening is that either a pthread mutex or a database 
lock is serializing the accesses, so each thread blocks the others.


Queries are taking a few seconds, even with covering indexes, and I have 
the RAM bandwidth available, so I'd really like to use it.


Any pointers?

Thank you.

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

[sqlite] BF Interpreter

[Gary Briggs]

Thanks to the help the other day with the strange concatentation result.

I was referring to a BF interpreter I was working on, in pure SQLite SQL.
Well, here it is, working.

Hopefully no-one finds this useful,
Gary

WITH RECURSIVE
  program AS
 (SELECT 
'++[>+++>++>+++>+-]>++.>+.+++..+++.>++.<<+++.>.+++.--..>+.>.'
 AS p,
'' AS input, 3 AS width
),
  jumpdepth AS
 (SELECT 0 AS idx, 0 AS jumpdepth, '' AS jumplist, NULL as jumpback, NULL 
AS direction, p || '0' AS p, width FROM program
  UNION ALL
SELECT idx+1, CASE SUBSTR(p, idx+1, 1)
WHEN '[' THEN jumpdepth+1
WHEN ']' THEN jumpdepth-1
ELSE jumpdepth END,
CASE SUBSTR(p, idx+1, 1)
WHEN '[' THEN SUBSTR('000' || (idx+1), -width) || jumplist
WHEN ']' THEN SUBSTR(jumplist,width+1)
ELSE jumplist END,
CASE SUBSTR(p, idx+1, 1)
WHEN ']' THEN CAST(SUBSTR(jumplist,1,width) AS INTEGER)
ELSE NULL END,
CASE SUBSTR(p, idx+1, 1)
WHEN '[' THEN 'L'
WHEN ']' THEN 'R'
ELSE NULL END,
p, width
  FROM jumpdepth
  WHERE LENGTH(p)>=idx),
  jumptable(a,b,dir) AS
  (SELECT idx,jumpback,'L' FROM jumpdepth WHERE jumpback IS NOT NULL
  UNION ALL
   SELECT jumpback,idx+1,'R' FROM jumpdepth WHERE jumpback IS NOT NULL),
  bf(ep, p, width, defaulttapeentry, ip, dp, instruction, output, input, tape) 
AS
   (SELECT 0, p, width, SUBSTR('000', -width), 1, 1, '', '', input, 
SUBSTR('00', -width)
   FROM program
UNION ALL
SELECT ep+1, p, width, defaulttapeentry, CASE WHEN jumptable.b IS NOT 
NULL AND
((dir='R' AND CAST(SUBSTR(tape, width*(dp-1)+1, width) AS 
INTEGER)=0)
OR
 (dir='L' AND CAST(SUBSTR(tape, width*(dp-1)+1, width) AS 
INTEGER)!=0)) THEN jumptable.b
  ELSE ip+1 END,
CASE SUBSTR(p, ip, 1)
   WHEN '>' THEN dp+1
   WHEN '<' THEN MAX(dp-1,1)
   ELSE dp END,
SUBSTR(p, ip, 1),
CASE WHEN SUBSTR(p, ip, 1)='.' THEN (output || CHAR(SUBSTR(tape, 
(dp-1)*width+1, width))) ELSE output END,
CASE WHEN SUBSTR(p, ip, 1)=',' THEN SUBSTR(input, 2) ELSE input END,
CASE SUBSTR(p, ip, 1)
WHEN '<' THEN CASE WHEN dp=1 THEN defaulttapeentry || tape ELSE 
tape END
WHEN '>' THEN CASE WHEN dp*width=LENGTH(tape) THEN tape || 
defaulttapeentry ELSE tape END
WHEN '+' THEN SUBSTR(tape,1,width*(dp-1)) || SUBSTR('000' 
|| (CAST(SUBSTR(tape,width*(dp-1)+1,width) AS INTEGER)+1), -width) || 
SUBSTR(tape,width*dp+1)
WHEN '-' THEN SUBSTR(tape,1,width*(dp-1)) || SUBSTR('000' 
|| (CAST(SUBSTR(tape,width*(dp-1)+1,width) AS INTEGER)-1), -width) || 
SUBSTR(tape,width*dp+1)
WHEN ',' THEN SUBSTR(tape,1,width*(dp-1)) || SUBSTR('000' 
|| (UNICODE(SUBSTR(input,1,1))), -width) || SUBSTR(tape,width*(dp+1))
ELSE tape END
  FROM bf LEFT JOIN jumptable ON jumptable.a=ip WHERE LENGTH(p) >= ip)
SELECT output FROM bf ORDER BY ep DESC LIMIT 1;

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

[sqlite] Help with json1 query?

[Charles Leifer]

Hi,

I'm prototyping a little graph library using SQLite. My idea is to store
vertices in a simple table like this:

CREATE TABLE "vertex" ("key" TEXT NOT NULL PRIMARY KEY, "metadata" JSON);
CREATE TABLE "edge" (
"id" INTEGER NOT NULL PRIMARY KEY,
"src" TEXT NOT NULL,
"dest" TEXT NOT NULL,
"metadata" JSON,
FOREIGN KEY ("src") REFERENCES "vertex" ("key"),
FOREIGN KEY ("dest") REFERENCES "vertex" ("key"));

What I'd like to do is allow querying of edges (or vertices) using a
*partial* metadata object. So if I had the following JSON object stored in
an edge's metadata:

{"k1": "v1", "k2": "v2", "k3": "v3"}

The user could provide me an object like {"k1": "v1", "k3": "v3"} and I
would be able to match the above edge's metadata.

I can see decomposing the user-provided dictionary and building up multiple
equality tests using the json_extract() function, e.g.:

select * from edge where json_extract(metadata, '$.k1') = 'v1' AND
json_extract(metadata, '$.k3') = 'v3';

But I was hoping there would be a more elegant way to express this that
someone would be able to share? It seems as though I should be able to use
`json_each()` (or even `json_tree()` if metadata could be nested?), but I'm
not sure how to formulate the query.

It'd be great if there were a JSON function like "json_contains()" where I
could write:

select * from edge where json_contains(metadata, '$', '{"k1": "v1", "k3":
"v3"}');

Any help appreciated!

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

Re: [sqlite] SQL command not equal comparison within json content

[R Smith]

On 2018/02/28 3:34 PM, tj5527 wrote:

I create a table with the command `CREATE TABLE test1 (key text primary key, 
obj json);` with two records inserted

 # select * from test1;
 key1|{"a":1,"b":2,"c":{"x":99},"status":"done"} # record 1
 key2|{ "key": key2 } # record 2

Now I want to retrieve the record that is not marked with status "done" (so basically it is 
expected to return the second record i.e. record 2 with key2). The command I use is `select * from test1, 
json_tree(test1.obj) where json_tree.value <> "done";` But it returns

 key1|{"a":1,"b":2,"c":{"x":99},"status":"done"}|a|1|integer|1|2|0|$.a|$
 key1|{"a":1,"b":2,"c":{"x":99},"status":"done"}|b|2|integer|2|4|0|$.b|$
 
key1|{"a":1,"b":2,"c":{"x":99},"status":"done"}|c|{"x":99}|object||6|0|$.c|$
 
key1|{"a":1,"b":2,"c":{"x":99},"status":"done"}|x|99|integer|99|8|6|$.c.x|$.c
 Error: malformed JSON


It's just like the error says: The JSON is malformed, it has nothing to 
do with SQLite. You can easily check the correctness of any JSON on one 
of the JSON validation sites, like this:

https://jsonlint.com/

If you go to that site and paste your json string from key 2:

{ "key": key2 }

in there, then hit "Validate", it will tell you why it is wrong.


SQLite version I use is 3.11.0 2016-02-15 17:29:24


That version is archaic, we are already past 3.20 - and there were some 
JSON enhancements in the time it took, which might benefit you.  Are you 
able to use a more recent version?



Cheers,
Ryan

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

Re: [sqlite] SQL command not equal comparison within json content

[Dan Kennedy]

On 02/28/2018 08:34 PM, tj5527 wrote:

I create a table with the command `CREATE TABLE test1 (key text primary key, 
obj json);` with two records inserted

 # select * from test1;
 key1|{"a":1,"b":2,"c":{"x":99},"status":"done"} # record 1
 key2|{ "key": key2 } # record 2


The second record is not valid json because "key2" is not quoted. 
Causing the error.


Dan.



Now I want to retrieve the record that is not marked with status "done" (so basically it is 
expected to return the second record i.e. record 2 with key2). The command I use is `select * from test1, 
json_tree(test1.obj) where json_tree.value <> "done";` But it returns

 key1|{"a":1,"b":2,"c":{"x":99},"status":"done"}|a|1|integer|1|2|0|$.a|$
 key1|{"a":1,"b":2,"c":{"x":99},"status":"done"}|b|2|integer|2|4|0|$.b|$
 
key1|{"a":1,"b":2,"c":{"x":99},"status":"done"}|c|{"x":99}|object||6|0|$.c|$
 
key1|{"a":1,"b":2,"c":{"x":99},"status":"done"}|x|99|integer|99|8|6|$.c.x|$.c
 Error: malformed JSON

What is the correct SQL command to achieve such effect?

SQLite version I use is 3.11.0 2016-02-15 17:29:24

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



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

[sqlite] SQL command not equal comparison within json content

[tj5527]

I create a table with the command `CREATE TABLE test1 (key text primary key, 
obj json);` with two records inserted

# select * from test1;
key1|{"a":1,"b":2,"c":{"x":99},"status":"done"} # record 1
key2|{ "key": key2 } # record 2

Now I want to retrieve the record that is not marked with status "done" (so 
basically it is expected to return the second record i.e. record 2 with key2). 
The command I use is `select * from test1, json_tree(test1.obj) where 
json_tree.value <> "done";` But it returns

key1|{"a":1,"b":2,"c":{"x":99},"status":"done"}|a|1|integer|1|2|0|$.a|$
key1|{"a":1,"b":2,"c":{"x":99},"status":"done"}|b|2|integer|2|4|0|$.b|$
key1|{"a":1,"b":2,"c":{"x":99},"status":"done"}|c|{"x":99}|object||6|0|$.c|$

key1|{"a":1,"b":2,"c":{"x":99},"status":"done"}|x|99|integer|99|8|6|$.c.x|$.c
Error: malformed JSON

What is the correct SQL command to achieve such effect?

SQLite version I use is 3.11.0 2016-02-15 17:29:24

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

Re: [sqlite] Database is locked

[Frank Millman]

On 2/28/18 2:53 PM, Richard Damon wrote:

> 
> On 2/28/18 6:59 AM, Frank Millman wrote:
> > Hi all
> >
> > I am using Python 3.6.0 and sqlite3 3.20.1. I am getting the message 
> > ‘database is locked’ which, from reading the docs, I think is coming from 
> > an SQL_BUSY error.
> >
> > It does not behave in the way I expect.
> 
[...]
> 
> Your description, seeming to imply a total order, has an issue. If this 
> is happening in a single thread, then if it uses a second connection to 
> try and insert, that will block and the thread will never get to step 3, 
> so the commit will not happen. You have a dead lock.
> 

Of course! I should have thought of that.
I am using python’s asyncio module, so everything is happening in a single 
thread.
I understand what is happening now. Thanks very much.
Frank
 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] not able to save log to sqlite

[Joe Mistachkin]

Abhijit Dass wrote:
>
> i want to logging exception details to sqlite database using
> Nlog libraries, m okay when i store logging details by using
> ado.net code but m not able to insert log details when i use
> nlog.config. please help me out how can i insert logging
> details by nlog.config.
> 

I have not used this component; however, is there some reason
why the file name in the connection string is prefixed with a
dollar sign?

> 
>  connectionString="Data
> Source=$C:\Users\abhijitd\Desktop\Test\Log.db3;Version=3;"
> 

Also, the connection string must use "Data Source", with a
space between the words.  I'm not sure if the space is be
preserved in your config file.

--
Joe Mistachkin 

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

Re: [sqlite] Database is locked

[Richard Damon]

On 2/28/18 6:59 AM, Frank Millman wrote:

Hi all

I am using Python 3.6.0 and sqlite3 3.20.1. I am getting the message ‘database 
is locked’ which, from reading the docs, I think is coming from an SQL_BUSY 
error.

It does not behave in the way I expect. I tested using two concurrent 
connections – call them conn_1 and conn_2.

1. conn_1 performs an INSERT.

2. conn_2 performs an INSERT.

3. conn_1 sleeps for 1 second, then performs a COMMIT.

4. conn_2 sleeps for 1 second, then performs a COMMIT.

After step 2, the database is locked, which I understand. I don’t know which 
connection is holding the lock, but I don’t think that matters.

What I expected to happen was that, after one second, whichever connection was 
holding the lock would COMMIT, which would free up the other connection, which 
would then complete normally.

AFAICT, what happens in practice is that the lock is held for the default 
timeout of 5 seconds, then the connection which did *not* hold the lock fails 
with ‘database is locked’, and the connection which *did* hold the lock 
completes normally.

Is there any way to get the behaviour that I am looking for?

Thanks

Frank Millman
Your description, seeming to imply a total order, has an issue. If this 
is happening in a single thread, then if it uses a second connection to 
try and insert, that will block and the thread will never get to step 3, 
so the commit will not happen. You have a dead lock.


If steps 1 and 3 are in one thread, and 2 and 4 are in a different 
thread then this should normally work. The only possible issue would be 
if the Python wrapper for sqlite doesn't let the first thread run when 
the second thread blocks waiting to get access to perform the insert.


The description here would be described as (note, T1 and T2 are the two 
threads, and ordering between them is generally weak except as enforced 
by locks)


T1-1 conn_1 performs an INSERT
T1-2 conn_1 sleeps for 1 second
T2-1 conn_2 attempts to perfom an INSERT, but blocks
T1-3 conn_1 wakes up and performs a commit
T2-1a conn_2 wakes up and performs an INSERT
T2-2 conn_2 sleeps for 1 second
T2-3 conn_2 wakes up and performs a commit

Note, it is was just a single thread, then your description forces what 
I call T2-1 to complete (in T2-1a) before you get to T1-2, and thus 
before T1-3, but by the lock, T1-3 must complete before you get to 
T2-1a, so you deadlock.


--
Richard Damon

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

[sqlite] not able to save log to sqlite

[Abhijit Dass]

hi,
i want to logging exception details to sqlite database using Nlog
libraries, m okay when i store logging details by using ado.net code but m
not able to insert log details when i use nlog.config. please help me out
how can i insert logging details by nlog.config.

here is my code-


  http://www.nlog-project.org/schemas/NLog.xsd;
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance;
 throwExceptions="false">
   
 

 
   
   
   
   
 
   
   
 

 
 



Thanks and regards
Abhijit Dass
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Database is locked

[Frank Millman]

Hi all

I am using Python 3.6.0 and sqlite3 3.20.1. I am getting the message ‘database 
is locked’ which, from reading the docs, I think is coming from an SQL_BUSY 
error.

It does not behave in the way I expect. I tested using two concurrent 
connections – call them conn_1 and conn_2.

1. conn_1 performs an INSERT.

2. conn_2 performs an INSERT.

3. conn_1 sleeps for 1 second, then performs a COMMIT.

4. conn_2 sleeps for 1 second, then performs a COMMIT.

After step 2, the database is locked, which I understand. I don’t know which 
connection is holding the lock, but I don’t think that matters.

What I expected to happen was that, after one second, whichever connection was 
holding the lock would COMMIT, which would free up the other connection, which 
would then complete normally.

AFAICT, what happens in practice is that the lock is held for the default 
timeout of 5 seconds, then the connection which did *not* hold the lock fails 
with ‘database is locked’, and the connection which *did* hold the lock 
completes normally.

Is there any way to get the behaviour that I am looking for?

Thanks

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