#!/usr/bin/perl -w
#----------------------------------------------------------------------
# copyright (C) 2002-20085 Mitel Networks Corporation
# copyright (C) 2002-2008 SME Server, INC
# 
# This program 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.
# 
# This program 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 this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307  USA
#----------------------------------------------------------------------

use strict;
use DBI;
use esmith::ConfigDB;
use esmith::util;

# Exit early if there is nothing to do
die("horde db must exist") unless ( -d "/var/lib/mysql/horde/");
die("horde db must exist") unless ( -f "/var/lib/mysql/horde/horde_datatree.frm");

# This is a translation of the script 'mysql_upgrade_1.1_to_1.2.sql
# that is safe to run multiple times, and which can be run on a 1.2
# installation without barfing.

my $conf = esmith::ConfigDB->open_ro 
    or die "Can't open configuration database: $!\n";
our $username       = 'root';
our $password       = esmith::util::LdapPassword();
our $horde_DATABASE = 'horde';
our $dbi_options = {RaiseError => 1, ChopBlanks => 1, AutoCommit => 1};

my $db_hordehandle = DBI->connect
                   ("DBI:mysql:$horde_DATABASE",
                     $username, $password, $dbi_options )
                     || die ("Connection error: $DBI::errstr");


# These are all safe to run multiple times

my @statements = (
    "ALTER TABLE horde_datatree CHANGE COLUMN datatree_id datatree_id INT(11) UNSIGNED NOT NULL",
    "ALTER TABLE horde_datatree_attributes CHANGE COLUMN datatree_id datatree_id INT(11) UNSIGNED NOT NULL",
    "ALTER TABLE horde_vfs CHANGE COLUMN vfs_id vfs_id INT(11) UNSIGNED NOT NULL",
    "ALTER TABLE horde_prefs CHANGE COLUMN pref_uid pref_uid VARCHAR(200) NOT NULL",
    "ALTER TABLE horde_tokens CHANGE COLUMN token_address token_address VARCHAR(100) NOT NULL",
    "ALTER TABLE horde_histories CHANGE COLUMN history_id history_id INT UNSIGNED NOT NULL",
    "ALTER TABLE horde_sessionhandler CHANGE COLUMN session_lastmodified session_lastmodified BIGINT NOT NULL",
    "ALTER TABLE horde_locks CHANGE COLUMN lock_type lock_type TINYINT UNSIGNED NOT NULL",
    "DELETE FROM horde_prefs WHERE pref_name = 'last_login' AND pref_scope = 'imp'",
);

foreach my $statement (@statements)
{
    $statement =
        $db_hordehandle->prepare("$statement")
            or die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}

# We now need to create some columns, but we need to first check
# whether they exist already
my $sth_horde_datatree = $db_hordehandle->prepare("show columns from horde_datatree");
$sth_horde_datatree->execute;
my $horde_datatree = $sth_horde_datatree->fetchall_hashref('Field');

my $sth_horde_datatree_attributes = $db_hordehandle->prepare("show columns from horde_datatree_attributes");
$sth_horde_datatree_attributes->execute;
my $horde_datatree_attributes = $sth_horde_datatree_attributes->fetchall_hashref('Field');

my $sth_horde_vfs = $db_hordehandle->prepare("show columns from horde_vfs");
$sth_horde_vfs->execute;
my $horde_vfs = $sth_horde_vfs->fetchall_hashref('Field');

my $sth_horde_histories = $db_hordehandle->prepare("show columns from horde_histories");
$sth_horde_histories->execute;
my $horde_histories = $sth_horde_histories->fetchall_hashref('Field');

my $sth_horde_sessionhandler = $db_hordehandle->prepare("show columns from horde_sessionhandler");
$sth_horde_sessionhandler->execute;
my $horde_sessionhandler = $sth_horde_sessionhandler->fetchall_hashref('Field');

my $sth_horde_syncml_map = $db_hordehandle->prepare("show columns from horde_syncml_map");
$sth_horde_syncml_map->execute;
my $horde_syncml_map = $sth_horde_syncml_map->fetchall_hashref('Field');

