X-Git-Url: https://git.sur5r.net/?a=blobdiff_plain;f=bacula%2Fsrc%2Fcats%2Fmake_mysql_tables.in;h=0c1ce16324ca4d61c60392cd4ccbe1b6de53e3be;hb=e0d658f2ba6596fa7d561cee8d8b5853603269ca;hp=fc4361db651093286cc97a15fe41d26d0f8e9cf0;hpb=2f54e36537e67addb3156d28eed881cd6343afae;p=bacula%2Fbacula diff --git a/bacula/src/cats/make_mysql_tables.in b/bacula/src/cats/make_mysql_tables.in index fc4361db65..0c1ce16324 100644 --- a/bacula/src/cats/make_mysql_tables.in +++ b/bacula/src/cats/make_mysql_tables.in @@ -27,17 +27,17 @@ CREATE TABLE Path ( ); -# ****FIXME**** make FileId BIGINT someday when MySQL works ***** 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,32 +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 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) ); @@ -125,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) ); @@ -142,37 +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, - 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, - 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, + 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 DEFAULT 0, + ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool, + RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool, + Comment BLOB, PRIMARY KEY(MediaId), INDEX (PoolId) ); @@ -184,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, @@ -216,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, @@ -243,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)) ); @@ -288,7 +331,7 @@ CREATE TABLE Version ( ); -- Initialize Version -INSERT INTO Version (VersionId) VALUES (9); +INSERT INTO Version (VersionId) VALUES (10); END-OF-DATA then