#!/usr/bin/perl -w -I/opt/ep2stable/perl_lib

######################################################################
#
#  This file is part of GNU EPrints 2.
#  
#  Copyright (c) 2000-2004 University of Southampton, UK. SO17 1BJ.
#  
#  EPrints 2 is free software; you can redistribute it and/or modify
#  it under the terms of the GNU General Public License as published by
#  the Free Software Foundation; either version 2 of the License, or
#  (at your option) any later version.
#  
#  EPrints 2 is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.
#  
#  You should have received a copy of the GNU General Public License
#  along with EPrints 2; if not, write to the Free Software
#  Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
#
######################################################################

=pod

=head1 NAME

B<upgrade> - Upgrade the stucture of the database to be compatable with
a newer version of eprints.

If upgrading requires more than one step then the system will perform
the upgrade step by step. If a step fails then you can continue from
where it left off.

=head1 SYNOPSIS

B<upgrade> I<archiveid> [B<options>]

=head1 DESCRIPTION

Some versions of eprints require modifications to be made to the database
tables used by earlier versions. 

Run this script on each archive after upgrading the eprints software. 

=head1 ARGUMENTS

=over 8

=item I<archiveid> 

The ID of the EPrint archive to effect.

=back

=head1 OPTIONS

=over 8

=item B<--help>

Print a brief help message and exit.

=item B<--man>

Print the full manual page and then exit.

=item B<--quiet>

This option doesn't do anything. You REALLY don't want to run this
script without knowing what's happening.

=item B<--verbose>

Explain in detail what is going on. 
May be repeated for greater effect.

=item B<--version>

Output version information and exit.

=back   

=head1 AUTHOR

This is part of this EPrints 2 system. EPrints 2 is developed by Christopher Gutteridge.

=head1 VERSION

EPrints Version: 2.3.11.99.4-beta

=head1 CONTACT

For more information goto B<http://www.eprints.org/> which give information on mailing lists and the like.

Chris Gutteridge may be contacted at B<support@eprints.org>

Should you need a real world address for some reason, EPrints can be contacted in the real world at

 EPrints c/o Christopher Gutteridge
 Department of Electronics and Computer Science
 University of Southampton
 SO17 1BJ
 United Kingdom

=head1 COPYRIGHT

This file is part of GNU EPrints 2.

Copyright (c) 2000-2004 University of Southampton, UK. SO17 1BJ.

EPrints 2 is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.

EPrints 2 is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with EPrints 2; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

=cut



use EPrints::Database;
use EPrints::Session;
use EPrints::Subject;

use strict;
use Getopt::Long;
use Pod::Usage;

my $TARGET = $EPrints::Database::DBVersion;

my $verbose = 0;
my $version = 0;
my $quiet = 0;
my $help = 0;
my $man = 0;

GetOptions( 
	'help|?' => \$help,
	'man' => => \$man,
	'verbose+' => \$verbose,
	'version' => \$version,
	'silent' => \$quiet,
	'quiet' => \$quiet
) || pod2usage( 2 );
EPrints::Utils::cmd_version( "upgrade" ) if $version;
pod2usage( 1 ) if $help;
pod2usage( -exitstatus => 0, -verbose => 2 ) if $man;
pod2usage( 2 ) if( scalar @ARGV != 1 ); 

my $noise = 1;
$noise = 0 if( $quiet );
$noise = 1+$verbose if( $verbose );

# Set STDOUT to auto flush (without needing a \n)
$|=1;

my $session = new EPrints::Session( 1, $ARGV[0], $noise, 1 );
exit( 1 ) unless defined $session;

if( $noise>=1 ) { print "What is the current compatability of the DB?\n"; }
my $db = $session->get_db();
my $dbversion = $db->get_version();
if( !defined $dbversion )
{
	if( $noise>=1 ) { print "...No version flag. Must be pre 2.1\n"; }
	$dbversion = "2.0";
	if( $noise>=1 ) { print "Setting version to be 2.0\n"; }
	$db->create_version_table;
	$db->set_version( $dbversion );
}
else
{
	print "...DB Tables compatable with ".$dbversion."\n"; 
}	
if( $noise>=1 ) 
{ 
	print "Target compatablity is ".$TARGET."\n"; 
}

my $rootdbh = undef;

