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,
113 CREATE INDEX inx61 ON JobHisto (StartTime);
115 CREATE TABLE Location (
117 Location TEXT NOT NULL,
118 Cost INTEGER DEFAULT 0,
120 PRIMARY KEY(LocationId)
123 CREATE TABLE LocationLog (
125 Date DATETIME NOT NULL,
126 Comment TEXT NOT NULL,
127 MediaId INTEGER UNSIGNED REFERENCES Media DEFAULT 0,
128 LocationId INTEGER UNSIGNED REFERENCES LocationId DEFAULT 0,
129 NewVolStatus VARCHAR(20) NOT NULL,
130 NewEnabled TINYINT NOT NULL,
131 PRIMARY KEY(LocLogId)
137 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
138 Time DATETIME NOT NULL,
139 LogText TEXT NOT NULL,
142 CREATE INDEX LogInx1 ON Log (JobId);
145 CREATE TABLE FileSet (
147 FileSet VARCHAR(128) NOT NULL,
148 MD5 VARCHAR(25) NOT NULL,
149 CreateTime DATETIME DEFAULT 0,
150 PRIMARY KEY(FileSetId)
153 CREATE TABLE JobMedia (
155 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
156 MediaId INTEGER UNSIGNED REFERENCES Media NOT NULL,
157 FirstIndex INTEGER UNSIGNED NOT NULL,
158 LastIndex INTEGER UNSIGNED NOT NULL,
159 StartFile INTEGER UNSIGNED DEFAULT 0,
160 EndFile INTEGER UNSIGNED DEFAULT 0,
161 StartBlock INTEGER UNSIGNED DEFAULT 0,
162 EndBlock INTEGER UNSIGNED DEFAULT 0,
163 VolIndex INTEGER UNSIGNED DEFAULT 0,
164 PRIMARY KEY(JobMediaId)
167 CREATE INDEX inx7 ON JobMedia (JobId, MediaId);
172 VolumeName VARCHAR(128) NOT NULL,
173 Slot INTEGER DEFAULT 0,
174 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
175 MediaType VARCHAR(128) NOT NULL,
176 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType DEFAULT 0,
177 LabelType TINYINT DEFAULT 0,
178 FirstWritten DATETIME DEFAULT 0,
179 LastWritten DATETIME DEFAULT 0,
180 LabelDate DATETIME DEFAULT 0,
181 VolJobs INTEGER UNSIGNED DEFAULT 0,
182 VolFiles INTEGER UNSIGNED DEFAULT 0,
183 VolBlocks INTEGER UNSIGNED DEFAULT 0,
184 VolMounts INTEGER UNSIGNED DEFAULT 0,
185 VolBytes BIGINT UNSIGNED DEFAULT 0,
186 VolParts INTEGER UNSIGNED DEFAULT 0,
187 VolErrors INTEGER UNSIGNED DEFAULT 0,
188 VolWrites INTEGER UNSIGNED DEFAULT 0,
189 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
190 VolStatus VARCHAR(20) NOT NULL,
191 Enabled TINYINT DEFAULT 1,
192 Recycle TINYINT DEFAULT 0,
193 ActionOnPurge TINYINT DEFAULT 0,
194 VolRetention BIGINT UNSIGNED DEFAULT 0,
195 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
196 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
197 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
198 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
199 InChanger TINYINT DEFAULT 0,
200 StorageId INTEGER UNSIGNED REFERENCES Storage DEFAULT 0,
201 DeviceId INTEGER UNSIGNED REFERENCES Device DEFAULT 0,
202 MediaAddressing TINYINT DEFAULT 0,
203 VolReadTime BIGINT UNSIGNED DEFAULT 0,
204 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
205 EndFile INTEGER UNSIGNED DEFAULT 0,
206 EndBlock INTEGER UNSIGNED DEFAULT 0,
207 LocationId INTEGER UNSIGNED REFERENCES Location DEFAULT 0,
208 RecycleCount INTEGER UNSIGNED DEFAULT 0,
209 InitialWrite DATETIME DEFAULT 0,
210 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
211 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
216 CREATE INDEX inx8 ON Media (PoolId);
218 CREATE TABLE MediaType (
220 MediaType VARCHAR(128) NOT NULL,
221 ReadOnly TINYINT DEFAULT 0,
222 PRIMARY KEY(MediaTypeId)
225 CREATE TABLE Storage (
227 Name VARCHAR(128) NOT NULL,
228 AutoChanger TINYINT DEFAULT 0,
229 PRIMARY KEY(StorageId)
232 CREATE TABLE Device (
234 Name VARCHAR(128) NOT NULL,
235 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType NOT NULL,
236 StorageId INTEGER UNSIGNED REFERENCES Storage,
237 DevMounts INTEGER UNSIGNED DEFAULT 0,
238 DevReadBytes BIGINT UNSIGNED DEFAULT 0,
239 DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
240 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
241 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
242 DevReadTime BIGINT UNSIGNED DEFAULT 0,
243 DevWriteTime BIGINT UNSIGNED DEFAULT 0,
244 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
245 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
246 CleaningDate DATETIME DEFAULT 0,
247 CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
248 PRIMARY KEY(DeviceId)
254 Name VARCHAR(128) NOT NULL,
255 NumVols INTEGER UNSIGNED DEFAULT 0,
256 MaxVols INTEGER UNSIGNED DEFAULT 0,
257 UseOnce TINYINT DEFAULT 0,
258 UseCatalog TINYINT DEFAULT 1,
259 AcceptAnyVolume TINYINT DEFAULT 0,
260 VolRetention BIGINT UNSIGNED DEFAULT 0,
261 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
262 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
263 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
264 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
265 AutoPrune TINYINT DEFAULT 0,
266 Recycle TINYINT DEFAULT 0,
267 ActionOnPurge TINYINT DEFAULT 0,
268 PoolType VARCHAR(20) NOT NULL,
269 LabelType TINYINT DEFAULT 0,
270 LabelFormat VARCHAR(128) NOT NULL,
271 Enabled TINYINT DEFAULT 1,
272 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
273 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
274 NextPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
275 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
276 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
277 MigrationTime BIGINT UNSIGNED DEFAULT 0,
283 CREATE TABLE Client (
285 Name VARCHAR(128) NOT NULL,
286 Uname VARCHAR(255) NOT NULL, -- uname -a field
287 AutoPrune TINYINT DEFAULT 0,
288 FileRetention BIGINT UNSIGNED DEFAULT 0,
289 JobRetention BIGINT UNSIGNED DEFAULT 0,
291 PRIMARY KEY(ClientId)
294 CREATE TABLE BaseFiles (
296 BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
297 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
298 FileId INTEGER UNSIGNED REFERENCES File NOT NULL,
299 FileIndex INTEGER UNSIGNED,
303 CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
305 CREATE TABLE UnsavedFiles (
307 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
308 PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
309 FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
310 PRIMARY KEY (UnsavedId)
314 CREATE TABLE NextId (
315 id INTEGER UNSIGNED DEFAULT 0,
316 TableName TEXT NOT NULL,
317 PRIMARY KEY (TableName)
322 -- Initialize JobId to start at 1
323 INSERT INTO NextId (id, TableName) VALUES (1, 'Job');
325 CREATE TABLE Version (
326 VersionId INTEGER UNSIGNED NOT NULL
330 CREATE TABLE Counters (
331 Counter TEXT NOT NULL,
332 MinValue INTEGER DEFAULT 0,
333 MaxValue INTEGER DEFAULT 0,
334 CurrentValue INTEGER DEFAULT 0,
335 WrapCounter TEXT NOT NULL,
336 PRIMARY KEY (Counter)
339 CREATE TABLE CDImages (
340 MediaId INTEGER UNSIGNED NOT NULL,
341 LastBurn DATETIME NOT NULL,
342 PRIMARY KEY (MediaId)
345 CREATE TABLE PathHierarchy
347 PathId integer NOT NULL,
348 PPathId integer NOT NULL,
349 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
352 CREATE INDEX pathhierarchy_ppathid
353 ON PathHierarchy (PPathId);
355 CREATE TABLE PathVisibility
357 PathId integer NOT NULL,
358 JobId integer NOT NULL,
360 Files int4 DEFAULT 0,
361 CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
364 CREATE INDEX pathvisibility_jobid
365 ON PathVisibility (JobId);
367 CREATE TABLE Status (
368 JobStatus CHAR(1) NOT NULL,
371 PRIMARY KEY (JobStatus)
374 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
375 ('C', 'Created, not yet running',15);
376 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
378 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
380 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
381 ('T', 'Completed successfully', 10);
382 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
383 ('E', 'Terminated with errors', 25);
384 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
385 ('e', 'Non-fatal error',20);
386 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
387 ('f', 'Fatal error',100);
388 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
389 ('D', 'Verify found differences',15);
390 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
391 ('A', 'Canceled by user',90);
392 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
393 ('F', 'Waiting for Client',15);
394 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
395 ('S', 'Waiting for Storage daemon',15);
396 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
397 ('m', 'Waiting for new media');
398 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
399 ('M', 'Waiting for media mount',15);
400 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
401 ('s', 'Waiting for storage resource',15);
402 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
403 ('j', 'Waiting for job resource',15);
404 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
405 ('c', 'Waiting for client resource',15);
406 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
407 ('d', 'Waiting on maximum jobs',15);
408 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
409 ('t', 'Waiting on start time',15);
410 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
411 ('p', 'Waiting on higher priority jobs',15);
412 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
413 ('a', 'SD despooling attributes',15);
414 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
415 ('i', 'Doing batch insert file records',15);
418 -- Initialize Version
419 INSERT INTO Version (VersionId) VALUES (12);
422 PRAGMA default_cache_size = 100000;
423 PRAGMA synchronous = NORMAL;
427 chmod 640 ${db_name}.db