3 # shell script to update SQLite from version 1.38 to 1.39
6 echo "This script will update a Bacula SQLite database from version 9 to 9"
7 echo "Depending on the size of your database,"
8 echo "this script may take several minutes to run."
15 ${bindir}/${sqlite} $* bacula.db <<END-OF-DATA
18 CREATE TEMPORARY TABLE Media_backup (
19 MediaId INTEGER UNSIGNED AUTOINCREMENT,
20 VolumeName VARCHAR(128) NOT NULL,
21 Slot INTEGER DEFAULT 0,
22 PoolId INTEGER UNSIGNED REFERENCES Pool NOT NULL,
23 MediaType VARCHAR(128) NOT NULL,
24 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType DEFAULT 0,
25 LabelType TINYINT DEFAULT 0,
26 FirstWritten DATETIME DEFAULT 0,
27 LastWritten DATETIME DEFAULT 0,
28 LabelDate DATETIME DEFAULT 0,
29 VolJobs INTEGER UNSIGNED DEFAULT 0,
30 VolFiles INTEGER UNSIGNED DEFAULT 0,
31 VolBlocks INTEGER UNSIGNED DEFAULT 0,
32 VolMounts INTEGER UNSIGNED DEFAULT 0,
33 VolBytes BIGINT UNSIGNED DEFAULT 0,
34 VolParts INTEGER UNSIGNED DEFAULT 0,
35 VolErrors INTEGER UNSIGNED DEFAULT 0,
36 VolWrites INTEGER UNSIGNED DEFAULT 0,
37 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
38 VolStatus VARCHAR(20) NOT NULL,
39 Enabled TINYINT DEFAULT 1,
40 Recycle TINYINT DEFAULT 0,
41 VolRetention BIGINT UNSIGNED DEFAULT 0,
42 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
43 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
44 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
45 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
46 InChanger TINYINT DEFAULT 0,
47 StorageId INTEGER UNSIGNED REFERENCES Storage,
48 DeviceId INTEGER UNSIGNED REFERENCES Device,
49 MediaAddressing TINYINT DEFAULT 0,
50 VolReadTime BIGINT UNSIGNED DEFAULT 0,
51 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
52 EndFile INTEGER UNSIGNED DEFAULT 0,
53 EndBlock INTEGER UNSIGNED DEFAULT 0,
54 LocationId INTEGER UNSIGNED REFERENCES Location,
55 RecycleCount INTEGER UNSIGNED DEFAULT 0,
56 InitialWrite DATETIME DEFAULT 0,
57 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
58 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
62 INSERT INTO Media_backup SELECT
63 MediaId, VolumeName, Slot, PoolId,
64 MediaType, LabelType, 0, FirstWritten, LastWritten,
65 LabelDate, VolJobs, VolFiles, VolBlocks,
66 VolMounts, VolBytes, VolParts, VolErrors, VolWrites,
67 VolCapacityBytes, VolStatus, 1, Recycle,
68 VolRetention, VolUseDuration, MaxVolJobs,
69 MaxVolFiles, MaxVolBytes, InChanger,
70 StorageId, 0, MediaAddressing,
71 VolReadTime, VolWriteTime, EndFile, EndBlock, 0, 0, 0, 0, 0
78 MediaId INTEGER UNSIGNED AUTOINCREMENT,
79 VolumeName VARCHAR(128) NOT NULL,
80 Slot INTEGER DEFAULT 0,
81 PoolId INTEGER UNSIGNED REFERENCES Pool NOT NULL,
82 MediaType VARCHAR(128) NOT NULL,
83 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType NOT NULL,
84 LabelType TINYINT DEFAULT 0,
85 FirstWritten DATETIME DEFAULT 0,
86 LastWritten DATETIME DEFAULT 0,
87 LabelDate DATETIME DEFAULT 0,
88 VolJobs INTEGER UNSIGNED DEFAULT 0,
89 VolFiles INTEGER UNSIGNED DEFAULT 0,
90 VolBlocks INTEGER UNSIGNED DEFAULT 0,
91 VolMounts INTEGER UNSIGNED DEFAULT 0,
92 VolBytes BIGINT UNSIGNED DEFAULT 0,
93 VolParts INTEGER UNSIGNED DEFAULT 0,
94 VolErrors INTEGER UNSIGNED DEFAULT 0,
95 VolWrites INTEGER UNSIGNED DEFAULT 0,
96 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
97 VolStatus VARCHAR(20) NOT NULL,
98 Enabled TINYINT DEFAULT 1,
99 Recycle TINYINT DEFAULT 0,
100 VolRetention BIGINT UNSIGNED DEFAULT 0,
101 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
102 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
103 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
104 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
105 InChanger TINYINT DEFAULT 0,
106 StorageId INTEGER UNSIGNED REFERENCES Storage,
107 DeviceId INTEGER UNSIGNED REFERENCES Device,
108 MediaAddressing TINYINT DEFAULT 0,
109 VolReadTime BIGINT UNSIGNED DEFAULT 0,
110 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
111 EndFile INTEGER UNSIGNED DEFAULT 0,
112 EndBlock INTEGER UNSIGNED DEFAULT 0,
113 LocationId INTEGER UNSIGNED REFERENCES Location,
114 RecycleCount INTEGER UNSIGNED DEFAULT 0,
115 InitialWrite DATETIME DEFAULT 0,
116 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
117 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
122 MediaId, VolumeName, Slot, PoolId,
123 MediaType, MediaTypeId, LabelType, FirstWritten, LastWritten,
124 LabelDate, VolJobs, VolFiles, VolBlocks,
125 VolMounts, VolBytes, VolParts, VolErrors, VolWrites,
126 VolCapacityBytes, VolStatus, Enabled, Recycle,
127 VolRetention, VolUseDuration, MaxVolJobs,
128 MaxVolFiles, MaxVolBytes,
129 InChanger, StorageId, DeviceId, MediaAddressing,
130 VolReadTime, VolWriteTime,
131 EndFile, EndBlock, LocationId, RecycleCount, InitialWrite,
132 ScratchPoolId, RecyclePoolId)
133 SELECT * FROM Media_backup;
136 DROP TABLE Media_backup;
137 CREATE INDEX inx8 ON Media (PoolId);
139 CREATE TEMPORARY TABLE job_backup
142 Job VARCHAR(128) NOT NULL,
143 Name VARCHAR(128) NOT NULL,
146 ClientId INTEGER REFERENCES Client DEFAULT 0,
147 JobStatus CHAR NOT NULL,
148 SchedTime DATETIME NOT NULL,
149 StartTime DATETIME DEFAULT 0,
150 EndTime DATETIME DEFAULT 0,
151 RealEndTime DATETIME DEFAULT 0,
152 JobTDate BIGINT UNSIGNED DEFAULT 0,
153 VolSessionId INTEGER UNSIGNED DEFAULT 0,
154 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
155 JobFiles INTEGER UNSIGNED DEFAULT 0,
156 JobBytes BIGINT UNSIGNED DEFAULT 0,
157 JobErrors INTEGER UNSIGNED DEFAULT 0,
158 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
159 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
160 FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0,
161 PriorJobId INTEGER UNSIGNED REFERENCES Job DEFAULT 0,
162 PurgedFiles TINYINT DEFAULT 0,
163 HasBase TINYINT DEFAULT 0,
167 INSERT INTO Job_backup SELECT
168 JobId, Job, Name, Type, Level, ClientId, JobStatus,
169 SchedTime, StartTime, EndTime, 0,
170 JobTDate, VolSessionId, VolSessionTime,
171 JobFiles, JobBytes, JobErrors, JobMissingFiles,
172 PoolId, FileSetId, 0, PurgedFiles, HasBase,
178 CREATE TEMPORARY TABLE Job
181 Job VARCHAR(128) NOT NULL,
182 Name VARCHAR(128) NOT NULL,
185 ClientId INTEGER REFERENCES Client DEFAULT 0,
186 JobStatus CHAR NOT NULL,
187 SchedTime DATETIME NOT NULL,
188 StartTime DATETIME DEFAULT 0,
189 EndTime DATETIME DEFAULT 0,
190 RealEndTime DATETIME DEFAULT 0,
191 JobTDate BIGINT UNSIGNED DEFAULT 0,
192 VolSessionId INTEGER UNSIGNED DEFAULT 0,
193 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
194 JobFiles INTEGER UNSIGNED DEFAULT 0,
195 JobBytes BIGINT UNSIGNED DEFAULT 0,
196 JobErrors INTEGER UNSIGNED DEFAULT 0,
197 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
198 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
199 FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0,
200 PriorJobId INTEGER UNSIGNED REFERENCES Job DEFAULT 0,
201 PurgedFiles TINYINT DEFAULT 0,
202 HasBase TINYINT DEFAULT 0,
206 INSERT INTO Job SELECT
207 JobId, Job, Name, Type, Level, ClientId, JobStatus,
208 SchedTime, StartTime, EndTime, RealEndTime,
209 JobTDate, VolSessionId, VolSessionTime,
210 JobFiles, JobBytes, JobErrors, JobMissingFiles,
211 PoolId, FileSetId, PriorJobId, PurgedFiles, HasBase
214 DROP TABLE Job_backup;
216 CREATE TABLE Location (
218 Location VARCHAR(128) NOT NULL,
219 Counter INTEGER DEFAULT 0,
220 PRIMARY KEY(LocationId)
225 INSERT INTO Version (VersionId) VALUES (10);