[sqlite] creating a sqlite db on raw disk ?
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 to find from its OWN B-tree (for example in ext3 fs ) the block location of that offset. innodb allows creating a db on a raw disk partition, can we do the same on sqlite? I tried directly creating a db on ramdisk, failed: javasqlite-20100727# sqlite3 /dev/ram0 SQLite version 3.7.3 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table blah ( x int ); Error: disk I/O error note that I was able to create an e2fs on the same ramdisk Thanks Yang ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Custom collating sequences and performance
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 evaluate >> FOO's comparison function when doing queries on mytable; the >> collation order should be implicit in the mykey column's index. Is >> this in fact the case? > > When doing which queries? > > How do you propose to look up a key value in the index without using > the collation function? > > I was thinking of queries like > > SELECT mykey FROM mytab ORDER BY mykey The collation sequence callback will not be invoked in that case. SQLite will just iterate from start to finish of the index b-tree. You do need to have the collation sequence registered when compiling the query though. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE during SELECT
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 main_column=value It should be safe. Though I don't see how this would be different than just updating all rows in a single query: UPDATE mytable SET other_column='foobar'; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] UPDATE during SELECT
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 -- »Time flies like an arrow, fruit flies like a Banana.« PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Custom collating sequences and performance
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; the > collation order should be implicit in the mykey column's index. Is > this in fact the case? When doing which queries? How do you propose to look up a key value in the index without using the collation function? I was thinking of queries like SELECT mykey FROM mytab ORDER BY mykey But yes, looking up a particular record would have to use the collation function function, wouldn't it. Will -- Will Duquette -- william.h.duque...@jpl.nasa.gov Athena Development Lead -- Jet Propulsion Laboratory "It's amazing what you can do with the right tools." ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Custom collating sequences and performance
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 index. Is > this in fact the case? When doing which queries? How do you propose to look up a key value in the index without using the collation function? ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Custom collating sequences and performance
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 should be implicit in the mykey column's index. Is this in fact the case? Thanks! Will -- Will Duquette -- william.h.duque...@jpl.nasa.gov Athena Development Lead -- Jet Propulsion Laboratory "It's amazing what you can do with the right tools." ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Read database from multiple processes
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 heavy duty disk IO for this reason. If the drive is hanging off a USB interface, it's much worse. In your case, I might consider a "server" application that answers requests from the clients so, only one process is banging on the disk at a time. C Thursday, November 18, 2010, 4:21:04 PM, you wrote: PRB> Hi, PRB> I have a database placed on a shared drive. PRB> Two processes (from different hosts) do a bunch of select commands. PRB> I have a busy handler that sleeps of 1 second in each attempt PRB> and bails out after 10 attempts. PRB> The observation is that, if only one process is running (on any PRB> host) the results are returned pretty fast. However, if both PRB> processes are doing the selects concurrently the performance is dead slow. PRB> Note that each process is doing somewhere around 5000 selects. PRB> My understanding is that when only selects are happening there is no locking involved. PRB> I am using 3.7.2. PRB> Regards, PRB> Prakash Bande PRB> Altair Engg. Inc. PRB> Troy MI PRB> Ph: 248-614-2400 ext 489 PRB> Cell: 248-404-0292 PRB> ___ PRB> sqlite-users mailing list PRB> sqlite-users@sqlite.org PRB> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Read database from multiple processes
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 are returned pretty fast. However, if both processes are doing the selects concurrently the performance is dead slow. Note that each process is doing somewhere around 5000 selects. My understanding is that when only selects are happening there is no locking involved. I am using 3.7.2. Regards, Prakash Bande Altair Engg. Inc. Troy MI Ph: 248-614-2400 ext 489 Cell: 248-404-0292 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using foreign key reference on RowID
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 their database! Jay A. Kreibich-2 wrote: > > 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 PRIMARY KEY column) > ROWID values are not preserved across vacuums or dumps. > >-j > > -- > Jay A. Kreibich < J A Y @ K R E I B I.C H > > > "Intelligence is like underwear: it is important that you have it, > but showing it to the wrong people has the tendency to make them > feel uncomfortable." -- Angela Johnson > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://old.nabble.com/Using-foreign-key-reference-on-RowID-tp30246958p30248863.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using foreign key reference on RowID
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 docs on the foreign key support page: http://www.sqlite.org/foreignkeys.html It's not clear from the CREATE TABLE page in the docs: http://www.sqlite.org/lang_createtable.html if there are any performance issues or other considerations in defining an alias to the rowid. The text there doesn't really offer any reason to someone new to the system to use an alias. Seems like a duplication of work for no benefit (because the benefits aren't clearly explained). Maybe this text should be updated so people are encouraged to use an alias instead of the 'hidden' column. Kees Nuyt wrote: > > 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 INTEGER NOT NULL REFERENCES JobPlans(RowID) ON DELETE CASCADE, UID UNIQUE NOT NULL); >> >>INSERT INTO JobPlans(Name) VALUES ('234234'); >> >># Following line generates the error: >>INSERT INTO Tasks(JobPlan_ID,UID) VALUES (1,'ZZZ'); >>~~~ >> >> If I explicitly declare an alias for the RowID: >> >>CREATE TABLE IF NOT EXISTS JobPlans (RowID INTEGER PRIMARY KEY, Name UNIQUE); >> >> I don't get the error. Is it not possible >> to reference the SQLite internal/default column >> for the RowID in a foreign key definition? > > No it isn't. In general you can't refer to anything that is not part > of your schema. > >> The online docs should be updated to reflect this. > > Disputable, as this is not specific for SQLite, it's part of SQL. > The fact that ROWID is something hidden is documented well enough. > > > The definition: > CREATE TABLE JobPlans ( > id INTEGER PRIMARY KEY NOT NULL, > Name UNIQUE > ); > is physically the same as > CREATE TABLE JobPlans ( > Name UNIQUE > ); > anyway, so what's the problem defining the alias? > Using the alias is much more portable. > > Note that the rowid alias doesn't have to be called RowID at all. > > CREATE TABLE IF NOT EXISTS Tasks ( > JobPlan_ID INTEGER NOT NULL > REFERENCES JobPlans(id) > ON DELETE CASCADE, > UID UNIQUE NOT NULL > ); > -- > ( Kees Nuyt > ) > c[_] > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://old.nabble.com/Using-foreign-key-reference-on-RowID-tp30246958p30248826.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using foreign key reference on RowID
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 PRIMARY KEY column) ROWID values are not preserved across vacuums or dumps. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using foreign key reference on RowID
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 INTEGER NOT NULL REFERENCES >JobPlans(RowID) ON DELETE CASCADE, UID UNIQUE NOT NULL); > >INSERT INTO JobPlans(Name) VALUES ('234234'); > ># Following line generates the error: >INSERT INTO Tasks(JobPlan_ID,UID) VALUES (1,'ZZZ'); >~~~ > > If I explicitly declare an alias for the RowID: > >CREATE TABLE IF NOT EXISTS JobPlans (RowID INTEGER PRIMARY KEY, Name UNIQUE); > > I don't get the error. Is it not possible > to reference the SQLite internal/default column > for the RowID in a foreign key definition? No it isn't. In general you can't refer to anything that is not part of your schema. > The online docs should be updated to reflect this. Disputable, as this is not specific for SQLite, it's part of SQL. The fact that ROWID is something hidden is documented well enough. The definition: CREATE TABLE JobPlans ( id INTEGER PRIMARY KEY NOT NULL, Name UNIQUE ); is physically the same as CREATE TABLE JobPlans ( Name UNIQUE ); anyway, so what's the problem defining the alias? Using the alias is much more portable. Note that the rowid alias doesn't have to be called RowID at all. CREATE TABLE IF NOT EXISTS Tasks ( JobPlan_ID INTEGER NOT NULL REFERENCES JobPlans(id) ON DELETE CASCADE, UID UNIQUE NOT NULL ); -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select where value in
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 I bind in the parameter here? An example would be welcome, of >>> course. >> >> You can't do that directly. One way is to create a temp table, like this: >> >> create temp table TabIdList (tabid integer); >> >> Now you can prepare this statement: >> >> select tabid, col1, col2 from myTable where tabid in (select tabid from >> TabIdList); >> >> Populate TabIdList with your list of IDs (you can use a prepared statement >> for this, too, along the lines of "insert into TabIdList(tabid) values (?); >> "). > 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? Put an index on the 'tabid' field of myTable and it'll be pretty fast. (PS: I fixed the text order of your reply.) Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select where value in
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 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 that directly. One way is to create a temp table, like this: > > create temp table TabIdList (tabid integer); > > Now you can prepare this statement: > > select tabid, col1, col2 from myTable where tabid in (select tabid from > TabIdList); > > Populate TabIdList with your list of IDs (you can use a prepared statement > for this, too, along the lines of "insert into TabIdList(tabid) values (?); > "). > -- > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select where value in
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 that directly. One way is to create a temp table, like this: create temp table TabIdList (tabid integer); Now you can prepare this statement: select tabid, col1, col2 from myTable where tabid in (select tabid from TabIdList); Populate TabIdList with your list of IDs (you can use a prepared statement for this, too, along the lines of "insert into TabIdList(tabid) values (?); "). -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] select where value in
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. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] append to a column?
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: http://www.sqlite.org/c3ref/blob_open.html -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to make this calculation in VIEW
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 lazy) CREATE VIEW IF NOT EXISTS [vwPatterns2] AS SELECT Patterns.Name AS Pattern, Patterns.Origin_X, Patterns.Origin_Y, Patterns.wMicrons, Patterns.hMicrons, COUNT(DISTINCT Offset_X) AS nTilesX, COUNT(DISTINCT Offset_Y) AS nTilesY, Patterns.wPixels, Patterns.hPixels, Patterns.wMicrons * nTilesX AS wTotalMicrons, Patterns.hMicrons * nTilesY AS hTotalMicrons, Patterns.wPixels * nTilesX AS wTotalPixels, Patterns.hPixels * nTilesY AS hTotalPixels, Patterns.Description FROM Tiles INNER JOIN vwPatterns AS Patterns ON Tiles.PatternID = vwPatterns.PatternID GROUP BY Tiles.PatternID; Or change the definition of the column within the view to.. Patterns.wMicrons * COUNT(DISTINCT Offset_X) AS wTotalMicrons, Hmm will this work ? try it and see ! Cheers Owen -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jeff Archer Sent: Wednesday, November 17, 2010 10:14 PM To: SQLite-user.org Subject: [sqlite] How to make this calculation in VIEW 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 NULL, Origin_X REAL NOT NULL DEFAULT 0.0, Origin_Y REAL NOT NULL DEFAULT 0.0, wMicrons REAL NOT NULL DEFAULT 0.0, hMicrons REAL NOT NULL DEFAULT 0.0, wPixels INTEGER NOT NULL DEFAULT 0.0, hPixels INTEGER NOT NULL DEFAULT 0.0 ); // Tiles table CREATE TABLE IF NOT EXISTS [Tiles] (\n" TileID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" PatternID INTEGER NOT NULL REFERENCES [Patterns] DEFERRABLE INITIALLY DEFERRED,\n" Offset_X REAL NOT NULL DEFAULT 0.0,\n" Offset_Y REAL NOT NULL DEFAULT 0.0\n" ); // vwPatterns CREATE VIEW IF NOT EXISTS [vwPatterns] AS SELECT Patterns.Name AS Pattern, Patterns.Origin_X, Patterns.Origin_Y, Patterns.wMicrons, Patterns.hMicrons, COUNT(DISTINCT Offset_X) AS nTilesX, COUNT(DISTINCT Offset_Y) AS nTilesY, Patterns.wPixels, Patterns.hPixels, Patterns.Description FROM Tiles INNER JOIN Patterns ON Tiles.PatternID = Patterns.PatternID GROUP BY Tiles.PatternID; I would like to add these calculated columns to my view but not sure how to make this work. Patterns.wMicrons * nTilesX AS wTotalMicrons, Patterns.hMicrons * nTilesY AS hTotalMicrons, Patterns.wPixels * nTilesX AS wTotalPixels, Patterns.hPixels * nTilesY AS hTotalPixels, i.e. // vwPatterns CREATE VIEW IF NOT EXISTS [vwPatterns] AS SELECT Patterns.Name AS Pattern, Patterns.Origin_X, Patterns.Origin_Y, Patterns.wMicrons, Patterns.hMicrons, COUNT(DISTINCT Offset_X) AS nTilesX, COUNT(DISTINCT Offset_Y) AS nTilesY, Patterns.wPixels, Patterns.hPixels, Patterns.wMicrons * nTilesX AS wTotalMicrons, Patterns.hMicrons * nTilesY AS hTotalMicrons, Patterns.wPixels * nTilesX AS wTotalPixels, Patterns.hPixels * nTilesY AS hTotalPixels, Patterns.Description FROM Tiles INNER JOIN Patterns ON Tiles.PatternID = Patterns.PatternID GROUP BY Tiles.PatternID; But it get error: "no such column: nTilesX" It works if I substitute a constant for the nTilesX and nTilesY so I think I do not know how to correctly reference these. Thank you. Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to make this calculation in VIEW
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 > this work. > > Patterns.wMicrons * nTilesX AS wTotalMicrons, > Patterns.hMicrons * nTilesY AS hTotalMicrons, > Patterns.wPixels * nTilesX AS wTotalPixels, > Patterns.hPixels * nTilesY AS hTotalPixels, > > i.e. > // vwPatterns > CREATE VIEW IF NOT EXISTS [vwPatterns] AS > SELECT Patterns.Name AS Pattern, > Patterns.Origin_X, > Patterns.Origin_Y, > Patterns.wMicrons, > Patterns.hMicrons, > COUNT(DISTINCT Offset_X) AS nTilesX, > COUNT(DISTINCT Offset_Y) AS nTilesY, > Patterns.wPixels, > Patterns.hPixels, > Patterns.wMicrons * nTilesX AS wTotalMicrons, > Patterns.hMicrons * nTilesY AS hTotalMicrons, > Patterns.wPixels * nTilesX AS wTotalPixels, > Patterns.hPixels * nTilesY AS hTotalPixels, > Patterns.Description > FROM Tiles INNER JOIN Patterns ON Tiles.PatternID = Patterns.PatternID > GROUP BY Tiles.PatternID; > > But it get error: "no such column: nTilesX" CREATE VIEW IF NOT EXISTS [vwPatterns] AS SELECT Patterns.Name AS Pattern, Patterns.Origin_X, Patterns.Origin_Y, Patterns.wMicrons, Patterns.hMicrons, COUNT(DISTINCT Offset_X) AS nTilesX, COUNT(DISTINCT Offset_Y) AS nTilesY, Patterns.wPixels, Patterns.hPixels, Patterns.wMicrons * COUNT(DISTINCT Offset_X) AS wTotalMicrons, Patterns.hMicrons * COUNT(DISTINCT Offset_Y) AS hTotalMicrons, Patterns.wPixels * COUNT(DISTINCT Offset_X) AS wTotalPixels, Patterns.hPixels * COUNT(DISTINCT Offset_Y) AS hTotalPixels, Patterns.Description FROM Tiles INNER JOIN Patterns ON Tiles.PatternID = Patterns.PatternID GROUP BY Tiles.PatternID; > > It works if I substitute a constant for the nTilesX and nTilesY so I think I > do > not know how to correctly reference these. > > Thank you. > > Jeff Archer > Nanotronics Imaging > jsarc...@nanotronicsimaging.com > <330>819.4615 Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] threads and last_insert_rowid()
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 suffer > > from threads? > > from borgan: > > Hi, i think this will probably be "atomic". > > What i mean is the transaction will aquire EXCLUSIVE lock (because of > > the insert command) and it will keep this lock until commit, which > > takes place after the last select and last semicolon. > > > > So i believe no other threads should be able to do anything with your > > db until the whole query finishes. > and from Kees Nuyt: > > The last_insert_rowid() function returns the ROWID of the last row > > insert from the database connection which invoked the function. > > So, yes, it suffers from threads if the threads use the same > > database connection. > > whom to trust? The docs, which make this fairly clear: http://sqlite.org/lang_corefunc.html#last_insert_rowid The last_insert_rowid() function returns the ROWID of the last row insert from the database connection which invoked the function. The last_insert_rowid() SQL function is a wrapper around the sqlite3_last_insert_rowid() C/C++ interface function. http://sqlite.org/c3ref/last_insert_rowid.html [...] This routine returns the rowid of the most recent successful INSERT into the database from the database connection in the first argument. If no successful INSERTs have ever occurred on that database connection, zero is returned. [...] So Kees Nuyt is correct... the value returned by last_insert_rowid() is "per database connection". It is not a global. It is not a per-database-file value. It is not a pre-thread value. If each thread is using its own, private database connection, then you're safe. If multiple threads are sharing a database connection and using the "serialized" thread mode, then you have a race condition as it is possible for the database connection to be handed off between statements. The value returned by last_insert_rowid() may be invalid by the time it is called. In either case, these two operations are never "atomic", in the fullest sense of that word. last_insert_rowid() is not bounded by transactions in the way statement processing is. > as for Kees Nuyt reply, did you toke int account that "select > last_insert_rowid() " > and insert query combined in single query and executed via single call > of sqlite3_exec()? Using sqlite3_exec() changes nothing. It just runs the normal prepare/step interfaces. It does not provide any kind of atomic processing above and beyond autocommit. It doesn't even wrap multiple statements in a transaction. It is just a syntax short-cut and provides no significant functionality beyond convenience. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Selective update of a column
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, at 3:29pm, Amit Chaudhuri wrote: > > > The approach I tried was to perform 2 sequential updates using first the > > fixed call source table then the mobile. I tried to restrict the update > to > > rows in target where the class of call (fixed or mobile) corresponded to > the > > content of the source. > > > > What seems to happen is that the second update blats the updates > performed > > by the first: I can have either fixed results or mobile results but not > > both. > > You /nearly/ got it right. Your commands were > >update target set cost = (select cost from source1 where > source1.Aend=target.Aend and source1.type=target.type and FM='Fixed'); > >update target set cost = (select cost from source2 where > source2.Aend=target.Aend and source2.type=target.type and FM='Mobile'); > > Your problem is that you have the clause about FM in the wrong part. The > FM column is in the table you're updating, not in the source. So the clause > about FM should be part of the UPDATE command, not part of the SELECT. Your > code should look more like > >UPDATE target SET cost = (SELECT cost FROM source1 WHERE > source1.Aend=target.Aend AND source1.type=target.type) WHERE FM='Fixed'; > >UPDATE target SET cost = (SELECT cost FROM source2 WHERE > source2.Aend=target.Aend AND source2.type=target.type) WHERE FM='Mobile'; > > I haven't tried the above code but I hope it might point you in the right > direction. > > To make things fast, don't forget to index your source* tables on > (Aend,type) or something like that. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Using foreign key reference on RowID
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 JobPlans(Name) VALUES ('234234'); # Following line generates the error: INSERT INTO Tasks(JobPlan_ID,UID) VALUES (1,'ZZZ'); ~~~ If I explicitly declare an alias for the RowID: CREATE TABLE IF NOT EXISTS JobPlans (RowID INTEGER PRIMARY KEY, Name UNIQUE); I don't get the error. Is it not possible to reference the SQLite internal/default column for the RowID in a foreign key definition? The online docs should be updated to reflect this. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to make this calculation in VIEW
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 NULL, Origin_X REAL NOT NULL DEFAULT 0.0, Origin_Y REAL NOT NULL DEFAULT 0.0, wMicrons REAL NOT NULL DEFAULT 0.0, hMicrons REAL NOT NULL DEFAULT 0.0, wPixels INTEGER NOT NULL DEFAULT 0.0, hPixels INTEGER NOT NULL DEFAULT 0.0 ); // Tiles table CREATE TABLE IF NOT EXISTS [Tiles] (\n" TileID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" PatternID INTEGER NOT NULL REFERENCES [Patterns] DEFERRABLE INITIALLY DEFERRED,\n" Offset_X REAL NOT NULL DEFAULT 0.0,\n" Offset_Y REAL NOT NULL DEFAULT 0.0\n" ); // vwPatterns CREATE VIEW IF NOT EXISTS [vwPatterns] AS SELECT Patterns.Name AS Pattern, Patterns.Origin_X, Patterns.Origin_Y, Patterns.wMicrons, Patterns.hMicrons, COUNT(DISTINCT Offset_X) AS nTilesX, COUNT(DISTINCT Offset_Y) AS nTilesY, Patterns.wPixels, Patterns.hPixels, Patterns.Description FROM Tiles INNER JOIN Patterns ON Tiles.PatternID = Patterns.PatternID GROUP BY Tiles.PatternID; I would like to add these calculated columns to my view but not sure how to make this work. Patterns.wMicrons * nTilesX AS wTotalMicrons, Patterns.hMicrons * nTilesY AS hTotalMicrons, Patterns.wPixels * nTilesX AS wTotalPixels, Patterns.hPixels * nTilesY AS hTotalPixels, i.e. // vwPatterns CREATE VIEW IF NOT EXISTS [vwPatterns] AS SELECT Patterns.Name AS Pattern, Patterns.Origin_X, Patterns.Origin_Y, Patterns.wMicrons, Patterns.hMicrons, COUNT(DISTINCT Offset_X) AS nTilesX, COUNT(DISTINCT Offset_Y) AS nTilesY, Patterns.wPixels, Patterns.hPixels, Patterns.wMicrons * nTilesX AS wTotalMicrons, Patterns.hMicrons * nTilesY AS hTotalMicrons, Patterns.wPixels * nTilesX AS wTotalPixels, Patterns.hPixels * nTilesY AS hTotalPixels, Patterns.Description FROM Tiles INNER JOIN Patterns ON Tiles.PatternID = Patterns.PatternID GROUP BY Tiles.PatternID; But it get error: "no such column: nTilesX" It works if I substitute a constant for the nTilesX and nTilesY so I think I do not know how to correctly reference these. Thank you. Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] append to a column?
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 and write out the entire blob with a >new value > > >without any special support, I can read existing blob, add some new >data to it, and update the column with the new blob value. >but because most incremental updates are small, reading the majority >of the old blob is a waste of time. > >ideally this is a sparse table ( each append creates a new "column", >there is no limit on how many columns each row could have, or any >schema for columns, for that matter). But I can't find an >off-the-shelf solution for sparse tables. (can't use Columnar DB, >since my access pattern is still first based on rows) A variable number of columns is not a good design: 1) It smells like a repeating group or array, which is not correct for a relational schema 2) Changing a schema on the fly is a bad idea, as it invalidates all sqlite3_prepared() statements. 3) All rows would have the number of columns needed by the row with the highest number of updates, most of them would be NULL Repeating groups and arrays can be prevented by adding a row for every append action. In order to do so, expand the primary key of the row with a fragment sequence number (or date/timestamp). CREATE TABLE Accumulatedblobs ( identity INTEGER, fragmentseq INTEGER, fragment BLOB, PRIMARY KEY (identity,fragmentseq) ); If the number of rows becomes a concern you could run a background optimization process to periodically collect all fragment rows with the same identity into one. Interesting to read: http://www.thethirdmanifesto.com/ -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] append to a column?
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, I can read existing blob, add some new data to it, and update the column with the new blob value. but because most incremental updates are small, reading the majority of the old blob is a waste of time. ideally this is a sparse table ( each append creates a new "column", there is no limit on how many columns each row could have, or any schema for columns, for that matter). But I can't find an off-the-shelf solution for sparse tables. (can't use Columnar DB, since my access pattern is still first based on rows) Thanks a lot Yang ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users