3 # Copyright (C) 2000-2015 Kern Sibbald
4 # License: BSD 2-Clause; see file LICENSE-FOSS
6 # Shell script to update MySQL Community version 3.0.x to 5.0.x
9 echo "This script will update a Bacula MySQL database from version 11 to 12"
10 echo " which is needed to convert from Bacula Community version 3.0.x to 5.0.x"
14 db_name=${db_name:-@db_name@}
16 mysql $* -D ${db_name} -e "select VersionId from Version\G" >/tmp/$$
17 DBVERSION=`sed -n -e 's/^VersionId: \(.*\)$/\1/p' /tmp/$$`
18 if [ $DBVERSION != 11 ] ; then
20 echo "The existing database is version $DBVERSION !!"
21 echo "This script can only update an existing version 11 database to version 12."
22 echo "Error. Cannot upgrade this database."
27 if mysql $* -f <<END-OF-DATA
30 ALTER TABLE JobMedia DROP Stripe ;
31 ALTER TABLE JobMedia DROP Copy ;
32 ALTER TABLE Job ADD COLUMN HasCache tinyint default 0 after HasBase;
33 ALTER TABLE Job ADD COLUMN Reviewed tinyint default 0 after HasCache;
34 ALTER TABLE Job ADD COLUMN Comment BLOB AFTER Reviewed;
35 ALTER TABLE JobHisto ADD COLUMN HasCache tinyint default 0 after HasBase;
36 ALTER TABLE JobHisto ADD COLUMN Reviewed tinyint default 0 after HasCache;
37 ALTER TABLE JobHisto ADD COLUMN Comment BLOB AFTER Reviewed;
39 ALTER TABLE Status ADD COLUMN Severity int;
40 UPDATE Status SET Severity = 15;
41 UPDATE Status SET Severity = 100 where JobStatus = 'f';
42 UPDATE Status SET Severity = 90 where JobStatus = 'A';
43 UPDATE Status SET Severity = 10 where JobStatus = 'T';
44 UPDATE Status SET Severity = 20 where JobStatus = 'e';
45 UPDATE Status SET Severity = 25 where JobStatus = 'E';
47 CREATE TABLE PathHierarchy
49 PathId integer NOT NULL,
50 PPathId integer NOT NULL,
51 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
54 CREATE INDEX pathhierarchy_ppathid
55 ON PathHierarchy (PPathId);
57 CREATE TABLE PathVisibility
59 PathId integer NOT NULL,
60 JobId integer NOT NULL,
63 CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
65 CREATE INDEX pathvisibility_jobid
66 ON PathVisibility (JobId);
68 CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
71 INSERT INTO Version (VersionId) VALUES (12);
75 echo "Update of Bacula MySQL tables succeeded."
77 echo "Update of Bacula MySQL tables failed."