Synchronizing with remote MySQL

[aklisiewicz]

I have a need for an App which will hold some data in SQlite locally. This App will need to synchronize the data with remote MySQL database. I already have MySQL application running on the Apache/PHP server. I need to create a diplicate database in SQLite and then setup some way of synchronizing. there are 2 possible scenarions and I need to figure out which one to chhose.
1. I can create a PHP/SQL script to copy new/updated records from MySQL to SQLite on the server then allow to download SQLite.db to the phone (possibly through FTP)
2. Go record by record. Pull up a recordset from the remote MySQL , process it on the phone (updating) local phone database.

The SYNC will be done on demand so once a day to once a week is sufficient. No need for live continuous updates

Perhaps there is another scenario which I'm not aware of.
I would like to see your opinions on which method is:
- easier to implement (I'm rather B4A newbie)
- is more efficient for the user
- is more reliable

On top of that, as a second stage I would like to implement synchronization in opposite direction (from the phone to the MySQL remote server). Initially phone App will work in Read Only mode but in a second stage it should allow full record manipulations and updates.

since all tables in both databases will be identical, but I realize that the 2 databases are not compatybile, the question arises:
Is there any utility.library which will allow to convert MySQL -> SQlite which I can incorporate into web App or mobile App ?

Thank you for opinions and advice.

Arthur

 

[Erel]

Both approaches are possible (sending the complete database file or sending the updates).

I would have probably chosen to only send the updates. I think that it is a more robust solution. You can add a time column to each record and then send a query to the database to return all the records that are newer than the device last successful update time.

 

[aklisiewicz]

thanks for your opinion.

BTW.
Based on the following scenario:
1. I have a DATETIME() type field, but want to store only DATE

2. I have DATETIME() field and want to store date and time (possibly in two separate variables)

both designated to hold last update date

Is there any function to separate DATE from TIME (since most SQL databases keeps the two in one column)?
How would I input / output (display) a curent date and time into/from the field ???


Arthur

 

[Erel]

SQLite date functions: SQLite Query Language: Date And Time Functions

You can also handle all the date / time values in Basic4androi.