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