Merging of SQlite and MySQL in C#

Question

I am currently writing an application that requires a db in the background. I am using MySQL on my server. If the user has no internet connection, the application should remain functional and resync when there is internet again.

Because my application is a multi user and multi device application I need the both DB's (remote one and local)

can anyone point me in the right direction to a syncing/database merging framework or if there isn't any maby a concept?

Language is C#

Answer 1

rudolph flash, The original question was looking for a way to synchronize an offline database with one that is online, my posts was meant to propose an idea of how it could be done.

All the issues related with data isolation you mentioned are indeed important factors, but for me, I would worry first about the primary topic, to implement synchronization of the offline & online database.

As much as it is important to prevent data mismatches (as per the article was covering), to worry about it now, while the synchronization system is not implemented, will only slow down the overall system development process.

IMHO the best option to deal with your scenario of multiple requests at the SAME TIME would be to design the client to query data to the server on demand, instead of showing data that may become obsolete at any time, or to have the client automatically checks for data validity on a specified period, or even better, a realtime data that is updated per second, if that's what it takes.

Best regards,

Answer 2

rudolph flash I have read the article, but still, I don't understand what this has to do with my post, even in that article it emphasized on the good and bad of the "locking" concept, stronger isolation doesn't necessarily mean very good (paragraph 2).

Do you have any suggestion regarding my post?

Answer 3

Pros:
- Record modification date/time log specific down to record level.

Cons:
- Extra space needed for the new fields on each record.
- Care must be taken in order to enforce create/update follows the procedure, to ensure <last_update> and <status> field on each records in sync-able tables be updated correctly. This is crucial if client is allowed to create/update data.

(Edit)
To sync client data that are created/updated offline the same concept applies, only this time, client is responsible to filter out which new/updated data are to be sent to server.

Hth, cmiiw

Answer 4

rudolph flash Can you explain to me what these phantoms, lost update, non-repeatable readings, reading of unrecorded data? these terms are strangers to me.

Also what did you mean by "blocking", what is there to lock? the concept of client-server is to allow multiple users to access/modify data, not for exclusive access with locks. And what you meant by mutual isolation and the isolation degree of transaction?

Answer 5

On server side, let's assume we're talking table A, we need to have a mechanism that will make the update to the <last_update> and <status> field on table A for create and update event, so that when a new record is created/updated in table A, the <last_update> field of that record in table A will be filled with server's current date/time, and <status> is filled with either 1 (for new record), or 2 (for updated record). A trigger, or stored procedure (if supported) can be used for this, I guess.

On client side (when internet connection is available), it should find the MAX(<last_update>) of its own table A, next, client query the server, to select all records from table A (on server) where the <last_update> is more recent than the MAX(<last_update>) found in client's table A. If this query yields any record, client will add/update those new/updated records into its own table A. So, which records are either new/updated? we can check the <status> field.

(Continued on next post [again])

Answer 6

Thank you for your answer! I have looked into Entity Framework and it looks great, althrought I am not sure if it suits my approach.

I really need those two databases (MySQL for working online, multi user and multi device support, and SQLite to enable users to modify data even if they are not connected to the internet)

Does Entity Framework come with any kind of sync adapter? I am already using another ORM (PetaPoco) and I am happy with it.

Answer 7

I have an idea, but I don't know if it is feasible for your project ;-)

I'm going to assume you meant that the server and client have the same set of tables, and there is a need to have all these tables' data to be synchronized, at any time client detects that internet connection is available.

Two new fields are needed for each table that needs sync support, this new fields are:

1. <last_update> date/time, purpose is to store the respective record last update date/time (for existing records), or creation date/time (for new records).

2. <status> int, purpose is to store the status of last update, e.g. 1 for creation (new records), and 2 for modification (updated records)

(Continued on next post)

Answer 8

rudolph flash I'm sorry, what do you mean settings of transactions and triggers?

Answer 9

as there is no framework for this (based on my research and noone posted one here), I will have to implement this myself.

I will use a master & slave model (server is always correct) to avoid conflicts. (which should work out pretty well IMHO)

Thx for all your input!