29

I am developing an android application. I want to update the local SQLite database with MySQL database on server. I am not able to figure out that what is the most appropriate and standardized way to do so?

Bill the Lizard
  • 398,270
  • 210
  • 566
  • 880
Harshit Agarwal
  • 1,345
  • 3
  • 20
  • 27

5 Answers5

14

Create a webservice (REST is probably best) and serialize your SQLite/MySQL data and PUT/POST/GET it to/from your web service. This will give you a nice layer of abstraction in case you decide to switch from MySQL to something else server side.

Andrew White
  • 52,720
  • 19
  • 113
  • 137
  • Can you please elaborate more on creating webservice? – Harshit Agarwal Jun 29 '11 at 04:59
  • 1
    That's a rather large topic that is more suitable for it's own question. You didn't supply any languages or platforms for the server side so there is not much more I can say. – Andrew White Jun 29 '11 at 11:51
  • 3
    what is the best approach to only update changed data from SQLite to MySQL databases? – Chris Mowbray Feb 04 '14 at 22:04
  • @AndrewWhite : Front end in Java with SQLite Database on the local client and PHP and SQL on the web end . Could you link me up to some tutorial on creating webservice for these requirement – Sagar Devanga Feb 24 '15 at 13:50
  • 1
    @SagarDevanga asking a favor of the author of an answer by commenting 4 years after the original post seems to be an overreaching use of the comment system. Feel free to ask a question on SO or programmer exchange. – Andrew White Feb 24 '15 at 14:24
  • @ChrisMowbray - the best approach to only update changed data that I have figured out and used is to use dirty bits to indicate which rows have been changed. – user2645830 Nov 10 '15 at 14:43
  • A good tip is to store lastUpdateDate and remoteId in the local android database in order to resolve conflicts. – quent Mar 24 '16 at 21:12
  • After you sync first time the db then send only the DIFF SQLs to the others – Duna Oct 28 '16 at 13:17
5

You may want to take a look at fyrecloud.com/amsler This is source code for a demonstration Android application that implements MySQL replication between a MySQL server and the SQLite db on an Android device.

Amsler rests on two pillars:

  1. It communicates with the MySQL server using the MySQL Client/Server protocol in order to connect to the server for authentication and for receiving replication events as they occur.

  2. It uses the Antlr lex and parse software in order to lex and parse incoming replication events and then to translate the MySQL commands into equivalent SQLite commands.

This is great for one-way replication. You can simulate two-way replication by modifying the MySQL server indirectly via RESTful type methods and then watching while MySQL sends a new replication event back.

Accessing a server via REST is easy enough. However, modifying an existing MySQL installation in order to support serialization presents too many headaches to enumerate here. Amsler takes advantage of pre-existing replication services. REST also depends upon some polling strategy in order to keep the local device reasonably up-to-date. Again, many problems with this approach. Amsler maintains a TCP/IP connection to the server which enables server-push notification of updates.

The most difficult part of Amsler is in figuring out the lexing/parsing. The Syntax between MySQL, SQLite, and the various versions of the same have many subtle differences. So many differences that it's impractical to provide a shrink-wrap translator and instead you must resort to modifying the grammar yourself.

Nevertheless, good, bad, or ugly, here it is. Take a look and maybe the glove fits.

Fyreman
  • 59
  • 1
  • 2
3

This is probably going to be helpful: sync databases Mysql SQLite

The real answer is that there is no standard or built in magic way to just copy a MySQL database that lives on a server somewhere to a device. You will have to implement either a webservice or somehow convert the MySQL db on the server to the android sqlite implementation and download that file into your app's data directory (not a route I'd recommend taking).

Community
  • 1
  • 1
QRohlf
  • 2,795
  • 3
  • 24
  • 27
  • 1
    quite interesting that the one you have linked to is marked as a duplicate of this one. – e4c5 Oct 02 '15 at 03:38
2

Late to the party, but http://www.symmetricds.org/ is a good solution.

Java, runs on Android too.

LGPL.

Can handle 10,000's of clients.

Neil McGuigan
  • 46,580
  • 12
  • 123
  • 152
  • Hey Neil, thanks for this post from a couple years ago. Sort of a vague question, since you're in the field is there anything else, newer, better, stronger, leaps over buildings ...... for the overwhelming issue of keeping a local android synced / offline with a remote sql database, like AmazonRDS say? Realm.io is fantastic but it only syncs/offlines with it's own database concept, "ROS" - not an ordinary sql server. Any thoughts perhaps? Cheers! – Fattie Dec 03 '16 at 15:04
0

There is no standard way. Depending on your needs you can e.g. use webservices in REST or SOAP protocols or more binary data exchange.

Manfred Moser
  • 29,539
  • 13
  • 92
  • 123
  • I would strong recommend against binary data exchange since they are so fragile (floating data type and data type size come to mind) – Andrew White Jun 28 '11 at 18:47
  • Depends on the data you are transferring. It can be advisable depending on the use case esp. when working with embedded devices. But yes.. in the normal use case you would probably look at REST first.. – Manfred Moser Jun 28 '11 at 18:54