Backup SQLITE3 db to mySQL

Solar2D General Questions/Discussion
#1

I want to add a backup feature to my biz app, now available on Apple/Google.

I have my own website with PHP/mySQL available to backup the SQLITE3 database to  in a BLOB field.

QUESTION

Is creating the backup (and restore) operation simply taking the sqlite3 db file and uploading it to mySQL? And the restore would do nothing more than drop the app’s existing Sqlite3 db - and download the stored file?

Am i being naive about this? Any gotchas? Any comments/advice re creating a backup process to mySQL (via PHP) much appreciated.

Does Coronium Cloud services provide a way to do a backup?

Thanks very much.

Seth

#2

I personally have never cared for blobs. It seems to me that if you want to save a file, that’s what the operating system is for. I’ve always been a proponent of saving the filename in the database along with other info and just using the system to manage the actual file. That said, using a blob is a valid technique, which is why that data type exists.

How big is the database going to be?

How often do you plan on backing it up?

Would it make sense to only back up individual records?

Are you going to be backing up many smaller individual databases or are all of your users going to have the same database?

Rob

#3

Rob,

I suspect it’s not a big effort to create a filename - and store that in the cloud-based database, along with an ID/email and password. And rename the incoming SQLITE3 database file with the new filename.

DB SIZE

A new Android install shows the DATA storage to be 92KB. This includes the sample data to start the app. A heavily used app could have a DB size of 1.0+MEG. At this point, I have no sense/idea of how long it will take to upload/download the database file from the cloud.

BACKUP FREQUENCY

I’ll probably create  a Backup button to create an ‘on demand’ backup by the User. I may provide an option to backup automatically on the app’s EXIT().

INDIVIDUAL RECORDS

I suppose here I’m essentially a bit lazy. If I do the individual records, it means numerous additional scripts; whereas if I do the entire database file ‘on demand’, it’s essentially one script to save; and another to restore it from the Cloud server.

SAME DATABASE

I thought on the Cloud-server, I’d have (at least) a UserID/Email field, a password field and the BLOB field for the SQLITE3 database file on the smartPhone. So there’s only one Cloud-based table to hold all the individual apps’ SQLITE3 databases. There is only one SQLITE3 database to be saved per smartPhone app.

it’d definitely be a nice prob to encounter when my mySQL server host says I need to buy more storage to handle the backups. For this reason alone, maybe your FILE SYSTEM implementation is more advantageous.

CORONIUM CLOUD

Does Corona’s new Coronoium Cloud offer a benefit here? or not even relevant?

Thanks for your input, Rob.

Seth

#4

Let me address a couple of these:

  1. uploading on exit. I know for certain this won’t do for Apple and I’m pretty sure Android is the same. When your app exits, you have just a short amount of time to close files and clean up before the OS force kills the app. You certainly won’t have time to upload a megabyte file on app exit.

  2. most people don’t have unlimited data plans and they are not always on WiFi. Frequent upload of a 1MB file or larger will be slow and eat up significant amounts of their data if it’s done too frequently. It seems to me that on-demand would make the most sense.

  3. Yes Coronium does support file uploads: http://docs.coronium.io/en/latest/client/Files/#uploadfile

#5

Thanks, Rob - your input always answers the question(s) posed and, as well, throws in a nuance that is invariably helpful.

#6

I personally have never cared for blobs. It seems to me that if you want to save a file, that’s what the operating system is for. I’ve always been a proponent of saving the filename in the database along with other info and just using the system to manage the actual file. That said, using a blob is a valid technique, which is why that data type exists.

How big is the database going to be?

How often do you plan on backing it up?

Would it make sense to only back up individual records?

Are you going to be backing up many smaller individual databases or are all of your users going to have the same database?

Rob

#7

Rob,

I suspect it’s not a big effort to create a filename - and store that in the cloud-based database, along with an ID/email and password. And rename the incoming SQLITE3 database file with the new filename.

DB SIZE

A new Android install shows the DATA storage to be 92KB. This includes the sample data to start the app. A heavily used app could have a DB size of 1.0+MEG. At this point, I have no sense/idea of how long it will take to upload/download the database file from the cloud.

BACKUP FREQUENCY

I’ll probably create  a Backup button to create an ‘on demand’ backup by the User. I may provide an option to backup automatically on the app’s EXIT().

INDIVIDUAL RECORDS

I suppose here I’m essentially a bit lazy. If I do the individual records, it means numerous additional scripts; whereas if I do the entire database file ‘on demand’, it’s essentially one script to save; and another to restore it from the Cloud server.

SAME DATABASE

I thought on the Cloud-server, I’d have (at least) a UserID/Email field, a password field and the BLOB field for the SQLITE3 database file on the smartPhone. So there’s only one Cloud-based table to hold all the individual apps’ SQLITE3 databases. There is only one SQLITE3 database to be saved per smartPhone app.

it’d definitely be a nice prob to encounter when my mySQL server host says I need to buy more storage to handle the backups. For this reason alone, maybe your FILE SYSTEM implementation is more advantageous.

CORONIUM CLOUD

Does Corona’s new Coronoium Cloud offer a benefit here? or not even relevant?

Thanks for your input, Rob.

Seth

#8

Let me address a couple of these:

  1. uploading on exit. I know for certain this won’t do for Apple and I’m pretty sure Android is the same. When your app exits, you have just a short amount of time to close files and clean up before the OS force kills the app. You certainly won’t have time to upload a megabyte file on app exit.

  2. most people don’t have unlimited data plans and they are not always on WiFi. Frequent upload of a 1MB file or larger will be slow and eat up significant amounts of their data if it’s done too frequently. It seems to me that on-demand would make the most sense.

  3. Yes Coronium does support file uploads: http://docs.coronium.io/en/latest/client/Files/#uploadfile

#9

Thanks, Rob - your input always answers the question(s) posed and, as well, throws in a nuance that is invariably helpful.