Re: [sqlite] Join performance in SQLite

[Nicolas Williams]

On Sat, May 30, 2009 at 07:01:31PM +0100, Simon Slavin wrote:
> I'm interested in how sqlite works differently to the SQL systems  
> which keep a daemon running as a background task.  One of the  
> advantages of having a daemon which persists between runs of an  
> application is that the daemon can keep its own list of ORDERs, and  
> JOINs which are asked for frequently, and decide to maintain them even  
> when no SQL-using application is running.  [...]

You don't need a daemon to do that.  One could use a special table in
the database itself (much like the master table) to keep statistics
about all sorts of things.

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

Re: [sqlite] Feature request

[Jim Wilcoxson]

I read through the header after Simon's request, and saw the data
change counter he mentions.  What I wasn't sure about is whether this
number gets reset during a rollback.  For example, you start a
transaction, make some changes, some cache pages get flushed, and
SQLite changes this number so that other processes know that the
database has changed.  In fact, SQLite may change this number anytime
it changes pages in my local cache - not sure.  The header word seems
to be designed to prevent stale cache lookups.

If I rollback my changes, it would be unimportant for cache management
whether the value rolled back also, but it would be important if the
data didn't actually change for the value to roll back.  Or, you could
use the value as "data might have changed", but then it would be less
useful for file synchronization.

Jim

On 5/30/09, Simon Slavin  wrote:
>
> On 23 May 2009, at 3:32pm, Filip Navara wrote:
>
>> PRAGMA schema_version ... for the second case.
>
> Okay.  Given that this does exactly what I want from one of my
> requests, and given Jim Wilcoxson's point that adding a PRAGMA is
> better than adding a function to the library, I can simplify my
> feature request to asking for something like
>
> PRAGMA content_version  OR  PRAGMA data_version
>
> which returns the number stored in bytes 24 to 27 of the header.  This
> should have only a small impact on library size, and require very
> little extra code since the number needs to be worked out anyway for
> storage in the header.  It should be useful for purposes associated
> with synchronisation and journaling but it's mostly so that
> applications which store some data outside SQL and some inside can
> tell if they need to worry about something messing with their data.
>
> It doesn't matter to me whether a schema-change is considered a
> content-change or not.  I can sum the two in my own code if needed.
> But the documentation should describe whether field-changes, or
> COMMITs, or whatever is counted.
>
> I know I can read the file's header myself (and that's what my current
> solution does) but this means I need to include file-handing libraries
> in my library which I don't otherwise need.  It's not a neat solution.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


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

Re: [sqlite] Join performance in SQLite

[Jim Wilcoxson]

SQLite has surprised me with its quick performance, not the other way
around.  In fact, I've implemented all kinds of lookup queries that I
knew could be optimized by caching results so I didn't have to keep
repeating the SQL query, but the performance was so good even
repeating the queries that I never bothered with the caching.

I'm sure there are queries that SQLite doesn't run as fast as database
product X, and I'm sure it goes the other way too.  It's a balancing
act, and as the primary developer, you have to choose for us what's
important to optimize and what isn't.

So far, I'm very happy with the choices and trade-offs that have been
made in SQLite. :-)

Jim

On 5/30/09, Simon Slavin  wrote:
> I'm interested in how sqlite works differently to the SQL systems
> which keep a daemon running as a background task.  One of the
> advantages of having a daemon which persists between runs of an
> application is that the daemon can keep its own list of ORDERs, and
> JOINs which are asked for frequently, and decide to maintain them even
> when no SQL-using application is running.  This can give the
> impression that something is being done very quickly, when in fact the
> majority of the time was taken during a previous run of the
> application.  It can be particularly hard to figure out what a
> performance test means under these circumstances.
>
> But the problem is that I like the way sqlite works.  I like the tiny
> library, I like the way that the SQL library is entirely inside my
> application, and any CPU load is mine.  I like knowing that when my
> app quits, nothing is going on.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


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

