348 lines
13 KiB
Transact-SQL
348 lines
13 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.ProjectHardDelete_ListFilePathsForOwner
|
|
@ProjectID int,
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
IF NOT EXISTS
|
|
(
|
|
SELECT 1
|
|
FROM dbo.Projects p
|
|
INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = p.ProjectID
|
|
WHERE p.ProjectID = @ProjectID
|
|
AND pua.UserID = @UserID
|
|
AND pua.AccessRole = N'Owner'
|
|
AND pua.IsActive = 1
|
|
AND p.IsArchived = 1
|
|
)
|
|
RETURN;
|
|
|
|
SELECT DISTINCT StoragePath
|
|
FROM dbo.UserFile
|
|
WHERE ProjectID = @ProjectID
|
|
AND NULLIF(LTRIM(RTRIM(StoragePath)), N'') IS NOT NULL
|
|
|
|
UNION
|
|
|
|
SELECT DISTINCT sa.FilePath
|
|
FROM dbo.SceneAttachments sa
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = sa.SceneID
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID
|
|
WHERE b.ProjectID = @ProjectID
|
|
AND NULLIF(LTRIM(RTRIM(sa.FilePath)), N'') IS NOT NULL;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.ProjectHardDelete_ForOwner
|
|
@ProjectID int,
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SET XACT_ABORT ON;
|
|
|
|
IF NOT EXISTS
|
|
(
|
|
SELECT 1
|
|
FROM dbo.Projects p
|
|
INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = p.ProjectID
|
|
WHERE p.ProjectID = @ProjectID
|
|
AND pua.UserID = @UserID
|
|
AND pua.AccessRole = N'Owner'
|
|
AND pua.IsActive = 1
|
|
)
|
|
THROW 52001, 'Project was not found for this owner.', 1;
|
|
|
|
IF EXISTS (SELECT 1 FROM dbo.Projects WHERE ProjectID = @ProjectID AND IsArchived = 0)
|
|
THROW 52002, 'Only archived projects can be permanently deleted.', 1;
|
|
|
|
BEGIN TRY
|
|
BEGIN TRANSACTION;
|
|
|
|
DELETE FROM dbo.ProjectActivity WHERE ProjectID = @ProjectID;
|
|
DELETE prh
|
|
FROM dbo.ProjectRestoreHistory prh
|
|
LEFT JOIN dbo.ProjectRestorePoints sourcePoint ON sourcePoint.ProjectRestorePointID = prh.SourceRestorePointID
|
|
LEFT JOIN dbo.ProjectRestorePoints safetyPoint ON safetyPoint.ProjectRestorePointID = prh.SafetyRestorePointID
|
|
WHERE prh.ProjectID = @ProjectID
|
|
OR sourcePoint.ProjectID = @ProjectID
|
|
OR safetyPoint.ProjectID = @ProjectID;
|
|
|
|
DELETE FROM dbo.ProjectRestorePoints WHERE ProjectID = @ProjectID;
|
|
DELETE FROM dbo.ProjectInvitation WHERE ProjectID = @ProjectID;
|
|
|
|
DELETE uf
|
|
FROM dbo.UserFile uf
|
|
WHERE uf.ProjectID = @ProjectID
|
|
OR EXISTS
|
|
(
|
|
SELECT 1
|
|
FROM dbo.SceneAttachments sa
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = sa.SceneID
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID
|
|
WHERE b.ProjectID = @ProjectID
|
|
AND sa.FilePath = uf.StoragePath
|
|
);
|
|
|
|
DELETE sw
|
|
FROM dbo.ScenarioWarnings sw
|
|
INNER JOIN dbo.Scenarios sc ON sc.ScenarioID = sw.ScenarioID
|
|
WHERE sc.ProjectID = @ProjectID;
|
|
|
|
DELETE svr
|
|
FROM dbo.ScenarioValidationRuns svr
|
|
INNER JOIN dbo.Scenarios sc ON sc.ScenarioID = svr.ScenarioID
|
|
WHERE sc.ProjectID = @ProjectID;
|
|
|
|
DELETE sso
|
|
FROM dbo.ScenarioSceneOrder sso
|
|
INNER JOIN dbo.Scenarios sc ON sc.ScenarioID = sso.ScenarioID
|
|
WHERE sc.ProjectID = @ProjectID;
|
|
|
|
DELETE FROM dbo.Scenarios WHERE ProjectID = @ProjectID;
|
|
|
|
DELETE FROM dbo.ContinuityWarnings WHERE ProjectID = @ProjectID;
|
|
DELETE FROM dbo.ValidationRuns WHERE ProjectID = @ProjectID;
|
|
|
|
DELETE sa
|
|
FROM dbo.SceneAttachments sa
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = sa.SceneID
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID
|
|
WHERE b.ProjectID = @ProjectID;
|
|
|
|
DELETE sci
|
|
FROM dbo.SceneChecklistItems sci
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = sci.SceneID
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID
|
|
WHERE b.ProjectID = @ProjectID;
|
|
|
|
DELETE sw
|
|
FROM dbo.SceneWorkflow sw
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = sw.SceneID
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID
|
|
WHERE b.ProjectID = @ProjectID;
|
|
|
|
DELETE sn
|
|
FROM dbo.SceneNotes sn
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = sn.SceneID
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID
|
|
WHERE b.ProjectID = @ProjectID;
|
|
|
|
DELETE cg
|
|
FROM dbo.ChapterGoals cg
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = cg.ChapterID
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID
|
|
WHERE b.ProjectID = @ProjectID;
|
|
|
|
DELETE sd
|
|
FROM dbo.SceneDependencies sd
|
|
LEFT JOIN dbo.Scenes ss ON ss.SceneID = sd.SourceSceneID
|
|
LEFT JOIN dbo.Chapters sc ON sc.ChapterID = ss.ChapterID
|
|
LEFT JOIN dbo.Books sb ON sb.BookID = sc.BookID
|
|
LEFT JOIN dbo.Scenes ts ON ts.SceneID = sd.TargetSceneID
|
|
LEFT JOIN dbo.Chapters tc ON tc.ChapterID = ts.ChapterID
|
|
LEFT JOIN dbo.Books tb ON tb.BookID = tc.BookID
|
|
WHERE sd.ProjectID = @ProjectID
|
|
OR sb.ProjectID = @ProjectID
|
|
OR tb.ProjectID = @ProjectID;
|
|
|
|
DELETE re
|
|
FROM dbo.RelationshipEvents re
|
|
INNER JOIN dbo.CharacterRelationships cr ON cr.CharacterRelationshipID = re.CharacterRelationshipID
|
|
WHERE cr.ProjectID = @ProjectID;
|
|
|
|
DELETE FROM dbo.CharacterRelationships WHERE ProjectID = @ProjectID;
|
|
|
|
DELETE ck
|
|
FROM dbo.CharacterKnowledge ck
|
|
LEFT JOIN dbo.Characters ch ON ch.CharacterID = ck.CharacterID
|
|
LEFT JOIN dbo.Scenes s ON s.SceneID = ck.SceneID
|
|
LEFT JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
|
|
LEFT JOIN dbo.Books b ON b.BookID = c.BookID
|
|
LEFT JOIN dbo.StoryAssets sa ON sa.StoryAssetID = ck.StoryAssetID
|
|
LEFT JOIN dbo.PlotThreads pt ON pt.PlotThreadID = ck.PlotThreadID
|
|
LEFT JOIN dbo.PlotLines pl ON pl.PlotLineID = pt.PlotLineID
|
|
WHERE ch.ProjectID = @ProjectID
|
|
OR b.ProjectID = @ProjectID
|
|
OR sa.ProjectID = @ProjectID
|
|
OR pl.ProjectID = @ProjectID;
|
|
|
|
DELETE cae
|
|
FROM dbo.CharacterAttributeEvents cae
|
|
INNER JOIN dbo.Characters ch ON ch.CharacterID = cae.CharacterID
|
|
WHERE ch.ProjectID = @ProjectID;
|
|
|
|
DELETE sc
|
|
FROM dbo.SceneCharacters sc
|
|
LEFT JOIN dbo.Characters ch ON ch.CharacterID = sc.CharacterID
|
|
LEFT JOIN dbo.Scenes s ON s.SceneID = sc.SceneID
|
|
LEFT JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
|
|
LEFT JOIN dbo.Books b ON b.BookID = c.BookID
|
|
WHERE ch.ProjectID = @ProjectID
|
|
OR b.ProjectID = @ProjectID;
|
|
|
|
DELETE acec
|
|
FROM dbo.AssetCustodyEventCharacters acec
|
|
INNER JOIN dbo.AssetCustodyEvents ace ON ace.AssetCustodyEventID = acec.AssetCustodyEventID
|
|
INNER JOIN dbo.StoryAssets sa ON sa.StoryAssetID = ace.StoryAssetID
|
|
WHERE sa.ProjectID = @ProjectID;
|
|
|
|
DELETE ace
|
|
FROM dbo.AssetCustodyEvents ace
|
|
INNER JOIN dbo.StoryAssets sa ON sa.StoryAssetID = ace.StoryAssetID
|
|
WHERE sa.ProjectID = @ProjectID;
|
|
|
|
DELETE ad
|
|
FROM dbo.AssetDependencies ad
|
|
INNER JOIN dbo.StoryAssets sourceAsset ON sourceAsset.StoryAssetID = ad.SourceAssetID
|
|
INNER JOIN dbo.StoryAssets targetAsset ON targetAsset.StoryAssetID = ad.TargetAssetID
|
|
WHERE sourceAsset.ProjectID = @ProjectID
|
|
OR targetAsset.ProjectID = @ProjectID;
|
|
|
|
DELETE ae
|
|
FROM dbo.AssetEvents ae
|
|
INNER JOIN dbo.StoryAssets sa ON sa.StoryAssetID = ae.StoryAssetID
|
|
WHERE sa.ProjectID = @ProjectID;
|
|
|
|
DELETE sal
|
|
FROM dbo.SceneAssetLocations sal
|
|
LEFT JOIN dbo.StoryAssets sa ON sa.StoryAssetID = sal.StoryAssetID
|
|
LEFT JOIN dbo.Scenes s ON s.SceneID = sal.SceneID
|
|
LEFT JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
|
|
LEFT JOIN dbo.Books b ON b.BookID = c.BookID
|
|
WHERE sa.ProjectID = @ProjectID
|
|
OR b.ProjectID = @ProjectID;
|
|
|
|
DELETE petpl
|
|
FROM dbo.PlotEventTargetPlotLines petpl
|
|
LEFT JOIN dbo.ThreadEvents te ON te.ThreadEventID = petpl.PlotEventID
|
|
LEFT JOIN dbo.PlotThreads pt ON pt.PlotThreadID = te.PlotThreadID
|
|
LEFT JOIN dbo.PlotLines eventPlotLine ON eventPlotLine.PlotLineID = pt.PlotLineID
|
|
LEFT JOIN dbo.PlotLines targetPlotLine ON targetPlotLine.PlotLineID = petpl.PlotLineID
|
|
WHERE eventPlotLine.ProjectID = @ProjectID
|
|
OR targetPlotLine.ProjectID = @ProjectID;
|
|
|
|
DELETE te
|
|
FROM dbo.ThreadEvents te
|
|
LEFT JOIN dbo.PlotThreads pt ON pt.PlotThreadID = te.PlotThreadID
|
|
LEFT JOIN dbo.PlotLines pl ON pl.PlotLineID = pt.PlotLineID
|
|
LEFT JOIN dbo.PlotLines target ON target.PlotLineID = te.TargetPlotLineID
|
|
WHERE pl.ProjectID = @ProjectID
|
|
OR target.ProjectID = @ProjectID;
|
|
|
|
DELETE pt
|
|
FROM dbo.PlotThreads pt
|
|
INNER JOIN dbo.PlotLines pl ON pl.PlotLineID = pt.PlotLineID
|
|
WHERE pl.ProjectID = @ProjectID;
|
|
|
|
UPDATE dbo.PlotLines
|
|
SET ParentPlotLineID = NULL,
|
|
EmergesFromPlotLineID = NULL
|
|
WHERE ProjectID = @ProjectID;
|
|
|
|
DELETE FROM dbo.PlotLines WHERE ProjectID = @ProjectID;
|
|
|
|
DELETE FROM dbo.TimelineViewPresets WHERE ProjectID = @ProjectID;
|
|
DELETE FROM dbo.ProjectTimelineMetricSettings WHERE ProjectID = @ProjectID;
|
|
DELETE FROM dbo.ProjectTimelineSettings WHERE ProjectID = @ProjectID;
|
|
|
|
DELETE smv
|
|
FROM dbo.SceneMetricValues smv
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = smv.SceneID
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID
|
|
WHERE b.ProjectID = @ProjectID;
|
|
|
|
DELETE sp
|
|
FROM dbo.ScenePurposes sp
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = sp.SceneID
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID
|
|
WHERE b.ProjectID = @ProjectID;
|
|
|
|
DELETE so
|
|
FROM dbo.SceneOutcomes so
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = so.SceneID
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID
|
|
WHERE b.ProjectID = @ProjectID;
|
|
|
|
UPDATE dbo.Characters
|
|
SET AgeReferenceSceneID = NULL
|
|
WHERE ProjectID = @ProjectID;
|
|
|
|
UPDATE s
|
|
SET PrimaryLocationID = NULL
|
|
FROM dbo.Scenes s
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID
|
|
WHERE b.ProjectID = @ProjectID;
|
|
|
|
UPDATE dbo.StoryAssets
|
|
SET CurrentLocationID = NULL
|
|
WHERE ProjectID = @ProjectID;
|
|
|
|
DELETE FROM dbo.StoryAssets WHERE ProjectID = @ProjectID;
|
|
|
|
UPDATE dbo.Locations
|
|
SET ParentLocationID = NULL
|
|
WHERE ProjectID = @ProjectID;
|
|
|
|
DELETE lr
|
|
FROM dbo.LocationRelationships lr
|
|
INNER JOIN dbo.Locations fromLocation ON fromLocation.LocationID = lr.FromLocationID
|
|
INNER JOIN dbo.Locations toLocation ON toLocation.LocationID = lr.ToLocationID
|
|
WHERE fromLocation.ProjectID = @ProjectID
|
|
OR toLocation.ProjectID = @ProjectID;
|
|
|
|
DELETE FROM dbo.Locations WHERE ProjectID = @ProjectID;
|
|
|
|
DELETE s
|
|
FROM dbo.Scenes s
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID
|
|
WHERE b.ProjectID = @ProjectID;
|
|
|
|
DELETE c
|
|
FROM dbo.Chapters c
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID
|
|
WHERE b.ProjectID = @ProjectID;
|
|
|
|
DELETE FROM dbo.Books WHERE ProjectID = @ProjectID;
|
|
DELETE FROM dbo.Characters WHERE ProjectID = @ProjectID;
|
|
|
|
DELETE gpmm
|
|
FROM dbo.GenreMetricPresetMetrics gpmm
|
|
INNER JOIN dbo.SceneMetricTypes mt ON mt.MetricTypeID = gpmm.MetricTypeID
|
|
WHERE mt.ProjectID = @ProjectID;
|
|
|
|
DELETE FROM dbo.SceneMetricTypes WHERE ProjectID = @ProjectID;
|
|
DELETE FROM dbo.CharacterAttributeTypes WHERE ProjectID = @ProjectID;
|
|
DELETE FROM dbo.AssetStates WHERE ProjectID = @ProjectID;
|
|
|
|
DELETE FROM dbo.ProjectUserAccess WHERE ProjectID = @ProjectID;
|
|
DELETE FROM dbo.Projects WHERE ProjectID = @ProjectID;
|
|
|
|
COMMIT TRANSACTION;
|
|
END TRY
|
|
BEGIN CATCH
|
|
IF @@TRANCOUNT > 0
|
|
ROLLBACK TRANSACTION;
|
|
|
|
THROW;
|
|
END CATCH
|
|
END;
|
|
GO
|