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)
95 CREATE TABLE FileSet (
97 FileSet VARCHAR(128) NOT NULL,
98 MD5 VARCHAR(25) NOT NULL,
99 CreateTime DATETIME DEFAULT 0,
100 PRIMARY KEY(FileSetId)
103 CREATE TABLE JobMedia (
105 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
106 MediaId INTEGER UNSIGNED REFERENCES Media NOT NULL,
107 FirstIndex INTEGER UNSIGNED NOT NULL,
108 LastIndex INTEGER UNSIGNED NOT NULL,
109 StartFile INTEGER UNSIGNED DEFAULT 0,
110 EndFile INTEGER UNSIGNED DEFAULT 0,
111 StartBlock INTEGER UNSIGNED DEFAULT 0,
112 EndBlock INTEGER UNSIGNED DEFAULT 0,
113 VolIndex INTEGER UNSIGNED DEFAULT 0,
114 Copy INTEGER UNSIGNED DEFAULT 0,
115 Stripe INTEGER UNSIGNED DEFAULT 0,
116 PRIMARY KEY(JobMediaId)
119 CREATE INDEX inx7 ON JobMedia (JobId, MediaId);
124 VolumeName VARCHAR(128) NOT NULL,
125 Slot INTEGER DEFAULT 0,
126 PoolId INTEGER UNSIGNED REFERENCES Pool NOT NULL,
127 MediaType VARCHAR(128) NOT NULL,
128 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType DEFAULT 0,
129 LabelType TINYINT DEFAULT 0,
130 FirstWritten DATETIME DEFAULT 0,
131 LastWritten DATETIME DEFAULT 0,
132 LabelDate DATETIME DEFAULT 0,
133 VolJobs INTEGER UNSIGNED DEFAULT 0,
134 VolFiles INTEGER UNSIGNED DEFAULT 0,
135 VolBlocks INTEGER UNSIGNED DEFAULT 0,
136 VolMounts INTEGER UNSIGNED DEFAULT 0,
137 VolBytes BIGINT UNSIGNED DEFAULT 0,
138 VolParts INTEGER UNSIGNED DEFAULT 0,
139 VolErrors INTEGER UNSIGNED DEFAULT 0,
140 VolWrites INTEGER UNSIGNED DEFAULT 0,
141 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
142 VolStatus VARCHAR(20) NOT NULL,
143 Recycle TINYINT DEFAULT 0,
144 VolRetention BIGINT UNSIGNED DEFAULT 0,
145 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
146 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
147 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
148 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
149 InChanger TINYINT DEFAULT 0,
150 StorageId INTEGER UNSIGNED REFERENCES Storage,
151 DeviceId INTEGER UNSIGNED REFERENCES Device,
152 MediaAddressing TINYINT DEFAULT 0,
153 VolReadTime BIGINT UNSIGNED DEFAULT 0,
154 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
155 EndFile INTEGER UNSIGNED DEFAULT 0,
156 EndBlock INTEGER UNSIGNED DEFAULT 0,
157 LocationId INTEGER UNSIGNED REFERENCES Location,
158 RecycleCount INTEGER UNSIGNED DEFAULT 0,
159 InitialWrite DATETIME DEFAULT 0,
160 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
161 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
165 CREATE INDEX inx8 ON Media (PoolId);
167 CREATE TABLE MediaType (
169 MediaType VARCHAR(128) NOT NULL,
170 ReadOnly TINYINT DEFAULT 0,
171 PRIMARY KEY(MediaTypeId)
174 CREATE TABLE Storage (
176 Name VARCHAR(128) NOT NULL,
177 AutoChanger TINYINT DEFAULT 0,
178 PRIMARY KEY(StorageId)
181 CREATE TABLE Device (
183 Name VARCHAR(128) NOT NULL,
184 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType NOT NULL,
185 StorageId INTEGER UNSIGNED REFERENCES Storage,
186 DevMounts INTEGER UNSIGNED DEFAULT 0,
187 DevReadBytes BIGINT UNSIGNED DEFAULT 0,
188 DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
189 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
190 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
191 DevReadTime BIGINT UNSIGNED DEFAULT 0,
192 DevWriteTime BIGINT UNSIGNED DEFAULT 0,
193 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
194 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
195 CleaningDate DATETIME DEFAULT 0,
196 CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
197 PRIMARY KEY(DeviceId)
203 Name VARCHAR(128) NOT NULL,
204 NumVols INTEGER UNSIGNED DEFAULT 0,
205 MaxVols INTEGER UNSIGNED DEFAULT 0,
206 UseOnce TINYINT DEFAULT 0,
207 UseCatalog TINYINT DEFAULT 1,
208 AcceptAnyVolume TINYINT DEFAULT 0,
209 VolRetention BIGINT UNSIGNED DEFAULT 0,
210 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
211 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
212 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
213 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
214 AutoPrune TINYINT DEFAULT 0,
215 Recycle TINYINT DEFAULT 0,
216 PoolType VARCHAR(20) NOT NULL,
217 LabelType TINYINT DEFAULT 0,
218 LabelFormat VARCHAR(128) NOT NULL,
219 Enabled TINYINT DEFAULT 1,
220 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
221 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
222 NextPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
223 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
224 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
225 MigrationTime BIGINT UNSIGNED DEFAULT 0,
231 CREATE TABLE Client (
233 Name VARCHAR(128) NOT NULL,
234 Uname VARCHAR(255) NOT NULL, -- uname -a field
235 AutoPrune TINYINT DEFAULT 0,
236 FileRetention BIGINT UNSIGNED DEFAULT 0,
237 JobRetention BIGINT UNSIGNED DEFAULT 0,
239 PRIMARY KEY(ClientId)
242 CREATE TABLE BaseFiles (
244 BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
245 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
246 FileId INTEGER UNSIGNED REFERENCES File NOT NULL,
247 FileIndex INTEGER UNSIGNED,
251 CREATE TABLE UnsavedFiles (
253 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
254 PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
255 FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
256 PRIMARY KEY (UnsavedId)
260 CREATE TABLE NextId (
261 id INTEGER UNSIGNED DEFAULT 0,
262 TableName TEXT NOT NULL,
263 PRIMARY KEY (TableName)
266 -- Initialize JobId to start at 1
267 INSERT INTO NextId (id, TableName) VALUES (1, "Job");
269 CREATE TABLE Version (
270 VersionId INTEGER UNSIGNED NOT NULL
274 CREATE TABLE Counters (
275 Counter TEXT NOT NULL,
276 MinValue INTEGER DEFAULT 0,
277 MaxValue INTEGER DEFAULT 0,
278 CurrentValue INTEGER DEFAULT 0,
279 WrapCounter TEXT NOT NULL,
280 PRIMARY KEY (Counter)
283 CREATE TABLE CDImages (
284 MediaId INTEGER UNSIGNED NOT NULL,
285 LastBurn DATETIME NOT NULL,
286 PRIMARY KEY (MediaId)
290 CREATE TABLE Status (
291 JobStatus CHAR(1) NOT NULL,
293 PRIMARY KEY (JobStatus)
296 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
297 ('C', 'Created, not yet running');
298 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
300 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
302 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
303 ('T', 'Completed successfully');
304 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
305 ('E', 'Terminated with errors');
306 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
307 ('e', 'Non-fatal error');
308 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
309 ('f', 'Fatal error');
310 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
311 ('D', 'Verify found differences');
312 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
313 ('A', 'Canceled by user');
314 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
315 ('F', 'Waiting for Client');
316 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
317 ('S', 'Waiting for Storage daemon');
318 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
319 ('m', 'Waiting for new media');
320 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
321 ('M', 'Waiting for media mount');
322 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
323 ('s', 'Waiting for storage resource');
324 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
325 ('j', 'Waiting for job resource');
326 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
327 ('c', 'Waiting for client resource');
328 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
329 ('d', 'Waiting on maximum jobs');
330 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
331 ('t', 'Waiting on start time');
332 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
333 ('p', 'Waiting on higher priority jobs');
336 -- Initialize Version
337 INSERT INTO Version (VersionId) VALUES (9);
340 PRAGMA default_synchronous = OFF;
341 PRAGMA default_cache_size = 10000;