#!/usr/bin/perl -w

#
# tasuke_db_upgrade.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 $DB      = '';
my $HOST    = 'localhost';
my $USER    = '';
my $PASS    = '';
my $RDBMS = '';
my $TABLESPACE = '';

################################################################
# Checking parameters and database connection
################################################################
GetOptions( 
            '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;
}

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();
    }
}else{
    $connect= DBI->connect('DBI:mysql:database='.$DB.';host='.$HOST,$USER,$PASS)
    or die "Can't connect to database: $DBI::errstr\n";
}

###########################################################
# Upgrading
###########################################################

if($RDBMS eq "oracle"){
    my $isChanged = 0;
    ### Create table for condition of general purpose track ##############
    if(!_check_exists_table('sys_condition')){
        print "\nUpgrading database version 1.5.0 : Step 1\n";
        _create_table_conditionTable();
        print "Done.\n";
        $isChanged++;
    }
    if($isChanged == 0){print "\nThis database schema is latest version.\n";}
    _exit();
    print "\nDatabase schema: 1.4.0\nFinished!\n\n";
}else{
    my $isChanged=0;
    _dbexe("LOCK TABLES sys_track WRITE, sys_accession WRITE, sys_chromosome READ");

    ### For annotation search ####################################
    if(! _check_exists_note()){
        print "\nUpgrading database version 1.0.0 : Step 1\n";
        _add_column_note();
         print "Done.\n";
         $isChanged++;
    }
    if(! _check_exists_noteidx()){
        print "\nUpgrading database version 1.0.0 : Step 2\n";
        _add_idx_note();
        print "Done.\n";
        $isChanged++;
    }

    ### Index for a GFF track ####################################
    if(! _check_exists_idx_pidx()){
        print "\nUpgrading database version 1.3.0 : Step 1\n";
        _add_idx_pidx();
        print "Done.\n";
        $isChanged++;
    }

    ### For a general purpose track ####################################
    if(!_check_exists_genPurTrack()){
        print "\nUpgrading database version 1.3.0 : Step 2\n";
        _add_column_numReads();
        my @c = _chrs();
        my @accessions = _get_list();
        for(my $x=0; $x<$#accessions; $x++){
            if ( ! defined _check_exists_table($accessions[$x]."_cstm") ) {
                my $s="CREATE TABLE IF NOT EXISTS ".$accessions[$x]."_cstm ( pos int primary key,";
                for ( my $i=0; $i<=$#c; $i++) {
                    $s = $s . $c[$i] . "_b smallint,";
                    $s = $s . $c[$i] . "_k smallint";
                    $s = $s . "," if ( $i < $#c);
                }
                $s=$s.")ENGINE MyISAM";
                _dbexe($s);
            }
        }
        print "Done.\n";
        $isChanged++;
    }

    ### For field length of sys_track ####################################
    if( _check_len_chr_sysTrack()){
        print "\nUpgrading database version 1.3.0 : Step 3\n";
        _alter_table_chr_sysTrack();
        print "Done.\n";
        $isChanged++;
    }
    
    ### Create table for condition of general purpose track ##############
    if(!_check_exists_table('sys_condition')){
        print "\nUpgrading database version 1.5.0 : Step 1\n";
        _create_table_conditionTable();
        print "Done.\n";
        $isChanged++;
    }
    
    _dbexe("UNLOCK TABLES");
    if($isChanged==0){
        print "\nThis database schema is latest version.\n";
    }
    print "\nDatabase schema: 1.5.0\nFinished!\n\n";
    _exit();
}

###########################################################
# Functions
###########################################################

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

#### Usage #######################################
sub _error_method {
    print "\nUpgrading Database\n";
    print "tasuke_db_upgrade.pl -db [database] -u [user] -p [password]\n( For oracle database: -b oracle -s [tablespace])\n\n";
}

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

### chromosome ################################################
sub _chrs {
    my $val="";
    my @c;
    my $query = "SELECT id FROM sys_chromosome";
    my $query_handle = $connect->prepare($query);
    $query_handle->execute();
    $query_handle->bind_columns(undef, \$val);
    while($query_handle->fetch()) {
        push @c, $val;
    }
    return @c;
}

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

### Checking table ####################################
sub _check_exists_table {
    my ($t) = @_;
    my $val;
    if($RDBMS eq "oracle"){
        $t = uc($t);
        $val = $connect->selectrow_array("select table_name from user_tables where tablespace_name = '$TABLESPACE' and table_name = '$t'");
    }else{
        $val = $connect->selectrow_array("show tables like '$t'");
    }
    return $val;
}

### 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;
}

#### 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);
}

### Checking column "note" ###############################
sub _check_exists_note {
    my $val = $connect->selectrow_array("show columns from sys_track where Field = 'note'");
    return $val;
}

### Add column "note"###################################
sub _add_column_note{
    _dbexe("alter table sys_track add column note text");
    _dbexe("alter table sys_track add column others text");
}

### Checking index "note" ###############################
sub _check_exists_noteidx {
    my $val = $connect->selectrow_array("show index from sys_track where Column_name='note' and Index_type='FULLTEXT'");
    return $val;
}

### Add idx "note"###################################
sub _add_idx_note{
    _dbexe("alter table sys_track add fulltext(note)");
    _dbexe("alter table sys_track add fulltext(others)");
}

### Checking index for loading ###############################
sub _check_exists_idx_pidx{
    my $val = $connect->selectrow_array("show index from sys_track where Key_name='p_idx'");
    return $val;
}

### Add index for loading ###################################
sub _add_idx_pidx{
    _dbexe("alter table sys_track add index p_idx(chr,pos)");
}

### Drop index for loading ###################################
sub _drop_idx_pidx{
    _dbexe("alter table sys_track drop index p_idx");
}

### Checking column "num_reads" ###############################
sub _check_exists_genPurTrack {
    my $val = $connect->selectrow_array("show columns from sys_accession where Field = 'num_reads'");
    return $val;
}

### Add column "note"###################################
sub _add_column_numReads{
    _dbexe("alter table sys_accession add column num_reads bigint default 0");
}

### Checking column "chr" in the sys_track ###############################
sub _check_len_chr_sysTrack {
    my @res = $connect->selectrow_array("show columns from sys_track where Field = 'chr'");
    $res[1] =~ s/.*\((\d+)\).*/$1/ ;
    if( defined $res[1] ){
        if($res[1] == 10 ){
            return 1;
        }else{
            return 0;
        }
    }else{
        return 0;
    }
}

### Checking column "chr" string length in the sys_track ###############################
sub _alter_table_chr_sysTrack {
   _dbexe("alter table sys_track change column chr chr varchar(50)");
}

### condition table ######################################################
sub _create_table_conditionTable{
    if($RDBMS eq "oracle"){
        _dbexe("CREATE TABLE sys_condition (
                  condition_id varchar2(255) NOT NULL,
                  table_id number(11,0) DEFAULT NULL,
                  CONSTRAINT KEY_SYS_CONDITION PRIMARY KEY (condition_id) )TABLESPACE $TABLESPACE");
        _dbexe("CREATE OR REPLACE PUBLIC SYNONYM sys_condition FOR $USER.sys_condition");
    }else{
        _dbexe("CREATE TABLE `sys_condition` (
                  `condition_id` varchar(255) DEFAULT NULL,
                  `table_id` int(11) DEFAULT NULL,
                  PRIMARY KEY (`condition_id`))");
    }
}
