0.7.719.0.sqldataprovider.sql

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

IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO

if exists (select * from sysobjects where id=object_id('[dbo].[BugTimeEntry]') and OBJECTPROPERTY(id, 'IsUserTable')=1)
  drop table [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


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


ALTER TABLE [dbo].[BugTimeEntry] WITH NOCHECK ADD  CONSTRAINT [PK_BugTimeEntry] PRIMARY KEY CLUSTERED ([BugTimeEntryId]) 
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].[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

/* User Projects Table */

-- Remove foreign key constraints

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

PRINT 'Updating dbo.UserProjects'
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_UserProjects]
    (
  [UserId] [uniqueidentifier] NOT NULL ,
  [ProjectId] [int] NOT NULL ,
  [UserProjectId] [int] NOT NULL IDENTITY (1, 1) ,
  [CreatedDate] [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
SET IDENTITY_INSERT [dbo].[tmp_sc_UserProjects] 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_UserProjects] ([UserId], [ProjectId], [UserProjectId], [CreatedDate]) 
SELECT (SELECT aspnet_users.UserId From aspnet_users join Users on UserProjects.UserId = Users.UserId where Users.Username = aspnet_users.username ), [ProjectId], [UserProjectId], [CreatedDate] FROM [dbo].[UserProjects]
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_UserProjects] 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].[UserProjects]') and OBJECTPROPERTY(id, 'IsUserTable')=1)
  drop table [dbo].[UserProjects]
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_UserProjects', 'UserProjects'
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].[UserProjects] WITH NOCHECK ADD  CONSTRAINT [PK_UserProjects_1] PRIMARY KEY CLUSTERED ([UserId], [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

/* Project Table */
-- Remove foreign key constraints

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].[Component] DROP CONSTRAINT [FK_Component_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].[ProjectMailBox] DROP CONSTRAINT [FK_ProjectMailBox_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].[Version] DROP CONSTRAINT [FK_Version_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

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

PRINT 'Updating dbo.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].[Project]
DROP
  CONSTRAINT [DF_Project_Active]
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_Project]
    (
  [ProjectID] [int] NOT NULL IDENTITY (1, 1) ,
  [Name] [nvarchar] (50) NOT NULL ,
  [Code] [nvarchar] (3) NOT NULL ,
  [Description] [nvarchar] (80) NOT NULL ,
  [UploadPath] [nvarchar] (80) NOT NULL ,
  [CreateDate] [datetime] NOT NULL ,
  [Active] [int] NOT NULL ,
  [AccessType] [int] NOT NULL ,
  [ManagerUserID] [uniqueidentifier] NOT NULL ,
  [CreatorUserID] [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
if exists (select * from syscolumns where id=OBJECT_ID('[dbo].[tmp_sc_Project]') and name='Active' and cdefault>0) EXEC ('sp_unbindefault ''[dbo].[tmp_sc_Project].[Active]''')

ALTER TABLE [dbo].[tmp_sc_Project] WITH NOCHECK
ADD
 CONSTRAINT [DF_Project_Active] DEFAULT ((1)) FOR [Active]
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_Project] 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_Project] ([ProjectID], [Name], [Code], [Description], [UploadPath], [CreateDate], [Active], [AccessType], [CreatorUserID],[ManagerUserId]) 
SELECT [ProjectID], [Name], [Code], [Description], [UploadPath], [CreateDate], [Active], [AccessType], 
(SELECT aspnet_users.UserId From aspnet_users join Users on Project.CreatorUserId = Users.UserId where Users.Username = aspnet_users.username ),
(SELECT aspnet_users.UserId From aspnet_users join Users on Project.ManagerId = Users.UserId where Users.Username = aspnet_users.username )
 FROM [dbo].[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
SET IDENTITY_INSERT [dbo].[tmp_sc_Project] 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].[Project]') and OBJECTPROPERTY(id, 'IsUserTable')=1)
  drop table [dbo].[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


sp_rename 'dbo.tmp_sc_Project', '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].[Project] WITH NOCHECK ADD  CONSTRAINT [PK__Product__1DE57479] 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


-- Add foreign key constraints

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].[ProjectMailBox] WITH NOCHECK ADD CONSTRAINT [FK_ProjectMailBox_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].[ProjectMailBox] CHECK CONSTRAINT [FK_ProjectMailBox_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].[Version] WITH NOCHECK ADD CONSTRAINT [FK_Version_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].[Version] CHECK CONSTRAINT [FK_Version_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

/* Project Mailbox Table */
-- Remove foreign key constraints

ALTER TABLE [dbo].[ProjectMailBox] DROP CONSTRAINT [FK_ProjectMailBox_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

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

PRINT 'Updating dbo.ProjectMailBox'
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_ProjectMailBox]
    (
  [ProjectMailboxId] [int] NOT NULL IDENTITY (1, 1) ,
  [MailBox] [nvarchar] (100) NOT NULL ,
  [ProjectId] [int] NOT NULL ,
  [AssignToUserId] [uniqueidentifier] NULL ,
  [IssueTypeId] [int] 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_ProjectMailBox] 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_ProjectMailBox] ([ProjectMailboxId], [MailBox], [ProjectId], [AssignToUserId], [IssueTypeId]) 
SELECT [ProjectMailboxId], [MailBox], [ProjectId], (SELECT aspnet_users.UserId From aspnet_users join Users on ProjectMailBox.AssignToUserId = Users.UserId where Users.Username = aspnet_users.username ), [IssueTypeId] 
FROM [dbo].[ProjectMailBox]
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_ProjectMailBox] 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].[ProjectMailBox]') and OBJECTPROPERTY(id, 'IsUserTable')=1)
  drop table [dbo].[ProjectMailBox]
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_ProjectMailBox', 'ProjectMailBox'
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].[ProjectMailBox] WITH NOCHECK ADD  CONSTRAINT [PK_ProjectMailBox] PRIMARY KEY CLUSTERED ([ProjectMailboxId]) 
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].[ProjectMailBox] WITH NOCHECK ADD CONSTRAINT [FK_ProjectMailBox_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].[ProjectMailBox] CHECK CONSTRAINT [FK_ProjectMailBox_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

/* Component Table */
--
-- Script for dbo.Component
-- Foreign keys etc. will appear at the end
--

PRINT 'Updating dbo.Component'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION