3 # Copyright (C) 2000-2015 Kern Sibbald
4 # License: BSD 2-Clause; see file LICENSE-FOSS
6 # Shell script to update SQLite3 tables from Bacula Community version 3.0.x to 5.0.0
9 echo "This script will update a Bacula SQLite3 database from version 11 to 12"
10 echo " which is needed to convert from Bacula Community version 3.0.x to 5.0.x"
13 bindir=@SQLITE_BINDIR@
18 DBV=`${sqlite} ${db_name}.db <<END
19 select VersionId from Version;
21 if [ $DBVERSION != 11 ] ; then
23 echo "The existing database is version $DBVERSION !!"
24 echo "This script can only update an existing version 11 database to version 12."
25 echo "Error. Cannot upgrade this database."
30 sqlite3 $* ${db_name}.db <<END-OF-DATA
32 ALTER TABLE Job ADD COLUMN HasCache TINYINT DEFAULT 0;
33 ALTER TABLE Job ADD COLUMN Reviewed TINYINT DEFAULT 0;
34 ALTER TABLE Job ADD COLUMN Comment TEXT;
35 ALTER TABLE JobHisto ADD COLUMN HasCache TINYINT DEFAULT 0;
36 ALTER TABLE JobHisto ADD COLUMN Reviewed TINYINT DEFAULT 0;
37 ALTER TABLE JobHisto ADD COLUMN Comment TEXT;
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)
66 CREATE INDEX pathvisibility_jobid
67 ON PathVisibility (JobId);
69 CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
71 UPDATE Version SET VersionId=12;
75 DROP INDEX IF EXISTS inx9;
76 CREATE INDEX file_jpf_idx ON File (JobId, PathId, FilenameId);