Help me structure database code for reference app?

Solar2D General Questions/Discussion
#1

Hi all,

I’ve been struggling with the problem of how to setup my database for my new app, so I thought, maybe you guys have some good ideas.

What I plan to do:

  • I’m creating a reference app that also features a quiz functionality. Basically it’s a bunch of items in several categories with some info and an image.
  • At first the user has access to 1-2 categories and can unlock others via in-app purchases.
  • I’m probably going to add more categories over time.
  • Right now I have a giant spreadsheet with all the information

My problem:

What is the best way to convert this spreadsheet to include in an app?

I see a couple of sollutions:

  1. convert the spreadsheet to a csv file and convert that to a giant lua table every time the app starts
  2. convert the spreadsheet to a csv and use that to populate a sqlite db (this is probably faster, but how do I go about when I update the csv in future updates?)
  3. stop using a spreadsheet and start using a database so I can export an sql and include that in the app code? But I’m not sure what to use to create and manage this database …

Does anyone have any brilliant ideas how to best tackle this?

#2

I’m a fan of databases. Structure good! I’m not sure how much or how you are working with the data. But I would look into having it in SQLite at all times.

Example workflow: 

Convert CSV to SQLite one time and use Firefox SQLite plugin to add/modify/delete data as necessary on a daily bases (or some other SQLite manager).

When ready to publish, just copy db to App directory as is. On app install you may need to copy to documents or temp directory. I’m not sure why, just remember hearing something about that.

#3

Yes, as @jon said, if you plan on changing the data in the DB at run time, you need to make a copy of it to the Documents directory or your package will become invalid to the system.

You might start here http://www.coronalabs.com/blog/2012/04/03/tutorial-database-access-in-corona/

Cheers.

#4

Wow, this sounds really interesting! I never realized that you could actually package a database with the app. Thought you had to create it at the first runtime. I’m going to play with this a bit, great!

#5

I’ve been playing around with it a bit and I only get it to work when I place my .sqlite database file that I created in the project sandbox (in the simulator). Is this the way to go?

You guys also mentioned that I should make a copy to the project directory, how do I go about that?

thanks!

#6

After some more digging I got it to work.

You can check out these APIs for moving the database from the ReferenceDirectory to the DocumentDirectory

http://docs.coronalabs.com/guide/data/readWriteFiles/index.html

#7

I’m a fan of databases. Structure good! I’m not sure how much or how you are working with the data. But I would look into having it in SQLite at all times.

Example workflow: 

Convert CSV to SQLite one time and use Firefox SQLite plugin to add/modify/delete data as necessary on a daily bases (or some other SQLite manager).

When ready to publish, just copy db to App directory as is. On app install you may need to copy to documents or temp directory. I’m not sure why, just remember hearing something about that.

#8

Yes, as @jon said, if you plan on changing the data in the DB at run time, you need to make a copy of it to the Documents directory or your package will become invalid to the system.

You might start here http://www.coronalabs.com/blog/2012/04/03/tutorial-database-access-in-corona/

Cheers.

#9

Wow, this sounds really interesting! I never realized that you could actually package a database with the app. Thought you had to create it at the first runtime. I’m going to play with this a bit, great!

#10

I’ve been playing around with it a bit and I only get it to work when I place my .sqlite database file that I created in the project sandbox (in the simulator). Is this the way to go?

You guys also mentioned that I should make a copy to the project directory, how do I go about that?

thanks!

#11

After some more digging I got it to work.

You can check out these APIs for moving the database from the ReferenceDirectory to the DocumentDirectory

http://docs.coronalabs.com/guide/data/readWriteFiles/index.html