110 lines
3.5 KiB
Transact-SQL
110 lines
3.5 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.TimelineViewPresets', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.TimelineViewPresets
|
|
(
|
|
TimelineViewPresetID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_TimelineViewPresets PRIMARY KEY,
|
|
ProjectID int NOT NULL,
|
|
BookID int NULL,
|
|
PresetName nvarchar(200) NOT NULL,
|
|
FilterJson nvarchar(max) NOT NULL,
|
|
VisibilityJson nvarchar(max) NOT NULL,
|
|
FocusType nvarchar(50) NULL,
|
|
FocusID int NULL,
|
|
CreatedDate datetime2 NOT NULL CONSTRAINT DF_TimelineViewPresets_CreatedDate DEFAULT SYSUTCDATETIME(),
|
|
UpdatedDate datetime2 NOT NULL CONSTRAINT DF_TimelineViewPresets_UpdatedDate DEFAULT SYSUTCDATETIME(),
|
|
IsArchived bit NOT NULL CONSTRAINT DF_TimelineViewPresets_IsArchived DEFAULT 0,
|
|
CONSTRAINT FK_TimelineViewPresets_Projects FOREIGN KEY (ProjectID) REFERENCES dbo.Projects(ProjectID),
|
|
CONSTRAINT FK_TimelineViewPresets_Books FOREIGN KEY (BookID) REFERENCES dbo.Books(BookID)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_TimelineViewPresets_ProjectBook' AND object_id = OBJECT_ID(N'dbo.TimelineViewPresets'))
|
|
CREATE INDEX IX_TimelineViewPresets_ProjectBook ON dbo.TimelineViewPresets(ProjectID, BookID, IsArchived, PresetName);
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.TimelineViewPreset_List
|
|
@ProjectID int,
|
|
@BookID int = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT TimelineViewPresetID, ProjectID, BookID, PresetName, FilterJson, VisibilityJson, FocusType, FocusID,
|
|
CreatedDate, UpdatedDate, IsArchived
|
|
FROM dbo.TimelineViewPresets
|
|
WHERE ProjectID = @ProjectID
|
|
AND IsArchived = 0
|
|
AND (@BookID IS NULL OR BookID IS NULL OR BookID = @BookID)
|
|
ORDER BY PresetName;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.TimelineViewPreset_Get
|
|
@TimelineViewPresetID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT TimelineViewPresetID, ProjectID, BookID, PresetName, FilterJson, VisibilityJson, FocusType, FocusID,
|
|
CreatedDate, UpdatedDate, IsArchived
|
|
FROM dbo.TimelineViewPresets
|
|
WHERE TimelineViewPresetID = @TimelineViewPresetID AND IsArchived = 0;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.TimelineViewPreset_Save
|
|
@TimelineViewPresetID int = 0,
|
|
@ProjectID int,
|
|
@BookID int = NULL,
|
|
@PresetName nvarchar(200),
|
|
@FilterJson nvarchar(max),
|
|
@VisibilityJson nvarchar(max),
|
|
@FocusType nvarchar(50) = NULL,
|
|
@FocusID int = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
IF @TimelineViewPresetID IS NULL OR @TimelineViewPresetID = 0
|
|
BEGIN
|
|
INSERT dbo.TimelineViewPresets (ProjectID, BookID, PresetName, FilterJson, VisibilityJson, FocusType, FocusID)
|
|
VALUES (@ProjectID, @BookID, @PresetName, @FilterJson, @VisibilityJson, @FocusType, @FocusID);
|
|
|
|
SELECT CAST(SCOPE_IDENTITY() AS int);
|
|
RETURN;
|
|
END;
|
|
|
|
UPDATE dbo.TimelineViewPresets
|
|
SET ProjectID = @ProjectID,
|
|
BookID = @BookID,
|
|
PresetName = @PresetName,
|
|
FilterJson = @FilterJson,
|
|
VisibilityJson = @VisibilityJson,
|
|
FocusType = @FocusType,
|
|
FocusID = @FocusID,
|
|
UpdatedDate = SYSUTCDATETIME()
|
|
WHERE TimelineViewPresetID = @TimelineViewPresetID;
|
|
|
|
SELECT @TimelineViewPresetID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.TimelineViewPreset_Archive
|
|
@TimelineViewPresetID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
UPDATE dbo.TimelineViewPresets
|
|
SET IsArchived = 1,
|
|
UpdatedDate = SYSUTCDATETIME()
|
|
WHERE TimelineViewPresetID = @TimelineViewPresetID;
|
|
END;
|
|
GO
|