3 -- Note, we use BLOB rather than TEXT because in MySQL,
\r
4 -- BLOBs are identical to TEXT except that BLOB is case
\r
5 -- sensitive in sorts, which is what we want, and TEXT
\r
6 -- is case insensitive.
\r
8 CREATE TABLE Filename (
\r
9 FilenameId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
\r
11 PRIMARY KEY(FilenameId),
\r
16 PathId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
\r
18 PRIMARY KEY(PathId),
\r
24 FileId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
\r
25 FileIndex INTEGER UNSIGNED DEFAULT 0,
\r
26 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
\r
27 PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
\r
28 FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
\r
29 MarkId INTEGER UNSIGNED DEFAULT 0,
\r
30 LStat TINYBLOB NOT NULL,
\r
32 PRIMARY KEY(FileId),
\r
34 INDEX (JobId, PathId, FilenameId)
\r
38 # Possibly add one or more of the following indexes
\r
39 # to the above File table if your Verifies are
\r
43 # INDEX (FilenameId),
\r
44 # INDEX (FilenameId, PathId)
\r
48 CREATE TABLE MediaType (
\r
49 MediaTypeId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
\r
50 MediaType TINYBLOB NOT NULL,
\r
51 ReadOnly TINYINT DEFAULT 0,
\r
52 PRIMARY KEY(MediaTypeId)
\r
55 CREATE TABLE Storage (
\r
56 StorageId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
\r
57 Name TINYBLOB NOT NULL,
\r
58 AutoChanger TINYINT DEFAULT 0,
\r
59 PRIMARY KEY(StorageId)
\r
62 CREATE TABLE Device (
\r
63 DeviceId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
\r
64 Name TINYBLOB NOT NULL,
\r
65 MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType,
\r
66 StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage,
\r
67 DevMounts INTEGER UNSIGNED DEFAULT 0,
\r
68 DevReadBytes BIGINT UNSIGNED DEFAULT 0,
\r
69 DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
\r
70 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
\r
71 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
\r
72 DevReadTime BIGINT UNSIGNED DEFAULT 0,
\r
73 DevWriteTime BIGINT UNSIGNED DEFAULT 0,
\r
74 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
\r
75 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
\r
76 CleaningDate DATETIME DEFAULT 0,
\r
77 CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
\r
78 PRIMARY KEY(DeviceId)
\r
83 JobId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
\r
84 Job TINYBLOB NOT NULL,
\r
85 Name TINYBLOB NOT NULL,
\r
86 Type BINARY(1) NOT NULL,
\r
87 Level BINARY(1) NOT NULL,
\r
88 ClientId INTEGER DEFAULT 0 REFERENCES Client,
\r
89 JobStatus BINARY(1) NOT NULL,
\r
90 SchedTime DATETIME DEFAULT 0,
\r
91 StartTime DATETIME DEFAULT 0,
\r
92 EndTime DATETIME DEFAULT 0,
\r
93 RealEndTime DATETIME DEFAULT 0,
\r
94 JobTDate BIGINT UNSIGNED DEFAULT 0,
\r
95 VolSessionId INTEGER UNSIGNED DEFAULT 0,
\r
96 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
\r
97 JobFiles INTEGER UNSIGNED DEFAULT 0,
\r
98 JobBytes BIGINT UNSIGNED DEFAULT 0,
\r
99 JobErrors INTEGER UNSIGNED DEFAULT 0,
\r
100 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
\r
101 PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
\r
102 FileSetId INTEGER UNSIGNED DEFAULT 0 REFERENCES FileSet,
\r
103 PriorJobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job,
\r
104 PurgedFiles TINYINT DEFAULT 0,
\r
105 HasBase TINYINT DEFAULT 0,
\r
106 PRIMARY KEY(JobId),
\r
111 CREATE TABLE Location (
\r
112 LocationId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
\r
113 Location TINYBLOB NOT NULL,
\r
114 Cost INTEGER DEFAULT 0,
\r
116 PRIMARY KEY(LocationId)
\r
119 CREATE TABLE LocationLog (
\r
120 LocLogId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
\r
121 Date DATETIME DEFAULT 0,
\r
122 Comment BLOB NOT NULL,
\r
123 MediaId INTEGER UNSIGNED DEFAULT 0 REFERENCES Media,
\r
124 LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location,
\r
125 NewVolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
\r
126 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
\r
127 NewEnabled TINYINT,
\r
128 PRIMARY KEY(LocLogId)
\r
133 CREATE TABLE FileSet (
\r
134 FileSetId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
\r
135 FileSet TINYBLOB NOT NULL,
\r
137 CreateTime DATETIME DEFAULT 0,
\r
138 PRIMARY KEY(FileSetId)
\r
141 CREATE TABLE JobMedia (
\r
142 JobMediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
\r
143 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
\r
144 MediaId INTEGER UNSIGNED NOT NULL REFERENCES Media,
\r
145 FirstIndex INTEGER UNSIGNED DEFAULT 0,
\r
146 LastIndex INTEGER UNSIGNED DEFAULT 0,
\r
147 StartFile INTEGER UNSIGNED DEFAULT 0,
\r
148 EndFile INTEGER UNSIGNED DEFAULT 0,
\r
149 StartBlock INTEGER UNSIGNED DEFAULT 0,
\r
150 EndBlock INTEGER UNSIGNED DEFAULT 0,
\r
151 VolIndex INTEGER UNSIGNED DEFAULT 0,
\r
152 Copy INTEGER UNSIGNED DEFAULT 0,
\r
153 Stripe INTEGER UNSIGNED DEFAULT 0,
\r
154 PRIMARY KEY(JobMediaId),
\r
155 INDEX (JobId, MediaId)
\r
159 CREATE TABLE Media (
\r
160 MediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
\r
161 VolumeName TINYBLOB NOT NULL,
\r
162 Slot INTEGER DEFAULT 0,
\r
163 PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
\r
164 MediaType TINYBLOB NOT NULL,
\r
165 MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType,
\r
166 LabelType TINYINT DEFAULT 0,
\r
167 FirstWritten DATETIME DEFAULT 0,
\r
168 LastWritten DATETIME DEFAULT 0,
\r
169 LabelDate DATETIME DEFAULT 0,
\r
170 VolJobs INTEGER UNSIGNED DEFAULT 0,
\r
171 VolFiles INTEGER UNSIGNED DEFAULT 0,
\r
172 VolBlocks INTEGER UNSIGNED DEFAULT 0,
\r
173 VolMounts INTEGER UNSIGNED DEFAULT 0,
\r
174 VolBytes BIGINT UNSIGNED DEFAULT 0,
\r
175 VolParts INTEGER UNSIGNED DEFAULT 0,
\r
176 VolErrors INTEGER UNSIGNED DEFAULT 0,
\r
177 VolWrites INTEGER UNSIGNED DEFAULT 0,
\r
178 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
\r
179 VolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
\r
180 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
\r
181 Enabled TINYINT DEFAULT 1,
\r
182 Recycle TINYINT DEFAULT 0,
\r
183 VolRetention BIGINT UNSIGNED DEFAULT 0,
\r
184 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
\r
185 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
\r
186 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
\r
187 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
\r
188 InChanger TINYINT DEFAULT 0,
\r
189 StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage,
\r
190 DeviceId INTEGER UNSIGNED DEFAULT 0 REFERENCES Device,
\r
191 MediaAddressing TINYINT DEFAULT 0,
\r
192 VolReadTime BIGINT UNSIGNED DEFAULT 0,
\r
193 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
\r
194 EndFile INTEGER UNSIGNED DEFAULT 0,
\r
195 EndBlock INTEGER UNSIGNED DEFAULT 0,
\r
196 LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location,
\r
197 RecycleCount INTEGER UNSIGNED DEFAULT 0,
\r
198 InitialWrite DATETIME DEFAULT 0,
\r
199 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
\r
200 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
\r
202 PRIMARY KEY(MediaId),
\r
206 CREATE INDEX inx8 ON Media (PoolId);
\r
210 CREATE TABLE Pool (
\r
211 PoolId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
\r
212 Name TINYBLOB NOT NULL,
\r
213 NumVols INTEGER UNSIGNED DEFAULT 0,
\r
214 MaxVols INTEGER UNSIGNED DEFAULT 0,
\r
215 UseOnce TINYINT DEFAULT 0,
\r
216 UseCatalog TINYINT DEFAULT 0,
\r
217 AcceptAnyVolume TINYINT DEFAULT 0,
\r
218 VolRetention BIGINT UNSIGNED DEFAULT 0,
\r
219 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
\r
220 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
\r
221 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
\r
222 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
\r
223 AutoPrune TINYINT DEFAULT 0,
\r
224 Recycle TINYINT DEFAULT 0,
\r
225 PoolType ENUM('Backup', 'Copy', 'Cloned', 'Archive', 'Migration', 'Scratch') NOT NULL,
\r
226 LabelType TINYINT DEFAULT 0,
\r
227 LabelFormat TINYBLOB,
\r
228 Enabled TINYINT DEFAULT 1,
\r
229 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
\r
230 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
\r
231 NextPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
\r
232 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
\r
233 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
\r
234 MigrationTime BIGINT UNSIGNED DEFAULT 0,
\r
235 UNIQUE (Name(128)),
\r
236 PRIMARY KEY (PoolId)
\r
240 CREATE TABLE Client (
\r
241 ClientId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
\r
242 Name TINYBLOB NOT NULL,
\r
243 Uname TINYBLOB NOT NULL, /* full uname -a of client */
\r
244 AutoPrune TINYINT DEFAULT 0,
\r
245 FileRetention BIGINT UNSIGNED DEFAULT 0,
\r
246 JobRetention BIGINT UNSIGNED DEFAULT 0,
\r
247 UNIQUE (Name(128)),
\r
248 PRIMARY KEY(ClientId)
\r
252 LogId INTEGER UNSIGNED AUTO_INCREMENT,
\r
253 JobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job,
\r
254 Time DATETIME DEFAULT 0,
\r
255 LogText BLOB NOT NULL,
\r
256 PRIMARY KEY(LogId),
\r
261 CREATE TABLE BaseFiles (
\r
262 BaseId INTEGER UNSIGNED AUTO_INCREMENT,
\r
263 BaseJobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
\r
264 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
\r
265 FileId INTEGER UNSIGNED NOT NULL REFERENCES File,
\r
266 FileIndex INTEGER UNSIGNED,
\r
267 PRIMARY KEY(BaseId)
\r
270 CREATE TABLE UnsavedFiles (
\r
271 UnsavedId INTEGER UNSIGNED AUTO_INCREMENT,
\r
272 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
\r
273 PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
\r
274 FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
\r
275 PRIMARY KEY (UnsavedId)
\r
280 CREATE TABLE Counters (
\r
281 Counter TINYBLOB NOT NULL,
\r
282 MinValue INTEGER DEFAULT 0,
\r
283 MaxValue INTEGER DEFAULT 0,
\r
284 CurrentValue INTEGER DEFAULT 0,
\r
285 WrapCounter TINYBLOB NOT NULL,
\r
286 PRIMARY KEY (Counter(128))
\r
289 CREATE TABLE CDImages (
\r
290 MediaId INTEGER UNSIGNED NOT NULL,
\r
291 LastBurn DATETIME NOT NULL,
\r
292 PRIMARY KEY (MediaId)
\r
295 CREATE TABLE Status (
\r
296 JobStatus CHAR(1) BINARY NOT NULL,
\r
297 JobStatusLong BLOB,
\r
298 PRIMARY KEY (JobStatus)
\r
301 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
302 ('C', 'Created, not yet running'),
\r
305 ('T', 'Completed successfully'),
\r
306 ('E', 'Terminated with errors'),
\r
307 ('e', 'Non-fatal error'),
\r
308 ('f', 'Fatal error'),
\r
309 ('D', 'Verify found differences'),
\r
310 ('A', 'Canceled by user'),
\r
311 ('F', 'Waiting for Client'),
\r
312 ('S', 'Waiting for Storage daemon'),
\r
313 ('m', 'Waiting for new media'),
\r
314 ('M', 'Waiting for media mount'),
\r
315 ('s', 'Waiting for storage resource'),
\r
316 ('j', 'Waiting for job resource'),
\r
317 ('c', 'Waiting for client resource'),
\r
318 ('d', 'Waiting on maximum jobs'),
\r
319 ('t', 'Waiting on start time'),
\r
320 ('p', 'Waiting on higher priority jobs');
\r
322 CREATE TABLE Version (
\r
323 VersionId INTEGER UNSIGNED NOT NULL
\r
326 -- Initialize Version
\r
327 INSERT INTO Version (VersionId) VALUES (10);
\r