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