Re: [sqlite] Couple of questions about WAL
On Thursday, July 22, 2010 6:14 PM, Dan Kennedy wrote: > 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. > > So one thing to bear in mind when using WAL mode is always to > call sqlite3_close() on all database handles before process > exit. Otherwise you may leave a big *-wal file in the file-system > which will need to be traversed by the next client that connects > to the database. > Hi, I've read this thread with interest, but it leaves me with one particular question to which I haven't found a definitive answer elsewhere. I am using sqlite in embedded devices. In this case, there is no "shutdown" and the application never ends, so at the moment it never calls sqlite3_close. The user will simply turn off the device, when they're done. Am I right in saying that WAL mode is therefore not really compatible with this, since the sqlite3_close call is never made? Or is this what I use the sqlite3_wal_checkpoint api function for instead, or have I misunderstood its purpose? Or... do I have to encapsulate blocks of accesses to the database inside their own sqlite3_open/sqlite3_close calls (surely not!)? Thanks for helping with my confusion! Andy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems with 'make test' on 3.7.0
On Thursday, July 22, 2010 8:21 PM, Richard Hipp wrote:
>> > The configure command I'm using is:
>> >
>> > ./configure
>> > CFLAGS="-std=c99 -Werror" --enable-threadsafe --enable-debug
>> --with-tcl=/usr/lib/tcl8.4
>> >
>>
>> Sorry, I should add, I'm running on Debian Linux Lenny, with gcc 4.4.4,
>> because this is probably useful information ;o)
>>
>
> Have you tried omitting the CFLAGS argument?
>
Thank you for your reply. I have tried it without the CFLAGS, although I
have found that CFLAGS="-std=gnu99 -Werror" also compiles without error.
(It turns out the usleep and fsync are only prototyped in unistd.h if
__USE_BSD is defined). Either way, I now get as far as this:
wal2-10.1.1... Ok
wal2-10.1.2... Ok
wal2-10.1.3... Ok
wal2-10.1.4... Ok
wal2-10.2.1... Ok
wal2-10.2.2... Ok
wal2-10.2.3... Ok
wal2-11.0... Ok
wal2-11.1.1... Ok
./testfixture: bad field specifier "t"
while executing
"binary scan $I t* L"
(file "../checkout.new/test/wal2.test" line 987)
invoked from within
"source ../checkout.new/test/wal2.test"
invoked from within
"interp eval tinterp $script"
(procedure "slave_test_script" line 24)
invoked from within
"slave_test_script [list source $zFile] "
invoked from within
"time { slave_test_script [list source $zFile] }"
(procedure "slave_test_file" line 14)
invoked from within
"slave_test_file $::testdir/$file"
(procedure "run_tests" line 12)
invoked from within
"run_tests veryquick -presql {} -files {bigfile.test shared3.test
tkt3419.test where9.test fts2f.test sync.test fts1o.test misc2.test
server1.test tkt3..."
("uplevel" body line 1)
invoked from within
"uplevel run_tests $name $::testspec($name)"
(procedure "run_test_suite" line 5)
invoked from within
"run_test_suite veryquick"
(file "../checkout.new/test/veryquick.test" line 16)
make: *** [test] Error 1
Thanks for your help.
Regards
Andy
___
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
> If I do a BEGIN, SELECT1 and at that point a writer does BEGIN > IMMEDIATE, SELECT3, UPDATE, COMMIT, and then I continue with SELECT2 > COMMIT, will SELECT1 and SELECT2 have a consistent view of the > database > unaffected by the UPDATE in the middle. In other words, is the > Readers > view of how far up the WAL it is allowed to look get controlled by the > BEGIN ... COMMIT bracket rather than just the individual SELECTS it is > performing. SELECT1 and SELECT2 will see a consistent snapshot. SELECT2 will not see any data committed after the read transaction has been started. ___ 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 22/07/10 23:38, 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 > > UPDATE table1 SET COL1 = (COL1 - (SELECT COL1 FROM table1 WHERE key = 0)); -- Alan Chandler http://www.chandlerfamily.org.uk ___ 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
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. Not sure if it's optimal, but it works: update table1 set col1=col1-(SELECT col1 FROM table1 WHERE key=0); The full session: [hudson:~] $ sqlite3 SQLite version 3.6.20 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table table1(key integer primary key, col1 integer); sqlite> insert into table1 values(0,1); sqlite> insert into table1 values(1,2); sqlite> insert into table1 values(2,3); sqlite> insert into table1 values(3,4); sqlite> select * from table1; 0|1 1|2 2|3 3|4 sqlite> begin; sqlite> update table1 set col1=col1-(SELECT col1 FROM table1 WHERE key=0); sqlite> select * from table1; 0|0 1|1 2|2 3|3 Eric -- Eric A. Smith (1) Auto da fe (2) Beating with clubs (3) Beheading: Decapitation (4) Blowing from cannon (5) Boiling (6) Breaking on the wheel (7) Burning (8) Burying alive (9) Crucifixion (10) Decimation (11) Dichotomy (12) Dismemberment (13) Drowning (14) Exposure to wild beasts etc. (15) Flaying alive (16) Flogging: Knout (17) Garrote (18) Guillotine (19) Hanging (20) Hari kari (21) Impalement (22) Iron Maiden (23) Peine Forte et Dure (24) Poisoning (25) Pounding in mortar (26) Precipitation (27) Pressing to death (28) Rack (29) Running the gauntlet (30) Shooting (31) Stabbing (32) Stoning (33) Strangling (34) Suffocation. -- List of execution methods compiled by the New York State Commission to Investigate and Report the Most Humane and Practical Methods of Carrying into Effect the Sentence of Death, 1888 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Help with complex UPDATE question
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-tp29239594p29239594.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 22/07/10 17:14, Dan Kennedy wrote: > > 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. > Aah - this is an important point which I didn't get from the docs. I thought the wal was only cleared after the 1000 page watermark had been reached. > > So one thing to bear in mind when using WAL mode is always to > call sqlite3_close() on all database handles before process > exit. Otherwise you may leave a big *-wal file in the file-system > which will need to be traversed by the next client that connects > to the database. Thanks - that is an important point. I don't do that at the moment. I wrote >> 2) If the WAL is not synced back to the database (because the 1000 >> pages >> have yet to be created), is it still in non volatile storage, synced >> at >> the end of the last commit. In other words, if the server happens to >> get shut down and rebooted, will the WAL still be there and continue >> to >> function from the last committed transaction. (And, given question 1 >> the WAL index will just be rebuilt). > > Yes and yes. Assuming you are using synchronous=FULL. If using > synchronous=NORMAL then the contents of the WAL may or may not > have made it to persistent media when the crash occured. In this > case the checksums in the WAL are used to recover as many > transactions as possible. I am not so worries about a system crash killing a transaction in the middle as an otherwise quiescent system being shut down normally. However, as you say, the last closed database connection clears the WAL anyway, so I don't think this matters >> 4) Is the escalation of locks process process still in place. In >> particular, are the semantics of BEGIN, BEGIN IMMEDIATE and BEGIN >> EXCLUSIVE the same? (My current approach is that for web access that >> does only reads, I do a BEGIN, and then the SELECTS and then COMMIT, >> for >> one that has some INSERT and UPDATES, I do a BEGIN IMMEDIATE, do an >> application level check (via SELECT) on a version field on the >> database >> to ensure its the same as when I originally read it, ROLLBACK if it >> isn't, but if it is proceed with the INSERTS and UPDATES and finally >> COMMIT). > > In WAL mode, "BEGIN IMMEDIATE" and "BEGIN EXCLUSIVE" do the same > thing - open a write transaction. In both cases readers are not > affected (different from rollback mode - in rollback mode a > "BEGIN EXCLUSIVE" would lock out all readers). I have a worry about consistency of view rather than whether or not a reader is locked out. If I do a BEGIN, SELECT1 and at that point a writer does BEGIN IMMEDIATE, SELECT3, UPDATE, COMMIT, and then I continue with SELECT2 COMMIT, will SELECT1 and SELECT2 have a consistent view of the database unaffected by the UPDATE in the middle. In other words, is the Readers view of how far up the WAL it is allowed to look get controlled by the BEGIN ... COMMIT bracket rather than just the individual SELECTS it is performing. -- Alan Chandler http://www.chandlerfamily.org.uk ___ 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
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. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.7.0
Darren Duncan wrote: > I don't have time to investigate right now, but both failing tests seem > to be connected with concurrent access to a table by two forked processes > (the test script forks a child, which does concurrent access). > > At least in the second case, the DROP TABLE and CREATE TABLE commands > are issued by the main process (after the child has dropped table2) and > are supposed to succeed, so I believe there's something else going on than > changed error codes (unless they trigger a bug within SQLite itself). I assume you aren't sharing a single database connection object between the parent and child processes. That would violate one of SQLite's use assumptions and could definitely cause problems. Eric -- Eric A. Smith Money is the root of all wealth. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.7.0
Dan Kennedy wrote: > Is there any way your tests could be deleting a database > file while there is still an open sqlite connection to it? > > With 3.7.0, if the underlying database file is unlinked > while you are connected to it, then you try to write to > the database, you get SQLITE_IOERR_FSTAT. Earlier versions > would continue writing without causing an error. > > You cannot delete a file while it is open on windows, so > this doesn't come up on win32. > > This happened with a couple of Tcl tests too. Perhaps. I do know now that someone else with nearly the same platform as mine, Mac OS X, is having the same failures, and has narrowed it down to multi-process access to the same database. Or specifically, they said this: I don't have time to investigate right now, but both failing tests seem to be connected with concurrent access to a table by two forked processes (the test script forks a child, which does concurrent access). At least in the second case, the DROP TABLE and CREATE TABLE commands are issued by the main process (after the child has dropped table2) and are supposed to succeed, so I believe there's something else going on than changed error codes (unless they trigger a bug within SQLite itself). In any event, I have and continue to forward any helpful comments on the sqlite-users list to the dbd-sqlite (Perl binding) developers list, since many of them aren't here. I anticipate the solution may be to change how the DBD::SQLite tests work. I'll report here once something's worked out. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems with 'make test' on 3.7.0
On Thu, Jul 22, 2010 at 12:39 PM, Andy Gibbs wrote: > On Thursday, July 22, 2010 6:36 PM, Andy Gibbs wrote: > > > The configure command I'm using is: > > > > ./configure > > CFLAGS="-std=c99 -Werror" --enable-threadsafe --enable-debug > --with-tcl=/usr/lib/tcl8.4 > > > > Sorry, I should add, I'm running on Debian Linux Lenny, with gcc 4.4.4, > because this is probably useful information ;o) > Have you tried omitting the CFLAGS argument? -- - D. Richard Hipp d...@sqlite.org ___ 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
Regarding win/dos COPY command and /b option: Thanks for correcting me, Michael. I somehow thought that NUL being the target would introduce some sort of "text affinity" but it's good to know the truth instead. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] memory only table
Sam Carleton wrote: > I am using SQLite inside of Apache. I am using the Apache connection pool > system, so as long as the server is running there is always one connection > to the database. I have one very high traffic table with lots of reads and > writes, it turns out that this info does *NOT* need to be resident beyond > the life of the Apache server. Is there any way to create a table that is > in memory only and can be accessed by multiple connections? Put it into a separate database living on a RAM drive, perhaps? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems with 'make test' on 3.7.0
On Thursday, July 22, 2010 6:36 PM, Andy Gibbs wrote: > The configure command I'm using is: > > ./configure > CFLAGS="-std=c99 -Werror" --enable-threadsafe --enable-debug > --with-tcl=/usr/lib/tcl8.4 > Sorry, I should add, I'm running on Debian Linux Lenny, with gcc 4.4.4, because this is probably useful information ;o) Cheers Andy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problems with 'make test' on 3.7.0
Hi, Has anyone else had any problems building sqlite 3.7.0 out of the fossil repository? 'configure' and 'make' run fine, but 'make test' gives me: cc1: warnings being treated as errors src/test_demovfs.c: In function 'demoSync': src/test_demovfs.c:318: error: implicit declaration of function 'fsync' src/test_demovfs.c: In function 'demoSleep': src/test_demovfs.c:581: error: implicit declaration of function 'usleep' make: *** [testfixture] Error 1 The configure command I'm using is: ./configure CFLAGS="-std=c99 -Werror" --enable-threadsafe --enable-debug --with-tcl=/usr/lib/tcl8.4 Anyone else had this? Cheers Andy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] memory only table
I am using SQLite inside of Apache. I am using the Apache connection pool system, so as long as the server is running there is always one connection to the database. I have one very high traffic table with lots of reads and writes, it turns out that this info does *NOT* need to be resident beyond the life of the Apache server. Is there any way to create a table that is in memory only and can be accessed by multiple connections? Sam ___ 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 Thu, Jul 22, 2010 at 12:14 PM, Dan Kennedy wrote: > > So one thing to bear in mind when using WAL mode is always to > call sqlite3_close() on all database handles before process > exit. Otherwise you may leave a big *-wal file in the file-system > which will need to be traversed by the next client that connects > to the database. > > We had to make this change in Fossil to get it to play well with WAL. See http://www.fossil-scm.org/fossil/ci/932825bc6a For the diffs. -- - D. Richard Hipp d...@sqlite.org ___ 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 22, 2010, at 6:07 PM, Alan Chandler wrote: > I have been reading about WAL, and there are a few questions I would > like to ask. > > 1) I am slightly confused about readers building the WAL index. It > says > way down the page > > quote: > > Using an ordinary disk file to provide shared memory has the > disadvantage that it might actually do unnecessary disk I/O by writing > the shared memory to disk. However, the developers do not think this > is > a major concern since the wal-index rarely exceeds 32 KiB in size > and is > never synced. Furthermore, the wal-index backing file is deleted when > the last database connection disconnects, which often prevents any > real > disk I/O from ever happening. > > /quote > > In a situation where I have a web application (php based) in essence > each request makes a database connection, does a couple of queries, > and > exits (thereby closing the connection). Unless my site gets really > loaded (which in the application I am thinking of porting over to > SQLite > doesn't happen) it is quite frequent that there are no open database > connections. Does this mean that every web access has to rebuild the > index just to perform a query? is this a long process? 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. When the next client connects (incrementing the database ref-count from 0 to 1), it does have to rebuild the wal-index. But since the *-wal file is empty, this is pretty quick. 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. So one thing to bear in mind when using WAL mode is always to call sqlite3_close() on all database handles before process exit. Otherwise you may leave a big *-wal file in the file-system which will need to be traversed by the next client that connects to the database. > 2) If the WAL is not synced back to the database (because the 1000 > pages > have yet to be created), is it still in non volatile storage, synced > at > the end of the last commit. In other words, if the server happens to > get shut down and rebooted, will the WAL still be there and continue > to > function from the last committed transaction. (And, given question 1 > the WAL index will just be rebuilt). Yes and yes. Assuming you are using synchronous=FULL. If using synchronous=NORMAL then the contents of the WAL may or may not have made it to persistent media when the crash occured. In this case the checksums in the WAL are used to recover as many transactions as possible. In other words, in synchronous=NORMAL mode your transactions are not necessarily durable if a system crash occurs. You might lose any transactions written since the most recent checkpoint. > 3) When the 1000 page (or perhaps a smaller number that might be more > appropriate in my case) watermark is passed, does the writer (if not > interrupted) completely clear the WAL. It tries to. If there are long-running read transactions still using old database snapshots it might not be able to checkpoint the entire WAL. In this case it does as much as it can without overwriting any database pages that existing readers may still be using. > 4) Is the escalation of locks process process still in place. In > particular, are the semantics of BEGIN, BEGIN IMMEDIATE and BEGIN > EXCLUSIVE the same? (My current approach is that for web access that > does only reads, I do a BEGIN, and then the SELECTS and then COMMIT, > for > one that has some INSERT and UPDATES, I do a BEGIN IMMEDIATE, do an > application level check (via SELECT) on a version field on the > database > to ensure its the same as when I originally read it, ROLLBACK if it > isn't, but if it is proceed with the INSERTS and UPDATES and finally > COMMIT). In WAL mode, "BEGIN IMMEDIATE" and "BEGIN EXCLUSIVE" do the same thing - open a write transaction. In both cases readers are not affected (different from rollback mode - in rollback mode a "BEGIN EXCLUSIVE" would lock out all readers). ___ 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
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 /b directs the command interpreter to read the number of bytes specified by the file size in the directory. /b is the default value for copy, unless copy combines files. When /b precedes a list of files on the command line, it applies to all listed files until copy encounters /a. In this case, /a applies to the file preceding /a. When /b follows a list of files on the command line, it applies to all listed files until copy encounters /a. In this case, /a applies to the file preceding /a. The effect of /b depends on its position in the commandline string. When /b follows Source, copy copies the entire file, including any end-of-file character. When /b follows Destination, copy does not add an end-of-file character. Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of Kees Nuyt Sent: Thu 7/22/2010 10:18 AM To: sqlite-users@sqlite.org Subject: EXTERNAL:Re: [sqlite] Very Slow DB Access After Reboot on Windows On Thu, 22 Jul 2010 08:56:31 -0400, "Griggs, Donald" wrote: > > >Regarding: >Also...try doing a "copy my.db nul:" to get it cached once before you use > it. > > >Am I right in thinking he may want to include the "/b" (binary) option so that >the copy doesn't stop at the first nul byte? > > copy /b my.db nul You are right. -- ( Kees Nuyt ) c[_] ___ 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] Very Slow DB Access After Reboot on Windows
On Thu, 22 Jul 2010 08:56:31 -0400, "Griggs, Donald" wrote: > > >Regarding: >Also...try doing a "copy my.db nul:" to get it cached once before you use > it. > > >Am I right in thinking he may want to include the "/b" (binary) option so that >the copy doesn't stop at the first nul byte? > > copy /b my.db nul You are right. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Linking SQLite 3.7.0 with PHP 5.3.2 -- php undefined symbols sqlite3_column_table_name
When I attempted to link PHP 5.3.2 with SQLite 3.7.0 I got an undefined symbol sqlite3_column_table_name. By adding -DSQLITE_ENABLE_COLUMN_METADATA to my SQLite CFLAGS in the configure, I was able to link the PHP 5.3.2 Apache Module: CFLAGS="-arch x86_64 -DSQLITE_ENABLE_COLUMN_METADATA" \ ./configure --prefix=/usr/local \ --enable-readline \ --enable-threadsafe I didn't have to do this for previous SQLite versions. NBD (No Big Deal). -- Jeffrey Thompson JANOAH, INC. Alpharetta, GA Office: 678-373-4157 jeff...@janoah.net http://twitter.com/janoahinc "where the Spirit of the Lord is, there is liberty" -- 2 Corinthians 3:17 ___ 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
Regarding: Also...try doing a "copy my.db nul:" to get it cached once before you use it. Am I right in thinking he may want to include the "/b" (binary) option so that the copy doesn't stop at the first nul byte? copy /b my.db nul ___ 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
Also...try doing a "copy my.db nul:" to get it cached once before you use it. You're probably running into disk head seeking the first time (due to random placement of your data relative to your query) which will slow you down a lot. Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of Simon Slavin Sent: Wed 7/21/2010 9:28 PM To: General Discussion of SQLite Database Subject: EXTERNAL:Re: [sqlite] Very Slow DB Access After Reboot on Windows On 21 Jul 2010, at 4:56pm, Samet YASLAN wrote: > I have a 30MB DB file with 4 tables. > Execution time for a query is 1 sec normally but it is like 40 secs > after restarting window. > This seems to be related with file caching of Windows. The same source > code does not cause this problem on Linux. What is the name of the database file ? I understand that there is a Windows bug where it tries to cache all of files with certain extensions like '.db'. Try changing the filename extention to '.sqlite' or something else unusual. Simon. ___ 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] SQLite version 3.7.0
On Thu, Jul 22, 2010 at 1:31 PM, Dan Kennedy wrote: [snip] > You cannot delete a file while it is open on windows, so > this doesn't come up on win32. Sure you can, except: - The correct sharing rights have to be specified for this to be allowed (FILE_SHARE_DELETE). As far as I know SQLite opens the files without this sharing right. - The file is deleted after the last handle to it is closed. This means that the same filename couldn't be reused until all the handles are closed. F. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.7.0
On Jul 22, 2010, at 1:08 PM, Darren Duncan wrote: > Roger Binns wrote: >> On 07/21/2010 08:01 PM, Darren Duncan wrote: >>> Simply substituting in 3.7.0 causes a few new test failures for me >>> with the Perl >>> binding, DBD::SQLite, citing "disk I/O error". >> >> I can't speak for the Perl binding, but some of the underlying error >> handling (invalid filenames) have been tweaked between the Unix and >> Windows >> VFS implementations. (I believe they tried to make both consistent >> with >> each other.) >> >> For example with my test suite running on Windows, invalid >> filenames used to >> get False returned from xAccess but now I get IO Error. With normal >> operation there is no problem. >> >> What this means is that you'll need someone who understands the >> DBD:SQLite >> tests to say what the issue is :-) > > On that note, I got this report from someone on Windows: > > Latest SVN trunk tested on win32 Strawberry perl v1.12.1 : all > tests pass, > no problem. > > ... and I was using a Unixen. Is there any way your tests could be deleting a database file while there is still an open sqlite connection to it? With 3.7.0, if the underlying database file is unlinked while you are connected to it, then you try to write to the database, you get SQLITE_IOERR_FSTAT. Earlier versions would continue writing without causing an error. You cannot delete a file while it is open on windows, so this doesn't come up on win32. This happened with a couple of Tcl tests too. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Couple of questions about WAL
I have been reading about WAL, and there are a few questions I would like to ask. 1) I am slightly confused about readers building the WAL index. It says way down the page quote: Using an ordinary disk file to provide shared memory has the disadvantage that it might actually do unnecessary disk I/O by writing the shared memory to disk. However, the developers do not think this is a major concern since the wal-index rarely exceeds 32 KiB in size and is never synced. Furthermore, the wal-index backing file is deleted when the last database connection disconnects, which often prevents any real disk I/O from ever happening. /quote In a situation where I have a web application (php based) in essence each request makes a database connection, does a couple of queries, and exits (thereby closing the connection). Unless my site gets really loaded (which in the application I am thinking of porting over to SQLite doesn't happen) it is quite frequent that there are no open database connections. Does this mean that every web access has to rebuild the index just to perform a query? is this a long process? 2) If the WAL is not synced back to the database (because the 1000 pages have yet to be created), is it still in non volatile storage, synced at the end of the last commit. In other words, if the server happens to get shut down and rebooted, will the WAL still be there and continue to function from the last committed transaction. (And, given question 1 the WAL index will just be rebuilt). 3) When the 1000 page (or perhaps a smaller number that might be more appropriate in my case) watermark is passed, does the writer (if not interrupted) completely clear the WAL. 4) Is the escalation of locks process process still in place. In particular, are the semantics of BEGIN, BEGIN IMMEDIATE and BEGIN EXCLUSIVE the same? (My current approach is that for web access that does only reads, I do a BEGIN, and then the SELECTS and then COMMIT, for one that has some INSERT and UPDATES, I do a BEGIN IMMEDIATE, do an application level check (via SELECT) on a version field on the database to ensure its the same as when I originally read it, ROLLBACK if it isn't, but if it is proceed with the INSERTS and UPDATES and finally COMMIT). -- Alan Chandler http://www.chandlerfamily.org.uk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
