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 $bindir/sqlite $* bacula.db <<END-OF-DATA
19 CREATE TEMPORARY TABLE Media_backup (
20 MediaId INTEGER UNSIGNED AUTOINCREMENT,
21 VolumeName VARCHAR(128) NOT NULL,
22 Slot INTEGER DEFAULT 0,
23 PoolId INTEGER UNSIGNED REFERENCES Pool NOT NULL,
24 MediaType VARCHAR(128) NOT NULL,
25 FirstWritten DATETIME DEFAULT 0,
26 LastWritten DATETIME DEFAULT 0,
27 LabelDate DATETIME DEFAULT 0,
28 VolJobs INTEGER UNSIGNED DEFAULT 0,
29 VolFiles INTEGER UNSIGNED DEFAULT 0,
30 VolBlocks INTEGER UNSIGNED DEFAULT 0,
31 VolMounts INTEGER UNSIGNED DEFAULT 0,
32 VolBytes BIGINT UNSIGNED DEFAULT 0,
33 VolErrors INTEGER UNSIGNED DEFAULT 0,
34 VolWrites INTEGER UNSIGNED DEFAULT 0,
35 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
36 VolStatus VARCHAR(20) NOT NULL,
37 Recycle TINYINT DEFAULT 0,
38 VolRetention BIGINT UNSIGNED DEFAULT 0,
39 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
40 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
41 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
42 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
43 InChanger TINYINT DEFAULT 0,
44 MediaAddressing TINYINT DEFAULT 0,
45 VolReadTime BIGINT UNSIGNED DEFAULT 0,
46 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
50 INSERT INTO Media_backup SELECT
51 MediaId, VolumeName, Slot, PoolId,
52 MediaType, FirstWritten, LastWritten,
53 LabelDate, VolJobs, VolFiles, VolBlocks,
54 VolMounts, VolBytes, VolErrors, VolWrites,
55 VolCapacityBytes, VolStatus, Recycle,
56 VolRetention, VolUseDuration, MaxVolJobs,
57 MaxVolFiles, MaxVolBytes, 0, 0, 0, 0
64 MediaId INTEGER UNSIGNED AUTOINCREMENT,
65 VolumeName VARCHAR(128) NOT NULL,
66 Slot INTEGER DEFAULT 0,
67 PoolId INTEGER UNSIGNED REFERENCES Pool NOT NULL,
68 MediaType VARCHAR(128) NOT NULL,
69 FirstWritten DATETIME DEFAULT 0,
70 LastWritten DATETIME DEFAULT 0,
71 LabelDate DATETIME DEFAULT 0,
72 VolJobs INTEGER UNSIGNED DEFAULT 0,
73 VolFiles INTEGER UNSIGNED DEFAULT 0,
74 VolBlocks INTEGER UNSIGNED DEFAULT 0,
75 VolMounts INTEGER UNSIGNED DEFAULT 0,
76 VolBytes BIGINT UNSIGNED DEFAULT 0,
77 VolErrors INTEGER UNSIGNED DEFAULT 0,
78 VolWrites INTEGER UNSIGNED DEFAULT 0,
79 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
80 VolStatus VARCHAR(20) NOT NULL,
81 Recycle TINYINT DEFAULT 0,
82 VolRetention BIGINT UNSIGNED DEFAULT 0,
83 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
84 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
85 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
86 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
87 InChanger TINYINT DEFAULT 0,
88 MediaAddressing TINYINT DEFAULT 0,
89 VolReadTime BIGINT UNSIGNED DEFAULT 0,
90 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
95 MediaId, VolumeName, Slot, PoolId,
96 MediaType, FirstWritten, LastWritten,
97 LabelDate, VolJobs, VolFiles, VolBlocks,
98 VolMounts, VolBytes, VolErrors, VolWrites,
99 VolCapacityBytes, VolStatus, Recycle,
100 VolRetention, VolUseDuration, MaxVolJobs,
101 MaxVolFiles, MaxVolBytes,
102 InChanger, MediaAddressing,
103 VolReadTime, VolWriteTime,
104 SELECT * FROM Media_backup;
106 DROP TABLE Media_backup;
108 CREATE INDEX inx8 ON Media (PoolId);
110 CREATE TABLE Pool_backup (
111 PoolId INTEGER UNSIGNED AUTOINCREMENT,
112 Name VARCHAR(128) NOT NULL,
113 NumVols INTEGER UNSIGNED DEFAULT 0,
114 MaxVols INTEGER UNSIGNED DEFAULT 0,
115 UseOnce TINYINT DEFAULT 0,
116 UseCatalog TINYINT DEFAULT 1,
117 AcceptAnyVolume TINYINT DEFAULT 0,
118 VolRetention BIGINT UNSIGNED DEFAULT 0,
119 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
120 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
121 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
122 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
123 AutoPrune TINYINT DEFAULT 0,
124 Recycle TINYINT DEFAULT 0,
125 PoolType VARCHAR(20) NOT NULL,
126 LabelFormat VARCHAR(128) NOT NULL,
127 Enabled TINYINT DEFAULT 1,
128 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
129 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
134 INSERT INTO Pool_backup SELECT
156 PoolId INTEGER UNSIGNED AUTOINCREMENT,
157 Name VARCHAR(128) NOT NULL,
158 NumVols INTEGER UNSIGNED DEFAULT 0,
159 MaxVols INTEGER UNSIGNED DEFAULT 0,
160 UseOnce TINYINT DEFAULT 0,
161 UseCatalog TINYINT DEFAULT 1,
162 AcceptAnyVolume TINYINT DEFAULT 0,
163 VolRetention BIGINT UNSIGNED DEFAULT 0,
164 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
165 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
166 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
167 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
168 AutoPrune TINYINT DEFAULT 0,
169 Recycle TINYINT DEFAULT 0,
170 PoolType VARCHAR(20) NOT NULL,
171 LabelFormat VARCHAR(128) NOT NULL,
172 Enabled TINYINT DEFAULT 1,
173 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
174 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
195 LabelFormat, Enabled, ScratchPoolId, RecyclePoolId)
196 SELECT * FROM Pool_backup;
198 DROP TABLE Pool_backup;
201 DROP TABLE BaseFiles;
203 CREATE TABLE BaseFiles (
204 BaseId INTEGER UNSIGNED AUTOINCREMENT,
205 BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
206 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
207 FileId INTEGER UNSIGNED REFERENCES File NOT NULL,
208 FileIndex INTEGER UNSIGNED,
215 INSERT INTO Version (VersionId) VALUES (7);