Thread: [tcdb-devel] SF.net SVN: tcdb: [4] Schema/TCDBSQLServer2005.sql

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

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.



