#!/usr/bin/perl -w

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

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

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

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

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

my $connect;
if($RDBMS eq "oracle"){
    if(!$TABLESPACE || !$TMPTABLESPACE){
        _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 creates a role of grant for Oracle database.";
    print "And set the role to user for only viewing.";
    exit;
}

if($RESTORE == 1){
    _remove_role();
}else{
    _create_role();
}
print "Finished!\n";
_exit();
    
### Creating role and set the role to the user.  #########################################
sub _create_role{
    if(!_check_exist_role()){
        my $ref = _get_list();
        my @accessions = @$ref;
        print "Attaching roles to $DESTUSER...\n";
        _dbexe("GRANT connect to $DESTUSER");
        _dbexe("ALTER user $DESTUSER default tablespace $TABLESPACE temporary tablespace $TMPTABLESPACE");
        print "Creating a role...\n";
        _dbexe("CREATE ROLE TSK_ACCESS");
        _dbexe("GRANT SELECT ON sys_accession TO TSK_ACCESS");
        _dbexe("GRANT SELECT ON sys_fasta TO TSK_ACCESS");
        _dbexe("GRANT SELECT ON sys_chromosome TO TSK_ACCESS");
        _dbexe("GRANT SELECT ON sys_track TO TSK_ACCESS");
        _dbexe("GRANT SELECT ON sys_snpeff TO TSK_ACCESS");
        foreach my $x(@accessions){
            _dbexe("GRANT SELECT ON ".$x."_variant TO TSK_ACCESS");
            _dbexe("GRANT SELECT ON ".$x."_accession TO TSK_ACCESS");
            _dbexe("GRANT SELECT ON ".$x."_accession_cstm TO TSK_ACCESS");
        }
        _dbexe("GRANT TSK_ACCESS TO $DESTUSER");
    }else{
       #pass
    }
}

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

sub _remove_role{
    if(_check_exist_role()){
        _dbexe("DROP ROLE TSK_ACCESS");
        print "The role was droped from the database.\n";
    }
}

sub _check_exist_role{
    my $val = $connect->selectrow_array("SELECT granted_role from user_role_privs where granted_role='TSK_ACCESS'");
    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, $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_grantForViewer.pl -db [database] -u [user] -p [password] -s [tablespace name] -t [temporary tablespace name] -v [user name for the setting] \n\n";
    print "* -r : Delete the permission \n\n";
}
