0

I've only recently started to deal with database systems. I'm developing an ios app that will have a local database (sqlite) and that will have to periodically update the internal database with the contents of a database stored in a webserver (mySQL). My questions is, whats the best way to fetch the data from the webserver and store it in the local database? There are some options that came to me, don't know if all of them are possible

  1. Webserver->XML/JSON->Send it->Locally convert and store in local database

  2. Webserver->backupFile->Send it->Feed it to the SQLite db

Are there any other options? Which one is better in terms of amount of data taken?

Thank you

John Parker
  • 54,048
  • 11
  • 129
  • 129
Mppl
  • 941
  • 10
  • 18
  • Your first approach is best, use RESTful web services to send data from your DB, parser the data and save it to your local sqlite – Shams Ahmed Aug 04 '13 at 11:06

1 Answers1

0

The XML/JSON route is by far the simplest while providing sufficient flexibility to handle updates to the database schema/older versions of the app accessing your web service.

In terms of the second option you mention, there are two approaches - either use an SQL statement dump, or a CSV dump. However:

  1. The "default" (i.e.: mysqldump generated) backup files won't import into SQLite without substantial massaging.

  2. Using a CSV extract/import will mean you have considerably less flexibility in terms of schema changes, etc. so it's probably not a sensible approach if the data format is ever likely to change.

As such, I'd recommend sticking with the tried and tested XML/JSON approach.

In terms of the amount of data transmitted, JSON may be smaller than the equivalent XML, but it really depends on the variable/element names used, etc. (See the existing How does JSON compare to XML in terms of file size and serialisation/deserialisation time? question for more information on this.)

Community
  • 1
  • 1
John Parker
  • 54,048
  • 11
  • 129
  • 129
  • Thank you for your answer. about the default backup files won't it be possible to structure the mySQL db in such a way as to make it directly importable to sqlite? Thank you. – Mppl Aug 04 '13 at 12:22
  • 1
    @mppl If you look at the file produced by mysqldump, you'll see that it uses syntax that SQLite won't understand, irrespective of the field types, etc. in use. (It should be trivial to test, that said.) – John Parker Aug 04 '13 at 12:26