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