SQLite DB: File location

Solar2D Daily Builds and Public Releases
#1

I’d like to be able to use my SQLite tool to create my DB, and then drop that file into a folder which acts as the “Documents Directory”, i.e. a special folder in the Corona project’s folder.

I find the current method of working to be a bit cumbersome, e.g. creating all the tables etc in Corona code. I know I could place my SQLite file into the sandbox area, but I don’t want to. I don’t do that with anything else. There should be a “Documents Directory” folder inside every Corona project folder for this purpose.

Unless I’m way off base here, in which case please enlighten me :slight_smile: [import]uid: 26769 topic_id: 7414 reply_id: 307414[/import]

#2

DDUUUDDEEE!!!

I was *JUST* wrestling with this last night!!!

I came across another thread with the solution:
http://developer.anscamobile.com/forum/2011/02/11/director-class-sqlite

But to shorten it for you, throw your database file in the SAME- DAMNED- DIRECTORY- as your’main.lua’ file!!

Yeah, I know, I know…makes no sense what so freakin’ ever…but there ya go.

NOTE THE “system.ResourceDirectory” part, THAT’S what’ll makeit work for you!

--Include sqlite  
require "sqlite3"  
local path = system.pathForFile("agm.sqlite", system.ResourceDirectory)  
db = sqlite3.open( path )

[import]uid: 11636 topic_id: 7414 reply_id: 29995[/import]

#3

That doesn’t really address his problem, but is certainly an important part to realize. The Corona command system.ResourceDirectory refers to the root directory you put your project in, so that can be used to directly read any files.

The problem is that directory is read-only. Obviously that’s fine if the only thing you want to do is read, but if you are also going to write to the database then it needs to be in the documents directory.

If you want to provide a database with your app that is later written to with new data, what you need to do is copy the database from the resource directory to the documents directory. That’s kinda cumbersome, so I find it easiest to simply generate the database in code.

makes no sense what so freakin’ ever

Why doesn’t that make any sense? The main project directory is where you stick all your graphics and sound assets after all. The only confusing part is that you refer to the project directory with system.ResourceDirectory [import]uid: 12108 topic_id: 7414 reply_id: 30008[/import]

#4

Oh, sorry! I didn’t see that he wanted to write to it afterwards. I thought he was in the same boat as me, creating and fiddling with the DB with like an admin tool on the PC (Or Mac or whatever) and then just throw the DB into a directory for deploying and reading from after deploying.

@lordmooch, did you want to alter the DB afterwards?

@jhocking “makes no sense what so freakin’ ever” because I couldn’t actually get my READ ONLY db to actually work in my deployed app (It would just not see it and be nil) until I threw it in the “system.ResourceDirectory”, so I think I’m gonna open up another thread and whine about that. I mean, it would work in the simulator, but just fail when I deployed it to my iPad or iPod or iPhone. :frowning: [import]uid: 11636 topic_id: 7414 reply_id: 30016[/import]

#5

I’ve now got a satisfactory solution to my problem. I use SQLite Manager to create and modify my database and I place the file it creates into my Corona app’s folder. I then use some code that I found on these forums to copy the file into the Documents Directory.

This works a treat and saves me having to do a lot of donkey work using Lua code.

However, whenever I release an app update after my app goes live, then I will be using Lua code to create the SQL statements to update the database. This is because I can’t overwrite the existing database as it would, of course, destroy the poor user’s data.

@jhocking: it’s not cumbersome at all to copy the file over to the Documents directory. I’ve got a lovely little bit of code that does just that. It’s much easier to work in SQLite Manager than it is to work in Lua for SQL work, but each to their own :slight_smile: [import]uid: 26769 topic_id: 7414 reply_id: 30142[/import]

#6

*shrug* I guess it depends on what exactly you are putting in the database. I’m mostly using SQLite to save user settings so that’s not something I need to do a lot of initial setup on; it’s just a few CREATE TABLE commands.

If I wanted to put lots of data in there like enemy properties then yeah I would go crazy doing that in code, plus doing it in code would be redundant because the data is already in the code. Although in that case I would actually have separate files for the read-only database and the user database; the former I would create outside the code and ship with the app, while the latter I would generate in code. [import]uid: 12108 topic_id: 7414 reply_id: 30188[/import]

#7

So let me confirm a couple things with you guys so I don’t tear my hair out down the road…

If I want to alter/update a database, it HAS to be in the “system.DocumentsDirectory”?

And if I drop a pre-made database into the “system.ResourceDirectory” (Where main.lua is) and want to do some INSERTS or update THAT particular database, I can’t until I copy it on over to the “system.DocumentsDirectory”? Using the awesome code located here:

http://developer.anscamobile.com/forum/2010/09/25/sqlite-writes-crashing-app-only-selects-my-app

I’d check this myself, but the dumb Corona Auth server ATE my damned license again and thinks it’s running on another computer when it’s not. Grrrrrr…

I appreciate the info here, cause it seems there’s some confusion regarding SQLite’s usage and implementation on the devices. I know my code works fine in the simulator but it’s a whole 'nother world once you deploy!
[import]uid: 11636 topic_id: 7414 reply_id: 30236[/import]

#8

Yes, that’s basically it :slight_smile:

While you can update something in the Resources directory it’s not advised because it causes your app signing to become invalid and your app won’t run. So, think of Resources as read only, and Documents as read-write. [import]uid: 26769 topic_id: 7414 reply_id: 30269[/import]

#9

Thanks folks, I appreciate it. I’m ramping up to use my databases in earnest now but I was planning on using a database to store player info too, good to know that the app-signing issue would come up unless I used the Documents directory.

Overall, great info here on the implementation of SQLite. [import]uid: 11636 topic_id: 7414 reply_id: 30385[/import]

#10

The system.resourceDirectory is where your main.lua file and generally all your asset (resource) files are stored. For security reasons, this directory is made read-only and enforced by the operating system and not Corona. If you need to update something in the resource directory, you need to move it to the Documents or Temporary directories. [import]uid: 7559 topic_id: 7414 reply_id: 63132[/import]