Simple multi-user database solution

I've written a Windows desktop application that works with Sqlite very nicely. 开发者_如何学运维 It was a single user app, and the database sits right on the machine where the app runs.

However, the application has grown, and now multiple users should be able to run the app and connect to one shared database.

I'd like to just be able to share the sqlite file on a network drive, but that would obviously result in corrupted data unless someone has any ideas.

I guess I could require the install of a database server, but a commercial MySQL license doesn't make sense, PostgreSQL is different enough that I'd have to rewrite a lot of my application. I haven't worked with Firebird at all, so I'm not sure if that's a good solution or not.

Are there any Sqlite database servers that can be installed that handle the incoming transactions on the Sqlite database file?

If I require the client to download and install MySQL on their own, do I have to have a commercial license?

Any suggestions or direction would be great, thank you.

You may be able to use the Sqlite file on a shared network drive as you described, depending on the underlying filesystem:

http://www.sqlite.org/faq.html#q5

Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however.

SQLite uses reader/writer locks to control access to the database. (Under Win95/98/ME which lacks support for reader/writer locks, a probabilistic simulation is used instead.) But use caution: this locking mechanism might not work correctly if the database file is kept on an NFS filesystem. This is because fcntl() file locking is broken on many NFS implementations. You should avoid putting SQLite database files on NFS if multiple processes might try to access the file at the same time. On Windows, Microsoft's documentation says that locking may not work under FAT filesystems if you are not running the Share.exe daemon. People who have a lot of experience with Windows tell me that file locking of network files is very buggy and is not dependable. If what they say is true, sharing an SQLite database between two or more Windows machines might cause unexpected problems.

I think that Firebird can be a very good choice

• It's free
• embedded version exist

What about SQL Server Express, its free and should stop you having to rewrite most of your code,

There are sqlite servers that make it possible to use sqlite in a multi user environment. Read here: http://www.sqlite.org/cvstrac/wiki?p=SqliteNetwork . I don't know how those solutions perform and scale.

I don't know why you assume that the data file will be corrupted if you put it on a network drive and let multiple instances of your app access it at the same time. If you do mainly reading, you should be alright. If you do a lot of writes, you will probably suffer a performance hit, since only one instance can write at the same time. (See http://www.sqlite.org/faq.html)

If you do a lot of writes, you probably will need a standalone server installation - have you considered MS SQL Server Express ? Should be easy and simple to get up and running.

Too late I know, but no one these days should have a need to write an application without a framework that offers database abstraction.

Personally I use Web2py which is a python based Web Framework. By default it uses SQLite which is great for single user apps on the local desktop or small to medium apps on the local network which are mainly read operations. However if I decided to increase the scope of the app usage, I would simply change the connection string in the database modal to use another database. Web2py or any other framework worth using would simply rewrite the SQL statements to meet the new requirements.

use this code on your connection string after that multiple user can insert,delete,select e.t.c. in your database without issue.

SQLiteConnection con = new SQLiteConnection("Data Source=D:\yourdatabase.db;Count Changes=off;Journal Mode=off;Pooling=true;Cache Size=10000;Page Size=4096;Synchronous=off");

If I require the client to download and install MySQL on their own, do I have to have a commercial license?

No.

The value of a MySQL commercial license is that it allows you to distribute standard MySQL or a modified MySQL in binary form without the oblihation to also distribute source code. It also gives you access to support.

However, if the user obtains MySQL themselves from Oracle, the GPL places no obligations on you to provide them MySQL source code. They can get it from Oracle. Indeed, Oracle is obliged to provide it, so long as they distribute under the GPL.