3 -- Note, we use BLOB rather than TEXT because in MySQL,
4 -- BLOBs are identical to TEXT except that BLOB is case
5 -- sensitive in sorts, which is what we want, and TEXT
6 -- is case insensitive.
8 CREATE TABLE Filename (
9 FilenameId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
11 PRIMARY KEY(FilenameId),
16 PathId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
24 FileId BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
25 FileIndex INTEGER UNSIGNED DEFAULT 0,
26 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
27 PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
28 FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
29 MarkId INTEGER UNSIGNED DEFAULT 0,
30 LStat TINYBLOB NOT NULL,
34 INDEX (JobId, PathId, FilenameId)
38 -- Possibly add one or more of the following indexes
39 -- to the above File table if your Verifies are
43 -- INDEX (FilenameId),
44 -- INDEX (FilenameId, PathId)
48 CREATE TABLE MediaType (
49 MediaTypeId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
50 MediaType TINYBLOB NOT NULL,
51 ReadOnly TINYINT DEFAULT 0,
52 PRIMARY KEY(MediaTypeId)
55 CREATE TABLE Storage (
56 StorageId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
57 Name TINYBLOB NOT NULL,
58 AutoChanger TINYINT DEFAULT 0,
59 PRIMARY KEY(StorageId)
63 DeviceId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
64 Name TINYBLOB NOT NULL,
65 MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType,
66 StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage,
67 DevMounts INTEGER UNSIGNED DEFAULT 0,
68 DevReadBytes BIGINT UNSIGNED DEFAULT 0,
69 DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
70 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
71 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
72 DevReadTime BIGINT UNSIGNED DEFAULT 0,
73 DevWriteTime BIGINT UNSIGNED DEFAULT 0,
74 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
75 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
76 CleaningDate DATETIME DEFAULT 0,
77 CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
83 JobId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
84 Job TINYBLOB NOT NULL,
85 Name TINYBLOB NOT NULL,
86 Type BINARY(1) NOT NULL,
87 Level BINARY(1) NOT NULL,
88 ClientId INTEGER DEFAULT 0 REFERENCES Client,
89 JobStatus BINARY(1) NOT NULL,
90 SchedTime DATETIME DEFAULT 0,
91 StartTime DATETIME DEFAULT 0,
92 EndTime DATETIME DEFAULT 0,
93 RealEndTime DATETIME DEFAULT 0,
94 JobTDate BIGINT UNSIGNED DEFAULT 0,
95 VolSessionId INTEGER UNSIGNED DEFAULT 0,
96 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
97 JobFiles INTEGER UNSIGNED DEFAULT 0,
98 JobBytes BIGINT UNSIGNED DEFAULT 0,
99 ReadBytes BIGINT UNSIGNED DEFAULT 0,
100 JobErrors INTEGER UNSIGNED DEFAULT 0,
101 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
102 PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
103 FileSetId INTEGER UNSIGNED DEFAULT 0 REFERENCES FileSet,
104 PriorJobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job,
105 PurgedFiles TINYINT DEFAULT 0,
106 HasBase TINYINT DEFAULT 0,
111 -- Create a table like Job for long term statistics
112 CREATE TABLE JobHisto (
113 JobId INTEGER UNSIGNED NOT NULL,
114 Job TINYBLOB NOT NULL,
115 Name TINYBLOB NOT NULL,
116 Type BINARY(1) NOT NULL,
117 Level BINARY(1) NOT NULL,
118 ClientId INTEGER DEFAULT 0,
119 JobStatus BINARY(1) NOT NULL,
120 SchedTime DATETIME DEFAULT 0,
121 StartTime DATETIME DEFAULT 0,
122 EndTime DATETIME DEFAULT 0,
123 RealEndTime DATETIME DEFAULT 0,
124 JobTDate BIGINT UNSIGNED DEFAULT 0,
125 VolSessionId INTEGER UNSIGNED DEFAULT 0,
126 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
127 JobFiles INTEGER UNSIGNED DEFAULT 0,
128 JobBytes BIGINT UNSIGNED DEFAULT 0,
129 ReadBytes BIGINT UNSIGNED DEFAULT 0,
130 JobErrors INTEGER UNSIGNED DEFAULT 0,
131 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
132 PoolId INTEGER UNSIGNED DEFAULT 0,
133 FileSetId INTEGER UNSIGNED DEFAULT 0,
134 PriorJobId INTEGER UNSIGNED DEFAULT 0,
135 PurgedFiles TINYINT DEFAULT 0,
136 HasBase TINYINT DEFAULT 0,
140 CREATE TABLE Location (
141 LocationId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
142 Location TINYBLOB NOT NULL,
143 Cost INTEGER DEFAULT 0,
145 PRIMARY KEY(LocationId)
148 CREATE TABLE LocationLog (
149 LocLogId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
150 Date DATETIME DEFAULT 0,
151 Comment BLOB NOT NULL,
152 MediaId INTEGER UNSIGNED DEFAULT 0 REFERENCES Media,
153 LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location,
154 NewVolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
155 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
157 PRIMARY KEY(LocLogId)
161 CREATE TABLE FileSet (
162 FileSetId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
163 FileSet TINYBLOB NOT NULL,
165 CreateTime DATETIME DEFAULT 0,
166 PRIMARY KEY(FileSetId)
169 CREATE TABLE JobMedia (
170 JobMediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
171 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
172 MediaId INTEGER UNSIGNED NOT NULL REFERENCES Media,
173 FirstIndex INTEGER UNSIGNED DEFAULT 0,
174 LastIndex INTEGER UNSIGNED DEFAULT 0,
175 StartFile INTEGER UNSIGNED DEFAULT 0,
176 EndFile INTEGER UNSIGNED DEFAULT 0,
177 StartBlock INTEGER UNSIGNED DEFAULT 0,
178 EndBlock INTEGER UNSIGNED DEFAULT 0,
179 VolIndex INTEGER UNSIGNED DEFAULT 0,
180 Copy INTEGER UNSIGNED DEFAULT 0,
181 Stripe INTEGER UNSIGNED DEFAULT 0,
182 PRIMARY KEY(JobMediaId),
183 INDEX (JobId, MediaId)
188 MediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
189 VolumeName TINYBLOB NOT NULL,
190 Slot INTEGER DEFAULT 0,
191 PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
192 MediaType TINYBLOB NOT NULL,
193 MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType,
194 LabelType TINYINT DEFAULT 0,
195 FirstWritten DATETIME DEFAULT 0,
196 LastWritten DATETIME DEFAULT 0,
197 LabelDate DATETIME DEFAULT 0,
198 VolJobs INTEGER UNSIGNED DEFAULT 0,
199 VolFiles INTEGER UNSIGNED DEFAULT 0,
200 VolBlocks INTEGER UNSIGNED DEFAULT 0,
201 VolMounts INTEGER UNSIGNED DEFAULT 0,
202 VolBytes BIGINT UNSIGNED DEFAULT 0,
203 VolParts INTEGER UNSIGNED DEFAULT 0,
204 VolErrors INTEGER UNSIGNED DEFAULT 0,
205 VolWrites INTEGER UNSIGNED DEFAULT 0,
206 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
207 VolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
208 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
209 Enabled TINYINT DEFAULT 1,
210 Recycle TINYINT DEFAULT 0,
211 ActionOnPurge TINYINT DEFAULT 0,
212 VolRetention BIGINT UNSIGNED DEFAULT 0,
213 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
214 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
215 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
216 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
217 InChanger TINYINT DEFAULT 0,
218 StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage,
219 DeviceId INTEGER UNSIGNED DEFAULT 0 REFERENCES Device,
220 MediaAddressing TINYINT DEFAULT 0,
221 VolReadTime BIGINT UNSIGNED DEFAULT 0,
222 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
223 EndFile INTEGER UNSIGNED DEFAULT 0,
224 EndBlock INTEGER UNSIGNED DEFAULT 0,
225 LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location,
226 RecycleCount INTEGER UNSIGNED DEFAULT 0,
227 InitialWrite DATETIME DEFAULT 0,
228 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
229 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
231 PRIMARY KEY(MediaId),
232 UNIQUE (VolumeName(128)),
237 PoolId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
238 Name TINYBLOB NOT NULL,
239 NumVols INTEGER UNSIGNED DEFAULT 0,
240 MaxVols INTEGER UNSIGNED DEFAULT 0,
241 UseOnce TINYINT DEFAULT 0,
242 UseCatalog TINYINT DEFAULT 0,
243 AcceptAnyVolume TINYINT DEFAULT 0,
244 VolRetention BIGINT UNSIGNED DEFAULT 0,
245 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
246 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
247 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
248 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
249 AutoPrune TINYINT DEFAULT 0,
250 Recycle TINYINT DEFAULT 0,
251 ActionOnPurge TINYINT DEFAULT 0,
252 PoolType ENUM('Backup', 'Copy', 'Cloned', 'Archive', 'Migration', 'Scratch') NOT NULL,
253 LabelType TINYINT DEFAULT 0,
254 LabelFormat TINYBLOB,
255 Enabled TINYINT DEFAULT 1,
256 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
257 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
258 NextPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
259 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
260 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
261 MigrationTime BIGINT UNSIGNED DEFAULT 0,
267 CREATE TABLE Client (
268 ClientId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
269 Name TINYBLOB NOT NULL,
270 Uname TINYBLOB NOT NULL, /* full uname -a of client */
271 AutoPrune TINYINT DEFAULT 0,
272 FileRetention BIGINT UNSIGNED DEFAULT 0,
273 JobRetention BIGINT UNSIGNED DEFAULT 0,
275 PRIMARY KEY(ClientId)
279 LogId INTEGER UNSIGNED AUTO_INCREMENT,
280 JobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job,
281 Time DATETIME DEFAULT 0,
282 LogText BLOB NOT NULL,
288 CREATE TABLE BaseFiles (
289 BaseId INTEGER UNSIGNED AUTO_INCREMENT,
290 BaseJobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
291 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
292 FileId BIGINT UNSIGNED NOT NULL REFERENCES File,
293 FileIndex INTEGER UNSIGNED,
297 CREATE TABLE UnsavedFiles (
298 UnsavedId INTEGER UNSIGNED AUTO_INCREMENT,
299 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
300 PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
301 FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
302 PRIMARY KEY (UnsavedId)
307 CREATE TABLE Counters (
308 Counter TINYBLOB NOT NULL,
309 MinValue INTEGER DEFAULT 0,
310 MaxValue INTEGER DEFAULT 0,
311 CurrentValue INTEGER DEFAULT 0,
312 WrapCounter TINYBLOB NOT NULL,
313 PRIMARY KEY (Counter(128))
316 CREATE TABLE CDImages (
317 MediaId INTEGER UNSIGNED NOT NULL,
318 LastBurn DATETIME NOT NULL,
319 PRIMARY KEY (MediaId)
322 CREATE TABLE Status (
323 JobStatus CHAR(1) BINARY NOT NULL,
325 PRIMARY KEY (JobStatus)
328 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
329 ('C', 'Created, not yet running'),
332 ('T', 'Completed successfully'),
333 ('E', 'Terminated with errors'),
334 ('e', 'Non-fatal error'),
335 ('f', 'Fatal error'),
336 ('D', 'Verify found differences'),
337 ('A', 'Canceled by user'),
338 ('F', 'Waiting for Client'),
339 ('S', 'Waiting for Storage daemon'),
340 ('m', 'Waiting for new media'),
341 ('M', 'Waiting for media mount'),
342 ('s', 'Waiting for storage resource'),
343 ('j', 'Waiting for job resource'),
344 ('c', 'Waiting for client resource'),
345 ('d', 'Waiting on maximum jobs'),
346 ('t', 'Waiting on start time'),
347 ('p', 'Waiting on higher priority jobs'),
348 ('i', 'Doing batch insert file records'),
349 ('a', 'SD despooling attributes');
351 CREATE TABLE Version (
352 VersionId INTEGER UNSIGNED NOT NULL
355 -- Initialize Version
356 INSERT INTO Version (VersionId) VALUES (11);