bugnet.schema.sqldataprovider.sql

BugNET is an issue tracking and project issue management solution built using the ASP.NET web applic

	) VALUES (
		@ProjectId, 
		@ResolutionName,
		@ResolutionImageUrl,
		@SortOrder
	)
	RETURN SCOPE_IDENTITY()
END
RETURN -1

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[BugNet_Role_DeleteRole]'
GO
CREATE PROCEDURE [dbo].[BugNet_Role_DeleteRole]
	@RoleId Int 
AS
DELETE 
	BugNet_Roles
WHERE
	RoleId = @RoleId
IF @@ROWCOUNT > 0 
	RETURN 0
ELSE
	RETURN 1

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[BugNet_HostSetting_UpdateHostSetting]'
GO

CREATE PROCEDURE [dbo].[BugNet_HostSetting_UpdateHostSetting]
 @SettingName	nvarchar(50),
 @SettingValue 	nvarchar(2000)
AS
UPDATE BugNet_HostSettings SET
	SettingName = @SettingName,
	SettingValue = @SettingValue
WHERE
	SettingName  = @SettingName


GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[BugNet_ProjectPriorities_DeletePriority]'
GO
CREATE PROCEDURE BugNet_ProjectPriorities_DeletePriority
 @PriorityIdToDelete	INT
AS
DELETE 
	BugNet_ProjectPriorities
WHERE
	PriorityId = @PriorityIdToDelete

IF @@ROWCOUNT > 0 
	RETURN 0
ELSE
	RETURN 1
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[BugNet_ProjectPriorities_GetPrioritiesByProjectId]'
GO
CREATE PROCEDURE [dbo].[BugNet_ProjectPriorities_GetPrioritiesByProjectId]
	@ProjectId int
AS
SELECT
	PriorityId,
	ProjectId,
	PriorityName,
	SortOrder,
	PriorityImageUrl
FROM 
	BugNet_ProjectPriorities
WHERE
	ProjectId = @ProjectId
ORDER BY SortOrder
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[BugNet_IssueRevision_DeleteIssueRevisions]'
GO
CREATE PROCEDURE [dbo].[BugNet_IssueRevision_DeleteIssueRevisions] 
  @IssueRevisionId Int
AS
DELETE FROM
	BugNet_IssueRevisions
WHERE
	IssueRevisionId = @IssueRevisionId



GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[BugNet_ProjectPriorities_UpdatePriority]'
GO

CREATE PROCEDURE [dbo].[BugNet_ProjectPriorities_UpdatePriority]
	@ProjectId int,
	@PriorityId int,
	@PriorityName NVARCHAR(50),
	@PriorityImageUrl NVARCHAR(50),
	@SortOrder int
AS

DECLARE @OldSortOrder int
DECLARE @OldPriorityId int

SELECT @OldSortOrder = SortOrder  FROM BugNet_ProjectPriorities WHERE PriorityId = @PriorityId
SELECT @OldPriorityId = PriorityId FROM BugNet_ProjectPriorities WHERE SortOrder = @SortOrder  AND ProjectId = @ProjectId


UPDATE BugNet_ProjectPriorities SET
	ProjectId = @ProjectId,
	PriorityName = @PriorityName,
	PriorityImageUrl = @PriorityImageUrl,
	SortOrder = @SortOrder
WHERE PriorityId = @PriorityId

UPDATE BugNet_ProjectPriorities SET
	SortOrder = @OldSortOrder
WHERE PriorityId = @OldPriorityId


GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[BugNet_RelatedIssue_DeleteChildIssue]'
GO
CREATE PROCEDURE BugNet_RelatedIssue_DeleteChildIssue
	@PrimaryIssueId Int,
	@SecondaryIssueId Int,
	@RelationType Int
AS
DELETE
	BugNet_RelatedIssues
WHERE
	PrimaryIssueId = @PrimaryIssueId
	AND SecondaryIssueId = @SecondaryIssueId
	AND RelationType = @RelationType

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[BugNet_ApplicationLog_GetLog]'
GO
CREATE PROCEDURE [dbo].[BugNet_ApplicationLog_GetLog] 
AS


