Re: [sqlite] Join performance in SQLite
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
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 Slavinwrote: > > 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
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 Slavinwrote: > 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
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
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
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
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
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
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
> 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 Hippwrote: > 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
> * 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 Junkerwrote: > 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
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
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
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
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
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
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
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 {}])?
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
On Sat, May 30, 2009 at 7:43 AM, John Machinwrote: > 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