embed read only database

Solar2D General Questions/Discussion
#1

I’m trying to include a database of information that would be part of the application for retrieval.

I’m looking at the SQLite3 package, but it looks like it’s meant to keep data on system that I could write to, which is great for other purposes, but I’d like to keep a collection of data that would only update if the user updates the app.

I could write the database onto the system the first time it runs (which I don’t like)

or I could create the data collection as a big table inside a lua file.

any other ideas?

#2

Even though you can write data with SQLite, it doesn’t mean you have to. 

Which method to choose depends on how much data you have, and how complex the data structure is. If you can get away with a table that’s OK. It’s definitely easier to maintain with smaller amounts of data (think a 100 or so records). But if you have thousands of records, it might be worth an SQLite instance. Especially if you have a relational data model with multiple tables.

#3

Could you encode the data in a JSON (or XML) file?  You could include it as an asset inside the app package, but it’s still fairly easy to read/import into the code.  

#4

that was my initial plan, but it seems to me that parsing the data from xml or json would add extra overhead. If I encode the data in a table inside a lua file, the end result is the same

#5

I wouldn’t have thousands of records, but the relational nature and SELECT commands would be nice.

is it possible to have a database that would be included with the package without creating the database on the system?

How do most people deal with using these databases? I take it the databases are created on first run.

#6

You can embed a full SQLite database within the app. It would be held in the Resources directory, and would be read only. You would access it with something like:

local sqlite3 = require("sqlite3") local path = system.pathForFile("GameSetup.sqlite", system.ResourceDirectory) local dbConnection = sqlite3.open(path) local query = "SELECT \* FROM SomeTable" dbConnection:nrows(query)

Simply create your DB in an editor of choice then save it to the root of your project folder (where main.lua is). It will get included in the app when you build.

#7

You can use something like http://sqlitebrowser.org to create and edit your database on your desktop.

#8

There is very little overhead for reading in text from a data file that’s JSON encoded and then decode it:

myDBTable = json.decode( JSONencodedTextReadFromFlatFile )

As mentioned above, you can put your SQLite DB file in the folder with your main.lua and it will be read only in your bundle.

Rob

#9

Thanks 'yall

I see what I was missing now.

I’ve been using 

local path = system.pathForFile(“GameSetup.sqlite”, system.DocumentsDirectory )

instead of 

local path = system.pathForFile(“GameSetup.sqlite”, system.ResourceDirectory)

Thanks

#10

It sort of sounds like you’re able to generate the database dynamically, but I’m not sure.

If so, you could create it on the first run in the simulator , where you have some leeway with the resource directory. As long as it’s there, it would then get picked up during builds.

#11

If you want your mobile app to create the database, you will still need to use system.DocumentsDirectory, but once it’s populated, move it to the folder with your main.lua and then in the future reference it from system.DocumentsDirectory.

#12

Hi Rob,

sorry I"m having a hard time here with the concept still,

Are you saying I can move it between locations in runtime?

#13

For your use, no, this is not what I’m talking about.   However it is a common practice to include an SQLite database in your ResourceDirectory (which is read only) and then on first start, create a writable DB in DocumentsDirectory and read all the records from the read only DB and write them to the writable DB for future use when you need a pre-populated writable DB.

But in your case, you need a way to create the read only DB.  There are various tools to do this, but if you want to use Corona, the Simulator and such to create the initial database, then you can use the simulator to build the SQLite DB and when it’s populated, copy it on your computer into the folder with main.lua and have your actual app look in system.ResourcesDirectory to find the read only DB.

Rob

#14

Even though you can write data with SQLite, it doesn’t mean you have to. 

Which method to choose depends on how much data you have, and how complex the data structure is. If you can get away with a table that’s OK. It’s definitely easier to maintain with smaller amounts of data (think a 100 or so records). But if you have thousands of records, it might be worth an SQLite instance. Especially if you have a relational data model with multiple tables.

#15

Could you encode the data in a JSON (or XML) file?  You could include it as an asset inside the app package, but it’s still fairly easy to read/import into the code.  

#16

that was my initial plan, but it seems to me that parsing the data from xml or json would add extra overhead. If I encode the data in a table inside a lua file, the end result is the same

#17

I wouldn’t have thousands of records, but the relational nature and SELECT commands would be nice.

is it possible to have a database that would be included with the package without creating the database on the system?

How do most people deal with using these databases? I take it the databases are created on first run.

#18

You can embed a full SQLite database within the app. It would be held in the Resources directory, and would be read only. You would access it with something like:

local sqlite3 = require("sqlite3") local path = system.pathForFile("GameSetup.sqlite", system.ResourceDirectory) local dbConnection = sqlite3.open(path) local query = "SELECT \* FROM SomeTable" dbConnection:nrows(query)

Simply create your DB in an editor of choice then save it to the root of your project folder (where main.lua is). It will get included in the app when you build.

#19

You can use something like http://sqlitebrowser.org to create and edit your database on your desktop.

#20

There is very little overhead for reading in text from a data file that’s JSON encoded and then decode it:

myDBTable = json.decode( JSONencodedTextReadFromFlatFile )

As mentioned above, you can put your SQLite DB file in the folder with your main.lua and it will be read only in your bundle.

Rob