Re: [sqlite] SQLJet - pure Java implementation of SQLite
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Fred Williams wrote: > I say there is no known translation that > would allow the three SQLite, "Small, Fast, Reliable" adjectives to > translate into any regurgitated language output, with the exception of > compiling SQLite source with a C++ compiler :-) If you read Bernstein's retrospective on qmail, one of his suggestions is to write code in a higher level language (especially more secure in the sense that C isn't such as preventing buffer & integer overflows etc) that is then transcoded to C. (Some conspiracy theorists looking at his code claimed he actually did do this :-) So in theory given unlimited CPU and memory it should be possible to take the SQLite C code and turn it into something higher level and then turn that back into the more verbose platforms (.NET, Java). The big advantage of this approach (after the upfront work) is that keeping up with SQLite progress is easy. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkqCTmwACgkQmOOfHg372QSCUwCeKD+V+e7yjYgbcF9e+lXhAbbE U9cAoIzXPblugsswnhbgnmTNYSrkdMj7 =VYQ3 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLJet - pure Java implementation of SQLite
Having had the unfortunate opportunity to use a couple of language translators as well as spending about six fruitless months developing one which in the end was no better, I say there is no known translation that would allow the three SQLite, "Small, Fast, Reliable" adjectives to translate into any regurgitated language output, with the exception of compiling SQLite source with a C++ compiler :-) Fred -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]on Behalf Of Roger Binns Sent: Tuesday, August 11, 2009 7:15 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLJet - pure Java implementation of SQLite -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Alexander Kitaev wrote: > Not to depend on native SQLite binaries or > opaque NestedVM code, As a matter of interest what problem exactly do you have with NestedVM? It's output is indeed opaque (not human comprehensible) but the same is true of Java source versus bytecode. In both cases the input source is readable. It would also be interesting if anyone has built something that comprehends the SQLite C source and then does the conversion into other languages based on that. It would make updates a lot easier, the generation of instrumented and test code easier, and the search for issues or optimisations easier. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkqCCWgACgkQmOOfHg372QQXqwCeJ4pqKa89vcCAxTQOelMyoPU6 cuQAoK6Feey6AL3pdzMgv983tn8Yg1ML =TKoq -END PGP SIGNATURE- ___ 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] UPDATE 11 times slower than SELECT?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Nikolaus Rath wrote: > Is it normal that UPDATE still takes more than 11 times as much time as > SELECT, or should I be able to get better performance? The UPDATE time also includes the time waiting to acquire file locks. If you have concurrent activity it could take a while. If you want to break out the lock time then restructure your sql to be: BEGIN IMMEDIATE; UPDATE ... ; COMMIT The BEGIN will then show up in apswtrace separately as the transaction wait time. Also note that these times are wall clock times so heavy threading could cause a lot of variance. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkqCOIAACgkQmOOfHg372QSWYgCfVRDkPdWcjJooIrUjsIM2iLLw bWQAnjC2SDgdtE87DcMbjXQjRN2F38fU =lLYM -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE 11 times slower than SELECT?
"Igor Tandetnik" writes: > Nikolaus Rath wrote: >> When running apswtrace on my Python program, I got the following >> output: >> >> LONGEST RUNNING - AGGREGATE >> >> 16638 11.041 UPDATE inodes SET size=MAX(size,?), ctime=?, mtime=? >> WHERE id=? 16638 0.938 SELECT s3key FROM inode_s3key WHERE inode=? >> AND offset=? >> >> i.e. both statements were executed 16638 times, but execution of the >> first took 11 seconds in total and execution of the second only 0.9 >> seconds. > > Select likely gets data from in-memory cache. Update has to physically > write to disk. Memory is much faster than disk. Why does it have write to the disk? With PRAGMA synchronous = off, SQLite should return as soon as the data is in the kernel cache, shouldn't it? > If at all possible, group write operations together into a transaction. Not possible here, unfortunately. The queries do not run sequentially but are triggered by external events. Best, -Nikolaus -- »Time flies like an arrow, fruit flies like a Banana.« PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE 11 times slower than SELECT?
Nikolaus Rath wrote: > When running apswtrace on my Python program, I got the following > output: > > LONGEST RUNNING - AGGREGATE > > 16638 11.041 UPDATE inodes SET size=MAX(size,?), ctime=?, mtime=? > WHERE id=? 16638 0.938 SELECT s3key FROM inode_s3key WHERE inode=? > AND offset=? > > i.e. both statements were executed 16638 times, but execution of the > first took 11 seconds in total and execution of the second only 0.9 > seconds. Select likely gets data from in-memory cache. Update has to physically write to disk. Memory is much faster than disk. If at all possible, group write operations together into a transaction. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] UPDATE 11 times slower than SELECT?
Hi, When running apswtrace on my Python program, I got the following output: LONGEST RUNNING - AGGREGATE 16638 11.041 UPDATE inodes SET size=MAX(size,?), ctime=?, mtime=? WHERE id=? 16638 0.938 SELECT s3key FROM inode_s3key WHERE inode=? AND offset=? i.e. both statements were executed 16638 times, but execution of the first took 11 seconds in total and execution of the second only 0.9 seconds. I was slightly surprised by this. The UPDATE statement just writes one integer and 2 reals. There is an index on the id column, and I have activated PRAGMA temp_store = 2 and PRAGMA synchronous = off. Is it normal that UPDATE still takes more than 11 times as much time as SELECT, or should I be able to get better performance? Best, -Nikolaus -- »Time flies like an arrow, fruit flies like a Banana.« PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLJet - pure Java implementation of SQLite
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Alexander Kitaev wrote: > Not to depend on native SQLite binaries or > opaque NestedVM code, As a matter of interest what problem exactly do you have with NestedVM? It's output is indeed opaque (not human comprehensible) but the same is true of Java source versus bytecode. In both cases the input source is readable. It would also be interesting if anyone has built something that comprehends the SQLite C source and then does the conversion into other languages based on that. It would make updates a lot easier, the generation of instrumented and test code easier, and the search for issues or optimisations easier. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkqCCWgACgkQmOOfHg372QQXqwCeJ4pqKa89vcCAxTQOelMyoPU6 cuQAoK6Feey6AL3pdzMgv983tn8Yg1ML =TKoq -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Busy Handler in Shared Cache Mode?
Hi, There has been a bit more discussion about this on http://code.google.com/p/apsw/issues/detail?id=59. I guesss Roger would be quite interested in the test case you mentioned. I have now decided to simply deactivate shared cache again. It seems the benefits aren't worth it in my case. As a side note: I think that my own program may actually suffer from problems with the Python GIL rather than SQLite locking. I discovered then when I ran in single threaded mode, the total execution time reduces by a factor of about 2.3... Best, Nikolaus "Edzard Pasma" writes: > Hi Nikolaus, > > Interesting to read your findings. I assume you finally disallowed > read_uncommitted. Trying to explain why the wait times can get longer, > I come to two reasons. > > - the time slots where a read-lock is obtained become smaller. > This can be significant if there are a lot of transactions with little > time in between. With the busy handling by polling, a reader may mis > slots. This will sure be improved by Unlock Notification. > > - the time slots where a read-lock can not be obtained become longer. > This can cause problems if there are long-running transaction. The > Unlock Notification feature is not going to help here. > > I still like to share an observation from my own test, which > reproduces the first case. I start four reading threads and one > writer. Each thread performs a fixed number of cycles. Apart from the > wait times, also the overall elapsed time is measured. Indeed the > maximum wait-time gets bigger if shared cache mode is enabled. > Interestingly, this does not apply to the elapsed time, which is still > reduced. Thus, an increase of the maximum wait-time is not necessarily > a worse performance. By the way, this was measured on a > single-processor system. > > Hope this is useful to know. > > Best regards, Edzard. > > On Fri, Aug 7, 2009 at 1:45 PM, Nikolaus > Rath wrote: >> Hello, >> >> I have program that continuously runs several threads (about 4 to >> 10) >> which concurrently read and write to an SQLite database. From >> http://www.sqlite.org/sharedcache.html it seems to me that I should >> be >> using shared cache mode. >> >> Until now, I have set the busy timeout to 500 msec and never had to > deal >> with SQLITE_BUSY errors. However, there seems to be no corresponding >> function for the SQLITE_LOCKED errors that are generated in shared > cache >> mode. So I changed the code manually sleep for a random amount (0 to > 100 >> msec) and then try to execute the statement again if it encounters >> SQLITE_LOCKED. But now the threads are often waiting for more than 1 >> second before they finally get their query executed. >> >> I suspect this is because the random sleep is wasting a lot of time, >> while without shared cache (and with enabled busy timeout) a thread >> blocks and gets revived as soon as the lock on the database is >> released. >> >> >> How can I avoid this problem? Is there a way to set a busy timeout >> for >> SQLITE_LOCKED as well? Or a more clever method instead of sleeping >> for >> random amounts? Or should I just avoid using shared cache mode? > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -Nikolaus -- »Time flies like an arrow, fruit flies like a Banana.« PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLJet - pure Java implementation of SQLite
Hello All, I'd like to invite those users who program in Java to take a look at our new project - SQLJet. SQLJet was started as part of another projects that has to work with SQLite database (SVNKit). Not to depend on native SQLite binaries or opaque NestedVM code, we've implemented part of SQLite core functionality in Java, and later moved that code to a separate project which we called SQLJet. SQLJet is an independent open source implementation of the SQLite core functionality; SQLJet provides API to read and modify SQLite database schema and data, but not to execute SQL queries yet. SQLJet project home page is at http://sqljet.com/ - you'll find mailing list, other information on SQLJet library and, of course, library itself there. We appreciate any feedback you may have on SQLJet library and we would be glad to prioritize further library development accordingly to your ideas and requests. Thanks, -- Alexander Kitaev, TMate Software, http://svnkit.com/ - Java [Sub]Versioning Library! http://sqljet.com/ - Java SQLite Library! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Help building SQLite project, please
This isn't directly related to SQLite, but rather to how I am converting the source code into a DLL. I hope someone here can help me understand what's happening. It appears that my previous problem was caused by some problem within the ancient, unbelievably slow and ugly library we have been using to talk to our SQLite database. It appears that that library will not allow any data to be written into any previously existing table. So, I wanted to write a little demonstration program that would add data to an existing table, using nothing but plain ordinary functions from the sqlite library. I created a dialog-based application with a button. When I click the button, a message box appears. I added a call to sqlite3_open(). When I start my application, even before my dialog's constructor or InitiInstance() get called, I get an error message: "Application failed to initialize properly" and an error code of 0xc07b. When I click OK, I get another saying "Win32 error: Path Not Found". I checked the other place in our code where we rely on SQLite: an ActiveX control that displays a graph. Instead of the graph, I got the blank box with a red X. Microsoft's Dependency Walker tool showed me there was a problem with the sqlite3_v3 DLL I was using. The DLL's icon was red, and the icon for every function in the DLL was red. On our test computer, Dependency Walker shows sqlite3_v3 DLL as normal, with green icons. When I copy the DLL from the test computer onto mine, the ActiveX control behaves normally, but I still get the 0xc07b error with my demo program. On the test computer, the bottom pane of Dependency Walker tells me that sqlite3_v3's subsystem is "Win32 console" and its base is 0x6090. But on my computer, the tool tells me that sqlite3_v3's subsystem is "Win32 GUI" and its base is 0x1000. That looks strange, because of course there's no GUI used in SQLite. That looks like a problem, but I don't know what's causing it. Can anyone suggest what setting I might have to change to get back to a console subsystem for my library? Thanks very much! RobR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] resource temporarily unavailable after sqlite3_open()
D. Richard Hipp , On 8/11/2009 16:39: > program is using LinuxThreads or NPTL for its threading. (SQLite has > to know which is used because there are serious bugs in LinuxThreads > that SQLite has to work around.) So pthreads gets used once, by > SQLite, even if you don't do any threading in your application. > Probably something about that pthread_create() call is messing up the > exec. > > If you app does not use threads, then by all means compile with > SQLITE_THREADSAFE=0 because that makes SQLite run faster. > Would it help to know, where threading is enabled in the library, if pthread_create() fails? Is there a function that can tell me the status? Or, is there a switch that will force sqlite to use native thread library instead? Fortunately my app isn't forking out SqLite tasks. It does fork itself to run as a daemon, tho. All my tests were done *before *forking. I tried the same test cases after the fork and got the same results. /m ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] resource temporarily unavailable after sqlite3_open()
On Aug 11, 2009, at 4:28 PM, Mark Richards wrote: > D. Richard Hipp , On 8/11/2009 16:02: >> >> Perhaps pthreads is going goofy. Please recompile with - >> DSQLITE_THREADSAFE=0 and see if that helps. >> > BINGO. > > env CC="gcc-cris -mlinux -isystem $EROOT/include"CPP="gcc-cris > -mlinux -E -isystem $EROOT/include" CXX="g++-cris -mlinux -xc++ > -isystem $EROOT/include" LDFLAGS="-L$EROOT/lib" > OBJCOPY="objcopy-cris" LD="ld-cris -mcrislinux" AR="ar-cris" > RANLIB="ranlib-cris" NM="nm-cris" STRIP="strip-cris" ./configure > --host=cris-axis-linux-gnu > --prefix=/AEMDEV/83+/devboard-R2_10/target/cris-axis-linux-gnu > --enable-static=yes --enable-shared=yes --disable-threadsafe -- > disable-tcl > > > Works like a charm. > > How can I help find the issue with my test case? On linux, SQLite has to start a thread in order to figure out if the program is using LinuxThreads or NPTL for its threading. (SQLite has to know which is used because there are serious bugs in LinuxThreads that SQLite has to work around.) So pthreads gets used once, by SQLite, even if you don't do any threading in your application. Probably something about that pthread_create() call is messing up the exec. If you app does not use threads, then by all means compile with SQLITE_THREADSAFE=0 because that makes SQLite run faster. > > /m > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 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] resource temporarily unavailable after sqlite3_open()
D. Richard Hipp , On 8/11/2009 16:02: > > Perhaps pthreads is going goofy. Please recompile with - > DSQLITE_THREADSAFE=0 and see if that helps. > BINGO. env CC="gcc-cris -mlinux -isystem $EROOT/include"CPP="gcc-cris -mlinux -E -isystem $EROOT/include" CXX="g++-cris -mlinux -xc++ -isystem $EROOT/include" LDFLAGS="-L$EROOT/lib" OBJCOPY="objcopy-cris" LD="ld-cris -mcrislinux" AR="ar-cris" RANLIB="ranlib-cris" NM="nm-cris" STRIP="strip-cris" ./configure --host=cris-axis-linux-gnu --prefix=/AEMDEV/83+/devboard-R2_10/target/cris-axis-linux-gnu --enable-static=yes --enable-shared=yes --disable-threadsafe --disable-tcl Works like a charm. How can I help find the issue with my test case? /m ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] resource temporarily unavailable after sqlite3_open()
On Aug 11, 2009, at 2:53 PM, Mark Richards wrote: > > When sqlite3_open() is called as above, got returns -1 (forever). -1 > returning from a NONBLOCK read is accepted behaviour, but pretty > quickly > the read() should give some data. Instead it returns -1 each time. > (If > I allow fd[0] to be in blocking mode, the read() never returns). > > Thinking that perhaps the > > A mangled stdout came to mind, but this does not appear to be the > case. > But maybe sqlite_open() or other internals diddle with stdout? SQLite shouldn't be messing with stdin or stdout. We've done things like this before. We know it works. Perhaps pthreads is going goofy. Please recompile with - DSQLITE_THREADSAFE=0 and see if that helps. 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] resource temporarily unavailable after sqlite3_open()
Yes, tried that. No change in result. Assuming sqlite does a |F_GETFD| then restores when done. I built in some testing to see if the file descriptor was being mangled, and cannot detect any difference. Test: flags = fcntl(fd[0], F_GETFL, 0); char *cFlagMsg; cFlagMsg=calloc(255,sizeof(char)); snprintf(cFlagMsg,255,"%s","FLAGS: "); if (flags & O_NONBLOCK) strcat(cFlagMsg,"O_NONBLOCK "); if (flags & O_APPEND) strcat(cFlagMsg,"O_APPEND "); if (flags & O_DSYNC) strcat(cFlagMsg,"O_DSYNC "); if (flags & O_RSYNC) strcat(cFlagMsg,"O_RSYNC "); if (flags & O_SYNC) strcat(cFlagMsg,"O_SYNC "); Results: // before change to O_NONBLOCK GETFL Flags= 0 // after change to O_NONBLOCK GETFL Flags= 2048 FLAGS: O_NONBLOCK I inserted this code in place of my original calls to open and close: sqlite3 *db; char *zErrMsg = 0; int rc; rc = sqlite3_open("/etc/solarwave/aem.db", &db); if( rc ) { WriteSyslogMessage("Can't open database: %s\n", sqlite3_errmsg(db)); sqlite3_close(db); exit(1); } rc = sqlite3_exec(db, "SELECT * FROM dbsensors", NULL, 0, &zErrMsg); if( rc!=SQLITE_OK ) { WriteSyslogMessage("SQL error: %s\n", zErrMsg); sqlite3_free(zErrMsg); } sqlite3_close(db); (Note: WriteSyslogMessage() issued a system("logger 'string'"); call) Same issue. -1 returned during read(). /m D. Richard Hipp , On 8/11/2009 15:38: > On Aug 11, 2009, at 2:53 PM, Mark Richards wrote: > > >> Environment: >> Linux axis 2.6.19 #9 PREEMPT Mon Apr 6 15:44:03 EDT 2009 cris >> unknown >> >> Sqlite: >> Sqlite: sqlite-3.6.14 >>./configure --host=cris-axis-linux-gnu >> --prefix=/AEMDEV/83+/devboard-R2_10/target/cris-axis-linux-gnu >> --enable-static=yes --enable-shared=yes --disable-dynamic-extensions >> >> Application: >> My application runs against the shared library built as above. >> >> Code in my application fails after calling sqlite3_open(). >> >> This synopsis is of a function designed to spawn a shell, execute a >> command, and read back the result via a pipe of stdout. It works >> fine, >> until sqlite3_open() is called anywhere PRIOR. >> >> prior sqlite3_open() call: >> sqlite3 *dbf; >> sqlite3_open("/path/to/my.db",&dbf); >> >> >> fflush(stdout); >> pipe(fd); >> pid = fork(); >> if (pid == 0) >> { >> dup2(fd[1], STDOUT_FILENO); >> dup2(fd[1], STDERR_FILENO); >> close(fd[0]); >> > Have you tried called sqlite3_close() here to see if that helps? All > of SQLite's file descriptors are FD_CLOEXEC, but who knows > > >> execl("/bin/sh", "sh", "-c", "echo 123", 0); >> } >> if (-1 == (flags = fcntl(fd[0], F_GETFL, 0))) >> flags = 0; >> fcntl(fd[0], F_SETFL, flags | O_NONBLOCK); >> >> .. within a loop: >> >> got=read(fd[0], buf, sizeof(buf)); >> if (got>-1) >> { >> snprintf(cValue,4,"%s",buf); >> break; >> } >> // got ==-1 >> > 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] resource temporarily unavailable after sqlite3_open()
On Aug 11, 2009, at 2:53 PM, Mark Richards wrote: > Environment: > Linux axis 2.6.19 #9 PREEMPT Mon Apr 6 15:44:03 EDT 2009 cris > unknown > > Sqlite: > Sqlite: sqlite-3.6.14 > ./configure --host=cris-axis-linux-gnu > --prefix=/AEMDEV/83+/devboard-R2_10/target/cris-axis-linux-gnu > --enable-static=yes --enable-shared=yes --disable-dynamic-extensions > > Application: > My application runs against the shared library built as above. > > Code in my application fails after calling sqlite3_open(). > > This synopsis is of a function designed to spawn a shell, execute a > command, and read back the result via a pipe of stdout. It works > fine, > until sqlite3_open() is called anywhere PRIOR. > > prior sqlite3_open() call: > sqlite3 *dbf; > sqlite3_open("/path/to/my.db", &dbf); > > > fflush(stdout); > pipe(fd); > pid = fork(); > if (pid == 0) > { > dup2(fd[1], STDOUT_FILENO); > dup2(fd[1], STDERR_FILENO); > close(fd[0]); Have you tried called sqlite3_close() here to see if that helps? All of SQLite's file descriptors are FD_CLOEXEC, but who knows > execl("/bin/sh", "sh", "-c", "echo 123", 0); > } > if (-1 == (flags = fcntl(fd[0], F_GETFL, 0))) > flags = 0; > fcntl(fd[0], F_SETFL, flags | O_NONBLOCK); > > .. within a loop: > > got=read(fd[0], buf, sizeof(buf)); > if (got>-1) > { > snprintf(cValue,4,"%s",buf); > break; > } > // got ==-1 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] resource temporarily unavailable after sqlite3_open()
I also (just) tried 3.6.17. Same issue. /m Mark Richards , On 8/11/2009 14:53: > Environment: > Linux axis 2.6.19 #9 PREEMPT Mon Apr 6 15:44:03 EDT 2009 cris unknown > > Sqlite: > Sqlite: sqlite-3.6.14 > ./configure --host=cris-axis-linux-gnu >--prefix=/AEMDEV/83+/devboard-R2_10/target/cris-axis-linux-gnu > --enable-static=yes --enable-shared=yes --disable-dynamic-extensions > > Application: > My application runs against the shared library built as above. > > Code in my application fails after calling sqlite3_open(). > > This synopsis is of a function designed to spawn a shell, execute a > command, and read back the result via a pipe of stdout. It works fine, > until sqlite3_open() is called anywhere PRIOR. > > prior sqlite3_open() call: > sqlite3 *dbf; > sqlite3_open("/path/to/my.db",&dbf); > > > fflush(stdout); > pipe(fd); > pid = fork(); > if (pid == 0) > { > dup2(fd[1], STDOUT_FILENO); > dup2(fd[1], STDERR_FILENO); > close(fd[0]); > execl("/bin/sh", "sh", "-c", "echo 123", 0); > } > if (-1 == (flags = fcntl(fd[0], F_GETFL, 0))) > flags = 0; > fcntl(fd[0], F_SETFL, flags | O_NONBLOCK); > > .. within a loop: > > got=read(fd[0], buf, sizeof(buf)); > if (got>-1) > { > snprintf(cValue,4,"%s",buf); > break; > } > // got ==-1 > > > > When sqlite3_open() is called as above, got returns -1 (forever). -1 > returning from a NONBLOCK read is accepted behaviour, but pretty quickly > the read() should give some data. Instead it returns -1 each time. (If > I allow fd[0] to be in blocking mode, the read() never returns). > > Thinking that perhaps the > > A mangled stdout came to mind, but this does not appear to be the case. > But maybe sqlite_open() or other internals diddle with stdout? > > > ___ > 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] resource temporarily unavailable after sqlite3_open()
Environment: Linux axis 2.6.19 #9 PREEMPT Mon Apr 6 15:44:03 EDT 2009 cris unknown Sqlite: Sqlite: sqlite-3.6.14 ./configure --host=cris-axis-linux-gnu --prefix=/AEMDEV/83+/devboard-R2_10/target/cris-axis-linux-gnu --enable-static=yes --enable-shared=yes --disable-dynamic-extensions Application: My application runs against the shared library built as above. Code in my application fails after calling sqlite3_open(). This synopsis is of a function designed to spawn a shell, execute a command, and read back the result via a pipe of stdout. It works fine, until sqlite3_open() is called anywhere PRIOR. prior sqlite3_open() call: sqlite3 *dbf; sqlite3_open("/path/to/my.db", &dbf); fflush(stdout); pipe(fd); pid = fork(); if (pid == 0) { dup2(fd[1], STDOUT_FILENO); dup2(fd[1], STDERR_FILENO); close(fd[0]); execl("/bin/sh", "sh", "-c", "echo 123", 0); } if (-1 == (flags = fcntl(fd[0], F_GETFL, 0))) flags = 0; fcntl(fd[0], F_SETFL, flags | O_NONBLOCK); .. within a loop: got=read(fd[0], buf, sizeof(buf)); if (got>-1) { snprintf(cValue,4,"%s",buf); break; } // got ==-1 When sqlite3_open() is called as above, got returns -1 (forever). -1 returning from a NONBLOCK read is accepted behaviour, but pretty quickly the read() should give some data. Instead it returns -1 each time. (If I allow fd[0] to be in blocking mode, the read() never returns). Thinking that perhaps the A mangled stdout came to mind, but this does not appear to be the case. But maybe sqlite_open() or other internals diddle with stdout? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cannot insert records into a table after dropping and recreating it
On 11 Aug 2009, at 4:50pm, Radcon Entec wrote: > At startup, the application creates three tables. If the file > previously existed, the create table queries fail. My code checks > the error message, and if it indicates that the table previously > existed, it ignores the error. Use the 'IF NOT EXISTS' form: CREATE TABLE IF NOT EXISTS myTable ... That way you don't need to have your own code to filter out just that one error. > There is a fourth table that is handled differently. This table > will be created at startup, used, and then dropped. So, my code > drops the table, then creates it, and then adds data to it. (The > final drop is not yet implemented.) Just in case the table got left > behind for some reason, my code drops the table before creating it. > Any errors from the drop are ignored, although there is an exception > handler there and I have verified that the hander is not being > executed. As a temporary fix to explore your problem, try executing the same commands in the command-line tool and see if it gives you any error messages. As a permanent fix I assume you want this table to be blank. So the best/fastest thing to do would be to drop the table if it exists, then always create a new one with that name. Use the form of 'DROP TABLE' which includes 'IF EXISTS': DROP TABLE IF EXISTS fourthTable; CREATE TABLE fourthTable ... This gives you two commands which should both always execute without errors. That means you can implement proper error-trapping and pay attention to the errors instead of ignoring them. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cannot insert records into a table after dropping and recreating it
--- On Tue, 8/11/09, Radcon Entec wrote: > From: Radcon Entec > Subject: [sqlite] Cannot insert records into a table after dropping and > recreating it > To: sqlite-users@sqlite.org > Date: Tuesday, August 11, 2009, 10:50 AM > Greetings! > > I have an application that uses an SQLite database file > that may or may not exist when the application starts. > At startup, the application creates three tables. If > the file previously existed, the create table queries > fail. My code checks the error message, and if it > indicates that the table previously existed, it ignores the > error. > > There is a fourth table that is handled differently. > This table will be created at startup, used, and then > dropped. So, my code drops the table, then creates it, > and then adds data to it. (The final drop is not yet > implemented.) Just in case the table got left behind > for some reason, my code drops the table before creating > it. Any errors from the drop are ignored, although > there is an exception handler there and I have verified that > the hander is not being executed. > > If the database did not exist before the application runs, > the temporary table is created and populated as > expected. But if the database did exist before the > application runs, the temporary table is dropped and created > as expected, but I cannot write any data into it. > There are no errors generated, but there is no data in the > table, either. > > I am inserting data into the table by building an insert > query and calling sqlite3_exec(). > > Am I leaving out a step that makes my newly created table > unwritable? > > Thanks very much! > > RolbR > > Have you verified that your drop and recreate isn't being called again (maybe after doing the inserts)??? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Cannot insert records into a table after dropping and recreating it
Greetings! I have an application that uses an SQLite database file that may or may not exist when the application starts. At startup, the application creates three tables. If the file previously existed, the create table queries fail. My code checks the error message, and if it indicates that the table previously existed, it ignores the error. There is a fourth table that is handled differently. This table will be created at startup, used, and then dropped. So, my code drops the table, then creates it, and then adds data to it. (The final drop is not yet implemented.) Just in case the table got left behind for some reason, my code drops the table before creating it. Any errors from the drop are ignored, although there is an exception handler there and I have verified that the hander is not being executed. If the database did not exist before the application runs, the temporary table is created and populated as expected. But if the database did exist before the application runs, the temporary table is dropped and created as expected, but I cannot write any data into it. There are no errors generated, but there is no data in the table, either. I am inserting data into the table by building an insert query and calling sqlite3_exec(). Am I leaving out a step that makes my newly created table unwritable? Thanks very much! RolbR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [Delphi] Reading list of tables?
On 11 Aug 2009, at 11:25am, Gilles Ganault wrote: > I'd like to fill a ComboBox widget with the list of tables in a > database file, using the free Aducom wrapper. > > Would someone have some code handy on how to extract this information > from a SQLite database, ie. the equivalent for the CLI command > ".tables". SELECT sql FROM sqlite_master WHERE type='table' Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [Delphi] Reading list of tables?
Hello I'd like to fill a ComboBox widget with the list of tables in a database file, using the free Aducom wrapper. Would someone have some code handy on how to extract this information from a SQLite database, ie. the equivalent for the CLI command ".tables". Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Busy Handler in Shared Cache Mode?
More detailed testing revealed that it is not a reader but the writer who observes a long wait time. The writer appears to get unlocked after all reader-threads have completed. This must be a case of writer starvation. I had not realized that shared cache mode is, by default, prone to this. The good news is that Unlock Notification should handle it (http://www.sqlite.org/unlock_notify.html). --- edz...@volcanomail.com wrote: From: "Edzard Pasma" To: "General Discussion of SQLite Database" Cc: Subject: Re: [sqlite] Busy Handler in Shared Cache Mode? Date: Mon, 10 Aug 2009 04:25:32 -0700 Hi Nikolaus, Interesting to read your findings. I assume you finally disallowed read_uncommitted. Trying to explain why the wait times can get longer, I come to two reasons. - the time slots where a read-lock is obtained become smaller. This can be significant if there are a lot of transactions with little time in between. With the busy handling by polling, a reader may mis slots. This will sure be improved by Unlock Notification. - the time slots where a read-lock can not be obtained become longer. This can cause problems if there are long-running transaction. The Unlock Notification feature is not going to help here. I still like to share an observation from my own test, which reproduces the first case. I start four reading threads and one writer. Each thread performs a fixed number of cycles. Apart from the wait times, also the overall elapsed time is measured. Indeed the maximum wait-time gets bigger if shared cache mode is enabled. Interestingly, this does not apply to the elapsed time, which is still reduced. Thus, an increase of the maximum wait-time is not necessarily a worse performance. By the way, this was measured on a single-processor system. Hope this is useful to know. Best regards, Edzard. On Fri, Aug 7, 2009 at 1:45 PM, Nikolaus Rath wrote: > Hello, > > I have program that continuously runs several threads (about 4 to 10) > which concurrently read and write to an SQLite database. From > http://www.sqlite.org/sharedcache.html it seems to me that I should be > using shared cache mode. > > Until now, I have set the busy timeout to 500 msec and never had to deal > with SQLITE_BUSY errors. However, there seems to be no corresponding > function for the SQLITE_LOCKED errors that are generated in shared cache > mode. So I changed the code manually sleep for a random amount (0 to 100 > msec) and then try to execute the statement again if it encounters > SQLITE_LOCKED. But now the threads are often waiting for more than 1 > second before they finally get their query executed. > > I suspect this is because the random sleep is wasting a lot of time, > while without shared cache (and with enabled busy timeout) a thread > blocks and gets revived as soon as the lock on the database is > released. > > > How can I avoid this problem? Is there a way to set a busy timeout for > SQLITE_LOCKED as well? Or a more clever method instead of sleeping for > random amounts? Or should I just avoid using shared cache mode? ___ 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