Re: [sqlite] Deleting database content : SQLite3 API

[Hamish Allan]

On Sat, May 30, 2009 at 1:20 PM,   wrote:

> Yes , I understand that. Infact I was doing that through a script during 
> system startup. I wanted to know whether SQLite provides any API to do the 
> same.

No, and it doesn't provide any API for changing access permissions on
the database file, or moving or copying it from one part of the
filesystem to another, or adding extended attributes to it, or sending
kernel notifications when it is modified, either. Nor does it provide
any API for on-the-fly JPEG decompression, sending tweets, processing
LaTeX documents, or taking cosines. Why would it?

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

Re: [sqlite] Feature request

[Simon Slavin]

On 23 May 2009, at 3:32pm, Filip Navara wrote:

> PRAGMA schema_version ... for the second case.

Okay.  Given that this does exactly what I want from one of my  
requests, and given Jim Wilcoxson's point that adding a PRAGMA is  
better than adding a function to the library, I can simplify my  
feature request to asking for something like

PRAGMA content_version  OR  PRAGMA data_version

which returns the number stored in bytes 24 to 27 of the header.  This  
should have only a small impact on library size, and require very  
little extra code since the number needs to be worked out anyway for  
storage in the header.  It should be useful for purposes associated  
with synchronisation and journaling but it's mostly so that  
applications which store some data outside SQL and some inside can  
tell if they need to worry about something messing with their data.

It doesn't matter to me whether a schema-change is considered a  
content-change or not.  I can sum the two in my own code if needed.   
But the documentation should describe whether field-changes, or  
COMMITs, or whatever is counted.

I know I can read the file's header myself (and that's what my current  
solution does) but this means I need to include file-handing libraries  
in my library which I don't otherwise need.  It's not a neat solution.

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

Re: [sqlite] Join performance in SQLite

[Simon Slavin]

I'm interested in how sqlite works differently to the SQL systems  
which keep a daemon running as a background task.  One of the  
advantages of having a daemon which persists between runs of an  
application is that the daemon can keep its own list of ORDERs, and  
JOINs which are asked for frequently, and decide to maintain them even  
when no SQL-using application is running.  This can give the  
impression that something is being done very quickly, when in fact the  
majority of the time was taken during a previous run of the  
application.  It can be particularly hard to figure out what a  
performance test means under these circumstances.

But the problem is that I like the way sqlite works.  I like the tiny  
library, I like the way that the SQL library is entirely inside my  
application, and any CPU load is mine.  I like knowing that when my  
app quits, nothing is going on.

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

Re: [sqlite] Join performance in SQLite

[John Elrick]

D. Richard Hipp wrote:
> There has been a recent flurry of comments about SQLite at
>
>  http://www.reddit.com/r/programming/comments/8oed5/how_sqlite_is_tested/
>  http://news.ycombinator.com/item?id=633151
>
> One of the criticisms of SQLite is that it is slow to do joins.  That  
> is true if SQLite is unable to figure out how to use an index to speed  
> the join.  I was under the impression that SQLite actually did a  
> fairly reasonable job of making use of indices, if they exist.  But  
> without indices, an k-way join takes time proportional to N^k.
>   

We're finishing a system which produces auto generated queries where k 
can potentially range from 1 to 16.  The only times I have seen Sqlite 
slowdown were when the indexes needed tweaking.

I noticed one of the comments made was that a three inner join query 
took 10 minutes and that the joins should have filtered the table, 
however, I did not notice any example, contrived or obfuscated, which 
would demonstrate the issue.  This absence means we must rely upon the 
author's interpretation of what the query did being accurate.  The 
author makes the assertion that each progressive inner join should make 
the search table smaller, however, just because that was the intent does 
not make it the reality.  The fact that temp tables with no apparent 
indexing ran faster makes me question whether or not the initial 
assumption was true.

