Re: [sqlite] No error on UPDATE setting duplicated value on UNIQUE column.

[Simon Slavin]

On 5 Oct 2010, at 9:40pm, Paweł Salawa wrote:

>> Maybe you should remove the "ON CONFLICT IGNORE" clause at the end of the
>> uniqueness constraint...
> 
> You're right, of course.
> Shame on me for missing it :( Shame on me!

We all make mistakes like that.  It's really useful to have someone else look 
at your code and ask "Hey, are you doing this for some wonderful reason I don't 
understand ?".

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

Re: [sqlite] No error on UPDATE setting duplicated value on UNIQUE column.

[Paweł Salawa]

> Maybe you should remove the "ON CONFLICT IGNORE" clause at the end of the
> uniqueness constraint...

You're right, of course.
Shame on me for missing it :( Shame on me!

-- 
Paweł Salawa
pawelsal...@gmail.com

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

Re: [sqlite] No error on UPDATE setting duplicated value on UNIQUE column.

[Jay A. Kreibich]

On Tue, Oct 05, 2010 at 09:56:52PM +0200, Pawe?? Salawa scratched on the wall:
> Hi,
> 
> My SQLite is 3.7.2.
> 
> I have a table like this:
> 
> CREATE TABLE [newsd] (
>   [id] INTEGER PRIMARY KEY AUTOINCREMENT,
>   [date] INTEGER NOT NULL, [title] TEXT NOT NULL,
>   [yhfgdfhd] NONE,
>   CONSTRAINT "fg" UNIQUE ([yhfgdfhd]) ON CONFLICT IGNORE
> )

> So I'm indeed unable to set duplicate on UNIQUE column, but SQLite doesn't 
> raise error, thus my application doesn't call ROLLBACK for that case.

  No error is raised because you told SQLite to ignore the error 
  with "ON CONFLICT IGNORE."  

  

  IGNORE

When an applicable constraint violation occurs, the IGNORE
resolution algorithm skips the one row that contains the
constraint violation and continues processing subsequent rows
of the SQL statement as if nothing went wrong. Other rows
before and after the row that contained the constraint
violation are inserted or updated normally. No error is
returned when the IGNORE conflict resolution algorithm is used.


  In specific, note the last sentence in that paragraph.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help registering custom tokenizer

[Travis Orr]

I did some more testing, and it is actually the code that register's the
tokenizer, not the code that creates the table that is seg faulting.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Travis Orr
Sent: October 5, 2010 9:34 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Help registering custom tokenizer

I am currently working on writing a custom tokenizer for use with a FTS3
indexed database. I believe I have written the new tokenizer module
correctly and am just missing something with registering the tokenizer
with the database.

 

To verify this I copied the simple_tokenizer1.c to a new file and
replaced all instances of 'simple' with 'unaccent'.

 

The following code was used to register the tokenizer.

 

sqlite3_stmt *pStmt;

const char *zSql = "SELECT fts3_tokenizer(?, ?)";

 

rc = sqlite3_prepare_v2(ldb_con, zSql, -1, , 0);

if( rc!=SQLITE_OK ){

return rc;

}

 

rc = sqlite3_bind_text(pStmt, 1, "unaccent", -1,
SQLITE_STATIC);

rc = sqlite3_bind_blob(pStmt, 2, , sizeof(p),
SQLITE_STATIC);

rc = sqlite3_step(pStmt);

 

rc = sqlite3_finalize(pStmt);

 

SQLITE_OK is the final rc.

 

When I try to create a table using the new tokenizer I get a segfault in
sqlite3Fts3InitTokenizer(). The code to create the table is:

 

query = "CREATE VIRTUAL TABLE testTable USING fts3
(tokenize='unaccent');";

rc = db_run_query_on_con (ldb_con, query, LOCAL_DB_ROOT,
false);

 

Am I missing something obvious?

 

Thanks,

 

Travis Orr

 

IVL Audio Inc.

#3-6703 Rajpur Place

Victoria BC

V8M 1Z5

 

___
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] No error on UPDATE setting duplicated value on UNIQUE column.

[Richard Hipp]

On Tue, Oct 5, 2010 at 3:56 PM, Paweł Salawa  wrote:

> Hi,
>
> My SQLite is 3.7.2.
>
> I have a table like this:
>
> CREATE TABLE [newsd] (
>  [id] INTEGER PRIMARY KEY AUTOINCREMENT,
>  [date] INTEGER NOT NULL, [title] TEXT NOT NULL,
>  [yhfgdfhd] NONE,
>  CONSTRAINT "fg" UNIQUE ([yhfgdfhd]) ON CONFLICT IGNORE
> )
>
> so column [yhfgdfhd] is UNIQUE, and [title] is NOT NULL.
> Now follow the log of 'sqlite3' calls:
>
> $ sqlite3 "data.db"
> SQLite version 3.7.2
> Enter ".help" for instructions
> sqlite> select * from newsd where ROWID = 16;
> 16|12.2.12||e
> sqlite> select * from newsd where ROWID = 21;
> 21|||x
> sqlite> UPDATE [newsd] SET [yhfgdfhd] = 'e' WHERE ROWID = 21;
> sqlite> select * from newsd where ROWID = 21;
> 21|||x
> sqlite> UPDATE [newsd] SET [title] = NULL WHERE ROWID = 21;
> SQL error: newsd.title may not be NULL
>
> So I'm indeed unable to set duplicate on UNIQUE column, but SQLite doesn't
> raise error, thus my application doesn't call ROLLBACK for that case.
>
> Last call at the end is to ensure that other constraint violation raises
> error
> correctly.
>

Maybe you should remove the "ON CONFLICT IGNORE" clause at the end of the
uniqueness constraint...



>
> Same behaviour takes place for tclsqlite extension.
>
> Regards,
> --
> Paweł Salawa
> pawelsal...@gmail.com
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

[sqlite] No error on UPDATE setting duplicated value on UNIQUE column.

[Paweł Salawa]

Hi,

My SQLite is 3.7.2.

I have a table like this:

CREATE TABLE [newsd] (
  [id] INTEGER PRIMARY KEY AUTOINCREMENT,
  [date] INTEGER NOT NULL, [title] TEXT NOT NULL,
  [yhfgdfhd] NONE,
  CONSTRAINT "fg" UNIQUE ([yhfgdfhd]) ON CONFLICT IGNORE
)

so column [yhfgdfhd] is UNIQUE, and [title] is NOT NULL.
Now follow the log of 'sqlite3' calls:

$ sqlite3 "data.db"
SQLite version 3.7.2
Enter ".help" for instructions
sqlite> select * from newsd where ROWID = 16;
16|12.2.12||e
sqlite> select * from newsd where ROWID = 21;
21|||x
sqlite> UPDATE [newsd] SET [yhfgdfhd] = 'e' WHERE ROWID = 21;
sqlite> select * from newsd where ROWID = 21;
21|||x
sqlite> UPDATE [newsd] SET [title] = NULL WHERE ROWID = 21;
SQL error: newsd.title may not be NULL

So I'm indeed unable to set duplicate on UNIQUE column, but SQLite doesn't 
raise error, thus my application doesn't call ROLLBACK for that case.

Last call at the end is to ensure that other constraint violation raises error 
correctly.

Same behaviour takes place for tclsqlite extension.

Regards,
-- 
Paweł Salawa
pawelsal...@gmail.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] errors after sqlite3_step

[Dave Dyer]

Analysis and possible solution to getting unexpected SQLITE_CANTOPEN
errors from sqlite3_step


In pagerSharedLock, there is a test for the journal file
acessability immediatly followed by an attempt to open it.

If the journal file goes away between these two actions, sqlite3OsOpen
will return SQLITE_CANTOPEN which becomes an irreversable error.  On the
other hand, if the journal file had just been missing according to 
sqlite3OsAccess,  that would have caused a SQLITE_BUSY which is 
retryable.

I propose treating CANTOPEN the same as BUSY

   rc = sqlite3OsAccess(pVfs,pPager->zJournal,SQLITE_ACCESS_EXISTS,);
