3 # shell script to create Bacula MySQL tables
6 # You won't get any support for performance issue if you changed the default
10 # Bacula® - The Network Backup Solution
12 # Copyright (C) 2000-2014 Free Software Foundation Europe e.V.
14 # The main author of Bacula is Kern Sibbald, with contributions from many
15 # others, a complete list can be found in the file AUTHORS.
17 # You may use this file and others of this release according to the
18 # license defined in the LICENSE file, which includes the Affero General
19 # Public License, v3.0 ("AGPLv3") and some additional permissions and
20 # terms pursuant to its AGPLv3 Section 7.
22 # Bacula® is a registered trademark of Kern Sibbald.
26 db_name=${db_name:-@db_name@}
28 if mysql -D ${db_name} $* -f <<END-OF-DATA
30 -- Note, we use BLOB rather than TEXT because in MySQL,
31 -- BLOBs are identical to TEXT except that BLOB is case
32 -- sensitive in sorts, which is what we want, and TEXT
33 -- is case insensitive.
35 CREATE TABLE Filename (
36 FilenameId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
38 PRIMARY KEY(FilenameId),
43 PathId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
49 -- We strongly recommend to avoid the temptation to add new indexes.
50 -- In general, these will cause very significant performance
51 -- problems in other areas. A better approch is to carefully check
52 -- that all your memory configuation parameters are
53 -- suitable for the size of your installation. If you backup
54 -- millions of files, you need to adapt the database memory
55 -- configuration parameters concerning sorting, joining and global
56 -- memory. By default, sort and join parameters are very small
57 -- (sometimes 8Kb), and having sufficient memory specified by those
58 -- parameters is extremely important to run fast.
61 -- FileIndex can be 0 for FT_DELETED files
62 -- FileNameId can link to Filename.Name='' for directories
64 FileId BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
65 FileIndex INTEGER UNSIGNED DEFAULT 0,
66 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
67 PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
68 FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
69 DeltaSeq SMALLINT UNSIGNED DEFAULT 0,
70 MarkId INTEGER UNSIGNED DEFAULT 0,
71 LStat TINYBLOB NOT NULL,
75 INDEX (JobId, PathId, FilenameId)
78 CREATE TABLE RestoreObject (
79 RestoreObjectId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
80 ObjectName BLOB NOT NULL,
81 RestoreObject LONGBLOB NOT NULL,
82 PluginName TINYBLOB NOT NULL,
83 ObjectLength INTEGER DEFAULT 0,
84 ObjectFullLength INTEGER DEFAULT 0,
85 ObjectIndex INTEGER DEFAULT 0,
86 ObjectType INTEGER DEFAULT 0,
87 FileIndex INTEGER UNSIGNED DEFAULT 0,
88 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
89 ObjectCompression INTEGER DEFAULT 0,
90 PRIMARY KEY(RestoreObjectId),
96 # Possibly add one or more of the following indexes
97 # to the above File table if your Verifies are
98 # too slow, but they can slow down backups.
101 # INDEX (FilenameId),
104 CREATE TABLE MediaType (
105 MediaTypeId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
106 MediaType TINYBLOB NOT NULL,
107 ReadOnly TINYINT DEFAULT 0,
108 PRIMARY KEY(MediaTypeId)
111 CREATE TABLE Storage (
112 StorageId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
113 Name TINYBLOB NOT NULL,
114 AutoChanger TINYINT DEFAULT 0,
115 PRIMARY KEY(StorageId)
118 CREATE TABLE Device (
119 DeviceId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
120 Name TINYBLOB NOT NULL,
121 MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType,
122 StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage,
123 DevMounts INTEGER UNSIGNED DEFAULT 0,
124 DevReadBytes BIGINT UNSIGNED DEFAULT 0,
125 DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
126 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
127 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
128 DevReadTime BIGINT UNSIGNED DEFAULT 0,
129 DevWriteTime BIGINT UNSIGNED DEFAULT 0,
130 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
131 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
132 CleaningDate DATETIME DEFAULT 0,
133 CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
134 PRIMARY KEY(DeviceId)
139 JobId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
140 Job TINYBLOB NOT NULL,
141 Name TINYBLOB NOT NULL,
142 Type BINARY(1) NOT NULL,
143 Level BINARY(1) NOT NULL,
144 ClientId INTEGER DEFAULT 0 REFERENCES Client,
145 JobStatus BINARY(1) NOT NULL,
146 SchedTime DATETIME DEFAULT 0,
147 StartTime DATETIME DEFAULT 0,
148 EndTime DATETIME DEFAULT 0,
149 RealEndTime DATETIME DEFAULT 0,
150 JobTDate BIGINT UNSIGNED DEFAULT 0,
151 VolSessionId INTEGER UNSIGNED DEFAULT 0,
152 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
153 JobFiles INTEGER UNSIGNED DEFAULT 0,
154 JobBytes BIGINT UNSIGNED DEFAULT 0,
155 ReadBytes BIGINT UNSIGNED DEFAULT 0,
156 JobErrors INTEGER UNSIGNED DEFAULT 0,
157 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
158 PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
159 FileSetId INTEGER UNSIGNED DEFAULT 0 REFERENCES FileSet,
160 PriorJobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job,
161 PurgedFiles TINYINT DEFAULT 0,
162 HasBase TINYINT DEFAULT 0,
163 HasCache TINYINT DEFAULT 0,
164 Reviewed TINYINT DEFAULT 0,
170 -- Create a table like Job for long term statistics
171 CREATE TABLE JobHisto (
172 JobId INTEGER UNSIGNED NOT NULL,
173 Job TINYBLOB NOT NULL,
174 Name TINYBLOB NOT NULL,
175 Type BINARY(1) NOT NULL,
176 Level BINARY(1) NOT NULL,
177 ClientId INTEGER DEFAULT 0,
178 JobStatus BINARY(1) NOT NULL,
179 SchedTime DATETIME DEFAULT 0,
180 StartTime DATETIME DEFAULT 0,
181 EndTime DATETIME DEFAULT 0,
182 RealEndTime DATETIME DEFAULT 0,
183 JobTDate BIGINT UNSIGNED DEFAULT 0,
184 VolSessionId INTEGER UNSIGNED DEFAULT 0,
185 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
186 JobFiles INTEGER UNSIGNED DEFAULT 0,
187 JobBytes BIGINT UNSIGNED DEFAULT 0,
188 ReadBytes BIGINT UNSIGNED DEFAULT 0,
189 JobErrors INTEGER UNSIGNED DEFAULT 0,
190 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
191 PoolId INTEGER UNSIGNED DEFAULT 0,
192 FileSetId INTEGER UNSIGNED DEFAULT 0,
193 PriorJobId INTEGER UNSIGNED DEFAULT 0,
194 PurgedFiles TINYINT DEFAULT 0,
195 HasBase TINYINT DEFAULT 0,
196 HasCache TINYINT DEFAULT 0,
197 Reviewed TINYINT DEFAULT 0,
203 CREATE TABLE Location (
204 LocationId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
205 Location TINYBLOB NOT NULL,
206 Cost INTEGER DEFAULT 0,
208 PRIMARY KEY(LocationId)
211 CREATE TABLE LocationLog (
212 LocLogId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
213 Date DATETIME DEFAULT 0,
214 Comment BLOB NOT NULL,
215 MediaId INTEGER UNSIGNED DEFAULT 0 REFERENCES Media,
216 LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location,
217 NewVolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
218 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
220 PRIMARY KEY(LocLogId)
225 CREATE TABLE FileSet (
226 FileSetId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
227 FileSet TINYBLOB NOT NULL,
229 CreateTime DATETIME DEFAULT 0,
230 PRIMARY KEY(FileSetId)
233 CREATE TABLE JobMedia (
234 JobMediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
235 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
236 MediaId INTEGER UNSIGNED NOT NULL REFERENCES Media,
237 FirstIndex INTEGER UNSIGNED DEFAULT 0,
238 LastIndex INTEGER UNSIGNED DEFAULT 0,
239 StartFile INTEGER UNSIGNED DEFAULT 0,
240 EndFile INTEGER UNSIGNED DEFAULT 0,
241 StartBlock INTEGER UNSIGNED DEFAULT 0,
242 EndBlock INTEGER UNSIGNED DEFAULT 0,
243 VolIndex INTEGER UNSIGNED DEFAULT 0,
244 PRIMARY KEY(JobMediaId),
245 INDEX (JobId, MediaId)
250 MediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
251 VolumeName TINYBLOB NOT NULL,
252 Slot INTEGER DEFAULT 0,
253 PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
254 MediaType TINYBLOB NOT NULL,
255 MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType,
256 LabelType TINYINT DEFAULT 0,
257 FirstWritten DATETIME DEFAULT 0,
258 LastWritten DATETIME DEFAULT 0,
259 LabelDate DATETIME DEFAULT 0,
260 VolJobs INTEGER UNSIGNED DEFAULT 0,
261 VolFiles INTEGER UNSIGNED DEFAULT 0,
262 VolBlocks INTEGER UNSIGNED DEFAULT 0,
263 VolMounts INTEGER UNSIGNED DEFAULT 0,
264 VolBytes BIGINT UNSIGNED DEFAULT 0,
265 VolParts INTEGER UNSIGNED DEFAULT 0,
266 VolErrors INTEGER UNSIGNED DEFAULT 0,
267 VolWrites INTEGER UNSIGNED DEFAULT 0,
268 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
269 VolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
270 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
271 Enabled TINYINT DEFAULT 1,
272 Recycle TINYINT DEFAULT 0,
273 ActionOnPurge TINYINT DEFAULT 0,
274 VolRetention BIGINT UNSIGNED DEFAULT 0,
275 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
276 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
277 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
278 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
279 InChanger TINYINT DEFAULT 0,
280 StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage,
281 DeviceId INTEGER UNSIGNED DEFAULT 0 REFERENCES Device,
282 MediaAddressing TINYINT DEFAULT 0,
283 VolReadTime BIGINT UNSIGNED DEFAULT 0,
284 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
285 EndFile INTEGER UNSIGNED DEFAULT 0,
286 EndBlock INTEGER UNSIGNED DEFAULT 0,
287 LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location,
288 RecycleCount INTEGER UNSIGNED DEFAULT 0,
289 InitialWrite DATETIME DEFAULT 0,
290 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
291 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
293 PRIMARY KEY(MediaId),
294 UNIQUE (VolumeName(128)),
299 PoolId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
300 Name TINYBLOB NOT NULL,
301 NumVols INTEGER UNSIGNED DEFAULT 0,
302 MaxVols INTEGER UNSIGNED DEFAULT 0,
303 UseOnce TINYINT DEFAULT 0,
304 UseCatalog TINYINT DEFAULT 0,
305 AcceptAnyVolume TINYINT DEFAULT 0,
306 VolRetention BIGINT UNSIGNED DEFAULT 0,
307 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
308 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
309 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
310 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
311 AutoPrune TINYINT DEFAULT 0,
312 Recycle TINYINT DEFAULT 0,
313 ActionOnPurge TINYINT DEFAULT 0,
314 PoolType ENUM('Backup', 'Copy', 'Cloned', 'Archive', 'Migration', 'Scratch') NOT NULL,
315 LabelType TINYINT DEFAULT 0,
316 LabelFormat TINYBLOB,
317 Enabled TINYINT DEFAULT 1,
318 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
319 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
320 NextPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
321 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
322 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
323 MigrationTime BIGINT UNSIGNED DEFAULT 0,
329 CREATE TABLE Client (
330 ClientId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
331 Name TINYBLOB NOT NULL,
332 Uname TINYBLOB NOT NULL, /* full uname -a of client */
333 AutoPrune TINYINT DEFAULT 0,
334 FileRetention BIGINT UNSIGNED DEFAULT 0,
335 JobRetention BIGINT UNSIGNED DEFAULT 0,
337 PRIMARY KEY(ClientId)
341 LogId INTEGER UNSIGNED AUTO_INCREMENT,
342 JobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job,
343 Time DATETIME DEFAULT 0,
344 LogText BLOB NOT NULL,
350 CREATE TABLE BaseFiles (
351 BaseId INTEGER UNSIGNED AUTO_INCREMENT,
352 BaseJobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
353 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
354 FileId BIGINT UNSIGNED NOT NULL REFERENCES File,
355 FileIndex INTEGER UNSIGNED,
359 CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
361 CREATE TABLE UnsavedFiles (
362 UnsavedId INTEGER UNSIGNED AUTO_INCREMENT,
363 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
364 PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
365 FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
366 PRIMARY KEY (UnsavedId)
371 CREATE TABLE Counters (
372 Counter TINYBLOB NOT NULL,
373 \`MinValue\` INTEGER DEFAULT 0,
374 \`MaxValue\` INTEGER DEFAULT 0,
375 CurrentValue INTEGER DEFAULT 0,
376 WrapCounter TINYBLOB NOT NULL,
377 PRIMARY KEY (Counter(128))
380 CREATE TABLE CDImages (
381 MediaId INTEGER UNSIGNED NOT NULL,
382 LastBurn DATETIME NOT NULL,
383 PRIMARY KEY (MediaId)
386 CREATE TABLE Status (
387 JobStatus CHAR(1) BINARY NOT NULL,
390 PRIMARY KEY (JobStatus)
393 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
394 ('C', 'Created, not yet running',15),
397 ('T', 'Completed successfully',10),
398 ('E', 'Terminated with errors',25),
399 ('e', 'Non-fatal error',20),
400 ('f', 'Fatal error',100),
401 ('D', 'Verify found differences',15),
402 ('A', 'Canceled by user',90),
403 ('F', 'Waiting for Client',15),
404 ('S', 'Waiting for Storage daemon',15),
405 ('m', 'Waiting for new media',15),
406 ('M', 'Waiting for media mount',15),
407 ('s', 'Waiting for storage resource',15),
408 ('j', 'Waiting for job resource',15),
409 ('c', 'Waiting for client resource',15),
410 ('d', 'Waiting on maximum jobs',15),
411 ('t', 'Waiting on start time',15),
412 ('p', 'Waiting on higher priority jobs',15),
413 ('i', 'Doing batch insert file records',15),
414 ('a', 'SD despooling attributes',15);
416 CREATE TABLE PathHierarchy
418 PathId integer NOT NULL,
419 PPathId integer NOT NULL,
420 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
423 CREATE INDEX pathhierarchy_ppathid
424 ON PathHierarchy (PPathId);
426 CREATE TABLE PathVisibility
428 PathId integer NOT NULL,
429 JobId integer NOT NULL,
431 Files int4 DEFAULT 0,
432 CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
434 CREATE INDEX pathvisibility_jobid
435 ON PathVisibility (JobId);
437 CREATE TABLE Version (
438 VersionId INTEGER UNSIGNED NOT NULL
441 -- Initialize Version
442 INSERT INTO Version (VersionId) VALUES (@BDB_VERSION@);
446 echo "Creation of Bacula MySQL tables succeeded."
448 echo "Creation of Bacula MySQL tables failed."