X-Git-Url: https://git.sur5r.net/?a=blobdiff_plain;f=bacula%2Fsrc%2Fcats%2Fmake_mysql_tables.in;h=0c1ce16324ca4d61c60392cd4ccbe1b6de53e3be;hb=8e81c40b1346cdbfd0bbb5f7557e7cdb804a553f;hp=549282b4ce0e628804126b1c7381b247ca68cb32;hpb=c6a1b2d710b28a7a5801698d1b908fc1d3fd561e;p=bacula%2Fbacula diff --git a/bacula/src/cats/make_mysql_tables.in b/bacula/src/cats/make_mysql_tables.in index 549282b4ce..0c1ce16324 100644 --- a/bacula/src/cats/make_mysql_tables.in +++ b/bacula/src/cats/make_mysql_tables.in @@ -29,13 +29,13 @@ CREATE TABLE Path ( CREATE TABLE File ( FileId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, - FileIndex INTEGER UNSIGNED NOT NULL DEFAULT 0, + FileIndex INTEGER UNSIGNED DEFAULT 0, JobId INTEGER UNSIGNED NOT NULL REFERENCES Job, PathId INTEGER UNSIGNED NOT NULL REFERENCES Path, FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename, - MarkId INTEGER UNSIGNED NOT NULL DEFAULT 0, + MarkId INTEGER UNSIGNED DEFAULT 0, LStat TINYBLOB NOT NULL, - MD5 TINYBLOB NOT NULL, + MD5 TINYBLOB, PRIMARY KEY(FileId), INDEX (JobId), INDEX (JobId, PathId, FilenameId) @@ -69,8 +69,8 @@ CREATE TABLE Storage ( CREATE TABLE Device ( DeviceId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, Name TINYBLOB NOT NULL, - MediaTypeId INTEGER UNSIGNED NOT NULL REFERENCES MediaType, - StorageId INTEGER UNSIGNED NOT NULL REFERENCES Storage, + MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType, + StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage, DevMounts INTEGER UNSIGNED DEFAULT 0, DevReadBytes BIGINT UNSIGNED DEFAULT 0, DevWriteBytes BIGINT UNSIGNED DEFAULT 0, @@ -92,50 +92,56 @@ CREATE TABLE Job ( Name TINYBLOB NOT NULL, Type BINARY(1) NOT NULL, Level BINARY(1) NOT NULL, - ClientId INTEGER NOT NULL REFERENCES Client, + ClientId INTEGER DEFAULT 0 REFERENCES Client, JobStatus BINARY(1) NOT NULL, - SchedTime DATETIME NOT NULL, - StartTime DATETIME NOT NULL, - EndTime DATETIME NOT NULL, - JobTDate BIGINT 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 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, + 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, + JobErrors INTEGER UNSIGNED DEFAULT 0, + JobMissingFiles INTEGER UNSIGNED DEFAULT 0, + PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool, + FileSetId INTEGER UNSIGNED DEFAULT 0 REFERENCES FileSet, + PriorJobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job, + PurgedFiles TINYINT DEFAULT 0, + HasBase TINYINT DEFAULT 0, PRIMARY KEY(JobId), INDEX (Name(128)) ); -CREATE TABLE MAC ( - JobId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, - OriginalJobId INTEGER UNSIGNED NOT NULL, - JobType BINARY(1) NOT NULL, - JobLevel BINARY(1) NOT NULL, - SchedTime DATETIME NOT NULL, - StartTime DATETIME NOT NULL, - EndTime DATETIME NOT NULL, - JobTDate BIGINT UNSIGNED NOT NULL, - PRIMARY KEY(JobId) - ); 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, FileSet TINYBLOB NOT NULL, - MD5 TINYBLOB NOT NULL, - CreateTime DATETIME NOT NULL, + MD5 TINYBLOB, + CreateTime DATETIME DEFAULT 0, PRIMARY KEY(FileSetId) ); @@ -143,15 +149,15 @@ 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 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, - Copy INTEGER UNSIGNED NOT NULL DEFAULT 0, - Stripe INTEGER UNSIGNED NOT NULL DEFAULT 0, + FirstIndex INTEGER UNSIGNED DEFAULT 0, + LastIndex INTEGER UNSIGNED DEFAULT 0, + StartFile INTEGER UNSIGNED DEFAULT 0, + EndFile INTEGER UNSIGNED DEFAULT 0, + 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) ); @@ -160,44 +166,46 @@ CREATE TABLE JobMedia ( CREATE TABLE Media ( MediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, VolumeName TINYBLOB NOT NULL, - Slot INTEGER NOT NULL DEFAULT 0, - PoolId INTEGER UNSIGNED NOT NULL REFERENCES Pool, + Slot INTEGER DEFAULT 0, + PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool, MediaType TINYBLOB NOT NULL, - MediaTypeId INTEGER UNSIGNED NOT NULL REFERENCES MediaType, - LabelType TINYINT NOT NULL DEFAULT 0, - FirstWritten DATETIME NOT NULL, - LastWritten DATETIME NOT NULL, - LabelDate DATETIME 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, - VolParts INTEGER UNSIGNED NOT NULL DEFAULT 0, - VolErrors INTEGER UNSIGNED NOT NULL DEFAULT 0, - VolWrites INTEGER UNSIGNED NOT NULL DEFAULT 0, - VolCapacityBytes BIGINT UNSIGNED NOT NULL, + MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType, + LabelType TINYINT DEFAULT 0, + FirstWritten DATETIME DEFAULT 0, + LastWritten DATETIME DEFAULT 0, + LabelDate DATETIME DEFAULT 0, + VolJobs INTEGER UNSIGNED DEFAULT 0, + VolFiles INTEGER UNSIGNED DEFAULT 0, + VolBlocks INTEGER UNSIGNED DEFAULT 0, + VolMounts INTEGER UNSIGNED DEFAULT 0, + VolBytes BIGINT UNSIGNED DEFAULT 0, + VolParts INTEGER UNSIGNED DEFAULT 0, + VolErrors INTEGER UNSIGNED DEFAULT 0, + VolWrites INTEGER UNSIGNED DEFAULT 0, + VolCapacityBytes BIGINT UNSIGNED DEFAULT 0, VolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged', '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, - StorageId INTEGER UNSIGNED NOT NULL REFERENCES Storage, - DeviceId INTEGER UNSIGNED NOT NULL REFERENCES Device, - 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, - LocationId INTEGER UNSIGNED NOT NULL REFERENCES Location, + Enabled TINYINT DEFAULT 1, + Recycle TINYINT DEFAULT 0, + VolRetention BIGINT UNSIGNED DEFAULT 0, + VolUseDuration BIGINT UNSIGNED DEFAULT 0, + MaxVolJobs INTEGER UNSIGNED DEFAULT 0, + MaxVolFiles INTEGER UNSIGNED DEFAULT 0, + MaxVolBytes BIGINT UNSIGNED DEFAULT 0, + InChanger TINYINT DEFAULT 0, + StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage, + DeviceId INTEGER UNSIGNED DEFAULT 0 REFERENCES Device, + MediaAddressing TINYINT DEFAULT 0, + VolReadTime BIGINT UNSIGNED DEFAULT 0, + VolWriteTime BIGINT UNSIGNED DEFAULT 0, + EndFile INTEGER UNSIGNED DEFAULT 0, + EndBlock INTEGER UNSIGNED DEFAULT 0, + LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location, RecycleCount INTEGER UNSIGNED DEFAULT 0, - InitialWrite DATETIME NOT NULL, + InitialWrite DATETIME DEFAULT 0, ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool, RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool, + Comment BLOB, PRIMARY KEY(MediaId), INDEX (PoolId) ); @@ -209,20 +217,20 @@ CREATE INDEX inx8 ON Media (PoolId); CREATE TABLE Pool ( PoolId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, Name TINYBLOB NOT NULL, - NumVols INTEGER UNSIGNED NOT NULL DEFAULT 0, - MaxVols INTEGER UNSIGNED NOT NULL DEFAULT 0, - UseOnce TINYINT NOT NULL, - UseCatalog TINYINT NOT NULL, + NumVols INTEGER UNSIGNED DEFAULT 0, + MaxVols INTEGER UNSIGNED DEFAULT 0, + UseOnce TINYINT DEFAULT 0, + UseCatalog TINYINT DEFAULT 0, AcceptAnyVolume TINYINT DEFAULT 0, - VolRetention BIGINT UNSIGNED NOT NULL, - VolUseDuration BIGINT UNSIGNED NOT NULL, - MaxVolJobs INTEGER UNSIGNED NOT NULL DEFAULT 0, - MaxVolFiles INTEGER UNSIGNED NOT NULL DEFAULT 0, - MaxVolBytes BIGINT UNSIGNED NOT NULL, + VolRetention BIGINT UNSIGNED DEFAULT 0, + VolUseDuration BIGINT UNSIGNED DEFAULT 0, + MaxVolJobs INTEGER UNSIGNED DEFAULT 0, + MaxVolFiles INTEGER UNSIGNED DEFAULT 0, + MaxVolBytes BIGINT UNSIGNED DEFAULT 0, AutoPrune TINYINT DEFAULT 0, Recycle TINYINT DEFAULT 0, PoolType ENUM('Backup', 'Copy', 'Cloned', 'Archive', 'Migration', 'Scratch') NOT NULL, - LabelType TINYINT NOT NULL DEFAULT 0, + LabelType TINYINT DEFAULT 0, LabelFormat TINYBLOB, Enabled TINYINT DEFAULT 1, ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool, @@ -241,12 +249,22 @@ CREATE TABLE Client ( Name TINYBLOB NOT NULL, Uname TINYBLOB NOT NULL, /* full uname -a of client */ AutoPrune TINYINT DEFAULT 0, - FileRetention BIGINT UNSIGNED NOT NULL, - JobRetention BIGINT UNSIGNED NOT NULL, + FileRetention BIGINT UNSIGNED DEFAULT 0, + JobRetention BIGINT UNSIGNED DEFAULT 0, UNIQUE (Name(128)), 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, @@ -268,9 +286,9 @@ CREATE TABLE UnsavedFiles ( CREATE TABLE Counters ( Counter TINYBLOB NOT NULL, - MinValue INTEGER, - MaxValue INTEGER, - CurrentValue INTEGER, + MinValue INTEGER DEFAULT 0, + MaxValue INTEGER DEFAULT 0, + CurrentValue INTEGER DEFAULT 0, WrapCounter TINYBLOB NOT NULL, PRIMARY KEY (Counter(128)) ); @@ -313,7 +331,7 @@ CREATE TABLE Version ( ); -- Initialize Version -INSERT INTO Version (VersionId) VALUES (9); +INSERT INTO Version (VersionId) VALUES (10); END-OF-DATA then