[sqlite] creating a sqlite db on raw disk ?

2010-11-18 Thread Yang
when I create a db on a file system, I guess a query process has to go through 2 levels of seeks ? first sqlite finds the B-tree node that stores the index to the file offset of my desired record, then sqlite uses that offset to make syscall seek(offset), then Kernel consults the FS implementation

Re: [sqlite] Custom collating sequences and performance

2010-11-18 Thread Dan Kennedy
On 11/19/2010 05:22 AM, Duquette, William H (316H) wrote: > On 11/18/10 2:16 PM, "Drake Wilson" wrote: > > Quoth "Duquette, William H (316H)", on > 2010-11-18 14:08:10 -0800: >> It seems to me that it shouldn't be necessary for SQLite to

Re: [sqlite] UPDATE during SELECT

2010-11-18 Thread Igor Tandetnik
Nikolaus Rath wrote: > I understand that running INSERT or DELETE during an active SELECT query > can get me into trouble. But is it safe to run (in pseudocode): > > for value in "SELECT main_column IN mytable": > > UPDATE mytable SET other_column='foobar' WHERE

[sqlite] UPDATE during SELECT

2010-11-18 Thread Nikolaus Rath
Hello, I understand that running INSERT or DELETE during an active SELECT query can get me into trouble. But is it safe to run (in pseudocode): for value in "SELECT main_column IN mytable": UPDATE mytable SET other_column='foobar' WHERE main_column=value ? Thanks, -Nikolaus --

Re: [sqlite] Custom collating sequences and performance

2010-11-18 Thread Duquette, William H (316H)
On 11/18/10 2:16 PM, "Drake Wilson" wrote: Quoth "Duquette, William H (316H)" , on 2010-11-18 14:08:10 -0800: > It seems to me that it shouldn't be necessary for SQLite to evaluate > FOO's comparison function when doing queries on mytable;

Re: [sqlite] Custom collating sequences and performance

2010-11-18 Thread Drake Wilson
Quoth "Duquette, William H (316H)" , on 2010-11-18 14:08:10 -0800: > It seems to me that it shouldn't be necessary for SQLite to evaluate > FOO's comparison function when doing queries on mytable; the > collation order should be implicit in the mykey column's

[sqlite] Custom collating sequences and performance

2010-11-18 Thread Duquette, William H (316H)
Suppose I define a custom collating sequence FOO, and use it on an indexed column: CREATE TABLE mytable (mykey TEXT PRIMARY KEY COLLATE FOO, ...) It seems to me that it shouldn't be necessary for SQLite to evaluate FOO's comparison function when doing queries on mytable; the collation order

Re: [sqlite] Read database from multiple processes

2010-11-18 Thread Teg
Hello Prakash, What kind of drive is it? My experience is that SATA drives hate being banged on from multiple threads. They just can't handle multiple simultaneous work loads without slowing down drastically. They're fine with heavy duty single threaded IO. In my application, I serialize most

[sqlite] Read database from multiple processes

2010-11-18 Thread Prakash Reddy Bande
Hi, I have a database placed on a shared drive. Two processes (from different hosts) do a bunch of select commands. I have a busy handler that sleeps of 1 second in each attempt and bails out after 10 attempts. The observation is that, if only one process is running (on any host) the results

Re: [sqlite] Using foreign key reference on RowID

2010-11-18 Thread Bernard Ertl
Wow. Thanks. I see now that this is mentioned in the docs on the page for the VACUUM statement. It really should be mentioned on the CREATE TABLE page also where the rowid is explained. This is important information for people who are learning SQLite and trying to figure out how to design

Re: [sqlite] Using foreign key reference on RowID

2010-11-18 Thread Bernard Ertl
As someone who just started using SQLite without any previous background in SQL, it was confusing to me. I did a search on nabble through this mailing list and see now that I'm not the first person to ask about this issue. IMO, it would be helpful to people new to SQLite to mention this in the

Re: [sqlite] Using foreign key reference on RowID

2010-11-18 Thread Jay A. Kreibich
On Wed, Nov 17, 2010 at 04:36:12PM -0600, Bernard Ertl scratched on the wall: > Is it not possible to reference the SQLite > internal/default column for the RowID in a foreign key definition? Even if you could, you don't want to do this. Unless you define an ROWID alias (i.e. an INTEGER

Re: [sqlite] Using foreign key reference on RowID

2010-11-18 Thread Kees Nuyt
On Wed, 17 Nov 2010 16:36:12 -0600, "Bernard Ertl" wrote: >I'm getting a "foreign key mismatch" error with the following code: > >~~~ > >PRAGMA foreign_keys = ON; > >CREATE TABLE IF NOT EXISTS JobPlans (Name UNIQUE); > >CREATE TABLE IF NOT EXISTS Tasks (JobPlan_ID

