3 # Shell script to update MySQL tables from Bacula Community version 3.0.x to 5.0.0
6 echo "This script will update a Bacula MySQL database from version 11 to 12"
7 echo " which is needed to convert from Bacula Community version 3.0.x to 5.0.x"
16 DBV=`${sqlite} ${db_name} $sbin/regress.db <<END
17 select VersionId from Version;
19 if [ $DBVERSION != 11 ] ; then
21 echo "The existing database is version $DBVERSION !!"
22 echo "This script can only update an existing version 11 database to version 12."
23 echo "Error. Cannot upgrade this database."
29 ${sqlite} $* ${db_name}.db <<END-OF-DATA
31 ALTER TABLE Job ADD COLUMN HasCache TINYINT DEFAULT 0;
32 ALTER TABLE Job ADD COLUMN Reviewed TINYINT DEFAULT 0;
33 ALTER TABLE Job ADD COLUMN Comment TEXT;
34 ALTER TABLE JobHisto ADD COLUMN HasCache TINYINT DEFAULT 0;
35 ALTER TABLE JobHisto ADD COLUMN Reviewed TINYINT DEFAULT 0;
36 ALTER TABLE JobHisto ADD COLUMN Comment TEXT;
38 ALTER TABLE Status ADD COLUMN Severity int;
39 UPDATE Status SET Severity = 15;
40 UPDATE Status SET Severity = 100 where JobStatus = 'f';
41 UPDATE Status SET Severity = 90 where JobStatus = 'A';
42 UPDATE Status SET Severity = 10 where JobStatus = 'T';
43 UPDATE Status SET Severity = 20 where JobStatus = 'e';
44 UPDATE Status SET Severity = 25 where JobStatus = 'E';
46 CREATE TABLE PathHierarchy
48 PathId integer NOT NULL,
49 PPathId integer NOT NULL,
50 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
53 CREATE INDEX pathhierarchy_ppathid
54 ON PathHierarchy (PPathId);
56 CREATE TABLE PathVisibility
58 PathId integer NOT NULL,
59 JobId integer NOT NULL,
62 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 );
70 UPDATE Version SET VersionId=12;
74 DROP INDEX IF EXISTS inx9;
75 CREATE INDEX file_jpf_idx ON File (JobId, PathId, FilenameId);