3 # shell script to create Bacula SQLite tables
9 ${bindir}/${sqlite} $* bacula.db <<END-OF-DATA
10 CREATE TABLE Filename (
13 PRIMARY KEY(FilenameId)
16 CREATE INDEX inx1 ON Filename (Name);
24 CREATE INDEX inx2 ON Path (Path);
29 FileIndex INTEGER UNSIGNED NOT NULL,
30 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
31 PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
32 FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
33 MarkId INTEGER UNSIGNED DEFAULT 0,
34 LStat VARCHAR(255) NOT NULL,
35 MD5 VARCHAR(255) NOT NULL,
39 CREATE INDEX inx3 ON File (JobId);
40 CREATE INDEX inx4 ON File (FilenameId, PathId);
42 -- Possibly add one or more of the following indexes
43 -- if your Verifies are too slow.
45 -- CREATE INDEX inx4 ON File (PathId);
46 -- CREATE INDEX inx5 ON File (FileNameId);
47 -- CREATE INDEX inx9 ON File (JobId, PathId, FilenameId);
51 Job VARCHAR(128) NOT NULL,
52 Name VARCHAR(128) NOT NULL,
55 ClientId INTEGER REFERENCES Client DEFAULT 0,
56 JobStatus CHAR NOT NULL,
57 SchedTime DATETIME NOT NULL,
58 StartTime DATETIME DEFAULT 0,
59 EndTime DATETIME DEFAULT 0,
60 RealEndTime DATETIME DEFAULT 0,
61 JobTDate BIGINT UNSIGNED DEFAULT 0,
62 VolSessionId INTEGER UNSIGNED DEFAULT 0,
63 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
64 JobFiles INTEGER UNSIGNED DEFAULT 0,
65 JobBytes BIGINT UNSIGNED DEFAULT 0,
66 JobErrors INTEGER UNSIGNED DEFAULT 0,
67 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
68 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
69 FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0,
70 PriorJobId INTEGER UNSIGNED REFERENCES Job DEFAULT 0,
71 PurgedFiles TINYINT DEFAULT 0,
72 HasBase TINYINT DEFAULT 0,
75 CREATE INDEX inx6 ON Job (Name);
77 CREATE TABLE Location (
79 Location TEXT NOT NULL,
80 Cost INTEGER DEFAULT 0,
82 PRIMARY KEY(LocationId)
85 CREATE TABLE LocationLog (
87 Date DATETIME NOT NULL,
88 Comment TEXT NOT NULL,
89 MediaId INTEGER UNSIGNED REFERENCES Media DEFAULT 0,
90 LocationId INTEGER UNSIGNED REFERENCES LocationId DEFAULT 0,
91 NewVolStatus VARCHAR(20) NOT NULL,
92 NewEnabled TINYINT NOT NULL,
99 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
100 Time DATETIME NOT NULL,
101 LogText TEXT NOT NULL,
104 CREATE INDEX LogInx1 ON File (JobId);
107 CREATE TABLE FileSet (
109 FileSet VARCHAR(128) NOT NULL,
110 MD5 VARCHAR(25) NOT NULL,
111 CreateTime DATETIME DEFAULT 0,
112 PRIMARY KEY(FileSetId)
115 CREATE TABLE JobMedia (
117 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
118 MediaId INTEGER UNSIGNED REFERENCES Media NOT NULL,
119 FirstIndex INTEGER UNSIGNED NOT NULL,
120 LastIndex INTEGER UNSIGNED NOT NULL,
121 StartFile INTEGER UNSIGNED DEFAULT 0,
122 EndFile INTEGER UNSIGNED DEFAULT 0,
123 StartBlock INTEGER UNSIGNED DEFAULT 0,
124 EndBlock INTEGER UNSIGNED DEFAULT 0,
125 VolIndex INTEGER UNSIGNED DEFAULT 0,
126 Copy INTEGER UNSIGNED DEFAULT 0,
127 PRIMARY KEY(JobMediaId)
130 CREATE INDEX inx7 ON JobMedia (JobId, MediaId);
135 VolumeName VARCHAR(128) NOT NULL,
136 Slot INTEGER DEFAULT 0,
137 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
138 MediaType VARCHAR(128) NOT NULL,
139 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType DEFAULT 0,
140 LabelType TINYINT DEFAULT 0,
141 FirstWritten DATETIME DEFAULT 0,
142 LastWritten DATETIME DEFAULT 0,
143 LabelDate DATETIME DEFAULT 0,
144 VolJobs INTEGER UNSIGNED DEFAULT 0,
145 VolFiles INTEGER UNSIGNED DEFAULT 0,
146 VolBlocks INTEGER UNSIGNED DEFAULT 0,
147 VolMounts INTEGER UNSIGNED DEFAULT 0,
148 VolBytes BIGINT UNSIGNED DEFAULT 0,
149 VolParts INTEGER UNSIGNED DEFAULT 0,
150 VolErrors INTEGER UNSIGNED DEFAULT 0,
151 VolWrites INTEGER UNSIGNED DEFAULT 0,
152 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
153 VolStatus VARCHAR(20) NOT NULL,
154 Enabled TINYINT DEFAULT 1,
155 Recycle TINYINT DEFAULT 0,
156 VolRetention BIGINT UNSIGNED DEFAULT 0,
157 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
158 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
159 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
160 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
161 InChanger TINYINT DEFAULT 0,
162 StorageId INTEGER UNSIGNED REFERENCES Storage DEFAULT 0,
163 DeviceId INTEGER UNSIGNED REFERENCES Device DEFAULT 0,
164 MediaAddressing TINYINT DEFAULT 0,
165 VolReadTime BIGINT UNSIGNED DEFAULT 0,
166 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
167 EndFile INTEGER UNSIGNED DEFAULT 0,
168 EndBlock INTEGER UNSIGNED DEFAULT 0,
169 LocationId INTEGER UNSIGNED REFERENCES Location DEFAULT 0,
170 RecycleCount INTEGER UNSIGNED DEFAULT 0,
171 InitialWrite DATETIME DEFAULT 0,
172 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
173 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
178 CREATE INDEX inx8 ON Media (PoolId);
180 CREATE TABLE MediaType (
182 MediaType VARCHAR(128) NOT NULL,
183 ReadOnly TINYINT DEFAULT 0,
184 PRIMARY KEY(MediaTypeId)
187 CREATE TABLE Storage (
189 Name VARCHAR(128) NOT NULL,
190 AutoChanger TINYINT DEFAULT 0,
191 PRIMARY KEY(StorageId)
194 CREATE TABLE Device (
196 Name VARCHAR(128) NOT NULL,
197 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType NOT NULL,
198 StorageId INTEGER UNSIGNED REFERENCES Storage,
199 DevMounts INTEGER UNSIGNED DEFAULT 0,
200 DevReadBytes BIGINT UNSIGNED DEFAULT 0,
201 DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
202 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
203 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
204 DevReadTime BIGINT UNSIGNED DEFAULT 0,
205 DevWriteTime BIGINT UNSIGNED DEFAULT 0,
206 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
207 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
208 CleaningDate DATETIME DEFAULT 0,
209 CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
210 PRIMARY KEY(DeviceId)
216 Name VARCHAR(128) NOT NULL,
217 NumVols INTEGER UNSIGNED DEFAULT 0,
218 MaxVols INTEGER UNSIGNED DEFAULT 0,
219 UseOnce TINYINT DEFAULT 0,
220 UseCatalog TINYINT DEFAULT 1,
221 AcceptAnyVolume TINYINT DEFAULT 0,
222 VolRetention BIGINT UNSIGNED DEFAULT 0,
223 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
224 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
225 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
226 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
227 AutoPrune TINYINT DEFAULT 0,
228 Recycle TINYINT DEFAULT 0,
229 PoolType VARCHAR(20) NOT NULL,
230 LabelType TINYINT DEFAULT 0,
231 LabelFormat VARCHAR(128) NOT NULL,
232 Enabled TINYINT DEFAULT 1,
233 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
234 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
235 NextPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
236 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
237 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
238 MigrationTime BIGINT UNSIGNED DEFAULT 0,
244 CREATE TABLE Client (
246 Name VARCHAR(128) NOT NULL,
247 Uname VARCHAR(255) NOT NULL, -- uname -a field
248 AutoPrune TINYINT DEFAULT 0,
249 FileRetention BIGINT UNSIGNED DEFAULT 0,
250 JobRetention BIGINT UNSIGNED DEFAULT 0,
252 PRIMARY KEY(ClientId)
255 CREATE TABLE BaseFiles (
257 BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
258 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
259 FileId INTEGER UNSIGNED REFERENCES File NOT NULL,
260 FileIndex INTEGER UNSIGNED,
264 CREATE TABLE UnsavedFiles (
266 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
267 PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
268 FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
269 PRIMARY KEY (UnsavedId)
273 CREATE TABLE NextId (
274 id INTEGER UNSIGNED DEFAULT 0,
275 TableName TEXT NOT NULL,
276 PRIMARY KEY (TableName)
281 -- Initialize JobId to start at 1
282 INSERT INTO NextId (id, TableName) VALUES (1, "Job");
284 CREATE TABLE Version (
285 VersionId INTEGER UNSIGNED NOT NULL
289 CREATE TABLE Counters (
290 Counter TEXT NOT NULL,
291 MinValue INTEGER DEFAULT 0,
292 MaxValue INTEGER DEFAULT 0,
293 CurrentValue INTEGER DEFAULT 0,
294 WrapCounter TEXT NOT NULL,
295 PRIMARY KEY (Counter)
298 CREATE TABLE CDImages (
299 MediaId INTEGER UNSIGNED NOT NULL,
300 LastBurn DATETIME NOT NULL,
301 PRIMARY KEY (MediaId)
305 CREATE TABLE Status (
306 JobStatus CHAR(1) NOT NULL,
308 PRIMARY KEY (JobStatus)
311 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
312 ('C', 'Created, not yet running');
313 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
315 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
317 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
318 ('T', 'Completed successfully');
319 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
320 ('E', 'Terminated with errors');
321 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
322 ('e', 'Non-fatal error');
323 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
324 ('f', 'Fatal error');
325 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
326 ('D', 'Verify found differences');
327 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
328 ('A', 'Canceled by user');
329 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
330 ('F', 'Waiting for Client');
331 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
332 ('S', 'Waiting for Storage daemon');
333 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
334 ('m', 'Waiting for new media');
335 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
336 ('M', 'Waiting for media mount');
337 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
338 ('s', 'Waiting for storage resource');
339 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
340 ('j', 'Waiting for job resource');
341 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
342 ('c', 'Waiting for client resource');
343 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
344 ('d', 'Waiting on maximum jobs');
345 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
346 ('t', 'Waiting on start time');
347 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
348 ('p', 'Waiting on higher priority jobs');
351 -- Initialize Version
352 INSERT INTO Version (VersionId) VALUES (10);
355 PRAGMA default_synchronous = OFF;
356 PRAGMA default_cache_size = 10000;