[sqlite] C++ ORM

[Simon Slavin]

On 9 Mar 2015, at 9:38pm, Scott Robison  wrote:

> A co-worker who is working on a project is interested in finding out if
> there is an effective ORM for C++ / SQLite. I've not used one so I'm
> turning to the list to see if anyone has a recommendation.

For those playing along at home, ORM == Object Relational Mapping.  In other 
words you do Object-oriented programming in C++ and the objects are stored in a 
SQLite database.

I'm not aware that this problem has been solved well in any language or with 
any database engine.  It might seem like a terrific oppotunity to write a 
demonstration library, but differences in how languages do OO and how databases 
store data seem to make this a difficult problem to crack.  I'd be interested 
in any solutions that use SQLite with any popular OO language.

Simon.

[sqlite] Question about ON DELETE CASCADE

[Simon Slavin]

On 9 Mar 2015, at 9:11pm, R.Smith  wrote:

>> If so, do I have to create a new database and repopulate it or is there a 
>> way to edit the existing database from the command shell?
> 
> There is always a way - but it is dangerous.

Mister Smith's method should work perfectly.  You will, of course, work on a 
copy of the database so you have a copy to fall back on if you mess it up.

If editing your schema worries you you might like to do something safer.  
Another method of changing a database schema is to use the shell tool to 
'.dump' the database to a text file.  You can then edit the text file to 
reflect your desired changes, then use the shell tool to create a new blank 
database and '.read' in the modified text file.

The two methods, if used correctly, will end up with the same result.

Simon.

[sqlite] sqlite3_exec ROLLBACK command

[Mayank Kumar (mayankum)]

Hi All

We have some legacy code which calls sqlite3_exec with ROLLBACk every time 
sqlite3_step fails irrespective what specific error occurred. I am wondering if 
this is a safe behavior or should I change this.  Can it cause any sideeffects 
or memory leak or any other issues ?

What is the right thing to do ?
I do see some recommendation here https://www.sqlite.org/lang_transaction.html,


For all of these errors, SQLite attempts to undo just the one statement it was 
working on and leave changes from prior statements within the same transaction 
intact and continue with the transaction. However, depending on the statement 
being evaluated and the point at which the error occurs, it might be necessary 
for SQLite to rollback and cancel the entire transaction. An application can 
tell which course of action SQLite took by using 
thesqlite3_get_autocommit() 
C-language interface.

It is recommended that applications respond to the errors listed above by 
explicitly issuing a ROLLBACK command. If the transaction has already been 
rolled back automatically by the error response, then the ROLLBACK command will 
fail with an error, but no harm is caused by this.

Should I be using get_autocommit to determine if rollback is necessary or just 
calling rollback every time is ok or should I be calling rollback only for 
errors FULL,IOERR,BUSY and NOMEM.

-Mayank

[sqlite] Question about ON DELETE CASCADE

[Drago, William @ CSG - NARDA-MITEQ]

All,

I want to modify my database so that deleting a row from a parent table will 
delete all dependant rows in all child tables. According to this page:

https://www.sqlite.org/foreignkeys.html

It looks like I have to add ON DELETE CASCADE to the child keys of all child 
tables. For example:

FROM:
DatasetID INTEGER REFERENCES UUT_Info

TO:
DatasetID INTEGER REFERENCES UUT_Info ON DELETE CASCADE

Is this correct?

If so, do I have to create a new database and repopulate it or is there a way 
to edit the existing database from the command shell?

Thank you,
--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] Read Blob Returning bytes till NULL Terminated String

[Hick Gunter]

How are you inserting the blob?

If you are using sqlite3_bind_blob, be sure to give the correct size (maybe you 
are inadvertently using strlen() or similar to compute a length?).
Are you using SQLITE_STATIC, SQLITE_TRANSIENT or passing a destructor function?

When retreiving the value, you should be calling sqlite3_column_blob() first 
(which makes sure the column is a blob and returns the address), and then 
sqlite3_column_bytes() to get the size.

-Urspr?ngliche Nachricht-
Von: reddy ykk [mailto:ykk157 at gmail.com]
Gesendet: Montag, 09. M?rz 2015 18:32
An: sqlite-users at mailinglists.sqlite.org
Betreff: [sqlite] Read Blob Returning bytes till NULL Terminated String

Hi Sir,

I have a requirement of reading an image data (BLOB) from a database.
I could successfully read the length of the blob using
sqlite3_column_bytes() but when i try to read the entire blob using 
sqlite3_column_blob(), i am getting undesired results.

