Tweak mutex order for SD

[bacula/bacula] / bacula / src / cats / make_mysql_tables.in

diff --git a/bacula/src/cats/make_mysql_tables.in b/bacula/src/cats/make_mysql_tables.in
index 43cd01410093484edca84b162e5b0228415e4402..3a6f4606199a272af3c4a52e5b6aade3593634bc 100644 (file)
--- a/bacula/src/cats/make_mysql_tables.in
+++ b/bacula/src/cats/make_mysql_tables.in
@@ -3,9 +3,10 @@
 # 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
@@ -26,9 +27,11 @@ CREATE TABLE Path (
    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,
@@ -103,6 +106,7 @@ CREATE TABLE Job (
    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,
@@ -114,14 +118,56 @@ CREATE TABLE Job (
    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,
    Location TINYBLOB NOT NULL,
    Cost INTEGER DEFAULT 0,
+   Enabled TINYINT,
    PRIMARY KEY(LocationId)
    );
 
+CREATE TABLE LocationLog (
+   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 Location,
+   NewVolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
+    'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
+   NewEnabled TINYINT,
+   PRIMARY KEY(LocLogId)
+);
+
+
 # 
 CREATE TABLE FileSet (
    FileSetId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
@@ -173,6 +219,7 @@ CREATE TABLE Media (
     '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,
@@ -191,14 +238,12 @@ CREATE TABLE Media (
    InitialWrite DATETIME DEFAULT 0,
    ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
    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,
@@ -214,6 +259,7 @@ CREATE TABLE Pool (
    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,
@@ -240,11 +286,21 @@ CREATE TABLE Client (
    PRIMARY KEY(ClientId)
    );
 
+CREATE TABLE Log (
+   LogId INTEGER UNSIGNED AUTO_INCREMENT,
+   JobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job,
+   Time DATETIME DEFAULT 0,
+   LogText BLOB NOT NULL,
+   PRIMARY KEY(LogId),
+   INDEX (JobId)
+   );
+
+
 CREATE TABLE BaseFiles (
    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)
    );
@@ -299,14 +355,16 @@ INSERT INTO Status (JobStatus,JobStatusLong) VALUES
    ('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