305 lines
12 KiB
Transact-SQL
305 lines
12 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.StoryIntelligenceConsents', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.StoryIntelligenceConsents
|
|
(
|
|
StoryIntelligenceConsentID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_StoryIntelligenceConsents PRIMARY KEY,
|
|
UserID int NOT NULL,
|
|
UserOnboardingStateID int NOT NULL,
|
|
ProjectID int NOT NULL,
|
|
BookID int NOT NULL,
|
|
ConsentedUtc datetime2 NOT NULL CONSTRAINT DF_StoryIntelligenceConsents_ConsentedUtc DEFAULT SYSUTCDATETIME(),
|
|
ConsentVersion nvarchar(40) NOT NULL CONSTRAINT DF_StoryIntelligenceConsents_ConsentVersion DEFAULT N'Phase20H',
|
|
ConsentText nvarchar(1000) NOT NULL,
|
|
CONSTRAINT FK_StoryIntelligenceConsents_AppUser FOREIGN KEY (UserID) REFERENCES dbo.AppUser(UserID),
|
|
CONSTRAINT FK_StoryIntelligenceConsents_UserOnboardingState FOREIGN KEY (UserOnboardingStateID) REFERENCES dbo.UserOnboardingState(UserOnboardingStateID),
|
|
CONSTRAINT FK_StoryIntelligenceConsents_Projects FOREIGN KEY (ProjectID) REFERENCES dbo.Projects(ProjectID),
|
|
CONSTRAINT FK_StoryIntelligenceConsents_Books FOREIGN KEY (BookID) REFERENCES dbo.Books(BookID)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.StoryIntelligenceJobs', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.StoryIntelligenceJobs
|
|
(
|
|
StoryIntelligenceJobID uniqueidentifier NOT NULL CONSTRAINT PK_StoryIntelligenceJobs PRIMARY KEY,
|
|
StoryIntelligenceConsentID int NOT NULL,
|
|
UserID int NOT NULL,
|
|
ProjectID int NOT NULL,
|
|
BookID int NOT NULL,
|
|
Status nvarchar(40) NOT NULL CONSTRAINT DF_StoryIntelligenceJobs_Status DEFAULT N'Pending',
|
|
CreatedUtc datetime2 NOT NULL CONSTRAINT DF_StoryIntelligenceJobs_CreatedUtc DEFAULT SYSUTCDATETIME(),
|
|
StartedUtc datetime2 NULL,
|
|
CompletedUtc datetime2 NULL,
|
|
CancelledUtc datetime2 NULL,
|
|
ProgressPercent int NOT NULL CONSTRAINT DF_StoryIntelligenceJobs_ProgressPercent DEFAULT 0,
|
|
CurrentStage nvarchar(80) NOT NULL CONSTRAINT DF_StoryIntelligenceJobs_CurrentStage DEFAULT N'Pending',
|
|
CurrentMessage nvarchar(400) NOT NULL CONSTRAINT DF_StoryIntelligenceJobs_CurrentMessage DEFAULT N'Story Intelligence is queued.',
|
|
EstimatedRemaining nvarchar(80) NULL,
|
|
ErrorMessage nvarchar(1000) NULL,
|
|
UpdatedUtc datetime2 NOT NULL CONSTRAINT DF_StoryIntelligenceJobs_UpdatedUtc DEFAULT SYSUTCDATETIME(),
|
|
CONSTRAINT FK_StoryIntelligenceJobs_Consent FOREIGN KEY (StoryIntelligenceConsentID) REFERENCES dbo.StoryIntelligenceConsents(StoryIntelligenceConsentID),
|
|
CONSTRAINT FK_StoryIntelligenceJobs_AppUser FOREIGN KEY (UserID) REFERENCES dbo.AppUser(UserID),
|
|
CONSTRAINT FK_StoryIntelligenceJobs_Projects FOREIGN KEY (ProjectID) REFERENCES dbo.Projects(ProjectID),
|
|
CONSTRAINT FK_StoryIntelligenceJobs_Books FOREIGN KEY (BookID) REFERENCES dbo.Books(BookID),
|
|
CONSTRAINT CK_StoryIntelligenceJobs_Status CHECK (Status IN
|
|
(
|
|
N'Pending', N'Preparing', N'ReadingScenes', N'AnalysingCharacters', N'AnalysingRelationships',
|
|
N'AnalysingLocations', N'AnalysingAssets', N'AnalysingTimeline', N'Finalising',
|
|
N'Completed', N'Cancelled', N'Failed'
|
|
)),
|
|
CONSTRAINT CK_StoryIntelligenceJobs_ProgressPercent CHECK (ProgressPercent >= 0 AND ProgressPercent <= 100)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_StoryIntelligenceJobs_User_Status' AND object_id = OBJECT_ID(N'dbo.StoryIntelligenceJobs'))
|
|
CREATE INDEX IX_StoryIntelligenceJobs_User_Status ON dbo.StoryIntelligenceJobs(UserID, Status, CreatedUtc DESC);
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_StoryIntelligenceJobs_Pending' AND object_id = OBJECT_ID(N'dbo.StoryIntelligenceJobs'))
|
|
CREATE INDEX IX_StoryIntelligenceJobs_Pending ON dbo.StoryIntelligenceJobs(Status, CreatedUtc) INCLUDE (StoryIntelligenceJobID);
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.StoryIntelligenceJob_Get
|
|
@UserID int,
|
|
@JobID uniqueidentifier
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT StoryIntelligenceJobID AS JobID, StoryIntelligenceConsentID AS ConsentID, UserID, ProjectID, BookID,
|
|
Status, CreatedUtc, StartedUtc, CompletedUtc, CancelledUtc, ProgressPercent, CurrentStage,
|
|
CurrentMessage, EstimatedRemaining, ErrorMessage, UpdatedUtc
|
|
FROM dbo.StoryIntelligenceJobs
|
|
WHERE StoryIntelligenceJobID = @JobID
|
|
AND UserID = @UserID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.StoryIntelligenceJob_CreateWithConsent
|
|
@UserID int,
|
|
@UserOnboardingStateID int,
|
|
@ProjectID int,
|
|
@BookID int,
|
|
@ConsentText nvarchar(1000)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SET XACT_ABORT ON;
|
|
|
|
IF NOT EXISTS
|
|
(
|
|
SELECT 1
|
|
FROM dbo.UserOnboardingState uos
|
|
INNER JOIN dbo.Books b ON b.BookID = @BookID AND b.ProjectID = @ProjectID AND b.IsArchived = 0
|
|
INNER JOIN dbo.Projects p ON p.ProjectID = @ProjectID AND p.IsArchived = 0
|
|
INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = p.ProjectID AND pua.UserID = @UserID AND pua.IsActive = 1
|
|
WHERE uos.UserOnboardingStateID = @UserOnboardingStateID
|
|
AND uos.UserID = @UserID
|
|
)
|
|
RETURN;
|
|
|
|
DECLARE @ExistingActiveJobID uniqueidentifier;
|
|
SELECT TOP (1) @ExistingActiveJobID = StoryIntelligenceJobID
|
|
FROM dbo.StoryIntelligenceJobs
|
|
WHERE UserID = @UserID
|
|
AND ProjectID = @ProjectID
|
|
AND BookID = @BookID
|
|
AND Status IN (N'Pending', N'Preparing', N'ReadingScenes', N'AnalysingCharacters', N'AnalysingRelationships',
|
|
N'AnalysingLocations', N'AnalysingAssets', N'AnalysingTimeline', N'Finalising')
|
|
ORDER BY CreatedUtc DESC;
|
|
|
|
IF @ExistingActiveJobID IS NOT NULL
|
|
BEGIN
|
|
EXEC dbo.StoryIntelligenceJob_Get @UserID = @UserID, @JobID = @ExistingActiveJobID;
|
|
RETURN;
|
|
END;
|
|
|
|
DECLARE @ConsentID int;
|
|
DECLARE @JobID uniqueidentifier = NEWID();
|
|
|
|
BEGIN TRANSACTION;
|
|
|
|
INSERT dbo.StoryIntelligenceConsents (UserID, UserOnboardingStateID, ProjectID, BookID, ConsentText)
|
|
VALUES (@UserID, @UserOnboardingStateID, @ProjectID, @BookID, @ConsentText);
|
|
SET @ConsentID = CAST(SCOPE_IDENTITY() AS int);
|
|
|
|
INSERT dbo.StoryIntelligenceJobs
|
|
(
|
|
StoryIntelligenceJobID, StoryIntelligenceConsentID, UserID, ProjectID, BookID,
|
|
Status, ProgressPercent, CurrentStage, CurrentMessage, EstimatedRemaining
|
|
)
|
|
VALUES
|
|
(
|
|
@JobID, @ConsentID, @UserID, @ProjectID, @BookID,
|
|
N'Pending', 0, N'Pending', N'Story Intelligence is queued.', N'Calculating'
|
|
);
|
|
|
|
COMMIT TRANSACTION;
|
|
|
|
EXEC dbo.StoryIntelligenceJob_Get @UserID = @UserID, @JobID = @JobID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.StoryIntelligenceJob_GetLatestForUser
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT TOP (1) StoryIntelligenceJobID AS JobID, StoryIntelligenceConsentID AS ConsentID, UserID, ProjectID, BookID,
|
|
Status, CreatedUtc, StartedUtc, CompletedUtc, CancelledUtc, ProgressPercent, CurrentStage,
|
|
CurrentMessage, EstimatedRemaining, ErrorMessage, UpdatedUtc
|
|
FROM dbo.StoryIntelligenceJobs
|
|
WHERE UserID = @UserID
|
|
ORDER BY CreatedUtc DESC;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.StoryIntelligenceJob_ClaimNextPending
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SET XACT_ABORT ON;
|
|
|
|
DECLARE @JobID uniqueidentifier;
|
|
SELECT TOP (1) @JobID = StoryIntelligenceJobID
|
|
FROM dbo.StoryIntelligenceJobs WITH (UPDLOCK, READPAST)
|
|
WHERE Status = N'Pending'
|
|
ORDER BY CreatedUtc;
|
|
|
|
IF @JobID IS NULL
|
|
BEGIN
|
|
SELECT TOP (0) StoryIntelligenceJobID AS JobID, StoryIntelligenceConsentID AS ConsentID, UserID, ProjectID, BookID,
|
|
Status, CreatedUtc, StartedUtc, CompletedUtc, CancelledUtc, ProgressPercent, CurrentStage,
|
|
CurrentMessage, EstimatedRemaining, ErrorMessage, UpdatedUtc
|
|
FROM dbo.StoryIntelligenceJobs;
|
|
RETURN;
|
|
END;
|
|
|
|
UPDATE dbo.StoryIntelligenceJobs
|
|
SET Status = N'Preparing',
|
|
StartedUtc = COALESCE(StartedUtc, SYSUTCDATETIME()),
|
|
ProgressPercent = 5,
|
|
CurrentStage = N'Preparing',
|
|
CurrentMessage = N'Preparing manuscript...',
|
|
EstimatedRemaining = N'Calculating',
|
|
UpdatedUtc = SYSUTCDATETIME()
|
|
WHERE StoryIntelligenceJobID = @JobID
|
|
AND Status = N'Pending';
|
|
|
|
SELECT j.StoryIntelligenceJobID AS JobID, j.StoryIntelligenceConsentID AS ConsentID, j.UserID, j.ProjectID, j.BookID,
|
|
j.Status, j.CreatedUtc, j.StartedUtc, j.CompletedUtc, j.CancelledUtc, j.ProgressPercent, j.CurrentStage,
|
|
j.CurrentMessage, j.EstimatedRemaining, j.ErrorMessage, j.UpdatedUtc
|
|
FROM dbo.StoryIntelligenceJobs j
|
|
WHERE j.StoryIntelligenceJobID = @JobID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.StoryIntelligenceJob_UpdateProgress
|
|
@JobID uniqueidentifier,
|
|
@Status nvarchar(40),
|
|
@ProgressPercent int,
|
|
@CurrentStage nvarchar(80),
|
|
@CurrentMessage nvarchar(400),
|
|
@EstimatedRemaining nvarchar(80) = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
UPDATE dbo.StoryIntelligenceJobs
|
|
SET Status = @Status,
|
|
ProgressPercent = @ProgressPercent,
|
|
CurrentStage = @CurrentStage,
|
|
CurrentMessage = @CurrentMessage,
|
|
EstimatedRemaining = @EstimatedRemaining,
|
|
UpdatedUtc = SYSUTCDATETIME()
|
|
WHERE StoryIntelligenceJobID = @JobID
|
|
AND Status NOT IN (N'Completed', N'Cancelled', N'Failed');
|
|
|
|
SELECT StoryIntelligenceJobID AS JobID, StoryIntelligenceConsentID AS ConsentID, UserID, ProjectID, BookID,
|
|
Status, CreatedUtc, StartedUtc, CompletedUtc, CancelledUtc, ProgressPercent, CurrentStage,
|
|
CurrentMessage, EstimatedRemaining, ErrorMessage, UpdatedUtc
|
|
FROM dbo.StoryIntelligenceJobs
|
|
WHERE StoryIntelligenceJobID = @JobID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.StoryIntelligenceJob_Complete
|
|
@JobID uniqueidentifier
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
UPDATE dbo.StoryIntelligenceJobs
|
|
SET Status = N'Completed',
|
|
ProgressPercent = 100,
|
|
CurrentStage = N'Completed',
|
|
CurrentMessage = N'Story Intelligence is ready.',
|
|
EstimatedRemaining = N'Complete',
|
|
CompletedUtc = COALESCE(CompletedUtc, SYSUTCDATETIME()),
|
|
UpdatedUtc = SYSUTCDATETIME()
|
|
WHERE StoryIntelligenceJobID = @JobID
|
|
AND Status NOT IN (N'Completed', N'Cancelled', N'Failed');
|
|
|
|
SELECT StoryIntelligenceJobID AS JobID, StoryIntelligenceConsentID AS ConsentID, UserID, ProjectID, BookID,
|
|
Status, CreatedUtc, StartedUtc, CompletedUtc, CancelledUtc, ProgressPercent, CurrentStage,
|
|
CurrentMessage, EstimatedRemaining, ErrorMessage, UpdatedUtc
|
|
FROM dbo.StoryIntelligenceJobs
|
|
WHERE StoryIntelligenceJobID = @JobID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.StoryIntelligenceJob_Cancel
|
|
@UserID int,
|
|
@JobID uniqueidentifier
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
UPDATE dbo.StoryIntelligenceJobs
|
|
SET Status = N'Cancelled',
|
|
CurrentStage = N'Cancelled',
|
|
CurrentMessage = N'Story Intelligence setup was cancelled.',
|
|
EstimatedRemaining = NULL,
|
|
CancelledUtc = COALESCE(CancelledUtc, SYSUTCDATETIME()),
|
|
UpdatedUtc = SYSUTCDATETIME()
|
|
WHERE StoryIntelligenceJobID = @JobID
|
|
AND UserID = @UserID
|
|
AND Status NOT IN (N'Completed', N'Cancelled', N'Failed');
|
|
|
|
EXEC dbo.StoryIntelligenceJob_Get @UserID = @UserID, @JobID = @JobID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.StoryIntelligenceJob_Fail
|
|
@JobID uniqueidentifier,
|
|
@ErrorMessage nvarchar(1000)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
UPDATE dbo.StoryIntelligenceJobs
|
|
SET Status = N'Failed',
|
|
CurrentStage = N'Failed',
|
|
CurrentMessage = N'Story Intelligence setup could not finish.',
|
|
ErrorMessage = @ErrorMessage,
|
|
EstimatedRemaining = NULL,
|
|
UpdatedUtc = SYSUTCDATETIME()
|
|
WHERE StoryIntelligenceJobID = @JobID
|
|
AND Status NOT IN (N'Completed', N'Cancelled');
|
|
|
|
SELECT StoryIntelligenceJobID AS JobID, StoryIntelligenceConsentID AS ConsentID, UserID, ProjectID, BookID,
|
|
Status, CreatedUtc, StartedUtc, CompletedUtc, CancelledUtc, ProgressPercent, CurrentStage,
|
|
CurrentMessage, EstimatedRemaining, ErrorMessage, UpdatedUtc
|
|
FROM dbo.StoryIntelligenceJobs
|
|
WHERE StoryIntelligenceJobID = @JobID;
|
|
END;
|
|
GO
|