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)
49 # Possibly add one or more of the following indexes
50 # to the above File table if your Verifies are
55 # INDEX (FilenameId, PathId)
59 CREATE TABLE MediaType (
60 MediaTypeId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
61 MediaType TINYBLOB NOT NULL,
62 ReadOnly TINYINT DEFAULT 0,
63 PRIMARY KEY(MediaTypeId)
66 CREATE TABLE Storage (
67 StorageId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
68 Name TINYBLOB NOT NULL,
69 AutoChanger TINYINT DEFAULT 0,
70 PRIMARY KEY(StorageId)
74 DeviceId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
75 Name TINYBLOB NOT NULL,
76 MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType,
77 StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage,
78 DevMounts INTEGER UNSIGNED DEFAULT 0,
79 DevReadBytes BIGINT UNSIGNED DEFAULT 0,
80 DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
81 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
82 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
83 DevReadTime BIGINT UNSIGNED DEFAULT 0,
84 DevWriteTime BIGINT UNSIGNED DEFAULT 0,
85 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
86 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
87 CleaningDate DATETIME DEFAULT 0,
88 CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
94 JobId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
95 Job TINYBLOB NOT NULL,
96 Name TINYBLOB NOT NULL,
97 Type BINARY(1) NOT NULL,
98 Level BINARY(1) NOT NULL,
99 ClientId INTEGER DEFAULT 0 REFERENCES Client,
100 JobStatus BINARY(1) NOT NULL,
101 SchedTime DATETIME DEFAULT 0,
102 StartTime DATETIME DEFAULT 0,
103 EndTime DATETIME DEFAULT 0,
104 RealEndTime DATETIME DEFAULT 0,
105 JobTDate BIGINT UNSIGNED DEFAULT 0,
106 VolSessionId INTEGER UNSIGNED DEFAULT 0,
107 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
108 JobFiles INTEGER UNSIGNED DEFAULT 0,
109 JobBytes BIGINT UNSIGNED DEFAULT 0,
110 ReadBytes BIGINT UNSIGNED DEFAULT 0,
111 JobErrors INTEGER UNSIGNED DEFAULT 0,
112 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
113 PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
114 FileSetId INTEGER UNSIGNED DEFAULT 0 REFERENCES FileSet,
115 PriorJobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job,
116 PurgedFiles TINYINT DEFAULT 0,
117 HasBase TINYINT DEFAULT 0,
118 HasCache TINYINT DEFAULT 0,
119 Reviewed TINYINT DEFAULT 0,
125 -- Create a table like Job for long term statistics
126 CREATE TABLE JobHisto (
127 JobId INTEGER UNSIGNED NOT NULL,
128 Job TINYBLOB NOT NULL,
129 Name TINYBLOB NOT NULL,
130 Type BINARY(1) NOT NULL,
131 Level BINARY(1) NOT NULL,
132 ClientId INTEGER DEFAULT 0,
133 JobStatus BINARY(1) NOT NULL,
134 SchedTime DATETIME DEFAULT 0,
135 StartTime DATETIME DEFAULT 0,
136 EndTime DATETIME DEFAULT 0,
137 RealEndTime DATETIME DEFAULT 0,
138 JobTDate BIGINT UNSIGNED DEFAULT 0,
139 VolSessionId INTEGER UNSIGNED DEFAULT 0,
140 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
141 JobFiles INTEGER UNSIGNED DEFAULT 0,
142 JobBytes BIGINT UNSIGNED DEFAULT 0,
143 ReadBytes BIGINT UNSIGNED DEFAULT 0,
144 JobErrors INTEGER UNSIGNED DEFAULT 0,
145 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
146 PoolId INTEGER UNSIGNED DEFAULT 0,
147 FileSetId INTEGER UNSIGNED DEFAULT 0,
148 PriorJobId INTEGER UNSIGNED DEFAULT 0,
149 PurgedFiles TINYINT DEFAULT 0,
150 HasBase TINYINT DEFAULT 0,
151 HasCache TINYINT DEFAULT 0,
152 Reviewed TINYINT DEFAULT 0,
157 CREATE TABLE Location (
158 LocationId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
159 Location TINYBLOB NOT NULL,
160 Cost INTEGER DEFAULT 0,
162 PRIMARY KEY(LocationId)
165 CREATE TABLE LocationLog (
166 LocLogId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
167 Date DATETIME DEFAULT 0,
168 Comment BLOB NOT NULL,
169 MediaId INTEGER UNSIGNED DEFAULT 0 REFERENCES Media,
170 LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location,
171 NewVolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
172 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
174 PRIMARY KEY(LocLogId)
179 CREATE TABLE FileSet (
180 FileSetId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
181 FileSet TINYBLOB NOT NULL,
183 CreateTime DATETIME DEFAULT 0,
184 PRIMARY KEY(FileSetId)
187 CREATE TABLE JobMedia (
188 JobMediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
189 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
190 MediaId INTEGER UNSIGNED NOT NULL REFERENCES Media,
191 FirstIndex INTEGER UNSIGNED DEFAULT 0,
192 LastIndex INTEGER UNSIGNED DEFAULT 0,
193 StartFile INTEGER UNSIGNED DEFAULT 0,
194 EndFile INTEGER UNSIGNED DEFAULT 0,
195 StartBlock INTEGER UNSIGNED DEFAULT 0,
196 EndBlock INTEGER UNSIGNED DEFAULT 0,
197 VolIndex INTEGER UNSIGNED DEFAULT 0,
198 PRIMARY KEY(JobMediaId),
199 INDEX (JobId, MediaId)
204 MediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
205 VolumeName TINYBLOB NOT NULL,
206 Slot INTEGER DEFAULT 0,
207 PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
208 MediaType TINYBLOB NOT NULL,
209 MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType,
210 LabelType TINYINT DEFAULT 0,
211 FirstWritten DATETIME DEFAULT 0,
212 LastWritten DATETIME DEFAULT 0,
213 LabelDate DATETIME DEFAULT 0,
214 VolJobs INTEGER UNSIGNED DEFAULT 0,
215 VolFiles INTEGER UNSIGNED DEFAULT 0,
216 VolBlocks INTEGER UNSIGNED DEFAULT 0,
217 VolMounts INTEGER UNSIGNED DEFAULT 0,
218 VolBytes BIGINT UNSIGNED DEFAULT 0,
219 VolParts INTEGER UNSIGNED DEFAULT 0,
220 VolErrors INTEGER UNSIGNED DEFAULT 0,
221 VolWrites INTEGER UNSIGNED DEFAULT 0,
222 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
223 VolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
224 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
225 Enabled TINYINT DEFAULT 1,
226 Recycle TINYINT DEFAULT 0,
227 ActionOnPurge TINYINT DEFAULT 0,
228 VolRetention BIGINT UNSIGNED DEFAULT 0,
229 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
230 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
231 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
232 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
233 InChanger TINYINT DEFAULT 0,
234 StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage,
235 DeviceId INTEGER UNSIGNED DEFAULT 0 REFERENCES Device,
236 MediaAddressing TINYINT DEFAULT 0,
237 VolReadTime BIGINT UNSIGNED DEFAULT 0,
238 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
239 EndFile INTEGER UNSIGNED DEFAULT 0,
240 EndBlock INTEGER UNSIGNED DEFAULT 0,
241 LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location,
242 RecycleCount INTEGER UNSIGNED DEFAULT 0,
243 InitialWrite DATETIME DEFAULT 0,
244 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
245 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
247 PRIMARY KEY(MediaId),
248 UNIQUE (VolumeName(128)),
253 PoolId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
254 Name TINYBLOB NOT NULL,
255 NumVols INTEGER UNSIGNED DEFAULT 0,
256 MaxVols INTEGER UNSIGNED DEFAULT 0,
257 UseOnce TINYINT DEFAULT 0,
258 UseCatalog TINYINT DEFAULT 0,
259 AcceptAnyVolume TINYINT DEFAULT 0,
260 VolRetention BIGINT UNSIGNED DEFAULT 0,
261 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
262 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
263 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
264 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
265 AutoPrune TINYINT DEFAULT 0,
266 Recycle TINYINT DEFAULT 0,
267 ActionOnPurge TINYINT DEFAULT 0,
268 PoolType ENUM('Backup', 'Copy', 'Cloned', 'Archive', 'Migration', 'Scratch') NOT NULL,
269 LabelType TINYINT DEFAULT 0,
270 LabelFormat TINYBLOB,
271 Enabled TINYINT DEFAULT 1,
272 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
273 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
274 NextPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
275 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
276 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
277 MigrationTime BIGINT UNSIGNED DEFAULT 0,
283 CREATE TABLE Client (
284 ClientId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
285 Name TINYBLOB NOT NULL,
286 Uname TINYBLOB NOT NULL, /* full uname -a of client */
287 AutoPrune TINYINT DEFAULT 0,
288 FileRetention BIGINT UNSIGNED DEFAULT 0,
289 JobRetention BIGINT UNSIGNED DEFAULT 0,
291 PRIMARY KEY(ClientId)
295 LogId INTEGER UNSIGNED AUTO_INCREMENT,
296 JobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job,
297 Time DATETIME DEFAULT 0,
298 LogText BLOB NOT NULL,
304 CREATE TABLE BaseFiles (
305 BaseId INTEGER UNSIGNED AUTO_INCREMENT,
306 BaseJobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
307 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
308 FileId BIGINT UNSIGNED NOT NULL REFERENCES File,
309 FileIndex INTEGER UNSIGNED,
313 CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
315 CREATE TABLE UnsavedFiles (
316 UnsavedId INTEGER UNSIGNED AUTO_INCREMENT,
317 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
318 PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
319 FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
320 PRIMARY KEY (UnsavedId)
325 CREATE TABLE Counters (
326 Counter TINYBLOB NOT NULL,
327 MinValue INTEGER DEFAULT 0,
328 MaxValue INTEGER DEFAULT 0,
329 CurrentValue INTEGER DEFAULT 0,
330 WrapCounter TINYBLOB NOT NULL,
331 PRIMARY KEY (Counter(128))
334 CREATE TABLE CDImages (
335 MediaId INTEGER UNSIGNED NOT NULL,
336 LastBurn DATETIME NOT NULL,
337 PRIMARY KEY (MediaId)
340 CREATE TABLE Status (
341 JobStatus CHAR(1) BINARY NOT NULL,
344 PRIMARY KEY (JobStatus)
347 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
348 ('C', 'Created, not yet running',15),
351 ('T', 'Completed successfully',10),
352 ('E', 'Terminated with errors',25),
353 ('e', 'Non-fatal error',20),
354 ('f', 'Fatal error',100),
355 ('D', 'Verify found differences',15),
356 ('A', 'Canceled by user',90),
357 ('F', 'Waiting for Client',15),
358 ('S', 'Waiting for Storage daemon',15),
359 ('m', 'Waiting for new media',15),
360 ('M', 'Waiting for media mount',15),
361 ('s', 'Waiting for storage resource',15),
362 ('j', 'Waiting for job resource',15),
363 ('c', 'Waiting for client resource',15),
364 ('d', 'Waiting on maximum jobs',15),
365 ('t', 'Waiting on start time',15),
366 ('p', 'Waiting on higher priority jobs',15),
367 ('i', 'Doing batch insert file records',15),
368 ('a', 'SD despooling attributes',15);
370 CREATE TABLE PathHierarchy
372 PathId integer NOT NULL,
373 PPathId integer NOT NULL,
374 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
377 CREATE INDEX pathhierarchy_ppathid
378 ON PathHierarchy (PPathId);
380 CREATE TABLE PathVisibility
382 PathId integer NOT NULL,
383 JobId integer NOT NULL,
385 Files int4 DEFAULT 0,
386 CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
388 CREATE INDEX pathvisibility_jobid
389 ON PathVisibility (JobId);
391 CREATE TABLE Version (
392 VersionId INTEGER UNSIGNED NOT NULL
395 -- Initialize Version
396 INSERT INTO Version (VersionId) VALUES (12);
400 echo "Creation of Bacula MySQL tables succeeded."
402 echo "Creation of Bacula MySQL tables failed."