#!/usr/bin/perl -w

#
# tasuke_init.pl
# http://tasuke.dna.affrc.go.jp/
#
# Author: Jungsok Kim, Ryutaro Itoh
#
# Copyright (c) 2013 National Institute of Agrobiological Sciences   
#

use strict;
use POSIX qw(ceil);
use DBI;
use Getopt::Long;
use FindBin qw($Bin);
use lib "$Bin";
use CommonLibTASUKE;

my $RESTORE = '';
my $DB      = '';
my $HOST    = 'localhost';
my $USER    = '';
my $PASS    = '';
my $SAMPLE   = 'accession_ID';
my $ID = '';
my $RDBMS = '';
my $TABLESPACE = '';

#my $CHR_DIVIDE_UNIT = 1;
my $CHR_DIVIDE_UNIT = 900;
my %TABLE2CHR;

################################################################
# 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;
}
if(!$RDBMS){
    $RDBMS = 'mysql';
}

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";
    
    my $connect_ctxsys = DBI->connect('DBI:Oracle:host='.$HOST.';SERVICE_NAME='.$DB.';',"ctxsys","ctxsys");
    if(!$connect_ctxsys){
        print "\n----------------------------------------------------------------------\n";
        print "Unlocking a user named CTXSYS is required for text searching function.\n";
        print "Try following command after logged on database admin user.\n";
        print "> ALTER USER ctxsys IDENTIFIED ctxsys ACCOUNT UNLOCK\;\n";
        print "----------------------------------------------------------------------\n";
        _exit(1);
    }else{
        $connect_ctxsys->disconnect or warn "Disconnection error: $DBI::errstr\n";
    }
    if(!_check_exists_tablespace($TABLESPACE)){
        print "Tablespace: '$TABLESPACE' is not found.";
        _exit(1);
    }
}else{
    $connect= DBI->connect('DBI:mysql:database='.$DB.';host='.$HOST,$USER,$PASS)
    or die "Can't connect to database: $DBI::errstr\n";
}

###############################################################
# Checking default tables
# [1] sys_track
# [2] sys_snpEff
# [3] sys_snpAnn
# [4] sys_snpblock
###############################################################
_check_default_tables();

###############################################################
# Installing Started 
###############################################################
### Starting chromosome & accession list tables
$SIG{'INT'} = '_SIGINThandler';
print "\n\n  ------------------------------------------------";
print "\n   Installing TASUKE multiple genome browser started";
print "\n  ------------------------------------------------";
print "\n\n  Input csv file about chromosome's information below.";
if ( ! defined _check_exists_table("sys_chromosome") ) {
    _first_chrs();
} else {
    print "  Completed.";
}

if ( ! defined _check_exists_table("sys_accession") ) {
    if($RDBMS eq "oracle"){
        _dbexe("CREATE TABLE sys_accession (
        accession_id varchar2(40) NOT NULL,
        accession_db varchar2(6) DEFAULT NULL,
        accession_name varchar2(40) DEFAULT NULL,
        variety varchar2(40) DEFAULT NULL,
        sub_variety varchar2(40) DEFAULT NULL,
        origin varchar2(30) DEFAULT NULL,
        origin_2 varchar2(30) DEFAULT NULL,
        type varchar2(40) DEFAULT NULL,
        num_reads number(16,0) DEFAULT 0,
        other_1 longtext,
        other_2 longtext,
        PRIMARY KEY (accession_id)
        )TABLESPACE $TABLESPACE");
        _dbexe("CREATE OR REPLACE PUBLIC SYNONYM sys_accession FOR $USER.sys_accession");
    }else{
        _dbexe("DROP TABLE IF EXISTS  sys_accession");
        _dbexe("CREATE TABLE `sys_accession` (
            `accession_id` varchar(40) NOT NULL,
            `accession_db` varchar(6) DEFAULT NULL,
            `accession_name` varchar(40) DEFAULT NULL,
            `variety` varchar(40) DEFAULT NULL,
            `sub_variety` varchar(40) DEFAULT NULL,
            `origin` varchar(20) DEFAULT NULL,
            `origin_2` varchar(30) DEFAULT NULL,
            `type` varchar(40) DEFAULT NULL,
            `num_reads` bigint DEFAULT 0,
       	    `other_1` longtext,
            `other_2` longtext,
             PRIMARY KEY (`accession_id`)
            )");
    }
}

print "\n\n  -----------\n";
print "   Next step\n";
print "  -----------\n\n";
print "   1. Input Accession information\n";
#print "     # tasuke_accession.pl -c -db $DB -u $USER -p $PASS\n\n";
print "     # tasuke_accession.pl -db $DB -u $USER -p $PASS\n\n";
print "   2. Input Reference fasta\n";
print "     # tasuke_ref.pl -f [file(.fa)] -db $DB -u $USER -p $PASS\n\n";
print "   3. Input VARIANT file as count of accessions you inputted.\n";
print "     # tasuke_variant_vcf.pl -n [$SAMPLE] -f [file(.vcf)] -db $DB -u $USER -p $PASS\n\n";
print "   4. Input DEPTH file as count of accessions you inputted. (Optional)\n";
print "     # tasuke_tsv_db.pl -n [$SAMPLE] -f [file(.tsv)] -db $DB -u $USER -p $PASS\n\n";
print "   5. Input tsv file for the custom track as count of accessions you inputted. (Optional)\n";
print "     # tasuke_tsv_db.pl -c -n [$SAMPLE] -f [file(.tsv)] -db $DB -u $USER -p $PASS\n\n";
print "   6. Input gene tracks (Optional)\n";
print "     # tasuke_track_gff.pl -t [track_name] -f [file(.gff)] -db $DB -u $USER -p $PASS\n\n";
print "\n#######################################################################\n";

