[Qt-interest] QSQLITE and Network Shares

[Gordon Schumacher]

Bill King wrote:
> On 11/17/2009 05:07 AM, ext Israel Brewster wrote:
>>
>> On Nov 15, 2009, at 2:13 PM, Bill King wrote:
>>
>>>>
>>>>>
>>>>>
>>> The problem is an sqlite problem. What you are seeing is that
>>> another query has a lock open on the table, and your query is trying
>>> to elevate it's lock status.
>>
>> First off yes- PostgreSQL is a good option, and one I have
>> implemented. I do agree that it is the way to go, however I also
>> think that leaving something non-functional just because there is a
>> better option is not acceptable. Of course, if this is a sqlite
>> problem, then it's not your issue.
>>
>> That said, there are two serious flaws with your argument that this
>> is an sqlite problem. First, there ISN'T another query that has a
>> lock on the table. Look at the unit test I sent (with my first
>> follow-up) - it creates a brand new database, opens it, and
>> IMMEDIATELY tries to run a CREATE TABLE, getting this error (if on a
>> network drive). The database hasn't even existed for more than a
>> fraction of second. How and what could POSSIBLY have another query
>> with a lock on the table, unless Qt itself put it there in the open
>> command? Besides, the query I am running is a CREATE TABLE, so the
>> table in question doesn't even exist when I run the query. How on
>> earth could there be a lock on a table that doesn't exist? Actually,
>> I get this error no matter the query I run, but using a CREATE TABLE
>> query on a brand-new database precludes the possibility of a lock
>> existing.
>>
>> Secondly, if I take Qt out of the equation, IT WORKS. Put Qt back
>> into the equation, it doesn't work. Given that the only difference
>> between the two tests was Qt (the test being create and open a
>> brand-new database on a network share, and then run a CREATE TABLE
>> query), how could this NOT be a Qt problem? I do apologize if I am
>> being stubborn here, but saying it is a sqlite problem just doesn't
>> fit the facts as I have presented them.
>>
>>>
>>> Seriously, I would consider a proper client/server database system
>>> rather than trying to get what is essentially a single user system
>>> to act in a networked fashion.
>>> There are lots of free/zero config databases out there. try
>>> firebirdsql for instance, or postgresql, both have very permissive
>>> licences, and cost you nothing.
>>
>> Agreed. That said, given that according to the official sqlite people
>> this should work (and, in fact, does in testing once Qt is removed
>> from the equation) doesn't it behoove you to make it work in Qt as well?

Well, this is all true, and it's not true... this sounds like the
byte-range locking issue; we ran into that too.
I will vouch that switching away from SQLite is not always an option -
in our case, we're limited by an environment where there's nowhere we
could run the DB server app!

You may, when building SQLite, define SQLITE_ENABLE_LOCKING_STYLE on the
command line.  I would suggest trying dot-file locking, or even *no*
locking if you know you can get away with it.  The catch is, the former
kills your performance IIRC, and the latter is dangerous.  You may have
to look at implementing your own locking... *eww*