[sqlite] Recipe to safely move/rename a database?

I have a scenario where I want to move 99+% of the records from one database to another, initially empty but for a set of table definitions (in practice, copied from a template file). On my Linux platform, I find that the INSERT INTO archive.my_table SELECT * FROM my_table WHERE (...) takes

Re: [sqlite] Do all db's in a connection need same level of lock?

But I did notice my code was doing a BEGIN EXCLUSIVE. Would this cause an unmodified database to be locked anyway? Chris On Thu, Jan 29, 2009 at 2:02 AM, Dan danielk1...@gmail.com wrote: On Jan 29, 2009, at 3:13 AM, Chris Eich wrote: I have read http://sqlite.org/atomiccommit.html which seems

[sqlite] Do all db's in a connection need same level of lock?

I have read http://sqlite.org/atomiccommit.html which seems to imply that all db files in a transaction are locked at the same level as the transaction progresses (i.e. all Reserved, then all Exclusive, ...). This makes sense when all the files are being changed, but I have a use case where I am

Re: [sqlite] Trigger commands don't update new record?

[Sorry if you see this twice; I sent it yesterday and see it in the archives, but never got it in my inbox. --Chris] On Sun, Nov 16, 2008 at 12:59 PM, Chris Eich [EMAIL PROTECTED] wrote: I have an events table where SET events for a given device_id and event_code are followed by CLR events

Re: [sqlite] Trigger commands don't update new record?

device_event SET event_correlation_id = new.event_id WHERE event_id = new.event_correlation_id; END; Chris On Mon, Nov 17, 2008 at 9:11 AM, Chris Eich [EMAIL PROTECTED] wrote: [Sorry if you see this twice; I sent it yesterday and see it in the archives, but never got it in my inbox. --Chris

[sqlite] Trigger commands don't update new record?

I have an events table where SET events for a given device_id and event_code are followed by CLR events. CREATE TABLE device_event ( event_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, device_id INTEGER NOT NULL REFERENCES device, event_date INTEGER NOT NULL, event_code INTEGER

Re: [sqlite] Re: Re: wrong index chosen, why?

order) the device_id can't be plugged into the dpi1 index. EXPLAIN QUERY PLAN output doesn't show how much of the index will be used--does anyone have a quick hack to show that (I'm studying where.c :-)? Chris On 7/10/07, Joe Wilson [EMAIL PROTECTED] wrote: --- Chris Eich [EMAIL PROTECTED] wrote

Re: [sqlite] wrong index chosen, why?

] [EMAIL PROTECTED] wrote: Joe Wilson [EMAIL PROTECTED] wrote: --- Chris Eich [EMAIL PROTECTED] wrote: Thanks, I tried both 3.3.13 and 3.4.0. With 3.3.13 the +'s didn't make a difference; the loops always were in d, dpi order with index dpi1 used. With 3.4.0 the loops were always in dpi, d order

Re: [sqlite] wrong index chosen, why?

Joe, the two plans are actually equal (within a few %). Thanks for all your help folks! I've learned quite a bit and I hope other readers did too. Chris

[sqlite] optimizing a query with ORDER BY and LIMIT

Me again. I said at the end of the last thread that I had learned a lot. What I learned is that my query's performance problems were not due to picking a bad index (given my dataset, the two indices were identical for this query). Instead the problem seems to be caused by ORDER BY and LIMIT

[sqlite] Re: optimizing a query with ORDER BY and LIMIT

I think the simplest approach for me is to get rid of the join on device. After I did so, SQLite uses the index on interval_end_date. I'll just need to grab the device - device_type mapping and interpret it myself. Chris On 7/11/07, Chris Eich [EMAIL PROTECTED] wrote: Me again. I said

[sqlite] wrong index chosen, why?

I have a database with the following tables: CREATE TABLE device ( device_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, device_type INTEGER NOT NULL, -- lookup in device_type ... ); CREATE TABLE device_perf_interval ( interval_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,

Re: [sqlite] Re: wrong index chosen, why?

I realize that but thought that device_id would be included because of the d.device_id=dpi.device_id term. What am I missing? Chris On 7/10/07, Igor Tandetnik [EMAIL PROTECTED] wrote: and two indexes on the latter table: CREATE INDEX dpi1 ON device_perf_interval( interval_type,

Re: [sqlite] Re: wrong index chosen, why?

something? Chris P.S. The vast majority of dpi rows have the same type and duration, so an index on just those wouldn't help much. On 7/10/07, Joe Wilson [EMAIL PROTECTED] wrote: --- Chris Eich [EMAIL PROTECTED] wrote: I realize that but thought that device_id would be included because

Re: [sqlite] Re: Re: wrong index chosen, why?

The output of EXPLAIN QUERY PLAN doesn't change when I use the CROSS JOIN trick to disable table reordering: sqlite explain query plan SELECT d.device_type, dpi.* FROM device d, device_perf_interval dpi WHERE d.device_id=dpi.device_id AND dpi.interval_type=1 AND dpi.interval_duration=300;