Purge Artifact API does not see to work

[Andrew Sumner]

I'm writing some SQL to purge older rejected builds and artifacts. AdminEdit_PurgeBuild cleans up the database nicely but leaves the artifacts behind so I thought that AdminEdit_PurgeArtifact would be the solution however that deleted the records from the database and left the artifacts behind.

Are there any SQL api calls that will delete the artifacts from the file system?

DECLARE build_cursor CURSOR FOR
	SELECT B.Application_Id, B.Release_Number, B.Build_Number
	FROM dbo.Builds B
	WHERE B.BuildStatus_Name = 'Rejected'
	AND B.Rejected_Date < GETDATE() - 30

OPEN build_cursor

DECLARE @RC int
DECLARE @Application_Id int
DECLARE @Release_Number RELEASENUMBER
DECLARE @Build_Number BUILDNUMBER
DECLARE @Artifact_Name varchar(50)
DECLARE @rows int = 0

FETCH NEXT FROM build_cursor INTO @Application_Id, @Release_Number, @Build_Number

WHILE (@@FETCH_STATUS <> -1)
BEGIN
	DECLARE artifact_cursor CURSOR FOR
		SELECT Artifact_Name
		FROM BuildArtifacts A
		WHERE A.Application_Id = @Application_Id
		AND A.Release_Number = @Release_Number
		AND A.Build_Number = @Build_Number

	OPEN artifact_cursor

	FETCH NEXT FROM artifact_cursor INTO @Artifact_Name

	WHILE (@@FETCH_STATUS <> -1)
	BEGIN
		PRINT 'Purging Artifacts for Application_Id=' + CAST(@Application_Id as varchar) + ', Release_Number=' + @Release_Number + ', Build_Number=' + @Build_Number + ', Artifact_Name=' + @Artifact_Name 
		EXECUTE @RC = [dbo].[AdminEdit_PurgeArtifact] @Application_Id, @Release_Number, @Build_Number, @Artifact_Name

		FETCH NEXT FROM artifact_cursor INTO @Artifact_Name
	END

	CLOSE artifact_cursor
	DEALLOCATE artifact_cursor

	PRINT 'Purging Application_Id=' + CAST(@Application_Id as varchar) + ', Release_Number=' + @Release_Number + ', Build_Number=' + @Build_Number
	SET @rows = @rows + 1
	--EXECUTE @RC = [dbo].[AdminEdit_PurgeBuild] @Application_Id, @Release_Number, @Build_Number

	FETCH NEXT FROM build_cursor INTO @Application_Id, @Release_Number, @Build_Number
END

IF @rows = 0
	PRINT 'No rejected builds older that 30 days have been found.'
ELSE
	PRINT CAST(@rows as varchar) + ' rejected builds older that 30 days have been purged.'

CLOSE build_cursor
DEALLOCATE build_cursor
GO

Product: BuildMaster
Version: 4.5.6

[Andrew Sumner]

On looking closer at the SQL API Stored Procedure it became obvious that unless there is a trigger I'm not aware of then this API is never going to touch the file system. This is my work around unless you have a better suggestion:

/*
Purge all artifacts from the file system, and data from the database for all rejected builds older that 30 days
*/

DECLARE build_cursor CURSOR FOR
	SELECT B.Application_Id, B.Release_Number, B.Build_Number, (SELECT count(*) FROM dbo.BuildArtifacts A WHERE A.Application_Id = B.Application_Id AND A.Release_Number = B.Release_Number AND A.Build_Number = B.Build_Number ) as Artifact_Count
	FROM dbo.Builds B
	WHERE B.BuildStatus_Name = 'Rejected'
	AND B.Rejected_Date < GETDATE() - 30
	--AND B.Application_Id = 30
	--AND B.Build_Number = '213'

OPEN build_cursor

DECLARE @RC int
DECLARE @Application_Id int
DECLARE @Release_Number RELEASENUMBER
DECLARE @Build_Number BUILDNUMBER
DECLARE @Artifact_Count int
DECLARE @rows int = 0
DECLARE @path varchar(8000)
DECLARE @cmd varchar(8000)

FETCH NEXT FROM build_cursor INTO @Application_Id, @Release_Number, @Build_Number, @Artifact_Count

WHILE (@@FETCH_STATUS <> -1)
BEGIN
	IF @Artifact_Count > 0
	BEGIN
	    SET @path = 'C:\BuildMaster\Artifacts\' + cast(@Application_Id as varchar) + '\' + @Release_Number + '\'
		PRINT 'Purging Artifacts from ' + @path + @Build_Number

		-- Delete any build related artifacts
		SET @cmd = 'rd /s /q ' + @path + @Build_Number
		exec xp_cmdshell @cmd, no_output
		
		-- First time I did this the release folder wasn't deleted and I thought it might be a timing issue.  Keep an eye on it and uncomment this line if needed.
		--WAITFOR DELAY '00:00:02'; --accept value hh:mm:ss

		-- Remove the release folder if it is empty
		SET @cmd = 'rd ' + @path
		exec xp_cmdshell @cmd, no_output
	END

	PRINT 'Purging Application_Id=' + CAST(@Application_Id as varchar) + ', Release_Number=' + @Release_Number + ', Build_Number=' + @Build_Number
	SET @rows = @rows + 1
	EXECUTE @RC = [dbo].[AdminEdit_PurgeBuild] @Application_Id, @Release_Number, @Build_Number

	FETCH NEXT FROM build_cursor INTO @Application_Id, @Release_Number, @Build_Number, @Artifact_Count
END

IF @rows = 0
	PRINT 'No rejected builds older that 30 days have been found.'
ELSE
	PRINT CAST(@rows as varchar) + ' rejected builds older that 30 days have been purged.'

CLOSE build_cursor
DEALLOCATE build_cursor
GO

[Dean]

I'll have to check with the dev team, but I think that was never intended to be part of the public API, since as you discovered, it takes a little application logic to clean up the actual artifact files as well. We originally had plans to add something to the service that would occasionally scan for orphaned artifacts on disk, but those were postponed.

Your workaround should be safe enough, but it's not something we can add, as we can't rely on the database being on a server that can access the artifact library, or even that it's running as a user account with sufficient privileges.

At some point, we'll likely reevaluate/extend our SOAP/JSON API's and allow them to do higher-level tasks like this, but there's no immediate plans for this.