3 # shell script to create Bacula MySQL tables
6 # You won't get any support for performance issue if you changed the default
11 db_name=${db_name:-@db_name@}
13 if mysql $* -f <<END-OF-DATA
16 -- Note, we use BLOB rather than TEXT because in MySQL,
17 -- BLOBs are identical to TEXT except that BLOB is case
18 -- sensitive in sorts, which is what we want, and TEXT
19 -- is case insensitive.
21 CREATE TABLE Filename (
22 FilenameId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
24 PRIMARY KEY(FilenameId),
29 PathId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
35 -- We strongly recommend to avoid the temptation to add new indexes.
36 -- In general, these will cause very significant performance
37 -- problems in other areas. A better approch is to carefully check
38 -- that all your memory configuation parameters are
39 -- suitable for the size of your installation. If you backup
40 -- millions of files, you need to adapt the database memory
41 -- configuration parameters concerning sorting, joining and global
42 -- memory. By default, sort and join parameters are very small
43 -- (sometimes 8Kb), and having sufficient memory specified by those
44 -- parameters is extremely important to run fast.
47 -- FileIndex can be 0 for FT_DELETED files
48 -- FileNameId can link to Filename.Name='' for directories
50 FileId BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
51 FileIndex INTEGER UNSIGNED DEFAULT 0,
52 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
53 PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
54 FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
55 DeltaSeq SMALLINT UNSIGNED DEFAULT 0,
56 MarkId INTEGER UNSIGNED DEFAULT 0,
57 LStat TINYBLOB NOT NULL,
61 INDEX (JobId, PathId, FilenameId)
64 CREATE TABLE RestoreObject (
65 RestoreObjectId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
66 ObjectName BLOB NOT NULL,
67 RestoreObject LONGBLOB NOT NULL,
68 PluginName TINYBLOB NOT NULL,
69 ObjectLength INTEGER DEFAULT 0,
70 ObjectFullLength INTEGER DEFAULT 0,
71 ObjectIndex INTEGER DEFAULT 0,
72 ObjectType INTEGER DEFAULT 0,
73 FileIndex INTEGER UNSIGNED DEFAULT 0,
74 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
75 ObjectCompression INTEGER DEFAULT 0,
76 PRIMARY KEY(RestoreObjectId),
82 # Possibly add one or more of the following indexes
83 # to the above File table if your Verifies are
84 # too slow, but they can slow down backups.
90 CREATE TABLE MediaType (
91 MediaTypeId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
92 MediaType TINYBLOB NOT NULL,
93 ReadOnly TINYINT DEFAULT 0,
94 PRIMARY KEY(MediaTypeId)
97 CREATE TABLE Storage (
98 StorageId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
99 Name TINYBLOB NOT NULL,
100 AutoChanger TINYINT DEFAULT 0,
101 PRIMARY KEY(StorageId)
104 CREATE TABLE Device (
105 DeviceId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
106 Name TINYBLOB NOT NULL,
107 MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType,
108 StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage,
109 DevMounts INTEGER UNSIGNED DEFAULT 0,
110 DevReadBytes BIGINT UNSIGNED DEFAULT 0,
111 DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
112 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
113 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
114 DevReadTime BIGINT UNSIGNED DEFAULT 0,
115 DevWriteTime BIGINT UNSIGNED DEFAULT 0,
116 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
117 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
118 CleaningDate DATETIME DEFAULT 0,
119 CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
120 PRIMARY KEY(DeviceId)
125 JobId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
126 Job TINYBLOB NOT NULL,
127 Name TINYBLOB NOT NULL,
128 Type BINARY(1) NOT NULL,
129 Level BINARY(1) NOT NULL,
130 ClientId INTEGER DEFAULT 0 REFERENCES Client,
131 JobStatus BINARY(1) NOT NULL,
132 SchedTime DATETIME DEFAULT 0,
133 StartTime DATETIME DEFAULT 0,
134 EndTime DATETIME DEFAULT 0,
135 RealEndTime DATETIME DEFAULT 0,
136 JobTDate BIGINT UNSIGNED DEFAULT 0,
137 VolSessionId INTEGER UNSIGNED DEFAULT 0,
138 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
139 JobFiles INTEGER UNSIGNED DEFAULT 0,
140 JobBytes BIGINT UNSIGNED DEFAULT 0,
141 ReadBytes BIGINT UNSIGNED DEFAULT 0,
142 JobErrors INTEGER UNSIGNED DEFAULT 0,
143 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
144 PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
145 FileSetId INTEGER UNSIGNED DEFAULT 0 REFERENCES FileSet,
146 PriorJobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job,
147 PurgedFiles TINYINT DEFAULT 0,
148 HasBase TINYINT DEFAULT 0,
149 HasCache TINYINT DEFAULT 0,
150 Reviewed TINYINT DEFAULT 0,
156 -- Create a table like Job for long term statistics
157 CREATE TABLE JobHisto (
158 JobId INTEGER UNSIGNED NOT NULL,
159 Job TINYBLOB NOT NULL,
160 Name TINYBLOB NOT NULL,
161 Type BINARY(1) NOT NULL,
162 Level BINARY(1) NOT NULL,
163 ClientId INTEGER DEFAULT 0,
164 JobStatus BINARY(1) NOT NULL,
165 SchedTime DATETIME DEFAULT 0,
166 StartTime DATETIME DEFAULT 0,
167 EndTime DATETIME DEFAULT 0,
168 RealEndTime DATETIME DEFAULT 0,
169 JobTDate BIGINT UNSIGNED DEFAULT 0,
170 VolSessionId INTEGER UNSIGNED DEFAULT 0,
171 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
172 JobFiles INTEGER UNSIGNED DEFAULT 0,
173 JobBytes BIGINT UNSIGNED DEFAULT 0,
174 ReadBytes BIGINT UNSIGNED DEFAULT 0,
175 JobErrors INTEGER UNSIGNED DEFAULT 0,
176 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
177 PoolId INTEGER UNSIGNED DEFAULT 0,
178 FileSetId INTEGER UNSIGNED DEFAULT 0,
179 PriorJobId INTEGER UNSIGNED DEFAULT 0,
180 PurgedFiles TINYINT DEFAULT 0,
181 HasBase TINYINT DEFAULT 0,
182 HasCache TINYINT DEFAULT 0,
183 Reviewed TINYINT DEFAULT 0,
189 CREATE TABLE Location (
190 LocationId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
191 Location TINYBLOB NOT NULL,
192 Cost INTEGER DEFAULT 0,
194 PRIMARY KEY(LocationId)
197 CREATE TABLE LocationLog (
198 LocLogId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
199 Date DATETIME DEFAULT 0,
200 Comment BLOB NOT NULL,
201 MediaId INTEGER UNSIGNED DEFAULT 0 REFERENCES Media,
202 LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location,
203 NewVolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
204 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
206 PRIMARY KEY(LocLogId)
211 CREATE TABLE FileSet (
212 FileSetId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
213 FileSet TINYBLOB NOT NULL,
215 CreateTime DATETIME DEFAULT 0,
216 PRIMARY KEY(FileSetId)
219 CREATE TABLE JobMedia (
220 JobMediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
221 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
222 MediaId INTEGER UNSIGNED NOT NULL REFERENCES Media,
223 FirstIndex INTEGER UNSIGNED DEFAULT 0,
224 LastIndex INTEGER UNSIGNED DEFAULT 0,
225 StartFile INTEGER UNSIGNED DEFAULT 0,
226 EndFile INTEGER UNSIGNED DEFAULT 0,
227 StartBlock INTEGER UNSIGNED DEFAULT 0,
228 EndBlock INTEGER UNSIGNED DEFAULT 0,
229 VolIndex INTEGER UNSIGNED DEFAULT 0,
230 PRIMARY KEY(JobMediaId),
231 INDEX (JobId, MediaId)
236 MediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
237 VolumeName TINYBLOB NOT NULL,
238 Slot INTEGER DEFAULT 0,
239 PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
240 MediaType TINYBLOB NOT NULL,
241 MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType,
242 LabelType TINYINT DEFAULT 0,
243 FirstWritten DATETIME DEFAULT 0,
244 LastWritten DATETIME DEFAULT 0,
245 LabelDate DATETIME DEFAULT 0,
246 VolJobs INTEGER UNSIGNED DEFAULT 0,
247 VolFiles INTEGER UNSIGNED DEFAULT 0,
248 VolBlocks INTEGER UNSIGNED DEFAULT 0,
249 VolMounts INTEGER UNSIGNED DEFAULT 0,
250 VolBytes BIGINT UNSIGNED DEFAULT 0,
251 VolParts INTEGER UNSIGNED DEFAULT 0,
252 VolErrors INTEGER UNSIGNED DEFAULT 0,
253 VolWrites INTEGER UNSIGNED DEFAULT 0,
254 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
255 VolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
256 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
257 Enabled TINYINT DEFAULT 1,
258 Recycle TINYINT DEFAULT 0,
259 ActionOnPurge TINYINT DEFAULT 0,
260 VolRetention BIGINT UNSIGNED DEFAULT 0,
261 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
262 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
263 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
264 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
265 InChanger TINYINT DEFAULT 0,
266 StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage,
267 DeviceId INTEGER UNSIGNED DEFAULT 0 REFERENCES Device,
268 MediaAddressing TINYINT DEFAULT 0,
269 VolReadTime BIGINT UNSIGNED DEFAULT 0,
270 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
271 EndFile INTEGER UNSIGNED DEFAULT 0,
272 EndBlock INTEGER UNSIGNED DEFAULT 0,
273 LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location,
274 RecycleCount INTEGER UNSIGNED DEFAULT 0,
275 InitialWrite DATETIME DEFAULT 0,
276 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
277 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
279 PRIMARY KEY(MediaId),
280 UNIQUE (VolumeName(128)),
285 PoolId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
286 Name TINYBLOB NOT NULL,
287 NumVols INTEGER UNSIGNED DEFAULT 0,
288 MaxVols INTEGER UNSIGNED DEFAULT 0,
289 UseOnce TINYINT DEFAULT 0,
290 UseCatalog TINYINT DEFAULT 0,
291 AcceptAnyVolume TINYINT DEFAULT 0,
292 VolRetention BIGINT UNSIGNED DEFAULT 0,
293 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
294 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
295 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
296 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
297 AutoPrune TINYINT DEFAULT 0,
298 Recycle TINYINT DEFAULT 0,
299 ActionOnPurge TINYINT DEFAULT 0,
300 PoolType ENUM('Backup', 'Copy', 'Cloned', 'Archive', 'Migration', 'Scratch') NOT NULL,
301 LabelType TINYINT DEFAULT 0,
302 LabelFormat TINYBLOB,
303 Enabled TINYINT DEFAULT 1,
304 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
305 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
306 NextPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
307 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
308 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
309 MigrationTime BIGINT UNSIGNED DEFAULT 0,
315 CREATE TABLE Client (
316 ClientId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
317 Name TINYBLOB NOT NULL,
318 Uname TINYBLOB NOT NULL, /* full uname -a of client */
319 AutoPrune TINYINT DEFAULT 0,
320 FileRetention BIGINT UNSIGNED DEFAULT 0,
321 JobRetention BIGINT UNSIGNED DEFAULT 0,
323 PRIMARY KEY(ClientId)
327 LogId INTEGER UNSIGNED AUTO_INCREMENT,
328 JobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job,
329 Time DATETIME DEFAULT 0,
330 LogText BLOB NOT NULL,
336 CREATE TABLE BaseFiles (
337 BaseId INTEGER UNSIGNED AUTO_INCREMENT,
338 BaseJobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
339 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
340 FileId BIGINT UNSIGNED NOT NULL REFERENCES File,
341 FileIndex INTEGER UNSIGNED,
345 CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
347 CREATE TABLE UnsavedFiles (
348 UnsavedId INTEGER UNSIGNED AUTO_INCREMENT,
349 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
350 PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
351 FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
352 PRIMARY KEY (UnsavedId)
357 CREATE TABLE Counters (
358 Counter TINYBLOB NOT NULL,
359 \`MinValue\` INTEGER DEFAULT 0,
360 \`MaxValue\` INTEGER DEFAULT 0,
361 CurrentValue INTEGER DEFAULT 0,
362 WrapCounter TINYBLOB NOT NULL,
363 PRIMARY KEY (Counter(128))
366 CREATE TABLE CDImages (
367 MediaId INTEGER UNSIGNED NOT NULL,
368 LastBurn DATETIME NOT NULL,
369 PRIMARY KEY (MediaId)
372 CREATE TABLE Status (
373 JobStatus CHAR(1) BINARY NOT NULL,
376 PRIMARY KEY (JobStatus)
379 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
380 ('C', 'Created, not yet running',15),
383 ('T', 'Completed successfully',10),
384 ('E', 'Terminated with errors',25),
385 ('e', 'Non-fatal error',20),
386 ('f', 'Fatal error',100),
387 ('D', 'Verify found differences',15),
388 ('A', 'Canceled by user',90),
389 ('F', 'Waiting for Client',15),
390 ('S', 'Waiting for Storage daemon',15),
391 ('m', 'Waiting for new media',15),
392 ('M', 'Waiting for media mount',15),
393 ('s', 'Waiting for storage resource',15),
394 ('j', 'Waiting for job resource',15),
395 ('c', 'Waiting for client resource',15),
396 ('d', 'Waiting on maximum jobs',15),
397 ('t', 'Waiting on start time',15),
398 ('p', 'Waiting on higher priority jobs',15),
399 ('i', 'Doing batch insert file records',15),
400 ('a', 'SD despooling attributes',15);
402 CREATE TABLE PathHierarchy
404 PathId integer NOT NULL,
405 PPathId integer NOT NULL,
406 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
409 CREATE INDEX pathhierarchy_ppathid
410 ON PathHierarchy (PPathId);
412 CREATE TABLE PathVisibility
414 PathId integer NOT NULL,
415 JobId integer NOT NULL,
417 Files int4 DEFAULT 0,
418 CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
420 CREATE INDEX pathvisibility_jobid
421 ON PathVisibility (JobId);
423 CREATE TABLE Version (
424 VersionId INTEGER UNSIGNED NOT NULL
427 -- Initialize Version
428 INSERT INTO Version (VersionId) VALUES (@BDB_VERSION@);
432 echo "Creation of Bacula MySQL tables succeeded."
434 echo "Creation of Bacula MySQL tables failed."