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,
188 CREATE TABLE Location (
189 LocationId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
190 Location TINYBLOB NOT NULL,
191 Cost INTEGER DEFAULT 0,
193 PRIMARY KEY(LocationId)
196 CREATE TABLE LocationLog (
197 LocLogId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
198 Date DATETIME DEFAULT 0,
199 Comment BLOB NOT NULL,
200 MediaId INTEGER UNSIGNED DEFAULT 0 REFERENCES Media,
201 LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location,
202 NewVolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
203 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
205 PRIMARY KEY(LocLogId)
210 CREATE TABLE FileSet (
211 FileSetId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
212 FileSet TINYBLOB NOT NULL,
214 CreateTime DATETIME DEFAULT 0,
215 PRIMARY KEY(FileSetId)
218 CREATE TABLE JobMedia (
219 JobMediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
220 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
221 MediaId INTEGER UNSIGNED NOT NULL REFERENCES Media,
222 FirstIndex INTEGER UNSIGNED DEFAULT 0,
223 LastIndex INTEGER UNSIGNED DEFAULT 0,
224 StartFile INTEGER UNSIGNED DEFAULT 0,
225 EndFile INTEGER UNSIGNED DEFAULT 0,
226 StartBlock INTEGER UNSIGNED DEFAULT 0,
227 EndBlock INTEGER UNSIGNED DEFAULT 0,
228 VolIndex INTEGER UNSIGNED DEFAULT 0,
229 PRIMARY KEY(JobMediaId),
230 INDEX (JobId, MediaId)
235 MediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
236 VolumeName TINYBLOB NOT NULL,
237 Slot INTEGER DEFAULT 0,
238 PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
239 MediaType TINYBLOB NOT NULL,
240 MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType,
241 LabelType TINYINT DEFAULT 0,
242 FirstWritten DATETIME DEFAULT 0,
243 LastWritten DATETIME DEFAULT 0,
244 LabelDate DATETIME DEFAULT 0,
245 VolJobs INTEGER UNSIGNED DEFAULT 0,
246 VolFiles INTEGER UNSIGNED DEFAULT 0,
247 VolBlocks INTEGER UNSIGNED DEFAULT 0,
248 VolMounts INTEGER UNSIGNED DEFAULT 0,
249 VolBytes BIGINT UNSIGNED DEFAULT 0,
250 VolParts INTEGER UNSIGNED DEFAULT 0,
251 VolErrors INTEGER UNSIGNED DEFAULT 0,
252 VolWrites INTEGER UNSIGNED DEFAULT 0,
253 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
254 VolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
255 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
256 Enabled TINYINT DEFAULT 1,
257 Recycle TINYINT DEFAULT 0,
258 ActionOnPurge TINYINT DEFAULT 0,
259 VolRetention BIGINT UNSIGNED DEFAULT 0,
260 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
261 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
262 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
263 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
264 InChanger TINYINT DEFAULT 0,
265 StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage,
266 DeviceId INTEGER UNSIGNED DEFAULT 0 REFERENCES Device,
267 MediaAddressing TINYINT DEFAULT 0,
268 VolReadTime BIGINT UNSIGNED DEFAULT 0,
269 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
270 EndFile INTEGER UNSIGNED DEFAULT 0,
271 EndBlock INTEGER UNSIGNED DEFAULT 0,
272 LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location,
273 RecycleCount INTEGER UNSIGNED DEFAULT 0,
274 InitialWrite DATETIME DEFAULT 0,
275 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
276 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
278 PRIMARY KEY(MediaId),
279 UNIQUE (VolumeName(128)),
284 PoolId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
285 Name TINYBLOB NOT NULL,
286 NumVols INTEGER UNSIGNED DEFAULT 0,
287 MaxVols INTEGER UNSIGNED DEFAULT 0,
288 UseOnce TINYINT DEFAULT 0,
289 UseCatalog TINYINT DEFAULT 0,
290 AcceptAnyVolume TINYINT DEFAULT 0,
291 VolRetention BIGINT UNSIGNED DEFAULT 0,
292 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
293 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
294 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
295 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
296 AutoPrune TINYINT DEFAULT 0,
297 Recycle TINYINT DEFAULT 0,
298 ActionOnPurge TINYINT DEFAULT 0,
299 PoolType ENUM('Backup', 'Copy', 'Cloned', 'Archive', 'Migration', 'Scratch') NOT NULL,
300 LabelType TINYINT DEFAULT 0,
301 LabelFormat TINYBLOB,
302 Enabled TINYINT DEFAULT 1,
303 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
304 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
305 NextPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
306 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
307 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
308 MigrationTime BIGINT UNSIGNED DEFAULT 0,
314 CREATE TABLE Client (
315 ClientId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
316 Name TINYBLOB NOT NULL,
317 Uname TINYBLOB NOT NULL, /* full uname -a of client */
318 AutoPrune TINYINT DEFAULT 0,
319 FileRetention BIGINT UNSIGNED DEFAULT 0,
320 JobRetention BIGINT UNSIGNED DEFAULT 0,
322 PRIMARY KEY(ClientId)
326 LogId INTEGER UNSIGNED AUTO_INCREMENT,
327 JobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job,
328 Time DATETIME DEFAULT 0,
329 LogText BLOB NOT NULL,
335 CREATE TABLE BaseFiles (
336 BaseId INTEGER UNSIGNED AUTO_INCREMENT,
337 BaseJobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
338 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
339 FileId BIGINT UNSIGNED NOT NULL REFERENCES File,
340 FileIndex INTEGER UNSIGNED,
344 CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
346 CREATE TABLE UnsavedFiles (
347 UnsavedId INTEGER UNSIGNED AUTO_INCREMENT,
348 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
349 PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
350 FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
351 PRIMARY KEY (UnsavedId)
356 CREATE TABLE Counters (
357 Counter TINYBLOB NOT NULL,
358 `MinValue` INTEGER DEFAULT 0,
359 `MaxValue` INTEGER DEFAULT 0,
360 CurrentValue INTEGER DEFAULT 0,
361 WrapCounter TINYBLOB NOT NULL,
362 PRIMARY KEY (Counter(128))
365 CREATE TABLE CDImages (
366 MediaId INTEGER UNSIGNED NOT NULL,
367 LastBurn DATETIME NOT NULL,
368 PRIMARY KEY (MediaId)
371 CREATE TABLE Status (
372 JobStatus CHAR(1) BINARY NOT NULL,
375 PRIMARY KEY (JobStatus)
378 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
379 ('C', 'Created, not yet running',15),
382 ('T', 'Completed successfully',10),
383 ('E', 'Terminated with errors',25),
384 ('e', 'Non-fatal error',20),
385 ('f', 'Fatal error',100),
386 ('D', 'Verify found differences',15),
387 ('A', 'Canceled by user',90),
388 ('F', 'Waiting for Client',15),
389 ('S', 'Waiting for Storage daemon',15),
390 ('m', 'Waiting for new media',15),
391 ('M', 'Waiting for media mount',15),
392 ('s', 'Waiting for storage resource',15),
393 ('j', 'Waiting for job resource',15),
394 ('c', 'Waiting for client resource',15),
395 ('d', 'Waiting on maximum jobs',15),
396 ('t', 'Waiting on start time',15),
397 ('p', 'Waiting on higher priority jobs',15),
398 ('i', 'Doing batch insert file records',15),
399 ('a', 'SD despooling attributes',15);
401 CREATE TABLE PathHierarchy
403 PathId integer NOT NULL,
404 PPathId integer NOT NULL,
405 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
408 CREATE INDEX pathhierarchy_ppathid
409 ON PathHierarchy (PPathId);
411 CREATE TABLE PathVisibility
413 PathId integer NOT NULL,
414 JobId integer NOT NULL,
416 Files int4 DEFAULT 0,
417 CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
419 CREATE INDEX pathvisibility_jobid
420 ON PathVisibility (JobId);
422 CREATE TABLE Version (
423 VersionId INTEGER UNSIGNED NOT NULL
426 -- Initialize Version
427 INSERT INTO Version (VersionId) VALUES (@BDB_VERSION@);
431 echo "Creation of Bacula MySQL tables succeeded."
433 echo "Creation of Bacula MySQL tables failed."