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";

 

}

[Linux] URL Encoding table for special characters

 

Use this URL Encoding table for special characters:

Character      Code    Character      Code

      %26%23169;     t       %74

�      %26%23174;     u       %75

      %E2%84%A2      v       %76

backspace      %08     w       %77

tab     %09     x       %78

linefeed       %0A     y       %79

creturn %0D     z       %7A

space   %20     {       %7B

!       %21     |       %7C

"       %22     }       %7D

#       %23     ~       %7E

$       %24           %A2

%       %25           %A3

&       %26           %A5

'       %27     |       %A6

(       %28     §      %A7

)       %29     ≪      %AB

*       %2A           %AC

+       %2B     ?       %AD

,       %2C     º      %B0

-       %2D     ±      %B1

.       %2E     ª      %B2

/       %2F     ,       %B4

0       %30     μ      %B5

1       %31     ≫      %BB

2       %32     ¼      %BC

3       %33     ½      %BD

4       %34     ¿      %BF

5       %35     A`      %C0

6       %36     A´     %C1

7       %37     A^      %C2

8       %38     A~      %C3

9       %39     A¨     %C4

:       %3A     A°     %C5

;       %3B     Æ      %C6

<       %3C     C¸     %C7

=       %3D     E`      %C8

>       %3E     E´     %C9

?       %3F     E^      %CA

@       %40     E¨     %CB

A       %41     I`      %CC

B       %42     I´     %CD

C       %43     I^      %CE

D       %44     I¨     %CF

E       %45     Р     %D0

F       %46     N~      %D1

G       %47     O`      %D2

H       %48     O´     %D3

I       %49     O^      %D4

J       %4A     O~      %D5

K       %4B     O¨     %D6

L       %4C     Ø      %D8

M       %4D     U`      %D9

N       %4E     U´     %DA

O       %4F     U^      %DB

P       %50     U¨     %DC

Q       %51     Y´     %DD

R       %52     Þ      %DE

S       %53     ß      %DF

T       %54     a`      %E0

U       %55     a´     %E1

V       %56     a^      %E2

W       %57     a~      %E3

X       %58     a¨     %E4

Y       %59     a°     %E5

Z       %5A     æ      %E6

[       %5B     c¸     %E7

\       %5C     e`      %E8

]       %5D     e´     %E9

^       %5E     e^      %EA

_       %5F     e¨     %EB

`       %60     i`      %EC

a       %61     i´     %ED

b       %62     i^      %EE

c       %63     i¨     %EF

d       %64     ð      %F0

e       %65     n~      %F1

f       %66     o`      %F2

g       %67     o´     %F3

h       %68     o^      %F4

i       %69     o~      %F5

j       %6A     o¨     %F6

k       %6B     ÷      %F7

l       %6C     ø      %F8

m       %6D     u`      %F9

n       %6E     u´     %FA

o       %6F     u^      %FB

p       %70     u¨     %FC

q       %71     y´     %FD

r       %72     þ      %FE

s       %73     y¨     %FF

 

 

[Linux] 리눅스에서 램디스크 생성(how to make ram disk in linux)

Creating a Linux ramdisk

 

While performing some testing a few weeks ago, I needed to create a ramdisk on one of my redhat AS 4.0 servers. I knew Solaris supported tmpfs, and after a bit of googling was surprised to find that Linux supported the tmpfs pseudo-file system as well. To create a ramdisk on a Linux host, you first need to find a suitable place to mount the tmpfs file system. For my tests, I used mkdir to create a directory valled /var/ramdisk:

 

$ mkdir /var/ramdisk

 

Once the mount point is identified, you can use the mount command to mount a tmpfs file system on top of that mount point:

 

$ mount -t tmpfs none /var/ramdisk -o size=28m

 

 

[perl] 파일의 생성시간과 현재 시간을 비교

#!/usr/bin/perl

 

 

use strict;

 

my $conv_imgae_root_dir = "/home/sjang/mydir1";

my $ori_imgae_root_dir = "/home/sjang/mydir2";

 

# expire time: 14days

my $expire_seconds = 10080*60;

 

my $current_time = time;

my $current_hour;

 

while(1)

{

        $current_hour = get_hour();

        if ($current_hour eq "04") # every dat 04:xx

        {

               # start to check...

               # print "$current_hour, 4 si\n";

               check_expire($conv_imgae_root_dir);

               check_expire($ori_imgae_root_dir);

        }

        # per hour

        sleep 60*60;

}

 

sub check_expire()

{

        my $check_dir = $_[0];

        my @files;

 

        #print "start ...$check_dir \n";

 

        opendir(DIR, $check_dir);

        @files = readdir(DIR);

        closedir(DIR);

         

         

        # build a unsorted list from the

        # # @files array:

 

        my $file;

        foreach $file (@files)

        {

               my $working_dir;

               my $ab_file;

               next if ($file eq "." or $file eq "..");

#              print "- $check_dir\/$file\n";

               $ab_file = "$check_dir/$file";

               if (-d $ab_file)

               {

#                      print "working dir = $ab_file\n";

                       check_expire($ab_file);

               }

               else

               {

                       my $file_time = get_file_time($ab_file);

                       my $diff_time = $current_time - $file_time;

                       if ( $diff_time > $expire_seconds)

                       {

#                             print "file remove = $ab_file\n";

                              system("rm $ab_file");

                       }

               }

        }

}

 

sub get_file_time()

{

        my $filename = $_[0];

        my $mtime;

        $mtime = (stat($filename))[9];

        return $mtime;

}

 

sub get_hour()

{

        my $today_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];

        #($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;

        }

 

        return $hour;

}

[perl] sub string 처리와 파일로부터 파일사이즈 구하기


파일 크기 간단하게 구하기.


#!/usr/bin/perl

my $sub_str;
my $file_size;

open FILE, "jpg_only.log" or die $!;

while (<FILE>)
{
        $sub_str =  substr $_, 26;
        chomp $sub_str;

        $file_size = -s $sub_str;

        if (($file_size < 550*1024) && ($file_size > 500*1024))
        {
                print "http://my.host.com/$sub_str\n";
        }
}

close(FILE);




[perl] sub string 구하기

스트링 안에서 특정 구간의 sub-스트링을 구하고 싶을 때...

---------------------------------------------------------

#!/usr/bin/perl

my $sub_str;

open FILE, "only_jpg" or die $!;

while (<FILE>)
{
        $sub_str =  substr $_, 42, 48;

        print "http://my.host.com/$sub_str\n";
}

close(FILE);

-----------------------------------------------------------