From b04e8bf2781c35d4f4e2835c1ee26a36519e278a Mon Sep 17 00:00:00 2001 From: Jan Huisink Date: Wed, 3 May 2017 15:35:09 +0200 Subject: [PATCH] updated MySQL creation schema to current standards --- src/cats/ddl/creates/mysql.sql | 74 +++++++++++++++++++++--------------------- 1 file changed, 37 insertions(+), 37 deletions(-) diff --git a/src/cats/ddl/creates/mysql.sql b/src/cats/ddl/creates/mysql.sql index 74ed62e7..755b50ba 100644 --- a/src/cats/ddl/creates/mysql.sql +++ b/src/cats/ddl/creates/mysql.sql @@ -89,8 +89,8 @@ CREATE TABLE Storage ( CREATE TABLE Device ( DeviceId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, Name TINYBLOB NOT NULL, - MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType, - StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage, + MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType(MediaTypeId), + StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage(StorageId), DevMounts INTEGER UNSIGNED DEFAULT 0, DevReadBytes BIGINT UNSIGNED DEFAULT 0, DevWriteBytes BIGINT UNSIGNED DEFAULT 0, @@ -100,7 +100,7 @@ CREATE TABLE Device ( DevWriteTime BIGINT UNSIGNED DEFAULT 0, DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0, DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0, - CleaningDate DATETIME DEFAULT 0, + CleaningDate DATETIME DEFAULT NULL, CleaningPeriod BIGINT UNSIGNED DEFAULT 0, PRIMARY KEY(DeviceId) ); @@ -111,12 +111,12 @@ CREATE TABLE Job ( Name TINYBLOB NOT NULL, Type BINARY(1) NOT NULL, Level BINARY(1) NOT NULL, - ClientId INTEGER DEFAULT 0 REFERENCES Client, + ClientId INTEGER DEFAULT 0 REFERENCES Client(ClientId), JobStatus BINARY(1) NOT NULL, - SchedTime DATETIME DEFAULT 0, - StartTime DATETIME DEFAULT 0, - EndTime DATETIME DEFAULT 0, - RealEndTime DATETIME DEFAULT 0, + SchedTime DATETIME DEFAULT NULL, + StartTime DATETIME DEFAULT NULL, + EndTime DATETIME DEFAULT NULL, + RealEndTime DATETIME DEFAULT NULL, JobTDate BIGINT UNSIGNED DEFAULT 0, VolSessionId INTEGER UNSIGNED DEFAULT 0, VolSessionTime INTEGER UNSIGNED DEFAULT 0, @@ -125,9 +125,9 @@ CREATE TABLE Job ( ReadBytes 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, + PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool(PoolId), + FileSetId INTEGER UNSIGNED DEFAULT 0 REFERENCES FileSet(FileSetId), + PriorJobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job(JobId), PurgedFiles TINYINT DEFAULT 0, HasBase TINYINT DEFAULT 0, HasCache TINYINT DEFAULT 0, @@ -146,10 +146,10 @@ CREATE TABLE JobHisto ( 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, + SchedTime DATETIME DEFAULT NULL, + StartTime DATETIME DEFAULT NULL, + EndTime DATETIME DEFAULT NULL, + RealEndTime DATETIME DEFAULT NULL, JobTDate BIGINT UNSIGNED DEFAULT 0, VolSessionId INTEGER UNSIGNED DEFAULT 0, VolSessionTime INTEGER UNSIGNED DEFAULT 0, @@ -180,10 +180,10 @@ CREATE TABLE Location ( CREATE TABLE LocationLog ( LocLogId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, - Date DATETIME DEFAULT 0, + Date DATETIME DEFAULT NULL, Comment BLOB NOT NULL, - MediaId INTEGER UNSIGNED DEFAULT 0 REFERENCES Media, - LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location, + MediaId INTEGER UNSIGNED DEFAULT 0 REFERENCES Media(MediaId), + LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location(LocationId), NewVolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged', 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL, NewEnabled TINYINT, @@ -194,7 +194,7 @@ CREATE TABLE FileSet ( FileSetId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, FileSet TINYBLOB NOT NULL, MD5 TINYBLOB, - CreateTime DATETIME DEFAULT 0, + CreateTime DATETIME DEFAULT NULL, FileSetText BLOB NOT NULL, PRIMARY KEY(FileSetId) ); @@ -218,13 +218,13 @@ CREATE TABLE Media ( MediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, VolumeName TINYBLOB NOT NULL, Slot INTEGER DEFAULT 0, - PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool, + PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool(PoolId), MediaType TINYBLOB NOT NULL, - MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType, + MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType(MediaTypeId), LabelType TINYINT DEFAULT 0, - FirstWritten DATETIME DEFAULT 0, - LastWritten DATETIME DEFAULT 0, - LabelDate DATETIME DEFAULT 0, + FirstWritten DATETIME DEFAULT NULL, + LastWritten DATETIME DEFAULT NULL, + LabelDate DATETIME DEFAULT NULL, VolJobs INTEGER UNSIGNED DEFAULT 0, VolFiles INTEGER UNSIGNED DEFAULT 0, VolBlocks INTEGER UNSIGNED DEFAULT 0, @@ -245,20 +245,20 @@ CREATE TABLE Media ( 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, + StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage(StorageId), + DeviceId INTEGER UNSIGNED DEFAULT 0 REFERENCES Device(DeviceId), 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, + LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location(LocationId), RecycleCount INTEGER UNSIGNED DEFAULT 0, MinBlockSize INTEGER UNSIGNED DEFAULT 0, MaxBlockSize INTEGER UNSIGNED DEFAULT 0, - InitialWrite DATETIME DEFAULT 0, - ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool, - RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool, + InitialWrite DATETIME DEFAULT NULL, + ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool(PoolId), + RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool(PoolId), EncryptionKey TINYBLOB, Comment BLOB, PRIMARY KEY(MediaId), @@ -311,8 +311,8 @@ CREATE TABLE Client ( CREATE TABLE Log ( LogId INTEGER UNSIGNED AUTO_INCREMENT, - JobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job, - Time DATETIME DEFAULT 0, + JobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job(JobId), + Time DATETIME DEFAULT NULL, LogText BLOB NOT NULL, PRIMARY KEY(LogId), INDEX (JobId) @@ -386,19 +386,19 @@ CREATE TABLE Quota ( ); CREATE TABLE NDMPLevelMap ( - ClientId INTEGER DEFAULT 0 REFERENCES Client, - FileSetId INTEGER UNSIGNED DEFAULT 0 REFERENCES FileSet, + ClientId INTEGER DEFAULT 0 REFERENCES Client(ClientId), + FileSetId INTEGER UNSIGNED DEFAULT 0 REFERENCES FileSet(FileSetId), FileSystem TINYBLOB NOT NULL, DumpLevel INTEGER NOT NULL, - CONSTRAINT NDMPLevelMap_pkey PRIMARY KEY (ClientId, FilesetId, FileSystem(256)) + CONSTRAINT NDMPLevelMap_pkey PRIMARY KEY (ClientId, FilesetId, FileSystem(255)) ); CREATE TABLE NDMPJobEnvironment ( - JobId INTEGER UNSIGNED NOT NULL REFERENCES Job, + JobId INTEGER UNSIGNED NOT NULL REFERENCES Job(JobId), FileIndex INTEGER UNSIGNED NOT NULL, EnvName TINYBLOB NOT NULL, EnvValue TINYBLOB NOT NULL, - CONSTRAINT NDMPJobEnvironment_pkey PRIMARY KEY (JobId, FileIndex, EnvName(256)) + CONSTRAINT NDMPJobEnvironment_pkey PRIMARY KEY (JobId, FileIndex, EnvName(255)) ); CREATE TABLE DeviceStats ( -- 2.11.0 (Apple Git-81)