if( rc==SQLITE_OK ){
  if( res ){
int fout = 0;
int f = SQLITE_OPEN_READWRITE|SQLITE_OPEN_MAIN_JOURNAL;
assert( !pPager->tempFile );
rc = sqlite3OsOpen(pVfs, pPager->zJournal, pPager->jfd, f, );

...
if(rc==SQLITE_CANTOPEN) { rc = SQLITE_BUSY; }


There's also the harmless inefficiency that sqlite3OsOpen can return a 
readonly file even though it was requested to provide a read/write file.
pagerSharedLock closes it and treats this as CANTOPEN.  Both of these
are conditions where the state of the file system is changing unexpectededly,
but it seems like the right thing to go with what is true rather than what
was predicted by sqlite3OsAccess.

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

Re: [sqlite] When do I need SQLITE_THREADSAFE?

[Jay A. Kreibich]

On Tue, Oct 05, 2010 at 12:44:59PM +0200, Zaher Dirkey scratched on the wall:
> On Tue, Oct 5, 2010 at 4:55 AM, Jay A. Kreibich  wrote:
> 
> > On Mon, Oct 04, 2010 at 07:25:05PM -0700, Dustin Sallings scratched on the
> > wall:
> >
> >
> >   The main difference between =1 and =2 is that =2 assumes you more or
> >  less know what you're doing and will either lock a database handle as
> >  you pass it between threads or you'll keep it private to a thread.
> >
> >  =1 is designed to be more or less idiot proof, and will simply not
> >  let you do something stupid with a database handle, like have two
> >  threads try to execute two different statements at the same time.
> >
>
> What if two therad make insert or update then commit, what is happen to the
> second thread after the first made commit?.

  The second thread will block if it attempts to use a database
  connection that the first thread is already using.  Once the first
  thread finishes (either with an explicit COMMIT/ROLLBACK, or because
  all auto-commit transactions go out of scope) then the first thread
  will release the database connection and the second thread will wake
  up and allowed to proceed.  That's why the =1 mode is called "serial"...
  it automatically serializes the database statements.

  At least, I'm pretty sure that's how it works.  I generally avoid
  threaded code, and when I do use it, I tend to use thread-specific
  resources that are carefully locked and guarded.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Help registering custom tokenizer

[Travis Orr]

I am currently working on writing a custom tokenizer for use with a FTS3
indexed database. I believe I have written the new tokenizer module
correctly and am just missing something with registering the tokenizer
with the database.

 

To verify this I copied the simple_tokenizer1.c to a new file and
replaced all instances of 'simple' with 'unaccent'.

 

The following code was used to register the tokenizer.

 

sqlite3_stmt *pStmt;

const char *zSql = "SELECT fts3_tokenizer(?, ?)";

 

rc = sqlite3_prepare_v2(ldb_con, zSql, -1, , 0);

if( rc!=SQLITE_OK ){

return rc;

}

 

rc = sqlite3_bind_text(pStmt, 1, "unaccent", -1,
SQLITE_STATIC);

rc = sqlite3_bind_blob(pStmt, 2, , sizeof(p),
SQLITE_STATIC);

rc = sqlite3_step(pStmt);

 

rc = sqlite3_finalize(pStmt);

 

SQLITE_OK is the final rc.

 

When I try to create a table using the new tokenizer I get a segfault in
sqlite3Fts3InitTokenizer(). The code to create the table is:

 

query = "CREATE VIRTUAL TABLE testTable USING fts3
(tokenize='unaccent');";

rc = db_run_query_on_con (ldb_con, query, LOCAL_DB_ROOT,
false);

 

Am I missing something obvious?

 

Thanks,

 

Travis Orr

 

IVL Audio Inc.

#3-6703 Rajpur Place

Victoria BC

V8M 1Z5

 

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

Re: [sqlite] Inserting images (gif) in Blob field (with php)

[Artur Reilin]

> Using base64 will allow you to take binary data (arbitrary 1s and 0s) and
> store it in a text field.  However, SQLite has BLOB fields, which can be
> used to store data in its original binary form, and will indeed take up
> fewer bytes than encoding it in base64.
>
> So you need to look at the PHP functions for SQLite which mention BLOB
> fields, or do a relevant Google search.  They're a little more complicated
> to use than other fields because they can be so long, but they work fine.
>
> If you want more details you need to tell us whether you're using the PDO
> interface to SQLite or the SQLite3 interface directly.  If you're not
> sure, just give us the URL of the page you're using for reference.
>
> Simon.

I forgot to write, that i used a blob field, but the problem was with the
query not seem to work right.

And your question about the way i used it: I use $db = new SQLite3. I
think that is the interface then, right? Just with oop.

But see my answer under the other quote.

> Why are you using addslashes?  This sounds like you're trying to embed
> the whole thing as an SQL literal.  Don't do that.  Use a parameter in
> the SQL, then bind it with type SQLITE3_BLOB, ideally using a column
> with NONE affinity (e.g., of type BLOB NULL) underneath.
>
> (The addslashes function is often a red flag in the general case.)
>
> See http://us2.php.net/manual/en/sqlite3stmt.bindvalue.php if you
> haven't already.
>
>---> Drake Wilson

That addslashes was just in the code. I don't get the idea, why the
creator of the code example used it. Perhaps it was more compatible with
mysql then.

Anyways, the way you gave me worked like charm. I got all the images (386
as number) in the database.

That was a significant file change from 2.87 mb to 1.82 mb. Like I
expected it.

I just don't get it back then, then i saw about that bindvalue thing,
what's the idea behind this. I never run in any problems. So, now I have
an idea why this is good.

That's how it worked for me:

$prepare_image = $db->prepare('insert or replace into thumbs
(image_name,image_datetime,image_data)
values("'.$filename.'","'.$filedate.'",:fileblob)');

$prepare_image->bindValue(':fileblob',$image,SQLITE3_BLOB);

$prepare_image->execute();

I used that for a foreach loop to get the whole folder in database.

So thanks to you both for the information. Now we have an image example
input in the archives. ;)

With best wishes.

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

Re: [sqlite] Slow operation

[Ian Hardingham]

Hey Jay, thanks for your feedback.

I am indeed using (several) delineated lists.  I would very much 
appreciate your input into how bad a decision this is.

So, I basically need to find entrys of Table B that do not appear in 
that list.  Obviously, it would be better to have a playedInf table and 
do some kind of SELECT FROM B NOT IN A query.

However, I would rather not do the processing at all.  I'm pushing off 
the calculation to the client - and the client does not use a database 
at all. I simply send the list as a text field and get the client to 
sort it out, as he has plenty of processor time.  It seems to me that 
this is quicker than making the list on the fly by doing a SELECT and 
then a concatination from another table...

Am I completely off-base on this?

Thanks,
Ian




>
>
>
>   That column name makes me suspicious, however... I have a bad feeling
>   that's a text field that is made up of delineated values,
>   essentially forming a list.  If true, that's a very poor design.
>
>   

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

Re: [sqlite] Slow operation

[Jay A. Kreibich]

On Tue, Oct 05, 2010 at 04:08:41AM -0700, Drake Wilson scratched on the wall:
> Quoth Ian Hardingham , on 2010-10-05 11:52:36 +0100:

> > I'm running this code from a scripting language bound to SQLite:
> > 
> >  %r = db.query("SELECT * FROM userTable", 0);
> >  %i = 0;
> > 
> >  db.query("BEGIN TRANSACTION", 0);
> >  while (%i < db.numRows(%r))
> >  {
> >  %username = db.getColumn(%r, name);
> >  db.query("UPDATE userTable SET playedInfIds='' WHERE name LIKE 
> > '?'", 0, %username);
> >  %i ++;
> >  }
> >  db.query("END TRANSACTION", 0);

> A number of points come to mind fairly immediately:
> 
>   - Don't keep a query from outside a transaction active inside it.

  While this is good advice, I don't think that's what is going on
  here.  The SELECT should definitely be inside the BEGIN/END, because
  otherwise the data may change between the SELECT and UPDATE loop, but 
  that's a different issue.  If I understand this scripting API, the
  query() function will run the SELECT to completion, so any auto-commit
  transaction that is open will be shut again by the time the code
  enters the loop.

  Not that it matters... If the SELECT did open an auto-commit
  transaction that was not closed, a transaction would already be open
  and the BEGIN/END wouldn't change that.  The whole collection of
  statements would be run inside the auto-commit transaction until
  all the statements were complete.

>   - This whole loop looks like it could be replaced with the single
> query ? UPDATE userTable SET playedInfIds = '' ? because you're
> just targeting all the rows, unless there's something unobviously
> different that I've missed.

  Yes... usually the mistake is to forget the WHERE, but in this case
  it looks like the OP actually wants to change every row in the table,
  and that can be done with a single UPDATE statement just drop the
  WHERE all together.


  That column name makes me suspicious, however... I have a bad feeling
  that's a text field that is made up of delineated values,
  essentially forming a list.  If true, that's a very poor design.

> Right now you're doing a full table scan to get each name, then doing
> another full table scan for each name to update each row with a
> similar name.  That's O(N^2) in the number of rows; with 3k rows, that
> requires ~9M processing steps.

  It isn't that bad.  He's looking up a PRIMARY KEY without wildcards
  (I assume), so each look-up is O(logN), making the total O(NlogN).
  It is a bloated O(logN) because you're depending on the LIKE
  optimizations, but it is still technically O(logN).  Not good for
  the desired results, which should more or less be O(N), but not
  nearly as bad as O(NN).

> Also, PRIMARY KEY UNIQUE is redundant.  A primary key is always
> unique.

  True, but it does no harm.  SQLite is smart enough to only create one
  index.  The NOT NULL would be redundant in most database systems as
  well, but SQLite requires it for true PK behavior.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inserting images (gif) in Blob field (with php)

[Drake Wilson]

Quoth Artur Reilin , on 2010-10-05 15:06:57 +0200:
> What I'm currently using is get the file contents and then use
> base64_encode to store them in database. But that just blows the size up.
> I saw that the firefox database (places.sqlite) store the favicons
> directly in database. I searched around and cannot found a doc how they do
> this.
> 
> Some days ago I found the following code:
> 
> $image_handle = fopen($file,"rb");
> $image = addslashes(fread($image_handle,filesize($file)));

Why are you using addslashes?  This sounds like you're trying to embed
the whole thing as an SQL literal.  Don't do that.  Use a parameter in
the SQL, then bind it with type SQLITE3_BLOB, ideally using a column
with NONE affinity (e.g., of type BLOB NULL) underneath.

(The addslashes function is often a red flag in the general case.)

See http://us2.php.net/manual/en/sqlite3stmt.bindvalue.php if you
haven't already.

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

Re: [sqlite] Inserting images (gif) in Blob field (with php)

[Simon Slavin]

On 5 Oct 2010, at 2:06pm, Artur Reilin wrote:

> I searched quiet around the internet and cannot found an solution.
> 
> What I'm currently using is get the file contents and then use
> base64_encode to store them in database. But that just blows the size up.
> I saw that the firefox database (places.sqlite) store the favicons
> directly in database. I searched around and cannot found a doc how they do
> this.

Using base64 will allow you to take binary data (arbitrary 1s and 0s) and store 
it in a text field.  However, SQLite has BLOB fields, which can be used to 
store data in its original binary form, and will indeed take up fewer bytes 
than encoding it in base64.

So you need to look at the PHP functions for SQLite which mention BLOB fields, 
or do a relevant Google search.  They're a little more complicated to use than 
other fields because they can be so long, but they work fine.

If you want more details you need to tell us whether you're using the PDO 
interface to SQLite or the SQLite3 interface directly.  If you're not sure, 
just give us the URL of the page you're using for reference.

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

[sqlite] Inserting images (gif) in Blob field (with php)

[Artur Reilin]

I searched quiet around the internet and cannot found an solution.

What I'm currently using is get the file contents and then use
base64_encode to store them in database. But that just blows the size up.
I saw that the firefox database (places.sqlite) store the favicons
directly in database. I searched around and cannot found a doc how they do
this.

Some days ago I found the following code:

$image_handle = fopen($file,"rb");
$image = addslashes(fread($image_handle,filesize($file)));

but this doesn't work, because I get the "unrecognized token" error at the
very first line of the image.

My current code:

$image = base64_encode(file_get_contents($file));

makes it bigger than I need it. I'm trying to get a smaller filesize than
a program who use one file per folder for thumbnails.

And with no search function on the user list, it's a pain to go through
all the months. (nothing found anyways)

With best wishes

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

Re: [sqlite] Slow operation

[Ian Hardingham]

  Thanks again Drake, I'll investigate those alternatives.

On 05/10/2010 13:52, Drake Wilson wrote:
> Quoth Ian Hardingham, on 2010-10-05 12:27:38 +0100:
>> CREATE TABLE IF NOT EXISTS userTable (name TEXT PRIMARY KEY NOT NULL
>> UNIQUE, password TEXT NOT NULL, email TEXT, key TEXT, status TEXT, date
>> TEXT, playedFor INTEGER, totalScore FLOAT DEFAULT 0, totalRecord TEXT
>> DEFAULT '0\t0', dailyScore FLOAT DEFAULT 0, dailyRecord TEXT DEFAULT
>> '0\t0', dailyGameRecord TEXT DEFAULT '', dailyGamesPlayed INTEGER
>> DEFAULT 0, scoreStreak TEXT DEFAULT '', scoreStreakNumber INT DEFAULT 0,
>> noEmail INT DEFAULT 0, playedInfIds TEXT DEFAULT '')
> Those *Record fields look like the sort of thing that will expand to
> include large blobs later on.  If this is the case, possibly consider:
>
>- Moving the blobs into a separate table or tables, if they're
>  really best represented as singular blobs.  Frequently updating
>  large blobs isn't going to give you very good performance, but
>  keeping them in separate tables will help prevent them from
>  impacting smaller updates.
>
>- Using separate tables and then actually storing the data in
>  relational form, if it's suitably representable.  This could
>  result in much more efficient storage and access, because you'd be
>  using the SQLite components in a more natural way.  The presence
>  of that \t suggests that you might be storing sequences of records
>  in those fields to start with; those could well be separate rows
>  in a suitable secondary table.
>
> --->  Drake Wilson
> ___
> 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] Slow operation

[Drake Wilson]

Quoth Ian Hardingham , on 2010-10-05 12:27:38 +0100:
> CREATE TABLE IF NOT EXISTS userTable (name TEXT PRIMARY KEY NOT NULL 
> UNIQUE, password TEXT NOT NULL, email TEXT, key TEXT, status TEXT, date 
> TEXT, playedFor INTEGER, totalScore FLOAT DEFAULT 0, totalRecord TEXT 
> DEFAULT '0\t0', dailyScore FLOAT DEFAULT 0, dailyRecord TEXT DEFAULT 
> '0\t0', dailyGameRecord TEXT DEFAULT '', dailyGamesPlayed INTEGER 
> DEFAULT 0, scoreStreak TEXT DEFAULT '', scoreStreakNumber INT DEFAULT 0, 
> noEmail INT DEFAULT 0, playedInfIds TEXT DEFAULT '')

Those *Record fields look like the sort of thing that will expand to
include large blobs later on.  If this is the case, possibly consider:

  - Moving the blobs into a separate table or tables, if they're
really best represented as singular blobs.  Frequently updating
large blobs isn't going to give you very good performance, but
keeping them in separate tables will help prevent them from
impacting smaller updates.

  - Using separate tables and then actually storing the data in
relational form, if it's suitably representable.  This could
result in much more efficient storage and access, because you'd be
using the SQLite components in a more natural way.  The presence
of that \t suggests that you might be storing sequences of records
in those fields to start with; those could well be separate rows
in a suitable secondary table.

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

Re: [sqlite] disk IO error after windows resumes from sleep

[Black, Michael (IS)]

I duplicated your problem.  Without the "FIX" defined this behaves badly after 
wakeup.
But just adding the retry fixed it on my testing.
 
#include 
#include 
#include 
#include "sqlite3.h"
int main()
{
 sqlite3 *db;
 int rc;
 remove("l:\test.db");
 rc=sqlite3_open("l:/mike/test.db",);
 if (rc != SQLITE_OK) {
  puts(sqlite3_errmsg(db));
 }
 sqlite3_exec(db,"create table t (i integer);",NULL,NULL,NULL);
 while(1) {
  rc=sqlite3_exec(db,"insert into t values(1);",NULL,NULL,NULL);
  if (rc != SQLITE_OK) {
   puts(sqlite3_errmsg(db));
#ifdef FIX
   while(rc != SQLITE_OK) {
sqlite3_close(db);
rc=sqlite3_open("l:/mike/test.db",);
if (rc != SQLITE_OK) {
puts(sqlite3_errmsg(db));
}
Sleep(1000);
   }
#endif
  }
  Sleep(1000);
  puts("1"); 
 }
 sqlite3_close(db);
}
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Serena Lien
Sent: Tue 10/5/2010 7:25 AM
To: General Discussion of SQLite Database
Subject: EXTERNAL:Re: [sqlite] disk IO error after windows resumes from sleep



Closing the handle before going to sleep sounds like a really sensible thing
to do which I hadn't heard of before, I will definitely try that!

Thanks for the quick responses from you all,
Serena.


On Tue, Oct 5, 2010 at 1:16 PM, Black, Michael (IS)
wrote:

> Could this also be because you never closed the database handle?  So Sqlite
> thinks it's still open?  First time you get an errror do an sqlite3_close()
> on the old handle.  That may solve your problem.
>
> Next thing, you should register your app to receive the  PBT_APMSUSPEND <
> http://msdn.microsoft.com/en-us/library/aa372721(v=VS.85).aspx>  event so
> you can close the db handle before the system goes to sleep (you have two
> seconds to do so -- you may need SetThreadExecutionState if you need
> longer).
>
> Other option would be to only open the database when you need to and use
> the SetThreadExecutionstate while you have it open.  That will keep the
> system from sleeping while it's busy.
>
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
>
>
> 
>
> From: sqlite-users-boun...@sqlite.org on behalf of Drake Wilson
> Sent: Tue 10/5/2010 5:59 AM
> To: General Discussion of SQLite Database
> Subject: EXTERNAL:Re: [sqlite] disk IO error after windows resumes from
> sleep
>
>
>
> Quoth Serena Lien , on 2010-10-05 11:46:18 +0100:
> > On a windows vista/win7 machine or a laptop which goes into sleep mode,
> when
> > it resumes and the application tries to open a database on a networked
> > drive, the open function returns SQLITE_CANTOPEN and SQLITE_IOERR. I
> don't
> > have a problem with this, if the OS has lost access to the network I can
> > imagine SQLITE_IOERR is quite valid. My question is, is there any way to
> > recover now from this error without forcing my application to exit and
> > restart? Any number of retries using sqlite3_open_v2 always continue to
> fail
> > with SQLITE_IOERR.
> >
> > It is possible the response will be "not sqlite's problem", but I would
> > appreciate any advice anyone has to give,
>
> I would say that unless SQLite is returning that error in unwarranted
> cases, this is really an application-level error recovery problem.
> What do you mean by "always continue to fail"?  Is this the case even
> after you have verified that the desired file is accessible?  Are you
> delaying retries at all?
>
> If the IOERR return code is truthfully signaling inability to access
> the file, then if this is an interactive application, you might signal
> the user to request a retry later.  If it's a batch process, you might
> schedule a retry for later.  If there's some alternative way of
> accessing the database or operating at reduced functionality without
> it, you might try that.  It's hard to be more specific without knowing
> what kind of application is being developed.
>
>   ---> Drake Wilson
> ___
> 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


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

Re: [sqlite] query performance question

[Simon Slavin]

On 5 Oct 2010, at 1:14pm, Igor Tandetnik wrote:

> Simon Slavin  wrote:
>> On 5 Oct 2010, at 10:43am, Mail wrote:
>> 
>>> SELECT COUNT(DISTINCT t0.Z_PK) FROM ZFFFOOD t0 WHERE (t0.ZFOODGROUP = ? AND 
>>> (SELECT COUNT(*) FROM ZFFWEIGHT t1 WHERE (t0.Z_PK =
>>> t1.ZFOOD) ) > ?) 
>> 
>> Did you miss out 'AS' from your FROM clause between ZFFFOOD and t0 ?  And 
>> again in the sub-SELECT ?
> 
> AS keyword is always optional.

I just noticed that on the syntax diagram.  Here's me trying to make my code 
understandable but SQL doesn't care.  Bah.

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

Re: [sqlite] disk IO error after windows resumes from sleep

[Simon Slavin]

On 5 Oct 2010, at 1:11pm, Serena Lien wrote:

> Yes - that is indeed what I am doing (your 3rd scenario) - but I have tried
> to close the connection first (and ignore any errors closing it) before
> retrying to open. As you say, the database object I am trying to close may
> not be valid so I am not able to close it first, and therefore unable to
> open again. Terminating the application obviously allows it to close, so
> that restarting my application now successfully opens it.

... and triggers SQLite's routines which spot the journal file and restore the 
database to a sane state.

> So my question is, is there any sqlite function I can call so that the
> connection can be successfully closed?

So you are doing this:

start application
sqlite3_open_v2()
sleep
wakeup
// Oh, my open connection is now useless, I need one that works !
sqlite3_close()
sqlite3_open_v2()

And I would guess that your _close() refuses to function properly, because its 
file handle is now useless.  Unfortunately, the _close() call can only assume 
that the OS is still in touch with all its files.  A programmer has to believe 
that the OS is sane.  And there's no 'close all connections without 
complaining' command.

I can't think of a way to satisfy this inside SQLite, unless you want to have 
your database open only while you are doing database ops, which will slow 
things down a bit.  In Mac programming an application can ask for notification 
when the computer is about to sleep and another immediately after the computer 
wakes up again.  You would do that, and put your '_close()' command inside the 
routine it calls.  I don't know what OS you're using but it might have a 
similar function: the ability to provide a service routine that's called before 
the computer sleeps.

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

Re: [sqlite] disk IO error after windows resumes from sleep

[Serena Lien]

Closing the handle before going to sleep sounds like a really sensible thing
to do which I hadn't heard of before, I will definitely try that!

Thanks for the quick responses from you all,
Serena.


On Tue, Oct 5, 2010 at 1:16 PM, Black, Michael (IS)
wrote:

> Could this also be because you never closed the database handle?  So Sqlite
> thinks it's still open?  First time you get an errror do an sqlite3_close()
> on the old handle.  That may solve your problem.
>
> Next thing, you should register your app to receive the  PBT_APMSUSPEND <
> http://msdn.microsoft.com/en-us/library/aa372721(v=VS.85).aspx>  event so
> you can close the db handle before the system goes to sleep (you have two
> seconds to do so -- you may need SetThreadExecutionState if you need
> longer).
>
> Other option would be to only open the database when you need to and use
> the SetThreadExecutionstate while you have it open.  That will keep the
> system from sleeping while it's busy.
>
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
>
>
> 
>
> From: sqlite-users-boun...@sqlite.org on behalf of Drake Wilson
> Sent: Tue 10/5/2010 5:59 AM
> To: General Discussion of SQLite Database
> Subject: EXTERNAL:Re: [sqlite] disk IO error after windows resumes from
> sleep
>
>
>
> Quoth Serena Lien , on 2010-10-05 11:46:18 +0100:
> > On a windows vista/win7 machine or a laptop which goes into sleep mode,
> when
> > it resumes and the application tries to open a database on a networked
> > drive, the open function returns SQLITE_CANTOPEN and SQLITE_IOERR. I
> don't
> > have a problem with this, if the OS has lost access to the network I can
> > imagine SQLITE_IOERR is quite valid. My question is, is there any way to
> > recover now from this error without forcing my application to exit and
> > restart? Any number of retries using sqlite3_open_v2 always continue to
> fail
> > with SQLITE_IOERR.
> >
> > It is possible the response will be "not sqlite's problem", but I would
> > appreciate any advice anyone has to give,
>
> I would say that unless SQLite is returning that error in unwarranted
> cases, this is really an application-level error recovery problem.
> What do you mean by "always continue to fail"?  Is this the case even
> after you have verified that the desired file is accessible?  Are you
> delaying retries at all?
>
> If the IOERR return code is truthfully signaling inability to access
> the file, then if this is an interactive application, you might signal
> the user to request a retry later.  If it's a batch process, you might
> schedule a retry for later.  If there's some alternative way of
> accessing the database or operating at reduced functionality without
> it, you might try that.  It's hard to be more specific without knowing
> what kind of application is being developed.
>
>   ---> Drake Wilson
> ___
> 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] disk IO error after windows resumes from sleep

[Black, Michael (IS)]

Could this also be because you never closed the database handle?  So Sqlite 
thinks it's still open?  First time you get an errror do an sqlite3_close() on 
the old handle.  That may solve your problem.
 
Next thing, you should register your app to receive the  PBT_APMSUSPEND 
  event so you 
can close the db handle before the system goes to sleep (you have two seconds 
to do so -- you may need SetThreadExecutionState if you need longer).
 
Other option would be to only open the database when you need to and use the 
SetThreadExecutionstate while you have it open.  That will keep the system from 
sleeping while it's busy.
 
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Drake Wilson
Sent: Tue 10/5/2010 5:59 AM
To: General Discussion of SQLite Database
Subject: EXTERNAL:Re: [sqlite] disk IO error after windows resumes from sleep



Quoth Serena Lien , on 2010-10-05 11:46:18 +0100:
> On a windows vista/win7 machine or a laptop which goes into sleep mode, when
> it resumes and the application tries to open a database on a networked
> drive, the open function returns SQLITE_CANTOPEN and SQLITE_IOERR. I don't
> have a problem with this, if the OS has lost access to the network I can
> imagine SQLITE_IOERR is quite valid. My question is, is there any way to
> recover now from this error without forcing my application to exit and
> restart? Any number of retries using sqlite3_open_v2 always continue to fail
> with SQLITE_IOERR.
>
> It is possible the response will be "not sqlite's problem", but I would
> appreciate any advice anyone has to give,

I would say that unless SQLite is returning that error in unwarranted
cases, this is really an application-level error recovery problem.
What do you mean by "always continue to fail"?  Is this the case even
after you have verified that the desired file is accessible?  Are you
delaying retries at all?

If the IOERR return code is truthfully signaling inability to access
the file, then if this is an interactive application, you might signal
the user to request a retry later.  If it's a batch process, you might
schedule a retry for later.  If there's some alternative way of
accessing the database or operating at reduced functionality without
it, you might try that.  It's hard to be more specific without knowing
what kind of application is being developed.

   ---> Drake Wilson
___
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] query performance question

