3 # shell script to create Bacula SQLite tables
5 # Copyright (C) 2000-2015 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);
106 -- Create a table like Job for long term statistics
107 CREATE TABLE JobHisto (
109 Job VARCHAR(128) NOT NULL,
110 Name VARCHAR(128) NOT NULL,
111 Type CHAR(1) NOT NULL,
112 Level CHAR(1) NOT NULL,
113 ClientId INTEGER DEFAULT 0,
114 JobStatus CHAR(1) NOT NULL,
115 SchedTime DATETIME NOT NULL,
116 StartTime DATETIME DEFAULT 0,
117 EndTime DATETIME DEFAULT 0,
118 RealEndTime DATETIME DEFAULT 0,
119 JobTDate BIGINT UNSIGNED DEFAULT 0,
120 VolSessionId INTEGER UNSIGNED DEFAULT 0,
121 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
122 JobFiles INTEGER UNSIGNED DEFAULT 0,
123 JobBytes BIGINT UNSIGNED DEFAULT 0,
124 ReadBytes BIGINT UNSIGNED DEFAULT 0,
125 JobErrors INTEGER UNSIGNED DEFAULT 0,
126 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
127 PoolId INTEGER UNSIGNED DEFAULT 0,
128 FileSetId INTEGER UNSIGNED DEFAULT 0,
129 PriorJobId INTEGER UNSIGNED DEFAULT 0,
130 PurgedFiles TINYINT DEFAULT 0,
131 HasBase TINYINT DEFAULT 0,
132 HasCache TINYINT DEFAULT 0,
133 Reviewed TINYINT DEFAULT 0,
135 FileTable TEXT DEFAULT 'File'
137 CREATE INDEX inx61 ON JobHisto (StartTime);
139 CREATE TABLE Location (
141 Location TEXT NOT NULL,
142 Cost INTEGER DEFAULT 0,
144 PRIMARY KEY(LocationId)
147 CREATE TABLE LocationLog (
149 Date DATETIME NOT NULL,
150 Comment TEXT NOT NULL,
151 MediaId INTEGER UNSIGNED REFERENCES Media DEFAULT 0,
152 LocationId INTEGER UNSIGNED REFERENCES LocationId DEFAULT 0,
153 NewVolStatus VARCHAR(20) NOT NULL,
154 NewEnabled TINYINT NOT NULL,
155 PRIMARY KEY(LocLogId)
161 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
162 Time DATETIME NOT NULL,
163 LogText TEXT NOT NULL,
166 CREATE INDEX LogInx1 ON Log (JobId);
169 CREATE TABLE FileSet (
171 FileSet VARCHAR(128) NOT NULL,
172 MD5 VARCHAR(25) NOT NULL,
173 CreateTime DATETIME DEFAULT 0,
174 PRIMARY KEY(FileSetId)
177 CREATE TABLE JobMedia (
179 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
180 MediaId INTEGER UNSIGNED REFERENCES Media NOT NULL,
181 FirstIndex INTEGER UNSIGNED NOT NULL,
182 LastIndex INTEGER UNSIGNED NOT NULL,
183 StartFile INTEGER UNSIGNED DEFAULT 0,
184 EndFile INTEGER UNSIGNED DEFAULT 0,
185 StartBlock INTEGER UNSIGNED DEFAULT 0,
186 EndBlock INTEGER UNSIGNED DEFAULT 0,
187 VolIndex INTEGER UNSIGNED DEFAULT 0,
188 PRIMARY KEY(JobMediaId)
191 CREATE INDEX inx7 ON JobMedia (JobId, MediaId);
196 VolumeName VARCHAR(128) NOT NULL,
197 Slot INTEGER DEFAULT 0,
198 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
199 MediaType VARCHAR(128) NOT NULL,
200 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType DEFAULT 0,
201 LabelType TINYINT DEFAULT 0,
202 FirstWritten DATETIME DEFAULT 0,
203 LastWritten DATETIME DEFAULT 0,
204 LabelDate DATETIME DEFAULT 0,
205 VolJobs INTEGER UNSIGNED DEFAULT 0,
206 VolFiles INTEGER UNSIGNED DEFAULT 0,
207 VolBlocks INTEGER UNSIGNED DEFAULT 0,
208 VolMounts INTEGER UNSIGNED DEFAULT 0,
209 VolBytes BIGINT UNSIGNED DEFAULT 0,
210 VolABytes BIGINT UNSIGNED DEFAULT 0,
211 VolAPadding BIGINT UNSIGNED DEFAULT 0,
212 VolHoleBytes BIGINT UNSIGNED DEFAULT 0,
213 VolHoles INTEGER UNSIGNED DEFAULT 0,
214 VolParts INTEGER UNSIGNED DEFAULT 0, /* Now used for VolType */
215 VolErrors INTEGER UNSIGNED DEFAULT 0,
216 VolWrites BIGINT UNSIGNED DEFAULT 0,
217 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
218 VolStatus VARCHAR(20) NOT NULL,
219 Enabled TINYINT DEFAULT 1,
220 Recycle TINYINT DEFAULT 0,
221 ActionOnPurge TINYINT DEFAULT 0,
222 VolRetention BIGINT UNSIGNED DEFAULT 0,
223 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
224 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
225 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
226 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
227 InChanger TINYINT DEFAULT 0,
228 StorageId INTEGER UNSIGNED REFERENCES Storage DEFAULT 0,
229 DeviceId INTEGER UNSIGNED REFERENCES Device DEFAULT 0,
230 MediaAddressing TINYINT DEFAULT 0,
231 VolReadTime BIGINT UNSIGNED DEFAULT 0,
232 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
233 EndFile INTEGER UNSIGNED DEFAULT 0,
234 EndBlock INTEGER UNSIGNED DEFAULT 0,
235 LocationId INTEGER UNSIGNED REFERENCES Location DEFAULT 0,
236 RecycleCount INTEGER UNSIGNED DEFAULT 0,
237 InitialWrite DATETIME DEFAULT 0,
238 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
239 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
244 CREATE INDEX inx8 ON Media (PoolId);
245 CREATE INDEX inx9 ON Media (StorageId);
248 CREATE TABLE MediaType (
250 MediaType VARCHAR(128) NOT NULL,
251 ReadOnly TINYINT DEFAULT 0,
252 PRIMARY KEY(MediaTypeId)
255 CREATE TABLE Storage (
257 Name VARCHAR(128) NOT NULL,
258 AutoChanger TINYINT DEFAULT 0,
259 PRIMARY KEY(StorageId)
262 CREATE TABLE Device (
264 Name VARCHAR(128) NOT NULL,
265 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType NOT NULL,
266 StorageId INTEGER UNSIGNED REFERENCES Storage,
267 DevMounts INTEGER UNSIGNED DEFAULT 0,
268 DevReadBytes BIGINT UNSIGNED DEFAULT 0,
269 DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
270 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
271 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
272 DevReadTime BIGINT UNSIGNED DEFAULT 0,
273 DevWriteTime BIGINT UNSIGNED DEFAULT 0,
274 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
275 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
276 CleaningDate DATETIME DEFAULT 0,
277 CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
278 PRIMARY KEY(DeviceId)
284 Name VARCHAR(128) NOT NULL,
285 NumVols INTEGER UNSIGNED DEFAULT 0,
286 MaxVols INTEGER UNSIGNED DEFAULT 0,
287 UseOnce TINYINT DEFAULT 0,
288 UseCatalog TINYINT DEFAULT 1,
289 AcceptAnyVolume TINYINT DEFAULT 0,
290 VolRetention BIGINT UNSIGNED DEFAULT 0,
291 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
292 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
293 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
294 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
295 AutoPrune TINYINT DEFAULT 0,
296 Recycle TINYINT DEFAULT 0,
297 ActionOnPurge TINYINT DEFAULT 0,
298 PoolType VARCHAR(20) NOT NULL,
299 LabelType TINYINT DEFAULT 0,
300 LabelFormat VARCHAR(128) NOT NULL,
301 Enabled TINYINT DEFAULT 1,
302 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
303 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
304 NextPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
305 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
306 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
307 MigrationTime BIGINT UNSIGNED DEFAULT 0,
313 CREATE TABLE Client (
315 Name VARCHAR(128) NOT NULL,
316 Uname VARCHAR(255) NOT NULL, -- uname -a field
317 AutoPrune TINYINT DEFAULT 0,
318 FileRetention BIGINT UNSIGNED DEFAULT 0,
319 JobRetention BIGINT UNSIGNED DEFAULT 0,
321 PRIMARY KEY(ClientId)
324 CREATE TABLE BaseFiles (
326 BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
327 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
328 FileId INTEGER UNSIGNED REFERENCES File NOT NULL,
329 FileIndex INTEGER UNSIGNED,
333 CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
335 CREATE TABLE UnsavedFiles (
337 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
338 PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
339 FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
340 PRIMARY KEY (UnsavedId)
344 CREATE TABLE NextId (
345 id INTEGER UNSIGNED DEFAULT 0,
346 TableName TEXT NOT NULL,
347 PRIMARY KEY (TableName)
352 -- Initialize JobId to start at 1
353 INSERT INTO NextId (id, TableName) VALUES (1, 'Job');
355 CREATE TABLE Version (
356 VersionId INTEGER UNSIGNED NOT NULL
360 CREATE TABLE Counters (
361 Counter TEXT NOT NULL,
362 MinValue INTEGER DEFAULT 0,
363 MaxValue INTEGER DEFAULT 0,
364 CurrentValue INTEGER DEFAULT 0,
365 WrapCounter TEXT NOT NULL,
366 PRIMARY KEY (Counter)
369 CREATE TABLE CDImages (
370 MediaId INTEGER UNSIGNED NOT NULL,
371 LastBurn DATETIME NOT NULL,
372 PRIMARY KEY (MediaId)
375 CREATE TABLE PathHierarchy
377 PathId integer NOT NULL,
378 PPathId integer NOT NULL,
379 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
382 CREATE INDEX pathhierarchy_ppathid
383 ON PathHierarchy (PPathId);
385 CREATE TABLE PathVisibility
387 PathId integer NOT NULL,
388 JobId integer NOT NULL,
390 Files int4 DEFAULT 0,
391 CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
394 CREATE INDEX pathvisibility_jobid
395 ON PathVisibility (JobId);
398 CREATE TABLE Snapshot (
401 JobId integer default 0,
402 FileSetId integer default 0,
403 CreateTDate bigint default 0,
404 CreateDate datetime not null,
405 ClientId int default 0,
406 Volume text not null,
407 Device text not null,
409 Retention int default 0,
411 primary key (SnapshotId)
414 CREATE UNIQUE INDEX snapshot_idx ON Snapshot (Device, Volume, Name);
416 CREATE TABLE Status (
417 JobStatus CHAR(1) NOT NULL,
420 PRIMARY KEY (JobStatus)
423 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
424 ('C', 'Created, not yet running',15);
425 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
427 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
429 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
430 ('T', 'Completed successfully', 10);
431 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
432 ('E', 'Terminated with errors', 25);
433 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
434 ('e', 'Non-fatal error',20);
435 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
436 ('f', 'Fatal error',100);
437 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
438 ('D', 'Verify found differences',15);
439 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
440 ('A', 'Canceled by user',90);
441 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
442 ('F', 'Waiting for Client',15);
443 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
444 ('S', 'Waiting for Storage daemon',15);
445 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
446 ('m', 'Waiting for new media');
447 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
448 ('M', 'Waiting for media mount',15);
449 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
450 ('s', 'Waiting for storage resource',15);
451 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
452 ('j', 'Waiting for job resource',15);
453 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
454 ('c', 'Waiting for client resource',15);
455 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
456 ('d', 'Waiting on maximum jobs',15);
457 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
458 ('t', 'Waiting on start time',15);
459 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
460 ('p', 'Waiting on higher priority jobs',15);
461 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
462 ('a', 'SD despooling attributes',15);
463 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
464 ('i', 'Doing batch insert file records',15);
465 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
466 ('I', 'Incomplete Job',25);
468 -- Initialize Version
469 INSERT INTO Version (VersionId) VALUES (15);
471 PRAGMA default_cache_size = 100000;
472 PRAGMA synchronous = NORMAL;
476 chmod 640 ${db_name}.db