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,
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 JobHistory (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),
213 UNIQUE (VolumeName(128)),
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'),
328 ('i', 'Doing batch insert file records'),
329 ('a', 'SD despooling attributes');
331 CREATE TABLE Version (
332 VersionId INTEGER UNSIGNED NOT NULL
335 -- Initialize Version
336 INSERT INTO Version (VersionId) VALUES (10);
340 echo "Creation of Bacula MySQL tables succeeded."
342 echo "Creation of Bacula MySQL tables failed."