Caused by a harddisk crash a couple of years ago we hade hundreds of Download-Database entries and since the UpDownload Module or even the core Module Downloads do not offer easy on-the-fly functions to validate entries AND removing corrupted at once i wrote a little perl tool which does exactly this.
It has two options: sync and check.
check does check ALL entries/files in the UpDownload Module and shows the result (amount of corrupt files, amount of downloadbytes which does not exist, enlistment of all missing files). it does not change ANYTHING on the filesystem or in the database itself.
sync does the same as check but its completly quiet AND does remove download entries where the files do not exist in the filesystem.
NOTE: this program does not yet check remote URLs for existing files, but will in the future. since this program does not spam stdout its nice to use for cron. ;)
#!/usr/bin/perl
# File: $Id: pn-uldl-validator,v 1.0 2006/02/28 23:21:21 simonlange Exp $
# ----------------------------------------------------------------------
# PostNuke UpDownload Module Validator
# Copyright (C) 2006 by Simon Lange
# http://www.thecenter.at
# ----------------------------------------------------------------------
# LICENSE
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License (GPL)
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# To read the license please visit http://www.gnu.org/copyleft/gpl.html
# ----------------------------------------------------------------------
# Original Author of file: Simon Lange
# ----------------------------------------------------------------------
use DBI;
#debugmode
$debug = 0; # 0 - disabled , 1 - enabled (if enabled no changes to the db or filesystem will be applied)
#database settings
$username = "username"; # enter the db username who has access to your postnuke installation
$password = "password"; # enter the db username's password
$database = "database"; # enter the db name of the postnuke installation
$dbserver = "localhost"; # enter the hostname of the database server which hosts the postnuke installation
$pnprefix = "pn_"; # enter the postnuke database prefix of the postnuke installation
$dbh = DBI->connect("DBI:mysql:$database:$dbserver","$username","$password") || showerror ("An error has accured when connecting to the database.");
# URL and path of the site ( NO TAILING SLASH !!! )
$primaryURL = "http://www.foo.bar"; # the PRIMARY URL of the postnuke site which is used in the download urls
$primaryPATH = "/path/to/your/webspace/documentroot"; # the filesystempath to your documentroot of your postnuke site
# Postnuke UpDownload Module tables
$uldlCategories = $pnprefix."downloads_categories";
$uldlDownloads = $pnprefix."downloads_downloads";
$uldlEditorials = $pnprefix."downloads_editorials";
$uldlModRequest = $pnprefix."downloads_modrequest";
$uldlNewDownload = $pnprefix."downloads_newdownload";
$uldlSubCategories = $pnprefix."downloads_subcategories";
$uldlVoteData = $pnprefix."downloads_votedata";
###
### DO NOT EDIT BELOW THIS LINE
### ===========================
if (@ARGV < 1 || $ARGV[0] eq "--help" || $ARGV[0] eq "-h") {
print [20] "[code]
#!/usr/bin/perl
# File: $Id: pn-uldl-validator,v 1.0 2006/02/28 23:21:21 simonlange Exp $
# ----------------------------------------------------------------------
# PostNuke UpDownload Module Validator
# Copyright (C) 2006 by Simon Lange
# http://www.thecenter.at
# ----------------------------------------------------------------------
# LICENSE
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License (GPL)
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# To read the license please visit http://www.gnu.org/copyleft/gpl.html
# ----------------------------------------------------------------------
# Original Author of file: Simon Lange
# ----------------------------------------------------------------------
use DBI;
#debugmode
$debug = 0; # 0 - disabled , 1 - enabled (if enabled no changes to the db or filesystem will be applied)
#database settings
$username = "username"; # enter the db username who has access to your postnuke installation
$password = "password"; # enter the db username's password
$database = "database"; # enter the db name of the postnuke installation
$dbserver = "localhost"; # enter the hostname of the database server which hosts the postnuke installation
$pnprefix = "pn_"; # enter the postnuke database prefix of the postnuke installation
$dbh = DBI->connect("DBI:mysql:$database:$dbserver","$username","$password") || showerror ("An error has accured when connecting to the database.");
# URL and path of the site ( NO TAILING SLASH !!! )
$primaryURL = "http://www.foo.bar"; # the PRIMARY URL of the postnuke site which is used in the download urls
$primaryPATH = "/path/to/your/webspace/documentroot"; # the filesystempath to your documentroot of your postnuke site
# Postnuke UpDownload Module tables
$uldlCategories = $pnprefix."downloads_categories";
$uldlDownloads = $pnprefix."downloads_downloads";
$uldlEditorials = $pnprefix."downloads_editorials";
$uldlModRequest = $pnprefix."downloads_modrequest";
$uldlNewDownload = $pnprefix."downloads_newdownload";
$uldlSubCategories = $pnprefix."downloads_subcategories";
$uldlVoteData = $pnprefix."downloads_votedata";
###
### DO NOT EDIT BELOW THIS LINE
### ===========================
if (@ARGV < 1 || $ARGV[0] eq "--help" || $ARGV[0] eq "-h") {
print " DIZKUSCODEREPLACEMENT0 [command]\tVersion 1.0\n";
print "[command] could be one of the following:\n";
print "\tcheck\tCheck Files but dont delete anything\n";
print "\tsync\tsynchronize local downloads with local filebase\n\n";
die "[c]opyright 2006 Simon Lange www.thecenter.at www.rack-it.de\nThis program is under the GPL-License\n";
}
if($ARGV[0] eq "sync") {
synchronize();
exit 0;
}
if($ARGV[0] eq "check") {
$debug=1;
synchronize();
exit 0;
}
exit 0;
sub showerror{
my $ec = shift;
die $ec."\n";
}
sub synchronize {
my $sql = qq{SELECT pn_lid,pn_url,pn_filesize FROM $uldlDownloads};
my $sth = $dbh->prepare($sql);
my @corrupted = ();
my $ci = -1;
my $cfs= 0;
$sth->execute();
while(@result=$sth->fetchrow_array){
$result[0]=~s/\n//g;
$result[1]=~s/\n//g;
if($result[1]=~/$primaryURL/i || $result[1]=~/^\//i){
$result[1]=~s/$primaryURL//gi;
$resultfile=$primaryPATH.$result[1];
$flag=0;
$flag=1 if -e $resultfile;
if($flag == 0) {
$cfs=$cfs+$result[2];
if($debug){print "!!($result[0]) ($result[2]bytes) does NOT exist. ($result[1])\n";}
else{unlink $resultfile;}
$ci++;$corrupted[$ci]=$result[0];
}
}
}
$sth->finish();
if(!$debug){
foreach $pnlid(@corrupted){
$dbh->do(qq~DELETE FROM $uldlDownloads WHERE pn_lid=$pnlid~);
$dbh->do(qq~DELETE FROM $uldlModRequest WHERE pn_lid=$pnlid~);
}
} else {
$ci++;
print "Found $ci corrupted UpDownload Module entries with a final volume of $cfs bytes.\n";
}
}
[/code] [command]\tVersion 1.0\n";
print [21] "[command] could be one of the following:\n";
print [22] "\tcheck\tCheck Files but dont delete anything\n";
print [23] "\tsync\tsynchronize local downloads with local filebase\n\n";
die [24] "[c]opyright 2006 Simon Lange www.thecenter.at www.rack-it.de\nThis program is under the GPL-License\n";
}
if($ARGV[0] eq "sync") {
synchronize();
exit [25] 0;
}
if($ARGV[0] eq "check") {
$debug=1;
synchronize();
exit [26] 0;
}
exit [27] 0;
sub showerror{
my $ec = shift;
die [28] $ec."\n";
}
sub synchronize {
my $sql = qq{SELECT pn_lid,pn_url,pn_filesize FROM $uldlDownloads};
my $sth = $dbh->prepare($sql);
my @corrupted = ();
my $ci = -1;
my $cfs= 0;
$sth->execute();
while(@result=$sth->fetchrow_array){
$result[0]=~s/\n//g;
$result[1]=~s/\n//g;
if($result[1]=~/$primaryURL/i || $result[1]=~/^\//i){
$result[1]=~s/$primaryURL//gi;
$resultfile=$primaryPATH.$result[1];
$flag=0;
$flag=1 if -e $resultfile;
if($flag == 0) {
$cfs=$cfs+$result[2];
if($debug){print [29] "!!($result[0]) ($result[2]bytes) does NOT exist. ($result[1])\n";}
else{unlink [30] $resultfile;}
$ci++;$corrupted[$ci]=$result[0];
}
}
}
$sth->finish();
if(!$debug){
foreach $pnlid(@corrupted){
$dbh->do(qq~DELETE FROM $uldlDownloads WHERE pn_lid=$pnlid~);
$dbh->do(qq~DELETE FROM $uldlModRequest WHERE pn_lid=$pnlid~);
}
} else {
$ci++;
print [31] "Found $ci corrupted UpDownload Module entries with a final volume of $cfs bytes.\n";
}
}
Checked with
PostNuke .762 and UpDownload 2.3
[17]
[32]