Any ideas on how to restore a SQlite database?

[dougiefresh]

I'm writing a mod dealing with restoring databases within the admin panel.  I'm confident that my code works for MySQL database, but restoring the database on SQlite database systems is a concern.  The SMF code for backing up the database is to literally read the database file and dump it via echo commands.....

I found this topic, which indicates that SQlite database backups aren't compatible with MySQL servers.... seems that the backup isn't a text file like MySQL....

I found a website on SQlite backup and Restore, which gave me a few ideas, but since I don't have access to the shell in the mod, this method is unsafe to assume that it will run on other systems....  even if I can figure out how to execute a command using the shell, which I'm not fond of doing if I can avoid it....

I looked through the Subs-Db-sqlite.php and found that it opens a connection to the database....  If I close the database connection in SMF, it should be possible to replace the database file, shouldn't it?  I could then reopen the database connection by calling the database initalization function once the database file has been replaced.....  Does this sound like a reasonable approach?

Any assistance and/or insight would be appreciated....

[dougiefresh]

I started looking at the phpBB board code for database backup and restore and found that phpBB executes queries to backup and restore the information in its database.  So I modified my mod to take the phpBB approach to the problem....  However, it still doesn't eliminate how to deal with the backups that the original SMF code makes....

Any insights and/or assistance would be appreciated...  Thanks!

[emanuele]

Well, for SQLite a dirty trick would be to just upload the file in the proper position (provided that the forum is not set up in a very safe way) and then swap the db name.

* emanuele likes tricks.

Though it's a very dirty solution.

The only alternative way would be to rewrite the smf_db_get_backup function for SQLite mimicking the MySQL or the PostgreSQL one.

Random thought: if you are uploading a backup with the sessions table in it, you may end up logged out at some point (not sure though).

[rooney123]

Two steps

(1) allow the user to back up the database by packaging it up, probably in a zip file and then attach it to an email and send it to themselves

(2) They can get the database back on the device by using iTunes File Sharing basically make sure you set the property on the plist, "UIFileSharingEnabled"

[dougiefresh]

@emanuele: Actually, neither the MySQL and PortgreSQL files have that function, which is why the original SMF database code has to execute all sorts of queries in order to get the information to make a backup....

I found the PHP function "flock", which can get me an "exclusive lock" on the database file.  If I lock it first, then replace the contents with the backup, and release the lock, would that work for the purpose of restoring?  Thanks!

I could copy the database file first in the event that the backup file either gets or is corrupted. 

EDIT: PS... I don't care about the sessions table.....    My mod sets maintenance mode, then redirects the admin performing the restore to the maintenance page, where the restore "actually" happens for the adminstrator...  Once the restore is complete, he/she is redirected back to the database page in Forum Maintanace....

@rooney123: I don't have a problem GETTING the backup....  The problem is RESTORING the database safely....  And why would I use iTunes to deal with this?!?  I'm using a WEB SERVER, not a device to host the forum  and while I am assuming most everybody that runs SMF uses a WEB SERVER of some kind, I have no idea how iTunes would factor into this....

[emanuele]

@emanuele: Actually, neither the MySQL and PortgreSQL files have that function, which is why the original SMF database code has to execute all sorts of queries in order to get the information to make a backup....

mmm...not sure which part you are referring to, but if I got it right, my "trick" is valid only for SQLite, not for any other dbms.
I posted it because you asked specifically about SQLite, so I imagined you already solved the issue with MySQL and was addressing SQLite.

[dougiefresh]

@emanuele: Actually, neither the MySQL and PortgreSQL files have that function, which is why the original SMF database code has to execute all sorts of queries in order to get the information to make a backup....

mmm...not sure which part you are referring to, but if I got it right, my "trick" is valid only for SQLite, not for any other dbms.
I posted it because you asked specifically about SQLite, so I imagined you already solved the issue with MySQL and was addressing SQLite.

You are correct on both counts....  The trick is valid only for SQLite and I've rewritten the code for the MySQL and postgreSQL systems....

The only alternative way would be to rewrite the smf_db_get_backup function for SQLite mimicking the MySQL or the PostgreSQL one.

DumpDatabase.php uses this code:

Code Select Expand

// If this database is flat file and has a handler function pass it to that.
if (!empty($smcFunc['db_get_backup']))
{
$smcFunc['db_get_backup']();
exit;
}
In DbExtra-mysql.php and DbExtra-postgresql.php in the Sources folder, there is no smf_db_get_backup function in them, nor is it referenced in the $smcFunc array.  So DumpDatabase.php has to make a bunch of queries in order to create the SQL file....

I have a feeling that whoever wrote the original database backup code wanted a quick and dirty way to backup the database....  This is about as quick and dirty as one can get....

I think I'm just gonna implement the trick you suggested....

EDIT: Where exactly is the database file stored in relation to the forum?  Is the $db_name variable an absolute path to the database file?

[emanuele]

I fixed the database export about a year ago and don't remember the details... (oh, and while I remember, have a look at the changes in the code in 2.1, they are quite important to be able to always obtain a functional database)

If smf_db_get_backup exists only for SQLite, then you can just "fix the trick" and remove that function from SQLite when you install your mod. That way SQLite will use the dumpdatabase code and do a "normal" backup (I'm not sure it will be 100% compatible with SQLite, never tried).
But I think it's not worth for SQLite in particular. Why? Simply because when you backup the database you may (very likely) get a file that is larger than post_max_size and upload_max_filesize (or alternatively the connection could timeout before the file is uploaded), leading to the impossibility to restore your backup from the admin panel.

You should create a way to split the file before the upload (I'd say during the backup) so that when you start the restoring you are sure the upload goes always smoothly.

SQLite is a very special case, and I wouldn't do anything to restore the database because it is already quite easy: just upload the file via ftp and you are done.

[dougiefresh]

Well, for SQLite a dirty trick would be to just upload the file in the proper position (provided that the forum is not set up in a very safe way) and then swap the db name.

Do you think this code might work to replace the SQlite database from within SMF using PHP?

Code Select Expand


// Replace the database file with the backup file:
$db_file = substr($db_name, -3) === '.db' ? $db_name : $db_name . '.db';
while (rename($db_file, $db_file . '.bak') === false)
{
sleep(1);
}
rename($filename, $db_file);

(This code waits until renaming is successful....) Thanks in advance!

EDIT: Do I need to backup the journal file(s) as well?

EDIT2: What do you mean "provided that the forum is not set up in a very safe way"?

EDIT3: I'm concerned because I don't know if the database connection needs to be closed before replacement of the file....  If it needs to be closed, how can I reopen it to show the template?  (Or does it need to be reopened?) 

[Arantor]

Let me get that code straight in my head
* Figure out what the DB's name is by figuring out if it ends in .db or not and putting that on the end if not (assumes SMF doesn't... wouldn't rely on that)
* Try to rename the current DB to a .bak file, and all the time this fails, sit in a loop
* Rename the restored file to the current file

Well... there's no need to sit in a loop. rename() is not asynchronous, it either returns true because it did it or false because it couldn't. So if it couldn't, e.g. destination file already exists and is read only or directory is read only, you're going to be sat in that loop until time limit expires.

Seriously, just do the first rename and check whether it was successful before doing the second. If the first failed, alert the user and don't proceed, if the second failed alert the user anyway.

[dougiefresh]

* Figure out what the DB's name is by figuring out if it ends in .db or not and putting that on the end if not (assumes SMF doesn't... wouldn't rely on that)

I borrowed that line of code from DbExtra-sqlite.php, from the smf_db_get_backup function.   I assumed that the authors of SMF know how to backup a SQlite database, so I figured that the assumption that it works would.... well, work for me 

Well... there's no need to sit in a loop. rename() is not asynchronous, it either returns true because it did it or false because it couldn't. So if it couldn't, e.g. destination file already exists and is read only or directory is read only, you're going to be sat in that loop until time limit expires.

Seriously, just do the first rename and check whether it was successful before doing the second. If the first failed, alert the user and don't proceed, if the second failed alert the user anyway.

Okay, cool.....  I did that...  Wish I could test it on a local machine, though....

EDIT: Do I need to backup the journal file(s) as well?

EDIT2: What do you mean "provided that the forum is not set up in a very safe way"?

EDIT3: I'm concerned because I don't know if the database connection needs to be closed before replacement of the file....  If it needs to be closed, how can I reopen it to show the template?  (Or does it need to be reopened?) 

Dang, you're fast to reply    Wanna say in advance I appreciate your assistance.....  I evidently edited my post while you were writing your response.....  Can you shine any light on what emanuele was referring to by "not set up in a very safe way"?  Answering any/all of the above questions would be very much appreciated, if you could.  Thank you.

[Arantor]

Can you shine any light on what emanuele was referring to by "not set up in a very safe way"?

The general premise of using a file database is that you try to keep it outside of the web root, otherwise people could just download it and be done with it... there are other factors around the safety of files in general where being writable by Apache/PHP are concerned but that's the big one.

Honestly, I have no idea why people would even use SQLite for SMF, it will bottleneck on I/O far before the forum ever gets big enough that uploading will ever be a real problem.

In general, restoring a database from a backup should NOT be performed inside SMF. It really shouldn't, regardless of DB system. It would be quite feasible, though marginally unlikely, for a restoration of a backup to break midway through if, say, the user performing the restoration is a new account and didn't exist in the previous backup since their account (and privileges) will disappear once the members table is restored. This has actually happened in the past.

Yeah, the whole thing around the filename is a bit weird because you can't rely on it having the .db extension though it really should.

Yes, the connection should be closed before restoring, however the template does not actually require the database by definition - all the template stuff happens after main execution has finished and by definition that should include all database stuff. There is one exception of course: session data.

This is the other reason you don't do a restoration from the admin panel, you essentially have to ignore sessions or pray you don't screw it up entirely in the process.

[emanuele]

Can you shine any light on what emanuele was referring to by "not set up in a very safe way"?  Answering any/all of the above questions would be very much appreciated, if you could.  Thank you.

At 2:something in the morning I'm usually deep asleep, sorry.

[dougiefresh]

The general premise of using a file database is that you try to keep it outside of the web root, otherwise people could just download it and be done with it... there are other factors around the safety of files in general where being writable by Apache/PHP are concerned but that's the big one.

OH!!!  I've made some changes to my mod to allow admin to change the download location, so that the backups aren't as vulnerable to theft like that...

In general, restoring a database from a backup should NOT be performed inside SMF. It really shouldn't, regardless of DB system. It would be quite feasible, though marginally unlikely, for a restoration of a backup to break midway through if, say, the user performing the restoration is a new account and didn't exist in the previous backup since their account (and privileges) will disappear once the members table is restored. This has actually happened in the past.

This is the other reason you don't do a restoration from the admin panel, you essentially have to ignore sessions or pray you don't screw it up entirely in the process.

Didn't think of either of those....  I made more changes to circumvent this issue you brought up.  My idea is that the restore is STARTED in the admin center, but completed by using a different action (ie restore_db).  That way, if the user "disappears" from the members table, the restore will still continue....  All variables necessary are passed by way of post variables, so that the restore can continue....

Yeah, the whole thing around the filename is a bit weird because you can't rely on it having the .db extension though it really should.

Agreed....

Yes, the connection should be closed before restoring, however the template does not actually require the database by definition - all the template stuff happens after main execution has finished and by definition that should include all database stuff. There is one exception of course: session data.

I looked further into the SQlite database functions for SMF....  The original SMF code doesn't close the connection before sending a copy of the file to the user...  Wonder if this has ever resulted in a corrupt download? 

Anyways, thanks for your answers, as well as your time.  Any additional thoughts, I'm all ears.... (despite being deaf in one ear....  )


@emanuele: No problems.... Thanks for your input!

[Arantor]

It's unlikely that it resulted in a corrupt download, SQLite is pretty good about not getting corrupted like that, primarily because it tries to be good about f'locking.

I should add, SQLite only got added to SMF back in the days when SQLite was added to PHP due to internal issues with PHP (like having to replace the MySQL connector due to licensing issues), and almost no-one uses it. Partly because it's not well tested, partly because it's the older version of SQLite (2.1 has SQLite 3.0 stuff in it) and partly because it bottlenecks on performance far far sooner than anything else.

I almost wouldn't worry about it personally.

[dougiefresh]

Thanks for the wealth of information.  It's been appreciated....

Off-Topic: Tested the changes to my mod concerning "disappearing members"...  It works!!  YAY    User is logged out, but restore keeps going....

EDIT: I've done all the modification to my mod that I'm going to do with respect to SQLite....  at least for now.  I'll worry about it more if/when someone complains about SQLite with my mod....