1#!/bin/sh 2# 3# shell script to create Bacula SQLite tables 4# 5# Copyright (C) 2000-2020 Kern Sibbald 6# License: BSD 2-Clause; see file LICENSE-FOSS 7# 8 9bindir=@SQLITE_BINDIR@ 10PATH="$bindir:$PATH" 11cd @working_dir@ 12db_name=@db_name@ 13 14sqlite3 $* ${db_name}.db <<END-OF-DATA 15CREATE TABLE Filename ( 16 FilenameId INTEGER, 17 Name TEXT DEFAULT '', 18 PRIMARY KEY(FilenameId) 19 ); 20 21CREATE INDEX inx1 ON Filename (Name); 22 23CREATE TABLE Path ( 24 PathId INTEGER, 25 Path TEXT DEFAULT '', 26 PRIMARY KEY(PathId) 27 ); 28 29CREATE INDEX inx2 ON Path (Path); 30 31-- In File table 32-- FileIndex can be 0 for FT_DELETED files 33-- FileNameId can link to Filename.Name='' for directories 34CREATE TABLE File ( 35 FileId INTEGER, 36 FileIndex INTEGER DEFAULT 0, 37 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL, 38 PathId INTEGER UNSIGNED REFERENCES Path NOT NULL, 39 FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL, 40 DeltaSeq SMALLINT UNSIGNED DEFAULT 0, 41 MarkId INTEGER UNSIGNED DEFAULT 0, 42 LStat VARCHAR(255) NOT NULL, 43 MD5 VARCHAR(255) NOT NULL, 44 PRIMARY KEY(FileId) 45 ); 46CREATE INDEX inx3 ON File (JobId); 47CREATE INDEX file_jpf_idx ON File (JobId, PathId, FilenameId); 48-- 49-- Possibly add one or more of the following indexes 50-- if your Verifies are too slow. 51-- 52-- CREATE INDEX inx4 ON File (PathId); 53-- CREATE INDEX inx5 ON File (FileNameId); 54 55 56CREATE TABLE RestoreObject ( 57 RestoreObjectId INTEGER, 58 ObjectName TEXT DEFAULT '', 59 RestoreObject TEXT DEFAULT '', 60 PluginName TEXT DEFAULT '', 61 ObjectLength INTEGER DEFAULT 0, 62 ObjectFullLength INTEGER DEFAULT 0, 63 ObjectIndex INTEGER DEFAULT 0, 64 ObjectType INTEGER DEFAULT 0, 65 FileIndex INTEGER DEFAULT 0, 66 ObjectCompression INTEGER DEFAULT 0, 67 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL, 68 PRIMARY KEY(RestoreObjectId) 69 ); 70CREATE INDEX restore_jobid_idx ON RestoreObject (JobId); 71 72 73CREATE TABLE Job ( 74 JobId INTEGER, 75 Job VARCHAR(128) NOT NULL, 76 Name VARCHAR(128) NOT NULL, 77 Type CHAR(1) NOT NULL, 78 Level CHAR(1) NOT NULL, 79 ClientId INTEGER REFERENCES Client DEFAULT 0, 80 JobStatus CHAR(1) NOT NULL, 81 SchedTime DATETIME NOT NULL, 82 StartTime DATETIME DEFAULT 0, 83 EndTime DATETIME DEFAULT 0, 84 RealEndTime DATETIME DEFAULT 0, 85 JobTDate BIGINT UNSIGNED DEFAULT 0, 86 VolSessionId INTEGER UNSIGNED DEFAULT 0, 87 VolSessionTime INTEGER UNSIGNED DEFAULT 0, 88 JobFiles INTEGER UNSIGNED DEFAULT 0, 89 JobBytes BIGINT UNSIGNED DEFAULT 0, 90 ReadBytes BIGINT UNSIGNED DEFAULT 0, 91 JobErrors INTEGER UNSIGNED DEFAULT 0, 92 JobMissingFiles INTEGER UNSIGNED DEFAULT 0, 93 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0, 94 FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0, 95 PriorJobId INTEGER UNSIGNED REFERENCES Job DEFAULT 0, 96 PurgedFiles TINYINT DEFAULT 0, 97 HasBase TINYINT DEFAULT 0, 98 HasCache TINYINT DEFAULT 0, 99 Reviewed TINYINT DEFAULT 0, 100 Comment TEXT, 101 FileTable TEXT DEFAULT 'File', 102 PRIMARY KEY(JobId) 103 ); 104CREATE INDEX inx6 ON Job (Name); 105CREATE INDEX job_jobtdate_inx ON Job (JobTDate); 106 107-- Create a table like Job for long term statistics 108CREATE TABLE JobHisto ( 109 JobId INTEGER, 110 Job VARCHAR(128) NOT NULL, 111 Name VARCHAR(128) NOT NULL, 112 Type CHAR(1) NOT NULL, 113 Level CHAR(1) NOT NULL, 114 ClientId INTEGER DEFAULT 0, 115 JobStatus CHAR(1) NOT NULL, 116 SchedTime DATETIME NOT NULL, 117 StartTime DATETIME DEFAULT 0, 118 EndTime DATETIME DEFAULT 0, 119 RealEndTime DATETIME DEFAULT 0, 120 JobTDate BIGINT UNSIGNED DEFAULT 0, 121 VolSessionId INTEGER UNSIGNED DEFAULT 0, 122 VolSessionTime INTEGER UNSIGNED DEFAULT 0, 123 JobFiles INTEGER UNSIGNED DEFAULT 0, 124 JobBytes BIGINT UNSIGNED DEFAULT 0, 125 ReadBytes BIGINT UNSIGNED DEFAULT 0, 126 JobErrors INTEGER UNSIGNED DEFAULT 0, 127 JobMissingFiles INTEGER UNSIGNED DEFAULT 0, 128 PoolId INTEGER UNSIGNED DEFAULT 0, 129 FileSetId INTEGER UNSIGNED DEFAULT 0, 130 PriorJobId INTEGER UNSIGNED DEFAULT 0, 131 PurgedFiles TINYINT DEFAULT 0, 132 HasBase TINYINT DEFAULT 0, 133 HasCache TINYINT DEFAULT 0, 134 Reviewed TINYINT DEFAULT 0, 135 Comment TEXT, 136 FileTable TEXT DEFAULT 'File' 137 ); 138CREATE INDEX inx61 ON JobHisto (StartTime); 139 140CREATE TABLE Location ( 141 LocationId INTEGER, 142 Location TEXT NOT NULL, 143 Cost INTEGER DEFAULT 0, 144 Enabled TINYINT, 145 PRIMARY KEY(LocationId) 146 ); 147 148CREATE TABLE LocationLog ( 149 LocLogId INTEGER, 150 Date DATETIME NOT NULL, 151 Comment TEXT NOT NULL, 152 MediaId INTEGER UNSIGNED REFERENCES Media DEFAULT 0, 153 LocationId INTEGER UNSIGNED REFERENCES LocationId DEFAULT 0, 154 NewVolStatus VARCHAR(20) NOT NULL, 155 NewEnabled TINYINT NOT NULL, 156 PRIMARY KEY(LocLogId) 157); 158 159 160CREATE TABLE Log ( 161 LogId INTEGER, 162 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL, 163 Time DATETIME NOT NULL, 164 LogText TEXT NOT NULL, 165 PRIMARY KEY(LogId) 166 ); 167CREATE INDEX LogInx1 ON Log (JobId); 168 169 170CREATE TABLE FileSet ( 171 FileSetId INTEGER, 172 FileSet VARCHAR(128) NOT NULL, 173 MD5 VARCHAR(25) NOT NULL, 174 CreateTime DATETIME DEFAULT 0, 175 PRIMARY KEY(FileSetId) 176 ); 177 178CREATE INDEX fileset_name_idx on FileSet (FileSet); 179 180CREATE TABLE JobMedia ( 181 JobMediaId INTEGER, 182 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL, 183 MediaId INTEGER UNSIGNED REFERENCES Media NOT NULL, 184 FirstIndex INTEGER UNSIGNED NOT NULL, 185 LastIndex INTEGER UNSIGNED NOT NULL, 186 StartFile INTEGER UNSIGNED DEFAULT 0, 187 EndFile INTEGER UNSIGNED DEFAULT 0, 188 StartBlock INTEGER UNSIGNED DEFAULT 0, 189 EndBlock INTEGER UNSIGNED DEFAULT 0, 190 VolIndex INTEGER UNSIGNED DEFAULT 0, 191 PRIMARY KEY(JobMediaId) 192 ); 193 194CREATE INDEX inx7 ON JobMedia (JobId, MediaId); 195 196 197CREATE TABLE Media ( 198 MediaId INTEGER, 199 VolumeName VARCHAR(128) NOT NULL, 200 Slot INTEGER DEFAULT 0, 201 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0, 202 MediaType VARCHAR(128) NOT NULL, 203 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType DEFAULT 0, 204 LabelType TINYINT DEFAULT 0, 205 FirstWritten DATETIME DEFAULT 0, 206 LastWritten DATETIME DEFAULT 0, 207 LabelDate DATETIME DEFAULT 0, 208 VolJobs INTEGER UNSIGNED DEFAULT 0, 209 VolFiles INTEGER UNSIGNED DEFAULT 0, 210 VolBlocks INTEGER UNSIGNED DEFAULT 0, 211 LastPartBytes BIGINT UNSIGNED DEFAULT 0, 212 VolMounts INTEGER UNSIGNED DEFAULT 0, 213 VolBytes BIGINT UNSIGNED DEFAULT 0, 214 VolABytes BIGINT UNSIGNED DEFAULT 0, 215 VolAPadding BIGINT UNSIGNED DEFAULT 0, 216 VolHoleBytes BIGINT UNSIGNED DEFAULT 0, 217 VolHoles INTEGER UNSIGNED DEFAULT 0, 218 VolType INTEGER UNSIGNED DEFAULT 0, 219 VolParts INTEGER UNSIGNED DEFAULT 0, 220 VolCloudParts INTEGER UNSIGNED DEFAULT 0, 221 VolErrors INTEGER UNSIGNED DEFAULT 0, 222 VolWrites BIGINT UNSIGNED DEFAULT 0, 223 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0, 224 VolStatus VARCHAR(20) NOT NULL, 225 Enabled TINYINT DEFAULT 1, 226 Recycle TINYINT DEFAULT 0, 227 ActionOnPurge TINYINT DEFAULT 0, 228 CacheRetention BIGINT UNSIGNED DEFAULT 0, 229 VolRetention BIGINT UNSIGNED DEFAULT 0, 230 VolUseDuration BIGINT UNSIGNED DEFAULT 0, 231 MaxVolJobs INTEGER UNSIGNED DEFAULT 0, 232 MaxVolFiles INTEGER UNSIGNED DEFAULT 0, 233 MaxVolBytes BIGINT UNSIGNED DEFAULT 0, 234 InChanger TINYINT DEFAULT 0, 235 StorageId INTEGER UNSIGNED REFERENCES Storage DEFAULT 0, 236 DeviceId INTEGER UNSIGNED REFERENCES Device DEFAULT 0, 237 MediaAddressing TINYINT DEFAULT 0, 238 VolReadTime BIGINT UNSIGNED DEFAULT 0, 239 VolWriteTime BIGINT UNSIGNED DEFAULT 0, 240 EndFile INTEGER UNSIGNED DEFAULT 0, 241 EndBlock INTEGER UNSIGNED DEFAULT 0, 242 LocationId INTEGER UNSIGNED REFERENCES Location DEFAULT 0, 243 RecycleCount INTEGER UNSIGNED DEFAULT 0, 244 InitialWrite DATETIME DEFAULT 0, 245 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0, 246 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0, 247 Comment TEXT, 248 PRIMARY KEY(MediaId) 249 ); 250 251CREATE UNIQUE INDEX Media_Volumename_Id ON Media (VolumeName); 252CREATE INDEX inx8 ON Media (PoolId); 253CREATE INDEX inx9 ON Media (StorageId); 254 255 256CREATE TABLE MediaType ( 257 MediaTypeId INTEGER, 258 MediaType VARCHAR(128) NOT NULL, 259 ReadOnly TINYINT DEFAULT 0, 260 PRIMARY KEY(MediaTypeId) 261 ); 262 263CREATE TABLE Storage ( 264 StorageId INTEGER, 265 Name VARCHAR(128) NOT NULL, 266 AutoChanger TINYINT DEFAULT 0, 267 PRIMARY KEY(StorageId) 268 ); 269 270CREATE TABLE Device ( 271 DeviceId INTEGER, 272 Name VARCHAR(128) NOT NULL, 273 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType NOT NULL, 274 StorageId INTEGER UNSIGNED REFERENCES Storage, 275 DevMounts INTEGER UNSIGNED DEFAULT 0, 276 DevReadBytes BIGINT UNSIGNED DEFAULT 0, 277 DevWriteBytes BIGINT UNSIGNED DEFAULT 0, 278 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0, 279 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0, 280 DevReadTime BIGINT UNSIGNED DEFAULT 0, 281 DevWriteTime BIGINT UNSIGNED DEFAULT 0, 282 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0, 283 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0, 284 CleaningDate DATETIME DEFAULT 0, 285 CleaningPeriod BIGINT UNSIGNED DEFAULT 0, 286 PRIMARY KEY(DeviceId) 287 ); 288 289 290CREATE TABLE Pool ( 291 PoolId INTEGER, 292 Name VARCHAR(128) NOT NULL, 293 NumVols INTEGER UNSIGNED DEFAULT 0, 294 MaxVols INTEGER UNSIGNED DEFAULT 0, 295 UseOnce TINYINT DEFAULT 0, 296 UseCatalog TINYINT DEFAULT 1, 297 AcceptAnyVolume TINYINT DEFAULT 0, 298 CacheRetention BIGINT UNSIGNED DEFAULT 0, 299 VolRetention BIGINT UNSIGNED DEFAULT 0, 300 VolUseDuration BIGINT UNSIGNED DEFAULT 0, 301 MaxVolJobs INTEGER UNSIGNED DEFAULT 0, 302 MaxVolFiles INTEGER UNSIGNED DEFAULT 0, 303 MaxVolBytes BIGINT UNSIGNED DEFAULT 0, 304 AutoPrune TINYINT DEFAULT 0, 305 Recycle TINYINT DEFAULT 0, 306 ActionOnPurge TINYINT DEFAULT 0, 307 PoolType VARCHAR(20) NOT NULL, 308 LabelType TINYINT DEFAULT 0, 309 LabelFormat VARCHAR(128) NOT NULL, 310 Enabled TINYINT DEFAULT 1, 311 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0, 312 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0, 313 NextPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0, 314 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0, 315 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0, 316 MigrationTime BIGINT UNSIGNED DEFAULT 0, 317 UNIQUE (Name), 318 PRIMARY KEY (PoolId) 319 ); 320 321CREATE INDEX pool_name_idx on Pool (Name); 322 323CREATE TABLE Client ( 324 ClientId INTEGER, 325 Name VARCHAR(128) NOT NULL, 326 Uname VARCHAR(255) NOT NULL, -- uname -a field 327 AutoPrune TINYINT DEFAULT 0, 328 FileRetention BIGINT UNSIGNED DEFAULT 0, 329 JobRetention BIGINT UNSIGNED DEFAULT 0, 330 UNIQUE (Name), 331 PRIMARY KEY(ClientId) 332 ); 333 334CREATE UNIQUE INDEX client_name_idx on Client (Name); 335 336CREATE TABLE BaseFiles ( 337 BaseId BIGINT, 338 BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL, 339 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL, 340 FileId INTEGER UNSIGNED REFERENCES File NOT NULL, 341 FileIndex INTEGER DEFAULT 0, 342 PRIMARY KEY(BaseId) 343 ); 344 345CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId ); 346 347CREATE TABLE UnsavedFiles ( 348 UnsavedId INTEGER, 349 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL, 350 PathId INTEGER UNSIGNED REFERENCES Path NOT NULL, 351 FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL, 352 PRIMARY KEY (UnsavedId) 353 ); 354 355 356CREATE TABLE Version ( 357 VersionId INTEGER UNSIGNED NOT NULL 358 ); 359 360 361CREATE TABLE Counters ( 362 Counter TEXT NOT NULL, 363 MinValue INTEGER DEFAULT 0, 364 MaxValue INTEGER DEFAULT 0, 365 CurrentValue INTEGER DEFAULT 0, 366 WrapCounter TEXT NOT NULL, 367 PRIMARY KEY (Counter) 368 ); 369 370CREATE TABLE CDImages ( 371 MediaId INTEGER UNSIGNED NOT NULL, 372 LastBurn DATETIME NOT NULL, 373 PRIMARY KEY (MediaId) 374 ); 375 376CREATE TABLE PathHierarchy 377( 378 PathId integer NOT NULL, 379 PPathId integer NOT NULL, 380 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId) 381); 382 383CREATE INDEX pathhierarchy_ppathid 384 ON PathHierarchy (PPathId); 385 386CREATE TABLE PathVisibility 387( 388 PathId integer NOT NULL, 389 JobId integer NOT NULL, 390 Size int8 DEFAULT 0, 391 Files int4 DEFAULT 0, 392 CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId) 393); 394 395CREATE INDEX pathvisibility_jobid 396 ON PathVisibility (JobId); 397 398 399CREATE TABLE Snapshot ( 400 SnapshotId serial, 401 Name text NOT NULL, 402 JobId integer default 0, 403 FileSetId integer default 0, 404 CreateTDate bigint default 0, 405 CreateDate datetime not null, 406 ClientId int default 0, 407 Volume text not null, 408 Device text not null, 409 Type text not null, 410 Retention int default 0, 411 Comment text, 412 primary key (SnapshotId) 413); 414 415CREATE UNIQUE INDEX snapshot_idx ON Snapshot (Device, Volume, Name); 416 417CREATE TABLE Status ( 418 JobStatus CHAR(1) NOT NULL, 419 JobStatusLong BLOB, 420 Severity INT, 421 PRIMARY KEY (JobStatus) 422 ); 423 424INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES 425 ('C', 'Created, not yet running',15); 426INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES 427 ('R', 'Running',15); 428INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES 429 ('B', 'Blocked',15); 430INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES 431 ('T', 'Completed successfully', 10); 432INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES 433 ('E', 'Terminated with errors', 25); 434INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES 435 ('e', 'Non-fatal error',20); 436INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES 437 ('f', 'Fatal error',100); 438INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES 439 ('D', 'Verify found differences',15); 440INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES 441 ('A', 'Canceled by user',90); 442INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES 443 ('F', 'Waiting for Client',15); 444INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES 445 ('S', 'Waiting for Storage daemon',15); 446INSERT INTO Status (JobStatus,JobStatusLong) VALUES 447 ('m', 'Waiting for new media'); 448INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES 449 ('M', 'Waiting for media mount',15); 450INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES 451 ('s', 'Waiting for storage resource',15); 452INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES 453 ('j', 'Waiting for job resource',15); 454INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES 455 ('c', 'Waiting for client resource',15); 456INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES 457 ('d', 'Waiting on maximum jobs',15); 458INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES 459 ('t', 'Waiting on start time',15); 460INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES 461 ('p', 'Waiting on higher priority jobs',15); 462INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES 463 ('a', 'SD despooling attributes',15); 464INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES 465 ('i', 'Doing batch insert file records',15); 466INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES 467 ('I', 'Incomplete Job',25); 468 469-- Initialize Version 470INSERT INTO Version (VersionId) VALUES (16); 471 472PRAGMA default_cache_size = 100000; 473PRAGMA synchronous = NORMAL; 474 475END-OF-DATA 476 477echo "" 478echo "WARNING: !!!! SQLite3 is no longer supported. !!!!" 479echo "WARNING: !!!! Please switch to MySQL or PostgreSQL !!!!" 480echo "WARNING: !!!! as soon as possible. !!!!" 481echo "" 482 483chmod 640 ${db_name}.db 484exit 0 485