| 1 |
#!/bin/sh |
| 2 |
# |
| 3 |
# shell script to update SQLite from version 1.29 to 1.30 |
| 4 |
# |
| 5 |
echo " " |
| 6 |
echo "This script will update a bacula database from version 5 to 6." |
| 7 |
echo "Depending on the size of your database," |
| 8 |
echo "this script may take several minutes to run." |
| 9 |
echo " " |
| 10 |
|
| 11 |
# The location of the sqlite program |
| 12 |
bindir=/usr/lib/sqlite |
| 13 |
# The location of your bacula working directory |
| 14 |
cd /var/bacula |
| 15 |
|
| 16 |
if [ -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 |
| 23 |
else |
| 24 |
echo "Sorry, I can't seem to locate a bacula database." |
| 25 |
exit 1 |
| 26 |
fi |
| 27 |
|
| 28 |
$bindir/sqlite $* bacula.db <<END-OF-DATA |
| 29 |
|
| 30 |
BEGIN TRANSACTION; |
| 31 |
CREATE 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 |
); |
| 37 |
INSERT INTO FileSet_backup SELECT * From FileSet; |
| 38 |
|
| 39 |
DROP TABLE FileSet; |
| 40 |
CREATE 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 |
); |
| 47 |
INSERT 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 |
|
| 53 |
DROP TABLE FileSet_backup; |
| 54 |
COMMIT; |
| 55 |
|
| 56 |
|
| 57 |
BEGIN TRANSACTION; |
| 58 |
CREATE 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 |
); |
| 80 |
INSERT INTO Job_backup SELECT * From Job; |
| 81 |
|
| 82 |
DROP TABLE Job; |
| 83 |
CREATE 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 |
); |
| 107 |
INSERT 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; |
| 113 |
DROP TABLE Job_backup; |
| 114 |
COMMIT; |
| 115 |
|
| 116 |
|
| 117 |
BEGIN TRANSACTION; |
| 118 |
CREATE 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 |
); |
| 130 |
INSERT INTO JobMedia_backup SELECT * From JobMedia; |
| 131 |
|
| 132 |
DROP TABLE JobMedia; |
| 133 |
CREATE 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 |
); |
| 146 |
INSERT INTO JobMedia ( |
| 147 |
JobMediaId,JobId,MediaId,FirstIndex, |
| 148 |
LastIndex,StartFile,EndFile,StartBlock, |
| 149 |
EndBlock) |
| 150 |
SELECT * FROM JobMedia_backup; |
| 151 |
DROP TABLE JobMedia_backup; |
| 152 |
COMMIT; |
| 153 |
|
| 154 |
CREATE 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 |
|
| 162 |
DROP TABLE Counters; |
| 163 |
|
| 164 |
CREATE 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 |
|
| 173 |
UPDATE Version SET VersionId=6; |
| 174 |
|
| 175 |
END-OF-DATA |