Re: [sqlite] Problem with SQLite Pecl extension for PHP4

[Chris Peachment]

There was a change of file structure with version 3.x.x
and this is described in the documentation under Version 3
Overview.

You are attempting to use Sqlite3 on a version 2.8.x database
and the formats are not compatible.


On Wed, 9 May 2007 19:42:06 -0400, Brandon Eley wrote:

>I've gone through a few tutorials and can't seem to get this right.  
>I've had my web host install the PECL extension and the following is  
>what is in my phpinfo();

>sqlite
>SQLite support enabled
>PECL Module version1.0.3 $Id: sqlite.c,v 1.62.2.25 2004/07/10  
>12:25:33 wez Exp $
>SQLite Library 2.8.14
>SQLite Encodingiso8859

>Directive  Local Value Master Value
>sqlite.assoc_case  0   0

>***

>This is the PHP code I'm using:

>$db = sqlite_open("/home/xxx/data/xxx.db");
>?>

>***

>This is the error I'm getting:

>Warning: sqlite_open() [function.sqlite-open]: file is encrypted or  
>is not a database in /home/xxx/public_html/test.php on line 140

>***

>What am I doing wrong? I can run queries all day long using the  
>command line sqlite3 but I can't seem to get PHP to read the stinkin  
>database!

>Any help would be GREATLY appreciated!

>Brandon Eley
>[EMAIL PROTECTED]








-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] sqlite3_step error code confusion

[Emerson Clarke]

Ive been getting SQLITE_MISUSE errors after calling sqlite3_step one
more time than required.  Im using sqlite3_prepare_v2 so im expecting
detailed error messages, but when i call sqlite3_errmsg all i seem to
get is "not an error".

So my question is this.  What is an error ?  It seems a little
confusing becuase there is a SQLITE_ERROR code, and also a number of
other detailed codes which i assume represent forms of errors.

The old version of sqlite3_step used to return SQLITE_ERROR, and then
you would call sqlite3_reset to get the detailed error code.  This
implies that the other codes are in fact errors.  Am i correct in
assuming that SQLITE_MISUSE is a form of error ?  Is there some subset
of the error codes which work with sqlite3_errmsg ?

I would have expected the error message that i got for SQLITE_MISUSE
to be "library used incorectly" or some string other than "not an
error".

Emerson

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Problem with SQLite Pecl extension for PHP4

[Jon Scully]

What user is httpd running as? Enter:

   ps aux | grep -v grep | grep httpd

Does that user (typically 'nobody') have permission to access the file
at your: /home/xxx/data/xxx.db ?

On 5/9/07, Brandon Eley <[EMAIL PROTECTED]> wrote:

I've gone through a few tutorials and can't seem to get this right.
I've had my web host install the PECL extension and the following is
what is in my phpinfo();

sqlite
SQLite support  enabled
PECL Module version 1.0.3 $Id: sqlite.c,v 1.62.2.25 2004/07/10
12:25:33 wez Exp $
SQLite Library  2.8.14
SQLite Encoding iso8859

Directive   Local Value Master Value
sqlite.assoc_case   0   0

***

This is the PHP code I'm using:



***

This is the error I'm getting:

Warning: sqlite_open() [function.sqlite-open]: file is encrypted or
is not a database in /home/xxx/public_html/test.php on line 140

***

What am I doing wrong? I can run queries all day long using the
command line sqlite3 but I can't seem to get PHP to read the stinkin
database!

Any help would be GREATLY appreciated!

Brandon Eley
[EMAIL PROTECTED]






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] ATTACH and sqlite3_open()

[Jon Scully]

That worked as hoped. (Used your sqlite3_exec() version.)  Thanks.

On 5/9/07, Michael Ruck <[EMAIL PROTECTED]> wrote:

Just call sqlite3_exec with the proper ATTACH as you would on the command
line. (Of course you could also do a prepare/step/finalize, but for ATTACH
sqlite3_exec is enough.)

Example:

sqlite3 *db = NULL;
/* ... */
sqlite3_exec(db, "ATTACH DATABASE 'filename' AS dbname", NULL, NULL,
);

Using SELECT sqlite_attach('filename','dbname',NULL) should also work -
haven't tried it though. This should have the benefit that the arguments can
be bound and the attach statement can be prepared. I'm not certain this is
possible with the ATTACH syntax. I'll try it soon though.

Mike

-Ursprüngliche Nachricht-
Von: Jon Scully [mailto:[EMAIL PROTECTED]
Gesendet: Donnerstag, 10. Mai 2007 01:18
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] ATTACH and sqlite3_open()

Simpler than that.  I merely want to attach to two databases (files).
flash.db contains a set of tables that hold non-volatile data; ram.db
contains a set of tables that is re-built (volatile) on re-boot -- but
offers fast, read-only access.  No table-name overlaps, of course.

I want to access both sets of tables, seamlessly, as I should be able to do
using ATTACH, at the command prompt, but do so using the C API.

Just wondering how others do this (Using ATTACH?  Using sqlite3_open()?
Obviously I haven't looked very far into the
sqlite3_open() code to see how it's put together, etc.).

Thanks for the prompt reply.

On 5/9/07, Andrew Finkenstadt <[EMAIL PROTECTED]> wrote:
> On further inspection of your code fragment, it appears you aren't
> really using (extra) attached  databases, but merely specifying an
> alternative file to use if the first file is not available.  Calling
> sqlite3_close(...) will do the right thing, by closing the actual
> database that succeeded in opening.
>
> --andy


-
To unsubscribe, send email to [EMAIL PROTECTED]

-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] A suggestion

[John Stanton]

Rich Shepard wrote:

On Wed, 9 May 2007, John Stanton wrote:

That program does have the capability, but may not be implemented that 
way on Windows.  Why not make the change yourself?


A.J.Millan wrote:

As a suggestion, and even in the risk to abuse of Mr Hipp's patience. 
Would

it be possible to include in the command-line program (sqlite3.exe) the
ability to edit, an repeat at least the five or six last commands, as in
Linux?. Is to say with up-arrow and down-arrow.  I believe it would 
be too

helpful.



  That, I believe, is a function of the shell, not the application using 
it.

For example, the command history available in bash is not present -- at
least, was not present -- in sh. Command history, IIRC, was a feature of 
the

csh, and bash (the Bourne Again Shell) took the best features of sh, csh,
and the Textronics version tcsh.

  Years ago, about a decade, when I had PC DOS 7.0 installed along with
linux, I discovered that the DOS shell allowed tab completions. I don't
recall if it had the history recall, too.

Rich


It would be a function of "readline".  I have not looked closely but I
would imagine that to implement or emulate readline on Windows would
achieve the function.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Longest "real" SQL statement

[drh]

Dave Dyer <[EMAIL PROTECTED]> wrote:
> I've recently been storing images in Sqlite datbases, and
> for various reasons don't want to use the normal "blob"
> mechanisms.  Consequently, a 10mb JPG image file would be
> encoded as slightly larger than 10mb sqlite "insert xx" command.
> 

The use of sqlite3_bind_blob() for this is MUCH faster, and
surely requires less code.  May I ask for more detail on why 
you chose to generate SQL statements with huge blob literals?

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


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Longest "real" SQL statement

[Dave Dyer]

