1#!/bin/sh 2# 3# shell script to update SQLite from version 1.29 to 1.30 4# 5echo " " 6echo "This script will update a bacula database from version 5 to 6." 7echo "Depending on the size of your database," 8echo "this script may take several minutes to run." 9echo " " 10 11# The location of the sqlite program 12bindir=/usr/lib/sqlite 13# The location of your bacula working directory 14cd /var/bacula 15 16if [ -s bacula.db ];then 17 DB_VER=`echo "select * from Version;" | $bindir/sqlite bacula.db | tail -n 1 2>/dev/null` 18 if [ -n "$DB_VER" ] && [ "$DB_VER" -ne "5" ]; then 19 echo "Sorry, this script is designed to update a version 5 database" 20 echo "and you have a version $DB_VER database." 21 exit 1 22 fi 23else 24 echo "Sorry, I can't seem to locate a bacula database." 25 exit 1 26fi 27 28$bindir/sqlite $* bacula.db <<END-OF-DATA 29 30BEGIN TRANSACTION; 31CREATE TEMPORARY TABLE FileSet_backup ( 32 FileSetId INTEGER UNSIGNED AUTOINCREMENT, 33 FileSet VARCHAR(128) NOT NULL, 34 MD5 VARCHAR(25) NOT NULL, 35 PRIMARY KEY(FileSetId) 36 ); 37INSERT INTO FileSet_backup SELECT * From FileSet; 38 39DROP TABLE FileSet; 40CREATE TABLE FileSet ( 41 FileSetId INTEGER UNSIGNED AUTOINCREMENT, 42 FileSet VARCHAR(128) NOT NULL, 43 MD5 VARCHAR(25) NOT NULL, 44 CreateTime DATETIME DEFAULT 0, 45 PRIMARY KEY(FileSetId) 46 ); 47INSERT INTO FileSet ( 48 FileSetId, FileSet, MD5, CreateTime) 49 SELECT FileSet_backup.FileSetId,FileSet,MD5,StartTime FROM FileSet_backup,Job 50 WHERE FileSet_backup.FileSetId=Job.FileSetId GROUP by FileSet_backup.FileSetId 51 ORDER BY Job.FileSetId; 52 53DROP TABLE FileSet_backup; 54COMMIT; 55 56 57BEGIN TRANSACTION; 58CREATE TEMPORARY TABLE Job_backup ( 59 JobId INTEGER UNSIGNED NOT NULL, 60 Job VARCHAR(128) NOT NULL, 61 Name VARCHAR(128) NOT NULL, 62 PurgedFiles TINYINT DEFAULT 0, 63 Type CHAR NOT NULL, 64 Level CHAR NOT NULL, 65 ClientId INTEGER REFERENCES Client DEFAULT 0, 66 JobStatus CHAR NOT NULL, 67 SchedTime DATETIME NOT NULL, 68 StartTime DATETIME DEFAULT 0, 69 EndTime DATETIME DEFAULT 0, 70 JobTDate BIGINT UNSIGNED DEFAULT 0, 71 VolSessionId INTEGER UNSIGNED DEFAULT 0, 72 VolSessionTime INTEGER UNSIGNED DEFAULT 0, 73 JobFiles INTEGER UNSIGNED DEFAULT 0, 74 JobBytes BIGINT UNSIGNED DEFAULT 0, 75 JobErrors INTEGER UNSIGNED DEFAULT 0, 76 JobMissingFiles INTEGER UNSIGNED DEFAULT 0, 77 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0, 78 FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0 79 ); 80INSERT INTO Job_backup SELECT * From Job; 81 82DROP TABLE Job; 83CREATE TABLE Job ( 84 JobId INTEGER UNSIGNED NOT NULL, 85 Job VARCHAR(128) NOT NULL, 86 Name VARCHAR(128) NOT NULL, 87 PurgedFiles TINYINT DEFAULT 0, 88 Type CHAR NOT NULL, 89 Level CHAR NOT NULL, 90 ClientId INTEGER REFERENCES Client DEFAULT 0, 91 JobStatus CHAR NOT NULL, 92 SchedTime DATETIME NOT NULL, 93 StartTime DATETIME DEFAULT 0, 94 EndTime DATETIME DEFAULT 0, 95 JobTDate BIGINT UNSIGNED DEFAULT 0, 96 VolSessionId INTEGER UNSIGNED DEFAULT 0, 97 VolSessionTime INTEGER UNSIGNED DEFAULT 0, 98 JobFiles INTEGER UNSIGNED DEFAULT 0, 99 JobBytes BIGINT UNSIGNED DEFAULT 0, 100 JobErrors INTEGER UNSIGNED DEFAULT 0, 101 JobMissingFiles INTEGER UNSIGNED DEFAULT 0, 102 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0, 103 FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0, 104 HasBase TINYINT DEFAULT 0, 105 PRIMARY KEY(JobId) 106 ); 107INSERT INTO Job (JobId,Job,Name,PurgedFiles, 108 Type,Level,ClientId,JobStatus,SchedTime, 109 StartTime,EndTime,JobTDate,VolSessionId, 110 VolSessionTime,JobFiles,JobBytes,JobErrors, 111 JobMissingFiles,PoolId,FileSetId) 112 SELECT * FROM Job_backup; 113DROP TABLE Job_backup; 114COMMIT; 115 116 117BEGIN TRANSACTION; 118CREATE TEMPORARY TABLE JobMedia_backup ( 119 JobMediaId INTEGER UNSIGNED AUTOINCREMENT, 120 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL, 121 MediaId INTEGER UNSIGNED REFERENCES Media NOT NULL, 122 FirstIndex INTEGER UNSIGNED NOT NULL, 123 LastIndex INTEGER UNSIGNED NOT NULL, 124 StartFile INTEGER UNSIGNED DEFAULT 0, 125 EndFile INTEGER UNSIGNED DEFAULT 0, 126 StartBlock INTEGER UNSIGNED DEFAULT 0, 127 EndBlock INTEGER UNSIGNED DEFAULT 0, 128 PRIMARY KEY(JobMediaId) 129 ); 130INSERT INTO JobMedia_backup SELECT * From JobMedia; 131 132DROP TABLE JobMedia; 133CREATE TABLE JobMedia ( 134 JobMediaId INTEGER UNSIGNED AUTOINCREMENT, 135 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL, 136 MediaId INTEGER UNSIGNED REFERENCES Media NOT NULL, 137 FirstIndex INTEGER UNSIGNED NOT NULL, 138 LastIndex INTEGER UNSIGNED NOT NULL, 139 StartFile INTEGER UNSIGNED DEFAULT 0, 140 EndFile INTEGER UNSIGNED DEFAULT 0, 141 StartBlock INTEGER UNSIGNED DEFAULT 0, 142 EndBlock INTEGER UNSIGNED DEFAULT 0, 143 VolIndex INTEGER UNSIGNED DEFAULT 0, 144 PRIMARY KEY(JobMediaId) 145 ); 146INSERT INTO JobMedia ( 147 JobMediaId,JobId,MediaId,FirstIndex, 148 LastIndex,StartFile,EndFile,StartBlock, 149 EndBlock) 150 SELECT * FROM JobMedia_backup; 151DROP TABLE JobMedia_backup; 152COMMIT; 153 154CREATE TABLE BaseFiles ( 155 BaseId INTEGER UNSIGNED AUTOINCREMENT, 156 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL, 157 FileId INTEGER UNSIGNED REFERENCES File NOT NULL, 158 FileIndex INTEGER UNSIGNED, 159 PRIMARY KEY(BaseId) 160 ); 161 162DROP TABLE Counters; 163 164CREATE TABLE Counters ( 165 Counter TEXT NOT NULL, 166 MinValue INTEGER DEFAULT 0, 167 MaxValue INTEGER DEFAULT 0, 168 CurrentValue INTEGER DEFAULT 0, 169 WrapCounter TEXT NOT NULL, 170 PRIMARY KEY (Counter) 171 ); 172 173UPDATE Version SET VersionId=6; 174 175END-OF-DATA 176