Re: [sqlite] sqlite3_open_v2 vs. *ppDb=NULL

[Krystian Bigaj]

Thanks! I'm not sure how I've missed it.

-- 
Best regards,
Krystian Bigaj
On Oct 12, 2011 11:01 PM, "David Garfield" 
wrote:

> The documentation also says (http://sqlite.org/c3ref/open.html):
>
>   If the 3rd parameter to sqlite3_open_v2() is not one of the
>   combinations shown above optionally combined with other
>   SQLITE_OPEN_* bits then the behavior is undefined.
>
> Seems like the undefined behavior was non-dangerous.
>
> That sqlite3_errmsg() had to guess and guessed wrong is not
> surprising.
>
> --David Garfield
>
> Krystian Bigaj writes:
> > Hi,
> >
> > Documentation says that:
> >
> > "The only exception is that if SQLite is unable to allocate memory to
> hold
> > the sqlite3 object, a NULL will be written into *ppDb instead of a
> pointer
> > to the sqlite3 object"
> >
> > but I see it's not entirely true. This example below stores NULL in *ppDb
> > even when (I think) no memory allocation problems occurs:
> >
> > void TestOpenMisuse(void)
> > {
> >   sqlite3 *ppDb;
> >   int err = sqlite3_open_v2("whatever", , SQLITE_OPEN_CREATE , 0);
> >   if (ppDb == NULL)
> >   {
> > printf("ppDb==NULL, but err==%d, sqlite3_errmsg(ppDb)==%s",
> > err, sqlite3_errmsg(ppDb));
> >   }
> > }
> >
> >
> > Error code in this case will be err==21 (SQLITE_MISUSE) because
> > flag SQLITE_OPEN_CREATE cannot be there without SQLITE_OPEN_READWRITE -
> > which is docummented.
> > But sqlite3_errmsg(ppDb) will return "out of memory" (because
> ppDb==NULL),
> > which can be sometimes little misleading (for me as a SQLite newbie it
> was
> > ;).
> >
> > SQLite 3.7.8 (amalgamation)
> >
> > --
> > Best regards,
> > Krystian Bigaj
> > ___
> > 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_open_v2 vs. *ppDb=NULL

[David Garfield]

The documentation also says (http://sqlite.org/c3ref/open.html):

   If the 3rd parameter to sqlite3_open_v2() is not one of the
   combinations shown above optionally combined with other
   SQLITE_OPEN_* bits then the behavior is undefined.

Seems like the undefined behavior was non-dangerous.

That sqlite3_errmsg() had to guess and guessed wrong is not
surprising.

--David Garfield

Krystian Bigaj writes:
> Hi,
> 
> Documentation says that:
> 
> "The only exception is that if SQLite is unable to allocate memory to hold
> the sqlite3 object, a NULL will be written into *ppDb instead of a pointer
> to the sqlite3 object"
> 
> but I see it's not entirely true. This example below stores NULL in *ppDb
> even when (I think) no memory allocation problems occurs:
> 
> void TestOpenMisuse(void)
> {
>   sqlite3 *ppDb;
>   int err = sqlite3_open_v2("whatever", , SQLITE_OPEN_CREATE , 0);
>   if (ppDb == NULL)
>   {
> printf("ppDb==NULL, but err==%d, sqlite3_errmsg(ppDb)==%s",
> err, sqlite3_errmsg(ppDb));
>   }
> }
> 
> 
> Error code in this case will be err==21 (SQLITE_MISUSE) because
> flag SQLITE_OPEN_CREATE cannot be there without SQLITE_OPEN_READWRITE -
> which is docummented.
> But sqlite3_errmsg(ppDb) will return "out of memory" (because ppDb==NULL),
> which can be sometimes little misleading (for me as a SQLite newbie it was
> ;).
> 
> SQLite 3.7.8 (amalgamation)
> 
> -- 
> Best regards,
> Krystian Bigaj
> ___
> 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] sqlite3_open_v2 vs. *ppDb=NULL

[Krystian Bigaj]

Hi,

Documentation says that:

"The only exception is that if SQLite is unable to allocate memory to hold
the sqlite3 object, a NULL will be written into *ppDb instead of a pointer
to the sqlite3 object"

but I see it's not entirely true. This example below stores NULL in *ppDb
even when (I think) no memory allocation problems occurs:

void TestOpenMisuse(void)
{
  sqlite3 *ppDb;
  int err = sqlite3_open_v2("whatever", , SQLITE_OPEN_CREATE , 0);
  if (ppDb == NULL)
  {
printf("ppDb==NULL, but err==%d, sqlite3_errmsg(ppDb)==%s",
err, sqlite3_errmsg(ppDb));
  }
}


Error code in this case will be err==21 (SQLITE_MISUSE) because
flag SQLITE_OPEN_CREATE cannot be there without SQLITE_OPEN_READWRITE -
which is docummented.
But sqlite3_errmsg(ppDb) will return "out of memory" (because ppDb==NULL),
which can be sometimes little misleading (for me as a SQLite newbie it was
;).

SQLite 3.7.8 (amalgamation)

-- 
Best regards,
Krystian Bigaj
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow JOIN on ROWID

[Petite Abeille]

On Oct 12, 2011, at 5:16 PM, Fabian wrote:

> Why is this very fast (20 ms):
> 0 0 0 SEARCH TABLE table1 USING INDEX data1idx (data1=?) (~3 rows)
> And this very slow (3500ms):
> 0 0 0 SEARCH TABLE table1 USING INDEX data1idx (data1=?) (~3 rows)
> 0 1 1 SCAN TABLE table2 VIRTUAL TABLE INDEX 1: (~0 rows)

The issue here is that offset works in term of your entire query. So for each 
row in table1 matching your where close, it's first going to do a join to 
table2, order the entire result set, skip the first half-a-million rows in the 
result set and then return the remaining 250 rows. A rather expensive 
proposition.

Here is an example using two table: mail_header [1] and mail_header_text, a FTS 
table [2]. It's a one-to-one relationship.

(0) Querying the count

selectcount( * )
from  mail_header

where mail_header.header_id = 2

order by  mail_header.id

0|0|0|SCAN TABLE mail_header (~219250 rows)

CPU Time: user 0.690721 sys 0.064676

Ok, 83,391 rows at play.


(1) Querying mail_header, with an offset

explain query plan
selectmail_header.id
from  mail_header

where mail_header.header_id = 2

order by  mail_header.id

limit 250
offset5;

0|0|0|SCAN TABLE mail_header USING INTEGER PRIMARY KEY (~219250 rows)

CPU Time: user 0.390615 sys 0.037031

Ok, we get 250 rows, after sorting 83,391 rows and skipping 50,000 of them.


(2) Same, but with join to mail_header_text 

explain query plan
selectmail_header.id,
  mail_header_text.value
from  mail_header

join  mail_header_text
onmail_header_text.docid = mail_header.id

where mail_header.header_id = 2

order by  mail_header.id

limit 250
offset5;

0|0|0|SCAN TABLE mail_header USING INTEGER PRIMARY KEY (~219250 rows)
0|1|1|SCAN TABLE mail_header_text VIRTUAL TABLE INDEX 1: (~0 rows)

CPU Time: user 2.153607 sys 0.265462

Note how it's an order of magnitude slower. This is because all these 
one-to-one joins on these 83K mail_header. They do have a cost.


(3) Same, with a join, but with the offset factored out 

explain query plan
selectmail_header.id,
  mail_header_text.value
from  (
selectmail_header.id
from  mail_header

where mail_header.header_id = 2

order by  mail_header.id

limit 250
offset5
  )
asmail_header

join  mail_header_text
onmail_header_text.docid = mail_header.id;

1|0|0|SCAN TABLE mail_header USING INTEGER PRIMARY KEY (~219250 rows)
0|0|0|SCAN SUBQUERY 1 AS mail_header (~250 rows)
0|1|1|SCAN TABLE mail_header_text VIRTUAL TABLE INDEX 1: (~0 rows)

CPU Time: user 0.402250 sys 0.039327

Now the join is performed only 250 times, adding just a small overhead compare 
the the bare bone query without the join.

The short of it: minimize the amount of work upfront :)

