3 # Shell script to update MySQL tables from version 1.29 to 1.30
6 echo "This script will update a bacula database from version 5 to 6."
7 echo "Depending on the size of your database,"
8 echo "this script may take several minutes to run."
11 # the location of the mysql program
14 DB_VER=`$bindir/mysql bacula -e 'select * from Version;'|tail -n 1 2>/dev/null`
15 if [ -z "$DB_VER" ]; then
16 echo "Sorry, I can't seem to locate a bacula database."
20 if [ -n "$DB_VER" ] && [ "$DB_VER" -ne "5" ]; then
21 echo "Sorry, this script is designed to update a version 5 database"
22 echo "and you have a version $DB_VER database."
26 if $bindir/mysql $* -f <<END-OF-DATA
29 ALTER TABLE JobMedia ADD COLUMN VolIndex INTEGER UNSIGNED NOT NULL;
31 ALTER TABLE Job ADD COLUMN HasBase TINYINT DEFAULT 0;
33 CREATE TABLE FileSet_backup (
34 FileSetId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
35 FileSet TINYBLOB NOT NULL,
36 MD5 TINYBLOB NOT NULL,
37 PRIMARY KEY(FileSetId)
39 INSERT INTO FileSet_backup SELECT * From FileSet;
43 CREATE TABLE FileSet (
44 FileSetId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
45 FileSet TINYBLOB NOT NULL,
46 MD5 TINYBLOB NOT NULL,
47 CreateTime DATETIME NOT NULL,
48 PRIMARY KEY(FileSetId)
52 FileSetId, FileSet, MD5, CreateTime)
53 SELECT FileSet_backup.FileSetId,FileSet,MD5,StartTime as CreateTime
54 FROM FileSet_backup,Job
55 WHERE FileSet_backup.FileSetId=Job.FileSetId
56 GROUP by FileSet_backup.FileSetId
57 ORDER BY Job.FileSetId;
59 DROP TABLE FileSet_backup;
62 CREATE TABLE BaseFiles (
63 BaseId INTEGER UNSIGNED AUTO_INCREMENT,
64 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
65 FileId INTEGER UNSIGNED NOT NULL REFERENCES File,
66 FileIndex INTEGER UNSIGNED,
70 CREATE TABLE UnsavedFiles (
71 UnsavedId INTEGER UNSIGNED AUTO_INCREMENT,
72 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
73 PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
74 FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
75 PRIMARY KEY (UnsavedId)
80 CREATE TABLE Counters (
81 Counter TINYBLOB NOT NULL,
85 WrapCounter TINYBLOB NOT NULL,
86 PRIMARY KEY (Counter(128))
89 UPDATE Version SET VersionId=6;
93 echo "Update of Bacula MySQL tables succeeded."
95 echo "Update of Bacula MySQL tables failed."