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 MarkId INTEGER UNSIGNED DEFAULT 0,
56 LStat TINYBLOB NOT NULL,
60 INDEX (JobId, PathId, FilenameId)
63 CREATE TABLE RestoreObject (
64 RestoreObjectId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
65 ObjectName BLOB NOT NULL,
66 RestoreObject LONGBLOB NOT NULL,
67 PluginName TINYBLOB NOT NULL,
68 ObjectLength INTEGER DEFAULT 0,
69 ObjectIndex INTEGER DEFAULT 0,
70 ObjectType INTEGER DEFAULT 0,
71 FileIndex INTEGER UNSIGNED DEFAULT 0,
72 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
73 ObjectCompression INTEGER DEFAULT 0,
74 PRIMARY KEY(RestoreObjectId),
80 # Possibly add one or more of the following indexes
81 # to the above File table if your Verifies are
82 # too slow, but they can slow down backups.
88 CREATE TABLE MediaType (
89 MediaTypeId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
90 MediaType TINYBLOB NOT NULL,
91 ReadOnly TINYINT DEFAULT 0,
92 PRIMARY KEY(MediaTypeId)
95 CREATE TABLE Storage (
96 StorageId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
97 Name TINYBLOB NOT NULL,
98 AutoChanger TINYINT DEFAULT 0,
99 PRIMARY KEY(StorageId)
102 CREATE TABLE Device (
103 DeviceId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
104 Name TINYBLOB NOT NULL,
105 MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType,
106 StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage,
107 DevMounts INTEGER UNSIGNED DEFAULT 0,
108 DevReadBytes BIGINT UNSIGNED DEFAULT 0,
109 DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
110 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
111 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
112 DevReadTime BIGINT UNSIGNED DEFAULT 0,
113 DevWriteTime BIGINT UNSIGNED DEFAULT 0,
114 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
115 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
116 CleaningDate DATETIME DEFAULT 0,
117 CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
118 PRIMARY KEY(DeviceId)
123 JobId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
124 Job TINYBLOB NOT NULL,
125 Name TINYBLOB NOT NULL,
126 Type BINARY(1) NOT NULL,
127 Level BINARY(1) NOT NULL,
128 ClientId INTEGER DEFAULT 0 REFERENCES Client,
129 JobStatus BINARY(1) NOT NULL,
130 SchedTime DATETIME DEFAULT 0,
131 StartTime DATETIME DEFAULT 0,
132 EndTime DATETIME DEFAULT 0,
133 RealEndTime DATETIME DEFAULT 0,
134 JobTDate BIGINT UNSIGNED DEFAULT 0,
135 VolSessionId INTEGER UNSIGNED DEFAULT 0,
136 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
137 JobFiles INTEGER UNSIGNED DEFAULT 0,
138 JobBytes BIGINT UNSIGNED DEFAULT 0,
139 ReadBytes BIGINT UNSIGNED DEFAULT 0,
140 JobErrors INTEGER UNSIGNED DEFAULT 0,
141 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
142 PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
143 FileSetId INTEGER UNSIGNED DEFAULT 0 REFERENCES FileSet,
144 PriorJobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job,
145 PurgedFiles TINYINT DEFAULT 0,
146 HasBase TINYINT DEFAULT 0,
147 HasCache TINYINT DEFAULT 0,
148 Reviewed TINYINT DEFAULT 0,
154 -- Create a table like Job for long term statistics
155 CREATE TABLE JobHisto (
156 JobId INTEGER UNSIGNED NOT NULL,
157 Job TINYBLOB NOT NULL,
158 Name TINYBLOB NOT NULL,
159 Type BINARY(1) NOT NULL,
160 Level BINARY(1) NOT NULL,
161 ClientId INTEGER DEFAULT 0,
162 JobStatus BINARY(1) NOT NULL,
163 SchedTime DATETIME DEFAULT 0,
164 StartTime DATETIME DEFAULT 0,
165 EndTime DATETIME DEFAULT 0,
166 RealEndTime DATETIME DEFAULT 0,
167 JobTDate BIGINT UNSIGNED DEFAULT 0,
168 VolSessionId INTEGER UNSIGNED DEFAULT 0,
169 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
170 JobFiles INTEGER UNSIGNED DEFAULT 0,
171 JobBytes BIGINT UNSIGNED DEFAULT 0,
172 ReadBytes BIGINT UNSIGNED DEFAULT 0,
173 JobErrors INTEGER UNSIGNED DEFAULT 0,
174 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
175 PoolId INTEGER UNSIGNED DEFAULT 0,
176 FileSetId INTEGER UNSIGNED DEFAULT 0,
177 PriorJobId INTEGER UNSIGNED DEFAULT 0,
178 PurgedFiles TINYINT DEFAULT 0,
179 HasBase TINYINT DEFAULT 0,
180 HasCache TINYINT DEFAULT 0,
181 Reviewed TINYINT DEFAULT 0,
186 CREATE TABLE Location (
187 LocationId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
188 Location TINYBLOB NOT NULL,
189 Cost INTEGER DEFAULT 0,
191 PRIMARY KEY(LocationId)
194 CREATE TABLE LocationLog (
195 LocLogId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
196 Date DATETIME DEFAULT 0,
197 Comment BLOB NOT NULL,
198 MediaId INTEGER UNSIGNED DEFAULT 0 REFERENCES Media,
199 LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location,
200 NewVolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
201 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
203 PRIMARY KEY(LocLogId)
208 CREATE TABLE FileSet (
209 FileSetId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
210 FileSet TINYBLOB NOT NULL,
212 CreateTime DATETIME DEFAULT 0,
213 PRIMARY KEY(FileSetId)
216 CREATE TABLE JobMedia (
217 JobMediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
218 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
219 MediaId INTEGER UNSIGNED NOT NULL REFERENCES Media,
220 FirstIndex INTEGER UNSIGNED DEFAULT 0,
221 LastIndex INTEGER UNSIGNED DEFAULT 0,
222 StartFile INTEGER UNSIGNED DEFAULT 0,
223 EndFile INTEGER UNSIGNED DEFAULT 0,
224 StartBlock INTEGER UNSIGNED DEFAULT 0,
225 EndBlock INTEGER UNSIGNED DEFAULT 0,
226 VolIndex INTEGER UNSIGNED DEFAULT 0,
227 PRIMARY KEY(JobMediaId),
228 INDEX (JobId, MediaId)
233 MediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
234 VolumeName TINYBLOB NOT NULL,
235 Slot INTEGER DEFAULT 0,
236 PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
237 MediaType TINYBLOB NOT NULL,
238 MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType,
239 LabelType TINYINT DEFAULT 0,
240 FirstWritten DATETIME DEFAULT 0,
241 LastWritten DATETIME DEFAULT 0,
242 LabelDate DATETIME DEFAULT 0,
243 VolJobs INTEGER UNSIGNED DEFAULT 0,
244 VolFiles INTEGER UNSIGNED DEFAULT 0,
245 VolBlocks INTEGER UNSIGNED DEFAULT 0,
246 VolMounts INTEGER UNSIGNED DEFAULT 0,
247 VolBytes BIGINT UNSIGNED DEFAULT 0,
248 VolParts INTEGER UNSIGNED DEFAULT 0,
249 VolErrors INTEGER UNSIGNED DEFAULT 0,
250 VolWrites INTEGER UNSIGNED DEFAULT 0,
251 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
252 VolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
253 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
254 Enabled TINYINT DEFAULT 1,
255 Recycle TINYINT DEFAULT 0,
256 ActionOnPurge TINYINT DEFAULT 0,
257 VolRetention BIGINT UNSIGNED DEFAULT 0,
258 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
259 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
260 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
261 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
262 InChanger TINYINT DEFAULT 0,
263 StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage,
264 DeviceId INTEGER UNSIGNED DEFAULT 0 REFERENCES Device,
265 MediaAddressing TINYINT DEFAULT 0,
266 VolReadTime BIGINT UNSIGNED DEFAULT 0,
267 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
268 EndFile INTEGER UNSIGNED DEFAULT 0,
269 EndBlock INTEGER UNSIGNED DEFAULT 0,
270 LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location,
271 RecycleCount INTEGER UNSIGNED DEFAULT 0,
272 InitialWrite DATETIME DEFAULT 0,
273 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
274 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
276 PRIMARY KEY(MediaId),
277 UNIQUE (VolumeName(128)),
282 PoolId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
283 Name TINYBLOB NOT NULL,
284 NumVols INTEGER UNSIGNED DEFAULT 0,
285 MaxVols INTEGER UNSIGNED DEFAULT 0,
286 UseOnce TINYINT DEFAULT 0,
287 UseCatalog TINYINT DEFAULT 0,
288 AcceptAnyVolume TINYINT DEFAULT 0,
289 VolRetention BIGINT UNSIGNED DEFAULT 0,
290 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
291 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
292 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
293 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
294 AutoPrune TINYINT DEFAULT 0,
295 Recycle TINYINT DEFAULT 0,
296 ActionOnPurge TINYINT DEFAULT 0,
297 PoolType ENUM('Backup', 'Copy', 'Cloned', 'Archive', 'Migration', 'Scratch') NOT NULL,
298 LabelType TINYINT DEFAULT 0,
299 LabelFormat TINYBLOB,
300 Enabled TINYINT DEFAULT 1,
301 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
302 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
303 NextPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
304 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
305 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
306 MigrationTime BIGINT UNSIGNED DEFAULT 0,
312 CREATE TABLE Client (
313 ClientId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
314 Name TINYBLOB NOT NULL,
315 Uname TINYBLOB NOT NULL, /* full uname -a of client */
316 AutoPrune TINYINT DEFAULT 0,
317 FileRetention BIGINT UNSIGNED DEFAULT 0,
318 JobRetention BIGINT UNSIGNED DEFAULT 0,
320 PRIMARY KEY(ClientId)
324 LogId INTEGER UNSIGNED AUTO_INCREMENT,
325 JobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job,
326 Time DATETIME DEFAULT 0,
327 LogText BLOB NOT NULL,
333 CREATE TABLE BaseFiles (
334 BaseId INTEGER UNSIGNED AUTO_INCREMENT,
335 BaseJobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
336 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
337 FileId BIGINT UNSIGNED NOT NULL REFERENCES File,
338 FileIndex INTEGER UNSIGNED,
342 CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
344 CREATE TABLE UnsavedFiles (
345 UnsavedId INTEGER UNSIGNED AUTO_INCREMENT,
346 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
347 PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
348 FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
349 PRIMARY KEY (UnsavedId)
354 CREATE TABLE Counters (
355 Counter TINYBLOB NOT NULL,
356 MinValue INTEGER DEFAULT 0,
357 MaxValue INTEGER DEFAULT 0,
358 CurrentValue INTEGER DEFAULT 0,
359 WrapCounter TINYBLOB NOT NULL,
360 PRIMARY KEY (Counter(128))
363 CREATE TABLE CDImages (
364 MediaId INTEGER UNSIGNED NOT NULL,
365 LastBurn DATETIME NOT NULL,
366 PRIMARY KEY (MediaId)
369 CREATE TABLE Status (
370 JobStatus CHAR(1) BINARY NOT NULL,
373 PRIMARY KEY (JobStatus)
376 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
377 ('C', 'Created, not yet running',15),
380 ('T', 'Completed successfully',10),
381 ('E', 'Terminated with errors',25),
382 ('e', 'Non-fatal error',20),
383 ('f', 'Fatal error',100),
384 ('D', 'Verify found differences',15),
385 ('A', 'Canceled by user',90),
386 ('F', 'Waiting for Client',15),
387 ('S', 'Waiting for Storage daemon',15),
388 ('m', 'Waiting for new media',15),
389 ('M', 'Waiting for media mount',15),
390 ('s', 'Waiting for storage resource',15),
391 ('j', 'Waiting for job resource',15),
392 ('c', 'Waiting for client resource',15),
393 ('d', 'Waiting on maximum jobs',15),
394 ('t', 'Waiting on start time',15),
395 ('p', 'Waiting on higher priority jobs',15),
396 ('i', 'Doing batch insert file records',15),
397 ('a', 'SD despooling attributes',15);
399 CREATE TABLE PathHierarchy
401 PathId integer NOT NULL,
402 PPathId integer NOT NULL,
403 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
406 CREATE INDEX pathhierarchy_ppathid
407 ON PathHierarchy (PPathId);
409 CREATE TABLE PathVisibility
411 PathId integer NOT NULL,
412 JobId integer NOT NULL,
414 Files int4 DEFAULT 0,
415 CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
417 CREATE INDEX pathvisibility_jobid
418 ON PathVisibility (JobId);
420 CREATE TABLE Version (
421 VersionId INTEGER UNSIGNED NOT NULL
424 -- Initialize Version
425 INSERT INTO Version (VersionId) VALUES (12);
429 echo "Creation of Bacula MySQL tables succeeded."
431 echo "Creation of Bacula MySQL tables failed."