Re: [sqlite] select where value in

2010-11-18 Thread Simon Slavin
On 18 Nov 2010, at 1:22pm, Joseph Garry wrote: >> From: itandet...@mvps.org >> Joseph Garry wrote: >>> I'd like to set up a compiled sqllite (sqlite3_prepare_v2) statement with a >>> query like >>> 'select tabid, col1, col2 from table where tabid in (?)' >>> But how do

Re: [sqlite] select where value in

2010-11-18 Thread Joseph Garry
That would work, but the thing I'm after here is speed. And I can't imagine what you're suggesting would be very fast. Am I mistaken? > To: sqlite-users@sqlite.org > From: itandet...@mvps.org > Date: Thu, 18 Nov 2010 08:08:51 -0500 > Subject: Re: [sqlite] select where value in > > Joseph

Re: [sqlite] select where value in

2010-11-18 Thread Igor Tandetnik
Joseph Garry wrote: > I'd like to set up a compiled sqllite (sqlite3_prepare_v2) statement with a > query like > 'select tabid, col1, col2 from table where tabid in (?)' > But how do I bind in the parameter here? An example would be welcome, of > course. You can't do

[sqlite] select where value in

2010-11-18 Thread Joseph Garry
I'd like to set up a compiled sqllite (sqlite3_prepare_v2) statement with a query like 'select tabid, col1, col2 from table where tabid in (?)' But how do I bind in the parameter here? An example would be welcome, of course.

Re: [sqlite] append to a column?

2010-11-18 Thread Igor Tandetnik
Yang wrote: > for a row, and one column (most likely blob type), I want to append > some value to the end of the blob, I do this many times. then I may > read up all the appended sections and write out the entire blob with a > new value BLOB API:

Re: [sqlite] How to make this calculation in VIEW

2010-11-18 Thread O'Neill, Owen
Hi Jeff, I haven't actually tried it, but just by inspection I would guess that a view can't refer to another column within itself, so there are 2 options. Create a second view on top of the first view. (I've not tested this - note how the view name is aliased to just 'Patterns' because I'm

Re: [sqlite] How to make this calculation in VIEW

2010-11-18 Thread Simon Davies
On 17 November 2010 22:13, Jeff Archer wrote: > First let me say thank you to all for the very good support that receive here. > > I have the these tables and view > . . . > > I would like to add these calculated columns to my view but not sure how to > make >

Re: [sqlite] threads and last_insert_rowid()

2010-11-18 Thread Jay A. Kreibich
On Tue, Nov 16, 2010 at 04:08:16PM +0300, Ruslan Mullakhmetov scratched on the wall: > i got following contradictory replies to my question > > > if i execute query like " insert into tbl( filed ) VALUES ( 1 ); Select > > last_insert_rowid() as li;" would be it atomic? or it anyway would

Re: [sqlite] Selective update of a column

2010-11-18 Thread Amit Chaudhuri
Simon - thanks v. much. Makes complete sense now - and it does precisely what I wanted. :) {Appreciate the tip on the index - performance hasn't been an issue yet but I'm sure I'll end up using it.} On Wed, Nov 17, 2010 at 12:51 PM, Simon Slavin wrote: > > On 16 Nov 2010,

[sqlite] Using foreign key reference on RowID

2010-11-18 Thread Bernard Ertl
I'm getting a "foreign key mismatch" error with the following code: ~~~ PRAGMA foreign_keys = ON; CREATE TABLE IF NOT EXISTS JobPlans (Name UNIQUE); CREATE TABLE IF NOT EXISTS Tasks (JobPlan_ID INTEGER NOT NULL REFERENCES JobPlans(RowID) ON DELETE CASCADE, UID UNIQUE NOT NULL); INSERT INTO

[sqlite] How to make this calculation in VIEW

2010-11-18 Thread Jeff Archer
First let me say thank you to all for the very good support that receive here. I have the these tables and view // Patterns table CREATE TABLE IF NOT EXISTS [Patterns] ( PatternID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, Name  VARCHAR NOT NULL UNIQUE, Description   VARCHAR NOT

Re: [sqlite] append to a column?

2010-11-18 Thread Kees Nuyt
On Thu, 18 Nov 2010 01:45:22 -0800, Yang wrote: >I wonder if I can do this efficiently in sqlite: > >for a row, and one column (most likely blob type), I want to append >some value to the end of the blob, I do this many times. then I may >read up all the appended sections

[sqlite] append to a column?

2010-11-18 Thread Yang
I wonder if I can do this efficiently in sqlite: for a row, and one column (most likely blob type), I want to append some value to the end of the blob, I do this many times. then I may read up all the appended sections and write out the entire blob with a new value without any special support,