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,
30 # ****FIXME**** make FileId BIGINT someday when MySQL works *****
32 FileId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
33 FileIndex INTEGER UNSIGNED NOT NULL 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 NOT NULL DEFAULT 0,
38 LStat TINYBLOB NOT NULL,
39 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 CREATE TABLE FileSet (
117 FileSetId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
118 FileSet TINYBLOB NOT NULL,
119 MD5 TINYBLOB NOT NULL,
120 CreateTime DATETIME NOT NULL,
121 PRIMARY KEY(FileSetId)
124 CREATE TABLE JobMedia (
125 JobMediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
126 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
127 MediaId INTEGER UNSIGNED NOT NULL REFERENCES Media,
128 FirstIndex INTEGER UNSIGNED NOT NULL DEFAULT 0,
129 LastIndex INTEGER UNSIGNED NOT NULL DEFAULT 0,
130 StartFile INTEGER UNSIGNED NOT NULL DEFAULT 0,
131 EndFile INTEGER UNSIGNED NOT NULL DEFAULT 0,
132 StartBlock INTEGER UNSIGNED NOT NULL DEFAULT 0,
133 EndBlock INTEGER UNSIGNED NOT NULL DEFAULT 0,
134 VolIndex INTEGER UNSIGNED NOT NULL DEFAULT 0,
135 Copy INTEGER UNSIGNED NOT NULL DEFAULT 0,
136 Stripe INTEGER UNSIGNED NOT NULL DEFAULT 0,
137 PRIMARY KEY(JobMediaId),
138 INDEX (JobId, MediaId)
143 MediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
144 VolumeName TINYBLOB NOT NULL,
145 Slot INTEGER NOT NULL DEFAULT 0,
146 PoolId INTEGER UNSIGNED NOT NULL REFERENCES Pool,
147 MediaType TINYBLOB NOT NULL,
148 LabelType TINYINT NOT NULL DEFAULT 0,
149 FirstWritten DATETIME NOT NULL,
150 LastWritten DATETIME NOT NULL,
151 LabelDate DATETIME NOT NULL,
152 VolJobs INTEGER UNSIGNED NOT NULL DEFAULT 0,
153 VolFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
154 VolBlocks INTEGER UNSIGNED NOT NULL DEFAULT 0,
155 VolMounts INTEGER UNSIGNED NOT NULL DEFAULT 0,
156 VolBytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
157 VolParts INTEGER UNSIGNED NOT NULL DEFAULT 0,
158 VolErrors INTEGER UNSIGNED NOT NULL DEFAULT 0,
159 VolWrites INTEGER UNSIGNED NOT NULL DEFAULT 0,
160 VolCapacityBytes BIGINT UNSIGNED NOT NULL,
161 VolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
162 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
163 Recycle TINYINT NOT NULL DEFAULT 0,
164 VolRetention BIGINT UNSIGNED NOT NULL DEFAULT 0,
165 VolUseDuration BIGINT UNSIGNED NOT NULL DEFAULT 0,
166 MaxVolJobs INTEGER UNSIGNED NOT NULL DEFAULT 0,
167 MaxVolFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
168 MaxVolBytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
169 InChanger TINYINT NOT NULL DEFAULT 0,
170 StorageId INTEGER UNSIGNED NOT NULL REFERENCES Storage,
171 MediaAddressing TINYINT NOT NULL DEFAULT 0,
172 VolReadTime BIGINT UNSIGNED NOT NULL DEFAULT 0,
173 VolWriteTime BIGINT UNSIGNED NOT NULL DEFAULT 0,
174 EndFile INTEGER UNSIGNED NOT NULL DEFAULT 0,
175 EndBlock INTEGER UNSIGNED NOT NULL DEFAULT 0,
176 PRIMARY KEY(MediaId),
180 CREATE INDEX inx8 ON Media (PoolId);
185 PoolId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
186 Name TINYBLOB NOT NULL,
187 NumVols INTEGER UNSIGNED NOT NULL DEFAULT 0,
188 MaxVols INTEGER UNSIGNED NOT NULL DEFAULT 0,
189 UseOnce TINYINT NOT NULL,
190 UseCatalog TINYINT NOT NULL,
191 AcceptAnyVolume TINYINT DEFAULT 0,
192 VolRetention BIGINT UNSIGNED NOT NULL,
193 VolUseDuration BIGINT UNSIGNED NOT NULL,
194 MaxVolJobs INTEGER UNSIGNED NOT NULL DEFAULT 0,
195 MaxVolFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
196 MaxVolBytes BIGINT UNSIGNED NOT NULL,
197 AutoPrune TINYINT DEFAULT 0,
198 Recycle TINYINT DEFAULT 0,
199 PoolType ENUM('Backup', 'Copy', 'Cloned', 'Archive', 'Migration', 'Scratch') NOT NULL,
200 LabelType TINYINT NOT NULL DEFAULT 0,
201 LabelFormat TINYBLOB,
202 Enabled TINYINT DEFAULT 1,
203 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
204 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
205 NextPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
206 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
207 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
208 MigrationTime BIGINT UNSIGNED DEFAULT 0,
214 CREATE TABLE Client (
215 ClientId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
216 Name TINYBLOB NOT NULL,
217 Uname TINYBLOB NOT NULL, /* full uname -a of client */
218 AutoPrune TINYINT DEFAULT 0,
219 FileRetention BIGINT UNSIGNED NOT NULL,
220 JobRetention BIGINT UNSIGNED NOT NULL,
222 PRIMARY KEY(ClientId)
225 CREATE TABLE BaseFiles (
226 BaseId INTEGER UNSIGNED AUTO_INCREMENT,
227 BaseJobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
228 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
229 FileId INTEGER UNSIGNED NOT NULL REFERENCES File,
230 FileIndex INTEGER UNSIGNED,
234 CREATE TABLE UnsavedFiles (
235 UnsavedId INTEGER UNSIGNED AUTO_INCREMENT,
236 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
237 PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
238 FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
239 PRIMARY KEY (UnsavedId)
244 CREATE TABLE Counters (
245 Counter TINYBLOB NOT NULL,
248 CurrentValue INTEGER,
249 WrapCounter TINYBLOB NOT NULL,
250 PRIMARY KEY (Counter(128))
253 CREATE TABLE CDImages (
254 MediaId INTEGER UNSIGNED NOT NULL,
255 LastBurn DATETIME NOT NULL,
256 PRIMARY KEY (MediaId)
259 CREATE TABLE Status (
260 JobStatus CHAR(1) BINARY NOT NULL,
262 PRIMARY KEY (JobStatus)
265 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
266 ('C', 'Created, not yet running'),
269 ('T', 'Completed successfully'),
270 ('E', 'Terminated with errors'),
271 ('e', 'Non-fatal error'),
272 ('f', 'Fatal error'),
273 ('D', 'Verify found differences'),
274 ('A', 'Canceled by user'),
275 ('F', 'Waiting for Client'),
276 ('S', 'Waiting for Storage daemon'),
277 ('m', 'Waiting for new media'),
278 ('M', 'Waiting for media mount'),
279 ('s', 'Waiting for storage resource'),
280 ('j', 'Waiting for job resource'),
281 ('c', 'Waiting for client resource'),
282 ('d', 'Waiting on maximum jobs'),
283 ('t', 'Waiting on start time'),
284 ('p', 'Waiting on higher priority jobs');
286 CREATE TABLE Version (
287 VersionId INTEGER UNSIGNED NOT NULL
290 -- Initialize Version
291 INSERT INTO Version (VersionId) VALUES (9);
295 echo "Creation of Bacula MySQL tables succeeded."
297 echo "Creation of Bacula MySQL tables failed."