I am getting the bytes till NULL terminated string is present. In my case, only 
3 bytes of data are being returned by the method.
Is there any way that if we specify the length, the whole blob data is 
retrieved irrespective of the NULL bytes .

Thanks and Regards,
Kishor Reddy
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] sqlite3_exec ROLLBACK command

[Richard Hipp]

On 3/9/15, Mayank Kumar (mayankum)  wrote:
> Hi All
>
> We have some legacy code which calls sqlite3_exec with ROLLBACk every time
> sqlite3_step fails irrespective what specific error occurred. I am wondering
> if this is a safe behavior or should I change this.  Can it cause any
> sideeffects or memory leak or any other issues ?

ROLLBACK is always "safe" in the sense that it preserves the database
in an intact and consistent form.  The only downside is that it might
undo some INSERTs, UPDATEs, or DELETEs that you have previously
executed but not yet committed.  If you don't mind losing all the
changes that have occurred since the start of the transaction, then it
is always safe to do ROLLBACK.


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Attach via prepared statement / Multiple attach

[Fabian Stumpf]

Hi everyone,

I am currently using
> ATTACH DATABASE ? AS ?;
to dynamically attach databases to a connection.
In some cases, the same physical database file is attached under
different names.

This all seems to work perfectly, although the documentation for the
ATTACH statement specifies the name as type "database-name", not as
"expr", so I'm not sure if specifying a bind parameter is "legal". So
I'd like to know if this behaviour is intended and thus safe to rely
on (and will it continue to be supported)? Same goes for attaching the
same file multiple times (under different names).

--
Kind regards,
FJS

[sqlite] C++ ORM

[Darren Duncan]

On 2015-03-09 3:25 PM, Simon Slavin wrote:
> On 9 Mar 2015, at 9:38pm, Scott Robison  wrote:
>
>> A co-worker who is working on a project is interested in finding out if
>> there is an effective ORM for C++ / SQLite. I've not used one so I'm
>> turning to the list to see if anyone has a recommendation.
>
> For those playing along at home, ORM == Object Relational Mapping.  In other 
> words you do Object-oriented programming in C++ and the objects are stored in 
> a SQLite database.
>
> I'm not aware that this problem has been solved well in any language or with 
> any database engine.  It might seem like a terrific oppotunity to write a 
> demonstration library, but differences in how languages do OO and how 
> databases store data seem to make this a difficult problem to crack.  I'd be 
> interested in any solutions that use SQLite with any popular OO language.

I am developing a project right now that aims to out-do ORM at its own game, by 
changing the paradigm so the relational database and application programming 
realms are unified into one environment, where relations and tuples are 
first-class types you can use in applications, and the same arbitrary 
user-defined types you can use in applications can be used directly in 
databases, no "mapping" required.  I hope to have a first version executing in 
about 2 months. -- Darren Duncan

[sqlite] C++ ORM

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 03/09/2015 02:38 PM, Scott Robison wrote:
> Note: He's okay using SQLite more or less directly if he needs to,
> as he recognizes the lack of reflection in C++ might lead to a less
> manageable ORM. Still, never hurts to ask.

Don't use an ORM unless the ongoing benefits outweigh the drawbacks.

http://blogs.tedneward.com/2006/06/26/The%2BVietnam%2BOf%2BComputer%2BScience.aspx

http://stackoverflow.com/questions/404083/is-orm-still-the-vietnam-of-computer-science

A nice quote from the latter:

  Object-oriented is still object-oriented and Relational is
  still Set-oriented.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlT+NmIACgkQmOOfHg372QQ7CQCgqZcwZ0xQK0fJoG39t6OKDHBF
oIEAoInFomPfmw77XSMbNWWZndb9IKQc
=u2/6
-END PGP SIGNATURE-

[sqlite] NtFlushBuffersFileEx for SQLITE_SYNC_DATAONLY on Windows ?

[Howard Kapustein]

Has anyone considered supporting SQLITE_SYNC_DATAONLY on Windows using 
NtFlushBuffersFileEx?
http://msdn.microsoft.com/en-us/library/windows/hardware/hh967720(v=vs.85).aspx

>When the SQLITE_SYNC_DATAONLY flag is used, it means that the sync operation
>only needs to flush data to mass storage. Inode information need not be 
>flushed.
Am I missing something in SQLite's expectations? Isn't this just flush the 
file's data/content but not the associated metadata (filesize, etc Windows 
inode-equivalent) i.e. 
NtFlushBuffersFileEx(...,flags=FLUSH_FLAGS_FILE_DATA_ONLY,...)?

It looks like it's just a change in winSync() to replace

  rc = osFlushFileBuffers(pFile->h);
