Local database storage in sandboxed app and real app on device

Solar2D General Questions/Discussion
#1

I am implementing a questionnaire like app with Corona, intending to store survey results in local SQLite database for later retrieving.  Some questions about programming db in app:

  1. ) In sandbox app within Corona simulator, I am not sure whether each time starting a fresh app, the local db

      in the app is re-initialize.  If yes, I think I need some mock data during development process.

  1. ) In real app on device, unless users uninstall the app or clean local data, the data is always there.  Is it

      true?

  1. ) I tried DB Browser for SQLite during developing my app.  Is it fine to use .sqlite as file extension and

      associate it with this DB Browser, or any other better software for browsing SQLite db?

Thanks for your replies in advance.

#2

Hello and welcome to the forums.

There will be two primary locations for working with your database:  system.ResourceDirectory which is read-only * and system.DocumentsDirectory which is writable. Your installable app will have to copy the database from system.ResourceDirectory to system.DocumentsDirectory on first start, or you have your base database creation/initialization code as part of your Lua code and you create the database in system.DocumentsDirectory from scratch.  I don’t believe the extension matters as you will provide the entire filename to Corona SDK’s API calls.

Now while you’re working in the simulator, we simulate the two main directories. system.ResourceDirectory is where your main.lua is and while it’s “read only”, the simulator may actually allow you to update it. This can be helpful while testing but it can also be a bad practice because you should plan from the beginning to only be able to write to system.DocumentsDirectory.  That folder is behind a really ugly path on your computer, but the simulator has an option to “Show Project Sandbox” which will take you to the folder where your files are stored. Once you know that path, you can use the locally installed copy of sqlite to open the database from the command line and manipulate it or use any app that can manipulate the database. I don’t know if you’re on a Mac or Windows so I’m being a little generic.

We have a tutorial that can help you learn to copy your binary initial database from system.ResourceDirectory to system.DocumentsDirectory.  See: https://coronalabs.com/blog/2015/05/19/tutorial-initializing-a-writable-sqlite-database-from-a-read-only-database/

We also have a more generic how to use SQLite in Corona tutorial here: 

https://coronalabs.com/blog/2012/04/03/tutorial-database-access-in-corona/

Rob

#3

Thanks for your reply.

I think setting local database in system.DocumentsDirectory is preferable because of frequent writing to the db in my app.

In addition, I find “Show Project Sandbox” is handy since I can browse the content of my db during development process.

Michael

#4

Hello and welcome to the forums.

There will be two primary locations for working with your database:  system.ResourceDirectory which is read-only * and system.DocumentsDirectory which is writable. Your installable app will have to copy the database from system.ResourceDirectory to system.DocumentsDirectory on first start, or you have your base database creation/initialization code as part of your Lua code and you create the database in system.DocumentsDirectory from scratch.  I don’t believe the extension matters as you will provide the entire filename to Corona SDK’s API calls.

Now while you’re working in the simulator, we simulate the two main directories. system.ResourceDirectory is where your main.lua is and while it’s “read only”, the simulator may actually allow you to update it. This can be helpful while testing but it can also be a bad practice because you should plan from the beginning to only be able to write to system.DocumentsDirectory.  That folder is behind a really ugly path on your computer, but the simulator has an option to “Show Project Sandbox” which will take you to the folder where your files are stored. Once you know that path, you can use the locally installed copy of sqlite to open the database from the command line and manipulate it or use any app that can manipulate the database. I don’t know if you’re on a Mac or Windows so I’m being a little generic.

We have a tutorial that can help you learn to copy your binary initial database from system.ResourceDirectory to system.DocumentsDirectory.  See: https://coronalabs.com/blog/2015/05/19/tutorial-initializing-a-writable-sqlite-database-from-a-read-only-database/

We also have a more generic how to use SQLite in Corona tutorial here: 

https://coronalabs.com/blog/2012/04/03/tutorial-database-access-in-corona/

Rob

#5

Thanks for your reply.

I think setting local database in system.DocumentsDirectory is preferable because of frequent writing to the db in my app.

In addition, I find “Show Project Sandbox” is handy since I can browse the content of my db during development process.

Michael