#!/bin/sh
#
# Shell script to update MySQL tables from Bacula Enterprise version from 4.0.x to 6.6.x
#  or Bacula Community version 5.0.x, 5.2.x, 6.0.x, 6.2.x, 6.4.x to 6.6.x
#
echo " "
echo "This script will update a Bacula MySQL database from version 12-14,1014-1015 to 1016"
echo "	which is needed to convert from Bacula Enterprise version 4.0.x to 6.6.x"
echo "	or Bacula Community version 5.0.x, 5.2.x, 6.0.x, 6.2.x, 6.4.x to 6.6.x"
echo " "
echo "Depending on the current version of your catalog, you may have to run this script"
echo "  multiple times"
echo " "
bindir=/usr/bin
PATH="$bindir:$PATH"
db_name=bacula

# Special trick to not do the upgrade from 1015 to 1016 with this script
if [ "$1" = "--stop1015" ]; then
    STOP1015=yes
    shift                       # remove this option from $*
fi

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 1014 -o "$DBVERSION" -gt 1015 ] ; then
  if [ "$DBVERSION" -lt 12 -o "$DBVERSION" -gt 14 ] ; then
    echo " "
    echo "The existing database is version $DBVERSION !!"
    echo "This script can only update an existing version 12-14 or 1014-1015 database to version 1016."
    echo "Error. Cannot upgrade this database."
    echo " "
    exit 1
  fi
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);
UPDATE Version SET VersionId=1014;
END-OF-DATA
    then
	echo "Update of Bacula MySQL tables from 14 to 1014 succeeded."
        getVersion
    else
	echo "Update of Bacula MySQL tables from 14 to 1014 failed."
        exit 1
    fi
fi

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

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;
UPDATE Version SET VersionId=1015;
END-OF-DATA
    then
	echo "Update of Bacula MySQL tables from 1014 to 1015 succeeded."
        getVersion
    else
	echo "Update of Bacula MySQL tables from 1014 to 1015 failed."
        exit 1
    fi
fi

if [ "$STOP1015" = "" -a "$DBVERSION" -eq 1015 ]; then
    if mysql --skip-reconnect $* <<END-OF-DATA
USE ${db_name};

DROP INDEX JobId on File;
DROP INDEX JobId_2 on File;

CREATE TABLE file_temp (
   FileId BIGINT UNSIGNED NOT NULL,
   FileIndex INTEGER UNSIGNED DEFAULT 0,
   JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
   PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
   Filename BLOB NOT NULL,
   DeltaSeq SMALLINT UNSIGNED DEFAULT 0,
   MarkId INTEGER UNSIGNED DEFAULT 0,
   LStat TINYBLOB NOT NULL,
   MD5 TINYBLOB
   );

INSERT INTO file_temp (FileId, FileIndex, JobId, PathId, Filename, DeltaSeq,
                       MarkId, LStat, Md5)
   SELECT FileId, FileIndex, JobId, PathId, Filename.Name, DeltaSeq, 
          MarkId, LStat, Md5
    FROM File JOIN Filename USING (FilenameId);

DROP TABLE Filename;
DROP TABLE File;

ALTER TABLE file_temp RENAME TO File;
ALTER TABLE File ADD PRIMARY KEY (FileId);
ALTER TABLE File MODIFY COLUMN FileId BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;
CREATE INDEX JobId on File (JobId);
CREATE INDEX JobId_2 on File (JobId, PathId, Filename(255));

ALTER TABLE Media CHANGE VolWrites VolWrites BIGINT UNSIGNED;
ALTER TABLE Job ADD COLUMN FileTable CHAR(20) default 'File';
ALTER TABLE JobHisto ADD COLUMN FileTable CHAR(20) default 'File';


CREATE TABLE Snapshot (
  SnapshotId      INTEGER UNSIGNED AUTO_INCREMENT,
  Name            TINYBLOB NOT NULL,
  CreateTDate     BIGINT   NOT NULL,
  CreateDate      DATETIME NOT NULL,
  ClientId        INTEGER 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));

UPDATE Version SET VersionId = 1016;

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

exit 0
