Re: [sqlite] Bugreport - slowdown in sqlite after the ANALYZE statement

On Tue, 9 Dec 2014 10:38:34 -0500 "James K. Lowden" wrote: > On Tue, 09 Dec 2014 12:06:20 +0100 > Jan Stan?k wrote: > > > INSERT INTO CoreCache (ModelID, ItemID) > > SELECT > ... > > ORDER BY Year > > Why ORDER BY on INSERT? Does it work

Re: [sqlite] Online/Hot backup of WAL journalling mode database

On 12/10/2014 05:06 AM, Simon Slavin wrote: On 9 Dec 2014, at 8:57pm, Nick wrote: Environment is Linux with multiple (c. 4-6) processes accessing a single sqlite database named "test.db". Backup: - New process started using cronjob to initiate application checkpoint

Re: [sqlite] Online/Hot backup of WAL journalling mode database

On 10 Dec 2014, at 12:30am, Nick wrote: > That's interesting Simon I didn't expect the database not to be trustworthy. The database will be trustworthy at any instant. Your copy of it will be corrupt because the file will be changing while you are copying it. > In WAL

Re: [sqlite] Online/Hot backup of WAL journalling mode database

On 9 Dec 2014, at 22:06, Simon Slavin wrote: > > On 9 Dec 2014, at 8:57pm, Nick wrote: > >> Environment is Linux with multiple (c. 4-6) processes accessing a single >> sqlite database named "test.db". >> >> Backup: >> - New process started using cronjob to initiate

Re: [sqlite] seeking advice

On 2014/12/09 22:41, Rene Zaumseil wrote: Hi there, I have to store and retrieve up to 2000 parameters. The parameters can have real and integer values. The max. change rate is 100ms and the max. duration is up to some hours. The simple solution would be to use plain binary files. It's fast

[sqlite] Docs: typos in SQLite Result Codes

There are some typos on the documentation page for SQLite Result codes "http://sqlite.org/rescode.html;. Under the heading "(1038) SQLITE_CANTOPEN_CONVPATH", the paragraph talks about the error code "SQLITE_CANTOPEN_SEEK". That constant does not exist. The text probably means

Re: [sqlite] Online/Hot backup of WAL journalling mode database

On 9 Dec 2014, at 8:57pm, Nick wrote: > Environment is Linux with multiple (c. 4-6) processes accessing a single > sqlite database named "test.db". > > Backup: > - New process started using cronjob to initiate application checkpoint until > completion. > - rsync diff the

[sqlite] Online/Hot backup of WAL journalling mode database

Hi, I'd like to check my understanding of Sqlite in WAL journalling mode. With automatic checkpointing turned off would the following psuedo-code result in a online backup approach that allows robust restore of the database with data fresh up to the last checkpoint? Environment is Linux with

Re: [sqlite] seeking advice

On 9 Dec 2014, at 8:41pm, Rene Zaumseil wrote: > Version 3: One table with time stamp, parameter id and parameter value > - Is it working when all values change? > - Is retrieving values for one parameter fast? That one. Versions 1 & 2 will both, technically, work,

[sqlite] seeking advice

Hi there, I have to store and retrieve up to 2000 parameters. The parameters can have real and integer values. The max. change rate is 100ms and the max. duration is up to some hours. The simple solution would be to use plain binary files. It's fast but not flexible. So I came to sqlite.

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

I just thought of what may be a simpler solution. I'm assuming that there is a certain limit to the length of the books (positions can be safely assumed to never exceed say, 100,000) So what can be done is update page set position=position + 10 where position>='3'; insert into

Re: [sqlite] Artificially slow VACUUM by injecting a sleep() somewhere?

> Also, SSD drives wear out fast. We don't have good figures yet for > mass-produced drives (manufacturers introduce new models faster than the old > ones wear out, so it's hard to gather stats) but typical figures show a drive > failing in from 2,000 to 3,000 write cycles of each single

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

I just thought of what may be a simpler solution. I'm assuming that there is a certain limit to the length of the books (positions can be safely assumed to never exceed say, 100,000) So what can be done is update page set position=position + 10 where position>='3'; insert into

Re: [sqlite] Artificially slow VACUUM by injecting a sleep() somewhere?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/08/2014 05:36 PM, David Barrett wrote: > *Re: "a simple way is to sleep in the progress callback"* -- Can > you tell me more about this? Are you referring to the callback > provided to sqlite3_exec(), or something else?

Re: [sqlite] How do I update multiple rows in a single sql statement

