Synchronize SQLite Client Database with MySQL Server Database

Question

I created an application in xcode with sqlite3. I want to create a button called sync to synchronize with my mysql base on my server. Any suggestion on the synchronization process? Please let me know.

Answer 1

Use the web service on the server to return the version number of the scheme and the last updated time stamp. If the client is out of date, he makes a second call to receive an updated schema and / or new data.

Answer 2

I suppose you understand that this is not a trivial problem. I wrote a library to accomplish this for a commercial application last year, and it took about 6 months to get it to where I was pleased with it.

Leaving aside the argument of using ports 80 and HTTP (TCP / IP) to avoid problems with the firewall and support, you need to develop a protocol. Since my project was very intuitive, I went with a binary protocol (and not a bloated xml) that could handle any data. I also wanted it to be bi-directional so that I could insert INSERT data as well as fulfill queries. I used CGI / FastCGI on the server.

The binary protocol I developed is pretty simple (always better) and breaks large transfers into pieces of a user-defined size (about 600K seems good). Each piece has a header followed by data.

Although this protocol can be used to transfer any kind of data, it is usually used for database style data, as your question suggests. To do this, I decided to use a line / column design approach. The data is stored in one row at a time, each of the columns is stored for the first row, then all the columns for row 2 ... row n.

Single Column Data Format:

' Col1Type 1Bytes - BYTE ' Data Type (REMSQL_TEXT etc) ' Col1Len 4Bytes - DWORD ' Length in bytes the Column Data - up to 4.2GB ' Col1Data nBytes - BYTE ' String data 

(in C, BYTE - CHAR)

This means that each column has a data type descriptor. All data types can be represented:

 REMSQL_NONE = 0 ' DataType undefined REMSQL_QUAD = 1 ' 64-bit signed integer REMSQL_DBLE = 2 ' 64-bit IEEE floating point number REMSQL_TEXT = 3 ' STRING - (CHAR) string of Ascii Bytes REMSQL_BLOB = 4 ' BLOB - (CHAR) string of Binary Bytes REMSQL_NULL = 5 ' NULL - Empty Column 

These data types are the same as the basic SQLite data types and are numerically equivalent to listing the fundamental SQL3 data.

In this project, if the field is empty (NULL), you saved only 5 bytes. If the field has 200 bytes of text, for example, only 205 bytes are required to store it. The biggest advantage is data analysis, because you can skip columns without reading all 200 bytes to find some kind of trailing character.

The Chunk header should contain things like number of rows, number of columns, total number of bytes, etc. If you use DWORD (unsigned integers), the theoretical limit for a chunk is 4.2gigs, which should be sufficient even for a local tranmission network.

To implement this function, you need to write SQLite / MYSQL covers. I use only the BINARY protocol, which takes a little time, but you need the following functions: Client side: SendRequest () - sends a request, waits for a response

Server side: ProcessRequest () - receives a request, processes and returns a response

In my case, the answer may be! 00MB of data or more. I retrieve the entire dataset from MySQL and save it to disk on the server. Then I return an empty piece that contains the dataset metrics. Then the client requests a data set in pieces of 600 thousand, one by one. If the connection is lost, it simply rises where it stopped.

Finally, the data set was mostly textual (address names, etc.), so it was ripe for compression. Security was a very big problem in this case, so encryption was necessary. This gets a little complicated for implementation, but basically you compress the entire fragment, pad, to a length that is a multiple of the BLOCKSIZE block ciphers and encrypts it.

In the process of all this, I write a very fast class of line builders, implementation of AES encryption in ASM and the entire FastCGI library (www.coastrd.com)

So, as I said, non-trivial. I will make this library available soon. If you want to check it out, write to me.

After you write a message, you can begin to develop synchronization. I would either use a hash for each entry, or a simple boolean flag. If something changes on the server, just send the entire record and overwrite it on the client side (provided that you are trying to synchronize the clients ...)

If you write your own, please write here about your experience!

PS. Consider changing the name to be more search friendly. Maybe something like:

"Synchronizing the SQLite Client Database with the MySQL Server Database"

Answer 3

I believe that you say that you have a MySql server running on the same computer, and you are using the application with the sqlite instance, and you want to update the sqlite server if new data appears on the MySql server.

I would do it this way

1) Make sure that the table on your computer and in the application has the same structure. Include Last Updated Field

2) To check who is the most updated, get the last row by your unique key, and then compare the updated field.

3) When the most recent field refers to the server, then figure out how many lines are different and start copying them in a do ... while () loop, but you prefer.

If you implement this code both on the server and on the client, they can update each other or if you prefer only updating the client.

The specifics depend on the language you want to use and the amount of time you want to contribute to the development.

Answer 4

A few questions:

• Are you trying to perform two-way synchronization, or just pulling updates from the server?
• What update operations are allowed? Insert / Update / Delete? In replicated databases, they are usually deleted.

If you only need to pull the inserts / updates from the server, you can create a PHP script that will return the SQLite statements to execute. The PHP script will accept the parameter of the last update / time sequence from the client.

If you plan on doing two-way synchronization, you need to think about mergers and conflict resolution. For two parties, it is best for each side to have a transaction queue. Just write down every CRUD statement executed against the database. When synchronizing, follow these instructions and then trim the queue.

Answer 5

SQuirreL In SQL (in Java using Hibernate) there is a DBCopy plugin. This may be possible using a script copy of the database. I have not tried, but this is the first direction I would go.

Answer 6

entropyDb has an automatic replication feature that can handle this