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, you may have to run this script"
13 echo " multiple times"
16 bindir=@SQLITE_BINDIR@
21 DBVERSION=`sqlite3 ${db_name}.db <<END
22 SELECT VersionId FROM Version LIMIT 1;
25 if [ "$DBVERSION" -lt 12 -o "$DBVERSION" -gt 14 ] ; then
27 echo "The existing database is version $DBVERSION !!"
28 echo "This script can only update an existing version 12, 13, 14 or 1014 database to version 1015."
29 echo "Error. Cannot upgrade this database."
34 if [ "$DBVERSION" = 12 ] ; then
35 sqlite3 $* ${db_name}.db <<END-OF-DATA
38 CREATE TABLE RestoreObject (
39 RestoreObjectId INTEGER,
40 ObjectName TEXT DEFAULT '',
41 RestoreObject TEXT DEFAULT '',
42 PluginName TEXT DEFAULT '',
43 ObjectLength INTEGER DEFAULT 0,
44 ObjectFullLength INTEGER DEFAULT 0,
45 ObjectIndex INTEGER DEFAULT 0,
46 ObjectType INTEGER DEFAULT 0,
47 FileIndex INTEGER UNSIGNED DEFAULT 0,
48 ObjectCompression INTEGER DEFAULT 0,
49 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
50 PRIMARY KEY(RestoreObjectId)
52 CREATE INDEX restore_jobid_idx ON RestoreObject (JobId);
54 UPDATE Version SET VersionId=13;
61 if [ "$DBVERSION" = 13 ] ; then
63 sqlite3 $* ${db_name}.db <<END-OF-DATA
66 ALTER TABLE File ADD COLUMN DeltaSeq smallint default 0;
67 UPDATE Version SET VersionId=14;
77 if [ "$DBVERSION" = 14 ] ; then
78 sqlite3 $* ${db_name}.db <<END-OF-DATA
79 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
80 ('I', 'Incomplete Job',25);
81 ALTER TABLE Media ADD COLUMN VolABytes BIGINT UNSIGNED DEFAULT 0;
82 ALTER TABLE Media ADD COLUMN VolAPadding BIGINT UNSIGNED DEFAULT 0;
83 ALTER TABLE Media ADD COLUMN VolHoleBytes BIGINT UNSIGNED DEFAULT 0;
84 ALTER TABLE Media ADD COLUMN VolHoles INTEGER UNSIGNED DEFAULT 0;
85 ALTER TABLE Media CHANGE VolWrites VolWrites BIGINT UNSIGNED;
87 CREATE TABLE Snapshot (
88 SnapshotId INTEGER UNSIGNED AUTO_INCREMENT,
89 Name TINYBLOB NOT NULL,
90 JobId INTEGER UNSIGNED DEFAULT 0,
91 FileSetId INTEGER UNSIGNED DEFAULT 0;
92 CreateTDate BIGINT NOT NULL,
93 CreateDate DATETIME NOT NULL,
94 ClientId INTEGER DEFAULT 0,
95 Volume TINYBLOB NOT NULL,
96 Device TINYBLOB NOT NULL,
97 Type TINYBLOB NOT NULL,
98 Retention INTEGER DEFAULT 0,
100 primary key (SnapshotId)
103 CREATE UNIQUE INDEX snapshot_idx ON Snapshot (Device(255), Volume(255), Name(255));
105 CREATE INDEX jobtdate_idx on JobHisto (JobTDate);
107 UPDATE Version SET VersionId=15;