2009년 9월 2일

[perl] access to mysql DB with perl DBI

 

#!/usr/bin/perl

 

#use strict;

use DBI;

 

my $cache_hit_days = 8;

 

my $base_id = "0000000000";

 

my $conf_file = "/etc/mig.conf";

my $installdir_key;

my $installdir_value;

 

open(MIG_CONF, $conf_file) || die "$conf_file is not valid\n";

while (<MIG_CONF>)

{

        chomp;

        ($installdir_key, $installdir_value) = split /=/, $_, 2;

        if ($installdir_key eq "InstallDir")

        {

#              print "$installdir_key = [$installdir_value]\n";

               last;

        }

}

my $first;

$first = substr($installdir_value, -1, 1);

#print "$first\n";

$first = substr($installdir_value, -2, 1);

#print "$first\n";

$first = substr($installdir_value, -3, 1);

#print "$first\n";

$first = substr($installdir_value, 0, 1);

#print "$first\n";

$first = substr($installdir_value, 1, 1);

#print "$first\n";

$first = substr($installdir_value, 2, 1);

#print "$first\n";

 

my $input_image_dir = $installdir_value."/input";

my $output_image_dir = $installdir_value."/output";

 

#my $image_sid = "12345678";

#my $conv_image_sid = "abcedfg";

 

#              my $image_sid_1 = substr($image_sid, -1, 1);

#              my $image_sid_2 = substr($image_sid, -2, 1);

#              my $image_sid_3 = substr($image_sid, -3, 1);

#              my $image_sid_4 = substr($image_sid, -4, 1);

#              my $original_image_filename = $input_image_dir."/".$image_sid_1."/".$image_sid_2."/".$image_sid_3."/".$image_sid_4."/".$image_sid;

 

 

#                      my $conv_image_sid_1 = substr($conv_image_sid, 0, 1);

#                      my $conv_image_sid_2 = substr($conv_image_sid, 1, 1);

#                      my $conv_image_sid_3 = substr($conv_image_sid, 2, 1);

#                      my $conv_image_sid_4 = substr($conv_image_sid, 3, 1);

#                      my $conv_image_filename = $output_image_dir."/".$conv_image_sid_1."/".$conv_image_sid_2."/".$conv_image_sid_3."/".$conv_image_sid_4."/".$conv_image_sid;

 

#print "ori = [$original_image_filename], conv = [$conv_image_filename]\n";

#exit;

 

 

# 현재 시각을 구한다.

#my $today_time;

my $cache_out_time;

my $day;

my $month;

my $year;

my $hour;

my $minutes;

my $seconds;

 

#($seconds, $minutes, $hour, $day, $month, $year) = (localtime)[0, 1, 2, 3, 4, 5];

($seconds, $minutes, $hour, $day, $month, $year) = localtime(time() - $cache_hit_days*(60*60*24));

#($day, $month, $year) = (localtime)[3, 4, 5];

$year += 1900;

$month += 1;

if ( $month < 10 )

{

    $month = "0".$month;

}

if ( $day < 10 )

{

    $day = "0".$day;

}

if ( $hour < 10 )

{

    $hour = "0".$hour;

}

if ( $minutes < 10 )

{

    $minutes = "0".$minutes;

}

if ( $seconds < 10 )

{

    $seconds = "0".$seconds;

}

#$today_time = $year."-".$month."-".$day." ".$hour.":".$minutes.":".$seconds;

$cache_out_time = $year."-".$month."-".$day." ".$hour.":".$minutes.":".$seconds;

#print "time = $cache_out_time\n";

#exit;

 

my $dbh = connect_to_db();

my $sth;

delete_cached_images($dbh);

disconnect_db($dbh);

 

 

sub connect_to_db

{

    my $server = 'my.host.com';

#    my $server = '1.2.3.4';

    my $db = 'DB_NAME';

    my $username = 'ID';

    my $password = 'PW';

 

    my $dbh = DBI->connect("dbi:mysql:$db:$server", $username, $password) or die print "connect error\n";

 

    return $dbh;

}

 

sub disconnect_db

{

    my $dbh = $_[0];

    $sth->finish();

    $dbh->disconnect;

}

 

sub delete_cached_images

