SQLite over a network

[Kiai]

I have an application that runs over a network, can have up to 300 users. Currently data is stored in flat files (around 50,000 'rows' total) and I'd like to convert it to a database. Because it is so easy and integrated with AutoIT, I've been looking at using SQlite. Perhaps I should go with Mysql, though I know less about programming with that.

This is what I understand:

There may be some performance issues because of the latency of data transfer across the network.

There is a possibility that the data base can get corrupted because of problems with Windows (or Linux) file locking not working properly, leading two different users to modify the same part of the data base.

Users in my application will perform database reads and writes of very small data chunks (2k reads, 100 byte writes) at the most about once per minute, and a wait for a read or write of 2-3 seconds would not be tragic.

My application already prevents two users from working on the same chunk of data. If I don't need to rely on Sqlite to ensure the locking down of data, is it still a viable option?

Any suggestions or help is appreciated.

[jchd]

I'll try to address the various points you raise.

I will recommend against bare bone SQLite in this context.

Network latency obviously always has a negative impact. The point at which that effect becomes a real nuisance depends heavily on the DB schema, the actual size of fields (indexes), the selectivity and complexity of queries. In short the only right way to appreciate the effect is trial.

The issue with defective SMB/NFS remote file locking can be more subtle than what you describe, but will surely result in DB corruption with that many users (in the long run). You see, there is a need to lock even for reads, so 300 users mean a significant number of lock (post, promote, free) operations and only one "quirk" can ruin everything. In view of this, the fact that the design of your application guarantees some isolation is not enough to make the base safe in all case and that isn't a viable option, sorry.

As a sidenote, I'm in a very similar situation with my own business DB which I'd need to share over the LAN. I'm refraining being too optimistic as I know it only takes very little Windows "imperfection" to make the whole base a pile of meaningless bits.

Now, everything is not lost. There are a number of client/server wrapper layers available for SQLite to deal with such situations, some open and free, other payware.

As an alternative you may switch to less lightweight DB engines, like MySQL, MS-SQL, PostGreSQL. All of them are native client/server architectures.

In my case, I'd like to stay with SQLite for several reasons. First, the base and applications are already running. Second, I've developped SQLite extensions to deal with Unicode casing, accentuation issues, fuzzy search and several other functions, which would need complete rewrite in another context. Then I'd like to stay with the current UDF interface paradigm and detail. There are also other reasons that make me favor SQLite over a big named engine.

What I see as possible workaround is write or adapt a client layer and give it exactly the same interface that current SQLite (up to function spelling, say like _rSQLite_Exec for Remote SQLite).

Then developping for local or remote can be 100% transparent and still safe.

[Zedna]

Definitely look at this page

http://www.sqlite.org/whentouse.html

[Kiai]

Definitely look at this page

http://www.sqlite.org/whentouse.html

I really appreciate both comments. I had seen the above page, and it still gives me pause. I'll probably go the mysql route, but I'm still trying to figure out how to use mysql.au3 and how to integrate this with AutoIT.

However, I did some tests today and, working across the network the transactions that I need to perform took 500 msec for the largest data fetch and 50 msec for the most complicated write transaction. I have trouble imagining the database crashing. ....

I have an option of using multiple, instead of one, sqlite databases. Essentially users work on a specific 'Department' so there could be 10 sqlite databases, one for each department, with at the most 10 concurrent users at any time.

As you can see, I'm still compelled by the simplicity of sqlite and the lack of a need for a dbase server.

[Kiai]

I should have included in the intro that this application is for a 'conversion' project. It will be used for 2-3 weeks max and each record in the database is essentially written to only once to say 'done.' The simplicity of the data queries, the small amount of data that needs to be written and the lack of any realistic real-world 'two users trying to write to the same row' scenario is what made me consider sqlite.

Thanks again.

[GEOSoft]

I should have included in the intro that this application is for a 'conversion' project. It will be used for 2-3 weeks max and each record in the database is essentially written to only once to say 'done.' The simplicity of the data queries, the small amount of data that needs to be written and the lack of any realistic real-world 'two users trying to write to the same row' scenario is what made me consider sqlite.

Thanks again.

In that case I don't see where there is a downside to using SQLite.

[Kiai]

In that case I don't see where there is a downside to using SQLite.

Fantastic tutorial on DLL and AutoIT!

[jchd]

A little update on this can be useful to make a decision:

From: Max Vlasov <max.vlasov@gmail.com>

Date: Wed, 28 Apr 2010 16:25:59 +0400

To: General Discussion of SQLite Database <sqlite-users@sqlite.org>

Subject: Re: [sqlite] Exclusive transactions over network

Jean-Christophe, from my experience it depends. We have several clients accessing a database shared on a 2003 server and no corruption took place so far, but sometimes freezing of a client was possible. Also when I did some artificial tests, when several clients tried to write on a constant basis there were cases when one of them could also freeze. Consider doing some die hard tests with your configuration. This should not be the same scheme as yours, the only thing you should additionally do from time to time is PRAGMA integrity_check. After a whole night test and thousands of successful writes from several computers you will at least have probability arguments on your side )

Max Vlasov, maxerist.net

On Wed, Apr 28, 2010 at 9:43 AM, Jean-Christophe Deschamps <jcd@q-e-d.org>wrote:

> Hi gurus, > > I'm aware of the limitations that generally preclude using SQLite over > a network. > Anyway do you think that doing so with every read or write operation > wrapped inside an explicit exclusive transaction can be a safe way to > run a DB for a group of 10 people under low load (typically 2Kb read or > 100b writes per user per minute)? > Schema will be very simple and queries / inserts as well. Speed is not > a real concern. > > So do you believe DB corruption can still occur in this context, > knowing that the use will be for a very limited time (2-3 weeks) and > low volume (~50K rows)? > > Using one of the available client/server wrappers is not a suitable option. > This is targeted at Windows, XP or later. > > Do you have a better idea to make the thing more robust, even at > additional cost in concurrency and/or speed.

You may want to setup a handful of peecees to test how that works in practice.

To read, prefer _SQLite_GetTable[2d] over applicative loops (it's faster) and don't forget that any Read/Modify/Write transaction _absolutely_ needs to be wrapped inside

_SQLite_Exec($hdb, "begin immediate;") ; if not "begin exclusive"

and

_SQLite_Exec($hdb, "commit;")