if( $dbversion eq "2.0" )
{
	my $sql;
	print <<END;
======================================================================
About to upgrade tables from 2.0 to 2.1

This will:

 * Erase the subscription tables. (If you have an active subscription
   service running on this archive then somethings wrong. Stop right
   now and figure it out.

 * Rebuild the subscription dataset tables how v2.1 wants them.

 * Set the subscription ID counter to zero.

 * Alter the columns "commentary", "replacedby" and "succeeds" in the
   tables "archive","deletion","inbox" and "buffer" from VARCHAR to
   INTEGER. These fields should have been INT's in the first place.

Are you sure you want to do this? (yes/no)
END
	
	my $input = <STDIN>;
	chomp( $input );
	if( $input ne "yes" )
	{
		print "Aborting then.\n";
		$session->terminate();
		exit( 1 );
	}

	$rootdbh = &getrootdbh( $session ) unless( defined $rootdbh );

	my $ok = 1;
	$ok = $ok && defined $rootdbh;

	print "Dropping old subscription tables.\n";
	# drop all the old subscription tables
	my @tables = $db->get_tables;
	foreach my $table ( @tables )
	{
		if( $table =~ m/^subscription/ )
		{
			$sql = "DROP TABLE ".$table;
			$ok = $ok && $db->do( $sql );
		}
	}
	print "Making new subscription tables.\n";
	my $subs_ds = $session->get_archive->get_dataset( "subscription" );
	$ok = $ok && $db->create_dataset_tables( $subs_ds );
	print "Creating subscriptionid counter.\n";
	$sql = "DELETE FROM counters WHERE countername = 'subscriptionid'"; 
	$ok = $ok && $db->do( $sql );
	$sql = "INSERT INTO counters (countername,counter) VALUES ".
		"( 'subscriptionid' , 0 )";
	$ok = $ok && $db->do( $sql );
	foreach my $table_id ( "archive","inbox","buffer","deletion" )
	{
		$sql = "SELECT eprintid,succeeds,replacedby,commentary FROM ".
			$table_id;
		my $sth = $db->prepare( $sql );
		$ok = $ok && $db->execute( $sth, $sql );
		my $file = "/tmp/valuedump-".$session->get_archive->get_id."-".
			$table_id;
		open( VDUMP, ">$file" );
		print VDUMP "eprintid,succeeds,replacedby,commentary\n";
		my @row;
		while( @row = $sth->fetchrow_array )
		{
			foreach( @row ) 
			{
				$_ = 'NULL' if( !defined $_ );
			}
			print VDUMP join( ",", @row )."\n";
		}
		close VDUMP;
        	$sth->finish;
		print "Saved backup of affected fileds to $file\n";
		print "Altering columns of ".$table_id."\n";
		foreach my $col_id ( "succeeds","replacedby","commentary" )
		{
			$sql = "ALTER TABLE ".$table_id." MODIFY ".
				$col_id." INTEGER";
			$ok = $ok && $rootdbh->do( $sql );
		}
	}
	if( !$ok )
	{
		print "\nFailed.\n";
		print "Please investigate cause of errors then try again.\n";
	}
	else
	{
		print "\n2.0 -> 2.1 done!\n";
		$dbversion = "2.1";
		$db->set_version( $dbversion );
	}
}

if( $dbversion eq "2.1" )
{
	my $sql;
	print <<END;
======================================================================
About to upgrade tables from 2.1 to 2.2

This will:

 * in dataset "subscription"
    - add a new boolean field "mailempty"

 * in dataset "user"
    - remove the field "editorsubjects"
    - add the following fields:
       + editperms (search) 
       + mailempty (boolean) 
       + frequency (set) 

Are you sure you want to do this? (yes/no)
END
	
	my $input = <STDIN>;
	chomp( $input );
	if( $input ne "yes" )
	{
		print "Aborting then.\n";
		$session->terminate();
		exit( 1 );
	}

	$rootdbh = &getrootdbh( $session ) unless( defined $rootdbh );

	my $ok = 1;
	$ok = $ok && defined $rootdbh;

	my @tables = $db->get_tables;

	# add "mailempty" to subscription

	$sql = "ALTER TABLE subscription ADD mailempty set('TRUE','FALSE') default NULL AFTER frequency";
	$ok = $ok && $rootdbh->do( $sql );
	foreach my $table ( @tables )
	{
		if( $table =~ m/^subscription__ordervalues_.*$/ )
		{
			$sql = "ALTER TABLE $table ADD mailempty text AFTER frequency",
			$ok = $ok && $rootdbh->do( $sql );
		}
	}


	# remove users "editorsubjects"

	$sql = "DROP TABLE users_editorsubjects";
	$ok = $ok && $rootdbh->do( $sql );
	foreach my $table ( @tables )
	{
		if( $table =~ m/^users__ordervalues_.*$/ )
		{
			$sql = "ALTER TABLE $table DROP editorsubjects";
			$ok = $ok && $rootdbh->do( $sql );
		}
	}


	# add "editperms","frequency" & "mailempty" to subscription

	$sql = "CREATE TABLE users_editperms ( userid INT NOT NULL, pos INT, editperms TEXT default NULL, KEY userid (userid), KEY pos (pos) )  ";
	$ok = $ok && $rootdbh->do( $sql );

	$sql = "ALTER TABLE users ADD frequency varchar(255) default NULL AFTER lang";
	$ok = $ok && $rootdbh->do( $sql );
	$sql = "ALTER TABLE users ADD mailempty set('TRUE','FALSE') default NULL AFTER frequency";
	$ok = $ok && $rootdbh->do( $sql );
	foreach my $table ( @tables )
	{
		if( $table =~ m/^users__ordervalues_.*$/ )
		{
			$sql = "ALTER TABLE $table ADD editperms TEXT AFTER lang";
			$ok = $ok && $rootdbh->do( $sql );
			$sql = "ALTER TABLE $table ADD frequency TEXT AFTER editperms";
			$ok = $ok && $rootdbh->do( $sql );
			$sql = "ALTER TABLE $table ADD mailempty TEXT AFTER frequency";
			$ok = $ok && $rootdbh->do( $sql );
		}
	}


#	# add "hash" to document
#
#	$sql = "ALTER TABLE document ADD hash TEXT default NULL AFTER main";
#	$ok = $ok && $rootdbh->do( $sql );
#	foreach my $table ( @tables )
#	{
#		if( $table =~ m/^document__ordervalues_.*$/ )
#		{
#			$sql = "ALTER TABLE $table ADD hash text AFTER main",
#			$ok = $ok && $rootdbh->do( $sql );
#		}
#	}


	if( !$ok )
	{
		print "\nFailed.\n";
		print "Please investigate cause of errors then try again.\n";
	}
	else
	{
		print "\n2.1 -> 2.2 done!\n";
		$dbversion = "2.2";
		$db->set_version( $dbversion );
	}
}

if( $dbversion eq "2.2" )
{
	my $sql;
	print <<END;
======================================================================
About to upgrade tables from 2.2 to 2.3

This will:

 * Remove the no longer used "rindex" tables. 

 * Grant ALTER permissions to the database user. 

Are you sure you want to do this? (yes/no)
END
	
	my $input = <STDIN>;
	chomp( $input );
	if( $input ne "yes" )
	{
		print "Aborting then.\n";
		$session->terminate();
		exit( 1 );
	}

	$rootdbh = &getrootdbh( $session ) unless( defined $rootdbh );

	my $ok = 1;
	$ok = $ok && defined $rootdbh;

	my @tables = $db->get_tables;

	# remove rindex tables

	foreach my $table ( @tables )
	{
		if( $table =~ m/__rindex$/ )
		{
			$sql = "DROP TABLE $table";
			$ok = $ok && $rootdbh->do( $sql );
		}
	}

	# Grant ALTER

	my $dbname = $session->get_archive->get_conf( "dbname" );
	my $dbuser = $session->get_archive->get_conf( "dbuser" );
	my $SQL = 'GRANT ALTER ON '.$dbname.'.* TO '.$dbuser.'@localhost';
	$rootdbh->do( $SQL );

	if( !$ok )
	{
		print "\nFailed.\n";
		print "Please investigate cause of errors then try again.\n";
	}
	else
	{
		print "\n2.2 -> 2.3 done!\n";
		$dbversion = "2.3";
		$db->set_version( $dbversion );
	}
}
if( $TARGET eq $dbversion)
{
	if( $noise>=1 )
	{
		print "Target compatablity reached.\n";
	}
}
else
{
	print <<END;
**********************************************************************
WARNING: Target compatablity NOT reached. That probably means 
something bad happend.
**********************************************************************
END
}

if( defined $rootdbh )
{
	$rootdbh->disconnect;
}

$session->terminate();
exit;


sub getrootdbh
{
	my( $session ) = @_;
	print <<END;

We will need to do some stuff to the database as root. Please enter
the MySQL root password. (Warning: It will not be ****'d out when you 
type it).

END
	my $mysqlrootpass = EPrints::Utils::get_input( '^.*$', "MySQL Root Password" );

	print "Connecting to the database [as root]\n";
        my $dbh = DBI->connect(
                EPrints::Database::build_connection_string(
                        dbname=>$session->get_archive->get_conf("dbname"),
                        dbsock=>$session->get_archive->get_conf("dbsock"),
                        dbport=>$session->get_archive->get_conf("dbport"),
                        dbhost=>$session->get_archive->get_conf("dbhost") ),
                "root",
                $mysqlrootpass );

	return $dbh;
}
