Re: [sqlite] Bug in Apress SQLite book. Was: Different result set of query in examples of SQLite book

On 2013.06.13 7:22 PM, Yongil Jang wrote: Thank you, Richard and James. 2013/6/14 James K. Lowden Why not simply SELECT f.name, count(e.food_id) as 'episodes' FROM foods as f OUTER JOINfoods_episodes as e ON f.id = e.food_id GROUP BY f.name ORDER

[sqlite] SQLite3 Database access

Hi, I am quite new with SQLite3, tried sample application to create, read & write database using SQLite3. Now I am trying to write test where multiple process are trying to access database using SQLite3. To get best result w.r.t Speed & Concurrency what is best mode & mechanism to be used.

Re: [sqlite] Bug in Apress SQLite book. Was: Different result set of query in examples of SQLite book

Thank you, Richard and James. 2013/6/14 James K. Lowden > > > Why not simply > > SELECT f.name, count(e.food_id) as 'episodes' > FROM foods as f > OUTER > JOINfoods_episodes as e > ON f.id = e.food_id > GROUP BY f.name > ORDER BY episodes DESC LIMIT 10;

Re: [sqlite] Prepared statement invariants

On 6/13/2013 9:15 PM, Maxim Khitrov wrote: This works and also triggers SQLITE_SCHEMA with v1 interface. I did a few more tests and it looks like the schema changes are ignored if the statement is in the middle of iteration. As you said, only the first step after a prepare/reset causes the

Re: [sqlite] Prepared statement invariants

On Thu, Jun 13, 2013 at 8:36 PM, Igor Tandetnik wrote: > On 6/13/2013 8:29 PM, Igor Tandetnik wrote: >> >> The column addition should have failed, unless you are using WAL in >> which case changes made by the writer are not visible to outstanding >> readers. A useful test

Re: [sqlite] Prepared statement invariants

On 6/13/2013 8:29 PM, Igor Tandetnik wrote: The column addition should have failed, unless you are using WAL in which case changes made by the writer are not visible to outstanding readers. A useful test would be to prepare the statement, then get column count (without ever stepping), then add

Re: [sqlite] Prepared statement invariants

On 6/13/2013 8:04 PM, Maxim Khitrov wrote: I'd like to check my assumptions. After preparing a statement using the v2 interface, which functions are guaranteed to return the same values for that statement instance? These are the ones that I'm interested in at the moment:

[sqlite] Prepared statement invariants

Hello, I'd like to check my assumptions. After preparing a statement using the v2 interface, which functions are guaranteed to return the same values for that statement instance? These are the ones that I'm interested in at the moment: sqlite3_bind_parameter_count sqlite3_bind_parameter_name

Re: [sqlite] Bug in Apress SQLite book. Was: Different result set of query in examples of SQLite book

On Thu, 13 Jun 2013 08:13:29 -0400 Richard Hipp wrote: > SELECT > name, > (SELECT COUNT(food_id) FROM foods_episodes WHERE food_id=f.id) count > FROM > foods f > ORDER BY count DESC LIMIT 10; ... > SELECT > name, > (SELECT COUNT(*) FROM foods_episodes WHERE

[sqlite] Bug in Apress SQLite book. Was: Different result set of query in examples of SQLite book

On Thu, Jun 13, 2013 at 12:24 AM, Yongil Jang wrote: > Dear all, > > Following select query returns different result data between v3.7.11 and > v3.7.13~. > > CREATE TABLE foods( > id integer primary key, > type_id integer, > name text ); > CREATE TABLE foods_episodes(

Re: [sqlite] Fine tuning SQLite performance with WAL mode

On 13 Jun 2013, at 7:34am, Runcy Oommen wrote: > PRAGMA journal_mode = wal; WAL is the new modern way to do things and is generally better in lots of ways. Use it unless it causes problems for you. > PRAGMA wal_autocheckpoint = 10; > > Now I know that the default

[sqlite] Fine tuning SQLite performance with WAL mode

Hello everyone, (CAUTION: Lengthy email, but very interesting issue and hence requesting your attention to help) My application uses 3 SQLite DBs with varying degrees of usage (which I will explain below). After reading about WAL mode, switched all my DBs from delete to wal. I'm however