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 JobTDate BIGINT UNSIGNED DEFAULT 0,
61 VolSessionId INTEGER UNSIGNED DEFAULT 0,
62 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
63 JobFiles INTEGER UNSIGNED DEFAULT 0,
64 JobBytes BIGINT UNSIGNED DEFAULT 0,
65 JobErrors INTEGER UNSIGNED DEFAULT 0,
66 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
67 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
68 FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0,
69 PurgedFiles TINYINT DEFAULT 0,
70 HasBase TINYINT DEFAULT 0,
74 CREATE INDEX inx6 ON Job (Name);
78 OriginalJobId INTEGER,
79 JobType CHAR NOT NULL,
80 JobLevel CHAR NOT NULL,
81 SchedTime DATETIME NOT NULL,
82 StartTime DATETIME DEFAULT 0,
83 EndTime DATETIME DEFAULT 0,
84 JobTDate BIGINT UNSIGNED DEFAULT 0,
89 CREATE TABLE FileSet (
91 FileSet VARCHAR(128) NOT NULL,
92 MD5 VARCHAR(25) NOT NULL,
93 CreateTime DATETIME DEFAULT 0,
94 PRIMARY KEY(FileSetId)
97 CREATE TABLE JobMedia (
99 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
100 MediaId INTEGER UNSIGNED REFERENCES Media NOT NULL,
101 FirstIndex INTEGER UNSIGNED NOT NULL,
102 LastIndex INTEGER UNSIGNED NOT NULL,
103 StartFile INTEGER UNSIGNED DEFAULT 0,
104 EndFile INTEGER UNSIGNED DEFAULT 0,
105 StartBlock INTEGER UNSIGNED DEFAULT 0,
106 EndBlock INTEGER UNSIGNED DEFAULT 0,
107 VolIndex INTEGER UNSIGNED DEFAULT 0,
108 Copy INTEGER UNSIGNED DEFAULT 0,
109 Stripe INTEGER UNSIGNED DEFAULT 0,
110 PRIMARY KEY(JobMediaId)
113 CREATE INDEX inx7 ON JobMedia (JobId, MediaId);
118 VolumeName VARCHAR(128) NOT NULL,
119 Slot INTEGER DEFAULT 0,
120 PoolId INTEGER UNSIGNED REFERENCES Pool NOT NULL,
121 MediaType VARCHAR(128) NOT NULL,
122 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType NOT NULL,
123 LabelType TINYINT DEFAULT 0,
124 FirstWritten DATETIME DEFAULT 0,
125 LastWritten DATETIME DEFAULT 0,
126 LabelDate DATETIME DEFAULT 0,
127 VolJobs INTEGER UNSIGNED DEFAULT 0,
128 VolFiles INTEGER UNSIGNED DEFAULT 0,
129 VolBlocks INTEGER UNSIGNED DEFAULT 0,
130 VolMounts INTEGER UNSIGNED DEFAULT 0,
131 VolBytes BIGINT UNSIGNED DEFAULT 0,
132 VolParts INTEGER UNSIGNED DEFAULT 0,
133 VolErrors INTEGER UNSIGNED DEFAULT 0,
134 VolWrites INTEGER UNSIGNED DEFAULT 0,
135 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
136 VolStatus VARCHAR(20) NOT NULL,
137 Recycle TINYINT DEFAULT 0,
138 VolRetention BIGINT UNSIGNED DEFAULT 0,
139 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
140 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
141 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
142 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
143 InChanger TINYINT DEFAULT 0,
144 StorageId INTEGER UNSIGNED REFERENCES Storage,
145 DeviceId INTEGER UNSIGNED REFERENCES Device,
146 MediaAddressing TINYINT DEFAULT 0,
147 VolReadTime BIGINT UNSIGNED DEFAULT 0,
148 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
149 EndFile INTEGER UNSIGNED DEFAULT 0,
150 EndBlock INTEGER UNSIGNED DEFAULT 0,
151 LocationId INTEGER UNSIGNED REFERENCES Location,
155 CREATE INDEX inx8 ON Media (PoolId);
157 CREATE TABLE MediaType (
159 MediaType VARCHAR(128) NOT NULL,
160 ReadOnly TINYINT DEFAULT 0,
161 PRIMARY KEY(MediaTypeId)
164 CREATE TABLE Storage (
166 Name VARCHAR(128) NOT NULL,
167 AutoChanger TINYINT DEFAULT 0,
168 PRIMARY KEY(StorageId)
171 CREATE TABLE Device (
173 Name VARCHAR(128) NOT NULL,
174 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType NOT NULL,
175 StorageId INTEGER UNSIGNED REFERENCES Storage,
176 DevMounts INTEGER UNSIGNED DEFAULT 0,
177 DevReadBytes BIGINT UNSIGNED DEFAULT 0,
178 DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
179 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
180 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
181 DevReadTime BIGINT UNSIGNED DEFAULT 0,
182 DevWriteTime BIGINT UNSIGNED DEFAULT 0,
183 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
184 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
185 CleaningDate DATETIME DEFAULT 0,
186 CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
187 PRIMARY KEY(DeviceId)
193 Name VARCHAR(128) NOT NULL,
194 NumVols INTEGER UNSIGNED DEFAULT 0,
195 MaxVols INTEGER UNSIGNED DEFAULT 0,
196 UseOnce TINYINT DEFAULT 0,
197 UseCatalog TINYINT DEFAULT 1,
198 AcceptAnyVolume TINYINT DEFAULT 0,
199 VolRetention BIGINT UNSIGNED DEFAULT 0,
200 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
201 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
202 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
203 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
204 AutoPrune TINYINT DEFAULT 0,
205 Recycle TINYINT DEFAULT 0,
206 PoolType VARCHAR(20) NOT NULL,
207 LabelType TINYINT DEFAULT 0,
208 LabelFormat VARCHAR(128) NOT NULL,
209 Enabled TINYINT DEFAULT 1,
210 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
211 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
212 NextPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
213 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
214 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
215 MigrationTime BIGINT UNSIGNED DEFAULT 0,
221 CREATE TABLE Client (
223 Name VARCHAR(128) NOT NULL,
224 Uname VARCHAR(255) NOT NULL, -- uname -a field
225 AutoPrune TINYINT DEFAULT 0,
226 FileRetention BIGINT UNSIGNED DEFAULT 0,
227 JobRetention BIGINT UNSIGNED DEFAULT 0,
229 PRIMARY KEY(ClientId)
232 CREATE TABLE BaseFiles (
234 BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
235 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
236 FileId INTEGER UNSIGNED REFERENCES File NOT NULL,
237 FileIndex INTEGER UNSIGNED,
241 CREATE TABLE UnsavedFiles (
243 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
244 PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
245 FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
246 PRIMARY KEY (UnsavedId)
250 CREATE TABLE NextId (
251 id INTEGER UNSIGNED DEFAULT 0,
252 TableName TEXT NOT NULL,
253 PRIMARY KEY (TableName)
256 -- Initialize JobId to start at 1
257 INSERT INTO NextId (id, TableName) VALUES (1, "Job");
259 CREATE TABLE Version (
260 VersionId INTEGER UNSIGNED NOT NULL
264 CREATE TABLE Counters (
265 Counter TEXT NOT NULL,
266 MinValue INTEGER DEFAULT 0,
267 MaxValue INTEGER DEFAULT 0,
268 CurrentValue INTEGER DEFAULT 0,
269 WrapCounter TEXT NOT NULL,
270 PRIMARY KEY (Counter)
273 CREATE TABLE CDImages (
274 MediaId INTEGER UNSIGNED NOT NULL,
275 LastBurn DATETIME NOT NULL,
276 PRIMARY KEY (MediaId)
280 CREATE TABLE Status (
281 JobStatus CHAR(1) NOT NULL,
283 PRIMARY KEY (JobStatus)
286 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
287 ('C', 'Created, not yet running');
288 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
290 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
292 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
293 ('T', 'Completed successfully');
294 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
295 ('E', 'Terminated with errors');
296 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
297 ('e', 'Non-fatal error');
298 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
299 ('f', 'Fatal error');
300 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
301 ('D', 'Verify found differences');
302 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
303 ('A', 'Canceled by user');
304 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
305 ('F', 'Waiting for Client');
306 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
307 ('S', 'Waiting for Storage daemon');
308 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
309 ('m', 'Waiting for new media');
310 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
311 ('M', 'Waiting for media mount');
312 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
313 ('s', 'Waiting for storage resource');
314 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
315 ('j', 'Waiting for job resource');
316 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
317 ('c', 'Waiting for client resource');
318 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
319 ('d', 'Waiting on maximum jobs');
320 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
321 ('t', 'Waiting on start time');
322 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
323 ('p', 'Waiting on higher priority jobs');
326 -- Initialize Version
327 INSERT INTO Version (VersionId) VALUES (9);
330 PRAGMA default_synchronous = OFF;
331 PRAGMA default_cache_size = 10000;