0.7.719.0.sqldataprovider.sql

来自「BugNET is an issue tracking and project 」· SQL 代码 · 共 1,929 行 · 第 1/5 页

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

PRINT 'Updating dbo.ProjectCustomFieldSelection'
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].[ProjectCustomFieldSelection]
    (
  [CustomFieldSelectionId] [int] NOT NULL IDENTITY (1, 1) ,
  [CustomFieldId] [int] NOT NULL ,
  [CustomFieldSelectionValue] [nchar] (255) NOT NULL ,
  [CustomFieldSelectionName] [nchar] (255) NOT NULL ,
  [CustomFieldSelectionSortOrder] [int] NOT NULL 
)



ALTER TABLE [dbo].[ProjectCustomFieldSelection] ADD CONSTRAINT [DF_ProjectCustomFieldSelection_CustomFieldSelectionSortOrder] DEFAULT ((0)) FOR [CustomFieldSelectionSortOrder]
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].[ProjectCustomFieldSelection] WITH NOCHECK ADD  CONSTRAINT [PK_ProjectCustomFieldSelection] PRIMARY KEY CLUSTERED ([CustomFieldSelectionId]) 
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.ProjectCustomFieldType
-- Foreign keys etc. will appear at the end
--

PRINT 'Updating dbo.ProjectCustomFieldType'
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].[ProjectCustomFieldType]
    (
  [CustomFieldTypeId] [int] NOT NULL IDENTITY (1, 1) ,
  [CustomFieldTypeName] [nvarchar] (50) NOT NULL 
)



ALTER TABLE [dbo].[ProjectCustomFieldType] WITH NOCHECK ADD  CONSTRAINT [PK_ProjectCustomFieldType] PRIMARY KEY CLUSTERED ([CustomFieldTypeId]) 
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.ProjectCustomFieldValues
-- Foreign keys etc. will appear at the end
--

PRINT 'Updating dbo.ProjectCustomFieldValues'
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].[ProjectCustomFieldValues]
    (
  [CustomFieldValueId] [int] NOT NULL IDENTITY (1, 1) ,
  [BugId] [int] NOT NULL ,
  [CustomFieldId] [int] NOT NULL ,
  [CustomFieldValue] [ntext] NOT NULL 
)



ALTER TABLE [dbo].[ProjectCustomFieldValues] WITH NOCHECK ADD  CONSTRAINT [PK_ProjectCustomFieldValues] PRIMARY KEY CLUSTERED ([CustomFieldValueId]) 
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].[ProjectCustomFields] WITH NOCHECK ADD CONSTRAINT [FK_ProjectCustomFields_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].[ProjectCustomFields] CHECK CONSTRAINT [FK_ProjectCustomFields_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].[ProjectCustomFields] WITH NOCHECK ADD CONSTRAINT [FK_ProjectCustomFields_ProjectCustomFieldType] FOREIGN KEY ([CustomFieldTypeId])  REFERENCES [dbo].[ProjectCustomFieldType] ([CustomFieldTypeId]) 
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].[ProjectCustomFields] CHECK CONSTRAINT [FK_ProjectCustomFields_ProjectCustomFieldType]
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].[ProjectCustomFieldSelection] WITH NOCHECK ADD CONSTRAINT [FK_ProjectCustomFieldSelection_ProjectCustomFields] FOREIGN KEY ([CustomFieldId])  REFERENCES [dbo].[ProjectCustomFields] ([CustomFieldId]) 
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].[ProjectCustomFieldSelection] CHECK CONSTRAINT [FK_ProjectCustomFieldSelection_ProjectCustomFields]
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].[ProjectCustomFieldValues] WITH NOCHECK ADD CONSTRAINT [FK_ProjectCustomFieldValues_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].[ProjectCustomFieldValues] CHECK CONSTRAINT [FK_ProjectCustomFieldValues_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].[ProjectCustomFieldValues] WITH NOCHECK ADD CONSTRAINT [FK_ProjectCustomFieldValues_ProjectCustomFields] FOREIGN KEY ([CustomFieldId])  REFERENCES [dbo].[ProjectCustomFields] ([CustomFieldId]) 
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].[ProjectCustomFieldValues] CHECK CONSTRAINT [FK_ProjectCustomFieldValues_ProjectCustomFields]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO

/* Remaining Tables */
-- Remove foreign key constraints

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
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].[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.ProjectRoles
-- Foreign keys etc. will appear at the end
--

PRINT 'Updating dbo.ProjectRoles'
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].[ProjectRoles]
    (
  [ProjectRoleId] [int] NOT NULL IDENTITY (1, 1) ,
  [RoleId] [uniqueidentifier] NOT NULL ,
  [ProjectId] [int] NOT NULL ,
  [CreatedDate] [datetime] NOT NULL 
)



ALTER TABLE [dbo].[ProjectRoles] WITH NOCHECK ADD  CONSTRAINT [PK_ProjectRoles] PRIMARY KEY CLUSTERED ([ProjectRoleId]) 
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.RelatedBug
-- Foreign keys etc. will appear at the end
--

PRINT 'Updating dbo.RelatedBug'
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_RelatedBug]
    (
  [RelatedBugID] [int] NOT NULL IDENTITY (1, 1) ,
  [BugID] [int] NOT NULL ,
  [LinkedBugID] [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
SET IDENTITY_INSERT [dbo].[tmp_sc_RelatedBug] 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_RelatedBug] ([RelatedBugID], [BugID], [LinkedBugID]) SELECT [RelatedBugID], [BugID], [LinkedBugID] FROM [dbo].[RelatedBug]
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_RelatedBug] 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].[RelatedBug]') and OBJECTPROPERTY(id, 'IsUserTable')=1)
  drop table [dbo].[RelatedBug]
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_RelatedBug', 'RelatedBug'
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 [PK_BugRelation] PRIMARY KEY CLUSTERED ([RelatedBugID]) 
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.Resolution
-- Foreign keys etc. will appear at the end
--

PRINT 'Updating dbo.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
CREATE TABLE [dbo].[tmp_sc_Resolution]
    (
  [ResolutionID] [int] NOT NULL IDENTITY (1, 1) ,
  [Name] [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
SET IDENTITY_INSERT [dbo].[tmp_sc_Resolution] 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_Resolution] ([ResolutionID], [Name]) SELECT [ResolutionID], [Name] FROM [dbo].[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
SET IDENTITY_INSERT [dbo].[tmp_sc_Resolution] 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].[Resolution]') and OBJECTPROPERTY(id, 'IsUserTable')=1)
  drop table [dbo].[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


sp_rename 'dbo.tmp_sc_Resolution', '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].[Resolution] WITH NOCHECK ADD  CONSTRAINT [PK__Resolution__00551192] 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


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

PRINT 'Updating dbo.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
CREATE TABLE [dbo].[tmp_sc_Status]
    (
  [StatusID] [int] NOT NULL IDENTITY (1, 1) ,
  [Name] [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
SET IDENTITY_INSERT [dbo].[tmp_sc_Status] 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_Status] ([StatusID], [Name]) SELECT [StatusID], [Name] FROM [dbo].[Status]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 RO