[1] http://dev.alt.textdrive.com/browser/Mail/Mail.ddl#L271
[2] http://dev.alt.textdrive.com/browser/Mail/Mail.ddl#L260
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Speed of sorting - diff between integer and string

[Shorty]

Thanks a bunch for all the great info and the benchmark test !!!

Also thanks for explaining the term normalization - as you can probably tell 
I am a newbie, and it is hard to look stuff up without knowing the right 
terms.  I spent hours looking for a technique to split results into pages, 
and then stumbled on the term "pagination", which really cracked open the 
door to finding great information that other programmers had posted.


I should have included the size of the database and other info, so here it 
is, incase that makes a difference.

--
Using the same grocery store example, the database I am going to create is 
probably going to peak out at 5000 grocery items, but may get as large as 
7000, but doubt any larger than that.


Each of those items is going to fall into one category, with only 10 
different categories (like fruits, vegetables, canned goods, boxed 
goods,...)


Then I am creating a second database that is like brands of the items. 
Such as Dole bananas, Delmonte bananas, John Doe's organic bananas etc. 
That database will have about 15,000 items in it, each one relating to just 
one of the grocery store items.


I am running this with php 5 and the sqlite that is included with php 5, on 
a web server that is the shared type.The website is not a storefront, it 
is a freely available database I am building that anyone in the public can 
access without any type of login.  I am expecting about 5,000 views a day on 
the page that returns the search query that I am asking about.


Thanks
Shorty 


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

Re: [sqlite] create sqlite3_value * from scratch?

[Ivan Shmakov]

> Pavel Ivanov writes:
> On Wed, Oct 12, 2011 at 11:12 AM, Ivan Shmakov wrote:

[…]

 >> Consider, e. g.:

 >> sqlite3_value *a
 >>   = sqlite3_int64_value (1);
 >> assert (a != 0);
 >> sqlite3_value *b
 >>   = sqlite3_text_value (-1, "qux");
 >> sqlite3_value *c
 >>   = sqlite3_blob_value (blob_size, blob);
 >> assert (b != 0);
 >> int r;
 >> r = db_triv_exec_bound (db, sql_1, a, b, 0);
 >> assert (r == SQLITE_OK);

[…]

 > You can do absolutely the same thing but use your_own_value* instead
 > of sqlite3_value*.  Why stick with SQLite's internal data structures?

No special reason, but it would have saved me some work.  Not to
mention that it seems to me a bit like a duplicate effort.

Note also that such a code would've to be updated should a new
type be introduced to SQLite (however unlikely, it still may
happen.)

-- 
FSF associate member #7257

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

Re: [sqlite] create sqlite3_value * from scratch?

[Pavel Ivanov]

On Wed, Oct 12, 2011 at 11:12 AM, Ivan Shmakov  wrote:
>  >> Given some way to construct a sqlite3_value wrapping object, I
>  >> could instead rely on sqlite3_bind_value () alone, thus
>  >> eliminating the necessity of type specifiers in the interface.
>
>  > Well, a hypothetical API that constructs sqlite3_value from raw data
>  > would have to take the type specifier anyway, wouldn't it?  You would
>  > just be moving the same logic to another place.
>
>        Yes.  But this still may make code clearer, and, occasionally,
>        also more concise.  Consider, e. g.:
>
>   sqlite3_value *a
>     = sqlite3_int64_value (1);
>   assert (a != 0);
>   sqlite3_value *b
>     = sqlite3_text_value (-1, "qux");
>   sqlite3_value *c
>     = sqlite3_blob_value (blob_size, blob);
>   assert (b != 0);
>   int r;
>   r = db_triv_exec_bound (db, sql_1, a, b, 0);
>   assert (r == SQLITE_OK);
>   r = db_triv_exec_bound (db, sql_2, b, c, 0);
>   assert (r == SQLITE_OK);
>   r = db_triv_exec_bound (db, sql_3, c, a, 0);
>   assert (r == SQLITE_OK);
>   sqlite3_value_free (a);
>   sqlite3_value_free (b);
>   sqlite3_value_free (c);

You can do absolutely the same thing but use your_own_value* instead
of sqlite3_value*. Why stick with SQLite's internal data structures?


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

Re: [sqlite] internal column header not case sensitive?

[Pavel Ivanov]

On Wed, Oct 12, 2011 at 11:28 AM, Simon Slavin  wrote:
> However, along the way ...
>
> sqlite> select a, case when b = 't' then 1 end, case when
>   ...> b = 'T' then 1 end from t;
> 1|1|
> 1|1|
> 1||1
> 2|1|
> 2||1
> 2||1
> sqlite> select a, case when b = 't' then 1 end, case when
>   ...> b = 'T' then 2 end from t;
> 1|1|
> 1|1|
> 1||2
> 2|1|
> 2||2
> 2||2
>
> Was that not what you were expecting ?  You are using count(2) not sum(2).

So? Number of non-NULL values in third column is 1 if first column is
equal 1 and 2 if first column is equal 2. And it doesn't matter
whether you use 1 inside case or 2. But results of select statement
show different numbers.


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

Re: [sqlite] internal column header not case sensitive?

[Petite Abeille]

On Oct 12, 2011, at 5:28 PM, Simon Slavin wrote:

> Was that not what you were expecting ?  You are using count(2) not sum(2).

I guess the posted test case had a typo.

Nonetheless, contrast:

sqlite> select a, sum(case when b = 't' then 1 end), sum(case when b = 'T' then 
1 end) from t group by a;
1|2|2
2|1|1

sqlite> select a, sum(case when lower( b ) = b and b = 't' then 1 end), 
sum(case when upper( b) = b and b = 'T' then 1 end) from t group by a;
1|2|1
2|1|2

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

Re: [sqlite] internal column header not case sensitive?

[Simon Slavin]

On 12 Oct 2011, at 3:54pm, Petite Abeille wrote:

> On Oct 12, 2011, at 4:45 PM, Simon Slavin wrote:
> 
>> You didn't define the columns as text columns, so SQLite doesn't understand 
>> that 'T' is anything like 't'.
> 
> Hmmm...?!?!? 

Sorry, I messed up and INSERTed the rows a second time and got different 
results.  My mistake.

However, along the way ...

sqlite> select a, case when b = 't' then 1 end, case when
   ...> b = 'T' then 1 end from t;
1|1|
1|1|
1||1
2|1|
2||1
2||1
sqlite> select a, case when b = 't' then 1 end, case when
   ...> b = 'T' then 2 end from t;
1|1|
1|1|
1||2
2|1|
2||2
2||2

Was that not what you were expecting ?  You are using count(2) not sum(2).

Simon.

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

[sqlite] Slow JOIN on ROWID

[Fabian]