I've been wrong far too often about the actual vs theoretical of my code 
in operation to accept anything such as this at face value; however, 
that caveat would apply from both directions.  The author said he 
reported it as a bug, which implies he presented a repeatable test 
case.  If that is so, that specific test case might merit further 
examination.

FWIW


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

Re: [sqlite] Join performance in SQLite

[Mark Hamburg]

Assuming memory is sufficiently inexpensive, I would think that it  
would almost always be useful to build an index for any field in a  
join rather than doing a full scan. (Or better yet, build a hash table  
if memory is sufficient.) Indices maintained in the database then  
become optimizations to avoid starting the query with an index build.

Mark

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

Re: [sqlite] Deleting database content : SQLite3 API

[John Stanton]

What would be the point of a function which just performs a syste call 
like unlink?  Bloat?

souvik.da...@wipro.com wrote:
> Yes , I understand that. Infact I was doing that through a script during 
> system startup. I wanted to know whether SQLite provides any API to do the 
> same.  
>
> Thanks and Regards,
> Souvik
> -Original Message-
> From: sqlite-users-boun...@sqlite.org on behalf of John Stanton
> Sent: Sat 5/30/2009 5:30 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Deleting database content : SQLite3 API
>  
> An Sqlite database is just  a file.  Delete the file  when you start 
> your program and when you open the database Sqlite will create  a fresh 
> dne, a very low overhead process..
> .
> souvik.da...@wipro.com wrote:
>   
>> Hello,
>>  
>> Please let me know if we have an API in SQLite3 which allows me to
>> retain the database but delete it's content at runtime. The problem I am
>> facing is that : Every time I restart my system , I need to create the
>> database. If the database exits already it's contents need to be
>> deleted. The issue is that the contents of the database varies during
>> one power ON - Power OFF cycle . As a result , after finding that the
>> database already exits at the system startup, I cannot just drop the
>> tables. ( As the table which are present in the existing data base is
>> not known. )
>>  
>> I am using sqlite3wrapped C++ library APIs.
>>  
>> Thanks and Regards,
>> Souvik
>>  
>>
>> Please do not print this email unless it is absolutely necessary. 
>>
>> The information contained in this electronic message and any attachments to 
>> this message are intended for the exclusive use of the addressee(s) and may 
>> contain proprietary, confidential or privileged information. If you are not 
>> the intended recipient, you should not disseminate, distribute or copy this 
>> e-mail. Please notify the sender immediately and destroy all copies of this 
>> message and any attachments. 
>>
>> WARNING: Computer viruses can be transmitted via email. The recipient should 
>> check this email and any attachments for the presence of viruses. The 
>> company accepts no liability for any damage caused by any virus transmitted 
>> by this email. 
>>
>> www.wipro.com
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>   
>> 
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> Please do not print this email unless it is absolutely necessary. 
>
> The information contained in this electronic message and any attachments to 
> this message are intended for the exclusive use of the addressee(s) and may 
> contain proprietary, confidential or privileged information. If you are not 
> the intended recipient, you should not disseminate, distribute or copy this 
> e-mail. Please notify the sender immediately and destroy all copies of this 
> message and any attachments. 
>
> WARNING: Computer viruses can be transmitted via email. The recipient should 
> check this email and any attachments for the presence of viruses. The company 
> accepts no liability for any damage caused by any virus transmitted by this 
> email. 
>
> www.wipro.com
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>   

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

Re: [sqlite] Join performance in SQLite

[Pavel Ivanov]

> Do other SQL database engines not have this same limitation?  Are
> MySQL and PostgreSQL and Firebird and MS-SQL and Oracle creating
> phantom indices on-the-fly to help them do joins faster, for example?

Sort of. There's 2 types of join methods in Oracle for this - Hash
joins and Sort merge joins - when server creates in memory (or in
temporary storage in general) sorted data for one or both merging data
sets and then merges these sets. You can read about it here:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#i51523.
Not sure though if it's worth to implement such technique in SQLite.

