Simple solution for multi-user database

Question

I wrote a desktop application for Windows that works very well with Sqlite. This is a single-user application, and the database is located directly on the machine where the application is running.

However, the application has grown, and now several users should be able to run the application and connect to one common database.

I would just like to share the sqlite file on a network drive, but this will obviously lead to data corruption if someone doesn't have any ideas.

I assume that I may need to install a database server, but a commercial license for MySQL does not make sense, PostgreSQL is so different that I will have to rewrite a lot of my application. I did not work with Firebird at all, so I'm not sure if this is a good solution or not.

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

If I need the client to download and install MySQL on my own, should I have a commercial license?

Any suggestions or direction would be great, thanks.

Answer 1

There are sqlite servers that allow you to use sqlite in a multi-user environment. Read here: http://www.sqlite.org/cvstrac/wiki?p=SqliteNetwork . I do not know how these solutions work and scale.

Answer 2

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

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

Several processes may have the same database open at the same time. Several processes can execute SELECT at the same time. But only one process can make changes to the database at any given time, however.

SQLite uses read / write locks to control database access. (Under Win95 / 98 / ME, which does not support read / write locks, probabilistic simulation is used instead.) But use a caution: this locking mechanism may not work correctly if the database file is stored on the NFS file system. This is due to file lock fcntl () broken in many NFS implementations. You should not install SQLite database files on NFS if several processes may try to access the file at the same time. On Windows, Microsoft's documentation says that locking may not work with FAT file systems if you are not using the Share.exe daemon. People who have a lot of experience with Windows tell me that file locking network files is very buggy and not reliable. If what they say is true, an SQLite database between two or more Windows machines may have unexpected problems.

Answer 3

What about SQL Server Express , it's free and should stop rewriting most of your code,

Answer 4

I think Firebird can be a very good choice.

• He is free
• the built-in version is

Answer 5

I don’t know why you assume that the data file will be damaged if you put it on a network drive and at the same time can use several instances of your application. If you mostly read, you should be fine. If you make a lot of recordings, you probably suffered from a performance hit because only one instance can write at a time. (See http://www.sqlite.org/faq.html )

If you record a lot, you probably need a standalone server installation - do you consider MS SQL Server Express? It should be easy and simple to get up and work.

Answer 6

Too late I know, but no one these days needs to write an application without a framework suggesting database abstraction.

Personally, I use Web2py, which is based on the Python Web Framework. By default, it uses SQLite, which is great for single-user applications on the local desktop or small and medium-sized applications on the local network that mainly perform read operations. However, if I decided to increase the use of the application, I would just change the connection string in the database module to use a different database. Web2py or any other infrastructure used would simply rewrite SQL statements to meet new requirements.

Answer 7

use this code in the connection string after several users can insert, delete, select etc in your database without any problems.

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);

Answer 8

If I need the client to download and install MySQL on my own, should I have a commercial license?

Not.

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

However, if the user himself gets MySQL from Oracle, the GPL does not undertake to provide you with the MySQL source code. They can get it from Oracle. Indeed, Oracle is required to provide it if they are distributed under the GPL.