Saturday, May 9, 2020

The database on network file problem!

Perhaps you're lucky and you're using a database server for your application. So any user is able to have full access to the server anytime. Or your application will not run on a network and you can easily use a simple file-based database like SQLite.

If not, welcome to the club of developers using no databases or any hacking tricks around the shared access problem.

There are some implementations out there in the wild with different approaches to overcome the problem.

You may ask: Why you don't just install a local database server?

Of course I could install a firebird server or the free MS-SQL Express server, but in these cases the PC must always be switched on, so that he is accessible in the network to play the server role. As always the easy solution is not possible.

Many of my clients have a really simple network using the "Fritzbox" as the router and up to tree PC connected to it. To be able to run our software on any of these PCs, without having to switch on the "Server-PC" each time, there is a NAS connected to the Fritzbox with all the data.

Shared access is handled by the file system. Yes, this is a working solution, no question! But as every application and also the stored data is always growing, there is a point where a real database would solve many problems.

Googleing for SQLite and Network you will find some implementation. uSQLiteServer, SQL4Sockets, SQLite ODBC Driver, or SQLiteDBMS. And you also find an easy protocol for handling these calls (TechFell).

Without going too deep into the research, everybody is using some kind of TCP/IP / Socket handler to restrict the access to the "database".

So why restrict to some cheap interface that can only handle the easy stuff?

Let's collect our needs: We want:
  • locking
  • an easy to use interface
  • threadsafe would be perfect
  • perhaps asynchrony access
  • some kind of remote procedure calls
  • perhaps some kind of caching? 

This should all be possible to write in a reasonable amount of time. The caching could be a challenge, but I would love to see a 64-Bit implementation of this, that is useable from a 32-Bit application, so the always empty 14 GB of spare memory could finally be filled with something useful.

I think I will start with a nice slim socket implementation, using UDP Broadcast to find other clients or "Server". Then connect over TCP, implement a simple low lever protocol for the handshake, ping, and version checking. Perhaps a plugin system that is able to auto-reload new versions from a server.

Yes this is all doable...

Why reinvent the wheel? My answer is as always: Because my wheels are running better. Or at least I think so... 

One big problem is still in my way: Find the time for doing this.

Perhaps you would like to see me live on YouTube trying this? Or my break down, because I have underestimated the problem... In any case, please leave a comment and subscribe to my channel.

I have to travel to a planet with a lower rotation speed!



  1. Is this project still alive? Any progress made sofar?

    1. The post is just a month old and yes.
      I've created the Plugin-Loader to load some XEx DLL to use by my D2007 application. One of this is the TCP-Locking-Service, one is the Database driver.
      I've also created a service and the loader that is able to load the right service (32 or 64 bit).
      I'm still working with my shared development system and so I work on this project just every 6 weeks for one week.