ПРИЛОЖЕНИЕ 3 ФАЙЛ Tables.sql

USE db209

GO

CREATE TABLE [dbo].[File]

(

Id INT IDENTITY NOT NULL PRIMARY KEY, --ID файла

Name NVARCHAR(256) NOT NULL UNIQUE CHECK (Name <> ''), -- Имя файла

Content VARBINARY(MAX) NOT NULL

)

CREATE TABLE [dbo].[Position]

(

Id INT IDENTITY NOT NULL PRIMARY KEY, --ID должности

Title NVARCHAR(256) NOT NULL UNIQUE CHECK (Title <> '') -- Наименование должности

)

CREATE TABLE [dbo].[State] -- Состояние задачи

(

Id INT IDENTITY NOT NULL PRIMARY KEY, --ID

Title NVARCHAR(256) NOT NULL UNIQUE CHECK (Title <> ''), -- Название состояния

)

CREATE TABLE [dbo].[Category] -- Категория (Тип) задачи

(

Id INT IDENTITY NOT NULL PRIMARY KEY, --ID

Title NVARCHAR(256) NOT NULL UNIQUE CHECK (Title <> ''), -- Название категории

InitialStateId INT NOT NULL, -- Исходное состояние

FinalStateId INT NOT NULL, -- Целевое состояние

CONSTRAINT [CK_Category_States] CHECK (InitialStateId <> FinalStateId),

CONSTRAINT [FK_Category_InitialState] FOREIGN KEY (InitialStateId) REFERENCES [dbo].[State](Id),

CONSTRAINT [FK_Category_FinalState] FOREIGN KEY (FinalStateId) REFERENCES [dbo].[State](Id),

)

CREATE TABLE [dbo].[StateTransition] -- Переход между состояниями задачи

(

CategoryId INT NOT NULL, -- ID категории задачи

InitialStateId INT NOT NULL, -- Исходное состояние

FinalStateId INT NOT NULL -- Целевое состояние

CONSTRAINT [PK_StateTransition] PRIMARY KEY (CategoryId, InitialStateId, FinalStateId),

CONSTRAINT [FK_StateTransition_Category] FOREIGN KEY (CategoryId) REFERENCES [dbo].[Category](Id) ON UPDATE CASCADE ON DELETE CASCADE,

CONSTRAINT [FK_StateTransition_InitialState] FOREIGN KEY (InitialStateId) REFERENCES [dbo].[State](Id),

CONSTRAINT [FK_StateTransition_FinalState] FOREIGN KEY (FinalStateId) REFERENCES [dbo].[State](Id),

)

CREATE TABLE [dbo].[User]

(

Id INT IDENTITY NOT NULL PRIMARY KEY, --ID пользователя

EMail VARCHAR(129) NOT NULL UNIQUE, --Адрес электронной почты

FirstName NVARCHAR(64) NOT NULL, -- Имя

MiddleName NVARCHAR(64) NOT NULL, -- Второе имя

LastName NVARCHAR(64) NOT NULL, -- Фамилия

Phone VARCHAR(32) NULL, -- Телефон

PositionId INT NOT NULL, -- Должность

PhotoFileId INT NULL, -- Фотография

SysUserId INT NULL, -- Связь с пользователем MS SQL

CONSTRAINT [CK_User_Name] CHECK (FirstName <> '' OR LastName <> ''),

CONSTRAINT [CK_User_EMail] CHECK (EMail LIKE '_%@__%.__%' AND EMail NOT LIKE '%[^-a-z0-9_.@]%'), -- Простая проверка на корректность почтового адреса

CONSTRAINT [CK_User_Phone] CHECK (Phone IS NULL OR Phone NOT LIKE '%[^-+0-9() ]%'), -- Простая проверка на корректность телефона

CONSTRAINT [FK_User_Position] FOREIGN KEY (PositionId) REFERENCES [dbo].[Position](Id) ON UPDATE CASCADE ON DELETE CASCADE,

CONSTRAINT [FK_User_PhotoFile] FOREIGN KEY (PhotoFileId) REFERENCES [dbo].[File](Id) ON UPDATE CASCADE ON DELETE SET DEFAULT,

)

CREATE TABLE [dbo].[Project]

(

Id INT IDENTITY NOT NULL PRIMARY KEY, --ID проекта

ParentId INT NULL,

Title NVARCHAR(256) NOT NULL CHECK (Title <> ''),

CreatorId INT NULL,

ManagerId INT NULL,

StartDate DATE NOT NULL DEFAULT GETDATE(),

EndDate DATE NULL,

Content NVARCHAR(MAX) NOT NULL,

IsDone BIT NOT NULL DEFAULT 0,

ModifyDate DATE NOT NULL DEFAULT GETDATE(),

CONSTRAINT [U_Project_ParentTitle] UNIQUE (ParentId, Title),

CONSTRAINT [CK_Project_EndDate] CHECK (EndDate >= StartDate AND (EndDate IS NOT NULL OR ParentId IS NOT NULL)),

CONSTRAINT [FK_Project_Parent] FOREIGN KEY (ParentId) REFERENCES [dbo].[Project](Id), -- +

CONSTRAINT [FK_Project_Creator] FOREIGN KEY (CreatorId) REFERENCES [dbo].[User](Id) ON UPDATE CASCADE ON DELETE SET DEFAULT,

CONSTRAINT [FK_Project_Manager] FOREIGN KEY (ManagerId) REFERENCES [dbo].[User](Id)

)

