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 MarkId INTEGER UNSIGNED DEFAULT 0,
37 LStat VARCHAR(255) NOT NULL,
38 MD5 VARCHAR(255) NOT NULL,
41 CREATE INDEX inx3 ON File (JobId);
42 CREATE INDEX file_jpf_idx ON File (JobId, PathId, FilenameId);
44 -- Possibly add one or more of the following indexes
45 -- if your Verifies are too slow.
47 -- CREATE INDEX inx4 ON File (PathId);
48 -- CREATE INDEX inx5 ON File (FileNameId);
51 CREATE TABLE RestoreObject (
52 RestoreObjectId INTEGER,
53 ObjectName TEXT DEFAULT '',
54 RestoreObject TEXT DEFAULT '',
55 PluginName TEXT DEFAULT '',
56 ObjectLength INTEGER DEFAULT 0,
57 ObjectFullLength INTEGER DEFAULT 0,
58 ObjectIndex INTEGER DEFAULT 0,
59 ObjectType INTEGER DEFAULT 0,
60 FileIndex INTEGER UNSIGNED DEFAULT 0,
61 ObjectCompression INTEGER DEFAULT 0,
62 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
63 PRIMARY KEY(RestoreObjectId)
65 CREATE INDEX restore_jobid_idx ON RestoreObject (JobId);
70 Job VARCHAR(128) NOT NULL,
71 Name VARCHAR(128) NOT NULL,
72 Type CHAR(1) NOT NULL,
73 Level CHAR(1) NOT NULL,
74 ClientId INTEGER REFERENCES Client DEFAULT 0,
75 JobStatus CHAR(1) NOT NULL,
76 SchedTime DATETIME NOT NULL,
77 StartTime DATETIME DEFAULT 0,
78 EndTime DATETIME DEFAULT 0,
79 RealEndTime DATETIME DEFAULT 0,
80 JobTDate BIGINT UNSIGNED DEFAULT 0,
81 VolSessionId INTEGER UNSIGNED DEFAULT 0,
82 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
83 JobFiles INTEGER UNSIGNED DEFAULT 0,
84 JobBytes BIGINT UNSIGNED DEFAULT 0,
85 ReadBytes BIGINT UNSIGNED DEFAULT 0,
86 JobErrors INTEGER UNSIGNED DEFAULT 0,
87 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
88 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
89 FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0,
90 PriorJobId INTEGER UNSIGNED REFERENCES Job DEFAULT 0,
91 PurgedFiles TINYINT DEFAULT 0,
92 HasBase TINYINT DEFAULT 0,
93 HasCache TINYINT DEFAULT 0,
94 Reviewed TINYINT DEFAULT 0,
98 CREATE INDEX inx6 ON Job (Name);
100 -- Create a table like Job for long term statistics
101 CREATE TABLE JobHisto (
103 Job VARCHAR(128) NOT NULL,
104 Name VARCHAR(128) NOT NULL,
105 Type CHAR(1) NOT NULL,
106 Level CHAR(1) NOT NULL,
107 ClientId INTEGER DEFAULT 0,
108 JobStatus CHAR(1) NOT NULL,
109 SchedTime DATETIME NOT NULL,
110 StartTime DATETIME DEFAULT 0,
111 EndTime DATETIME DEFAULT 0,
112 RealEndTime DATETIME DEFAULT 0,
113 JobTDate BIGINT UNSIGNED DEFAULT 0,
114 VolSessionId INTEGER UNSIGNED DEFAULT 0,
115 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
116 JobFiles INTEGER UNSIGNED DEFAULT 0,
117 JobBytes BIGINT UNSIGNED DEFAULT 0,
118 ReadBytes BIGINT UNSIGNED DEFAULT 0,
119 JobErrors INTEGER UNSIGNED DEFAULT 0,
120 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
121 PoolId INTEGER UNSIGNED DEFAULT 0,
122 FileSetId INTEGER UNSIGNED DEFAULT 0,
123 PriorJobId INTEGER UNSIGNED DEFAULT 0,
124 PurgedFiles TINYINT DEFAULT 0,
125 HasBase TINYINT DEFAULT 0,
126 HasCache TINYINT DEFAULT 0,
127 Reviewed TINYINT DEFAULT 0,
130 CREATE INDEX inx61 ON JobHisto (StartTime);
132 CREATE TABLE Location (
134 Location TEXT NOT NULL,
135 Cost INTEGER DEFAULT 0,
137 PRIMARY KEY(LocationId)
140 CREATE TABLE LocationLog (
142 Date DATETIME NOT NULL,
143 Comment TEXT NOT NULL,
144 MediaId INTEGER UNSIGNED REFERENCES Media DEFAULT 0,
145 LocationId INTEGER UNSIGNED REFERENCES LocationId DEFAULT 0,
146 NewVolStatus VARCHAR(20) NOT NULL,
147 NewEnabled TINYINT NOT NULL,
148 PRIMARY KEY(LocLogId)
154 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
155 Time DATETIME NOT NULL,
156 LogText TEXT NOT NULL,
159 CREATE INDEX LogInx1 ON Log (JobId);
162 CREATE TABLE FileSet (
164 FileSet VARCHAR(128) NOT NULL,
165 MD5 VARCHAR(25) NOT NULL,
166 CreateTime DATETIME DEFAULT 0,
167 PRIMARY KEY(FileSetId)
170 CREATE TABLE JobMedia (
172 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
173 MediaId INTEGER UNSIGNED REFERENCES Media NOT NULL,
174 FirstIndex INTEGER UNSIGNED NOT NULL,
175 LastIndex INTEGER UNSIGNED NOT NULL,
176 StartFile INTEGER UNSIGNED DEFAULT 0,
177 EndFile INTEGER UNSIGNED DEFAULT 0,
178 StartBlock INTEGER UNSIGNED DEFAULT 0,
179 EndBlock INTEGER UNSIGNED DEFAULT 0,
180 VolIndex INTEGER UNSIGNED DEFAULT 0,
181 PRIMARY KEY(JobMediaId)
184 CREATE INDEX inx7 ON JobMedia (JobId, MediaId);
189 VolumeName VARCHAR(128) NOT NULL,
190 Slot INTEGER DEFAULT 0,
191 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
192 MediaType VARCHAR(128) NOT NULL,
193 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType DEFAULT 0,
194 LabelType TINYINT DEFAULT 0,
195 FirstWritten DATETIME DEFAULT 0,
196 LastWritten DATETIME DEFAULT 0,
197 LabelDate DATETIME DEFAULT 0,
198 VolJobs INTEGER UNSIGNED DEFAULT 0,
199 VolFiles INTEGER UNSIGNED DEFAULT 0,
200 VolBlocks INTEGER UNSIGNED DEFAULT 0,
201 VolMounts INTEGER UNSIGNED DEFAULT 0,
202 VolBytes BIGINT UNSIGNED DEFAULT 0,
203 VolParts INTEGER UNSIGNED DEFAULT 0,
204 VolErrors INTEGER UNSIGNED DEFAULT 0,
205 VolWrites INTEGER UNSIGNED DEFAULT 0,
206 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
207 VolStatus VARCHAR(20) NOT NULL,
208 Enabled TINYINT DEFAULT 1,
209 Recycle TINYINT DEFAULT 0,
210 ActionOnPurge TINYINT DEFAULT 0,
211 VolRetention BIGINT UNSIGNED DEFAULT 0,
212 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
213 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
214 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
215 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
216 InChanger TINYINT DEFAULT 0,
217 StorageId INTEGER UNSIGNED REFERENCES Storage DEFAULT 0,
218 DeviceId INTEGER UNSIGNED REFERENCES Device DEFAULT 0,
219 MediaAddressing TINYINT DEFAULT 0,
220 VolReadTime BIGINT UNSIGNED DEFAULT 0,
221 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
222 EndFile INTEGER UNSIGNED DEFAULT 0,
223 EndBlock INTEGER UNSIGNED DEFAULT 0,
224 LocationId INTEGER UNSIGNED REFERENCES Location DEFAULT 0,
225 RecycleCount INTEGER UNSIGNED DEFAULT 0,
226 InitialWrite DATETIME DEFAULT 0,
227 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
228 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
233 CREATE INDEX inx8 ON Media (PoolId);
235 CREATE TABLE MediaType (
237 MediaType VARCHAR(128) NOT NULL,
238 ReadOnly TINYINT DEFAULT 0,
239 PRIMARY KEY(MediaTypeId)
242 CREATE TABLE Storage (
244 Name VARCHAR(128) NOT NULL,
245 AutoChanger TINYINT DEFAULT 0,
246 PRIMARY KEY(StorageId)
249 CREATE TABLE Device (
251 Name VARCHAR(128) NOT NULL,
252 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType NOT NULL,
253 StorageId INTEGER UNSIGNED REFERENCES Storage,
254 DevMounts INTEGER UNSIGNED DEFAULT 0,
255 DevReadBytes BIGINT UNSIGNED DEFAULT 0,
256 DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
257 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
258 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
259 DevReadTime BIGINT UNSIGNED DEFAULT 0,
260 DevWriteTime BIGINT UNSIGNED DEFAULT 0,
261 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
262 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
263 CleaningDate DATETIME DEFAULT 0,
264 CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
265 PRIMARY KEY(DeviceId)
271 Name VARCHAR(128) NOT NULL,
272 NumVols INTEGER UNSIGNED DEFAULT 0,
273 MaxVols INTEGER UNSIGNED DEFAULT 0,
274 UseOnce TINYINT DEFAULT 0,
275 UseCatalog TINYINT DEFAULT 1,
276 AcceptAnyVolume TINYINT DEFAULT 0,
277 VolRetention BIGINT UNSIGNED DEFAULT 0,
278 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
279 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
280 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
281 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
282 AutoPrune TINYINT DEFAULT 0,
283 Recycle TINYINT DEFAULT 0,
284 ActionOnPurge TINYINT DEFAULT 0,
285 PoolType VARCHAR(20) NOT NULL,
286 LabelType TINYINT DEFAULT 0,
287 LabelFormat VARCHAR(128) NOT NULL,
288 Enabled TINYINT DEFAULT 1,
289 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
290 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
291 NextPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
292 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
293 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
294 MigrationTime BIGINT UNSIGNED DEFAULT 0,
300 CREATE TABLE Client (
302 Name VARCHAR(128) NOT NULL,
303 Uname VARCHAR(255) NOT NULL, -- uname -a field
304 AutoPrune TINYINT DEFAULT 0,
305 FileRetention BIGINT UNSIGNED DEFAULT 0,
306 JobRetention BIGINT UNSIGNED DEFAULT 0,
308 PRIMARY KEY(ClientId)
311 CREATE TABLE BaseFiles (
313 BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
314 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
315 FileId INTEGER UNSIGNED REFERENCES File NOT NULL,
316 FileIndex INTEGER UNSIGNED,
320 CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
322 CREATE TABLE UnsavedFiles (
324 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
325 PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
326 FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
327 PRIMARY KEY (UnsavedId)
331 CREATE TABLE NextId (
332 id INTEGER UNSIGNED DEFAULT 0,
333 TableName TEXT NOT NULL,
334 PRIMARY KEY (TableName)
339 -- Initialize JobId to start at 1
340 INSERT INTO NextId (id, TableName) VALUES (1, 'Job');
342 CREATE TABLE Version (
343 VersionId INTEGER UNSIGNED NOT NULL
347 CREATE TABLE Counters (
348 Counter TEXT NOT NULL,
349 MinValue INTEGER DEFAULT 0,
350 MaxValue INTEGER DEFAULT 0,
351 CurrentValue INTEGER DEFAULT 0,
352 WrapCounter TEXT NOT NULL,
353 PRIMARY KEY (Counter)
356 CREATE TABLE CDImages (
357 MediaId INTEGER UNSIGNED NOT NULL,
358 LastBurn DATETIME NOT NULL,
359 PRIMARY KEY (MediaId)
362 CREATE TABLE PathHierarchy
364 PathId integer NOT NULL,
365 PPathId integer NOT NULL,
366 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
369 CREATE INDEX pathhierarchy_ppathid
370 ON PathHierarchy (PPathId);
372 CREATE TABLE PathVisibility
374 PathId integer NOT NULL,
375 JobId integer NOT NULL,
377 Files int4 DEFAULT 0,
378 CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
381 CREATE INDEX pathvisibility_jobid
382 ON PathVisibility (JobId);
384 CREATE TABLE Status (
385 JobStatus CHAR(1) NOT NULL,
388 PRIMARY KEY (JobStatus)
391 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
392 ('C', 'Created, not yet running',15);
393 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
395 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
397 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
398 ('T', 'Completed successfully', 10);
399 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
400 ('E', 'Terminated with errors', 25);
401 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
402 ('e', 'Non-fatal error',20);
403 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
404 ('f', 'Fatal error',100);
405 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
406 ('D', 'Verify found differences',15);
407 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
408 ('A', 'Canceled by user',90);
409 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
410 ('F', 'Waiting for Client',15);
411 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
412 ('S', 'Waiting for Storage daemon',15);
413 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
414 ('m', 'Waiting for new media');
415 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
416 ('M', 'Waiting for media mount',15);
417 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
418 ('s', 'Waiting for storage resource',15);
419 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
420 ('j', 'Waiting for job resource',15);
421 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
422 ('c', 'Waiting for client resource',15);
423 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
424 ('d', 'Waiting on maximum jobs',15);
425 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
426 ('t', 'Waiting on start time',15);
427 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
428 ('p', 'Waiting on higher priority jobs',15);
429 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
430 ('a', 'SD despooling attributes',15);
431 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
432 ('i', 'Doing batch insert file records',15);
435 -- Initialize Version
436 INSERT INTO Version (VersionId) VALUES (@BDB_VERSION@);
439 PRAGMA default_cache_size = 100000;
440 PRAGMA synchronous = NORMAL;
444 chmod 640 ${db_name}.db