Rebuild the database?

[RonS]

Is there any utility out there to rebuild the database?

I lost a week or two of database entries due to a disk crash.  The files are still in all the directories, only the database is out of date (and now out of sync with the files in the directories).

Anyway to run a utility to walk all the user directories and build a database entry for each?  Even if they're all put into one big album, I can then re classify them.

Thanks.

[Joachim Müller]

no such utility available. Batch-add is not an option for user uploads either. I'm sorry, but I guess this incident will only be there to teach you the importance of frequent db backups.

[RonS]

The backups for everything but the DB worked; the backups for the db partition were corrupted.  No need to be snide.

I guess I'll write it for you then, and maybe I'll even give you and your community such a basic recovery tool.  Of course, I'll just write the bare bones, I'll leave it up to you guyz to make it distributable.

Now, one pinch of batch upload utility, one pinch of shell script.... there, that ought to do it for a recipe.  Shouldn't be too hard.  Let's try.

[RonS]

Well, it took about 3 hours, maybe 4 to figure out the code and an approach, and to execute it.  The execution was about an hour of that time for 226 users and the 208 pictures that were uploaded in the past couple of weeks.  That wasn't an hour of machine time, it was a semi-manual process that took all the time.

My installation is merged with phpBB, so this code is for that type of an installation..

The approach was to modify the batch upload script to allow looking into the user albums, and to then treat them as batch uploads, except that addpicture was modified to decipher the directory and get the userid, then use that userid to both set owner_userid, and to get phpbb_users.username and set cpg_pictures.owner_username with that name.

Here's the modifications; remember this was quick and dirty and for use with CPG integrated with phpBB.  Get the usernames from other sources, including cpg if needed.

Searchnew is modified to display user albums, and to only show files that are not already in the database.

copy searchnew.php to searchnew_rebuild.php

In searchnew_rebuild.php, aroundline 143
FIND:

Code Select Expand

        $checked = isset($expic_array[$picfile]) ? '' : 'checked';
ADD, AFTER:

Code Select Expand

if ($checked === 'checked')  //Return a row only for checked pics (ones that aren't already in the db, for example.
{
around line 161
FIND:

Code Select Expand

EOT;ADD, AFTER:

Code Select Expand

}
else
{
        return;
}
around line 228
FIND:

Code Select Expand

    while ($file = readdir($dir)) {
REPLACE:

Code Select Expand

    while ($rs_file = readdir($dir)) { $rs_filearray[] = $rs_file; }
    natcasesort($rs_filearray);  //Display the folders in some sort of alphabetical order
    foreach ($rs_filearray as $file) {
around line 233
FIND:

Code Select Expand

        if (is_dir($CONFIG['fullpath'] . $folder . $file) && substr($file,0,1) != "." && strpos($file,"'") == FALSE && $file != substr($CONFIG['userpics'],0,strlen($CONFIG['userpics'])-1)  && $file != "edit" ) {
REPLACE:

Code Select Expand

        if (is_dir($CONFIG['fullpath'] . $folder . $file) && substr($file,0,1) != "." && strpos($file,"'") == FALSE && $file != "edit" ) {

Save and close....


In file include/picmgmt.inc.php
around line 86 find:

Code Select Expand

$username= addslashes(USER_NAME);
ADD, AFTER

Code Select Expand

//
// This code was used to rebuild the database from the user's picture directories, after a disk crash lost some of the db entries.
// This code works with searchnew_rebuild.php, which was simply modified to allow looking into user albums and to not display
// pics that are already in the db.  Go look in that file. :D
//
// YOU MUST REMOVE (OR COMMENT OUT) THIS CODE AFTER YOU ARE DONE REBUILDING YOUR DATABASE!!! THIS IS NOT PRODUCTION CODE!!!
//
$user_id= substr($filepath,10,4)+0;
$sql = "select username from phpbb_users where user_id=$user_id";
$result = db_query($sql);
$rs_foo = mysql_fetch_array($result);
$username = addslashes($rs_foo[0]);
//

Save and close...

Point your browser at your new searchnew_rebuild.php, and walk through all of your user's directories.

If I had a gazillion users, I probably would have modified the code further to walk the directories and build one giant list of the files, or something like that.  But I only have a couple of hundred users, and I was expecting only a couple of hundred uploads, which is what I got.  As you can see I didn't perform even basic error checking, if you are going to use this code, you should probably do that.n  One thing you'll probably want to check for is the existence of the user_id in phpbb (if integrated), especially if you've lost database entries like user registrations.  Otherwise you'll be assigning files to a not-yet existent user; someone new will join your forum and have someone else's files.  Perhaps it is best to just let them sit in the user_id based directory, or better yet, manually move them out to some sort of batch directory, or take ownership of them yourself and put them into your own directory.

It seems to have worked fine for me, YMMV, no warranties expressed or implied.

I dumped all of the files into one public album.  My mods and I will sort the pics into more appropriate albums as time permits. My version of CPG doesn't allow the owner of the baord to move a file into a private album.  If users want to claim their files back into private albums, they can do so, I suppose.  I don't have private albums allowed on my site for a few reasons, one of which is that lack of admin control of private albums.

Good luck!

[Joachim Müller]

In file include/picmgmt.inc.php
around line 86 find:

Code Select Expand

ADD, AFTER

find what?

[RonS]

find what?

post above updated with the following:

In file include/picmgmt.inc.php
around line 86 find:

Code Select Expand

$username= addslashes(USER_NAME);