Menu
▾
▴
[tcdb-devel] SF.net SVN: tcdb: [9] Schema/TCDBSQLServer2005.sql
|
From: <jon...@us...> - 2006-05-28 21:44:25
|
Revision: 9 Author: jon_r_johnson Date: 2006-05-28 14:44:22 -0700 (Sun, 28 May 2006) ViewCVS: http://svn.sourceforge.net/tcdb/?rev=9&view=rev Log Message: ----------- Added Primary Key / Identity to the Table tcdb_logs. Modified Paths: -------------- Schema/TCDBSQLServer2005.sql Modified: Schema/TCDBSQLServer2005.sql =================================================================== --- Schema/TCDBSQLServer2005.sql 2006-05-27 00:53:13 UTC (rev 8) +++ Schema/TCDBSQLServer2005.sql 2006-05-28 21:44:22 UTC (rev 9) @@ -1,769 +1,774 @@ -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]( - [userRightsID] [int] IDENTITY(1,1) NOT NULL, - [userID] [int] NULL, - [rightsID] [int] NULL, - [dateCreated] [datetime] NULL, - [grantedBy] [int] NULL, - CONSTRAINT [PK_tcdb_userRights] PRIMARY KEY CLUSTERED -( - [userRightsID] 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_featureVersion]') AND type in (N'U')) -BEGIN -CREATE TABLE [dbo].[tcdb_featureVersion]( - [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 -)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_platform]') AND type in (N'U')) -BEGIN -CREATE TABLE [dbo].[tcdb_platform]( - [platformID] [int] IDENTITY(1,1) NOT NULL, - [architectureID] [int] NULL, - [osID] [int] NULL, - CONSTRAINT [PK_tcdb_platform] PRIMARY KEY CLUSTERED -( - [platformID] 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_testCaseTags]') AND type in (N'U')) -BEGIN -CREATE TABLE [dbo].[tcdb_testCaseTags]( - [testcaseTagsID] [int] IDENTITY(1,1) NOT NULL, - [testcaseID] [int] NULL, - [tagID] [int] NULL, - CONSTRAINT [PK_tcdb_testcasetags] PRIMARY KEY CLUSTERED -( - [testcaseTagsID] 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_workOrderTestCase]') AND type in (N'U')) -BEGIN -CREATE TABLE [dbo].[tcdb_workOrderTestCase]( - [workOrderTestCaseID] [int] IDENTITY(1,1) NOT NULL, - [workOrderID] [int] NULL, - [testCaseID] [int] NULL, - CONSTRAINT [PK_tcdb_workOrderTestCase] PRIMARY KEY CLUSTERED -( - [workOrderTestCaseID] 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_savedWorkOrderTestCase]') AND type in (N'U')) -BEGIN -CREATE TABLE [dbo].[tcdb_savedWorkOrderTestCase]( - [savedWOTestCaseID] [int] IDENTITY(1,1) NOT NULL, - [savedWorkOrderID] [int] NULL, - [testCaseID] [int] NULL, - CONSTRAINT [PK_tcdb_savedWorkOrderTestCase] PRIMARY KEY CLUSTERED -( - [savedWOTestCaseID] 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]( - [actionItemID] [int] IDENTITY(1,1) NOT NULL, - [name] [varchar](255) NULL, - [description] [text] NULL, - [creatorID] [int] NULL, - [dateCreated] [datetime] NULL, - [percentComplete] [int] NULL, - CONSTRAINT [PK_tcdb_actionItem] PRIMARY KEY CLUSTERED -( - [actionItemID] 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_testPass]') AND type in (N'U')) -BEGIN -CREATE TABLE [dbo].[tcdb_testPass]( - [testPassID] [int] IDENTITY(1,1) NOT NULL, - [name] [varchar](255) NULL, - [versionID] [int] NULL, - CONSTRAINT [PK_tcdb_testPass] PRIMARY KEY CLUSTERED -( - [testPassID] 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_result]') AND type in (N'U')) -BEGIN -CREATE TABLE [dbo].[tcdb_result]( - [resultID] [bigint] IDENTITY(1,1) NOT NULL, - [name] [varchar](255) NULL, - [statusEnum] [int] NULL, - [code] [text] NULL, - CONSTRAINT [PK_tcdb_result] PRIMARY KEY CLUSTERED -( - [resultID] 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_results]') AND type in (N'U')) -BEGIN -CREATE TABLE [dbo].[tcdb_results]( - [resultsID] [bigint] IDENTITY(1,1) NOT NULL, - [testCaseID] [int] NULL, - [platformID] [int] NULL, - [dateStarted] [datetime] NULL, - [dateFinished] [datetime] NULL, - [assignedID] [int] NULL, - [testPassID] [int] NULL, - [resultID] [bigint] NULL, - [buildID] [int] NULL, - [workOrderID] [int] NULL -) 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_logs]') AND type in (N'U')) -BEGIN -CREATE TABLE [dbo].[tcdb_logs]( - [logID] [bigint] NULL, - [resultID] [bigint] NULL, - [name] [varchar](255) NULL, - [dataLocation] [text] NULL -) 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_state]') AND type in (N'U')) -BEGIN -CREATE TABLE [dbo].[tcdb_state]( - [stateID] [int] IDENTITY(1,1) NOT NULL, - [currentDomain] [varchar](15) NULL, - [vasClnt] [varchar](16) NULL, - [vasClnts] [varchar](16) NULL, - [vasDev] [varchar](16) NULL, - [vasDevi] [varchar](16) NULL, - [vasGps] [varchar](16) NULL, - [vasProxy] [varchar](16) NULL, - [vasYP] [varchar](16) NULL, - [nis] [varchar](4) NULL, - [openssh] [varchar](16) NULL, - CONSTRAINT [PK_tcdb_state] PRIMARY KEY CLUSTERED -( - [stateID] 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_architecture]') AND type in (N'U')) -BEGIN -CREATE TABLE [dbo].[tcdb_architecture]( - [architectureID] [int] IDENTITY(1,1) NOT NULL, - [name] [varchar](255) NULL, - CONSTRAINT [PK_tcdb_architecture] PRIMARY KEY CLUSTERED -( - [architectureID] 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_assignmentStatus]') AND type in (N'U')) -BEGIN -CREATE TABLE [dbo].[tcdb_assignmentStatus]( - [assignmentStatusID] [int] IDENTITY(1,1) NOT NULL, - [statusName] [varchar](255) NULL, - [statusEnum] [int] NULL, - CONSTRAINT [PK_tcdb_assignmentStatus] PRIMARY KEY CLUSTERED -( - [assignmentStatusID] 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_computer]') AND type in (N'U')) -BEGIN -CREATE TABLE [dbo].[tcdb_computer]( - [computerID] [int] IDENTITY(1,1) NOT NULL, - [name] [varchar](255) NULL, - [ipAddress] [varchar](15) NULL, - [hostname] [varchar](50) NULL, - [platformID] [int] NULL, - [labID] [int] NULL, - [stateID] [int] NULL, - CONSTRAINT [PK_tcdb_computer] PRIMARY KEY CLUSTERED -( - [computerID] 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_feature]') AND type in (N'U')) -BEGIN -CREATE TABLE [dbo].[tcdb_feature]( - [featureID] [int] IDENTITY(1,1) NOT NULL, - [parentFeatureID] [int] NULL, - [name] [varchar](255) NULL, - [description] [text] NULL, - [userID] [int] NULL, - [dateCreated] [datetime] NULL, - [featureNumber] [varchar](20) NULL, - [orderNumber] [int] NULL, - CONSTRAINT [PK_tcdb_feature] PRIMARY KEY CLUSTERED -( - [featureID] 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_lab]') AND type in (N'U')) -BEGIN -CREATE TABLE [dbo].[tcdb_lab]( - [labID] [int] IDENTITY(1,1) NOT NULL, - [name] [varchar](255) NULL, - [description] [text] NULL, - [DNS] [varchar](15) NULL, - CONSTRAINT [PK_tcdb_lab] PRIMARY KEY CLUSTERED -( - [labID] 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_notes]') AND type in (N'U')) -BEGIN -CREATE TABLE [dbo].[tcdb_notes]( - [noteID] [int] IDENTITY(1,1) NOT NULL, - [tableName] [varchar](50) NULL, - [tableID] [int] NULL, - [dateCreated] [datetime] NULL, - [noteField] [text] NULL, - [noteAuthor] [int] NULL, - CONSTRAINT [PK_tcdb_notes] PRIMARY KEY CLUSTERED -( - [noteID] 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_objective]') AND type in (N'U')) -BEGIN -CREATE TABLE [dbo].[tcdb_objective]( - [objectiveID] [int] IDENTITY(1,1) NOT NULL, - [featureID] [int] NULL, - [name] [varchar](255) NULL, - [description] [text] NULL, - [userID] [int] NULL, - [dateEdited] [datetime] NULL, - [statusID] [int] NULL, - CONSTRAINT [PK_tcdb_objective] PRIMARY KEY CLUSTERED -( - [objectiveID] 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_os]') AND type in (N'U')) -BEGIN -CREATE TABLE [dbo].[tcdb_os]( - [osID] [int] IDENTITY(1,1) NOT NULL, - [friendlyName] [varchar](255) NULL, - [codeName] [varchar](30) NULL, - CONSTRAINT [PK_tcdb_os] PRIMARY KEY CLUSTERED -( - [osID] 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_product]') AND type in (N'U')) -BEGIN -CREATE TABLE [dbo].[tcdb_product]( - [productID] [int] IDENTITY(1,1) NOT NULL, - [name] [varchar](255) NULL, - [description] [text] NULL, - [devManager] [int] NULL, - [qaManager] [int] NULL, - [devLead] [int] NULL, - [qaLead] [int] NULL, - [pm] [int] NULL, - [codeName] [varchar](10) NULL, - CONSTRAINT [PK_tcdb_product] PRIMARY KEY CLUSTERED -( - [productID] 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_rights]') AND type in (N'U')) -BEGIN -CREATE TABLE [dbo].[tcdb_rights]( - [rightsID] [int] NOT NULL, - [name] [varchar](255) NULL, - [description] [text] NULL, - CONSTRAINT [PK_tcdb_rights] PRIMARY KEY CLUSTERED -( - [rightsID] 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_savedWorkOrder]') AND type in (N'U')) -BEGIN -CREATE TABLE [dbo].[tcdb_savedWorkOrder]( - [savedWorkOrderID] [int] IDENTITY(1,1) NOT NULL, - [name] [varchar](255) NULL, - [platformID] [int] NULL, - [creatorID] [int] NULL, - [versionID] [int] NULL, - CONSTRAINT [PK_tcdb_savedWorkOrder] PRIMARY KEY CLUSTERED -( - [savedWorkOrderID] 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_tags]') AND type in (N'U')) -BEGIN -CREATE TABLE [dbo].[tcdb_tags]( - [tagID] [int] IDENTITY(1,1) NOT NULL, - [name] [varchar](255) NULL, - [description] [text] NULL, - CONSTRAINT [PK_tcdb_tags] PRIMARY KEY CLUSTERED -( - [tagID] 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_testCase]') AND type in (N'U')) -BEGIN -CREATE TABLE [dbo].[tcdb_testCase]( - [testCaseID] [int] IDENTITY(1,1) NOT NULL, - [objectiveID] [int] NULL, - [name] [varchar](255) NULL, - [number] [varchar](30) NULL, - [steps] [text] NULL, - [creatorID] [int] NULL, - [lastEditID] [int] NULL, - [dateCreated] [datetime] NULL, - [dateEdited] [datetime] NULL, - [statusID] [int] NULL, - CONSTRAINT [PK_tcdb_testcase] PRIMARY KEY CLUSTERED -( - [testCaseID] 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_version]') AND type in (N'U')) -BEGIN -CREATE TABLE [dbo].[tcdb_version]( - [versionID] [int] IDENTITY(1,1) NOT NULL, - [productID] [int] NULL, - [number] [varchar](50) NULL, - [code] [varchar](50) NULL, - [active] [bit] NULL, - [dateCreated] [datetime] NULL, - [devManager] [int] NULL, - [qaManager] [int] NULL, - [devLead] [int] NULL, - [pm] [int] NULL, - CONSTRAINT [PK_tcdb_version] PRIMARY KEY CLUSTERED -( - [versionID] 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_workOrder]') AND type in (N'U')) -BEGIN -CREATE TABLE [dbo].[tcdb_workOrder]( - [workorderID] [int] IDENTITY(1,1) NOT NULL, - [name] [varchar](255) NULL, - [platformID] [int] NULL, - [dateCreated] [datetime] NULL, - [dateCompleted] [datetime] NULL, - [creatorID] [int] NULL, - [testPassID] [int] NULL, - CONSTRAINT [PK_tcdb_workorder] PRIMARY KEY CLUSTERED -( - [workorderID] 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'------------------------------------------------------------------ --- Stored Procedure: ins_user --- --- Database: TCDB --- Author: Jon R. Johnson --- Date: 05/23/2006 --- Purpose: Insert a new user in the master users table. --- --- Input: @firstName -- User First Name --- @lastName -- User Last Name --- @email -- Email Address for User --- @officePhone -- Office Phone for User I.E. ''(xxx) xxx-xxxx'' --- @homePhone -- Office Phone for User I.E. ''(xxx) xxx-xxxx'' --- @cellPhone -- Office Phone for User I.E. ''(xxx) xxx-xxxx'' --- @automation -- 0 - Normal Person, 1 - Automation Account --- --- Output: none ------------------------------------------------------------------- -CREATE proc [dbo].[ins_user] ( - @firstName varchar(24), - @lastName varchar(24), - @email varchar(50), - @officePhone varchar(14) = null, - @homePhone varchar(14) = null, - @cellPhone varchar(14) = null, - @automation bit = null -) as - --- 2006/05/24 JRJ Default to Normal Person -if (@automation is null) - set @automation = 0 - --- 2006/05/24 JRJ Corrected table name -insert into tcdb_user -select - @email, - @officePhone, - @homePhone, - @cellPhone, - @firstName, - @lastName, - @automation' -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_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_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_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] NOT 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_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'------------------------------------------------------------------ --- Stored Procedure: ins_product --- --- Database: TCDB --- Author: Jon R. Johnson --- Date: 05/23/2006 --- Purpose: Insert a new product in the master products table. --- --- Input: @name -- Product Name --- @description -- Product Description --- @devManager -- Development Manager --- @qaManager -- Q.A. Manager --- @devLead -- Development Lead --- @qaLead -- Q.A. Lead --- @pm -- Product Manager --- @codeName -- Product Code Name --- --- Output: none ------------------------------------------------------------------- -create proc [dbo].[ins_product] ( - @name varchar(255), - @description text, - @devManager varchar(49), - @qaManager varchar(49), - @devLead varchar(49), - @qaLead varchar(49), - @pm varchar(49), - @codeName varchar(10) -) as - - -insert into tcdb_product -select - @name, - @description, - devManager=(select userID from tcdb_user where firstName+'' ''+lastName = @devManager), - qaManager=(select userID from tcdb_user where firstName+'' ''+lastName = @qaManager), - devLead=(select userID from tcdb_user where firstName+'' ''+lastName = @devLead), - qaLead=(select userID from tcdb_user where firstName+'' ''+lastName = @qaLead), - pm=(select userID from tcdb_user where firstName+'' ''+lastName = @pm), - @codeName - -' -END +\xFF\xFES |
