#!/bin/sh
#
# Shell script to update MySQL
#
echo " "
echo "This script will update a Bacula MySQL database from version 1015 to 1016"
echo " which is needed to convert from Bacula version 6.4.x to 6.6.x"
echo " "
bindir=/usr/bin
PATH="$bindir:$PATH"
db_name=${db_name:-bacula}

mysql $* -D ${db_name} -e "select VersionId from Version\G" >/tmp/$$
DBVERSION=`sed -n -e 's/^VersionId: \(.*\)$/\1/p' /tmp/$$`
if [ $DBVERSION != 1015 ] ; then
   echo " "
   echo "The existing database is version $DBVERSION !!"
   echo "This script can only update an existing version 1015 database to version 1016."
   echo "Error. Cannot upgrade this database."
   echo " "
   exit 1
fi

if mysql $* -f <<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 succeeded."
else
   echo "Update of Bacula MySQL tables failed."
fi
exit 0