Why is this very fast (20 ms):



SELECT table1.data1, table1.data2
FROM table1
WHERE table1.data1 = 10
ORDER BY table1.rowid DESC
LIMIT 250
OFFSET 50

0 0 0 SEARCH TABLE table1 USING INDEX data1idx (data1=?) (~3 rows)



And this very slow (3500ms):



SELECT table1.data1, table2.data2
FROM table1 JOIN table2 ON table1.rowid=table2.rowid
WHERE table1.data1 = 10
ORDER BY table1.rowid DESC
LIMIT 250
OFFSET 50

0 0 0 SEARCH TABLE table1 USING INDEX data1idx (data1=?) (~3 rows)
0 1 1 SCAN TABLE table2 VIRTUAL TABLE INDEX 1: (~0 rows)



The values in table1.data2 and table2.data2 are 100% identical, because my
app has always duplicated all columns of table2 (fts4 table) into table1 to
work around this issue. But I really like to solve it some day, because it
doubles my database size.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] create sqlite3_value * from scratch?

[Ivan Shmakov]

> Igor Tandetnik writes:
> Ivan Shmakov  wrote:

 >> I wonder, is it possible to create sqlite3_value * from scratch
 >> from within SQLite library's user code?

 > There's a circuitous route that leads there.  You can prepare a
 > statement of the form "select ?;", bind the parameter with one of
 > sqlite3_bind_* functions, then step it once, and retrieve the value
 > right back with sqlite3_column_value.  Note that the resulting
 > sqlite3_value is "unprotected" (see
 > http://sqlite.org/c3ref/value.html) and can only be used in limited
 > ways.  And of course it's only valid until the "select ?;" statement
 > is reset or finalized.

Unfortunately, the latter clearly prevents the kind of interface
I've had in mind (below.)

 >> Given some way to construct a sqlite3_value wrapping object, I
 >> could instead rely on sqlite3_bind_value () alone, thus
 >> eliminating the necessity of type specifiers in the interface.

 > Well, a hypothetical API that constructs sqlite3_value from raw data
 > would have to take the type specifier anyway, wouldn't it?  You would
 > just be moving the same logic to another place.

Yes.  But this still may make code clearer, and, occasionally,
also more concise.  Consider, e. g.:

   sqlite3_value *a
 = sqlite3_int64_value (1);
   assert (a != 0);
   sqlite3_value *b
 = sqlite3_text_value (-1, "qux");
   sqlite3_value *c
 = sqlite3_blob_value (blob_size, blob);
   assert (b != 0);
   int r;
   r = db_triv_exec_bound (db, sql_1, a, b, 0);
   assert (r == SQLITE_OK);
   r = db_triv_exec_bound (db, sql_2, b, c, 0);
   assert (r == SQLITE_OK);
   r = db_triv_exec_bound (db, sql_3, c, a, 0);
   assert (r == SQLITE_OK);
   sqlite3_value_free (a);
   sqlite3_value_free (b);
   sqlite3_value_free (c);

And I see no way to implement my_sqlite3_int64_value () using
the identity SELECT (above), as it'd require
my_sqlite3_value_free () to find the prepared statement
associated with the value (to call sqlite3_finalize () on it, so
to avoid a memory leak), and I know of no documented way of
doing that.

-- 
FSF associate member #7257

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

Re: [sqlite] FTS4: OFFSET Performance

[Fabian]

2011/10/12 Dan Kennedy 

>
> (INDEX 1) == lookup by rowid.
>
>
Okay! Then maybe my problem is unrelated to FTS, I will create a new
question.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] internal column header not case sensitive?

[Petite Abeille]

On Oct 12, 2011, at 4:45 PM, Simon Slavin wrote:

> You didn't define the columns as text columns, so SQLite doesn't understand 
> that 'T' is anything like 't'.

Hmmm...?!?!? 

select b, typeof( b ) from t;
t|text
t|text
T|text
t|text
T|text
T|text

http://www.sqlite.org/datatype3.html

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

Re: [sqlite] FTS4: OFFSET Performance

[Dan Kennedy]

On 10/12/2011 09:39 PM, Fabian wrote:

2011/10/12 Dan Kennedy



Can we see the output of EXPLAIN for this query?







Without selecting table2.data2:

0 0 0 SEARCH TABLE table1 USING INDEX data1idx (data1=?) (~3 rows)

Including selecting table2.data2:

0 0 0 SEARCH TABLE table1 USING INDEX data1idx (data1=?) (~3 rows)
0 1 1 SCAN TABLE table2 VIRTUAL TABLE INDEX 1: (~0 rows)

So it looks like the whole problem is caused by the 'SCAN' on table2. Is
there any reason why SQLite does this SCAN instead of a SEARCH, since I JOIN
the two tables on ROWID, why does it have to do a full table scan?


(INDEX 1) == lookup by rowid.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] internal column header not case sensitive?

[Simon Slavin]

On 12 Oct 2011, at 3:27pm, Pavel Ivanov wrote:

> sqlite> create table t (a, b);

You didn't define the columns as text columns, so SQLite doesn't understand 
that 'T' is anything like 't'.

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

Re: [sqlite] internal column header not case sensitive?

[Petite Abeille]

On Oct 12, 2011, at 4:27 PM, Pavel Ivanov wrote:

> I can confirm this with the following script:
> 
> sqlite> select a, count(case when b = 't' then 1 end), count(case when b = 
> 'T' then 1 end) from t group by a;
> 1|2|2
> 2|1|1

Hmmm... yes... something very wrong:

select a, count(case when lower( b ) = b and b = 't' then 1 end), count(case 
when upper( b) = b and b = 'T' then 1 end) from t group by a;

1|2|1
2|1|2

$ sqlite3 -version
3.7.8 2011-09-19 14:49:19 3e0da808d2f5b4d12046e05980ca04578f581177


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

Re: [sqlite] FTS4: OFFSET Performance

[Fabian]

2011/10/12 Dan Kennedy 

>
> Can we see the output of EXPLAIN for this query?
>

>

Without selecting table2.data2:

0 0 0 SEARCH TABLE table1 USING INDEX data1idx (data1=?) (~3 rows)

Including selecting table2.data2:

0 0 0 SEARCH TABLE table1 USING INDEX data1idx (data1=?) (~3 rows)
0 1 1 SCAN TABLE table2 VIRTUAL TABLE INDEX 1: (~0 rows)

So it looks like the whole problem is caused by the 'SCAN' on table2. Is
there any reason why SQLite does this SCAN instead of a SEARCH, since I JOIN
the two tables on ROWID, why does it have to do a full table scan?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] internal column header not case sensitive?

[Pavel Ivanov]

I can confirm this with the following script:

SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t (a, b);
sqlite> insert into t values (1, 't');
sqlite> insert into t values (1, 't');
sqlite> insert into t values (1, 'T');
sqlite> insert into t values (2, 't');
sqlite> insert into t values (2, 'T');
sqlite> insert into t values (2, 'T');
sqlite> select a, count(case when b = 't' then 1 end), count(case when
b = 'T' then 1 end) from t group by a;
1|2|2
2|1|1
sqlite> select a, count(case when b = 't' then 1 end), count(case when
b = 'T' then 2 end) from t group by a;
1|2|1
2|1|2
sqlite>


Pavel


On Tue, Oct 11, 2011 at 11:30 AM, Nelson, Bjorn
 wrote:
