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,
118 -- Create a table like Job for long term statistics
119 CREATE TABLE JobStat (LIKE Job);
121 CREATE TABLE Location (
122 LocationId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
123 Location TINYBLOB NOT NULL,
124 Cost INTEGER DEFAULT 0,
126 PRIMARY KEY(LocationId)
129 CREATE TABLE LocationLog (
130 LocLogId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
131 Date DATETIME DEFAULT 0,
132 Comment BLOB NOT NULL,
133 MediaId INTEGER UNSIGNED DEFAULT 0 REFERENCES Media,
134 LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location,
135 NewVolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
136 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
138 PRIMARY KEY(LocLogId)
143 CREATE TABLE FileSet (
144 FileSetId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
145 FileSet TINYBLOB NOT NULL,
147 CreateTime DATETIME DEFAULT 0,
148 PRIMARY KEY(FileSetId)
151 CREATE TABLE JobMedia (
152 JobMediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
153 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
154 MediaId INTEGER UNSIGNED NOT NULL REFERENCES Media,
155 FirstIndex INTEGER UNSIGNED DEFAULT 0,
156 LastIndex INTEGER UNSIGNED DEFAULT 0,
157 StartFile INTEGER UNSIGNED DEFAULT 0,
158 EndFile INTEGER UNSIGNED DEFAULT 0,
159 StartBlock INTEGER UNSIGNED DEFAULT 0,
160 EndBlock INTEGER UNSIGNED DEFAULT 0,
161 VolIndex INTEGER UNSIGNED DEFAULT 0,
162 Copy INTEGER UNSIGNED DEFAULT 0,
163 Stripe INTEGER UNSIGNED DEFAULT 0,
164 PRIMARY KEY(JobMediaId),
165 INDEX (JobId, MediaId)
170 MediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
171 VolumeName TINYBLOB NOT NULL,
172 Slot INTEGER DEFAULT 0,
173 PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
174 MediaType TINYBLOB NOT NULL,
175 MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType,
176 LabelType TINYINT DEFAULT 0,
177 FirstWritten DATETIME DEFAULT 0,
178 LastWritten DATETIME DEFAULT 0,
179 LabelDate DATETIME DEFAULT 0,
180 VolJobs INTEGER UNSIGNED DEFAULT 0,
181 VolFiles INTEGER UNSIGNED DEFAULT 0,
182 VolBlocks INTEGER UNSIGNED DEFAULT 0,
183 VolMounts INTEGER UNSIGNED DEFAULT 0,
184 VolBytes BIGINT UNSIGNED DEFAULT 0,
185 VolParts INTEGER UNSIGNED DEFAULT 0,
186 VolErrors INTEGER UNSIGNED DEFAULT 0,
187 VolWrites INTEGER UNSIGNED DEFAULT 0,
188 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
189 VolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
190 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
191 Enabled TINYINT DEFAULT 1,
192 Recycle TINYINT DEFAULT 0,
193 VolRetention BIGINT UNSIGNED DEFAULT 0,
194 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
195 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
196 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
197 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
198 InChanger TINYINT DEFAULT 0,
199 StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage,
200 DeviceId INTEGER UNSIGNED DEFAULT 0 REFERENCES Device,
201 MediaAddressing TINYINT DEFAULT 0,
202 VolReadTime BIGINT UNSIGNED DEFAULT 0,
203 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
204 EndFile INTEGER UNSIGNED DEFAULT 0,
205 EndBlock INTEGER UNSIGNED DEFAULT 0,
206 LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location,
207 RecycleCount INTEGER UNSIGNED DEFAULT 0,
208 InitialWrite DATETIME DEFAULT 0,
209 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
210 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
212 PRIMARY KEY(MediaId),
216 CREATE INDEX inx8 ON Media (PoolId);
221 PoolId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
222 Name TINYBLOB NOT NULL,
223 NumVols INTEGER UNSIGNED DEFAULT 0,
224 MaxVols INTEGER UNSIGNED DEFAULT 0,
225 UseOnce TINYINT DEFAULT 0,
226 UseCatalog TINYINT DEFAULT 0,
227 AcceptAnyVolume 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 AutoPrune TINYINT DEFAULT 0,
234 Recycle TINYINT DEFAULT 0,
235 PoolType ENUM('Backup', 'Copy', 'Cloned', 'Archive', 'Migration', 'Scratch') NOT NULL,
236 LabelType TINYINT DEFAULT 0,
237 LabelFormat TINYBLOB,
238 Enabled TINYINT DEFAULT 1,
239 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
240 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
241 NextPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
242 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
243 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
244 MigrationTime BIGINT UNSIGNED DEFAULT 0,
250 CREATE TABLE Client (
251 ClientId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
252 Name TINYBLOB NOT NULL,
253 Uname TINYBLOB NOT NULL, /* full uname -a of client */
254 AutoPrune TINYINT DEFAULT 0,
255 FileRetention BIGINT UNSIGNED DEFAULT 0,
256 JobRetention BIGINT UNSIGNED DEFAULT 0,
258 PRIMARY KEY(ClientId)
262 LogId INTEGER UNSIGNED AUTO_INCREMENT,
263 JobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job,
264 Time DATETIME DEFAULT 0,
265 LogText BLOB NOT NULL,
271 CREATE TABLE BaseFiles (
272 BaseId INTEGER UNSIGNED AUTO_INCREMENT,
273 BaseJobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
274 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
275 FileId INTEGER UNSIGNED NOT NULL REFERENCES File,
276 FileIndex INTEGER UNSIGNED,
280 CREATE TABLE UnsavedFiles (
281 UnsavedId INTEGER UNSIGNED AUTO_INCREMENT,
282 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
283 PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
284 FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
285 PRIMARY KEY (UnsavedId)
290 CREATE TABLE Counters (
291 Counter TINYBLOB NOT NULL,
292 MinValue INTEGER DEFAULT 0,
293 MaxValue INTEGER DEFAULT 0,
294 CurrentValue INTEGER DEFAULT 0,
295 WrapCounter TINYBLOB NOT NULL,
296 PRIMARY KEY (Counter(128))
299 CREATE TABLE CDImages (
300 MediaId INTEGER UNSIGNED NOT NULL,
301 LastBurn DATETIME NOT NULL,
302 PRIMARY KEY (MediaId)
305 CREATE TABLE Status (
306 JobStatus CHAR(1) BINARY NOT NULL,
308 PRIMARY KEY (JobStatus)
311 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
312 ('C', 'Created, not yet running'),
315 ('T', 'Completed successfully'),
316 ('E', 'Terminated with errors'),
317 ('e', 'Non-fatal error'),
318 ('f', 'Fatal error'),
319 ('D', 'Verify found differences'),
320 ('A', 'Canceled by user'),
321 ('F', 'Waiting for Client'),
322 ('S', 'Waiting for Storage daemon'),
323 ('m', 'Waiting for new media'),
324 ('M', 'Waiting for media mount'),
325 ('s', 'Waiting for storage resource'),
326 ('j', 'Waiting for job resource'),
327 ('c', 'Waiting for client resource'),
328 ('d', 'Waiting on maximum jobs'),
329 ('t', 'Waiting on start time'),
330 ('p', 'Waiting on higher priority jobs'),
331 ('i', 'Doing batch insert file records'),
332 ('a', 'SD despooling attributes');
334 CREATE TABLE Version (
335 VersionId INTEGER UNSIGNED NOT NULL
338 -- Initialize Version
339 INSERT INTO Version (VersionId) VALUES (11);
343 echo "Creation of Bacula MySQL tables succeeded."
345 echo "Creation of Bacula MySQL tables failed."