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