[Howto] Fix wrong data in the proftpd accounting

[aseques]

There's a problem (although I couldn't find a ticket for it) about proftpd and quotas.
Currently whenever a new domain is created ispcp adds ads a new record in quotalimits, the field bytes_in_avail is set to the maximum space available for that domain.
Afterwards, everytime the user uploads or deletes something the field bytes_in_avail in the table quotatallies gets increased or decreased depending on the action.

The problem is that sometimes different events can trigger changes not accounted by the proftpd, like the admin removing infected files or some random actions with the ftp client.

This script will prepare the queries to update the values for all you domains, after that you've the run the queries yourself.
You also need to download the diskuse tool from this page: http://www.castaglia.org/proftpd/contrib/diskuse

Code:

#!/bin/sh
#Shell script to dump the queries needed to update the quotatallies table
# to the proper values
#We are expecting diskuse to be in the same path as we are
#The output can be redirected to a file using the > filename syntax and then
# injected into mysql in the form "mysql -u root -p ispcp < filename

DIR=`dirname $0`

if ! [ -e  $DIR/diskuse ] ; then
        echo "The diskuse script cannot be found, you can get it at:"
        echo " http://www.castaglia.org/proftpd/contrib/diskuse"
        exit 1
fi

#TODO, we should count the space the root of the folder but excluding some dirs
#  (logs,  phptmp,  statistics ..)
#TODO, the queries to delete orphaned entries could be created too, altough
# ispcp should take care of those
#Loop through the domains
for i in `ls -1 /var/www/virtual/` ; do
        GROUP=`ls -l /var/www/virtual/ | grep "\b$i\$" | tr -s ' ' | cut -d' ' -f3`
        DISK=`perl $DIR/diskuse --group $GROUP /var/www/virtual/$i/htdocs/  | cut -d' ' -f2`
        case $DISK in
                ''|*[!0-9]*)
                        echo "#Calculation error for $i";;
                *)
                        echo "REPLACE INTO ispcp.quotatallies (name,quota_type,bytes_in_used) VALUES ('$i','group',$DISK);";;
        esac
done

Any improvements will be gladly accepted, also a rewrite would be nice, this script is quite hackish..

[aseques]

Added some comments to the code after finding a couple of issues