I've recently been storing images in Sqlite datbases, and
for various reasons don't want to use the normal "blob"
mechanisms.  Consequently, a 10mb JPG image file would be
encoded as slightly larger than 10mb sqlite "insert xx" command.

The size of the commands required by this method is limited only by the
size of images I think is reasonable to store as pseudo-blobs.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Problem with SQLite Pecl extension for PHP4

[Brandon Eley]

I've gone through a few tutorials and can't seem to get this right.  
I've had my web host install the PECL extension and the following is  
what is in my phpinfo();


sqlite
SQLite support  enabled
PECL Module version 	1.0.3 $Id: sqlite.c,v 1.62.2.25 2004/07/10  
12:25:33 wez Exp $

SQLite Library  2.8.14
SQLite Encoding iso8859

Directive   Local Value Master Value
sqlite.assoc_case   0   0

***

This is the PHP code I'm using:



***

This is the error I'm getting:

Warning: sqlite_open() [function.sqlite-open]: file is encrypted or  
is not a database in /home/xxx/public_html/test.php on line 140


***

What am I doing wrong? I can run queries all day long using the  
command line sqlite3 but I can't seem to get PHP to read the stinkin  
database!


Any help would be GREATLY appreciated!

Brandon Eley
[EMAIL PROTECTED]

[sqlite] Longest "real" SQL statement

[drh]

I'm looking for an upper bound on how big legitimate 
SQL statements handed to SQLite get to be.  I'm not
interested in contrived examples.  I want to see
really big SQL statements that are actually used in
real programs.

"Big" can be defined in several ways:

*  Number of bytes of text in the SQL statement.
*  Number of tokens in the SQL statement
*  Number of result columns in a SELECT
*  Number of terms in an expression

If you are using really big SQL statements, please
tell me about them.  I'd like to see the actual
SQL text if possible.  But if your use is proprietary,
please at least tell me how big your query is in
bytes or tokens or columns or expression terms.

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


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

AW: [sqlite] ATTACH and sqlite3_open()

[Michael Ruck]

Just call sqlite3_exec with the proper ATTACH as you would on the command
line. (Of course you could also do a prepare/step/finalize, but for ATTACH
sqlite3_exec is enough.)

Example:

sqlite3 *db = NULL;
/* ... */
sqlite3_exec(db, "ATTACH DATABASE 'filename' AS dbname", NULL, NULL,
);

Using SELECT sqlite_attach('filename','dbname',NULL) should also work -
haven't tried it though. This should have the benefit that the arguments can
be bound and the attach statement can be prepared. I'm not certain this is
possible with the ATTACH syntax. I'll try it soon though.

Mike

-Ursprüngliche Nachricht-
Von: Jon Scully [mailto:[EMAIL PROTECTED] 
Gesendet: Donnerstag, 10. Mai 2007 01:18
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] ATTACH and sqlite3_open()

Simpler than that.  I merely want to attach to two databases (files).
flash.db contains a set of tables that hold non-volatile data; ram.db
contains a set of tables that is re-built (volatile) on re-boot -- but
offers fast, read-only access.  No table-name overlaps, of course.

I want to access both sets of tables, seamlessly, as I should be able to do
using ATTACH, at the command prompt, but do so using the C API.

Just wondering how others do this (Using ATTACH?  Using sqlite3_open()?
Obviously I haven't looked very far into the
sqlite3_open() code to see how it's put together, etc.).

Thanks for the prompt reply.

On 5/9/07, Andrew Finkenstadt <[EMAIL PROTECTED]> wrote:
> On further inspection of your code fragment, it appears you aren't 
> really using (extra) attached  databases, but merely specifying an 
> alternative file to use if the first file is not available.  Calling 
> sqlite3_close(...) will do the right thing, by closing the actual 
> database that succeeded in opening.
>
> --andy


-
To unsubscribe, send email to [EMAIL PROTECTED]

-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] ATTACH and sqlite3_open()

[Jon Scully]

Simpler than that.  I merely want to attach to two databases (files).
flash.db contains a set of tables that hold non-volatile data; ram.db
contains a set of tables that is re-built (volatile) on re-boot -- but
offers fast, read-only access.  No table-name overlaps, of course.

I want to access both sets of tables, seamlessly, as I should be able
to do using ATTACH, at the command prompt, but do so using the C API.

Just wondering how others do this (Using ATTACH?  Using
sqlite3_open()? Obviously I haven't looked very far into the
sqlite3_open() code to see how it's put together, etc.).

Thanks for the prompt reply.

On 5/9/07, Andrew Finkenstadt <[EMAIL PROTECTED]> wrote:

On further inspection of your code fragment, it appears you aren't really
using (extra) attached  databases, but merely specifying an alternative file
to use if the first file is not available.  Calling sqlite3_close(...) will
do the right thing, by closing the actual database that succeeded in
opening.

--andy


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] ATTACH and sqlite3_open()

[Andrew Finkenstadt]

On further inspection of your code fragment, it appears you aren't really
using (extra) attached  databases, but merely specifying an alternative file
to use if the first file is not available.  Calling sqlite3_close(...) will
do the right thing, by closing the actual database that succeeded in
opening.

--andy


On 5/9/07, Jon Scully <[EMAIL PROTECTED]> wrote:


Can one attach multiple database files using C API?  The only example
I've seen is CLI-based (using ATTACH).  The Owens book seems to hint
that it's possible to do the same trick using sqlite3_open()...

Quote Pgs. 206-207:
This is more of a connection handle than a database handle since it is
possible to attach multiple databases to a single connection.
However, this connection still represents exactly one transaction
context regardless of how many databases are attached.

But no example is given.  Here's mine:

sqlite3 *db;