> I am having an issue where I build a query that creates columns based on case 
> expressions.  The expressions are identical except for a case difference.  
> The result only shows data from the first expression duplicated in both 
> columns.
>
> The query that results in the issue:
> select
>  mycategory,
>  count(case when type = 'typea' then 1 end) as 'test1',
>  count(case when type = 'Typea' then 1 end) as 'test2'
> from mytable
> group by mycategory
>
> Results in a table like this:
> mycategory|test1|test2
> cat1|500|500
> cat2|350|350
>
> But if I change it to:
> select
>  mycategory,
>  count(case when type = 'typea' then 1 end) as 'test1',
>  count(case when type = 'Typea' then 2 end) as 'test2'
> from mytable
> group by mycategory
>
> Results in a table like this:
> mycategory|test1|test2
> cat1|500|200
> cat2|350|150
>
> It's as if, even though I specify an AS clause, it is representing the column 
> header internally as the expression (ignoring the unique name I provided), 
> and since columns are case insensitive then it just refers to the first 
> column when printing it out.
>
> Bjorn Nelson
>
> --
> NOTICE: Morgan Stanley is not acting as a municipal advisor and the opinions 
> or views contained herein are not intended to be, and do not constitute, 
> advice within the meaning of Section 975 of the Dodd-Frank Wall Street Reform 
> and Consumer Protection Act. If you have received this communication in 
> error, please destroy all electronic and paper copies and notify the sender 
> immediately. Mistransmission is not intended to waive confidentiality or 
> privilege. Morgan Stanley reserves the right, to the extent permitted under 
> applicable law, to monitor electronic communications. This message is subject 
> to terms available at the following link: 
> http://www.morganstanley.com/disclaimers. If you cannot access these links, 
> please notify us by reply message and we will send the contents to you. By 
> messaging with Morgan Stanley you consent to the foregoing.
> ___
> 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] DateTimeOffset in SQLite

[Pavel Ivanov]

On Wed, Oct 12, 2011 at 9:08 AM, Steffen Mangold
 wrote:
>
> Igor Tandetnik wrote:
>
>>What's DateTimeOffset? Offset from what to what? What exactly are you trying 
>>to achieve?
>>See if this helps: http://www.sqlite.org/lang_datefunc.html
>
> In C# (.Net) it is the type Timespan.
> I want to save a timespan in the DB and get an TimeSpan object in .Net out of 
> the DB.

I don't know C#, but quick look at Microsoft's documentation shows
that you can get Ticks() from TimeSpan, save it into DB and then when
you get Int64 from DB you can create TimeSpan from it.


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

Re: [sqlite] EXT :Re: OFFSET Performance

[Black, Michael (IS)]

What about storing the rowid's of the offsets in a separate table and using 
that?



How dynamic is your data?





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Fabian [fabianpi...@gmail.com]
Sent: Wednesday, October 12, 2011 8:34 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] OFFSET Performance

2011/10/12 Igor Tandetnik 

>
> See if this helps: http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor


I tried to implement the method as suggested in the article, but it will
only work for pagination where the user is only allowed to go 1 page back or
1 page forward (since you have to remember the last rowid). In my case, the
user is allowed to jump to the last page, without visiting any of the
previous pages, making the suggested method impossible to implement.
___
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] OFFSET Performance

[Igor Tandetnik]

Fabian  wrote:
> 2011/10/12 Igor Tandetnik 
> 
>> 
>> See if this helps: http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor
> 
> 
> I tried to implement the method as suggested in the article, but it will
> only work for pagination where the user is only allowed to go 1 page back or
> 1 page forward (since you have to remember the last rowid). In my case, the
> user is allowed to jump to the last page, without visiting any of the
> previous pages, making the suggested method impossible to implement.

To get to the last page, you could reverse the ORDER BY, effectively reading 
backwards. Of course you'll have to reverse again in the application code, for 
presentation.
-- 
Igor Tandetnik

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

Re: [sqlite] OFFSET Performance

[Fabian]

2011/10/12 Igor Tandetnik 

>
> See if this helps: http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor


I tried to implement the method as suggested in the article, but it will
only work for pagination where the user is only allowed to go 1 page back or
1 page forward (since you have to remember the last rowid). In my case, the
user is allowed to jump to the last page, without visiting any of the
previous pages, making the suggested method impossible to implement.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTimeOffset in SQLite

[Steffen Mangold]

Igor Tandetnik wrote:

>What's DateTimeOffset? Offset from what to what? What exactly are you trying 
>to achieve?
>See if this helps: http://www.sqlite.org/lang_datefunc.html

In C# (.Net) it is the type Timespan.
I want to save a timespan in the DB and get an TimeSpan object in .Net out of 
the DB.


-- 
Steffen Mangold

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

Re: [sqlite] OFFSET Performance

[Fabian]

2011/10/12 Igor Tandetnik 

>
> See if this helps: http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor


Thanks! Very interesting! I already was aware that using a large OFFSET
could potentially be slow, because SQLite internally reads all preceding
rows, and just discards them. But I do my offsets exclusively on rowid, and
it appears there is some optimization taking place, since I can specify very
large offsets, without a change in performance. So I assumed that SQLite is
taking some shortcut when using rowid for OFFSET, and I was only wondering
why this 'shortcut' is disabled when joining a FTS table.

I'm going to implement the method described in the above article, and see if
it makes any differences.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS4: OFFSET Performance

[Fabian]

2011/10/12 Dan Kennedy 

>
> Are you using 3.7.7 or newer? And specifying the order=DESC
> option when creating the FTS4 table? If not, it might be a big
> help in this case.
>
>  
> http://www.sqlite.org/fts3.**html#fts4order
>
>
Yes I'm using 3.7.7, and I already considered specifying order=DESC upon
creation, but I suspect it will reverse the problem: Slow queries with
OFFSET=0 and fast queries with a large OFFSET, which would be no solution.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS4: OFFSET Performance

[Fabian]

2011/10/12 Petite Abeille 

>
> In any case, you can easily refactor the query into two steps:
>
> (1) First get your pagination
> (2) Then join to your fts
>
>
I tried implementing your refactoring, but immediately run into a problem.
The user must be able to sort the data on any of the columns returned.
So 'ORDER
BY table1.rowid DESC' is really a variabele, which could easily be 'ORDER BY
table2.data2 DESC', in which cases the query fails, because .data2 is not
selected in the inner query. And I cannot move 'ORDER BY' into the outer
loop, because else it will only sort the 250 results returned, not the
underlying data.

So the questions remains: why does increasing OFFSET kill performance when
selecting table2.data2 in additation to the columns of table1.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Prepare statement in separate function

[Igor Tandetnik]

enjoythe...@hushmail.com wrote:
> I was allocating memory because I wanted to prepare the statement
> in a separate function.

Just prepare the statement, and return sqlite3_stmt* by value. You are not 
allocating memory when returning, say, an int, right? sqlite3_stmt* is 
comparable in size, and can be treated similarly.
-- 
Igor Tandetnik

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

Re: [sqlite] OFFSET Performance

[Igor Tandetnik]

Fabian  wrote:
> This query returns the results as expected, and performs well. But as soon
> as I raise the OFFSET to a large value (for pagination) the performance
> drops drastically.

See if this helps: http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor
-- 
Igor Tandetnik

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

Re: [sqlite] create sqlite3_value * from scratch?

[Igor Tandetnik]

Ivan Shmakov  wrote:
> I wonder, is it possible to create sqlite3_value * from scratch
> from within SQLite library's user code?

