#!/bin/sh
#
# Shell script to update MySQL tables from version 1.29 to 1.30
#
echo " "
echo "This script will update a bacula database from version 5 to 6."
echo "Depending on the size of your database,"
echo "this script may take several minutes to run."
echo " "

# the location of the mysql program
bindir=/usr/bin

DB_VER=`$bindir/mysql bacula -e 'select * from Version;'|tail -n 1 2>/dev/null`
if [ -z "$DB_VER" ]; then
	echo "Sorry, I can't seem to locate a bacula database."
	exit 1
fi

if [ -n "$DB_VER" ] && [ "$DB_VER" -ne "5" ]; then
	echo "Sorry, this script is designed to update a version 5 database"
	echo "and you have a version $DB_VER database."
	exit 1
fi

if $bindir/mysql $* -f <<END-OF-DATA
USE bacula;

ALTER TABLE JobMedia ADD COLUMN VolIndex INTEGER UNSIGNED NOT NULL;

ALTER TABLE Job ADD COLUMN HasBase TINYINT DEFAULT 0;

CREATE TABLE FileSet_backup (
   FileSetId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
   FileSet TINYBLOB NOT NULL,
   MD5 TINYBLOB NOT NULL,
   PRIMARY KEY(FileSetId)
   );
INSERT INTO FileSet_backup SELECT * From FileSet;

DROP TABLE FileSet;

CREATE TABLE FileSet (
   FileSetId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
   FileSet TINYBLOB NOT NULL,
   MD5 TINYBLOB NOT NULL,
   CreateTime DATETIME NOT NULL,
   PRIMARY KEY(FileSetId)
   );

INSERT INTO FileSet (
   FileSetId, FileSet, MD5, CreateTime) 
   SELECT FileSet_backup.FileSetId,FileSet,MD5,StartTime as CreateTime
     FROM FileSet_backup,Job
     WHERE FileSet_backup.FileSetId=Job.FileSetId 
     GROUP by FileSet_backup.FileSetId
     ORDER BY Job.FileSetId;

DROP TABLE FileSet_backup;


CREATE TABLE BaseFiles (
   BaseId INTEGER UNSIGNED AUTO_INCREMENT,
   JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
   FileId INTEGER UNSIGNED NOT NULL REFERENCES File,
   FileIndex INTEGER UNSIGNED,
   PRIMARY KEY(BaseId)
   );

CREATE TABLE UnsavedFiles (
   UnsavedId INTEGER UNSIGNED AUTO_INCREMENT,
   JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
   PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
   FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
   PRIMARY KEY (UnsavedId)
   );

DROP TABLE Counters;

CREATE TABLE Counters (
   Counter TINYBLOB NOT NULL,
   MinValue INTEGER,
   MaxValue INTEGER,
   CurrentValue INTEGER,
   WrapCounter TINYBLOB NOT NULL,
   PRIMARY KEY (Counter(128))
   );

UPDATE Version SET VersionId=6;

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