3 # Copyright (C) 2000-2015 Kern Sibbald
4 # License: BSD 2-Clause; see file LICENSE-FOSS
6 # Shell script to update MySQL tables from Bacula Community version
10 echo "This script will update a Bacula MySQL database from version 12-14 to 15"
12 echo "Depending on the current version of your catalog,"
13 echo "you may have to run this script multiple times."
24 mysql $ARGS -D ${db_name} -e "select VersionId from Version LIMIT 1\G" >/tmp/$$
25 DBVERSION=`sed -n -e 's/^VersionId: \(.*\)$/\1/p' /tmp/$$`
30 if [ "x$DBVERSION" = x ]; then
32 echo "Unable to detect database version, you can specify connection information"
33 echo "on the command line."
34 echo "Error. Cannot upgrade this database."
38 if [ "$DBVERSION" -lt 12 -o "$DBVERSION" -gt 14 ] ; then
40 echo "The existing database is version $DBVERSION !!"
41 echo "This script can only update an existing version 12-14 version 15."
42 echo "Error. Cannot upgrade this database."
47 # For all versions, we need to create the Index on Media(StorageId)
48 # It may fail, but it's not a big problem
49 # mysql $* -f <<END-OF-DATA >/dev/null 2> /dev/null
50 # CREATE INDEX media_storageid_idx ON Media (StorageId);
53 if [ "$DBVERSION" -eq 12 ] ; then
54 if mysql $* -f <<END-OF-DATA
57 CREATE TABLE RestoreObject (
58 RestoreObjectId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
59 ObjectName BLOB NOT NULL,
60 RestoreObject LONGBLOB NOT NULL,
61 PluginName TINYBLOB NOT NULL,
62 ObjectLength INTEGER DEFAULT 0,
63 ObjectFullLength INTEGER DEFAULT 0,
64 ObjectIndex INTEGER DEFAULT 0,
65 ObjectType INTEGER DEFAULT 0,
66 FileIndex INTEGER UNSIGNED DEFAULT 0,
67 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
68 ObjectCompression INTEGER DEFAULT 0,
69 PRIMARY KEY(RestoreObjectId),
73 CREATE INDEX jobhisto_jobid_idx ON JobHisto (JobId);
74 UPDATE Version SET VersionId=13;
78 echo "Update of Bacula MySQL tables from 12 to 13 succeeded."
81 echo "Update of Bacula MySQL tables from 12 to 13 failed."
86 if [ "$DBVERSION" -eq 13 ] ; then
87 if mysql $* -f <<END-OF-DATA
90 ALTER TABLE File ADD COLUMN DeltaSeq smallint default 0;
91 UPDATE Version SET VersionId=14;
95 echo "Update of Bacula MySQL tables from 13 to 14 succeeded."
98 echo "Update of Bacula MySQL tables from 13 to 14 failed."
103 if [ "$DBVERSION" -eq 14 ] ; then
104 if mysql $* -f <<END-OF-DATA
107 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
108 ('I', 'Incomplete Job',25);
109 ALTER TABLE Media ADD COLUMN VolABytes BIGINT UNSIGNED DEFAULT 0;
110 ALTER TABLE Media ADD COLUMN VolAPadding BIGINT UNSIGNED DEFAULT 0;
111 ALTER TABLE Media ADD COLUMN VolHoleBytes BIGINT UNSIGNED DEFAULT 0;
112 ALTER TABLE Media ADD COLUMN VolHoles INTEGER UNSIGNED DEFAULT 0;
113 ALTER TABLE Media CHANGE VolWrites VolWrites BIGINT UNSIGNED;
115 CREATE TABLE Snapshot (
116 SnapshotId INTEGER UNSIGNED AUTO_INCREMENT,
117 Name TINYBLOB NOT NULL,
118 JobId INTEGER UNSIGNED DEFAULT 0,
119 FileSetId INTEGER UNSIGNED DEFAULT 0,
120 CreateTDate BIGINT NOT NULL,
121 CreateDate DATETIME NOT NULL,
122 ClientId INTEGER UNSIGNED DEFAULT 0,
123 Volume TINYBLOB NOT NULL,
124 Device TINYBLOB NOT NULL,
125 Type TINYBLOB NOT NULL,
126 Retention INTEGER DEFAULT 0,
128 primary key (SnapshotId)
131 CREATE UNIQUE INDEX snapshot_idx ON Snapshot (Device(255), Volume(255), Name(255));
133 CREATE INDEX jobtdate_idx on JobHisto (JobTDate);
135 UPDATE Version SET VersionId=15;
138 echo "Update of Bacula MySQL tables from 14 to 15 succeeded."
141 echo "Update of Bacula MySQL tables from 14 to 15 failed."