CREATE TABLE [dbo].[ProjectUser]

(

ProjectId INT NOT NULL,

UserId INT NOT NULL,

CONSTRAINT [PK_ProjectUser] PRIMARY KEY (ProjectId, UserId),

CONSTRAINT [FK_ProjectUser_Project] FOREIGN KEY (ProjectId) REFERENCES [dbo].[Project](Id) ON UPDATE CASCADE ON DELETE CASCADE,

CONSTRAINT [FK_ProjectUser_User] FOREIGN KEY (UserId) REFERENCES [dbo].[User](Id) -- +

)

CREATE TABLE [dbo].[ProjectFile]

(

ProjectId INT NOT NULL,

FileId INT NOT NULL,

CONSTRAINT [PK_ProjectFile] PRIMARY KEY (ProjectId, FileId),

CONSTRAINT [FK_ProjectFile_Project] FOREIGN KEY (ProjectId) REFERENCES [dbo].[Project](Id) ON UPDATE CASCADE ON DELETE CASCADE,

CONSTRAINT [FK_ProjectFile_File] FOREIGN KEY (FileId) REFERENCES [dbo].[File](Id) -- +

)

CREATE TABLE [dbo].[Component]

(

Id INT IDENTITY NOT NULL PRIMARY KEY, --ID компонента

ParentId INT NULL, -- FK

ProjectId INT NOT NULL, -- FK

Title NVARCHAR(256) NOT NULL CHECK (Title <> ''), -- Название компонента

CONSTRAINT [U_Component_ProjectTitle] UNIQUE (ProjectId, Title),

CONSTRAINT [FK_Component_Parent] FOREIGN KEY (ParentId) REFERENCES [dbo].[Component](Id), -- +

CONSTRAINT [FK_Component_Project] FOREIGN KEY (ProjectId) REFERENCES [dbo].[Project](Id) ON UPDATE CASCADE ON DELETE CASCADE

)

CREATE TABLE [dbo].[Milestone]

(

Id INT IDENTITY NOT NULL PRIMARY KEY, --ID вехи

ProjectId INT NOT NULL,

Title NVARCHAR(256) NOT NULL CHECK (Title <> ''),

Due DATE NOT NULL,

CONSTRAINT [U_Milestone_ProjectTitle] UNIQUE (ProjectId, Title),

CONSTRAINT [FK_Milestone_Project] FOREIGN KEY (ProjectId) REFERENCES [dbo].[Project](Id) ON UPDATE CASCADE ON DELETE CASCADE

)

CREATE TABLE [dbo].[Task]

(

Id INT IDENTITY NOT NULL PRIMARY KEY, --ID задачи

ParentId INT NULL,

ProjectId INT NOT NULL,

Title NVARCHAR(256) NOT NULL CHECK (Title <> ''),

CategoryId INT NOT NULL, -- FK

ComponentId INT NULL, -- FK

StateId INT NOT NULL, -- FK

StartDate DATE NOT NULL DEFAULT GETDATE(),

EndDate DATE NULL, -- При отсутствии даты, при показе всегда получать её от вехи / вышестоящей задачи / проекта

CreatorId INT NULL,

PerformerId INT NULL,

MilestoneId INT NULL, -- Веха, FK

EstimatedTime DECIMAL(6, 1) NULL CHECK (EstimatedTime > 0),

SpentTime DECIMAL(6, 1) NULL CHECK (SpentTime > 0),

Progress TINYINT NOT NULL DEFAULT 0 CHECK (Progress >= 0 AND Progress <= 100),

Content NVARCHAR(MAX) NOT NULL,

ModifyDate DATE NOT NULL DEFAULT GETDATE(),

CONSTRAINT [U_Task_ProjectParentTitle] UNIQUE (ProjectId, ParentId, Title),

CONSTRAINT [CK_Task_CheckTask] CHECK ([dbo].[CheckTask](ProjectId, ParentId, ComponentId, MilestoneId, CategoryId, StateId) > 0),

CONSTRAINT [CK_Task_EndDate] CHECK (EndDate >= StartDate),

CONSTRAINT [FK_Task_Parent] FOREIGN KEY (ParentId) REFERENCES [dbo].[Task](Id),

CONSTRAINT [FK_Task_Project] FOREIGN KEY (ProjectId) REFERENCES [dbo].[Project](Id) ON UPDATE CASCADE ON DELETE CASCADE,

CONSTRAINT [FK_Task_Category] FOREIGN KEY (CategoryId) REFERENCES [dbo].[Category](Id) ON UPDATE CASCADE ON DELETE CASCADE,

CONSTRAINT [FK_Task_Component] FOREIGN KEY (ComponentId) REFERENCES [dbo].[Component](Id), -- +,

CONSTRAINT [FK_Task_CreatorUser] FOREIGN KEY (CreatorId) REFERENCES [dbo].[User](Id),

CONSTRAINT [FK_Task_PerformerUser] FOREIGN KEY (PerformerId) REFERENCES [dbo].[User](Id),

CONSTRAINT [FK_Task_Milestone] FOREIGN KEY (MilestoneId) REFERENCES [dbo].[Milestone](Id)

)

