[sqlite] Re: - [sqlite] NOT NULL in create table command not work

[rbundy]

Are you confusing a NULL with an empty (zero length) string? They are not
the same.

Regards.

rayB



|-+>
| |   "Nguyen Dang |
| |   Quang"   |
| |   <[EMAIL PROTECTED]|
| |   m>   |
| ||
| |   24/05/2006 16:08 |
| |   Please respond to|
| |   sqlite-users |
| ||
|-+>
  
>--|
  | 
 |
  |   To:  
 |
  |   cc:   
 |
  |   Subject:  - [sqlite] NOT NULL in create table command not work
 |
  
>--|




Hi guys,

I used the following command to create table:

create table ne(ne_id integer primary key autoincrement, name varchar(50)
not null, directory varchar(256) not null, adaptor_name varchar(50) not
null, note varchar(500), filter_rule varchar(50), unique(name),
unique(directory))

But my application still insert emty string into DIRECTORY field

Why? It's a bug? How can I solve this problems?

Thanks





** PLEASE CONSIDER OUR ENVIRONMENT BEFORE PRINTING
*
*** Confidentiality and Privilege Notice
***

This e-mail is intended only to be read or used by the addressee. It is 
confidential and may contain legally privileged information. If you are not the 
addressee indicated in this message (or responsible for delivery of the message 
to such person), you may not copy or deliver this message to anyone, and you 
should destroy this message and kindly notify the sender by reply e-mail. 
Confidentiality and legal privilege are not waived or lost by reason of 
mistaken delivery to you.

Qantas Airways Limited
ABN 16 009 661 901

Visit Qantas online at http://qantas.com

[sqlite] NOT NULL in create table command not work

[Nguyen Dang Quang]

Hi guys,

I used the following command to create table:

create table ne(ne_id integer primary key autoincrement, name varchar(50)
not null, directory varchar(256) not null, adaptor_name varchar(50) not
null, note varchar(500), filter_rule varchar(50), unique(name),
unique(directory))

But my application still insert emty string into DIRECTORY field

Why? It's a bug? How can I solve this problems?

Thanks

[sqlite] GROUP BY regression workaround?

[Joe Wilson]

Anyone have any ideas how to speed up GROUP BY on huge views 
in recent versions of SQLite?

  http://www.sqlite.org/cvstrac/tktview?tn=1809

The older versions of SQLite (prior to SQLite 3.2.6) used to 
perform GROUP BY operations in the main table loop, grouping
rows as it went along. But the new version appears to create 
a huge temp table of results, sort them, and only then determine 
the groups. This takes up a massive amount of temp store.
The old GROUP BY algorithm excels in the case where there are 
a lot of groupable rows in the dataset; i.e., the typical case.


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [sqlite] scary warnings

[Nuno Lucas]

On 5/23/06, Cory Nelson <[EMAIL PROTECTED]> wrote:

> I'm not fully sure about this as I never had to do any programming for
> 64 bits, yet, so correct me if I'm wrong.

GCC's int is also 32bit on x64 (likely because x64 supports 32bit
arithmetic in 64bit apps without penalty, while 64bit operations
require a REX prefix which increases code size and can hurt cache).
The type that differs between the compilers is long, which is 32bit in
vc++ and 64bit in gcc.


Thanks for the correction. I had probably read something about some
other 64 bits platform (maybe IA-64, as I think I recall something
about it being slower in 32 bits mode).


Best regards,
~Nuno Lucas

[sqlite] In the year 4461763

[Chris Werner]

Hello,

SQLite version 3.3.4  

Linux ** 2.4.31 #4 SMP Wed Apr 19 16:30:29 CDT 2006 i686 unknown unknown
GNU/Linux

 

I am just curious about SQLite's date and time manipulation functions. I am
using the sqlite3 command line interface on the above described platform.

 

When I select a datetime for 2^47:

sqlite> SELECT datetime(140737488355328, 'unixepoch');

4461763-06-20 05:22:07

 

Adding a second to the time  [2^47 +1] seems to add 2 seconds to the
return??

sqlite> SELECT datetime(140737488355329, 'unixepoch');

4461763-06-20 05:22:09

 

I suspect an overflow of some sort, but I cannot phrase it concisely.

 

Date time seems to return reasonable values up until  185327782012799 [you
tell me], after which the return format is not a valid date. 

Again, I suspect an overflow of some sort, can anyone explain?

 

sqlite> SELECT datetime(185327782012799, 'unixepoch');

5874773-08-15 23:59:58

sqlite> SELECT datetime(185327782012800, 'unixepoch');

-5884205--1-00 00:00:00

 

 

Not a critical item, nor important enough to be reported as a bug,

Just curious,

Christian Werner

Re: [sqlite] "SQL logic error or missing database"

[John Stanton]

Why do you connect twice to the DB?  You then run into synchronization 
issues.

JS

Ran wrote:

Hi all,

Could someone help me with the script below? I get an "SQL logic error or
missing database" and cannot find what I do wrong.
I use sqlite 3.3.4 on Linux.

What I do there is:
1. Open connection to a new database.
2. Create table bla in a transaction.
3. Open another connection to the database.
4. Create table foo in a transaction using the second connection.
5. Try to delete from the table bla using the first connection. ==> this
gives "SQL logic error or missing database".

Here is how it runs (without arguments, the second connection is not 
opened,

and the table foo is not created - and this runs OK):

./bug
Opened the database.
deleted all from bla successfully
./bug 1
Opened the database.
Opened the database.
Failed to step statement: SQL logic error or missing database

Here is the script:

// compile with: gcc -g bug.cpp -lsqlite3 -o bug
#include 
#include 
#include 
#include 

int main(int argc, char** argv) {
 int rc;
 sqlite3* db1;
 sqlite3* db2;
 sqlite3_stmt *pStmt1;
 sqlite3_stmt *pStmt2;
 sqlite3_stmt *pStmt3;

 unlink("bug.db"); // for the test, we make sure we have a new database.

 // create first connection to the database: db1.
 rc = sqlite3_open("bug.db", &db1);
 if (rc) {
   printf("Cannot open database: %s\n", sqlite3_errmsg(db1));
   exit(1);
 }
 printf("Opened the database.\n");

 // create table bla using the first connection db1, inside a transaction.
 sqlite3_exec(db1, "begin", 0, 0, 0);
 rc = sqlite3_prepare(db1,// Database handle
  "create table bla(a int,b int)",
  -1, // Length of the statement
  &pStmt1,   // OUT: Statement handle
  0); // OUT: Pointer to unused portion
  // of the statement

 rc = sqlite3_step(pStmt1);
 if (rc != SQLITE_DONE) { // if we failed, we show it.
   printf("Failed to step statement: %s\n", sqlite3_errmsg(db1));
 }
 rc = sqlite3_finalize(pStmt1);
 sqlite3_exec(db1, "commit", 0, 0, 0); // here we commit the transaction.

 // now we suppose to have inside the database the table bla.

 // here we, optionally, create another connection to the same database,
 // and then create other table in a transaction.
 if (argc > 1) {
   rc = sqlite3_open("bug.db", &db2); // create the second connection.
   if (rc) {
 printf("Cannot open database again: %s\n", sqlite3_errmsg(db2));
 exit(1);
   }
   else {
 printf("Opened the database.\n");
   }

   // create table foo
   sqlite3_exec(db2, "begin", 0, 0, 0); // start the transaction.
   rc = sqlite3_prepare(db2,// Database handle
"create table foo(c int,d int)",
-1, // Length of the statement
&pStmt2,// OUT: Statement handle
0); // OUT: Pointer to unused
portion
// of the statement

   rc = sqlite3_step(pStmt2);
   if (rc != SQLITE_DONE) { // if we failed, we show it.
 printf("Failed to step statement: %s\n", sqlite3_errmsg(db2));
   }
   rc = sqlite3_finalize(pStmt2);
   sqlite3_exec(db2, "commit", 0, 0, 0);
 }

 // delete from table bla using the first connection.
 sqlite3_exec(db1, "begin", 0, 0, 0);
 rc = sqlite3_prepare(db1,// Database handle
  "delete from bla",
  -1, // Length of the statement
  &pStmt3,   // OUT: Statement handle
  0); // OUT: Pointer to unused portion
  // of the statement

 rc = sqlite3_step(pStmt3);
 if (rc != SQLITE_DONE) { // if we failed, we log it.
   printf("Failed to step statement: %s\n", sqlite3_errmsg(db1));
 }
 else {
   printf("deleted all from bla successfully\n");
 }
 rc = sqlite3_finalize(pStmt3);
 sqlite3_exec(db1, "commit", 0, 0, 0);
}

Re: [sqlite] Having troubles with Sqlite3 Crypto extension on v3.3.5

[Dennis Jenkins]

[EMAIL PROTECTED] wrote:
> Dennis Jenkins <[EMAIL PROTECTED]> wrote:
>   
>> Has the crypto extension been updated since last summer?
>>
>> 
>
> Yes.  The latest code has been sent to you by private email.
> --
> D. Richard Hipp   <[EMAIL PROTECTED]>
>
>   
Thank you.  The code seems to have fixed the problems.

Re: [sqlite] Having troubles with Sqlite3 Crypto extension on v3.3.5

[drh]

Dennis Jenkins <[EMAIL PROTECTED]> wrote:
> 
> Has the crypto extension been updated since last summer?
> 

Yes.  The latest code has been sent to you by private email.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] Re: "SQL logic error or missing database"

[Ran]

I would like to thank all of you. Although it all sounds logic to me, I was
very far from finding the problem alone.

So thanks again,

Ran

On 5/23/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:


