3 # Shell script to update SQLite3 tables from Bacula Community version 3.0.x to 5.0.0
6 echo "This script will update a Bacula SQLite3 database from version 11 to 12"
7 echo " which is needed to convert from Bacula Community version 3.0.x to 5.0.x"
10 bindir=@SQLITE_BINDIR@
15 DBV=`${sqlite} ${db_name}.db <<END
16 select VersionId from Version;
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 sqlite3 $* ${db_name}.db <<END-OF-DATA
29 ALTER TABLE Job ADD COLUMN HasCache TINYINT DEFAULT 0;
30 ALTER TABLE Job ADD COLUMN Reviewed TINYINT DEFAULT 0;
31 ALTER TABLE Job ADD COLUMN Comment TEXT;
32 ALTER TABLE JobHisto ADD COLUMN HasCache TINYINT DEFAULT 0;
33 ALTER TABLE JobHisto ADD COLUMN Reviewed TINYINT DEFAULT 0;
34 ALTER TABLE JobHisto ADD COLUMN Comment TEXT;
36 ALTER TABLE Status ADD COLUMN Severity int;
37 UPDATE Status SET Severity = 15;
38 UPDATE Status SET Severity = 100 where JobStatus = 'f';
39 UPDATE Status SET Severity = 90 where JobStatus = 'A';
40 UPDATE Status SET Severity = 10 where JobStatus = 'T';
41 UPDATE Status SET Severity = 20 where JobStatus = 'e';
42 UPDATE Status SET Severity = 25 where JobStatus = 'E';
44 CREATE TABLE PathHierarchy
46 PathId integer NOT NULL,
47 PPathId integer NOT NULL,
48 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
51 CREATE INDEX pathhierarchy_ppathid
52 ON PathHierarchy (PPathId);
54 CREATE TABLE PathVisibility
56 PathId integer NOT NULL,
57 JobId integer NOT NULL,
60 CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
63 CREATE INDEX pathvisibility_jobid
64 ON PathVisibility (JobId);
66 CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
68 UPDATE Version SET VersionId=12;
72 DROP INDEX IF EXISTS inx9;
73 CREATE INDEX file_jpf_idx ON File (JobId, PathId, FilenameId);