[sqlite] Documentation error in async_cond_wait
This sentence in .../ext/async/sqlite3async.c for async_cond_wait() ** It is guaranteed that no other thread will call async_cond_wait() when ** there is already a thread waiting on the condition variable. is wrong for both Win32 and POSIX implementations. It should be left out or read ** Another thread is allowed to call async_cond_wait() while there is ** already a thread waiting on the condition variable. However, only ** one thread will be made runnable for one async_cond_signal() call ** on the condition variable. -- Christian Werner
[sqlite] Detect if db is already opened by another process?
On Wed, 23 Sep 2015 18:01:47 +0200 Michael Schlenker wrote: > Hi, > > i just wondered if there is an API to detect if a sqlite database file > is already opened by another process. > > I can make the assumptions that: > > 1. WAL mode is in use > 2. Linux and Windows only > 3. No network filesystems > 4. I only care if the access is done by another SQLite library, >not simple open() calls. > > I didn't see any explicit API to check for this, but assumed there > might be some way to find out via the .shm files? > > Usecase is a server process that keeps an SQLite DB open while it is > running and a commandline tool that manipulates the same DB file for > maintenance tasks. The maintenance tool should not change the DB if > the server is running. > > I could of course do explicit locking via other means to mediate > access (actually i do that now, but it is not really elegant), but if > there is an SQLite API way to do it, it would be nicer. > > Any good hints? If the other process is yours, you can use pragma user_version to store a reference counte of the number of times the db is opened, increasing each time you open it and decreasing each time you close it. > > Michael --- --- Eduardo Morras
[sqlite] Detect if db is already opened by another process?
On 2015-09-23 06:09 PM, Richard Hipp wrote: > On 9/23/15, Michael Schlenker wrote: >> Hi, >> >> i just wondered if there is an API to detect if a sqlite database file >> is already opened by another process. > Maybe try to change in or out of WAL mode? That only works if there > is a single connection to the database file. Not sure if that solution improves vastly on the "elegance" of it all, but it should work well - might I suggest the server opens the DB simply in Read-Only mode? (This is what I do and never had an issue). See: https://www.sqlite.org/c3ref/open.html with: https://www.sqlite.org/rescode.html and: https://www.sqlite.org/c3ref/db_readonly.html
[sqlite] Detect if db is already opened by another process?
Hi, i just wondered if there is an API to detect if a sqlite database file is already opened by another process. I can make the assumptions that: 1. WAL mode is in use 2. Linux and Windows only 3. No network filesystems 4. I only care if the access is done by another SQLite library, not simple open() calls. I didn't see any explicit API to check for this, but assumed there might be some way to find out via the .shm files? Usecase is a server process that keeps an SQLite DB open while it is running and a commandline tool that manipulates the same DB file for maintenance tasks. The maintenance tool should not change the DB if the server is running. I could of course do explicit locking via other means to mediate access (actually i do that now, but it is not really elegant), but if there is an SQLite API way to do it, it would be nicer. Any good hints? Michael -- Michael Schlenker Senior Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Stra?e 1-3 Fax:+49 (421) 20153-41 28359 Bremen E-Mail: michael.schlenker at contact-software.com http://www.contact-software.com/ Registered office: Bremen, Germany Managing directors: Karl Heinz Zachries, Ralf Holtgrefe Court of register: Amtsgericht Bremen HRB 1321 -- Michael Schlenker Senior Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Stra?e 1-3 Fax:+49 (421) 20153-41 28359 Bremen E-Mail: michael.schlenker at contact-software.com http://www.contact-software.com/ Registered office: Bremen, Germany Managing directors: Karl Heinz Zachries, Ralf Holtgrefe Court of register: Amtsgericht Bremen HRB 1321
[sqlite] Detect if db is already opened by another process?
On 23 Sep 2015, at 5:01pm, Michael Schlenker wrote: > i just wondered if there is an API to detect if a sqlite database file > is already opened by another process. Thanks for your use-case and assumptions which makes this far simpler to answer. I don't know of any simple way to know if another process has a connection to the process. Having a connection doesn't modify the file in any way. And in WAL mode you cannot check the journal file to see if a connection exists. You can tell if the database file is locked for changes. Make a connection and execute "BEGIN EXCLUSIVE". If this fails something else has the database locked in such a way that you should not be modifying it. If it succeeds it's okay to make changes and you should do "END" when you're finished. You can misuse this to make your server the ultimate database hog by having it keep the database locked. As soon as the server opens the database it should execute "BEGIN EXCLUSIVE". When it wants to flush a set of changes to disk it should do "END" and then immediately another "BEGIN EXCLUSIVE". And just before it closes the connection it should do another "END". This database-hogging is extremely unusual use of SQLite and people may hate you for it. But it might be a usable solution to your particular problem. Hmm. I wonder if this would actually work in WAL mode. Would other processes just work on their own private copy of the database until the server does "END" ? Simon.
[sqlite] UPDATE silently failing
Hugues Bruant wrote on Wednesday, September 23, 2015 2:06 AM > > > in some cases the SIndex captured inside the first lambda (UPDATE > > > statement) appeared to be null even though it wasn't null in the > > > enclosing scope (setVersion_) > > > > Interesting (and disturbing) result. Is this with Oracle's java > compiler? > > > Compiler is Oracle JDK 8u40-b25 on OSX > Runtime is OpenJDK 8u60-b04 built from source on OS X, Windows and > Linux > > It's pretty rare but damaging enough that I'm going to avoid capturing > lambdas in critical code paths from now on. Perhaps everyone is aware already, but it seems like at least sometimes lambdas expose underlying thread synchronization problems. -- This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer. If you are not the intended recipient, please delete this message.
[sqlite] UPDATE silently failing
On 23 September 2015 at 12:32, Hugues Bruant wrote: > On Wed, Sep 23, 2015 at 12:00 AM, Rowan Worth wrote: > > > Has many possible explanations. > > I can't think of any that is consistent with the Java specification. > Yeah fair enough, SIndex looks watertight. It's still possible for some other code to change _i through reflection but (a) I presume you would know if you were doing something like that and (b) it wouldn't explain the null SIndex capture. in some cases the SIndex captured inside the first lambda > (UPDATE statement) appeared to be null even though it wasn't null in the > enclosing scope (setVersion_) Interesting (and disturbing) result. Is this with Oracle's java compiler? -Rowan
[sqlite] Detect if db is already opened by another process?
On 9/23/15, Michael Schlenker wrote: > Hi, > > i just wondered if there is an API to detect if a sqlite database file > is already opened by another process. Maybe try to change in or out of WAL mode? That only works if there is a single connection to the database file. > > I can make the assumptions that: > > 1. WAL mode is in use > 2. Linux and Windows only > 3. No network filesystems > 4. I only care if the access is done by another SQLite library, >not simple open() calls. > > I didn't see any explicit API to check for this, but assumed there might > be some way to find out via the .shm files? > > Usecase is a server process that keeps an SQLite DB open while it is > running and a commandline tool that manipulates the same DB file for > maintenance tasks. The maintenance tool should not change the DB if the > server is running. > > I could of course do explicit locking via other means to mediate access > (actually i do that now, but it is not really elegant), but if there is > an SQLite API way to do it, it would be nicer. > > Any good hints? > > Michael > > -- > Michael Schlenker > Senior Software Engineer > > CONTACT Software GmbH Tel.: +49 (421) 20153-80 > Wiener Stra?e 1-3 Fax:+49 (421) 20153-41 > 28359 Bremen > E-Mail: michael.schlenker at contact-software.com > http://www.contact-software.com/ > > Registered office: Bremen, Germany > Managing directors: Karl Heinz Zachries, Ralf Holtgrefe > Court of register: Amtsgericht Bremen HRB 1321 > > -- > Michael Schlenker > Senior Software Engineer > > CONTACT Software GmbH Tel.: +49 (421) 20153-80 > Wiener Stra?e 1-3 Fax:+49 (421) 20153-41 > 28359 Bremen > E-Mail: michael.schlenker at contact-software.com > http://www.contact-software.com/ > > Registered office: Bremen, Germany > Managing directors: Karl Heinz Zachries, Ralf Holtgrefe > Court of register: Amtsgericht Bremen HRB 1321 > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp drh at sqlite.org
[sqlite] UPDATE silently failing
> SIndex sidx is just a boxed immutable integer. If it were me I'd be reviewing this assumption very carefully. The sequence of events: 1. SIndex.getInt() returns 0 2. SIndex.toString() returns "1" 3. SIndex.getInt() returns 1 Has many possible explanations. You could also try 'final int sid = sidx.getInt();' at the start of setVersion_, and refer to sid throughout instead of sidx to guarantee a consistent value. If the same problem occurs then you know something is going wrong in the jdbc/sqlite layer, otherwise if you start getting rows inserted with cv_s = 0 you know sidx is somehow changing. -Rowan
[sqlite] UPDATE silently failing
> > in some cases the SIndex captured inside the first lambda > > (UPDATE statement) appeared to be null even though it wasn't null in the > > enclosing scope (setVersion_) > > Interesting (and disturbing) result. Is this with Oracle's java compiler? > Compiler is Oracle JDK 8u40-b25 on OSX Runtime is OpenJDK 8u60-b04 built from source on OS X, Windows and Linux It's pretty rare but damaging enough that I'm going to avoid capturing lambdas in critical code paths from now on.
[sqlite] UPDATE silently failing
On Wed, Sep 23, 2015 at 12:00 AM, Rowan Worth wrote: > > SIndex sidx is just a boxed immutable integer. > > If it were me I'd be reviewing this assumption very carefully. The sequence > of events: > public class SIndex { private final int _i; public SIndex(int i) { _i = i; } public int getInt() { return _i; } @Override public String toString() { return Integer.toString(getInt()); } } The way these objects are created ensures that the integer value is always strictly positive. > 1. SIndex.getInt() returns 0 > 2. SIndex.toString() returns "1" > 3. SIndex.getInt() returns 1 > You forgot step 0 where getInt() returns 1 > Has many possible explanations. > I can't think of any that is consistent with the Java specification. Interestingly enough, I ran into something even weirder over the course of my testing: in some cases the SIndex captured inside the first lambda (UPDATE statement) appeared to be null even though it wasn't null in the enclosing scope (setVersion_) and the second lambda (INSERT statement). I don't have time to dig into JVM internals so I just opted to remove all uses of lambdas in DB code.
[sqlite] Building SQLite autoconf on SCO 5.0.7v
I ran into some trouble building sqlite-autoconf-3081101 on SCO 5.0.7v with GNU tools. I'm not so good with autoconf scripts, but I can tell you what changes had to be made to sqlite3.c and the Makefile to get a successful build. I hope this can get into a future configure script somehow... 1. In sqlite3.c, I had to add #include . 2. In the Makefile produced by configure, I had to change AR = false to AR = gar. 3. Also in the Makefile, I had to set LIBS = /usr/lib/libpthread.so /usr/lib/libsocket.so I can provide more info if needed.
[sqlite] get the data anyway no matter what the entry of the second table is existing or not
On 2015-09-22 11:17 PM, ChingChang Hsiao wrote: > SELECT * FROM service_table AS a > LEFT JOIN service_fib_table AS b ON a.service_no=b.service_no ; > > > How about adding one more further conditional JOIN in one statement above. > > service_fib_table.fib_id=service_fib_port_table.fib_id > > It means there is a hierarchy > service_table->service_fib_table->service_fib_port_table. Oops, did not finish... SELECT * FROM service_table AS a LEFT JOIN service_fib_table AS b ON a.service_no=b.service_no AND a.species=b.species AND b.class='Donkey' AND a.points < b.points LEFT JOIN service_fib_table2 AS c ON c.service_no=a.service_no LEFT JOIN service_fib_table3 AS d ON d.service_no=a.service_no ; etc. You may well add the same table more than once even if that is helpful . very few restrictions on joining things.
[sqlite] get the data anyway no matter what the entry of the second table is existing or not
On 2015-09-22 11:17 PM, ChingChang Hsiao wrote: > SELECT * FROM service_table AS a > LEFT JOIN service_fib_table AS b ON a.service_no=b.service_no ; > > > How about adding one more further conditional JOIN in one statement above. > > service_fib_table.fib_id=service_fib_port_table.fib_id > > It means there is a hierarchy > service_table->service_fib_table->service_fib_port_table. Add as many as you like... SELECT * FROM service_table AS a LEFT JOIN service_fib_table AS b ON a.service_no=b.service_no AND a.species=b.species AND b.class='Donkey' AND a.points < b.points;
[sqlite] UPDATE silently failing
On 22 Sep 2015, at 5:40am, Hugues Bruant wrote: > Forgot to include the db in the previous email. You can't attach files to messages in this list. If it's important, please post the file somewhere and send us -- or perhaps just a couple of people -- a URL. Simon.