SQL Lite 3 Questions and Assistance

Solar2D General Questions/Discussion
#1

Hi All,

        I was just looking for some guidance with my app design. I’m going to have a local sqllite database pre populated with about 1000 records.

These records will need to be read frequently within the app to update the UI.

The records will need to be updated from within the app.

Is a local mysql database the best way to do this or should I be storing all this info in a massive lua table? The database has 2 tables one with 2 columns and one with 10 columns.

I don’t want the data to be accessible from outside the app as some of the data is going to be paid for content.

How would I go about releasing updates in the future? If I upgrade my app to version 2 and add new records to the database… how would I go about keeping the users existing data in the database and just adding the updated stuff?

Hope someone can point me in the right direction!

Many Thanks,

Krivvenz.

#2

I generally make a decision on database vs. large table based on a couple of criteria 

  1. Do you need all the data at once or do you only need a few records at a time?

  2. How much data?

Let’s look at them backwards. A million rows of data is going to take up a lot of device memory. In this case, it’s simply not practical for this to be a Lua table.

The first one comes down to search-ability. Do you need to iterate over all the rows every time you do it or is the data something that a simple search would bring up a more usable set of data?  Databases are about storing many records of data and giving you the few you need to work with. If this doesn’t describe your data usage, then tables may be the better way to go.

Now to the update question.

If you include your sqlite database in with your main.lua, it will be read-only and you can’t update it. You have to on first run, copy the database to system.DocuementsDirectory and have your app access it from there if you want updatable data. This tutorial will get you started solving that issue:  https://coronalabs.com/blog/2015/05/19/tutorial-initializing-a-writable-sqlite-database-from-a-read-only-database/

How do you plan to update the database?  New updates of your app? Requesting updates from a webserver?

It sounds like you’re planning on going the update your app route. In that case, you would test for the existence of the database in system.DocumentsDirectory and if you don’t find it, initialize a new one. If you find the database, then you would need code to only insert the new/updated records.

Rob

#3

Hi Rob,

           Thanks for taking the time to reply. What you said sums up exactly what I thought and you have given me the confidence to move forward :).

There will be about 1000 records but I will be querying them and only returning like 50 at a time so I think I had the right idea by using a database instead of Lua tables :).

With updating I did think I might have to have a second database or a text file and then copy it to the documents folder then put a check in to see if the main database has been updated and if not then run the insert / updates.

When someone downloads a new version of an app / updates am I right in saying that the stuff in the documents folder doesn’t get over written?

Many Thanks,

Krivvenz.

#4

Stuff in system.DocumentsDirectory is safe unless the user removes the app first. Normal updates, system.DocumentsDirectory is protected.

#5

I generally make a decision on database vs. large table based on a couple of criteria 

  1. Do you need all the data at once or do you only need a few records at a time?

  2. How much data?

Let’s look at them backwards. A million rows of data is going to take up a lot of device memory. In this case, it’s simply not practical for this to be a Lua table.

The first one comes down to search-ability. Do you need to iterate over all the rows every time you do it or is the data something that a simple search would bring up a more usable set of data?  Databases are about storing many records of data and giving you the few you need to work with. If this doesn’t describe your data usage, then tables may be the better way to go.

Now to the update question.

If you include your sqlite database in with your main.lua, it will be read-only and you can’t update it. You have to on first run, copy the database to system.DocuementsDirectory and have your app access it from there if you want updatable data. This tutorial will get you started solving that issue:  https://coronalabs.com/blog/2015/05/19/tutorial-initializing-a-writable-sqlite-database-from-a-read-only-database/

How do you plan to update the database?  New updates of your app? Requesting updates from a webserver?

It sounds like you’re planning on going the update your app route. In that case, you would test for the existence of the database in system.DocumentsDirectory and if you don’t find it, initialize a new one. If you find the database, then you would need code to only insert the new/updated records.

Rob

#6

Hi Rob,

           Thanks for taking the time to reply. What you said sums up exactly what I thought and you have given me the confidence to move forward :).

There will be about 1000 records but I will be querying them and only returning like 50 at a time so I think I had the right idea by using a database instead of Lua tables :).

With updating I did think I might have to have a second database or a text file and then copy it to the documents folder then put a check in to see if the main database has been updated and if not then run the insert / updates.

When someone downloads a new version of an app / updates am I right in saying that the stuff in the documents folder doesn’t get over written?

Many Thanks,

Krivvenz.

#7

Stuff in system.DocumentsDirectory is safe unless the user removes the app first. Normal updates, system.DocumentsDirectory is protected.