Pavel

On Sat, May 30, 2009 at 11:11 AM, D. Richard Hipp  wrote:
> There has been a recent flurry of comments about SQLite at
>
>     http://www.reddit.com/r/programming/comments/8oed5/how_sqlite_is_tested/
>     http://news.ycombinator.com/item?id=633151
>
> One of the criticisms of SQLite is that it is slow to do joins.  That
> is true if SQLite is unable to figure out how to use an index to speed
> the join.  I was under the impression that SQLite actually did a
> fairly reasonable job of making use of indices, if they exist.  But
> without indices, an k-way join takes time proportional to N^k.
>
> Do other SQL database engines not have this same limitation?  Are
> MySQL and PostgreSQL and Firebird and MS-SQL and Oracle creating
> phantom indices on-the-fly to help them do joins faster, for example?
> Or do their optimizers do a better job of finding ways to use indices
> in a join?  Can somebody supply me with specific examples of joins
> that other database engines do efficiently but that SQLite does
> slowly?  Is join efficiency really a frustration to many SQLite users?
>
> Curiously, in some of our own internal tests, SQLite is much, much
> faster than MS-SQL, MySQL, and PostgreSQL for k-way joins where k is
> large - greater than 20 or 30.  (SQLite can handle up to a 64-way
> join.)  This is because SQLite uses a O(k*k) greedy algorithm for
> selecting the ordering of tables in the join whereas the other guys
> all do a much more extensive search.  So the performance loss in the
> other engines is due to the excessive time spent in the query planner,
> not the time actually running the query.  SQLite can plan a 64-way
> join in the blink of an eye, whereas PostgreSQL requires several
> minutes.
>
> But for the tests described in the previous paragraph, there were
> always good indices so that the time to actually run the join was
> approximately linear.  What about situations where you have a 4- or 5-
> way join on tables that are not indexed?  Do other database engines
> handle those more efficiently than SQLite somehow?  Is this something
> we need to look into?
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GROUPY BY alias backward incompatibility

[Pavel Ivanov]

>  * The query that works on SQLite all versions fails on Oracle.

False conclusion. Did you try to make only one row in t?

>  * Behaviour is inconsistent between MySQL and Oracle.

I believe this conclusion is also false. Did you try several rows in t
on MySQL? If it worked I wonder how it showed you the results?

>  * The query that fails on SQLite > 3.5.3 works on both MySQL and Oracle.

Regardless of that my opinion is that the query is nonsense and any
effort should not be taken to make it work.


Pavel

On Sat, May 30, 2009 at 4:30 AM, Ralf Junker  wrote:
> At 15:37 28.05.2009, D. Richard Hipp wrote:
>
>>Have you tried these two queries on other SQL database engines besides
>>SQLite?  What do PostgreSQL and MySQL make of them?
>
> I could now run the queries on Oracle Database 10g Express Edition Release 
> 10.2.0.1.0.
>
> Prepare the table:
>
>  create table t (c integer);
>
>  insert into t values (1);
>  insert into t values (2);
>
> Query with t_outer (the one that fails SQLite > 3.5.3) runs without error:
>
>  select
>    (select count(*) from t t_inner
>     group by t_outer.c)             -- t_outer !!!
>  from t t_outer;
>
> Query with t_inner (which works on all SQLite versions) fails with error 
> "ORA-01427: single-row subquery returns more than one row":
>
>  select
>    (select count(*) from t t_inner
>     group by t_inner.c)             -- t_inner !!!
>  from t t_outer;
>
> Preliminary conclusion:
>
>  * Behaviour is inconsistent between MySQL and Oracle.
>
>  * The query that fails on SQLite > 3.5.3 works on both MySQL and Oracle.
>
>  * The query that works on SQLite all versions fails on Oracle.
>
> Additional findings from other DB engines would be helpful. Anyone?
>
> Other than that, I believe it would be desirable if SQLite would support the 
> t_outer query as it did up to 3.5.3.
>
> Ralf
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Join performance in SQLite

