#!/usr/bin/perl -w
#
# cpg2zenphoto - Max Baker <max@warped.org>
#   2/22/2009
#
# Copies META data from Coppermine photo gallery (CPG)
#   to ZenPhoto photo gallery (ZP)
#
# Currently supports :
#   - Title
#   - Caption/Description
#   - Votes
#   - Hits
#
# USAGE:
#   1. Keep your coppermine database in place
#   2. Move/Copy all your files over to ZenPhoto and get it all setup
#   3. Edit the paths below and run this script
#
# REQUIRES:
#   1. CPG and ZP be installed and setup
#   2. DBI and DBD::mysql modules be installed for Perl
#
# CAVEATS/BUGS:
#   1. If two images have the same filename in different albums, then 
#      one will be wrong.
#   2. Only a few things bits of meta data are supported right now
#
# LICENSE: You can freely use, modify and redistribute this code as long as it 
#          stays in the public domain / open source.

$VERSION = 0.1;
use DBI;

$CPG_Path   = '/home/warped/public_html/pix';
$ZP_Path    = '/home/warped/public_html/photos';
$CPG_Table_Pix = 'cpg_pictures';
$ZP_Table_Pix  = 'zp_images';

# Translate CPG db columns to ZP columns
%COLS = ( 
    'title'     => 'title',
    'caption'   => 'desc',
    'votes'     => 'total_votes',
    'hits'      => 'hitcounter',
);


#
# You shouldn't have to change anything from here out, should be 
# parsed from your config files.  Just in case you can set it manually
# below and comment out the "grab_settings()" line.
#
$CPG_Config = 'include/config.inc.php';
$ZP_Config  = 'zp-core/zp-config.php';

# manual/default settings
$CPG_Host = 'localhost';
$CPG_User = 'cpg';
$CPG_Pass = 'password';
$CPG_DB   = 'cpg';
$ZP_Host  = 'localhost';
$ZP_User  = 'zenphoto';
$ZP_Pass  = 'password';
$ZP_DB    = 'zenphoto';
# Grab db settings from config files
grab_settings();
print_settings();

# connect to each database
$DB_CPG = db_handle($CPG_DB,$CPG_Host,$CPG_User,$CPG_Pass);
$DB_ZP  = db_handle($ZP_DB,$ZP_Host,$ZP_User,$ZP_Pass);

my $sth_cpg = $DB_CPG->prepare("SELECT * from $CPG_Table_Pix");
$sth_cpg->execute;

#
# CopperMine :
# Table : cpg_pictures
#   Columns : filename, title, caption, votes, hits, keywords
#
# ZenPhoto :
# Table : zp_images
#   Columns : filename, title, desc, total_votes, hitcounter
# Table : zp_tags
#
#   Columns : id, name
# Loop over every image in CPG's table
# Update every image in ZP that has the same filename.
while (my $row_cpg = $sth_cpg->fetchrow_hashref()) {
    my $filename = $row_cpg->{'filename'};
    next unless defined $filename and $filename !~ /^\s*$/;

    # Look for matching rows in ZP
    #my $sth_zp = $DB_ZP->prepare( sprintf("SELECT * from $ZP_Table_Pix where filename = %s",
    #    $DB_ZP->quote($filename)));
    #$sth_zp->execute;
    #while (my $row_zp = $sth_zp->fetchrow_hashref()) {
    #    print "$row_zp->{id} $row_zp->{filename}\n";
    #}
    #$sth_zp->finish;

    # Update matching rows in ZP
    foreach my $col (keys %COLS) {
        my $target_col = $COLS{$col};
        my $val = $row_cpg->{$col};
        next unless defined $val and $val !~ /^\s*$/;
        # why does zp use desc, a reserved word, as a column name? duh.
        my $sql = sprintf("UPDATE $ZP_Table_Pix SET $ZP_Table_Pix.$target_col = %s WHERE filename = %s",
                $DB_ZP->quote($val), $DB_ZP->quote($filename));
        print "$sql\n";
        $DB_ZP->do($sql);
    } 
     
}

$sth_cpg->finish;

exit;


sub grab_settings {
    open ( C, "< $CPG_Path/$CPG_Config") or die "Can't open $CPG_Path/$CPG_Config.  $!\n";
    while (<C>) {
        if (/CONFIG\[\s*'dbserver'\s*\]\s*=\s*'([^']+)'/) {
            $CPG_Host = $1;
        }
        if (/CONFIG\[\s*'dbuser'\s*\]\s*=\s*'([^']+)'/) {
            $CPG_User = $1;
        }
        if (/CONFIG\[\s*'dbpass'\s*\]\s*=\s*'([^']+)'/) {
            $CPG_Pass = $1;
        }
        if (/CONFIG\[\s*'dbname'\s*\]\s*=\s*'([^']+)'/) {
            $CPG_DB = $1;
        }
    }
    close C;

    # Grab db settings from Zen
    open ( C, "< $ZP_Path/$ZP_Config") or die "Can't open $ZP_Path/$ZP_Config.  $!\n";
    while (<C>) {
        if (/conf\[\s*'mysql_host'\s*\]\s*=\s*'([^']+)'/) {
            $ZP_Host = $1;
        }
        if (/conf\[\s*'mysql_user'\s*\]\s*=\s*'([^']+)'/) {
            $ZP_User = $1;
        }
        if (/conf\[\s*'mysql_pass'\s*\]\s*=\s*'([^']+)'/) {
            $ZP_Pass = $1;
        }
        if (/conf\[\s*'mysql_database'\s*\]\s*=\s*'([^']+)'/) {
            $ZP_DB = $1;
        }
    }
    close C;
}

sub print_settings {
    print << "end_settings";
Using These settings :

CPG :
  Host : $CPG_Host
  DB   : $CPG_DB
  User : $CPG_User
  Pass : $CPG_Pass

ZenPhoto :
  Host : $ZP_Host
  DB   : $ZP_DB
  User : $ZP_User
  Pass : $ZP_Pass


end_settings
}

sub db_handle {
    my ($db,$host,$user,$pass) = @_;
    my $dsn = "DBI:mysql:database=$db;host=$host";
    my $dbh = DBI->connect($dsn, $user, $pass, {'RaiseError' => 1});
    return $dbh;
}