1
2CREATE TABLE Filename (
3  FilenameId INTEGER,
4  Name TEXT DEFAULT '',
5  PRIMARY KEY(FilenameId)
6  );
7
8CREATE INDEX inx1 ON Filename (Name);
9
10CREATE TABLE Path (
11   PathId INTEGER,
12   Path TEXT DEFAULT '',
13   PRIMARY KEY(PathId)
14   );
15
16CREATE INDEX inx2 ON Path (Path);
17
18
19CREATE TABLE File (
20   FileId INTEGER,
21   FileIndex INTEGER UNSIGNED NOT NULL,
22   JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
23   PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
24   FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
25   MarkId INTEGER UNSIGNED DEFAULT 0,
26   LStat VARCHAR(255) NOT NULL,
27   MD5 VARCHAR(255) NOT NULL,
28   PRIMARY KEY(FileId)
29   );
30
31CREATE INDEX inx3 ON File (JobId);
32CREATE INDEX inx4 ON File (FilenameId, PathId);
33--
34-- Possibly add one or more of the following indexes
35--  if your Verifies are too slow.
36--
37-- CREATE INDEX inx4 ON File (PathId);
38-- CREATE INDEX inx5 ON File (FileNameId);
39-- CREATE INDEX inx9 ON File (JobId, PathId, FilenameId);
40
41CREATE TABLE Job (
42   JobId INTEGER,
43   Job VARCHAR(128) NOT NULL,
44   Name VARCHAR(128) NOT NULL,
45   Type CHAR(1) NOT NULL,
46   Level CHAR(1) NOT NULL,
47   ClientId INTEGER REFERENCES Client DEFAULT 0,
48   JobStatus CHAR(1) NOT NULL,
49   SchedTime DATETIME NOT NULL,
50   StartTime DATETIME DEFAULT 0,
51   EndTime DATETIME DEFAULT 0,
52   RealEndTime DATETIME DEFAULT 0,
53   JobTDate BIGINT UNSIGNED DEFAULT 0,
54   VolSessionId INTEGER UNSIGNED DEFAULT 0,
55   VolSessionTime INTEGER UNSIGNED DEFAULT 0,
56   JobFiles INTEGER UNSIGNED DEFAULT 0,
57   JobBytes BIGINT UNSIGNED DEFAULT 0,
58   ReadBytes BIGINT UNSIGNED DEFAULT 0,
59   JobErrors INTEGER UNSIGNED DEFAULT 0,
60   JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
61   PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
62   FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0,
63   PriorJobId INTEGER UNSIGNED REFERENCES Job DEFAULT 0,
64   PurgedFiles TINYINT DEFAULT 0,
65   HasBase TINYINT DEFAULT 0,
66   PRIMARY KEY(JobId)
67   );
68CREATE INDEX inx6 ON Job (Name);
69
70-- Create a table like Job for long term statistics
71CREATE TABLE JobHisto (
72   JobId INTEGER,
73   Job VARCHAR(128) NOT NULL,
74   Name VARCHAR(128) NOT NULL,
75   Type CHAR(1) NOT NULL,
76   Level CHAR(1) NOT NULL,
77   ClientId INTEGER DEFAULT 0,
78   JobStatus CHAR(1) NOT NULL,
79   SchedTime DATETIME NOT NULL,
80   StartTime DATETIME DEFAULT 0,
81   EndTime DATETIME DEFAULT 0,
82   RealEndTime DATETIME DEFAULT 0,
83   JobTDate BIGINT UNSIGNED DEFAULT 0,
84   VolSessionId INTEGER UNSIGNED DEFAULT 0,
85   VolSessionTime INTEGER UNSIGNED DEFAULT 0,
86   JobFiles INTEGER UNSIGNED DEFAULT 0,
87   JobBytes BIGINT UNSIGNED DEFAULT 0,
88   ReadBytes BIGINT UNSIGNED DEFAULT 0,
89   JobErrors INTEGER UNSIGNED DEFAULT 0,
90   JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
91   PoolId INTEGER UNSIGNED DEFAULT 0,
92   FileSetId INTEGER UNSIGNED DEFAULT 0,
93   PriorJobId INTEGER UNSIGNED DEFAULT 0,
94   PurgedFiles TINYINT DEFAULT 0,
95   HasBase TINYINT DEFAULT 0
96   );
97CREATE INDEX inx61 ON JobHisto (StartTime);
98
99CREATE TABLE Location (
100   LocationId INTEGER,
101   Location TEXT NOT NULL,
102   Cost INTEGER DEFAULT 0,
103   Enabled TINYINT,
104   PRIMARY KEY(LocationId)
105   );
106
107CREATE TABLE LocationLog (
108   LocLogId INTEGER,
109   Date DATETIME NOT NULL,
110   Comment TEXT NOT NULL,
111   MediaId INTEGER UNSIGNED REFERENCES Media DEFAULT 0,
112   LocationId INTEGER UNSIGNED REFERENCES LocationId DEFAULT 0,
113   NewVolStatus VARCHAR(20) NOT NULL,
114   NewEnabled TINYINT NOT NULL,
115   PRIMARY KEY(LocLogId)
116);
117
118
119CREATE TABLE Log (
120   LogId INTEGER,
121   JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
122   Time DATETIME NOT NULL,
123   LogText TEXT NOT NULL,
124   PRIMARY KEY(LogId)
125   );
126CREATE INDEX LogInx1 ON Log (JobId);
127
128
129CREATE TABLE FileSet (
130   FileSetId INTEGER,
131   FileSet VARCHAR(128) NOT NULL,
132   MD5 VARCHAR(25) NOT NULL,
133   CreateTime DATETIME DEFAULT 0,
134   PRIMARY KEY(FileSetId)
135   );
136
137CREATE TABLE JobMedia (
138   JobMediaId INTEGER,
139   JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
140   MediaId INTEGER UNSIGNED REFERENCES Media NOT NULL,
141   FirstIndex INTEGER UNSIGNED NOT NULL,
142   LastIndex INTEGER UNSIGNED NOT NULL,
143   StartFile INTEGER UNSIGNED DEFAULT 0,
144   EndFile INTEGER UNSIGNED DEFAULT 0,
145   StartBlock INTEGER UNSIGNED DEFAULT 0,
146   EndBlock INTEGER UNSIGNED DEFAULT 0,
147   VolIndex INTEGER UNSIGNED DEFAULT 0,
148   Copy     INTEGER UNSIGNED DEFAULT 0,
149   PRIMARY KEY(JobMediaId)
150   );
151
152CREATE INDEX inx7 ON JobMedia (JobId, MediaId);
153
154
155CREATE TABLE Media (
156   MediaId INTEGER,
157   VolumeName VARCHAR(128) NOT NULL,
158   Slot INTEGER DEFAULT 0,
159   PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
160   MediaType VARCHAR(128) NOT NULL,
161   MediaTypeId INTEGER UNSIGNED REFERENCES MediaType DEFAULT 0,
162   LabelType TINYINT DEFAULT 0,
163   FirstWritten DATETIME DEFAULT 0,
164   LastWritten DATETIME DEFAULT 0,
165   LabelDate DATETIME DEFAULT 0,
166   VolJobs INTEGER UNSIGNED DEFAULT 0,
167   VolFiles INTEGER UNSIGNED DEFAULT 0,
168   VolBlocks INTEGER UNSIGNED DEFAULT 0,
169   VolMounts INTEGER UNSIGNED DEFAULT 0,
170   VolBytes BIGINT UNSIGNED DEFAULT 0,
171   VolParts INTEGER UNSIGNED DEFAULT 0,
172   VolErrors INTEGER UNSIGNED DEFAULT 0,
173   VolWrites INTEGER UNSIGNED DEFAULT 0,
174   VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
175   VolStatus VARCHAR(20) NOT NULL,
176   Enabled TINYINT DEFAULT 1,
177   Recycle TINYINT DEFAULT 0,
178   ActionOnPurge     TINYINT    DEFAULT 0,
179   VolRetention BIGINT UNSIGNED DEFAULT 0,
180   VolUseDuration BIGINT UNSIGNED DEFAULT 0,
181   MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
182   MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
183   MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
184   InChanger TINYINT DEFAULT 0,
185   StorageId INTEGER UNSIGNED REFERENCES Storage DEFAULT 0,
186   DeviceId INTEGER UNSIGNED REFERENCES Device DEFAULT 0,
187   MediaAddressing TINYINT DEFAULT 0,
188   VolReadTime BIGINT UNSIGNED DEFAULT 0,
189   VolWriteTime BIGINT UNSIGNED DEFAULT 0,
190   EndFile INTEGER UNSIGNED DEFAULT 0,
191   EndBlock INTEGER UNSIGNED DEFAULT 0,
192   LocationId INTEGER UNSIGNED REFERENCES Location DEFAULT 0,
193   RecycleCount INTEGER UNSIGNED DEFAULT 0,
194   InitialWrite DATETIME DEFAULT 0,
195   ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
196   RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
197   Comment TEXT,
198   PRIMARY KEY(MediaId)
199   );
200
201CREATE INDEX inx8 ON Media (PoolId);
202
203CREATE TABLE MediaType (
204   MediaTypeId INTEGER,
205   MediaType VARCHAR(128) NOT NULL,
206   ReadOnly TINYINT DEFAULT 0,
207   PRIMARY KEY(MediaTypeId)
208   );
209
210CREATE TABLE Storage (
211   StorageId INTEGER,
212   Name VARCHAR(128) NOT NULL,
213   AutoChanger TINYINT DEFAULT 0,
214   PRIMARY KEY(StorageId)
215   );
216
217CREATE TABLE Device (
218   DeviceId INTEGER,
219   Name VARCHAR(128) NOT NULL,
220   MediaTypeId INTEGER UNSIGNED REFERENCES MediaType NOT NULL,
221   StorageId INTEGER UNSIGNED REFERENCES Storage,
222   DevMounts INTEGER UNSIGNED DEFAULT 0,
223   DevReadBytes BIGINT UNSIGNED DEFAULT 0,
224   DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
225   DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
226   DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
227   DevReadTime BIGINT UNSIGNED DEFAULT 0,
228   DevWriteTime BIGINT UNSIGNED DEFAULT 0,
229   DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
230   DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
231   CleaningDate DATETIME DEFAULT 0,
232   CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
233   PRIMARY KEY(DeviceId)
234   );
235
236
237CREATE TABLE Pool (
238   PoolId INTEGER,
239   Name VARCHAR(128) NOT NULL,
240   NumVols INTEGER UNSIGNED DEFAULT 0,
241   MaxVols INTEGER UNSIGNED DEFAULT 0,
242   UseOnce TINYINT DEFAULT 0,
243   UseCatalog TINYINT DEFAULT 1,
244   AcceptAnyVolume TINYINT DEFAULT 0,
245   VolRetention BIGINT UNSIGNED DEFAULT 0,
246   VolUseDuration BIGINT UNSIGNED DEFAULT 0,
247   MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
248   MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
249   MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
250   AutoPrune TINYINT DEFAULT 0,
251   Recycle TINYINT DEFAULT 0,
252   ActionOnPurge     TINYINT    DEFAULT 0,
253   PoolType VARCHAR(20) NOT NULL,
254   LabelType TINYINT DEFAULT 0,
255   LabelFormat VARCHAR(128) NOT NULL,
256   Enabled TINYINT DEFAULT 1,
257   ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
258   RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
259   NextPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
260   MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
261   MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
262   MigrationTime BIGINT UNSIGNED DEFAULT 0,
263   UNIQUE (Name),
264   PRIMARY KEY (PoolId)
265   );
266
267
268CREATE TABLE Client (
269   ClientId INTEGER,
270   Name VARCHAR(128) NOT NULL,
271   Uname VARCHAR(255) NOT NULL,   -- uname -a field
272   AutoPrune TINYINT DEFAULT 0,
273   FileRetention BIGINT UNSIGNED DEFAULT 0,
274   JobRetention  BIGINT UNSIGNED DEFAULT 0,
275   UNIQUE (Name),
276   PRIMARY KEY(ClientId)
277   );
278
279CREATE TABLE BaseFiles (
280   BaseId INTEGER,
281   BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
282   JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
283   FileId INTEGER UNSIGNED REFERENCES File NOT NULL,
284   FileIndex INTEGER UNSIGNED,
285   PRIMARY KEY(BaseId)
286   );
287
288CREATE TABLE UnsavedFiles (
289   UnsavedId INTEGER,
290   JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
291   PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
292   FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
293   PRIMARY KEY (UnsavedId)
294   );
295
296
297CREATE TABLE NextId (
298   id INTEGER UNSIGNED DEFAULT 0,
299   TableName TEXT NOT NULL,
300   PRIMARY KEY (TableName)
301   );
302
303
304
305-- Initialize JobId to start at 1
306INSERT INTO NextId (id, TableName) VALUES (1, 'Job');
307
308CREATE TABLE Version (
309   VersionId INTEGER UNSIGNED NOT NULL
310   );
311
312
313CREATE TABLE Counters (
314   Counter TEXT NOT NULL,
315   MinValue INTEGER DEFAULT 0,
316   MaxValue INTEGER DEFAULT 0,
317   CurrentValue INTEGER DEFAULT 0,
318   WrapCounter TEXT NOT NULL,
319   PRIMARY KEY (Counter)
320   );
321
322CREATE TABLE CDImages (
323   MediaId INTEGER UNSIGNED NOT NULL,
324   LastBurn DATETIME NOT NULL,
325   PRIMARY KEY (MediaId)
326   );
327
328
329CREATE TABLE Status (
330   JobStatus CHAR(1) NOT NULL,
331   JobStatusLong BLOB,
332   PRIMARY KEY (JobStatus)
333   );
334
335INSERT INTO Status (JobStatus,JobStatusLong) VALUES
336   ('C', 'Created, not yet running');
337INSERT INTO Status (JobStatus,JobStatusLong) VALUES
338   ('R', 'Running');
339INSERT INTO Status (JobStatus,JobStatusLong) VALUES
340   ('B', 'Blocked');
341INSERT INTO Status (JobStatus,JobStatusLong) VALUES
342   ('T', 'Completed successfully');
343INSERT INTO Status (JobStatus,JobStatusLong) VALUES
344   ('E', 'Terminated with errors');
345INSERT INTO Status (JobStatus,JobStatusLong) VALUES
346   ('e', 'Non-fatal error');
347INSERT INTO Status (JobStatus,JobStatusLong) VALUES
348   ('f', 'Fatal error');
349INSERT INTO Status (JobStatus,JobStatusLong) VALUES
350   ('D', 'Verify found differences');
351INSERT INTO Status (JobStatus,JobStatusLong) VALUES
352   ('A', 'Canceled by user');
353INSERT INTO Status (JobStatus,JobStatusLong) VALUES
354   ('F', 'Waiting for Client');
355INSERT INTO Status (JobStatus,JobStatusLong) VALUES
356   ('S', 'Waiting for Storage daemon');
357INSERT INTO Status (JobStatus,JobStatusLong) VALUES
358   ('m', 'Waiting for new media');
359INSERT INTO Status (JobStatus,JobStatusLong) VALUES
360   ('M', 'Waiting for media mount');
361INSERT INTO Status (JobStatus,JobStatusLong) VALUES
362   ('s', 'Waiting for storage resource');
363INSERT INTO Status (JobStatus,JobStatusLong) VALUES
364   ('j', 'Waiting for job resource');
365INSERT INTO Status (JobStatus,JobStatusLong) VALUES
366   ('c', 'Waiting for client resource');
367INSERT INTO Status (JobStatus,JobStatusLong) VALUES
368   ('d', 'Waiting on maximum jobs');
369INSERT INTO Status (JobStatus,JobStatusLong) VALUES
370   ('t', 'Waiting on start time');
371INSERT INTO Status (JobStatus,JobStatusLong) VALUES
372   ('p', 'Waiting on higher priority jobs');
373INSERT INTO Status (JobStatus,JobStatusLong) VALUES
374   ('a', 'SD despooling attributes');
375INSERT INTO Status (JobStatus,JobStatusLong) VALUES
376   ('i', 'Doing batch insert file records');
377
378
379-- Initialize Version
380INSERT INTO Version (VersionId) VALUES (11);
381
382
383PRAGMA default_cache_size = 100000;
384PRAGMA synchronous = NORMAL;
385