bugnet.schema.sqldataprovider.sql

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

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Creating [dbo].[BugNet_ProjectStatus]'
GO
CREATE TABLE [dbo].[BugNet_ProjectStatus]
(
[StatusId] [int] NOT NULL IDENTITY(1, 1),
[ProjectId] [int] NOT NULL,
[StatusName] [nvarchar] (50) NOT NULL,
[StatusImageUrl] [nvarchar] (50) NOT NULL,
[SortOrder] [int] NOT NULL,
[IsClosedState] [bit] NOT 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_ProjectStatus] on [dbo].[BugNet_ProjectStatus]'
GO
ALTER TABLE [dbo].[BugNet_ProjectStatus] ADD CONSTRAINT [PK_BugNet_ProjectStatus] PRIMARY KEY CLUSTERED  ([StatusId])
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_ProjectStatus_DeleteStatus]'
GO

CREATE PROCEDURE [dbo].[BugNet_ProjectStatus_DeleteStatus]
 	@StatusIdToDelete INT
AS
DELETE
	BugNet_ProjectStatus
WHERE
	StatusId = @StatusIdToDelete

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_ProjectResolutions]'
GO
CREATE TABLE [dbo].[BugNet_ProjectResolutions]
(
[ResolutionId] [int] NOT NULL IDENTITY(1, 1),
[ProjectId] [int] NOT NULL,
[ResolutionName] [nvarchar] (50) NOT NULL,
[ResolutionImageUrl] [nvarchar] (50) NOT NULL,
[SortOrder] [int] NOT 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_ProjectResolutions] on [dbo].[BugNet_ProjectResolutions]'
GO
ALTER TABLE [dbo].[BugNet_ProjectResolutions] ADD CONSTRAINT [PK_BugNet_ProjectResolutions] PRIMARY KEY CLUSTERED  ([ResolutionId])
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_ProjectResolutions_DeleteResolution]'
GO

CREATE PROCEDURE [dbo].[BugNet_ProjectResolutions_DeleteResolution]
 	@ResolutionIdToDelete INT
AS
DELETE
	BugNet_ProjectResolutions
WHERE
	ResolutionId = @ResolutionIdToDelete

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]'
GO
CREATE TABLE [dbo].[BugNet_ProjectPriorities]
(
[PriorityId] [int] NOT NULL IDENTITY(1, 1),
[ProjectId] [int] NOT NULL,
[PriorityName] [nvarchar] (50) NOT NULL,
[PriorityImageUrl] [nvarchar] (50) NOT NULL,
[SortOrder] [int] NOT 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_ProjectPriorities] on [dbo].[BugNet_ProjectPriorities]'
GO
ALTER TABLE [dbo].[BugNet_ProjectPriorities] ADD CONSTRAINT [PK_BugNet_ProjectPriorities] PRIMARY KEY CLUSTERED  ([PriorityId])
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_GetPriorityById]'
GO
CREATE PROCEDURE [dbo].[BugNet_ProjectPriorities_GetPriorityById]
	@PriorityId int
AS
SELECT
	PriorityId,
	ProjectId,
	PriorityName,
	SortOrder,
	PriorityImageUrl
FROM 
	BugNet_ProjectPriorities
WHERE
	PriorityId = @PriorityId



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]'
GO
CREATE TABLE [dbo].[BugNet_ProjectMilestones]
(
[MilestoneId] [int] NOT NULL IDENTITY(1, 1),
[ProjectId] [int] NOT NULL,
[MilestoneName] [nvarchar] (50) NOT NULL,
[MilestoneImageUrl] [nvarchar] (50) NOT NULL,
[SortOrder] [int] NOT NULL,
[DateCreated] [datetime] NOT NULL CONSTRAINT [DF_BugNet_ProjectMilestones_CreateDate] DEFAULT (getdate())
)

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_ProjectMilestones] on [dbo].[BugNet_ProjectMilestones]'
GO
ALTER TABLE [dbo].[BugNet_ProjectMilestones] ADD CONSTRAINT [PK_BugNet_ProjectMilestones] PRIMARY KEY CLUSTERED  ([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_IssueRevisions]'
GO
CREATE TABLE [dbo].[BugNet_IssueRevisions]
(
[IssueRevisionId] [int] NOT NULL IDENTITY(1, 1),
[Revision] [int] NOT NULL,
[IssueId] [int] NOT NULL,
[Repository] [nvarchar] (400) NOT NULL,
[RevisionAuthor] [nvarchar] (100) NOT NULL,
[RevisionDate] [nvarchar] (100) NOT NULL,
[RevisionMessage] [ntext] NOT NULL,
[DateCreated] [datetime] NOT 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_IssueRevisions] on [dbo].[BugNet_IssueRevisions]'
GO
ALTER TABLE [dbo].[BugNet_IssueRevisions] ADD CONSTRAINT [PK_BugNet_IssueRevisions] PRIMARY KEY CLUSTERED  ([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_IssueRevision_GetIssueRevisionsByIssueId]'
GO
CREATE PROCEDURE [dbo].[BugNet_IssueRevision_GetIssueRevisionsByIssueId] 
  @IssueId Int
AS
SELECT 
	*
FROM 
	BugNet_IssueRevisions
WHERE
	IssueId = @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_Roles]'
GO
CREATE TABLE [dbo].[BugNet_Roles]
(
[RoleId] [int] NOT NULL IDENTITY(1, 1),
[ProjectId] [int] NULL,
[RoleName] [nvarchar] (256) NOT NULL,
[RoleDescription] [nvarchar] (256) NOT NULL,
[AutoAssign] [bit] NOT 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_Roles] on [dbo].[BugNet_Roles]'
GO
ALTER TABLE [dbo].[BugNet_Roles] ADD CONSTRAINT [PK_BugNet_Roles] PRIMARY KEY CLUSTERED  ([RoleId])
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_UserRoles]'
GO
CREATE TABLE [dbo].[BugNet_UserRoles]
(
[UserId] [uniqueidentifier] NOT NULL,
[RoleId] [int] NOT 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_UserRoles] on [dbo].[BugNet_UserRoles]'
GO
ALTER TABLE [dbo].[BugNet_UserRoles] ADD CONSTRAINT [PK_BugNet_UserRoles] PRIMARY KEY CLUSTERED  ([UserId], [RoleId])
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_ProjectStatus_CreateNewStatus]'
GO