On 5/23/06, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
>
> When a new connection is opened, SQLite reads and caches the schema.
> Prepare operation uses this cached schema, without reading from disk.
> This is why prepare doesn't know that schema has changed.
>
> It would be pretty pointless to have prepare check for schema changes.
> This would incur a disk read, and the schema can very well change again
> between prepare and step so this read won't achieve anything. Now, the
> first step operation has to perform a disk I/O anyway, in order to
> acquire a lock. This is when the schema is checked and, if a change is
> detected, the current schema is retrieved and cached for the connection.

Learned something important there, thanks Igor!

Re: [sqlite] "SQL logic error or missing database"

[drh]

Ran <[EMAIL PROTECTED]> wrote:
> Indeed if I reset after the first step failed, and than prepare again, the
> select works. But I guess this is not the usual way to do things right? I
> mean - shouldn't the first prepare be aware of the fact that the database
> was changed? Or maybe CREATE TABLE is a special case?
> 

Because the schema change happened on a separate
database connection, the other connection does not
realize that the schema has changed until it accesses
the disk again.  And no disk access occurs until you
run sqlite3_step().  Thus, even though the schema
change occurred temporally before the sqlite3_prepare(),
the sqlite3_prepare() had no way of knowing about the
schema change and used the old schema.

--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] Re: "SQL logic error or missing database"

[Jay Sprenkle]

On 5/23/06, Igor Tandetnik <[EMAIL PROTECTED]> wrote:


When a new connection is opened, SQLite reads and caches the schema.
Prepare operation uses this cached schema, without reading from disk.
This is why prepare doesn't know that schema has changed.

It would be pretty pointless to have prepare check for schema changes.
This would incur a disk read, and the schema can very well change again
between prepare and step so this read won't achieve anything. Now, the
first step operation has to perform a disk I/O anyway, in order to
acquire a lock. This is when the schema is checked and, if a change is
detected, the current schema is retrieved and cached for the connection.


Learned something important there, thanks Igor!

Re: [sqlite] "SQL logic error or missing database"

[drh]

Ran <[EMAIL PROTECTED]> wrote:
> 
>   rc = sqlite3_step(pStmt3);
>   if (rc != SQLITE_DONE) { // if we failed, we log it.
> printf("Failed to step statement: %s\n", sqlite3_errmsg(db1));
>   }

The sqlite3_errmsg() API does not return the correct error
message text until after you do sqlite3_reset() or
sqlite3_finalize().  So if you change the above to read:

rc = sqlite3_step(pStmt3);
if (rc != SQLITE_DONE) { // if we failed, we show it.
  sqlite3_reset(pStmt3);  //  This line inserted
  printf("Failed to step statement: %s\n", sqlite3_errmsg(db1));
}

Then the program will give you the correct error message:

   "database schema has changed"

Finalize your statement and rebuild it by a second call to
sqlite3_prepare() and it should work.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

[sqlite] Re: "SQL logic error or missing database"

[Igor Tandetnik]

Jay Sprenkle <[EMAIL PROTECTED]> wrote:

It was prepared after the schema was changed and written to disk.
That seems pretty unintuitive to me. When you prepare the statement
it evidently doesn't use the current schema, though it knows the
schema has changed...


When a new connection is opened, SQLite reads and caches the schema. 
Prepare operation uses this cached schema, without reading from disk. 
This is why prepare doesn't know that schema has changed.


It would be pretty pointless to have prepare check for schema changes. 
This would incur a disk read, and the schema can very well change again 
between prepare and step so this read won't achieve anything. Now, the 
first step operation has to perform a disk I/O anyway, in order to 
acquire a lock. This is when the schema is checked and, if a change is 
detected, the current schema is retrieved and cached for the connection.


Igor Tandetnik 

Re: [sqlite] can you speed this query up?

[A. Pagaltzis]

* A. Pagaltzis <[EMAIL PROTECTED]> [2006-05-23 09:40]:
> Now we can group together the conditions which do not involve
> the `bounds` table:
> 
> (r.qi = 5604 AND r.ri <= 5468) OR (r.ri = 5468 AND r.qi <= 5604)
> AND r.qi >= b.bqis
> AND r.ri >= b.bris
> AND b.bi = 1

Ack. The ORed clauses need to be parenthesised:

((r.qi = 5604 AND r.ri <= 5468) OR (r.ri = 5468 AND r.qi <= 5604))
AND r.qi >= b.bqis
AND r.ri >= b.bris
AND b.bi = 1

> The rest can then be factored into a subquery:

Same bug slipped into this query.

Regards,
-- 
Aristotle Pagaltzis // 

RE: [sqlite] Having troubles with Sqlite3 Crypto extension on v3.3.5

[Robert Simpson]

> -Original Message-
> From: Dennis Jenkins [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, May 23, 2006 11:49 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Having troubles with Sqlite3 Crypto 
> extension on v3.3.5
> 
> Hello,
> 
> I'm going to be light on the details here because I'm not 
> sure how much I can publically discuss (with respect to the 
> Sqlite3 crypto license).
> 
> Out project has been using Sqlite3 v 3.2.1 for a long 
> time, with Dr.
> Hipp's encryption extension (purchased 2005-7-11).  I am 
> about to begin a long stretch of development and wanted to 
> update the sqlite code (which we statically compile into our 
> win32 app).  I read that v 3.3.4 fixes some multi-threaded 
> bugs.  I did not see an easy way to get 3.3.4 so I grabbed 
> 3.3.5.  I managed to get it all to compile.  However, 
> whenever I use the crypto extension, the next SQL that I 
> execute returns code "1" (not a database) when I call 
> sqlite3_step.  If I disabled the calls to "sqlite3_key()" and 
> start with an empty database file (ie, I nuke the file 
> first), I get no errors, but no encryption.
> 
> Is anyone else successfully using the crypto extension 
> with sqlite 3.3.5?
> 
> How can I get the sources for 3.3.4?  I assume that I 
> need to use CVS some how, but I'm not sure.
> 
> Has the crypto extension been updated since last summer?

Yes, there were some changes since then.  I've never seen DRH's encryption
module, but I rolled my own encryption into SQLite that uses the same
plumbing.  I've had to change it a couple times to adapt to some of the
recent changes.

Robert

Re: [sqlite] can you speed this query up?

[A. Pagaltzis]

* Adrian Ho <[EMAIL PROTECTED]> [2006-05-23 16:05]:
> On Tue, May 23, 2006 at 08:50:56AM +0200, A. Pagaltzis wrote:
> > * [EMAIL PROTECTED] <[EMAIL PROTECTED]> [2006-05-23 02:35]:
> > > What you have to do is:
> > > 
> > >SELECT qi, ri, drl, score
> > >  FROM ...
> > > WHERE score=(SELECT max(score) FROM ...)
> > 
> > Actually, in cases such as this, the easiest approach is to
> > use `LIMIT`:
> > 
> > SELECT qi, ri, drl, score
> > FROM ...
> > WHERE ...
> > ORDER BY score DESC
> > LIMIT 1
> 
> Only if "cases such as this" is defined as "datasets where only
> one record has the maximum score" (which may be the case that
> Brannon presented -- I don't recall offhand).  Otherwise, the
> two queries above are semantically different and should
> reasonably be expected to return different results.

I actually thought of that. However, note that the *original*
query to which Mr. Hipp is referring actually used an aggregate
function in the `SELECT` clause and would thus always return only
exactly one row. So my reformulation is arguably the correct way
to express Brannon’s original intent.

Regards,
-- 
Aristotle Pagaltzis // 

RE: [sqlite] "SQL logic error or missing database"

[Robert Simpson]

> -Original Message-
> From: Ran [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, May 23, 2006 11:47 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] "SQL logic error or missing database"
> 
> Indeed if I reset after the first step failed, and than 
> prepare again, the select works. But I guess this is not the 
> usual way to do things right? I mean - shouldn't the first 
> prepare be aware of the fact that the database was changed? 
> Or maybe CREATE TABLE is a special case?

I suspect that in order to save time, prepare() will not check to see if the
schema has changed unless the SQL fails to compile.  If the SQL fails to
compile, it'll go ahead and check the schema and report the schema failure
if there is one.

Since your SQL didn't fail to compile, it didn't bother checking to see if
the schema had expired.

The overhead of checking for expired schema on every prepare would be
prohibitive, I would think.

Robert

Re: [sqlite] "SQL logic error or missing database"

[Craig Morrison]

Ran wrote:

Indeed if I reset after the first step failed, and than prepare again, the
select works. But I guess this is not the usual way to do things right? I
mean - shouldn't the first prepare be aware of the fact that the database
was changed? Or maybe CREATE TABLE is a special case?


If I were to hazzard a guess, its because there was a second handle 
opened to the database that modified the schema which the first handle 
doesn't know about until it does its sanity check before operating on 
the database.


--
Craig Morrison
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
http://pse.2cah.com
  Controlling pseudoephedrine purchases.

http://www.mtsprofessional.com/
  A Win32 email server that works for You.

Re: [sqlite] can you speed this query up?

[A. Pagaltzis]

Hi Brannon,

* Brannon King <[EMAIL PROTECTED]> [2006-05-23 20:05]:
> Thank you for taking the time to sort out my query!

NP. I have only recently studied SQL in depth, and this was an
interesting exercise.

> This one above was as slow as the original. 

Yes, as expected – it is exactly the same query, only written
slightly differently as a starting point so I could actually see
what was going on.

> But this one was twice as fast! I was able to use Mr. Cote's
> suggestion of EXPLAIN QUERY PLAN to look at the indexes being
> used and make it run even faster.

Great. :-)

Regards,
-- 
Aristotle Pagaltzis // 

