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,
88 CREATE TABLE Location (
90 Location TINYBLOB NOT NULL,
91 PRIMARY KEY(LocationId)
94 CREATE TABLE FileSet (
96 FileSet VARCHAR(128) NOT NULL,
97 MD5 VARCHAR(25) NOT NULL,
98 CreateTime DATETIME DEFAULT 0,
99 PRIMARY KEY(FileSetId)
102 CREATE TABLE JobMedia (
104 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
105 MediaId INTEGER UNSIGNED REFERENCES Media NOT NULL,
106 FirstIndex INTEGER UNSIGNED NOT NULL,
107 LastIndex INTEGER UNSIGNED NOT NULL,
108 StartFile INTEGER UNSIGNED DEFAULT 0,
109 EndFile INTEGER UNSIGNED DEFAULT 0,
110 StartBlock INTEGER UNSIGNED DEFAULT 0,
111 EndBlock INTEGER UNSIGNED DEFAULT 0,
112 VolIndex INTEGER UNSIGNED DEFAULT 0,
113 Copy INTEGER UNSIGNED DEFAULT 0,
114 Stripe INTEGER UNSIGNED DEFAULT 0,
115 PRIMARY KEY(JobMediaId)
118 CREATE INDEX inx7 ON JobMedia (JobId, MediaId);
123 VolumeName VARCHAR(128) NOT NULL,
124 Slot INTEGER DEFAULT 0,
125 PoolId INTEGER UNSIGNED REFERENCES Pool NOT NULL,
126 MediaType VARCHAR(128) NOT NULL,
127 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType DEFAULT 0,
128 LabelType TINYINT DEFAULT 0,
129 FirstWritten DATETIME DEFAULT 0,
130 LastWritten DATETIME DEFAULT 0,
131 LabelDate DATETIME DEFAULT 0,
132 VolJobs INTEGER UNSIGNED DEFAULT 0,
133 VolFiles INTEGER UNSIGNED DEFAULT 0,
134 VolBlocks INTEGER UNSIGNED DEFAULT 0,
135 VolMounts INTEGER UNSIGNED DEFAULT 0,
136 VolBytes BIGINT UNSIGNED DEFAULT 0,
137 VolParts INTEGER UNSIGNED DEFAULT 0,
138 VolErrors INTEGER UNSIGNED DEFAULT 0,
139 VolWrites INTEGER UNSIGNED DEFAULT 0,
140 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
141 VolStatus VARCHAR(20) NOT NULL,
142 Recycle TINYINT DEFAULT 0,
143 VolRetention BIGINT UNSIGNED DEFAULT 0,
144 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
145 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
146 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
147 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
148 InChanger TINYINT DEFAULT 0,
149 StorageId INTEGER UNSIGNED REFERENCES Storage,
150 DeviceId INTEGER UNSIGNED REFERENCES Device,
151 MediaAddressing TINYINT DEFAULT 0,
152 VolReadTime BIGINT UNSIGNED DEFAULT 0,
153 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
154 EndFile INTEGER UNSIGNED DEFAULT 0,
155 EndBlock INTEGER UNSIGNED DEFAULT 0,
156 LocationId INTEGER UNSIGNED REFERENCES Location,
157 RecycleCount INTEGER UNSIGNED DEFAULT 0,
158 InitialWrite DATETIME DEFAULT 0,
159 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
160 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
164 CREATE INDEX inx8 ON Media (PoolId);
166 CREATE TABLE MediaType (
168 MediaType VARCHAR(128) NOT NULL,
169 ReadOnly TINYINT DEFAULT 0,
170 PRIMARY KEY(MediaTypeId)
173 CREATE TABLE Storage (
175 Name VARCHAR(128) NOT NULL,
176 AutoChanger TINYINT DEFAULT 0,
177 PRIMARY KEY(StorageId)
180 CREATE TABLE Device (
182 Name VARCHAR(128) NOT NULL,
183 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType NOT NULL,
184 StorageId INTEGER UNSIGNED REFERENCES Storage,
185 DevMounts INTEGER UNSIGNED DEFAULT 0,
186 DevReadBytes BIGINT UNSIGNED DEFAULT 0,
187 DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
188 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
189 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
190 DevReadTime BIGINT UNSIGNED DEFAULT 0,
191 DevWriteTime BIGINT UNSIGNED DEFAULT 0,
192 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
193 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
194 CleaningDate DATETIME DEFAULT 0,
195 CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
196 PRIMARY KEY(DeviceId)
202 Name VARCHAR(128) NOT NULL,
203 NumVols INTEGER UNSIGNED DEFAULT 0,
204 MaxVols INTEGER UNSIGNED DEFAULT 0,
205 UseOnce TINYINT DEFAULT 0,
206 UseCatalog TINYINT DEFAULT 1,
207 AcceptAnyVolume TINYINT DEFAULT 0,
208 VolRetention BIGINT UNSIGNED DEFAULT 0,
209 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
210 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
211 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
212 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
213 AutoPrune TINYINT DEFAULT 0,
214 Recycle TINYINT DEFAULT 0,
215 PoolType VARCHAR(20) NOT NULL,
216 LabelType TINYINT DEFAULT 0,
217 LabelFormat VARCHAR(128) NOT NULL,
218 Enabled TINYINT DEFAULT 1,
219 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
220 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
221 NextPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
222 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
223 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
224 MigrationTime BIGINT UNSIGNED DEFAULT 0,
230 CREATE TABLE Client (
232 Name VARCHAR(128) NOT NULL,
233 Uname VARCHAR(255) NOT NULL, -- uname -a field
234 AutoPrune TINYINT DEFAULT 0,
235 FileRetention BIGINT UNSIGNED DEFAULT 0,
236 JobRetention BIGINT UNSIGNED DEFAULT 0,
238 PRIMARY KEY(ClientId)
241 CREATE TABLE BaseFiles (
243 BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
244 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
245 FileId INTEGER UNSIGNED REFERENCES File NOT NULL,
246 FileIndex INTEGER UNSIGNED,
250 CREATE TABLE UnsavedFiles (
252 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
253 PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
254 FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
255 PRIMARY KEY (UnsavedId)
259 CREATE TABLE NextId (
260 id INTEGER UNSIGNED DEFAULT 0,
261 TableName TEXT NOT NULL,
262 PRIMARY KEY (TableName)
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 (9);
339 PRAGMA default_synchronous = OFF;
340 PRAGMA default_cache_size = 10000;