[Igor Tandetnik]

Simon Slavin  wrote:
> On 5 Oct 2010, at 10:43am, Mail wrote:
> 
>> SELECT COUNT(DISTINCT t0.Z_PK) FROM ZFFFOOD t0 WHERE (t0.ZFOODGROUP = ? AND 
>> (SELECT COUNT(*) FROM ZFFWEIGHT t1 WHERE (t0.Z_PK =
>> t1.ZFOOD) ) > ?) 
> 
> Did you miss out 'AS' from your FROM clause between ZFFFOOD and t0 ?  And 
> again in the sub-SELECT ?

AS keyword is always optional.
-- 
Igor Tandetnik

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

Re: [sqlite] query performance question

[Simon Slavin]

On 5 Oct 2010, at 10:43am, Mail wrote:

> SELECT COUNT(DISTINCT t0.Z_PK) FROM ZFFFOOD t0 WHERE (t0.ZFOODGROUP = ? AND 
> (SELECT COUNT(*) FROM ZFFWEIGHT t1 WHERE (t0.Z_PK = t1.ZFOOD) ) > ?)

Did you miss out 'AS' from your FROM clause between ZFFFOOD and t0 ?  And again 
in the sub-SELECT ?

Looking at your WHERE clause you have

't0.ZFOODGROUP = ?'