RE: [sqlite] "SQL logic error or missing database"

[Robert Simpson]

> -Original Message-
> From: Jay Sprenkle [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, May 23, 2006 11:27 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] "SQL logic error or missing database"
> 
> It was prepared after the schema was changed and written to disk.
> That seems pretty unintuitive to me. When you prepare the 
> statement it evidently doesn't use the current schema, though 
> it knows the schema has changed...
> I guess that implies the call to reset() reloads the schema 
> I'll check that=0

I changed the select statement to read "select * from foo" which was the new
table created on the 2nd connection.  When I did that, sqlite3_prepare()
returned immediately with SQLITE_SCHEMA.  So it appears that the schema is
only reloaded on prepare() under certain conditions. 

Immediately calling prepare() again after getting SQLITE_SCHEMA above
resulted in a properly prepared statement that step()'d normally.

Robert

[sqlite] Having troubles with Sqlite3 Crypto extension on v3.3.5

[Dennis Jenkins]

Hello,

I'm going to be light on the details here because I'm not sure how
much I can publically discuss (with respect to the Sqlite3 crypto license).

Out project has been using Sqlite3 v 3.2.1 for a long time, with Dr.
Hipp's encryption extension (purchased 2005-7-11).  I am about to begin
a long stretch of development and wanted to update the sqlite code
(which we statically compile into our win32 app).  I read that v 3.3.4
fixes some multi-threaded bugs.  I did not see an easy way to get 3.3.4
so I grabbed 3.3.5.  I managed to get it all to compile.  However,
whenever I use the crypto extension, the next SQL that I execute returns
code "1" (not a database) when I call sqlite3_step.  If I disabled the
calls to "sqlite3_key()" and start with an empty database file (ie, I
nuke the file first), I get no errors, but no encryption.

Is anyone else successfully using the crypto extension with sqlite
3.3.5?

How can I get the sources for 3.3.4?  I assume that I need to use
CVS some how, but I'm not sure.

Has the crypto extension been updated since last summer?

Re: [sqlite] "SQL logic error or missing database"

[Ran]

Indeed if I reset after the first step failed, and than prepare again, the
select works. But I guess this is not the usual way to do things right? I
mean - shouldn't the first prepare be aware of the fact that the database
was changed? Or maybe CREATE TABLE is a special case?

On 5/23/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:


On 5/23/06, Robert Simpson <[EMAIL PROTECTED]> wrote:
> > That doesn't seem right.
> > The change was made and committed then the database statement
> > prepared.
> > The change should have already been written so the prepare
> > should have gotten the latest stuff. Does it need to be
> > closed and reopened for a schema change to be recognized?=
>
> Beats me, but that's the way it seems to work.  You should always
reset() a
> statement after a failed step(), and check the reset()'s error code to
see
> if it's SQLITE_SCHEMA.  If it is, you should re-prepare the
statement.  I
> don't think it matters when the statement was prepare()'d as long as you
> follow that rule.

It was prepared after the schema was changed and written to disk.
That seems pretty unintuitive to me. When you prepare the statement
it evidently doesn't use the current schema, though it knows the schema
has changed...
I guess that implies the call to reset() reloads the schema
I'll check that

Re: [sqlite] "SQL logic error or missing database"

[Jay Sprenkle]

On 5/23/06, Robert Simpson <[EMAIL PROTECTED]> wrote:

> That doesn't seem right.
> The change was made and committed then the database statement
> prepared.
> The change should have already been written so the prepare
> should have gotten the latest stuff. Does it need to be
> closed and reopened for a schema change to be recognized?=

Beats me, but that's the way it seems to work.  You should always reset() a
statement after a failed step(), and check the reset()'s error code to see
if it's SQLITE_SCHEMA.  If it is, you should re-prepare the statement.  I
don't think it matters when the statement was prepare()'d as long as you
follow that rule.


It was prepared after the schema was changed and written to disk.
That seems pretty unintuitive to me. When you prepare the statement
it evidently doesn't use the current schema, though it knows the schema
has changed...
I guess that implies the call to reset() reloads the schema
I'll check that

RE: [sqlite] "SQL logic error or missing database"

[Robert Simpson]

> -Original Message-
> From: Jay Sprenkle [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, May 23, 2006 10:55 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] "SQL logic error or missing database"
> 
> On 5/23/06, Robert Simpson <[EMAIL PROTECTED]> wrote:
> > After sqlite3_step() fails, you should call sqlite3_reset() on the 
> > statement.  This is what will give you the SQLITE_SCHEMA error, 
> > indicating you need to re-prepare your statement.
> 
> 
> That doesn't seem right.
> The change was made and committed then the database statement 
> prepared.
> The change should have already been written so the prepare 
> should have gotten the latest stuff. Does it need to be 
> closed and reopened for a schema change to be recognized?=

Beats me, but that's the way it seems to work.  You should always reset() a
statement after a failed step(), and check the reset()'s error code to see
if it's SQLITE_SCHEMA.  If it is, you should re-prepare the statement.  I
don't think it matters when the statement was prepare()'d as long as you
follow that rule.

Robert

RE: [sqlite] can you speed this query up?

[Brannon King]

Thank you for taking the time to sort out my query! (The meat is at the
bottom.)

> SELECT
> r.qi,
> r.ri,
> r.drl,
> r.score
> FROM
> results_1 r
> INNER JOIN bounds b ON
> r.qis = b.bqis AND r.ris = b.bris
> WHERE
> (r.qi = 5604 OR r.ri = 5468)
> AND (r.qi >= b.bqis AND r.qi <= 5604)
> AND (r.ri >= b.bris AND r.ri <= 5468)
> AND b.bi = 1
> GROUP BY
> r.score DESC
> LIMIT 1

This one above was as slow as the original. 

> SELECT
> r.qi,
> r.ri,
> r.drl,
> r.score
> FROM
> results_1 r
> WHERE
> (r.qi = 5604 AND r.ri <= 5468) OR (r.ri = 5468 AND 
> r.qi <= 5604)
> AND EXISTS (
> SELECT
> NULL
> FROM
> bounds b
> WHERE
> b.bi = 1
> AND b.bqis = r.qis
> AND b.bris = r.ris
> AND b.bqis <= r.qi
> AND b.bris <= r.ri
> )
> ORDER BY
> r.score DESC
> LIMIT 1

But this one was twice as fast! I was able to use Mr. Cote's suggestion of
EXPLAIN QUERY PLAN to look at the indexes being used and make it run even
faster. My results were interesting, though. I had an index on results_1
that was both (qi,ri), call it coord. That was being unused in the above
query. I added a qi index and that made the above query use coord.
Apparently it doesn't matter if I add an index for qi or ri or both, any of
those options makes it use the coord index. I don't understand exactly why
on that. I tried adding an index on score. That seemed to speed up queries
where there were lots of scores, but it slowed down queries where there were
few scores.

Re: [sqlite] "SQL logic error or missing database"

[Jay Sprenkle]

On 5/23/06, Robert Simpson <[EMAIL PROTECTED]> wrote:

After sqlite3_step() fails, you should call sqlite3_reset() on the
statement.  This is what will give you the SQLITE_SCHEMA error, indicating
you need to re-prepare your statement.



That doesn't seem right.
The change was made and committed then the database statement prepared.
The change should have already been written so the prepare should have
gotten the latest stuff. Does it need to be closed and reopened for a schema
change to be recognized?

RE: [sqlite] "SQL logic error or missing database"

[Robert Simpson]

> -Original Message-
> From: Robert Simpson [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, May 23, 2006 10:30 AM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] "SQL logic error or missing database"
> 
[snip]
>   rc = sqlite3_step(pStmt3);
>   if (rc) rc = sqlite3_reset(pStmt3);
>   if (rc == SQLITE_SCHEMA) {
> rc = sqlite3_finalize(pStmt3);
> // Todo: re-prepare the statement according to the FAQ at
> // http://www.sqlite.org/faq.html#q17
>   }

Bah the above code isn't right. Check for 100 and 101 after the first
sqlite3_step() before checking for an error rc code.

RE: [sqlite] "SQL logic error or missing database"

[Robert Simpson]

> -Original Message-
> From: Ran [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, May 23, 2006 10:08 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] "SQL logic error or missing database"
> 
> Oh! Did you run it with a parameter so:
> 
> ./bug 1
> 

No I missed that little gem.  After I passed in a parameter, it failed.

However, the failure is due to a SQLITE_SCHEMA error.  (I am using 3.3.5)

I modified the code slightly to catch the schema error:

  rc = sqlite3_prepare(db1,// Database handle
   "select * from bla",
   -1, // Length of the statement
   &pStmt3,// OUT: Statement handle
   0); // OUT: Pointer to unused portion

   // of the statement
  if (rc != SQLITE_OK) {
printf("Failed to prepare statement: %s\n", sqlite3_errmsg(db1));
  }

  rc = sqlite3_step(pStmt3);
  if (rc) rc = sqlite3_reset(pStmt3);
  if (rc == SQLITE_SCHEMA) {
rc = sqlite3_finalize(pStmt3);
// Todo: re-prepare the statement according to the FAQ at
// http://www.sqlite.org/faq.html#q17
  }


After sqlite3_step() fails, you should call sqlite3_reset() on the
statement.  This is what will give you the SQLITE_SCHEMA error, indicating
you need to re-prepare your statement.

Robert

Re: [sqlite] "SQL logic error or missing database"

[Ran]

Oh! Did you run it with a parameter so:

./bug 1

(otherwise the second connection is not created - sorry, I guess this is
confusing - I just wanted to show that the second connection or the second
table create the problem).

