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 DEFAULT 0,
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,
152 RecycleCount INTEGER UNSIGNED DEFAULT 0,
153 InitialWrite DATETIME DEFAULT 0,
154 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
155 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
159 CREATE INDEX inx8 ON Media (PoolId);
161 CREATE TABLE MediaType (
163 MediaType VARCHAR(128) NOT NULL,
164 ReadOnly TINYINT DEFAULT 0,
165 PRIMARY KEY(MediaTypeId)
168 CREATE TABLE Storage (
170 Name VARCHAR(128) NOT NULL,
171 AutoChanger TINYINT DEFAULT 0,
172 PRIMARY KEY(StorageId)
175 CREATE TABLE Device (
177 Name VARCHAR(128) NOT NULL,
178 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType NOT NULL,
179 StorageId INTEGER UNSIGNED REFERENCES Storage,
180 DevMounts INTEGER UNSIGNED DEFAULT 0,
181 DevReadBytes BIGINT UNSIGNED DEFAULT 0,
182 DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
183 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
184 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
185 DevReadTime BIGINT UNSIGNED DEFAULT 0,
186 DevWriteTime BIGINT UNSIGNED DEFAULT 0,
187 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
188 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
189 CleaningDate DATETIME DEFAULT 0,
190 CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
191 PRIMARY KEY(DeviceId)
197 Name VARCHAR(128) NOT NULL,
198 NumVols INTEGER UNSIGNED DEFAULT 0,
199 MaxVols INTEGER UNSIGNED DEFAULT 0,
200 UseOnce TINYINT DEFAULT 0,
201 UseCatalog TINYINT DEFAULT 1,
202 AcceptAnyVolume TINYINT DEFAULT 0,
203 VolRetention BIGINT UNSIGNED DEFAULT 0,
204 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
205 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
206 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
207 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
208 AutoPrune TINYINT DEFAULT 0,
209 Recycle TINYINT DEFAULT 0,
210 PoolType VARCHAR(20) NOT NULL,
211 LabelType TINYINT DEFAULT 0,
212 LabelFormat VARCHAR(128) NOT NULL,
213 Enabled TINYINT DEFAULT 1,
214 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
215 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
216 NextPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
217 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
218 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
219 MigrationTime BIGINT UNSIGNED DEFAULT 0,
225 CREATE TABLE Client (
227 Name VARCHAR(128) NOT NULL,
228 Uname VARCHAR(255) NOT NULL, -- uname -a field
229 AutoPrune TINYINT DEFAULT 0,
230 FileRetention BIGINT UNSIGNED DEFAULT 0,
231 JobRetention BIGINT UNSIGNED DEFAULT 0,
233 PRIMARY KEY(ClientId)
236 CREATE TABLE BaseFiles (
238 BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
239 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
240 FileId INTEGER UNSIGNED REFERENCES File NOT NULL,
241 FileIndex INTEGER UNSIGNED,
245 CREATE TABLE UnsavedFiles (
247 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
248 PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
249 FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
250 PRIMARY KEY (UnsavedId)
254 CREATE TABLE NextId (
255 id INTEGER UNSIGNED DEFAULT 0,
256 TableName TEXT NOT NULL,
257 PRIMARY KEY (TableName)
260 -- Initialize JobId to start at 1
261 INSERT INTO NextId (id, TableName) VALUES (1, "Job");
263 CREATE TABLE Version (
264 VersionId INTEGER UNSIGNED NOT NULL
268 CREATE TABLE Counters (
269 Counter TEXT NOT NULL,
270 MinValue INTEGER DEFAULT 0,
271 MaxValue INTEGER DEFAULT 0,
272 CurrentValue INTEGER DEFAULT 0,
273 WrapCounter TEXT NOT NULL,
274 PRIMARY KEY (Counter)
277 CREATE TABLE CDImages (
278 MediaId INTEGER UNSIGNED NOT NULL,
279 LastBurn DATETIME NOT NULL,
280 PRIMARY KEY (MediaId)
284 CREATE TABLE Status (
285 JobStatus CHAR(1) NOT NULL,
287 PRIMARY KEY (JobStatus)
290 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
291 ('C', 'Created, not yet running');
292 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
294 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
296 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
297 ('T', 'Completed successfully');
298 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
299 ('E', 'Terminated with errors');
300 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
301 ('e', 'Non-fatal error');
302 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
303 ('f', 'Fatal error');
304 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
305 ('D', 'Verify found differences');
306 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
307 ('A', 'Canceled by user');
308 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
309 ('F', 'Waiting for Client');
310 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
311 ('S', 'Waiting for Storage daemon');
312 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
313 ('m', 'Waiting for new media');
314 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
315 ('M', 'Waiting for media mount');
316 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
317 ('s', 'Waiting for storage resource');
318 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
319 ('j', 'Waiting for job resource');
320 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
321 ('c', 'Waiting for client resource');
322 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
323 ('d', 'Waiting on maximum jobs');
324 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
325 ('t', 'Waiting on start time');
326 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
327 ('p', 'Waiting on higher priority jobs');
330 -- Initialize Version
331 INSERT INTO Version (VersionId) VALUES (9);
334 PRAGMA default_synchronous = OFF;
335 PRAGMA default_cache_size = 10000;