[D. Richard Hipp]

There has been a recent flurry of comments about SQLite at

 http://www.reddit.com/r/programming/comments/8oed5/how_sqlite_is_tested/
 http://news.ycombinator.com/item?id=633151

One of the criticisms of SQLite is that it is slow to do joins.  That  
is true if SQLite is unable to figure out how to use an index to speed  
the join.  I was under the impression that SQLite actually did a  
fairly reasonable job of making use of indices, if they exist.  But  
without indices, an k-way join takes time proportional to N^k.

Do other SQL database engines not have this same limitation?  Are  
MySQL and PostgreSQL and Firebird and MS-SQL and Oracle creating  
phantom indices on-the-fly to help them do joins faster, for example?   
Or do their optimizers do a better job of finding ways to use indices  
in a join?  Can somebody supply me with specific examples of joins  
that other database engines do efficiently but that SQLite does  
slowly?  Is join efficiency really a frustration to many SQLite users?

Curiously, in some of our own internal tests, SQLite is much, much  
faster than MS-SQL, MySQL, and PostgreSQL for k-way joins where k is  
large - greater than 20 or 30.  (SQLite can handle up to a 64-way  
join.)  This is because SQLite uses a O(k*k) greedy algorithm for  
selecting the ordering of tables in the join whereas the other guys  
all do a much more extensive search.  So the performance loss in the  
other engines is due to the excessive time spent in the query planner,  
not the time actually running the query.  SQLite can plan a 64-way  
join in the blink of an eye, whereas PostgreSQL requires several  
minutes.

But for the tests described in the previous paragraph, there were  
always good indices so that the time to actually run the join was  
approximately linear.  What about situations where you have a 4- or 5- 
way join on tables that are not indexed?  Do other database engines  
handle those more efficiently than SQLite somehow?  Is this something  
we need to look into?

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Deleting database content : SQLite3 API

[Kees Nuyt]

On Sat, 30 May 2009 17:50:36 +0530, 
wrote:

>
>Yes , I understand that. Infact I was doing that 
>through a script during system startup. I wanted 
>to know whether SQLite provides any API to do the same.  

No, it doesn't. You could write it yourself:

foreach $name in \
sql(SELECT name FROM sqlite_master WHERE type='table';)
do
sql(DELETE FROM $name;)
done

If you use a startup script, there is no need for the C API,
you might as well do something like:

  sqlite3 dbfile .schema|sqlite3 dbfile.new

or (if the database is not overly large)

  sqlite3 dbfile .dump|grep - v INSERT|sqlite3 dbfile.new

You may want to add a few initialization PRAGMA's to the
pipe.

>Thanks and Regards,
>Souvik
>-Original Message-
>From: sqlite-users-boun...@sqlite.org on behalf of John Stanton
>Sent: Sat 5/30/2009 5:30 PM
>To: General Discussion of SQLite Database
>Subject: Re: [sqlite] Deleting database content : SQLite3 API
> 
>An Sqlite database is just  a file.  Delete the file  when you start 
>your program and when you open the database Sqlite will create  a fresh 
>dne, a very low overhead process..
>.
>souvik.da...@wipro.com wrote:
>> Hello,
>>  
>> Please let me know if we have an API in SQLite3 which allows me to
>> retain the database but delete it's content at runtime. The problem I am
>> facing is that : Every time I restart my system , I need to create the
>> database. If the database exits already it's contents need to be
>> deleted. The issue is that the contents of the database varies during
>> one power ON - Power OFF cycle . As a result , after finding that the
>> database already exits at the system startup, I cannot just drop the
>> tables. ( As the table which are present in the existing data base is
>> not known. )
>>  
>> I am using sqlite3wrapped C++ library APIs.
>>  
>> Thanks and Regards,
>> Souvik
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Deleting database content : SQLite3 API

