3 # shell script to update SQLite from version 1.32 to 1.33
6 echo "This script will update a bacula database from version 6 to 7."
7 echo "Depending on the size of your database,"
8 echo "this script may take several minutes to run."
11 # The location of the sqlite program
12 bindir=/usr/lib/sqlite
13 # The location of your bacula working directory
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 "6" ]; then
19 echo "Sorry, this script is designed to update a version 6 database"
20 echo "and you have a version $DB_VER database."
24 echo "Sorry, I can't seem to locate a bacula database."
28 $bindir/sqlite $* bacula.db <<END-OF-DATA
31 CREATE TEMPORARY TABLE Media_backup (
32 MediaId INTEGER UNSIGNED AUTOINCREMENT,
33 VolumeName VARCHAR(128) NOT NULL,
34 Slot INTEGER DEFAULT 0,
35 PoolId INTEGER UNSIGNED REFERENCES Pool NOT NULL,
36 MediaType VARCHAR(128) NOT NULL,
37 FirstWritten DATETIME DEFAULT 0,
38 LastWritten DATETIME DEFAULT 0,
39 LabelDate DATETIME DEFAULT 0,
40 VolJobs INTEGER UNSIGNED DEFAULT 0,
41 VolFiles INTEGER UNSIGNED DEFAULT 0,
42 VolBlocks INTEGER UNSIGNED DEFAULT 0,
43 VolMounts INTEGER UNSIGNED DEFAULT 0,
44 VolBytes BIGINT UNSIGNED DEFAULT 0,
45 VolErrors INTEGER UNSIGNED DEFAULT 0,
46 VolWrites INTEGER UNSIGNED DEFAULT 0,
47 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
48 VolStatus VARCHAR(20) NOT NULL,
49 Recycle TINYINT DEFAULT 0,
50 VolRetention BIGINT UNSIGNED DEFAULT 0,
51 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
52 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
53 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
54 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
55 InChanger TINYINT DEFAULT 0,
56 MediaAddressing TINYINT DEFAULT 0,
57 VolReadTime BIGINT UNSIGNED DEFAULT 0,
58 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
62 INSERT INTO Media_backup SELECT
63 MediaId, VolumeName, Slot, PoolId,
64 MediaType, FirstWritten, LastWritten,
65 LabelDate, VolJobs, VolFiles, VolBlocks,
66 VolMounts, VolBytes, VolErrors, VolWrites,
67 VolCapacityBytes, VolStatus, Recycle,
68 VolRetention, VolUseDuration, MaxVolJobs,
69 MaxVolFiles, MaxVolBytes, 0, 0, 0, 0
76 MediaId INTEGER UNSIGNED AUTOINCREMENT,
77 VolumeName VARCHAR(128) NOT NULL,
78 Slot INTEGER DEFAULT 0,
79 PoolId INTEGER UNSIGNED REFERENCES Pool NOT NULL,
80 MediaType VARCHAR(128) NOT NULL,
81 FirstWritten DATETIME DEFAULT 0,
82 LastWritten DATETIME DEFAULT 0,
83 LabelDate DATETIME DEFAULT 0,
84 VolJobs INTEGER UNSIGNED DEFAULT 0,
85 VolFiles INTEGER UNSIGNED DEFAULT 0,
86 VolBlocks INTEGER UNSIGNED DEFAULT 0,
87 VolMounts INTEGER UNSIGNED DEFAULT 0,
88 VolBytes BIGINT UNSIGNED DEFAULT 0,
89 VolErrors INTEGER UNSIGNED DEFAULT 0,
90 VolWrites INTEGER UNSIGNED DEFAULT 0,
91 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
92 VolStatus VARCHAR(20) NOT NULL,
93 Recycle TINYINT DEFAULT 0,
94 VolRetention BIGINT UNSIGNED DEFAULT 0,
95 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
96 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
97 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
98 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
99 InChanger TINYINT DEFAULT 0,
100 MediaAddressing TINYINT DEFAULT 0,
101 VolReadTime BIGINT UNSIGNED DEFAULT 0,
102 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
107 MediaId, VolumeName, Slot, PoolId,
108 MediaType, FirstWritten, LastWritten,
109 LabelDate, VolJobs, VolFiles, VolBlocks,
110 VolMounts, VolBytes, VolErrors, VolWrites,
111 VolCapacityBytes, VolStatus, Recycle,
112 VolRetention, VolUseDuration, MaxVolJobs,
113 MaxVolFiles, MaxVolBytes,
114 InChanger, MediaAddressing,
115 VolReadTime, VolWriteTime)
116 SELECT * FROM Media_backup;
118 DROP TABLE Media_backup;
120 CREATE INDEX inx8 ON Media (PoolId);
122 CREATE TABLE Pool_backup (
123 PoolId INTEGER UNSIGNED AUTOINCREMENT,
124 Name VARCHAR(128) NOT NULL,
125 NumVols INTEGER UNSIGNED DEFAULT 0,
126 MaxVols INTEGER UNSIGNED DEFAULT 0,
127 UseOnce TINYINT DEFAULT 0,
128 UseCatalog TINYINT DEFAULT 1,
129 AcceptAnyVolume TINYINT DEFAULT 0,
130 VolRetention BIGINT UNSIGNED DEFAULT 0,
131 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
132 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
133 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
134 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
135 AutoPrune TINYINT DEFAULT 0,
136 Recycle TINYINT DEFAULT 0,
137 PoolType VARCHAR(20) NOT NULL,
138 LabelFormat VARCHAR(128) NOT NULL,
139 Enabled TINYINT DEFAULT 1,
140 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
141 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
146 INSERT INTO Pool_backup SELECT
168 PoolId INTEGER UNSIGNED AUTOINCREMENT,
169 Name VARCHAR(128) NOT NULL,
170 NumVols INTEGER UNSIGNED DEFAULT 0,
171 MaxVols INTEGER UNSIGNED DEFAULT 0,
172 UseOnce TINYINT DEFAULT 0,
173 UseCatalog TINYINT DEFAULT 1,
174 AcceptAnyVolume TINYINT DEFAULT 0,
175 VolRetention BIGINT UNSIGNED DEFAULT 0,
176 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
177 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
178 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
179 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
180 AutoPrune TINYINT DEFAULT 0,
181 Recycle TINYINT DEFAULT 0,
182 PoolType VARCHAR(20) NOT NULL,
183 LabelFormat VARCHAR(128) NOT NULL,
184 Enabled TINYINT DEFAULT 1,
185 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
186 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
207 LabelFormat, Enabled, ScratchPoolId, RecyclePoolId)
208 SELECT * FROM Pool_backup;
210 DROP TABLE Pool_backup;
213 DROP TABLE BaseFiles;
215 CREATE TABLE BaseFiles (
216 BaseId INTEGER UNSIGNED AUTOINCREMENT,
217 BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
218 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
219 FileId INTEGER UNSIGNED REFERENCES File NOT NULL,
220 FileIndex INTEGER UNSIGNED,
227 INSERT INTO Version (VersionId) VALUES (7);