#!/bin/sh
#
# Copyright (C) 2000-2015 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
#
echo " "
echo "This script will update a Bacula MySQL database from version 12-14 to 15"
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"
cd /var/spool/bacula
db_name=bacula

DBVERSION=`sqlite3 ${db_name}.db <<END
SELECT VersionId FROM Version LIMIT 1;
END
`
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, 13, 14 or 1014 database to version 1015."
    echo "Error. Cannot upgrade this database."
    echo " "
    exit 1
fi

if [ "$DBVERSION" = 12 ] ; then
sqlite3 $* ${db_name}.db <<END-OF-DATA
BEGIN;

CREATE TABLE RestoreObject (
   RestoreObjectId INTEGER,
   ObjectName TEXT DEFAULT '',
   RestoreObject TEXT DEFAULT '',
   PluginName TEXT DEFAULT '',
   ObjectLength INTEGER DEFAULT 0,
   ObjectFullLength INTEGER DEFAULT 0,
   ObjectIndex INTEGER DEFAULT 0,
   ObjectType INTEGER DEFAULT 0,
   FileIndex INTEGER UNSIGNED DEFAULT 0,
   ObjectCompression INTEGER DEFAULT 0,
   JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
   PRIMARY KEY(RestoreObjectId)
   );
CREATE INDEX restore_jobid_idx ON RestoreObject (JobId);

UPDATE Version SET VersionId=13;
COMMIT;

END-OF-DATA
DBVERSION=13
fi

if [ "$DBVERSION" = 13 ] ; then

sqlite3 $* ${db_name}.db <<END-OF-DATA
BEGIN;

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

COMMIT;

END-OF-DATA

DBVERSION=14

fi

if [ "$DBVERSION" = 14 ] ; then
    sqlite3 $* ${db_name}.db  <<END-OF-DATA
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 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
DBVERSION=15
fi

END-OF-DATA
