3 # shell script to update SQLite from version 1.32 to 1.33
6 echo "Depending on the size of your database,"
7 echo "this script may take several minutes to run."
13 $bindir/sqlite $* bacula.db <<END-OF-DATA
16 CREATE TEMPORARY TABLE Media_backup (
17 MediaId INTEGER UNSIGNED AUTOINCREMENT,
18 VolumeName VARCHAR(128) NOT NULL,
19 Slot INTEGER DEFAULT 0,
20 PoolId INTEGER UNSIGNED REFERENCES Pool NOT NULL,
21 MediaType VARCHAR(128) NOT NULL,
22 FirstWritten DATETIME DEFAULT 0,
23 LastWritten DATETIME DEFAULT 0,
24 LabelDate DATETIME DEFAULT 0,
25 VolJobs INTEGER UNSIGNED DEFAULT 0,
26 VolFiles INTEGER UNSIGNED DEFAULT 0,
27 VolBlocks INTEGER UNSIGNED DEFAULT 0,
28 VolMounts INTEGER UNSIGNED DEFAULT 0,
29 VolBytes BIGINT UNSIGNED DEFAULT 0,
30 VolErrors INTEGER UNSIGNED DEFAULT 0,
31 VolWrites INTEGER UNSIGNED DEFAULT 0,
32 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
33 VolStatus VARCHAR(20) NOT NULL,
34 Recycle TINYINT DEFAULT 0,
35 VolRetention BIGINT UNSIGNED DEFAULT 0,
36 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
37 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
38 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
39 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
40 InChanger TINYINT DEFAULT 0,
41 MediaAddressing TINYINT DEFAULT 0,
42 VolReadTime BIGINT UNSIGNED DEFAULT 0,
43 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
47 INSERT INTO Media_backup SELECT
48 MediaId, VolumeName, Slot, PoolId,
49 MediaType, FirstWritten, LastWritten,
50 LabelDate, VolJobs, VolFiles, VolBlocks,
51 VolMounts, VolBytes, VolErrors, VolWrites,
52 VolCapacityBytes, VolStatus, Recycle,
53 VolRetention, VolUseDuration, MaxVolJobs,
54 MaxVolFiles, MaxVolBytes, 0, 0, 0, 0
61 MediaId INTEGER UNSIGNED AUTOINCREMENT,
62 VolumeName VARCHAR(128) NOT NULL,
63 Slot INTEGER DEFAULT 0,
64 PoolId INTEGER UNSIGNED REFERENCES Pool NOT NULL,
65 MediaType VARCHAR(128) NOT NULL,
66 FirstWritten DATETIME DEFAULT 0,
67 LastWritten DATETIME DEFAULT 0,
68 LabelDate DATETIME DEFAULT 0,
69 VolJobs INTEGER UNSIGNED DEFAULT 0,
70 VolFiles INTEGER UNSIGNED DEFAULT 0,
71 VolBlocks INTEGER UNSIGNED DEFAULT 0,
72 VolMounts INTEGER UNSIGNED DEFAULT 0,
73 VolBytes BIGINT UNSIGNED DEFAULT 0,
74 VolErrors INTEGER UNSIGNED DEFAULT 0,
75 VolWrites INTEGER UNSIGNED DEFAULT 0,
76 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
77 VolStatus VARCHAR(20) NOT NULL,
78 Recycle TINYINT DEFAULT 0,
79 VolRetention BIGINT UNSIGNED DEFAULT 0,
80 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
81 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
82 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
83 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
84 InChanger TINYINT DEFAULT 0,
85 MediaAddressing TINYINT DEFAULT 0,
86 VolReadTime BIGINT UNSIGNED DEFAULT 0,
87 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
92 MediaId, VolumeName, Slot, PoolId,
93 MediaType, FirstWritten, LastWritten,
94 LabelDate, VolJobs, VolFiles, VolBlocks,
95 VolMounts, VolBytes, VolErrors, VolWrites,
96 VolCapacityBytes, VolStatus, Recycle,
97 VolRetention, VolUseDuration, MaxVolJobs,
98 MaxVolFiles, MaxVolBytes,
99 InChanger, MediaAddressing,
100 VolReadTime, VolWriteTime,
101 SELECT * FROM Media_backup;
103 DROP TABLE Media_backup;
105 CREATE INDEX inx8 ON Media (PoolId);
107 CREATE TABLE Pool_backup (
108 PoolId INTEGER UNSIGNED AUTOINCREMENT,
109 Name VARCHAR(128) NOT NULL,
110 NumVols INTEGER UNSIGNED DEFAULT 0,
111 MaxVols INTEGER UNSIGNED DEFAULT 0,
112 UseOnce TINYINT DEFAULT 0,
113 UseCatalog TINYINT DEFAULT 1,
114 AcceptAnyVolume TINYINT DEFAULT 0,
115 VolRetention BIGINT UNSIGNED DEFAULT 0,
116 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
117 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
118 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
119 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
120 AutoPrune TINYINT DEFAULT 0,
121 Recycle TINYINT DEFAULT 0,
122 PoolType VARCHAR(20) NOT NULL,
123 LabelFormat VARCHAR(128) NOT NULL,
124 Enabled TINYINT DEFAULT 1,
125 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
126 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
131 INSERT INTO Pool_backup SELECT
153 PoolId INTEGER UNSIGNED AUTOINCREMENT,
154 Name VARCHAR(128) NOT NULL,
155 NumVols INTEGER UNSIGNED DEFAULT 0,
156 MaxVols INTEGER UNSIGNED DEFAULT 0,
157 UseOnce TINYINT DEFAULT 0,
158 UseCatalog TINYINT DEFAULT 1,
159 AcceptAnyVolume TINYINT DEFAULT 0,
160 VolRetention BIGINT UNSIGNED DEFAULT 0,
161 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
162 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
163 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
164 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
165 AutoPrune TINYINT DEFAULT 0,
166 Recycle TINYINT DEFAULT 0,
167 PoolType VARCHAR(20) NOT NULL,
168 LabelFormat VARCHAR(128) NOT NULL,
169 Enabled TINYINT DEFAULT 1,
170 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
171 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
192 LabelFormat, Enabled, ScratchPoolId, RecyclePoolId)
193 SELECT * FROM Pool_backup;
195 DROP TABLE Pool_backup;
198 DROP TABLE BaseFiles;
200 CREATE TABLE BaseFiles (
201 BaseId INTEGER UNSIGNED AUTOINCREMENT,
202 BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
203 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
204 FileId INTEGER UNSIGNED REFERENCES File NOT NULL,
205 FileIndex INTEGER UNSIGNED,
212 INSERT INTO Version (VersionId) VALUES (7);