How do I check a sqlite database exists?

Solar2D General Questions/Discussion
#1

This is something thats puzzled me when developing in flash…

I would go to lengths as to check for a specific table names/data before I could make a decision on if a database had already been created
so in corona to both “open” and “create” a sqlite database you would do somthing like the below…

[lua]local path = system.pathForFile( “data.db”, system.DocumentsDirectory)
local db = sqlite3.open( path )[/lua]
But how would I check to see if a database already existed so that I know if im working with a empty database or one with some data init?

Thanks
Aidan [import]uid: 102413 topic_id: 26102 reply_id: 326102[/import]

#2

An empty database needs tables, and if run this code you know that it just has been created.

local tablesetup = [[CREATE TABLE IF NOT EXISTS gamedata (id INTEGER PRIMARY KEY, opened INTEGER, PO INTEGER);]];  
db:exec( tablesetup );

And you can also query a table to see if it has any records, if not it new for sure :slight_smile:

Joakim [import]uid: 81188 topic_id: 26102 reply_id: 105696[/import]

#3

But would this return anything Joakim?
Like a true/false as to whether or not it created the table “gamedata”

Thanks
Aidan [import]uid: 102413 topic_id: 26102 reply_id: 105699[/import]

#4

No, I guess not but use a query to check the tables for data. If there is no data, then you know that it is empty. Heres a snippet of code I am using in my current game.

--Check if gamesettings is empty, if so fill it up with defaults  
for row in db:nrows("SELECT max(id) as RET FROM gamedata") do  
 if not (row.RET) then  
 local tablesetup = [[INSERT INTO gamedata (opened,PO) VALUES(0,0);]];  
 db:exec( tablesetup );  
 storyboard.isOpened = 0;  
 isPO = 0;  
 else  
 for row in db:nrows("SELECT opened, PO FROM gamedata") do   
 storyboard.isOpened = row.opened;  
 isPO = row.PO;  
 end  
 end  
end

I am looking at the index field id, and selecting the max id from the table. The I just use a if else statement to do what I want.

Joakim [import]uid: 81188 topic_id: 26102 reply_id: 105701[/import]

#5

Yeah I did something very similar to that in flash…
Just looking for something sweet un simple!

google threw up this for java… simply checks file exists first.
That would be nice in corona…