and I can see an index on the ZFOODGROUP column, so that should be okay.  So I 
suspect that the time-suck is the sub-SELECT.  Can you rephrase the main SELECT 
so it has the appropriate kind of JOIN instead of a sub-SELECT ?

But I think your biggest problem is that COUNT(*) is extremely slow because it 
can't use indexes.  Could you put a field in ZFFFOOD which tells you how many 
groups that ZFFFOOD has ?  I know it raises problems of database integrity but 
if you need to do this operation a lot it's going to speed it up tremendously.

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

Re: [sqlite] disk IO error after windows resumes from sleep

[Serena Lien]

Yes - that is indeed what I am doing (your 3rd scenario) - but I have tried
to close the connection first (and ignore any errors closing it) before
retrying to open. As you say, the database object I am trying to close may
not be valid so I am not able to close it first, and therefore unable to
open again. Terminating the application obviously allows it to close, so
that restarting my application now successfully opens it.

So my question is, is there any sqlite function I can call so that the
connection can be successfully closed?

thanks Serena.


On Tue, Oct 5, 2010 at 12:57 PM, Simon Slavin  wrote:

>
> On 5 Oct 2010, at 11:46am, Serena Lien wrote:
>
> > On a windows vista/win7 machine or a laptop which goes into sleep mode,
> when
> > it resumes and the application tries to open a database on a networked
> > drive, the open function returns SQLITE_CANTOPEN and SQLITE_IOERR. I
> don't
> > have a problem with this, if the OS has lost access to the network I can
> > imagine SQLITE_IOERR is quite valid. My question is, is there any way to
> > recover now from this error without forcing my application to exit and
> > restart? Any number of retries using sqlite3_open_v2 always continue to
> fail
> > with SQLITE_IOERR.
>
> SQLite doesn't do anything if you don't have any connections open.  So as
> far as SQLite is concerned, there's absolutely no difference between
>
> start application
> sleep
> wakeup
> sqlite3_open_v2()
>
> and
>
> start application
> sleep
> wakeup
> quit application
> restart application
> sqlite3_open_v2()
>
> There's just no way for SQLite to tell the difference between these two
> states.  So if something is stopping your application from accessing a file
> until it has been restarted, it's a function of the operating system rather
> than something that SQLite is doing.  However, are you perhaps doing
>
> start application
> sqlite3_open_v2()
> sleep
> wakeup
> // Oh, my open connection is now useless, I need one that works !
> sqlite3_open_v2()
>
> I can imagine that failing because SQLite thinks it already has a working
> connection to that database.  The cure would be to use sqlite3_close()
> before the new _open, though I'm not sure whether you'd have an appropriate
> connection you could use with it.  Another problem with this is that if you
> have a prepared statement for a connection, you can't close it.  You have to
> finalize first.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query performance question