In any case, I reinstalled sqlite-3.3.4 on my Linux - and the bug is still
there. In addition, the bug was found originally on Windows (the same
version of sqlite), so I suspect it should be also there.

Ran

On 5/23/06, Robert Simpson <[EMAIL PROTECTED]> wrote:


> -Original Message-
> From: Ran [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 23, 2006 8:37 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] "SQL logic error or missing database"
>
> On 5/23/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:
> > hmmm...
> > it gives a schema changed because 'delete * from x'
> actually drops the
> table
> > but I'm not sure why it gave an error since the prepare was
> done after
> > the other change was committed...
> > ...
>
> Thanks for your efforts!
>
> I am afraid that the delete is not connected to the problem.
> Also the prepare/step is not. Even the reset/finalize are not
> connected to it. The script below gives the problem, while
> all the create statements are run by sqlite3_exec (so no
> reset/finalize are used there at all). The problem happens
> only when stepping the select. See the updated script below:

I pasted your code into my Windows environment and ran it.  It completed
successfully with no errors.

> // compile with: gcc -g bug.cpp -lsqlite3 -o bug #include
>  #include  #include  #include 
>
> int main(int argc, char** argv) {
>   int rc;
>   sqlite3* db1;
>   sqlite3* db2;
>   sqlite3_stmt *pStmt3;
>
>   unlink("bug.db"); // for the test, we make sure we have a
> new database.
>
>   // create first connection to the database: db1.
>   rc = sqlite3_open("bug.db", &db1);
>   if (rc) {
> printf("Cannot open database: %s\n", sqlite3_errmsg(db1));
> exit(1);
>   }
>   printf("Opened the database.\n");
>
>   // create table bla using the first connection db1, inside
> a transaction.
>   sqlite3_exec(db1, "begin", 0, 0, 0);
>   rc = sqlite3_exec(db1, "create table bla(c int,d int)", 0, 0, 0);
>   if (rc != SQLITE_OK) { // if we failed, we show it.
> printf("Failed to exec statement: %s\n", sqlite3_errmsg(db1));
>   }
>   sqlite3_exec(db1, "commit", 0, 0, 0); // here we commit the
> transaction.
>
>   // here we, optionally, create another connection to the
> same database,
>   // and then create other table in a transaction.
>   if (argc > 1) {
> rc = sqlite3_open("bug.db", &db2); // create the second
> connection.
> if (rc) {
>   printf("Cannot open database again: %s\n", sqlite3_errmsg(db2));
>   exit(1);
> }
> else {
>   printf("Opened the database.\n");
> }
>
> // create table foo
> sqlite3_exec(db2, "begin", 0, 0, 0); // start the transaction.
> rc = sqlite3_exec(db2, "create table foo(c int,d int)", 0, 0, 0);
> if (rc != SQLITE_OK) { // if we failed, we show it.
>   printf("Failed to exec statement: %s\n", sqlite3_errmsg(db2));
> }
> sqlite3_exec(db2, "commit", 0, 0, 0);
>   }
>
>   // select from table bla using the first connection.
>   sqlite3_exec(db1, "begin", 0, 0, 0);
>   rc = sqlite3_prepare(db1,// Database handle
>"select * from bla",
>-1, // Length of the statement
>&pStmt3,// OUT: Statement handle
>0); // OUT: Pointer to
> unused portion
>
>// of the statement
>   if (rc != SQLITE_OK) {
> printf("Failed to prepare statement: %s\n", sqlite3_errmsg(db1));
>   }
>   rc = sqlite3_step(pStmt3);
>   if (rc != SQLITE_DONE) { // if we failed, we log it.
> printf("Failed to step statement: %s\n", sqlite3_errmsg(db1));
>   }
>   else {
> printf("deleted all from bla successfully\n");
>   }
>   rc = sqlite3_reset(pStmt3);
>   sqlite3_exec(db1, "commit", 0, 0, 0);
>   rc = sqlite3_finalize(pStmt3);
> }
>

Re: [sqlite] "SQL logic error or missing database"

[Jay Sprenkle]

On 5/23/06, Robert Simpson <[EMAIL PROTECTED]> wrote:


I pasted your code into my Windows environment and ran it.  It completed
successfully with no errors.


which version of sqlite are you using?

RE: [sqlite] "SQL logic error or missing database"

[Robert Simpson]

> -Original Message-
> From: Jay Sprenkle [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, May 23, 2006 9:53 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] "SQL logic error or missing database"
> 
> On 5/23/06, Ran <[EMAIL PROTECTED]> wrote:
> > Thanks for your replies.
> >
> > Actually, in my last email (probably you got it after 
> sending yours), 
> > there is a script which exec the create statements, and check the 
> > prepare returned value of a SELECT (and not delete). Still 
> I get the 
> > same bug. So the problem must be in other place.
> 
> I checked all the return codes in the version I posted as well.=0

I'm always late to the party :(

RE: [sqlite] "SQL logic error or missing database"

[Robert Simpson]

> -Original Message-
> From: Ran [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, May 23, 2006 8:37 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] "SQL logic error or missing database"
> 
> On 5/23/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:
> > hmmm...
> > it gives a schema changed because 'delete * from x' 
> actually drops the
> table
> > but I'm not sure why it gave an error since the prepare was 
> done after 
> > the other change was committed...
> > ...
> 
> Thanks for your efforts!
> 
> I am afraid that the delete is not connected to the problem. 
> Also the prepare/step is not. Even the reset/finalize are not 
> connected to it. The script below gives the problem, while 
> all the create statements are run by sqlite3_exec (so no 
> reset/finalize are used there at all). The problem happens 
> only when stepping the select. See the updated script below:

I pasted your code into my Windows environment and ran it.  It completed
successfully with no errors.

> // compile with: gcc -g bug.cpp -lsqlite3 -o bug #include 
>  #include  #include  #include 
> 
> int main(int argc, char** argv) {
>   int rc;
>   sqlite3* db1;
>   sqlite3* db2;
>   sqlite3_stmt *pStmt3;
> 
>   unlink("bug.db"); // for the test, we make sure we have a 
> new database.
> 
>   // create first connection to the database: db1.
>   rc = sqlite3_open("bug.db", &db1);
>   if (rc) {
> printf("Cannot open database: %s\n", sqlite3_errmsg(db1));
> exit(1);
>   }
>   printf("Opened the database.\n");
> 
>   // create table bla using the first connection db1, inside 
> a transaction.
>   sqlite3_exec(db1, "begin", 0, 0, 0);
>   rc = sqlite3_exec(db1, "create table bla(c int,d int)", 0, 0, 0);
>   if (rc != SQLITE_OK) { // if we failed, we show it.
> printf("Failed to exec statement: %s\n", sqlite3_errmsg(db1));
>   }
>   sqlite3_exec(db1, "commit", 0, 0, 0); // here we commit the 
> transaction.
> 
>   // here we, optionally, create another connection to the 
> same database,
>   // and then create other table in a transaction.
>   if (argc > 1) {
> rc = sqlite3_open("bug.db", &db2); // create the second 
> connection.
> if (rc) {
>   printf("Cannot open database again: %s\n", sqlite3_errmsg(db2));
>   exit(1);
> }
> else {
>   printf("Opened the database.\n");
> }
> 
> // create table foo
> sqlite3_exec(db2, "begin", 0, 0, 0); // start the transaction.
> rc = sqlite3_exec(db2, "create table foo(c int,d int)", 0, 0, 0);
> if (rc != SQLITE_OK) { // if we failed, we show it.
>   printf("Failed to exec statement: %s\n", sqlite3_errmsg(db2));
> }
> sqlite3_exec(db2, "commit", 0, 0, 0);
>   }
> 
>   // select from table bla using the first connection.
>   sqlite3_exec(db1, "begin", 0, 0, 0);
>   rc = sqlite3_prepare(db1,// Database handle
>"select * from bla",
>-1, // Length of the statement
>&pStmt3,// OUT: Statement handle
>0); // OUT: Pointer to 
> unused portion
> 
>// of the statement
>   if (rc != SQLITE_OK) {
> printf("Failed to prepare statement: %s\n", sqlite3_errmsg(db1));
>   }
>   rc = sqlite3_step(pStmt3);
>   if (rc != SQLITE_DONE) { // if we failed, we log it.
> printf("Failed to step statement: %s\n", sqlite3_errmsg(db1));
>   }
>   else {
> printf("deleted all from bla successfully\n");
>   }
>   rc = sqlite3_reset(pStmt3);
>   sqlite3_exec(db1, "commit", 0, 0, 0);
>   rc = sqlite3_finalize(pStmt3);
> }
> 

Re: [sqlite] "SQL logic error or missing database"

[Jay Sprenkle]

On 5/23/06, Ran <[EMAIL PROTECTED]> wrote:

Thanks for your replies.

Actually, in my last email (probably you got it after sending yours), there
is a script which exec the create statements, and check the prepare returned
value of a SELECT (and not delete). Still I get the same bug. So the problem
must be in other place.


I checked all the return codes in the version I posted as well.

Re: [sqlite] "SQL logic error or missing database"

[Ran]

Thanks for your replies.

Actually, in my last email (probably you got it after sending yours), there
is a script which exec the create statements, and check the prepare returned
value of a SELECT (and not delete). Still I get the same bug. So the problem
must be in other place.

On 5/23/06, Robert Simpson <[EMAIL PROTECTED]> wrote:


> -Original Message-
> From: Jay Sprenkle [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 23, 2006 8:13 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] "SQL logic error or missing database"
>
> hmmm...
> it gives a schema changed because 'delete * from x' actually
> drops the table but I'm not sure why it gave an error since
> the prepare was done after the other change was committed...

