#!/bin/sh
#
# Shell script to update MySQL tables from version 1.32 to 1.33
#
echo " "
echo "This script will update a bacula database from version 6 to 7."
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 "6" ]; then
	echo "Sorry, this script is designed to update a version 6 database"
	echo "and you have a version $DB_VER database."
	exit 1
fi

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

ALTER TABLE Media ADD COLUMN InChanger TINYINT NOT NULL DEFAULT 0;
ALTER TABLE Media ADD COLUMN MediaAddressing TINYINT NOT NULL DEFAULT 0;
ALTER TABLE Media ADD COLUMN VolReadTime BIGINT UNSIGNED NOT NULL DEFAULT 0;
ALTER TABLE Media ADD COLUMN VolWriteTime BIGINT UNSIGNED NOT NULL DEFAULT 0;

ALTER TABLE Pool ADD COLUMN Enabled TINYINT DEFAULT 1;
ALTER TABLE Pool ADD COLUMN ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool;
ALTER TABLE Pool ADD COLUMN RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool;

DROP TABLE BaseFiles;


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)
   );

DROP TABLE UnsavedFiles;

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 BaseFiles;

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

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

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