Online database with offline capabailities?

Solar2D General Questions/Discussion
#1

Here is the scenario:

  • As a group, we want a database that will contain records that will be retrieved into a table view; no great shakes.
  • However, we will want to use this data abroad, where internet usage may be non existent or expensive.
  • so… we would like a solution where we can as a group modify freely that data when we are at home, from our various devices in a central place; but when we are on site we would like the data cached offline so we can see that same data (however not edit it, as it would then become out of sync)

I haven’t used database with corona sdk platform so i’m not sure what is achievable. My initial thoughts are…

  • App to download all data in JSON format and store locally (if internet not present, use the last saved json grab of the database). If the app has internet connection, enable the saving of data direct to the online database, and reload the local store.
  • Much the same as above, but rather than an online mysql database, have my website host a flat file database that is downloaded to the app when online, and edits posted back to the server.

Interested to know you thoughts, or if there is a better solution I am not aware of?

Thanks in advance,

Adam.

#2

Your online database should be whatever you need. But whatever data you need to sync, make sure the table has a lastUpdated column with a date value. I would personally recommend using an unsigned integer field and store the Unix time in seconds since Jan 1 1970 value that you get from calling the C-Library time function (available in PHP, JavaScript,… and Corona). Then when the mobile device needs to sync, it can send your online script the date/time value that it last synced. Then your online script can use as part of the query something like:  SELECT * FROM yourdata WHERE lastUpdateTime > clientLastUpdateTime

This will produce a minimal set of records that need replaced locally. If you use sqlite3 in your Corona App, you can use the REPLACE query which will replace any record with a matching ID. If not found, it will do an INSERT instead.

Rob

#3

Your online database should be whatever you need. But whatever data you need to sync, make sure the table has a lastUpdated column with a date value. I would personally recommend using an unsigned integer field and store the Unix time in seconds since Jan 1 1970 value that you get from calling the C-Library time function (available in PHP, JavaScript,… and Corona). Then when the mobile device needs to sync, it can send your online script the date/time value that it last synced. Then your online script can use as part of the query something like:  SELECT * FROM yourdata WHERE lastUpdateTime > clientLastUpdateTime

This will produce a minimal set of records that need replaced locally. If you use sqlite3 in your Corona App, you can use the REPLACE query which will replace any record with a matching ID. If not found, it will do an INSERT instead.

Rob