There's a circuitous route that leads there. You can prepare a statement of the 
form "select ?;", bind the parameter with one of sqlite3_bind_* functions, then 
step it once, and retrieve the value right back with sqlite3_column_value. Note 
that the resulting sqlite3_value is "unprotected" (see 
http://sqlite.org/c3ref/value.html) and can only be used in limited ways. And 
of course it's only valid until the "select ?;" statement is reset or finalized.

> Given some way to construct a sqlite3_value wrapping object, I
> could instead rely on sqlite3_bind_value () alone, thus
> eliminating the necessity of type specifiers in the interface.

Well, a hypothetical API that constructs sqlite3_value from raw data would have 
to take the type specifier anyway, wouldn't it? You would just be moving the 
same logic to another place.
-- 
Igor Tandetnik

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

Re: [sqlite] : Speed of sorting - diff between integer and string

[Black, Michael (IS)]

To answer your question of which is fastersee the benchmark below...there's 
no difference at 100,000 records.  The index creation is a bit faster for 
integer.
But for your data you won't be doing many inserts or updates on items...more on 
prices so the grocery_type_index won't get touched very much.


#include 
main()
{
int i;
for(i=0;i<10;++i) {
char sql[4096];
char *type;
if ((i%5)==0) type="fruit";
if ((i%5)==1) type="vegetable";
if ((i%5)==2) type="meat";
if ((i%5)==3) type="sundrie";
if ((i%5)==4) type="bakery";
sprintf(sql,"insert into store 
values(%d,'%s',155);\n",i%5,type,"testing"); // for int grocery type
//sprintf(sql,"insert into store 
values('%s','%s',155);\n",type,"testing"); // for string grocery type
printf("%s",sql);
}
}


sqlite> create table store (grocery_type text, description text,price int);
sqlite> begin;
sqlite> .read data1.sql
sqlite> commit;
sqlite> .timer on
sqlite> create index grocery_type_index on store(grocery_type);
CPU Time: user 0.247962 sys 0.005999
sqlite> select count(*) from store where grocery_type='fruit';
2
CPU Time: user 0.002999 sys 0.00
sqlite> select count(*) from store where grocery_type='vegetable';
2
CPU Time: user 0.003000 sys 0.00
sqlite> select count(*) from store where grocery_type='meat';
2
CPU Time: user 0.003000 sys 0.00
sqlite> select count(*) from store where grocery_type='meat';
2
CPU Time: user 0.003000 sys 0.00
sqlite> select count(*) from store where grocery_type='sundrie';
2
CPU Time: user 0.002999 sys 0.00

Now do it with integer grocery_type
sqlite> create table store (grocery_type int, description text,price int);
sqlite> begin;
sqlite> .read data2.sql
sqlite> commit;
sqlite> .timer on
sqlite> create index grocery_type_index on store(grocery_type);
CPU Time: user 0.197970 sys 0.003000
sqlite> select count(*) from store where grocery_type='vegetable';
0
CPU Time: user 0.00 sys 0.00
sqlite> select count(*) from store where grocery_type=0;
2
CPU Time: user 0.002999 sys 0.00
sqlite> select count(*) from store where grocery_type=1;
2
CPU Time: user 0.003000 sys 0.00
sqlite> select count(*) from store where grocery_type=2;
2
CPU Time: user 0.002999 sys 0.00
sqlite> select count(*) from store where grocery_type=3;
2
CPU Time: user 0.003000 sys 0.00
sqlite> select count(*) from store where grocery_type=4;
2
CPU Time: user 0.003000 sys 0.00


Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Shorty [sho...@shortypen.com]
Sent: Tuesday, October 11, 2011 8:31 PM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Speed of sorting - diff between integer and string


I am building a database that is going to be used to produce a list of items
on my website.   The list is created depending on how the user wants the
list sorted. For example, lets say I am going to list groceries that are
available in my store.   Users will typically select the type of grocery
they want to see, like list all the fruits, or a list all vegatables.

Here is an example table:

COLUMNS:
grocery_type, description, price

ROWS:
fruit, apple, 1.23
fruit, banana, 5.35
vegetable, carrot,  1.55
vegetable, spinach, 6.85

-- HERE IS MY QUESTION: --
Is faster for the sqlite database to have the grocery_type as a string or
integer?
Or is the speed difference so small it doesn't matter?

Like instead of having "fruit" in the grocery_type column, I could have it
as a "1" type, vegetables would be "2", and then translate that just before
spitting out the table to my web page.

Thanks -
Shorty

___
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] Is it possible to optimize this query on a very largedatabase

[Igor Tandetnik]

Frank Chang  wrote:
> void cIntersectingGroupCache::AGGREGATEBLOBFunc(sqlite3_context *context, int 
> argc, sqlite3_value **argv){

How come you only show one function? A user-defined aggregate function is 
actually represented by two C[++] functions - one that is called for every row 
and performs actual aggregation, and another that's called at the end of each 
group, reports the result and resets the state machine to prepare for the next 
group. You can use sqlite3_context to store state between invocations - see 
sqlite3_aggregate_context.

In light of this, it's not clear why you need PreviousFieldName.

> switch( sqlite3_value_type(argv[0]) ){

Why would argv[0] be anything other than a blob? Are you storing different 
types of data in Vertices column?

> case SQLITE_INTEGER: {
> iVal = sqlite3_value_int64(argv[0]);
> iVal = ( iVal > 0) ? 1: ( iVal < 0 ) ? -1: 0;
> sqlite3_result_int64(context, iVal);

Again - you are not supposed to report the result until you've seen all rows in 
a group (at which point your xFinal callback is called).

> case SQLITE_BLOB: {
> size = sqlite3_value_bytes(argv[0]);
> ip2 = (int *)sqlite3_value_blob(argv[0]);
> for (int i = 0; i < size/sizeof(int); i++){
> ((cIntersectingGroupCache*)(sqlite3_value_int(argv[2])))->Column3.push_back(ip2[i]);
> }

Assuming Column3 is a vector (or a similar STL container), you can replace 
your loop with something like this:

vector& v = 
((cIntersectingGroupCache*)(sqlite3_value_int(argv[2])))->Column3;
v.insert(v.end(), ip2, ip2 + size/sizeof(int));

> sqlite3_result_blob(context,blob,((cIntersectingGroupCache*)(sqlite3_value_int(argv[2])))->Column3.size()*sizeof(int),NULL);

And again - you shouldn't report the result until the whole group is processed 
and xFinal is called.
-- 
Igor Tandetnik

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

[sqlite] create sqlite3_value * from scratch?

[Ivan Shmakov]

I wonder, is it possible to create sqlite3_value * from scratch
from within SQLite library's user code?

The idea is as follows.  The current dbtriv.c [1] code has a
db_triv_exec_bound () function, used roughly as follows:

   /* sqlite3 *db; */
   const char *sql
 = ("INSERT INTO foo (bar, baz)"
" VALUES ($1, $2);");
   int r
 = db_triv_exec_bound (db, sql,
   SQLITE_INTEGER, (sqlite3_int64)1,
   SQLITE3_TEXT,   -1, "qux",
   -1);
   assert (r == SQLITE_OK);

This function will look up a cached prepared statement for the
SQL code passed, creating and caching one if none's found, and
then bind the parameters with sqlite3_bind_int64 (),
sqlite3_bind_text (), etc., matching the type specifiers passed.

Given some way to construct a sqlite3_value wrapping object, I
could instead rely on sqlite3_bind_value () alone, thus
eliminating the necessity of type specifiers in the interface.

TIA.

[1] https://gitorious.org/e2dis/e2dis-devel/blobs/master/lib/dbtriv.c

-- 
FSF associate member #7257

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

Re: [sqlite] does sqlite work with Apple iCloud and IOS 5

[Alex Bowden]

Andy

Simon's answer is totally nonsense.  I suspect that he has misunderstood 
something that he's read.

The only thing that he is right about, is that neither of us can tell you 
anything about iOS 5 until the cloud API is public.

However, what I can tell you, which isn't about iOS 5, is this.

Apple uses sqlite for pretty much all iOS based data storage.  You can use 
other storage mechanisms, but sqlite is the default.

Most code that has been developed for iOS (rather than ported to iOS) does not 
use sqlite directly.  It uses core data which is a persistent object store for 
objective C objects.  The default choice for storing these persistent objects 
is to let core data store them in sqlite.

Core data really is an excellent facility which is tightly integrated with 
Objective C, Xcode, with iOS's User Interface facilities, iOS's undo stack, 
etc..  It is unlikely that Apple would walk away from that as a primary storage 
mechanism.

If you have an iPhone app, then you are probably a registered developer, in 
which case you can watch the video's from the recent WWDC.  Search for any with 
iCloud in their name, and watch them.

Otherwise, why not spend the time from now until release, finding out what core 
data will do.

Alex


On 11 Oct 2011, at 17:34, Simon Slavin wrote:

> 
> On 10 Oct 2011, at 9:55pm, Andy Davidson wrote:
> 
>> I have an iPhone app that uses a sqlite base . Apple's iCloud is very cool
>> It automatically syncs your changes back to the cloud and out to all your
>> other devices. Does anyone know  what I need to do to get sqlite to work
>> with Apple's iCloud.
> 
> There's really no point to integrating them.  They both do similar things.  
> Just one stores data in a file an your hard disk (or networked server) and 
> the other stores data at Apple's server farm.
> 
> I'm afraid I can't give details until the iCloud APIs go public, which is not 
> yet.  The same is true of everything to do with iOS 5.  But basically yes, 
> stuff works, roughly the way you'd expect it to.
> 
> It's worth bearing in mind that your device's communications with the cloud 
> might be going through a poor quality phone connection, at phone connection 
> speeds.  You do not want to store more than a few K in the cloud.  Thirty or 
> forty important locations for your GPS device ?  Fine.  Every place you've 
> ever stopped ?  No.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[sqlite] internal column header not case sensitive?

[Nelson, Bjorn]

I am having an issue where I build a query that creates columns based on case 
expressions.  The expressions are identical except for a case difference.  The 
result only shows data from the first expression duplicated in both columns.

The query that results in the issue:
select
  mycategory,
  count(case when type = 'typea' then 1 end) as 'test1',
  count(case when type = 'Typea' then 1 end) as 'test2'
from mytable
group by mycategory

Results in a table like this:
mycategory|test1|test2
cat1|500|500
cat2|350|350

But if I change it to:
select
  mycategory,
  count(case when type = 'typea' then 1 end) as 'test1',
  count(case when type = 'Typea' then 2 end) as 'test2'
from mytable
group by mycategory

Results in a table like this:
mycategory|test1|test2
cat1|500|200
cat2|350|150

It's as if, even though I specify an AS clause, it is representing the column 
header internally as the expression (ignoring the unique name I provided), and 
since columns are case insensitive then it just refers to the first column when 
printing it out.

Bjorn Nelson

--
NOTICE: Morgan Stanley is not acting as a municipal advisor and the opinions or 
views contained herein are not intended to be, and do not constitute, advice 
within the meaning of Section 975 of the Dodd-Frank Wall Street Reform and 
Consumer Protection Act. If you have received this communication in error, 
please destroy all electronic and paper copies and notify the sender 
immediately. Mistransmission is not intended to waive confidentiality or 
privilege. Morgan Stanley reserves the right, to the extent permitted under 
applicable law, to monitor electronic communications. This message is subject 
to terms available at the following link: 
http://www.morganstanley.com/disclaimers. If you cannot access these links, 
please notify us by reply message and we will send the contents to you. By 
messaging with Morgan Stanley you consent to the foregoing.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] DISTINCT vs. UNIQUE INDEX, and NOT EXISTS vs. EXCEPT

[Ivan Shmakov]

> Jim Morris writes:
> On 10/6/2011 10:43 PM, Ivan Shmakov wrote:
> Jim Morris writes:

[…]

 >>> INSERT INTO fts3_table (a,b,c)
 >>> SELECT 'an A','a B','a C'
 >>> WHERE NOT EXISTS
 >>> (SELECT DISTINCT a,b,c
 >>> FROM fts3_table
 >>> WHERE a='an A' AND b='a B' AND c='a C');

 >> However, I wonder, would the following (slightly more concise) query
 >> imply any performance loss in comparison to the one above?

 >> INSERT INTO fts3_table (a, b, c)
 >> SELECT 'an A', 'a B', 'a C'
 >> EXCEPT SELECT DISTINCT a, b, c
 >>FROM fts3_table;

 >> Also, I'm curious if DISTINCT may cause any performance loss in the
 >> case that the columns in question are constrained by an UNIQUE
 >> index?  Like:

 >> CREATE UNIQUE INDEX "foo-unique"
 >> ON "foo" (a, b, c);

 > I don't know for sure.  You would need to do some testing to
 > determine performance issues.  I wouldn't use distinct if the values
 > are already guaranteed to be unique

As per EXPLAIN, SELECT DISTINCT indeed doesn't take the
uniqueness constraint into consideration.

 > or in a not exits clause since it may have additional overhead.

Surprisingly enough, when used in the EXCEPT part of the query,
DISTINCT has no effect on the VM code (as shown by EXPLAIN)!

At a first glance, the code for the NOT EXISTS variant (sans
INSERT; hopefully it doesn't matter) seems simpler (one
operation less in total, one less Next, has no OpenEphemeral's,
and IdxGE instead of IdxInsert and IdxDelete):

addr  opcode p1p2p3p4 p5  comment  
  -        -  --  -
0 Trace  0 0 000   
1 Goto   0 21000   
2 OpenRead   0 2 1 1  00   
3 Rewind 0 19000   
4 Integer0 2 000   
5 Integer1 3 000   
6 OpenRead   2 3 0 keyinfo(1,BINARY)  00   
7 Column 0 0 400   
8 IsNull 4 14000   
9 SeekGe 2 144 1  00   
10IdxGE  2 144 1  01   
11Integer1 2 000   
12IfZero 3 14-1   00   
13Next   2 10000   
14Close  2 0 000   
15If 2 18100   
16Column 0 0 700   
17ResultRow  7 1 000   
18Next   0 4 001   
19Close  0 0 000   
20Halt   0 0 000   
21Transaction0 0 000   
22VerifyCookie   0 2 000   
23Transaction1 0 000   
24VerifyCookie   1 1 000   
25TableLock  1 2 0 temp.staged00   
26TableLock  0 2 0 foo00   
27Goto   0 2 000   

The code for the EXCEPT variant is:

addr  opcode p1p2p3p4 p5  comment  
  -        -  --  -
0 Trace  0 0 000   
1 OpenEphemeral  2 1 0 keyinfo(1,BINARY)  00   
2 Goto   0 22000   
3 OpenRead   1 2 1 1  00   
4 Rewind 1 9 000   
5 Column 1 0 100   
6 MakeRecord 1 1 200   
7 IdxInsert  2 2 000   
8 Next   1 5 001   
9 Close  1 0 000   
10OpenRead   0 2 0 2  00   
11Rewind 0 15000   
12Column 0 1 100   
13IdxDelete  2 1 100   
14Next   0 12001   

Re: [sqlite] FTS4: OFFSET Performance

[Fabian]

>
>
> > How would I optimize the above queries, to take advantage of the
> > LIMIT/OFFSET values, making them suitable for fast pagination?
>
> Are you sure the issue is with the fts table? Or is it the order by desc?
>
>
If I omit the FTS table in the query, like so:

SELECT table1.data1
FROM table1
WHERE table1.data1 = 10
ORDER BY table1.rowid DESC
LIMIT 250
OFFSET 0

The query always executes fast, independant of how large OFFSET is. The only
difference is that I don't include 'table2.data2' in the output, so that
leads me to think that the issue is related to FTS, or is there another
possibility? And in the FTS docs I read:

"Because of the way SQLite's virtual table interface works, retrieving the
value of the "title" column requires loading the entire row from disk
(including the "content" field, which may be quite large). This means that
if the users query matches several thousand documents, many megabytes of
"title" and "content" data may be loaded from disk into memory even though
they will never be used for any purpose."

Which seems to confirm that the reason the query is so slow, is because I
include the value of 'table2.data2' in the output?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FTS4: Use Cases

[Fabian]

I have three types columns in my table, which I want to move to a FTS4
table, but I'm not sure whether that would make sense.

1.) This column contains hash-values. I always need to lookup 100% exact
matches. Normally you would store them with 'TEXT INDEX' in a standard
table. Would there be any advantage moving this column to FTS? Or would it
perform worse than 'TEXT INDEX'?

2.) This column also contains hash-values, but I always need to look them up
by prefix (LIKE 'start%'), never by exact match. And the size of this prefix
is unknown in advantage, so I can't use FTS4 optimization for fixed-size
prefix searches. Is there any advantage over a traditional LIKE query? Since
I suspect FTS to only index full words, which would mean no advantage.

3.) This column contains a list of short tags seperated by spaces. For
example: 'v45 s12 h65', which I currently look up using 'LIKE '%tag%', to
search for a certain combination of tags. In a good database-design, these
tags would be stored in a second table, referencing the 'rowid' of the first
table. But every row can have many tags, and storing them in normalized
form, would make the INSERTS very slow, because I have to insert an extra
row for each tag, instead of just one row. Since my app does much more
inserts than actual selects, I choosed to store them into a single column.
Would there be any advantages storing these tags into a FTS column, so I can
do quick lookups? One concern I have is that they're not unique enough (just
like stopwords), eventually bloating the index. Or is the only right option
to create a second table with a TEXT INDEX column on these tags?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS4: OFFSET Performance

