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
71 # INDEX (FilenameId, PathId)
75 CREATE TABLE MediaType (
76 MediaTypeId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
77 MediaType TINYBLOB NOT NULL,
78 ReadOnly TINYINT DEFAULT 0,
79 PRIMARY KEY(MediaTypeId)
82 CREATE TABLE Storage (
83 StorageId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
84 Name TINYBLOB NOT NULL,
85 AutoChanger TINYINT DEFAULT 0,
86 PRIMARY KEY(StorageId)
90 DeviceId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
91 Name TINYBLOB NOT NULL,
92 MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType,
93 StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage,
94 DevMounts INTEGER UNSIGNED DEFAULT 0,
95 DevReadBytes BIGINT UNSIGNED DEFAULT 0,
96 DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
97 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
98 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
99 DevReadTime BIGINT UNSIGNED DEFAULT 0,
100 DevWriteTime BIGINT UNSIGNED DEFAULT 0,
101 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
102 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
103 CleaningDate DATETIME DEFAULT 0,
104 CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
105 PRIMARY KEY(DeviceId)
110 JobId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
111 Job TINYBLOB NOT NULL,
112 Name TINYBLOB NOT NULL,
113 Type BINARY(1) NOT NULL,
114 Level BINARY(1) NOT NULL,
115 ClientId INTEGER DEFAULT 0 REFERENCES Client,
116 JobStatus BINARY(1) NOT NULL,
117 SchedTime DATETIME DEFAULT 0,
118 StartTime DATETIME DEFAULT 0,
119 EndTime DATETIME DEFAULT 0,
120 RealEndTime DATETIME DEFAULT 0,
121 JobTDate BIGINT UNSIGNED DEFAULT 0,
122 VolSessionId INTEGER UNSIGNED DEFAULT 0,
123 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
124 JobFiles INTEGER UNSIGNED DEFAULT 0,
125 JobBytes BIGINT UNSIGNED DEFAULT 0,
126 ReadBytes BIGINT UNSIGNED DEFAULT 0,
127 JobErrors INTEGER UNSIGNED DEFAULT 0,
128 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
129 PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
130 FileSetId INTEGER UNSIGNED DEFAULT 0 REFERENCES FileSet,
131 PriorJobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job,
132 PurgedFiles TINYINT DEFAULT 0,
133 HasBase TINYINT DEFAULT 0,
134 HasCache TINYINT DEFAULT 0,
135 Reviewed TINYINT DEFAULT 0,
141 -- Create a table like Job for long term statistics
142 CREATE TABLE JobHisto (
143 JobId INTEGER UNSIGNED NOT NULL,
144 Job TINYBLOB NOT NULL,
145 Name TINYBLOB NOT NULL,
146 Type BINARY(1) NOT NULL,
147 Level BINARY(1) NOT NULL,
148 ClientId INTEGER DEFAULT 0,
149 JobStatus BINARY(1) NOT NULL,
150 SchedTime DATETIME DEFAULT 0,
151 StartTime DATETIME DEFAULT 0,
152 EndTime DATETIME DEFAULT 0,
153 RealEndTime DATETIME DEFAULT 0,
154 JobTDate BIGINT UNSIGNED DEFAULT 0,
155 VolSessionId INTEGER UNSIGNED DEFAULT 0,
156 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
157 JobFiles INTEGER UNSIGNED DEFAULT 0,
158 JobBytes BIGINT UNSIGNED DEFAULT 0,
159 ReadBytes BIGINT UNSIGNED DEFAULT 0,
160 JobErrors INTEGER UNSIGNED DEFAULT 0,
161 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
162 PoolId INTEGER UNSIGNED DEFAULT 0,
163 FileSetId INTEGER UNSIGNED DEFAULT 0,
164 PriorJobId INTEGER UNSIGNED DEFAULT 0,
165 PurgedFiles TINYINT DEFAULT 0,
166 HasBase TINYINT DEFAULT 0,
167 HasCache TINYINT DEFAULT 0,
168 Reviewed TINYINT DEFAULT 0,
173 CREATE TABLE Location (
174 LocationId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
175 Location TINYBLOB NOT NULL,
176 Cost INTEGER DEFAULT 0,
178 PRIMARY KEY(LocationId)
181 CREATE TABLE LocationLog (
182 LocLogId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
183 Date DATETIME DEFAULT 0,
184 Comment BLOB NOT NULL,
185 MediaId INTEGER UNSIGNED DEFAULT 0 REFERENCES Media,
186 LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location,
187 NewVolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
188 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
190 PRIMARY KEY(LocLogId)
195 CREATE TABLE FileSet (
196 FileSetId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
197 FileSet TINYBLOB NOT NULL,
199 CreateTime DATETIME DEFAULT 0,
200 PRIMARY KEY(FileSetId)
203 CREATE TABLE JobMedia (
204 JobMediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
205 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
206 MediaId INTEGER UNSIGNED NOT NULL REFERENCES Media,
207 FirstIndex INTEGER UNSIGNED DEFAULT 0,
208 LastIndex INTEGER UNSIGNED DEFAULT 0,
209 StartFile INTEGER UNSIGNED DEFAULT 0,
210 EndFile INTEGER UNSIGNED DEFAULT 0,
211 StartBlock INTEGER UNSIGNED DEFAULT 0,
212 EndBlock INTEGER UNSIGNED DEFAULT 0,
213 VolIndex INTEGER UNSIGNED DEFAULT 0,
214 PRIMARY KEY(JobMediaId),
215 INDEX (JobId, MediaId)
220 MediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
221 VolumeName TINYBLOB NOT NULL,
222 Slot INTEGER DEFAULT 0,
223 PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
224 MediaType TINYBLOB NOT NULL,
225 MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType,
226 LabelType TINYINT DEFAULT 0,
227 FirstWritten DATETIME DEFAULT 0,
228 LastWritten DATETIME DEFAULT 0,
229 LabelDate DATETIME DEFAULT 0,
230 VolJobs INTEGER UNSIGNED DEFAULT 0,
231 VolFiles INTEGER UNSIGNED DEFAULT 0,
232 VolBlocks INTEGER UNSIGNED DEFAULT 0,
233 VolMounts INTEGER UNSIGNED DEFAULT 0,
234 VolBytes BIGINT UNSIGNED DEFAULT 0,
235 VolParts INTEGER UNSIGNED DEFAULT 0,
236 VolErrors INTEGER UNSIGNED DEFAULT 0,
237 VolWrites INTEGER UNSIGNED DEFAULT 0,
238 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
239 VolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
240 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
241 Enabled TINYINT DEFAULT 1,
242 Recycle TINYINT DEFAULT 0,
243 ActionOnPurge TINYINT DEFAULT 0,
244 VolRetention BIGINT UNSIGNED DEFAULT 0,
245 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
246 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
247 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
248 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
249 InChanger TINYINT DEFAULT 0,
250 StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage,
251 DeviceId INTEGER UNSIGNED DEFAULT 0 REFERENCES Device,
252 MediaAddressing TINYINT DEFAULT 0,
253 VolReadTime BIGINT UNSIGNED DEFAULT 0,
254 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
255 EndFile INTEGER UNSIGNED DEFAULT 0,
256 EndBlock INTEGER UNSIGNED DEFAULT 0,
257 LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location,
258 RecycleCount INTEGER UNSIGNED DEFAULT 0,
259 InitialWrite DATETIME DEFAULT 0,
260 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
261 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
263 PRIMARY KEY(MediaId),
264 UNIQUE (VolumeName(128)),
269 PoolId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
270 Name TINYBLOB NOT NULL,
271 NumVols INTEGER UNSIGNED DEFAULT 0,
272 MaxVols INTEGER UNSIGNED DEFAULT 0,
273 UseOnce TINYINT DEFAULT 0,
274 UseCatalog TINYINT DEFAULT 0,
275 AcceptAnyVolume TINYINT DEFAULT 0,
276 VolRetention BIGINT UNSIGNED DEFAULT 0,
277 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
278 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
279 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
280 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
281 AutoPrune TINYINT DEFAULT 0,
282 Recycle TINYINT DEFAULT 0,
283 ActionOnPurge TINYINT DEFAULT 0,
284 PoolType ENUM('Backup', 'Copy', 'Cloned', 'Archive', 'Migration', 'Scratch') NOT NULL,
285 LabelType TINYINT DEFAULT 0,
286 LabelFormat TINYBLOB,
287 Enabled TINYINT DEFAULT 1,
288 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
289 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
290 NextPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
291 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
292 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
293 MigrationTime BIGINT UNSIGNED DEFAULT 0,
299 CREATE TABLE Client (
300 ClientId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
301 Name TINYBLOB NOT NULL,
302 Uname TINYBLOB NOT NULL, /* full uname -a of client */
303 AutoPrune TINYINT DEFAULT 0,
304 FileRetention BIGINT UNSIGNED DEFAULT 0,
305 JobRetention BIGINT UNSIGNED DEFAULT 0,
307 PRIMARY KEY(ClientId)
311 LogId INTEGER UNSIGNED AUTO_INCREMENT,
312 JobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job,
313 Time DATETIME DEFAULT 0,
314 LogText BLOB NOT NULL,
320 CREATE TABLE BaseFiles (
321 BaseId INTEGER UNSIGNED AUTO_INCREMENT,
322 BaseJobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
323 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
324 FileId BIGINT UNSIGNED NOT NULL REFERENCES File,
325 FileIndex INTEGER UNSIGNED,
329 CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
331 CREATE TABLE UnsavedFiles (
332 UnsavedId INTEGER UNSIGNED AUTO_INCREMENT,
333 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
334 PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
335 FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
336 PRIMARY KEY (UnsavedId)
341 CREATE TABLE Counters (
342 Counter TINYBLOB NOT NULL,
343 MinValue INTEGER DEFAULT 0,
344 MaxValue INTEGER DEFAULT 0,
345 CurrentValue INTEGER DEFAULT 0,
346 WrapCounter TINYBLOB NOT NULL,
347 PRIMARY KEY (Counter(128))
350 CREATE TABLE CDImages (
351 MediaId INTEGER UNSIGNED NOT NULL,
352 LastBurn DATETIME NOT NULL,
353 PRIMARY KEY (MediaId)
356 CREATE TABLE Status (
357 JobStatus CHAR(1) BINARY NOT NULL,
360 PRIMARY KEY (JobStatus)
363 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
364 ('C', 'Created, not yet running',15),
367 ('T', 'Completed successfully',10),
368 ('E', 'Terminated with errors',25),
369 ('e', 'Non-fatal error',20),
370 ('f', 'Fatal error',100),
371 ('D', 'Verify found differences',15),
372 ('A', 'Canceled by user',90),
373 ('F', 'Waiting for Client',15),
374 ('S', 'Waiting for Storage daemon',15),
375 ('m', 'Waiting for new media',15),
376 ('M', 'Waiting for media mount',15),
377 ('s', 'Waiting for storage resource',15),
378 ('j', 'Waiting for job resource',15),
379 ('c', 'Waiting for client resource',15),
380 ('d', 'Waiting on maximum jobs',15),
381 ('t', 'Waiting on start time',15),
382 ('p', 'Waiting on higher priority jobs',15),
383 ('i', 'Doing batch insert file records',15),
384 ('a', 'SD despooling attributes',15);
386 CREATE TABLE PathHierarchy
388 PathId integer NOT NULL,
389 PPathId integer NOT NULL,
390 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
393 CREATE INDEX pathhierarchy_ppathid
394 ON PathHierarchy (PPathId);
396 CREATE TABLE PathVisibility
398 PathId integer NOT NULL,
399 JobId integer NOT NULL,
401 Files int4 DEFAULT 0,
402 CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
404 CREATE INDEX pathvisibility_jobid
405 ON PathVisibility (JobId);
407 CREATE TABLE Version (
408 VersionId INTEGER UNSIGNED NOT NULL
411 -- Initialize Version
412 INSERT INTO Version (VersionId) VALUES (12);
416 echo "Creation of Bacula MySQL tables succeeded."
418 echo "Creation of Bacula MySQL tables failed."