0.7.719.0.sqldataprovider.sql

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

([BugAttachmentID], [BugID], [FileName], [Description], [FileSize], [Type], [UploadedDate],[UploadedUserId]) 
SELECT [BugAttachmentID], [BugID], [FileName], [Description], [FileSize], [Type], [UploadedDate] , 
(SELECT aspnet_users.UserId From aspnet_users join Users on BugAttachment.UploadedUser = Users.UserId where Users.Username = aspnet_users.username )
FROM [dbo].[BugAttachment]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
SET IDENTITY_INSERT [dbo].[tmp_sc_BugAttachment] OFF
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO

if exists (select * from sysobjects where id=object_id('[dbo].[BugAttachment]') and OBJECTPROPERTY(id, 'IsUserTable')=1)
  drop table [dbo].[BugAttachment]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO


sp_rename 'dbo.tmp_sc_BugAttachment', 'BugAttachment'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO


ALTER TABLE [dbo].[BugAttachment] WITH NOCHECK ADD  CONSTRAINT [PK__BugAttachment__1273C1CD] PRIMARY KEY CLUSTERED ([BugAttachmentID]) 
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO


-- Add foreign key constraints

ALTER TABLE [dbo].[BugAttachment] WITH NOCHECK ADD CONSTRAINT [FK_BugAttachment_Bug] FOREIGN KEY ([BugID])  REFERENCES [dbo].[Bug] ([BugID]) 
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
ALTER TABLE [dbo].[BugAttachment] CHECK CONSTRAINT [FK_BugAttachment_Bug]
GO

/* Bug Comment Table */ 

-- Remove foreign key constraints

ALTER TABLE [dbo].[BugComment] DROP CONSTRAINT [FK_BugComment_Bug]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO

--
-- Script for dbo.BugComment
-- Foreign keys etc. will appear at the end
--