[John Machin]

On 30/05/2009 10:20 PM, souvik.da...@wipro.com wrote:

[top-posting unscrambled]

[first message]
 >> As a result , after finding that the
 >> database already exits at the system startup, I cannot just drop the
 >> tables. ( As the table which are present in the existing data base is
 >> not known. )

They are easily knowable. Otherwise how could the database work? Use 
"select * from sqlite_master;"

[2nd message, in response to suggestion to delete the file and start 
with a new one]
> Yes , I understand that. Infact I was doing that through a script
 > during system startup. I wanted to know whether SQLite provides
 > any API to do the same.

No, why should it? "Lite" means among other things don't reproduce what 
is readily available elsewhere. Consider using the facilities provided 
by your C++ runtime libraries.

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

Re: [sqlite] Deleting database content : SQLite3 API

[souvik.datta]

Yes , I understand that. Infact I was doing that through a script during system 
startup. I wanted to know whether SQLite provides any API to do the same.  

Thanks and Regards,
Souvik
-Original Message-
From: sqlite-users-boun...@sqlite.org on behalf of John Stanton
Sent: Sat 5/30/2009 5:30 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Deleting database content : SQLite3 API
 
An Sqlite database is just  a file.  Delete the file  when you start 
your program and when you open the database Sqlite will create  a fresh 
dne, a very low overhead process..
.
souvik.da...@wipro.com wrote:
> Hello,
>  
> Please let me know if we have an API in SQLite3 which allows me to
> retain the database but delete it's content at runtime. The problem I am
> facing is that : Every time I restart my system , I need to create the
> database. If the database exits already it's contents need to be
> deleted. The issue is that the contents of the database varies during
> one power ON - Power OFF cycle . As a result , after finding that the
> database already exits at the system startup, I cannot just drop the
> tables. ( As the table which are present in the existing data base is
> not known. )
>  
> I am using sqlite3wrapped C++ library APIs.
>  
> Thanks and Regards,
> Souvik
>  
>
> Please do not print this email unless it is absolutely necessary. 
>
> The information contained in this electronic message and any attachments to 
> this message are intended for the exclusive use of the addressee(s) and may 
> contain proprietary, confidential or privileged information. If you are not 
> the intended recipient, you should not disseminate, distribute or copy this 
> e-mail. Please notify the sender immediately and destroy all copies of this 
> message and any attachments. 
>
> WARNING: Computer viruses can be transmitted via email. The recipient should 
> check this email and any attachments for the presence of viruses. The company 
> accepts no liability for any damage caused by any virus transmitted by this 
> email. 
>
> www.wipro.com
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>   

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


Please do not print this email unless it is absolutely necessary. 

The information contained in this electronic message and any attachments to 
this message are intended for the exclusive use of the addressee(s) and may 
contain proprietary, confidential or privileged information. If you are not the 
intended recipient, you should not disseminate, distribute or copy this e-mail. 
Please notify the sender immediately and destroy all copies of this message and 
any attachments. 

WARNING: Computer viruses can be transmitted via email. The recipient should 
check this email and any attachments for the presence of viruses. The company 
accepts no liability for any damage caused by any virus transmitted by this 
email. 

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

Re: [sqlite] Deleting database content : SQLite3 API

[John Stanton]