[Dan Kennedy]

On 10/12/2011 04:36 PM, Fabian wrote:

I have two tables, one normal table (table1), and one FTS4 table (table2).
The ROWID's between the tables are in sync. I often run queries on table1,
but every row returned must also include a value from table2 (data2), so I
create a query like this:

SELECT table1.data1, table2.data2
FROM table1 JOIN table2 ON table1.rowid=table2.rowid
WHERE table1.data1 = 10
ORDER BY table1.rowid DESC
LIMIT 250
OFFSET 0

This query returns the results as expected, and performs well. But as soon
as I raise the OFFSET to a large value (for pagination) the performance
drops drastically. After a long search through the documentation, I found
out the reason: the SELECT on table2.data2 causes FTS4 to load the entire
column from disk, and doesn't take into account the LIMIT/OFFSET clauses.


Can we see the output of EXPLAIN for this query?


I have a related query, where I do a MATCH on table2.data2,

SELECT table1.data1, table2.data2
FROM table2 JOIN table1 ON table2.rowid=table1.rowid
WHERE table2.data2 MATCH 'value' AND table1.data1 = 10
ORDER BY table1.rowid DESC
LIMIT 250
OFFSET 0


Are you using 3.7.7 or newer? And specifying the order=DESC
option when creating the FTS4 table? If not, it might be a big
help in this case.

  http://www.sqlite.org/fts3.html#fts4order

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

