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);
30 FileIndex INTEGER UNSIGNED NOT NULL,
31 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
32 PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
33 FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
34 MarkId INTEGER UNSIGNED DEFAULT 0,
35 LStat VARCHAR(255) NOT NULL,
36 MD5 VARCHAR(255) NOT NULL,
40 CREATE INDEX inx3 ON File (JobId);
41 CREATE INDEX inx4 ON File (FilenameId, PathId);
43 -- Possibly add one or more of the following indexes
44 -- if your Verifies are too slow.
46 -- CREATE INDEX inx4 ON File (PathId);
47 -- CREATE INDEX inx5 ON File (FileNameId);
48 -- CREATE INDEX inx9 ON File (JobId, PathId, FilenameId);
52 Job VARCHAR(128) NOT NULL,
53 Name VARCHAR(128) NOT NULL,
56 ClientId INTEGER REFERENCES Client DEFAULT 0,
57 JobStatus CHAR NOT NULL,
58 SchedTime DATETIME NOT NULL,
59 StartTime DATETIME DEFAULT 0,
60 EndTime DATETIME DEFAULT 0,
61 RealEndTime DATETIME DEFAULT 0,
62 JobTDate BIGINT UNSIGNED DEFAULT 0,
63 VolSessionId INTEGER UNSIGNED DEFAULT 0,
64 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
65 JobFiles INTEGER UNSIGNED DEFAULT 0,
66 JobBytes BIGINT UNSIGNED DEFAULT 0,
67 JobErrors INTEGER UNSIGNED DEFAULT 0,
68 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
69 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
70 FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0,
71 PriorJobId INTEGER UNSIGNED REFERENCES Job DEFAULT 0,
72 PurgedFiles TINYINT DEFAULT 0,
73 HasBase TINYINT DEFAULT 0,
76 CREATE INDEX inx6 ON Job (Name);
78 CREATE TABLE Location (
80 Location TEXT NOT NULL,
81 Cost INTEGER DEFAULT 0,
83 PRIMARY KEY(LocationId)
86 CREATE TABLE LocationLog (
88 Date DATETIME NOT NULL,
89 Comment TEXT NOT NULL,
90 MediaId INTEGER UNSIGNED REFERENCES Media DEFAULT 0,
91 LocationId INTEGER UNSIGNED REFERENCES LocationId DEFAULT 0,
92 NewVolStatus VARCHAR(20) NOT NULL,
93 NewEnabled TINYINT NOT NULL,
100 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
101 Time DATETIME NOT NULL,
102 LogText TEXT NOT NULL,
105 CREATE INDEX LogInx1 ON Log (JobId);
108 CREATE TABLE FileSet (
110 FileSet VARCHAR(128) NOT NULL,
111 MD5 VARCHAR(25) NOT NULL,
112 CreateTime DATETIME DEFAULT 0,
113 PRIMARY KEY(FileSetId)
116 CREATE TABLE JobMedia (
118 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
119 MediaId INTEGER UNSIGNED REFERENCES Media NOT NULL,
120 FirstIndex INTEGER UNSIGNED NOT NULL,
121 LastIndex INTEGER UNSIGNED NOT NULL,
122 StartFile INTEGER UNSIGNED DEFAULT 0,
123 EndFile INTEGER UNSIGNED DEFAULT 0,
124 StartBlock INTEGER UNSIGNED DEFAULT 0,
125 EndBlock INTEGER UNSIGNED DEFAULT 0,
126 VolIndex INTEGER UNSIGNED DEFAULT 0,
127 Copy INTEGER UNSIGNED DEFAULT 0,
128 PRIMARY KEY(JobMediaId)
131 CREATE INDEX inx7 ON JobMedia (JobId, MediaId);
136 VolumeName VARCHAR(128) NOT NULL,
137 Slot INTEGER DEFAULT 0,
138 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
139 MediaType VARCHAR(128) NOT NULL,
140 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType DEFAULT 0,
141 LabelType TINYINT DEFAULT 0,
142 FirstWritten DATETIME DEFAULT 0,
143 LastWritten DATETIME DEFAULT 0,
144 LabelDate DATETIME DEFAULT 0,
145 VolJobs INTEGER UNSIGNED DEFAULT 0,
146 VolFiles INTEGER UNSIGNED DEFAULT 0,
147 VolBlocks INTEGER UNSIGNED DEFAULT 0,
148 VolMounts INTEGER UNSIGNED DEFAULT 0,
149 VolBytes BIGINT UNSIGNED DEFAULT 0,
150 VolParts INTEGER UNSIGNED DEFAULT 0,
151 VolErrors INTEGER UNSIGNED DEFAULT 0,
152 VolWrites INTEGER UNSIGNED DEFAULT 0,
153 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
154 VolStatus VARCHAR(20) NOT NULL,
155 Enabled TINYINT DEFAULT 1,
156 Recycle TINYINT DEFAULT 0,
157 VolRetention BIGINT UNSIGNED DEFAULT 0,
158 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
159 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
160 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
161 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
162 InChanger TINYINT DEFAULT 0,
163 StorageId INTEGER UNSIGNED REFERENCES Storage DEFAULT 0,
164 DeviceId INTEGER UNSIGNED REFERENCES Device DEFAULT 0,
165 MediaAddressing TINYINT DEFAULT 0,
166 VolReadTime BIGINT UNSIGNED DEFAULT 0,
167 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
168 EndFile INTEGER UNSIGNED DEFAULT 0,
169 EndBlock INTEGER UNSIGNED DEFAULT 0,
170 LocationId INTEGER UNSIGNED REFERENCES Location DEFAULT 0,
171 RecycleCount INTEGER UNSIGNED DEFAULT 0,
172 InitialWrite DATETIME DEFAULT 0,
173 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
174 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
179 CREATE INDEX inx8 ON Media (PoolId);
181 CREATE TABLE MediaType (
183 MediaType VARCHAR(128) NOT NULL,
184 ReadOnly TINYINT DEFAULT 0,
185 PRIMARY KEY(MediaTypeId)
188 CREATE TABLE Storage (
190 Name VARCHAR(128) NOT NULL,
191 AutoChanger TINYINT DEFAULT 0,
192 PRIMARY KEY(StorageId)
195 CREATE TABLE Device (
197 Name VARCHAR(128) NOT NULL,
198 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType NOT NULL,
199 StorageId INTEGER UNSIGNED REFERENCES Storage,
200 DevMounts INTEGER UNSIGNED DEFAULT 0,
201 DevReadBytes BIGINT UNSIGNED DEFAULT 0,
202 DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
203 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
204 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
205 DevReadTime BIGINT UNSIGNED DEFAULT 0,
206 DevWriteTime BIGINT UNSIGNED DEFAULT 0,
207 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
208 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
209 CleaningDate DATETIME DEFAULT 0,
210 CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
211 PRIMARY KEY(DeviceId)
217 Name VARCHAR(128) NOT NULL,
218 NumVols INTEGER UNSIGNED DEFAULT 0,
219 MaxVols INTEGER UNSIGNED DEFAULT 0,
220 UseOnce TINYINT DEFAULT 0,
221 UseCatalog TINYINT DEFAULT 1,
222 AcceptAnyVolume TINYINT DEFAULT 0,
223 VolRetention BIGINT UNSIGNED DEFAULT 0,
224 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
225 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
226 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
227 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
228 AutoPrune TINYINT DEFAULT 0,
229 Recycle TINYINT DEFAULT 0,
230 PoolType VARCHAR(20) NOT NULL,
231 LabelType TINYINT DEFAULT 0,
232 LabelFormat VARCHAR(128) NOT NULL,
233 Enabled TINYINT DEFAULT 1,
234 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
235 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
236 NextPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
237 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
238 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
239 MigrationTime BIGINT UNSIGNED DEFAULT 0,
245 CREATE TABLE Client (
247 Name VARCHAR(128) NOT NULL,
248 Uname VARCHAR(255) NOT NULL, -- uname -a field
249 AutoPrune TINYINT DEFAULT 0,
250 FileRetention BIGINT UNSIGNED DEFAULT 0,
251 JobRetention BIGINT UNSIGNED DEFAULT 0,
253 PRIMARY KEY(ClientId)
256 CREATE TABLE BaseFiles (
258 BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
259 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
260 FileId INTEGER UNSIGNED REFERENCES File NOT NULL,
261 FileIndex INTEGER UNSIGNED,
265 CREATE TABLE UnsavedFiles (
267 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
268 PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
269 FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
270 PRIMARY KEY (UnsavedId)
274 CREATE TABLE NextId (
275 id INTEGER UNSIGNED DEFAULT 0,
276 TableName TEXT NOT NULL,
277 PRIMARY KEY (TableName)
282 -- Initialize JobId to start at 1
283 INSERT INTO NextId (id, TableName) VALUES (1, "Job");
285 CREATE TABLE Version (
286 VersionId INTEGER UNSIGNED NOT NULL
290 CREATE TABLE Counters (
291 Counter TEXT NOT NULL,
292 MinValue INTEGER DEFAULT 0,
293 MaxValue INTEGER DEFAULT 0,
294 CurrentValue INTEGER DEFAULT 0,
295 WrapCounter TEXT NOT NULL,
296 PRIMARY KEY (Counter)
299 CREATE TABLE CDImages (
300 MediaId INTEGER UNSIGNED NOT NULL,
301 LastBurn DATETIME NOT NULL,
302 PRIMARY KEY (MediaId)
306 CREATE TABLE Status (
307 JobStatus CHAR(1) NOT NULL,
309 PRIMARY KEY (JobStatus)
312 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
313 ('C', 'Created, not yet running');
314 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
316 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
318 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
319 ('T', 'Completed successfully');
320 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
321 ('E', 'Terminated with errors');
322 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
323 ('e', 'Non-fatal error');
324 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
325 ('f', 'Fatal error');
326 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
327 ('D', 'Verify found differences');
328 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
329 ('A', 'Canceled by user');
330 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
331 ('F', 'Waiting for Client');
332 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
333 ('S', 'Waiting for Storage daemon');
334 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
335 ('m', 'Waiting for new media');
336 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
337 ('M', 'Waiting for media mount');
338 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
339 ('s', 'Waiting for storage resource');
340 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
341 ('j', 'Waiting for job resource');
342 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
343 ('c', 'Waiting for client resource');
344 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
345 ('d', 'Waiting on maximum jobs');
346 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
347 ('t', 'Waiting on start time');
348 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
349 ('p', 'Waiting on higher priority jobs');
352 -- Initialize Version
353 INSERT INTO Version (VersionId) VALUES (10);
356 PRAGMA default_synchronous = OFF;
357 PRAGMA default_cache_size = 10000;
361 chmod 640 ${db_name}.db