[Sven]

Thank you very much! Your query is exactly what I was looking for.

If I understand you correctly, the main bottleneck is the count in the inner 
select - is that correct? (disregarding the overall loop for all groups)


Am 05.10.2010 um 12:24 schrieb Drake Wilson:

> Quoth Mail , on 2010-10-05 11:43:29 +0200:
>> SELECT COUNT(DISTINCT t0.Z_PK) FROM ZFFFOOD t0 WHERE (t0.ZFOODGROUP
>> = ? AND (SELECT COUNT(*) FROM ZFFWEIGHT t1 WHERE (t0.Z_PK =
>> t1.ZFOOD) ) > ?)
> 
> There should not need to be a DISTINCT when talking about a primary
> key.  They will already be distinct by definition, no?  (I'm assuming
> your primary keys will not be null, even though you don't have a NOT
> NULL constraint on the column.)
> 
> Aside from that, if you want "at least one", EXISTS is the obvious
> operator to use rather than first requesting the count (and having to
> iterate all the rows even though only the first one is needed to
> demonstrate existence.)
> 
>> My current task is to get the number of foods that belong to each
>> group and have at least one weight data related to them.
> 
> That suggests something like:
> 
>  SELECT g.Z_PK AS "group", COUNT(f.Z_PK) AS "count"
>FROM ZFFFOODGROUP g
> INNER JOIN ZFFFOOD f ON f.ZFOODGROUP = g.Z_PK
>   WHERE EXISTS (SELECT w.Z_PK FROM ZFFWEIGHT w WHERE w.ZFOOD = f.Z_PK)
>   GROUP BY g.Z_PK;
> 
> (I think the WHERE EXISTS could be done with another join, but it
> would probably be more convoluted.)
> 
>> When inserting 12 for ZFOODGROUP and 0 for count (I never check for
>> another count value here, it's always 0), I get a result of 761
>> which takes 0.0591 seconds. As I have to fetch the counts of all
>> available groups, this query is sent in a loop which easily takes
>> several seconds to finish.
> 
> Do the loop in the query itself using GROUP BY instead, if you can,
> rather than issuing several queries.
> 
> My local, unrigorous measurements suggest that on my machine, with the
> provided example database, your original query takes 40 ms of CPU per
> run and the above takes 16 ms total; I imagine the use of GROUP BY and
> EXISTS and the lack of the extra DISTINCT are the primary factors, but
> I haven't checked thoroughly enough to say so confidently.  I'm using
> SQLite 3.7.2 on Debian GNU/Linux sid AMD64.
> 
>   ---> Drake Wilson
> ___
> 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] query performance question

