SQLite best practices

Solar2D General Questions/Discussion
#1

Hi Corona Wizards…

I have a prepopulated SQLite db I have created which holds puzzle data and also has some tables to accomodate tracking scores etc (so it needs to be writeable).

From what I have read the documents directory is required for Read/Write access. The resource directory is Read only.

In several of the examples I have seen, it seems the common practice is to dynamically test for the tables existence and create and populate as necessary.

Is it bad practice to have the populated DB distributed with the app in the resource directory and copied over at runtime versus dynamic creation? Are there downsides to this methodology?

I intend to use in-app purchase for additional puzzles…I assume I can then programatically INSERT new puzzles into the DB? (Forgive my lack of experience with in-app). Does this have any impact on the placement choice of the DB?

Any information is appreciated,

Rick

#2

If you plan to write to the database (add new records, update existing ones) then it must be in a Read/Write area in the sandbox… i.e. system.DocumentsDirectory.  I’ve never been fond of trying to do a binary copy of databases.  But you would need to create a new DB in system.DocumentsDirectory and then populate it.  Perhaps, a good way would to have your initialzation SQL in a text file, and if you detect the database isn’t where you expect it, read in the SQL and populate the DB.

Once you are running, you can then use IAP to determine if something is unlocked and them whatever means you like to get the data into the tables (download SQL from a server, have it pre-populated in your bundle and load it in,  Have it be part of your master DB to begin with and have a field that shows those records have to be unlocked.

Keep in mind that if someone deletes your app and then reinstalls it everything in your system.DocumentsDirectory will be removed too and you will have to do a store.restore() to unlock previous purchases and re-initialze your DB accordingly.

#3

If you plan to write to the database (add new records, update existing ones) then it must be in a Read/Write area in the sandbox… i.e. system.DocumentsDirectory.  I’ve never been fond of trying to do a binary copy of databases.  But you would need to create a new DB in system.DocumentsDirectory and then populate it.  Perhaps, a good way would to have your initialzation SQL in a text file, and if you detect the database isn’t where you expect it, read in the SQL and populate the DB.

Once you are running, you can then use IAP to determine if something is unlocked and them whatever means you like to get the data into the tables (download SQL from a server, have it pre-populated in your bundle and load it in,  Have it be part of your master DB to begin with and have a field that shows those records have to be unlocked.

Keep in mind that if someone deletes your app and then reinstalls it everything in your system.DocumentsDirectory will be removed too and you will have to do a store.restore() to unlock previous purchases and re-initialze your DB accordingly.

#4

I have a query related to this.

My current sit - iOS only.

Have database with game data is downloaded with app and game accesses it via 

        local pathSq = system.pathForFile( “database.db”, system.ResourceDirectory )

ie system.ResourceDirectory  - I currently only read only no writing to it - and if in future did require writing would probably create separate file.

In Corona Geek 47 - there was mention of possible problems with access to ResourceDirectory (re Android). And the files associated with this episode had the following code;

[lua]require(“sqlite3”)

local widget = require(“widget”)

local zip = {}

local doneDB = false

– Does the database exist in the documents directory (allows updating and persistance)

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

file = io.open( path, “r” )

   if( file == nil )then           

       – Doesn’t Already Exist, So Copy it In From Resource Directory                          

       pathSource     = system.pathForFile( “zip.sqlite”, system.ResourceDirectory )  

       fileSource = io.open( pathSource, “rb” ) 

       contentsSource = fileSource:read( “*a” )                                  

        --Write Destination File in Documents Directory                                  

        pathDest = system.pathForFile( “zip.sqlite”, system.DocumentsDirectory )                 

        fileDest = io.open( pathDest, “wb” )                 

        fileDest:write( contentsSource )                 

         – Done                      

        io.close( fileSource )        

        io.close( fileDest )         

   end   

– One Way or Another The Database File Exists Now 

[/lua]

My question relates to the “(allows updating and persistance)” - HOW?

I’m really a newbie - but have gotten some apps up and running  - but if i change the database under my current situation it gets included in the binary so when user downloads they get the new version of the database and since the app accesses this db - no problem.

If i implement this transfer of db to the system.DocumentsDirectory - how can any changes to the downloaded db be included??

The way i read it is the code checks to see if the db exists in system.DocumentsDirectory - if it does then the app will use this db rather than the one in system.ResourceDirectory - am i correct? 

just me trying to understand here - Can i assume that the check to see if db exists Checks All properties of the db file - ie database.db {filename}, file size, file create date, file modified date { metadata (if thats the correct term) } and all properties would have to match perfectly between system.DocumentsDirectory & system.ResourceDirectory. - If they did not match perfectly then the system.DocumentsDirectory file would be overwritten with the new db file.

Am i right in that assumption?

thanks

TDS

#5

I have a query related to this.

My current sit - iOS only.

Have database with game data is downloaded with app and game accesses it via 

        local pathSq = system.pathForFile( “database.db”, system.ResourceDirectory )

ie system.ResourceDirectory  - I currently only read only no writing to it - and if in future did require writing would probably create separate file.

In Corona Geek 47 - there was mention of possible problems with access to ResourceDirectory (re Android). And the files associated with this episode had the following code;

[lua]require(“sqlite3”)

local widget = require(“widget”)

local zip = {}

local doneDB = false

– Does the database exist in the documents directory (allows updating and persistance)

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

file = io.open( path, “r” )

   if( file == nil )then           

       – Doesn’t Already Exist, So Copy it In From Resource Directory                          

       pathSource     = system.pathForFile( “zip.sqlite”, system.ResourceDirectory )  

       fileSource = io.open( pathSource, “rb” ) 

       contentsSource = fileSource:read( “*a” )                                  

        --Write Destination File in Documents Directory                                  

        pathDest = system.pathForFile( “zip.sqlite”, system.DocumentsDirectory )                 

        fileDest = io.open( pathDest, “wb” )                 

        fileDest:write( contentsSource )                 

         – Done                      

        io.close( fileSource )        

        io.close( fileDest )         

   end   

– One Way or Another The Database File Exists Now 

[/lua]

My question relates to the “(allows updating and persistance)” - HOW?

I’m really a newbie - but have gotten some apps up and running  - but if i change the database under my current situation it gets included in the binary so when user downloads they get the new version of the database and since the app accesses this db - no problem.

If i implement this transfer of db to the system.DocumentsDirectory - how can any changes to the downloaded db be included??

The way i read it is the code checks to see if the db exists in system.DocumentsDirectory - if it does then the app will use this db rather than the one in system.ResourceDirectory - am i correct? 

just me trying to understand here - Can i assume that the check to see if db exists Checks All properties of the db file - ie database.db {filename}, file size, file create date, file modified date { metadata (if thats the correct term) } and all properties would have to match perfectly between system.DocumentsDirectory & system.ResourceDirectory. - If they did not match perfectly then the system.DocumentsDirectory file would be overwritten with the new db file.

Am i right in that assumption?

thanks

TDS