Re: [sqlite] PRAGMA database_list: insert into table?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/23/2010 07:47 PM, ve3meo wrote: > Is it possible to store the results of a PRAGMA statement, especially PRAGMA > database_list in a SQLite temporary table using only SQLite commands? No. Is there any particular reason your code can't copy them internally? Remember that SQLite is a library - it lives inside your application - and is not some remote unchangeable component. Depending on what the information is you are obtaining, it may be possible to get it via direct queries on sqlite_master. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkxKW9EACgkQmOOfHg372QQTSwCgwNSQxsC4utvX53H/iVYAJSiF nlgAniMMP0svxm59BjTeMJnr+EBGTnJt =kAD0 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] PRAGMA database_list: insert into table?
Is it possible to store the results of a PRAGMA statement, especially PRAGMA database_list in a SQLite temporary table using only SQLite commands? I have tried every combination I can think of without success. Tom ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with complex UPDATE question
Jim, I see what you mean. Would it be faster then if I read the from the table, do the math in a program, and then insert the values back into the table? Or would it faster to amend the table as you suggest and use SQL UPDATE? I wonder. Jim Morris-4 wrote: > > AYou must add additional data to the rows so you can refer to them > unambiguously. > > table1 (KEY, COL1, ord) > > 0, 1,1 > 0, 2,2 > 1, 3,1 > 1, 4,2 > 2, 5,1 > 2, 6,2 > 3, 7,1 > 3, 8,2 > > > On 7/23/2010 12:16 PM, peterwinson1 wrote: >> Jim you maybe correct that I don't have enough data to unambiguously >> identify >> the rows. But just in case I was not very clear the first time. >> >> What I want to do is take the COL1 values of the first 2 rows [1, 2] and >> subtract them from the COL1 values, two rows at a time. so [1, 2] - [1, >> 2], >> then [3, 4] - [1, 2], then [5, 6] - [1, 2], and finally [7, 8] - [1, 2]. >> The question is can I do this just using SQL? >> >> >> >> >> Jim Morris-4 wrote: >> >>> What you are trying to do is unclear to me. It seems that table1 >>> doesn't have enough data to unambiguously identify the rows. >>> >>> On 7/23/2010 8:03 AM, peterwinson1 wrote: >>> Thanks Eric and Alan for your help. I tried to apply your code to my problem and it works to a limited extent because the problem is more complicated than the example I gave in the post. I tries to simplify my exact problem but that didn't work out. So here is the problem that I trying to solve. table1 (KEY, COL1) 0, 1 0, 2 1, 3 1, 4 2, 5 2, 6 3, 7 3, 8 table2 (KEY, X, Y) 0, 0, 0 1, 0, 1 2, 1, 0 3, 1, 1 What I would like to do is, like before, subtract COL1 from COL1 where table1.KEY = 0 and WHERE table1.KEY is IN (SELECT table2 WHERE X=0). But I want to do a vector subtraction instead of a scalar subtraction. So far I have UPDATE table1 set COL1 = COL1 - (SELECT COL1 WHERE table1.KEY = 0) WHERE table1.KEY IN (SELECT table2 WHERE X=0) The result I would like to get is table1 0, 0 //(1 - 1) 0, 0 //(2 - 2) 1, 2 //(3 - 1) 1, 2 //(4 - 2) 2, 5 2, 6 3, 7 3, 8 Instead I get 0, 0 //(1 - 1) 0, 1 //(2 - 1) 1, 2 //(3 - 1) 1, 3 //(4 - 1) 2, 5 2, 6 3, 7 3, 8 Is this possible in SQL? peterwinson1 wrote: > Hello, > > I have a some what complex question about UPDATE. I have the > following > table > > table1 (KEY, COL1) > > 0, 1 > 1, 2 > 2, 3 > 3, 4 > > What I would like to do is to UPDATE COL1 by subtracting the COL1 > value > where KEY = 0 from the COL1 value of the current row so that the > result > would be. > > 0, 0 > 1, 1 > 2, 2 > 3, 3 > > Can this be done in SQL? It does not have to be one UPDATE/SELECT > statement. > > Thank you > pw > > > > > >>> ___ >>> 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 > > -- View this message in context: http://old.nabble.com/Help-with-complex-UPDATE-question-tp29239594p29252817.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with complex UPDATE question
You must add additional data to the rows so you can refer to them unambiguously. table1 (KEY, COL1, ord) 0, 1,1 0, 2,2 1, 3,1 1, 4,2 2, 5,1 2, 6,2 3, 7,1 3, 8,2 On 7/23/2010 12:16 PM, peterwinson1 wrote: > Jim you maybe correct that I don't have enough data to unambiguously identify > the rows. But just in case I was not very clear the first time. > > What I want to do is take the COL1 values of the first 2 rows [1, 2] and > subtract them from the COL1 values, two rows at a time. so [1, 2] - [1, 2], > then [3, 4] - [1, 2], then [5, 6] - [1, 2], and finally [7, 8] - [1, 2]. > The question is can I do this just using SQL? > > > > > Jim Morris-4 wrote: > >> What you are trying to do is unclear to me. It seems that table1 >> doesn't have enough data to unambiguously identify the rows. >> >> On 7/23/2010 8:03 AM, peterwinson1 wrote: >> >>> Thanks Eric and Alan for your help. I tried to apply your code to my >>> problem >>> and it works to a limited extent because the problem is more complicated >>> than the example I gave in the post. I tries to simplify my exact >>> problem >>> but that didn't work out. So here is the problem that I trying to solve. >>> >>> table1 (KEY, COL1) >>> >>> 0, 1 >>> 0, 2 >>> 1, 3 >>> 1, 4 >>> 2, 5 >>> 2, 6 >>> 3, 7 >>> 3, 8 >>> >>> table2 (KEY, X, Y) >>> >>> 0, 0, 0 >>> 1, 0, 1 >>> 2, 1, 0 >>> 3, 1, 1 >>> >>> What I would like to do is, like before, subtract COL1 from COL1 where >>> table1.KEY = 0 and WHERE table1.KEY is IN (SELECT table2 WHERE X=0). But >>> I >>> want to do a vector subtraction instead of a scalar subtraction. >>> >>> So far I have >>> >>> UPDATE table1 set COL1 = COL1 - (SELECT COL1 WHERE table1.KEY = 0) WHERE >>> table1.KEY IN (SELECT table2 WHERE X=0) >>> >>> The result I would like to get is >>> >>> table1 >>> >>> 0, 0 //(1 - 1) >>> 0, 0 //(2 - 2) >>> 1, 2 //(3 - 1) >>> 1, 2 //(4 - 2) >>> 2, 5 >>> 2, 6 >>> 3, 7 >>> 3, 8 >>> >>> Instead I get >>> >>> 0, 0 //(1 - 1) >>> 0, 1 //(2 - 1) >>> 1, 2 //(3 - 1) >>> 1, 3 //(4 - 1) >>> 2, 5 >>> 2, 6 >>> 3, 7 >>> 3, 8 >>> >>> Is this possible in SQL? >>> >>> >>> peterwinson1 wrote: >>> >>> Hello, I have a some what complex question about UPDATE. I have the following table table1 (KEY, COL1) 0, 1 1, 2 2, 3 3, 4 What I would like to do is to UPDATE COL1 by subtracting the COL1 value where KEY = 0 from the COL1 value of the current row so that the result would be. 0, 0 1, 1 2, 2 3, 3 Can this be done in SQL? It does not have to be one UPDATE/SELECT statement. Thank you pw >>> >>> >> ___ >> 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] Help with complex UPDATE question
Jim you maybe correct that I don't have enough data to unambiguously identify the rows. But just in case I was not very clear the first time. What I want to do is take the COL1 values of the first 2 rows [1, 2] and subtract them from the COL1 values, two rows at a time. so [1, 2] - [1, 2], then [3, 4] - [1, 2], then [5, 6] - [1, 2], and finally [7, 8] - [1, 2]. The question is can I do this just using SQL? Jim Morris-4 wrote: > > What you are trying to do is unclear to me. It seems that table1 > doesn't have enough data to unambiguously identify the rows. > > On 7/23/2010 8:03 AM, peterwinson1 wrote: >> Thanks Eric and Alan for your help. I tried to apply your code to my >> problem >> and it works to a limited extent because the problem is more complicated >> than the example I gave in the post. I tries to simplify my exact >> problem >> but that didn't work out. So here is the problem that I trying to solve. >> >> table1 (KEY, COL1) >> >> 0, 1 >> 0, 2 >> 1, 3 >> 1, 4 >> 2, 5 >> 2, 6 >> 3, 7 >> 3, 8 >> >> table2 (KEY, X, Y) >> >> 0, 0, 0 >> 1, 0, 1 >> 2, 1, 0 >> 3, 1, 1 >> >> What I would like to do is, like before, subtract COL1 from COL1 where >> table1.KEY = 0 and WHERE table1.KEY is IN (SELECT table2 WHERE X=0). But >> I >> want to do a vector subtraction instead of a scalar subtraction. >> >> So far I have >> >> UPDATE table1 set COL1 = COL1 - (SELECT COL1 WHERE table1.KEY = 0) WHERE >> table1.KEY IN (SELECT table2 WHERE X=0) >> >> The result I would like to get is >> >> table1 >> >> 0, 0 //(1 - 1) >> 0, 0 //(2 - 2) >> 1, 2 //(3 - 1) >> 1, 2 //(4 - 2) >> 2, 5 >> 2, 6 >> 3, 7 >> 3, 8 >> >> Instead I get >> >> 0, 0 //(1 - 1) >> 0, 1 //(2 - 1) >> 1, 2 //(3 - 1) >> 1, 3 //(4 - 1) >> 2, 5 >> 2, 6 >> 3, 7 >> 3, 8 >> >> Is this possible in SQL? >> >> >> peterwinson1 wrote: >> >>> Hello, >>> >>> I have a some what complex question about UPDATE. I have the following >>> table >>> >>> table1 (KEY, COL1) >>> >>> 0, 1 >>> 1, 2 >>> 2, 3 >>> 3, 4 >>> >>> What I would like to do is to UPDATE COL1 by subtracting the COL1 value >>> where KEY = 0 from the COL1 value of the current row so that the result >>> would be. >>> >>> 0, 0 >>> 1, 1 >>> 2, 2 >>> 3, 3 >>> >>> Can this be done in SQL? It does not have to be one UPDATE/SELECT >>> statement. >>> >>> Thank you >>> pw >>> >>> >>> >>> >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://old.nabble.com/Help-with-complex-UPDATE-question-tp29239594p29249685.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with complex UPDATE question
On 7/23/2010 10:09 AM, Jim Morris wrote: > What you are trying to do is unclear to me. It seems that table1 > doesn't have enough data to unambiguously identify the rows. > > On 7/23/2010 8:03 AM, peterwinson1 wrote: >> Thanks Eric and Alan for your help. I tried to apply your code to my problem >> and it works to a limited extent because the problem is more complicated >> than the example I gave in the post. I tries to simplify my exact problem >> but that didn't work out. So here is the problem that I trying to solve. >> >> table1 (KEY, COL1) >> >> 0, 1 >> 0, 2 >> 1, 3 >> 1, 4 >> 2, 5 >> 2, 6 >> 3, 7 >> 3, 8 >> It seems to me that you should really have: table1 (KEY, COL1, COL2) 0, 1, 2 1, 3, 4 2, 5, 6 3, 7, 8 based on the rest of your question. Is there a good reason you can not use a schema like this? Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with complex UPDATE question
What you are trying to do is unclear to me. It seems that table1 doesn't have enough data to unambiguously identify the rows. On 7/23/2010 8:03 AM, peterwinson1 wrote: > Thanks Eric and Alan for your help. I tried to apply your code to my problem > and it works to a limited extent because the problem is more complicated > than the example I gave in the post. I tries to simplify my exact problem > but that didn't work out. So here is the problem that I trying to solve. > > table1 (KEY, COL1) > > 0, 1 > 0, 2 > 1, 3 > 1, 4 > 2, 5 > 2, 6 > 3, 7 > 3, 8 > > table2 (KEY, X, Y) > > 0, 0, 0 > 1, 0, 1 > 2, 1, 0 > 3, 1, 1 > > What I would like to do is, like before, subtract COL1 from COL1 where > table1.KEY = 0 and WHERE table1.KEY is IN (SELECT table2 WHERE X=0). But I > want to do a vector subtraction instead of a scalar subtraction. > > So far I have > > UPDATE table1 set COL1 = COL1 - (SELECT COL1 WHERE table1.KEY = 0) WHERE > table1.KEY IN (SELECT table2 WHERE X=0) > > The result I would like to get is > > table1 > > 0, 0 //(1 - 1) > 0, 0 //(2 - 2) > 1, 2 //(3 - 1) > 1, 2 //(4 - 2) > 2, 5 > 2, 6 > 3, 7 > 3, 8 > > Instead I get > > 0, 0 //(1 - 1) > 0, 1 //(2 - 1) > 1, 2 //(3 - 1) > 1, 3 //(4 - 1) > 2, 5 > 2, 6 > 3, 7 > 3, 8 > > Is this possible in SQL? > > > peterwinson1 wrote: > >> Hello, >> >> I have a some what complex question about UPDATE. I have the following >> table >> >> table1 (KEY, COL1) >> >> 0, 1 >> 1, 2 >> 2, 3 >> 3, 4 >> >> What I would like to do is to UPDATE COL1 by subtracting the COL1 value >> where KEY = 0 from the COL1 value of the current row so that the result >> would be. >> >> 0, 0 >> 1, 1 >> 2, 2 >> 3, 3 >> >> Can this be done in SQL? It does not have to be one UPDATE/SELECT >> statement. >> >> Thank you >> pw >> >> >> >> > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recovering crashed database using C++ interface
On Fri, Jul 23, 2010 at 12:10 PM, Robert McVicar wrote: > I have the occasional instance of my application crashing, leaving > behind a journal file. However, on restart and opening the database > again, it doesn't seem to do any rollback action and I am unable to > write to the database. > Is there a C++ function that is necessary to trigger the rollback or > recovery to remove the journal file and release any locks no the > database? I have tried looking at the documentation, but am unable to > find anything. > The rollback is completely automatic, as is the release of all locks. There is nothing you need to do. Perhaps the reason you cannot write is that you do not have write permission on the database file or on the directory that contains the database file? > > Thanks > > R > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- - D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Recovering crashed database using C++ interface
I have the occasional instance of my application crashing, leaving behind a journal file. However, on restart and opening the database again, it doesn't seem to do any rollback action and I am unable to write to the database. Is there a C++ function that is necessary to trigger the rollback or recovery to remove the journal file and release any locks no the database? I have tried looking at the documentation, but am unable to find anything. Thanks R ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with complex UPDATE question
Thanks Eric and Alan for your help. I tried to apply your code to my problem and it works to a limited extent because the problem is more complicated than the example I gave in the post. I tries to simplify my exact problem but that didn't work out. So here is the problem that I trying to solve. table1 (KEY, COL1) 0, 1 0, 2 1, 3 1, 4 2, 5 2, 6 3, 7 3, 8 table2 (KEY, X, Y) 0, 0, 0 1, 0, 1 2, 1, 0 3, 1, 1 What I would like to do is, like before, subtract COL1 from COL1 where table1.KEY = 0 and WHERE table1.KEY is IN (SELECT table2 WHERE X=0). But I want to do a vector subtraction instead of a scalar subtraction. So far I have UPDATE table1 set COL1 = COL1 - (SELECT COL1 WHERE table1.KEY = 0) WHERE table1.KEY IN (SELECT table2 WHERE X=0) The result I would like to get is table1 0, 0 //(1 - 1) 0, 0 //(2 - 2) 1, 2 //(3 - 1) 1, 2 //(4 - 2) 2, 5 2, 6 3, 7 3, 8 Instead I get 0, 0 //(1 - 1) 0, 1 //(2 - 1) 1, 2 //(3 - 1) 1, 3 //(4 - 1) 2, 5 2, 6 3, 7 3, 8 Is this possible in SQL? peterwinson1 wrote: > > Hello, > > I have a some what complex question about UPDATE. I have the following > table > > table1 (KEY, COL1) > > 0, 1 > 1, 2 > 2, 3 > 3, 4 > > What I would like to do is to UPDATE COL1 by subtracting the COL1 value > where KEY = 0 from the COL1 value of the current row so that the result > would be. > > 0, 0 > 1, 1 > 2, 2 > 3, 3 > > Can this be done in SQL? It does not have to be one UPDATE/SELECT > statement. > > Thank you > pw > > > -- View this message in context: http://old.nabble.com/Help-with-complex-UPDATE-question-tp29239594p29248382.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Couple of questions about WAL
On Jul 23, 2010, at 8:56 PM, Doug wrote: > Thanks for your explanations Dan. The new WAL feature sounds great > and I'm > excited to try it. Two questions below: > >> When in WAL mode, clients use file-locks to implement a kind of >> robust (crash-proof) reference counting for each database file. >> When a client disconnects, if it is the only client connected to >> that database file, it automatically runs a checkpoint and >> then deletes the *-wal file. >> > ... >> On the other hand, if the only client connected to a database >> does not disconnect cleanly (i.e. it crashes, the system crashes, >> or the client exits without calling sqlite3_close()), then it >> leaves the *-wal file in place. In this case, when the next >> client connects to the database file it has to read the entire >> *-wal file to reconstruct the wal-index. If the *-wal file is >> large, this might take a while. > > With WAL mode if there is a crash, it seems like the reference > counting > would be messed up from that point on (meaning too high). In that > case, the > *-wal file will always exist, right? It wouldn't affect the database > robustness but I guess it would be a case where the startup > performance > being discussed would be affected. > > Also, is the reference counting per process or per connection? Well, that was a little bit deceptive, there is actually no explicit reference counting code in user space. SQLite uses file-locks to get the kernel to do it. When a process dies the kernel automatically cleans up any locks held by the process, so the reference count is kept straight even if a crash occurs. Basically each process holds a shared (read) lock on a well known region of the wal-index file. When a process connects, it tries to take an exclusive (write) lock on that same region. If successful, this proves the reference count just went from 0->1. So the process builds a fresh wal-index and downgrades to a shared lock. If it cannot get the exclusive lock there must be some other process already connected to the database. In this case it just grabs a shared lock and trusts that the wal-index has already been built. Beating the various race conditions makes things a bit more complex than the explanation above of course. But that's the gist of it. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Couple of questions about WAL
Thanks for your explanations Dan. The new WAL feature sounds great and I'm excited to try it. Two questions below: > When in WAL mode, clients use file-locks to implement a kind of > robust (crash-proof) reference counting for each database file. > When a client disconnects, if it is the only client connected to > that database file, it automatically runs a checkpoint and > then deletes the *-wal file. > ... > On the other hand, if the only client connected to a database > does not disconnect cleanly (i.e. it crashes, the system crashes, > or the client exits without calling sqlite3_close()), then it > leaves the *-wal file in place. In this case, when the next > client connects to the database file it has to read the entire > *-wal file to reconstruct the wal-index. If the *-wal file is > large, this might take a while. With WAL mode if there is a crash, it seems like the reference counting would be messed up from that point on (meaning too high). In that case, the *-wal file will always exist, right? It wouldn't affect the database robustness but I guess it would be a case where the startup performance being discussed would be affected. Also, is the reference counting per process or per connection? Thanks Doug ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Re ferring to subselect multiple times
> Is there a way? a) Temporary table b) Do it in your application instead of SQL - that's pretty easy. Pavel On Fri, Jul 23, 2010 at 5:17 AM, westmeadboy wrote: > > I have a complex query which returns multiple rows of a single TEXT column. > > I want to filter this so that only the longest strings are returned. In > other words, if the longest text is N chars long, then I want to return all > rows with N chars. > > If the query was on a table then this would be easy: > > SELECT mytext FROM mytable WHERE length(mytext) = (SELECT > MAX(length(mytext)) FROM mytable) > > But the problem is I want to use a subselect instead of mytable. > > Is there a way? > -- > View this message in context: > http://old.nabble.com/Referring-to-subselect-multiple-times-tp29245474p29245474.html > Sent from the SQLite mailing list archive at Nabble.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] Coping with database growth/fragmentation
On 23 Jul 2010, at 2:11am, Taras Glek wrote: > Recently I spent some time investigating sqlite IO patterns in Mozilla. > Two issues came up: keeping sqlite files from getting fragmented and > fixing fragmented sqlite files. If I understand correctly, there are two levels of fragmentation involved: disk fragmentation (where the sectors of the disk file are spread about) and database fragmentation (where the pages of database information are spread about the disk file). > First on fixing fragmentation: > Currently we write pretty heavily to our databases. This causes the > databases to grow, queries to slow down. Can I check that you are not just seeing fragmentation, but are actually seeing performance vary with fragmentation ? Because having that happen to an extent that's noticeable is something traditionally associated only with Windows, and your blog entry says you're using ext4 on Linux. Other platforms and file systems /have/ fragmentation, of course, but it doesn't normally slow them down as much as fragmentation slows down Windows. Some platforms handle this in unexpected ways. For instance, OS X will automatically defragment files smaller than 20MB each time they're opened. It won't defragment the database pages because, of course, it doesn't understand SQLite format. The easiest way to make a defragmented copy of a SQLite file would be to use the command-line tool to .dump a copy of a database to a text file, then again to .read that textfile into a database. Under Unix you can do it in one command: sqlite3 old_database.sqlite .dump | sqlite3 new_database.sqlite The resulting SQLite database file will not only be defragmented but will have some other optimal characteristics. I would be interested to know if you really do see performance improvements by doing this then replacing old_database.sqlite with new_database.sqlite . Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Coping with database growth/fragmentation
Hello Taras, List, I have been fighting the same problems described here for a long time, and have no real elegant solution. So, the proposed solution of the OP below would be ideal for me too. The proposed pragma could also define a number of pages to be allocated at once instead of a number of bytes. In my case, the database grows continously and the file is often extremely fragmented when the growth phase is finished (this concerns the file on the disk, not internal fragmentation) Currently, i monitor the size of the database using pragma freelist_count. When I see the value of free pages approach zero, i create a dummy table with a blob field and fill it with a very large empty blob. Then i drop the table. The empty pages remain behind and page_count does not rise any more for a time. This has been proposed to me on this list a while ago. However, testing the database in this way and creating and dropping the table carries a performance penalty, and finding the strategic places in my application to do this has been difficult. Martin Am 23.07.2010 03:11, schrieb Taras Glek: > Seems like the > easiest fix here is to add a pragma fs_allocation_size. It would > preallocate a continuous chunk of diskspace. Sqlite would behave exactly > as it does now, except it would avoid truncating the file beyond a > multiple of the fs_allocation_size. > For example, pragma fs_allocation_size=50M would grow the db file to > 50megabytes. Once the db grows to beyond 50mb the underlying file would > get resized to 100mb. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXTERNAL:Re: Very Slow DB Access After Reboot on Windows
On 23 Jul 2010, at 12:22pm, Black, Michael (IS) wrote: > #1 Are you using the same database file on both Windows and Linux or are you > creating it independently on each? I'm wondering if your Windows system is > fragmented (much more likely than your Linux system is to frag). How full is > the disk that your database is on and have you defragged that disk? > #2 How long does your fread program take to run? If must be much less then > 40 seconds I would imagine. > #3 How long does "select * from mytable" take when it's the first thing you > run on both Linux and Windows? > #4 Have you tried changing your caching to "System Cache" instead of > "Programs" jsut to see what happens? Change the extension of the file from .db to .sqlite or something else. Windows treats .db files in a special way. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXTERNAL:Re: Very Slow DB Access After Reboot on Windows
Hmmm...I would've thought copy would cache the file...learned something new
(again)but at least you've identified the problem as caching vs disk i/o.
#1 Are you using the same database file on both Windows and Linux or are you
creating it independently on each? I'm wondering if your Windows system is
fragmented (much more likely than your Linux system is to frag). How full is
the disk that your database is on and have you defragged that disk?
#2 How long does your fread program take to run? If must be much less then 40
seconds I would imagine.
#3 How long does "select * from mytable" take when it's the first thing you run
on both Linux and Windows?
#4 Have you tried changing your caching to "System Cache" instead of "Programs"
jsut to see what happens?
People with gigabyte-sized+ files are frequently out of luck if they have to
actually query the whole database (this is where indexes can come in real handy
to minimize how much reading goes on). But if you walk through all the data
caching the whole database isn't an option if it's bigger than available memory.
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org on behalf of Samet YASLAN
Sent: Fri 7/23/2010 3:05 AM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:Re: [sqlite] Very Slow DB Access After Reboot on Windows
I have tried changing the extension and also "copy my.db nul:". The
result does not change a bit. Still 40sec.
I also tried reading the file with fread before openning db connection like;
FILE* fp = fopen(fileName.c_str(), "rb");
if ( fp ) {
char pBuffer[1024*32];
while ( fread(pBuffer, 1024, 32, fp) > 0 )
{
}
fclose(fp);
}
This is very helpful and really decreases the total read performance
including this operation.
But if the file size is too big than this may lead to some problems again.
Anyway there must be some users reaching gigabytes of DB size and I am
not sure they are waiting one hour after restarting their PCs.
There must be a way to solve this problem. As I said before Linux is
just fine. I wish to see Linux on every PC one day.
*Samet YASLAN*
On 22.07.2010 22:51, Kees Nuyt wrote:
> On Thu, 22 Jul 2010 10:44:50 -0500, "Black, Michael (IS)"
> wrote:
>
>
>> Wrongread the docs...if copy didn't do binary by default there would so
>> many screwed up computers in the world
>>
>>
>> http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/copy.mspx?mfr=true
>>
>> Using /b
>>
> I stand corrected, thanks for the research.
>
___
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] Bug: Column Constraint
Not a bug. See the 4th paragraph at http://www.sqlite.org/lang_createtable.html#rowid On Thu, Jul 22, 2010 at 8:03 AM, sanjiv wrote: > When I use the following command string: >"CREATE TABLE tbl1 (Id INT CONSTRAINT PK_Id PRIMARY KEY > AUTOINCREMENT NOT NULL ,Name VARCHAR(20) )" > I get the following SQLite exception: >"AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY" > > wherease no exception is thrown if INT in the command is changed to > INTEGER. As per the affinity rules, INT should be treated as INTEGER. > > Thanks > > Sanjiv > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- - D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Coping with database growth/fragmentation
Hello, Recently I spent some time investigating sqlite IO patterns in Mozilla. Two issues came up: keeping sqlite files from getting fragmented and fixing fragmented sqlite files. First on fixing fragmentation: Currently we write pretty heavily to our databases. This causes the databases to grow, queries to slow down. VACUUM shrinks the databases down to a reasonable size(among other things) and queries speed up again, as expected. Problem is that databases get fragmented as they grow. VACUUM only reduces filesystem fragmentation proportionally to reduction in file size. It seems like the most obvious solution is to do some sort of a hot copy and switch to a new database connection to the copy. It would be nice if sqlite could do this by default. Is process described in http://www.sqlite.org/backup.html the best way to accomplish this? Avoiding Fragmentation: Writing to the db file causes fragmentation when the underlying filesystem fails to anticipate the sqlite growth pattern. Seems like the easiest fix here is to add a pragma fs_allocation_size. It would preallocate a continuous chunk of diskspace. Sqlite would behave exactly as it does now, except it would avoid truncating the file beyond a multiple of the fs_allocation_size. For example, pragma fs_allocation_size=50M would grow the db file to 50megabytes. Once the db grows to beyond 50mb the underlying file would get resized to 100mb. Clearly one can implement something like this via VFS handlers, but it would be nice to do this on a slightly higher level. Where in the code would this be appropriate? It would be great to get some help so I could submit a patch for this. I am pretty new to sqlite, so I could be on the wrong track here. Would like to see an expert opinion on these two approaches. There is also approach #3 of running a defrag tool to fix this, but I'd rather fix the cause of fragmentation. Thanks, Taras ps. For details see my blog post on fragmentation: http://blog.mozilla.com/tglek/2010/07/22/file-fragmentation/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug: Column Constraint
When I use the following command string: "CREATE TABLE tbl1 (Id INT CONSTRAINT PK_Id PRIMARY KEY AUTOINCREMENT NOT NULL ,Name VARCHAR(20) )" I get the following SQLite exception: "AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY" wherease no exception is thrown if INT in the command is changed to INTEGER. As per the affinity rules, INT should be treated as INTEGER. Thanks Sanjiv ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Version compatibility
Hi, I've been reading about version compatibility between different versions of sqlite at the link below: http://www.sqlite.org/formatchng.html It states the expected behaviour for old and new with a different first number, and a different second number, but not a different third number. I presume this means that a different third number means old and new are completely compatible but is there anything I can show to my QA person that states this? Cheers, Andy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Re ferring to subselect multiple times
I have a complex query which returns multiple rows of a single TEXT column. I want to filter this so that only the longest strings are returned. In other words, if the longest text is N chars long, then I want to return all rows with N chars. If the query was on a table then this would be easy: SELECT mytext FROM mytable WHERE length(mytext) = (SELECT MAX(length(mytext)) FROM mytable) But the problem is I want to use a subselect instead of mytable. Is there a way? -- View this message in context: http://old.nabble.com/Referring-to-subselect-multiple-times-tp29245474p29245474.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Very Slow DB Access After Reboot on Windows
I have tried changing the extension and also "copy my.db nul:". The
result does not change a bit. Still 40sec.
I also tried reading the file with fread before openning db connection like;
FILE* fp = fopen(fileName.c_str(), "rb");
if ( fp ) {
char pBuffer[1024*32];
while ( fread(pBuffer, 1024, 32, fp) > 0 )
{
}
fclose(fp);
}
This is very helpful and really decreases the total read performance
including this operation.
But if the file size is too big than this may lead to some problems again.
Anyway there must be some users reaching gigabytes of DB size and I am
not sure they are waiting one hour after restarting their PCs.
There must be a way to solve this problem. As I said before Linux is
just fine. I wish to see Linux on every PC one day.
*Samet YASLAN*
On 22.07.2010 22:51, Kees Nuyt wrote:
> On Thu, 22 Jul 2010 10:44:50 -0500, "Black, Michael (IS)"
> wrote:
>
>
>> Wrongread the docs...if copy didn't do binary by default there would so
>> many screwed up computers in the world
>>
>>
>> http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/copy.mspx?mfr=true
>>
>> Using /b
>>
> I stand corrected, thanks for the research.
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