my $sth_horde_alarms = $db_hordehandle->prepare("show columns from horde_alarms");
$sth_horde_alarms->execute;
my $horde_alarms = $sth_horde_alarms->fetchall_hashref('Field');

my $sth_horde_prefs = $db_hordehandle->prepare("show columns from horde_prefs");
$sth_horde_prefs->execute;
my $horde_prefs = $sth_horde_prefs->fetchall_hashref('Field');

my $sth_horde_users = $db_hordehandle->prepare("show columns from horde_users");
$sth_horde_users->execute;
my $horde_users = $sth_horde_users->fetchall_hashref('Field');

my $sth_horde_groups_members  = $db_hordehandle->prepare("show columns from horde_groups_members ");
$sth_horde_groups_members ->execute;
my $horde_groups_members  = $sth_horde_groups_members ->fetchall_hashref('Field');

my $sth_horde_syncml_anchors  = $db_hordehandle->prepare("show columns from horde_syncml_anchors ");
$sth_horde_syncml_anchors ->execute;
my $horde_syncml_anchors  = $sth_horde_syncml_anchors ->fetchall_hashref('Field');

my $sth_horde_signups  = $db_hordehandle->prepare("show columns from horde_signups ");
$sth_horde_signups ->execute;
my $horde_signups  = $sth_horde_signups ->fetchall_hashref('Field');


unless (defined $horde_users->{user_soft_expiration_date})
{
    # We need to be careful about this one as it will fail if the 
    # column exists, so we check the error. 
    my $statement = 'ALTER TABLE horde_users ADD COLUMN user_soft_expiration_date INT';
    $statement = $db_hordehandle->prepare($statement) or 
        die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}

unless (defined $horde_users->{user_hard_expiration_date})
{
    # We need to be careful about this one too 
    my $statement = 'ALTER TABLE horde_users ADD COLUMN user_hard_expiration_date INT';
    $statement = $db_hordehandle->prepare($statement) or
        die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}

unless (!defined $horde_signups->{signup_email})
{
    # We need to be careful about this one as it will fail if the
    # column exists, so we check the error.
    my $statement =
        'ALTER TABLE horde_signups DROP COLUMN signup_email';
    $statement = $db_hordehandle->prepare($statement) or
        die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}

# Create an index for group_uid if needed
unless ($horde_groups_members ->{group_uid}->{Key})
{
    my $statement = 'alter table horde_groups_members  ' .
                    'add index group_uid_idx (group_uid)';
    $statement = $db_hordehandle->prepare($statement) or 
        die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}

# Create an index for user_uid if needed
unless ($horde_groups_members ->{user_uid}->{Key})
{
    my $statement = 'alter table horde_groups_members  ' .
                    'add index user_uid_idx (user_uid)';
    $statement = $db_hordehandle->prepare($statement) or 
        die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}

# Create an index for pref_uid if needed
unless ($horde_prefs->{pref_uid}->{Key})
{
    my $statement = 'alter table horde_prefs ' .
                    'add index pref_uid_idx (pref_uid)';
    $statement = $db_hordehandle->prepare($statement) or 
        die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}

# Create an index for pref_scope if needed
unless ($horde_prefs->{pref_scope}->{Key})
{
    my $statement = 'alter table horde_prefs ' .
                    'add index pref_scope_idx (pref_scope)';
    $statement = $db_hordehandle->prepare($statement) or 
        die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}

# Create an index for datatree_name if needed
unless ($horde_datatree->{datatree_name}->{Key})
{
    my $statement = 'alter table horde_datatree ' .
                    'add index datatree_datatree_name_idx (datatree_name)';
    $statement = $db_hordehandle->prepare($statement) or 
        die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}

# Create an index for group_uid if needed
unless ($horde_datatree->{group_uid}->{Key})
{
    my $statement = 'alter table horde_datatree ' .
                    'add index datatree_group_idx (group_uid)';
    $statement = $db_hordehandle->prepare($statement) or 
        die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}