[Igor Tandetnik]

Mail  wrote:
> My current task is to get the number of foods that belong to each group and 
> have at least one weight data related to them.
> 
> The query I am using for this is:
> SELECT COUNT(DISTINCT t0.Z_PK) FROM ZFFFOOD t0 WHERE (t0.ZFOODGROUP = ? AND 
> (SELECT COUNT(*) FROM ZFFWEIGHT t1 WHERE (t0.Z_PK =
> t1.ZFOOD) ) > ?) 
> 
> When inserting 12 for ZFOODGROUP and 0 for count (I never check for another 
> count value here, it's always 0), I get a result of
> 761 which takes 0.0591 seconds. As I have to fetch the counts of all 
> available groups, this query is sent in a loop which easily
> takes several seconds to finish.  

select ZFOODGROUP, count(Z_PK) from ZFFFOOD
where Z_PK in (select ZFOOD from ZFFWEIGHT)
group by ZFOODGROUP;

This gives you counts for all groups at once.

Igor Tandetnik

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

Re: [sqlite] disk IO error after windows resumes from sleep

[Simon Slavin]

On 5 Oct 2010, at 11:46am, Serena Lien wrote:

> On a windows vista/win7 machine or a laptop which goes into sleep mode, when
> it resumes and the application tries to open a database on a networked
> drive, the open function returns SQLITE_CANTOPEN and SQLITE_IOERR. I don't
> have a problem with this, if the OS has lost access to the network I can
> imagine SQLITE_IOERR is quite valid. My question is, is there any way to
> recover now from this error without forcing my application to exit and
> restart? Any number of retries using sqlite3_open_v2 always continue to fail
> with SQLITE_IOERR.

SQLite doesn't do anything if you don't have any connections open.  So as far 
as SQLite is concerned, there's absolutely no difference between

start application
sleep
wakeup
sqlite3_open_v2()

and

start application
sleep
wakeup
quit application
restart application
sqlite3_open_v2()

There's just no way for SQLite to tell the difference between these two states. 
 So if something is stopping your application from accessing a file until it 
has been restarted, it's a function of the operating system rather than 
something that SQLite is doing.  However, are you perhaps doing

start application
sqlite3_open_v2()
sleep
wakeup
// Oh, my open connection is now useless, I need one that works !
sqlite3_open_v2()

I can imagine that failing because SQLite thinks it already has a working 
connection to that database.  The cure would be to use sqlite3_close() before 
the new _open, though I'm not sure whether you'd have an appropriate connection 
you could use with it.  Another problem with this is that if you have a 
prepared statement for a connection, you can't close it.  You have to finalize 
first.

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

Re: [sqlite] disk IO error after windows resumes from sleep

[Serena Lien]

In response to your question - by "always continues to fail" I mean that
yes, after delaying and retrying, even when the file should be accessible, I
still get SQLITE_IOERR returned from sqlite3_open_v2. If my application
exits and restarts, it will try to call sqlite3_open_v2 again on the same
file and this time it will succeed, which is why I said the file *should* be
accessible.

Does that make sense and should that be the application's problem, or
sqlite's?

thanks, Serena.


On Tue, Oct 5, 2010 at 11:59 AM, Drake Wilson  wrote:

> Quoth Serena Lien , on 2010-10-05 11:46:18 +0100:
> > On a windows vista/win7 machine or a laptop which goes into sleep mode,
> when
> > it resumes and the application tries to open a database on a networked
> > drive, the open function returns SQLITE_CANTOPEN and SQLITE_IOERR. I
> don't
> > have a problem with this, if the OS has lost access to the network I can
> > imagine SQLITE_IOERR is quite valid. My question is, is there any way to
> > recover now from this error without forcing my application to exit and
> > restart? Any number of retries using sqlite3_open_v2 always continue to
> fail
> > with SQLITE_IOERR.
> >
> > It is possible the response will be "not sqlite's problem", but I would
> > appreciate any advice anyone has to give,
>
> I would say that unless SQLite is returning that error in unwarranted
> cases, this is really an application-level error recovery problem.
> What do you mean by "always continue to fail"?  Is this the case even
> after you have verified that the desired file is accessible?  Are you
> delaying retries at all?
>
> If the IOERR return code is truthfully signaling inability to access
> the file, then if this is an interactive application, you might signal
> the user to request a retry later.  If it's a batch process, you might
> schedule a retry for later.  If there's some alternative way of
> accessing the database or operating at reduced functionality without
> it, you might try that.  It's hard to be more specific without knowing
> what kind of application is being developed.
>
>   ---> Drake Wilson
> ___
> 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] Slow operation

[Ian Hardingham]

  I typed the command into my console - not doing it once per row.  
Doing it again, it was more like one second.  No other SQLite commands 
should have been happening near the time of execution.

I'm not entirely sure what "schema" means in this context.  The 
definiton of userTable is:

CREATE TABLE IF NOT EXISTS userTable (name TEXT PRIMARY KEY NOT NULL 
UNIQUE, password TEXT NOT NULL, email TEXT, key TEXT, status TEXT, date 
TEXT, playedFor INTEGER, totalScore FLOAT DEFAULT 0, totalRecord TEXT 
DEFAULT '0\t0', dailyScore FLOAT DEFAULT 0, dailyRecord TEXT DEFAULT 
'0\t0', dailyGameRecord TEXT DEFAULT '', dailyGamesPlayed INTEGER 
DEFAULT 0, scoreStreak TEXT DEFAULT '', scoreStreakNumber INT DEFAULT 0, 
noEmail INT DEFAULT 0, playedInfIds TEXT DEFAULT '')

I haven't changed any of the default PRAGMAs, although I suspect it 
might help me.  This is a game server which generally has 10 - 1000 
users, each sending requests every 5-10 seconds.  Every 30 minutes 
there's a big operation which re-calculates the leaderboards, and that 
can take up to 20 seconds.  I'm considering doing that on a separate 
thread, maybe even with a duplicate database.

Your help us really appreciated.

Ian

On 05/10/2010 12:22, Drake Wilson wrote:
> Quoth Ian Hardingham, on 2010-10-05 12:16:11 +0100:
>> Your query,
>>
>> UPDATE userTable SET playedInfIds = ''
>>
>> Still took two seconds actually...  but significantly better than what I
>> was doing.
> You're doing this only once rather than once per row, right?  On a
> table with around 3k rows, it seems a little odd that it would take
> that long, even if updating every row tends to be expensive in
> general.  What does your schema look like, if I might ask?  Is there
> significant concurrent access with that giant update?
>
>> Ian
> --->  Drake Wilson
> ___
> 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] Slow operation

[Drake Wilson]

Quoth Ian Hardingham , on 2010-10-05 12:16:11 +0100:
> Your query,
> 
> UPDATE userTable SET playedInfIds = ''
> 
> Still took two seconds actually...  but significantly better than what I 
> was doing.

You're doing this only once rather than once per row, right?  On a
table with around 3k rows, it seems a little odd that it would take
that long, even if updating every row tends to be expensive in
general.  What does your schema look like, if I might ask?  Is there
significant concurrent access with that giant update?

> Ian

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

Re: [sqlite] Slow operation

[Ian Hardingham]

  Many thanks Drake, all of your points were highly pertinent.  I'll 
stop lazily replying to threads and changing the subject!

I indeed see that my approach was pretty bafflingly bad in highsight.  I 
tend to do most "logic" in the scripting language as opposed to in 
SQLite commands as it's what I'm comfortable with, but I probably need 
to think more in terms of what SQLite can automate for me.

Your query,

UPDATE userTable SET playedInfIds = ''

Still took two seconds actually...  but significantly better than what I 
was doing.

Ian

