Moving via the UI doesn't work for closed cases. Therefore, I wrote some T-SQL SPs to really merge projects on the DB level for FB6 (may need to be updated to FB7 - haven't tried it) because we had a 3-digit count of projects, some of which were good merge candidates.
For the code see below - use at your own risk and make sure to have a backup of your DB before using any of this stuff!
Notes:
Copy the whole code into a SQL query window attached to the FB database and run it, it will create 3 SPs which you can then call.
There are separate SPs to first move the data, then fully delete a project (can also be sued before moving data away).
As a bonus, there is also a SP to remove persons which are obsolete because they link to nothing.
To avoid mistyping project numbers, both the project ID and name are required as arguments when calling the SP.
The area flag controls whether all cases are moved to a new area with the old project name or if the areas are moved also. This can be useful if you want to "mirror" the source project as area in the destination project.
Code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [DeleteProject] @ixProject int, @sProject nvarchar(128)
AS
BEGIN
SET NOCOUNT ON ;
IF ((SELECT COUNT(*) FROM Project p WHERE (p.ixProject = @ixProject) AND (p.sProject = @sProject)) > 0)
BEGIN
DELETE ACL
WHERE (ACL.ixSection = @ixProject)
AND (sSectionName = 'Project') ;
DELETE ProjectView
WHERE ixProject = @ixProject ;
UPDATE Bug
SET ixBugEventLatest=NULL
WHERE ixProject = @ixProject ;
UPDATE Bug
SET ixBugEventLatestText=NULL
WHERE ixProject = @ixProject ;
-- Temporary storage for indexes to be deleted
DECLARE @ToDelete TABLE (
ix int NOT NULL
PRIMARY KEY
) ;
-- Find the bugs to be deleted
INSERT @ToDelete
SELECT b.ixBug
FROM Bug b
WHERE b.ixProject = @ixProject ;
UPDATE BugEvent
SET ixAttachment=NULL
FROM @ToDelete td
WHERE td.ix = BugEvent.ixBug ;
DELETE Attachment
FROM @ToDelete td
JOIN BugEvent be ON td.ix = be.ixBug
WHERE (Attachment.ixBugEvent = be.ixBugEvent) ;
DELETE BugView
FROM @ToDelete td
JOIN BugEvent be ON td.ix = be.ixBug
WHERE (BugView.ixBugEvent = be.ixBugEvent)
OR (BugView.ixBug = td.ix) ;
DELETE BugEvent
FROM @ToDelete td
WHERE (BugEvent.ixBug = td.ix) ;
DELETE BugRelation
FROM @ToDelete td
WHERE (BugRelation.ixBugFrom = td.ix)
OR (BugRelation.ixBugTo = td.ix) ;
DELETE CVS
FROM @ToDelete td
WHERE (CVS.ixBug = td.ix) ;
DELETE Duplicates
FROM @ToDelete td
WHERE (Duplicates.ixBugDupe = td.ix)
OR (Duplicates.ixBugDupeOf = td.ix) ;
DELETE Scout
FROM @ToDelete td
WHERE (Scout.ixBug = td.ix) ;
DELETE Subscriptions
FROM @ToDelete td
WHERE (Subscriptions.ixBug = td.ix) ;
DELETE DocListItem
FROM @ToDelete td
WHERE (DocListItem.sType = 'Bug')
AND (DocListItem.ixItem = td.ix) ;
DELETE TimeInterval
FROM @ToDelete td
WHERE (TimeInterval.ixBug = td.ix) ;
DELETE TrainingRequest
FROM @ToDelete td
WHERE (TrainingRequest.ixBug = td.ix) ;
DELETE Bug
FROM @ToDelete td
WHERE (td.ix = Bug.ixBug) ;
DELETE @ToDelete ;
-- Then find the areas to be deleted
INSERT @ToDelete
SELECT a.ixArea
FROM Area a
WHERE a.ixProject = @ixProject ;
DELETE Prediction
FROM @ToDelete td
WHERE (Prediction.ixAreaA = td.ix)
OR (Prediction.ixAreaB = td.ix) ;
DELETE GridColumn
FROM Filter f
LEFT JOIN @ToDelete td ON (td.ix = f.ixArea)
WHERE (
(td.ix IS NOT NULL)
OR (f.ixProject = @ixProject)
)
AND (GridColumn.ixFilter = f.ixFilter) ;
DELETE Filter
FROM @ToDelete td
WHERE (Filter.ixArea = td.ix)
OR (Filter.ixProject = @ixProject) ;
DELETE TokenAssociation
FROM @ToDelete td
WHERE (TokenAssociation.ixArea = td.ix)
OR (TokenAssociation.ixProject = @ixProject) ;
DELETE Area
FROM @ToDelete td
WHERE Area.ixArea = td.ix ;
DELETE @ToDelete ;
-- Remove direct project dependencies
DELETE ShipDate
FROM FixFor ff
WHERE (ff.ixProject = @ixProject)
AND (ShipDate.ixFixFor = ff.ixFixFor) ;
DELETE FixFor
WHERE FixFor.ixProject = @ixProject ;
DELETE Mailbox
WHERE ixProject = @ixProject ;
-- Finally, remove the project itself
DELETE Project
WHERE Project.ixProject = @ixProject ;
-- Output success message
SELECT 'Project deleted' Success, @ixProject ixProject, @sProject sProject;
END
ELSE
BEGIN
SELECT 'Project ID does not match project name' Error, p.ixProject, p.sProject FROM Project p WHERE (p.ixProject = @ixProject) OR (p.sProject = @sProject);
END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [DeleteObsoletePersons]
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @ToDelete TABLE (
ixPerson int NOT NULL
PRIMARY KEY
)
-- Find persons who are "deleted" and which have not entered a case, discussion topic or wiki page revision into the system
INSERT INTO @ToDelete
SELECT p.ixPerson
FROM Person p
LEFT JOIN Revision r ON (r.ixPerson = p.ixPerson)
LEFT JOIN BugEvent be ON (be.ixPerson = p.ixPerson)
LEFT JOIN DiscussTopic dt ON (dt.ixPerson = p.ixPerson)
WHERE (r.ixPerson IS NULL)
AND (dt.ixPerson IS NULL)
AND (be.ixPerson IS NULL)
AND (p.fDeleted = 1) ;
-- Select the persons to be deleted as (informative) output
SELECT 'Deleting person' Success, p.ixPerson, p.sFullName
FROM Person p
JOIN @ToDelete td ON p.ixPerson = td.ixPerson ;
--- Remove this persons tokens, views, subscriptions, favorites, drafts, ACLs, snippets, holidays, working schedule, time intervals and search log
DELETE Tokens
FROM @toDelete td
WHERE Tokens.ixPerson = td.ixPerson ;
DELETE BugView
FROM @toDelete td
WHERE BugView.ixPerson = td.ixPerson ;
DELETE ProjectView
FROM @toDelete td
WHERE ProjectView.ixPerson = td.ixPerson ;
DELETE DiscussTopicView
FROM @toDelete td
WHERE DiscussTopicView.ixPerson = td.ixPerson ;
DELETE WikiPageView
FROM @toDelete td
WHERE WikiPageView.ixPerson = td.ixPerson ;
DELETE Subscriptions
FROM @toDelete td
WHERE Subscriptions.ixPerson = td.ixPerson ;
DELETE SearchLog
FROM @toDelete td
WHERE SearchLog.ixPerson = td.ixPerson ;
DELETE DocListItem
FROM DocList dl
JOIN @ToDelete td ON dl.ixPerson = td.ixPerson
WHERE DocListItem.ixDocList = dl.ixDocList ;
DELETE DocList
FROM @ToDelete td
WHERE DocList.ixPerson = td.ixPerson ;
DELETE Snippet
FROM @ToDelete td
WHERE Snippet.ixPerson = td.ixPerson ;
DELETE Draft
FROM @ToDelete td
WHERE Draft.ixPerson = td.ixPerson ;
DELETE ACL
FROM @ToDelete td
WHERE ACL.ixPerson = td.ixPerson ;
DELETE Holiday
FROM @ToDelete td
WHERE Holiday.ixPerson = td.ixPerson ;
DELETE WorkingSchedule
FROM @ToDelete td
WHERE WorkingSchedule.ixPerson = td.ixPerson ;
DELETE TimeInterval
FROM @ToDelete td
WHERE TimeInterval.ixPerson = td.ixPerson ;
DELETE Word
FROM @ToDelete td
WHERE Word.ixPerson = td.ixPerson ;
-- Reassign the area owner to "primary contact"
UPDATE Area
SET ixPersonOwner=-1
FROM @ToDelete td
WHERE td.ixPerson = Area.ixPersonOwner ;
-- Clear out this person from any bugs
UPDATE Bug
SET ixPersonLastEditedBy=-1
FROM @ToDelete td
WHERE td.ixPerson = Bug.ixPersonLastEditedBy ;
UPDATE Bug
SET ixPersonClosedBy=0
FROM @ToDelete td
WHERE td.ixPerson = Bug.ixPersonClosedBy ;
UPDATE Bug
SET ixPersonEstimator=0
FROM @ToDelete td
WHERE td.ixPerson = Bug.ixPersonEstimator ;
UPDATE Bug
SET ixPersonResolvedBy=0
FROM @ToDelete td
WHERE td.ixPerson = Bug.ixPersonResolvedBy ;
UPDATE Bug
SET ixPersonAssignedTo=p.ixPersonOwner
FROM @ToDelete td
JOIN Project p ON p.ixProject = ixProject
WHERE td.ixPerson = Bug.ixPersonAssignedTo ;
-- If this person has deleted discussion topics, re-assign the "CLOSED" user
UPDATE DiscussTopic
SET ixPersonDeletedBy=1
FROM @ToDelete td
WHERE DiscussTopic.ixPersonDeletedBy = td.ixPerson ;
-- Remove any filter where the person is referred (this should be OK since this would yield no result anyways)
DELETE GridColumn
FROM Filter f
JOIN @ToDelete td ON (f.ixPerson = td.ixPerson)
OR (f.ixPersonAssignedTo = td.ixPerson)
OR (f.ixPersonClosedBy = td.ixPerson)
OR (f.ixPersonLastEditedBy = td.ixPerson)
OR (f.ixPersonOpenedBy = td.ixPerson)
OR (f.ixPersonResolvedBy = td.ixPerson)
WHERE GridColumn.ixFilter = f.ixFilter ;
DELETE Filter
FROM @ToDelete td
WHERE (Filter.ixPerson = td.ixPerson)
OR (Filter.ixPersonAssignedTo = td.ixPerson)
OR (Filter.ixPersonClosedBy = td.ixPerson)
OR (Filter.ixPersonLastEditedBy = td.ixPerson)
OR (Filter.ixPersonOpenedBy = td.ixPerson)
OR (Filter.ixPersonResolvedBy = td.ixPerson) ;
-- Remove from mailboxes
UPDATE Mailbox
SET ixPersonOpenedBy=-1
FROM @ToDelete td
WHERE Mailbox.ixPersonOpenedBy = td.ixPerson ;
UPDATE Mailbox
SET ixPersonAssignedTo=-1
FROM @ToDelete td
WHERE Mailbox.ixPersonAssignedTo = td.ixPerson ;
-- Finally, remove the person
DELETE Person
FROM @ToDelete td
WHERE Person.ixPerson = td.ixPerson ;
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [MoveProjectData]
@ixProjectSource int,
@sProjectSource nvarchar(128),
@ixProjectDest int,
@sProjectDest nvarchar(128),
@bCreateProjectArea bit
AS
BEGIN
SET NOCOUNT ON ;
IF (
SELECT COUNT(*)
FROM Project p
WHERE (
(p.ixProject = @ixProjectSource)
AND (p.sProject = @sProjectSource)
)
OR (
(p.ixProject = @ixProjectDest)
AND (p.sProject = @sProjectDest)
)
) = 2
BEGIN
-- The Area map is used to handle the Area transition in cases
DECLARE @ixAreaMap TABLE (
ixAreaOld int NOT NULL
PRIMARY KEY,
ixAreaNew int,
sAreaNew nvarchar(50) NOT NULL
)
-- All Areas used in the project
INSERT @ixAreaMap (ixAreaOld, ixAreaNew, sAreaNew)
SELECT a.ixArea, a.ixArea, CAST(@sProjectSource+' '+a.sArea AS nvarchar(50))
FROM Area a
JOIN (
SELECT DISTINCT
b.ixArea
FROM Bug b
WHERE b.ixProject = @ixProjectSource
) b ON (a.ixArea = b.ixArea) ;
-- Use a project area means to move all cases into an area with the name of the (old) project
IF (@bCreateProjectArea = 1)
BEGIN
DECLARE @ixAreaUniversal int ;
SELECT TOP 1
@ixAreaUniversal=a.ixArea
FROM Area a
WHERE (a.ixProject = @ixProjectDest)
AND (a.sArea = @sProjectSource) ;
IF (@@ROWCOUNT = 0)
BEGIN
INSERT Area (ixProject, fDeleted, nType, cDoc, sArea, ixPersonOwner)
VALUES (@ixProjectDest, 0, 0, 0, @sProjectSource, -1) ;
SET @ixAreaUniversal=SCOPE_IDENTITY() ;
END ;
UPDATE @ixAreaMap
SET ixAreaNew=@ixAreaUniversal,
sAreaNew=@sProjectSource ;
END
ELSE
BEGIN
-- Find existing Areas (by name) in destination project
UPDATE @ixAreaMap
SET ixAreaNew=an.ixArea, sAreaNew=an.sArea
FROM @ixAreaMap iam
JOIN Area ao ON (iam.ixAreaOld = ao.ixArea)
JOIN Area an ON (ao.sArea = an.sArea)
WHERE (an.ixProject = @ixProjectDest);
-- Apply names with project prefix to existing areas, and move them to the destination project
UPDATE Area
SET ixProject=@ixProjectDest,
sArea=iam.sAreaNew
FROM @ixAreaMap iam
WHERE (Area.ixArea = iam.ixAreaNew)
AND (Area.ixProject = @ixProjectSource);
END ;
-- The FixFor map is used to handle the FixFor transition in cases
DECLARE @ixFixForMap TABLE (
ixFixForOld int NOT NULL
PRIMARY KEY,
ixFixForNew int,
sFixForNew nvarchar(255) NOT NULL
)
-- All FixFors which are specific to the project are defaulted to be moved (may be changed afterwards)
INSERT @ixFixForMap (ixFixForOld, ixFixForNew, sFixForNew)
SELECT ff.ixFixFor, CASE WHEN ff.ixProject = @ixProjectSource THEN ff.ixFixFor
END, CAST(@sProjectSource+' '+ff.sFixFor AS nvarchar(255))
FROM FixFor ff
JOIN (
SELECT DISTINCT
b.ixFixFor
FROM Bug b
WHERE b.ixProject = @ixProjectSource
) b ON (ff.ixFixFor = b.ixFixFor) ;
-- Find existing FixFors (by name) in destination project
UPDATE @ixFixForMap
SET ixFixForNew=ff.ixFixFor
FROM FixFor ff
WHERE (ff.ixProject = @ixProjectDest)
AND (ff.sFixFor = sFixForNew) ;
-- Create new FixFors in the destination project for those not matched
INSERT FixFor (sFixFor, dt, bDeleted, ixProject)
SELECT iffm.sFixForNew, ff.dt, ff.bDeleted, @ixProjectDest
FROM @ixFixForMap iffm
JOIN FixFor ff ON (iffm.ixFixForOld = ff.ixFixFor)
WHERE iffm.ixFixForNew IS NULL ;
-- Match and assign the identities of the new FixFors
UPDATE @ixFixForMap
SET ixFixForNew=ff.ixFixFor
FROM FixFor ff
JOIN @ixFixForMap iffm ON (ff.sFixfor = iffm.sFixForNew) AND (iffm.ixFixForNew IS NULL)
WHERE ff.ixProject = @ixProjectDest;
-- Apply names with project prefix to existing FixFors, and move them to the destination project
UPDATE FixFor
SET FixFor.ixProject=@ixProjectDest,
FixFor.sFixFor=iffm.sFixForNew
FROM @ixFixForMap iffm
WHERE (FixFor.ixFixFor = iffm.ixFixForOld)
AND (FixFor.ixProject = @ixProjectSource) ;
-- Move all Bugs to new project, changing their Area and FixFor on the fly
UPDATE Bug
SET ixProject=@ixProjectDest,
ixArea=b.ixAreaNew,
ixFixFor=b.ixFixForNew
FROM (
SELECT b.ixBug, iam.ixAreaNew, iffm.ixFixForNew
FROM Bug b
JOIN @ixAreaMap iam ON (b.ixArea = iam.ixAreaOld)
JOIN @ixFixForMap iffm ON (b.ixFixFor = iffm.ixFixForOld)
) b
WHERE (Bug.ixBug = b.ixBug) ;
-- And as little sugar, also fix any filters relying on the project, fix for or areas
UPDATE Filter
SET ixArea=iam.ixAreaNew
FROM @ixAreaMap iam
WHERE Filter.ixArea = iam.ixAreaOld ;
UPDATE Filter
SET ixFixFor=iffm.ixFixForNew
FROM @ixFixForMap iffm
JOIN FixFor ff ON (ff.ixFixFor = iffm.ixFixForOld)
AND (ff.ixProject = @ixProjectSource)
WHERE Filter.ixFixFor = iffm.ixFixForOld ;
UPDATE Filter
SET ixProject=@ixProjectDest
WHERE ixProject = @ixProjectSource ;
-- Output success message
SELECT 'Project data moved' Success, @ixProjectSource ixProjectSource, @sProjectSource sProjectSource, @ixProjectDest ixProjectDest, @sProjectDest sProjectDest;
END
ELSE
BEGIN
-- Output error message
SELECT 'Project ID does not match project name' Error, p.ixProject, p.sProject
FROM Project p
WHERE (p.ixProject = @ixProjectSource)
OR (p.sProject = @sProjectSource)
OR (p.ixProject = @ixProjectDest)
OR (p.sProject = @sProjectDest) ;
END
END
