airtime_update_filesize.pl

#!/usr/bin/perl


use strict;
use warnings;
use diagnostics;
use DBI;

my $dbh = DBI->connect("dbi:Pg:dbname=airtime;host=localhost;port=5432;", "airtime", "airtime");
my $sQuery = "SELECT cc_files.id as id_cc_files,cc_music_dirs.directory as dirname,filepath FROM cc_files,cc_music_dirs WHERE cc_files.directory=cc_music_dirs.id";
my $sth = $dbh->prepare($sQuery);
unless ($sth->execute()) {
        print "SQL Error : " . $DBI::errstr . " Query : " . $sQuery . "\n";
}
else {
        while (my $ref = $sth->fetchrow_hashref()) {
                my $id = $ref->{'id_cc_files'};
                my $filepath = $ref->{'filepath'};
                my $dirname = $ref->{'dirname'};

                my ($dev,$ino,$mode,$nlink,$uid,$gid,$rdev,$size,$atime,$mtime,$ctime,$blksize,$blocks) = stat("$dirname/$filepath");
                print "$dirname/$filepath : $size bytes\n";
                $sQuery = "UPDATE cc_files SET filesize=? WHERE cc_files.id=?";
                my $sth2 = $dbh->prepare($sQuery);
                unless ($sth2->execute($size,$id)) {
                        print "SQL Error : " . $DBI::errstr . " Query : " . $sQuery . "\n";
                }
                else {
                        print "Updated row id : $id filesize=$size\n";
                }
        }
}