249 lines
11 KiB
Transact-SQL
249 lines
11 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
IF COL_LENGTH(N'dbo.CharacterRelationships', N'IsInitialRelationship') IS NULL
|
|
BEGIN
|
|
ALTER TABLE dbo.CharacterRelationships
|
|
ADD IsInitialRelationship bit NOT NULL CONSTRAINT DF_CharacterRelationships_IsInitialRelationship DEFAULT 0;
|
|
END;
|
|
GO
|
|
|
|
IF COL_LENGTH(N'dbo.CharacterRelationships', N'InitialBookID') IS NULL
|
|
BEGIN
|
|
ALTER TABLE dbo.CharacterRelationships
|
|
ADD InitialBookID int NULL;
|
|
END;
|
|
GO
|
|
|
|
IF COL_LENGTH(N'dbo.CharacterRelationships', N'InitialBookID') IS NOT NULL
|
|
AND NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = N'FK_CharacterRelationships_InitialBook')
|
|
BEGIN
|
|
ALTER TABLE dbo.CharacterRelationships
|
|
ADD CONSTRAINT FK_CharacterRelationships_InitialBook FOREIGN KEY (InitialBookID) REFERENCES dbo.Books(BookID);
|
|
END;
|
|
GO
|
|
|
|
IF COL_LENGTH(N'dbo.CharacterRelationships', N'ReaderInitiallyKnows') IS NULL
|
|
BEGIN
|
|
ALTER TABLE dbo.CharacterRelationships
|
|
ADD ReaderInitiallyKnows bit NOT NULL CONSTRAINT DF_CharacterRelationships_ReaderInitiallyKnows DEFAULT 0;
|
|
END;
|
|
GO
|
|
|
|
IF COL_LENGTH(N'dbo.CharacterRelationships', N'InitialRelationshipStateID') IS NULL
|
|
BEGIN
|
|
ALTER TABLE dbo.CharacterRelationships
|
|
ADD InitialRelationshipStateID int NULL;
|
|
END;
|
|
GO
|
|
|
|
IF COL_LENGTH(N'dbo.CharacterRelationships', N'InitialRelationshipStateID') IS NOT NULL
|
|
AND NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = N'FK_CharacterRelationships_InitialState')
|
|
BEGIN
|
|
ALTER TABLE dbo.CharacterRelationships
|
|
ADD CONSTRAINT FK_CharacterRelationships_InitialState FOREIGN KEY (InitialRelationshipStateID) REFERENCES dbo.RelationshipStates(RelationshipStateID);
|
|
END;
|
|
GO
|
|
|
|
IF COL_LENGTH(N'dbo.CharacterRelationships', N'InitialIntensity') IS NULL
|
|
BEGIN
|
|
ALTER TABLE dbo.CharacterRelationships
|
|
ADD InitialIntensity int NULL;
|
|
END;
|
|
GO
|
|
|
|
IF COL_LENGTH(N'dbo.CharacterRelationships', N'InitialIntensity') IS NOT NULL
|
|
AND NOT EXISTS (SELECT 1 FROM sys.check_constraints WHERE name = N'CK_CharacterRelationships_InitialIntensity')
|
|
BEGIN
|
|
ALTER TABLE dbo.CharacterRelationships
|
|
ADD CONSTRAINT CK_CharacterRelationships_InitialIntensity CHECK (InitialIntensity IS NULL OR InitialIntensity BETWEEN 1 AND 10);
|
|
END;
|
|
GO
|
|
|
|
IF COL_LENGTH(N'dbo.CharacterRelationships', N'IsReciprocal') IS NULL
|
|
BEGIN
|
|
ALTER TABLE dbo.CharacterRelationships
|
|
ADD IsReciprocal bit NOT NULL CONSTRAINT DF_CharacterRelationships_IsReciprocal DEFAULT 0;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Relationship_ListInitialByCharacter
|
|
@CharacterID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT cr.CharacterRelationshipID, cr.ProjectID, cr.CharacterAID, ca.CharacterName AS CharacterAName,
|
|
cr.CharacterBID, cb.CharacterName AS CharacterBName, cr.RelationshipTypeID, rt.TypeName AS RelationshipTypeName,
|
|
cr.IsPermanent, cr.StartSceneID, cr.EndSceneID, cr.IsKnownToReader, cr.Notes,
|
|
cr.IsInitialRelationship, cr.InitialBookID, b.BookTitle AS InitialBookTitle, cr.ReaderInitiallyKnows,
|
|
cr.InitialRelationshipStateID, rs.StateName AS InitialRelationshipStateName, cr.InitialIntensity, cr.IsReciprocal,
|
|
cr.CreatedDate, cr.UpdatedDate, cr.IsArchived
|
|
FROM dbo.CharacterRelationships cr
|
|
INNER JOIN dbo.Characters ca ON ca.CharacterID = cr.CharacterAID
|
|
INNER JOIN dbo.Characters cb ON cb.CharacterID = cr.CharacterBID
|
|
INNER JOIN dbo.RelationshipTypes rt ON rt.RelationshipTypeID = cr.RelationshipTypeID
|
|
LEFT JOIN dbo.Books b ON b.BookID = cr.InitialBookID
|
|
LEFT JOIN dbo.RelationshipStates rs ON rs.RelationshipStateID = cr.InitialRelationshipStateID
|
|
WHERE cr.IsArchived = 0
|
|
AND cr.IsInitialRelationship = 1
|
|
AND @CharacterID IN (cr.CharacterAID, cr.CharacterBID)
|
|
ORDER BY COALESCE(b.SortOrder, 0), ca.CharacterName, cb.CharacterName, rt.SortOrder;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Relationship_ListByCharacter
|
|
@CharacterID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT cr.CharacterRelationshipID, cr.ProjectID, cr.CharacterAID, ca.CharacterName AS CharacterAName,
|
|
cr.CharacterBID, cb.CharacterName AS CharacterBName, cr.RelationshipTypeID, rt.TypeName AS RelationshipTypeName,
|
|
cr.IsPermanent, cr.StartSceneID, cr.EndSceneID, cr.IsKnownToReader, cr.Notes,
|
|
cr.IsInitialRelationship, cr.InitialBookID, b.BookTitle AS InitialBookTitle, cr.ReaderInitiallyKnows,
|
|
cr.InitialRelationshipStateID, rs.StateName AS InitialRelationshipStateName, cr.InitialIntensity, cr.IsReciprocal,
|
|
cr.CreatedDate, cr.UpdatedDate, cr.IsArchived
|
|
FROM dbo.CharacterRelationships cr
|
|
INNER JOIN dbo.Characters ca ON ca.CharacterID = cr.CharacterAID
|
|
INNER JOIN dbo.Characters cb ON cb.CharacterID = cr.CharacterBID
|
|
INNER JOIN dbo.RelationshipTypes rt ON rt.RelationshipTypeID = cr.RelationshipTypeID
|
|
LEFT JOIN dbo.Books b ON b.BookID = cr.InitialBookID
|
|
LEFT JOIN dbo.RelationshipStates rs ON rs.RelationshipStateID = cr.InitialRelationshipStateID
|
|
WHERE cr.IsArchived = 0 AND @CharacterID IN (cr.CharacterAID, cr.CharacterBID)
|
|
ORDER BY ca.CharacterName, cb.CharacterName, rt.SortOrder;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Relationship_ListByProject
|
|
@ProjectID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT cr.CharacterRelationshipID, cr.ProjectID, cr.CharacterAID, ca.CharacterName AS CharacterAName,
|
|
cr.CharacterBID, cb.CharacterName AS CharacterBName, cr.RelationshipTypeID, rt.TypeName AS RelationshipTypeName,
|
|
cr.IsPermanent, cr.StartSceneID, cr.EndSceneID, cr.IsKnownToReader, cr.Notes,
|
|
cr.IsInitialRelationship, cr.InitialBookID, b.BookTitle AS InitialBookTitle, cr.ReaderInitiallyKnows,
|
|
cr.InitialRelationshipStateID, rs.StateName AS InitialRelationshipStateName, cr.InitialIntensity, cr.IsReciprocal,
|
|
cr.CreatedDate, cr.UpdatedDate, cr.IsArchived
|
|
FROM dbo.CharacterRelationships cr
|
|
INNER JOIN dbo.Characters ca ON ca.CharacterID = cr.CharacterAID
|
|
INNER JOIN dbo.Characters cb ON cb.CharacterID = cr.CharacterBID
|
|
INNER JOIN dbo.RelationshipTypes rt ON rt.RelationshipTypeID = cr.RelationshipTypeID
|
|
LEFT JOIN dbo.Books b ON b.BookID = cr.InitialBookID
|
|
LEFT JOIN dbo.RelationshipStates rs ON rs.RelationshipStateID = cr.InitialRelationshipStateID
|
|
WHERE cr.ProjectID = @ProjectID AND cr.IsArchived = 0
|
|
ORDER BY ca.CharacterName, cb.CharacterName, rt.SortOrder;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Relationship_Save
|
|
@CharacterRelationshipID int = NULL,
|
|
@ProjectID int,
|
|
@CharacterAID int,
|
|
@CharacterBID int,
|
|
@RelationshipTypeID int,
|
|
@IsPermanent bit,
|
|
@StartSceneID int = NULL,
|
|
@EndSceneID int = NULL,
|
|
@IsKnownToReader bit,
|
|
@Notes nvarchar(max) = NULL,
|
|
@IsInitialRelationship bit = 0,
|
|
@InitialBookID int = NULL,
|
|
@ReaderInitiallyKnows bit = 0,
|
|
@InitialRelationshipStateID int = NULL,
|
|
@InitialIntensity int = NULL,
|
|
@IsReciprocal bit = 0
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
IF @InitialIntensity IS NOT NULL AND (@InitialIntensity < 1 OR @InitialIntensity > 10)
|
|
THROW 53000, 'Initial relationship intensity must be between 1 and 10.', 1;
|
|
|
|
IF @CharacterRelationshipID IS NULL OR @CharacterRelationshipID = 0
|
|
BEGIN
|
|
INSERT dbo.CharacterRelationships
|
|
(ProjectID, CharacterAID, CharacterBID, RelationshipTypeID, IsPermanent, StartSceneID, EndSceneID, IsKnownToReader, Notes,
|
|
IsInitialRelationship, InitialBookID, ReaderInitiallyKnows, InitialRelationshipStateID, InitialIntensity, IsReciprocal)
|
|
VALUES
|
|
(@ProjectID, @CharacterAID, @CharacterBID, @RelationshipTypeID, @IsPermanent, @StartSceneID, @EndSceneID, @IsKnownToReader, @Notes,
|
|
@IsInitialRelationship, @InitialBookID, @ReaderInitiallyKnows, @InitialRelationshipStateID, @InitialIntensity, @IsReciprocal);
|
|
|
|
SET @CharacterRelationshipID = CAST(SCOPE_IDENTITY() AS int);
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE dbo.CharacterRelationships
|
|
SET CharacterAID = @CharacterAID,
|
|
CharacterBID = @CharacterBID,
|
|
RelationshipTypeID = @RelationshipTypeID,
|
|
IsPermanent = @IsPermanent,
|
|
StartSceneID = @StartSceneID,
|
|
EndSceneID = @EndSceneID,
|
|
IsKnownToReader = @IsKnownToReader,
|
|
Notes = @Notes,
|
|
IsInitialRelationship = @IsInitialRelationship,
|
|
InitialBookID = @InitialBookID,
|
|
ReaderInitiallyKnows = @ReaderInitiallyKnows,
|
|
InitialRelationshipStateID = @InitialRelationshipStateID,
|
|
InitialIntensity = @InitialIntensity,
|
|
IsReciprocal = @IsReciprocal,
|
|
UpdatedDate = SYSUTCDATETIME()
|
|
WHERE CharacterRelationshipID = @CharacterRelationshipID
|
|
AND ProjectID = @ProjectID;
|
|
END
|
|
|
|
SELECT @CharacterRelationshipID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.RelationshipEvent_ListByCharacter
|
|
@CharacterID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT re.RelationshipEventID, re.CharacterRelationshipID, cr.ProjectID, cr.CharacterAID, ca.CharacterName AS CharacterAName,
|
|
cr.CharacterBID, cb.CharacterName AS CharacterBName, rt.TypeName AS RelationshipTypeName,
|
|
cr.IsReciprocal, re.SceneID, s.SceneNumber, s.SceneTitle, ch.ChapterNumber, b.BookTitle,
|
|
re.RelationshipStateID, rs.StateName AS RelationshipStateName, re.Intensity, re.IsKnownToOtherCharacter,
|
|
re.Description, re.CreatedDate, re.UpdatedDate
|
|
FROM dbo.RelationshipEvents re
|
|
INNER JOIN dbo.CharacterRelationships cr ON cr.CharacterRelationshipID = re.CharacterRelationshipID
|
|
INNER JOIN dbo.Characters ca ON ca.CharacterID = cr.CharacterAID
|
|
INNER JOIN dbo.Characters cb ON cb.CharacterID = cr.CharacterBID
|
|
INNER JOIN dbo.RelationshipTypes rt ON rt.RelationshipTypeID = cr.RelationshipTypeID
|
|
INNER JOIN dbo.RelationshipStates rs ON rs.RelationshipStateID = re.RelationshipStateID
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = re.SceneID
|
|
INNER JOIN dbo.Chapters ch ON ch.ChapterID = s.ChapterID
|
|
INNER JOIN dbo.Books b ON b.BookID = ch.BookID
|
|
WHERE @CharacterID IN (cr.CharacterAID, cr.CharacterBID)
|
|
ORDER BY b.SortOrder, ch.SortOrder, s.SortOrder, re.CreatedDate, re.RelationshipEventID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.RelationshipEvent_ListByScene
|
|
@SceneID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT re.RelationshipEventID, re.CharacterRelationshipID, cr.ProjectID, cr.CharacterAID, ca.CharacterName AS CharacterAName,
|
|
cr.CharacterBID, cb.CharacterName AS CharacterBName, rt.TypeName AS RelationshipTypeName,
|
|
cr.IsReciprocal, re.SceneID, re.RelationshipStateID, rs.StateName AS RelationshipStateName, re.Intensity, re.IsKnownToOtherCharacter,
|
|
re.Description, re.CreatedDate, re.UpdatedDate
|
|
FROM dbo.RelationshipEvents re
|
|
INNER JOIN dbo.CharacterRelationships cr ON cr.CharacterRelationshipID = re.CharacterRelationshipID
|
|
INNER JOIN dbo.Characters ca ON ca.CharacterID = cr.CharacterAID
|
|
INNER JOIN dbo.Characters cb ON cb.CharacterID = cr.CharacterBID
|
|
INNER JOIN dbo.RelationshipTypes rt ON rt.RelationshipTypeID = cr.RelationshipTypeID
|
|
INNER JOIN dbo.RelationshipStates rs ON rs.RelationshipStateID = re.RelationshipStateID
|
|
WHERE re.SceneID = @SceneID
|
|
ORDER BY ca.CharacterName, cb.CharacterName, re.CreatedDate, re.RelationshipEventID;
|
|
END;
|
|
GO
|