with something like

  IO_STATUS_BLOCK ioStatusBlock;

  ...

  int isDataOnly = (flags_SYNC_DATAONLY);

  ...

  if (isDataOnly)

rc = osFlushFileBuffers(pFile->h);

  else

rc = HRESULT_FROM_NT(NtFlushBuffersFileEx(pFile->h, 
FLUSH_FLAGS_FILE_DATA_ONLY, );

Would a new system call be used to optionally support sync-dataonly semantics? 
Or would this be handled via a new compile-time option and #ifdef in winSync?


-  Howard

[sqlite] regarding loops in joins(VDBE)

[Sairam Gaddam]

I am trying to understand how sqlite joins execute and this is just a
simple example.

Can you please elaborate a bit on the index look up part ?
How is sqlite navigating the cursor to point to the rows of other table
with out a loop ?
How does index look up work without looping  as indexes are also
represented by cursor ?

On Mon, Mar 9, 2015 at 4:37 PM, Hick Gunter  wrote:

> Are you trying to create tables with INTEGER PRIMARY KEY? You have to
> write EXACTLY "integer primary key" (not case specific) to achieve this.
> The em <=> lo join would probably profit from this.
>
> Your join specifies to compare em.name (a column with numeric affinity)
> to idv.name (a column with text affinity); this will force SQLite to
> convert one of the fields for the conversion. Is this what you intended?
>
> Maybe you should try to "EXPLAIN QUERY PLAN" to find out what SQLite plans
> to do.
>
> From a quick look at the VDBE code it is clearly choosing to scan your em
> table and access the lo and idv tables via index lookup. If either of the
> index lookups were not UNIQUE, there would be an inner loop to resolve the
> join.
>
> Since em.name and lo.name are matching primary keys, why the two tables?
> Which other entity has a lo(cation)? And how are you going to keep that
> other entity from assigning conflicting keys (i.e. those already used for
> em entries)?
>
> Are you sure the em(ployee)'s name is an integer?
>
> -Urspr?ngliche Nachricht-
> Von: Sairam Gaddam [mailto:gaddamsairam at gmail.com]
> Gesendet: Montag, 09. M?rz 2015 11:40
> An: General Discussion of SQLite Database
> Betreff: [sqlite] regarding loops in joins(VDBE)
>
> sir,
> I have a join query with 3 tables and 2 conditions in the where
> clause.But the program executes by opening single loop.Can any one know how
> is executes?
>
> I have included the query and the vdbe program in the link below:
> http://pastebin.com/aA3QSJ7w
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: hick at scigames.at
>
> This communication (including any attachments) is intended for the use of
> the intended recipient(s) only and may contain information that is
> confidential, privileged or legally protected. Any unauthorized use or
> dissemination of this communication is strictly prohibited. If you have
> received this communication in error, please immediately notify the sender
> by return e-mail message and delete all copies of the original
> communication. Thank you for your cooperation.
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] regarding loops in joins(VDBE)

[Sairam Gaddam]

sir,
I have a join query with 3 tables and 2 conditions in the where
clause.But the program executes by opening single loop.Can any one know how
is executes?

I have included the query and the vdbe program in the link below:
http://pastebin.com/aA3QSJ7w

[sqlite] minor doc error

[Richard Hipp]

Fix at (https://www.sqlite.org/docsrc/timeline?c=2015-03-09+19:42:10).
Tnx for the typo report.

On 3/9/15, Adam Devita  wrote:
>  https://www.sqlite.org/tempfiles.html
>
> "On of the distinctive features  of
> SQLite"
> should be
> "One of the distinctive features  of
> SQLite"
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] C++ ORM

[Scott Robison]

A co-worker who is working on a project is interested in finding out if
there is an effective ORM for C++ / SQLite. I've not used one so I'm
turning to the list to see if anyone has a recommendation.

Note: He's okay using SQLite more or less directly if he needs to, as he
recognizes the lack of reflection in C++ might lead to a less manageable
ORM. Still, never hurts to ask.

-- 
Scott Robison

[sqlite] minor doc error

[Adam Devita]

 https://www.sqlite.org/tempfiles.html

"On of the distinctive features  of
SQLite"
should be
"One of the distinctive features  of
SQLite"

[sqlite] Read Blob Returning bytes till NULL Terminated String

[Igor Tandetnik]

On 3/9/2015 1:31 PM, reddy ykk wrote:
> I am getting the bytes till NULL terminated string is present.

How exactly do you determine that? You aren't using strcpy or similar on 
those bytes, are you? I suggest you show your code that calls 
sqlite3_column_blob, and especially what you do afterwards with the 
pointer it returns.