On 12/9/2014 10:38 AM, James K. Lowden wrote: If the subquery to the right of the SET clause produces more than one row, the statement fails. Are you sure? Normally, a scalar subquery doesn't fail when the resultset contains more than one row - it just silently produces the value from the

Re: [sqlite] Bugreport - slowdown in sqlite after the ANALYZE statement

On Tue, 09 Dec 2014 12:06:20 +0100 Jan Stan?k wrote: > INSERT INTO CoreCache (ModelID, ItemID) > SELECT ... > ORDER BY Year Why ORDER BY on INSERT? Does it work better? I would expect the unnecessary sort to be pure overhead. --jkl

Re: [sqlite] How do I update multiple rows in a single sql statement

On Mon, 08 Dec 2014 20:57:00 -0500 Igor Tandetnik wrote: > Yes, there are workarounds (a view; or REPLACE INTO may sometimes be > pressed into service). But I, for one, kinda miss UPDATE ... FROM. Be careful what you wish for. :-) The only implementation of

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

On Mon, 08 Dec 2014 15:48:41 +0200 RSmith wrote: > > UPDATE pages SET position = position + 1 WHERE book_id = 0 AND > > position >= 1; > > NOT a bug... the moment you SET position to position +1 for the > first iteration of the query, it tries to make that entry look like >

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

On Mon, 08 Dec 2014 22:01:15 +0700 Dan Kennedy wrote: > On 12/08/2014 09:55 PM, Nico Williams wrote: > > Ideally there would be something like DEFERRED foreign key checking > > for uniqueness constraints... > > You could hack SQLite to do enforce unique constraints the

Re: [sqlite] Drop Table Behavior

Lukas wrote: > PRAGMA foreign_keys = ON; > > create table a ( id int primary key ); > create table b ( id int primary key ); > create table c ( id int primary key, > aid int, > bid int, > foreign key (aid) references a (id) on delete cascade, >

[sqlite] Drop Table Behavior

Hello We found some strange behavior we can not explain. We execute the following script: PRAGMA foreign_keys = ON; create table a ( id int primary key ); create table b ( id int primary key ); create table c (id int primary key, aid int, bid int, foreign key (aid) references a (id) on

Re: [sqlite] Bugreport - slowdown in sqlite after the ANALYZE statement

Answered by adding a comment at https://bugzilla.redhat.com/show_bug.cgi?id=1161844 On Tue, Dec 9, 2014 at 6:06 AM, Jan Staněk wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Hi, > some of the banshee users noticed a huge slowdown in its operation > after

Re: [sqlite] Using binding in sqlite insert statements

Thanks a lot for your reply Simon. It was returning SQLITE_DONE. (But I have checked for SQLITE_OK in my code). It's fixed now Thank you Prakash On Tue, Dec 9, 2014 at 5:02 PM, Simon Davies wrote: > On 9 December 2014 at 10:56, Prakash Premkumar

Re: [sqlite] Using binding in sqlite insert statements

On 9 December 2014 at 10:56, Prakash Premkumar wrote: > Hi, > > I'm trying to use the sqlite_bind function calls to pass values to the > insert queries in sql. > > Here's the code I'm trying to execute: http://pastebin.com/kMvAHsrk > > I get an error after the

[sqlite] Bugreport - slowdown in sqlite after the ANALYZE statement

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, some of the banshee users noticed a huge slowdown in its operation after upgrading to version 3.8.7 from 3.8.6. Here is the related log : [4 Debug 13:24:27.263] Executed in 12819ms DELETE FROM CoreCache WHERE ModelID = 9;

[sqlite] Using binding in sqlite insert statements

Hi, I'm trying to use the sqlite_bind function calls to pass values to the insert queries in sql. Here's the code I'm trying to execute: http://pastebin.com/kMvAHsrk I get an error after the sqlite3_step() function call : The error message is "Unknown error". Can you kindly help me fix this ?

Re: [sqlite] How do I update multiple rows in a single sql statement

On 9-12-2014 02:31, Igor Tandetnik wrote: On 12/8/2014 8:20 PM, Keith Medcalf wrote: update temp_table set id=(select id from some_table where c=42), operation='UPDATE' where exists (select 1 from some_table s where s.a=temp_table.a and s.b=temp_table.b and s.c=42); is the proper

Re: [sqlite] Artificially slow VACUUM by injecting a sleep() somewhere?

On 2014/12/09 03:36, David Barrett wrote: Hi all, great questions: *Re: Why VACUUM.* We vacuum weekly. This particular database is a "rolling journal" -- we are constantly adding new rows to the end of the table, and every week we truncate off the head of the journal to only keep 3M rows at