sqlite3_prepare() will return a SQLITE_SCHEMA error if SQLite detects the
connection's schema is outdated.  There's arguments for and against
transparently handling it in prepare.  I figured DRH's logic behind
returning the error is maintaining consistency.  If some schema change
errors were transparently handled and some were not, then a programmer
interested in capturing all schema change events would not be able to do
so.

Robert

Re: [sqlite] Alter table to add a variable named column

[Pam Greene]

The workaround would be to build the statement some other way
(sqlite3_mprintf(), for example) for each individual ALTER TABLE command.
At that point you may want to use sqlite3_exec() instead of
sqlite3_prepare(), depending on how you'll be using the statement.  You'll
also have to be more careful about SQL injection, if the variable column
name comes from any sort of user input.

- Pam

On 5/23/06, Dennis Cote <[EMAIL PROTECTED]> wrote:


Kevin Piciulo wrote:
>  Can I add a column using a variable for the column name?  Below is
> the prepare statement, which is returning an error.
>
> sqlite3_prepare(m_dbDataBase, "ALTER TABLE users ADD COLUMN ?
> varchar;", -1, &stmt, NULL);
>
>  I'm pretty sure my syntax is correct which leads me to believe you
> cannot do this.  If that's the case is there some sort of work around?
>
Kevin,

You are correct, this is illegal. You can only use a parameter where an
"expression" is allowed in the SQL syntax. Parameters do not do string
substitution in the SQL. You can check if your SQL still makes sense by
substituting a simple sum expression for your parameter. In your case,
the following does not make sense.

  ALTER TABLE users ADD COLUMN 5+2 varchar;

HTH
Dennis Cote

[sqlite] SQLite with Eclipse BIRT?

[andreas.goetz]

This may be way off-topic (or really a BIRT bug), but I'm trying to use
eclipse BIRT to create a report off a sqlite DB:

1) add jni dll path to eclipse startup parameters
2) setup sqlite driver in BIRT data sources
3) create new report datasource pointing to existing sqlite DB and test
connection

Error:

Java.sql.SqlException. SQLite.Exception: error 21 in sqlite*_exec

I've found that:

SQLITE_MISUSE This error might occur if one or more of the SQLite API
routines is used incorrectly. Examples of incorrect usage include
calling sqlite_exec after the database has been closed using
sqlite_close or calling sqlite_exec with the same database pointer
simultaneously from two separate threads.

Any idea what I could do about it except reporting a possible BIRT
issue?

Thanks,
Andi


This message is for the designated recipient only and may contain privileged, 
proprietary, or otherwise private information.  If you have received it in 
error, please notify the sender immediately and delete the original.  Any other 
use of the email by you is prohibited.

RE: [sqlite] "SQL logic error or missing database"

[Robert Simpson]

> -Original Message-
> From: Jay Sprenkle [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, May 23, 2006 8:13 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] "SQL logic error or missing database"
> 
> hmmm...
> it gives a schema changed because 'delete * from x' actually 
> drops the table but I'm not sure why it gave an error since 
> the prepare was done after the other change was committed...

sqlite3_prepare() will return a SQLITE_SCHEMA error if SQLite detects the
connection's schema is outdated.  There's arguments for and against
transparently handling it in prepare.  I figured DRH's logic behind
returning the error is maintaining consistency.  If some schema change
errors were transparently handled and some were not, then a programmer
interested in capturing all schema change events would not be able to do so.

Robert

Re: [sqlite] Alter table to add a variable named column

[Dennis Cote]

Kevin Piciulo wrote:
 Can I add a column using a variable for the column name?  Below is 
the prepare statement, which is returning an error.


