3 # shell script to create Bacula SQLite tables
10 sqlite3 $* ${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);
28 -- FileIndex can be 0 for FT_DELETED files
29 -- FileNameId can link to Filename.Name='' for directories
32 FileIndex INTEGER UNSIGNED NOT NULL,
33 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
34 PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
35 FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
36 DeltaSeq SMALLINT UNSIGNED DEFAULT 0,
37 MarkId INTEGER UNSIGNED DEFAULT 0,
38 LStat VARCHAR(255) NOT NULL,
39 MD5 VARCHAR(255) NOT NULL,
42 CREATE INDEX inx3 ON File (JobId);
43 CREATE INDEX file_jpf_idx ON File (JobId, PathId, FilenameId);
45 -- Possibly add one or more of the following indexes
46 -- if your Verifies are too slow.
48 -- CREATE INDEX inx4 ON File (PathId);
49 -- CREATE INDEX inx5 ON File (FileNameId);
52 CREATE TABLE RestoreObject (
53 RestoreObjectId INTEGER,
54 ObjectName TEXT DEFAULT '',
55 RestoreObject TEXT DEFAULT '',
56 PluginName TEXT DEFAULT '',
57 ObjectLength INTEGER DEFAULT 0,
58 ObjectFullLength INTEGER DEFAULT 0,
59 ObjectIndex INTEGER DEFAULT 0,
60 ObjectType INTEGER DEFAULT 0,
61 FileIndex INTEGER UNSIGNED DEFAULT 0,
62 ObjectCompression INTEGER DEFAULT 0,
63 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
64 PRIMARY KEY(RestoreObjectId)
66 CREATE INDEX restore_jobid_idx ON RestoreObject (JobId);
71 Job VARCHAR(128) NOT NULL,
72 Name VARCHAR(128) NOT NULL,
73 Type CHAR(1) NOT NULL,
74 Level CHAR(1) NOT NULL,
75 ClientId INTEGER REFERENCES Client DEFAULT 0,
76 JobStatus CHAR(1) NOT NULL,
77 SchedTime DATETIME NOT NULL,
78 StartTime DATETIME DEFAULT 0,
79 EndTime DATETIME DEFAULT 0,
80 RealEndTime DATETIME DEFAULT 0,
81 JobTDate BIGINT UNSIGNED DEFAULT 0,
82 VolSessionId INTEGER UNSIGNED DEFAULT 0,
83 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
84 JobFiles INTEGER UNSIGNED DEFAULT 0,
85 JobBytes BIGINT UNSIGNED DEFAULT 0,
86 ReadBytes BIGINT UNSIGNED DEFAULT 0,
87 JobErrors INTEGER UNSIGNED DEFAULT 0,
88 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
89 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
90 FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0,
91 PriorJobId INTEGER UNSIGNED REFERENCES Job DEFAULT 0,
92 PurgedFiles TINYINT DEFAULT 0,
93 HasBase TINYINT DEFAULT 0,
94 HasCache TINYINT DEFAULT 0,
95 Reviewed TINYINT DEFAULT 0,
99 CREATE INDEX inx6 ON Job (Name);
101 -- Create a table like Job for long term statistics
102 CREATE TABLE JobHisto (
104 Job VARCHAR(128) NOT NULL,
105 Name VARCHAR(128) NOT NULL,
106 Type CHAR(1) NOT NULL,
107 Level CHAR(1) NOT NULL,
108 ClientId INTEGER DEFAULT 0,
109 JobStatus CHAR(1) NOT NULL,
110 SchedTime DATETIME NOT NULL,
111 StartTime DATETIME DEFAULT 0,
112 EndTime DATETIME DEFAULT 0,
113 RealEndTime DATETIME DEFAULT 0,
114 JobTDate BIGINT UNSIGNED DEFAULT 0,
115 VolSessionId INTEGER UNSIGNED DEFAULT 0,
116 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
117 JobFiles INTEGER UNSIGNED DEFAULT 0,
118 JobBytes BIGINT UNSIGNED DEFAULT 0,
119 ReadBytes BIGINT UNSIGNED DEFAULT 0,
120 JobErrors INTEGER UNSIGNED DEFAULT 0,
121 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
122 PoolId INTEGER UNSIGNED DEFAULT 0,
123 FileSetId INTEGER UNSIGNED DEFAULT 0,
124 PriorJobId INTEGER UNSIGNED DEFAULT 0,
125 PurgedFiles TINYINT DEFAULT 0,
126 HasBase TINYINT DEFAULT 0,
127 HasCache TINYINT DEFAULT 0,
128 Reviewed TINYINT DEFAULT 0,
131 CREATE INDEX inx61 ON JobHisto (StartTime);
133 CREATE TABLE Location (
135 Location TEXT NOT NULL,
136 Cost INTEGER DEFAULT 0,
138 PRIMARY KEY(LocationId)
141 CREATE TABLE LocationLog (
143 Date DATETIME NOT NULL,
144 Comment TEXT NOT NULL,
145 MediaId INTEGER UNSIGNED REFERENCES Media DEFAULT 0,
146 LocationId INTEGER UNSIGNED REFERENCES LocationId DEFAULT 0,
147 NewVolStatus VARCHAR(20) NOT NULL,
148 NewEnabled TINYINT NOT NULL,
149 PRIMARY KEY(LocLogId)
155 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
156 Time DATETIME NOT NULL,
157 LogText TEXT NOT NULL,
160 CREATE INDEX LogInx1 ON Log (JobId);
163 CREATE TABLE FileSet (
165 FileSet VARCHAR(128) NOT NULL,
166 MD5 VARCHAR(25) NOT NULL,
167 CreateTime DATETIME DEFAULT 0,
168 PRIMARY KEY(FileSetId)
171 CREATE TABLE JobMedia (
173 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
174 MediaId INTEGER UNSIGNED REFERENCES Media NOT NULL,
175 FirstIndex INTEGER UNSIGNED NOT NULL,
176 LastIndex INTEGER UNSIGNED NOT NULL,
177 StartFile INTEGER UNSIGNED DEFAULT 0,
178 EndFile INTEGER UNSIGNED DEFAULT 0,
179 StartBlock INTEGER UNSIGNED DEFAULT 0,
180 EndBlock INTEGER UNSIGNED DEFAULT 0,
181 VolIndex INTEGER UNSIGNED DEFAULT 0,
182 PRIMARY KEY(JobMediaId)
185 CREATE INDEX inx7 ON JobMedia (JobId, MediaId);
190 VolumeName VARCHAR(128) NOT NULL,
191 Slot INTEGER DEFAULT 0,
192 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
193 MediaType VARCHAR(128) NOT NULL,
194 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType DEFAULT 0,
195 LabelType TINYINT DEFAULT 0,
196 FirstWritten DATETIME DEFAULT 0,
197 LastWritten DATETIME DEFAULT 0,
198 LabelDate DATETIME DEFAULT 0,
199 VolJobs INTEGER UNSIGNED DEFAULT 0,
200 VolFiles INTEGER UNSIGNED DEFAULT 0,
201 VolBlocks INTEGER UNSIGNED DEFAULT 0,
202 VolMounts INTEGER UNSIGNED DEFAULT 0,
203 VolBytes BIGINT UNSIGNED DEFAULT 0,
204 VolParts INTEGER UNSIGNED DEFAULT 0,
205 VolErrors INTEGER UNSIGNED DEFAULT 0,
206 VolWrites INTEGER UNSIGNED DEFAULT 0,
207 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
208 VolStatus VARCHAR(20) NOT NULL,
209 Enabled TINYINT DEFAULT 1,
210 Recycle TINYINT DEFAULT 0,
211 ActionOnPurge TINYINT DEFAULT 0,
212 VolRetention BIGINT UNSIGNED DEFAULT 0,
213 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
214 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
215 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
216 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
217 InChanger TINYINT DEFAULT 0,
218 StorageId INTEGER UNSIGNED REFERENCES Storage DEFAULT 0,
219 DeviceId INTEGER UNSIGNED REFERENCES Device DEFAULT 0,
220 MediaAddressing TINYINT DEFAULT 0,
221 VolReadTime BIGINT UNSIGNED DEFAULT 0,
222 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
223 EndFile INTEGER UNSIGNED DEFAULT 0,
224 EndBlock INTEGER UNSIGNED DEFAULT 0,
225 LocationId INTEGER UNSIGNED REFERENCES Location DEFAULT 0,
226 RecycleCount INTEGER UNSIGNED DEFAULT 0,
227 InitialWrite DATETIME DEFAULT 0,
228 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
229 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
234 CREATE INDEX inx8 ON Media (PoolId);
236 CREATE TABLE MediaType (
238 MediaType VARCHAR(128) NOT NULL,
239 ReadOnly TINYINT DEFAULT 0,
240 PRIMARY KEY(MediaTypeId)
243 CREATE TABLE Storage (
245 Name VARCHAR(128) NOT NULL,
246 AutoChanger TINYINT DEFAULT 0,
247 PRIMARY KEY(StorageId)
250 CREATE TABLE Device (
252 Name VARCHAR(128) NOT NULL,
253 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType NOT NULL,
254 StorageId INTEGER UNSIGNED REFERENCES Storage,
255 DevMounts INTEGER UNSIGNED DEFAULT 0,
256 DevReadBytes BIGINT UNSIGNED DEFAULT 0,
257 DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
258 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
259 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
260 DevReadTime BIGINT UNSIGNED DEFAULT 0,
261 DevWriteTime BIGINT UNSIGNED DEFAULT 0,
262 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
263 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
264 CleaningDate DATETIME DEFAULT 0,
265 CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
266 PRIMARY KEY(DeviceId)
272 Name VARCHAR(128) NOT NULL,
273 NumVols INTEGER UNSIGNED DEFAULT 0,
274 MaxVols INTEGER UNSIGNED DEFAULT 0,
275 UseOnce TINYINT DEFAULT 0,
276 UseCatalog TINYINT DEFAULT 1,
277 AcceptAnyVolume TINYINT DEFAULT 0,
278 VolRetention BIGINT UNSIGNED DEFAULT 0,
279 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
280 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
281 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
282 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
283 AutoPrune TINYINT DEFAULT 0,
284 Recycle TINYINT DEFAULT 0,
285 ActionOnPurge TINYINT DEFAULT 0,
286 PoolType VARCHAR(20) NOT NULL,
287 LabelType TINYINT DEFAULT 0,
288 LabelFormat VARCHAR(128) NOT NULL,
289 Enabled TINYINT DEFAULT 1,
290 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
291 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
292 NextPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
293 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
294 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
295 MigrationTime BIGINT UNSIGNED DEFAULT 0,
301 CREATE TABLE Client (
303 Name VARCHAR(128) NOT NULL,
304 Uname VARCHAR(255) NOT NULL, -- uname -a field
305 AutoPrune TINYINT DEFAULT 0,
306 FileRetention BIGINT UNSIGNED DEFAULT 0,
307 JobRetention BIGINT UNSIGNED DEFAULT 0,
309 PRIMARY KEY(ClientId)
312 CREATE TABLE BaseFiles (
314 BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
315 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
316 FileId INTEGER UNSIGNED REFERENCES File NOT NULL,
317 FileIndex INTEGER UNSIGNED,
321 CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
323 CREATE TABLE UnsavedFiles (
325 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
326 PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
327 FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
328 PRIMARY KEY (UnsavedId)
332 CREATE TABLE NextId (
333 id INTEGER UNSIGNED DEFAULT 0,
334 TableName TEXT NOT NULL,
335 PRIMARY KEY (TableName)
340 -- Initialize JobId to start at 1
341 INSERT INTO NextId (id, TableName) VALUES (1, 'Job');
343 CREATE TABLE Version (
344 VersionId INTEGER UNSIGNED NOT NULL
348 CREATE TABLE Counters (
349 Counter TEXT NOT NULL,
350 MinValue INTEGER DEFAULT 0,
351 MaxValue INTEGER DEFAULT 0,
352 CurrentValue INTEGER DEFAULT 0,
353 WrapCounter TEXT NOT NULL,
354 PRIMARY KEY (Counter)
357 CREATE TABLE CDImages (
358 MediaId INTEGER UNSIGNED NOT NULL,
359 LastBurn DATETIME NOT NULL,
360 PRIMARY KEY (MediaId)
363 CREATE TABLE PathHierarchy
365 PathId integer NOT NULL,
366 PPathId integer NOT NULL,
367 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
370 CREATE INDEX pathhierarchy_ppathid
371 ON PathHierarchy (PPathId);
373 CREATE TABLE PathVisibility
375 PathId integer NOT NULL,
376 JobId integer NOT NULL,
378 Files int4 DEFAULT 0,
379 CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
382 CREATE INDEX pathvisibility_jobid
383 ON PathVisibility (JobId);
385 CREATE TABLE Status (
386 JobStatus CHAR(1) NOT NULL,
389 PRIMARY KEY (JobStatus)
392 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
393 ('C', 'Created, not yet running',15);
394 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
396 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
398 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
399 ('T', 'Completed successfully', 10);
400 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
401 ('E', 'Terminated with errors', 25);
402 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
403 ('e', 'Non-fatal error',20);
404 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
405 ('f', 'Fatal error',100);
406 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
407 ('D', 'Verify found differences',15);
408 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
409 ('A', 'Canceled by user',90);
410 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
411 ('F', 'Waiting for Client',15);
412 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
413 ('S', 'Waiting for Storage daemon',15);
414 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
415 ('m', 'Waiting for new media');
416 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
417 ('M', 'Waiting for media mount',15);
418 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
419 ('s', 'Waiting for storage resource',15);
420 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
421 ('j', 'Waiting for job resource',15);
422 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
423 ('c', 'Waiting for client resource',15);
424 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
425 ('d', 'Waiting on maximum jobs',15);
426 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
427 ('t', 'Waiting on start time',15);
428 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
429 ('p', 'Waiting on higher priority jobs',15);
430 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
431 ('a', 'SD despooling attributes',15);
432 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
433 ('i', 'Doing batch insert file records',15);
436 -- Initialize Version
437 INSERT INTO Version (VersionId) VALUES (@BDB_VERSION@);
440 PRAGMA default_cache_size = 100000;
441 PRAGMA synchronous = NORMAL;
445 chmod 640 ${db_name}.db