# Create an index for user_uid if needed
unless ($horde_datatree->{user_uid}->{Key})
{
    my $statement = 'alter table horde_datatree ' .
                    'add index datatree_user_idx (user_uid)';
    $statement = $db_hordehandle->prepare($statement) or 
        die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}

# Create an index for datatree_serialized if needed
unless ($horde_datatree->{datatree_serialized}->{Key})
{
    my $statement = 'alter table horde_datatree ' .
                    'add index datatree_serialized_idx (datatree_serialized)';
    $statement = $db_hordehandle->prepare($statement) or 
        die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}

# Create an index for datatree_parents if needed
unless ($horde_datatree->{datatree_parents}->{Key})
{
    my $statement = 'alter table horde_datatree ' .
                    'add index datatree_parents_idx (datatree_parents)';
    $statement = $db_hordehandle->prepare($statement) or 
        die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}

# Create an index for datatree_id if needed
unless ($horde_datatree_attributes->{datatree_id}->{Key})
{
    my $statement = 'alter table horde_datatree_attributes ' .
                    'add index datatree_attribute_idx (datatree_id)';
    $statement = $db_hordehandle->prepare($statement) or 
        die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}

# Create an index for attribute_name if needed
unless ($horde_datatree_attributes->{attribute_name}->{Key})
{
    my $statement = 'alter table horde_datatree_attributes ' .
                    'add index datatree_attribute_name_idx (attribute_name)';
    $statement = $db_hordehandle->prepare($statement) or 
        die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}

# Create an index for attribute_key if needed
unless ($horde_datatree_attributes->{attribute_key}->{Key})
{
    my $statement = 'alter table horde_datatree_attributes ' .
                    'add index datatree_attribute_key_idx (attribute_key)';
    $statement = $db_hordehandle->prepare($statement) or 
        die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}

# Create an index for attribute_value if needed
unless ($horde_datatree_attributes->{attribute_value}->{Key})
{
    my $statement = 'alter table horde_datatree_attributes ' .
                    'add index datatree_attribute_value_idx (attribute_value(255))';
    $statement = $db_hordehandle->prepare($statement) or 
        die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}

# Create an index for vfs_path if needed
unless ($horde_vfs->{vfs_path}->{Key})
{
    my $statement = 'alter table horde_vfs ' .
                    'add index vfs_path_idx (vfs_path)';
    $statement = $db_hordehandle->prepare($statement) or 
        die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}

# Create an index for vfs_name if needed
unless ($horde_vfs->{vfs_name}->{Key})
{
    my $statement = 'alter table horde_vfs ' .
                    'add index vfs_name_idx (vfs_name)';
    $statement = $db_hordehandle->prepare($statement) or 
        die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}

# Create an index for history_action if needed
unless ($horde_histories->{history_action}->{Key})
{
    my $statement = 'alter table horde_histories ' .
                    'add index history_action_idx (history_action)';
    $statement = $db_hordehandle->prepare($statement) or 
        die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}

# Create an index for history_ts if needed
unless ($horde_histories->{history_ts}->{Key})
{
    my $statement = 'alter table horde_histories ' .
                    'add index history_ts_idx (history_ts)';
    $statement = $db_hordehandle->prepare($statement) or 
        die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}

# Create an index for history_uid if needed
unless ($horde_histories->{object_uid}->{Key})
{
    my $statement = 'alter table horde_histories ' .
                    'add index history_uid_idx (object_uid)';
    $statement = $db_hordehandle->prepare($statement) or 
        die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}

# Create an index for session_lastmodified if needed
unless ($horde_sessionhandler->{session_lastmodified}->{Key})
{
    my $statement = 'alter table horde_sessionhandler ' .
                    'add index session_lastmodified_idx (session_lastmodified)';
    $statement = $db_hordehandle->prepare($statement) or 
        die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}

# Create an index for syncml_syncpartner if needed
unless ($horde_syncml_map->{syncml_syncpartner}->{Key})
{
    my $statement = 'alter table horde_syncml_map ' .
                    'add index syncml_syncpartner_idx (syncml_syncpartner)';
    $statement = $db_hordehandle->prepare($statement) or 
        die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}

