I have searched the web, and will continue while monitoring this thread. I’m looking for the best way to implement syncing for large database files before I get started on my project.
[Scenario]
Server App: Hosts a SQLite.db file
Client App: Reads/Sends data to Server App
I just realised that if I do a pull from the server on the client app each time I want to load, its going to be quite slow.
So I was thinking I should implement something were the clients store a copy of the database and sync themselves with the server. When the client imports data to the server DB, it copies it into the local DB. When reading information, it reads from the local copy. If a change is made in the server, it sends a message out to all clients to check themselves before they wreck themselves
So what’s a good implementation here…
Should I manage this myself by having the client do a check every few minutes, comparing record count and latest ID. I can see flaws in it already, like if you delete row 7 and someone else deletes row 9 and the latest row is 12 -_-
I’m using WCF to handle the server requests. Not sure if there is something already there that can handle this.
Do you want to stick to SQLite? Actually you could use JSON and some REST API to get new entries or delete old ones. Using some kind of unique ID to always keep the client synched to the server could help you too. Just one suggestion. Also not sure, if you can’t do something similar with SQLite. One main question is, why is it slow? So is it the internet connection, is it the server itself, too much data synched?
I haven’t started yet so I cant say for sure it will be slow, but I’m guessing it will be.
The table design would have a table main, the reference point to other records on different tables.
One table each for 3D Models, Audio, Textures etc all stored in blobs.
The initial pull of the main table wouldn’t be that bad. But say you want to load a record that stored a 5mb 3Dmodel in it, over the network. That will have some delays.
If you take a look at my old app, it will give you an idea of what I’m trying to achieve, made in Unity surprisingly https://harley-torrisi.itch.io/assetcat
I’m trying to re-build a client/host version of that rather than just a client with locally stored files.
I would like to stick with SQLite. I had some frustrating days on Asset Cat dealing with file integrity. If I can reference everything by table/ID, select and update. It will keep things nice and clean. Asset Cat was build around serializing classes, before I learnt a bit of SQL.
I read something similar, they also mentions JSON. So how would a Unique ID keep two databases synced?
Do you mean every time the server DB changes, it changes it’s own UID. Which is a flag for the clients local DB to sync when a difference is found?
You can read only the commands which makes changes into DB… like: insert into, create, delete (only the modifications).
Store these commands into a separate table with TimeStamp. In your local DB you have also the same table and it’s easy now to build a select from server_db only the latest modifications.