_exit();

###############################################################
# Set csv file for chromosomes
###############################################################
sub _first_chrs {
    my $F;
    print "\n\n    +----  CSV  -----------------------+";
    print "\n    | chr01,43270923,16610866,17243770 |";
    print "\n    | chr02,35937250,13872411,13541821 |";
    print "\n      ..";
    print "\n    | chr12,27531856,11761737,12103486 |";
    print "\n    +----------------------------------+";
    print "\n    [Formats] chrs, size of chrs, start of centromere, end of centromere\n\n";

    ## Require csv file
    print "> Where is the csv file? # ";
    while (defined(my $line = <STDIN>)){
        chomp($line);
        $line=~s/^\s*(.*?)\s*$/$1/;
        $F=$line;
        if ( $F ne "" && -e $F ) {
            if ( _check_chrs_csv($F) ) {
                print "It is wrong format.\n";
#            } elsif( _check_sequence_name ($F) ){
#                print "TASUKE does not accept the chromosome name which is included \".\"(dot).\n";
            } else {
                last;
            }
        } elsif ( $F ne "" && ! -e $F ) {
            print "File does not exists.\n";
        }
        print "> Where is the csv file? # ";
    }

    ## Confirmation
    if(_confirm_chrs($F) !=0){_exit(1);}
    print "> Are you sure creating database?[y|n] ";
    while (defined(my $line = <STDIN>)){
        chomp($line);
        if ( $line eq "y" || $line eq "Y" ) {
            last;
        } elsif ( $line eq "n" || $line eq "N" ) {
            print "finished.\n";
            exit;
        } else {
            print "> Are you sure creating database?[y|n] ";
        }
    }
    _create_chrs_table($F);
}

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


###############################################################
# Recognization csv file
###############################################################
sub _confirm_chrs {
    my ($f) = @_;
    my $e = 0;
    my $num = 0;
    print "\n   * Have you check the list you allocate.[".$f."]\n";
    print "   +----------------------------------------------------------------+\n";
    print "   | chrs  | size of chrs | start of centromere | end of centromere |\n";
    print "   +----------------------------------------------------------------+\n";
    open IN, '<', $f or die "file open error: $!";
    while (<IN>) {
        chomp($_);
        my @line=split(/\,/, $_);
        printf("   |%6s |%13d |%20s |%18s |\n",$line[0],$line[1],$line[2],$line[3]);
        $num++;
    }
    print "   +----------------------------------------------------------------+\n";
    if($num > $CHR_DIVIDE_UNIT){
#        $e = 1;
        my $maxSerial = ceil($num / $CHR_DIVIDE_UNIT);
        print "\n   * WARNING\n";
        print "     Over $CHR_DIVIDE_UNIT sequences were detected in this file.\n";
        printf "       Sequence table(sys_fasta) is split into %d pieces.\n", $maxSerial;
#        print "     TASUKE can accept under 2047 sequences.\n";
#        print "Exit\n";
    }else{
        print "\n   * WARNING\n";
        print "     Don't change column at [chrs], [size of chrs] on the database after any of this process.\n";
        print "     If you found miss some values, select [n] and again this process now.\n";
        print "     \n";
    }
    close IN;
    return $e;
}

###############################################################
# Checking chromosome file
###############################################################
sub _check_chrs_csv {
    my ($f)= @_;
    my $e=0;
    open IN, '<', $f or die "file open error: $!";
    while (<IN>) {
        chomp($_);
        my @line=split(/,/, $_);
        $e=1 if $#line != 3; 
    }
    close IN;
    return $e;
}

###############################################################
# Checking chromosome file
###############################################################
sub _check_sequence_name {
    my ($f)= @_;
    my $e=0;
    open IN, '<', $f or die "file open error: $!";
    while (<IN>) {
        chomp($_);
        my @line=split(/,/, $_);
        if($line[0] =~ /\./){$e=1;}
    }
    close IN;
    return $e;
}