# Create an index for syncml_db if needed
unless ($horde_syncml_map->{syncml_db}->{Key})
{
    my $statement = 'alter table horde_syncml_map ' .
                    'add index syncml_db_idx (syncml_db)';
    $statement = $db_hordehandle->prepare($statement) or 
        die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}

# Create an index for syncml_uid if needed
unless ($horde_syncml_map->{syncml_uid}->{Key})
{
    my $statement = 'alter table horde_syncml_map ' .
                    'add index syncml_uid_idx (syncml_uid)';
    $statement = $db_hordehandle->prepare($statement) or 
        die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}

# Create an index for syncml_cuid if needed
unless ($horde_syncml_map->{syncml_cuid}->{Key})
{
    my $statement = 'alter table horde_syncml_map ' .
                    'add index syncml_cuid_idx (syncml_cuid)';
    $statement = $db_hordehandle->prepare($statement) or 
        die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}

# Create an index for syncml_suid if needed
unless ($horde_syncml_map->{syncml_suid}->{Key})
{
    my $statement = 'alter table horde_syncml_map ' .
                    'add index syncml_suid_idx (syncml_suid)';
    $statement = $db_hordehandle->prepare($statement) or 
        die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}

# Create an index for syncml_syncpartner if needed
unless ($horde_syncml_anchors->{syncml_syncpartner}->{Key})
{
    my $statement = 'alter table horde_syncml_anchors ' .
                    'add index syncml_anchors_syncpartner_idx (syncml_syncpartner)';
    $statement = $db_hordehandle->prepare($statement) or 
        die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}

# Create an index for syncml_db if needed
unless ($horde_syncml_anchors->{syncml_db}->{Key})
{
    my $statement = 'alter table horde_syncml_anchors ' .
                    'add index syncml_anchors_db_idx (syncml_db)';
    $statement = $db_hordehandle->prepare($statement) or 
        die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}

# Create an index for syncml_uid if needed
unless ($horde_syncml_anchors->{syncml_uid}->{Key})
{
    my $statement = 'alter table horde_syncml_anchors ' .
                    'add index syncml_anchors_uid_idx (syncml_uid)';
    $statement = $db_hordehandle->prepare($statement) or 
        die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}

# Create an index for alarm_id if needed
unless ($horde_alarms->{alarm_id}->{Key})
{
    my $statement = 'alter table horde_alarms ' .
                    'add index alarm_id_idx (alarm_id)';
    $statement = $db_hordehandle->prepare($statement) or 
        die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}

# Create an index for alarm_uid if needed
unless ($horde_alarms->{alarm_uid}->{Key})
{
    my $statement = 'alter table horde_alarms ' .
                    'add index alarm_user_idx (alarm_uid)';
    $statement = $db_hordehandle->prepare($statement) or 
        die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}

# Create an index for alarm_start if needed
unless ($horde_alarms->{alarm_start}->{Key})
{
    my $statement = 'alter table horde_alarms ' .
                    'add index alarm_start_idx (alarm_start)';
    $statement = $db_hordehandle->prepare($statement) or 
        die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}

# Create an index for alarm_end if needed
unless ($horde_alarms->{alarm_end}->{Key})
{
    my $statement = 'alter table horde_alarms ' .
                    'add index alarm_end_idx (alarm_end)';
    $statement = $db_hordehandle->prepare($statement) or 
        die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}

# Create an index for alarm_snooze if needed
unless ($horde_alarms->{alarm_snooze}->{Key})
{
    my $statement = 'alter table horde_alarms ' .
                    'add index alarm_snooze_idx (alarm_snooze)';
    $statement = $db_hordehandle->prepare($statement) or 
        die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}

# Create an index for alarm_dismissed if needed
unless ($horde_alarms->{alarm_dismissed}->{Key})
{
    my $statement = 'alter table horde_alarms ' .
                    'add index alarm_dismissed (alarm_dismissed)';
    $statement = $db_hordehandle->prepare($statement) or 
        die "prepare: $$statement: $DBI::errstr";
    $statement->execute or die "execute: $$statement: $DBI::errstr";
}
