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 NOT NULL,
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)
257 -- Initialize JobId to start at 1
258 INSERT INTO NextId (id, TableName) VALUES (1, "Job");
260 CREATE TABLE Version (
261 VersionId INTEGER UNSIGNED NOT NULL
265 CREATE TABLE Counters (
266 Counter TEXT NOT NULL,
267 MinValue INTEGER DEFAULT 0,
268 MaxValue INTEGER DEFAULT 0,
269 CurrentValue INTEGER DEFAULT 0,
270 WrapCounter TEXT NOT NULL,
271 PRIMARY KEY (Counter)
274 CREATE TABLE CDImages (
275 MediaId INTEGER UNSIGNED NOT NULL,
276 LastBurn DATETIME NOT NULL,
277 PRIMARY KEY (MediaId)
281 CREATE TABLE Status (
282 JobStatus CHAR(1) NOT NULL,
284 PRIMARY KEY (JobStatus)
287 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
288 ('C', 'Created, not yet running');
289 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
291 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
293 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
294 ('T', 'Completed successfully');
295 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
296 ('E', 'Terminated with errors');
297 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
298 ('e', 'Non-fatal error');
299 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
300 ('f', 'Fatal error');
301 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
302 ('D', 'Verify found differences');
303 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
304 ('A', 'Canceled by user');
305 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
306 ('F', 'Waiting for Client');
307 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
308 ('S', 'Waiting for Storage daemon');
309 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
310 ('m', 'Waiting for new media');
311 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
312 ('M', 'Waiting for media mount');
313 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
314 ('s', 'Waiting for storage resource');
315 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
316 ('j', 'Waiting for job resource');
317 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
318 ('c', 'Waiting for client resource');
319 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
320 ('d', 'Waiting on maximum jobs');
321 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
322 ('t', 'Waiting on start time');
323 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
324 ('p', 'Waiting on higher priority jobs');
327 -- Initialize Version
328 INSERT INTO Version (VersionId) VALUES (10);
331 PRAGMA default_synchronous = OFF;
332 PRAGMA default_cache_size = 10000;