#!/usr/bin/perl -w

#
# tasuke_dbCompression.pl
# http://tasuke.dna.affrc.go.jp/
#
# Author: Ryutaro Itoh
#
# Copyright (c) 2013 National Institute of Agrobiological Sciences   
#
# This software is released under the MIT License.
#
# http://opensource.org/licenses/mit-license.php
#

use strict;
use DBI;
use Getopt::Long;

my $RESTORE = 0;
my $DB      = '';
my $HOST    = 'localhost';
my $USER    = '';
my $PASS    = '';
my $RDBMS = '';
my $TABLESPACE = '';

################################################################
# Checking parameters and database connection
################################################################
GetOptions( 'r|restore'     => \$RESTORE,
            'db|database=s' => \$DB,
            'h|host=s'      => \$HOST,
            'u|user=s'      => \$USER,
            'b|backend=s'      => \$RDBMS,
            's|space=s'   => \$TABLESPACE,
            'p|password=s'  => \$PASS
);

if ( !$DB || !$USER ) {
    _error_method();
    exit;
}
if(!$RDBMS){
    $RDBMS = 'oracle';
}

my $connect;
if($RDBMS eq "oracle"){
    if(!$TABLESPACE){
        _error_method();
        exit;
    }
    $connect = DBI->connect('DBI:Oracle:host='.$HOST.';SERVICE_NAME='.$DB.';',$USER,$PASS)
    or die "Can't connect to database: $DBI::errstr\n";
    if(!_check_exists_tablespace($TABLESPACE)){
        print "Tablespace: '$TABLESPACE' is not found.";
        _exit(1);
    }
}else{
    print "This tool compresses tables for Oracle database Enterprise edition.\n\n";
    exit;
}

if($RESTORE == 1){
    _decompress_db();
}else{
    _compress_db();
}
_print_time("Finished!");
_exit();
    
### Compressing  #########################################
sub _compress_db{
    my $ref = _get_list();
    my @accessions = @$ref;
    my $len = @accessions;
    my $num = 1;
    my $guide = 10;
    my $pct = 0;
    _print_time("Compressing tables & Rebuilding indexes..."); 
    foreach my $x(@accessions){
        $pct = int(($num/$len)*100);
        if($pct > $guide){
            print $pct."%\n";
            $guide = $guide+10;
        }
        _dbexe("ALTER TABLE ".$x."_VARIANT move compress");
        _dbexe("ALTER index ".$x."_VAR_IDX rebuild");
        _dbexe("ALTER index KEY_".$x."_VAR rebuild");
        $num++;
    }
}

### Decompressing  #########################################
sub _decompress_db{
    my $ref = _get_list();
    my @accessions = @$ref;
    my $len = @accessions;
    my $num = 1;
    my $guide = 10;
    my $pct = 0;
    _print_time("Decompressing tables & Rebuilding indexes..."); 
    foreach my $x(@accessions){
        $pct = int(($num/$len)*100);
        if($pct > $guide){
            print $pct."%\n";
            $guide = $guide+10;
        }
        _dbexe("ALTER TABLE ".$x."_VARIANT move nocompress");
        _dbexe("ALTER index ".$x."_VAR_IDX rebuild");
        _dbexe("ALTER index KEY_".$x."_VAR rebuild");
        $num++;
    }
}

### Checking tablespace ####################################
sub _check_exists_tablespace {
    my ($t) = @_;
    $t = uc($t);
    my $val = $connect->selectrow_array("select tablespace_name from user_tablespaces where tablespace_name = '$t'");
    return $val;
}

### Checking list table  #########################################
sub _get_list{
    my $val="";
    my @id;
    my $query = "SELECT accession_db FROM sys_accession";
    my $query_handle = $connect->prepare($query);
    
    $query_handle->execute();
    $query_handle->bind_columns(undef, \$val);
    while($query_handle->fetch()) {
        push @id, uc($val);
    }
    return \@id;
}

### Execute DB ###################################################
sub _dbexe {
    my ($query) = @_;
    my $query_handle = $connect->prepare($query);
    $query_handle->execute() or warn "Excute error: $DBI::errstr\n";
}

#### Disconnection and exit #######################################
sub _exit {
    my ($arg) = @_;
    my $code = 0;
    if(defined $arg){$code = $arg;}
    $connect->disconnect or warn "Disconnection error: $DBI::errstr\n";
    exit($code);
}

#### Time #######################################
sub _print_time {
    my ($str) = @_;
    my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime;
    $year += 1900;
    $mon += 1;
    my $datetime = sprintf "%04d-%02d-%02d %02d:%02d:%02d", $year, $mon, $mday, $hour, $min, $sec;
    print "$str : $datetime\n";
}

#### Usage #######################################
sub _error_method {
    print "\ntasuke_dbCompression.pl -db [database] -u [user] -p [password] -s [tablespace name]\n";
    print "* -r : Decompress the database \n\n";
}