Re: [sqlite] load database into memory java applet

[Simon Slavin]

On 12 Oct 2011, at 7:35am, Orçun Ertuğrul wrote:

> I am trying to load sqlite database that is placed on the net from an
> applet. When trying to attach http address as source, it gives "Unable to
> open database file" error.

Sorry, but although the syntax of a URL allows you to specify 'http://', the 
driver in SQLite recognises only 'file://'.  If you have your shared filespace 
mapped to a folder then and can specify the file location as 
'file://' then it should work.

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

Re: [sqlite] FTS4: OFFSET Performance

[Petite Abeille]

On Oct 12, 2011, at 11:36 AM, Fabian wrote:

> How would I optimize the above queries, to take advantage of the
> LIMIT/OFFSET values, making them suitable for fast pagination?

Are you sure the issue is with the fts table? Or is it the order by desc?

In any case, you can easily refactor the query into two steps:

(1) First get your pagination
(2) Then join to your fts

selecttable1.data1, 
  table2.data2
from  (
selecttable1.data1
from  table1

where table1.data1 = 10

order by  table1.rowid desc
limit 250
offset0
  )
astable1 

join  table2 
ontable1.rowid = table2.rowid


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

[sqlite] FTS4: OFFSET Performance

[Fabian]

I have two tables, one normal table (table1), and one FTS4 table (table2).
The ROWID's between the tables are in sync. I often run queries on table1,
but every row returned must also include a value from table2 (data2), so I
create a query like this:

SELECT table1.data1, table2.data2
FROM table1 JOIN table2 ON table1.rowid=table2.rowid
WHERE table1.data1 = 10
ORDER BY table1.rowid DESC
LIMIT 250
OFFSET 0

This query returns the results as expected, and performs well. But as soon
as I raise the OFFSET to a large value (for pagination) the performance
drops drastically. After a long search through the documentation, I found
out the reason: the SELECT on table2.data2 causes FTS4 to load the entire
column from disk, and doesn't take into account the LIMIT/OFFSET clauses.

I have a related query, where I do a MATCH on table2.data2,

SELECT table1.data1, table2.data2
FROM table2 JOIN table1 ON table2.rowid=table1.rowid
WHERE table2.data2 MATCH 'value' AND table1.data1 = 10
ORDER BY table1.rowid DESC
LIMIT 250
OFFSET 0

Here the problem is even worse. When MATCH 'value' returns a lot of results,
the query is very slow (even with OFFSET 0) because it ignores the fact it
only has to return the first 250 results.

How would I optimize the above queries, to take advantage of the
LIMIT/OFFSET values, making them suitable for fast pagination?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it possible to optimize this query on a very large database

[Frank Chang]

  Igor Tandetnik,  I tried your suggestion 
 
