1USE ${db_name};
2--
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.
7--
8CREATE TABLE Filename (
9  FilenameId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
10  Name BLOB NOT NULL,
11  PRIMARY KEY(FilenameId),
12  INDEX (Name(255))
13  );
14
15CREATE TABLE Path (
16   PathId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
17   Path BLOB NOT NULL,
18   PRIMARY KEY(PathId),
19   INDEX (Path(255))
20   );
21
22
23CREATE TABLE File (
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,
31   MD5 TINYBLOB,
32   PRIMARY KEY(FileId),
33   INDEX (JobId),
34   INDEX (JobId, PathId, FilenameId)
35   );
36
37--
38-- Possibly add one or more of the following indexes
39--  to the above File table if your Verifies are
40--  too slow.
41--
42--  INDEX (PathId),
43--  INDEX (FilenameId),
44--  INDEX (FilenameId, PathId)
45--  INDEX (JobId),
46--
47
48CREATE TABLE MediaType (
49   MediaTypeId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
50   MediaType TINYBLOB NOT NULL,
51   ReadOnly TINYINT DEFAULT 0,
52   PRIMARY KEY(MediaTypeId)
53   );
54
55CREATE TABLE Storage (
56   StorageId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
57   Name TINYBLOB NOT NULL,
58   AutoChanger TINYINT DEFAULT 0,
59   PRIMARY KEY(StorageId)
60   );
61
62CREATE TABLE Device (
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,
78   PRIMARY KEY(DeviceId)
79   );
80
81
82CREATE TABLE Job (
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,
107   PRIMARY KEY(JobId),
108   INDEX (Name(128))
109   );
110
111-- Create a table like Job for long term statistics
112CREATE 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,
137   INDEX (StartTime)
138   );
139
140CREATE TABLE Location (
141   LocationId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
142   Location TINYBLOB NOT NULL,
143   Cost INTEGER DEFAULT 0,
144   Enabled TINYINT,
145   PRIMARY KEY(LocationId)
146   );
147
148CREATE 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,
156   NewEnabled TINYINT,
157   PRIMARY KEY(LocLogId)
158);
159
160
161CREATE TABLE FileSet (
162   FileSetId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
163   FileSet TINYBLOB NOT NULL,
164   MD5 TINYBLOB,
165   CreateTime DATETIME DEFAULT 0,
166   PRIMARY KEY(FileSetId)
167   );
168
169CREATE 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)
184   );
185
186
187CREATE TABLE Media (
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,
230   Comment BLOB,
231   PRIMARY KEY(MediaId),
232   UNIQUE (VolumeName(128)),
233   INDEX (PoolId)
234   );
235
236CREATE TABLE Pool (
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,
262   UNIQUE (Name(128)),
263   PRIMARY KEY (PoolId)
264   );
265
266
267CREATE 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,
274   UNIQUE (Name(128)),
275   PRIMARY KEY(ClientId)
276   );
277
278CREATE TABLE Log (
279   LogId INTEGER UNSIGNED AUTO_INCREMENT,
280   JobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job,
281   Time DATETIME DEFAULT 0,
282   LogText BLOB NOT NULL,
283   PRIMARY KEY(LogId),
284   INDEX (JobId)
285   );
286
287
288CREATE 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,
294   PRIMARY KEY(BaseId)
295   );
296
297CREATE 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)
303   );
304
305
306
307CREATE 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))
314   );
315
316CREATE TABLE CDImages (
317   MediaId INTEGER UNSIGNED NOT NULL,
318   LastBurn DATETIME NOT NULL,
319   PRIMARY KEY (MediaId)
320   );
321
322CREATE TABLE Status (
323   JobStatus CHAR(1) BINARY NOT NULL,
324   JobStatusLong BLOB,
325   PRIMARY KEY (JobStatus)
326   );
327
328INSERT INTO Status (JobStatus,JobStatusLong) VALUES
329   ('C', 'Created, not yet running'),
330   ('R', 'Running'),
331   ('B', 'Blocked'),
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');
350
351CREATE TABLE Version (
352   VersionId INTEGER UNSIGNED NOT NULL
353   );
354
355-- Initialize Version
356INSERT INTO Version (VersionId) VALUES (11);
357
358