417 lines
18 KiB
Transact-SQL
417 lines
18 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
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;
|
|
|
|
DECLARE @BookIDs TABLE (BookID int NOT NULL PRIMARY KEY);
|
|
DECLARE @ChapterIDs TABLE (ChapterID int NOT NULL PRIMARY KEY);
|
|
DECLARE @SceneIDs TABLE (SceneID int NOT NULL PRIMARY KEY);
|
|
DECLARE @StoryIntelligenceRunIDs TABLE (StoryIntelligenceRunID int NOT NULL PRIMARY KEY);
|
|
DECLARE @StoryIntelligenceChapterResultIDs TABLE (ChapterResultID int NOT NULL PRIMARY KEY);
|
|
|
|
INSERT @BookIDs (BookID)
|
|
SELECT BookID FROM dbo.Books WHERE ProjectID = @ProjectID;
|
|
|
|
INSERT @ChapterIDs (ChapterID)
|
|
SELECT c.ChapterID
|
|
FROM dbo.Chapters c
|
|
INNER JOIN @BookIDs b ON b.BookID = c.BookID;
|
|
|
|
INSERT @SceneIDs (SceneID)
|
|
SELECT s.SceneID
|
|
FROM dbo.Scenes s
|
|
INNER JOIN @ChapterIDs c ON c.ChapterID = s.ChapterID;
|
|
|
|
INSERT @StoryIntelligenceRunIDs (StoryIntelligenceRunID)
|
|
SELECT DISTINCT r.StoryIntelligenceRunID
|
|
FROM dbo.StoryIntelligenceRuns r
|
|
WHERE r.ProjectID = @ProjectID
|
|
OR EXISTS (SELECT 1 FROM @BookIDs b WHERE b.BookID = r.BookID)
|
|
OR EXISTS (SELECT 1 FROM @ChapterIDs c WHERE c.ChapterID = r.ChapterID)
|
|
OR EXISTS (SELECT 1 FROM @SceneIDs s INNER JOIN dbo.Scenes scene ON scene.SceneID = s.SceneID WHERE scene.ImportRunID = r.StoryIntelligenceRunID);
|
|
|
|
INSERT @StoryIntelligenceChapterResultIDs (ChapterResultID)
|
|
SELECT DISTINCT cr.ChapterResultID
|
|
FROM dbo.StoryIntelligenceChapterResults cr
|
|
WHERE cr.ProjectID = @ProjectID
|
|
OR EXISTS (SELECT 1 FROM @StoryIntelligenceRunIDs r WHERE r.StoryIntelligenceRunID = cr.StoryIntelligenceRunID)
|
|
OR EXISTS (SELECT 1 FROM @BookIDs b WHERE b.BookID = cr.BookID)
|
|
OR EXISTS (SELECT 1 FROM @ChapterIDs c WHERE c.ChapterID = cr.ChapterID);
|
|
|
|
DELETE sr
|
|
FROM dbo.StoryIntelligenceSceneResults sr
|
|
WHERE sr.ProjectID = @ProjectID
|
|
OR EXISTS (SELECT 1 FROM @StoryIntelligenceRunIDs r WHERE r.StoryIntelligenceRunID = sr.StoryIntelligenceRunID)
|
|
OR EXISTS (SELECT 1 FROM @StoryIntelligenceChapterResultIDs cr WHERE cr.ChapterResultID = sr.ChapterResultID)
|
|
OR EXISTS (SELECT 1 FROM @BookIDs b WHERE b.BookID = sr.BookID)
|
|
OR EXISTS (SELECT 1 FROM @ChapterIDs c WHERE c.ChapterID = sr.ChapterID)
|
|
OR EXISTS (SELECT 1 FROM @SceneIDs s WHERE s.SceneID = sr.SceneID);
|
|
|
|
DELETE cr
|
|
FROM dbo.StoryIntelligenceChapterResults cr
|
|
WHERE cr.ProjectID = @ProjectID
|
|
OR EXISTS (SELECT 1 FROM @StoryIntelligenceRunIDs r WHERE r.StoryIntelligenceRunID = cr.StoryIntelligenceRunID)
|
|
OR EXISTS (SELECT 1 FROM @StoryIntelligenceChapterResultIDs result WHERE result.ChapterResultID = cr.ChapterResultID)
|
|
OR EXISTS (SELECT 1 FROM @BookIDs b WHERE b.BookID = cr.BookID)
|
|
OR EXISTS (SELECT 1 FROM @ChapterIDs c WHERE c.ChapterID = cr.ChapterID);
|
|
|
|
DELETE commitRows
|
|
FROM dbo.StoryIntelligenceImportCommits commitRows
|
|
WHERE commitRows.ProjectID = @ProjectID
|
|
OR EXISTS (SELECT 1 FROM @StoryIntelligenceRunIDs r WHERE r.StoryIntelligenceRunID = commitRows.StoryIntelligenceRunID)
|
|
OR EXISTS (SELECT 1 FROM @BookIDs b WHERE b.BookID = commitRows.BookID)
|
|
OR EXISTS (SELECT 1 FROM @ChapterIDs c WHERE c.ChapterID = commitRows.ChapterID);
|
|
|
|
UPDATE scene
|
|
SET ImportRunID = NULL
|
|
FROM dbo.Scenes scene
|
|
INNER JOIN @SceneIDs s ON s.SceneID = scene.SceneID
|
|
WHERE scene.ImportRunID IS NOT NULL;
|
|
|
|
DELETE r
|
|
FROM dbo.StoryIntelligenceRuns r
|
|
WHERE EXISTS (SELECT 1 FROM @StoryIntelligenceRunIDs runIDs WHERE runIDs.StoryIntelligenceRunID = r.StoryIntelligenceRunID);
|
|
|
|
DELETE job
|
|
FROM dbo.StoryIntelligenceJobs job
|
|
LEFT JOIN dbo.StoryIntelligenceConsents consent ON consent.StoryIntelligenceConsentID = job.StoryIntelligenceConsentID
|
|
WHERE job.ProjectID = @ProjectID
|
|
OR EXISTS (SELECT 1 FROM @BookIDs b WHERE b.BookID = job.BookID)
|
|
OR consent.ProjectID = @ProjectID
|
|
OR EXISTS (SELECT 1 FROM @BookIDs b WHERE b.BookID = consent.BookID);
|
|
|
|
DELETE consent
|
|
FROM dbo.StoryIntelligenceConsents consent
|
|
WHERE consent.ProjectID = @ProjectID
|
|
OR EXISTS (SELECT 1 FROM @BookIDs b WHERE b.BookID = consent.BookID);
|
|
|
|
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 @SceneIDs s ON s.SceneID = sa.SceneID
|
|
WHERE sa.FilePath = uf.StoragePath
|
|
);
|
|
|
|
DELETE wsi
|
|
FROM dbo.WritingScheduleItems wsi
|
|
INNER JOIN dbo.WritingPlans wp ON wp.WritingPlanID = wsi.WritingPlanID
|
|
WHERE wp.ProjectID = @ProjectID;
|
|
|
|
DELETE wsi
|
|
FROM dbo.WritingScheduleItems wsi
|
|
INNER JOIN @SceneIDs s ON s.SceneID = wsi.SceneID;
|
|
|
|
DELETE FROM dbo.WritingPlans WHERE ProjectID = @ProjectID OR EXISTS (SELECT 1 FROM @BookIDs b WHERE b.BookID = dbo.WritingPlans.BookID);
|
|
|
|
UPDATE dbo.UserOnboardingState
|
|
SET ProjectID = NULL,
|
|
BookID = NULL
|
|
WHERE ProjectID = @ProjectID
|
|
OR EXISTS (SELECT 1 FROM @BookIDs b WHERE b.BookID = dbo.UserOnboardingState.BookID);
|
|
|
|
DELETE ombsm FROM dbo.OnboardingManuscriptBuildSceneMap ombsm INNER JOIN @SceneIDs scene ON scene.SceneID = ombsm.SceneID;
|
|
DELETE ombcm FROM dbo.OnboardingManuscriptBuildChapterMap ombcm INNER JOIN @ChapterIDs chapter ON chapter.ChapterID = ombcm.ChapterID;
|
|
DELETE ombcm
|
|
FROM dbo.OnboardingManuscriptBuildChapterMap ombcm
|
|
INNER JOIN dbo.OnboardingManuscriptBuilds build ON build.OnboardingManuscriptBuildID = ombcm.OnboardingManuscriptBuildID
|
|
WHERE build.ProjectID = @ProjectID OR EXISTS (SELECT 1 FROM @BookIDs b WHERE b.BookID = build.BookID);
|
|
DELETE ombsm
|
|
FROM dbo.OnboardingManuscriptBuildSceneMap ombsm
|
|
INNER JOIN dbo.OnboardingManuscriptBuilds build ON build.OnboardingManuscriptBuildID = ombsm.OnboardingManuscriptBuildID
|
|
WHERE build.ProjectID = @ProjectID OR EXISTS (SELECT 1 FROM @BookIDs b WHERE b.BookID = build.BookID);
|
|
DELETE ombcharm
|
|
FROM dbo.OnboardingManuscriptBuildCharacterMap ombcharm
|
|
INNER JOIN dbo.OnboardingManuscriptBuilds build ON build.OnboardingManuscriptBuildID = ombcharm.OnboardingManuscriptBuildID
|
|
WHERE build.ProjectID = @ProjectID OR EXISTS (SELECT 1 FROM @BookIDs b WHERE b.BookID = build.BookID);
|
|
DELETE FROM dbo.OnboardingManuscriptBuilds WHERE ProjectID = @ProjectID OR EXISTS (SELECT 1 FROM @BookIDs b WHERE b.BookID = dbo.OnboardingManuscriptBuilds.BookID);
|
|
|
|
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 sso
|
|
FROM dbo.ScenarioSceneOrder sso
|
|
WHERE EXISTS (SELECT 1 FROM @SceneIDs scene WHERE scene.SceneID = sso.SceneID)
|
|
OR EXISTS (SELECT 1 FROM @ChapterIDs chapter WHERE chapter.ChapterID = sso.ProposedChapterID);
|
|
|
|
DELETE FROM dbo.Scenarios WHERE ProjectID = @ProjectID;
|
|
|
|
DELETE cwa
|
|
FROM dbo.ContinuityWarningAcknowledgements cwa
|
|
WHERE cwa.ProjectID = @ProjectID
|
|
OR EXISTS (SELECT 1 FROM @SceneIDs scene WHERE scene.SceneID = cwa.SceneID);
|
|
|
|
DELETE FROM dbo.ContinuityWarnings WHERE ProjectID = @ProjectID;
|
|
DELETE FROM dbo.ValidationRuns WHERE ProjectID = @ProjectID;
|
|
|
|
DELETE sfpo FROM dbo.SceneFloorPlanOccupancy sfpo INNER JOIN @SceneIDs s ON s.SceneID = sfpo.SceneID;
|
|
DELETE sa FROM dbo.SceneAttachments sa INNER JOIN @SceneIDs s ON s.SceneID = sa.SceneID;
|
|
DELETE sci FROM dbo.SceneChecklistItems sci INNER JOIN @SceneIDs s ON s.SceneID = sci.SceneID;
|
|
DELETE sw FROM dbo.SceneWorkflow sw INNER JOIN @SceneIDs s ON s.SceneID = sw.SceneID;
|
|
DELETE sn FROM dbo.SceneNotes sn INNER JOIN @SceneIDs s ON s.SceneID = sn.SceneID;
|
|
DELETE cg FROM dbo.ChapterGoals cg INNER JOIN @ChapterIDs c ON c.ChapterID = cg.ChapterID;
|
|
|
|
DELETE sd
|
|
FROM dbo.SceneDependencies sd
|
|
LEFT JOIN @SceneIDs ss ON ss.SceneID = sd.SourceSceneID
|
|
LEFT JOIN @SceneIDs ts ON ts.SceneID = sd.TargetSceneID
|
|
WHERE sd.ProjectID = @ProjectID
|
|
OR ss.SceneID IS NOT NULL
|
|
OR ts.SceneID IS NOT NULL;
|
|
|
|
DELETE re
|
|
FROM dbo.RelationshipEvents re
|
|
INNER JOIN dbo.CharacterRelationships cr ON cr.CharacterRelationshipID = re.CharacterRelationshipID
|
|
WHERE cr.ProjectID = @ProjectID;
|
|
|
|
DELETE re FROM dbo.RelationshipEvents re INNER JOIN @SceneIDs scene ON scene.SceneID = re.SceneID;
|
|
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 @SceneIDs scene ON scene.SceneID = ck.SceneID
|
|
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 scene.SceneID IS NOT NULL
|
|
OR sa.ProjectID = @ProjectID
|
|
OR pl.ProjectID = @ProjectID;
|
|
|
|
DELETE cae
|
|
FROM dbo.CharacterAttributeEvents cae
|
|
LEFT JOIN dbo.Characters ch ON ch.CharacterID = cae.CharacterID
|
|
LEFT JOIN @SceneIDs scene ON scene.SceneID = cae.SceneID
|
|
WHERE ch.ProjectID = @ProjectID
|
|
OR scene.SceneID IS NOT NULL;
|
|
|
|
DELETE sc
|
|
FROM dbo.SceneCharacters sc
|
|
LEFT JOIN dbo.Characters ch ON ch.CharacterID = sc.CharacterID
|
|
LEFT JOIN @SceneIDs scene ON scene.SceneID = sc.SceneID
|
|
WHERE ch.ProjectID = @ProjectID
|
|
OR scene.SceneID IS NOT NULL;
|
|
|
|
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 acec
|
|
FROM dbo.AssetCustodyEventCharacters acec
|
|
INNER JOIN dbo.AssetCustodyEvents ace ON ace.AssetCustodyEventID = acec.AssetCustodyEventID
|
|
INNER JOIN @SceneIDs scene ON scene.SceneID = ace.SceneID;
|
|
|
|
DELETE ace
|
|
FROM dbo.AssetCustodyEvents ace
|
|
LEFT JOIN dbo.StoryAssets sa ON sa.StoryAssetID = ace.StoryAssetID
|
|
LEFT JOIN @SceneIDs scene ON scene.SceneID = ace.SceneID
|
|
WHERE sa.ProjectID = @ProjectID
|
|
OR scene.SceneID IS NOT NULL;
|
|
|
|
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
|
|
LEFT JOIN dbo.StoryAssets sa ON sa.StoryAssetID = ae.StoryAssetID
|
|
LEFT JOIN @SceneIDs scene ON scene.SceneID = ae.SceneID
|
|
WHERE sa.ProjectID = @ProjectID
|
|
OR scene.SceneID IS NOT NULL;
|
|
|
|
DELETE sal
|
|
FROM dbo.SceneAssetLocations sal
|
|
LEFT JOIN dbo.StoryAssets sa ON sa.StoryAssetID = sal.StoryAssetID
|
|
LEFT JOIN @SceneIDs scene ON scene.SceneID = sal.SceneID
|
|
WHERE sa.ProjectID = @ProjectID
|
|
OR scene.SceneID IS NOT NULL;
|
|
|
|
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
|
|
OR EXISTS (SELECT 1 FROM @SceneIDs scene WHERE scene.SceneID = te.SceneID);
|
|
|
|
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
|
|
LEFT JOIN @SceneIDs scene ON scene.SceneID = te.SceneID
|
|
WHERE pl.ProjectID = @ProjectID
|
|
OR target.ProjectID = @ProjectID
|
|
OR scene.SceneID IS NOT NULL;
|
|
|
|
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 OR EXISTS (SELECT 1 FROM @BookIDs b WHERE b.BookID = dbo.TimelineViewPresets.BookID);
|
|
DELETE FROM dbo.ProjectTimelineMetricSettings WHERE ProjectID = @ProjectID;
|
|
DELETE FROM dbo.ProjectTimelineSettings WHERE ProjectID = @ProjectID;
|
|
|
|
DELETE smv FROM dbo.SceneMetricValues smv INNER JOIN @SceneIDs s ON s.SceneID = smv.SceneID;
|
|
DELETE sp FROM dbo.ScenePurposes sp INNER JOIN @SceneIDs s ON s.SceneID = sp.SceneID;
|
|
DELETE so FROM dbo.SceneOutcomes so INNER JOIN @SceneIDs s ON s.SceneID = so.SceneID;
|
|
|
|
UPDATE dbo.Characters
|
|
SET AgeReferenceSceneID = NULL
|
|
WHERE ProjectID = @ProjectID;
|
|
|
|
UPDATE s
|
|
SET PrimaryLocationID = NULL,
|
|
FloorPlanID = NULL,
|
|
InitialFloorPlanFloorID = NULL,
|
|
ManuscriptDocumentID = NULL
|
|
FROM dbo.Scenes s
|
|
INNER JOIN @SceneIDs scene ON scene.SceneID = s.SceneID;
|
|
|
|
UPDATE c
|
|
SET ManuscriptDocumentID = NULL
|
|
FROM dbo.Chapters c
|
|
INNER JOIN @ChapterIDs chapter ON chapter.ChapterID = c.ChapterID;
|
|
|
|
UPDATE dbo.StoryAssets
|
|
SET CurrentLocationID = NULL
|
|
WHERE ProjectID = @ProjectID;
|
|
|
|
DELETE fpt
|
|
FROM dbo.FloorPlanTransitions fpt
|
|
INNER JOIN dbo.FloorPlanFloors fpf ON fpf.FloorPlanFloorID = fpt.FloorPlanFloorID
|
|
INNER JOIN dbo.FloorPlans fp ON fp.FloorPlanID = fpf.FloorPlanID
|
|
WHERE fp.ProjectID = @ProjectID;
|
|
|
|
DELETE fpb
|
|
FROM dbo.FloorPlanBlocks fpb
|
|
INNER JOIN dbo.FloorPlanFloors fpf ON fpf.FloorPlanFloorID = fpb.FloorPlanFloorID
|
|
INNER JOIN dbo.FloorPlans fp ON fp.FloorPlanID = fpf.FloorPlanID
|
|
WHERE fp.ProjectID = @ProjectID;
|
|
|
|
DELETE fpf
|
|
FROM dbo.FloorPlanFloors fpf
|
|
INNER JOIN dbo.FloorPlans fp ON fp.FloorPlanID = fpf.FloorPlanID
|
|
WHERE fp.ProjectID = @ProjectID;
|
|
|
|
DELETE FROM dbo.FloorPlans 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 @SceneIDs scene ON scene.SceneID = s.SceneID;
|
|
DELETE c FROM dbo.Chapters c INNER JOIN @ChapterIDs chapter ON chapter.ChapterID = c.ChapterID;
|
|
|
|
DELETE FROM dbo.ManuscriptDocuments
|
|
WHERE ProjectID = @ProjectID
|
|
OR EXISTS (SELECT 1 FROM @BookIDs b WHERE b.BookID = dbo.ManuscriptDocuments.BookID);
|
|
|
|
DELETE FROM dbo.Books WHERE ProjectID = @ProjectID;
|
|
|
|
DELETE FROM dbo.CharacterImages 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
|