An Sqlite database is just  a file.  Delete the file  when you start 
your program and when you open the database Sqlite will create  a fresh 
dne, a very low overhead process..
.
souvik.da...@wipro.com wrote:
> Hello,
>  
> Please let me know if we have an API in SQLite3 which allows me to
> retain the database but delete it's content at runtime. The problem I am
> facing is that : Every time I restart my system , I need to create the
> database. If the database exits already it's contents need to be
> deleted. The issue is that the contents of the database varies during
> one power ON - Power OFF cycle . As a result , after finding that the
> database already exits at the system startup, I cannot just drop the
> tables. ( As the table which are present in the existing data base is
> not known. )
>  
> I am using sqlite3wrapped C++ library APIs.
>  
> Thanks and Regards,
> Souvik
>  
>
> Please do not print this email unless it is absolutely necessary. 
>
> The information contained in this electronic message and any attachments to 
> this message are intended for the exclusive use of the addressee(s) and may 
> contain proprietary, confidential or privileged information. If you are not 
> the intended recipient, you should not disseminate, distribute or copy this 
> e-mail. Please notify the sender immediately and destroy all copies of this 
> message and any attachments. 
>
> WARNING: Computer viruses can be transmitted via email. The recipient should 
> check this email and any attachments for the presence of viruses. The company 
> accepts no liability for any damage caused by any virus transmitted by this 
> email. 
>
> www.wipro.com
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>   

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

[sqlite] Deleting database content : SQLite3 API

[souvik.datta]

Hello,
 
Please let me know if we have an API in SQLite3 which allows me to
retain the database but delete it's content at runtime. The problem I am
facing is that : Every time I restart my system , I need to create the
database. If the database exits already it's contents need to be
deleted. The issue is that the contents of the database varies during
one power ON - Power OFF cycle . As a result , after finding that the
database already exits at the system startup, I cannot just drop the
tables. ( As the table which are present in the existing data base is
not known. )
 
I am using sqlite3wrapped C++ library APIs.
 
Thanks and Regards,
Souvik
 

Please do not print this email unless it is absolutely necessary. 

The information contained in this electronic message and any attachments to 
this message are intended for the exclusive use of the addressee(s) and may 
contain proprietary, confidential or privileged information. If you are not the 
intended recipient, you should not disseminate, distribute or copy this e-mail. 
Please notify the sender immediately and destroy all copies of this message and 
any attachments. 

WARNING: Computer viruses can be transmitted via email. The recipient should 
check this email and any attachments for the presence of viruses. The company 
accepts no liability for any damage caused by any virus transmitted by this 
email. 

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

Re: [sqlite] GROUPY BY alias backward incompatibility

[Ralf Junker]

At 15:37 28.05.2009, D. Richard Hipp wrote:

>Have you tried these two queries on other SQL database engines besides  
>SQLite?  What do PostgreSQL and MySQL make of them?

I could now run the queries on Oracle Database 10g Express Edition Release 
10.2.0.1.0.

Prepare the table:

  create table t (c integer);

  insert into t values (1);
  insert into t values (2);

Query with t_outer (the one that fails SQLite > 3.5.3) runs without error:

  select
(select count(*) from t t_inner
 group by t_outer.c) -- t_outer !!!
  from t t_outer;

Query with t_inner (which works on all SQLite versions) fails with error 
"ORA-01427: single-row subquery returns more than one row":

  select
(select count(*) from t t_inner
 group by t_inner.c) -- t_inner !!!
  from t t_outer;

Preliminary conclusion:

  * Behaviour is inconsistent between MySQL and Oracle.

  * The query that fails on SQLite > 3.5.3 works on both MySQL and Oracle.

  * The query that works on SQLite all versions fails on Oracle.

Additional findings from other DB engines would be helpful. Anyone?

Other than that, I believe it would be desirable if SQLite would support the 
t_outer query as it did up to 3.5.3.

Ralf 

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

Re: [sqlite] Is this test failure serious: *malloc*.transient.42 failures (expected [1 1], got [0 {}])?

[Nicolas Williams]

On Thu, May 28, 2009 at 04:38:50PM -0400, D. Richard Hipp wrote:
> >> To debug, set a breakpoint on sqlite3Fault().  That routine is
> >> called whenever an OOM fault is simulated.  Run to the point of the
> >> OOM  fault that is causing the problem.  Figure out which malloc()
> >> is falling  and why the problem is not being reported back up to
> >> the top level.