{

 

# SELECT sid FROM source_image WHERE etime <= CURRENT_TIMESTAMP

# DELETE FROM source_image WHERE etime <= CURRENT_TIMESTAMP

# UPDATE conv_image b SET expired = 'Y' WHERE b.source_image_sid = ( SELECT a.sid FROM source_image a WHERE a.sid = ? )

 

    my $dbh = $_[0];

    my $db_query;

    my $row;

        my $count = 0;

#    $db_query = "SELECT sid FROM source_image WHERE etime <= '$today_time'";

#       $db_query = "SELECT sid FROM source_image WHERE htime <= '2006-11-25 00:30:00'";

    $db_query = "SELECT sid FROM source_image WHERE htime <= '$cache_out_time' and htime != '0000-00-00 00:00:00'";

#    $db_query = "SELECT sid FROM source_image WHERE ctime > '2007-01-18 18:00:00' and ctime <= '2007-01-18 19:00:00' and htime != '0000-00-00 00:00:00'";

#       $db_query = "UPDATE conv_image b SET expired = 'Y' WHERE b.source_image_sid = ( SELECT a.sid FROM source_image a WHERE a.sid = '10')";

        #print "query = $db_query\n";

    $sth = $dbh->prepare($db_query);

    $sth->execute();

        while($row = $sth->fetchrow_arrayref)

        {

               $count++;

###            print "----------------------------------------------------[$count]\n";

        #my @row = $sth->fetchrow_array;

       

               #print "row[$i] = $row[$i] \n";

               #print "sid = $row->[0] \n";

 

               my $image_sid = $row->[0];

 

               # DB에서 삭제

###            print "image_sid = [$image_sid]\n";

        my $db_query_delete = "DELETE FROM source_image WHERE sid = '$image_sid'";

        my $sth_delete = $dbh->prepare($db_query_delete);

        $sth_delete->execute();

 

               my $db_query_update = "UPDATE conv_image SET expired = 'Y' WHERE source_image_sid = $image_sid";

                my $sth_update = $dbh->prepare($db_query_update);

        $sth_update->execute();

 

               my $image_sid_length = length($image_sid);

               my $image_sid_ori = $image_sid;

 

               my $i = 0;

 

               # 10 original image의 파일이름 길이

               if ($image_sid_length <= 10)

               {

                       for ($i = $image_sid_length; $i < 10;  $i++)

                       {

                              $image_sid = "0".$image_sid;

                       }

               }

#              print "original image sid $image_sid\n";

 

 

               # original 이미지 파일 삭제

               my $image_sid_1 = substr($image_sid, -4, 1);

               my $image_sid_2 = substr($image_sid, -3, 1);

               my $image_sid_3 = substr($image_sid, -2, 1);

               my $image_sid_4 = substr($image_sid, -1, 1);

               my $original_image_filename = $input_image_dir."/".$image_sid_1."/".$image_sid_2."/".$image_sid_3."/".$image_sid_4."/".$image_sid;

###            print "original image delete $original_image_filename\n";

               system("rm $original_image_filename");

 

 

#              print "ori_image_sid = $image_sid_ori\n";

               # Conv 이미지 파일 삭제

               my $db_query_get_scode = "SELECT s_code, filetype FROM conv_image WHERE source_image_sid = $image_sid_ori";

#              print"query = $db_query_get_scode\n";

        my $sth_get_scode = $dbh->prepare($db_query_get_scode);

        $sth_get_scode->execute();

               while($row = $sth_get_scode->fetchrow_arrayref)

               {

                       my $conv_image_sid = $row->[0];

                       my $conv_image_ext = $row->[1];

#                      print "conv image sid = $conv_image_sid\n";

 

                       my $conv_image_sid_1 = substr($conv_image_sid, 0, 1);

                       my $conv_image_sid_2 = substr($conv_image_sid, 1, 1);

                       my $conv_image_sid_3 = substr($conv_image_sid, 2, 1);

                       my $conv_image_sid_4 = substr($conv_image_sid, 3, 1);

                       my $conv_image_filename = $output_image_dir."/".$conv_image_sid_1."/".$conv_image_sid_2."/".$conv_image_sid_3."/".$conv_image_sid_4."/".$conv_image_sid.".".$conv_image_ext;

###                    print "conv image delete $conv_image_filename\n";

                       system("rm $conv_image_filename");

 

               }

        }

        print "deleted Image count = [$count]\n";

 

}

댓글 없음:

댓글 쓰기