###############################################################
# Creating chromosome table
###############################################################
sub _create_chrs_table {
    my ($f)= @_;
    my $e=0;
    
    if($RDBMS eq "oracle"){
        if ( defined _check_exists_table("sys_chromosome") ) {
            _dbexe("DROP TABLE sys_chromosome cascade constraint");
        }
        _dbexe("CREATE TABLE sys_chromosome (
                CONSTRAINT KEY_SYS_CHR seq number(6,0) PRIMARY KEY,
                id VARCHAR2(50) NOT NULL,
                seq_size number(11,0) NOT NULL,
                centromere_s number(11,0),  
                centromere_e number(11,0)
                )TABLESPACE $TABLESPACE");
        if(! _check_exists_seq("SEQ_SYS_CHROMOSOME")){
            _dbexe("CREATE SEQUENCE SEQ_SYS_CHROMOSOME INCREMENT BY 1 START WITH 1");
        }
        _dbexeIgnNamePh("CREATE OR REPLACE TRIGGER TRG_SYS_CHROMOSOME 
                        BEFORE INSERT ON sys_chromosome FOR EACH ROW 
                        BEGIN SELECT SEQ_SYS_CHROMOSOME.NEXTVAL INTO :NEW.seq FROM DUAL; 
                        END; 
                        ");
        _dbexe("CREATE OR REPLACE PUBLIC SYNONYM sys_chromosome FOR $USER.sys_chromosome");
        open IN, '<', $f or die "file open error: $!";
        while (<IN>) {
            chomp($_);
            my @line=split(/,/, $_);
            $line[0] =~ s/^\s*(.*?)\s*$/$1/;
            $line[1] =~ s/^\s*(.*?)\s*$/$1/;
            $line[2] =~ s/^\s*(.*?)\s*$/$1/;
            $line[3] =~ s/^\s*(.*?)\s*$/$1/;
            _dbexe("INSERT INTO sys_chromosome(id,seq_size,centromere_s,centromere_e)
                    VALUES('".$line[0]."',".$line[1].",".$line[2].",".$line[3].")");
        }
        close IN;

	## Set Table <=> Chr map
	setTable2Chr();

        ## Creating table sys_fasta
	_createSysFasta();
=head
        my @c=_chrs();
        if ( ! defined _check_exists_table("sys_fasta") ) {
            my $s="CREATE TABLE sys_fasta ( pos number(11,0) PRIMARY KEY,";
            for ( my $i=0; $i<=$#c; $i++) {
                $s = $s . $c[$i] . "_f VARCHAR2(1)";
                $s = $s . "," if ( $i < $#c);
            }
            $s=$s.")TABLESPACE $TABLESPACE";
            _dbexe($s);
            _dbexe("CREATE OR REPLACE PUBLIC SYNONYM sys_fasta FOR $USER.sys_fasta");
        }
        print "OK. ".int($#c+1)." chromosomes is setted.";
=cut
    }else{
        _dbexe("DROP TABLE iF EXISTS  sys_chromosome");
        _dbexe("CREATE TABLE sys_chromosome (
                seq SMALLINT AUTO_INCREMENT PRIMARY KEY,
                id VARCHAR(50) NOT NULL,
                size INT NOT NULL,
                centromere_s INT,  
                centromere_e INT
            )");
        open IN, '<', $f or die "file open error: $!";
        while (<IN>) {
            chomp($_);
            my @line=split(/,/, $_);
            $line[0] =~ s/^\s*(.*?)\s*$/$1/;
            $line[1] =~ s/^\s*(.*?)\s*$/$1/;
            $line[2] =~ s/^\s*(.*?)\s*$/$1/;
            $line[3] =~ s/^\s*(.*?)\s*$/$1/;
            _dbexe("INSERT INTO sys_chromosome(id,size,centromere_s,centromere_e)
                     VALUES('".$line[0]."',".$line[1].",".$line[2].",".$line[3].")");
        }
        close IN;

	## Set Table <=> Chr map
	setTable2Chr();

        ## Creating table sys_fasta
	_createSysFasta();
=head
        my @c=_chrs();
        if ( ! defined _check_exists_table("sys_fasta") ) {
            my $s="CREATE TABLE sys_fasta ( pos INT PRIMARY KEY AUTO_INCREMENT,";
            for ( my $i=0; $i<=$#c; $i++) {
                $s = $s. "`". $c[$i]. "_f` CHAR(1)";
                $s = $s. "," if ( $i < $#c);
            }
            $s=$s.")ENGINE MyISAM";
            _dbexe($s);
        }
        print "OK. ".int($#c+1)." chromosomes is setted.";
=cut
    }
    return $e;
}
###############################################################
# Creating fasta table
###############################################################
sub _createSysFasta {
        my $baseCreateSql = "CREATE TABLE %s (pos INT PRIMARY KEY AUTO_INCREMENT,%s) ENGINE MyISAM";
        my @tables = keys(%TABLE2CHR);
        foreach my $table (@tables) {
                my $s = "";
                foreach my $chr (@{$TABLE2CHR{$table}}) {
                        my $colname = "`". $chr. "_f`";
                        $s .= "$colname CHAR(1),";
                }
                $s =~ s/,$//;
                my $sql = sprintf($baseCreateSql, $table, $s);
                _dbexe($sql);
        }
}

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

### Set normal/serial tables ###
sub setTable2Chr {
	my $TABLE = 'sys_fasta';

	my @c=_chrs();
        if(!$CHR_DIVIDE_UNIT || @c <= $CHR_DIVIDE_UNIT) {
                my $s = "";
                for ( my $i=0; $i<=$#c; $i++) {
                        $TABLE2CHR{$TABLE} = [] if(!exists($TABLE2CHR{$TABLE}));
                        push @{$TABLE2CHR{$TABLE}}, $c[$i];
                }
        } else {
                my $serial = 1;
                for ( my $i=0; $i<=$#c; $i++) {
                        my $tablename = $TABLE. $serial;

                        $TABLE2CHR{$tablename} = [] if(!exists($TABLE2CHR{$tablename}));
                        push @{$TABLE2CHR{$tablename}}, $c[$i];
                        if( ($i != 0 && (!(($i+1)%$CHR_DIVIDE_UNIT) || $i == $#c)) || $CHR_DIVIDE_UNIT == 1 ) {
                                $serial++;
                        }
                }
        }
}

### Checking default(base) tables  #########################################
sub _check_default_tables {
    if($RDBMS eq "oracle"){
        my $val1 = $connect->selectrow_array("select table_name from user_tables where tablespace_name = '$TABLESPACE' and table_name = 'SYS_SNPEFF'");
        if ( ! defined $val1 ) {
            _dbexe("CREATE TABLE sys_snpEff(
                    eff_id number(6,0) NOT NULL,
                    eff_name varchar2(100) DEFAULT NULL,
                    eff_grp number(6,0) DEFAULT NULL,
                    CONSTRAINT KEY_SYS_SNPEFF PRIMARY KEY (eff_id)
                    ) TABLESPACE $TABLESPACE");
            if(! _check_exists_seq("SEQ_SYS_SNPEFF")){
                _dbexe("CREATE SEQUENCE SEQ_SYS_SNPEFF INCREMENT BY 1 START WITH 1");
            }
            _dbexeIgnNamePh("CREATE OR REPLACE TRIGGER TRG_SYS_SNPEFF 
                            BEFORE INSERT ON sys_snpEff FOR EACH ROW 
                            BEGIN SELECT SEQ_SYS_SNPEFF.NEXTVAL INTO :NEW.eff_id FROM DUAL; 
                            END; 
                            ");
            _dbexe("INSERT ALL 
                    INTO sys_snpEff VALUES(1,'INTERGENIC',0)
                    INTO sys_snpEff VALUES(2,'UPSTREAM',1)
                    INTO sys_snpEff VALUES(3,'DOWNSTREAM',1)
                    INTO sys_snpEff VALUES(4,'UTR_5_PRIME',2)
                    INTO sys_snpEff VALUES(5,'UTR_3_PRIME',2)
                    INTO sys_snpEff VALUES(6,'REGULATION',2)
                    INTO sys_snpEff VALUES(7,'INTRAGENIC',2)
                    INTO sys_snpEff VALUES(8,'GENE',2)
                    INTO sys_snpEff VALUES(9,'INTRON_CONSERVED',2)
                    INTO sys_snpEff VALUES(10,'INTRON',2)
                    INTO sys_snpEff VALUES(11,'INTERGENIC_CONSERVED',2)
                    INTO sys_snpEff VALUES(12,'SYNONYMOUS_START',3)
                    INTO sys_snpEff VALUES(13,'NON_SYNONYMOUS_START',3)
                    INTO sys_snpEff VALUES(14,'START_GAINED',3)
                    INTO sys_snpEff VALUES(15,'SYNONYMOUS_CODING',3)
                    INTO sys_snpEff VALUES(16,'SYNONYMOUS_STOP',3)
                    INTO sys_snpEff VALUES(17,'EXON',3)
                    INTO sys_snpEff VALUES(18,'CDS',3)
                    INTO sys_snpEff VALUES(19,'TRANSCRIPT',3)
                    INTO sys_snpEff VALUES(20,'CHROMOSOME_LARGE_DUPLICATION',3)
                    INTO sys_snpEff VALUES(21,'SPLICE_SITE_REGION',3)
                    INTO sys_snpEff VALUES(22,'SPLICE_SITE_BRANCH',3)
                    INTO sys_snpEff VALUES(23,'SPLICE_SITE_BRANCH_U12',3)
                    INTO sys_snpEff VALUES(24,'MICRO_RNA',3)
                    INTO sys_snpEff VALUES(25,'NON_SYNONYMOUS_STOP',3)
                    INTO sys_snpEff VALUES(26,'NON_SYNONYMOUS_CODING',4)
                    INTO sys_snpEff VALUES(27,'CODON_CHANGE',4)
                    INTO sys_snpEff VALUES(28,'CODON_INSERTION',4)
                    INTO sys_snpEff VALUES(29,'CODON_CHANGE_PLUS_CODON_INSERTION',4)
                    INTO sys_snpEff VALUES(30,'CODON_DELETION',4)
                    INTO sys_snpEff VALUES(31,'CODON_CHANGE_PLUS_CODON_DELETION',4)
                    INTO sys_snpEff VALUES(32,'UTR_5_DELETED',4)
                    INTO sys_snpEff VALUES(33,'UTR_3_DELETED',4)
                    INTO sys_snpEff VALUES(34,'CHROMOSOME_LARGE_INVERSION',4)
                    INTO sys_snpEff VALUES(35,'NEXT_PROT',4)
                    INTO sys_snpEff VALUES(36,'SPLICE_SITE_ACCEPTOR',5)
                    INTO sys_snpEff VALUES(37,'SPLICE_SITE_DONOR',5)
                    INTO sys_snpEff VALUES(38,'START_LOST',5)
                    INTO sys_snpEff VALUES(39,'EXON_DELETED',5)
                    INTO sys_snpEff VALUES(40,'FRAME_SHIFT',5)
                    INTO sys_snpEff VALUES(41,'STOP_GAINED',5)
                    INTO sys_snpEff VALUES(42,'STOP_LOST',5)
                    INTO sys_snpEff VALUES(43,'RARE_AMINO_ACID',5) 
                    INTO sys_snpEff VALUES(44,'CHROMOSOME_LARGE DELETION',5)
		    INTO sys_snpEff VALUES(45,'EXON_DELETED_PARTIAL',5)
                    INTO sys_snpEff VALUES(46,'EXON_DUPLICATION',5)
                    INTO sys_snpEff VALUES(47,'EXON_DUPLICATION_PARTIAL',5)
                    INTO sys_snpEff VALUES(48,'EXON_INVERSION',5)
                    INTO sys_snpEff VALUES(49,'EXON_INVERSION_PARTIAL',5)
                    INTO sys_snpEff VALUES(50,'GENE_DELETED',5)
                    INTO sys_snpEff VALUES(51,'GENE_DUPLICATION',5)
                    INTO sys_snpEff VALUES(52,'GENE_FUSION',5)
                    INTO sys_snpEff VALUES(53,'GENE_FUSION_HALF',5)
                    INTO sys_snpEff VALUES(54,'GENE_FUSION_REVERESE',5)
                    INTO sys_snpEff VALUES(55,'GENE_REARRANGEMENT',5)
                    INTO sys_snpEff VALUES(56,'PROTEIN_PROTEIN_INTERACTION_LOCUS',5)
                    INTO sys_snpEff VALUES(57,'PROTEIN_STRUCTURAL_INTERACTION_LOCUS',5)
                    INTO sys_snpEff VALUES(58,'TRANSCRIPT_DELETED',5)
                    SELECT * from dual");
            _dbexe("CREATE OR REPLACE PUBLIC SYNONYM sys_snpEff FOR $USER.sys_snpEff");
        }
        my $val2 = $connect->selectrow_array("select table_name from user_tables where tablespace_name = '$TABLESPACE' and table_name = 'SYS_TRACK'");
        if ( ! defined $val2 ) {
            _dbexe("CREATE TABLE sys_track (
                   seq number(38,0) NOT NULL ,
                   track varchar2(50) DEFAULT NULL,
                   chr varchar2(50) DEFAULT NULL,
                   id varchar2(50) DEFAULT NULL,
                   name varchar2(59) DEFAULT NULL,
                   parent number(1,0) DEFAULT NULL,
                   type varchar2(40) DEFAULT NULL,
                   pos number(11,0) DEFAULT NULL,
                   end number(11,0) DEFAULT NULL,
                   strand number(4,0) DEFAULT NULL,
                   note varchar2(4000) DEFAULT NULL,
                   others varchar2(4000) DEFAULT NULL,
                   CONSTRAINT KEY_SYS_TRACK PRIMARY KEY (seq)
                    )TABLESPACE $TABLESPACE");
            if(! _check_exists_seq("SEQ_SYS_TRACK")){
                _dbexe("CREATE SEQUENCE SEQ_SYS_TRACK INCREMENT BY 1 START WITH 1");
            }
            _dbexeIgnNamePh("CREATE OR REPLACE TRIGGER TRG_SYS_TRACK 
                            BEFORE INSERT ON sys_track FOR EACH ROW 
                            BEGIN SELECT SEQ_SYS_TRACK.NEXTVAL INTO :NEW.seq FROM DUAL; 
                            END; 
                            ");
            _dbexe("CREATE INDEX sys_track_idx on sys_track(id) TABLESPACE $TABLESPACE");
            _dbexe("CREATE INDEX sys_track_pidx on sys_track(chr, pos) TABLESPACE $TABLESPACE");
            if(! _check_exists_pref("GFF_NOTE_LEXER") && ! _check_exists_pref("GFF_OTHERS_LEXER") && ! _check_exists_pref("GFF_ID_LEXER")){
                _dbexeIgnNamePh("BEGIN
                                ctx_ddl.create_preference('GFF_ID_LEXER','BASIC_LEXER');
                                ctx_ddl.create_preference('GFF_NOTE_LEXER','BASIC_LEXER');
                                ctx_ddl.create_preference('GFF_OTHERS_LEXER','BASIC_LEXER');
                                END;
                                ");
            }
            _dbexe("CREATE INDEX sys_track_id_idx on sys_track(id) indextype is ctxsys.context parameters('LEXER GFF_ID_LEXER')");
            _dbexe("CREATE INDEX sys_track_note_idx on sys_track(note) indextype is ctxsys.context parameters('LEXER GFF_NOTE_LEXER')");
            _dbexe("CREATE INDEX sys_track_others_idx on sys_track(others) indextype is ctxsys.context parameters('LEXER GFF_OTHERS_LEXER')");
            _dbexe("CREATE OR REPLACE PUBLIC SYNONYM sys_track FOR $USER.sys_track");
        }
        my $val3 = $connect->selectrow_array("select table_name from user_tables where tablespace_name = '$TABLESPACE' and table_name = 'SYS_SNPBLOCK'");
        if ( ! defined $val3 ) {
            _dbexe("CREATE TABLE sys_snpblock (
                  accession varchar2(40) DEFAULT NULL,
                  rate float DEFAULT NULL,
                  peak varchar2(20) DEFAULT NULL)TABLESPACE $TABLESPACE");
            _dbexe("CREATE OR REPLACE PUBLIC SYNONYM sys_snpblock FOR $USER.sys_snpblock");
        }
        my $val4 = $connect->selectrow_array("select table_name from user_tables where tablespace_name = '$TABLESPACE' and table_name = 'SYS_CONDITION'");
        if ( ! defined $val4 ) {
            _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{
        my $sqlGen = new CommonMysql();
        my $val1 = $connect->selectrow_array("SHOW TABLES LIKE 'sys_snpEff'");
        if ( ! defined $val1 ) {
		_dbexe($sqlGen->getCreateTableSysSnpeff());
		_dbexe($sqlGen->getInsertSysSnpeff());
=head
        _dbexe("CREATE TABLE `sys_snpEff` (
                `eff_id` smallint(6) NOT NULL AUTO_INCREMENT,
                `eff_name` varchar(100) DEFAULT NULL,
                `eff_grp` smallint(6) DEFAULT NULL,
                `eff_type` varchar(8) DEFAULT NULL,
                PRIMARY KEY (`eff_id`))");
        _dbexe("INSERT INTO `sys_snpEff` VALUES
               (null,'INTERGENIC',0,'EFF'),(null,'UPSTREAM',0,'EFF'),(null,'DOWNSTREAM',0,'EFF'),(null,'UTR_5_PRIME',0,'EFF'),
               (null,'UTR_3_PRIME',0,'EFF'),(null,'REGULATION',0,'EFF'),(null,'INTRAGENIC',0,'EFF'),(null,'GENE',0,'EFF'),
               (null,'INTRON_CONSERVED',0,'EFF'),(null,'INTRON',0,'EFF'),(null,'INTERGENIC_CONSERVED',0,'EFF'),
               (null,'SYNONYMOUS_START',1,'EFF'),(null,'NON_SYNONYMOUS_START',1,'EFF'),(null,'START_GAINED',1,'EFF'),
               (null,'SYNONYMOUS_CODING',1,'EFF'),(null,'SYNONYMOUS_STOP',1,'EFF'),(null,'EXON',1,'EFF'),(null,'CDS',1,'EFF'),
               (null,'TRANSCRIPT',1,'EFF'),(null,'CHROMOSOME_LARGE_DUPLICATION',1,'EFF'),(null,'SPLICE_SITE_REGION',1,'EFF'),
               (null,'SPLICE_SITE_BRANCH',1,'EFF'),(null,'SPLICE_SITE_BRANCH_U12',1,'EFF'),(null,'MICRO_RNA',1,'EFF'),
               (null,'NON_SYNONYMOUS_STOP',1,'EFF'),(null,'NON_SYNONYMOUS_CODING',2,'EFF'),(null,'CODON_CHANGE',2,'EFF'),
               (null,'CODON_INSERTION',2,'EFF'),(null,'CODON_CHANGE_PLUS_CODON_INSERTION',2,'EFF'),(null,'CODON_DELETION',2,'EFF'),
               (null,'CODON_CHANGE_PLUS_CODON_DELETION',2,'EFF'),(null,'UTR_5_DELETED',2,'EFF'),(null,'UTR_3_DELETED',2,'EFF'),
               (null,'CHROMOSOME_LARGE_INVERSION',2,'EFF'),(null,'NEXT_PROT',2,'EFF'),(null,'SPLICE_SITE_ACCEPTOR',3,'EFF'),
               (null,'SPLICE_SITE_DONOR',3,'EFF'),(null,'START_LOST',3,'EFF'),(null,'EXON_DELETED',3,'EFF'),(null,'FRAME_SHIFT',3,'EFF'),
               (null,'STOP_GAINED',3,'EFF'),(null,'STOP_LOST',3,'EFF'),(null,'RARE_AMINO_ACID',3,'EFF'),
               (null,'CHROMOSOME_LARGEDELETION',3,'EFF'),(null,'EXON_DELETED_PARTIAL',3,'EFF'),(null,'EXON_DUPLICATION',3,'EFF'),
               (null,'EXON_DUPLICATION_PARTIAL',3,'EFF'),(null,'EXON_INVERSION',3,'EFF'),(null,'EXON_INVERSION_PARTIAL',3,'EFF'),
               (null,'GENE_DELETED',3,'EFF'),(null,'GENE_DUPLICATION',3,'EFF'),(null,'GENE_FUSION',3,'EFF'),
               (null,'GENE_FUSION_HALF',3,'EFF'),(null,'GENE_FUSION_REVERESE',3,'EFF'),(null,'GENE_REARRANGEMENT',3,'EFF'),
               (null,'PROTEIN_PROTEIN_INTERACTION_LOCUS',3,'EFF'),(null,'PROTEIN_STRUCTURAL_INTERACTION_LOCUS',3,'EFF'),(null,'TRANSCRIPT_DELETED',3,'EFF'),
               (null,'coding_sequence_variant',0,'ANN'),(null,'chromosome',3,'ANN'),(null,'duplication',3,'ANN'),(null,'inversion',3,'ANN'),
               (null,'inframe_insertion',2,'ANN'),(null,'disruptive_inframe_insertion',2,'ANN'),(null,'inframe_deletion',2,'ANN'),(null,'disruptive_inframe_deletion',2,'ANN'),
               (null,'downstream_gene_variant',0,'ANN'),(null,'exon_variant',0,'ANN'),(null,'exon_loss_variant',3,'ANN'),(null,'frameshift_variant',3,'ANN'),
               (null,'gene_variant',0,'ANN'),(null,'feature_ablation',3,'ANN'),(null,'gene_fusion',3,'ANN'),(null,'bidirectional_gene_fusion',3,'ANN'),
               (null,'rearranged_at_DNA_level',3,'ANN'),(null,'intergenic_region',0,'ANN'),(null,'conserved_intergenic_variant',0,'ANN'),(null,'intragenic_variant',0,'ANN'),
               (null,'intron_variant',0,'ANN'),(null,'conserved_intron_variant',0,'ANN'),(null,'miRNA',0,'ANN'),(null,'missense_variant',2,'ANN'),
               (null,'initiator_codon_variant',1,'ANN'),(null,'stop_retained_variant',1,'ANN'),(null,'protein_protein_contact',3,'ANN'),(null,'structural_interaction_variant',3,'ANN'),
               (null,'rare_amino_acid_variant',3,'ANN'),(null,'splice_acceptor_variant',3,'ANN'),(null,'splice_donor_variant',3,'ANN'),(null,'splice_region_variant',1,'ANN'),
               (null,'stop_lost',3,'ANN'),(null,'5_prime_UTR_prematurestart_codon_gain_variant',1,'ANN'),(null,'start_lost',3,'ANN'),(null,'stop_gained',3,'ANN'),
               (null,'synonymous_variant',1,'ANN'),(null,'start_retained',1,'ANN'),(null,'transcript_variant',0,'ANN'),(null,'regulatory_region_variant',0,'ANN'),
               (null,'upstream_gene_variant',0,'ANN'),(null,'3_prime_UTR_variant',0,'ANN'),(null,'3_prime_UTR_truncation&exon_loss',2,'ANN'),(null,'5_prime_UTR_variant',0,'ANN'),
               (null,'5_prime_UTR_truncation&exon_loss_variant',2,'ANN'),(null,'sequence_feature&exon_loss_variant',0,'ANN'),
               (null,'non_coding_transcript_exon_variant',0,'ANN'),
               (null,'5_prime_UTR_premature_start_codon_gain_variant',1,'ANN'),
               (null,'conservative_inframe_deletion',2,'ANN'),
               (null,'conservative_inframe_insertion',2,'ANN'),
               (null,'non_coding_transcript_variant',0,'ANN')
        ");
=cut
        }

        my $val2 = $connect->selectrow_array("show tables like 'sys_track'");
        if ( ! defined $val2 ) {
        _dbexe("CREATE TABLE `sys_track` (
               `seq` int(11) NOT NULL AUTO_INCREMENT,
               `track` varchar(50) DEFAULT NULL,
               `chr` varchar(50) DEFAULT NULL,
               `id` varchar(50) DEFAULT NULL,
               `name` varchar(59) DEFAULT NULL,
               `parent` int(1) DEFAULT NULL,
               `type` varchar(40) DEFAULT NULL,
               `pos` int(11) DEFAULT NULL,
               `end` int(11) DEFAULT NULL,
               `strand` tinyint(4) DEFAULT NULL,
               `note` text ,
               `others` text ,
               PRIMARY KEY (`seq`),
               KEY `idx` (`id`),
               KEY `p_idx` (`chr`,`pos`)
            )ENGINE MyISAM");
        }

        my $val3 = $connect->selectrow_array("show tables like 'sys_snpblock'");
        if ( ! defined $val3 ) {
        _dbexe("CREATE TABLE `sys_snpblock` (
                  `accession` varchar(40) DEFAULT NULL,
                  `rate` float DEFAULT NULL,
                  `peak` varchar(20) DEFAULT NULL)");
        }
        
        my $val4 = $connect->selectrow_array("show tables like 'sys_condition'");
        if ( ! defined $val4 ) {
        _dbexe("CREATE TABLE `sys_condition` (
                  `condition_id` varchar(255),
                  `table_id` int(11) DEFAULT NULL,
                  PRIMARY KEY (`condition_id`))");
        }
        my $val5 = $connect->selectrow_array("show tables like 'sys_phenotype'");
        if ( ! defined $val5 ) {
                _dbexe("CREATE TABLE `sys_phenotype` (
                        `phenotype_id` varchar(40) NOT NULL,
                        `phenotype` varchar(40) NOT NULL,
                        PRIMARY KEY (`phenotype_id`)
                        )");
                _dbexe("CREATE TABLE `sys_phenotype_expression` (
                        `accession_id` varchar(40) NOT NULL,
                        `phenotype_id` varchar(40) NOT NULL,
                        `expression` varchar(100) NOT NULL,
                        PRIMARY KEY (`accession_id`,`phenotype_id`)
                        )");
                _dbexe("CREATE TABLE `sys_phenotype_prob` (
                        `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                        `phenotype_id` varchar(40) NOT NULL DEFAULT '',
                        `chr` varchar(20) NOT NULL DEFAULT '',
                        `pos` int(11) NOT NULL DEFAULT '0',
                        `prob` float DEFAULT NULL,
                        PRIMARY KEY (`id`),
                        UNIQUE KEY `id` (`id`),
                        KEY `phenotype_id` (`phenotype_id`,`chr`,`pos`),
                        KEY `pos_idx` (`pos`),
                        KEY `chr_pos_idx` (`chr`,`pos`)
                        )");
                _dbexe("CREATE TABLE `sys_phenotype_gene` (
                        `id` bigint(20) unsigned NOT NULL,
                        `phenotype_prob_id` int(10) NOT NULL,
                        `phenotype_id` varchar(40) DEFAULT NULL,
                        `chr` varchar(20) DEFAULT NULL,
                        `pos` int(11) DEFAULT NULL,
                        `prob` float DEFAULT NULL,
                        `Effect` text DEFAULT NULL,
                        `note` text DEFAULT NULL,
                        UNIQUE KEY `id` (`id`),
                        KEY `phenotype_id` (`phenotype_id`),
                        KEY `prob` (`prob`)
                        )");
                _dbexe("CREATE TABLE `id2eff` (
                        `id` bigint(20) NOT NULL,
                        `eff_grp` smallint(6) NOT NULL,
                        PRIMARY KEY (`id`,`eff_grp`)
                        )");
=head
		_dbexe("CREATE TABLE `sys_phenotype` (
                        `phenotype_id` varchar(40) NOT NULL,
                        `phenotype` varchar(40) NOT NULL,
                        PRIMARY KEY (`phenotype_id`))");
                _dbexe("CREATE TABLE `sys_phenotype_expression` (
                        `accession_id` varchar(40) NOT NULL,
                        `phenotype_id` varchar(40) NOT NULL,
                        `expression` varchar(100) NOT NULL,
                        PRIMARY KEY (`accession_id`,`phenotype_id`)
q
                        )");
                _dbexe("CREATE TABLE `sys_phenotype_prob` (
                        `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                        `phenotype_id` varchar(40) NOT NULL DEFAULT '',
                        `chr` varchar(20) NOT NULL DEFAULT '',
                        `pos` int(11) NOT NULL DEFAULT '0',
                        `prob` float DEFAULT NULL,
                        PRIMARY KEY (`id`),
                        UNIQUE KEY `id` (`id`),
                        KEY `phenotype_id` (`phenotype_id`,`chr`,`pos`),
                        KEY `pos_idx` (`pos`)
                        )");
                _dbexe("CREATE TABLE `sys_phenotype_gene` (
                        `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                        `phenotype_prob_id` int(10) NOT NULL,
                        `phenotype_id` varchar(40) DEFAULT NULL,
                        `chr` varchar(20) DEFAULT NULL,
                        `pos` int(11) DEFAULT NULL,
                        `prob` float DEFAULT NULL,
                        `gene_id` longtext NOT NULL,
                        `name` longtext DEFAULT NULL,
                        `note` text, PRIMARY KEY (`id`),
                        `Effect` text DEFAULT NULL,
                        `effnum` text DEFAULT NULL,
                        UNIQUE KEY `id` (`id`),
                        KEY `prob` (`prob`)
                        )");
                _dbexe("CREATE TABLE `id2eff` (
                        `id` bigint(20) NOT NULL,
                        `eff_grp` smallint(6) NOT NULL,
                        PRIMARY KEY (`id`,`eff_grp`)
                        )");
=cut
        }
    }
}

### Checking list table  #########################################
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 chromosome 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 oracel sequence ####################################
sub _check_exists_seq {
    my ($t) = @_;
    $t = uc($t);
    my $val = $connect->selectrow_array("select sequence_name from user_sequences where sequence_name = '$t'");
    return $val;
}

### Checking preference ####################################
sub _check_exists_pref {
    my ($t) = @_;
    $t = uc($t);
    my $val = $connect->selectrow_array("select pre_name from ctx_user_preferences where pre_name = '$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;
}

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

sub _dbexeIgnNamePh{
    my ($query) = @_;
    my $query_handle = $connect;
    $query_handle->{odbc_ignore_named_placeholders} = 1;
    $query_handle->do($query) or warn "Excute error: $DBI::errstr\n";#odbc_ignore_named_placeholders
}

#### 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 "\nFor MySQL database:\n";
    print "tasuke_init.pl -db [database] -u [user] -p [password]\n\n";
    print "For Oracle database:\n";
    print "tasuke_init.pl -db [sid or service_name] -b oracle -s [table_space_name] -u [user] -p [password]\n\n";
}

### SIGINT ###
sub _SIGINThandler{
    print "\n\nFinished.\n";
    _exit(1);
}
