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 Drive INTEGER DEFAULT 0,
41 InChanger TINYINT DEFAULT 0,
42 MediaAddressing TINYINT DEFAULT 0,
46 INSERT INTO Media_backup SELECT
47 MediaId, VolumeName, Slot, PoolId,
48 MediaType, FirstWritten, LastWritten,
49 LabelDate, VolJobs, VolFiles, VolBlocks,
50 VolMounts, VolBytes, VolErrors, VolWrites,
51 VolCapacityBytes, VolStatus, Recycle,
52 VolRetention, VolUseDuration, MaxVolJobs,
53 MaxVolFiles, MaxVolBytes, 0, 0, 0
60 MediaId INTEGER UNSIGNED AUTOINCREMENT,
61 VolumeName VARCHAR(128) NOT NULL,
62 Slot INTEGER DEFAULT 0,
63 PoolId INTEGER UNSIGNED REFERENCES Pool NOT NULL,
64 MediaType VARCHAR(128) NOT NULL,
65 FirstWritten DATETIME DEFAULT 0,
66 LastWritten DATETIME DEFAULT 0,
67 LabelDate DATETIME DEFAULT 0,
68 VolJobs INTEGER UNSIGNED DEFAULT 0,
69 VolFiles INTEGER UNSIGNED DEFAULT 0,
70 VolBlocks INTEGER UNSIGNED DEFAULT 0,
71 VolMounts INTEGER UNSIGNED DEFAULT 0,
72 VolBytes BIGINT UNSIGNED DEFAULT 0,
73 VolErrors INTEGER UNSIGNED DEFAULT 0,
74 VolWrites INTEGER UNSIGNED DEFAULT 0,
75 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
76 VolStatus VARCHAR(20) NOT NULL,
77 Recycle TINYINT DEFAULT 0,
78 VolRetention BIGINT UNSIGNED DEFAULT 0,
79 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
80 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
81 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
82 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
83 Drive INTEGER DEFAULT 0,
84 InChanger TINYINT DEFAULT 0,
85 MediaAddressing TINYINT DEFAULT 0,
90 MediaId, VolumeName, Slot, PoolId,
91 MediaType, FirstWritten, LastWritten,
92 LabelDate, VolJobs, VolFiles, VolBlocks,
93 VolMounts, VolBytes, VolErrors, VolWrites,
94 VolCapacityBytes, VolStatus, Recycle,
95 VolRetention, VolUseDuration, MaxVolJobs,
96 MaxVolFiles, MaxVolBytes, Drive,
97 InChanger, MediaAddressing)
98 SELECT * FROM Media_backup;
100 DROP TABLE Media_backup;
102 CREATE INDEX inx8 ON Media (PoolId);
104 CREATE TABLE Pool_backup (
105 PoolId INTEGER UNSIGNED AUTOINCREMENT,
106 Name VARCHAR(128) NOT NULL,
107 NumVols INTEGER UNSIGNED DEFAULT 0,
108 MaxVols INTEGER UNSIGNED DEFAULT 0,
109 UseOnce TINYINT DEFAULT 0,
110 UseCatalog TINYINT DEFAULT 1,
111 AcceptAnyVolume TINYINT DEFAULT 0,
112 VolRetention BIGINT UNSIGNED DEFAULT 0,
113 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
114 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
115 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
116 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
117 AutoPrune TINYINT DEFAULT 0,
118 Recycle TINYINT DEFAULT 0,
119 PoolType VARCHAR(20) NOT NULL,
120 LabelFormat VARCHAR(128) NOT NULL,
121 Enabled TINYINT DEFAULT 1,
122 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
123 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
128 INSERT INTO Pool_backup SELECT
150 PoolId INTEGER UNSIGNED AUTOINCREMENT,
151 Name VARCHAR(128) NOT NULL,
152 NumVols INTEGER UNSIGNED DEFAULT 0,
153 MaxVols INTEGER UNSIGNED DEFAULT 0,
154 UseOnce TINYINT DEFAULT 0,
155 UseCatalog TINYINT DEFAULT 1,
156 AcceptAnyVolume TINYINT DEFAULT 0,
157 VolRetention BIGINT UNSIGNED DEFAULT 0,
158 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
159 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
160 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
161 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
162 AutoPrune TINYINT DEFAULT 0,
163 Recycle TINYINT DEFAULT 0,
164 PoolType VARCHAR(20) NOT NULL,
165 LabelFormat VARCHAR(128) NOT NULL,
166 Enabled TINYINT DEFAULT 1,
167 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
168 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
189 LabelFormat, Enabled, ScratchPoolId, RecyclePoolId)
190 SELECT * FROM Pool_backup;
192 DROP TABLE Pool_backup;
195 DROP TABLE BaseFiles;
197 CREATE TABLE BaseFiles (
198 BaseId INTEGER UNSIGNED AUTOINCREMENT,
199 BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
200 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
201 FileId INTEGER UNSIGNED REFERENCES File NOT NULL,
202 FileIndex INTEGER UNSIGNED,
208 UPDATE Version SET VersionId=7;