sqlite3_prepare(m_dbDataBase, "ALTER TABLE users ADD COLUMN ? 
varchar;", -1, &stmt, NULL);


 I'm pretty sure my syntax is correct which leads me to believe you 
cannot do this.  If that's the case is there some sort of work around?



Kevin,

You are correct, this is illegal. You can only use a parameter where an 
"expression" is allowed in the SQL syntax. Parameters do not do string 
substitution in the SQL. You can check if your SQL still makes sense by 
substituting a simple sum expression for your parameter. In your case, 
the following does not make sense.


 ALTER TABLE users ADD COLUMN 5+2 varchar;

HTH
Dennis Cote

RE: [sqlite] "SQL logic error or missing database"

[Robert Simpson]

> -Original Message-
> From: Ran [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, May 23, 2006 6:38 AM
> To: sqlite-users
> Subject: [sqlite] "SQL logic error or missing database"
>
[snip]
> Here is the script:
[snip]
>   rc = sqlite3_prepare(db1,// Database handle
>"create table bla(a int,b int)",
>-1, // Length of the statement
>&pStmt1,   // OUT: Statement handle
>0); // OUT: Pointer to 
> unused portion
>// of the statement
> 
>   rc = sqlite3_step(pStmt1);
[snip]
> rc = sqlite3_prepare(db2,// Database handle
>  "create table foo(c int,d int)",
>  -1, // Length of the 
> statement
>  &pStmt2,// OUT: Statement handle
>  0); // OUT: Pointer to unused
> portion
>  // of the statement
> 
> rc = sqlite3_step(pStmt2);
[snip]
>   // delete from table bla using the first connection.
>   sqlite3_exec(db1, "begin", 0, 0, 0);
>   rc = sqlite3_prepare(db1,// Database handle
>"delete from bla",
>-1, // Length of the statement
>&pStmt3,   // OUT: Statement handle
>0); // OUT: Pointer to 
> unused portion
>// of the statement
> 
>   rc = sqlite3_step(pStmt3);
[snip]


I strongly suspect the bug is related to you not checking the return code
from sqlite3_prepare().  I believe during the course of execution one of
those prepares is returning a SQLITE_SCHEMA error, at which point you need
to retry the statement.  Something like this works:

// Try 3 times to prepare the statement
int n = 0;
while (n < 3) {
  rc = sqlite3_prepare(db1,
   "delete from bla",
   -1,
&pStmt3,
0);
  if (rc != SQLITE_SCHEMA) break;
  n ++;
}

if (rc) {
  // blah blah
  exit(1);
}
//


Robert

Re: [sqlite] can you speed this query up?

[Dennis Cote]

A. Pagaltzis wrote:


I can’t interpret the `EXPLAIN` output well enough to tell
whether this is likely to be faster, I’m afraid. (Actually I
don’t even understand how to tell whether/which indices are being
used; I tried creating a few and they didn’t seem to make a
discernible difference.)

  
Try using the EXPLAIN QUERY PLAN SELECT... command instead. It will show 
the order the tables are scanned and which indexes are being used.


HTH
Dennis Cote

Re: [sqlite] "SQL logic error or missing database"

[Ran]

On 5/23/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:

hmmm...
it gives a schema changed because 'delete * from x' actually drops the

table

but I'm not sure why it gave an error since the prepare was done after the
other change was committed...
...


Thanks for your efforts!

I am afraid that the delete is not connected to the problem. Also the
prepare/step is not. Even the reset/finalize are not connected to it. The
script below gives the problem, while all the create statements are run by
sqlite3_exec (so no reset/finalize are used there at all). The problem
happens only when stepping the select. See the updated script below:

// compile with: gcc -g bug.cpp -lsqlite3 -o bug
#include 
#include 
#include 
#include 

int main(int argc, char** argv) {
 int rc;
 sqlite3* db1;
 sqlite3* db2;
 sqlite3_stmt *pStmt3;

 unlink("bug.db"); // for the test, we make sure we have a new database.

 // create first connection to the database: db1.
 rc = sqlite3_open("bug.db", &db1);
 if (rc) {
   printf("Cannot open database: %s\n", sqlite3_errmsg(db1));
   exit(1);
 }
 printf("Opened the database.\n");

 // create table bla using the first connection db1, inside a transaction.
 sqlite3_exec(db1, "begin", 0, 0, 0);
 rc = sqlite3_exec(db1, "create table bla(c int,d int)", 0, 0, 0);
 if (rc != SQLITE_OK) { // if we failed, we show it.
   printf("Failed to exec statement: %s\n", sqlite3_errmsg(db1));
 }
 sqlite3_exec(db1, "commit", 0, 0, 0); // here we commit the transaction.

 // here we, optionally, create another connection to the same database,
 // and then create other table in a transaction.
 if (argc > 1) {
   rc = sqlite3_open("bug.db", &db2); // create the second connection.
   if (rc) {
 printf("Cannot open database again: %s\n", sqlite3_errmsg(db2));
 exit(1);
   }
   else {
 printf("Opened the database.\n");
   }

   // create table foo
   sqlite3_exec(db2, "begin", 0, 0, 0); // start the transaction.
   rc = sqlite3_exec(db2, "create table foo(c int,d int)", 0, 0, 0);
   if (rc != SQLITE_OK) { // if we failed, we show it.
 printf("Failed to exec statement: %s\n", sqlite3_errmsg(db2));
   }
   sqlite3_exec(db2, "commit", 0, 0, 0);
 }

 // select from table bla using the first connection.
 sqlite3_exec(db1, "begin", 0, 0, 0);
 rc = sqlite3_prepare(db1,// Database handle
  "select * from bla",
  -1, // Length of the statement
  &pStmt3,// OUT: Statement handle
  0); // OUT: Pointer to unused portion

  // of the statement
 if (rc != SQLITE_OK) {
   printf("Failed to prepare statement: %s\n", sqlite3_errmsg(db1));
 }
 rc = sqlite3_step(pStmt3);
 if (rc != SQLITE_DONE) { // if we failed, we log it.
   printf("Failed to step statement: %s\n", sqlite3_errmsg(db1));
 }
 else {
   printf("deleted all from bla successfully\n");
 }
 rc = sqlite3_reset(pStmt3);
 sqlite3_exec(db1, "commit", 0, 0, 0);
 rc = sqlite3_finalize(pStmt3);
}

Re: [sqlite] "SQL logic error or missing database"

[Jay Sprenkle]

I don't see any reason why this should not work.
I can reproduce it with vc6. You should ask DRH
if he can explain what's up.

Re: [sqlite] How to check whether sqlite_open created the new database ?

[Dennis Cote]

kamil wrote:

I have to create a database schema in such case.

Thanks in advance,
Kamil
  

Kamil,

You could use the PRAGMA user_version command (see 
http://www.sqlite.org/pragma.html). If you set the user version when you 
initialize your schema, you can check if it has been set or not when you 
open a database.


HTH
Dennis Cote

Re: [sqlite] "SQL logic error or missing database"

[Jay Sprenkle]

hmmm...
it gives a schema changed because 'delete * from x' actually drops the table
but I'm not sure why it gave an error since the prepare was done after the
other change was committed...

program output:

Opened the database.
Opened the database.
Failed to step statement: database schema has changed
finalize failed: database schema has changed

program code:

#include 
#include 
//#include 
#include 

int main(int argc, char** argv)
  {
int rc;
sqlite3* db1;
sqlite3* db2;
sqlite3_stmt *pStmt1;
sqlite3_stmt *pStmt2;
sqlite3_stmt *pStmt3;

unlink("bug.db"); // for the test, we make sure we have a new database.

// create first connection to the database: db1.
rc = sqlite3_open("bug.db", &db1);
if (rc)
   {
  printf("Cannot open database: %s\n", sqlite3_errmsg(db1));
  exit(1);
}
printf("Opened the database.\n");

// create table bla using the first connection db1, inside a transaction.
rc = sqlite3_exec(db1, "begin", 0, 0, 0);
if (rc != SQLITE_OK)
  {
 printf("begin failed: %s\n", sqlite3_errmsg(db1));
 exit(1);
  }

rc = sqlite3_prepare(db1,// Database handle
 "create table bla(a int,b int)",
 -1, // Length of the statement
 &pStmt1,   // OUT: Statement handle
 0); // OUT: Pointer to unused portion
 // of the statement
if (rc != SQLITE_OK)
  {
 printf("prepare failed: %s\n", sqlite3_errmsg(db1));
 exit(1);
  }

rc = sqlite3_step(pStmt1);
if (rc != SQLITE_DONE)
   { // if we failed, we show it.
 printf("Failed to step statement: %s\n", sqlite3_errmsg(db1));
  }

rc = sqlite3_finalize(pStmt1);
if (rc != SQLITE_OK)
  {
 printf("finalize failed: %s\n", sqlite3_errmsg(db1));
 exit(1);
  }

sqlite3_exec(db1, "commit", 0, 0, 0); // here we commit the transaction.
if (rc != SQLITE_OK)
  {
 printf("commit failed: %s\n", sqlite3_errmsg(db1));
 exit(1);
  }

// now we suppose to have inside the database the table bla.

// here we, optionally, create another connection to the same database,
// and then create other table in a transaction.
  rc = sqlite3_open("bug.db", &db2); // create the second connection.
  if (rc) {
printf("Cannot open database again: %s\n", sqlite3_errmsg(db2));
exit(1);
  }

  printf("Opened the database.\n");

  // create table foo
  rc = sqlite3_exec(db2, "begin", 0, 0, 0); // start the transaction.
if (rc != SQLITE_OK)
  {
 printf("begin failed: %s\n", sqlite3_errmsg(db2));
 exit(1);
  }

rc = sqlite3_prepare(db2,// Database handle
   "create table foo(c int,d int)",
   -1, // Length of the statement
   &pStmt2,// OUT: Statement handle
   0); // OUT: Pointer to unused portion
if (rc != SQLITE_OK)
  {
 printf("prepare failed: %s\n", sqlite3_errmsg(db2));
 exit(1);
  }


  rc = sqlite3_step(pStmt2);
  if (rc != SQLITE_DONE) { // if we failed, we show it.
printf("Failed to step statement: %s\n", sqlite3_errmsg(db2));
  }

  rc = sqlite3_finalize(pStmt2);
if (rc != SQLITE_OK)
  {
 printf("finalize failed: %s\n", sqlite3_errmsg(db2));
 exit(1);
  }

sqlite3_exec(db2, "commit", 0, 0, 0);
if (rc != SQLITE_OK)
  {
 printf("commit failed: %s\n", sqlite3_errmsg(db2));
 exit(1);
  }

// delete from table bla using the first connection.
sqlite3_exec(db1, "begin", 0, 0, 0);
if (rc != SQLITE_OK)
  {
 printf("begin failed: %s\n", sqlite3_errmsg(db1));
 exit(1);
  }

rc = sqlite3_prepare(db1,// Database handle
 "delete from bla",
 -1, // Length of the statement
 &pStmt3,   // OUT: Statement handle
 0); // OUT: Pointer to unused portion
 // of the statement
if (rc != SQLITE_OK)
  {
 printf("prepare failed: %s\n", sqlite3_errmsg(db1));
 exit(1);
  }

rc = sqlite3_step(pStmt3);
if (rc != SQLITE_DONE) { // if we failed, we log it.
  printf("Failed to step statement: %s\n", sqlite3_errmsg(db1));
}
else {
  printf("deleted all from bla successfully\n");
}

rc = sqlite3_finalize(pStmt3);
if (rc != SQLITE_OK)
  {
 printf("finalize failed: %s\n", sqlite3_errmsg(db1));
 exit(1);
  }

sqlite3_exec(db1, "commit", 0, 0, 0);
if (rc != SQLITE_OK)
  {
 printf("commit failed: %s\n", sqlite3_errmsg(db1));
 exit(1);
  }

return 0;
}

Re: [sqlite] scary warnings

[Jay Sprenkle]

On 5/23/06, Craig Morrison <[EMAIL PROTECTED]> wrote:

[EMAIL PROTECTED] wrote:
> If you do not care to see the warnings, there is probably
> some way to turn them off in your compiler.

To the OP:

In VC7 and above.. Open the project settings for your project, navigate
to: Configuration Properties -> C/C++ -> Disable Specific Warnings. Plug
in the warning number(s) you don't want to see.


In vc6 :
#pragma warning( disable : 4786 4503 )

Re: [sqlite] scary warnings

[Craig Morrison]

[EMAIL PROTECTED] wrote:

If you do not care to see the warnings, there is probably
some way to turn them off in your compiler.


To the OP:

In VC7 and above.. Open the project settings for your project, navigate 
to: Configuration Properties -> C/C++ -> Disable Specific Warnings. Plug 
in the warning number(s) you don't want to see.


--
Craig Morrison
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
http://pse.2cah.com
  Controlling pseudoephedrine purchases.

http://www.mtsprofessional.com/
  A Win32 email server that works for You.

Re: [sqlite] "SQL logic error or missing database"

[Ran]

Thanks for your answer.

Actually, I tried to have resets there although I think that finalize is as
good in releasing the locks on tables. It didn't help.

I do it with prepare and step because this script is a demo of a bug I have
in my code, where I use all over a certain function that prepare and step.
It is true that here I could use also exec.

However, if I change the script to have the first two create statements run
by sqlite3_exec, and change the last statement to a select (to justify the
use of prepare) I still get the "SQL logic error or missing database". So I
suspect that the problem is because I use two open connections to the
database (again, this is a demo of the bug which happen in much bigger
application where opening two connections to the same database make sense),
and somehow when I create a second table with the second connection it
locks/invalidate or whatever the first connection and I get this error. I
even checked the code with valgrind, but got nothing...

Any help will be appreciated.

Ran

On 5/23/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:


On 5/23/06, Ran <[EMAIL PROTECTED]> wrote:
> Hi all,
>
>
>   rc = sqlite3_prepare(db1,// Database handle
>"create table bla(a int,b int)",
>-1, // Length of the statement
>&pStmt1,   // OUT: Statement handle
>0); // OUT: Pointer to unused
portion
>// of the statement
>
>   rc = sqlite3_step(pStmt1);
>   if (rc != SQLITE_DONE) { // if we failed, we show it.
> printf("Failed to step statement: %s\n", sqlite3_errmsg(db1));
>   }
>   rc = sqlite3_finalize(pStmt1);
>   sqlite3_exec(db1, "commit", 0, 0, 0); // here we commit the
transaction.

You forgot the reset here:
int sqlite3_reset(sqlite3_stmt *pStmt);
http://sqlite.org/capi3ref.html#sqlite3_reset

Why are you preparing this statement?
Just sqlite3_exec() it.

>
>   // now we suppose to have inside the database the table bla.
>
>   // here we, optionally, create another connection to the same
database,
>   // and then create other table in a transaction.
>   if (argc > 1) {
> rc = sqlite3_open("bug.db", &db2); // create the second connection.
> if (rc) {
>   printf("Cannot open database again: %s\n", sqlite3_errmsg(db2));
>   exit(1);
> }
> else {
>   printf("Opened the database.\n");
> }

You still have a valid handle to the open database. Why create a second
one?


>
> // create table foo
> sqlite3_exec(db2, "begin", 0, 0, 0); // start the transaction.
> rc = sqlite3_prepare(db2,// Database handle
>  "create table foo(c int,d int)",
>  -1, // Length of the statement
>  &pStmt2,// OUT: Statement handle
>  0); // OUT: Pointer to unused
> portion
>  // of the statement
>
> rc = sqlite3_step(pStmt2);
> if (rc != SQLITE_DONE) { // if we failed, we show it.
>   printf("Failed to step statement: %s\n", sqlite3_errmsg(db2));
> }
> rc = sqlite3_finalize(pStmt2);
> sqlite3_exec(db2, "commit", 0, 0, 0);
>   }
>
>   // delete from table bla using the first connection.
>   sqlite3_exec(db1, "begin", 0, 0, 0);
>   rc = sqlite3_prepare(db1,// Database handle
>"delete from bla",
>-1, // Length of the statement
>&pStmt3,   // OUT: Statement handle
>0); // OUT: Pointer to unused
portion
>// of the statement
>
>   rc = sqlite3_step(pStmt3);
>   if (rc != SQLITE_DONE) { // if we failed, we log it.
> printf("Failed to step statement: %s\n", sqlite3_errmsg(db1));
>   }
>   else {
> printf("deleted all from bla successfully\n");
>   }
>   rc = sqlite3_finalize(pStmt3);
>   sqlite3_exec(db1, "commit", 0, 0, 0);
> }

Again, why prepare something that returns no results and will
not be used more than once?

Re: [sqlite] can you speed this query up?

[Adrian Ho]

On Tue, May 23, 2006 at 08:50:56AM +0200, A. Pagaltzis wrote:
> * [EMAIL PROTECTED] <[EMAIL PROTECTED]> [2006-05-23 02:35]:
> > What you have to do is:
> > 
> >SELECT qi, ri, drl, score
> >  FROM ...
> > WHERE score=(SELECT max(score) FROM ...)
> 
> Actually, in cases such as this, the easiest approach is to use
> `LIMIT`:
> 
> SELECT qi, ri, drl, score
> FROM ...
> WHERE ...
> ORDER BY score DESC
> LIMIT 1

Only if "cases such as this" is defined as "datasets where only one record
has the maximum score" (which may be the case that Brannon presented -- I
don't recall offhand).  Otherwise, the two queries above are semantically
different and should reasonably be expected to return different results.

- Adrian

[sqlite] Alter table to add a variable named column

[Kevin Piciulo]

 I asked a similar question to this about accessing columns using a 
variable name, and the answer was you cannot.  Sadly I cannot find the 
email explaining why so I'll ask this similar question:


 Can I add a column using a variable for the column name?  Below is the 
prepare statement, which is returning an error.


sqlite3_prepare(m_dbDataBase, "ALTER TABLE users ADD COLUMN ? 
varchar;", -1, &stmt, NULL);


 I'm pretty sure my syntax is correct which leads me to believe you 
cannot do this.  If that's the case is there some sort of work around?


 Thanks in advance for any help.
  Kevin Piciulo

Re: [sqlite] "SQL logic error or missing database"

[Jay Sprenkle]

On 5/23/06, Ran <[EMAIL PROTECTED]> wrote:

Hi all,


  rc = sqlite3_prepare(db1,// Database handle
   "create table bla(a int,b int)",
   -1, // Length of the statement
   &pStmt1,   // OUT: Statement handle
   0); // OUT: Pointer to unused portion
   // of the statement

  rc = sqlite3_step(pStmt1);
  if (rc != SQLITE_DONE) { // if we failed, we show it.
printf("Failed to step statement: %s\n", sqlite3_errmsg(db1));
  }
  rc = sqlite3_finalize(pStmt1);
  sqlite3_exec(db1, "commit", 0, 0, 0); // here we commit the transaction.


You forgot the reset here:
int sqlite3_reset(sqlite3_stmt *pStmt);
http://sqlite.org/capi3ref.html#sqlite3_reset

Why are you preparing this statement?
Just sqlite3_exec() it.



  // now we suppose to have inside the database the table bla.

  // here we, optionally, create another connection to the same database,
  // and then create other table in a transaction.
  if (argc > 1) {
rc = sqlite3_open("bug.db", &db2); // create the second connection.
if (rc) {
  printf("Cannot open database again: %s\n", sqlite3_errmsg(db2));
  exit(1);
}
else {
  printf("Opened the database.\n");
}


You still have a valid handle to the open database. Why create a second one?




// create table foo
sqlite3_exec(db2, "begin", 0, 0, 0); // start the transaction.
rc = sqlite3_prepare(db2,// Database handle
 "create table foo(c int,d int)",
 -1, // Length of the statement
 &pStmt2,// OUT: Statement handle
 0); // OUT: Pointer to unused
portion
 // of the statement

rc = sqlite3_step(pStmt2);
if (rc != SQLITE_DONE) { // if we failed, we show it.
  printf("Failed to step statement: %s\n", sqlite3_errmsg(db2));
}
rc = sqlite3_finalize(pStmt2);
sqlite3_exec(db2, "commit", 0, 0, 0);
  }

  // delete from table bla using the first connection.
  sqlite3_exec(db1, "begin", 0, 0, 0);
  rc = sqlite3_prepare(db1,// Database handle
   "delete from bla",
   -1, // Length of the statement
   &pStmt3,   // OUT: Statement handle
   0); // OUT: Pointer to unused portion
   // of the statement

  rc = sqlite3_step(pStmt3);
  if (rc != SQLITE_DONE) { // if we failed, we log it.
printf("Failed to step statement: %s\n", sqlite3_errmsg(db1));
  }
  else {
printf("deleted all from bla successfully\n");
  }
  rc = sqlite3_finalize(pStmt3);
  sqlite3_exec(db1, "commit", 0, 0, 0);
}


Again, why prepare something that returns no results and will
not be used more than once?

[sqlite] "SQL logic error or missing database"

[Ran]

Hi all,

Could someone help me with the script below? I get an "SQL logic error or
missing database" and cannot find what I do wrong.
I use sqlite 3.3.4 on Linux.

What I do there is:
1. Open connection to a new database.
2. Create table bla in a transaction.
3. Open another connection to the database.
4. Create table foo in a transaction using the second connection.
5. Try to delete from the table bla using the first connection. ==> this
gives "SQL logic error or missing database".

Here is how it runs (without arguments, the second connection is not opened,
and the table foo is not created - and this runs OK):

./bug
Opened the database.
deleted all from bla successfully
./bug 1
Opened the database.
Opened the database.
Failed to step statement: SQL logic error or missing database

Here is the script:

// compile with: gcc -g bug.cpp -lsqlite3 -o bug
#include 
#include 
#include 
#include 

int main(int argc, char** argv) {
 int rc;
 sqlite3* db1;
 sqlite3* db2;
 sqlite3_stmt *pStmt1;
 sqlite3_stmt *pStmt2;
 sqlite3_stmt *pStmt3;

 unlink("bug.db"); // for the test, we make sure we have a new database.

 // create first connection to the database: db1.
 rc = sqlite3_open("bug.db", &db1);
 if (rc) {
   printf("Cannot open database: %s\n", sqlite3_errmsg(db1));
   exit(1);
 }
 printf("Opened the database.\n");

 // create table bla using the first connection db1, inside a transaction.
 sqlite3_exec(db1, "begin", 0, 0, 0);
 rc = sqlite3_prepare(db1,// Database handle
  "create table bla(a int,b int)",
  -1, // Length of the statement
  &pStmt1,   // OUT: Statement handle
  0); // OUT: Pointer to unused portion
  // of the statement

 rc = sqlite3_step(pStmt1);
 if (rc != SQLITE_DONE) { // if we failed, we show it.
   printf("Failed to step statement: %s\n", sqlite3_errmsg(db1));
 }
 rc = sqlite3_finalize(pStmt1);
 sqlite3_exec(db1, "commit", 0, 0, 0); // here we commit the transaction.

 // now we suppose to have inside the database the table bla.

 // here we, optionally, create another connection to the same database,
 // and then create other table in a transaction.
 if (argc > 1) {
   rc = sqlite3_open("bug.db", &db2); // create the second connection.
   if (rc) {
 printf("Cannot open database again: %s\n", sqlite3_errmsg(db2));
 exit(1);
   }
   else {
 printf("Opened the database.\n");
   }

   // create table foo
   sqlite3_exec(db2, "begin", 0, 0, 0); // start the transaction.
   rc = sqlite3_prepare(db2,// Database handle
"create table foo(c int,d int)",
-1, // Length of the statement
&pStmt2,// OUT: Statement handle
0); // OUT: Pointer to unused
portion
// of the statement

   rc = sqlite3_step(pStmt2);
   if (rc != SQLITE_DONE) { // if we failed, we show it.
 printf("Failed to step statement: %s\n", sqlite3_errmsg(db2));
   }
   rc = sqlite3_finalize(pStmt2);
   sqlite3_exec(db2, "commit", 0, 0, 0);
 }

 // delete from table bla using the first connection.
 sqlite3_exec(db1, "begin", 0, 0, 0);
 rc = sqlite3_prepare(db1,// Database handle
  "delete from bla",
  -1, // Length of the statement
  &pStmt3,   // OUT: Statement handle
  0); // OUT: Pointer to unused portion
  // of the statement

 rc = sqlite3_step(pStmt3);
 if (rc != SQLITE_DONE) { // if we failed, we log it.
   printf("Failed to step statement: %s\n", sqlite3_errmsg(db1));
 }
 else {
   printf("deleted all from bla successfully\n");
 }
 rc = sqlite3_finalize(pStmt3);
 sqlite3_exec(db1, "commit", 0, 0, 0);
}

Re: [sqlite] High retrieval time. Please help

[Jay Sprenkle]

On 5/23/06, Anish Enos Mathew <[EMAIL PROTECTED]> wrote:


Hi Michael,
   I am retrieving records from the data base randomly. I want
to perform 1,000,000 retrieval of 1,000,000 random records from the data
base. When I am retrieving 15 bytes records from the data base, it works
fine. It just takes around 20 seconds for 1,000,000 retrievals. But when
I am trying to retrieve 1k records, it's taking too long a time. Around
100 sec for just 50,000 retrievals. Also I am not querying the data
using ROWID. I want to reduce the retrieval time without increasing the
memory of the system. How to do that. As you told me in the previous
mail, there must be lot of IO calls since the whole database doesn't fit
into memory. Is there any way out to solve this problem ???


Add a new numeric column to the table (lets call it "X").
Set column X to a random number for every row.
Create an index on column X.
Then you can use this sql to retrieve the records:
select * from mytable order by X limit 100

RE: [sqlite] High retrieval time. Please help

[Anish Enos Mathew]

Hi Michael,
   I am retrieving records from the data base randomly. I want
to perform 1,000,000 retrieval of 1,000,000 random records from the data
base. When I am retrieving 15 bytes records from the data base, it works
fine. It just takes around 20 seconds for 1,000,000 retrievals. But when
I am trying to retrieve 1k records, it's taking too long a time. Around
100 sec for just 50,000 retrievals. Also I am not querying the data
using ROWID. I want to reduce the retrieval time without increasing the
memory of the system. How to do that. As you told me in the previous
mail, there must be lot of IO calls since the whole database doesn't fit
into memory. Is there any way out to solve this problem ???

-Original Message-
From: Michael Sizaki [mailto:[EMAIL PROTECTED]
Sent: Monday, May 22, 2006 6:50 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] High retrieval time. Please help

Hi Anish,

when a database hits the disk, there's not much you can
do about. You can increase the memory of your system, so
that the entire database fits into memory. If the database
is "cold" (the system has started and the database is not
in the file system cache), you can read the entire database
file once using to get it into the cache. Unfortunately,
this does not help much, if the database is too big to fit
into memory. Another trick that could work in some cases:
if you know you have 50.000 requests and you know the order
in which the data is in the database, you can sort the requests
before you access the database in the order in which they are physically
stored in the database. Normally you would not know the exact order,
but if you do a VACUUM on the database you know that the data
is ordered in order of ROWID. But that works only in some cases,
where you query the data by ROWID.

I general, sqlite is great as long as all data fits into
memory. I have no comparison with other database systems,
how they perform when you hit the disk.

Michael


Anish Enos Mathew wrote:
> Hi Michael,
>I came to know that increasing the page size would help in
> better performance. So I used PRAGMA and set the page size to 32768
> using the command,
>   sqlite3_exec (db, "PRAGMA page_size = 32768", NULL, NULL,
> NULL);
>
> Still result is the same. Taking a time of 110 sec for 50,000
> retrievals. Can u suggest me a method by which the performance of
> retrieval can be increased.
>
> -Original Message-
> From: Michael Sizaki [mailto:[EMAIL PROTECTED]
>
> Sent: Saturday, May 20, 2006 12:00 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] High retrieval time. Please help
>
> Anish,
>
>> So my problem of retrieving 1,000,000 random records 1,000,000 times
>> works fine for 15 bytes. But it is taking too long a time for 1k
>> records. It is almost taking 102 seconds for retrieving 50,000
records
>> of size 1k. Can u suggest me a way for reducing the time taken for
the
>> same? I have'nt done any changes in my program. The only change I
made
>> was adding primary key to the seq_number column in my data insertion
>> program.
>
> My guess is that with 15 byte records you operating system keeps the
> entire database in the file cache and no real IO is done. When you use
> the 1k records, the entire database does not fit into memory anymore,
> and therefore real IO is done. That slows database access dramatically
> down. I guess if you watch the CPU usage in the 15 byte case, its
close
> to 100% and in the 1k case it's very low (an you have a lot of disc
> access).
>
> Michael
>
>
> The information contained in, or attached to, this e-mail, contains
confidential information and is intended solely for the use of the
individual or entity to whom they are addressed and is subject to legal
privilege. If you have received this e-mail in error you should notify
the sender immediately by reply e-mail, delete the message from your
system and notify your system manager. Please do not copy it for any
purpose, or disclose its contents to any other person. The views or
opinions presented in this e-mail are solely those of the author and do
not necessarily represent those of the company. The recipient should
check this e-mail and any attachments for the presence of viruses. The
company accepts no liability for any damage caused, directly or
indirectly, by any virus transmitted in this email.
>
> www.aztecsoft.com
>
>



The information contained in, or attached to, this e-mail, contains 
confidential information and is intended solely for the use of the individual 
or entity to whom they are addressed and is subject to legal privilege. If you 
have received this e-mail in error you should notify the sender immediately by 
reply e-mail, delete the message from your system and notify your system 
manager. Please do not copy it for any purpose, or disclose its contents to any 
other person. The views or opinions presented in this e-mail are solely those 
of the author and do not necessarily represent those of th

Re: [sqlite] scary warnings

[drh]

"Cory Nelson" <[EMAIL PROTECTED]> wrote:
> 
> To get back on track:  Brannon, submit a patch.  An alarmingly high
> number of people believe those warnings are superfluous and from what
> I understand drh is one of them, so I imagine a patch is the only way
> proper casting will be put in.
> 

Even a patch is unlikely to result in a change.  I disapprove
of cluttering up source code with lots of explicit casts that
serve no perpose other than silencing superfluous compiler
warnings.  Explicit casts clutter the code making subsequent
changes more difficult and masking real errors.

If you do not care to see the warnings, there is probably
some way to turn them off in your compiler.

The code quality of SQLite is maintained by full-coverage
testing, which is a far superior method of finding errors
than looking at compiler warnings.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] can you speed this query up?

