3 # shell script to create Bacula SQLite tables
10 ${bindir}/${sqlite} $* ${db_name}.db <<END-OF-DATA
11 CREATE TABLE Filename (
14 PRIMARY KEY(FilenameId)
17 CREATE INDEX inx1 ON Filename (Name);
25 CREATE INDEX inx2 ON Path (Path);
30 FileIndex INTEGER UNSIGNED NOT NULL,
31 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
32 PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
33 FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
34 MarkId INTEGER UNSIGNED DEFAULT 0,
35 LStat VARCHAR(255) NOT NULL,
36 MD5 VARCHAR(255) NOT NULL,
40 CREATE INDEX inx3 ON File (JobId);
41 CREATE INDEX inx4 ON File (FilenameId, PathId);
43 -- Possibly add one or more of the following indexes
44 -- if your Verifies are too slow.
46 -- CREATE INDEX inx4 ON File (PathId);
47 -- CREATE INDEX inx5 ON File (FileNameId);
48 -- CREATE INDEX inx9 ON File (JobId, PathId, FilenameId);
52 Job VARCHAR(128) NOT NULL,
53 Name VARCHAR(128) NOT NULL,
54 Type CHAR(1) NOT NULL,
55 Level CHAR(1) NOT NULL,
56 ClientId INTEGER REFERENCES Client DEFAULT 0,
57 JobStatus CHAR(1) NOT NULL,
58 SchedTime DATETIME NOT NULL,
59 StartTime DATETIME DEFAULT 0,
60 EndTime DATETIME DEFAULT 0,
61 RealEndTime DATETIME DEFAULT 0,
62 JobTDate BIGINT UNSIGNED DEFAULT 0,
63 VolSessionId INTEGER UNSIGNED DEFAULT 0,
64 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
65 JobFiles INTEGER UNSIGNED DEFAULT 0,
66 JobBytes BIGINT UNSIGNED DEFAULT 0,
67 JobErrors INTEGER UNSIGNED DEFAULT 0,
68 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
69 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
70 FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0,
71 PriorJobId INTEGER UNSIGNED REFERENCES Job DEFAULT 0,
72 PurgedFiles TINYINT DEFAULT 0,
73 HasBase TINYINT DEFAULT 0,
76 CREATE INDEX inx6 ON Job (Name);
78 -- Create a table like Job for long term statistics
79 CREATE TABLE JobHistory (
81 Job VARCHAR(128) NOT NULL,
82 Name VARCHAR(128) NOT NULL,
83 Type CHAR(1) NOT NULL,
84 Level CHAR(1) NOT NULL,
85 ClientId INTEGER REFERENCES Client DEFAULT 0,
86 JobStatus CHAR(1) NOT NULL,
87 SchedTime DATETIME NOT NULL,
88 StartTime DATETIME DEFAULT 0,
89 EndTime DATETIME DEFAULT 0,
90 RealEndTime DATETIME DEFAULT 0,
91 JobTDate BIGINT UNSIGNED DEFAULT 0,
92 VolSessionId INTEGER UNSIGNED DEFAULT 0,
93 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
94 JobFiles INTEGER UNSIGNED DEFAULT 0,
95 JobBytes BIGINT UNSIGNED DEFAULT 0,
96 JobErrors INTEGER UNSIGNED DEFAULT 0,
97 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
98 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
99 FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0,
100 PriorJobId INTEGER UNSIGNED REFERENCES Job DEFAULT 0,
101 PurgedFiles TINYINT DEFAULT 0,
102 HasBase TINYINT DEFAULT 0,
105 CREATE INDEX inx61 ON JobHistory (Name);
107 CREATE TABLE Location (
109 Location TEXT NOT NULL,
110 Cost INTEGER DEFAULT 0,
112 PRIMARY KEY(LocationId)
115 CREATE TABLE LocationLog (
117 Date DATETIME NOT NULL,
118 Comment TEXT NOT NULL,
119 MediaId INTEGER UNSIGNED REFERENCES Media DEFAULT 0,
120 LocationId INTEGER UNSIGNED REFERENCES LocationId DEFAULT 0,
121 NewVolStatus VARCHAR(20) NOT NULL,
122 NewEnabled TINYINT NOT NULL,
123 PRIMARY KEY(LocLogId)
129 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
130 Time DATETIME NOT NULL,
131 LogText TEXT NOT NULL,
134 CREATE INDEX LogInx1 ON Log (JobId);
137 CREATE TABLE FileSet (
139 FileSet VARCHAR(128) NOT NULL,
140 MD5 VARCHAR(25) NOT NULL,
141 CreateTime DATETIME DEFAULT 0,
142 PRIMARY KEY(FileSetId)
145 CREATE TABLE JobMedia (
147 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
148 MediaId INTEGER UNSIGNED REFERENCES Media NOT NULL,
149 FirstIndex INTEGER UNSIGNED NOT NULL,
150 LastIndex INTEGER UNSIGNED NOT NULL,
151 StartFile INTEGER UNSIGNED DEFAULT 0,
152 EndFile INTEGER UNSIGNED DEFAULT 0,
153 StartBlock INTEGER UNSIGNED DEFAULT 0,
154 EndBlock INTEGER UNSIGNED DEFAULT 0,
155 VolIndex INTEGER UNSIGNED DEFAULT 0,
156 Copy INTEGER UNSIGNED DEFAULT 0,
157 PRIMARY KEY(JobMediaId)
160 CREATE INDEX inx7 ON JobMedia (JobId, MediaId);
165 VolumeName VARCHAR(128) NOT NULL,
166 Slot INTEGER DEFAULT 0,
167 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
168 MediaType VARCHAR(128) NOT NULL,
169 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType DEFAULT 0,
170 LabelType TINYINT DEFAULT 0,
171 FirstWritten DATETIME DEFAULT 0,
172 LastWritten DATETIME DEFAULT 0,
173 LabelDate DATETIME DEFAULT 0,
174 VolJobs INTEGER UNSIGNED DEFAULT 0,
175 VolFiles INTEGER UNSIGNED DEFAULT 0,
176 VolBlocks INTEGER UNSIGNED DEFAULT 0,
177 VolMounts INTEGER UNSIGNED DEFAULT 0,
178 VolBytes BIGINT UNSIGNED DEFAULT 0,
179 VolParts INTEGER UNSIGNED DEFAULT 0,
180 VolErrors INTEGER UNSIGNED DEFAULT 0,
181 VolWrites INTEGER UNSIGNED DEFAULT 0,
182 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
183 VolStatus VARCHAR(20) NOT NULL,
184 Enabled TINYINT DEFAULT 1,
185 Recycle TINYINT DEFAULT 0,
186 VolRetention BIGINT UNSIGNED DEFAULT 0,
187 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
188 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
189 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
190 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
191 InChanger TINYINT DEFAULT 0,
192 StorageId INTEGER UNSIGNED REFERENCES Storage DEFAULT 0,
193 DeviceId INTEGER UNSIGNED REFERENCES Device DEFAULT 0,
194 MediaAddressing TINYINT DEFAULT 0,
195 VolReadTime BIGINT UNSIGNED DEFAULT 0,
196 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
197 EndFile INTEGER UNSIGNED DEFAULT 0,
198 EndBlock INTEGER UNSIGNED DEFAULT 0,
199 LocationId INTEGER UNSIGNED REFERENCES Location DEFAULT 0,
200 RecycleCount INTEGER UNSIGNED DEFAULT 0,
201 InitialWrite DATETIME DEFAULT 0,
202 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
203 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
208 CREATE INDEX inx8 ON Media (PoolId);
210 CREATE TABLE MediaType (
212 MediaType VARCHAR(128) NOT NULL,
213 ReadOnly TINYINT DEFAULT 0,
214 PRIMARY KEY(MediaTypeId)
217 CREATE TABLE Storage (
219 Name VARCHAR(128) NOT NULL,
220 AutoChanger TINYINT DEFAULT 0,
221 PRIMARY KEY(StorageId)
224 CREATE TABLE Device (
226 Name VARCHAR(128) NOT NULL,
227 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType NOT NULL,
228 StorageId INTEGER UNSIGNED REFERENCES Storage,
229 DevMounts INTEGER UNSIGNED DEFAULT 0,
230 DevReadBytes BIGINT UNSIGNED DEFAULT 0,
231 DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
232 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
233 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
234 DevReadTime BIGINT UNSIGNED DEFAULT 0,
235 DevWriteTime BIGINT UNSIGNED DEFAULT 0,
236 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
237 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
238 CleaningDate DATETIME DEFAULT 0,
239 CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
240 PRIMARY KEY(DeviceId)
246 Name VARCHAR(128) NOT NULL,
247 NumVols INTEGER UNSIGNED DEFAULT 0,
248 MaxVols INTEGER UNSIGNED DEFAULT 0,
249 UseOnce TINYINT DEFAULT 0,
250 UseCatalog TINYINT DEFAULT 1,
251 AcceptAnyVolume TINYINT DEFAULT 0,
252 VolRetention BIGINT UNSIGNED DEFAULT 0,
253 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
254 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
255 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
256 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
257 AutoPrune TINYINT DEFAULT 0,
258 Recycle TINYINT DEFAULT 0,
259 PoolType VARCHAR(20) NOT NULL,
260 LabelType TINYINT DEFAULT 0,
261 LabelFormat VARCHAR(128) NOT NULL,
262 Enabled TINYINT DEFAULT 1,
263 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
264 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
265 NextPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
266 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
267 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
268 MigrationTime BIGINT UNSIGNED DEFAULT 0,
274 CREATE TABLE Client (
276 Name VARCHAR(128) NOT NULL,
277 Uname VARCHAR(255) NOT NULL, -- uname -a field
278 AutoPrune TINYINT DEFAULT 0,
279 FileRetention BIGINT UNSIGNED DEFAULT 0,
280 JobRetention BIGINT UNSIGNED DEFAULT 0,
282 PRIMARY KEY(ClientId)
285 CREATE TABLE BaseFiles (
287 BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
288 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
289 FileId INTEGER UNSIGNED REFERENCES File NOT NULL,
290 FileIndex INTEGER UNSIGNED,
294 CREATE TABLE UnsavedFiles (
296 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
297 PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
298 FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
299 PRIMARY KEY (UnsavedId)
303 CREATE TABLE NextId (
304 id INTEGER UNSIGNED DEFAULT 0,
305 TableName TEXT NOT NULL,
306 PRIMARY KEY (TableName)
311 -- Initialize JobId to start at 1
312 INSERT INTO NextId (id, TableName) VALUES (1, 'Job');
314 CREATE TABLE Version (
315 VersionId INTEGER UNSIGNED NOT NULL
319 CREATE TABLE Counters (
320 Counter TEXT NOT NULL,
321 MinValue INTEGER DEFAULT 0,
322 MaxValue INTEGER DEFAULT 0,
323 CurrentValue INTEGER DEFAULT 0,
324 WrapCounter TEXT NOT NULL,
325 PRIMARY KEY (Counter)
328 CREATE TABLE CDImages (
329 MediaId INTEGER UNSIGNED NOT NULL,
330 LastBurn DATETIME NOT NULL,
331 PRIMARY KEY (MediaId)
335 CREATE TABLE Status (
336 JobStatus CHAR(1) NOT NULL,
338 PRIMARY KEY (JobStatus)
341 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
342 ('C', 'Created, not yet running');
343 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
345 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
347 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
348 ('T', 'Completed successfully');
349 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
350 ('E', 'Terminated with errors');
351 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
352 ('e', 'Non-fatal error');
353 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
354 ('f', 'Fatal error');
355 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
356 ('D', 'Verify found differences');
357 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
358 ('A', 'Canceled by user');
359 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
360 ('F', 'Waiting for Client');
361 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
362 ('S', 'Waiting for Storage daemon');
363 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
364 ('m', 'Waiting for new media');
365 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
366 ('M', 'Waiting for media mount');
367 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
368 ('s', 'Waiting for storage resource');
369 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
370 ('j', 'Waiting for job resource');
371 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
372 ('c', 'Waiting for client resource');
373 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
374 ('d', 'Waiting on maximum jobs');
375 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
376 ('t', 'Waiting on start time');
377 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
378 ('p', 'Waiting on higher priority jobs');
379 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
380 ('a', 'SD despooling attributes');
381 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
382 ('i', 'Doing batch insert file records');
385 -- Initialize Version
386 INSERT INTO Version (VersionId) VALUES (10);
389 PRAGMA default_synchronous = OFF;
390 PRAGMA default_cache_size = 10000;
394 chmod 640 ${db_name}.db