if (sqlite3_open("flash.db", )) {
fprintf(stderr, "Can't open the database in the Flash file
system\n");
exit(2);
} else if (sqlite3_open("ram.db", )) {
fprintf(stderr, "Can't open the database in the RAM-disk file
system\n");
sqlite3_close(db);
exit(2);
}

Should this work?  If so, is sqlite3_close() "smart" enough to handle
this situation?  If not, how does sqlite3 (CLI) manage this feat?

Thanks.


-
To unsubscribe, send email to [EMAIL PROTECTED]

-

Re: [sqlite] ATTACH and sqlite3_open()

[Andrew Finkenstadt]

From the comments around the attach function, you'd have to execute a SQL

statement "attach database x as y KEY z".  I assume that 'key Z' is for the
encrypting version of SQLite3 distributed by drh.

/*
** An SQL user-function registered to do the work of an ATTACH statement.
The
** three arguments to the function come directly from an attach statement:
**
** ATTACH DATABASE x AS y KEY z
**
** SELECT sqlite_attach(x, y, z)
**
** If the optional "KEY z" syntax is omitted, an SQL NULL is passed as the
** third argument.
*/

--a


On 5/9/07, Jon Scully <[EMAIL PROTECTED]> wrote:


Can one attach multiple database files using C API?  The only example
I've seen is CLI-based (using ATTACH).  The Owens book seems to hint
that it's possible to do the same trick using sqlite3_open()...

Quote Pgs. 206-207:
This is more of a connection handle than a database handle since it is
possible to attach multiple databases to a single connection.
However, this connection still represents exactly one transaction
context regardless of how many databases are attached.

But no example is given.  Here's mine:

sqlite3 *db;

if (sqlite3_open("flash.db", )) {
fprintf(stderr, "Can't open the database in the Flash file
system\n");
exit(2);
} else if (sqlite3_open("ram.db", )) {
fprintf(stderr, "Can't open the database in the RAM-disk file
system\n");
sqlite3_close(db);
exit(2);
}

Should this work?  If so, is sqlite3_close() "smart" enough to handle
this situation?  If not, how does sqlite3 (CLI) manage this feat?

Thanks.


-
To unsubscribe, send email to [EMAIL PROTECTED]

-

[sqlite] ATTACH and sqlite3_open()

[Jon Scully]

Can one attach multiple database files using C API?  The only example
I've seen is CLI-based (using ATTACH).  The Owens book seems to hint
that it's possible to do the same trick using sqlite3_open()...

Quote Pgs. 206-207:
This is more of a connection handle than a database handle since it is
possible to attach multiple databases to a single connection.
However, this connection still represents exactly one transaction
context regardless of how many databases are attached.

But no example is given.  Here's mine:

   sqlite3 *db;

   if (sqlite3_open("flash.db", )) {
   fprintf(stderr, "Can't open the database in the Flash file system\n");
   exit(2);
   } else if (sqlite3_open("ram.db", )) {
   fprintf(stderr, "Can't open the database in the RAM-disk file
system\n");
   sqlite3_close(db);
   exit(2);
   }

Should this work?  If so, is sqlite3_close() "smart" enough to handle
this situation?  If not, how does sqlite3 (CLI) manage this feat?

Thanks.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] The need for sqlite3_encode_binary and sqlite3_decode_binary

[Andrew Finkenstadt]

It would appear that by using bind variables instead of '%Q' in the SQL
string, the need for sqlite3_encode_binary and sqlite3_decode_binary is
eliminated.  Is that indeed the case?

--andy

[sqlite] Re: Help wiith SQL - first row of each group

[A. Pagaltzis]

* Ed Pasma <[EMAIL PROTECTED]> [2007-05-07 10:28]:
> This solution may is tricky but has occasoinaly helped me. It
> is written here dedicated for the example data. For real data
> the leftpadding should likely be increased to the content of
> the sorting key. Also the result may need to be converted to
> the expected data type, it has now become text.
> 
> SELECT
> g,
> SUBSTR (MAX (SUBSTR ('  ' || p, -2, 2) || v), 3, 1) v FROM
> t
> GROUP BY
> g
> ;

Heh, very cool. A Guttman-Rosler transform [1] in SQL.

[1] http://www.sysarch.com/Perl/sort_paper.html

Regards,
-- 
Aristotle Pagaltzis // 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] A suggestion

[Rich Shepard]

On Wed, 9 May 2007, John Stanton wrote:

That program does have the capability, but may not be implemented that way on 
Windows.  Why not make the change yourself?


A.J.Millan wrote:

As a suggestion, and even in the risk to abuse of Mr Hipp's patience. Would
it be possible to include in the command-line program (sqlite3.exe) the
ability to edit, an repeat at least the five or six last commands, as in
Linux?. Is to say with up-arrow and down-arrow.  I believe it would be too
helpful.


  That, I believe, is a function of the shell, not the application using it.
For example, the command history available in bash is not present -- at
least, was not present -- in sh. Command history, IIRC, was a feature of the
csh, and bash (the Bourne Again Shell) took the best features of sh, csh,
and the Textronics version tcsh.

  Years ago, about a decade, when I had PC DOS 7.0 installed along with
linux, I discovered that the DOS shell allowed tab completions. I don't
recall if it had the history recall, too.

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
 Voice: 503-667-4517  Fax: 503-667-8863

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] A suggestion

[John Stanton]

That program does have the capability, but may not be implemented that 
way on Windows.  Why not make the change yourself?


A.J.Millan wrote:

As a suggestion, and even in the risk to abuse of Mr Hipp's patience. Would
it be possible to include in the command-line program (sqlite3.exe) the
ability to edit, an repeat at least the five or six last commands, as in
Linux?. Is to say with up-arrow and down-arrow.  I believe it would be too
helpful.

A.J.Millan
ZATOR Systems


-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] adding a default ESCAPE character to LIKE

[Jeffrey . H . Mitchell]

Sorry if this isn't the proper channel, but I couldn't figure out where to 
submit a patch.  See below for a patch to src/func.c that alters LIKE such 
that it has a default escape character of '\'.  That is, LIKE behaves by 
default as if ESCAPE '\' were appended.  Both MySQL and PostgreSQL behave 
this way.  The current default behavior (no escape) can be done by using 
ESCAPE ''.

--Jeff Mitchell

I have never submitted anything to SQLite before, so here is my copyright 
statement.

The author or authors of this code dedicate any and all copyright interest 
in this code to the public domain. We make this dedication for the benefit 
of the public at large and to the detriment of our heirs and successors. 
We intend this dedication to be an overt act of relinquishment in 
perpetuity of all present and future rights this code under copyright law.

520c520
<   int escape = 0;
---
>   int escape = sqlite3ReadUtf8("\\");   /* default to '\' */
522c522,523
< /* The escape character string must consist of a single UTF-8 
character.
---
> /* The escape character string must be either the empty string 
(disabling
> ** escaping) or a single UTF-8 character.
526c527
< if( sqlite3utf8CharLen((char*)zEsc, -1)!=1 ){
---
> if( sqlite3utf8CharLen((char*)zEsc, -1) > 1 ){
528c529
<   "ESCAPE expression must be a single character", -1);
---
> "ESCAPE expression must be the empty string or a single 
character", -1);

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Variable substitution for table names

[drh]

"Jeff Hamilton" <[EMAIL PROTECTED]> wrote:
> Hi all,
> 
> I have a complex query that I want to run often and store the results
> to a temporary table. I'd like to compile a statement that looks
> something like:
> 
> CREATE TEMP TABLE ? AS SELECT * FROM data WHERE value = ?;
> 
> so that I can have multiple threads accessing the results of the query
> in various temp tables simultaneously. The above gives me a parse
> error on the first ? for the temp table name. Is this a bug, or
> something that SQLite can't support? I have also tried compiling a
> simple query SELECT * FROM ?; which also gives a parse error.
> 

A host parameter (a.k.a. "?") can only be used in places
where it is legal to put a literal value.  You cannot use
it as a place holder for program structure.

Changing a table in a query can result it radically different
compiled code.  (Use EXPLAIN to see this.)  It is not feasible
to recompile the SQL statement in response to a bind().

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


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Variable substitution for table names

[Igor Tandetnik]

Jeff Hamilton <[EMAIL PROTECTED]> wrote:

I have a complex query that I want to run often and store the results
to a temporary table. I'd like to compile a statement that looks
something like:

CREATE TEMP TABLE ? AS SELECT * FROM data WHERE value = ?;

so that I can have multiple threads accessing the results of the query
in various temp tables simultaneously. The above gives me a parse
error on the first ? for the temp table name. Is this a bug, or
something that SQLite can't support?


Something that SQLite can't support. A parameter is only allowed where 
an expression can appear.


Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Variable substitution for table names

[Jeff Hamilton]

Hi all,

I have a complex query that I want to run often and store the results
to a temporary table. I'd like to compile a statement that looks
something like:

CREATE TEMP TABLE ? AS SELECT * FROM data WHERE value = ?;

so that I can have multiple threads accessing the results of the query
in various temp tables simultaneously. The above gives me a parse
error on the first ? for the temp table name. Is this a bug, or
something that SQLite can't support? I have also tried compiling a
simple query SELECT * FROM ?; which also gives a parse error.

-Jeff

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] perfomance degradation for expr "foo = X or bar =X"

[Dennis Cote]

[EMAIL PROTECTED] wrote:


INTERSECT would give you x=5 AND y=7.  For x=5 OR y=7 you want UNION.
  


Oops, yes of course.

I was thinking of the higher level problem with circles where 
intersection could be used to find a small subset of the table that 
would then be scanned to locate the points in the circle using the exact 
distance calculation.


select * from ex4 where rowid in
   (
   select rowid from ex4 where x between :x_center - :radius and 
:x_center + :radius

   intersect
   select rowid from ex4 where y between :y_center - :radius and 
:y_center + :radius

   )

This will find points in a circumscribed square around the center of the 
circle. The distance calculation would eliminate those points outside 
the circle (i.e. the points in the corners of the squares).


Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] perfomance degradation for expr "foo = X or bar =X"

[bash]

On Wed, 9 May 2007 21:00:46 +0400
Tomash Brechko <[EMAIL PROTECTED]> wrote:

> On Wed, May 09, 2007 at 14:45:33 +, [EMAIL PROTECTED] wrote:
> > You need an R-Tree index to do something like this.  The
> > public-domain version of SQLite only supports B-Tree indices.
> > So, no, indices are not going to help you here.
> 
> Alternatively to R-tree index, you may simply partition the space into
> NxM cells, with, say, left and bottom border belonging to the cell
> itself (while right and upper borders belonging to the right and upper
> cells as their left and bottom borders respectively), and enumerate
> these cells row-by-row like
> 
>   10|11|12|13|14
>  ---+--+--+--+---
>5| 6| 7| 8| 9
>  ---+--+--+--+---
>0| 1| 2| 3| 4
> 
> 
> This way every point belongs to exactly one cell.  Then you create
> 
>CREATE TABLE map (
>x INTEGER,
>y INTEGER,
>name TEXT,
>cell_no INTEGER
>);
>CREATE INDEX map_cell_no ON map (cell_no);
> 
> When inserting a point, you compute its cell_no (something like
> 
>   cell_no(x, y) = y / cell_height * cells_in_row + x / cell_width;
> 
> 
> ).  When doing a region query, you compute a set of cell numbers that
> intersect with a query window, accumulate them in a (memory) table
> selected_cells, and then do
> 
>SELECT map.*
>FROM mem.selected_cells sc CROSS JOIN map ON sc.cell_no = map.cell_no;
> 
> Better yet to compute two sets: those cells that reside completely
> within the query window, and those that intersect window border.
> Points from the latter cells should be filtered further.
> 
> Reasonable cell dimensions based on typical query window size and
> points distribution will give quite reasonable performance.

Interesting idea. I'll try to test it.
Dimension for my map is 800x800 and number of points 50,000+.


-- 
Biomechanical Artificial Sabotage Humanoid

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] sql stack using sqlite

[Ken]

Id like to get your ideas on implementing a stack using sql tables.
 
 table a, contains references to b 
 table b contains refernce to a
 
 table c contains delete entries for A (but b must also be purged!)
 
 My processing forces me to load all of a,b and c. 
 There may be cases where table C indicates a complete deletion for table A. As 
a special case there is another table D that indicates a complete delete. In 
this instance I can delete by another unique Id that is contained in all of the 
tables but ommitted for brevity.
 
 
 create table a ( id integer, ref integer, cnt integer );
 create table b ( id integer, ref integer, val text );
 create table c ( id integer, cnt integer );
 
 insert into a values (1,32,5);
 insert into b values (11,32,'first data item');
 insert into c values (1,5) ;
 
 insert into a values (1,33,5);
 insert into b values (11,33,'second data item');
 insert into c values (1,5) ;
 
 insert into a values (1,34,5);
 insert into b values (11,34,'third data item');
 
 After processing, Id like to be left with the following:
 a ( 1, 34,5)
 b (11, 34, 'third data item')
 
 This is easily implemented in a memory stack. but I'm not sure how to 
implement using sql.
 
 thanks for any ideas.
 Ken
 
 
 
 
 
 

Re: [sqlite] perfomance degradation for expr "foo = X or bar =X"

[Tomash Brechko]

On Wed, May 09, 2007 at 14:45:33 +, [EMAIL PROTECTED] wrote:
> You need an R-Tree index to do something like this.  The
> public-domain version of SQLite only supports B-Tree indices.
> So, no, indices are not going to help you here.

Alternatively to R-tree index, you may simply partition the space into
NxM cells, with, say, left and bottom border belonging to the cell
itself (while right and upper borders belonging to the right and upper
cells as their left and bottom borders respectively), and enumerate
these cells row-by-row like

  10|11|12|13|14
 ---+--+--+--+---
   5| 6| 7| 8| 9
 ---+--+--+--+---
   0| 1| 2| 3| 4


This way every point belongs to exactly one cell.  Then you create

   CREATE TABLE map (
   x INTEGER,
   y INTEGER,
   name TEXT,
   cell_no INTEGER
   );
   CREATE INDEX map_cell_no ON map (cell_no);

When inserting a point, you compute its cell_no (something like

  cell_no(x, y) = y / cell_height * cells_in_row + x / cell_width;


).  When doing a region query, you compute a set of cell numbers that
intersect with a query window, accumulate them in a (memory) table
selected_cells, and then do

   SELECT map.*
   FROM mem.selected_cells sc CROSS JOIN map ON sc.cell_no = map.cell_no;

Better yet to compute two sets: those cells that reside completely
within the query window, and those that intersect window border.
Points from the latter cells should be filtered further.

Reasonable cell dimensions based on typical query window size and
points distribution will give quite reasonable performance.


-- 
   Tomash Brechko

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] perfomance degradation for expr "foo = X or bar =X"

[drh]

Dennis Cote <[EMAIL PROTECTED]> wrote:
> >
> > Why full table scan? :/
> > SQLite can takes set (1) of rowid by ex(x) index for
> > "X=5". Then takes another set (2) of rowid by ex(y) for "Y=7".
> > Then SQLite need only to union this two set (1) and (2).
> > Final SQLite should returns rows where rowid in (set1 union set2).
> >
> >
> >   
> I think you mean intersection where you have used union. SQLite won't 
> optimize the query this way, but you can do it manually.
> 
> Instead of
> 
> select * from ex4 where x = 5 or y = 7;
> 
> You can do this
> 
> select * from ex4 where rowid in
> (
> select rowid from ex4 where x = 5
> intersect
> select rowid from ex4 where y = 7
> );
> 
> The intersect operation allows each of the sub-selects to be executed 
> using an independent index, and the outer select uses the implicit index 
> on the rowid.
> 

INTERSECT would give you x=5 AND y=7.  For x=5 OR y=7 you want UNION.
--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] perfomance degradation for expr "foo = X or bar =X"

[Dennis Cote]

bash wrote:


Why full table scan? :/
SQLite can takes set (1) of rowid by ex(x) index for
"X=5". Then takes another set (2) of rowid by ex(y) for "Y=7".
Then SQLite need only to union this two set (1) and (2).
Final SQLite should returns rows where rowid in (set1 union set2).


  
I think you mean intersection where you have used union. SQLite won't 
optimize the query this way, but you can do it manually.


Instead of

   select * from ex4 where x = 5 or y = 7;

You can do this

   select * from ex4 where rowid in
   (
   select rowid from ex4 where x = 5
   intersect
   select rowid from ex4 where y = 7
   );

The intersect operation allows each of the sub-selects to be executed 
using an independent index, and the outer select uses the implicit index 
on the rowid.


HTH
Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] perfomance degradation for expr "foo = X or bar =X"

[bash]

On Wed, 9 May 2007 11:08:26 -0400
"Samuel R. Neff" <[EMAIL PROTECTED]> wrote:

>  
> I wonder if it would be beneficial to add an additional where clause which
> can prefilter the data so you only need to perform the full calculation on a
> subset of records. 
> 
> I haven't done the math, but let's supposed that point_x is 10 and that for
> any result of your long calculation to be true, then x must be between 5 and
> 15, then you can use the where clause
> 
> WHERE
>   X BETWEEN 5 AND 15
> AND   (point_x - x)^2 + (point_y -y)^2 < R^2;
> 

Yes... it should help a little :)

-- 
Biomechanical Artificial Sabotage Humanoid

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] perfomance degradation for expr "foo = X or bar =X"

[bash]

On Wed, 09 May 2007 14:45:33 +
[EMAIL PROTECTED] wrote:

> bash <[EMAIL PROTECTED]> wrote:
> > 
> > Oh... so this is implementation limitation.
> > Im currently thinking about this table:
> > 
> > CREATE TABLE map (
> > x int,
> > y int,
> > name char
> > );
> > CREATE INDEX map_x ON map(x);
> > CREATE INDEX map_y ON map(y);
> > 
> > And query for it will be something like this (circle):
> > SELECT name
> >   FROM map
> >   WHERE (point_x - x)^2 + (point_y -y)^2 < R^2;
> > 
> > How SQLite will works? Is there any benefit in indexes?
> > 
> 
> You need an R-Tree index to do something like this.  The
> public-domain version of SQLite only supports B-Tree indices.
> So, no, indices are not going to help you here.

Thanks for advice.

-- 
Biomechanical Artificial Sabotage Humanoid

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] perfomance degradation for expr "foo = X or bar =X"

[Ken]

[EMAIL PROTECTED] wrote:Note that some client/server database engines (ex: 
PostgreSQL
and I think also firebird) will automatically rewrite the
original query into something logically similar to my
second example above.  But the query optimizer in SQLite 
does not attempt to be quite that clever.

--
D. Richard Hipp 


I  think that is a good thing that sqlite isn't that clever! I've seen too many 
cases of advanced optimizers such as Oracle totally mess up the execution plans 
for complex query operations. There are simply too many choices for the 
optimizer to pick the best plan. 
 
 Ken

Re: [sqlite] Conditional table select

[Dennis Cote]

Vitali Lovich wrote:

The solution I came up with is:

SELECT coalesce(
(SELECT field1 FROM tbl1 WHERE key = $key),
(SELECT field1 FROM tbl2 WHERE key = $key),
(SELECT field1 FROM tbl3 WHERE key = $key))
, field2 FROM tbl1 WHERE key = $key;

However, if
coalesce works the way I think it does, then it'll do early evaluation
and stop at the first non null parameter. 




Vitali,

Coalesce does not work the way you suggest. It is an SQL function and 
like all functions in SQLite it is passed all its arguments during the 
call. So SQLite has to evaluate all the select expressions to generate 
the arguments before calling coalesce. The coalesce function simply 
returns the first non null argument.


You could  combine the tables like this:

   select field1 from
   (
   select 1 as tbl, field1 from tbl1 where key = :key and field1 
not null

   union
   select 2 as tbl, field1 from tbl2 where key = :key and field1 
not null

   union
   select 3 as tbl, field1 from tbl3 where key = :key and field1 
not null

   )
   order by tbl limit 1

Or you could use a case expression to execute the lookups only if 
needed, but at  the expense of repeating the succesful lookup twice.


   select
   case
   when (select field1 from tbl1 where key = :key) not null
   then (select field1 from tbl1 where key = :key)
   else
   case
   when (select field1 from tbl2 where key = :key) not null
   then (select field1 from tbl2 where key = :key)
   else
   case
   when (select field1 from tbl3 where key = :key) not null
   then (select field1 from tbl3 where key = :key)
   else null
   end
   end
   end
   as field1;

This could be optimized somewhat if SQLite supported the WITH clause for 
named subexpressions. This would allow the lookup queries to be executed 
only once to build an internal temporary table. If the optimizer is 
smart enough, it could delay the execution of the temp table creation 
until the temp table is referenced. This would do the lookups only once, 
and only if needed.


   with
   tbl1_f1 (field1) as (select field1 from tbl1 where key = :key)
   tbl2_f1 (field1) as (select field1 from tbl2 where key = :key)
   tbl3_f1 (field1) as (select field1 from tbl3 where key = :key)
   select
   case
   when (select field1 from tbl1_f1) not null
   then (select field1 from tbl1_f1)
   else
   case
   when (select field1 from tbl2_f1) not null
   then (select field1 from tbl2_f1)
   else
   case
   when (select field1 from tbl3_f1) not null
   then (select field1 from tbl3_f1)
   else null
   end
   end
   end
   as field1;

The question that I have to ask is why is the data separated into these 
three tables? Your life would be a whole lot easier if you combined the 
three tables into one with an additional column that indicated the 
source of the original data. Instead of this:


   create table tbl1 (key primary key, field1);
   create table tbl2 (key primary key, field1);
   create table tbl3 (key primary key, field1);

You could do this:

   create table tbl_all (tbl, key, field1, primary key(tbl, key));
   insert into tbl_all
   select 1 as tbl, key, field1 from tbl1
   union
   select 2 as tbl, key, field1 from tbl2
   union
   select 3 as tbl, key, field1 from tbl3

Then your query becomes

   select field1 from tbl_all
   where key = :key and field1 not null
   order by tbl limit 1

If you don't want to create an actual table with the combined data you 
could get much the same effect using a view.


   create view view_all as
   select 1 as tbl, key, field1 from tbl1
   union
   select 2 as tbl, key, field1 from tbl2
   union
   select 3 as tbl, key, field1 from tbl3
  
   select field1 from view_all

   where key = :key and field1 not null
   order by tbl limit 1

The drawback to this approach is that it will actually build the entire 
table defined by the view as a temporary table before executing a query 
that references the view.


HTH
Dennis Cote




  



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] perfomance degradation for expr "foo = X or bar =X"

[Samuel R. Neff]

 
I wonder if it would be beneficial to add an additional where clause which
can prefilter the data so you only need to perform the full calculation on a
subset of records. 

I haven't done the math, but let's supposed that point_x is 10 and that for
any result of your long calculation to be true, then x must be between 5 and
15, then you can use the where clause

WHERE
X BETWEEN 5 AND 15
AND (point_x - x)^2 + (point_y -y)^2 < R^2;


If you can make this type of pre-calculation and get a proper range for X
then it can reduce the number of records that need to be checked.  I'm
assuming SQLite will use an index on X for BETWEEN (I don't know for sure).
Also you should do testing to be sure this query really is faster in
practice--I'm only theorizing here.  :-)

HTH,

Sam


---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: bash [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, May 09, 2007 10:33 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] perfomance degradation for expr "foo = X or bar =X"

On Wed, 9 May 2007 18:13:07 +0400
Tomash Brechko <[EMAIL PROTECTED]> wrote:

Im currently thinking about this table:

CREATE TABLE map (
x int,
y int,
name char
);
CREATE INDEX map_x ON map(x);
CREATE INDEX map_y ON map(y);

And query for it will be something like this (circle):
SELECT name
  FROM map
  WHERE (point_x - x)^2 + (point_y -y)^2 < R^2;

How SQLite will works? Is there any benefit in indexes?



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] perfomance degradation for expr "foo = X or bar =X"

[drh]

bash <[EMAIL PROTECTED]> wrote:
> 
> Oh... so this is implementation limitation.
> Im currently thinking about this table:
> 
> CREATE TABLE map (
>   x int,
>   y int,
>   name char
> );
> CREATE INDEX map_x ON map(x);
> CREATE INDEX map_y ON map(y);
> 
> And query for it will be something like this (circle):
> SELECT name
>   FROM map
>   WHERE (point_x - x)^2 + (point_y -y)^2 < R^2;
> 
> How SQLite will works? Is there any benefit in indexes?
> 

You need an R-Tree index to do something like this.  The
public-domain version of SQLite only supports B-Tree indices.
So, no, indices are not going to help you here.

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


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] perfomance degradation for expr "foo = X or bar =X"

[bash]

On Wed, 9 May 2007 18:13:07 +0400
Tomash Brechko <[EMAIL PROTECTED]> wrote:

> On Wed, May 09, 2007 at 17:45:52 +0400, bash wrote:
> > > One index per table rule.  At first glance it seems like SQLite could
> > > use at least one index for "x=5 OR y=7" case too, but there is no
> > > point in that, as the other part of the OR would require full table
> > > scan anyway.
> > 
> > Why full table scan? :/
> > SQLite can takes set (1) of rowid by ex(x) index for
> > "X=5". Then takes another set (2) of rowid by ex(y) for "Y=7".
> > Then SQLite need only to union this two set (1) and (2).
> > Final SQLite should returns rows where rowid in (set1 union set2).
> 
> You should read it the following way: "SQLite can't use two indexes
> per table, and using only one index is pointless, hence no index is
> used at all".
> 
> So your question is actually "why SQLite uses at most one index per
> table?".  My guess is that the benefits are out-weighted by the
> implementation complexity.

Oh... so this is implementation limitation.
Im currently thinking about this table:

CREATE TABLE map (
x int,
y int,
name char
);
CREATE INDEX map_x ON map(x);
CREATE INDEX map_y ON map(y);

And query for it will be something like this (circle):
SELECT name
  FROM map
  WHERE (point_x - x)^2 + (point_y -y)^2 < R^2;

How SQLite will works? Is there any benefit in indexes?

-- 
Biomechanical Artificial Sabotage Humanoid

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] perfomance degradation for expr "foo = X or bar =X"

[Tomash Brechko]

On Wed, May 09, 2007 at 17:45:52 +0400, bash wrote:
> > One index per table rule.  At first glance it seems like SQLite could
> > use at least one index for "x=5 OR y=7" case too, but there is no
> > point in that, as the other part of the OR would require full table
> > scan anyway.
> 
> Why full table scan? :/
> SQLite can takes set (1) of rowid by ex(x) index for
> "X=5". Then takes another set (2) of rowid by ex(y) for "Y=7".
> Then SQLite need only to union this two set (1) and (2).
> Final SQLite should returns rows where rowid in (set1 union set2).

You should read it the following way: "SQLite can't use two indexes
per table, and using only one index is pointless, hence no index is
used at all".

So your question is actually "why SQLite uses at most one index per
table?".  My guess is that the benefits are out-weighted by the
implementation complexity.


-- 
   Tomash Brechko

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] perfomance degradation for expr "foo = X or bar =X"

[drh]

Tomash Brechko <[EMAIL PROTECTED]> wrote:
> 
>   sqlite> explain query plan
>  ...>   SELECT id, n1, n2
>  ...>   FROM tbl
>  ...>   WHERE  n1 = $I
>  ...> UNION
>  ...>   SELECT id, n1, n2
>  ...>   FROM tbl
>  ...>   WHERE  n2 = $I
>  ...> ORDER BY id DESC;
>   0|0|TABLE tbl WITH INDEX idx1
>   0|0|TABLE tbl WITH INDEX idx2
> 

Correct.  Notice, however, that the UNION is not strictly
equivalent to the original query.  The UNION query above
gives the same results as:

   SELECT DISTINCT id, n1, n2 FROM tbl WHERE n1=$I OR n2=$I;

Perhaps the added DISTINCT will make no difference in
the output.  If so, then the UNION is the best way to go.
But if DISTINCT will combine records that you do not want
to be combined, then you might consider rewriting the query
as follows:

   SELECT id, n1, n2 FROM tbl
WHERE rowid in (
SELECT rowid FROM tbl WHERE n1=$I
UNION
SELECT rowid FROM tbl WHERE n2=$I
)

Note that some client/server database engines (ex: PostgreSQL
and I think also firebird) will automatically rewrite the
original query into something logically similar to my
second example above.  But the query optimizer in SQLite 
does not attempt to be quite that clever.

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


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] perfomance degradation for expr "foo = X or bar =X"

[bash]

On Wed, 9 May 2007 17:29:29 +0400
Tomash Brechko <[EMAIL PROTECTED]> wrote:

> 
> On Wed, May 09, 2007 at 16:32:34 +0400, bash wrote:
> > SELECT * FROM ex1 WHERE x>'abc' AND y>'abc';
> > In this form only one indexes will be used, why not both?
> 
> One index per table rule.  At first glance it seems like SQLite could
> use at least one index for "x=5 OR y=7" case too, but there is no
> point in that, as the other part of the OR would require full table
> scan anyway.

Why full table scan? :/
SQLite can takes set (1) of rowid by ex(x) index for
"X=5". Then takes another set (2) of rowid by ex(y) for "Y=7".
Then SQLite need only to union this two set (1) and (2).
Final SQLite should returns rows where rowid in (set1 union set2).


> And for the solution with the UNION,
> 
>   sqlite> explain query plan
>  ...>   SELECT id, n1, n2
>  ...>   FROM tbl
>  ...>   WHERE  n1 = $I
>  ...> UNION
>  ...>   SELECT id, n1, n2
>  ...>   FROM tbl
>  ...>   WHERE  n2 = $I
>  ...> ORDER BY id DESC;
>   0|0|TABLE tbl WITH INDEX idx1
>   0|0|TABLE tbl WITH INDEX idx2

Yep... Im using now this construction.

-- 
Biomechanical Artificial Sabotage Humanoid

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] perfomance degradation for expr "foo = X or bar =X"

[Tomash Brechko]

On Wed, May 09, 2007 at 16:34:57 +0400, bash wrote:
> On Wed, 9 May 2007 14:24:27 +0400
> Tomash Brechko <[EMAIL PROTECTED]> wrote:
> > From http://www.sqlite.org/optoverview.html section 6.0:
> > 
> >   Each table in the FROM clause of a query can use at most one index...
> > 
> > So the first query can't benefit from both idx1 and idx2.  You may use
> > 
> >   EXPLAIN QUERY PLAN SELECT ...
> > 
> > to see what indexes will be used.
> 
> If i understand right from previous post by Peter there will be not
> used any indexes because of "OR".

I think those slides are a bit outdated.  On the same
http://www.sqlite.org/optoverview.html page the section "3.0: The OR
optimization" says that the query from the slide 52

  SELECT * FROM ex4 WHERE x=5 OR x=7;

will be rewritten as

  SELECT * FROM ex4 WHERE x IN (5, 7);

and IN can use indexes.  But "x=5 OR y=7" (i.e. conditions on
_different_ columns) can't be rewritten that way---exactly your
situation.


On Wed, May 09, 2007 at 16:32:34 +0400, bash wrote:
> SELECT * FROM ex1 WHERE x>'abc' AND y>'abc';
> In this form only one indexes will be used, why not both?

One index per table rule.  At first glance it seems like SQLite could
use at least one index for "x=5 OR y=7" case too, but there is no
point in that, as the other part of the OR would require full table
scan anyway.

And for the solution with the UNION,

  sqlite> explain query plan
 ...>   SELECT id, n1, n2
 ...>   FROM tbl
 ...>   WHERE  n1 = $I
 ...> UNION
 ...>   SELECT id, n1, n2
 ...>   FROM tbl
 ...>   WHERE  n2 = $I
 ...> ORDER BY id DESC;
  0|0|TABLE tbl WITH INDEX idx1
  0|0|TABLE tbl WITH INDEX idx2

both indexes are used.


-- 
   Tomash Brechko

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Update callback and REPLACE

[Krzysztof Sobolewski]

Hi all,

I have a question about an update callback. It's supposed to be called for
every row that's inserted, updated and deleted and it works fine. Now
there's a little thing calles INSERT OR UPDATE (and its friends) which
removes conflicting rows before the INSERT. The thing is, the callback gets
called for the row inserted, but NOT for the row deleted. Is this expected
behaviour? I'm depending on the callback to notify me of *all* changes and
this one missing row is not something I like :)
-KS

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: perfomance degradation for expr "foo = X or bar =X"

[bash]

On Wed, 9 May 2007 14:24:27 +0400
Tomash Brechko <[EMAIL PROTECTED]> wrote:

> On Wed, May 09, 2007 at 14:03:54 +0400, bash wrote:
> > Im simplify environment:
> > 
> > CREATE TABLE tbl(
> > id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
> > n1 int,
> > n2 int
> > );
> > CREATE INDEX idx1 on tbl(n1);
> > CREATE INDEX idx2 on tbl(n2);
> > 
> > sqlite> select count(*) from tbl;
> > 63026
> > 
> > 1 query:
> > SELECT id, n1, n2
> > FROM tbl
> > WHERE  n1 = $I OR n2 = $I
> > ORDER BY id DESC;
> > 
> > 2 query:
> >   SELECT id, n1, n2 
> >   FROM tbl
> >   WHERE  n1 = $I
> > UNION
> >   SELECT id, n1, n2
> >   FROM tbl
> >   WHERE  n2 = $I
> > ORDER BY id DESC;
> > 
> > Timing:
> > 1: 0.080 s.
> > 2: 0.000 s.
> 
> From http://www.sqlite.org/optoverview.html section 6.0:
> 
>   Each table in the FROM clause of a query can use at most one index...
> 
> So the first query can't benefit from both idx1 and idx2.  You may use
> 
>   EXPLAIN QUERY PLAN SELECT ...
> 
> to see what indexes will be used.

If i understand right from previous post by Peter there will be not
used any indexes because of "OR".

-- 
Biomechanical Artificial Sabotage Humanoid

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] perfomance degradation for expr "foo = X or bar =X"

[bash]

On Wed, 9 May 2007 12:23:14 +0200
Peter van Dijk <[EMAIL PROTECTED]> wrote:

> 
> On 9-mei-2007, at 11:28, bash wrote:
> 
> > SELECT type, stamp_id, old_player_id, new_player_id
> > FROM town_log
> > WHERE old_player_id = $ID OR new_player_id = $ID
> > ORDER BY stamp_id DESC;
> >
> > This query works really slowly and i don't know why :/
> > For example, the same result by another QUERY work much faster!
> >
> > SELECT type, stamp_id, old_player_id, new_player_id
> > FROM town_log
> > WHERE old_player_id = $ID
> > UNION
> > SELECT type, stamp_id, old_player_id, new_player_id
> > FROM town_log
> > WHERE new_player_id = %d
> > ORDER BY stamp_id DESC;
> 
> Hello Humanoid,
> 
> UNION performing much better than an equivalent query with OR is a  
> known 'limitation' in many database systems, including MySQL and  
> SQLite. Page 52 of http://www.sqlite.org/php2004/slides-all.html  
> explicitly recommends to use UNION here.
> 
> Cheers, Peter.


Thanks a lot! I don't know that information :)
Moreover this papers is really interesting.
But i don't understand about indexes this:
http://www.sqlite.org/php2004/slides-all.html
Page 54 of 63

For example:
CREATE TABLE ex1(
   id INTEGER PRIMARY KEY,
   x ,
   y
);
CREATE INDEX idx1 ON ex1(x);
CREATE INDEX idx2 ON ex1(y);

SELECT * FROM ex1 WHERE x>'abc' AND y>'abc';
In this form only one indexes will be used, why not both?

For example by idx1 we can get set1 of ROWIDs which is satisfy "x >
'abc'" term, by idx2 we can get set2 of ROWIDs which is satisfy
"y>'abc'" term and then just union set1 with set2. So next we should
just extracts rows with ROWIDS in this union


-- 
Biomechanical Artificial Sabotage Humanoid

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] A suggestion

[A.J.Millan]

Sorry!!

Although there are two other boxes around; one with XP and the other with
Vista, for test purposes, actually I still use  a third, with my old Windows
98SE and a utility, DOSKEY.COM, who emulates the Linux behavior in the
Windows shell (who natively does not do that), but when I use it with
sqlite3.exe that ability is lost.

By the way, after this response, I have been looking in the XP box and there
are a doskey.exe, but does not work in that way, but when try with sqlite3
in it, that works fine!!

Perhaps that would make me jump to that system despite my reluctance :-)

A.J.Millan
ZATOR Systems



- Original Message - 
From: "Peter van Dijk" <[EMAIL PROTECTED]>
To: 
Sent: Wednesday, May 09, 2007 12:11 PM
Subject: Re: [sqlite] A suggestion


>
> On 9-mei-2007, at 11:06, A.J.Millan wrote:
>
> > As a suggestion, and even in the risk to abuse of Mr Hipp's
> > patience. Would
> > it be possible to include in the command-line program (sqlite3.exe)
> > the
> > ability to edit, an repeat at least the five or six last commands,
> > as in
> > Linux?. Is to say with up-arrow and down-arrow.  I believe it would
> > be too
> > helpful.
>
> Arrow up and down work fine for me, I'm not sure what your actual
> request is then?
>
> Cheers, Peter.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] A suggestion

[Lloyd]

In old versions it work... But in new versions (3.* I think) its not
working!

On Wed, 2007-05-09 at 12:11 +0200, Peter van Dijk wrote:
> On 9-mei-2007, at 11:06, A.J.Millan wrote:
> 
> > As a suggestion, and even in the risk to abuse of Mr Hipp's  
> > patience. Would
> > it be possible to include in the command-line program (sqlite3.exe)  
> > the
> > ability to edit, an repeat at least the five or six last commands,  
> > as in
> > Linux?. Is to say with up-arrow and down-arrow.  I believe it would  
> > be too
> > helpful.
> 
> Arrow up and down work fine for me, I'm not sure what your actual  
> request is then?
> 
> Cheers, Peter.
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -


__
Scanned and protected by Email scanner

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: perfomance degradation for expr "foo = X or bar =X"

[Tomash Brechko]

On Wed, May 09, 2007 at 14:03:54 +0400, bash wrote:
> Im simplify environment:
> 
> CREATE TABLE tbl(
>   id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
>   n1 int,
>   n2 int
> );
> CREATE INDEX idx1 on tbl(n1);
> CREATE INDEX idx2 on tbl(n2);
> 
> sqlite> select count(*) from tbl;
> 63026
> 
> 1 query:
> SELECT id, n1, n2
> FROM tbl
> WHERE  n1 = $I OR n2 = $I
> ORDER BY id DESC;
> 
> 2 query:
>   SELECT id, n1, n2 
>   FROM tbl
>   WHERE  n1 = $I
> UNION
>   SELECT id, n1, n2
>   FROM tbl
>   WHERE  n2 = $I
> ORDER BY id DESC;
> 
> Timing:
> 1: 0.080 s.
> 2: 0.000 s.

>From http://www.sqlite.org/optoverview.html section 6.0:

  Each table in the FROM clause of a query can use at most one index...

So the first query can't benefit from both idx1 and idx2.  You may use

  EXPLAIN QUERY PLAN SELECT ...

to see what indexes will be used.


-- 
   Tomash Brechko

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] perfomance degradation for expr "foo = X or bar =X"

[Peter van Dijk]

On 9-mei-2007, at 11:28, bash wrote:


SELECT type, stamp_id, old_player_id, new_player_id
FROM town_log
WHERE old_player_id = $ID OR new_player_id = $ID
ORDER BY stamp_id DESC;

This query works really slowly and i don't know why :/
For example, the same result by another QUERY work much faster!

SELECT type, stamp_id, old_player_id, new_player_id
FROM town_log
WHERE old_player_id = $ID
UNION
SELECT type, stamp_id, old_player_id, new_player_id
FROM town_log
WHERE new_player_id = %d
ORDER BY stamp_id DESC;


Hello Humanoid,

UNION performing much better than an equivalent query with OR is a  
known 'limitation' in many database systems, including MySQL and  
SQLite. Page 52 of http://www.sqlite.org/php2004/slides-all.html  
explicitly recommends to use UNION here.


Cheers, Peter.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] A suggestion

[Peter van Dijk]

On 9-mei-2007, at 11:06, A.J.Millan wrote:

As a suggestion, and even in the risk to abuse of Mr Hipp's  
patience. Would
it be possible to include in the command-line program (sqlite3.exe)  
the
ability to edit, an repeat at least the five or six last commands,  
as in
Linux?. Is to say with up-arrow and down-arrow.  I believe it would  
be too

helpful.


Arrow up and down work fine for me, I'm not sure what your actual  
request is then?


Cheers, Peter.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: perfomance degradation for expr "foo = X or bar =X"

[bash]

Im simplify environment:

CREATE TABLE tbl(
id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
n1 int,
n2 int
);
CREATE INDEX idx1 on tbl(n1);
CREATE INDEX idx2 on tbl(n2);

sqlite> select count(*) from tbl;
63026

1 query:
SELECT id, n1, n2
FROM tbl
WHERE  n1 = $I OR n2 = $I
ORDER BY id DESC;

2 query:
  SELECT id, n1, n2 
  FROM tbl
  WHERE  n1 = $I
UNION
  SELECT id, n1, n2
  FROM tbl
  WHERE  n2 = $I
ORDER BY id DESC;

Timing:
1: 0.080 s.
2: 0.000 s.

Still big degradation.

-- 
Biomechanical Artificial Sabotage Humanoid

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] perfomance degradation for expr "foo = X or bar =X"

[bash]

 Hello All,
Im using SQLite-3.3.17.
My table is:

CREATE TABLE town_log (
id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
town_id int,
stamp_id int,
old_player_id int,
new_player_id int,
type int
);
CREATE INDEX tl_np_id on town_log(new_player_id);
CREATE INDEX tl_op_id on town_log(old_player_id);
CREATE INDEX tl_st_id on town_log(stamp_id);
CREATE INDEX tl_tw_id on town_log(town_id);
CREATE INDEX tl_type on town_log(type);

And I'm trying to execute this query:

SELECT type, stamp_id, old_player_id, new_player_id
FROM town_log
WHERE old_player_id = $ID OR new_player_id = $ID
ORDER BY stamp_id DESC;

This query works really slowly and i don't know why :/
For example, the same result by another QUERY work much faster!

SELECT type, stamp_id, old_player_id, new_player_id
FROM town_log
WHERE old_player_id = $ID
UNION
SELECT type, stamp_id, old_player_id, new_player_id
FROM town_log
WHERE new_player_id = %d
ORDER BY stamp_id DESC;

Timing by
http://katastrophos.net/andre/blog/2007/01/04/sqlite-simple-timing-profiler-patch/
shows this times:
1st query: 0.250 s.
2st query: 0.000 s.

PS. ANALYZE do not help to solve this problem. I think problem(?) in
query optimizer.

-- 
Biomechanical Artificial Sabotage Humanoid

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] A suggestion

[A.J.Millan]

As a suggestion, and even in the risk to abuse of Mr Hipp's patience. Would
it be possible to include in the command-line program (sqlite3.exe) the
ability to edit, an repeat at least the five or six last commands, as in
Linux?. Is to say with up-arrow and down-arrow.  I believe it would be too
helpful.

A.J.Millan
ZATOR Systems


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] porting sqlite3 to embeded os-----lock question

[allen . zhang]

I am porting sqlite3 to the embeded os,such as threadx,nucleus,ect..
I am writing the file such as os_threadx.c,os_nucleus.c according to the 
os_win.c,os_unix.c.
I have read the os_win.c and find that there is a switcher OS_WINCE in the 
struct winFile.
Is this mean the windows platform don't need the function such as share 
memory(CreateFileMappingW,MapViewOfFile) inside the OS_WINCE swither?

whether I should realize the share memory lock function in the embeded os? 
I have find there is not a direct way similar to the windows share memory 
and the interface funcitons.
It seems difficult to simulate the share memory funciton and it's lock 
function in my embeded os.
Does it mean I must realize it. or the porting will fail.

another question:
There is also a little difficult to realize the 
sqlite3WinThreadSpecificData function to get the thread information, 
Is this also must realize ?

thanks a lot.
allen.zhang