#!/bin/sh
#
# Copyright (C) 2000-2015 Kern Sibbald
# License: BSD 2-Clause; see file LICENSE-FOSS
#
# Shell script to update MySQL tables from version 2.0 to 3.0	
#
echo " "
echo "This script will update a Bacula MySQL database from version 10 to 11"
echo " which is needed to convert from Bacula version 2.0.x to 3.0.x or higher"
echo " "
bindir=@MYSQL_BINDIR@
PATH="$bindir:$PATH"
db_name=${db_name:-@db_name@}

if mysql $* -f <<END-OF-DATA
USE ${db_name};

-- Fix bad index on Media table
DROP INDEX inx8 ON Media;
CREATE UNIQUE INDEX inx8 ON Media (VolumeName(128));
ALTER TABLE File CHANGE FileId FileId BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE BaseFiles CHANGE FileId FileId BIGINT UNSIGNED NOT NULL;
ALTER TABLE Job ADD ReadBytes BIGINT UNSIGNED DEFAULT 0 AFTER JobBytes; 
ALTER TABLE Media ADD ActionOnPurge TINYINT DEFAULT 0 AFTER Recycle;
ALTER TABLE Pool ADD ActionOnPurge TINYINT DEFAULT 0 AFTER Recycle;

DELETE FROM Version;
INSERT INTO Version (VersionId) VALUES (11);

-- If you have already this table, you can remove it with:
-- DROP TABLE JobHistory;

-- Create a table like Job for long term statistics
CREATE TABLE JobHisto (
   JobId INTEGER UNSIGNED NOT NULL,
   Job TINYBLOB NOT NULL,
   Name TINYBLOB NOT NULL,
   Type BINARY(1) NOT NULL,
   Level BINARY(1) NOT NULL,
   ClientId INTEGER DEFAULT 0,
   JobStatus BINARY(1) NOT NULL,
   SchedTime DATETIME DEFAULT 0,
   StartTime DATETIME DEFAULT 0,
   EndTime DATETIME DEFAULT 0,
   RealEndTime DATETIME DEFAULT 0,
   JobTDate BIGINT UNSIGNED DEFAULT 0,
   VolSessionId INTEGER UNSIGNED DEFAULT 0,
   VolSessionTime INTEGER UNSIGNED DEFAULT 0,
   JobFiles INTEGER UNSIGNED DEFAULT 0,
   JobBytes BIGINT UNSIGNED DEFAULT 0,
   ReadBytes BIGINT UNSIGNED DEFAULT 0,
   JobErrors INTEGER UNSIGNED DEFAULT 0,
   JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
   PoolId INTEGER UNSIGNED DEFAULT 0,
   FileSetId INTEGER UNSIGNED DEFAULT 0,
   PriorJobId INTEGER UNSIGNED DEFAULT 0,
   PurgedFiles TINYINT DEFAULT 0,
   HasBase TINYINT DEFAULT 0,
   INDEX (StartTime)
   );

END-OF-DATA
then
   echo "Update of Bacula MySQL tables succeeded."
else
   echo "Update of Bacula MySQL tables failed."
fi
exit 0