#!/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," echo "you may have to run this script multiple times." echo " " bindir=@MYSQL_BINDIR@ PATH="$bindir:$PATH" db_name=@db_name@ 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 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 version 15." echo "Error. Cannot upgrade this database." echo " " exit 1 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); 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 UNSIGNED 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 then echo "Update of Bacula MySQL tables from 14 to 15 succeeded." getVersion else echo "Update of Bacula MySQL tables from 14 to 15 failed." exit 1 fi fi exit 0