The stack trace when sqlite3Fault() is called is this:

14671:  ./testfixture test/a.test   

-  lwp# 1 / thread# 1   

 0807a065 sqlite3Fault (8139018, 1e8, 8043e1c, 8086a7f, 1e8, 1e4) + 1   

 0807a0de faultsimMalloc (1e8, 1e4, 8043e1c, 80866aa) + e   

 08086a7f mallocWithAlarm (1e4) + 63

 08086b1f sqlite3Malloc (1e4, 81451b8, 8043e78, fef5e6ad) + 4b  

 08087029 sqlite3MallocZero (1e4, 8130d00, 8043eac, 80d3d9f) + 11   

 080d3e54 openDatabase (8043ef0) + cc   

 08083802 DbMain   (0, 8115090, 3, 8120fa8) + 32a   

 fef17834 TclEvalObjvInternal (8115090, 3, 8120fa8, 0, 0, 0) + 200  

 fef3bf35 TclExecuteByteCode (8115090, 8161ed0) + 1199  

 fef3ad06 TclCompEvalObj (8115090, 8144e88) + 112   

 fef18661 Tcl_EvalObjEx (8115090, 8144e88, 0) + 69  

...
 fef52d07 Tcl_EvalFile (8115090, 8046e16, 80e890c, 1) + 2f  

 08083c9d main (2, 8046c98, 8046ca4, 8046c8c) + 231 

 08068a9d _start   (2, 8046e08, 8046e16, 0, 8046e22, 8046e35) + 7d  

-  lwp# 2 / thread# 2   

 fed1cd07 pollsys  (fec2ed30, 1, 0, 0)  

 fecd337e pselect  (4, fec2ee10, fec2ee90, fec2ef10, 0, 0) + 19e

 fecd368e select   (4, fec2ee10, fec2ee90, fec2ef10, 0) + 7e

 fef7e521 NotifierThreadProc (0) + 279  

 fed190a0 _thrp_setup (feb20200) + 70   

 fed19300 _lwp_start (feb20200, 0, 0, 0, 0, 0)  


Tracing function entry/return from that point I see this:

  1  <- sqlite3Fault  0 

  1  <- faultsimStep  1 

  1  <- faultsimMalloc0 

  1  <- mallocWithAlarm 488 

  1  -> sqlite3_mutex_leave13270897144254570

  1-> pthreadMutexLeave13270897144257718

  1<- pthreadMutexLeave   135264012 

  1-> mutex_unlock 13270897144264723

  1  -> mutex_unlock_queue 13270897144269040

  1-> clear_lockbyte   13270897144272919

  1<- clear_lockbyte   16777216 
   

Re: [sqlite] Corruption of incremental_vacuum databases

[Filip Navara]

On Sat, May 30, 2009 at 7:43 AM, John Machin  wrote:
> On 17/04/2009 1:39 AM, Filip Navara wrote:
>> Hello,
>>
>> I have expected at least some reply. Oh well, new the corruption has happened
>> again (on another different machine) and I have saved the database files. One
>> of the corrupted files is available at 
>> http://www.emclient.com/temp/folders.zip.
>
> U ... your first message [scroll down to read] is talking about
> *incremental* vacuuming; however the database file that you made
> available has FULL (not incremental) auto-vacumming set.

I switch to incremental vacuum mode at run-time once the database is opened.

> Six orphan pages at the end of the file plus another 3 orphans suggests
> that an auto-vacuum (full or incremental) may have been interrupted --
> or perhaps later given that you are? were? using synchronous=off.

While I was using synchronous=off in this instance, it happened even
with synchronous=FULL. There was never a power failure during the
tests, only the application may have been killed few times.

> Did you get any resolution on this?

Updating to latest SQLite, which includes change #6413, fixed it.
Unfortunately it created a lot of other problems that are hopefully
now solved with the 3.6.14.2 release.

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