Re: [sqlite] Best way to implement Find commands
On Thu, 23 May 2013 18:38:57 -0600 "Keith Medcalf" wrote: > > And, can I depend on SQLite to generate results in the same order > > as the original prepare/step sequence and the temp table > > generation, ie. is the temp table's rowid going to be consistent > > with the original step order? > > If you use an ORDER BY clause, yes. If not, then the rows are > returned in random order. Pardon me while I fix that for you. s/random/nondeterministic/ but for the purposes of discussion much the same: without ORDER BY, the order cannot be relied on. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Best way to implement Find commands
The real problem is going to be the breakpoint and ordering. FindFirst is simply a re-execution of the query. FindLast is the same, with inverted ordering. FindNext is just stepping (or issuing the same query but adding a condition to start after the breakboint). FindPrevious is simply the same query again, in inverted order, for the subset before the breakpoint). For example if your query is something like: Select Lastname, Firstname from Names order by LastName, FirstName; Then assuming your row X is Lastname='Cricket' and Firstname='Jiminy' then: FindFirst is select lastname, firstname from names where (conditions) order by lastname, firstname limit 1; FindLast is select lastname, firstname from names where (conditions) order by lastname desc, firstname desc limit 1; FindNext is select lastname, firstname from names where (conditions) and lastname>='Cricket' and firstname>='Jiminy' order by lastname, firstname limit 1,1; FindPrev is select lastname, firstname from names where (conditions) and lastname<='Cricket' and firstname<='Jiminy' order by lastname desc, firstname desc limit 1,1; You can of course work with bounded subsets by getting the X and Y row keys and using BETWEEN on the unique ordered row keys to select the subset to which the (condition) should be applied. You will need approriate indexes and you will need to make sure that the order by (break) columns are constrained unique. SQL databases are not navigational record stores but are rather set algebra based. You can emulate a navigational recordstore using a relational database, but it will be somewhat inefficient no matter what you do ... Whatever you do, you must always specify an order by, otherwise you should assume that the rows will be returned in a random order which changes with each SELECT (this is especially true since you will need lots of judiciously chosen indexes to achieve any sort of performance whatsoever for any non-trivial table size -- there is nothing worse that someone designing and testing something with a database with 7 rows in it. Assume millions of rows. If it works for millions of rows acceptably then it will work just as well with 7 rows -- but the opposite is not true. --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Michael Falconer > Sent: Thursday, 23 May, 2013 20:47 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Best way to implement Find commands > > Leading on from what Keith suggests above re: 'and join' can we simplify > as: > > Findfirst: > > SELECT * FROM some_table WHERE original_expression AND new_expression > LIMIT > 1; > > What the LIMIT 1 will return depends on what index/primary key is in > effect, sort of hoping for rowid here :-) > > This appears equivalent to the temp_table from query + new_query approach, > but perhaps there is something you have omitted that makes this invalid??? > Also not not sure where you are getting your 'X' value for 'next' and > 'previous' processing. as there is no real find operation (which would > likely return multiple records). If this is being stored, perhaps in > external program code, then fine, there is a clear concept of current > record and therefore next and previous become trivial (less than, greater > than current, being X). > > FindPrevious: > > SELECT * FROM some_table WHERE id = ( SELECT max(rowid) FROM ( SELECT * > FROM some_table WHERE original_expression AND new_expression AND rowid < X > ) ); > > FindNext: > > SELECT * FROM some_table WHERE id = ( SELECT max(rowid) FROM ( SELECT * > FROM some_table WHERE original_expression AND new_expression AND rowid > X > ) ); > > FindLast: > > SELECT * FROM some_table WHERE id = ( SELECT max(rowid) FROM ( SELECT * > FROM some_table WHERE original_expression AND new_expression ) ); > > You can of course use the temp table approach, but sqlite I believe > creates > a temp table to facilitate the nested SELECT so we are indirectly using > the > temp approach..hope this helps. > > regards, > Michael.j.Falconer. > > > > > > On Fri, May 24, 2013 at 10:38 AM, Keith Medcalf > wrote: > > > > > > And, can I depend on SQLite to generate results in the same order as > the > > > original prepare/step sequence and the temp table generation, ie. is > the > > > temp table's rowid going to be consistent with the original step > order? > > > > If you use an ORDER BY clause, yes. If not, then the rows are returned > in > > random order. > > > > As for the rest of your questions, why do you not just join the search > > conditions with an AND? > > > > --- > > () ascii ribbon campaign against html e-mail > > /\ www.asciiribbon.org > > > > > > > > > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/m
Re: [sqlite] Best way to implement Find commands
OOoops typo on the FindNext, should be a min() instead of max(), i.e. SELECT * FROM some_table WHERE id = ( SELECT min(rowid) FROM ( SELECT * FROM some_table WHERE original_expression AND new_expression AND rowid > X ) ); ...typing has never been my strong point :-) On Fri, May 24, 2013 at 12:47 PM, Michael Falconer < michael.j.falco...@gmail.com> wrote: > Leading on from what Keith suggests above re: 'and join' can we simplify > as: > > Findfirst: > > SELECT * FROM some_table WHERE original_expression AND new_expression > LIMIT 1; > > What the LIMIT 1 will return depends on what index/primary key is in > effect, sort of hoping for rowid here :-) > > This appears equivalent to the temp_table from query + new_query approach, > but perhaps there is something you have omitted that makes this invalid??? > Also not not sure where you are getting your 'X' value for 'next' and > 'previous' processing. as there is no real find operation (which would > likely return multiple records). If this is being stored, perhaps in > external program code, then fine, there is a clear concept of current > record and therefore next and previous become trivial (less than, greater > than current, being X). > > FindPrevious: > > SELECT * FROM some_table WHERE id = ( SELECT max(rowid) FROM ( SELECT * > FROM some_table WHERE original_expression AND new_expression AND rowid < X > ) ); > > FindNext: > > SELECT * FROM some_table WHERE id = ( SELECT max(rowid) FROM ( SELECT * > FROM some_table WHERE original_expression AND new_expression AND rowid > X > ) ); > > FindLast: > > SELECT * FROM some_table WHERE id = ( SELECT max(rowid) FROM ( SELECT * > FROM some_table WHERE original_expression AND new_expression ) ); > > You can of course use the temp table approach, but sqlite I believe > creates a temp table to facilitate the nested SELECT so we are indirectly > using the temp approach..hope this helps. > > regards, > Michael.j.Falconer. > > > > > > On Fri, May 24, 2013 at 10:38 AM, Keith Medcalf wrote: > >> >> > And, can I depend on SQLite to generate results in the same order as the >> > original prepare/step sequence and the temp table generation, ie. is the >> > temp table's rowid going to be consistent with the original step order? >> >> If you use an ORDER BY clause, yes. If not, then the rows are returned >> in random order. >> >> As for the rest of your questions, why do you not just join the search >> conditions with an AND? >> >> --- >> () ascii ribbon campaign against html e-mail >> /\ www.asciiribbon.org >> >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > Regards, > Michael.j.Falconer. > -- Regards, Michael.j.Falconer. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Best way to implement Find commands
Leading on from what Keith suggests above re: 'and join' can we simplify as: Findfirst: SELECT * FROM some_table WHERE original_expression AND new_expression LIMIT 1; What the LIMIT 1 will return depends on what index/primary key is in effect, sort of hoping for rowid here :-) This appears equivalent to the temp_table from query + new_query approach, but perhaps there is something you have omitted that makes this invalid??? Also not not sure where you are getting your 'X' value for 'next' and 'previous' processing. as there is no real find operation (which would likely return multiple records). If this is being stored, perhaps in external program code, then fine, there is a clear concept of current record and therefore next and previous become trivial (less than, greater than current, being X). FindPrevious: SELECT * FROM some_table WHERE id = ( SELECT max(rowid) FROM ( SELECT * FROM some_table WHERE original_expression AND new_expression AND rowid < X ) ); FindNext: SELECT * FROM some_table WHERE id = ( SELECT max(rowid) FROM ( SELECT * FROM some_table WHERE original_expression AND new_expression AND rowid > X ) ); FindLast: SELECT * FROM some_table WHERE id = ( SELECT max(rowid) FROM ( SELECT * FROM some_table WHERE original_expression AND new_expression ) ); You can of course use the temp table approach, but sqlite I believe creates a temp table to facilitate the nested SELECT so we are indirectly using the temp approach..hope this helps. regards, Michael.j.Falconer. On Fri, May 24, 2013 at 10:38 AM, Keith Medcalf wrote: > > > And, can I depend on SQLite to generate results in the same order as the > > original prepare/step sequence and the temp table generation, ie. is the > > temp table's rowid going to be consistent with the original step order? > > If you use an ORDER BY clause, yes. If not, then the rows are returned in > random order. > > As for the rest of your questions, why do you not just join the search > conditions with an AND? > > --- > () ascii ribbon campaign against html e-mail > /\ www.asciiribbon.org > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Regards, Michael.j.Falconer. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Best way to implement Find commands
> And, can I depend on SQLite to generate results in the same order as the > original prepare/step sequence and the temp table generation, ie. is the > temp table's rowid going to be consistent with the original step order? If you use an ORDER BY clause, yes. If not, then the rows are returned in random order. As for the rest of your questions, why do you not just join the search conditions with an AND? --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SQLite.NET] Cannot read BLOB data
Kevin Keigwin wrote: > > I cannot read the data into a DataTable using the adapter, because the > exception "Invalid storage type: DBNull." is thrown. > There was a recently fixed issue with the SQLiteDataAdapter class. The fix can be seen here: https://system.data.sqlite.org/index.html/info/dcc277f29a It will be present in the next release (1.0.86.0), due out shortly. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite NULL or 0-Length
No. All SQL functions can safely take NULL as an argument. LENGTH(NULL) returns NULL, so LENGTH(NULL) = 0 is always false. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rob Richardson Sent: Thursday, May 23, 2013 3:34 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite NULL or 0-Length Simon, Is there a danger here if firstname is NULL and the LENGTH() function is called first? RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Thursday, May 23, 2013 2:54 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite NULL or 0-Length On 23 May 2013, at 7:06pm, Jill Rabinowitz wrote: > Does anyone know how I can check the column and set it to a value if > it has nothing in it? UPDATE myTable SET firstname='xxx' WHERE LENGTH(firstname) = 0 OR firstname IS NULL 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 This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite NULL or 0-Length
Simon, Is there a danger here if firstname is NULL and the LENGTH() function is called first? RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Thursday, May 23, 2013 2:54 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite NULL or 0-Length On 23 May 2013, at 7:06pm, Jill Rabinowitz wrote: > Does anyone know how I can check the column and set it to a value if > it has nothing in it? UPDATE myTable SET firstname='xxx' WHERE LENGTH(firstname) = 0 OR firstname IS NULL 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
Re: [sqlite] Are RTREE virtual tables supposed to treat NULL values as 0.0?
RTREE only understands floating-point numbers (or integers if you use "rtree_i32" instead of "rtree"). It does not do NULLs or strings or blobs. If you give it one of these other values, it will try to convert that value into a floating-point number as best it can. The best it can do with a NULL is convert it into 0.0. On Thu, May 23, 2013 at 3:09 PM, Peter Aronson wrote: > So, I was looking at some triggers to update an RTREE virtual table that > someone > else wrote. I noticed that the trigger didn't handle NULLs. I was > curious, and > decided to see what happened if you tried to insert NULL values into an > RTREE. > Actually, I rather expected it to throw an error. Instead, the values > (aside > from the id which is a separate issue) became 0.0 like so: > > CREATE VIRTUAL TABLE nulltest USING RTREE (pkid,v1,v2); > INSERT INTO nulltest DEFAULT VALUES; > SELECT * FROM nulltest; > pkid v1 v2 > -- -- -- > 1 0.00.0 > > This is not actually an ideal result, since 0.0 could either be a > legitimate > value, which means a search of the rtree table could produce a false > positive; > or it could be a completely unexpected value and cause who know what sort > of > problems. > > Mind you, while I use RTREEs in SQLite myself, this isn't a problem for > me, as I > always supply legitimate values. > > When I looked at the SQLite source, you can see that there is no check for > NULL > in the rtree code except for ids, so the calls to sqlite3_value_double in > rtreeValueDown and rtreeValueUp are just going to return 0.0 when NULLs are > supplied. It seems to me that logically, they should probably throw > constraint > violations instead, but at this point that would be an issue for backwards > compatibility. > > Peter > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Are RTREE virtual tables supposed to treat NULL values as 0.0?
So, I was looking at some triggers to update an RTREE virtual table that someone else wrote. I noticed that the trigger didn't handle NULLs. I was curious, and decided to see what happened if you tried to insert NULL values into an RTREE. Actually, I rather expected it to throw an error. Instead, the values (aside from the id which is a separate issue) became 0.0 like so: CREATE VIRTUAL TABLE nulltest USING RTREE (pkid,v1,v2); INSERT INTO nulltest DEFAULT VALUES; SELECT * FROM nulltest; pkid v1 v2 -- -- -- 1 0.0 0.0 This is not actually an ideal result, since 0.0 could either be a legitimate value, which means a search of the rtree table could produce a false positive; or it could be a completely unexpected value and cause who know what sort of problems. Mind you, while I use RTREEs in SQLite myself, this isn't a problem for me, as I always supply legitimate values. When I looked at the SQLite source, you can see that there is no check for NULL in the rtree code except for ids, so the calls to sqlite3_value_double in rtreeValueDown and rtreeValueUp are just going to return 0.0 when NULLs are supplied. It seems to me that logically, they should probably throw constraint violations instead, but at this point that would be an issue for backwards compatibility. Peter ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Best way to implement Find commands
I've got a prepared statement from a original query that may have been stepped x times to row X. I want to implement the functions: FindFirst - find the first row (between row 1 and the last available row inclusive) that satisifies a new query. FindLast - find the last row (between row 1 and the last available row inclusive) that satisifies a new query. FindNext - find the next row (between row X and the last available row inclusive) that satisfies a new query. FindPrevious - find the last row (between row 1 and row X-1 inclusive) that satisfies a new query. What I thought I would do is generate a temporary table based on the original select statement and then execute the new query combined with rowid constraints and LIMIT of 1 in order to generate the row for the Find statement. The new query is defined by SELECT * FROM original_select_sql WHERE new_expr. I would generate atemporary table with CREATE TEMP TABLE temp_xxx AS original_select_sql; So, for FindFirst the resultant new query would be SELECT * from temp_xxx WHERE (new_expr) LIMIT 1; For FindNext: SELECT * from temp_xxx WHERE (new_expr) AND rowid > X LIMIT 1; Is this the bset approach? And if so, can you suggest queries for FindLast and FindPrevious? And, can I depend on SQLite to generate results in the same order as the original prepare/step sequence and the temp table generation, ie. is the temp table's rowid going to be consistent with the original step order? Thanks for any insight? Vance ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite NULL or 0-Length
On 23 May 2013, at 7:06pm, Jill Rabinowitz wrote: > Does anyone know how I can check the column and set it to a value if it has > nothing in it? UPDATE myTable SET firstname='xxx' WHERE LENGTH(firstname) = 0 OR firstname IS NULL Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite NULL or 0-Length
On Thu, May 23, 2013 at 2:06 PM, Jill Rabinowitz wrote: > Hello, > > I am having trouble with a SQLite IFNULL and replace statements. I am > trying to put a value into a column that has no value / has a zero length. >I am wondering whether anyone can shed light on this. > > I'm trying to set column firstname to 'xxx' if the column has a NULL value > (or has length = 0). I am able to check the number of rows returned by > running a select statement with the following "where" clauses: > 1) where firstname IS NULL -> 0 rows returned > 2) where length(firstname) = 0 -> returns 100 rows > 3) where firstname=""--> returns 100 rows > it appears that your "firstname" columns have been loaded with empty strings rather than NULLs. You seem to already understand the difference there, so I won't explain > > The problem is that the IFNULL and REPLACE functions are not working in my > SELECT statement, so I am unable to set firstname = 'xxx' where no value > exists. > Maybe this: SELECT ifnull(nullif(firstname,''),'xxx') ...; Or this: SELECT CASE WHEN firstname IS NULL OR firstname='' THEN 'xxx' ELSE firstname END, ... > > 1) select IFNULL(firstname, 'xxx') <--- does > not set the value to 'xxx', which is consistent with (1) above > from tablename; > > 2) select replace(firstname, '','xxx') <--- does > not set the value to 'xxx', which contradicts (3) above, as the string is > empty > > Does anyone know how I can check the column and set it to a value if it has > nothing in it? > > Thank you in advance! > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite NULL or 0-Length
Hello, I am having trouble with a SQLite IFNULL and replace statements. I am trying to put a value into a column that has no value / has a zero length. I am wondering whether anyone can shed light on this. I'm trying to set column firstname to 'xxx' if the column has a NULL value (or has length = 0). I am able to check the number of rows returned by running a select statement with the following "where" clauses: 1) where firstname IS NULL -> 0 rows returned 2) where length(firstname) = 0 -> returns 100 rows 3) where firstname=""--> returns 100 rows The problem is that the IFNULL and REPLACE functions are not working in my SELECT statement, so I am unable to set firstname = 'xxx' where no value exists. 1) select IFNULL(firstname, 'xxx') <--- does not set the value to 'xxx', which is consistent with (1) above from tablename; 2) select replace(firstname, '','xxx') <--- does not set the value to 'xxx', which contradicts (3) above, as the string is empty Does anyone know how I can check the column and set it to a value if it has nothing in it? Thank you in advance! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [SQLite.NET] Cannot read BLOB data
I am using the 10.0.85 version of the Win32 SQLite.NET data adapter to read a spatialite database table. This table has a single geometry column in it, which stores data as a BLOB. I cannot read the data into a DataTable using the adapter, because the exception "Invalid storage type: DBNull." is thrown. The exception appears to be thrown by the DbDataAdapter base class as a result of the SQLiteDataAdapter assigning the BLOB column the type of System.DBNull. Stepping through the code, I can see in SQLiteConvert.cs the method SQLiteTypeToType() uses the column's type (Object) and its affinity (null) to determine the "equivalent" .NET Type as DBNull. I see that the lookup array used by SQLiteTypeToType() provides a conversion from affinity BLOB to Type byte[], which is what is desired here. However, the SQLite 3 documentation is clear that there is no such affinity - BLOBs are given affinity "none". At this point, I am unclear how to read this data using the SQLiteDataAdapter. Thanks in advance for any help. Kevin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Potential bug in crash-recovery code: unlink() and friends are not synchronous
Hi Marc, Thanks for your comments! I just got confused that some SQLite webpages ( http://www.sqlite.org/transactional.html, http://www.sqlite.org/features.html) mention that transactions are durable after a power loss (the D in ACID); nowhere has it been mentioned that "immediate durability after a commit" is true only when the journal_mode is explicitly set to WAL. The documentation, however, was thorough regarding stuff like bad disks and bad OSes, and how they affect the integrity/corruption of the database. My view had been fully biased: I was looking at some applications that use MySQL and such, acting as nodes in a network, and they require this "immediate durability" guarantee. I totally understand now that SQLite is aimed at an entirely different set of applications that don't require "immediate durability", but would rather that the database is fast. Thanks again, Thanu On Thu, May 23, 2013 at 8:00 AM, Marc L. Allen wrote: > Just to throw in my $0.02 as a user > > Given the SQL stream of... > > > COMMIT > > > Vs. > > > > > > Except in cases where, in the first example, I have time to inform someone > about the COMMIT before the power loss, there's no functional difference > between the two events. I would hate to think I would ever demand that > SQLite guarantee the commit occurs regardless of how quickly the power loss > happens after the commit statement. > > For a huge majority of the applications I've dealt with (and I say huge > instead of all only in case there's one I've forgotten about), the timing > difference doesn't matter. What matters is that when I come back up I have > an intact database. > > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto: > sqlite-users-boun...@sqlite.org] On Behalf Of thanumalayan mad > Sent: Wednesday, May 22, 2013 8:31 AM > To: Richard Hipp > Cc: General Discussion of SQLite Database > Subject: Re: [sqlite] Potential bug in crash-recovery code: unlink() and > friends are not synchronous > > I do not observe any loss in durability in WAL mode: it works totally fine. > > As for the documentation, http://www.sqlite.org/transactional.html and > http://www.sqlite.org/features.html claim that SQLite is durable during > power failures; and DELETE is the default journal_mode. Also, other pages, > http://www.sqlite.org/pragma.html#pragma_synchronous, > http://www.sqlite.org/atomiccommit.html, and > http://www.sqlite.org/faq.html, made me think that rollback journaling > ensures durability (given a honest disk and a "honest" OS), although those > pages do not "legally" say that; I'm not sure if others would understand it > this way though. The usual opinion in blogosphere (and forums) also seems > to be that SQLite is, by default, durable across power failures, though > they might be meaning only about a "5 second eventual durability". Finally, > (I hope I do not come across here as being authoritative), a quick fix > might be explicitly mentioning somewhere in the documentation that DELETE > and TRUNCATE modes do not ensure immediate durability after a power loss; > this would combat any wrongly-understood claims in the rest of the > documentation. > > Also, not to spam, but it would be great if you could answer these > questions for my research (you might send me a reply directly without going > through the mailing list): [a] Was it always understood that unlink() and > ftruncate() are not synchronous, and that SQLite transactions in DELETE > mode are not immediately-durable in Linux; or had you initially > misunderstood the semantics of those calls, or left-off the fsync() because > of a typo error? [b] While designing the crash-tests, were the semantics of > the calls in Unix understood? What if ftruncate() not being synchronous did > lead to a consistency-loss? Was it reasoned-out that the non-synchronous > ftruncate would not produce corruption? [c] How much of a loss in > durability (what other than 5 seconds) would be "good enough" in most > cases? ... Again, sorry for the spam; my research is trying to make > sense of the flushing-mess in the entire storage stack, and feedback would > be extremely useful. > > PS: @Richard Hipp and other developers - thanks for SQLite! Amazing piece > of software. > > -- > Thanumalayan Sankaranarayana Pillai > (Graduate student at the University of Wisconsin-Madison) > > > On Wed, May 22, 2013 at 5:49 AM, Richard Hipp wrote: > > > > > > > On Sat, May 18, 2013 at 4:41 AM, thanumalayan mad >wrote: > > > >> > >> Expected result: You always find that the transaction had been executed. > >> Observed result: You sometimes find that the transaction did not > execute. > >> > > > > The core team has discussed this. In order to avoid a substantial > > performance hit against transaction COMMIT, we have chosen to not do > > fsyncs on the directory when a file is unlinked, and thus to allow > > loss of durability following a power loss event. ACI without the D is > > still guaranteed. But
Re: [sqlite] Max of 63 columns for a covering index to work?
On Thu, May 23, 2013 at 3:02 PM, Marc L. Allen wrote: > I'm not familiar with that. It's a "view" where Oracle actually stores > the view data as a physical table? And updates these tables as the main > table updates? Pretty much. And the query optimizer is aware of the relationship of course, and queries on the main table can be optionally routed to the mat-view. (see http://docs.oracle.com/cd/E11882_01/server.112/e25554/qradv.htm) And if you're willing to have a slight delay for the updating of the mat-view (async mode), you don't slow down inserts on the main table, and background processes (or threads on windows) "mine" the undo/redo logs to update the mat-view. In sync mode, you don't write the trigger, that's done for you, you just define the view as usual. But I'm no expert on the subject, just sharing perspective from the non-lite point of view, that's all. See http://docs.oracle.com/cd/E16338_01/server.112/e10706/repmview.htm for details. Here it's "Enable Data Subsetting" I guess. Closing the off-topic aside now --DD ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Max of 63 columns for a covering index to work?
I'm not familiar with that. It's a "view" where Oracle actually stores the view data as a physical table? And updates these tables as the main table updates? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dominique Devienne Sent: Thursday, May 23, 2013 8:57 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Max of 63 columns for a covering index to work? On Wed, May 22, 2013 at 9:11 PM, Marc L. Allen wrote: > [...]. It makes me think you might be better off using triggers to > maintain separate tables with covered data instead of indexes. [...]. > This sounds like Oracle's materialized views to me, which come in synchronous (trigger-based) or asynchronous (log-mining-based) variants. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Potential bug in crash-recovery code: unlink() and friends are not synchronous
Just to throw in my $0.02 as a user Given the SQL stream of... COMMIT Vs. Except in cases where, in the first example, I have time to inform someone about the COMMIT before the power loss, there's no functional difference between the two events. I would hate to think I would ever demand that SQLite guarantee the commit occurs regardless of how quickly the power loss happens after the commit statement. For a huge majority of the applications I've dealt with (and I say huge instead of all only in case there's one I've forgotten about), the timing difference doesn't matter. What matters is that when I come back up I have an intact database. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of thanumalayan mad Sent: Wednesday, May 22, 2013 8:31 AM To: Richard Hipp Cc: General Discussion of SQLite Database Subject: Re: [sqlite] Potential bug in crash-recovery code: unlink() and friends are not synchronous I do not observe any loss in durability in WAL mode: it works totally fine. As for the documentation, http://www.sqlite.org/transactional.html and http://www.sqlite.org/features.html claim that SQLite is durable during power failures; and DELETE is the default journal_mode. Also, other pages, http://www.sqlite.org/pragma.html#pragma_synchronous, http://www.sqlite.org/atomiccommit.html, and http://www.sqlite.org/faq.html, made me think that rollback journaling ensures durability (given a honest disk and a "honest" OS), although those pages do not "legally" say that; I'm not sure if others would understand it this way though. The usual opinion in blogosphere (and forums) also seems to be that SQLite is, by default, durable across power failures, though they might be meaning only about a "5 second eventual durability". Finally, (I hope I do not come across here as being authoritative), a quick fix might be explicitly mentioning somewhere in the documentation that DELETE and TRUNCATE modes do not ensure immediate durability after a power loss; this would combat any wrongly-understood claims in the rest of the documentation. Also, not to spam, but it would be great if you could answer these questions for my research (you might send me a reply directly without going through the mailing list): [a] Was it always understood that unlink() and ftruncate() are not synchronous, and that SQLite transactions in DELETE mode are not immediately-durable in Linux; or had you initially misunderstood the semantics of those calls, or left-off the fsync() because of a typo error? [b] While designing the crash-tests, were the semantics of the calls in Unix understood? What if ftruncate() not being synchronous did lead to a consistency-loss? Was it reasoned-out that the non-synchronous ftruncate would not produce corruption? [c] How much of a loss in durability (what other than 5 seconds) would be "good enough" in most cases? ... Again, sorry for the spam; my research is trying to make sense of the flushing-mess in the entire storage stack, and feedback would be extremely useful. PS: @Richard Hipp and other developers - thanks for SQLite! Amazing piece of software. -- Thanumalayan Sankaranarayana Pillai (Graduate student at the University of Wisconsin-Madison) On Wed, May 22, 2013 at 5:49 AM, Richard Hipp wrote: > > > On Sat, May 18, 2013 at 4:41 AM, thanumalayan mad wrote: > >> >> Expected result: You always find that the transaction had been executed. >> Observed result: You sometimes find that the transaction did not execute. >> > > The core team has discussed this. In order to avoid a substantial > performance hit against transaction COMMIT, we have chosen to not do > fsyncs on the directory when a file is unlinked, and thus to allow > loss of durability following a power loss event. ACI without the D is > still guaranteed. But not the D. The overwhelming majority of > applications care not one wit about durability following power loss. > For most applications, it is sufficient that the file is uncorrupted. > If recovery gives you a snapshot of the file as it existed 5 seconds > prior to the power loss, that's fine. > > WAL-mode transactions should be durable across power-loss events. So > if durability is vitally important to you, you can always set PRAGMA > journal_mode=WAL. Are you observing loss of durability following > power loss in WAL mode? > > Is there any place in the documentation that we have overlooked where > SQLite claims to be durable across a power loss in rollback mode? > > > > -- > D. Richard Hipp > d...@sqlite.org > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized
Re: [sqlite] Max of 63 columns for a covering index to work?
On Wed, May 22, 2013 at 9:11 PM, Marc L. Allen wrote: > [...]. It makes me think you might be better off using triggers to > maintain separate tables with covered data instead of indexes. [...]. > This sounds like Oracle's materialized views to me, which come in synchronous (trigger-based) or asynchronous (log-mining-based) variants. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Potential bug in crash-recovery code: unlink() and friends are not synchronous
I do not observe any loss in durability in WAL mode: it works totally fine. As for the documentation, http://www.sqlite.org/transactional.html and http://www.sqlite.org/features.html claim that SQLite is durable during power failures; and DELETE is the default journal_mode. Also, other pages, http://www.sqlite.org/pragma.html#pragma_synchronous, http://www.sqlite.org/atomiccommit.html, and http://www.sqlite.org/faq.html, made me think that rollback journaling ensures durability (given a honest disk and a "honest" OS), although those pages do not "legally" say that; I'm not sure if others would understand it this way though. The usual opinion in blogosphere (and forums) also seems to be that SQLite is, by default, durable across power failures, though they might be meaning only about a "5 second eventual durability". Finally, (I hope I do not come across here as being authoritative), a quick fix might be explicitly mentioning somewhere in the documentation that DELETE and TRUNCATE modes do not ensure immediate durability after a power loss; this would combat any wrongly-understood claims in the rest of the documentation. Also, not to spam, but it would be great if you could answer these questions for my research (you might send me a reply directly without going through the mailing list): [a] Was it always understood that unlink() and ftruncate() are not synchronous, and that SQLite transactions in DELETE mode are not immediately-durable in Linux; or had you initially misunderstood the semantics of those calls, or left-off the fsync() because of a typo error? [b] While designing the crash-tests, were the semantics of the calls in Unix understood? What if ftruncate() not being synchronous did lead to a consistency-loss? Was it reasoned-out that the non-synchronous ftruncate would not produce corruption? [c] How much of a loss in durability (what other than 5 seconds) would be "good enough" in most cases? ... Again, sorry for the spam; my research is trying to make sense of the flushing-mess in the entire storage stack, and feedback would be extremely useful. PS: @Richard Hipp and other developers - thanks for SQLite! Amazing piece of software. -- Thanumalayan Sankaranarayana Pillai (Graduate student at the University of Wisconsin-Madison) On Wed, May 22, 2013 at 5:49 AM, Richard Hipp wrote: > > > On Sat, May 18, 2013 at 4:41 AM, thanumalayan mad wrote: > >> >> Expected result: You always find that the transaction had been executed. >> Observed result: You sometimes find that the transaction did not execute. >> > > The core team has discussed this. In order to avoid a substantial > performance hit against transaction COMMIT, we have chosen to not do fsyncs > on the directory when a file is unlinked, and thus to allow loss of > durability following a power loss event. ACI without the D is still > guaranteed. But not the D. The overwhelming majority of applications care > not one wit about durability following power loss. For most applications, > it is sufficient that the file is uncorrupted. If recovery gives you a > snapshot of the file as it existed 5 seconds prior to the power loss, > that's fine. > > WAL-mode transactions should be durable across power-loss events. So if > durability is vitally important to you, you can always set PRAGMA > journal_mode=WAL. Are you observing loss of durability following power > loss in WAL mode? > > Is there any place in the documentation that we have overlooked where > SQLite claims to be durable across a power loss in rollback mode? > > > > -- > D. Richard Hipp > d...@sqlite.org > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] compiling sqlite4 on ubuntu 12.04 64 bit
Hi, On Thu, May 23, 2013 at 3:23 PM, Stephan Beal wrote: > gcc -fPIC -o libsqlite4.so -shared $(ar t libsqlite4.a) Works :) Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] compiling sqlite4 on ubuntu 12.04 64 bit
On Thu, May 23, 2013 at 11:45 AM, Prashant Shah wrote: > Works ! > :). That fix is in the trunk now. > How do I build the libsqlite4.so shared object file ? There is no > .libs folder in sqlite4 > There currently aren't build rules for the .so, but here's a quick-hack way to do it: gcc -fPIC -o libsqlite4.so -shared $(ar t libsqlite4.a) you might not need the -fPIC part - on my system it makes no difference. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] compiling sqlite4 on ubuntu 12.04 64 bit
Hi, On Thu, May 23, 2013 at 3:02 PM, Stephan Beal wrote: > Please try: > > - Edit Makefile.linux-gcc and make this change: > -TLIBS = > +TLIBS ?= > > - Run: make TLIBS=-lm Works ! How do I build the libsqlite4.so shared object file ? There is no .libs folder in sqlite4 Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] compiling sqlite4 on ubuntu 12.04 64 bit
On Thu, May 23, 2013 at 11:23 AM, Stephan Beal wrote: > The problem is that fts5func.c uses log() from math.h, which requires > linking against -lm. A quick workaround is to copy/paste the output from > where the build fails and add -lm to it: > Please try: - Edit Makefile.linux-gcc and make this change: -TLIBS = +TLIBS ?= - Run: make TLIBS=-lm That _assumes_ you have symlinked Makefile.linux-gcc to "GNUmakefile". If not, add "-f Makefile.linux-gcc" to the make arguments. That works for me. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] compiling sqlite4 on ubuntu 12.04 64 bit
On Thu, May 23, 2013 at 7:33 AM, Prashant Shah wrote: > libsqlite4.a(fts5func.o): In function `fts5Rank': > /home/user/db/build/sqlite4/src/fts5func.c:159: undefined reference to > `log' > collect2: ld returned 1 exit status > make: *** [sqlite4] Error 1 > The problem is that fts5func.c uses log() from math.h, which requires linking against -lm. A quick workaround is to copy/paste the output from where the build fails and add -lm to it: stephan@tiny:~/cvs/fossil/sqlite4$ gcc -g -fPIC -UNDEBUG -DDEBUG -Wall -std=c89 -I. -I./src -I. -I./ext/rtree -I./ext/icu -I./ext/fts3 -I./ext/async -o sqlite4 ./src/shell.c libsqlite4.a -lm ./src/shell.c: In function 'do_meta_command': ./src/shell.c:1888:7: warning: implicit declaration of function 'pclose' [-Wimplicit-function-declaration] ./src/shell.c:1894:7: warning: implicit declaration of function 'popen' [-Wimplicit-function-declaration] ./src/shell.c:1894:14: warning: assignment makes pointer from integer without a cast [enabled by default] stephan@tiny:~/cvs/fossil/sqlite4$ ldd sqlite4 linux-gate.so.1 => (0xb77bd000) libm.so.6 => /lib/i386-linux-gnu/libm.so.6 (0xb7775000) libc.so.6 => /lib/i386-linux-gnu/libc.so.6 (0xb75cb000) /lib/ld-linux.so.2 (0xb77be000) i'll try to figure out where that fix belongs. fts5func.c says, though: #include /* temporary: For log() */ so i suspect that -lm is not intended to be needed long-term. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