CREATE PROCEDURE [dbo].[BugNet_ProjectStatus_CreateNewStatus]
 	@ProjectId INT,
	@StatusName NVARCHAR(50),
	@StatusImageUrl NVARCHAR(50),
	@IsClosedState bit
AS
IF NOT EXISTS(SELECT StatusId  FROM BugNet_ProjectStatus WHERE LOWER(StatusName)= LOWER(@StatusName) AND ProjectId = @ProjectId)
BEGIN
	DECLARE @SortOrder int
	SELECT @SortOrder = ISNULL(MAX(SortOrder + 1),1) FROM BugNet_ProjectStatus WHERE ProjectId = @ProjectId
	INSERT BugNet_ProjectStatus
	(
		ProjectId, 
		StatusName ,
		StatusImageUrl,
		SortOrder,
		IsClosedState
	) VALUES (
		@ProjectId, 
		@StatusName,
		@StatusImageUrl,
		@SortOrder,
		@IsClosedState
	)
	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_RelatedIssues]'
GO
CREATE TABLE [dbo].[BugNet_RelatedIssues]
(
[PrimaryIssueId] [int] NOT NULL,
[SecondaryIssueId] [int] NOT NULL,
[RelationType] [int] NOT 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 [dbo].[BugNet_RelatedIssue_DeleteParentIssue]'
GO
CREATE PROCEDURE  BugNet_RelatedIssue_DeleteParentIssue
	@PrimaryIssueId Int,
	@SecondaryIssueId Int,
	@RelationType Int
AS
DELETE
	BugNet_RelatedIssues
WHERE
	PrimaryIssueId = @SecondaryIssueId
	AND SecondaryIssueId = @PrimaryIssueId
	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_RelatedIssue_CreateNewChildIssue]'
GO
CREATE PROCEDURE BugNet_RelatedIssue_CreateNewChildIssue 
	@PrimaryIssueId Int,
	@SecondaryIssueId Int,
	@RelationType Int
AS
IF NOT EXISTS(SELECT PrimaryIssueId FROM BugNet_RelatedIssues WHERE PrimaryIssueId = @PrimaryIssueId AND SecondaryIssueId = @SecondaryIssueId AND RelationType = @RelationType)
BEGIN
	INSERT BugNet_RelatedIssues
	(
		PrimaryIssueId,
		SecondaryIssueId,
		RelationType
	)
	VALUES
	(
		@PrimaryIssueId,
		@SecondaryIssueId,
		@RelationType
	)
END

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_IssueAttachments]'
GO
CREATE TABLE [dbo].[BugNet_IssueAttachments]
(
[IssueAttachmentId] [int] NOT NULL IDENTITY(1, 1),
[IssueId] [int] NOT NULL,
[FileName] [nvarchar] (250) NOT NULL,
[Description] [nvarchar] (80) NOT NULL,
[FileSize] [int] NOT NULL,
[ContentType] [nvarchar] (50) NOT NULL,
[DateCreated] [datetime] NOT NULL CONSTRAINT [DF_BugNet_IssueAttachments_DateCreated] DEFAULT (getdate()),
[UserId] [uniqueidentifier] NOT NULL,
[Attachment] [image] 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_IssueAttachments] on [dbo].[BugNet_IssueAttachments]'
GO
ALTER TABLE [dbo].[BugNet_IssueAttachments] ADD CONSTRAINT [PK_BugNet_IssueAttachments] PRIMARY KEY CLUSTERED  ([IssueAttachmentId])
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_RolePermissions]'
GO
CREATE TABLE [dbo].[BugNet_RolePermissions]
(
[PermissionId] [int] NOT NULL,
[RoleId] [int] NOT 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_RolePermissions] on [dbo].[BugNet_RolePermissions]'
GO
ALTER TABLE [dbo].[BugNet_RolePermissions] ADD CONSTRAINT [PK_BugNet_RolePermissions] PRIMARY KEY CLUSTERED  ([RoleId], [PermissionId])
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_IssueNotifications]'
GO
CREATE TABLE [dbo].[BugNet_IssueNotifications]
(
[IssueNotificationId] [int] NOT NULL IDENTITY(1, 1),
[IssueId] [int] NOT NULL,
[UserId] [uniqueidentifier] NOT 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_IssueNotifications] on [dbo].[BugNet_IssueNotifications]'
GO
ALTER TABLE [dbo].[BugNet_IssueNotifications] ADD CONSTRAINT [PK_BugNet_IssueNotifications] PRIMARY KEY CLUSTERED  ([IssueNotificationId])
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_Permissions]'
GO
CREATE TABLE [dbo].[BugNet_Permissions]
(
[PermissionId] [int] NOT NULL,
[PermissionKey] [nvarchar] (50) NOT NULL,
[PermissionName] [nvarchar] (50) NOT 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_Permissions] on [dbo].[BugNet_Permissions]'
GO
ALTER TABLE [dbo].[BugNet_Permissions] ADD CONSTRAINT [PK_BugNet_Permissions] PRIMARY KEY CLUSTERED  ([PermissionId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO