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