#!/usr/local/perl5.00503/bin/perl -w
#-------------------------------------------------------------------------------
# Script that extracts all the info from the csv file and inserts it into the
# oracle data table, the only thing it doesnt work out is all the page category 
# codes, so you have to do that yourself.
#-------------------------------------------------------------------------------

use strict;
use lib '../lib';
use lib '/eld/lib/perl5/site_perl/5.005/sun4-solaris/';
use DBI;

my $csvdbh = DBI->connect('DBI:CSV:f_dir=../data','','', {RaiseError => 1, PrintError => 1}) or die "cant connect $DBI::errstr";
my $oradbh = DBI->connect('DBI:Oracle:hloglive.world','uid','pwd', {
                           RaiseError => 1,
                           PrintError => 1,
                           AutoCommit => 0}) or die "cant do ora $DBI::errstr";

my $sth3 = $csvdbh->prepare('select * from linkdata');
$sth3->execute or die "cant execute csv $DBI::errstr";

my $rownum = 1;
my $sth2 = $oradbh->prepare('select id from page_cat where name = ?');
my $ref;

while ($ref = $sth3->fetchrow_arrayref) {
    foreach (@$ref) { $_ = '' unless defined }
    my @row = @$ref;

    $owner = uc($row[1]);
    $row[3] = ' ' if ($row[3] eq '');

    my $page = '';      
    $sth2->execute($row[9]); 
    while (@_ = $sth2->fetchrow) {
        $page = $_[0];
    }
    $page = 1 unless ($page);
    $sth2->finish;


    $row[5] =~ s/^(.{30})?.+/$1/ if (length($row[5]) > 30);
    $row[6] =~ s/^(.{200})?.+/$1/ if (length($row[6]) > 200);
    eval {
        for (@row[3..8]) {
            $_ = $oradbh->quote($_);
        } 
        my $sql = qq%insert into DATA values 
                   ($rownum, '$owner', $page, SYSDATE, 
                   $row[3],$row[5],$row[6],$row[7], $row[8],'','','','','' )%;

        my $insth = $oradbh->prepare($sql);
        $insth->execute;
        $insth->finish;
    };
    $rownum++;
    if ($@) {
       print "$@";
       $oradbh->rollback;
    } else {
       print "row done";
       $oradbh->commit;
    }
    print "$rownum\n";
}

$sth3->finish or die "$DBI::errstr";

$oradbh->disconnect;
$csvdbh->disconnect;