This commit was manufactured by cvs2svn to create tag

[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 daa339907a5ab05dd7319d763c4825d024ab0540..2bbd71b2473dc0262a2977d8e04fbda845d45c04 100644 (file)
--- a/bacula/src/cats/make_mysql_tables.in
+++ b/bacula/src/cats/make_mysql_tables.in
@@ -4,45 +4,52 @@
 #
 bindir=@SQL_BINDIR@
 
-if $bindir/mysql -f <<END-OF-DATA
+if $bindir/mysql $* -f <<END-OF-DATA
 USE bacula;
 CREATE TABLE Filename (
   FilenameId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
   Name BLOB NOT NULL,
   PRIMARY KEY(FilenameId),
-  INDEX (Name(30))
+  INDEX (Name(255))
   );
 
 CREATE TABLE Path (
    PathId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    Path BLOB NOT NULL,
    PRIMARY KEY(PathId),
-   INDEX (Path(50))
+   INDEX (Path(255))
    );
 
 
 # ****FIXME**** make FileId BIGINT someday when MySQL works *****
 CREATE TABLE File (
    FileId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
-   FileIndex INTEGER UNSIGNED NOT NULL,
+   FileIndex INTEGER UNSIGNED NOT NULL DEFAULT 0,
    JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
    PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
-   FilenameId INTEGER NOT NULL REFERENCES Filename,
+   FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
    MarkId INTEGER UNSIGNED NOT NULL DEFAULT 0,
    LStat TINYBLOB NOT NULL,
    MD5 TINYBLOB NOT NULL,
    PRIMARY KEY(FileId),
-   INDEX (JobId),
-   INDEX (PathId),
-   INDEX (FilenameId)
+   INDEX (JobId, PathId, FilenameId)
    );
 
+#
+# Possibly add one or more of the following indexes
+#  to the above File table if your Verifies are
+#  too slow.
+#
+#  INDEX (JobId),
+#  INDEX (PathId),
+#  INDEX (FilenameId),
+#  INDEX (FilenameId, PathId)
+#
 
 CREATE TABLE Job (
    JobId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    Job TINYBLOB NOT NULL,
    Name TINYBLOB NOT NULL,
-   PurgedFiles TINYINT NOT NULL DEFAULT 0,
    Type BINARY(1) NOT NULL,
    Level BINARY(1) NOT NULL,
    ClientId INTEGER NOT NULL REFERENCES Client,
@@ -51,14 +58,16 @@ CREATE TABLE Job (
    StartTime DATETIME NOT NULL,
    EndTime DATETIME NOT NULL,
    JobTDate BIGINT UNSIGNED NOT NULL,
-   VolSessionId INTEGER UNSIGNED NOT NULL,
-   VolSessionTime INTEGER UNSIGNED NOT NULL,
-   JobFiles INTEGER UNSIGNED NOT NULL,
+   VolSessionId INTEGER UNSIGNED NOT NULL DEFAULT 0,
+   VolSessionTime INTEGER UNSIGNED NOT NULL DEFAULT 0,
+   JobFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
    JobBytes BIGINT UNSIGNED NOT NULL,
-   JobErrors INTEGER UNSIGNED NOT NULL,
-   JobMissingFiles INTEGER UNSIGNED NOT NULL,
+   JobErrors INTEGER UNSIGNED NOT NULL DEFAULT 0,
+   JobMissingFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
    PoolId INTEGER UNSIGNED NOT NULL REFERENCES Pool,
    FileSetId INTEGER UNSIGNED NOT NULL REFERENCES FileSet,
+   PurgedFiles TINYINT NOT NULL DEFAULT 0,
+   HasBase TINYINT NOT NULL DEFAULT 0,
    PRIMARY KEY(JobId),
    INDEX (Name(128))
    );
@@ -68,6 +77,7 @@ CREATE TABLE FileSet (
    FileSetId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    FileSet TINYBLOB NOT NULL,
    MD5 TINYBLOB NOT NULL,
+   CreateTime DATETIME NOT NULL,
    PRIMARY KEY(FileSetId)
    );
 
@@ -75,12 +85,13 @@ CREATE TABLE JobMedia (
    JobMediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
    MediaId INTEGER UNSIGNED NOT NULL REFERENCES Media,
-   FirstIndex INTEGER UNSIGNED NOT NULL,
-   LastIndex INTEGER UNSIGNED NOT NULL,
-   StartFile INTEGER UNSIGNED NOT NULL,
-   EndFile INTEGER UNSIGNED NOT NULL,
-   StartBlock INTEGER UNSIGNED NOT NULL,
-   EndBlock INTEGER UNSIGNED NOT NULL,
+   FirstIndex INTEGER UNSIGNED NOT NULL DEFAULT 0,
+   LastIndex INTEGER UNSIGNED NOT NULL DEFAULT 0,
+   StartFile INTEGER UNSIGNED NOT NULL DEFAULT 0,
+   EndFile INTEGER UNSIGNED NOT NULL DEFAULT 0,
+   StartBlock INTEGER UNSIGNED NOT NULL DEFAULT 0,
+   EndBlock INTEGER UNSIGNED NOT NULL DEFAULT 0,
+   VolIndex INTEGER UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY(JobMediaId),
    INDEX (JobId, MediaId)
    );
@@ -95,22 +106,28 @@ CREATE TABLE Media (
    FirstWritten DATETIME NOT NULL,
    LastWritten DATETIME NOT NULL,
    LabelDate DATETIME NOT NULL,
-   VolJobs INTEGER UNSIGNED NOT NULL,
-   VolFiles INTEGER UNSIGNED NOT NULL,
-   VolBlocks INTEGER UNSIGNED NOT NULL,
-   VolMounts INTEGER UNSIGNED NOT NULL,
-   VolBytes BIGINT UNSIGNED NOT NULL,
-   VolErrors INTEGER UNSIGNED NOT NULL,
-   VolWrites INTEGER UNSIGNED NOT NULL,
-   MaxVolBytes BIGINT UNSIGNED NOT NULL,
+   VolJobs INTEGER UNSIGNED NOT NULL DEFAULT 0,
+   VolFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
+   VolBlocks INTEGER UNSIGNED NOT NULL DEFAULT 0,
+   VolMounts INTEGER UNSIGNED NOT NULL DEFAULT 0,
+   VolBytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
+   VolErrors INTEGER UNSIGNED NOT NULL DEFAULT 0,
+   VolWrites INTEGER UNSIGNED NOT NULL DEFAULT 0,
    VolCapacityBytes BIGINT UNSIGNED NOT NULL,
    VolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
-    'Read-Only', 'Disabled', 'Error', 'Busy', 'Used') NOT NULL,
-   Recycle TINYINT NOT NULL,
-   VolRetention BIGINT UNSIGNED NOT NULL,
-   VolUseDuration BIGINT UNSIGNED NOT NULL,
-   MaxVolJobs INTEGER UNSIGNED NOT NULL,
-   MaxVolFiles INTEGER UNSIGNED NOT NULL,
+    'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
+   Recycle TINYINT NOT NULL DEFAULT 0,
+   VolRetention BIGINT UNSIGNED NOT NULL DEFAULT 0,
+   VolUseDuration BIGINT UNSIGNED NOT NULL DEFAULT 0,
+   MaxVolJobs INTEGER UNSIGNED NOT NULL DEFAULT 0,
+   MaxVolFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
+   MaxVolBytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
+   InChanger TINYINT NOT NULL DEFAULT 0,
+   MediaAddressing TINYINT NOT NULL DEFAULT 0,
+   VolReadTime BIGINT UNSIGNED NOT NULL DEFAULT 0,
+   VolWriteTime BIGINT UNSIGNED NOT NULL DEFAULT 0,
+   EndFile INTEGER UNSIGNED NOT NULL DEFAULT 0,
+   EndBlock INTEGER UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY(MediaId),
    INDEX (PoolId)
    );
@@ -118,20 +135,23 @@ CREATE TABLE Media (
 CREATE TABLE Pool (
    PoolId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    Name TINYBLOB NOT NULL,
-   NumVols INTEGER UNSIGNED NOT NULL,
-   MaxVols INTEGER UNSIGNED NOT NULL,
+   NumVols INTEGER UNSIGNED NOT NULL DEFAULT 0,
+   MaxVols INTEGER UNSIGNED NOT NULL DEFAULT 0,
    UseOnce TINYINT NOT NULL,
    UseCatalog TINYINT NOT NULL,
    AcceptAnyVolume TINYINT DEFAULT 0,
    VolRetention BIGINT UNSIGNED NOT NULL,
    VolUseDuration BIGINT UNSIGNED NOT NULL,
-   MaxVolJobs INTEGER UNSIGNED NOT NULL,
-   MaxVolFiles INTEGER UNSIGNED NOT NULL,
+   MaxVolJobs INTEGER UNSIGNED NOT NULL DEFAULT 0,
+   MaxVolFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
    MaxVolBytes BIGINT UNSIGNED NOT NULL,
    AutoPrune TINYINT DEFAULT 0,
    Recycle TINYINT DEFAULT 0,
-   PoolType ENUM('Backup', 'Copy', 'Cloned', 'Archive', 'Migration') NOT NULL,
+   PoolType ENUM('Backup', 'Copy', 'Cloned', 'Archive', 'Migration', 'Scratch') NOT NULL,
    LabelFormat TINYBLOB,
+   Enabled TINYINT DEFAULT 1,
+   ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
+   RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
    UNIQUE (Name(128)),
    PRIMARY KEY (PoolId)
    );
@@ -148,56 +168,51 @@ CREATE TABLE Client (
    PRIMARY KEY(ClientId)
    );
 
+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,
+   FileIndex INTEGER UNSIGNED,
+   PRIMARY KEY(BaseId)
+   );
+
+CREATE TABLE UnsavedFiles (
+   UnsavedId INTEGER UNSIGNED AUTO_INCREMENT,
+   JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
+   PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
+   FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
+   PRIMARY KEY (UnsavedId)
+   );
+
+
 CREATE TABLE Version (
    VersionId INTEGER UNSIGNED NOT NULL 
    );
 
 -- Initialize Version           
-INSERT INTO Version (VersionId) VALUES (5);
+INSERT INTO Version (VersionId) VALUES (8);
 
 CREATE TABLE Counters (
    Counter TINYBLOB NOT NULL,
-   PoolId INTEGER UNSIGNED NOT NULL REFERENCES Pool,
    MinValue INTEGER,
    MaxValue INTEGER,
    CurrentValue INTEGER,
-   WrapCounter TINYBLOB NOT NULL
+   WrapCounter TINYBLOB NOT NULL,
+   PRIMARY KEY (Counter(128))
    );
 
-## Experimental
-#CREATE TABLE FileSave (
-#   FileSaveId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
-#   FileIndex INTEGER UNSIGNED NOT NULL,
-#   JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
-#   FileId INTEGER UNSIGNED NOT NULL REFERENCES File,
-#   VLStat TINYBLOB NOT NULL,
-#   PRIMARY KEY(FileSaveId),
-#   );
-
-# Experimental stuff below. Not used.
-# Invariant part of File
-#CREATE TABLE BaseFile (
-#   FileId INTEGER UNSIGNED AUTOINCREMENT,
-#   PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
-#   FilenameId INTEGER REFERENCES Filename NOT NULL,
-#   LStat VARCHAR(255) NOT NULL,
-#   MD5 VARCHAR(25) NOT NULL,
-#   PRIMARY KEY(FileId) 
-#   );
-
-# Variable part of File
-#CREATE TABLE FileSave (
-#   FileId INTEGER UNSIGNED REFERENCES BaseFile NOT NULL,
-#   FileIndex INTEGER UNSIGNED NOT NULL,
-#   JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
-#   PRIMARY KEY(FileId) 
-#   );
+CREATE TABLE CDImages (
+   MediaId INTEGER UNSIGNED NOT NULL,
+   LastBurn DATETIME NOT NULL,
+   PRIMARY KEY (MediaId)
+   );
 
 
 END-OF-DATA
 then
-   echo "Creation of Bacula tables succeeded."
+   echo "Creation of Bacula MySQL tables succeeded."
 else
-   echo "Creation of Bacula tables failed."
+   echo "Creation of Bacula MySQL tables failed."
 fi
 exit 0