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,
81 CREATE INDEX inx6 ON Job (Name);
83 -- Create a table like Job for long term statistics
84 CREATE TABLE JobHisto (
86 Job VARCHAR(128) NOT NULL,
87 Name VARCHAR(128) NOT NULL,
88 Type CHAR(1) NOT NULL,
89 Level CHAR(1) NOT NULL,
90 ClientId INTEGER DEFAULT 0,
91 JobStatus CHAR(1) NOT NULL,
92 SchedTime DATETIME NOT NULL,
93 StartTime DATETIME DEFAULT 0,
94 EndTime DATETIME DEFAULT 0,
95 RealEndTime DATETIME DEFAULT 0,
96 JobTDate BIGINT UNSIGNED DEFAULT 0,
97 VolSessionId INTEGER UNSIGNED DEFAULT 0,
98 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
99 JobFiles INTEGER UNSIGNED DEFAULT 0,
100 JobBytes BIGINT UNSIGNED DEFAULT 0,
101 ReadBytes BIGINT UNSIGNED DEFAULT 0,
102 JobErrors INTEGER UNSIGNED DEFAULT 0,
103 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
104 PoolId INTEGER UNSIGNED DEFAULT 0,
105 FileSetId INTEGER UNSIGNED DEFAULT 0,
106 PriorJobId INTEGER UNSIGNED DEFAULT 0,
107 PurgedFiles TINYINT DEFAULT 0,
108 HasBase TINYINT DEFAULT 0,
109 HasCache TINYINT DEFAULT 0,
112 CREATE INDEX inx61 ON JobHisto (StartTime);
114 CREATE TABLE Location (
116 Location TEXT NOT NULL,
117 Cost INTEGER DEFAULT 0,
119 PRIMARY KEY(LocationId)
122 CREATE TABLE LocationLog (
124 Date DATETIME NOT NULL,
125 Comment TEXT NOT NULL,
126 MediaId INTEGER UNSIGNED REFERENCES Media DEFAULT 0,
127 LocationId INTEGER UNSIGNED REFERENCES LocationId DEFAULT 0,
128 NewVolStatus VARCHAR(20) NOT NULL,
129 NewEnabled TINYINT NOT NULL,
130 PRIMARY KEY(LocLogId)
136 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
137 Time DATETIME NOT NULL,
138 LogText TEXT NOT NULL,
141 CREATE INDEX LogInx1 ON Log (JobId);
144 CREATE TABLE FileSet (
146 FileSet VARCHAR(128) NOT NULL,
147 MD5 VARCHAR(25) NOT NULL,
148 CreateTime DATETIME DEFAULT 0,
149 PRIMARY KEY(FileSetId)
152 CREATE TABLE JobMedia (
154 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
155 MediaId INTEGER UNSIGNED REFERENCES Media NOT NULL,
156 FirstIndex INTEGER UNSIGNED NOT NULL,
157 LastIndex INTEGER UNSIGNED NOT NULL,
158 StartFile INTEGER UNSIGNED DEFAULT 0,
159 EndFile INTEGER UNSIGNED DEFAULT 0,
160 StartBlock INTEGER UNSIGNED DEFAULT 0,
161 EndBlock INTEGER UNSIGNED DEFAULT 0,
162 VolIndex INTEGER UNSIGNED DEFAULT 0,
163 PRIMARY KEY(JobMediaId)
166 CREATE INDEX inx7 ON JobMedia (JobId, MediaId);
171 VolumeName VARCHAR(128) NOT NULL,
172 Slot INTEGER DEFAULT 0,
173 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
174 MediaType VARCHAR(128) NOT NULL,
175 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType DEFAULT 0,
176 LabelType TINYINT DEFAULT 0,
177 FirstWritten DATETIME DEFAULT 0,
178 LastWritten DATETIME DEFAULT 0,
179 LabelDate DATETIME DEFAULT 0,
180 VolJobs INTEGER UNSIGNED DEFAULT 0,
181 VolFiles INTEGER UNSIGNED DEFAULT 0,
182 VolBlocks INTEGER UNSIGNED DEFAULT 0,
183 VolMounts INTEGER UNSIGNED DEFAULT 0,
184 VolBytes BIGINT UNSIGNED DEFAULT 0,
185 VolParts INTEGER UNSIGNED DEFAULT 0,
186 VolErrors INTEGER UNSIGNED DEFAULT 0,
187 VolWrites INTEGER UNSIGNED DEFAULT 0,
188 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
189 VolStatus VARCHAR(20) NOT NULL,
190 Enabled TINYINT DEFAULT 1,
191 Recycle TINYINT DEFAULT 0,
192 ActionOnPurge TINYINT DEFAULT 0,
193 VolRetention BIGINT UNSIGNED DEFAULT 0,
194 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
195 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
196 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
197 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
198 InChanger TINYINT DEFAULT 0,
199 StorageId INTEGER UNSIGNED REFERENCES Storage DEFAULT 0,
200 DeviceId INTEGER UNSIGNED REFERENCES Device DEFAULT 0,
201 MediaAddressing TINYINT DEFAULT 0,
202 VolReadTime BIGINT UNSIGNED DEFAULT 0,
203 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
204 EndFile INTEGER UNSIGNED DEFAULT 0,
205 EndBlock INTEGER UNSIGNED DEFAULT 0,
206 LocationId INTEGER UNSIGNED REFERENCES Location DEFAULT 0,
207 RecycleCount INTEGER UNSIGNED DEFAULT 0,
208 InitialWrite DATETIME DEFAULT 0,
209 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
210 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
215 CREATE INDEX inx8 ON Media (PoolId);
217 CREATE TABLE MediaType (
219 MediaType VARCHAR(128) NOT NULL,
220 ReadOnly TINYINT DEFAULT 0,
221 PRIMARY KEY(MediaTypeId)
224 CREATE TABLE Storage (
226 Name VARCHAR(128) NOT NULL,
227 AutoChanger TINYINT DEFAULT 0,
228 PRIMARY KEY(StorageId)
231 CREATE TABLE Device (
233 Name VARCHAR(128) NOT NULL,
234 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType NOT NULL,
235 StorageId INTEGER UNSIGNED REFERENCES Storage,
236 DevMounts INTEGER UNSIGNED DEFAULT 0,
237 DevReadBytes BIGINT UNSIGNED DEFAULT 0,
238 DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
239 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
240 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
241 DevReadTime BIGINT UNSIGNED DEFAULT 0,
242 DevWriteTime BIGINT UNSIGNED DEFAULT 0,
243 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
244 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
245 CleaningDate DATETIME DEFAULT 0,
246 CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
247 PRIMARY KEY(DeviceId)
253 Name VARCHAR(128) NOT NULL,
254 NumVols INTEGER UNSIGNED DEFAULT 0,
255 MaxVols INTEGER UNSIGNED DEFAULT 0,
256 UseOnce TINYINT DEFAULT 0,
257 UseCatalog TINYINT DEFAULT 1,
258 AcceptAnyVolume TINYINT DEFAULT 0,
259 VolRetention BIGINT UNSIGNED DEFAULT 0,
260 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
261 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
262 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
263 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
264 AutoPrune TINYINT DEFAULT 0,
265 Recycle TINYINT DEFAULT 0,
266 ActionOnPurge TINYINT DEFAULT 0,
267 PoolType VARCHAR(20) NOT NULL,
268 LabelType TINYINT DEFAULT 0,
269 LabelFormat VARCHAR(128) NOT NULL,
270 Enabled TINYINT DEFAULT 1,
271 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
272 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
273 NextPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
274 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
275 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
276 MigrationTime BIGINT UNSIGNED DEFAULT 0,
282 CREATE TABLE Client (
284 Name VARCHAR(128) NOT NULL,
285 Uname VARCHAR(255) NOT NULL, -- uname -a field
286 AutoPrune TINYINT DEFAULT 0,
287 FileRetention BIGINT UNSIGNED DEFAULT 0,
288 JobRetention BIGINT UNSIGNED DEFAULT 0,
290 PRIMARY KEY(ClientId)
293 CREATE TABLE BaseFiles (
295 BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
296 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
297 FileId INTEGER UNSIGNED REFERENCES File NOT NULL,
298 FileIndex INTEGER UNSIGNED,
302 CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
304 CREATE TABLE UnsavedFiles (
306 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
307 PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
308 FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
309 PRIMARY KEY (UnsavedId)
313 CREATE TABLE NextId (
314 id INTEGER UNSIGNED DEFAULT 0,
315 TableName TEXT NOT NULL,
316 PRIMARY KEY (TableName)
321 -- Initialize JobId to start at 1
322 INSERT INTO NextId (id, TableName) VALUES (1, 'Job');
324 CREATE TABLE Version (
325 VersionId INTEGER UNSIGNED NOT NULL
329 CREATE TABLE Counters (
330 Counter TEXT NOT NULL,
331 MinValue INTEGER DEFAULT 0,
332 MaxValue INTEGER DEFAULT 0,
333 CurrentValue INTEGER DEFAULT 0,
334 WrapCounter TEXT NOT NULL,
335 PRIMARY KEY (Counter)
338 CREATE TABLE CDImages (
339 MediaId INTEGER UNSIGNED NOT NULL,
340 LastBurn DATETIME NOT NULL,
341 PRIMARY KEY (MediaId)
344 CREATE TABLE PathHierarchy
346 PathId integer NOT NULL,
347 PPathId integer NOT NULL,
348 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
351 CREATE INDEX pathhierarchy_ppathid
352 ON PathHierarchy (PPathId);
354 CREATE TABLE PathVisibility
356 PathId integer NOT NULL,
357 JobId integer NOT NULL,
359 Files int4 DEFAULT 0,
360 CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
363 CREATE INDEX pathvisibility_jobid
364 ON PathVisibility (JobId);
366 CREATE TABLE Status (
367 JobStatus CHAR(1) NOT NULL,
370 PRIMARY KEY (JobStatus)
373 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
374 ('C', 'Created, not yet running',15);
375 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
377 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
379 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
380 ('T', 'Completed successfully', 10);
381 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
382 ('E', 'Terminated with errors', 25);
383 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
384 ('e', 'Non-fatal error',20);
385 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
386 ('f', 'Fatal error',100);
387 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
388 ('D', 'Verify found differences',15);
389 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
390 ('A', 'Canceled by user',90);
391 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
392 ('F', 'Waiting for Client',15);
393 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
394 ('S', 'Waiting for Storage daemon',15);
395 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
396 ('m', 'Waiting for new media');
397 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
398 ('M', 'Waiting for media mount',15);
399 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
400 ('s', 'Waiting for storage resource',15);
401 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
402 ('j', 'Waiting for job resource',15);
403 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
404 ('c', 'Waiting for client resource',15);
405 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
406 ('d', 'Waiting on maximum jobs',15);
407 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
408 ('t', 'Waiting on start time',15);
409 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
410 ('p', 'Waiting on higher priority jobs',15);
411 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
412 ('a', 'SD despooling attributes',15);
413 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
414 ('i', 'Doing batch insert file records',15);
417 -- Initialize Version
418 INSERT INTO Version (VersionId) VALUES (12);
421 PRAGMA default_cache_size = 100000;
422 PRAGMA synchronous = NORMAL;
426 chmod 640 ${db_name}.db