On 05/10/2010 12:08, Drake Wilson wrote:
> Quoth Ian Hardingham, on 2010-10-05 11:52:36 +0100:
>>Hey guys.  My apologies in advance if this is a slightly mundane question.
> (Please don't start new threads by replying to random messages.  The
> resultant header information indicates falsely that your email is part
> of the same thread.)
>
>> I'm running this code from a scripting language bound to SQLite:
>>
>>   %r = db.query("SELECT * FROM userTable", 0);
>>   %i = 0;
>>
>>   db.query("BEGIN TRANSACTION", 0);
>>   while (%i<  db.numRows(%r))
>>   {
>>   %username = db.getColumn(%r, name);
>>   db.query("UPDATE userTable SET playedInfIds='' WHERE name LIKE
>> '?'", 0, %username);
>>   %i ++;
>>   }
>>   db.query("END TRANSACTION", 0);
> Ah-heh?
>
> A number of points come to mind fairly immediately:
>
>- Don't keep a query from outside a transaction active inside it.
>
>- Don't SELECT * when all you need is one column.
>
>- You shouldn't have to iterate a result set by numerically iterating
>  until you hit the total number of rows, but I don't know what API
>  this is, so I don't know exactly how the replacement would look.
>
>- This whole loop looks like it could be replaced with the single
>  query « UPDATE userTable SET playedInfIds = '' » because you're
>  just targeting all the rows, unless there's something unobviously
>  different that I've missed.
>
> Right now you're doing a full table scan to get each name, then doing
> another full table scan for each name to update each row with a
> similar name.  That's O(N^2) in the number of rows; with 3k rows, that
> requires ~9M processing steps.
>
>> Is there anything obvious I'm doing wrong?  I know using LIKE is not
>> ideal, but the scripting language does not guarantee case so it is
>> necessary here.
> Store the name in a canonical form (e.g., all lowercase) in the
> database, then query based on that form.  You can store the
> non-canonical form next to it in a separate column if it's needed.
> The fact that you are using LIKE suggests that 'ian' and 'Ian' should
> be treated identically, but currently your primary key allows separate
> rows to exist for each of those.
>
> Also, PRIMARY KEY UNIQUE is redundant.  A primary key is always
> unique.
>
> --->  Drake Wilson
> ___
> 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] Incomplete PRAGMA table_info() documentation

[anatoly techtonik]

Hello,

PRAGMA table_info() doc describes only 4 columns in the output while
there are actually 6 in SQLite 3.5.9
http://www.sqlite.org/pragma.html#pragma_table_info
The last number is especially interesting.

Please, CC.
--
anatoly t.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Errors after upgrade from 3.6.22 to 3.7.2

[list67]

Hi.  After upgrading from SQLite 3.6.22 to 3.7.2 an application that I work on 
generated a lot of SQLite related errors.

