3 # shell script to create Bacula MySQL tables
6 db_name=${db_name-@db_name@}
8 if $bindir/mysql $* -f <<END-OF-DATA
11 -- Note, we use BLOB rather than TEXT because in MySQL,
12 -- BLOBs are identical to TEXT except that BLOB is case
13 -- sensitive in sorts, which is what we want, and TEXT
14 -- is case insensitive.
16 CREATE TABLE Filename (
17 FilenameId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
19 PRIMARY KEY(FilenameId),
24 PathId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
32 FileId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
33 FileIndex INTEGER UNSIGNED DEFAULT 0,
34 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
35 PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
36 FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
37 MarkId INTEGER UNSIGNED DEFAULT 0,
38 LStat TINYBLOB NOT NULL,
41 INDEX (JobId, PathId, FilenameId)
45 # Possibly add one or more of the following indexes
46 # to the above File table if your Verifies are
51 # INDEX (FilenameId, PathId)
54 CREATE TABLE MediaType (
55 MediaTypeId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
56 MediaType TINYBLOB NOT NULL,
57 ReadOnly TINYINT DEFAULT 0,
58 PRIMARY KEY(MediaTypeId)
61 CREATE TABLE Storage (
62 StorageId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
63 Name TINYBLOB NOT NULL,
64 AutoChanger TINYINT DEFAULT 0,
65 PRIMARY KEY(StorageId)
69 DeviceId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
70 Name TINYBLOB NOT NULL,
71 MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType,
72 StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage,
73 DevMounts INTEGER UNSIGNED DEFAULT 0,
74 DevReadBytes BIGINT UNSIGNED DEFAULT 0,
75 DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
76 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
77 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
78 DevReadTime BIGINT UNSIGNED DEFAULT 0,
79 DevWriteTime BIGINT UNSIGNED DEFAULT 0,
80 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
81 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
82 CleaningDate DATETIME DEFAULT 0,
83 CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
89 JobId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
90 Job TINYBLOB NOT NULL,
91 Name TINYBLOB NOT NULL,
92 Type BINARY(1) NOT NULL,
93 Level BINARY(1) NOT NULL,
94 ClientId INTEGER DEFAULT 0 REFERENCES Client,
95 JobStatus BINARY(1) NOT NULL,
96 SchedTime DATETIME DEFAULT 0,
97 StartTime DATETIME DEFAULT 0,
98 EndTime DATETIME DEFAULT 0,
99 RealEndTime DATETIME DEFAULT 0,
100 JobTDate BIGINT UNSIGNED DEFAULT 0,
101 VolSessionId INTEGER UNSIGNED DEFAULT 0,
102 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
103 JobFiles INTEGER UNSIGNED DEFAULT 0,
104 JobBytes BIGINT UNSIGNED DEFAULT 0,
105 JobErrors INTEGER UNSIGNED DEFAULT 0,
106 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
107 PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
108 FileSetId INTEGER UNSIGNED DEFAULT 0 REFERENCES FileSet,
109 PriorJobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job,
110 PurgedFiles TINYINT DEFAULT 0,
111 HasBase TINYINT DEFAULT 0,
117 CREATE TABLE Location (
118 LocationId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
119 Location TINYBLOB NOT NULL,
120 Cost INTEGER DEFAULT 0,
122 PRIMARY KEY(LocationId)
125 CREATE TABLE LocationLog (
126 LocLogId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
127 Date DATETIME DEFAULT 0,
128 Comment BLOB NOT NULL,
129 MediaId INTEGER UNSIGNED DEFAULT 0 REFERENCES Media,
130 LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location,
131 NewVolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
132 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
134 PRIMARY KEY(LocLogId)
139 CREATE TABLE FileSet (
140 FileSetId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
141 FileSet TINYBLOB NOT NULL,
143 CreateTime DATETIME DEFAULT 0,
144 PRIMARY KEY(FileSetId)
147 CREATE TABLE JobMedia (
148 JobMediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
149 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
150 MediaId INTEGER UNSIGNED NOT NULL REFERENCES Media,
151 FirstIndex INTEGER UNSIGNED DEFAULT 0,
152 LastIndex INTEGER UNSIGNED DEFAULT 0,
153 StartFile INTEGER UNSIGNED DEFAULT 0,
154 EndFile INTEGER UNSIGNED DEFAULT 0,
155 StartBlock INTEGER UNSIGNED DEFAULT 0,
156 EndBlock INTEGER UNSIGNED DEFAULT 0,
157 VolIndex INTEGER UNSIGNED DEFAULT 0,
158 Copy INTEGER UNSIGNED DEFAULT 0,
159 Stripe INTEGER UNSIGNED DEFAULT 0,
160 PRIMARY KEY(JobMediaId),
161 INDEX (JobId, MediaId)
166 MediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
167 VolumeName TINYBLOB NOT NULL,
168 Slot INTEGER DEFAULT 0,
169 PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
170 MediaType TINYBLOB NOT NULL,
171 MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType,
172 LabelType TINYINT DEFAULT 0,
173 FirstWritten DATETIME DEFAULT 0,
174 LastWritten DATETIME DEFAULT 0,
175 LabelDate DATETIME DEFAULT 0,
176 VolJobs INTEGER UNSIGNED DEFAULT 0,
177 VolFiles INTEGER UNSIGNED DEFAULT 0,
178 VolBlocks INTEGER UNSIGNED DEFAULT 0,
179 VolMounts INTEGER UNSIGNED DEFAULT 0,
180 VolBytes BIGINT UNSIGNED DEFAULT 0,
181 VolParts INTEGER UNSIGNED DEFAULT 0,
182 VolErrors INTEGER UNSIGNED DEFAULT 0,
183 VolWrites INTEGER UNSIGNED DEFAULT 0,
184 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
185 VolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
186 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
187 Enabled TINYINT DEFAULT 1,
188 Recycle TINYINT DEFAULT 0,
189 VolRetention BIGINT UNSIGNED DEFAULT 0,
190 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
191 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
192 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
193 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
194 InChanger TINYINT DEFAULT 0,
195 StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage,
196 DeviceId INTEGER UNSIGNED DEFAULT 0 REFERENCES Device,
197 MediaAddressing TINYINT DEFAULT 0,
198 VolReadTime BIGINT UNSIGNED DEFAULT 0,
199 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
200 EndFile INTEGER UNSIGNED DEFAULT 0,
201 EndBlock INTEGER UNSIGNED DEFAULT 0,
202 LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location,
203 RecycleCount INTEGER UNSIGNED DEFAULT 0,
204 InitialWrite DATETIME DEFAULT 0,
205 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
206 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
208 PRIMARY KEY(MediaId),
212 CREATE INDEX inx8 ON Media (PoolId);
217 PoolId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
218 Name TINYBLOB NOT NULL,
219 NumVols INTEGER UNSIGNED DEFAULT 0,
220 MaxVols INTEGER UNSIGNED DEFAULT 0,
221 UseOnce TINYINT DEFAULT 0,
222 UseCatalog TINYINT DEFAULT 0,
223 AcceptAnyVolume TINYINT DEFAULT 0,
224 VolRetention BIGINT UNSIGNED DEFAULT 0,
225 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
226 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
227 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
228 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
229 AutoPrune TINYINT DEFAULT 0,
230 Recycle TINYINT DEFAULT 0,
231 PoolType ENUM('Backup', 'Copy', 'Cloned', 'Archive', 'Migration', 'Scratch') NOT NULL,
232 LabelType TINYINT DEFAULT 0,
233 LabelFormat TINYBLOB,
234 Enabled TINYINT DEFAULT 1,
235 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
236 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
237 NextPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
238 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
239 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
240 MigrationTime BIGINT UNSIGNED DEFAULT 0,
246 CREATE TABLE Client (
247 ClientId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
248 Name TINYBLOB NOT NULL,
249 Uname TINYBLOB NOT NULL, /* full uname -a of client */
250 AutoPrune TINYINT DEFAULT 0,
251 FileRetention BIGINT UNSIGNED DEFAULT 0,
252 JobRetention BIGINT UNSIGNED DEFAULT 0,
254 PRIMARY KEY(ClientId)
258 LogId INTEGER UNSIGNED AUTO_INCREMENT,
259 JobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job,
260 Time DATETIME DEFAULT 0,
261 LogText BLOB NOT NULL,
267 CREATE TABLE BaseFiles (
268 BaseId INTEGER UNSIGNED AUTO_INCREMENT,
269 BaseJobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
270 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
271 FileId INTEGER UNSIGNED NOT NULL REFERENCES File,
272 FileIndex INTEGER UNSIGNED,
276 CREATE TABLE UnsavedFiles (
277 UnsavedId INTEGER UNSIGNED AUTO_INCREMENT,
278 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
279 PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
280 FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
281 PRIMARY KEY (UnsavedId)
286 CREATE TABLE Counters (
287 Counter TINYBLOB NOT NULL,
288 MinValue INTEGER DEFAULT 0,
289 MaxValue INTEGER DEFAULT 0,
290 CurrentValue INTEGER DEFAULT 0,
291 WrapCounter TINYBLOB NOT NULL,
292 PRIMARY KEY (Counter(128))
295 CREATE TABLE CDImages (
296 MediaId INTEGER UNSIGNED NOT NULL,
297 LastBurn DATETIME NOT NULL,
298 PRIMARY KEY (MediaId)
301 CREATE TABLE Status (
302 JobStatus CHAR(1) BINARY NOT NULL,
304 PRIMARY KEY (JobStatus)
307 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
308 ('C', 'Created, not yet running'),
311 ('T', 'Completed successfully'),
312 ('E', 'Terminated with errors'),
313 ('e', 'Non-fatal error'),
314 ('f', 'Fatal error'),
315 ('D', 'Verify found differences'),
316 ('A', 'Canceled by user'),
317 ('F', 'Waiting for Client'),
318 ('S', 'Waiting for Storage daemon'),
319 ('m', 'Waiting for new media'),
320 ('M', 'Waiting for media mount'),
321 ('s', 'Waiting for storage resource'),
322 ('j', 'Waiting for job resource'),
323 ('c', 'Waiting for client resource'),
324 ('d', 'Waiting on maximum jobs'),
325 ('t', 'Waiting on start time'),
326 ('p', 'Waiting on higher priority jobs');
328 CREATE TABLE Version (
329 VersionId INTEGER UNSIGNED NOT NULL
332 -- Initialize Version
333 INSERT INTO Version (VersionId) VALUES (10);
337 echo "Creation of Bacula MySQL tables succeeded."
339 echo "Creation of Bacula MySQL tables failed."