Jump to content

[TOPIC: topicViewTemplate]
[GLOBAL: userSmallPhoto]
Photo

How to backup a SQLITE database?
Started by MauMau Nov 09 2018 02:41 AM

- - - - -
3 replies to this topic
sqlite backup dump copy permission denied
[TOPIC CONTROLS]
This topic has been archived. This means that you cannot reply to this topic.
[/TOPIC CONTROLS]
[modOptionsDropdown]
[/modOptionsDropdown]
[reputationFilter]
[TOPIC: post.html]
#1

MauMau

[GLOBAL: userInfoPane.html]
MauMau
  • Contributor

  • 111 posts
  • Corona SDK

I am working with a SQLITE database from within my app. The database file itself is stored as "data.db" in the documents folder.

 

Since users should be able to backup and restore this data I need to make a copy of the database file.

 

How could this be done at best (creating a copy of the database file in the documents directory)?

 

Currently I am trying to use io.open / io.write to read in the database file in binary mode and writing it to a destination file ("backup.db") - but the Simulator does not allow this and gives a "permission denied" warning as soon as I try to io.open the database file for binary reading.

 

Of course I close the database connection before using the DB:close() method, but this does no make a difference. I also used a time delay between closing the database and copying the file but this also gives a "permission denied".

 

It seems that the database file is locked as soon as it is accessed the first time from within the app and not released anymore until the app is completely terminated again (f.e. Simulator closed) :blink:

 

Does SQLITE offer any built-in methods to clone a database file? 

 

Or are there any other ways to do so?



[TOPIC: post.html]
#2

nick_sherman

[GLOBAL: userInfoPane.html]
nick_sherman
  • Corona Geek

  • 1,747 posts
  • Corona SDK

I've not had a problem doing this, I backup the saved game file every time the user advances a day in case of a crash during processing. This is my code:

 

 
database:close()
database = nil
 
local pathSource = system.pathForFile("database.db", system.DocumentsDirectory)
local sourceFile, error = io.open(pathSource, "rb")
 
local txt = sourceFile:read("*a")
 
local pathDestination = system.PathForFile("backup.db", system.DocumentsDirectory)
local destinationFile , error = io.open(pathDestination, "wb")
 
destinationFile:write(txt)
io.close(destinationFile)
io.close(sourceFile)
 



[TOPIC: post.html]
#3

MauMau

[GLOBAL: userInfoPane.html]
MauMau
  • Contributor

  • 111 posts
  • Corona SDK

Above code also results in a "permission denied" error :(


 

Are you using the simulator on Win or Mac? On Windows, all project files keep being locked until the sim is closed again. That's so annoying - same with images, so can't replace any graphics while the sim is running.  I reported this annoying behaviour to Corona Labs years before but obviously this still hasn't been fixed yet :mellow:



[TOPIC: post.html]
#4

nick_sherman

[GLOBAL: userInfoPane.html]
nick_sherman
  • Corona Geek

  • 1,747 posts
  • Corona SDK

I do develop on a mac, but have Windows running on a VM and I'm sure I got around the file locking problem. I'll look at what else I'm doing - perhaps deleting the backup file first?


[topic_controls]
[/topic_controls]