--
-- BugNET 0.7 Upgrade Script
-- 
-- 
-- 
--
-- 
--
--

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


BEGIN TRANSACTION
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO

-- Remove foreign key constraints

ALTER TABLE [dbo].[Bug] DROP CONSTRAINT [FK_Bug_Environment]
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].[Bug] DROP CONSTRAINT [FK_Bug_Hardware]
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].[Bug] DROP CONSTRAINT [FK_Bug_OperatingSystem]
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].[Bug] DROP CONSTRAINT [FK_Bug_Priority]
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].[Bug] DROP CONSTRAINT [FK_Bug_Project]
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].[Bug] DROP CONSTRAINT [FK_Bug_Resolution]
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].[Bug] DROP CONSTRAINT [FK_Bug_Status]
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].[Bug] DROP CONSTRAINT [FK_Bug_Type]
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] DROP CONSTRAINT [FK_BugAttachment_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
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
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
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
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
ALTER TABLE [dbo].[RelatedBug] DROP CONSTRAINT [FK_RelatedBug_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.Bug
-- Foreign keys etc. will appear at the end
--

PRINT 'Updating dbo.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
CREATE TABLE [dbo].[tmp_sc_Bug]
    (
  [BugID] [int] NOT NULL IDENTITY (1, 1) ,
  [Summary] [nvarchar] (500) NOT NULL ,
  [Description] [ntext] NOT NULL ,
  [ReportedDate] [datetime] NOT NULL ,
  [StatusID] [int] NOT NULL ,
  [PriorityID] [int] NOT NULL ,
  [TypeID] [int] NOT NULL ,
  [ComponentID] [int] NOT NULL ,
  [ProjectID] [int] NOT NULL ,
  [ResolutionID] [int] NOT NULL ,
  [VersionID] [int] NOT NULL ,
  [LastUpdate] [datetime] NOT NULL ,
  [ReporterUserId] [uniqueidentifier] NOT NULL ,
  [AssignedToUserId] [uniqueidentifier] NULL ,
  [LastUpdateUserId] [uniqueidentifier] NOT NULL ,
  [DueDate] [datetime] NULL ,
  [FixedInVersionId] [int] NOT NULL ,
  [Visibility] [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
if exists (select * from syscolumns where id=OBJECT_ID('[dbo].[tmp_sc_Bug]') and name='DueDate' and cdefault>0) EXEC ('sp_unbindefault ''[dbo].[tmp_sc_Bug].[DueDate]''')


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_Bug] 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_Bug] 
([BugID], [Summary], [Description], [ReportedDate], [StatusID], [PriorityID], 
[TypeID], [ComponentID], [ProjectID], [ResolutionID], [VersionID], 
[LastUpdate],[Visibility],[FixedInVersionId],[LastUpdateUserId],[ReporterUserId],[AssignedToUserId]) 
SELECT [BugID], [Summary], [Description], [ReportedDate], 
[StatusID], [PriorityID], [TypeID], [ComponentID], [ProjectID], 
[ResolutionID], [VersionID], [LastUpdate], 0,-1,
(SELECT aspnet_users.UserId From aspnet_users join Users on Bug.LastUpdateUser = Users.UserId where Users.Username = aspnet_users.username ),
(SELECT aspnet_users.UserId From aspnet_users join Users on Bug.ReportedUser = Users.UserId where Users.Username = aspnet_users.username),
(SELECT aspnet_users.UserId From aspnet_users join Users on Bug.AssignedTo = Users.UserId where Users.Username = aspnet_users.username)
FROM [dbo].[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
SET IDENTITY_INSERT [dbo].[tmp_sc_Bug] 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].[Bug]') and OBJECTPROPERTY(id, 'IsUserTable')=1)
  drop table [dbo].[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


sp_rename 'dbo.tmp_sc_Bug', '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


ALTER TABLE [dbo].[Bug] WITH NOCHECK ADD  CONSTRAINT [PK__Bug__145C0A3F] PRIMARY KEY CLUSTERED ([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


-- Add foreign key constraints

ALTER TABLE [dbo].[Bug] WITH NOCHECK ADD CONSTRAINT [FK_Bug_Priority] FOREIGN KEY ([PriorityID])  REFERENCES [dbo].[Priority] ([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
ALTER TABLE [dbo].[Bug] CHECK CONSTRAINT [FK_Bug_Priority]
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].[Bug] WITH NOCHECK ADD CONSTRAINT [FK_Bug_Project] FOREIGN KEY ([ProjectID])  REFERENCES [dbo].[Project] ([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
ALTER TABLE [dbo].[Bug] CHECK CONSTRAINT [FK_Bug_Project]
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].[Bug] WITH NOCHECK ADD CONSTRAINT [FK_Bug_Resolution] FOREIGN KEY ([ResolutionID])  REFERENCES [dbo].[Resolution] ([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
ALTER TABLE [dbo].[Bug] CHECK CONSTRAINT [FK_Bug_Resolution]
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].[Bug] WITH NOCHECK ADD CONSTRAINT [FK_Bug_Status] FOREIGN KEY ([StatusID])  REFERENCES [dbo].[Status] ([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
ALTER TABLE [dbo].[Bug] CHECK CONSTRAINT [FK_Bug_Status]
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].[Bug] WITH NOCHECK ADD CONSTRAINT [FK_Bug_Type] FOREIGN KEY ([TypeID])  REFERENCES [dbo].[Type] ([TypeID]) 
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].[Bug] CHECK CONSTRAINT [FK_Bug_Type]
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 [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
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 [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
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 [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
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 [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
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].[BugTimeEntry] WITH NOCHECK ADD CONSTRAINT [FK_BugTimeEntry_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].[BugTimeEntry] CHECK 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
ALTER TABLE [dbo].[RelatedBug] WITH NOCHECK ADD CONSTRAINT [FK_RelatedBug_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].[RelatedBug] CHECK CONSTRAINT [FK_RelatedBug_Bug]
GO

/* Bug Attachment Table */

-- Remove foreign key constraints

ALTER TABLE [dbo].[BugAttachment] DROP CONSTRAINT [FK_BugAttachment_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.BugAttachment
-- Foreign keys etc. will appear at the end
--

PRINT 'Updating 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
CREATE TABLE [dbo].[tmp_sc_BugAttachment]
    (
  [BugAttachmentID] [int] NOT NULL IDENTITY (1, 1) ,
  [BugID] [int] NOT NULL ,
  [FileName] [nvarchar] (100) NOT NULL ,
  [Description] [nvarchar] (80) NOT NULL ,
  [FileSize] [int] NOT NULL ,
  [Type] [nvarchar] (50) NOT NULL ,
  [UploadedDate] [datetime] NOT NULL ,
  [UploadedUserId] [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_BugAttachment] 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_BugAttachment]