[A. Pagaltzis]

* Brannon King <[EMAIL PROTECTED]> [2006-05-23 01:40]:
> It seems that I yet need help with another query. This one is just too slow.
> I've included the "explain" and the table schema. I've been using the
> prepare/step model directly. What should I change on my indexing to make it
> faster?
> 
> The schema:
> CREATE TEMPORARY TABLE IF NOT EXISTS bounds (bi INTEGER PRIMARY KEY
> AUTOINCREMENT DEFAULT NULL, bqi INTEGER, bri INTEGER, bqis INTEGER, bris
> INTEGER);
> CREATE UNIQUE INDEX IF NOT EXISTS qisris ON bounds (bqis, bris);
> CREATE UNIQUE INDEX IF NOT EXISTS qiri ON bounds (bqi, bri);
> insert into bounds values(NULL,1,1,5880,5880);
> CREATE TABLE results_1 (qi INTEGER, ri INTEGER, drl INTEGER, score INTEGER,
> qis INTEGER, ris INTEGER);
> CREATE UNIQUE INDEX loc_1 ON results_1 (qi,ri); 
> 
> The queries (both of these run slow but I care about the second):
> "select count(*) from results_1 where qi = 5604 OR ri = 5468;"
> returns 102
> 
> So you can see the following query should only be doing a max over a 102
> pieces; that's not very many. 
> 
> "explain 
> select qi,ri,drl,max(score) as scr from results_1, bounds where (qi = 5604
> OR ri = 5468) AND (qi >= bqis
> AND qi <= 5604) AND (ri >= bris AND ri <= 5468) AND bi = 1 and qis = bqis
> AND ris = bris;"

