3 # Author: Eric Bollengier, 2009
6 # shell script to update SQLite from version 1.38 to 2.0
9 echo "This script will update a Bacula SQLite database from version 9 to 10"
10 echo " which is needed to convert from Bacula version 1.38.x to 2.0.x or higher"
11 echo "Depending on the size of your database,"
12 echo "this script may take several minutes to run."
15 bindir=@SQLITE_BINDIR@
20 sqlite3 $* ${db_name}.db <<END-OF-DATA
23 CREATE TEMPORARY TABLE Media_backup (
24 MediaId INTEGER UNSIGNED AUTOINCREMENT,
25 VolumeName VARCHAR(128) NOT NULL,
26 Slot INTEGER DEFAULT 0,
27 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
28 MediaType VARCHAR(128) NOT NULL,
29 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType DEFAULT 0,
30 LabelType TINYINT DEFAULT 0,
31 FirstWritten DATETIME DEFAULT 0,
32 LastWritten DATETIME DEFAULT 0,
33 LabelDate DATETIME DEFAULT 0,
34 VolJobs INTEGER UNSIGNED DEFAULT 0,
35 VolFiles INTEGER UNSIGNED DEFAULT 0,
36 VolBlocks INTEGER UNSIGNED DEFAULT 0,
37 VolMounts INTEGER UNSIGNED DEFAULT 0,
38 VolBytes BIGINT UNSIGNED DEFAULT 0,
39 VolParts INTEGER UNSIGNED DEFAULT 0,
40 VolErrors INTEGER UNSIGNED DEFAULT 0,
41 VolWrites INTEGER UNSIGNED DEFAULT 0,
42 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
43 VolStatus VARCHAR(20) NOT NULL,
44 Enabled TINYINT DEFAULT 1,
45 Recycle TINYINT DEFAULT 0,
46 VolRetention BIGINT UNSIGNED DEFAULT 0,
47 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
48 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
49 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
50 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
51 InChanger TINYINT DEFAULT 0,
52 StorageId INTEGER UNSIGNED REFERENCES Storage,
53 DeviceId INTEGER UNSIGNED REFERENCES Device,
54 MediaAddressing TINYINT DEFAULT 0,
55 VolReadTime BIGINT UNSIGNED DEFAULT 0,
56 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
57 EndFile INTEGER UNSIGNED DEFAULT 0,
58 EndBlock INTEGER UNSIGNED DEFAULT 0,
59 LocationId INTEGER UNSIGNED REFERENCES Location,
60 RecycleCount INTEGER UNSIGNED DEFAULT 0,
61 InitialWrite DATETIME DEFAULT 0,
62 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
63 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
67 INSERT INTO Media_backup SELECT
68 MediaId, VolumeName, Slot, PoolId,
69 MediaType, LabelType, 0, FirstWritten, LastWritten,
70 LabelDate, VolJobs, VolFiles, VolBlocks,
71 VolMounts, VolBytes, VolParts, VolErrors, VolWrites,
72 VolCapacityBytes, VolStatus, 1, Recycle,
73 VolRetention, VolUseDuration, MaxVolJobs,
74 MaxVolFiles, MaxVolBytes, InChanger,
75 StorageId, 0, MediaAddressing,
76 VolReadTime, VolWriteTime, EndFile, EndBlock, 0, 0, 0, 0, 0
84 VolumeName VARCHAR(128) NOT NULL,
85 Slot INTEGER DEFAULT 0,
86 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
87 MediaType VARCHAR(128) NOT NULL,
88 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType DEFAULT 0,
89 LabelType TINYINT DEFAULT 0,
90 FirstWritten DATETIME DEFAULT 0,
91 LastWritten DATETIME DEFAULT 0,
92 LabelDate DATETIME DEFAULT 0,
93 VolJobs INTEGER UNSIGNED DEFAULT 0,
94 VolFiles INTEGER UNSIGNED DEFAULT 0,
95 VolBlocks INTEGER UNSIGNED DEFAULT 0,
96 VolMounts INTEGER UNSIGNED DEFAULT 0,
97 VolBytes BIGINT UNSIGNED DEFAULT 0,
98 VolParts INTEGER UNSIGNED DEFAULT 0,
99 VolErrors INTEGER UNSIGNED DEFAULT 0,
100 VolWrites INTEGER UNSIGNED DEFAULT 0,
101 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
102 VolStatus VARCHAR(20) NOT NULL,
103 Enabled TINYINT DEFAULT 1,
104 Recycle TINYINT DEFAULT 0,
105 VolRetention BIGINT UNSIGNED DEFAULT 0,
106 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
107 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
108 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
109 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
110 InChanger TINYINT DEFAULT 0,
111 StorageId INTEGER UNSIGNED REFERENCES Storage DEFAULT 0,
112 DeviceId INTEGER UNSIGNED REFERENCES Device DEFAULT 0,
113 MediaAddressing TINYINT DEFAULT 0,
114 VolReadTime BIGINT UNSIGNED DEFAULT 0,
115 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
116 EndFile INTEGER UNSIGNED DEFAULT 0,
117 EndBlock INTEGER UNSIGNED DEFAULT 0,
118 LocationId INTEGER UNSIGNED REFERENCES Location DEFAULT 0,
119 RecycleCount INTEGER UNSIGNED DEFAULT 0,
120 InitialWrite DATETIME DEFAULT 0,
121 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
122 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
128 MediaId, VolumeName, Slot, PoolId,
129 MediaType, MediaTypeId, LabelType, FirstWritten, LastWritten,
130 LabelDate, VolJobs, VolFiles, VolBlocks,
131 VolMounts, VolBytes, VolParts, VolErrors, VolWrites,
132 VolCapacityBytes, VolStatus, Enabled, Recycle,
133 VolRetention, VolUseDuration, MaxVolJobs,
134 MaxVolFiles, MaxVolBytes,
135 InChanger, StorageId, DeviceId, MediaAddressing,
136 VolReadTime, VolWriteTime,
137 EndFile, EndBlock, LocationId, RecycleCount, InitialWrite,
138 ScratchPoolId, RecyclePoolId)
139 SELECT * FROM Media_backup;
142 DROP TABLE Media_backup;
143 CREATE INDEX inx8 ON Media (PoolId);
145 CREATE TEMPORARY TABLE job_backup
148 Job VARCHAR(128) NOT NULL,
149 Name VARCHAR(128) NOT NULL,
152 ClientId INTEGER REFERENCES Client DEFAULT 0,
153 JobStatus CHAR NOT NULL,
154 SchedTime DATETIME NOT NULL,
155 StartTime DATETIME DEFAULT 0,
156 EndTime DATETIME DEFAULT 0,
157 RealEndTime DATETIME DEFAULT 0,
158 JobTDate BIGINT UNSIGNED DEFAULT 0,
159 VolSessionId INTEGER UNSIGNED DEFAULT 0,
160 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
161 JobFiles INTEGER UNSIGNED DEFAULT 0,
162 JobBytes BIGINT UNSIGNED DEFAULT 0,
163 JobErrors INTEGER UNSIGNED DEFAULT 0,
164 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
165 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
166 FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0,
167 PriorJobId INTEGER UNSIGNED REFERENCES Job DEFAULT 0,
168 PurgedFiles TINYINT DEFAULT 0,
169 HasBase TINYINT DEFAULT 0
172 INSERT INTO Job_backup SELECT
173 JobId, Job, Name, Type, Level, ClientId, JobStatus,
174 SchedTime, StartTime, EndTime, 0,
175 JobTDate, VolSessionId, VolSessionTime,
176 JobFiles, JobBytes, JobErrors, JobMissingFiles,
177 PoolId, FileSetId, 0, PurgedFiles, HasBase
185 Job VARCHAR(128) NOT NULL,
186 Name VARCHAR(128) NOT NULL,
189 ClientId INTEGER REFERENCES Client DEFAULT 0,
190 JobStatus CHAR NOT NULL,
191 SchedTime DATETIME NOT NULL,
192 StartTime DATETIME DEFAULT 0,
193 EndTime DATETIME DEFAULT 0,
194 RealEndTime DATETIME DEFAULT 0,
195 JobTDate BIGINT UNSIGNED DEFAULT 0,
196 VolSessionId INTEGER UNSIGNED DEFAULT 0,
197 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
198 JobFiles INTEGER UNSIGNED DEFAULT 0,
199 JobBytes BIGINT UNSIGNED DEFAULT 0,
200 JobErrors INTEGER UNSIGNED DEFAULT 0,
201 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
202 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
203 FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0,
204 PriorJobId INTEGER UNSIGNED REFERENCES Job DEFAULT 0,
205 PurgedFiles TINYINT DEFAULT 0,
206 HasBase TINYINT DEFAULT 0,
209 CREATE INDEX inx6 ON Job (Name);
212 JobId, Job, Name, Type, Level, ClientId, JobStatus,
213 SchedTime, StartTime, EndTime, RealEndTime,
214 JobTDate, VolSessionId, VolSessionTime,
215 JobFiles, JobBytes, JobErrors, JobMissingFiles,
216 PoolId, FileSetId, PriorJobId, PurgedFiles, HasBase)
217 SELECT * FROM Job_backup;
219 DROP TABLE Job_backup;
221 CREATE TABLE LocationLog (
223 Date DATETIME NOT NULL,
224 Comment TEXT NOT NULL,
225 MediaId INTEGER UNSIGNED REFERENCES Media DEFAULT 0,
226 LocationId INTEGER UNSIGNED REFERENCES LocationId DEFAULT 0,
227 NewVolStatus VARCHAR(20) NOT NULL,
228 NewEnabled TINYINT NOT NULL,
229 PRIMARY KEY(LocLogId)
234 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
235 Time DATETIME NOT NULL,
236 LogText TEXT NOT NULL,
239 CREATE INDEX LogInx1 ON File (JobId);
241 CREATE TABLE Location (
243 Location TEXT NOT NULL,
244 Cost INTEGER DEFAULT 0,
246 PRIMARY KEY(LocationId)
251 INSERT INTO Version (VersionId) VALUES (10);