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