Re: [sqlite] range enclosing a number
On Mon, Jul 13, 2009 at 10:33:00PM -0400, Pavel Ivanov scratched on the wall: > Jay, you're pretty much mistaken: > > > I'm pretty sure you don't want to do it this way. What this does is > > gather every row that meets the WHERE condition and then runs a max() > > or min() aggregation function across all of those rows. That means > > that even if the column "number" has an index on it, between these > > two statements you're going to end up scanning the whole table. > > All database engines optimize queries which ask for min/max on indexed > column with condition including only <, > or = on this very column. > And SQLite is among these too: Not "all." This type of optimization is actually a fairly new addition to SQLite (considering the product lifetime) and first appeared in 3.5.5, which was released in early 2008. And I'd still go with ORDER/LIMIT. Here's why: For my tests I just used the default build under the current version of Mac OS X, which is a bit old (3.4). Under that build, the ORDER/LIMIT is clearly faster, as this is before the optimization existed: (using the same test set you did) Full scan, 3.4: - real 0m5.99s user 0m4.73s sys 0m0.84s Using ORDER/LIMIT, 3.4: - real 0m0.00s user 0m0.01s sys 0m0.00s Using min/max, 3.4: - real 0m5.97s user 0m2.94s sys 0m0.38s In this case, it is clear that min/max are NOT integrated into the optimizer, and requires half a table scan, just as I stated. I also have a build of the current 3.6.16 around, and in that case, the numbers are better: Using ORDER/LIMIT, 3.6.16 - real 0m0.12s user 0m0.01s sys 0m0.03s Using min/max, 3.6.16 - real 0m0.04s user 0m0.01s sys 0m0.03s This clearly shows that the optimization does exist, and that for this very basic case my assumptions were incorrect. With the current 3.6.16 build, using min/max seems a tad faster-- but only in "real" time. In terms of user/sys times, the results shown here (and you're own numbers, which were 0.043/0.001/0.005 and 0.005/0.001/0.001) were pretty typical (i.e. very very close). That might just be an I/O fluke. We're getting small enough that to really say anything definite requires better profiling. So there does appear to be a difference, but it is pretty small and unclear where it is coming from. However, I'd point out that using ORDER/LIMIT under 3.4 is the fastest of all. This isn't just a quirk of one run, either. I ran these several times and the 3.4 ORDER/LIMIT was always fastest. We're still playing with number to small to really trust, but it seems that if the 3.6.16 ORDER/LIMIT was as fast as the one in 3.4, it would likely be the best choice of all. So you've sold me that the current version of SQLite clearly does have the min/max optimization and doesn't require a table scan. It also appears to be slightly faster, but not by a big enough gap to clearly consider it a better choice on that alone. Personally, I'd still go with ORDER/LIMIT. With the current version of SQLite the runtimes of both approaches are extremely similar, but the min/max approach depends on the query optimizer being able to take the min/max notation and basically turn it into an internal ORDER BY (look at the code). There are a lot of limits on when the optimizer can do this. If the real-world query is a bit more complex than this example, or if (down the road) the query conditions get changed, or if the query gets more complex in just about anyway, the optimization is going to break with the min/max approach and you'll be stuck with a table scan-- something that I think we can all agree is MUCH slower. If that comes up due to a change in the SQL six months down the road, you're going to be spending a lot of time wondering why things just got so slow. The ORDER/LIMIT approach is much more resilient to changes, however, and should more or less behave the same no matter what you do to the rest of the query. The ORDER/LIMIT approach is also the clear winner if there is any possible chance at all you'll be running on older code. "Older," in this case, is code that was released less than two years ago. In many production environments, that's not all that long. There is also this... > But of course your other point is true - if you want some other data > from table along with min/max value, you need to make additional > select in case of using min/max. ... which also negates any possible advantage of using the min/max method. The ORDER/LIMIT approach lets you pull out all the data you might need or want in a single query. For the simple case on newer code, both approaches give similar returns. But the min/max approach will default to a very expensive behavior if it is changed in just about any way, while the ORDER/LIMI
Re: [sqlite] range enclosing a number
On 14/07/2009 11:44 AM, Jay A. Kreibich wrote: > On Mon, Jul 13, 2009 at 07:40:48PM -0400, Wes Freeman scratched on the wall: >> Yeah, sorry about that. In two statements: >> >> select max(number) from table where number < ? >> select min(number) from table where number > ? > > I'm pretty sure you don't want to do it this way. What this does is > gather every row that meets the WHERE condition and then runs a max() > or min() aggregation function across all of those rows. That means > that even if the column "number" has an index on it, between these > two statements you're going to end up scanning the whole table. Let's assume right from the start that there's going to be an index on the subject column. too_slow * 2 == too_slow in boss arithmetic :-) Are you sure? I'm no expert on decoding the EXPLAIN output but the two look essentially the same to me: set up a cursor on the index, do a SeekLt(the_input_parameter) then test the limit in the first case, do exactly ONE AggStep operation in the other case > > You also have the problem that you can't return the rest of the row. (1) RowS plural. The limit 1 is arbitrary; there may be more than one row with such a value of number. (2) I would have thought it possible to return the rest of the rows using something like this: select * from table t1 where t1.number = (select max(t2.number) from table t2 where t2.number <= ?); with optional LIMIT if desired. My rules of thumb: (1) a sub-select like that can be used just about everywhere (2) whenever I see "limit 1" I get nervous and want to make absolutely sure that the query isn't going to generate a zillion rows and throw all but one away, or generate 5 and throw 4 away when somebody has presumed incorrectly that there would be only one row not 5. BTW, has the OP thought about the end conditions (no such lower value, no such higher value)? Cheers, John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] range enclosing a number
Jay, you're pretty much mistaken: > I'm pretty sure you don't want to do it this way. What this does is > gather every row that meets the WHERE condition and then runs a max() > or min() aggregation function across all of those rows. That means > that even if the column "number" has an index on it, between these > two statements you're going to end up scanning the whole table. All database engines optimize queries which ask for min/max on indexed column with condition including only <, > or = on this very column. And SQLite is among these too: > rm test.db > ( echo "create table t (i integer); begin;"; for ((i = 0;i<1000;++i)); do > echo "insert into t values ($i);"; done; echo "end; create index t_i on t > (i);" ) | sqlite3 test.db > # First force the full table scan > time sqlite3 test.db "select count(*) from t where i * i < 500;" 223607 real0m1.610s user0m1.469s sys 0m0.125s > # now using index > time sqlite3 test.db "select * from t where i < 500 order by i desc limit > 1;" 499 real0m0.043s user0m0.001s sys 0m0.005s > time sqlite3 test.db "select max(i) from t where i < 500;" 499 real0m0.005s user0m0.001s sys 0m0.001s As you see using max() works better than "order by ... limit 1". But of course your other point is true - if you want some other data from table along with min/max value, you need to make additional select in case of using min/max. Pavel On Mon, Jul 13, 2009 at 9:44 PM, Jay A. Kreibich wrote: > On Mon, Jul 13, 2009 at 07:40:48PM -0400, Wes Freeman scratched on the wall: >> Yeah, sorry about that. In two statements: >> >> select max(number) from table where number < ? >> select min(number) from table where number > ? > > I'm pretty sure you don't want to do it this way. What this does is > gather every row that meets the WHERE condition and then runs a max() > or min() aggregation function across all of those rows. That means > that even if the column "number" has an index on it, between these > two statements you're going to end up scanning the whole table. > > You also have the problem that you can't return the rest of the row. > The min() and max() functions will extract the right answer, but > something like this will not: > > SELECT min(number), other_column, FROM table WHERE number > ? > > In that case, it is VERY likely that the value of "other_column" will > not come from the same row as "min(number)". > > For example: > > sqlite> create table t ( i integer, s char ); > sqlite> insert into t values (1, 'a'); > sqlite> insert into t values (2, 'b'); > sqlite> insert into t values (3, 'c'); > sqlite> select min(i), s from t; > 1|c > sqlite> > > This is because the min() and max() aggregations imply a GROUP BY. > Since none is given, the whole result is grouped. That works fine > for min() and max() and gives you the right answer, but any other > column you specify is simply going to return the value for the last > row processed. That's why the above example returns 'c' for the > second column. > > >> >>> select * from table where number <= ? order by number desc limit 1; >> >>> select * from table where number>= ? order by number asc limit 1; > > This is the best solution, especially if the "number" column has an > index on it. In that case the correct row can be extracted directly > from the next and results are nearly instant, no matter how large the > table is. > > As others have pointed out, you do, of course, want to use < and >, > and not <= and >=. > > -j > > -- > Jay A. Kreibich < J A Y @ K R E I B I.C H > > > "Our opponent is an alien starship packed with atomic bombs. We have > a protractor." "I'll go home and see if I can scrounge up a ruler > and a piece of string." --from Anathem by Neal Stephenson > ___ > 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] range enclosing a number
On Mon, Jul 13, 2009 at 07:40:48PM -0400, Wes Freeman scratched on the wall: > Yeah, sorry about that. In two statements: > > select max(number) from table where number < ? > select min(number) from table where number > ? I'm pretty sure you don't want to do it this way. What this does is gather every row that meets the WHERE condition and then runs a max() or min() aggregation function across all of those rows. That means that even if the column "number" has an index on it, between these two statements you're going to end up scanning the whole table. You also have the problem that you can't return the rest of the row. The min() and max() functions will extract the right answer, but something like this will not: SELECT min(number), other_column, FROM table WHERE number > ? In that case, it is VERY likely that the value of "other_column" will not come from the same row as "min(number)". For example: sqlite> create table t ( i integer, s char ); sqlite> insert into t values (1, 'a'); sqlite> insert into t values (2, 'b'); sqlite> insert into t values (3, 'c'); sqlite> select min(i), s from t; 1|c sqlite> This is because the min() and max() aggregations imply a GROUP BY. Since none is given, the whole result is grouped. That works fine for min() and max() and gives you the right answer, but any other column you specify is simply going to return the value for the last row processed. That's why the above example returns 'c' for the second column. > >>> select * from table where number <= ? order by number desc limit 1; > >>> select * from table where number>= ? order by number asc limit 1; This is the best solution, especially if the "number" column has an index on it. In that case the correct row can be extracted directly from the next and results are nearly instant, no matter how large the table is. As others have pointed out, you do, of course, want to use < and >, and not <= and >=. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] range enclosing a number
Thank you, seems like a good solution. Best regards,Bogdan > From: freeman@gmail.com > Date: Mon, 13 Jul 2009 19:40:48 -0400 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] range enclosing a number > > Yeah, sorry about that. In two statements: > > select max(number) from table where number < ? > select min(number) from table where number> ? > > if you want to merge them into a single statement, you can do: > select (select max(number) from table where number < ?) > highest_smaller, (select min(number) from table where number> ?) > lowest_greater; > > On Mon, Jul 13, 2009 at 7:32 PM, Bogdan Nicula wrote: >> >> >> Thank you for your answer. >> Looking back to my original email, I believe I didn't explain well enough: I >> want to find the highest smaller and lowest greater numbers enclosing the >> number. >> For example, given the sequence: 1, 3, 5, 7, 9, etc., if I query for 6, I >> would like to get 5 and 7. >> >> Thanks,Bogdan >> >>> >>> Select max(number), min(number) from table; >>> >>> Wes >>> >>> On Mon, Jul 13, 2009 at 7:16 PM, Bogdan Nicula wrote: Hi, Sorry for my lack of SQL knowledge which triggered this help request: Given a column containing numbers, which is the most efficient manner to find out the highest smaller and lowest greater number? That is, is there a better way than: select * from table where number <= ? order by number desc limit 1; select * from table where number>= ? order by number asc limit 1; What if the table contains millions of rows? Thank you for any help,Bogdan _ >>> >> >> _ >> Share your memories online with anyone you want. >> http://www.microsoft.com/middleeast/windows/windowslive/products/photos-share.aspx?tab=1 >> ___ >> 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 _ With Windows Live, you can organize, edit, and share your photos. http://www.microsoft.com/middleeast/windows/windowslive/products/photo-gallery-edit.aspx ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] range enclosing a number
On 14 Jul 2009, at 12:16am, Bogdan Nicula wrote: > Given a column containing numbers, which is the most efficient > manner to find out the highest smaller and lowest greater number? > That is, is there a better way than: > > select * from table where number <= ? order by number desc limit 1; > select * from table where number>= ? order by number asc limit 1; Nope, that's a pretty good method. Except of course you need '>' not '>=' and '<' not '<='. Make sure, of course, that there's an index that starts with the 'number' field. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] string is converted into a number
On 14 Jul 2009, at 12:49am, John Machin wrote: > On 14/07/2009 3:04 AM, Simon Slavin wrote: >> >> The reference you were pointed to explains what happens: >> >> http://www.sqlite.org/datatype3.html#affinity >> >> So you want 'TEXT' ... 'char' doesn't mean anything to SQLite. > > @Simon: I'm not sure what you mean by that; see below: > > From the quoted URL: > """ > If the datatype of the column contains any of the strings "CHAR", > "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the > type VARCHAR contains the string "CHAR" and is thus assigned TEXT > affinity. > """ Wow, I completely missed that. Sorry: you're right and I was wrong. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] string is converted into a number
On 14/07/2009 3:04 AM, Simon Slavin wrote: > On 13 Jul 2009, at 4:35pm, Wilfried Mestdagh wrote: > >> But the circumstances are not really described (possible I cannot read >> between the lines as my English is not perfect). So as far as I >> understand >> the page if I want to store / retrieve a string (which can be a >> numeric >> string) I have to create my field as "char", "text" or as "none". Is >> this >> correct? @Wilfried: in CREATE TABLE, you should use a data type that contains "char", "clob", or "text" (uppercase or lowercase doesn't matter) -- that way the column has TEXT affinity which biases SQLite towards storing data as TEXT instead of as numbers. See example below. > > The reference you were pointed to explains what happens: > > http://www.sqlite.org/datatype3.html#affinity > > So you want 'TEXT' ... 'char' doesn't mean anything to SQLite. @Simon: I'm not sure what you mean by that; see below: From the quoted URL: """ If the datatype of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity. """ Perhaps it's case sensitive? A weird definition of "contains"? Doesn't seem so: SQLite version 3.6.14 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table x (a text, b varchar, c char, d string); sqlite> insert into x values('1', '2', '3', '4'); sqlite> select quote(a), quote(b), quote(c), quote(d) from x; '1'|'2'|'3'|4 sqlite> select typeof(a), typeof(b), typeof(c), typeof(d) from x; text|text|text|integer sqlite> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] range enclosing a number
Yeah, sorry about that. In two statements: select max(number) from table where number < ? select min(number) from table where number > ? if you want to merge them into a single statement, you can do: select (select max(number) from table where number < ?) highest_smaller, (select min(number) from table where number > ?) lowest_greater; On Mon, Jul 13, 2009 at 7:32 PM, Bogdan Nicula wrote: > > > Thank you for your answer. > Looking back to my original email, I believe I didn't explain well enough: I > want to find the highest smaller and lowest greater numbers enclosing the > number. > For example, given the sequence: 1, 3, 5, 7, 9, etc., if I query for 6, I > would like to get 5 and 7. > > Thanks,Bogdan > >> >> Select max(number), min(number) from table; >> >> Wes >> >> On Mon, Jul 13, 2009 at 7:16 PM, Bogdan Nicula wrote: >>> >>> >>> Hi, >>> >>> Sorry for my lack of SQL knowledge which triggered this help request: >>> Given a column containing numbers, which is the most efficient manner to >>> find out the highest smaller and lowest greater number? >>> That is, is there a better way than: >>> >>> select * from table where number <= ? order by number desc limit 1; >>> select * from table where number>= ? order by number asc limit 1; >>> >>> >>> What if the table contains millions of rows? >>> >>> Thank you for any help,Bogdan >>> _ >> > > _ > Share your memories online with anyone you want. > http://www.microsoft.com/middleeast/windows/windowslive/products/photos-share.aspx?tab=1 > ___ > 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] range enclosing a number
Thank you for your answer. Looking back to my original email, I believe I didn't explain well enough: I want to find the highest smaller and lowest greater numbers enclosing the number. For example, given the sequence: 1, 3, 5, 7, 9, etc., if I query for 6, I would like to get 5 and 7. Thanks,Bogdan > > Select max(number), min(number) from table; > > Wes > > On Mon, Jul 13, 2009 at 7:16 PM, Bogdan Nicula wrote: >> >> >> Hi, >> >> Sorry for my lack of SQL knowledge which triggered this help request: >> Given a column containing numbers, which is the most efficient manner to >> find out the highest smaller and lowest greater number? >> That is, is there a better way than: >> >> select * from table where number <= ? order by number desc limit 1; >> select * from table where number>= ? order by number asc limit 1; >> >> >> What if the table contains millions of rows? >> >> Thank you for any help,Bogdan >> _ > _ Share your memories online with anyone you want. http://www.microsoft.com/middleeast/windows/windowslive/products/photos-share.aspx?tab=1 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] range enclosing a number
Sorry, I misread the question... Still, I think min/max are better than order by limit 1. Wes On Mon, Jul 13, 2009 at 7:24 PM, Wes Freeman wrote: > Select max(number), min(number) from table; > > Wes > > On Mon, Jul 13, 2009 at 7:16 PM, Bogdan Nicula wrote: >> >> >> Hi, >> >> Sorry for my lack of SQL knowledge which triggered this help request: >> Given a column containing numbers, which is the most efficient manner to >> find out the highest smaller and lowest greater number? >> That is, is there a better way than: >> >> select * from table where number <= ? order by number desc limit 1; >> select * from table where number>= ? order by number asc limit 1; >> >> >> What if the table contains millions of rows? >> >> Thank you for any help,Bogdan >> _ >> Show them the way! Add maps and directions to your party invites. >> http://www.microsoft.com/windows/windowslive/products/events.aspx >> ___ >> 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] range enclosing a number
Select max(number), min(number) from table; Wes On Mon, Jul 13, 2009 at 7:16 PM, Bogdan Nicula wrote: > > > Hi, > > Sorry for my lack of SQL knowledge which triggered this help request: > Given a column containing numbers, which is the most efficient manner to find > out the highest smaller and lowest greater number? > That is, is there a better way than: > > select * from table where number <= ? order by number desc limit 1; > select * from table where number>= ? order by number asc limit 1; > > > What if the table contains millions of rows? > > Thank you for any help,Bogdan > _ > Show them the way! Add maps and directions to your party invites. > http://www.microsoft.com/windows/windowslive/products/events.aspx > ___ > 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] range enclosing a number
Hi, Sorry for my lack of SQL knowledge which triggered this help request: Given a column containing numbers, which is the most efficient manner to find out the highest smaller and lowest greater number? That is, is there a better way than: select * from table where number <= ? order by number desc limit 1; select * from table where number>= ? order by number asc limit 1; What if the table contains millions of rows? Thank you for any help,Bogdan _ Show them the way! Add maps and directions to your party invites. http://www.microsoft.com/windows/windowslive/products/events.aspx ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] trigger in self-attached db
Hi, if I attach a database to itself the trigger in the second db seem not to work as expected (as I wrongly expected? .-): Does anyone no why and how I could work around this? sqlite3 adb .headers on create table a ( pk integer primary key ); create table b ( pk integer primary key, fk integer constraint fk_c references a (pk) on delete cascade on update cascade); .genfkey --exec insert into a values (1); insert into b values (1, 1); --test trigger update a set pk=2; -- ok select * from b; .exit sqlite3 adb .headers on attach database adb as bdb; --test trigger update bdb.a set pk=3; -- fails update a set pk=3; -- ok select * from bdb.b; select * from b; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Raise is not working
On Sun, 12 Jul 2009 18:03:14 -0700, "Jim Showalter" wrote: >Schema: > >create table words (_id integer primary key autoincrement, wordtext >text not null unique); > >create table definitions (_id integer primary key autoincrement, >owningWordId integer not null unique, deftext text not null); > >create trigger fki_definitions_words_id before insert on definitions >for each row >begin >select raise (rollback, 'insert on table definitions violates >foreign-key constraint fki_definitions_words_id') >where (select _id from words where _id = NEW.owningWordId ) is >null; > >end; > >Call db.insert, passing it a definition that has the owningWordId set >to -1, and the insert returns a -1 instead of throwing. > >Because it doesn't throw, I don't have the error message "insert on >table definitions violates foreign-key constraint >fki_definitions_words_id" to work from. Information is simply lost. > >Why isn't it raising an exception? It does raise. SQLite behaves as expected. Must be the wrapper. sqlite_version():3.6.14.2 create table words ( _id integer primary key autoincrement, wordtext text not null unique ); create table definitions ( _id integer primary key autoincrement, owningWordId integer not null unique, deftext text not null ); create trigger fki_definitions_words_id before insert on definitions for each row begin select raise (rollback, 'insert on table definitions violates foreign-key constraint fki_definitions_words_id') where ( select _id from words where _id = NEW.owningWordId ) is null; end; .bail off insert into words (wordtext) VALUES ('wordone'); insert into definitions (owningWordId,deftext) VALUES ((select last_insert_rowid()),'defone'); insert into definitions (owningWordId,deftext) VALUES (-1,'deftwo'); SQL error near line 7: insert on table definitions violates foreign-key constraint fki_definitions_words_id -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Updating a database by email
Bruce Robertson wrote: >> We use a system of updating an Sqlite database buit use port 80 to send >> data in XML format. >> >> Is there a reason you have access to the email port and not the HTTP >> port 80? It is very complex to identify a missing email but using port >> 80 and HTTP the process is almost trivial >> >> Our system maintains sync'd databases and compensates for network >> outages quite robustly. Remote work sites can continue to operate when >> the communication link is down and thus deliver 24/7 availability.. >> > > Could you describe this in more detail? > The application is actually a set of remote weighstations. The load cell is interfaced to a PC which has an internet connection. The local PC maintains a mirror of the parts of the central database relevant to that location. Transcaction data is embedded in an XML document and uses port 80 to penetrate a firewall and reach the central server. An ACK or NAK comes back in XML. Reference data updates come from the central server in XML and update the local mirror DB. Locally added reference data ytiggers an XML message to the central server. Each table in the central server has a signature word which is updated at each modification. The remote mirros use that signature to verify their sync state. When the internet connection is broken local transactions are queued and when the connection is restored the queue empties. The remote application is a Windows or Linux program which implements HTTP protocol. The central server just uses a regular web server. XML is used to carry the data so that changes in database schema and message content will not break deployed systems. This system is easy to install because it will work anywhere a browser will work to support this simple distributed database. > > ___ > 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] SQLite Editor or Database Console for iPhone/iPod Touch
Anyone here on the list have experience with either app? Thanks, Monte ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] string is converted into a number
On 13 Jul 2009, at 4:35pm, Wilfried Mestdagh wrote: > But the circumstances are not really described (possible I cannot read > between the lines as my English is not perfect). So as far as I > understand > the page if I want to store / retrieve a string (which can be a > numeric > string) I have to create my field as "char", "text" or as "none". Is > this > correct? The reference you were pointed to explains what happens: http://www.sqlite.org/datatype3.html#affinity So you want 'TEXT' ... 'char' doesn't mean anything to SQLite. And you also need to pass your values as text too. If you use (1, 2, 3, 4) or use numeric binding, you're passing numeric values into it, and they may be manipulated as numbers. If you pass ('1', '2', '3', '4') or use string binding, then you're passing text, and if it's stored in TEXT fields SQLite won't do anything numeric with it. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] string is converted into a number
Wilfried Mestdagh wrote: > Hi Dan, > > Thank you for your reply. I read: > > >> Under circumstances described below, the database engine may convert >> values between numeric storage classes (INTEGER > and REAL) and >> TEXT during query execution >> > > But the circumstances are not really described (possible I cannot read > between the lines as my English is not perfect). So as far as I understand > the page if I want to store / retrieve a string (which can be a numeric > string) I have to create my field as "char", "text" or as "none". Is this > correct? > ...or varchar, which is SQL for string. John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] string is converted into a number
Hi Dan, Thank you for your reply. I read: > Under circumstances described below, the database engine may convert > values between numeric storage classes (INTEGER > and REAL) and >TEXT during query execution But the circumstances are not really described (possible I cannot read between the lines as my English is not perfect). So as far as I understand the page if I want to store / retrieve a string (which can be a numeric string) I have to create my field as "char", "text" or as "none". Is this correct? thanks, Wilfried 2009/7/13 Dan > > On Jul 13, 2009, at 3:38 PM, Wilfried Mestdagh wrote: > > > Hi, > > > > It seems that if I create the table as a field type 'char' instead of > > 'string' then the issue is solved. But I thought the field type was > > of non > > importance? > > Details here: > > http://www.sqlite.org/datatype3.html#affinity > > > > > > > > rgds, Wilfried > > > > 2009/7/13 Wilfried Mestdagh > > > >> Hello, > >> > >> I'm using sqlite3.dll and Delphi 7. In certain cirumstances a > >> string seems > >> to be converted to a number. To test I use SQLiteSpy from Ralf > >> Junker wich > >> is a nice tool. > >> > >> When I do this: > >> insert into Queue (NetworkID) values ("200907130833123740007") > >> > >> Then the result of the field NetworkID is: > >> 2.00907130833124E20 > >> > >> To solve this I add a blank in front of the ID, like this: > >> insert into Queue (NetworkID) values (" 200907130833123740007") > >> > >> But is this a good method, and is this a known issue? > >> > >> The table is created like this: > >> 'create table Queue (' + > >> '[NetworkID] string, ' + > >> '[State] integer, ' + > >> // Etc... > >> > >> -- > >> mvg, Wilfried > >> http://www.mestdagh.biz > >> > > > > > > > > -- > > mvg, Wilfried > > http://www.mestdagh.biz > > ___ > > 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 > -- mvg, Wilfried http://www.mestdagh.biz ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
-- This message has been scanned for viruses and dangerous content by Pinpoint, and is believed to be clean. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] writing images to an SQLite database using SQLite commandline program.
chandan wrote: > I would like to know how to store images inside a SQLite database > using the SQLite command line program. You can't, really. You'd have to write your own application to handle such BLOBs. > consider the following example: > > create table img_tbl ( > img_id int primary key, > img blob); > > In the above case how do I use the SQL "insert" statement to store > images into the "img" column? Well, the syntax you could use in sqlite3 command line utility is insert into img_tbl(img) values(x'1234ABCD...') where the value in parentheses is the contents of the file represented in hex. This is, of course, impractical except for very small blobs. That's why you need to write your own program to do that, using SQLite API. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] two question, 1.)sqlite3's select? 2.)errno and the sqlite3's result code
liubin liu wrote: > 1.)How does sqlite3's select work? It works well. What specifically do you want to now? > Does it need to seach for all the records? Sometimes. Other times, it could use indexes to reduce the number of records a statement needs to look at. See: http://sqlite.org/optoverview.html http://sqlite.org/lang_explain.html > 2.)Is there any relation between errno and the sqlite3's result code? None. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3 is truncating numbers--can you help me figure out why?
On Sun, Jul 12, 2009 at 11:00 PM, tetragon tetragon wrote: > create table words (_id integer primary key autoincrement, wordtext text not > null unique, timestamp integer not null); > > public class Word > { > long _id; > String wordtext; > long timestamp; > } > > timestamp: > before save: 1247435151517 > after save : 1247435160847 > > Why is it doing this? > > > not for me... [10:54 AM] ~$sqlite3 SQLite version 3.6.11 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table words (_id integer primary key autoincrement, wordtext text not null unique, timestamp integer not null); sqlite> insert into words (wordtext, timestamp) values ('foo', 1247435151517); sqlite> select * from words; 1|foo|1247435151517 sqlite> maybe your application? -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite 3 is truncating numbers--can you help me figure out why?
create table words (_id integer primary key autoincrement, wordtext text not null unique, timestamp integer not null); public class Word { long _id; String wordtext; long timestamp; } timestamp: before save: 1247435151517 after save : 1247435160847 Why is it doing this? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] "database is locked" error using lastest linux kernel
My problem is get a "database is locked" error using lastest linux kernel (above 2.6.28) , while the code can run smoothly on linux 2.6.26.2 kernel(vmware pc686 host) and on 2.6.26.3 kernel(arm9 embed system). The problem code is: #if 1 if (sqlite3_exec(gJcDb, "PRAGMA cache_size = 4000", NULL, NULL, &errMsg) != SQLITE_OK) { fprintf(stderr, "!!!cache_size set error, %s\n", errMsg); sqlite3_free(errMsg); } #endif Even if I comment the the #if 0/1 #endif code block, still can't open a table and access the table data. I tried sqlite3.6.7 and sqlite3.6.16, the problem is same. Three attached files are compiling&making sqlite3 Makefile, short test code(code.c) and test database(jc.db). Compile Sqlite with full functions and NDEBUG option and run, I get following info: fcntl unknown 4 1 0 fcntl unknown 4 2 0 fcntl 1073864000 4 SETLK RDLCK 0 1 0 -1 fcntl-failure-reason: RDLCK 0 1 0 fcntl 1073864000 4 SETLK RDLCK 1073741824 1 1 -1 fcntl-failure-reason: RDLCK 1073741824 1 1 PRAGMA page_size value is 1024 PRAGMA temp_store value is 2 PRAGMA read_uncommitted value is 1 PRAGMA journal_mode value is off fcntl 1073864000 4 SETLK RDLCK 1073741824 1 229840 -1 fcntl-failure-reason: RDLCK 1073741824 1 229840 fcntl 1073864000 4 SETLK RDLCK 1073741824 1 229840 -1 fcntl-failure-reason: RDLCK 1073741824 1 229840 !!!Load Terminal from db failedfcntl 1073864000 4 SETLK RDLCK 1073741824 1 229840 -1 fcntl-failure-reason: RDLCK 1073741824 1 229840 Any advise from you will be appreciated! inline static int PragmaSetCallback(void * pParam, int pColumnCount, char ** pColumnValue, char ** pColumnName) { fprintf(stdout, "%s value is %s\n", (char *)pParam, pColumnValue[0]); return 0; } static bool OpenAndInitDb(char * pDbFileName) { char * errMsg = NULL; sqlite3_enable_shared_cache(1); if (sqlite3_open(pDbFileName, &gJcDb) != SQLITE_OK) { fprintf(stderr, "!!!Open database error: %s\n", sqlite3_errmsg(gJcDb)); return false; } #if 1 if (sqlite3_exec(gJcDb, "PRAGMA cache_size = 4000", NULL, NULL, &errMsg) != SQLITE_OK) { fprintf(stderr, "!!!cache_size set error, %s\n", errMsg); sqlite3_free(errMsg); } #endif sqlite3_exec(gJcDb, "PRAGMA cache_size", PragmaSetCallback, "PRAGMA cache_size", &errMsg); #if 1 if (SQLITE_OK != sqlite3_exec(gJcDb, "PRAGMA synchronous = FULL", NULL, NULL, &errMsg)) //OFF FULL NORMAL { fprintf(stderr, "!!!synchronous set error, %s\n", errMsg); sqlite3_free(errMsg); } #endif sqlite3_exec(gJcDb, "PRAGMA synchronous", PragmaSetCallback, "PRAGMA synchronous", &errMsg); return true; } static bool LoadTerminalFromDb(sqlite3 * pDb, Terminal * pTerminal) { pTerminal->WorkStateId = 1; pTerminal->DefaultUpChannelTypeId = ChannelType_UpTnGprsClient; pTerminal->IsChanged = false; sqlite3_stmt * stmt = 0; if (sqlite3_prepare_v2(pDb, "select * from Terminal", -1, &stmt, 0) != SQLITE_OK) { return false; } if (sqlite3_step(stmt) != SQLITE_ROW) { sqlite3_finalize(stmt); return false; } return true; } sqlite3 *gJcDb = NULL; int main(int argc, char *argv[]) { char * db = "./jc.db"; if (access(db, F_OK) || !OpenAndInitDb(db)) { fprintf(stderr, "!!!Open and init db failed"); return 1; } if (!LoadTerminalFromDb(gJcDb, &gTerminal)) { fprintf(stderr, "!!!Load Terminal from db failed"); CloseDb(gJcDb); return 2; } return 0; }___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] string is converted into a number
On Jul 13, 2009, at 3:38 PM, Wilfried Mestdagh wrote: > Hi, > > It seems that if I create the table as a field type 'char' instead of > 'string' then the issue is solved. But I thought the field type was > of non > importance? Details here: http://www.sqlite.org/datatype3.html#affinity > > > rgds, Wilfried > > 2009/7/13 Wilfried Mestdagh > >> Hello, >> >> I'm using sqlite3.dll and Delphi 7. In certain cirumstances a >> string seems >> to be converted to a number. To test I use SQLiteSpy from Ralf >> Junker wich >> is a nice tool. >> >> When I do this: >> insert into Queue (NetworkID) values ("200907130833123740007") >> >> Then the result of the field NetworkID is: >> 2.00907130833124E20 >> >> To solve this I add a blank in front of the ID, like this: >> insert into Queue (NetworkID) values (" 200907130833123740007") >> >> But is this a good method, and is this a known issue? >> >> The table is created like this: >> 'create table Queue (' + >> '[NetworkID] string, ' + >> '[State] integer, ' + >> // Etc... >> >> -- >> mvg, Wilfried >> http://www.mestdagh.biz >> > > > > -- > mvg, Wilfried > http://www.mestdagh.biz > ___ > 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] string is converted into a number
Hi, It seems that if I create the table as a field type 'char' instead of 'string' then the issue is solved. But I thought the field type was of non importance? rgds, Wilfried 2009/7/13 Wilfried Mestdagh > Hello, > > I'm using sqlite3.dll and Delphi 7. In certain cirumstances a string seems > to be converted to a number. To test I use SQLiteSpy from Ralf Junker wich > is a nice tool. > > When I do this: > insert into Queue (NetworkID) values ("200907130833123740007") > > Then the result of the field NetworkID is: > 2.00907130833124E20 > > To solve this I add a blank in front of the ID, like this: > insert into Queue (NetworkID) values (" 200907130833123740007") > > But is this a good method, and is this a known issue? > > The table is created like this: > 'create table Queue (' + >'[NetworkID] string, ' + >'[State] integer, ' + >// Etc... > > -- > mvg, Wilfried > http://www.mestdagh.biz > -- mvg, Wilfried http://www.mestdagh.biz ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
-- This message has been scanned for viruses and dangerous content by Pinpoint, and is believed to be clean. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] string is converted into a number
Hello, I'm using sqlite3.dll and Delphi 7. In certain cirumstances a string seems to be converted to a number. To test I use SQLiteSpy from Ralf Junker wich is a nice tool. When I do this: insert into Queue (NetworkID) values ("200907130833123740007") Then the result of the field NetworkID is: 2.00907130833124E20 To solve this I add a blank in front of the ID, like this: insert into Queue (NetworkID) values (" 200907130833123740007") But is this a good method, and is this a known issue? The table is created like this: 'create table Queue (' + '[NetworkID] string, ' + '[State] integer, ' + // Etc... -- mvg, Wilfried http://www.mestdagh.biz ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] two question, 1.)sqlite3's select? 2.)errno and the sqlite3's result code
1.)How does sqlite3's select work? Does it need to seach for all the records? 2.)Is there any relation between errno and the sqlite3's result code? -- View this message in context: http://www.nabble.com/two-question%2C-1.%29sqlite3%27s-select--2.%29errno-and-the-sqlite3%27s-result-code-tp24457119p24457119.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