1 CREATE TABLE Filename (
\r
3 Name TEXT DEFAULT "",
\r
4 PRIMARY KEY(FilenameId)
\r
7 CREATE INDEX inx1 ON Filename (Name);
\r
11 Path TEXT DEFAULT "",
\r
12 PRIMARY KEY(PathId)
\r
15 CREATE INDEX inx2 ON Path (Path);
\r
20 FileIndex INTEGER UNSIGNED NOT NULL,
\r
21 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
\r
22 PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
\r
23 FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
\r
24 MarkId INTEGER UNSIGNED DEFAULT 0,
\r
25 LStat VARCHAR(255) NOT NULL,
\r
26 MD5 VARCHAR(255) NOT NULL,
\r
27 PRIMARY KEY(FileId)
\r
30 CREATE INDEX inx3 ON File (JobId);
\r
31 CREATE INDEX inx4 ON File (FilenameId, PathId);
\r
33 -- Possibly add one or more of the following indexes
\r
34 -- if your Verifies are too slow.
\r
36 -- CREATE INDEX inx4 ON File (PathId);
\r
37 -- CREATE INDEX inx5 ON File (FileNameId);
\r
38 -- CREATE INDEX inx9 ON File (JobId, PathId, FilenameId);
\r
42 Job VARCHAR(128) NOT NULL,
\r
43 Name VARCHAR(128) NOT NULL,
\r
45 Level CHAR NOT NULL,
\r
46 ClientId INTEGER REFERENCES Client DEFAULT 0,
\r
47 JobStatus CHAR NOT NULL,
\r
48 SchedTime DATETIME NOT NULL,
\r
49 StartTime DATETIME DEFAULT 0,
\r
50 EndTime DATETIME DEFAULT 0,
\r
51 RealEndTime DATETIME DEFAULT 0,
\r
52 JobTDate BIGINT UNSIGNED DEFAULT 0,
\r
53 VolSessionId INTEGER UNSIGNED DEFAULT 0,
\r
54 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
\r
55 JobFiles INTEGER UNSIGNED DEFAULT 0,
\r
56 JobBytes BIGINT UNSIGNED DEFAULT 0,
\r
57 JobErrors INTEGER UNSIGNED DEFAULT 0,
\r
58 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
\r
59 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
\r
60 FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0,
\r
61 PriorJobId INTEGER UNSIGNED REFERENCES Job DEFAULT 0,
\r
62 PurgedFiles TINYINT DEFAULT 0,
\r
63 HasBase TINYINT DEFAULT 0,
\r
66 CREATE INDEX inx6 ON Job (Name);
\r
68 CREATE TABLE Location (
\r
70 Location TEXT NOT NULL,
\r
71 Cost INTEGER DEFAULT 0,
\r
73 PRIMARY KEY(LocationId)
\r
76 CREATE TABLE LocationLog (
\r
78 Date DATETIME NOT NULL,
\r
79 Comment TEXT NOT NULL,
\r
80 MediaId INTEGER UNSIGNED REFERENCES Media DEFAULT 0,
\r
81 LocationId INTEGER UNSIGNED REFERENCES LocationId DEFAULT 0,
\r
82 NewVolStatus VARCHAR(20) NOT NULL,
\r
83 NewEnabled TINYINT NOT NULL,
\r
84 PRIMARY KEY(LocLogId)
\r
90 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
\r
91 Time DATETIME NOT NULL,
\r
92 LogText TEXT NOT NULL,
\r
95 CREATE INDEX LogInx1 ON File (JobId);
\r
98 CREATE TABLE FileSet (
\r
100 FileSet VARCHAR(128) NOT NULL,
\r
101 MD5 VARCHAR(25) NOT NULL,
\r
102 CreateTime DATETIME DEFAULT 0,
\r
103 PRIMARY KEY(FileSetId)
\r
106 CREATE TABLE JobMedia (
\r
107 JobMediaId INTEGER,
\r
108 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
\r
109 MediaId INTEGER UNSIGNED REFERENCES Media NOT NULL,
\r
110 FirstIndex INTEGER UNSIGNED NOT NULL,
\r
111 LastIndex INTEGER UNSIGNED NOT NULL,
\r
112 StartFile INTEGER UNSIGNED DEFAULT 0,
\r
113 EndFile INTEGER UNSIGNED DEFAULT 0,
\r
114 StartBlock INTEGER UNSIGNED DEFAULT 0,
\r
115 EndBlock INTEGER UNSIGNED DEFAULT 0,
\r
116 VolIndex INTEGER UNSIGNED DEFAULT 0,
\r
117 Copy INTEGER UNSIGNED DEFAULT 0,
\r
118 PRIMARY KEY(JobMediaId)
\r
121 CREATE INDEX inx7 ON JobMedia (JobId, MediaId);
\r
124 CREATE TABLE Media (
\r
126 VolumeName VARCHAR(128) NOT NULL,
\r
127 Slot INTEGER DEFAULT 0,
\r
128 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
\r
129 MediaType VARCHAR(128) NOT NULL,
\r
130 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType DEFAULT 0,
\r
131 LabelType TINYINT DEFAULT 0,
\r
132 FirstWritten DATETIME DEFAULT 0,
\r
133 LastWritten DATETIME DEFAULT 0,
\r
134 LabelDate DATETIME DEFAULT 0,
\r
135 VolJobs INTEGER UNSIGNED DEFAULT 0,
\r
136 VolFiles INTEGER UNSIGNED DEFAULT 0,
\r
137 VolBlocks INTEGER UNSIGNED DEFAULT 0,
\r
138 VolMounts INTEGER UNSIGNED DEFAULT 0,
\r
139 VolBytes BIGINT UNSIGNED DEFAULT 0,
\r
140 VolParts INTEGER UNSIGNED DEFAULT 0,
\r
141 VolErrors INTEGER UNSIGNED DEFAULT 0,
\r
142 VolWrites INTEGER UNSIGNED DEFAULT 0,
\r
143 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
\r
144 VolStatus VARCHAR(20) NOT NULL,
\r
145 Enabled TINYINT DEFAULT 1,
\r
146 Recycle TINYINT DEFAULT 0,
\r
147 VolRetention BIGINT UNSIGNED DEFAULT 0,
\r
148 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
\r
149 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
\r
150 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
\r
151 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
\r
152 InChanger TINYINT DEFAULT 0,
\r
153 StorageId INTEGER UNSIGNED REFERENCES Storage DEFAULT 0,
\r
154 DeviceId INTEGER UNSIGNED REFERENCES Device DEFAULT 0,
\r
155 MediaAddressing TINYINT DEFAULT 0,
\r
156 VolReadTime BIGINT UNSIGNED DEFAULT 0,
\r
157 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
\r
158 EndFile INTEGER UNSIGNED DEFAULT 0,
\r
159 EndBlock INTEGER UNSIGNED DEFAULT 0,
\r
160 LocationId INTEGER UNSIGNED REFERENCES Location DEFAULT 0,
\r
161 RecycleCount INTEGER UNSIGNED DEFAULT 0,
\r
162 InitialWrite DATETIME DEFAULT 0,
\r
163 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
\r
164 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
\r
166 PRIMARY KEY(MediaId)
\r
169 CREATE INDEX inx8 ON Media (PoolId);
\r
171 CREATE TABLE MediaType (
\r
172 MediaTypeId INTEGER,
\r
173 MediaType VARCHAR(128) NOT NULL,
\r
174 ReadOnly TINYINT DEFAULT 0,
\r
175 PRIMARY KEY(MediaTypeId)
\r
178 CREATE TABLE Storage (
\r
180 Name VARCHAR(128) NOT NULL,
\r
181 AutoChanger TINYINT DEFAULT 0,
\r
182 PRIMARY KEY(StorageId)
\r
185 CREATE TABLE Device (
\r
187 Name VARCHAR(128) NOT NULL,
\r
188 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType NOT NULL,
\r
189 StorageId INTEGER UNSIGNED REFERENCES Storage,
\r
190 DevMounts INTEGER UNSIGNED DEFAULT 0,
\r
191 DevReadBytes BIGINT UNSIGNED DEFAULT 0,
\r
192 DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
\r
193 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
\r
194 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
\r
195 DevReadTime BIGINT UNSIGNED DEFAULT 0,
\r
196 DevWriteTime BIGINT UNSIGNED DEFAULT 0,
\r
197 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
\r
198 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
\r
199 CleaningDate DATETIME DEFAULT 0,
\r
200 CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
\r
201 PRIMARY KEY(DeviceId)
\r
205 CREATE TABLE Pool (
\r
207 Name VARCHAR(128) NOT NULL,
\r
208 NumVols INTEGER UNSIGNED DEFAULT 0,
\r
209 MaxVols INTEGER UNSIGNED DEFAULT 0,
\r
210 UseOnce TINYINT DEFAULT 0,
\r
211 UseCatalog TINYINT DEFAULT 1,
\r
212 AcceptAnyVolume TINYINT DEFAULT 0,
\r
213 VolRetention BIGINT UNSIGNED DEFAULT 0,
\r
214 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
\r
215 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
\r
216 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
\r
217 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
\r
218 AutoPrune TINYINT DEFAULT 0,
\r
219 Recycle TINYINT DEFAULT 0,
\r
220 PoolType VARCHAR(20) NOT NULL,
\r
221 LabelType TINYINT DEFAULT 0,
\r
222 LabelFormat VARCHAR(128) NOT NULL,
\r
223 Enabled TINYINT DEFAULT 1,
\r
224 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
\r
225 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
\r
226 NextPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
\r
227 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
\r
228 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
\r
229 MigrationTime BIGINT UNSIGNED DEFAULT 0,
\r
231 PRIMARY KEY (PoolId)
\r
235 CREATE TABLE Client (
\r
237 Name VARCHAR(128) NOT NULL,
\r
238 Uname VARCHAR(255) NOT NULL, -- uname -a field
\r
239 AutoPrune TINYINT DEFAULT 0,
\r
240 FileRetention BIGINT UNSIGNED DEFAULT 0,
\r
241 JobRetention BIGINT UNSIGNED DEFAULT 0,
\r
243 PRIMARY KEY(ClientId)
\r
246 CREATE TABLE BaseFiles (
\r
248 BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
\r
249 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
\r
250 FileId INTEGER UNSIGNED REFERENCES File NOT NULL,
\r
251 FileIndex INTEGER UNSIGNED,
\r
252 PRIMARY KEY(BaseId)
\r
255 CREATE TABLE UnsavedFiles (
\r
257 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
\r
258 PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
\r
259 FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
\r
260 PRIMARY KEY (UnsavedId)
\r
264 CREATE TABLE NextId (
\r
265 id INTEGER UNSIGNED DEFAULT 0,
\r
266 TableName TEXT NOT NULL,
\r
267 PRIMARY KEY (TableName)
\r
272 -- Initialize JobId to start at 1
\r
273 INSERT INTO NextId (id, TableName) VALUES (1, "Job");
\r
275 CREATE TABLE Version (
\r
276 VersionId INTEGER UNSIGNED NOT NULL
\r
280 CREATE TABLE Counters (
\r
281 Counter TEXT NOT NULL,
\r
282 MinValue INTEGER DEFAULT 0,
\r
283 MaxValue INTEGER DEFAULT 0,
\r
284 CurrentValue INTEGER DEFAULT 0,
\r
285 WrapCounter TEXT NOT NULL,
\r
286 PRIMARY KEY (Counter)
\r
289 CREATE TABLE CDImages (
\r
290 MediaId INTEGER UNSIGNED NOT NULL,
\r
291 LastBurn DATETIME NOT NULL,
\r
292 PRIMARY KEY (MediaId)
\r
296 CREATE TABLE Status (
\r
297 JobStatus CHAR(1) NOT NULL,
\r
298 JobStatusLong BLOB,
\r
299 PRIMARY KEY (JobStatus)
\r
302 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
303 ('C', 'Created, not yet running');
\r
304 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
306 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
308 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
309 ('T', 'Completed successfully');
\r
310 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
311 ('E', 'Terminated with errors');
\r
312 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
313 ('e', 'Non-fatal error');
\r
314 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
315 ('f', 'Fatal error');
\r
316 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
317 ('D', 'Verify found differences');
\r
318 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
319 ('A', 'Canceled by user');
\r
320 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
321 ('F', 'Waiting for Client');
\r
322 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
323 ('S', 'Waiting for Storage daemon');
\r
324 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
325 ('m', 'Waiting for new media');
\r
326 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
327 ('M', 'Waiting for media mount');
\r
328 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
329 ('s', 'Waiting for storage resource');
\r
330 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
331 ('j', 'Waiting for job resource');
\r
332 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
333 ('c', 'Waiting for client resource');
\r
334 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
335 ('d', 'Waiting on maximum jobs');
\r
336 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
337 ('t', 'Waiting on start time');
\r
338 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
339 ('p', 'Waiting on higher priority jobs');
\r
342 -- Initialize Version
\r
343 INSERT INTO Version (VersionId) VALUES (10);
\r
346 PRAGMA default_synchronous = OFF;
\r
347 PRAGMA default_cache_size = 10000;
\r