3 # shell script to create Bacula MySQL tables
7 db_name=${db_name:-@db_name@}
9 if mysql $* -f <<END-OF-DATA
12 -- Note, we use BLOB rather than TEXT because in MySQL,
13 -- BLOBs are identical to TEXT except that BLOB is case
14 -- sensitive in sorts, which is what we want, and TEXT
15 -- is case insensitive.
17 CREATE TABLE Filename (
18 FilenameId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
20 PRIMARY KEY(FilenameId),
25 PathId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
32 -- FileIndex can be 0 for FT_DELETED files
33 -- FileNameId can link to Filename.Name='' for directories
35 FileId BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
36 FileIndex INTEGER UNSIGNED DEFAULT 0,
37 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
38 PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
39 FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
40 MarkId INTEGER UNSIGNED DEFAULT 0,
41 LStat TINYBLOB NOT NULL,
45 INDEX (JobId, PathId, FilenameId)
48 CREATE TABLE RestoreObject (
49 RestoreObjectId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
52 RestoreObject BLOB NOT NULL,
53 PluginName TINYBLOB NOT NULL,
54 ObjectLength INTEGER DEFAULT 0,
55 ObjectIndex INTEGER DEFAULT 0,
56 ObjectType INTEGER DEFAULT 0,
57 FileIndex INTEGER UNSIGNED DEFAULT 0,
58 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
59 PRIMARY KEY(RestoreObjectId),
65 # Possibly add one or more of the following indexes
66 # to the above File table if your Verifies are
73 CREATE TABLE MediaType (
74 MediaTypeId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
75 MediaType TINYBLOB NOT NULL,
76 ReadOnly TINYINT DEFAULT 0,
77 PRIMARY KEY(MediaTypeId)
80 CREATE TABLE Storage (
81 StorageId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
82 Name TINYBLOB NOT NULL,
83 AutoChanger TINYINT DEFAULT 0,
84 PRIMARY KEY(StorageId)
88 DeviceId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
89 Name TINYBLOB NOT NULL,
90 MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType,
91 StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage,
92 DevMounts INTEGER UNSIGNED DEFAULT 0,
93 DevReadBytes BIGINT UNSIGNED DEFAULT 0,
94 DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
95 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
96 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
97 DevReadTime BIGINT UNSIGNED DEFAULT 0,
98 DevWriteTime BIGINT UNSIGNED DEFAULT 0,
99 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
100 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
101 CleaningDate DATETIME DEFAULT 0,
102 CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
103 PRIMARY KEY(DeviceId)
108 JobId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
109 Job TINYBLOB NOT NULL,
110 Name TINYBLOB NOT NULL,
111 Type BINARY(1) NOT NULL,
112 Level BINARY(1) NOT NULL,
113 ClientId INTEGER DEFAULT 0 REFERENCES Client,
114 JobStatus BINARY(1) NOT NULL,
115 SchedTime DATETIME DEFAULT 0,
116 StartTime DATETIME DEFAULT 0,
117 EndTime DATETIME DEFAULT 0,
118 RealEndTime DATETIME DEFAULT 0,
119 JobTDate BIGINT UNSIGNED DEFAULT 0,
120 VolSessionId INTEGER UNSIGNED DEFAULT 0,
121 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
122 JobFiles INTEGER UNSIGNED DEFAULT 0,
123 JobBytes BIGINT UNSIGNED DEFAULT 0,
124 ReadBytes BIGINT UNSIGNED DEFAULT 0,
125 JobErrors INTEGER UNSIGNED DEFAULT 0,
126 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
127 PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
128 FileSetId INTEGER UNSIGNED DEFAULT 0 REFERENCES FileSet,
129 PriorJobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job,
130 PurgedFiles TINYINT DEFAULT 0,
131 HasBase TINYINT DEFAULT 0,
132 HasCache TINYINT DEFAULT 0,
133 Reviewed TINYINT DEFAULT 0,
139 -- Create a table like Job for long term statistics
140 CREATE TABLE JobHisto (
141 JobId INTEGER UNSIGNED NOT NULL,
142 Job TINYBLOB NOT NULL,
143 Name TINYBLOB NOT NULL,
144 Type BINARY(1) NOT NULL,
145 Level BINARY(1) NOT NULL,
146 ClientId INTEGER DEFAULT 0,
147 JobStatus BINARY(1) NOT NULL,
148 SchedTime DATETIME DEFAULT 0,
149 StartTime DATETIME DEFAULT 0,
150 EndTime DATETIME DEFAULT 0,
151 RealEndTime DATETIME DEFAULT 0,
152 JobTDate BIGINT UNSIGNED DEFAULT 0,
153 VolSessionId INTEGER UNSIGNED DEFAULT 0,
154 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
155 JobFiles INTEGER UNSIGNED DEFAULT 0,
156 JobBytes BIGINT UNSIGNED DEFAULT 0,
157 ReadBytes BIGINT UNSIGNED DEFAULT 0,
158 JobErrors INTEGER UNSIGNED DEFAULT 0,
159 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
160 PoolId INTEGER UNSIGNED DEFAULT 0,
161 FileSetId INTEGER UNSIGNED DEFAULT 0,
162 PriorJobId INTEGER UNSIGNED DEFAULT 0,
163 PurgedFiles TINYINT DEFAULT 0,
164 HasBase TINYINT DEFAULT 0,
165 HasCache TINYINT DEFAULT 0,
166 Reviewed TINYINT DEFAULT 0,
171 CREATE TABLE Location (
172 LocationId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
173 Location TINYBLOB NOT NULL,
174 Cost INTEGER DEFAULT 0,
176 PRIMARY KEY(LocationId)
179 CREATE TABLE LocationLog (
180 LocLogId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
181 Date DATETIME DEFAULT 0,
182 Comment BLOB NOT NULL,
183 MediaId INTEGER UNSIGNED DEFAULT 0 REFERENCES Media,
184 LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location,
185 NewVolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
186 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
188 PRIMARY KEY(LocLogId)
193 CREATE TABLE FileSet (
194 FileSetId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
195 FileSet TINYBLOB NOT NULL,
197 CreateTime DATETIME DEFAULT 0,
198 PRIMARY KEY(FileSetId)
201 CREATE TABLE JobMedia (
202 JobMediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
203 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
204 MediaId INTEGER UNSIGNED NOT NULL REFERENCES Media,
205 FirstIndex INTEGER UNSIGNED DEFAULT 0,
206 LastIndex INTEGER UNSIGNED DEFAULT 0,
207 StartFile INTEGER UNSIGNED DEFAULT 0,
208 EndFile INTEGER UNSIGNED DEFAULT 0,
209 StartBlock INTEGER UNSIGNED DEFAULT 0,
210 EndBlock INTEGER UNSIGNED DEFAULT 0,
211 VolIndex INTEGER UNSIGNED DEFAULT 0,
212 PRIMARY KEY(JobMediaId),
213 INDEX (JobId, MediaId)
218 MediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
219 VolumeName TINYBLOB NOT NULL,
220 Slot INTEGER DEFAULT 0,
221 PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
222 MediaType TINYBLOB NOT NULL,
223 MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType,
224 LabelType TINYINT DEFAULT 0,
225 FirstWritten DATETIME DEFAULT 0,
226 LastWritten DATETIME DEFAULT 0,
227 LabelDate DATETIME DEFAULT 0,
228 VolJobs INTEGER UNSIGNED DEFAULT 0,
229 VolFiles INTEGER UNSIGNED DEFAULT 0,
230 VolBlocks INTEGER UNSIGNED DEFAULT 0,
231 VolMounts INTEGER UNSIGNED DEFAULT 0,
232 VolBytes BIGINT UNSIGNED DEFAULT 0,
233 VolParts INTEGER UNSIGNED DEFAULT 0,
234 VolErrors INTEGER UNSIGNED DEFAULT 0,
235 VolWrites INTEGER UNSIGNED DEFAULT 0,
236 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
237 VolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
238 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
239 Enabled TINYINT DEFAULT 1,
240 Recycle TINYINT DEFAULT 0,
241 ActionOnPurge TINYINT DEFAULT 0,
242 VolRetention BIGINT UNSIGNED DEFAULT 0,
243 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
244 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
245 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
246 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
247 InChanger TINYINT DEFAULT 0,
248 StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage,
249 DeviceId INTEGER UNSIGNED DEFAULT 0 REFERENCES Device,
250 MediaAddressing TINYINT DEFAULT 0,
251 VolReadTime BIGINT UNSIGNED DEFAULT 0,
252 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
253 EndFile INTEGER UNSIGNED DEFAULT 0,
254 EndBlock INTEGER UNSIGNED DEFAULT 0,
255 LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location,
256 RecycleCount INTEGER UNSIGNED DEFAULT 0,
257 InitialWrite DATETIME DEFAULT 0,
258 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
259 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
261 PRIMARY KEY(MediaId),
262 UNIQUE (VolumeName(128)),
267 PoolId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
268 Name TINYBLOB NOT NULL,
269 NumVols INTEGER UNSIGNED DEFAULT 0,
270 MaxVols INTEGER UNSIGNED DEFAULT 0,
271 UseOnce TINYINT DEFAULT 0,
272 UseCatalog TINYINT DEFAULT 0,
273 AcceptAnyVolume 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 AutoPrune TINYINT DEFAULT 0,
280 Recycle TINYINT DEFAULT 0,
281 ActionOnPurge TINYINT DEFAULT 0,
282 PoolType ENUM('Backup', 'Copy', 'Cloned', 'Archive', 'Migration', 'Scratch') NOT NULL,
283 LabelType TINYINT DEFAULT 0,
284 LabelFormat TINYBLOB,
285 Enabled TINYINT DEFAULT 1,
286 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
287 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
288 NextPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
289 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
290 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
291 MigrationTime BIGINT UNSIGNED DEFAULT 0,
297 CREATE TABLE Client (
298 ClientId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
299 Name TINYBLOB NOT NULL,
300 Uname TINYBLOB NOT NULL, /* full uname -a of client */
301 AutoPrune TINYINT DEFAULT 0,
302 FileRetention BIGINT UNSIGNED DEFAULT 0,
303 JobRetention BIGINT UNSIGNED DEFAULT 0,
305 PRIMARY KEY(ClientId)
309 LogId INTEGER UNSIGNED AUTO_INCREMENT,
310 JobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job,
311 Time DATETIME DEFAULT 0,
312 LogText BLOB NOT NULL,
318 CREATE TABLE BaseFiles (
319 BaseId INTEGER UNSIGNED AUTO_INCREMENT,
320 BaseJobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
321 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
322 FileId BIGINT UNSIGNED NOT NULL REFERENCES File,
323 FileIndex INTEGER UNSIGNED,
327 CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
329 CREATE TABLE UnsavedFiles (
330 UnsavedId INTEGER UNSIGNED AUTO_INCREMENT,
331 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
332 PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
333 FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
334 PRIMARY KEY (UnsavedId)
339 CREATE TABLE Counters (
340 Counter TINYBLOB NOT NULL,
341 MinValue INTEGER DEFAULT 0,
342 MaxValue INTEGER DEFAULT 0,
343 CurrentValue INTEGER DEFAULT 0,
344 WrapCounter TINYBLOB NOT NULL,
345 PRIMARY KEY (Counter(128))
348 CREATE TABLE CDImages (
349 MediaId INTEGER UNSIGNED NOT NULL,
350 LastBurn DATETIME NOT NULL,
351 PRIMARY KEY (MediaId)
354 CREATE TABLE Status (
355 JobStatus CHAR(1) BINARY NOT NULL,
358 PRIMARY KEY (JobStatus)
361 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
362 ('C', 'Created, not yet running',15),
365 ('T', 'Completed successfully',10),
366 ('E', 'Terminated with errors',25),
367 ('e', 'Non-fatal error',20),
368 ('f', 'Fatal error',100),
369 ('D', 'Verify found differences',15),
370 ('A', 'Canceled by user',90),
371 ('F', 'Waiting for Client',15),
372 ('S', 'Waiting for Storage daemon',15),
373 ('m', 'Waiting for new media',15),
374 ('M', 'Waiting for media mount',15),
375 ('s', 'Waiting for storage resource',15),
376 ('j', 'Waiting for job resource',15),
377 ('c', 'Waiting for client resource',15),
378 ('d', 'Waiting on maximum jobs',15),
379 ('t', 'Waiting on start time',15),
380 ('p', 'Waiting on higher priority jobs',15),
381 ('i', 'Doing batch insert file records',15),
382 ('a', 'SD despooling attributes',15);
384 CREATE TABLE PathHierarchy
386 PathId integer NOT NULL,
387 PPathId integer NOT NULL,
388 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
391 CREATE INDEX pathhierarchy_ppathid
392 ON PathHierarchy (PPathId);
394 CREATE TABLE PathVisibility
396 PathId integer NOT NULL,
397 JobId integer NOT NULL,
399 Files int4 DEFAULT 0,
400 CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
402 CREATE INDEX pathvisibility_jobid
403 ON PathVisibility (JobId);
405 CREATE TABLE Version (
406 VersionId INTEGER UNSIGNED NOT NULL
409 -- Initialize Version
410 INSERT INTO Version (VersionId) VALUES (12);
414 echo "Creation of Bacula MySQL tables succeeded."
416 echo "Creation of Bacula MySQL tables failed."