# shell script to create Bacula MySQL tables
#
bindir=@SQL_BINDIR@
+db_name=${db_name:-@db_name@}
if $bindir/mysql $* -f <<END-OF-DATA
-USE bacula;
+USE ${db_name};
--
-- Note, we use BLOB rather than TEXT because in MySQL,
-- BLOBs are identical to TEXT except that BLOB is case
INDEX (Path(255))
);
-
+-- In File table
+-- FileIndex can be 0 for FT_DELETED files
+-- FileNameId can link to Filename.Name='' for directories
CREATE TABLE File (
- FileId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
+ FileId BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
FileIndex INTEGER UNSIGNED DEFAULT 0,
JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
VolSessionTime INTEGER UNSIGNED DEFAULT 0,
JobFiles INTEGER UNSIGNED DEFAULT 0,
JobBytes BIGINT UNSIGNED DEFAULT 0,
+ ReadBytes BIGINT UNSIGNED DEFAULT 0,
JobErrors INTEGER UNSIGNED DEFAULT 0,
JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
INDEX (Name(128))
);
+-- Create a table like Job for long term statistics
+CREATE TABLE JobHisto (
+ JobId INTEGER UNSIGNED NOT NULL,
+ Job TINYBLOB NOT NULL,
+ Name TINYBLOB NOT NULL,
+ Type BINARY(1) NOT NULL,
+ Level BINARY(1) NOT NULL,
+ ClientId INTEGER DEFAULT 0,
+ JobStatus BINARY(1) NOT NULL,
+ SchedTime DATETIME DEFAULT 0,
+ StartTime DATETIME DEFAULT 0,
+ EndTime DATETIME DEFAULT 0,
+ RealEndTime DATETIME DEFAULT 0,
+ JobTDate BIGINT UNSIGNED DEFAULT 0,
+ VolSessionId INTEGER UNSIGNED DEFAULT 0,
+ VolSessionTime INTEGER UNSIGNED DEFAULT 0,
+ JobFiles INTEGER UNSIGNED DEFAULT 0,
+ JobBytes BIGINT UNSIGNED DEFAULT 0,
+ ReadBytes BIGINT UNSIGNED DEFAULT 0,
+ JobErrors INTEGER UNSIGNED DEFAULT 0,
+ JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
+ PoolId INTEGER UNSIGNED DEFAULT 0,
+ FileSetId INTEGER UNSIGNED DEFAULT 0,
+ PriorJobId INTEGER UNSIGNED DEFAULT 0,
+ PurgedFiles TINYINT DEFAULT 0,
+ HasBase TINYINT DEFAULT 0,
+ INDEX (StartTime)
+ );
CREATE TABLE Location (
LocationId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
LocLogId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
Date DATETIME DEFAULT 0,
Comment BLOB NOT NULL,
- MediaId INTEGER UNSIGNED DEFAULT 0 REFERENCES Media;
- LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES LocationId;
+ MediaId INTEGER UNSIGNED DEFAULT 0 REFERENCES Media,
+ LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location,
NewVolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
NewEnabled TINYINT,
'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
Enabled TINYINT DEFAULT 1,
Recycle TINYINT DEFAULT 0,
+ ActionOnPurge TINYINT DEFAULT 0,
VolRetention BIGINT UNSIGNED DEFAULT 0,
VolUseDuration BIGINT UNSIGNED DEFAULT 0,
MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
Comment BLOB,
PRIMARY KEY(MediaId),
+ UNIQUE (VolumeName(128)),
INDEX (PoolId)
);
-CREATE INDEX inx8 ON Media (PoolId);
-
-
-
CREATE TABLE Pool (
PoolId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
Name TINYBLOB NOT NULL,
MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
AutoPrune TINYINT DEFAULT 0,
Recycle TINYINT DEFAULT 0,
+ ActionOnPurge TINYINT DEFAULT 0,
PoolType ENUM('Backup', 'Copy', 'Cloned', 'Archive', 'Migration', 'Scratch') NOT NULL,
LabelType TINYINT DEFAULT 0,
LabelFormat TINYBLOB,
CREATE TABLE Log (
LogId INTEGER UNSIGNED AUTO_INCREMENT,
- JobId INTEGER INTEGER UNSIGNED DEFAULT 0 REFERENCES JobId,
+ JobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job,
Time DATETIME DEFAULT 0,
LogText BLOB NOT NULL,
PRIMARY KEY(LogId),
BaseId INTEGER UNSIGNED AUTO_INCREMENT,
BaseJobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
- FileId INTEGER UNSIGNED NOT NULL REFERENCES File,
+ FileId BIGINT UNSIGNED NOT NULL REFERENCES File,
FileIndex INTEGER UNSIGNED,
PRIMARY KEY(BaseId)
);
('c', 'Waiting for client resource'),
('d', 'Waiting on maximum jobs'),
('t', 'Waiting on start time'),
- ('p', 'Waiting on higher priority jobs');
+ ('p', 'Waiting on higher priority jobs'),
+ ('i', 'Doing batch insert file records'),
+ ('a', 'SD despooling attributes');
CREATE TABLE Version (
VersionId INTEGER UNSIGNED NOT NULL
);
-- Initialize Version
-INSERT INTO Version (VersionId) VALUES (10);
+INSERT INTO Version (VersionId) VALUES (11);
END-OF-DATA
then