[java]
public static void databaseConnect(String dbName) throws Exception {

File file = new File (dbName);

if(file.exists()) //here’s how to check
{
System.out.print(“This database name already exists”);
}
else{

Class.forName(“SQLite.JDBCDriver”).newInstance();
conn = DriverManager.getConnection(“jdbc:sqlite:/”+ dbName);
stat = conn.createStatement();

}
[/java]

But yes I will probably do something similar to what you showed.
Thanks [import]uid: 102413 topic_id: 26102 reply_id: 105705[/import]

#6

You could use the new Lua filesystem to see if the file is present - but that doesn’t make any sense, you have to know if it has tables and data i guess…

Joakim [import]uid: 81188 topic_id: 26102 reply_id: 105709[/import]

#7

I ship a database with my apps that’s already built. It has schema and default data as necessary. I simply check for the existence of this database in the Documents directory, and if it’s not there I copy it over. If you don’t “CREATE” new databases dynamically in code this is a good approach (i.e., it is not possible for the database to be empty). [import]uid: 44647 topic_id: 26102 reply_id: 105728[/import]

#8

ahh this is what I need anyway Toby, I just couldnt find much documentation on shipping db’s with an app…
How do you go about that? [import]uid: 102413 topic_id: 26102 reply_id: 105731[/import]

#9

You know anything you package in your app is in the Resource Directory, where it’s read-only. Lots of databases stay right there because they don’t need to be modified at runtime. You ship a user database that remembers user options or high scores or something, you need to move it over to the Documents directory so it can be written to.

I use a function that looks like this. I call it in main.lua before transitioning to my first scene.

[lua]local function checkDB(dbName)
local dbPath = system.pathForFile(dbName, system.DocumentsDirectory)
–test the dbFile
local dbFile = io.open( dbPath, “r” )
if not dbFile then
– db not in documents! copy the thing right over.
local dbSourcePath = system.pathForFile(dbName, system.ResourceDirectory)
local dbFileSource = io.open( dbSourcePath, “r” )
local dbContentSource = dbFileSource:read( “*a” )

local dbFileDest = io.open ( dbPath, “w” )
dbFileDest:write( dbContentSource )

io.close (dbFileSource)
io.close (dbFileDest)
end
end

checkDB(“user.db”)[/lua] [import]uid: 44647 topic_id: 26102 reply_id: 105732[/import]

#10

wayyy thats spot on that toby!
Works a charm!
Thanks for sharing.
[import]uid: 102413 topic_id: 26102 reply_id: 105747[/import]

#11

Hey Toby, I was having a play around with the code you posted. Ive noticed that when i open the database that was copied over to the sandbox folder, I find its now corrupt and I caunt write any date to the tables in that database. Do you not have the same issue? [import]uid: 102413 topic_id: 26102 reply_id: 106911[/import]

#12

I am also having this problem. I tried the same code above with the same results. i get a corrupted database file on the system.DocumentsDirectory

anyone know why? [import]uid: 88147 topic_id: 26102 reply_id: 107061[/import]

#13

I’m not sure, guys. I haven’t had any such problems. You could take out the spaces between the function name and parameters in lines 11, 14, and 15, but I don’t know if that matters.

One other thing missing from the code snippet is to close the file handle if it’s open:

[lua] if not dbFile then
– db not in documents! copy the thing right over.
local dbSourcePath = system.pathForFile(dbName, system.ResourceDirectory)
local dbFileSource = io.open( dbSourcePath, “r” )
local dbContentSource = dbFileSource:read( “*a” )

local dbFileDest = io.open( dbPath, “w” )
dbFileDest:write( dbContentSource )

io.close(dbFileSource)
io.close(dbFileDest)
else
io.close( dbFile )
end[/lua]

Clean your sandbox directories and run the code again. If the resulting db file has size 0 there’s something preventing a clean copy. [import]uid: 44647 topic_id: 26102 reply_id: 107069[/import]

#14

I am getting a 4k file instead of a 17k file.

My database has multiple tables in it, that wouldn’t make a difference on how you read the file would it? [import]uid: 88147 topic_id: 26102 reply_id: 107077[/import]

#15

Thanks Toby,
I still get that the same issue with it though.

What is everyone’s O.S? could be a bug?

I’m on Windows vista. [import]uid: 102413 topic_id: 26102 reply_id: 107116[/import]

#16

I am on windows 7… and also have a mac… and this code seems to just corrupt my database file…

I have been looking for alternate methods, but each one has been putting me into a different road block.

the current one i am using is i TAR the good database in with the app. then when i first launch my app i untar the database file into the system.documentsdirectory… at that point everything seems to be good.

except when i power off the device… all changes are lost. its like i am getting a read only version of the database.
[import]uid: 88147 topic_id: 26102 reply_id: 110578[/import]

#17

i use this lua code and it works like a champ every time.

[code]
function doesDBFileExists(sFileAndPath)

local bResults = false

– io.open opens a file at filePath. returns nil if no file found
local file = io.open( sFileAndPath, “r” )

if file then – YES FILE OPENed -->
io.close( file )
bResults = true
else – FILE - NOT - OPENed -->
bResults = false
end

return bResults
end

if doesDBFileExists(dbFileNameAndPath ) == true then
print (“doesDBFileExists ->> true”)
else
print (“doesDBFileExists ->> false”)
end

[/code] [import]uid: 11860 topic_id: 26102 reply_id: 114246[/import]

#18

Hi! I have the same problem now, when I want to copy my SqLite database from the Resource directory to the Documents directory, it goes from 1,400 KB to 24 KB and corrupt.
Have you already found a way to fix this problem?

Thanks a lot! [import]uid: 132375 topic_id: 26102 reply_id: 115152[/import]

#19

Hey Richie,
Ive actually settled for just creating a new database if one does not exist. its only two tables so its only really two lines of sql to build it.

that issue your having is exactly the same as what I had before switching to just “building” one.

Give doubleslashdesign’s code a crack and see if you have any luck with that.

Let us know though! If ever have to create something big with sql, I would rather be able to copy it across! [import]uid: 102413 topic_id: 26102 reply_id: 116056[/import]

#20

Hi Aidan, just now I’ve solved the problem with the help of the “Corona Lab” staff. They’ve helped me a lot of times.
The solution is quite simple, you have to open and write the file in “binary” mode:

local readfh = io.open( readfilePath, “rb” );
local writefh = io.open( writefilePath, “wb” );

Hope this will help and simplify your code,

Regards!

[import]uid: 132375 topic_id: 26102 reply_id: 116067[/import]