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 TABLE JobMedia (
180 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
181 MediaId INTEGER UNSIGNED REFERENCES Media NOT NULL,
182 FirstIndex INTEGER UNSIGNED NOT NULL,
183 LastIndex INTEGER UNSIGNED NOT NULL,
184 StartFile INTEGER UNSIGNED DEFAULT 0,
185 EndFile INTEGER UNSIGNED DEFAULT 0,
186 StartBlock INTEGER UNSIGNED DEFAULT 0,
187 EndBlock INTEGER UNSIGNED DEFAULT 0,
188 VolIndex INTEGER UNSIGNED DEFAULT 0,
189 PRIMARY KEY(JobMediaId)
192 CREATE INDEX inx7 ON JobMedia (JobId, MediaId);
197 VolumeName VARCHAR(128) NOT NULL,
198 Slot INTEGER DEFAULT 0,
199 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
200 MediaType VARCHAR(128) NOT NULL,
201 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType DEFAULT 0,
202 LabelType TINYINT DEFAULT 0,
203 FirstWritten DATETIME DEFAULT 0,
204 LastWritten DATETIME DEFAULT 0,
205 LabelDate DATETIME DEFAULT 0,
206 VolJobs INTEGER UNSIGNED DEFAULT 0,
207 VolFiles INTEGER UNSIGNED DEFAULT 0,
208 VolBlocks INTEGER UNSIGNED DEFAULT 0,
209 LastPartBytes BIGINT UNSIGNED DEFAULT 0,
210 VolMounts INTEGER UNSIGNED DEFAULT 0,
211 VolBytes BIGINT UNSIGNED DEFAULT 0,
212 VolABytes BIGINT UNSIGNED DEFAULT 0,
213 VolAPadding BIGINT UNSIGNED DEFAULT 0,
214 VolHoleBytes BIGINT UNSIGNED DEFAULT 0,
215 VolHoles INTEGER UNSIGNED DEFAULT 0,
216 VolType INTEGER UNSIGNED DEFAULT 0,
217 VolParts INTERGER UNSIGNED DEFAULT 0,
218 VolCloudParts INTERGER UNSIGNED DEFAULT 0,
219 VolErrors INTEGER UNSIGNED DEFAULT 0,
220 VolWrites BIGINT UNSIGNED DEFAULT 0,
221 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
222 VolStatus VARCHAR(20) NOT NULL,
223 Enabled TINYINT DEFAULT 1,
224 Recycle TINYINT DEFAULT 0,
225 ActionOnPurge TINYINT DEFAULT 0,
226 CacheRetention BIGINT UNSIGNED DEFAULT 0,
227 VolRetention BIGINT UNSIGNED DEFAULT 0,
228 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
229 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
230 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
231 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
232 InChanger TINYINT DEFAULT 0,
233 StorageId INTEGER UNSIGNED REFERENCES Storage DEFAULT 0,
234 DeviceId INTEGER UNSIGNED REFERENCES Device DEFAULT 0,
235 MediaAddressing TINYINT DEFAULT 0,
236 VolReadTime BIGINT UNSIGNED DEFAULT 0,
237 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
238 EndFile INTEGER UNSIGNED DEFAULT 0,
239 EndBlock INTEGER UNSIGNED DEFAULT 0,
240 LocationId INTEGER UNSIGNED REFERENCES Location DEFAULT 0,
241 RecycleCount INTEGER UNSIGNED DEFAULT 0,
242 InitialWrite DATETIME DEFAULT 0,
243 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
244 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
249 CREATE INDEX inx8 ON Media (PoolId);
250 CREATE INDEX inx9 ON Media (StorageId);
253 CREATE TABLE MediaType (
255 MediaType VARCHAR(128) NOT NULL,
256 ReadOnly TINYINT DEFAULT 0,
257 PRIMARY KEY(MediaTypeId)
260 CREATE TABLE Storage (
262 Name VARCHAR(128) NOT NULL,
263 AutoChanger TINYINT DEFAULT 0,
264 PRIMARY KEY(StorageId)
267 CREATE TABLE Device (
269 Name VARCHAR(128) NOT NULL,
270 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType NOT NULL,
271 StorageId INTEGER UNSIGNED REFERENCES Storage,
272 DevMounts INTEGER UNSIGNED DEFAULT 0,
273 DevReadBytes BIGINT UNSIGNED DEFAULT 0,
274 DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
275 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
276 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
277 DevReadTime BIGINT UNSIGNED DEFAULT 0,
278 DevWriteTime BIGINT UNSIGNED DEFAULT 0,
279 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
280 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
281 CleaningDate DATETIME DEFAULT 0,
282 CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
283 PRIMARY KEY(DeviceId)
289 Name VARCHAR(128) NOT NULL,
290 NumVols INTEGER UNSIGNED DEFAULT 0,
291 MaxVols INTEGER UNSIGNED DEFAULT 0,
292 UseOnce TINYINT DEFAULT 0,
293 UseCatalog TINYINT DEFAULT 1,
294 AcceptAnyVolume TINYINT DEFAULT 0,
295 CacheRetention BIGINT UNSIGNED DEFAULT 0,
296 VolRetention BIGINT UNSIGNED DEFAULT 0,
297 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
298 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
299 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
300 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
301 AutoPrune TINYINT DEFAULT 0,
302 Recycle TINYINT DEFAULT 0,
303 ActionOnPurge TINYINT DEFAULT 0,
304 PoolType VARCHAR(20) NOT NULL,
305 LabelType TINYINT DEFAULT 0,
306 LabelFormat VARCHAR(128) NOT NULL,
307 Enabled TINYINT DEFAULT 1,
308 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
309 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
310 NextPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
311 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
312 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
313 MigrationTime BIGINT UNSIGNED DEFAULT 0,
319 CREATE TABLE Client (
321 Name VARCHAR(128) NOT NULL,
322 Uname VARCHAR(255) NOT NULL, -- uname -a field
323 AutoPrune TINYINT DEFAULT 0,
324 FileRetention BIGINT UNSIGNED DEFAULT 0,
325 JobRetention BIGINT UNSIGNED DEFAULT 0,
327 PRIMARY KEY(ClientId)
330 CREATE TABLE BaseFiles (
332 BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
333 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
334 FileId INTEGER UNSIGNED REFERENCES File NOT NULL,
335 FileIndex INTEGER UNSIGNED,
339 CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
341 CREATE TABLE UnsavedFiles (
343 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
344 PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
345 FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
346 PRIMARY KEY (UnsavedId)
350 CREATE TABLE NextId (
351 id INTEGER UNSIGNED DEFAULT 0,
352 TableName TEXT NOT NULL,
353 PRIMARY KEY (TableName)
358 -- Initialize JobId to start at 1
359 INSERT INTO NextId (id, TableName) VALUES (1, 'Job');
361 CREATE TABLE Version (
362 VersionId INTEGER UNSIGNED NOT NULL
366 CREATE TABLE Counters (
367 Counter TEXT NOT NULL,
368 MinValue INTEGER DEFAULT 0,
369 MaxValue INTEGER DEFAULT 0,
370 CurrentValue INTEGER DEFAULT 0,
371 WrapCounter TEXT NOT NULL,
372 PRIMARY KEY (Counter)
375 CREATE TABLE CDImages (
376 MediaId INTEGER UNSIGNED NOT NULL,
377 LastBurn DATETIME NOT NULL,
378 PRIMARY KEY (MediaId)
381 CREATE TABLE PathHierarchy
383 PathId integer NOT NULL,
384 PPathId integer NOT NULL,
385 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
388 CREATE INDEX pathhierarchy_ppathid
389 ON PathHierarchy (PPathId);
391 CREATE TABLE PathVisibility
393 PathId integer NOT NULL,
394 JobId integer NOT NULL,
396 Files int4 DEFAULT 0,
397 CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
400 CREATE INDEX pathvisibility_jobid
401 ON PathVisibility (JobId);
404 CREATE TABLE Snapshot (
407 JobId integer default 0,
408 FileSetId integer default 0,
409 CreateTDate bigint default 0,
410 CreateDate datetime not null,
411 ClientId int default 0,
412 Volume text not null,
413 Device text not null,
415 Retention int default 0,
417 primary key (SnapshotId)
420 CREATE UNIQUE INDEX snapshot_idx ON Snapshot (Device, Volume, Name);
422 CREATE TABLE Status (
423 JobStatus CHAR(1) NOT NULL,
426 PRIMARY KEY (JobStatus)
429 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
430 ('C', 'Created, not yet running',15);
431 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
433 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
435 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
436 ('T', 'Completed successfully', 10);
437 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
438 ('E', 'Terminated with errors', 25);
439 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
440 ('e', 'Non-fatal error',20);
441 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
442 ('f', 'Fatal error',100);
443 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
444 ('D', 'Verify found differences',15);
445 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
446 ('A', 'Canceled by user',90);
447 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
448 ('F', 'Waiting for Client',15);
449 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
450 ('S', 'Waiting for Storage daemon',15);
451 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
452 ('m', 'Waiting for new media');
453 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
454 ('M', 'Waiting for media mount',15);
455 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
456 ('s', 'Waiting for storage resource',15);
457 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
458 ('j', 'Waiting for job resource',15);
459 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
460 ('c', 'Waiting for client resource',15);
461 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
462 ('d', 'Waiting on maximum jobs',15);
463 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
464 ('t', 'Waiting on start time',15);
465 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
466 ('p', 'Waiting on higher priority jobs',15);
467 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
468 ('a', 'SD despooling attributes',15);
469 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
470 ('i', 'Doing batch insert file records',15);
471 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
472 ('I', 'Incomplete Job',25);
474 -- Initialize Version
475 INSERT INTO Version (VersionId) VALUES (15);
477 PRAGMA default_cache_size = 100000;
478 PRAGMA synchronous = NORMAL;
482 chmod 640 ${db_name}.db