Menu
▾
▴
[tcdb-devel] SF.net SVN: tcdb: [4] Schema/TCDBSQLServer2005.sql
|
From: <jon...@us...> - 2006-05-25 21:38:51
|
Revision: 4 Author: jon_r_johnson Date: 2006-05-25 14:38:46 -0700 (Thu, 25 May 2006) ViewCVS: http://svn.sourceforge.net/tcdb/?rev=4&view=rev Log Message: ----------- Modified Paths: -------------- Schema/TCDBSQLServer2005.sql Modified: Schema/TCDBSQLServer2005.sql =================================================================== --- Schema/TCDBSQLServer2005.sql 2006-05-25 20:24:12 UTC (rev 3) +++ Schema/TCDBSQLServer2005.sql 2006-05-25 21:38:46 UTC (rev 4) @@ -2,28 +2,6 @@ GO SET QUOTED_IDENTIFIER ON GO -IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tcdb_user]') AND type in (N'U')) -BEGIN -CREATE TABLE [dbo].[tcdb_user]( - [userID] [int] IDENTITY(1,1) NOT NULL, - [email] [varchar](50) NULL, - [officePhone] [varchar](14) NULL, - [homePhone] [varchar](14) NULL, - [cellPhone] [varchar](14) NULL, - [firstName] [varchar](24) NULL, - [lastName] [varchar](24) NULL, - [automation] [bit] NULL, - CONSTRAINT [PK_tcdb_user] PRIMARY KEY CLUSTERED -( - [userID] ASC -)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] -) ON [PRIMARY] -END -GO -SET ANSI_NULLS ON -GO -SET QUOTED_IDENTIFIER ON -GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tcdb_userRights]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[tcdb_userRights]( @@ -49,6 +27,9 @@ [featureVersionID] [int] IDENTITY(1,1) NOT NULL, [featureID] [int] NULL, [versionID] [int] NULL, + [include] [bit] NULL CONSTRAINT [DF_tcdb_featureVersion_include] DEFAULT ((0)), + [minVersion] [varchar](50) NULL, + [maxVersion] [varchar](50) NULL, CONSTRAINT [PK_tcdb_featureVersion] PRIMARY KEY CLUSTERED ( [featureVersionID] ASC @@ -128,28 +109,6 @@ GO SET QUOTED_IDENTIFIER ON GO -IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tcdb_assignment]') AND type in (N'U')) -BEGIN -CREATE TABLE [dbo].[tcdb_assignment]( - [assignmentID] [int] IDENTITY(1,1) NOT NULL, - [creatorID] [int] NULL, - [assignedID] [int] NULL, - [dateAssigned] [datetime] NULL, - [dateFinished] [datetime] NULL, - [assignmentStatusID] [int] NULL, - [workOrderID] [int] NULL, - [actionItemID] [int] NULL, - CONSTRAINT [PK_tcdb_assignment] PRIMARY KEY CLUSTERED -( - [assignmentID] ASC -)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] -) ON [PRIMARY] -END -GO -SET ANSI_NULLS ON -GO -SET QUOTED_IDENTIFIER ON -GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tcdb_actionItem]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[tcdb_actionItem]( @@ -327,6 +286,7 @@ [userID] [int] NULL, [dateCreated] [datetime] NULL, [featureNumber] [varchar](20) NULL, + [orderNumber] [int] NULL, CONSTRAINT [PK_tcdb_feature] PRIMARY KEY CLUSTERED ( [featureID] ASC @@ -385,6 +345,7 @@ [description] [text] NULL, [userID] [int] NULL, [dateEdited] [datetime] NULL, + [statusID] [int] NULL, CONSTRAINT [PK_tcdb_objective] PRIMARY KEY CLUSTERED ( [objectiveID] ASC @@ -501,6 +462,7 @@ [lastEditID] [int] NULL, [dateCreated] [datetime] NULL, [dateEdited] [datetime] NULL, + [statusID] [int] NULL, CONSTRAINT [PK_tcdb_testcase] PRIMARY KEY CLUSTERED ( [testCaseID] ASC @@ -557,45 +519,6 @@ GO SET QUOTED_IDENTIFIER ON GO -IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tcdb_versionTestCase]') AND type in (N'U')) -BEGIN -CREATE TABLE [dbo].[tcdb_versionTestCase]( - [versionTestCaseID] [int] IDENTITY(1,1) NOT NULL, - [testCaseID] [int] NULL, - [versionID] [int] NULL, - [include] [bit] NULL, - [minVersion] [varchar](50) NULL, - [maxVersion] [varchar](50) NULL, - CONSTRAINT [PK_tcdb_versionTestCase] PRIMARY KEY CLUSTERED -( - [versionTestCaseID] ASC -)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] -) ON [PRIMARY] -END -GO -SET ANSI_NULLS ON -GO -SET QUOTED_IDENTIFIER ON -GO -IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tcdb_build]') AND type in (N'U')) -BEGIN -CREATE TABLE [dbo].[tcdb_build]( - [buildID] [int] IDENTITY(1,1) NOT NULL, - [versionID] [int] NULL, - [number] [varchar](20) NULL, - [active] [bit] NULL, - [dateCreated] [datetime] NULL, - CONSTRAINT [PK_tcdb_build] PRIMARY KEY CLUSTERED -( - [buildID] ASC -)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] -) ON [PRIMARY] -END -GO -SET ANSI_NULLS ON -GO -SET QUOTED_IDENTIFIER ON -GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ins_user]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'------------------------------------------------------------------ @@ -642,6 +565,157 @@ GO SET QUOTED_IDENTIFIER ON GO +IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tcdb_versionTestCase]') AND type in (N'U')) +BEGIN +CREATE TABLE [dbo].[tcdb_versionTestCase]( + [versionTestCaseID] [int] IDENTITY(1,1) NOT NULL, + [testCaseID] [int] NULL, + [versionID] [int] NULL, + [include] [bit] NULL CONSTRAINT [DF_tcdb_versionTestCase_include] DEFAULT ((0)), + [minVersion] [varchar](50) NULL, + [maxVersion] [varchar](50) NULL, + CONSTRAINT [PK_tcdb_versionTestCase] PRIMARY KEY CLUSTERED +( + [versionTestCaseID] ASC +)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] +) ON [PRIMARY] +END +GO +SET ANSI_NULLS ON +GO +SET QUOTED_IDENTIFIER ON +GO +IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tcdb_user]') AND type in (N'U')) +BEGIN +CREATE TABLE [dbo].[tcdb_user]( + [userID] [int] IDENTITY(1,1) NOT NULL, + [email] [varchar](50) NOT NULL, + [officePhone] [varchar](14) NULL, + [homePhone] [varchar](14) NULL, + [cellPhone] [varchar](14) NULL, + [firstName] [varchar](24) NOT NULL, + [lastName] [varchar](24) NOT NULL, + [automation] [bit] NULL, + CONSTRAINT [PK_tcdb_user] PRIMARY KEY CLUSTERED +( + [email] ASC, + [firstName] ASC, + [lastName] ASC +)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] +) ON [PRIMARY] +END +GO + +IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tcdb_user]') AND name = N'ix_userID') +CREATE UNIQUE NONCLUSTERED INDEX [ix_userID] ON [dbo].[tcdb_user] +( + [userID] ASC +)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] +GO +SET ANSI_NULLS ON +GO +SET QUOTED_IDENTIFIER ON +GO +IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tcdb_platformVersion]') AND type in (N'U')) +BEGIN +CREATE TABLE [dbo].[tcdb_platformVersion]( + [platformVersionID] [int] IDENTITY(1,1) NOT NULL, + [platformID] [int] NULL, + [versionID] [int] NULL, + [include] [bit] NULL CONSTRAINT [DF_tcdb_platformVersion_include] DEFAULT ((0)), + [minVersion] [varchar](50) NULL, + [maxVersion] [varchar](50) NULL, + CONSTRAINT [PK_tcdb_platformVersion] PRIMARY KEY CLUSTERED +( + [platformVersionID] ASC +)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] +) ON [PRIMARY] +END +GO +SET ANSI_NULLS ON +GO +SET QUOTED_IDENTIFIER ON +GO +IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tcdb_testcasePlatform]') AND type in (N'U')) +BEGIN +CREATE TABLE [dbo].[tcdb_testcasePlatform]( + [testcasePlatformID] [int] IDENTITY(1,1) NOT NULL, + [testcaseID] [int] NULL, + [platformID] [int] NULL, + [include] [bit] NULL CONSTRAINT [DF_tcdb_testcasePlatform_include] DEFAULT ((0)), + CONSTRAINT [PK_tcdb_testcasePlatform] PRIMARY KEY CLUSTERED +( + [testcasePlatformID] ASC +)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] +) ON [PRIMARY] +END +GO +SET ANSI_NULLS ON +GO +SET QUOTED_IDENTIFIER ON +GO +IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tcdb_status]') AND type in (N'U')) +BEGIN +CREATE TABLE [dbo].[tcdb_status]( + [statusID] [int] IDENTITY(1,1) NOT NULL, + [statusGroup] [varchar](50) NULL, + [statusName] [varchar](50) NULL, + [Description] [text] NULL, + CONSTRAINT [PK_tcdb_status] PRIMARY KEY CLUSTERED +( + [statusID] ASC +)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] +) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] +END +GO +SET ANSI_NULLS ON +GO +SET QUOTED_IDENTIFIER ON +GO +IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tcdb_assignment]') AND type in (N'U')) +BEGIN +CREATE TABLE [dbo].[tcdb_assignment]( + [assignmentID] [int] IDENTITY(1,1) NOT NULL, + [creatorID] [int] NULL, + [assignedID] [int] NULL, + [dateAssigned] [datetime] NULL, + [dateFinished] [datetime] NULL, + [dateDue] [datetime] NULL, + [assignmentStatusID] [int] NULL, + [workOrderID] [int] NULL, + [actionItemID] [int] NULL, + [highPriority] [bit] NULL CONSTRAINT [DF_tcdb_assignment_highPriority] DEFAULT ((0)), + [statusID] [int] NULL, + CONSTRAINT [PK_tcdb_assignment] PRIMARY KEY CLUSTERED +( + [assignmentID] ASC +)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] +) ON [PRIMARY] +END +GO +SET ANSI_NULLS ON +GO +SET QUOTED_IDENTIFIER ON +GO +IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tcdb_build]') AND type in (N'U')) +BEGIN +CREATE TABLE [dbo].[tcdb_build]( + [buildID] [int] IDENTITY(1,1) NOT NULL, + [versionID] [int] NULL, + [number] [varchar](20) NULL, + [active] [bit] NULL, + [dateCreated] [datetime] NULL, + CONSTRAINT [PK_tcdb_build] PRIMARY KEY CLUSTERED +( + [buildID] ASC +)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] +) ON [PRIMARY] +END +GO +SET ANSI_NULLS ON +GO +SET QUOTED_IDENTIFIER ON +GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ins_product]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'------------------------------------------------------------------ This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
