X-Git-Url: https://git.sur5r.net/?a=blobdiff_plain;f=bacula%2Fsrc%2Fcats%2Fmake_mysql_tables.in;h=71b4fff695213a36a76e9a6c210b47ee8a86948d;hb=c9657772721a3782558c4eb8b99978c7873b45fd;hp=8ce92132c07862e0716d5e6ca2b5abccff1f9a50;hpb=c2d212a329778e22987d38efb820e162141b5f27;p=bacula%2Fbacula

diff --git a/bacula/src/cats/make_mysql_tables.in b/bacula/src/cats/make_mysql_tables.in
index 8ce92132c0..71b4fff695 100644
--- a/bacula/src/cats/make_mysql_tables.in
+++ b/bacula/src/cats/make_mysql_tables.in
@@ -2,10 +2,16 @@
 #
 # shell script to create Bacula MySQL tables
 #
+# Important note: 
+#   You won't get any support for performance issue if you changed the default
+#   schema.
+
 bindir=@SQL_BINDIR@
+PATH="$bindir:$PATH"
+db_name=${db_name:-@db_name@}
 
-if $bindir/mysql $* -f <<END-OF-DATA
-USE bacula;
+if mysql $* -f <<END-OF-DATA
+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 +32,22 @@ CREATE TABLE Path (
    INDEX (Path(255))
    );
 
-
+-- We strongly recommend to avoid the temptation to add new indexes.
+-- In general, these will cause very significant performance
+-- problems in other areas.  A better approch is to carefully check
+-- that all your memory configuation parameters are
+-- suitable for the size of your installation.	If you backup
+-- millions of files, you need to adapt the database memory
+-- configuration parameters concerning sorting, joining and global
+-- memory.  By default, sort and join parameters are very small
+-- (sometimes 8Kb), and having sufficient memory specified by those
+-- parameters is extremely important to run fast.  
+
+-- 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,
@@ -41,15 +60,30 @@ CREATE TABLE File (
    INDEX (JobId, PathId, FilenameId)
    );
 
+CREATE TABLE RestoreObject (
+   RestoreObjectId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
+   ObjectName BLOB NOT NULL,
+   RestoreObject LONGBLOB NOT NULL,
+   PluginName TINYBLOB NOT NULL,
+   ObjectLength INTEGER DEFAULT 0,
+   ObjectFullLength INTEGER DEFAULT 0,
+   ObjectIndex INTEGER DEFAULT 0,
+   ObjectType INTEGER DEFAULT 0,
+   FileIndex INTEGER UNSIGNED DEFAULT 0,
+   JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
+   ObjectCompression INTEGER DEFAULT 0,
+   PRIMARY KEY(RestoreObjectId),
+   INDEX (JobId)
+   );
+
+
 #
 # Possibly add one or more of the following indexes
 #  to the above File table if your Verifies are
-#  too slow.
+#  too slow, but they can slow down backups.
 #
 #  INDEX (PathId),
 #  INDEX (FilenameId),
-#  INDEX (FilenameId, PathId)
-#  INDEX (JobId),
 #
 
 CREATE TABLE MediaType (
@@ -103,6 +137,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,
@@ -110,10 +145,45 @@ CREATE TABLE Job (
    PriorJobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job,
    PurgedFiles TINYINT DEFAULT 0,
    HasBase TINYINT DEFAULT 0,
+   HasCache TINYINT DEFAULT 0,
+   Reviewed TINYINT DEFAULT 0,
+   Comment BLOB,
    PRIMARY KEY(JobId),
    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,
+   HasCache TINYINT DEFAULT 0,
+   Reviewed TINYINT DEFAULT 0,
+   Comment BLOB,
+   INDEX (JobId),
+   INDEX (StartTime)
+   );
 
 CREATE TABLE Location (
    LocationId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
@@ -123,6 +193,19 @@ CREATE TABLE Location (
    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,
@@ -143,8 +226,6 @@ CREATE TABLE JobMedia (
    StartBlock INTEGER UNSIGNED DEFAULT 0,
    EndBlock INTEGER UNSIGNED DEFAULT 0,
    VolIndex INTEGER UNSIGNED DEFAULT 0,
-   Copy INTEGER UNSIGNED DEFAULT 0,
-   Stripe INTEGER UNSIGNED DEFAULT 0,
    PRIMARY KEY(JobMediaId),
    INDEX (JobId, MediaId)
    );
@@ -174,6 +255,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,
@@ -192,14 +274,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,
@@ -215,6 +295,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,
@@ -242,8 +323,11 @@ CREATE TABLE Client (
    );
 
 CREATE TABLE Log (
-   JobId INTEGER INTEGER UNSIGNED DEFAULT 0 REFERENCES JobId,
+   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)
    );
 
@@ -252,11 +336,13 @@ 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)
    );
 
+CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
+
 CREATE TABLE UnsavedFiles (
    UnsavedId INTEGER UNSIGNED AUTO_INCREMENT,
    JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
@@ -269,8 +355,8 @@ CREATE TABLE UnsavedFiles (
 
 CREATE TABLE Counters (
    Counter TINYBLOB NOT NULL,
-   MinValue INTEGER DEFAULT 0,
-   MaxValue INTEGER DEFAULT 0,
+   'MinValue' INTEGER DEFAULT 0,
+   'MaxValue' INTEGER DEFAULT 0,
    CurrentValue INTEGER DEFAULT 0,
    WrapCounter TINYBLOB NOT NULL,
    PRIMARY KEY (Counter(128))
@@ -284,37 +370,61 @@ CREATE TABLE CDImages (
 
 CREATE TABLE Status (
    JobStatus CHAR(1) BINARY NOT NULL,
-   JobStatusLong BLOB, 
+   JobStatusLong BLOB,
+   Severity INT,
    PRIMARY KEY (JobStatus)
    );
 
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
-   ('C', 'Created, not yet running'),
-   ('R', 'Running'),
-   ('B', 'Blocked'),
-   ('T', 'Completed successfully'),
-   ('E', 'Terminated with errors'),
-   ('e', 'Non-fatal error'),
-   ('f', 'Fatal error'),
-   ('D', 'Verify found differences'),
-   ('A', 'Canceled by user'),
-   ('F', 'Waiting for Client'),
-   ('S', 'Waiting for Storage daemon'),
-   ('m', 'Waiting for new media'),
-   ('M', 'Waiting for media mount'),
-   ('s', 'Waiting for storage resource'),
-   ('j', 'Waiting for job resource'),
-   ('c', 'Waiting for client resource'),
-   ('d', 'Waiting on maximum jobs'),
-   ('t', 'Waiting on start time'),
-   ('p', 'Waiting on higher priority jobs');
+INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
+   ('C', 'Created, not yet running',15),
+   ('R', 'Running',15),
+   ('B', 'Blocked',15),
+   ('T', 'Completed successfully',10),
+   ('E', 'Terminated with errors',25),
+   ('e', 'Non-fatal error',20),
+   ('f', 'Fatal error',100),
+   ('D', 'Verify found differences',15),
+   ('A', 'Canceled by user',90),
+   ('F', 'Waiting for Client',15),
+   ('S', 'Waiting for Storage daemon',15),
+   ('m', 'Waiting for new media',15),
+   ('M', 'Waiting for media mount',15),
+   ('s', 'Waiting for storage resource',15),
+   ('j', 'Waiting for job resource',15),
+   ('c', 'Waiting for client resource',15),
+   ('d', 'Waiting on maximum jobs',15),
+   ('t', 'Waiting on start time',15),
+   ('p', 'Waiting on higher priority jobs',15),
+   ('i', 'Doing batch insert file records',15),
+   ('a', 'SD despooling attributes',15);
+
+CREATE TABLE PathHierarchy
+(
+     PathId integer NOT NULL,
+     PPathId integer NOT NULL,
+     CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
+);
+
+CREATE INDEX pathhierarchy_ppathid 
+	  ON PathHierarchy (PPathId);
+
+CREATE TABLE PathVisibility
+(
+      PathId integer NOT NULL,
+      JobId integer NOT NULL,
+      Size int8 DEFAULT 0,
+      Files int4 DEFAULT 0,
+      CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
+);
+CREATE INDEX pathvisibility_jobid
+	     ON PathVisibility (JobId);
 
 CREATE TABLE Version (
    VersionId INTEGER UNSIGNED NOT NULL 
    );
 
 -- Initialize Version		 
-INSERT INTO Version (VersionId) VALUES (10);
+INSERT INTO Version (VersionId) VALUES (@BDB_VERSION@);
 
 END-OF-DATA
 then