You know, it would really, *REALLY* help someone else to figure
out what you’re doing it if you use table aliases everywhere so
one doesn’t need to crossreference the schema constantly when
trying to read the query in order to know what’s coming from
where. Together with the correction of your use of the aggregate
function as pointed out elsewhere, your query is as follows:

SELECT
r.qi,
r.ri,
r.drl,
r.score
FROM
results_1 r
INNER JOIN bounds b ON
r.qis = b.bqis AND r.ris = b.bris
WHERE
(r.qi = 5604 OR r.ri = 5468)
AND (r.qi >= b.bqis AND r.qi <= 5604)
AND (r.ri >= b.bris AND r.ri <= 5468)
AND b.bi = 1
GROUP BY
r.score DESC
LIMIT 1

Now I realise after the fact that all your columns from `bounds`
start with `b`, but I had to unravel the query in order to pick
up on that.

It also makes obvious that your query returns no columns from
the `bounds` table, it just uses them to constrain the result
set. In that case, you might get better performance by checking a
correlated subquery with `EXISTS`.

Let’s see. Most of those parentheses in the `WHERE` clause are
unnecessary:

(r.qi = 5604 OR r.ri = 5468)
AND r.qi <= 5604
AND r.ri <= 5468
AND r.qi >= b.bqis
AND r.ri >= b.bris
AND b.bi = 1

Now we can group together the conditions which do not involve the
`bounds` table:

(r.qi = 5604 AND r.ri <= 5468) OR (r.ri = 5468 AND r.qi <= 5604)
AND r.qi >= b.bqis
AND r.ri >= b.bris
AND b.bi = 1

The rest can then be factored into a subquery:

SELECT
r.qi,
r.ri,
r.drl,
r.score
FROM
results_1 r
WHERE
(r.qi = 5604 AND r.ri <= 5468) OR (r.ri = 5468 AND r.qi <= 5604)
AND EXISTS (
SELECT
NULL
FROM
bounds b
WHERE
b.bi = 1
AND b.bqis = r.qis
AND b.bris = r.ris
AND b.bqis <= r.qi
AND b.bris <= r.ri
)
ORDER BY
r.score DESC
LIMIT 1

I can’t interpret the `EXPLAIN` output well enough to tell
whether this is likely to be faster, I’m afraid. (Actually I
don’t even understand how to tell whether/which indices are being
used; I tried creating a few and they didn’t seem to make a
discernible difference.)

Regards,
-- 
Aristotle Pagaltzis //