Developing SQLite-based apps

Solar2D General Questions/Discussion
#1

Hi guys,

Is there any way to test apps which require SQLite - ie, making sure the data is correct, and that the database actually populates correctly…
Thanks in advance! [import]uid: 40538 topic_id: 9910 reply_id: 309910[/import]

#2

What I do is use an SQLITE browser to open my database file. It lets me run queries on the database or make changes. I don’t remember exactly where I got it from but I remember that I just Googled for one and downloaded it. [import]uid: 31262 topic_id: 9910 reply_id: 36140[/import]

#3

Thanks for replying aaaron,

I’ve had to create a manual data.db file for test purposes and make the path point to system.ResourceDirectory. Something is definately getting picked up by Corona and my app as there is content inside the file when I open it up.

Only thing is, that the content of the file is the query that I use to create the database in the first place!
Would you mind letting me know how to set this up correctly?

Same with databases held in memory - this would actually suit my app better, and again, I’d rather know what it contains first time round rather than processing queries to display the contents.
Thanks [import]uid: 40538 topic_id: 9910 reply_id: 36142[/import]

#4

So your code to create the database actually just stores the query? Probably just an error in the code. I used ricardorauber’s code on this page as a starting point when I first started with sqlite. It’s located here:

http://developer.anscamobile.com/forum/2011/02/02/how-can-i-easily-implement-save-and-load-system-0

This section checks to see if a database exists, and if it doesn’t then it creates it.

local dbPath = system.pathForFile("settings.db", system.DocumentsDirectory)  
local db = sqlite3.open( dbPath )  
--  
db:exec [[CREATE TABLE IF NOT EXISTS tb\_settings (  
 name VARCHAR(50) PRIMARY KEY,   
 type VARCHAR(1),  
 value\_string TEXT,   
 value\_number NUMBER);]]  
--  
local function onSystemEvent( event )  
 if( event.type == "applicationExit" ) then   
 db:close()  
 end  
end

He also tells you how to use it in the 4th post. I had to simplify the rest of it since my game doesn’t need to check for types etc. I have a database with 1 table and about 5 columns that I read and write to. I also added a line that inserts default values into the database if it was just created for the first time.

If this doesn’t really do what you need maybe elaborate a bit on what you need the database for. Maybe my version may be a better fit. I can post it when I get home from work today if you need.
[import]uid: 31262 topic_id: 9910 reply_id: 36264[/import]

#5

Thanks aaaron,

I’ll try with the advice you’ve given and see how it goes.
Cheers again! [import]uid: 40538 topic_id: 9910 reply_id: 36326[/import]