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 a table like Job for long term statistics
79 CREATE TABLE JobStat (LIKE Job);
81 CREATE TABLE Location (
83 Location TEXT NOT NULL,
84 Cost INTEGER DEFAULT 0,
86 PRIMARY KEY(LocationId)
89 CREATE TABLE LocationLog (
91 Date DATETIME NOT NULL,
92 Comment TEXT NOT NULL,
93 MediaId INTEGER UNSIGNED REFERENCES Media DEFAULT 0,
94 LocationId INTEGER UNSIGNED REFERENCES LocationId DEFAULT 0,
95 NewVolStatus VARCHAR(20) NOT NULL,
96 NewEnabled TINYINT NOT NULL,
103 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
104 Time DATETIME NOT NULL,
105 LogText TEXT NOT NULL,
108 CREATE INDEX LogInx1 ON Log (JobId);
111 CREATE TABLE FileSet (
113 FileSet VARCHAR(128) NOT NULL,
114 MD5 VARCHAR(25) NOT NULL,
115 CreateTime DATETIME DEFAULT 0,
116 PRIMARY KEY(FileSetId)
119 CREATE TABLE JobMedia (
121 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
122 MediaId INTEGER UNSIGNED REFERENCES Media NOT NULL,
123 FirstIndex INTEGER UNSIGNED NOT NULL,
124 LastIndex INTEGER UNSIGNED NOT NULL,
125 StartFile INTEGER UNSIGNED DEFAULT 0,
126 EndFile INTEGER UNSIGNED DEFAULT 0,
127 StartBlock INTEGER UNSIGNED DEFAULT 0,
128 EndBlock INTEGER UNSIGNED DEFAULT 0,
129 VolIndex INTEGER UNSIGNED DEFAULT 0,
130 Copy INTEGER UNSIGNED DEFAULT 0,
131 PRIMARY KEY(JobMediaId)
134 CREATE INDEX inx7 ON JobMedia (JobId, MediaId);
139 VolumeName VARCHAR(128) NOT NULL,
140 Slot INTEGER DEFAULT 0,
141 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
142 MediaType VARCHAR(128) NOT NULL,
143 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType DEFAULT 0,
144 LabelType TINYINT DEFAULT 0,
145 FirstWritten DATETIME DEFAULT 0,
146 LastWritten DATETIME DEFAULT 0,
147 LabelDate DATETIME DEFAULT 0,
148 VolJobs INTEGER UNSIGNED DEFAULT 0,
149 VolFiles INTEGER UNSIGNED DEFAULT 0,
150 VolBlocks INTEGER UNSIGNED DEFAULT 0,
151 VolMounts INTEGER UNSIGNED DEFAULT 0,
152 VolBytes BIGINT UNSIGNED DEFAULT 0,
153 VolParts INTEGER UNSIGNED DEFAULT 0,
154 VolErrors INTEGER UNSIGNED DEFAULT 0,
155 VolWrites INTEGER UNSIGNED DEFAULT 0,
156 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
157 VolStatus VARCHAR(20) NOT NULL,
158 Enabled TINYINT DEFAULT 1,
159 Recycle TINYINT DEFAULT 0,
160 VolRetention BIGINT UNSIGNED DEFAULT 0,
161 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
162 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
163 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
164 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
165 InChanger TINYINT DEFAULT 0,
166 StorageId INTEGER UNSIGNED REFERENCES Storage DEFAULT 0,
167 DeviceId INTEGER UNSIGNED REFERENCES Device DEFAULT 0,
168 MediaAddressing TINYINT DEFAULT 0,
169 VolReadTime BIGINT UNSIGNED DEFAULT 0,
170 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
171 EndFile INTEGER UNSIGNED DEFAULT 0,
172 EndBlock INTEGER UNSIGNED DEFAULT 0,
173 LocationId INTEGER UNSIGNED REFERENCES Location DEFAULT 0,
174 RecycleCount INTEGER UNSIGNED DEFAULT 0,
175 InitialWrite DATETIME DEFAULT 0,
176 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
177 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
182 CREATE INDEX inx8 ON Media (PoolId);
184 CREATE TABLE MediaType (
186 MediaType VARCHAR(128) NOT NULL,
187 ReadOnly TINYINT DEFAULT 0,
188 PRIMARY KEY(MediaTypeId)
191 CREATE TABLE Storage (
193 Name VARCHAR(128) NOT NULL,
194 AutoChanger TINYINT DEFAULT 0,
195 PRIMARY KEY(StorageId)
198 CREATE TABLE Device (
200 Name VARCHAR(128) NOT NULL,
201 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType NOT NULL,
202 StorageId INTEGER UNSIGNED REFERENCES Storage,
203 DevMounts INTEGER UNSIGNED DEFAULT 0,
204 DevReadBytes BIGINT UNSIGNED DEFAULT 0,
205 DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
206 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
207 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
208 DevReadTime BIGINT UNSIGNED DEFAULT 0,
209 DevWriteTime BIGINT UNSIGNED DEFAULT 0,
210 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
211 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
212 CleaningDate DATETIME DEFAULT 0,
213 CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
214 PRIMARY KEY(DeviceId)
220 Name VARCHAR(128) NOT NULL,
221 NumVols INTEGER UNSIGNED DEFAULT 0,
222 MaxVols INTEGER UNSIGNED DEFAULT 0,
223 UseOnce TINYINT DEFAULT 0,
224 UseCatalog TINYINT DEFAULT 1,
225 AcceptAnyVolume TINYINT DEFAULT 0,
226 VolRetention BIGINT UNSIGNED DEFAULT 0,
227 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
228 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
229 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
230 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
231 AutoPrune TINYINT DEFAULT 0,
232 Recycle TINYINT DEFAULT 0,
233 PoolType VARCHAR(20) NOT NULL,
234 LabelType TINYINT DEFAULT 0,
235 LabelFormat VARCHAR(128) NOT NULL,
236 Enabled TINYINT DEFAULT 1,
237 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
238 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
239 NextPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
240 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
241 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
242 MigrationTime BIGINT UNSIGNED DEFAULT 0,
248 CREATE TABLE Client (
250 Name VARCHAR(128) NOT NULL,
251 Uname VARCHAR(255) NOT NULL, -- uname -a field
252 AutoPrune TINYINT DEFAULT 0,
253 FileRetention BIGINT UNSIGNED DEFAULT 0,
254 JobRetention BIGINT UNSIGNED DEFAULT 0,
256 PRIMARY KEY(ClientId)
259 CREATE TABLE BaseFiles (
261 BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
262 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
263 FileId INTEGER UNSIGNED REFERENCES File NOT NULL,
264 FileIndex INTEGER UNSIGNED,
268 CREATE TABLE UnsavedFiles (
270 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
271 PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
272 FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
273 PRIMARY KEY (UnsavedId)
277 CREATE TABLE NextId (
278 id INTEGER UNSIGNED DEFAULT 0,
279 TableName TEXT NOT NULL,
280 PRIMARY KEY (TableName)
285 -- Initialize JobId to start at 1
286 INSERT INTO NextId (id, TableName) VALUES (1, "Job");
288 CREATE TABLE Version (
289 VersionId INTEGER UNSIGNED NOT NULL
293 CREATE TABLE Counters (
294 Counter TEXT NOT NULL,
295 MinValue INTEGER DEFAULT 0,
296 MaxValue INTEGER DEFAULT 0,
297 CurrentValue INTEGER DEFAULT 0,
298 WrapCounter TEXT NOT NULL,
299 PRIMARY KEY (Counter)
302 CREATE TABLE CDImages (
303 MediaId INTEGER UNSIGNED NOT NULL,
304 LastBurn DATETIME NOT NULL,
305 PRIMARY KEY (MediaId)
309 CREATE TABLE Status (
310 JobStatus CHAR(1) NOT NULL,
312 PRIMARY KEY (JobStatus)
315 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
316 ('C', 'Created, not yet running');
317 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
319 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
321 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
322 ('T', 'Completed successfully');
323 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
324 ('E', 'Terminated with errors');
325 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
326 ('e', 'Non-fatal error');
327 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
328 ('f', 'Fatal error');
329 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
330 ('D', 'Verify found differences');
331 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
332 ('A', 'Canceled by user');
333 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
334 ('F', 'Waiting for Client');
335 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
336 ('S', 'Waiting for Storage daemon');
337 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
338 ('m', 'Waiting for new media');
339 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
340 ('M', 'Waiting for media mount');
341 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
342 ('s', 'Waiting for storage resource');
343 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
344 ('j', 'Waiting for job resource');
345 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
346 ('c', 'Waiting for client resource');
347 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
348 ('d', 'Waiting on maximum jobs');
349 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
350 ('t', 'Waiting on start time');
351 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
352 ('p', 'Waiting on higher priority jobs');
353 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
354 ('a', 'SD despooling attributes');
355 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
356 ('i', 'Doing batch insert file records');
359 -- Initialize Version
360 INSERT INTO Version (VersionId) VALUES (10);
363 PRAGMA default_synchronous = OFF;
364 PRAGMA default_cache_size = 10000;
368 chmod 640 ${db_name}.db