insert or replace into BlobLastNameTest(rowid, FieldName, Vertices) 
select min(rowid), FieldName, AGGREGATEBLOB(Vertices,rowid,%d,\'%s\')
from BlobLastNameTest group by FieldName", 
 
(The AGGREGATEBLOB C++ User Defined Function is shown at bottom of this post)
 
but it only concatenates a maximum of two rowids into the  VERTICES BLOB for 
the MIN(ROWID). 
 
Perhaps, We need to break this into a several steps
 
1. CREATE TABLE TEMPRESULT(FieldName CHAR(25), PreviousFieldName CHAR(25). 
MINROWID INT);
 
2. INSERT INTO TEMPRESULTS SELECT .
 
 
3.  C++ code NOT SQL Code
sprintf(InsertStatement"insert or replace into BlobLastNameTest(rowid, 
FieldName, Vertices) 
select min(rowid), FieldName, 
AGGREGATEBLOB(X.Vertices,X.rowid,%d,\'%s\',X.FIELDNAME,Y.PREVIOUSFIELDNAME)
from BlobLastNameTest X, TEMPRESULTS Y WHERE X.FIELDNAME = Y.FIELDNAME group by 
X.FieldName", 
 
In this way when the FieldName changes , we could possibly refresh the Standard 
Template Library Vector Column3 which  stores the rowid's which are embedded in 
the VERTICES BLOB for each unique fieldname Thank you for your help.
 
 
void cIntersectingGroupCache::AGGREGATEBLOBFunc(sqlite3_context *context, int 
argc, sqlite3_value **argv){
char* TableName;
int size; 
int* ip2;
long long int iVal;
 
((cIntersectingGroupCache*)(sqlite3_value_int(argv[2])))->Column3.clear();
switch( sqlite3_value_type(argv[0]) ){
case SQLITE_INTEGER: {
iVal = sqlite3_value_int64(argv[0]);
iVal = ( iVal > 0) ? 1: ( iVal < 0 ) ? -1: 0;
sqlite3_result_int64(context, iVal);
break;
}
case SQLITE_NULL: {
size = sqlite3_value_bytes(argv[3]);
TableName = new char[size + 1];
memcpy(TableName, sqlite3_value_text(argv[3]),size);
TableName[size] = '\x0';
((cIntersectingGroupCache*)(sqlite3_value_int(argv[2])))->RowCountMap[TableName]
 += 1;
delete [] TableName;
break;
}
case SQLITE_BLOB: {
size = sqlite3_value_bytes(argv[0]);
ip2 = (int *)sqlite3_value_blob(argv[0]);
for (int i = 0; i < size/sizeof(int); i++){
((cIntersectingGroupCache*)(sqlite3_value_int(argv[2])))->Column3.push_back(ip2[i]);
}
break;
}
default: {
break;
}
}
switch( sqlite3_value_type(argv[1]) ){
case SQLITE_INTEGER: {
int iVal = sqlite3_value_int(argv[1]);
((cIntersectingGroupCache*)(sqlite3_value_int(argv[2])))->Column3.push_back(iVal);
char* blob = 
reinterpret_cast(&(((cIntersectingGroupCache*)(sqlite3_value_int(argv[2])))->Column3[0]));
sqlite3_result_blob(context,blob,((cIntersectingGroupCache*)(sqlite3_value_int(argv[2])))->Column3.size()*sizeof(int),NULL);
break;
}
default: {
break;
}
}
} 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Speed of sorting - diff between integer and string

[Petite Abeille]

On Oct 12, 2011, at 3:31 AM, Shorty wrote:

> Or is the speed difference so small it doesn't matter?

If you have a trivial amount of data, then it doesn't matter much either way. 
Anything goes when you have no data :)

But in general, do normalize your data and use foreign keys. There are numerous 
benefits in doing so. 

> Like instead of having "fruit" in the grocery_type column, I could have it as 
> a "1" type, vegetables would be "2", and then translate that just before 
> spitting out the table to my web page.

Yes, as pointed out by Simon, this is called normalization [1]. Which is a good 
thing to do, in the same way as designing a proper OO model is important when 
doing, well, OOP.

As far as performance goes, thing about normalization as a form of compression: 
for each discreet piece of information (e..g. grocery type) you assign a unique 
number to it. Then you always refer to that data using that unique number. So, 
say, instead of storing the literal 'vegetable' half-a-million time, you might 
just store its foreign key, say, 1. Same applies for any indices you might have 
on that column. Such "compression" add up quite quickly. You database engine 
will be grateful  :)

Tangentially related:

Double-thinking in SQL
http://explainextended.com/2009/07/12/double-thinking-in-sql/


[1] http://en.wikipedia.org/wiki/Database_normalization

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

Re: [sqlite] Prepare statement in separate function

[enjoythesun]

hello martin,

I was allocating memory because I wanted to prepare the statement 
in a separate function. After all I have changed the whole 
implementation design to something less awkward :)

greetings,
john

On Tue, 11 Oct 2011 11:46:01 +0200 Martin Engelschalk 
 wrote:
>Hello John,
>
>why do you malloc() your DB- and Statement handle?
>
>I  declare a
>
>sqlite3* pDB;
>sqlite3_stmt* pStmnt;
>
>then open the database with
>
>int nRet = sqlite3_open("MyDatabaseName", );
>
>and prepare a statement using
>
>nRet = sqlite3_prepare_v2(pDB, "insert .", -1, , 
>);
>
>no need to malloc (or free) anything, and passing pDB or pStmnt to 
>
>functions is easy.
>
>Martin
>
>
>Am 11.10.2011 11:27, schrieb enjoythe...@hushmail.com:
>> hello list,
>>
>> I have a question regarding prepared statements. I'm fairly new 
>to
>> sqlite3 and it's already pretty late, so if I'm overlooking
>> something obvious please forgive me :) I'm on Windows 7 x64 and
>> using sqlite-3070800 (amalgamation).
>>
>> I'm trying to prepare an insert statement, that I will reuse 
>many
>> times using bound parameters. In a short test application, if I
>> prepare the statement within the main function everything is 
>fine:
>>
>> CODE
>> ...
>> int rc=0;
>> pDb = (sqlite3*) malloc (sizeof(sqlite3*));
>> stmt = (sqlite3_stmt*) malloc (sizeof(sqlite3_stmt*));
>> ...
>>
>> if((rc=sqlite3_prepare_v2(pDb, INSERT_STMT , -1,, NULL)) !=
>> SQLITE_OK){
>>  /* ... error ... */
>> }
>>
>> sqlite3_step(stmt);
>> ...
>> \CODE
>>
>> However, if I try throw this code in a separate function like 
>this,
>>
>> CODE
>> int prepareStatement(sqlite3_stmt* stmt, sqlite3* pDb){
>>
>>  int rc=0;
>>
>>  if((rc=sqlite3_prepare_v2(pDb, INSERT_STMT , -1,, NULL)) 
>!=
>> SQLITE_OK){
>>  /* ... error ... */
>>  }
>>  return 0;
>> }
>> \CODE
>>
>> it fails as soon as I call sqlite3_step(stmt) afterwards. The
>> debugger stops at:
>>
>> sqlite3_mutex_enter(db->mutex);
>>
>> At first I thought I was doing smthg wrong during memory
>> allocation, but everything seems to be fine there. I'm sure I'm
>> overlooking an obvious mistake. Maybe somebody can give me a 
>hint!
>>
>> thanks,
>> John
>>
>> ___
>> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] load database into memory java applet

[Orçun Ertuğrul]

 I am trying to load sqlite database that is placed on the net from an
applet. When trying to attach http address as source, it gives "Unable to
open database file" error.


Class.forName("org.sqlite.JDBC");
Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:");
String attachStmt = "ATTACH " + "'
http://localhost:8080/appletOrcun/SertifikaDeposu.svt'" + " AS src";
Statement stt = conn.createStatement();
stt.execute(attachStmt);
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users