Re: [sqlite] range enclosing a number

[Jay A. Kreibich]

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
  

Re: [sqlite] range enclosing a number

[John Machin]

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

[Pavel Ivanov]

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

[Jay A. Kreibich]

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

[Bogdan Nicula]

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

[Simon Slavin]

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

[Simon Slavin]

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

[John Machin]

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

[Wes Freeman]

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

[Bogdan Nicula]

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

[Wes Freeman]

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

[Wes Freeman]

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

[Bogdan Nicula]

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

[Jan]

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

[Kees Nuyt]

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

[John Stanton]

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

[Monte Milanuk]

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

[Simon Slavin]

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

[John Elrick]

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

[Wilfried Mestdagh]

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)

[Frank]

-- 
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.

[Igor Tandetnik]

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

[Igor Tandetnik]

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?

[P Kishor]

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?

[tetragon tetragon]

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

[hua zhou]

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, ) != 
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 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, ) != 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, 
) != 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", );



#if 1
if (SQLITE_OK != sqlite3_exec(gJcDb, "PRAGMA synchronous = FULL", NULL, 
 NULL, )) //OFF FULL NORMAL
{
fprintf(stderr, "!!!synchronous set error, %s\n", errMsg);
sqlite3_free(errMsg);
}
#endif
sqlite3_exec(gJcDb, "PRAGMA synchronous", PragmaSetCallback, "PRAGMA 
synchronous", );

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, , 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, ))
{
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

[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

Re: [sqlite] string is converted into a number

[Wilfried Mestdagh]

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)

[Frank]

-- 
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

[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
___
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

[liubin liu]

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

[sqlite] writing images to an SQLite database using SQLite command line program.

[chandan]

Hi,
   I would like to know how to store images inside a SQLite database 
using the SQLite command line program.

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?

Regards,
chandan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users