Also, do you eyeball the value in the debugger? The debugger would 
assume that a variable of type char* points to a NUL-terminated string, 
and display it as such.
-- 
Igor Tandetnik

[sqlite] Can I copy one column of data to another table?

[Dave]

Thanks for the help Igor. :-)
Dave

On 3/7/2015 1:37 PM, Igor Tandetnik wrote:
>
>
> On 3/7/2015 11:42 AM, Dave wrote:
>> Now when trying to use the database I see that I should have made 1
>> table with all the related data (I think) and am trying to copy one
>> column of data at a time to the "main" table. Can that be done and if so
>> how? The data in all the columns has to line up with the ID numbers.
>
> insert into WideTable(ID, C1, C2, C3)
> select t1.ID, t1.C1, t2.C2, t3.C3
> from NarrowTable1 t1 join NarrowTable2 t2 using (ID) join NarrowTable3 
> t3 using (ID);
>
>

[sqlite] Read Blob Returning bytes till NULL Terminated String

[Richard Hipp]

On 3/9/15, reddy ykk  wrote:
> Hi Sir,
>
> I have a requirement of reading an image data (BLOB) from a database.
> I could successfully read the length of the blob using
> sqlite3_column_bytes() but when i try to read the entire blob using
> sqlite3_column_blob(), i am getting undesired results.
>
> I am getting the bytes till NULL terminated string is present. In my case,
> only 3 bytes of data are being returned by the method.
> Is there any way that if we specify the length, the whole blob data is
> retrieved irrespective of the NULL bytes .
>

SQLite always returns the full BLOB if you give it the right length.
zero bytes do not make a difference.

If you BLOB is getting truncated, perhaps that is due to whatever
wrapper interface you are using to access SQLite.  What programming
language are you using?

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] regarding loops in joins(VDBE)

[Hick Gunter]

A PRIMARY KEY is also UNIQUE. Lookup for equality by primary key can return at 
most 1 row, so there is no need for a loop.

-Urspr?ngliche Nachricht-
Von: Sairam Gaddam [mailto:gaddamsairam at gmail.com]
Gesendet: Montag, 09. M?rz 2015 12:36
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] regarding loops in joins(VDBE)

I am trying to understand how sqlite joins execute and this is just a simple 
example.

Can you please elaborate a bit on the index look up part ?
How is sqlite navigating the cursor to point to the rows of other table with 
out a loop ?
How does index look up work without looping  as indexes are also represented by 
cursor ?

On Mon, Mar 9, 2015 at 4:37 PM, Hick Gunter  wrote:

> Are you trying to create tables with INTEGER PRIMARY KEY? You have to
> write EXACTLY "integer primary key" (not case specific) to achieve this.
> The em <=> lo join would probably profit from this.
>
> Your join specifies to compare em.name (a column with numeric
> affinity) to idv.name (a column with text affinity); this will force
> SQLite to convert one of the fields for the conversion. Is this what you 
> intended?
>
> Maybe you should try to "EXPLAIN QUERY PLAN" to find out what SQLite
> plans to do.
>
> From a quick look at the VDBE code it is clearly choosing to scan your
> em table and access the lo and idv tables via index lookup. If either
> of the index lookups were not UNIQUE, there would be an inner loop to
> resolve the join.
>
> Since em.name and lo.name are matching primary keys, why the two tables?
> Which other entity has a lo(cation)? And how are you going to keep
> that other entity from assigning conflicting keys (i.e. those already
> used for em entries)?
>
> Are you sure the em(ployee)'s name is an integer?
>
> -Urspr?ngliche Nachricht-
> Von: Sairam Gaddam [mailto:gaddamsairam at gmail.com]
> Gesendet: Montag, 09. M?rz 2015 11:40
> An: General Discussion of SQLite Database
> Betreff: [sqlite] regarding loops in joins(VDBE)
>
> sir,
> I have a join query with 3 tables and 2 conditions in the where
> clause.But the program executes by opening single loop.Can any one
> know how is executes?
>
> I have included the query and the vdbe program in the link below:
> http://pastebin.com/aA3QSJ7w
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: hick at scigames.at
>
> This communication (including any attachments) is intended for the use
> of the intended recipient(s) only and may contain information that is
> confidential, privileged or legally protected. Any unauthorized use or
> dissemination of this communication is strictly prohibited. If you
> have received this communication in error, please immediately notify
> the sender by return e-mail message and delete all copies of the
> original communication. Thank you for your cooperation.
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] Read Blob Returning bytes till NULL Terminated String

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 03/09/2015 10:31 AM, reddy ykk wrote:
> I have a requirement of reading an image data (BLOB) from a
> database. I could successfully read the length of the blob using 
> sqlite3_column_bytes() but when i try to read the entire blob
> using sqlite3_column_blob(), i am getting undesired results.