SELECT L.* FROM BugNet_ApplicationLog L ORDER BY L.Date DESC






GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[BugNet_ProjectMilestones_GetMilestoneById]'
GO

CREATE PROCEDURE [dbo].[BugNet_ProjectMilestones_GetMilestoneById]
 @MilestoneId INT
AS
SELECT
	MilestoneId,
	ProjectId,
	MilestoneName,
	MilestoneImageUrl,
	SortOrder
FROM 
	BugNet_ProjectMilestones
WHERE
	MilestoneId = @MilestoneId



GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[BugNet_Issues]'
GO
CREATE TABLE [dbo].[BugNet_Issues]
(
[IssueId] [int] NOT NULL IDENTITY(1, 1),
[IssueTitle] [nvarchar] (500) NOT NULL,
[IssueDescription] [ntext] NOT NULL,
[IssueStatusId] [int] NOT NULL,
[IssuePriorityId] [int] NOT NULL,
[IssueTypeId] [int] NOT NULL,
[IssueCategoryId] [int] NULL,
[ProjectId] [int] NOT NULL,
[IssueResolutionId] [int] NULL,
[IssueCreatorUserId] [uniqueidentifier] NOT NULL,
[IssueAssignedUserId] [uniqueidentifier] NULL,
[IssueOwnerUserId] [uniqueidentifier] NULL,
[IssueDueDate] [datetime] NULL CONSTRAINT [DF_BugNet_Issues_DueDate] DEFAULT ('1/1/1900 12:00:00 AM'),
[IssueMilestoneId] [int] NULL,
[IssueVisibility] [int] NOT NULL,
[IssueEstimation] [decimal] (5, 2) NOT NULL CONSTRAINT [DF_BugNet_Issues_Estimation] DEFAULT ((0)),
[IssueProgress] [int] NOT NULL CONSTRAINT [DF_BugNet_Issues_IssueProgress] DEFAULT ((0)),
[DateCreated] [datetime] NOT NULL CONSTRAINT [DF_BugNet_Issues_DateCreated] DEFAULT (getdate()),
[LastUpdate] [datetime] NOT NULL,
[LastUpdateUserId] [uniqueidentifier] NOT NULL,
[Disabled] [bit] NOT NULL CONSTRAINT [DF_BugNet_Issues_Disabled] DEFAULT ((0))
)

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_BugNet_Issues] on [dbo].[BugNet_Issues]'
GO
ALTER TABLE [dbo].[BugNet_Issues] ADD CONSTRAINT [PK_BugNet_Issues] PRIMARY KEY CLUSTERED  ([IssueId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[BugNet_Issue_UpdateIssue]'
GO
CREATE PROCEDURE [dbo].[BugNet_Issue_UpdateIssue]
  @IssueId Int,
  @IssueTitle nvarchar(500),
  @IssueDescription ntext,
  @ProjectId Int,
  @IssueCategoryId Int,
  @IssueStatusId Int,
  @IssuePriorityId Int,
  @IssueMilestoneId Int,
  @IssueTypeId Int,
  @IssueResolutionId Int,
  @IssueAssignedUserName NVarChar(255),
  @IssueCreatorUserName NVarChar(255),
  @IssueOwnerUserName NVarChar(255),
  @IssueDueDate datetime,
  @IssueVisibility int,
  @IssueEstimation decimal(5,2),
  @IssueProgress int
AS

DECLARE @IssueAssignedUserId	UNIQUEIDENTIFIER
DECLARE @IssueCreatorUserId		UNIQUEIDENTIFIER
DECLARE @IssueOwnerUserId		UNIQUEIDENTIFIER

SELECT @IssueAssignedUserId = UserId FROM aspnet_users WHERE UserName = @IssueAssignedUserName
SELECT @IssueCreatorUserId = UserId FROM aspnet_users WHERE UserName = @IssueCreatorUserName
SELECT @IssueOwnerUserId = UserId FROM aspnet_users WHERE UserName = @IssueOwnerUserName

BEGIN TRAN
	UPDATE BugNet_Issues SET
		IssueTitle = @IssueTitle,
		IssueCategoryId = @IssueCategoryId,
		ProjectId = @ProjectId,
		IssueStatusId = @IssueStatusId,
		IssuePriorityId = @IssuePriorityId,
		IssueMilestoneId = @IssueMilestoneId,
		IssueAssignedUserId = @IssueAssignedUserId,
		IssueOwnerUserId = @IssueOwnerUserId,
		IssueTypeId = @IssueTypeId,
		IssueResolutionId = @IssueResolutionId,
		IssueDueDate = @IssueDueDate,
		IssueVisibility = @IssueVisibility,
		IssueEstimation = @IssueEstimation,
		IssueProgress = @IssueProgress
	WHERE 
		IssueId = @IssueId
	
	/*EXEC BugNet_IssueHistory_CreateNewHistory @IssueId, @IssueCreatorId*/
COMMIT TRAN

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[BugNet_IssueComment_CreateNewIssueComment]'
GO
CREATE PROCEDURE [dbo].[BugNet_IssueComment_CreateNewIssueComment]
	@IssueId int,
	@CreatorUserName NVarChar(255),
	@Comment ntext
AS
-- Get Last Update UserID
DECLARE @UserId uniqueidentifier
SELECT @UserId = UserId FROM aspnet_users WHERE Username = @CreatorUserName
INSERT BugNet_IssueComments
(
	IssueId,
	UserId,
	DateCreated,
	Comment
) 
VALUES 
(
	@IssueId,
	@UserId,
	GetDate(),
	@Comment
)

/* Update the LastUpdate fields of this bug*/
UPDATE BugNet_Issues SET LastUpdate = GetDate(),LastUpdateUserId = @UserId WHERE IssueId = @IssueId

RETURN scope_identity()

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[BugNet_Projects]'
GO
CREATE TABLE [dbo].[BugNet_Projects]
(
[ProjectId] [int] NOT NULL IDENTITY(1, 1),
[ProjectName] [nvarchar] (50) NOT NULL,
[ProjectCode] [nvarchar] (3) NOT NULL,
[ProjectDescription] [nvarchar] (1000) NULL,
[AttachmentUploadPath] [nvarchar] (256) NOT NULL,
[DateCreated] [datetime] NOT NULL CONSTRAINT [DF_BugNet_Projects_DateCreated] DEFAULT (getdate()),
[ProjectDisabled] [bit] NOT NULL CONSTRAINT [DF_BugNet_Projects_Active] DEFAULT ((0)),
[ProjectAccessType] [int] NOT NULL,
[ProjectManagerUserId] [uniqueidentifier] NOT NULL,
[ProjectCreatorUserId] [uniqueidentifier] NOT NULL,
[AllowAttachments] [bit] NOT NULL CONSTRAINT [DF_BugNet_Projects_AllowAttachments] DEFAULT ((1)),
[AttachmentStorageType] [int] NULL,
[SvnRepositoryUrl] [nvarchar] (255) NULL
)

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_BugNet_Projects] on [dbo].[BugNet_Projects]'
GO
ALTER TABLE [dbo].[BugNet_Projects] ADD CONSTRAINT [PK_BugNet_Projects] PRIMARY KEY CLUSTERED  ([ProjectId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[BugNet_Project_GetProjectsByMemberUsername]'
GO
CREATE PROCEDURE [dbo].[BugNet_Project_GetProjectsByMemberUsername]
	@Username nvarchar(255),
	@ActiveOnly bit
AS
DECLARE @Disabled bit
SET @Disabled = 1
DECLARE @UserId UNIQUEIDENTIFIER
SELECT @UserId = UserId FROM aspnet_users WHERE Username = @Username
IF @ActiveOnly = 1
BEGIN
	SET @Disabled = 0
END
SELECT DISTINCT
	P.ProjectId,
	ProjectName,
	ProjectCode,
	ProjectDescription,
	AttachmentUploadPath,
	ProjectManagerUserId,
	ProjectCreatorUserId,
	P.DateCreated,
	P.ProjectDisabled,
	ProjectAccessType,
	Managers.Username ManagerDisplayName,
	Creators.Username CreatorDisplayName,
	AllowAttachments,
	AttachmentStorageType,
	SvnRepositoryUrl
FROM 
	BugNet_Projects P
	INNER JOIN aspnet_users AS Managers ON Managers.UserId = P.ProjectManagerUserId	
	INNER JOIN aspnet_users AS Creators ON Creators.UserId = P.ProjectCreatorUserId
	Left JOIN BugNet_UserProjects UP ON UP.ProjectId = P.ProjectId
WHERE
	 (P.ProjectAccessType = 1 AND P.ProjectDisabled = @Disabled) OR
     (P.ProjectAccessType = 2 AND P.ProjectDisabled = @Disabled AND UP.UserId = @UserId)
              
ORDER BY ProjectName ASC



GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[BugNet_ProjectCategories]'
GO
CREATE TABLE [dbo].[BugNet_ProjectCategories]
(
[CategoryId] [int] NOT NULL IDENTITY(1, 1),
[CategoryName] [nvarchar] (100) NOT NULL,
[ProjectId] [int] NOT NULL,
[ParentCategoryId] [int] NOT NULL CONSTRAINT [DF_BugNet_ProjectCategories_ParentCategoryId] DEFAULT ((0))
)

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_BugNet_ProjectCategories] on [dbo].[BugNet_ProjectCategories]'
GO
ALTER TABLE [dbo].[BugNet_ProjectCategories] ADD CONSTRAINT [PK_BugNet_ProjectCategories] PRIMARY KEY CLUSTERED  ([CategoryId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[BugNet_IssuesView]'
GO
CREATE VIEW dbo.BugNet_IssuesView
AS
SELECT     dbo.BugNet_Issues.IssueId, dbo.BugNet_Issues.IssueTitle, dbo.BugNet_Issues.IssueDescription, dbo.BugNet_Issues.IssueStatusId, 
                      dbo.BugNet_Issues.IssuePriorityId, dbo.BugNet_Issues.IssueTypeId, dbo.BugNet_Issues.IssueCategoryId, dbo.BugNet_Issues.ProjectId, 
                      dbo.BugNet_Issues.IssueResolutionId, dbo.BugNet_Issues.IssueCreatorUserId, dbo.BugNet_Issues.IssueAssignedUserId, dbo.BugNet_Issues.IssueOwnerUserId, 
                      dbo.BugNet_Issues.IssueDueDate, dbo.BugNet_Issues.IssueMilestoneId, dbo.BugNet_Issues.IssueVisibility, dbo.BugNet_Issues.IssueEstimation, 
                      dbo.BugNet_Issues.DateCreated, dbo.BugNet_Issues.LastUpdate, dbo.BugNet_Issues.LastUpdateUserId, dbo.BugNet_Projects.ProjectName, 
                      dbo.BugNet_Projects.ProjectCode, dbo.BugNet_ProjectPriorities.PriorityName, dbo.BugNet_ProjectIssueTypes.IssueTypeName, 
                      dbo.BugNet_ProjectCategories.CategoryName, dbo.BugNet_ProjectStatus.StatusName, ISNULL(dbo.BugNet_ProjectMilestones.MilestoneName, N'none') 
                      AS MilestoneName, ISNULL(dbo.BugNet_ProjectResolutions.ResolutionName, 'none') AS ResolutionName, LastUpdateUsers.UserName AS LastUpdateUserName, 
                      ISNULL(AssignedUsers.UserName, N'none') AS AssignedUsername, ISNULL(AssignedUsersProfile.DisplayName, N'none') AS AssignedDispl