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);
76 CREATE TABLE FileSet (
78 FileSet VARCHAR(128) NOT NULL,
79 MD5 VARCHAR(25) NOT NULL,
80 CreateTime DATETIME DEFAULT 0,
81 PRIMARY KEY(FileSetId)
84 CREATE TABLE JobMedia (
86 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
87 MediaId INTEGER UNSIGNED REFERENCES Media NOT NULL,
88 FirstIndex INTEGER UNSIGNED NOT NULL,
89 LastIndex INTEGER UNSIGNED NOT NULL,
90 StartFile INTEGER UNSIGNED DEFAULT 0,
91 EndFile INTEGER UNSIGNED DEFAULT 0,
92 StartBlock INTEGER UNSIGNED DEFAULT 0,
93 EndBlock INTEGER UNSIGNED DEFAULT 0,
94 VolIndex INTEGER UNSIGNED DEFAULT 0,
95 Copy INTEGER UNSIGNED DEFAULT 0,
96 Stripe INTEGER UNSIGNED DEFAULT 0,
97 PRIMARY KEY(JobMediaId)
100 CREATE INDEX inx7 ON JobMedia (JobId, MediaId);
105 VolumeName VARCHAR(128) NOT NULL,
106 Slot INTEGER DEFAULT 0,
107 PoolId INTEGER UNSIGNED REFERENCES Pool NOT NULL,
108 MediaType VARCHAR(128) NOT NULL,
109 LabelType TINYINT DEFAULT 0,
110 FirstWritten DATETIME DEFAULT 0,
111 LastWritten DATETIME DEFAULT 0,
112 LabelDate DATETIME DEFAULT 0,
113 VolJobs INTEGER UNSIGNED DEFAULT 0,
114 VolFiles INTEGER UNSIGNED DEFAULT 0,
115 VolBlocks INTEGER UNSIGNED DEFAULT 0,
116 VolMounts INTEGER UNSIGNED DEFAULT 0,
117 VolBytes BIGINT UNSIGNED DEFAULT 0,
118 VolParts INTEGER UNSIGNED DEFAULT 0,
119 VolErrors INTEGER UNSIGNED DEFAULT 0,
120 VolWrites INTEGER UNSIGNED DEFAULT 0,
121 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
122 VolStatus VARCHAR(20) NOT NULL,
123 Recycle TINYINT DEFAULT 0,
124 VolRetention BIGINT UNSIGNED DEFAULT 0,
125 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
126 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
127 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
128 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
129 InChanger TINYINT DEFAULT 0,
130 StorageId INTEGER UNSIGNED REFERENCES Storage,
131 MediaAddressing TINYINT DEFAULT 0,
132 VolReadTime BIGINT UNSIGNED DEFAULT 0,
133 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
134 EndFile INTEGER UNSIGNED DEFAULT 0,
135 EndBlock INTEGER UNSIGNED DEFAULT 0,
139 CREATE INDEX inx8 ON Media (PoolId);
141 CREATE TABLE MediaType (
143 MediaType VARCHAR(128) NOT NULL,
144 ReadOnly TINYINT DEFAULT 0,
145 PRIMARY KEY(MediaTypeId)
148 CREATE TABLE Storage (
150 Name VARCHAR(128) NOT NULL,
151 AutoChanger TINYINT DEFAULT 0,
152 PRIMARY KEY(StorageId)
155 CREATE TABLE Device (
157 Name VARCHAR(128) NOT NULL,
158 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType NOT NULL,
159 StorageId INTEGER UNSIGNED REFERENCES Storage,
160 DevMounts INTEGER UNSIGNED DEFAULT 0,
161 DevReadBytes BIGINT UNSIGNED DEFAULT 0,
162 DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
163 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
164 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
165 DevReadTime BIGINT UNSIGNED DEFAULT 0,
166 DevWriteTime BIGINT UNSIGNED DEFAULT 0,
167 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
168 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
169 CleaningDate DATETIME DEFAULT 0,
170 CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
171 PRIMARY KEY(DeviceId)
177 Name VARCHAR(128) NOT NULL,
178 NumVols INTEGER UNSIGNED DEFAULT 0,
179 MaxVols INTEGER UNSIGNED DEFAULT 0,
180 UseOnce TINYINT DEFAULT 0,
181 UseCatalog TINYINT DEFAULT 1,
182 AcceptAnyVolume TINYINT DEFAULT 0,
183 VolRetention BIGINT UNSIGNED DEFAULT 0,
184 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
185 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
186 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
187 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
188 AutoPrune TINYINT DEFAULT 0,
189 Recycle TINYINT DEFAULT 0,
190 PoolType VARCHAR(20) NOT NULL,
191 LabelType TINYINT DEFAULT 0,
192 LabelFormat VARCHAR(128) NOT NULL,
193 Enabled TINYINT DEFAULT 1,
194 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
195 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
196 NextPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
197 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
198 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
199 MigrationTime BIGINT UNSIGNED DEFAULT 0,
205 CREATE TABLE Client (
207 Name VARCHAR(128) NOT NULL,
208 Uname VARCHAR(255) NOT NULL, -- uname -a field
209 AutoPrune TINYINT DEFAULT 0,
210 FileRetention BIGINT UNSIGNED DEFAULT 0,
211 JobRetention BIGINT UNSIGNED DEFAULT 0,
213 PRIMARY KEY(ClientId)
216 CREATE TABLE BaseFiles (
218 BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
219 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
220 FileId INTEGER UNSIGNED REFERENCES File NOT NULL,
221 FileIndex INTEGER UNSIGNED,
225 CREATE TABLE UnsavedFiles (
227 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
228 PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
229 FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
230 PRIMARY KEY (UnsavedId)
234 CREATE TABLE NextId (
235 id INTEGER UNSIGNED DEFAULT 0,
236 TableName TEXT NOT NULL,
237 PRIMARY KEY (TableName)
240 -- Initialize JobId to start at 1
241 INSERT INTO NextId (id, TableName) VALUES (1, "Job");
243 CREATE TABLE Version (
244 VersionId INTEGER UNSIGNED NOT NULL
248 CREATE TABLE Counters (
249 Counter TEXT NOT NULL,
250 MinValue INTEGER DEFAULT 0,
251 MaxValue INTEGER DEFAULT 0,
252 CurrentValue INTEGER DEFAULT 0,
253 WrapCounter TEXT NOT NULL,
254 PRIMARY KEY (Counter)
257 CREATE TABLE CDImages (
258 MediaId INTEGER UNSIGNED NOT NULL,
259 LastBurn DATETIME NOT NULL,
260 PRIMARY KEY (MediaId)
264 CREATE TABLE Status (
265 JobStatus CHAR(1) NOT NULL,
267 PRIMARY KEY (JobStatus)
270 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
271 ('C', 'Created, not yet running');
272 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
274 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
276 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
277 ('T', 'Completed successfully');
278 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
279 ('E', 'Terminated with errors');
280 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
281 ('e', 'Non-fatal error');
282 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
283 ('f', 'Fatal error');
284 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
285 ('D', 'Verify found differences');
286 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
287 ('A', 'Canceled by user');
288 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
289 ('F', 'Waiting for Client');
290 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
291 ('S', 'Waiting for Storage daemon');
292 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
293 ('m', 'Waiting for new media');
294 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
295 ('M', 'Waiting for media mount');
296 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
297 ('s', 'Waiting for storage resource');
298 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
299 ('j', 'Waiting for job resource');
300 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
301 ('c', 'Waiting for client resource');
302 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
303 ('d', 'Waiting on maximum jobs');
304 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
305 ('t', 'Waiting on start time');
306 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
307 ('p', 'Waiting on higher priority jobs');
310 -- Initialize Version
311 INSERT INTO Version (VersionId) VALUES (9);
314 PRAGMA default_synchronous = OFF;
315 PRAGMA default_cache_size = 10000;