PRINT 'Updating dbo.BugComment'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
CREATE TABLE [dbo].[tmp_sc_BugComment]
    (
  [BugCommentID] [int] NOT NULL IDENTITY (1, 1) ,
  [BugID] [int] NOT NULL ,
  [CreatedDate] [datetime] NOT NULL ,
  [Comment] [ntext] NOT NULL ,
  [CreatedUserId] [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
SET IDENTITY_INSERT [dbo].[tmp_sc_BugComment] ON
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
INSERT INTO [dbo].[tmp_sc_BugComment] ([BugCommentID], [BugID], [CreatedDate], [Comment],[CreatedUserId]) 
SELECT [BugCommentID], [BugID], [CreatedDate], [Comment],(SELECT aspnet_users.UserId From aspnet_users join Users on BugComment.UserId = Users.UserId where Users.Username = aspnet_users.username )
 FROM [dbo].[BugComment]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
SET IDENTITY_INSERT [dbo].[tmp_sc_BugComment] OFF
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO

if exists (select * from sysobjects where id=object_id('[dbo].[BugComment]') and OBJECTPROPERTY(id, 'IsUserTable')=1)
  drop table [dbo].[BugComment]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO


sp_rename 'dbo.tmp_sc_BugComment', 'BugComment'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO


ALTER TABLE [dbo].[BugComment] WITH NOCHECK ADD  CONSTRAINT [PK__BugComment__164452B1] PRIMARY KEY CLUSTERED ([BugCommentID]) 
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO


-- Add foreign key constraints

ALTER TABLE [dbo].[BugComment] WITH NOCHECK ADD CONSTRAINT [FK_BugComment_Bug] FOREIGN KEY ([BugID])  REFERENCES [dbo].[Bug] ([BugID]) 
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
ALTER TABLE [dbo].[BugComment] CHECK CONSTRAINT [FK_BugComment_Bug]
GO

/* Bug History Table */
-- Remove foreign key constraints

ALTER TABLE [dbo].[BugHistory] DROP CONSTRAINT [FK_BugHistory_Bug]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO

--
-- Script for dbo.BugHistory
-- Foreign keys etc. will appear at the end
--

PRINT 'Updating dbo.BugHistory'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
CREATE TABLE [dbo].[tmp_sc_BugHistory]
    (
  [BugHistoryID] [int] NOT NULL IDENTITY (1, 1) ,
  [BugID] [int] NOT NULL ,
  [FieldChanged] [nvarchar] (50) NOT NULL ,
  [OldValue] [nvarchar] (50) NOT NULL ,
  [NewValue] [nvarchar] (50) NOT NULL ,
  [CreatedDate] [datetime] NOT NULL ,
  [CreatedUserId] [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
SET IDENTITY_INSERT [dbo].[tmp_sc_BugHistory] ON
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
INSERT INTO [dbo].[tmp_sc_BugHistory] ([BugHistoryID], [BugID], [FieldChanged], [OldValue], [NewValue], [CreatedDate],[CreatedUserId]) 
SELECT [BugHistoryID], [BugID], [FieldChanged], [OldValue], [NewValue], [CreatedDate],
(SELECT aspnet_users.UserId From aspnet_users join Users on BugHistory.UserId = Users.UserId where Users.Username = aspnet_users.username )
FROM [dbo].[BugHistory]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
SET IDENTITY_INSERT [dbo].[tmp_sc_BugHistory] OFF
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO

if exists (select * from sysobjects where id=object_id('[dbo].[BugHistory]') and OBJECTPROPERTY(id, 'IsUserTable')=1)
  drop table [dbo].[BugHistory]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO


sp_rename 'dbo.tmp_sc_BugHistory', 'BugHistory'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO


ALTER TABLE [dbo].[BugHistory] WITH NOCHECK ADD  CONSTRAINT [PK__BugHistory__182C9B23] PRIMARY KEY CLUSTERED ([BugHistoryID]) 
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO


-- Add foreign key constraints

ALTER TABLE [dbo].[BugHistory] WITH NOCHECK ADD CONSTRAINT [FK_BugHistory_Bug] FOREIGN KEY ([BugID])  REFERENCES [dbo].[Bug] ([BugID]) 
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
ALTER TABLE [dbo].[BugHistory] CHECK CONSTRAINT [FK_BugHistory_Bug]
GO


/* Bug Notfication Table */
-- Remove foreign key constraints

ALTER TABLE [dbo].[BugNotification] DROP CONSTRAINT [FK_BugNotification_Bug]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO

--
-- Script for dbo.BugNotification
-- Foreign keys etc. will appear at the end
--

PRINT 'Updating dbo.BugNotification'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
CREATE TABLE [dbo].[tmp_sc_BugNotification]
    (
  [BugNotificationID] [int] NOT NULL IDENTITY (1, 1) ,
  [BugID] [int] NOT NULL ,
  [CreatedUserId] [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
SET IDENTITY_INSERT [dbo].[tmp_sc_BugNotification] ON
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
INSERT INTO [dbo].[tmp_sc_BugNotification] ([BugNotificationID], [BugID],[CreatedUserId]) SELECT [BugNotificationID], [BugID],
(SELECT aspnet_users.UserId From aspnet_users join Users on BugNotification.UserId = Users.UserId where Users.Username = aspnet_users.username )
FROM [dbo].[BugNotification]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
SET IDENTITY_INSERT [dbo].[tmp_sc_BugNotification] OFF
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO

if exists (select * from sysobjects where id=object_id('[dbo].[BugNotification]') and OBJECTPROPERTY(id, 'IsUserTable')=1)
  drop table [dbo].[BugNotification]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO


sp_rename 'dbo.tmp_sc_BugNotification', 'BugNotification'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO


ALTER TABLE [dbo].[BugNotification] WITH NOCHECK ADD  CONSTRAINT [PK_BugNotification] PRIMARY KEY CLUSTERED ([BugNotificationID]) 
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO


-- Add foreign key constraints

ALTER TABLE [dbo].[BugNotification] WITH NOCHECK ADD CONSTRAINT [FK_BugNotification_Bug] FOREIGN KEY ([BugID])  REFERENCES [dbo].[Bug] ([BugID]) 
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
ALTER TABLE [dbo].[BugNotification] CHECK CONSTRAINT [FK_BugNotification_Bug]
GO

/* Bug Time Entry Table */
-- Remove foreign key constraints

ALTER TABLE [dbo].[BugTimeEntry] DROP CONSTRAINT [FK_BugTimeEntry_Bug]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO

--
-- Script for dbo.BugTimeEntry
-- Foreign keys etc. will appear at the end
--

PRINT 'Updating dbo.BugTimeEntry'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
CREATE TABLE [dbo].[tmp_sc_BugTimeEntry]
    (
  [BugTimeEntryId] [int] NOT NULL IDENTITY (1, 1) ,
  [BugId] [int] NOT NULL ,
  [WorkDate] [datetime] NOT NULL ,
  [Duration] [decimal] (4, 2) NOT NULL ,
  [BugCommentId] [int] NOT NULL ,
  [CreatedUserId] [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
SET IDENTITY_INSERT [dbo].[tmp_sc_BugTimeEntry] ON
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
INSERT INTO [dbo].[tmp_sc_BugTimeEntry] ([BugTimeEntryId], [BugId], [WorkDate], [Duration], [BugCommentId],[CreatedUserId])
SELECT [BugTimeEntryId], [BugId], [WorkDate], [Duration], [BugCommentId],
(SELECT aspnet_users.UserId From aspnet_users join Users on BugTimeEntry.UserId = Users.UserId where Users.Username = aspnet_users.username )
FROM [dbo].[BugTimeEntry]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
SET IDENTITY_INSERT [dbo].[tmp_sc_BugTimeEntry] OFF
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO