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 NOT NULL 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 NOT NULL DEFAULT 0,
37 LStat TINYBLOB NOT NULL,
38 MD5 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 NOT NULL REFERENCES MediaType,
73 StorageId INTEGER UNSIGNED NOT NULL 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 NOT NULL REFERENCES Client,
96 JobStatus BINARY(1) NOT NULL,
97 SchedTime DATETIME NOT NULL,
98 StartTime DATETIME NOT NULL,
99 EndTime DATETIME NOT NULL,
100 JobTDate BIGINT UNSIGNED NOT NULL,
101 VolSessionId INTEGER UNSIGNED NOT NULL DEFAULT 0,
102 VolSessionTime INTEGER UNSIGNED NOT NULL DEFAULT 0,
103 JobFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
104 JobBytes BIGINT UNSIGNED NOT NULL,
105 JobErrors INTEGER UNSIGNED NOT NULL DEFAULT 0,
106 JobMissingFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
107 PoolId INTEGER UNSIGNED NOT NULL REFERENCES Pool,
108 FileSetId INTEGER UNSIGNED NOT NULL REFERENCES FileSet,
109 PurgedFiles TINYINT NOT NULL DEFAULT 0,
110 HasBase TINYINT NOT NULL DEFAULT 0,
116 JobId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
117 OriginalJobId INTEGER UNSIGNED NOT NULL,
118 JobType BINARY(1) NOT NULL,
119 JobLevel BINARY(1) NOT NULL,
120 SchedTime DATETIME NOT NULL,
121 StartTime DATETIME NOT NULL,
122 EndTime DATETIME NOT NULL,
123 JobTDate BIGINT UNSIGNED NOT NULL,
127 CREATE TABLE Location (
128 LocationId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
129 Location TINYBLOB NOT NULL,
130 PRIMARY KEY(LocationId)
134 CREATE TABLE FileSet (
135 FileSetId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
136 FileSet TINYBLOB NOT NULL,
137 MD5 TINYBLOB NOT NULL,
138 CreateTime DATETIME NOT NULL,
139 PRIMARY KEY(FileSetId)
142 CREATE TABLE JobMedia (
143 JobMediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
144 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
145 MediaId INTEGER UNSIGNED NOT NULL REFERENCES Media,
146 FirstIndex INTEGER UNSIGNED NOT NULL DEFAULT 0,
147 LastIndex INTEGER UNSIGNED NOT NULL DEFAULT 0,
148 StartFile INTEGER UNSIGNED NOT NULL DEFAULT 0,
149 EndFile INTEGER UNSIGNED NOT NULL DEFAULT 0,
150 StartBlock INTEGER UNSIGNED NOT NULL DEFAULT 0,
151 EndBlock INTEGER UNSIGNED NOT NULL DEFAULT 0,
152 VolIndex INTEGER UNSIGNED NOT NULL DEFAULT 0,
153 Copy INTEGER UNSIGNED NOT NULL DEFAULT 0,
154 Stripe INTEGER UNSIGNED NOT NULL DEFAULT 0,
155 PRIMARY KEY(JobMediaId),
156 INDEX (JobId, MediaId)
161 MediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
162 VolumeName TINYBLOB NOT NULL,
163 Slot INTEGER NOT NULL DEFAULT 0,
164 PoolId INTEGER UNSIGNED NOT NULL REFERENCES Pool,
165 MediaType TINYBLOB NOT NULL,
166 MediaTypeId INTEGER UNSIGNED NOT NULL REFERENCES MediaType,
167 LabelType TINYINT NOT NULL DEFAULT 0,
168 FirstWritten DATETIME NOT NULL,
169 LastWritten DATETIME NOT NULL,
170 LabelDate DATETIME NOT NULL,
171 VolJobs INTEGER UNSIGNED NOT NULL DEFAULT 0,
172 VolFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
173 VolBlocks INTEGER UNSIGNED NOT NULL DEFAULT 0,
174 VolMounts INTEGER UNSIGNED NOT NULL DEFAULT 0,
175 VolBytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
176 VolParts INTEGER UNSIGNED NOT NULL DEFAULT 0,
177 VolErrors INTEGER UNSIGNED NOT NULL DEFAULT 0,
178 VolWrites INTEGER UNSIGNED NOT NULL DEFAULT 0,
179 VolCapacityBytes BIGINT UNSIGNED NOT NULL,
180 VolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
181 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
182 Recycle TINYINT NOT NULL DEFAULT 0,
183 VolRetention BIGINT UNSIGNED NOT NULL DEFAULT 0,
184 VolUseDuration BIGINT UNSIGNED NOT NULL DEFAULT 0,
185 MaxVolJobs INTEGER UNSIGNED NOT NULL DEFAULT 0,
186 MaxVolFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
187 MaxVolBytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
188 InChanger TINYINT NOT NULL DEFAULT 0,
189 StorageId INTEGER UNSIGNED NOT NULL REFERENCES Storage,
190 DeviceId INTEGER UNSIGNED NOT NULL REFERENCES Device,
191 MediaAddressing TINYINT NOT NULL DEFAULT 0,
192 VolReadTime BIGINT UNSIGNED NOT NULL DEFAULT 0,
193 VolWriteTime BIGINT UNSIGNED NOT NULL DEFAULT 0,
194 EndFile INTEGER UNSIGNED NOT NULL DEFAULT 0,
195 EndBlock INTEGER UNSIGNED NOT NULL DEFAULT 0,
196 LocationId INTEGER UNSIGNED NOT NULL REFERENCES Location,
197 RecycleCount INTEGER UNSIGNED DEFAULT 0,
198 InitialWrite DATETIME NOT NULL,
199 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
200 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
201 PRIMARY KEY(MediaId),
205 CREATE INDEX inx8 ON Media (PoolId);
210 PoolId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
211 Name TINYBLOB NOT NULL,
212 NumVols INTEGER UNSIGNED NOT NULL DEFAULT 0,
213 MaxVols INTEGER UNSIGNED NOT NULL DEFAULT 0,
214 UseOnce TINYINT NOT NULL,
215 UseCatalog TINYINT NOT NULL,
216 AcceptAnyVolume TINYINT DEFAULT 0,
217 VolRetention BIGINT UNSIGNED NOT NULL,
218 VolUseDuration BIGINT UNSIGNED NOT NULL,
219 MaxVolJobs INTEGER UNSIGNED NOT NULL DEFAULT 0,
220 MaxVolFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
221 MaxVolBytes BIGINT UNSIGNED NOT NULL,
222 AutoPrune TINYINT DEFAULT 0,
223 Recycle TINYINT DEFAULT 0,
224 PoolType ENUM('Backup', 'Copy', 'Cloned', 'Archive', 'Migration', 'Scratch') NOT NULL,
225 LabelType TINYINT NOT NULL DEFAULT 0,
226 LabelFormat TINYBLOB,
227 Enabled TINYINT DEFAULT 1,
228 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
229 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
230 NextPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
231 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
232 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
233 MigrationTime BIGINT UNSIGNED DEFAULT 0,
239 CREATE TABLE Client (
240 ClientId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
241 Name TINYBLOB NOT NULL,
242 Uname TINYBLOB NOT NULL, /* full uname -a of client */
243 AutoPrune TINYINT DEFAULT 0,
244 FileRetention BIGINT UNSIGNED NOT NULL,
245 JobRetention BIGINT UNSIGNED NOT NULL,
247 PRIMARY KEY(ClientId)
250 CREATE TABLE BaseFiles (
251 BaseId INTEGER UNSIGNED AUTO_INCREMENT,
252 BaseJobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
253 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
254 FileId INTEGER UNSIGNED NOT NULL REFERENCES File,
255 FileIndex INTEGER UNSIGNED,
259 CREATE TABLE UnsavedFiles (
260 UnsavedId INTEGER UNSIGNED AUTO_INCREMENT,
261 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
262 PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
263 FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
264 PRIMARY KEY (UnsavedId)
269 CREATE TABLE Counters (
270 Counter TINYBLOB NOT NULL,
273 CurrentValue INTEGER,
274 WrapCounter TINYBLOB NOT NULL,
275 PRIMARY KEY (Counter(128))
278 CREATE TABLE CDImages (
279 MediaId INTEGER UNSIGNED NOT NULL,
280 LastBurn DATETIME NOT NULL,
281 PRIMARY KEY (MediaId)
284 CREATE TABLE Status (
285 JobStatus CHAR(1) BINARY NOT NULL,
287 PRIMARY KEY (JobStatus)
290 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
291 ('C', 'Created, not yet running'),
294 ('T', 'Completed successfully'),
295 ('E', 'Terminated with errors'),
296 ('e', 'Non-fatal error'),
297 ('f', 'Fatal error'),
298 ('D', 'Verify found differences'),
299 ('A', 'Canceled by user'),
300 ('F', 'Waiting for Client'),
301 ('S', 'Waiting for Storage daemon'),
302 ('m', 'Waiting for new media'),
303 ('M', 'Waiting for media mount'),
304 ('s', 'Waiting for storage resource'),
305 ('j', 'Waiting for job resource'),
306 ('c', 'Waiting for client resource'),
307 ('d', 'Waiting on maximum jobs'),
308 ('t', 'Waiting on start time'),
309 ('p', 'Waiting on higher priority jobs');
311 CREATE TABLE Version (
312 VersionId INTEGER UNSIGNED NOT NULL
315 -- Initialize Version
316 INSERT INTO Version (VersionId) VALUES (9);
320 echo "Creation of Bacula MySQL tables succeeded."
322 echo "Creation of Bacula MySQL tables failed."