#!/bin/sh
#
# Copyright (C) 2000-2017 Kern Sibbald
# License: BSD 2-Clause; see file LICENSE-FOSS
#
# Shell script to update MySQL tables from Bacula Community version 
#  5.0.x, 5.2.x, 7.0.x, 7.2.x, 7.4.x
#
echo " "
echo "This script will update a Bacula MySQL database from version 12-15 to 16"
echo " "
echo "Depending on the current version of your catalog,"
echo "you may have to run this script multiple times."
echo " "

bindir=@MYSQL_BINDIR@
PATH="$bindir:$PATH"
db_name=@db_name@

ARGS=$*

getVersion()
{
    mysql $ARGS -D ${db_name} -e "select VersionId from Version LIMIT 1\G" >/tmp/$$
    DBVERSION=`sed -n -e 's/^VersionId: \(.*\)$/\1/p' /tmp/$$`
}

getVersion

if [ "x$DBVERSION" = x ]; then
    echo
    echo "Unable to detect database version, you can specify connection information"
    echo "on the command line."
    echo "Error. Cannot upgrade this database."
    exit 1
fi

if [ "$DBVERSION" -lt 12 -o "$DBVERSION" -gt 15 ] ; then
    echo " "
    echo "The existing database is version $DBVERSION !!"
    echo "This script can only update an existing version 12-15 to version 16."
    echo "Error. Cannot upgrade this database."
    echo " "
    exit 1
fi

# For all versions, we need to create the Index on Media(StorageId)
# It may fail, but it's not a big problem
# mysql $* -f <<END-OF-DATA >/dev/null 2> /dev/null
# CREATE INDEX media_storageid_idx ON Media (StorageId);
# END-OF-DATA

if [ "$DBVERSION" -eq 12 ] ; then
    if mysql $* -f <<END-OF-DATA
USE ${db_name};

CREATE TABLE RestoreObject (
   RestoreObjectId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
   ObjectName BLOB NOT NULL,
   RestoreObject LONGBLOB NOT NULL,
   PluginName TINYBLOB NOT NULL,
   ObjectLength INTEGER DEFAULT 0,
   ObjectFullLength INTEGER DEFAULT 0,
   ObjectIndex INTEGER DEFAULT 0,
   ObjectType INTEGER DEFAULT 0,
   FileIndex INTEGER UNSIGNED DEFAULT 0,
   JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
   ObjectCompression INTEGER DEFAULT 0,
   PRIMARY KEY(RestoreObjectId),
   INDEX (JobId)
);

CREATE INDEX jobhisto_jobid_idx ON JobHisto (JobId);
UPDATE Version SET VersionId=13;

END-OF-DATA
    then
	echo "Update of Bacula MySQL tables from 12 to 13 succeeded."
	getVersion
    else
	echo "Update of Bacula MySQL tables from 12 to 13 failed."
	exit 1
    fi
fi

if [ "$DBVERSION" -eq 13 ] ; then
    if mysql $* -f <<END-OF-DATA
USE ${db_name};

ALTER TABLE File ADD COLUMN DeltaSeq smallint default 0;
UPDATE Version SET VersionId=14;

END-OF-DATA
    then
	echo "Update of Bacula MySQL tables from 13 to 14 succeeded."
	getVersion
    else
	echo "Update of Bacula MySQL tables from 13 to 14 failed."
	exit 1
    fi
fi

if [ "$DBVERSION" -eq 14 ] ; then
    if mysql $* -f <<END-OF-DATA
USE ${db_name};

INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
   ('I', 'Incomplete Job',25);
ALTER TABLE Media ADD COLUMN VolABytes BIGINT UNSIGNED DEFAULT 0;
ALTER TABLE Media ADD COLUMN VolAPadding BIGINT UNSIGNED DEFAULT 0;
ALTER TABLE Media ADD COLUMN VolHoleBytes BIGINT UNSIGNED DEFAULT 0;
ALTER TABLE Media ADD COLUMN VolHoles INTEGER UNSIGNED DEFAULT 0;
ALTER TABLE Media CHANGE VolWrites VolWrites BIGINT UNSIGNED;