I think these errors have been traced to a change made in the winAccess(...) 
function.  Specifically the "return SQLITE_IOERR_ACCESS" line that was added.  
Removing the newly added winAccess section (from "WIN32_FILE_ATTRIBUTE_DATA 
..." through just before the "/* isNT" comment and replacing it with the 3.6.22 
"attr = GetFileAttributesW((WCHAR*)zConverted);") seems to prevent the errors.

Some more information about the context of the errors:  The 
sqlite3PagerSharedLock() function reaches the "failed:" case with a rc value != 
SQLITE_OK.  It gets that rc value from the "rc = 
pagerOpenWalIfPresent(pPager);" line (about 13 lines before the "failed:" case).

Is this a bug?

Thank you.

Mike





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

Re: [sqlite] Slow operation

[Drake Wilson]

Quoth Ian Hardingham , on 2010-10-05 11:52:36 +0100:
>   Hey guys.  My apologies in advance if this is a slightly mundane question.

(Please don't start new threads by replying to random messages.  The
resultant header information indicates falsely that your email is part
of the same thread.)

> I'm running this code from a scripting language bound to SQLite:
> 
>  %r = db.query("SELECT * FROM userTable", 0);
>  %i = 0;
> 
>  db.query("BEGIN TRANSACTION", 0);
>  while (%i < db.numRows(%r))
>  {
>  %username = db.getColumn(%r, name);
>  db.query("UPDATE userTable SET playedInfIds='' WHERE name LIKE 
> '?'", 0, %username);
>  %i ++;
>  }
>  db.query("END TRANSACTION", 0);

Ah-heh?

A number of points come to mind fairly immediately:

  - Don't keep a query from outside a transaction active inside it.

  - Don't SELECT * when all you need is one column.

  - You shouldn't have to iterate a result set by numerically iterating
until you hit the total number of rows, but I don't know what API
this is, so I don't know exactly how the replacement would look.

  - This whole loop looks like it could be replaced with the single
query « UPDATE userTable SET playedInfIds = '' » because you're
just targeting all the rows, unless there's something unobviously
different that I've missed.

Right now you're doing a full table scan to get each name, then doing
another full table scan for each name to update each row with a
similar name.  That's O(N^2) in the number of rows; with 3k rows, that
requires ~9M processing steps.

> Is there anything obvious I'm doing wrong?  I know using LIKE is not 
> ideal, but the scripting language does not guarantee case so it is 
> necessary here.

Store the name in a canonical form (e.g., all lowercase) in the
database, then query based on that form.  You can store the
non-canonical form next to it in a separate column if it's needed.
The fact that you are using LIKE suggests that 'ian' and 'Ian' should
be treated identically, but currently your primary key allows separate
rows to exist for each of those.

Also, PRIMARY KEY UNIQUE is redundant.  A primary key is always
unique.

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

Re: [sqlite] disk IO error after windows resumes from sleep

[Drake Wilson]

Quoth Serena Lien , on 2010-10-05 11:46:18 +0100:
> On a windows vista/win7 machine or a laptop which goes into sleep mode, when
> it resumes and the application tries to open a database on a networked
> drive, the open function returns SQLITE_CANTOPEN and SQLITE_IOERR. I don't
> have a problem with this, if the OS has lost access to the network I can
> imagine SQLITE_IOERR is quite valid. My question is, is there any way to
> recover now from this error without forcing my application to exit and
> restart? Any number of retries using sqlite3_open_v2 always continue to fail
> with SQLITE_IOERR.
> 
> It is possible the response will be "not sqlite's problem", but I would
> appreciate any advice anyone has to give,

I would say that unless SQLite is returning that error in unwarranted
cases, this is really an application-level error recovery problem.
What do you mean by "always continue to fail"?  Is this the case even
after you have verified that the desired file is accessible?  Are you
delaying retries at all?

If the IOERR return code is truthfully signaling inability to access
the file, then if this is an interactive application, you might signal
the user to request a retry later.  If it's a batch process, you might
schedule a retry for later.  If there's some alternative way of
accessing the database or operating at reduced functionality without
it, you might try that.  It's hard to be more specific without knowing
what kind of application is being developed.

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

[sqlite] Slow operation

[Ian Hardingham]

  Hey guys.  My apologies in advance if this is a slightly mundane question.

I'm running this code from a scripting language bound to SQLite:

 %r = db.query("SELECT * FROM userTable", 0);
 %i = 0;

 db.query("BEGIN TRANSACTION", 0);
 while (%i < db.numRows(%r))
 {
 %username = db.getColumn(%r, name);
 db.query("UPDATE userTable SET playedInfIds='' WHERE name LIKE 
'?'", 0, %username);
 %i ++;
 }
 db.query("END TRANSACTION", 0);

This query takes about 5 seconds to process - which seems like a long 
time.  userTable has 3000 entries, and the name column is TEXT PRIMARY 
KEY NOT NULL UNIQUE.

Is there anything obvious I'm doing wrong?  I know using LIKE is not 
ideal, but the scripting language does not guarantee case so it is 
necessary here.

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

[sqlite] disk IO error after windows resumes from sleep

[Serena Lien]

I have a question about recovering from SQLITE_IOERR? We are using sqlite
v3.6.15 on windows where the databases may be accessed across a network (I
am aware of the caveats here).

On a windows vista/win7 machine or a laptop which goes into sleep mode, when
it resumes and the application tries to open a database on a networked
drive, the open function returns SQLITE_CANTOPEN and SQLITE_IOERR. I don't
have a problem with this, if the OS has lost access to the network I can
imagine SQLITE_IOERR is quite valid. My question is, is there any way to
recover now from this error without forcing my application to exit and
restart? Any number of retries using sqlite3_open_v2 always continue to fail
with SQLITE_IOERR.

It is possible the response will be "not sqlite's problem", but I would
appreciate any advice anyone has to give,
thanks Serena.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When do I need SQLITE_THREADSAFE?

[Zaher Dirkey]

On Tue, Oct 5, 2010 at 4:55 AM, Jay A. Kreibich  wrote:

> On Mon, Oct 04, 2010 at 07:25:05PM -0700, Dustin Sallings scratched on the
> wall:
>
>
>   The main difference between =1 and =2 is that =2 assumes you more or
>  less know what you're doing and will either lock a database handle as
>  you pass it between threads or you'll keep it private to a thread.
>
>  =1 is designed to be more or less idiot proof, and will simply not
>  let you do something stupid with a database handle, like have two
>  threads try to execute two different statements at the same time.
>
>
What if two therad make insert or update then commit, what is happen to the
second thread after the first made commit?.



>  Either way, any multi-threaded program requires something other than =0.
>
>  In your case, it sounds like =2 would be sufficient, but if you're
>  not looking for every millisecond of performance you can find, the
>  default =1 might be easier and safer.
>

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

Re: [sqlite] query performance question

[Drake Wilson]

Quoth Drake Wilson , on 2010-10-05 03:24:01 -0700:
> > My current task is to get the number of foods that belong to each
> > group and have at least one weight data related to them.
> 
> That suggests something like:
> 
>   SELECT g.Z_PK AS "group", COUNT(f.Z_PK) AS "count"
> FROM ZFFFOODGROUP g
>  INNER JOIN ZFFFOOD f ON f.ZFOODGROUP = g.Z_PK
>WHERE EXISTS (SELECT w.Z_PK FROM ZFFWEIGHT w WHERE w.ZFOOD = f.Z_PK)
>GROUP BY g.Z_PK;

Oh---actually I may have misinterpreted your semantics slightly.  This
will only find groups that have at least one such row.  If you want to
find groups with no such foods and get an explicit zero as a result,
you would have to change that to a LEFT JOIN and then move the EXISTS
somewhere else to ensure at least one row for the zero-result groups.
I'd probably drop the WHERE, then replace the COUNT with SUM(EXISTS
(SELECT ...)) because the EXISTS will return 1 for rows where the
subselect returns non-empty, though there might be a better way to do
that.

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

Re: [sqlite] query performance question

[Drake Wilson]

Quoth Mail , on 2010-10-05 11:43:29 +0200:
> SELECT COUNT(DISTINCT t0.Z_PK) FROM ZFFFOOD t0 WHERE (t0.ZFOODGROUP
> = ? AND (SELECT COUNT(*) FROM ZFFWEIGHT t1 WHERE (t0.Z_PK =
> t1.ZFOOD) ) > ?)

There should not need to be a DISTINCT when talking about a primary
key.  They will already be distinct by definition, no?  (I'm assuming
your primary keys will not be null, even though you don't have a NOT
NULL constraint on the column.)

Aside from that, if you want "at least one", EXISTS is the obvious
operator to use rather than first requesting the count (and having to
iterate all the rows even though only the first one is needed to
demonstrate existence.)

> My current task is to get the number of foods that belong to each
> group and have at least one weight data related to them.

That suggests something like:

  SELECT g.Z_PK AS "group", COUNT(f.Z_PK) AS "count"
FROM ZFFFOODGROUP g
 INNER JOIN ZFFFOOD f ON f.ZFOODGROUP = g.Z_PK
   WHERE EXISTS (SELECT w.Z_PK FROM ZFFWEIGHT w WHERE w.ZFOOD = f.Z_PK)
   GROUP BY g.Z_PK;

(I think the WHERE EXISTS could be done with another join, but it
would probably be more convoluted.)

> When inserting 12 for ZFOODGROUP and 0 for count (I never check for
> another count value here, it's always 0), I get a result of 761
> which takes 0.0591 seconds. As I have to fetch the counts of all
> available groups, this query is sent in a loop which easily takes
> several seconds to finish.

Do the loop in the query itself using GROUP BY instead, if you can,
rather than issuing several queries.

My local, unrigorous measurements suggest that on my machine, with the
provided example database, your original query takes 40 ms of CPU per
run and the above takes 16 ms total; I imagine the use of GROUP BY and
EXISTS and the lack of the extra DISTINCT are the primary factors, but
I haven't checked thoroughly enough to say so confidently.  I'm using
SQLite 3.7.2 on Debian GNU/Linux sid AMD64.

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

[sqlite] query performance question

[Mail]

Hi everybody,

after reading some parts of the documentation and numerous Google searches, I 
still have no solution for fixing my slow query. I would really appreciate if 
you could point me in the right direction.

The basis is a food database (~38 megabyte total size) that contains 
groups/categories, foods themselves and nutrition information.

The database schema:
CREATE TABLE ZFFFOOD ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, 
ZFOODGROUP INTEGER, ZSHORTDESC VARCHAR, ZLONGDESC VARCHAR );
CREATE TABLE ZFFFOODGROUP ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT 
INTEGER, ZLONGDESC VARCHAR, ZICON VARCHAR );
CREATE TABLE ZFFFOODNUTRIENT ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT 
INTEGER, ZFOOD INTEGER, ZNUTRIENT INTEGER, ZAMOUNTINHUNDREDGRAM FLOAT );
CREATE TABLE ZFFNUTRIENT ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT 
INTEGER, ZTAGNAME VARCHAR, ZUNITS VARCHAR, ZLONGDESC VARCHAR );
CREATE TABLE ZFFWEIGHT ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT 
INTEGER, ZFOOD INTEGER, ZGRAMWEIGHT FLOAT, ZAMOUNT FLOAT, ZLONGDESC VARCHAR );
CREATE TABLE Z_METADATA (Z_VERSION INTEGER PRIMARY KEY, Z_UUID VARCHAR(255), 
Z_PLIST BLOB);
CREATE TABLE Z_PRIMARYKEY (Z_ENT INTEGER PRIMARY KEY, Z_NAME VARCHAR, Z_SUPER 
INTEGER, Z_MAX INTEGER);
CREATE INDEX ZFFFOODNUTRIENT_ZAMOUNTINHUNDREDGRAM_INDEX ON ZFFFOODNUTRIENT 
(ZAMOUNTINHUNDREDGRAM);
CREATE INDEX ZFFFOODNUTRIENT_ZFOOD_INDEX ON ZFFFOODNUTRIENT (ZFOOD);
CREATE INDEX ZFFFOODNUTRIENT_ZNUTRIENT_INDEX ON ZFFFOODNUTRIENT (ZNUTRIENT);
CREATE INDEX ZFFFOOD_ZFOODGROUP_INDEX ON ZFFFOOD (ZFOODGROUP);
CREATE INDEX ZFFFOOD_ZLONGDESC_INDEX ON ZFFFOOD (ZLONGDESC);
CREATE INDEX ZFFNUTRIENT_ZTAGNAME_INDEX ON ZFFNUTRIENT (ZTAGNAME);
CREATE INDEX ZFFWEIGHT_ZFOOD_INDEX ON ZFFWEIGHT (ZFOOD);
CREATE INDEX ZFFWEIGHT_ZLONGDESC_INDEX ON ZFFWEIGHT (ZLONGDESC);

My current task is to get the number of foods that belong to each group and 
have at least one weight data related to them.

The query I am using for this is:
SELECT COUNT(DISTINCT t0.Z_PK) FROM ZFFFOOD t0 WHERE (t0.ZFOODGROUP = ? AND 
(SELECT COUNT(*) FROM ZFFWEIGHT t1 WHERE (t0.Z_PK = t1.ZFOOD) ) > ?)

When inserting 12 for ZFOODGROUP and 0 for count (I never check for another 
count value here, it's always 0), I get a result of 761 which takes 0.0591 
seconds. As I have to fetch the counts of all available groups, this query is 
sent in a loop which easily takes several seconds to finish.

I dropped the inner select - disregarding the wrong count as this is just a 
speed test - and the query is a bit faster, but still too slow (0.0314 seconds):
SELECT COUNT(DISTINCT t0.Z_PK) FROM ZFFFOOD t0 WHERE t0.ZFOODGROUP = ?

Due to the size of the database, I put it online for download rather than 
attaching it. ;-)
Please download a copy here: http://dl.dropbox.com/u/8867517/food.sqlite.zip

Any hints are welcome. I'm keen to learn. :-)

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