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,
76 CREATE INDEX inx6 ON Job (Name);
78 CREATE TABLE Location (
80 Location TINYBLOB NOT NULL,
81 Cost INTEGER DEFAULT 0,
83 PRIMARY KEY(LocationId)
87 CREATE TABLE FileSet (
89 FileSet VARCHAR(128) NOT NULL,
90 MD5 VARCHAR(25) NOT NULL,
91 CreateTime DATETIME DEFAULT 0,
92 PRIMARY KEY(FileSetId)
95 CREATE TABLE JobMedia (
97 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
98 MediaId INTEGER UNSIGNED REFERENCES Media NOT NULL,
99 FirstIndex INTEGER UNSIGNED NOT NULL,
100 LastIndex INTEGER UNSIGNED NOT NULL,
101 StartFile INTEGER UNSIGNED DEFAULT 0,
102 EndFile INTEGER UNSIGNED DEFAULT 0,
103 StartBlock INTEGER UNSIGNED DEFAULT 0,
104 EndBlock INTEGER UNSIGNED DEFAULT 0,
105 VolIndex INTEGER UNSIGNED DEFAULT 0,
106 Copy INTEGER UNSIGNED DEFAULT 0,
107 PRIMARY KEY(JobMediaId)
110 CREATE INDEX inx7 ON JobMedia (JobId, MediaId);
115 VolumeName VARCHAR(128) NOT NULL,
116 Slot INTEGER DEFAULT 0,
117 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
118 MediaType VARCHAR(128) NOT NULL,
119 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType DEFAULT 0,
120 LabelType TINYINT DEFAULT 0,
121 FirstWritten DATETIME DEFAULT 0,
122 LastWritten DATETIME DEFAULT 0,
123 LabelDate DATETIME DEFAULT 0,
124 VolJobs INTEGER UNSIGNED DEFAULT 0,
125 VolFiles INTEGER UNSIGNED DEFAULT 0,
126 VolBlocks INTEGER UNSIGNED DEFAULT 0,
127 VolMounts INTEGER UNSIGNED DEFAULT 0,
128 VolBytes BIGINT UNSIGNED DEFAULT 0,
129 VolParts INTEGER UNSIGNED DEFAULT 0,
130 VolErrors INTEGER UNSIGNED DEFAULT 0,
131 VolWrites INTEGER UNSIGNED DEFAULT 0,
132 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
133 VolStatus VARCHAR(20) NOT NULL,
134 Enabled TINYINT DEFAULT 1,
135 Recycle TINYINT DEFAULT 0,
136 VolRetention BIGINT UNSIGNED DEFAULT 0,
137 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
138 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
139 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
140 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
141 InChanger TINYINT DEFAULT 0,
142 StorageId INTEGER UNSIGNED REFERENCES Storage DEFAULT 0,
143 DeviceId INTEGER UNSIGNED REFERENCES Device DEFAULT 0,
144 MediaAddressing TINYINT DEFAULT 0,
145 VolReadTime BIGINT UNSIGNED DEFAULT 0,
146 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
147 EndFile INTEGER UNSIGNED DEFAULT 0,
148 EndBlock INTEGER UNSIGNED DEFAULT 0,
149 LocationId INTEGER UNSIGNED REFERENCES Location DEFAULT 0,
150 RecycleCount INTEGER UNSIGNED DEFAULT 0,
151 InitialWrite DATETIME DEFAULT 0,
152 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
153 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
157 CREATE INDEX inx8 ON Media (PoolId);
159 CREATE TABLE MediaType (
161 MediaType VARCHAR(128) NOT NULL,
162 ReadOnly TINYINT DEFAULT 0,
163 PRIMARY KEY(MediaTypeId)
166 CREATE TABLE Storage (
168 Name VARCHAR(128) NOT NULL,
169 AutoChanger TINYINT DEFAULT 0,
170 PRIMARY KEY(StorageId)
173 CREATE TABLE Device (
175 Name VARCHAR(128) NOT NULL,
176 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType NOT NULL,
177 StorageId INTEGER UNSIGNED REFERENCES Storage,
178 DevMounts INTEGER UNSIGNED DEFAULT 0,
179 DevReadBytes BIGINT UNSIGNED DEFAULT 0,
180 DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
181 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
182 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
183 DevReadTime BIGINT UNSIGNED DEFAULT 0,
184 DevWriteTime BIGINT UNSIGNED DEFAULT 0,
185 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
186 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
187 CleaningDate DATETIME DEFAULT 0,
188 CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
189 PRIMARY KEY(DeviceId)
195 Name VARCHAR(128) NOT NULL,
196 NumVols INTEGER UNSIGNED DEFAULT 0,
197 MaxVols INTEGER UNSIGNED DEFAULT 0,
198 UseOnce TINYINT DEFAULT 0,
199 UseCatalog TINYINT DEFAULT 1,
200 AcceptAnyVolume TINYINT DEFAULT 0,
201 VolRetention BIGINT UNSIGNED DEFAULT 0,
202 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
203 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
204 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
205 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
206 AutoPrune TINYINT DEFAULT 0,
207 Recycle TINYINT DEFAULT 0,
208 PoolType VARCHAR(20) NOT NULL,
209 LabelType TINYINT DEFAULT 0,
210 LabelFormat VARCHAR(128) NOT NULL,
211 Enabled TINYINT DEFAULT 1,
212 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
213 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
214 NextPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
215 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
216 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
217 MigrationTime BIGINT UNSIGNED DEFAULT 0,
223 CREATE TABLE Client (
225 Name VARCHAR(128) NOT NULL,
226 Uname VARCHAR(255) NOT NULL, -- uname -a field
227 AutoPrune TINYINT DEFAULT 0,
228 FileRetention BIGINT UNSIGNED DEFAULT 0,
229 JobRetention BIGINT UNSIGNED DEFAULT 0,
231 PRIMARY KEY(ClientId)
234 CREATE TABLE BaseFiles (
236 BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
237 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
238 FileId INTEGER UNSIGNED REFERENCES File NOT NULL,
239 FileIndex INTEGER UNSIGNED,
243 CREATE TABLE UnsavedFiles (
245 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
246 PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
247 FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
248 PRIMARY KEY (UnsavedId)
252 CREATE TABLE NextId (
253 id INTEGER UNSIGNED DEFAULT 0,
254 TableName TEXT NOT NULL,
255 PRIMARY KEY (TableName)
259 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
260 LogText TEXT NOT NULL,
265 -- Initialize JobId to start at 1
266 INSERT INTO NextId (id, TableName) VALUES (1, "Job");
268 CREATE TABLE Version (
269 VersionId INTEGER UNSIGNED NOT NULL
273 CREATE TABLE Counters (
274 Counter TEXT NOT NULL,
275 MinValue INTEGER DEFAULT 0,
276 MaxValue INTEGER DEFAULT 0,
277 CurrentValue INTEGER DEFAULT 0,
278 WrapCounter TEXT NOT NULL,
279 PRIMARY KEY (Counter)
282 CREATE TABLE CDImages (
283 MediaId INTEGER UNSIGNED NOT NULL,
284 LastBurn DATETIME NOT NULL,
285 PRIMARY KEY (MediaId)
289 CREATE TABLE Status (
290 JobStatus CHAR(1) NOT NULL,
292 PRIMARY KEY (JobStatus)
295 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
296 ('C', 'Created, not yet running');
297 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
299 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
301 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
302 ('T', 'Completed successfully');
303 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
304 ('E', 'Terminated with errors');
305 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
306 ('e', 'Non-fatal error');
307 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
308 ('f', 'Fatal error');
309 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
310 ('D', 'Verify found differences');
311 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
312 ('A', 'Canceled by user');
313 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
314 ('F', 'Waiting for Client');
315 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
316 ('S', 'Waiting for Storage daemon');
317 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
318 ('m', 'Waiting for new media');
319 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
320 ('M', 'Waiting for media mount');
321 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
322 ('s', 'Waiting for storage resource');
323 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
324 ('j', 'Waiting for job resource');
325 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
326 ('c', 'Waiting for client resource');
327 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
328 ('d', 'Waiting on maximum jobs');
329 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
330 ('t', 'Waiting on start time');
331 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
332 ('p', 'Waiting on higher priority jobs');
335 -- Initialize Version
336 INSERT INTO Version (VersionId) VALUES (10);
339 PRAGMA default_synchronous = OFF;
340 PRAGMA default_cache_size = 10000;