3 # shell script to create Bacula SQLite tables
5 # Copyright (C) 2000-2017 Kern Sibbald
6 # License: BSD 2-Clause; see file LICENSE-FOSS
14 sqlite3 $* ${db_name}.db <<END-OF-DATA
15 CREATE TABLE Filename (
18 PRIMARY KEY(FilenameId)
21 CREATE INDEX inx1 ON Filename (Name);
29 CREATE INDEX inx2 ON Path (Path);
32 -- FileIndex can be 0 for FT_DELETED files
33 -- FileNameId can link to Filename.Name='' for directories
36 FileIndex INTEGER UNSIGNED NOT NULL,
37 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
38 PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
39 FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
40 DeltaSeq SMALLINT UNSIGNED DEFAULT 0,
41 MarkId INTEGER UNSIGNED DEFAULT 0,
42 LStat VARCHAR(255) NOT NULL,
43 MD5 VARCHAR(255) NOT NULL,
46 CREATE INDEX inx3 ON File (JobId);
47 CREATE INDEX file_jpf_idx ON File (JobId, PathId, FilenameId);
49 -- Possibly add one or more of the following indexes
50 -- if your Verifies are too slow.
52 -- CREATE INDEX inx4 ON File (PathId);
53 -- CREATE INDEX inx5 ON File (FileNameId);
56 CREATE TABLE RestoreObject (
57 RestoreObjectId INTEGER,
58 ObjectName TEXT DEFAULT '',
59 RestoreObject TEXT DEFAULT '',
60 PluginName TEXT DEFAULT '',
61 ObjectLength INTEGER DEFAULT 0,
62 ObjectFullLength INTEGER DEFAULT 0,
63 ObjectIndex INTEGER DEFAULT 0,
64 ObjectType INTEGER DEFAULT 0,
65 FileIndex INTEGER UNSIGNED DEFAULT 0,
66 ObjectCompression INTEGER DEFAULT 0,
67 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
68 PRIMARY KEY(RestoreObjectId)
70 CREATE INDEX restore_jobid_idx ON RestoreObject (JobId);
75 Job VARCHAR(128) NOT NULL,
76 Name VARCHAR(128) NOT NULL,
77 Type CHAR(1) NOT NULL,
78 Level CHAR(1) NOT NULL,
79 ClientId INTEGER REFERENCES Client DEFAULT 0,
80 JobStatus CHAR(1) NOT NULL,
81 SchedTime DATETIME NOT NULL,
82 StartTime DATETIME DEFAULT 0,
83 EndTime DATETIME DEFAULT 0,
84 RealEndTime DATETIME DEFAULT 0,
85 JobTDate BIGINT UNSIGNED DEFAULT 0,
86 VolSessionId INTEGER UNSIGNED DEFAULT 0,
87 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
88 JobFiles INTEGER UNSIGNED DEFAULT 0,
89 JobBytes BIGINT UNSIGNED DEFAULT 0,
90 ReadBytes BIGINT UNSIGNED DEFAULT 0,
91 JobErrors INTEGER UNSIGNED DEFAULT 0,
92 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
93 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
94 FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0,
95 PriorJobId INTEGER UNSIGNED REFERENCES Job DEFAULT 0,
96 PurgedFiles TINYINT DEFAULT 0,
97 HasBase TINYINT DEFAULT 0,
98 HasCache TINYINT DEFAULT 0,
99 Reviewed TINYINT DEFAULT 0,
101 FileTable TEXT DEFAULT 'File',
104 CREATE INDEX inx6 ON Job (Name);
105 CREATE INDEX job_jobtdate_inx ON Job (JobTDate);
107 -- Create a table like Job for long term statistics
108 CREATE TABLE JobHisto (
110 Job VARCHAR(128) NOT NULL,
111 Name VARCHAR(128) NOT NULL,
112 Type CHAR(1) NOT NULL,
113 Level CHAR(1) NOT NULL,
114 ClientId INTEGER DEFAULT 0,
115 JobStatus CHAR(1) NOT NULL,
116 SchedTime DATETIME NOT NULL,
117 StartTime DATETIME DEFAULT 0,
118 EndTime DATETIME DEFAULT 0,
119 RealEndTime DATETIME DEFAULT 0,
120 JobTDate BIGINT UNSIGNED DEFAULT 0,
121 VolSessionId INTEGER UNSIGNED DEFAULT 0,
122 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
123 JobFiles INTEGER UNSIGNED DEFAULT 0,
124 JobBytes BIGINT UNSIGNED DEFAULT 0,
125 ReadBytes BIGINT UNSIGNED DEFAULT 0,
126 JobErrors INTEGER UNSIGNED DEFAULT 0,
127 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
128 PoolId INTEGER UNSIGNED DEFAULT 0,
129 FileSetId INTEGER UNSIGNED DEFAULT 0,
130 PriorJobId INTEGER UNSIGNED DEFAULT 0,
131 PurgedFiles TINYINT DEFAULT 0,
132 HasBase TINYINT DEFAULT 0,
133 HasCache TINYINT DEFAULT 0,
134 Reviewed TINYINT DEFAULT 0,
136 FileTable TEXT DEFAULT 'File'
138 CREATE INDEX inx61 ON JobHisto (StartTime);
140 CREATE TABLE Location (
142 Location TEXT NOT NULL,
143 Cost INTEGER DEFAULT 0,
145 PRIMARY KEY(LocationId)
148 CREATE TABLE LocationLog (
150 Date DATETIME NOT NULL,
151 Comment TEXT NOT NULL,
152 MediaId INTEGER UNSIGNED REFERENCES Media DEFAULT 0,
153 LocationId INTEGER UNSIGNED REFERENCES LocationId DEFAULT 0,
154 NewVolStatus VARCHAR(20) NOT NULL,
155 NewEnabled TINYINT NOT NULL,
156 PRIMARY KEY(LocLogId)
162 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
163 Time DATETIME NOT NULL,
164 LogText TEXT NOT NULL,
167 CREATE INDEX LogInx1 ON Log (JobId);
170 CREATE TABLE FileSet (
172 FileSet VARCHAR(128) NOT NULL,
173 MD5 VARCHAR(25) NOT NULL,
174 CreateTime DATETIME DEFAULT 0,
175 PRIMARY KEY(FileSetId)
178 CREATE INDEX fileset_name_idx on FileSet (FileSet);
180 CREATE TABLE JobMedia (
182 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
183 MediaId INTEGER UNSIGNED REFERENCES Media NOT NULL,
184 FirstIndex INTEGER UNSIGNED NOT NULL,
185 LastIndex INTEGER UNSIGNED NOT NULL,
186 StartFile INTEGER UNSIGNED DEFAULT 0,
187 EndFile INTEGER UNSIGNED DEFAULT 0,
188 StartBlock INTEGER UNSIGNED DEFAULT 0,
189 EndBlock INTEGER UNSIGNED DEFAULT 0,
190 VolIndex INTEGER UNSIGNED DEFAULT 0,
191 PRIMARY KEY(JobMediaId)
194 CREATE INDEX inx7 ON JobMedia (JobId, MediaId);
199 VolumeName VARCHAR(128) NOT NULL,
200 Slot INTEGER DEFAULT 0,
201 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
202 MediaType VARCHAR(128) NOT NULL,
203 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType DEFAULT 0,
204 LabelType TINYINT DEFAULT 0,
205 FirstWritten DATETIME DEFAULT 0,
206 LastWritten DATETIME DEFAULT 0,
207 LabelDate DATETIME DEFAULT 0,
208 VolJobs INTEGER UNSIGNED DEFAULT 0,
209 VolFiles INTEGER UNSIGNED DEFAULT 0,
210 VolBlocks INTEGER UNSIGNED DEFAULT 0,
211 LastPartBytes BIGINT UNSIGNED DEFAULT 0,
212 VolMounts INTEGER UNSIGNED DEFAULT 0,
213 VolBytes BIGINT UNSIGNED DEFAULT 0,
214 VolABytes BIGINT UNSIGNED DEFAULT 0,
215 VolAPadding BIGINT UNSIGNED DEFAULT 0,
216 VolHoleBytes BIGINT UNSIGNED DEFAULT 0,
217 VolHoles INTEGER UNSIGNED DEFAULT 0,
218 VolType INTEGER UNSIGNED DEFAULT 0,
219 VolParts INTERGER UNSIGNED DEFAULT 0,
220 VolCloudParts INTERGER UNSIGNED DEFAULT 0,
221 VolErrors INTEGER UNSIGNED DEFAULT 0,
222 VolWrites BIGINT UNSIGNED DEFAULT 0,
223 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
224 VolStatus VARCHAR(20) NOT NULL,
225 Enabled TINYINT DEFAULT 1,
226 Recycle TINYINT DEFAULT 0,
227 ActionOnPurge TINYINT DEFAULT 0,
228 CacheRetention BIGINT UNSIGNED DEFAULT 0,
229 VolRetention BIGINT UNSIGNED DEFAULT 0,
230 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
231 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
232 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
233 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
234 InChanger TINYINT DEFAULT 0,
235 StorageId INTEGER UNSIGNED REFERENCES Storage DEFAULT 0,
236 DeviceId INTEGER UNSIGNED REFERENCES Device DEFAULT 0,
237 MediaAddressing TINYINT DEFAULT 0,
238 VolReadTime BIGINT UNSIGNED DEFAULT 0,
239 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
240 EndFile INTEGER UNSIGNED DEFAULT 0,
241 EndBlock INTEGER UNSIGNED DEFAULT 0,
242 LocationId INTEGER UNSIGNED REFERENCES Location DEFAULT 0,
243 RecycleCount INTEGER UNSIGNED DEFAULT 0,
244 InitialWrite DATETIME DEFAULT 0,
245 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
246 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
251 CREATE UNIQUE INDEX Media_Volumename_Id ON Media (VolumeName);
252 CREATE INDEX inx8 ON Media (PoolId);
253 CREATE INDEX inx9 ON Media (StorageId);
256 CREATE TABLE MediaType (
258 MediaType VARCHAR(128) NOT NULL,
259 ReadOnly TINYINT DEFAULT 0,
260 PRIMARY KEY(MediaTypeId)
263 CREATE TABLE Storage (
265 Name VARCHAR(128) NOT NULL,
266 AutoChanger TINYINT DEFAULT 0,
267 PRIMARY KEY(StorageId)
270 CREATE TABLE Device (
272 Name VARCHAR(128) NOT NULL,
273 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType NOT NULL,
274 StorageId INTEGER UNSIGNED REFERENCES Storage,
275 DevMounts INTEGER UNSIGNED DEFAULT 0,
276 DevReadBytes BIGINT UNSIGNED DEFAULT 0,
277 DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
278 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
279 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
280 DevReadTime BIGINT UNSIGNED DEFAULT 0,
281 DevWriteTime BIGINT UNSIGNED DEFAULT 0,
282 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
283 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
284 CleaningDate DATETIME DEFAULT 0,
285 CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
286 PRIMARY KEY(DeviceId)
292 Name VARCHAR(128) NOT NULL,
293 NumVols INTEGER UNSIGNED DEFAULT 0,
294 MaxVols INTEGER UNSIGNED DEFAULT 0,
295 UseOnce TINYINT DEFAULT 0,
296 UseCatalog TINYINT DEFAULT 1,
297 AcceptAnyVolume TINYINT DEFAULT 0,
298 CacheRetention BIGINT UNSIGNED DEFAULT 0,
299 VolRetention BIGINT UNSIGNED DEFAULT 0,
300 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
301 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
302 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
303 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
304 AutoPrune TINYINT DEFAULT 0,
305 Recycle TINYINT DEFAULT 0,
306 ActionOnPurge TINYINT DEFAULT 0,
307 PoolType VARCHAR(20) NOT NULL,
308 LabelType TINYINT DEFAULT 0,
309 LabelFormat VARCHAR(128) NOT NULL,
310 Enabled TINYINT DEFAULT 1,
311 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
312 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
313 NextPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
314 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
315 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
316 MigrationTime BIGINT UNSIGNED DEFAULT 0,
321 CREATE INDEX pool_name_idx on Pool (Name);
323 CREATE TABLE Client (
325 Name VARCHAR(128) NOT NULL,
326 Uname VARCHAR(255) NOT NULL, -- uname -a field
327 AutoPrune TINYINT DEFAULT 0,
328 FileRetention BIGINT UNSIGNED DEFAULT 0,
329 JobRetention BIGINT UNSIGNED DEFAULT 0,
331 PRIMARY KEY(ClientId)
334 CREATE UNIQUE INDEX client_name_idx on Client (Name);
336 CREATE TABLE BaseFiles (
338 BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
339 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
340 FileId INTEGER UNSIGNED REFERENCES File NOT NULL,
341 FileIndex INTEGER UNSIGNED,
345 CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
347 CREATE TABLE UnsavedFiles (
349 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
350 PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
351 FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
352 PRIMARY KEY (UnsavedId)
356 CREATE TABLE Version (
357 VersionId INTEGER UNSIGNED NOT NULL
361 CREATE TABLE Counters (
362 Counter TEXT NOT NULL,
363 MinValue INTEGER DEFAULT 0,
364 MaxValue INTEGER DEFAULT 0,
365 CurrentValue INTEGER DEFAULT 0,
366 WrapCounter TEXT NOT NULL,
367 PRIMARY KEY (Counter)
370 CREATE TABLE CDImages (
371 MediaId INTEGER UNSIGNED NOT NULL,
372 LastBurn DATETIME NOT NULL,
373 PRIMARY KEY (MediaId)
376 CREATE TABLE PathHierarchy
378 PathId integer NOT NULL,
379 PPathId integer NOT NULL,
380 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
383 CREATE INDEX pathhierarchy_ppathid
384 ON PathHierarchy (PPathId);
386 CREATE TABLE PathVisibility
388 PathId integer NOT NULL,
389 JobId integer NOT NULL,
391 Files int4 DEFAULT 0,
392 CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
395 CREATE INDEX pathvisibility_jobid
396 ON PathVisibility (JobId);
399 CREATE TABLE Snapshot (
402 JobId integer default 0,
403 FileSetId integer default 0,
404 CreateTDate bigint default 0,
405 CreateDate datetime not null,
406 ClientId int default 0,
407 Volume text not null,
408 Device text not null,
410 Retention int default 0,
412 primary key (SnapshotId)
415 CREATE UNIQUE INDEX snapshot_idx ON Snapshot (Device, Volume, Name);
417 CREATE TABLE Status (
418 JobStatus CHAR(1) NOT NULL,
421 PRIMARY KEY (JobStatus)
424 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
425 ('C', 'Created, not yet running',15);
426 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
428 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
430 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
431 ('T', 'Completed successfully', 10);
432 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
433 ('E', 'Terminated with errors', 25);
434 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
435 ('e', 'Non-fatal error',20);
436 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
437 ('f', 'Fatal error',100);
438 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
439 ('D', 'Verify found differences',15);
440 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
441 ('A', 'Canceled by user',90);
442 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
443 ('F', 'Waiting for Client',15);
444 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
445 ('S', 'Waiting for Storage daemon',15);
446 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
447 ('m', 'Waiting for new media');
448 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
449 ('M', 'Waiting for media mount',15);
450 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
451 ('s', 'Waiting for storage resource',15);
452 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
453 ('j', 'Waiting for job resource',15);
454 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
455 ('c', 'Waiting for client resource',15);
456 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
457 ('d', 'Waiting on maximum jobs',15);
458 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
459 ('t', 'Waiting on start time',15);
460 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
461 ('p', 'Waiting on higher priority jobs',15);
462 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
463 ('a', 'SD despooling attributes',15);
464 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
465 ('i', 'Doing batch insert file records',15);
466 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
467 ('I', 'Incomplete Job',25);
469 -- Initialize Version
470 INSERT INTO Version (VersionId) VALUES (16);
472 PRAGMA default_cache_size = 100000;
473 PRAGMA synchronous = NORMAL;
478 echo "WARNING: !!!! SQLite3 is no longer supported. !!!!"
479 echo "WARNING: !!!! Please switch to MySQL or PostgreSQL !!!!"
480 echo "WARNING: !!!! as soon as possible. !!!!"
483 chmod 640 ${db_name}.db