351 lines
15 KiB
Transact-SQL
351 lines
15 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.ManuscriptDocuments', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.ManuscriptDocuments
|
|
(
|
|
ManuscriptDocumentID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_ManuscriptDocuments PRIMARY KEY,
|
|
BookID int NOT NULL,
|
|
ProjectID int NOT NULL,
|
|
DocumentGuid uniqueidentifier NOT NULL,
|
|
BindingVersion int NOT NULL CONSTRAINT DF_ManuscriptDocuments_BindingVersion DEFAULT (1),
|
|
CreatedUtc datetime2 NOT NULL CONSTRAINT DF_ManuscriptDocuments_CreatedUtc DEFAULT SYSUTCDATETIME(),
|
|
LastSyncUtc datetime2 NULL,
|
|
LastOpenedUtc datetime2 NULL,
|
|
IsActive bit NOT NULL CONSTRAINT DF_ManuscriptDocuments_IsActive DEFAULT (1),
|
|
CreatedByUserID int NULL,
|
|
CONSTRAINT FK_ManuscriptDocuments_Books FOREIGN KEY (BookID) REFERENCES dbo.Books(BookID),
|
|
CONSTRAINT FK_ManuscriptDocuments_Projects FOREIGN KEY (ProjectID) REFERENCES dbo.Projects(ProjectID),
|
|
CONSTRAINT FK_ManuscriptDocuments_AppUser FOREIGN KEY (CreatedByUserID) REFERENCES dbo.AppUser(UserID)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'UX_ManuscriptDocuments_ActiveBook' AND object_id = OBJECT_ID(N'dbo.ManuscriptDocuments'))
|
|
CREATE UNIQUE INDEX UX_ManuscriptDocuments_ActiveBook ON dbo.ManuscriptDocuments(BookID) WHERE IsActive = 1;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'UX_ManuscriptDocuments_ActiveGuid' AND object_id = OBJECT_ID(N'dbo.ManuscriptDocuments'))
|
|
CREATE UNIQUE INDEX UX_ManuscriptDocuments_ActiveGuid ON dbo.ManuscriptDocuments(DocumentGuid) WHERE IsActive = 1;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_ManuscriptDocuments_Project' AND object_id = OBJECT_ID(N'dbo.ManuscriptDocuments'))
|
|
CREATE INDEX IX_ManuscriptDocuments_Project ON dbo.ManuscriptDocuments(ProjectID, IsActive);
|
|
GO
|
|
|
|
IF COL_LENGTH(N'dbo.Chapters', N'IsLinkedToManuscript') IS NULL
|
|
ALTER TABLE dbo.Chapters ADD IsLinkedToManuscript bit NOT NULL CONSTRAINT DF_Chapters_IsLinkedToManuscript DEFAULT (0);
|
|
GO
|
|
|
|
IF COL_LENGTH(N'dbo.Chapters', N'ManuscriptDocumentID') IS NULL
|
|
ALTER TABLE dbo.Chapters ADD ManuscriptDocumentID int NULL;
|
|
GO
|
|
|
|
IF COL_LENGTH(N'dbo.Chapters', N'ExternalReference') IS NULL
|
|
ALTER TABLE dbo.Chapters ADD ExternalReference nvarchar(100) NULL;
|
|
GO
|
|
|
|
IF COL_LENGTH(N'dbo.Chapters', N'LinkedUtc') IS NULL
|
|
ALTER TABLE dbo.Chapters ADD LinkedUtc datetime2 NULL;
|
|
GO
|
|
|
|
IF COL_LENGTH(N'dbo.Scenes', N'IsLinkedToManuscript') IS NULL
|
|
ALTER TABLE dbo.Scenes ADD IsLinkedToManuscript bit NOT NULL CONSTRAINT DF_Scenes_IsLinkedToManuscript DEFAULT (0);
|
|
GO
|
|
|
|
IF COL_LENGTH(N'dbo.Scenes', N'ManuscriptDocumentID') IS NULL
|
|
ALTER TABLE dbo.Scenes ADD ManuscriptDocumentID int NULL;
|
|
GO
|
|
|
|
IF COL_LENGTH(N'dbo.Scenes', N'ExternalReference') IS NULL
|
|
ALTER TABLE dbo.Scenes ADD ExternalReference nvarchar(100) NULL;
|
|
GO
|
|
|
|
IF COL_LENGTH(N'dbo.Scenes', N'LinkedUtc') IS NULL
|
|
ALTER TABLE dbo.Scenes ADD LinkedUtc datetime2 NULL;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = N'FK_Chapters_ManuscriptDocuments')
|
|
ALTER TABLE dbo.Chapters ADD CONSTRAINT FK_Chapters_ManuscriptDocuments FOREIGN KEY (ManuscriptDocumentID) REFERENCES dbo.ManuscriptDocuments(ManuscriptDocumentID);
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = N'FK_Scenes_ManuscriptDocuments')
|
|
ALTER TABLE dbo.Scenes ADD CONSTRAINT FK_Scenes_ManuscriptDocuments FOREIGN KEY (ManuscriptDocumentID) REFERENCES dbo.ManuscriptDocuments(ManuscriptDocumentID);
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_Chapters_ManuscriptLink' AND object_id = OBJECT_ID(N'dbo.Chapters'))
|
|
CREATE INDEX IX_Chapters_ManuscriptLink ON dbo.Chapters(ManuscriptDocumentID, IsLinkedToManuscript, ExternalReference);
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_Scenes_ManuscriptLink' AND object_id = OBJECT_ID(N'dbo.Scenes'))
|
|
CREATE INDEX IX_Scenes_ManuscriptLink ON dbo.Scenes(ManuscriptDocumentID, IsLinkedToManuscript, ExternalReference);
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.ManuscriptDocument_GetByBook
|
|
@BookID int,
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT md.ManuscriptDocumentID, md.BookID, md.ProjectID, md.DocumentGuid, md.BindingVersion,
|
|
md.CreatedUtc, md.LastSyncUtc, md.LastOpenedUtc, md.IsActive, md.CreatedByUserID
|
|
FROM dbo.ManuscriptDocuments md
|
|
INNER JOIN dbo.Books b ON b.BookID = md.BookID
|
|
INNER JOIN dbo.Projects p ON p.ProjectID = b.ProjectID
|
|
INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = p.ProjectID
|
|
WHERE md.BookID = @BookID
|
|
AND md.IsActive = 1
|
|
AND b.IsArchived = 0
|
|
AND p.IsArchived = 0
|
|
AND pua.UserID = @UserID
|
|
AND pua.IsActive = 1;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.ManuscriptDocument_GetByGuid
|
|
@DocumentGuid uniqueidentifier,
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT md.ManuscriptDocumentID, md.BookID, md.ProjectID, md.DocumentGuid, md.BindingVersion,
|
|
md.CreatedUtc, md.LastSyncUtc, md.LastOpenedUtc, md.IsActive, md.CreatedByUserID
|
|
FROM dbo.ManuscriptDocuments md
|
|
INNER JOIN dbo.Books b ON b.BookID = md.BookID
|
|
INNER JOIN dbo.Projects p ON p.ProjectID = b.ProjectID
|
|
INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = p.ProjectID
|
|
WHERE md.DocumentGuid = @DocumentGuid
|
|
AND md.IsActive = 1
|
|
AND b.IsArchived = 0
|
|
AND p.IsArchived = 0
|
|
AND pua.UserID = @UserID
|
|
AND pua.IsActive = 1;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.ManuscriptDocument_Save
|
|
@BookID int,
|
|
@DocumentGuid uniqueidentifier,
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @ProjectID int;
|
|
SELECT @ProjectID = b.ProjectID
|
|
FROM dbo.Books b
|
|
INNER JOIN dbo.Projects p ON p.ProjectID = b.ProjectID
|
|
INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = p.ProjectID
|
|
WHERE b.BookID = @BookID
|
|
AND b.IsArchived = 0
|
|
AND p.IsArchived = 0
|
|
AND pua.UserID = @UserID
|
|
AND pua.IsActive = 1;
|
|
|
|
IF @ProjectID IS NULL
|
|
RETURN;
|
|
|
|
IF EXISTS
|
|
(
|
|
SELECT 1
|
|
FROM dbo.ManuscriptDocuments
|
|
WHERE DocumentGuid = @DocumentGuid
|
|
AND IsActive = 1
|
|
AND BookID <> @BookID
|
|
)
|
|
THROW 51000, 'This manuscript document is already linked to another book.', 1;
|
|
|
|
DECLARE @ManuscriptDocumentID int;
|
|
SELECT @ManuscriptDocumentID = ManuscriptDocumentID
|
|
FROM dbo.ManuscriptDocuments
|
|
WHERE BookID = @BookID
|
|
AND IsActive = 1;
|
|
|
|
IF @ManuscriptDocumentID IS NULL
|
|
BEGIN
|
|
INSERT dbo.ManuscriptDocuments (BookID, ProjectID, DocumentGuid, CreatedByUserID)
|
|
VALUES (@BookID, @ProjectID, @DocumentGuid, @UserID);
|
|
|
|
SET @ManuscriptDocumentID = CAST(SCOPE_IDENTITY() AS int);
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE dbo.ManuscriptDocuments
|
|
SET DocumentGuid = @DocumentGuid,
|
|
ProjectID = @ProjectID
|
|
WHERE ManuscriptDocumentID = @ManuscriptDocumentID;
|
|
END;
|
|
|
|
EXEC dbo.ManuscriptDocument_GetByBook @BookID = @BookID, @UserID = @UserID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.ManuscriptDocument_UpdateLastSync
|
|
@ManuscriptDocumentID int,
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
UPDATE md
|
|
SET LastSyncUtc = SYSUTCDATETIME()
|
|
FROM dbo.ManuscriptDocuments md
|
|
INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = md.ProjectID
|
|
WHERE md.ManuscriptDocumentID = @ManuscriptDocumentID
|
|
AND md.IsActive = 1
|
|
AND pua.UserID = @UserID
|
|
AND pua.IsActive = 1;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.ManuscriptDocument_UpdateLastOpened
|
|
@ManuscriptDocumentID int,
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
UPDATE md
|
|
SET LastOpenedUtc = SYSUTCDATETIME()
|
|
FROM dbo.ManuscriptDocuments md
|
|
INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = md.ProjectID
|
|
WHERE md.ManuscriptDocumentID = @ManuscriptDocumentID
|
|
AND md.IsActive = 1
|
|
AND pua.UserID = @UserID
|
|
AND pua.IsActive = 1;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Chapter_ListByBook
|
|
@BookID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT c.ChapterID, c.BookID, c.ChapterNumber, c.ChapterTitle, c.POVCharacterID, c.Summary,
|
|
c.SortOrder, c.RevisionStatusID, rs.StatusName AS RevisionStatusName,
|
|
c.CreatedDate, c.UpdatedDate, c.IsArchived, c.ArchivedDate, c.ArchivedReason,
|
|
c.IsLinkedToManuscript, c.ManuscriptDocumentID, c.ExternalReference, c.LinkedUtc
|
|
FROM dbo.Chapters c
|
|
INNER JOIN dbo.RevisionStatuses rs ON rs.RevisionStatusID = c.RevisionStatusID
|
|
WHERE c.BookID = @BookID AND c.IsArchived = 0
|
|
ORDER BY c.SortOrder, c.ChapterNumber, c.ChapterTitle;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Chapter_Get
|
|
@ChapterID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT c.ChapterID, c.BookID, c.ChapterNumber, c.ChapterTitle, c.POVCharacterID, c.Summary,
|
|
c.SortOrder, c.RevisionStatusID, rs.StatusName AS RevisionStatusName,
|
|
c.CreatedDate, c.UpdatedDate, c.IsArchived, c.ArchivedDate, c.ArchivedReason,
|
|
c.IsLinkedToManuscript, c.ManuscriptDocumentID, c.ExternalReference, c.LinkedUtc
|
|
FROM dbo.Chapters c
|
|
INNER JOIN dbo.RevisionStatuses rs ON rs.RevisionStatusID = c.RevisionStatusID
|
|
WHERE c.ChapterID = @ChapterID AND c.IsArchived = 0;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Scene_ListByChapter
|
|
@ChapterID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT s.SceneID, s.ChapterID, s.SceneNumber, s.SceneTitle, s.Summary, s.POVCharacterID,
|
|
s.PrimaryLocationID, s.FloorPlanID, s.InitialFloorPlanFloorID, s.SortOrder,
|
|
s.TimeModeID, tm.TimeModeName, s.StartDateTime, s.EndDateTime, s.DurationAmount,
|
|
s.DurationUnitID, du.DurationUnitName, s.RelativeTimeText, s.TimeConfidenceID,
|
|
tc.TimeConfidenceName, s.ScenePurposeNotes, s.SceneOutcomeNotes, s.RevisionStatusID,
|
|
rs.StatusName AS RevisionStatusName, s.CreatedDate, s.UpdatedDate, s.IsArchived,
|
|
s.ArchivedDate, s.ArchivedReason, s.IsLinkedToManuscript, s.ManuscriptDocumentID,
|
|
s.ExternalReference, s.LinkedUtc
|
|
FROM dbo.Scenes s
|
|
INNER JOIN dbo.TimeModes tm ON tm.TimeModeID = s.TimeModeID
|
|
LEFT JOIN dbo.DurationUnits du ON du.DurationUnitID = s.DurationUnitID
|
|
INNER JOIN dbo.TimeConfidences tc ON tc.TimeConfidenceID = s.TimeConfidenceID
|
|
INNER JOIN dbo.RevisionStatuses rs ON rs.RevisionStatusID = s.RevisionStatusID
|
|
WHERE s.ChapterID = @ChapterID AND s.IsArchived = 0
|
|
ORDER BY s.SortOrder, s.SceneNumber, s.SceneTitle;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Scene_Get
|
|
@SceneID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
EXEC dbo.SceneMetric_EnsureDefaults @SceneID;
|
|
SELECT s.SceneID, s.ChapterID, s.SceneNumber, s.SceneTitle, s.Summary, s.POVCharacterID, s.PrimaryLocationID,
|
|
s.FloorPlanID, s.InitialFloorPlanFloorID,
|
|
location.LocationName AS PrimaryLocationName, location.LocationPath AS PrimaryLocationPath,
|
|
s.SortOrder, s.TimeModeID, tm.TimeModeName, s.StartDateTime, s.EndDateTime, s.DurationAmount, s.DurationUnitID,
|
|
du.DurationUnitName, s.RelativeTimeText, s.TimeConfidenceID, tc.TimeConfidenceName, s.ScenePurposeNotes,
|
|
s.SceneOutcomeNotes, s.RevisionStatusID, rs.StatusName AS RevisionStatusName, s.CreatedDate, s.UpdatedDate, s.IsArchived,
|
|
s.ArchivedDate, s.ArchivedReason, s.IsLinkedToManuscript, s.ManuscriptDocumentID, s.ExternalReference, s.LinkedUtc
|
|
FROM dbo.Scenes s
|
|
INNER JOIN dbo.TimeModes tm ON tm.TimeModeID = s.TimeModeID
|
|
INNER JOIN dbo.TimeConfidences tc ON tc.TimeConfidenceID = s.TimeConfidenceID
|
|
INNER JOIN dbo.RevisionStatuses rs ON rs.RevisionStatusID = s.RevisionStatusID
|
|
LEFT JOIN dbo.DurationUnits du ON du.DurationUnitID = s.DurationUnitID
|
|
LEFT JOIN dbo.LocationPaths location ON location.LocationID = s.PrimaryLocationID
|
|
WHERE s.SceneID = @SceneID AND s.IsArchived = 0;
|
|
SELECT ScenePurposeTypeID FROM dbo.ScenePurposes WHERE SceneID = @SceneID;
|
|
EXEC dbo.SceneMetric_ListByScene @SceneID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Archive_Entity
|
|
@EntityType nvarchar(80),
|
|
@EntityID int,
|
|
@ArchivedReason nvarchar(500) = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
IF @EntityType = N'Project' UPDATE dbo.Projects SET IsArchived = 1, ArchivedDate = SYSUTCDATETIME(), ArchivedReason = @ArchivedReason, UpdatedDate = SYSUTCDATETIME() WHERE ProjectID = @EntityID;
|
|
ELSE IF @EntityType = N'Book' UPDATE dbo.Books SET IsArchived = 1, ArchivedDate = SYSUTCDATETIME(), ArchivedReason = @ArchivedReason, UpdatedDate = SYSUTCDATETIME() WHERE BookID = @EntityID;
|
|
ELSE IF @EntityType = N'Chapter'
|
|
BEGIN
|
|
IF EXISTS (SELECT 1 FROM dbo.Chapters WHERE ChapterID = @EntityID AND IsLinkedToManuscript = 1)
|
|
THROW 51000, 'This chapter is linked to a manuscript and cannot be deleted from PlotDirector. Delete the chapter from Microsoft Word to remove it from the manuscript.', 1;
|
|
|
|
UPDATE dbo.Chapters SET IsArchived = 1, ArchivedDate = SYSUTCDATETIME(), ArchivedReason = @ArchivedReason, UpdatedDate = SYSUTCDATETIME() WHERE ChapterID = @EntityID;
|
|
END
|
|
ELSE IF @EntityType = N'Scene'
|
|
BEGIN
|
|
DECLARE @ChapterID int;
|
|
SELECT @ChapterID = ChapterID FROM dbo.Scenes WHERE SceneID = @EntityID AND IsArchived = 0;
|
|
|
|
IF EXISTS (SELECT 1 FROM dbo.Scenes WHERE SceneID = @EntityID AND IsLinkedToManuscript = 1)
|
|
THROW 51000, 'This scene is linked to a manuscript and cannot be deleted from PlotDirector. Delete the scene from Microsoft Word to remove it from the manuscript.', 1;
|
|
|
|
IF @ChapterID IS NULL
|
|
RETURN;
|
|
|
|
IF (SELECT COUNT(*) FROM dbo.Scenes WHERE ChapterID = @ChapterID AND IsArchived = 0) <= 1
|
|
THROW 51000, 'A chapter must contain at least one scene.', 1;
|
|
|
|
UPDATE dbo.Scenes SET IsArchived = 1, ArchivedDate = SYSUTCDATETIME(), ArchivedReason = @ArchivedReason, UpdatedDate = SYSUTCDATETIME() WHERE SceneID = @EntityID;
|
|
END
|
|
ELSE IF @EntityType = N'PlotLine' UPDATE dbo.PlotLines SET IsArchived = 1, ArchivedDate = SYSUTCDATETIME(), ArchivedReason = @ArchivedReason, UpdatedDate = SYSUTCDATETIME() WHERE PlotLineID = @EntityID;
|
|
ELSE IF @EntityType = N'PlotThread' UPDATE dbo.PlotThreads SET IsArchived = 1, ArchivedDate = SYSUTCDATETIME(), ArchivedReason = @ArchivedReason, UpdatedDate = SYSUTCDATETIME() WHERE PlotThreadID = @EntityID;
|
|
ELSE IF @EntityType = N'StoryAsset' UPDATE dbo.StoryAssets SET IsArchived = 1, ArchivedDate = SYSUTCDATETIME(), ArchivedReason = @ArchivedReason, UpdatedDate = SYSUTCDATETIME() WHERE StoryAssetID = @EntityID;
|
|
ELSE IF @EntityType = N'Character' UPDATE dbo.Characters SET IsArchived = 1, ArchivedDate = SYSUTCDATETIME(), ArchivedReason = @ArchivedReason, UpdatedDate = SYSUTCDATETIME() WHERE CharacterID = @EntityID;
|
|
ELSE IF @EntityType = N'Location' UPDATE dbo.Locations SET IsArchived = 1, ArchivedDate = SYSUTCDATETIME(), ArchivedReason = @ArchivedReason, UpdatedDate = SYSUTCDATETIME() WHERE LocationID = @EntityID;
|
|
ELSE IF @EntityType = N'Scenario' UPDATE dbo.Scenarios SET IsArchived = 1, ArchivedDate = SYSUTCDATETIME(), ArchivedReason = @ArchivedReason, UpdatedDate = SYSUTCDATETIME() WHERE ScenarioID = @EntityID;
|
|
ELSE IF @EntityType = N'Relationship' UPDATE dbo.CharacterRelationships SET IsArchived = 1, ArchivedDate = SYSUTCDATETIME(), ArchivedReason = @ArchivedReason, UpdatedDate = SYSUTCDATETIME() WHERE CharacterRelationshipID = @EntityID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Chapter_Archive @ChapterID int AS BEGIN SET NOCOUNT ON; EXEC dbo.Archive_Entity N'Chapter', @ChapterID, NULL; END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Scene_Archive @SceneID int AS BEGIN SET NOCOUNT ON; EXEC dbo.Archive_Entity N'Scene', @SceneID, NULL; END;
|
|
GO
|