Menu
▾
▴
[tcdb-devel] SF.net SVN: tcdb: [16] Schema/TCDBSQLServer2005.sql
|
From: <jon...@us...> - 2006-06-02 23:10:32
|
Revision: 16 Author: jon_r_johnson Date: 2006-06-02 16:10:27 -0700 (Fri, 02 Jun 2006) ViewCVS: http://svn.sourceforge.net/tcdb/?rev=16&view=rev Log Message: ----------- Added Column username to tcdb_users table, this will be used to help verify user authentication in the application. Modified Paths: -------------- Schema/TCDBSQLServer2005.sql Modified: Schema/TCDBSQLServer2005.sql =================================================================== --- Schema/TCDBSQLServer2005.sql 2006-06-01 00:03:40 UTC (rev 15) +++ Schema/TCDBSQLServer2005.sql 2006-06-02 23:10:27 UTC (rev 16) @@ -1,1218 +1,1220 @@ -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_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_logs]') AND type in (N'U')) -BEGIN -CREATE TABLE [dbo].[tcdb_logs]( - [logID] [bigint] IDENTITY(1,1) NOT NULL, - [resultID] [bigint] NULL, - [name] [varchar](255) NULL, - [dataLocation] [text] NULL, - CONSTRAINT [PK_tcdb_logs] PRIMARY KEY CLUSTERED -( - [logID] 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_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_platform]') AND type in (N'P', N'PC')) -BEGIN -EXEC dbo.sp_executesql @statement = N'------------------------------------------------------------------ --- Stored Procedure: ins_platform --- --- Database: TCDB --- Author: Jon R. Johnson --- Date: 05/30/2006 --- Purpose: Insert a new computer in the master computers table. --- --- Input: @architectureID -- Identity of the Architecture for this Platform --- @osID -- Identity of the OS for this Platform --- --- Output: none ------------------------------------------------------------------- -CREATE proc [dbo].[ins_platform] ( - @architectureID int, - @osID int -) as - -insert into tcdb_platform -select - @architectureID, - @osID - -' -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_testCaseTags]') AND type in (N'P', N'PC')) -BEGIN -EXEC dbo.sp_executesql @statement = N'------------------------------------------------------------------ --- Stored Procedure: ins_testCaseTags --- --- Database: TCDB --- Author: Jon R. Johnson --- Date: 05/31/2006 --- Purpose: Insert a new test case tag in the test case tags table. --- --- Input: @testcaseID -- Identity of the Testcase for this Tag --- @tagID -- Identity of the Tag for this Testcase --- --- Output: none ------------------------------------------------------------------- -CREATE proc [dbo].[ins_testCaseTags] ( - @testcaseID int, - @tagID int -) as - -insert into tcdb_testCaseTags -select - @testcaseID, - @tagID - -' -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_testPass]') AND type in (N'P', N'PC')) -BEGIN -EXEC dbo.sp_executesql @statement = N'------------------------------------------------------------------ --- Stored Procedure: ins_testPass --- --- Database: TCDB --- Author: Jon R. Johnson --- Date: 05/31/2006 --- Purpose: Insert a new computer in the master computers table. --- --- Input: @name -- Test Pass Name --- @versionID -- Identity of the Version for this test pass --- --- Output: none ------------------------------------------------------------------- -CREATE proc [dbo].[ins_testPass] ( - @name varchar(255), - @versionID int -) as - -insert into tcdb_testPass -select - @name, - @versionID - -' -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_architecture]') AND type in (N'P', N'PC')) -BEGIN -EXEC dbo.sp_executesql @statement = N'------------------------------------------------------------------ --- Stored Procedure: ins_architecture --- --- Database: TCDB --- Author: Jon R. Johnson --- Date: 05/30/2006 --- Purpose: Insert a new architecture in the master architecture table. --- --- Input: @name -- Architecture Name --- --- Output: none ------------------------------------------------------------------- -CREATE proc [dbo].[ins_architecture] ( - @name varchar(255) -) as - -insert into tcdb_architecture -select - @name - -' -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_computer]') AND type in (N'P', N'PC')) -BEGIN -EXEC dbo.sp_executesql @statement = N'------------------------------------------------------------------ --- Stored Procedure: ins_computer --- --- Database: TCDB --- Author: Jon R. Johnson --- Date: 05/30/2006 --- Purpose: Insert a new computer in the master computers table. --- --- Input: @name -- Computer Name --- @ipAddress -- Computer IP Address --- @hostname -- Computer Hostname --- @platformID -- Identity of the Platform for this Computer --- @labID -- Identity of the Lab for this Computer --- @stateID -- Identity of the State for this Computer --- --- Output: none ------------------------------------------------------------------- -CREATE proc [dbo].[ins_computer] ( - @name varchar(255), - @ipAddress varchar(15), - @hostname varchar(50), - @platformID int, - @labID int, - @stateID int -) as - -insert into tcdb_computer -select - @name, - @ipAddress, - @hostname, - @platformID, - @labID, - @stateID - -' -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_lab]') AND type in (N'P', N'PC')) -BEGIN -EXEC dbo.sp_executesql @statement = N'------------------------------------------------------------------ --- Stored Procedure: ins_lab --- --- Database: TCDB --- Author: Jon R. Johnson --- Date: 05/30/2006 --- Purpose: Insert a new lab in the lab table. --- --- Input: @name -- Lab Name --- @description -- Description of the Lab --- @DNS -- LAB DNS (I.P.) --- --- Output: none ------------------------------------------------------------------- -CREATE proc [dbo].[ins_lab] ( - @name varchar(255), - @description text, - @DNS varchar(15) -) as - -insert into tcdb_lab -select - @name, - @description, - @DNS - -' -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_os]') AND type in (N'P', N'PC')) -BEGIN -EXEC dbo.sp_executesql @statement = N'------------------------------------------------------------------ --- Stored Procedure: ins_os --- --- Database: TCDB --- Author: Jon R. Johnson --- Date: 05/30/2006 --- Purpose: Insert a new OS in the OS table. --- --- Input: @friendlyname -- OS Friendly Name --- @codeName -- OS Code Name --- --- Output: none ------------------------------------------------------------------- -CREATE proc [dbo].[ins_os] ( - @friendlyname varchar(255), - @codeName varchar(30) -) as - -insert into tcdb_os -select - @friendlyname, - @codeName - -' -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 -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_tags]') AND type in (N'P', N'PC')) -BEGIN -EXEC dbo.sp_executesql @statement = N'------------------------------------------------------------------ --- Stored Procedure: ins_tags --- --- Database: TCDB --- Author: Jon R. Johnson --- Date: 05/30/2006 --- Purpose: Insert a new tag in the tag table. --- --- Input: @name -- Tag Name --- @description -- Description of tag, which can apply to any test case --- --- Output: none ------------------------------------------------------------------- -CREATE proc [dbo].[ins_tags] ( - @name varchar(255), - @description text -) as - -insert into tcdb_tags -select - @name, - @description - -' -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_testCase]') AND type in (N'P', N'PC')) -BEGIN -EXEC dbo.sp_executesql @statement = N'------------------------------------------------------------------ --- Stored Procedure: ins_testCase --- --- Database: TCDB --- Author: Jon R. Johnson --- Date: 05/30/2006 --- Purpose: Insert a new test case in the test case table. --- --- Input: @objectiveID -- Identity of the objective for this test case --- @name -- Test case name --- @number -- Test case number --- @steps -- Steps for this test case to execute --- @creatorID -- Identity of the creator of this test case --- @statusID -- Identity of the status for this test case --- --- Output: none ------------------------------------------------------------------- -CREATE proc [dbo].[ins_testCase] ( - @objectiveID int, - @name varchar(255), - @number varchar(30), - @steps text, - @creatorID int, - @statusID int -) as - -insert into tcdb_testCase -select - @objectiveID, - @name, - @number, - @steps, - @creatorID, - lastEditID=null, - dateCreated=getdate(), - dateEdited=null, - @statusID - -' -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_version]') AND type in (N'P', N'PC')) -BEGIN -EXEC dbo.sp_executesql @statement = N'------------------------------------------------------------------ --- Stored Procedure: ins_version --- --- Database: TCDB --- Author: Jon R. Johnson --- Date: 05/31/2006 --- Purpose: Insert a new product version in the version table. --- --- Input: @productID -- Identity of the Product for this Version --- @number -- Product Version Number --- @code -- Product Code Version --- @active -- Is Version Active=1 / Inactive=0 --- @devManager -- Identity of the Dev Manager for this Version --- @qaManager -- Identity of the QA Manager for this Version --- @devLead -- Identity of the Dev Lead for this Version --- @pm -- Identity of the Product Manager for this Version --- --- Output: none ------------------------------------------------------------------- -CREATE proc [dbo].[ins_version] ( - @productID int, - @number varchar(50), - @code varchar(50), - @active bit, - @devManager int, - @qaManager int, - @devLead int, - @pm int -) as - -insert into tcdb_version -select - @productID, - @number, - @code, - @active, - dateCreated=getdate(), - @devManager, - @qaManager, - @devLead, - @pm - -' -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_versionTestCase]') AND type in (N'P', N'PC')) -BEGIN -EXEC dbo.sp_executesql @statement = N'------------------------------------------------------------------ --- Stored Procedure: ins_versionTestCase --- --- Database: TCDB --- Author: Jon R. Johnson --- Date: 05/31/2006 --- Purpose: Insert a new product version specific test case in the version --- test case table. --- --- Input: @testCaseID -- Identity of the test case --- @versionID -- Identity of the Product Version Number --- @include -- Specialty Include=1 / Exclude=0 --- @minVersion -- Minimum product version test case applies to / null is everything up to maxversion --- @maxVersion -- Maximum product version test case applies to / null is everything above minversion --- --- Output: none ------------------------------------------------------------------- -CREATE proc [dbo].[ins_versionTestCase] ( - @testCaseID int, - @versionID int, - @include bit, - @minVersion varchar(50) = null, - @maxVersion varchar(50) = null -) as - -insert into tcdb_versionTestCase -select - @testCaseID, - @versionID, - @include, - @minVersion, - @maxVersion - -' -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_build]') AND type in (N'P', N'PC')) -BEGIN -EXEC dbo.sp_executesql @statement = N'------------------------------------------------------------------ --- Stored Procedure: ins_build --- --- Database: TCDB --- Author: Jon R. Johnson --- Date: 05/30/2006 --- Purpose: Insert a new build in the build table. --- --- Input: @versionID -- Identity of the Version this build belongs to --- @number -- Build Number --- @active -- 0=Inactive Build 1=Active Build --- --- Output: none ------------------------------------------------------------------- -CREATE proc [dbo].[ins_build] ( - @versionID int, - @number varchar(20), - @active bit -) as - - -insert into tcdb_build -select - @versionID, - @number, - @active, - getdate() - -' -END +\xFF\xFES |