You will need to give more detailed information, so we know what you
are actually doing and what undesired results you are actually getting.

  http://catb.org/~esr/faqs/smart-questions.html

> I am getting the bytes till NULL terminated string is present. In
> my case, only 3 bytes of data are being returned by the method.

That indicates a misunderstanding.  You can only deal with blobs by
having a pointer and a length.  sqlite3_column_bytes() returns that
length, and sqlite3_column_blob() returns a pointer where the length
bytes are the data originally supplied.

> Is there any way that if we specify the length, the whole blob data
> is retrieved irrespective of the NULL bytes .

sqlite3_column_bytes/blob do fundamentally work, and do inspection of
the data since all bytes are valid in blobs.  If they didn't a heck of
a lot of people would have noticed by now.

Repeatedly mentioning NULL is a strong indication that whatever
non-SQLite routines you are using are the problem.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlT96gwACgkQmOOfHg372QQw+wCfU3la7vcCoezrsELJXmcQ7OjZ
/Z8Ani3+roNc0s5d+jKQAm46xLw/i1Q7
=2RtC
-END PGP SIGNATURE-

[sqlite] regarding loops in joins(VDBE)

[Hick Gunter]

Are you trying to create tables with INTEGER PRIMARY KEY? You have to write 
EXACTLY "integer primary key" (not case specific) to achieve this. The em <=> 
lo join would probably profit from this.

Your join specifies to compare em.name (a column with numeric affinity) to 
idv.name (a column with text affinity); this will force SQLite to convert one 
of the fields for the conversion. Is this what you intended?

Maybe you should try to "EXPLAIN QUERY PLAN" to find out what SQLite plans to 
do.

[sqlite] Optimization Opportunity?

[Hick Gunter]

I personally would use "... EXISTS ( SELECT 1 ...", which requires no extra 
columns to be acessed at all.

-Urspr?ngliche Nachricht-
Von: Wolfgang Enzinger [mailto:sqlite at enzinger.net]
Gesendet: Samstag, 07. M?rz 2015 19:25
An: sqlite-users at mailinglists.sqlite.org
Betreff: [sqlite] Optimization Opportunity?

Hi dev team,

not sure if this is actually a useful hint, but ...

CREATE TABLE a(a1 INTEGER PRIMARY KEY);
INSERT INTO a VALUES (1),(2),(3);
CREATE TABLE b(a1 INTEGER REFERENCES a(a1),b1 INTEGER PRIMARY KEY); INSERT INTO 
b VALUES (1,11),(2,22),(3,33); CREATE UNIQUE INDEX b_ui ON b(a1,b1); CREATE 
TABLE c(b1 INTEGER REFERENCES b(b1),c1 INTEGER PRIMARY KEY,c2 TEXT); INSERT 
INTO c VALUES (11,111,'a'),(22,222,'b'),(33,333,'c');
CREATE UNIQUE INDEX c_ui ON c(b1,c1);
ANALYZE;

Query 1:

EXPLAIN QUERY PLAN
SELECT a1 FROM a WHERE EXISTS(SELECT * FROM b INNER JOIN c USING(b1) WHERE
b.a1=a.a1 AND c.c1=222);

selectidorder   fromdetail
0   0   0   SCAN TABLE a
0   0   0   EXECUTE CORRELATED SCALAR SUBQUERY 1
1   0   1   SEARCH TABLE c USING INTEGER PRIMARY KEY (rowid=?)
1   1   0   SEARCH TABLE b USING INTEGER PRIMARY KEY (rowid=?)

Query 2:

EXPLAIN QUERY PLAN
SELECT a1 FROM a WHERE EXISTS(SELECT c1 FROM b INNER JOIN c USING(b1) WHERE
b.a1=a.a1 AND c.c1=222);

selectidorder   fromdetail
0   0   0   SCAN TABLE a
0   0   0   EXECUTE CORRELATED SCALAR SUBQUERY 1
1   0   0   SEARCH TABLE b USING COVERING INDEX b_ui (a1=?)
1   1   1   SEARCH TABLE c USING COVERING INDEX c_ui (b1=?)

Note that the only difference between the two is "SELECT *" vs. "SELECT c1"
within the EXISTS-block. The result is the same in both cases, however the 
second query uses COVERING INDEXes which should be more efficient (as far as I 
know).

HTH; and sorry for the noise if not.

Wolfgang
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.