3 # shell script to create Bacula SQLite tables
10 ${bindir}/${sqlite} $* ${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,
42 CREATE INDEX inx3 ON File (JobId);
43 CREATE INDEX inx4 ON File (FilenameId, PathId);
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);
50 -- CREATE INDEX inx9 ON File (JobId, PathId, 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,
79 CREATE INDEX inx6 ON Job (Name);
81 -- Create a table like Job for long term statistics
82 CREATE TABLE JobHisto (
84 Job VARCHAR(128) NOT NULL,
85 Name VARCHAR(128) NOT NULL,
86 Type CHAR(1) NOT NULL,
87 Level CHAR(1) NOT NULL,
88 ClientId INTEGER DEFAULT 0,
89 JobStatus CHAR(1) NOT NULL,
90 SchedTime DATETIME NOT NULL,
91 StartTime DATETIME DEFAULT 0,
92 EndTime DATETIME DEFAULT 0,
93 RealEndTime DATETIME DEFAULT 0,
94 JobTDate BIGINT UNSIGNED DEFAULT 0,
95 VolSessionId INTEGER UNSIGNED DEFAULT 0,
96 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
97 JobFiles INTEGER UNSIGNED DEFAULT 0,
98 JobBytes BIGINT UNSIGNED DEFAULT 0,
99 ReadBytes BIGINT UNSIGNED DEFAULT 0,
100 JobErrors INTEGER UNSIGNED DEFAULT 0,
101 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
102 PoolId INTEGER UNSIGNED DEFAULT 0,
103 FileSetId INTEGER UNSIGNED DEFAULT 0,
104 PriorJobId INTEGER UNSIGNED DEFAULT 0,
105 PurgedFiles TINYINT DEFAULT 0,
106 HasBase TINYINT DEFAULT 0
108 CREATE INDEX inx61 ON JobHisto (StartTime);
110 CREATE TABLE Location (
112 Location TEXT NOT NULL,
113 Cost INTEGER DEFAULT 0,
115 PRIMARY KEY(LocationId)
118 CREATE TABLE LocationLog (
120 Date DATETIME NOT NULL,
121 Comment TEXT NOT NULL,
122 MediaId INTEGER UNSIGNED REFERENCES Media DEFAULT 0,
123 LocationId INTEGER UNSIGNED REFERENCES LocationId DEFAULT 0,
124 NewVolStatus VARCHAR(20) NOT NULL,
125 NewEnabled TINYINT NOT NULL,
126 PRIMARY KEY(LocLogId)
132 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
133 Time DATETIME NOT NULL,
134 LogText TEXT NOT NULL,
137 CREATE INDEX LogInx1 ON Log (JobId);
140 CREATE TABLE FileSet (
142 FileSet VARCHAR(128) NOT NULL,
143 MD5 VARCHAR(25) NOT NULL,
144 CreateTime DATETIME DEFAULT 0,
145 PRIMARY KEY(FileSetId)
148 CREATE TABLE JobMedia (
150 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
151 MediaId INTEGER UNSIGNED REFERENCES Media NOT NULL,
152 FirstIndex INTEGER UNSIGNED NOT NULL,
153 LastIndex INTEGER UNSIGNED NOT NULL,
154 StartFile INTEGER UNSIGNED DEFAULT 0,
155 EndFile INTEGER UNSIGNED DEFAULT 0,
156 StartBlock INTEGER UNSIGNED DEFAULT 0,
157 EndBlock INTEGER UNSIGNED DEFAULT 0,
158 VolIndex INTEGER UNSIGNED DEFAULT 0,
159 Copy INTEGER UNSIGNED DEFAULT 0,
160 PRIMARY KEY(JobMediaId)
163 CREATE INDEX inx7 ON JobMedia (JobId, MediaId);
168 VolumeName VARCHAR(128) NOT NULL,
169 Slot INTEGER DEFAULT 0,
170 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
171 MediaType VARCHAR(128) NOT NULL,
172 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType DEFAULT 0,
173 LabelType TINYINT DEFAULT 0,
174 FirstWritten DATETIME DEFAULT 0,
175 LastWritten DATETIME DEFAULT 0,
176 LabelDate DATETIME DEFAULT 0,
177 VolJobs INTEGER UNSIGNED DEFAULT 0,
178 VolFiles INTEGER UNSIGNED DEFAULT 0,
179 VolBlocks INTEGER UNSIGNED DEFAULT 0,
180 VolMounts INTEGER UNSIGNED DEFAULT 0,
181 VolBytes BIGINT UNSIGNED DEFAULT 0,
182 VolParts INTEGER UNSIGNED DEFAULT 0,
183 VolErrors INTEGER UNSIGNED DEFAULT 0,
184 VolWrites INTEGER UNSIGNED DEFAULT 0,
185 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
186 VolStatus VARCHAR(20) NOT NULL,
187 Enabled TINYINT DEFAULT 1,
188 Recycle TINYINT DEFAULT 0,
189 ActionOnPurge TINYINT DEFAULT 0,
190 VolRetention BIGINT UNSIGNED DEFAULT 0,
191 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
192 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
193 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
194 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
195 InChanger TINYINT DEFAULT 0,
196 StorageId INTEGER UNSIGNED REFERENCES Storage DEFAULT 0,
197 DeviceId INTEGER UNSIGNED REFERENCES Device DEFAULT 0,
198 MediaAddressing TINYINT DEFAULT 0,
199 VolReadTime BIGINT UNSIGNED DEFAULT 0,
200 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
201 EndFile INTEGER UNSIGNED DEFAULT 0,
202 EndBlock INTEGER UNSIGNED DEFAULT 0,
203 LocationId INTEGER UNSIGNED REFERENCES Location DEFAULT 0,
204 RecycleCount INTEGER UNSIGNED DEFAULT 0,
205 InitialWrite DATETIME DEFAULT 0,
206 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
207 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
212 CREATE INDEX inx8 ON Media (PoolId);
214 CREATE TABLE MediaType (
216 MediaType VARCHAR(128) NOT NULL,
217 ReadOnly TINYINT DEFAULT 0,
218 PRIMARY KEY(MediaTypeId)
221 CREATE TABLE Storage (
223 Name VARCHAR(128) NOT NULL,
224 AutoChanger TINYINT DEFAULT 0,
225 PRIMARY KEY(StorageId)
228 CREATE TABLE Device (
230 Name VARCHAR(128) NOT NULL,
231 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType NOT NULL,
232 StorageId INTEGER UNSIGNED REFERENCES Storage,
233 DevMounts INTEGER UNSIGNED DEFAULT 0,
234 DevReadBytes BIGINT UNSIGNED DEFAULT 0,
235 DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
236 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
237 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
238 DevReadTime BIGINT UNSIGNED DEFAULT 0,
239 DevWriteTime BIGINT UNSIGNED DEFAULT 0,
240 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
241 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
242 CleaningDate DATETIME DEFAULT 0,
243 CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
244 PRIMARY KEY(DeviceId)
250 Name VARCHAR(128) NOT NULL,
251 NumVols INTEGER UNSIGNED DEFAULT 0,
252 MaxVols INTEGER UNSIGNED DEFAULT 0,
253 UseOnce TINYINT DEFAULT 0,
254 UseCatalog TINYINT DEFAULT 1,
255 AcceptAnyVolume TINYINT DEFAULT 0,
256 VolRetention BIGINT UNSIGNED DEFAULT 0,
257 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
258 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
259 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
260 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
261 AutoPrune TINYINT DEFAULT 0,
262 Recycle TINYINT DEFAULT 0,
263 ActionOnPurge TINYINT DEFAULT 0,
264 PoolType VARCHAR(20) NOT NULL,
265 LabelType TINYINT DEFAULT 0,
266 LabelFormat VARCHAR(128) NOT NULL,
267 Enabled TINYINT DEFAULT 1,
268 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
269 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
270 NextPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
271 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
272 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
273 MigrationTime BIGINT UNSIGNED DEFAULT 0,
279 CREATE TABLE Client (
281 Name VARCHAR(128) NOT NULL,
282 Uname VARCHAR(255) NOT NULL, -- uname -a field
283 AutoPrune TINYINT DEFAULT 0,
284 FileRetention BIGINT UNSIGNED DEFAULT 0,
285 JobRetention BIGINT UNSIGNED DEFAULT 0,
287 PRIMARY KEY(ClientId)
290 CREATE TABLE BaseFiles (
292 BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
293 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
294 FileId INTEGER UNSIGNED REFERENCES File NOT NULL,
295 FileIndex INTEGER UNSIGNED,
299 CREATE TABLE UnsavedFiles (
301 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
302 PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
303 FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
304 PRIMARY KEY (UnsavedId)
308 CREATE TABLE NextId (
309 id INTEGER UNSIGNED DEFAULT 0,
310 TableName TEXT NOT NULL,
311 PRIMARY KEY (TableName)
316 -- Initialize JobId to start at 1
317 INSERT INTO NextId (id, TableName) VALUES (1, 'Job');
319 CREATE TABLE Version (
320 VersionId INTEGER UNSIGNED NOT NULL
324 CREATE TABLE Counters (
325 Counter TEXT NOT NULL,
326 MinValue INTEGER DEFAULT 0,
327 MaxValue INTEGER DEFAULT 0,
328 CurrentValue INTEGER DEFAULT 0,
329 WrapCounter TEXT NOT NULL,
330 PRIMARY KEY (Counter)
333 CREATE TABLE CDImages (
334 MediaId INTEGER UNSIGNED NOT NULL,
335 LastBurn DATETIME NOT NULL,
336 PRIMARY KEY (MediaId)
340 CREATE TABLE Status (
341 JobStatus CHAR(1) NOT NULL,
343 PRIMARY KEY (JobStatus)
346 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
347 ('C', 'Created, not yet running');
348 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
350 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
352 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
353 ('T', 'Completed successfully');
354 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
355 ('E', 'Terminated with errors');
356 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
357 ('e', 'Non-fatal error');
358 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
359 ('f', 'Fatal error');
360 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
361 ('D', 'Verify found differences');
362 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
363 ('A', 'Canceled by user');
364 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
365 ('F', 'Waiting for Client');
366 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
367 ('S', 'Waiting for Storage daemon');
368 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
369 ('m', 'Waiting for new media');
370 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
371 ('M', 'Waiting for media mount');
372 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
373 ('s', 'Waiting for storage resource');
374 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
375 ('j', 'Waiting for job resource');
376 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
377 ('c', 'Waiting for client resource');
378 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
379 ('d', 'Waiting on maximum jobs');
380 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
381 ('t', 'Waiting on start time');
382 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
383 ('p', 'Waiting on higher priority jobs');
384 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
385 ('a', 'SD despooling attributes');
386 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
387 ('i', 'Doing batch insert file records');
390 -- Initialize Version
391 INSERT INTO Version (VersionId) VALUES (11);
394 PRAGMA default_cache_size = 100000;
395 PRAGMA synchronous = NORMAL;
399 chmod 640 ${db_name}.db