#
# Query the DPM database and updates the 's_uid' and 'protocol' fields
#

package UpdateDpmDatabase;

use DBI;
use Common;
use strict;
use warnings; 

sub find_and_update_s_uid_field($$) {
    my ($dbh_dpns, $dbh_dpm) = @_;

    my $select;
    my $count = 0;

    my ($userid, $username);
    $select = $dbh_dpns->prepare("
	SELECT userid, username 
        FROM Cns_userinfo");
    $select->execute();

    while(($userid, $username) = $select->fetchrow_array()) {
	Common::updateFieldInDpmWhereString($dbh_dpm, "dpm_space_reserv", "s_uid", $userid, "client_dn", $username);
	$count = $count+1;
    }

    $select->finish;
    $dbh_dpm->commit;
    return $count;
}

sub find_and_update_protocol_field($) {
    my ($dbh_dpm) = @_;

    my ($select1, $select2, $select3);
    my $count = 0;

    my ($r_token, $r_type, $protocol);

    # in dpm_req, "get" requests
    $select1 = $dbh_dpm->prepare("
	SELECT r_token, protocol
	FROM dpm_req
	WHERE r_type = 'G' OR r_type = 'B'");
    $select1->execute();

    while (($r_token, $protocol) = $select1->fetchrow_array()) {
	Common::updateFieldInDpmWhereString($dbh_dpm, "dpm_get_filereq", "protocol", $protocol, "r_token", $r_token);
	$count = $count+1;
    }
    $select1->finish;
    $dbh_dpm->commit;

    # in dpm_req, "put" requests
    $select2 = $dbh_dpm->prepare("
	SELECT r_token, protocol
	FROM dpm_req
	WHERE r_type = 'P'");
    $select2->execute();
    $dbh_dpm->commit;

    while (($r_token, $protocol) = $select2->fetchrow_array()) {
	Common::updateFieldInDpmWhereString($dbh_dpm, "dpm_put_filereq", "protocol", $protocol, "r_token", $r_token);
	$count = $count+1;
    }
    $select2->finish;
    $dbh_dpm->commit;

    # in dpm_pending_req
    $select3 = $dbh_dpm->prepare("
	SELECT r_token, protocol
	FROM dpm_pending_req");
    $select3->execute();

    while (($r_token, $protocol) = $select3->fetchrow_array()) {
	Common::updateFieldInDpmWhereString($dbh_dpm, "dpm_get_filereq", "protocol", $protocol, "r_token", $r_token);
	Common::updateFieldInDpmWhereString($dbh_dpm, "dpm_put_filereq", "protocol", $protocol, "r_token", $r_token);
	$count = $count+1;
    }
    $select3->finish;
    $dbh_dpm->commit;

    return ($count);
}

sub update_defpintime($) {
    my ($dbh_dpm) = @_;

    my $select;
    my $count = 0;

    my ($defpintime, $poolname, $new_defpintime);

    $select = $dbh_dpm->prepare("
	SELECT defpintime, poolname
	FROM dpm_pool");
    $select->execute();

    while (($defpintime, $poolname) = $select->fetchrow_array()) {
	if ($defpintime == 0) {
		$new_defpintime = 7200;
		Common::updateNumberInDpmWhereString($dbh_dpm, "dpm_pool", "defpintime", $new_defpintime, "poolname", $poolname);
		$count = $count+1;
	}
    }
    $select->finish;
    $dbh_dpm->commit;

    return ($count);
}

sub modify_dpm_db_before_updating_fields_mysql($$) {
	my ($dbh_dpm, $lifetime) = @_;

# dpm_pool table
	$dbh_dpm->do ("ALTER TABLE dpm_pool ADD def_lifetime INTEGER");
	$dbh_dpm->do ("ALTER TABLE dpm_pool ADD max_lifetime INTEGER");
	$dbh_dpm->do ("ALTER TABLE dpm_pool ADD maxpintime INTEGER");
	$dbh_dpm->do ("ALTER TABLE dpm_pool ADD mig_policy VARCHAR(15) BINARY");
	$dbh_dpm->do ("ALTER TABLE dpm_pool ADD ret_policy CHAR(1)");
	$dbh_dpm->do ("UPDATE dpm_pool SET def_lifetime=604800, max_lifetime=2592000, maxpintime=43200");
	$dbh_dpm->do ("UPDATE dpm_pool SET mig_policy='none', ret_policy='R'");

# dpm_get_filereq table
	$dbh_dpm->do ("ALTER TABLE dpm_get_filereq ADD protocol VARCHAR(7) BINARY");
	$dbh_dpm->do ("ALTER TABLE dpm_get_filereq ADD ret_policy CHAR(1)");
	$dbh_dpm->do ("UPDATE dpm_get_filereq SET ret_policy='_'");

# dpm_put_filereq table
	$dbh_dpm->do ("ALTER TABLE dpm_put_filereq ADD protocol VARCHAR(7) BINARY");
	$dbh_dpm->do ("ALTER TABLE dpm_put_filereq ADD f_lifetime INTEGER");
	$dbh_dpm->do ("ALTER TABLE dpm_put_filereq ADD ret_policy CHAR(1)");
	$dbh_dpm->do ("ALTER TABLE dpm_put_filereq ADD ac_latency CHAR(1)");
	$dbh_dpm->do ("UPDATE dpm_put_filereq SET ret_policy='_', ac_latency='O'");
	$dbh_dpm->do ("UPDATE dpm_put_filereq SET f_lifetime=86400");
	$dbh_dpm->do ("UPDATE dpm_put_filereq SET f_lifetime=2147483647 WHERE f_type = 'P'");
        $dbh_dpm->do ("UPDATE dpm_put_filereq SET f_lifetime=$lifetime WHERE f_type = 'V' AND status=4096"); # DPM_DONE status
	$dbh_dpm->do ("UPDATE dpm_put_filereq SET f_lifetime=$lifetime WHERE f_type = 'D' AND status=4096"); # DPM_DONE status

# dpm_req
	$dbh_dpm->do ("DELETE FROM dpm_req WHERE r_type = 'C'");

# dpm_pending_req
	$dbh_dpm->do ("DELETE FROM dpm_pending_req WHERE r_type = 'C'");

# dpm_copy_filereq table
	$dbh_dpm->do ("DELETE FROM dpm_copy_filereq"); # as copy is not supported yet, delete all copy requests
	$dbh_dpm->do ("ALTER TABLE dpm_copy_filereq CHANGE lifetime f_lifetime INTEGER");
	$dbh_dpm->do ("ALTER TABLE dpm_copy_filereq ADD ret_policy CHAR(1)");
	$dbh_dpm->do ("ALTER TABLE dpm_copy_filereq ADD ac_latency CHAR(1)");
	$dbh_dpm->do ("UPDATE dpm_copy_filereq SET ret_policy='_', ac_latency='O'");

# dpm_space_reserv table
	$dbh_dpm->do ("ALTER TABLE dpm_space_reserv MODIFY u_space BIGINT");
	$dbh_dpm->do ("ALTER TABLE dpm_space_reserv ADD s_uid INTEGER");
	$dbh_dpm->do ("ALTER TABLE dpm_space_reserv ADD s_gid INTEGER");
	$dbh_dpm->do ("ALTER TABLE dpm_space_reserv ADD ret_policy CHAR(1)");
	$dbh_dpm->do ("ALTER TABLE dpm_space_reserv ADD ac_latency CHAR(1)");
	$dbh_dpm->do ("ALTER TABLE dpm_space_reserv ADD poolname VARCHAR(15)");
	$dbh_dpm->do ("UPDATE dpm_space_reserv SET s_gid=0, ret_policy='R', ac_latency='O'");
	$dbh_dpm->commit;
}

sub modify_cns_db_mysql($$) {
	my ($dbh_dpns, $lifetime) = @_;

	$dbh_dpns->do ("ALTER TABLE Cns_file_metadata MODIFY name VARCHAR(255) BINARY");
	$dbh_dpns->do ("ALTER TABLE Cns_file_replica ADD ctime INTEGER");
	$dbh_dpns->do ("ALTER TABLE Cns_file_replica ADD ltime INTEGER");
	$dbh_dpns->do ("ALTER TABLE Cns_file_replica ADD r_type CHAR(1) BINARY");
	$dbh_dpns->do ("ALTER TABLE Cns_file_replica ADD setname VARCHAR(36) BINARY");
	$dbh_dpns->do ("ALTER TABLE Cns_file_replica ADD INDEX (host)");
	$dbh_dpns->do ("UPDATE Cns_file_replica SET ltime=0x7FFFFFFF WHERE f_type = 'P'");
	$dbh_dpns->do ("UPDATE Cns_file_replica SET ltime=$lifetime WHERE f_type = 'V'");
	$dbh_dpns->do ("UPDATE Cns_file_replica SET ltime=$lifetime WHERE f_type = 'D'");
	$dbh_dpns->commit;
}

sub modify_cns_db_oracle($$) {
	my ($dbh_dpns, $lifetime) = @_;

	$dbh_dpns->do ("ALTER TABLE Cns_file_metadata MODIFY name VARCHAR2(255)");
	$dbh_dpns->do ("ALTER TABLE Cns_file_replica ADD ctime NUMBER(10)");
	$dbh_dpns->do ("ALTER TABLE Cns_file_replica ADD ltime NUMBER(10)");
	$dbh_dpns->do ("ALTER TABLE Cns_file_replica ADD r_type CHAR(1)");
	$dbh_dpns->do ("ALTER TABLE Cns_file_replica ADD setname VARCHAR2(36)");
	$dbh_dpns->do ("CREATE INDEX replica_host ON Cns_file_replica(host)");
	$dbh_dpns->do ("UPDATE Cns_file_replica SET ltime=2147483647 WHERE f_type = 'P'");
	$dbh_dpns->do ("UPDATE Cns_file_replica SET ltime=$lifetime WHERE f_type = 'V'");
	$dbh_dpns->do ("UPDATE Cns_file_replica SET ltime=$lifetime WHERE f_type = 'D'");
	$dbh_dpns->commit;
}

sub modify_dpm_db_before_updating_fields_oracle($$) {
        my ($dbh_dpm, $lifetime) = @_;

# dpm_pool table
	$dbh_dpm->do ("ALTER TABLE dpm_pool ADD def_lifetime NUMBER(10)");
	$dbh_dpm->do ("ALTER TABLE dpm_pool ADD max_lifetime NUMBER(10)");
	$dbh_dpm->do ("ALTER TABLE dpm_pool ADD maxpintime NUMBER(10)");
	$dbh_dpm->do ("ALTER TABLE dpm_pool ADD mig_policy VARCHAR2(15)");
	$dbh_dpm->do ("ALTER TABLE dpm_pool ADD ret_policy CHAR(1)");
	$dbh_dpm->do ("UPDATE dpm_pool SET def_lifetime=604800, max_lifetime=2592000, maxpintime=43200");
	$dbh_dpm->do ("UPDATE dpm_pool SET mig_policy='none', ret_policy='R'");

# dpm_get_filereq table
	$dbh_dpm->do ("ALTER TABLE dpm_get_filereq ADD protocol VARCHAR2(7)");
	$dbh_dpm->do ("ALTER TABLE dpm_get_filereq ADD ret_policy CHAR(1)");
	$dbh_dpm->do ("UPDATE dpm_get_filereq SET ret_policy='-'");

# dpm_put_filereq table
	$dbh_dpm->do ("ALTER TABLE dpm_put_filereq ADD protocol VARCHAR2(7)");
	$dbh_dpm->do ("ALTER TABLE dpm_put_filereq ADD f_lifetime NUMBER(10)");
	$dbh_dpm->do ("ALTER TABLE dpm_put_filereq ADD ret_policy CHAR(1)");
	$dbh_dpm->do ("ALTER TABLE dpm_put_filereq ADD ac_latency CHAR(1)");
	$dbh_dpm->do ("UPDATE dpm_put_filereq SET ret_policy='_', ac_latency='O'");
	$dbh_dpm->do ("UPDATE dpm_put_filereq SET f_lifetime=86400");
	$dbh_dpm->do ("UPDATE dpm_put_filereq SET f_lifetime=2147483647 WHERE f_type = 'P'");
	$dbh_dpm->do ("UPDATE dpm_put_filereq SET f_lifetime=$lifetime WHERE f_type = 'V' and status=4096"); # DPM_DONE status
	$dbh_dpm->do ("UPDATE dpm_put_filereq SET f_lifetime=$lifetime WHERE f_type = 'D' and status=4096"); # DPM_DONE status

# dpm_req
	$dbh_dpm->do ("DELETE FROM dpm_req WHERE r_type = 'C'");

# dpm_pending_req
	$dbh_dpm->do ("DELETE FROM dpm_pending_req WHERE r_type = 'C'");

# dpm_copy_filereq table
        $dbh_dpm->do ("DELETE FROM dpm_copy_filereq"); # as copy is not supported yet, delete all copy requests
	$dbh_dpm->do ("ALTER TABLE dpm_copy_filereq RENAME COLUMN lifetime TO f_lifetime");
	$dbh_dpm->do ("ALTER TABLE dpm_copy_filereq ADD ret_policy CHAR(1)");
	$dbh_dpm->do ("ALTER TABLE dpm_copy_filereq ADD ac_latency CHAR(1)");
	$dbh_dpm->do ("UPDATE dpm_copy_filereq SET ret_policy='_', ac_latency='O'");

# dpm_space_reserv table
	$dbh_dpm->do ("ALTER TABLE dpm_space_reserv ADD s_uid NUMBER(10)");
	$dbh_dpm->do ("ALTER TABLE dpm_space_reserv ADD s_gid NUMBER(10)");
	$dbh_dpm->do ("ALTER TABLE dpm_space_reserv ADD ret_policy CHAR(1)");
	$dbh_dpm->do ("ALTER TABLE dpm_space_reserv ADD ac_latency CHAR(1)");
	$dbh_dpm->do ("ALTER TABLE dpm_space_reserv ADD poolname VARCHAR2(15)");
	$dbh_dpm->do ("UPDATE dpm_space_reserv SET s_gid=0, ret_policy='R', ac_latency='O'");
	$dbh_dpm->commit;
}

sub modify_db_after_updating_fields_mysql($$) {
	my ($dbh_dpns, $dbh_dpm) = @_;

	$dbh_dpm->do ("ALTER TABLE dpm_pending_req DROP protocol");
	$dbh_dpm->do ("ALTER TABLE dpm_req DROP protocol");
	$dbh_dpm->do ("ALTER TABLE dpm_pool DROP put_retenp");
	$dbh_dpm->do ("ALTER TABLE dpm_put_filereq DROP sr_expiretime");	
	$dbh_dpm->do ("DELETE FROM schema_version");
	$dbh_dpm->do ("INSERT INTO schema_version VALUES(3, 0, 0)");
        $dbh_dpm->commit;       # first commit before the indexes, in case the indexes already exist

	$dbh_dpns->do ("DELETE FROM schema_version");
	$dbh_dpns->do ("INSERT INTO schema_version VALUES(3, 0, 0)");
	$dbh_dpns->do ("ALTER TABLE Cns_file_replica DROP INDEX sfn");		# drop unique constraint 
	$dbh_dpns->do ("ALTER TABLE Cns_file_replica ADD INDEX (sfn(255))");	# and replace it with an index
	$dbh_dpns->commit;

	$dbh_dpm->do ("CREATE INDEX G_PFN_IDX ON dpm_get_filereq(pfn(255))");
	$dbh_dpm->do ("CREATE INDEX P_PFN_IDX ON dpm_put_filereq(pfn(255))");
	$dbh_dpm->commit;
}

sub modify_db_after_updating_fields_oracle($$) {
	my ($dbh_dpns, $dbh_dpm) = @_;

	$dbh_dpm->do ("ALTER TABLE dpm_pending_req DROP COLUMN protocol");
	$dbh_dpm->do ("ALTER TABLE dpm_req DROP COLUMN protocol");
	$dbh_dpm->do ("ALTER TABLE dpm_pool DROP COLUMN put_retenp");
	$dbh_dpm->do ("ALTER TABLE dpm_put_filereq DROP COLUMN sr_expiretime");
	$dbh_dpm->do ("CREATE INDEX G_PFN_IDX ON dpm_get_filereq(pfn)");
	$dbh_dpm->do ("CREATE INDEX P_PFN_IDX ON dpm_put_filereq(pfn)");

	$dbh_dpm->do ("DELETE FROM schema_version");
	$dbh_dpm->do ("INSERT INTO schema_version VALUES(3, 0, 0)");
	$dbh_dpm->commit;       # first commit before the indexes, in case the indexes already exist

	$dbh_dpns->do ("DELETE FROM schema_version");
	$dbh_dpns->do ("INSERT INTO schema_version VALUES(3, 0, 0)");
	$dbh_dpns->commit;

	$dbh_dpm->do ("CREATE INDEX G_PFN_IDX ON dpm_get_filereq(pfn)");
        $dbh_dpm->do ("CREATE INDEX P_PFN_IDX ON dpm_put_filereq(pfn)");
	$dbh_dpm->commit;
}

1;