3 # shell script to create Bacula MySQL tables
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,
42 INDEX (JobId, PathId, FilenameId)
46 # Possibly add one or more of the following indexes
47 # to the above File table if your Verifies are
52 # INDEX (FilenameId, PathId)
56 CREATE TABLE MediaType (
57 MediaTypeId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
58 MediaType TINYBLOB NOT NULL,
59 ReadOnly TINYINT DEFAULT 0,
60 PRIMARY KEY(MediaTypeId)
63 CREATE TABLE Storage (
64 StorageId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
65 Name TINYBLOB NOT NULL,
66 AutoChanger TINYINT DEFAULT 0,
67 PRIMARY KEY(StorageId)
71 DeviceId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
72 Name TINYBLOB NOT NULL,
73 MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType,
74 StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage,
75 DevMounts INTEGER UNSIGNED DEFAULT 0,
76 DevReadBytes BIGINT UNSIGNED DEFAULT 0,
77 DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
78 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
79 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
80 DevReadTime BIGINT UNSIGNED DEFAULT 0,
81 DevWriteTime BIGINT UNSIGNED DEFAULT 0,
82 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
83 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
84 CleaningDate DATETIME DEFAULT 0,
85 CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
91 JobId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
92 Job TINYBLOB NOT NULL,
93 Name TINYBLOB NOT NULL,
94 Type BINARY(1) NOT NULL,
95 Level BINARY(1) NOT NULL,
96 ClientId INTEGER DEFAULT 0 REFERENCES Client,
97 JobStatus BINARY(1) NOT NULL,
98 SchedTime DATETIME DEFAULT 0,
99 StartTime DATETIME DEFAULT 0,
100 EndTime DATETIME DEFAULT 0,
101 RealEndTime DATETIME DEFAULT 0,
102 JobTDate BIGINT UNSIGNED DEFAULT 0,
103 VolSessionId INTEGER UNSIGNED DEFAULT 0,
104 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
105 JobFiles INTEGER UNSIGNED DEFAULT 0,
106 JobBytes BIGINT UNSIGNED DEFAULT 0,
107 JobErrors INTEGER UNSIGNED DEFAULT 0,
108 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
109 PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
110 FileSetId INTEGER UNSIGNED DEFAULT 0 REFERENCES FileSet,
111 PriorJobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job,
112 PurgedFiles TINYINT DEFAULT 0,
113 HasBase TINYINT DEFAULT 0,
119 CREATE TABLE Location (
120 LocationId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
121 Location TINYBLOB NOT NULL,
122 Cost INTEGER DEFAULT 0,
124 PRIMARY KEY(LocationId)
127 CREATE TABLE LocationLog (
128 LocLogId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
129 Date DATETIME DEFAULT 0,
130 Comment BLOB NOT NULL,
131 MediaId INTEGER UNSIGNED DEFAULT 0 REFERENCES Media,
132 LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location,
133 NewVolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
134 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
136 PRIMARY KEY(LocLogId)
141 CREATE TABLE FileSet (
142 FileSetId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
143 FileSet TINYBLOB NOT NULL,
145 CreateTime DATETIME DEFAULT 0,
146 PRIMARY KEY(FileSetId)
149 CREATE TABLE JobMedia (
150 JobMediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
151 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
152 MediaId INTEGER UNSIGNED NOT NULL REFERENCES Media,
153 FirstIndex INTEGER UNSIGNED DEFAULT 0,
154 LastIndex INTEGER UNSIGNED DEFAULT 0,
155 StartFile INTEGER UNSIGNED DEFAULT 0,
156 EndFile INTEGER UNSIGNED DEFAULT 0,
157 StartBlock INTEGER UNSIGNED DEFAULT 0,
158 EndBlock INTEGER UNSIGNED DEFAULT 0,
159 VolIndex INTEGER UNSIGNED DEFAULT 0,
160 Copy INTEGER UNSIGNED DEFAULT 0,
161 Stripe INTEGER UNSIGNED DEFAULT 0,
162 PRIMARY KEY(JobMediaId),
163 INDEX (JobId, MediaId)
168 MediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
169 VolumeName TINYBLOB NOT NULL,
170 Slot INTEGER DEFAULT 0,
171 PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
172 MediaType TINYBLOB NOT NULL,
173 MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType,
174 LabelType TINYINT DEFAULT 0,
175 FirstWritten DATETIME DEFAULT 0,
176 LastWritten DATETIME DEFAULT 0,
177 LabelDate DATETIME DEFAULT 0,
178 VolJobs INTEGER UNSIGNED DEFAULT 0,
179 VolFiles INTEGER UNSIGNED DEFAULT 0,
180 VolBlocks INTEGER UNSIGNED DEFAULT 0,
181 VolMounts INTEGER UNSIGNED DEFAULT 0,
182 VolBytes BIGINT UNSIGNED DEFAULT 0,
183 VolParts INTEGER UNSIGNED DEFAULT 0,
184 VolErrors INTEGER UNSIGNED DEFAULT 0,
185 VolWrites INTEGER UNSIGNED DEFAULT 0,
186 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
187 VolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
188 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
189 Enabled TINYINT DEFAULT 1,
190 Recycle TINYINT DEFAULT 0,
191 VolRetention BIGINT UNSIGNED DEFAULT 0,
192 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
193 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
194 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
195 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
196 InChanger TINYINT DEFAULT 0,
197 StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage,
198 DeviceId INTEGER UNSIGNED DEFAULT 0 REFERENCES Device,
199 MediaAddressing TINYINT DEFAULT 0,
200 VolReadTime BIGINT UNSIGNED DEFAULT 0,
201 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
202 EndFile INTEGER UNSIGNED DEFAULT 0,
203 EndBlock INTEGER UNSIGNED DEFAULT 0,
204 LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location,
205 RecycleCount INTEGER UNSIGNED DEFAULT 0,
206 InitialWrite DATETIME DEFAULT 0,
207 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
208 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
210 PRIMARY KEY(MediaId),
214 CREATE INDEX inx8 ON Media (PoolId);
219 PoolId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
220 Name TINYBLOB NOT NULL,
221 NumVols INTEGER UNSIGNED DEFAULT 0,
222 MaxVols INTEGER UNSIGNED DEFAULT 0,
223 UseOnce TINYINT DEFAULT 0,
224 UseCatalog TINYINT DEFAULT 0,
225 AcceptAnyVolume TINYINT DEFAULT 0,
226 VolRetention BIGINT UNSIGNED DEFAULT 0,
227 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
228 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
229 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
230 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
231 AutoPrune TINYINT DEFAULT 0,
232 Recycle TINYINT DEFAULT 0,
233 PoolType ENUM('Backup', 'Copy', 'Cloned', 'Archive', 'Migration', 'Scratch') NOT NULL,
234 LabelType TINYINT DEFAULT 0,
235 LabelFormat TINYBLOB,
236 Enabled TINYINT DEFAULT 1,
237 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
238 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
239 NextPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
240 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
241 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
242 MigrationTime BIGINT UNSIGNED DEFAULT 0,
248 CREATE TABLE Client (
249 ClientId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
250 Name TINYBLOB NOT NULL,
251 Uname TINYBLOB NOT NULL, /* full uname -a of client */
252 AutoPrune TINYINT DEFAULT 0,
253 FileRetention BIGINT UNSIGNED DEFAULT 0,
254 JobRetention BIGINT UNSIGNED DEFAULT 0,
256 PRIMARY KEY(ClientId)
260 LogId INTEGER UNSIGNED AUTO_INCREMENT,
261 JobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job,
262 Time DATETIME DEFAULT 0,
263 LogText BLOB NOT NULL,
269 CREATE TABLE BaseFiles (
270 BaseId INTEGER UNSIGNED AUTO_INCREMENT,
271 BaseJobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
272 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
273 FileId INTEGER UNSIGNED NOT NULL REFERENCES File,
274 FileIndex INTEGER UNSIGNED,
278 CREATE TABLE UnsavedFiles (
279 UnsavedId INTEGER UNSIGNED AUTO_INCREMENT,
280 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
281 PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
282 FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
283 PRIMARY KEY (UnsavedId)
288 CREATE TABLE Counters (
289 Counter TINYBLOB NOT NULL,
290 MinValue INTEGER DEFAULT 0,
291 MaxValue INTEGER DEFAULT 0,
292 CurrentValue INTEGER DEFAULT 0,
293 WrapCounter TINYBLOB NOT NULL,
294 PRIMARY KEY (Counter(128))
297 CREATE TABLE CDImages (
298 MediaId INTEGER UNSIGNED NOT NULL,
299 LastBurn DATETIME NOT NULL,
300 PRIMARY KEY (MediaId)
303 CREATE TABLE Status (
304 JobStatus CHAR(1) BINARY NOT NULL,
306 PRIMARY KEY (JobStatus)
309 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
310 ('C', 'Created, not yet running'),
313 ('T', 'Completed successfully'),
314 ('E', 'Terminated with errors'),
315 ('e', 'Non-fatal error'),
316 ('f', 'Fatal error'),
317 ('D', 'Verify found differences'),
318 ('A', 'Canceled by user'),
319 ('F', 'Waiting for Client'),
320 ('S', 'Waiting for Storage daemon'),
321 ('m', 'Waiting for new media'),
322 ('M', 'Waiting for media mount'),
323 ('s', 'Waiting for storage resource'),
324 ('j', 'Waiting for job resource'),
325 ('c', 'Waiting for client resource'),
326 ('d', 'Waiting on maximum jobs'),
327 ('t', 'Waiting on start time'),
328 ('p', 'Waiting on higher priority jobs');
330 CREATE TABLE Version (
331 VersionId INTEGER UNSIGNED NOT NULL
334 -- Initialize Version
335 INSERT INTO Version (VersionId) VALUES (10);
339 echo "Creation of Bacula MySQL tables succeeded."
341 echo "Creation of Bacula MySQL tables failed."