3 # shell script to create Bacula SQLite tables
11 ${sqlite} $* ${db_name}.db <<END-OF-DATA
12 CREATE TABLE Filename (
15 PRIMARY KEY(FilenameId)
18 CREATE INDEX inx1 ON Filename (Name);
26 CREATE INDEX inx2 ON Path (Path);
29 -- FileIndex can be 0 for FT_DELETED files
30 -- FileNameId can link to Filename.Name='' for directories
33 FileIndex INTEGER UNSIGNED NOT NULL,
34 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
35 PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
36 FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
37 MarkId INTEGER UNSIGNED DEFAULT 0,
38 LStat VARCHAR(255) NOT NULL,
39 MD5 VARCHAR(255) NOT NULL,
43 CREATE INDEX inx3 ON File (JobId);
44 CREATE INDEX file_jpf_idx ON File (JobId, PathId, FilenameId);
46 -- Possibly add one or more of the following indexes
47 -- if your Verifies are too slow.
49 -- CREATE INDEX inx4 ON File (PathId);
50 -- CREATE INDEX inx5 ON File (FileNameId);
54 Job VARCHAR(128) NOT NULL,
55 Name VARCHAR(128) NOT NULL,
56 Type CHAR(1) NOT NULL,
57 Level CHAR(1) NOT NULL,
58 ClientId INTEGER REFERENCES Client DEFAULT 0,
59 JobStatus CHAR(1) NOT NULL,
60 SchedTime DATETIME NOT NULL,
61 StartTime DATETIME DEFAULT 0,
62 EndTime DATETIME DEFAULT 0,
63 RealEndTime DATETIME DEFAULT 0,
64 JobTDate BIGINT UNSIGNED DEFAULT 0,
65 VolSessionId INTEGER UNSIGNED DEFAULT 0,
66 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
67 JobFiles INTEGER UNSIGNED DEFAULT 0,
68 JobBytes BIGINT UNSIGNED DEFAULT 0,
69 ReadBytes BIGINT UNSIGNED DEFAULT 0,
70 JobErrors INTEGER UNSIGNED DEFAULT 0,
71 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
72 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
73 FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0,
74 PriorJobId INTEGER UNSIGNED REFERENCES Job DEFAULT 0,
75 PurgedFiles TINYINT DEFAULT 0,
76 HasBase TINYINT DEFAULT 0,
77 HasCache TINYINT DEFAULT 0,
78 Reviewed TINYINT DEFAULT 0,
82 CREATE INDEX inx6 ON Job (Name);
84 -- Create a table like Job for long term statistics
85 CREATE TABLE JobHisto (
87 Job VARCHAR(128) NOT NULL,
88 Name VARCHAR(128) NOT NULL,
89 Type CHAR(1) NOT NULL,
90 Level CHAR(1) NOT NULL,
91 ClientId INTEGER DEFAULT 0,
92 JobStatus CHAR(1) NOT NULL,
93 SchedTime DATETIME NOT NULL,
94 StartTime DATETIME DEFAULT 0,
95 EndTime DATETIME DEFAULT 0,
96 RealEndTime DATETIME DEFAULT 0,
97 JobTDate BIGINT UNSIGNED DEFAULT 0,
98 VolSessionId INTEGER UNSIGNED DEFAULT 0,
99 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
100 JobFiles INTEGER UNSIGNED DEFAULT 0,
101 JobBytes BIGINT UNSIGNED DEFAULT 0,
102 ReadBytes BIGINT UNSIGNED DEFAULT 0,
103 JobErrors INTEGER UNSIGNED DEFAULT 0,
104 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
105 PoolId INTEGER UNSIGNED DEFAULT 0,
106 FileSetId INTEGER UNSIGNED DEFAULT 0,
107 PriorJobId INTEGER UNSIGNED DEFAULT 0,
108 PurgedFiles TINYINT DEFAULT 0,
109 HasBase TINYINT DEFAULT 0,
110 HasCache TINYINT DEFAULT 0,
111 Reviewed TINYINT DEFAULT 0,
114 CREATE INDEX inx61 ON JobHisto (StartTime);
116 CREATE TABLE Location (
118 Location TEXT NOT NULL,
119 Cost INTEGER DEFAULT 0,
121 PRIMARY KEY(LocationId)
124 CREATE TABLE LocationLog (
126 Date DATETIME NOT NULL,
127 Comment TEXT NOT NULL,
128 MediaId INTEGER UNSIGNED REFERENCES Media DEFAULT 0,
129 LocationId INTEGER UNSIGNED REFERENCES LocationId DEFAULT 0,
130 NewVolStatus VARCHAR(20) NOT NULL,
131 NewEnabled TINYINT NOT NULL,
132 PRIMARY KEY(LocLogId)
138 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
139 Time DATETIME NOT NULL,
140 LogText TEXT NOT NULL,
143 CREATE INDEX LogInx1 ON Log (JobId);
146 CREATE TABLE FileSet (
148 FileSet VARCHAR(128) NOT NULL,
149 MD5 VARCHAR(25) NOT NULL,
150 CreateTime DATETIME DEFAULT 0,
151 PRIMARY KEY(FileSetId)
154 CREATE TABLE JobMedia (
156 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
157 MediaId INTEGER UNSIGNED REFERENCES Media NOT NULL,
158 FirstIndex INTEGER UNSIGNED NOT NULL,
159 LastIndex INTEGER UNSIGNED NOT NULL,
160 StartFile INTEGER UNSIGNED DEFAULT 0,
161 EndFile INTEGER UNSIGNED DEFAULT 0,
162 StartBlock INTEGER UNSIGNED DEFAULT 0,
163 EndBlock INTEGER UNSIGNED DEFAULT 0,
164 VolIndex INTEGER UNSIGNED DEFAULT 0,
165 PRIMARY KEY(JobMediaId)
168 CREATE INDEX inx7 ON JobMedia (JobId, MediaId);
173 VolumeName VARCHAR(128) NOT NULL,
174 Slot INTEGER DEFAULT 0,
175 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
176 MediaType VARCHAR(128) NOT NULL,
177 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType DEFAULT 0,
178 LabelType TINYINT DEFAULT 0,
179 FirstWritten DATETIME DEFAULT 0,
180 LastWritten DATETIME DEFAULT 0,
181 LabelDate DATETIME DEFAULT 0,
182 VolJobs INTEGER UNSIGNED DEFAULT 0,
183 VolFiles INTEGER UNSIGNED DEFAULT 0,
184 VolBlocks INTEGER UNSIGNED DEFAULT 0,
185 VolMounts INTEGER UNSIGNED DEFAULT 0,
186 VolBytes BIGINT UNSIGNED DEFAULT 0,
187 VolParts INTEGER UNSIGNED DEFAULT 0,
188 VolErrors INTEGER UNSIGNED DEFAULT 0,
189 VolWrites INTEGER UNSIGNED DEFAULT 0,
190 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
191 VolStatus VARCHAR(20) NOT NULL,
192 Enabled TINYINT DEFAULT 1,
193 Recycle TINYINT DEFAULT 0,
194 ActionOnPurge TINYINT DEFAULT 0,
195 VolRetention BIGINT UNSIGNED DEFAULT 0,
196 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
197 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
198 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
199 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
200 InChanger TINYINT DEFAULT 0,
201 StorageId INTEGER UNSIGNED REFERENCES Storage DEFAULT 0,
202 DeviceId INTEGER UNSIGNED REFERENCES Device DEFAULT 0,
203 MediaAddressing TINYINT DEFAULT 0,
204 VolReadTime BIGINT UNSIGNED DEFAULT 0,
205 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
206 EndFile INTEGER UNSIGNED DEFAULT 0,
207 EndBlock INTEGER UNSIGNED DEFAULT 0,
208 LocationId INTEGER UNSIGNED REFERENCES Location DEFAULT 0,
209 RecycleCount INTEGER UNSIGNED DEFAULT 0,
210 InitialWrite DATETIME DEFAULT 0,
211 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
212 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
217 CREATE INDEX inx8 ON Media (PoolId);
219 CREATE TABLE MediaType (
221 MediaType VARCHAR(128) NOT NULL,
222 ReadOnly TINYINT DEFAULT 0,
223 PRIMARY KEY(MediaTypeId)
226 CREATE TABLE Storage (
228 Name VARCHAR(128) NOT NULL,
229 AutoChanger TINYINT DEFAULT 0,
230 PRIMARY KEY(StorageId)
233 CREATE TABLE Device (
235 Name VARCHAR(128) NOT NULL,
236 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType NOT NULL,
237 StorageId INTEGER UNSIGNED REFERENCES Storage,
238 DevMounts INTEGER UNSIGNED DEFAULT 0,
239 DevReadBytes BIGINT UNSIGNED DEFAULT 0,
240 DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
241 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
242 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
243 DevReadTime BIGINT UNSIGNED DEFAULT 0,
244 DevWriteTime BIGINT UNSIGNED DEFAULT 0,
245 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
246 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
247 CleaningDate DATETIME DEFAULT 0,
248 CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
249 PRIMARY KEY(DeviceId)
255 Name VARCHAR(128) NOT NULL,
256 NumVols INTEGER UNSIGNED DEFAULT 0,
257 MaxVols INTEGER UNSIGNED DEFAULT 0,
258 UseOnce TINYINT DEFAULT 0,
259 UseCatalog TINYINT DEFAULT 1,
260 AcceptAnyVolume TINYINT DEFAULT 0,
261 VolRetention BIGINT UNSIGNED DEFAULT 0,
262 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
263 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
264 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
265 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
266 AutoPrune TINYINT DEFAULT 0,
267 Recycle TINYINT DEFAULT 0,
268 ActionOnPurge TINYINT DEFAULT 0,
269 PoolType VARCHAR(20) NOT NULL,
270 LabelType TINYINT DEFAULT 0,
271 LabelFormat VARCHAR(128) NOT NULL,
272 Enabled TINYINT DEFAULT 1,
273 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
274 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
275 NextPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
276 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
277 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
278 MigrationTime BIGINT UNSIGNED DEFAULT 0,
284 CREATE TABLE Client (
286 Name VARCHAR(128) NOT NULL,
287 Uname VARCHAR(255) NOT NULL, -- uname -a field
288 AutoPrune TINYINT DEFAULT 0,
289 FileRetention BIGINT UNSIGNED DEFAULT 0,
290 JobRetention BIGINT UNSIGNED DEFAULT 0,
292 PRIMARY KEY(ClientId)
295 CREATE TABLE BaseFiles (
297 BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
298 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
299 FileId INTEGER UNSIGNED REFERENCES File NOT NULL,
300 FileIndex INTEGER UNSIGNED,
304 CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
306 CREATE TABLE UnsavedFiles (
308 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
309 PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
310 FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
311 PRIMARY KEY (UnsavedId)
315 CREATE TABLE NextId (
316 id INTEGER UNSIGNED DEFAULT 0,
317 TableName TEXT NOT NULL,
318 PRIMARY KEY (TableName)
323 -- Initialize JobId to start at 1
324 INSERT INTO NextId (id, TableName) VALUES (1, 'Job');
326 CREATE TABLE Version (
327 VersionId INTEGER UNSIGNED NOT NULL
331 CREATE TABLE Counters (
332 Counter TEXT NOT NULL,
333 MinValue INTEGER DEFAULT 0,
334 MaxValue INTEGER DEFAULT 0,
335 CurrentValue INTEGER DEFAULT 0,
336 WrapCounter TEXT NOT NULL,
337 PRIMARY KEY (Counter)
340 CREATE TABLE CDImages (
341 MediaId INTEGER UNSIGNED NOT NULL,
342 LastBurn DATETIME NOT NULL,
343 PRIMARY KEY (MediaId)
346 CREATE TABLE PathHierarchy
348 PathId integer NOT NULL,
349 PPathId integer NOT NULL,
350 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
353 CREATE INDEX pathhierarchy_ppathid
354 ON PathHierarchy (PPathId);
356 CREATE TABLE PathVisibility
358 PathId integer NOT NULL,
359 JobId integer NOT NULL,
361 Files int4 DEFAULT 0,
362 CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
365 CREATE INDEX pathvisibility_jobid
366 ON PathVisibility (JobId);
368 CREATE TABLE Status (
369 JobStatus CHAR(1) NOT NULL,
372 PRIMARY KEY (JobStatus)
375 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
376 ('C', 'Created, not yet running',15);
377 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
379 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
381 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
382 ('T', 'Completed successfully', 10);
383 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
384 ('E', 'Terminated with errors', 25);
385 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
386 ('e', 'Non-fatal error',20);
387 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
388 ('f', 'Fatal error',100);
389 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
390 ('D', 'Verify found differences',15);
391 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
392 ('A', 'Canceled by user',90);
393 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
394 ('F', 'Waiting for Client',15);
395 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
396 ('S', 'Waiting for Storage daemon',15);
397 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
398 ('m', 'Waiting for new media');
399 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
400 ('M', 'Waiting for media mount',15);
401 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
402 ('s', 'Waiting for storage resource',15);
403 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
404 ('j', 'Waiting for job resource',15);
405 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
406 ('c', 'Waiting for client resource',15);
407 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
408 ('d', 'Waiting on maximum jobs',15);
409 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
410 ('t', 'Waiting on start time',15);
411 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
412 ('p', 'Waiting on higher priority jobs',15);
413 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
414 ('a', 'SD despooling attributes',15);
415 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
416 ('i', 'Doing batch insert file records',15);
419 -- Initialize Version
420 INSERT INTO Version (VersionId) VALUES (12);
423 PRAGMA default_cache_size = 100000;
424 PRAGMA synchronous = NORMAL;
428 chmod 640 ${db_name}.db