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)
127 CREATE TABLE FileSet (
128 FileSetId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
129 FileSet TINYBLOB NOT NULL,
131 CreateTime DATETIME DEFAULT 0,
132 PRIMARY KEY(FileSetId)
135 CREATE TABLE JobMedia (
136 JobMediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
137 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
138 MediaId INTEGER UNSIGNED NOT NULL REFERENCES Media,
139 FirstIndex INTEGER UNSIGNED DEFAULT 0,
140 LastIndex INTEGER UNSIGNED DEFAULT 0,
141 StartFile INTEGER UNSIGNED DEFAULT 0,
142 EndFile INTEGER UNSIGNED DEFAULT 0,
143 StartBlock INTEGER UNSIGNED DEFAULT 0,
144 EndBlock INTEGER UNSIGNED DEFAULT 0,
145 VolIndex INTEGER UNSIGNED DEFAULT 0,
146 Copy INTEGER UNSIGNED DEFAULT 0,
147 Stripe INTEGER UNSIGNED DEFAULT 0,
148 PRIMARY KEY(JobMediaId),
149 INDEX (JobId, MediaId)
154 MediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
155 VolumeName TINYBLOB NOT NULL,
156 Slot INTEGER DEFAULT 0,
157 PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
158 MediaType TINYBLOB NOT NULL,
159 MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType,
160 LabelType TINYINT DEFAULT 0,
161 FirstWritten DATETIME DEFAULT 0,
162 LastWritten DATETIME DEFAULT 0,
163 LabelDate DATETIME DEFAULT 0,
164 VolJobs INTEGER UNSIGNED DEFAULT 0,
165 VolFiles INTEGER UNSIGNED DEFAULT 0,
166 VolBlocks INTEGER UNSIGNED DEFAULT 0,
167 VolMounts INTEGER UNSIGNED DEFAULT 0,
168 VolBytes BIGINT UNSIGNED DEFAULT 0,
169 VolParts INTEGER UNSIGNED DEFAULT 0,
170 VolErrors INTEGER UNSIGNED DEFAULT 0,
171 VolWrites INTEGER UNSIGNED DEFAULT 0,
172 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
173 VolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
174 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
175 Enabled TINYINT DEFAULT 1,
176 Recycle TINYINT DEFAULT 0,
177 VolRetention BIGINT UNSIGNED DEFAULT 0,
178 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
179 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
180 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
181 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
182 InChanger TINYINT DEFAULT 0,
183 StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage,
184 DeviceId INTEGER UNSIGNED DEFAULT 0 REFERENCES Device,
185 MediaAddressing TINYINT DEFAULT 0,
186 VolReadTime BIGINT UNSIGNED DEFAULT 0,
187 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
188 EndFile INTEGER UNSIGNED DEFAULT 0,
189 EndBlock INTEGER UNSIGNED DEFAULT 0,
190 LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location,
191 RecycleCount INTEGER UNSIGNED DEFAULT 0,
192 InitialWrite DATETIME DEFAULT 0,
193 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
194 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
195 PRIMARY KEY(MediaId),
199 CREATE INDEX inx8 ON Media (PoolId);
204 PoolId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
205 Name TINYBLOB NOT NULL,
206 NumVols INTEGER UNSIGNED DEFAULT 0,
207 MaxVols INTEGER UNSIGNED DEFAULT 0,
208 UseOnce TINYINT DEFAULT 0,
209 UseCatalog TINYINT DEFAULT 0,
210 AcceptAnyVolume TINYINT DEFAULT 0,
211 VolRetention BIGINT UNSIGNED DEFAULT 0,
212 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
213 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
214 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
215 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
216 AutoPrune TINYINT DEFAULT 0,
217 Recycle TINYINT DEFAULT 0,
218 PoolType ENUM('Backup', 'Copy', 'Cloned', 'Archive', 'Migration', 'Scratch') NOT NULL,
219 LabelType TINYINT DEFAULT 0,
220 LabelFormat TINYBLOB,
221 Enabled TINYINT DEFAULT 1,
222 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
223 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
224 NextPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
225 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
226 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
227 MigrationTime BIGINT UNSIGNED DEFAULT 0,
233 CREATE TABLE Client (
234 ClientId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
235 Name TINYBLOB NOT NULL,
236 Uname TINYBLOB NOT NULL, /* full uname -a of client */
237 AutoPrune TINYINT DEFAULT 0,
238 FileRetention BIGINT UNSIGNED DEFAULT 0,
239 JobRetention BIGINT UNSIGNED DEFAULT 0,
241 PRIMARY KEY(ClientId)
245 JobId INTEGER UNSIGNED DEFAULT 0 REFERENCES JobId,
246 LogText BLOB NOT NULL,
251 CREATE TABLE BaseFiles (
252 BaseId INTEGER UNSIGNED AUTO_INCREMENT,
253 BaseJobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
254 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
255 FileId INTEGER UNSIGNED NOT NULL REFERENCES File,
256 FileIndex INTEGER UNSIGNED,
260 CREATE TABLE UnsavedFiles (
261 UnsavedId INTEGER UNSIGNED AUTO_INCREMENT,
262 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
263 PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
264 FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
265 PRIMARY KEY (UnsavedId)
270 CREATE TABLE Counters (
271 Counter TINYBLOB NOT NULL,
272 MinValue INTEGER DEFAULT 0,
273 MaxValue INTEGER DEFAULT 0,
274 CurrentValue INTEGER DEFAULT 0,
275 WrapCounter TINYBLOB NOT NULL,
276 PRIMARY KEY (Counter(128))
279 CREATE TABLE CDImages (
280 MediaId INTEGER UNSIGNED NOT NULL,
281 LastBurn DATETIME NOT NULL,
282 PRIMARY KEY (MediaId)
285 CREATE TABLE Status (
286 JobStatus CHAR(1) BINARY NOT NULL,
288 PRIMARY KEY (JobStatus)
291 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
292 ('C', 'Created, not yet running'),
295 ('T', 'Completed successfully'),
296 ('E', 'Terminated with errors'),
297 ('e', 'Non-fatal error'),
298 ('f', 'Fatal error'),
299 ('D', 'Verify found differences'),
300 ('A', 'Canceled by user'),
301 ('F', 'Waiting for Client'),
302 ('S', 'Waiting for Storage daemon'),
303 ('m', 'Waiting for new media'),
304 ('M', 'Waiting for media mount'),
305 ('s', 'Waiting for storage resource'),
306 ('j', 'Waiting for job resource'),
307 ('c', 'Waiting for client resource'),
308 ('d', 'Waiting on maximum jobs'),
309 ('t', 'Waiting on start time'),
310 ('p', 'Waiting on higher priority jobs');
312 CREATE TABLE Version (
313 VersionId INTEGER UNSIGNED NOT NULL
316 -- Initialize Version
317 INSERT INTO Version (VersionId) VALUES (10);
321 echo "Creation of Bacula MySQL tables succeeded."
323 echo "Creation of Bacula MySQL tables failed."