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