CREATE TABLE Snapshot (
  SnapshotId	  INTEGER UNSIGNED AUTO_INCREMENT,
  Name		  TINYBLOB NOT NULL,
  JobId 	  INTEGER UNSIGNED DEFAULT 0,
  FileSetId	  INTEGER UNSIGNED DEFAULT 0,
  CreateTDate	  BIGINT   NOT NULL,
  CreateDate	  DATETIME NOT NULL,
  ClientId	  INTEGER UNSIGNED DEFAULT 0,
  Volume	  TINYBLOB NOT NULL,
  Device	  TINYBLOB NOT NULL,
  Type		  TINYBLOB NOT NULL,
  Retention	  INTEGER DEFAULT 0,
  Comment	  BLOB,
  primary key (SnapshotId)
);

CREATE UNIQUE INDEX snapshot_idx ON Snapshot (Device(255), Volume(255), Name(255));

CREATE INDEX jobtdate_idx on JobHisto (JobTDate);

UPDATE Version SET VersionId=15;
END-OF-DATA
    then
	echo "Update of Bacula MySQL tables from 14 to 15 succeeded."
	getVersion
    else
	echo "Update of Bacula MySQL tables from 14 to 15 failed."
	exit 1
    fi
fi

if [ "$DBVERSION" -eq 15 ] ; then
    if mysql $* -f  <<END-OF-DATA
USE ${db_name};
ALTER TABLE BaseFiles MODIFY COLUMN BaseId BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE Media CHANGE COLUMN VolParts VolType INTEGER UNSIGNED DEFAULT 0;
ALTER TABLE Media ADD COLUMN VolParts INTEGER DEFAULT 0;
ALTER TABLE Media ADD COLUMN VolCloudParts INTEGER DEFAULT 0;
ALTER TABLE Media ADD COLUMN LastPartBytes BIGINT DEFAULT 0;
ALTER TABLE Media ADD COLUMN CacheRetention BIGINT DEFAULT 0;
ALTER TABLE Pool  ADD COLUMN CacheRetention BIGINT DEFAULT 0;


-- If you switch to MySQL 5.7
ALTER TABLE Device ALTER COLUMN CleaningDate DROP DEFAULT;
ALTER TABLE Job    ALTER COLUMN SchedTime    DROP DEFAULT;
ALTER TABLE Job    ALTER COLUMN StartTime    DROP DEFAULT;
ALTER TABLE Job    ALTER COLUMN EndTime      DROP DEFAULT;
ALTER TABLE Job    ALTER COLUMN RealEndTime  DROP DEFAULT;
ALTER TABLE JobHisto ALTER COLUMN SchedTime  DROP DEFAULT;
ALTER TABLE JobHisto ALTER COLUMN StartTime  DROP DEFAULT;
ALTER TABLE JobHisto ALTER COLUMN EndTime    DROP DEFAULT;
ALTER TABLE JobHisto ALTER COLUMN RealEndTime DROP DEFAULT;
ALTER TABLE LocationLog ALTER COLUMN Date DROP DEFAULT;
ALTER TABLE FileSet	ALTER COLUMN CreateTime DROP DEFAULT;
ALTER TABLE Media	ALTER COLUMN FirstWritten DROP DEFAULT;
ALTER TABLE Media	ALTER COLUMN LastWritten DROP DEFAULT;
ALTER TABLE Media	ALTER COLUMN LabelDate DROP DEFAULT;
ALTER TABLE Media	ALTER COLUMN InitialWrite DROP DEFAULT;
ALTER TABLE Log 	ALTER COLUMN Time DROP DEFAULT;
# speeds up restore selection if many files and accurate
CREATE INDEX job_jobtdate_idx ON Job (JobTDate);

UPDATE Version SET VersionId=16;
END-OF-DATA
    then
	echo "Update of Bacula MySQL tables 15 to 16 succeeded."
	getVersion
    else
	echo "Update of Bacula MySQL tables 15 to 16 failed."
	exit 1
    fi
fi


exit 0