1#!/bin/sh 2# 3# shell script to update SQLite from version 1.34 to 1.35.5 4# 5echo " " 6echo "This script will update a Bacula SQLite database from version 7 to 8" 7echo "Depending on the size of your database," 8echo "this script may take several minutes to run." 9echo " " 10 11bindir=/usr/bin 12 13# The location of your bacula working directory 14workdir=/var/lib/bacula 15 16 17cd $workdir 18 19if [ ! -r bacula.db -o ! -s bacula.db ];then 20 echo "Sorry, can't find a Bacula DB. Aborting." 21 exit 1 22fi 23 24DB_VER="`echo "select * from Version;" | $bindir/sqlite bacula.db | tail -1 2>/dev/null`" 25if [ -n "$DB_VER" ]; then 26 27 if [ "$DB_VER" = "8" ]; then 28 echo "The Catalog is already at version 8. Nothing to do!" 29 exit 0 30 elif [ "$DB_VER" -ne "7" ]; then 31 echo "Sorry, this script is designed to update a version 7 database" 32 echo "and you have a version $DB_VER database." 33 exit 1 34 fi 35 36else 37 echo "Sorry, I can't seem to locate a bacula database." 38 exit 1 39fi 40 41 42$bindir/sqlite $* bacula.db <<END_OF_DATA 43 44BEGIN TRANSACTION; 45CREATE TEMPORARY TABLE Media_backup ( 46 MediaId INTEGER UNSIGNED AUTOINCREMENT, 47 VolumeName VARCHAR(128) NOT NULL, 48 Slot INTEGER DEFAULT 0, 49 PoolId INTEGER UNSIGNED REFERENCES Pool NOT NULL, 50 MediaType VARCHAR(128) NOT NULL, 51 FirstWritten DATETIME DEFAULT 0, 52 LastWritten DATETIME DEFAULT 0, 53 LabelDate DATETIME DEFAULT 0, 54 VolJobs INTEGER UNSIGNED DEFAULT 0, 55 VolFiles INTEGER UNSIGNED DEFAULT 0, 56 VolBlocks INTEGER UNSIGNED DEFAULT 0, 57 VolMounts INTEGER UNSIGNED DEFAULT 0, 58 VolBytes BIGINT UNSIGNED DEFAULT 0, 59 VolErrors INTEGER UNSIGNED DEFAULT 0, 60 VolWrites INTEGER UNSIGNED DEFAULT 0, 61 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0, 62 VolStatus VARCHAR(20) NOT NULL, 63 Recycle TINYINT DEFAULT 0, 64 VolRetention BIGINT UNSIGNED DEFAULT 0, 65 VolUseDuration BIGINT UNSIGNED DEFAULT 0, 66 MaxVolJobs INTEGER UNSIGNED DEFAULT 0, 67 MaxVolFiles INTEGER UNSIGNED DEFAULT 0, 68 MaxVolBytes BIGINT UNSIGNED DEFAULT 0, 69 InChanger TINYINT DEFAULT 0, 70 MediaAddressing TINYINT DEFAULT 0, 71 VolReadTime BIGINT UNSIGNED DEFAULT 0, 72 VolWriteTime BIGINT UNSIGNED DEFAULT 0, 73 EndFile INTEGER UNSIGNED DEFAULT 0, 74 EndBlock INTEGER UNSIGNED DEFAULT 0, 75 PRIMARY KEY(MediaId) 76 ); 77 78INSERT INTO Media_backup SELECT 79 MediaId, VolumeName, Slot, PoolId, 80 MediaType, FirstWritten, LastWritten, 81 LabelDate, VolJobs, VolFiles, VolBlocks, 82 VolMounts, VolBytes, VolErrors, VolWrites, 83 VolCapacityBytes, VolStatus, Recycle, 84 VolRetention, VolUseDuration, MaxVolJobs, 85 MaxVolFiles, MaxVolBytes, InChanger, MediaAddressing, 86 VolReadTime, VolWriteTime, 0, 0 87 FROM Media; 88 89 90DROP TABLE Media; 91 92CREATE TABLE Media ( 93 MediaId INTEGER UNSIGNED AUTOINCREMENT, 94 VolumeName VARCHAR(128) NOT NULL, 95 Slot INTEGER DEFAULT 0, 96 PoolId INTEGER UNSIGNED REFERENCES Pool NOT NULL, 97 MediaType VARCHAR(128) NOT NULL, 98 FirstWritten DATETIME DEFAULT 0, 99 LastWritten DATETIME DEFAULT 0, 100 LabelDate DATETIME DEFAULT 0, 101 VolJobs INTEGER UNSIGNED DEFAULT 0, 102 VolFiles INTEGER UNSIGNED DEFAULT 0, 103 VolBlocks INTEGER UNSIGNED DEFAULT 0, 104 VolMounts INTEGER UNSIGNED DEFAULT 0, 105 VolBytes BIGINT UNSIGNED DEFAULT 0, 106 VolErrors INTEGER UNSIGNED DEFAULT 0, 107 VolWrites INTEGER UNSIGNED DEFAULT 0, 108 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0, 109 VolStatus VARCHAR(20) NOT NULL, 110 Recycle TINYINT DEFAULT 0, 111 VolRetention BIGINT UNSIGNED DEFAULT 0, 112 VolUseDuration BIGINT UNSIGNED DEFAULT 0, 113 MaxVolJobs INTEGER UNSIGNED DEFAULT 0, 114 MaxVolFiles INTEGER UNSIGNED DEFAULT 0, 115 MaxVolBytes BIGINT UNSIGNED DEFAULT 0, 116 InChanger TINYINT DEFAULT 0, 117 MediaAddressing TINYINT DEFAULT 0, 118 VolReadTime BIGINT UNSIGNED DEFAULT 0, 119 VolWriteTime BIGINT UNSIGNED DEFAULT 0, 120 EndFile INTEGER UNSIGNED DEFAULT 0, 121 EndBlock INTEGER UNSIGNED DEFAULT 0, 122 PRIMARY KEY(MediaId) 123 ); 124 125INSERT INTO Media ( 126 MediaId, VolumeName, Slot, PoolId, 127 MediaType, FirstWritten, LastWritten, 128 LabelDate, VolJobs, VolFiles, VolBlocks, 129 VolMounts, VolBytes, VolErrors, VolWrites, 130 VolCapacityBytes, VolStatus, Recycle, 131 VolRetention, VolUseDuration, MaxVolJobs, 132 MaxVolFiles, MaxVolBytes, 133 InChanger, MediaAddressing, 134 VolReadTime, VolWriteTime, 135 EndFile, EndBlock) 136 SELECT * FROM Media_backup; 137 138DROP TABLE Media_backup; 139 140CREATE INDEX inx9 ON File (JobId, PathId, FileNameId); 141 142COMMIT; 143 144UPDATE Filename SET Name='' WHERE Name=' '; 145 146DELETE FROM Version; 147INSERT INTO Version (VersionId) VALUES (8); 148 149END_OF_DATA 150