CREATE TABLE [dbo].[TaskAuditor]

(

TaskId INT NOT NULL,

UserId INT NOT NULL,

CONSTRAINT [PK_TaskAuditor] PRIMARY KEY (TaskId, UserId),

CONSTRAINT [FK_TaskAuditor_Task] FOREIGN KEY (TaskId) REFERENCES [dbo].[Task](Id) ON UPDATE CASCADE ON DELETE CASCADE,

CONSTRAINT [FK_TaskAuditor_User] FOREIGN KEY (UserId) REFERENCES [dbo].[User](Id) -- +

)

CREATE TABLE [dbo].[TaskCheckItem]

(

Id INT IDENTITY NOT NULL PRIMARY KEY, --ID пункта задачи

TaskId INT NOT NULL, -- FK

Title NVARCHAR(256) NOT NULL CHECK (Title <> ''), -- Название пункта задачи

IsDone BIT NOT NULL DEFAULT 0, -- Выполнен ли пункт

CONSTRAINT [U_TaskCheckItem_TaskTitle] UNIQUE (TaskId, Title),

CONSTRAINT [FK_TaskCheckItem_Task] FOREIGN KEY (TaskId) REFERENCES [dbo].[Task](Id) ON UPDATE CASCADE ON DELETE CASCADE

)

CREATE TABLE [dbo].[TaskFile]

(

TaskId INT NOT NULL,

FileId INT NOT NULL,

CONSTRAINT [PK_TaskFile] PRIMARY KEY (TaskId, FileId),

CONSTRAINT [FK_TaskFile_Task] FOREIGN KEY (TaskId) REFERENCES [dbo].[Task](Id) ON UPDATE CASCADE ON DELETE CASCADE,

CONSTRAINT [FK_TaskFile_File] FOREIGN KEY (FileId) REFERENCES [dbo].[File](Id) -- +

)

CREATE TABLE [dbo].[Comment]

(

Id INT IDENTITY NOT NULL PRIMARY KEY, --ID комментария

UserId INT NOT NULL,

TaskId INT NOT NULL,

Content NVARCHAR(MAX) NOT NULL,

CONSTRAINT [FK_Comment_User] FOREIGN KEY (UserId) REFERENCES [dbo].[User](Id), -- +

CONSTRAINT [FK_Comment_Task] FOREIGN KEY (TaskId) REFERENCES [dbo].[Task](Id) ON UPDATE CASCADE ON DELETE CASCADE

)

CREATE TABLE [dbo].[CommentFile]

(

CommentId INT NOT NULL,

FileId INT NOT NULL,

CONSTRAINT [PK_CommentFile] PRIMARY KEY (CommentId, FileId),

CONSTRAINT [FK_CommentFile_Comment] FOREIGN KEY (CommentId) REFERENCES [dbo].[Comment](Id) ON UPDATE CASCADE ON DELETE CASCADE,

CONSTRAINT [FK_CommentFile_File] FOREIGN KEY (FileId) REFERENCES [dbo].[File](Id) -- +

)

GO

CREATE INDEX IX_CommentFile_CommentId ON [dbo].[CommentFile] (CommentId)

CREATE INDEX IX_Comment_TaskId ON [dbo].[Comment] (TaskId)

CREATE INDEX IX_TaskFile_TaskId ON [dbo].[TaskFile] (TaskId)

CREATE INDEX IX_TaskCheckItem_TaskId ON [dbo].[TaskCheckItem] (TaskId)

CREATE INDEX IX_TaskAuditor_TaskId ON [dbo].[TaskAuditor] (TaskId)

CREATE INDEX IX_ProjectFile_ProjectId ON [dbo].[ProjectFile] (ProjectId)

CREATE INDEX IX_ProjectUser_ProjectId ON [dbo].[ProjectUser] (ProjectId)

CREATE INDEX IX_StateTransition_InitialStateId ON [dbo].[StateTransition] (InitialStateId)

CREATE INDEX IX_StateTransition_FinalStateId ON [dbo].[StateTransition] (FinalStateId)

GO

Дата добавления: 2015-10-31