Script.sql

--<ScriptOptions statementTerminator="GO"/>

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE RefPhoneCommands (
	CommandName null NOT NULL,
	RefPhoneName null NOT NULL,
	Commands null NOT NULL,
	CommandDescription null NOT NULL,
	CONSTRAINT PK_RefPhoneCommands PRIMARY KEY (CommandName, RefPhoneName) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

)
GO

CREATE TABLE ExecuteState (
	StateID UNIQUEIDENTIFIER NOT NULL,
	PcName VARCHAR(50) NOT NULL,
	Product VARCHAR(50) NOT NULL,
	Version VARCHAR(50) NOT NULL,
	Remark VARCHAR(255) NOT NULL,
	Freeze INT NOT NULL,
	Reboot INT NOT NULL,
	StartTime DATETIME NOT NULL,
	LastUpdate DATETIME NOT NULL,
	CONSTRAINT PK_ExecuteState PRIMARY KEY (StateID) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

)
GO

CREATE TABLE System (
	SystemID INT NOT NULL,
	TestSystem VARCHAR(50),
	PlaceID VARCHAR(50),
	PCHostname VARCHAR(50),
	TestSWver VARCHAR(50),
	TCdB INT,
	SettingsFile VARCHAR(200),
	SettingsFilever VARCHAR(50),
	LogFileName VARCHAR(250),
	CONSTRAINT PK_System PRIMARY KEY (SystemID) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

)
GO

CREATE TABLE TDFeatureGroup (
	FeatureGroupID UNIQUEIDENTIFIER DEFAULT (newid()),
	FeatureGroup VARCHAR(50) NOT NULL,
	rowguid UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
	TraceID INT,
	CONSTRAINT PK_TFeatureGroup PRIMARY KEY (FeatureGroup) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

)
GO

CREATE TABLE ResObjects (
	ResObjectID INT NOT NULL,
	ResultID INT,
	Object IMAGE,
	Format VARCHAR(3)
)
GO

CREATE TABLE TStep (
	StepNo INT NOT NULL,
	StepDesc VARCHAR(50),
	Commands TEXT,
	StepRem TEXT,
	StepID UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
	CaseID UNIQUEIDENTIFIER NOT NULL,
	SpecID UNIQUEIDENTIFIER NOT NULL,
	SysSetup VARCHAR(300) NOT NULL,
	rowguid UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
	CONSTRAINT PK_TStep PRIMARY KEY (CaseID, StepNo) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

)
GO

CREATE TABLE Users (
	UserID VARCHAR(50) NOT NULL,
	Role VARCHAR(50),
	CreateTime DATETIME DEFAULT (getdate()),
	mail VARCHAR(50),
	FullName VARCHAR(50),
	CONSTRAINT PK__Users__1788CCAC149C0161 PRIMARY KEY (UserID) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

)
GO

CREATE TABLE ResultAsObj (
	ResultID INT NOT NULL,
	ResObj IMAGE,
	ResFormat VARCHAR(50)
)
GO

CREATE TABLE ResultSpec (
	SpecIDname VARCHAR(150) NOT NULL,
	ResType VARCHAR(3),
	Unit VARCHAR(20),
	ExpectedRes TEXT,
	ExpectedObj IMAGE,
	EObjFormat VARCHAR(50),
	AlikeSpecID INT,
	AreaNo UNIQUEIDENTIFIER NOT NULL,
	SpecID UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
	rowguid UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
	CONSTRAINT PK_ResultSpec PRIMARY KEY (SpecIDname, AreaNo) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

)
GO

CREATE TABLE TMessages (
	MsgName VARCHAR(250) NOT NULL,
	MsgContent IMAGE NOT NULL,
	MsgType VARCHAR(10) NOT NULL,
	MsgRemark VARCHAR(250) NOT NULL,
	rowguid UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
	CONSTRAINT PK_TMessages PRIMARY KEY (MsgName) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

)
GO

CREATE TABLE TDTestArea (
	TDTestAreaID UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
	TDTestAreaDesc VARCHAR(100) NOT NULL,
	rowguid UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL
)
GO

CREATE TABLE TArea (
	AreaDesc VARCHAR(50) NOT NULL,
	AreaNo UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
	ProductName VARCHAR(20) NOT NULL,
	ProductStyle VARCHAR(50),
	ProductDesc VARCHAR(150),
	rowguid UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
	TraceID INT,
	CONSTRAINT PK_TArea PRIMARY KEY (AreaDesc, ProductName) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

)
GO

CREATE TABLE History (
	VersionID INT NOT NULL,
	Table_ VARCHAR(40),
	TableKey INT,
	ModifiedBy VARCHAR(40),
	History TEXT,
	TimeStamp DATETIME,
	Version TEXT,
	rowguid UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
	CONSTRAINT PK_History PRIMARY KEY (VersionID) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

)
GO

CREATE TABLE Task_Station_Status (
	StationID INT,
	PcName VARCHAR(100),
	TaskID INT,
	Wk INT,
	Status INT
)
GO

CREATE TABLE StationEvent (
	StationID INT,
	EventTime DATETIME DEFAULT (getdate()),
	EventType INT,
	Details VARCHAR(500)
)
GO

CREATE TABLE Result (
	ResultID INT NOT NULL,
	ConditionID INT,
	LimitID INT,
	Result FLOAT(53),
	ResultAsText VARCHAR(250),
	TimeStamp DATETIME,
	StepID UNIQUEIDENTIFIER,
	TD_TC_Instance INT,
	TD_TC_ID INT,
	CONSTRAINT PK_Result PRIMARY KEY (ResultID) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

)
GO

CREATE TABLE TLanguage (
	LanguageID INT NOT NULL,
	Lang VARCHAR(50),
	LangType VARCHAR(50),
	AreaNo UNIQUEIDENTIFIER NOT NULL,
	rowguid UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
	CONSTRAINT PK_TLanguage PRIMARY KEY (LanguageID, AreaNo) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

)
GO

CREATE TABLE TraceData (
	TraceID INT NOT NULL,
	Name VARCHAR(50) NOT NULL,
	Desc VARCHAR(255) NOT NULL,
	TraceObj IMAGE NOT NULL,
	CONSTRAINT PK_TraceData PRIMARY KEY (TraceID) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

)
GO

CREATE TABLE Limit (
	LimitID INT NOT NULL,
	FileID INT,
	MeasGroup VARCHAR(200),
	TestType VARCHAR(200),
	TestNo INT,
	Text VARCHAR(200),
	Unit VARCHAR(50),
	Norm VARCHAR(255),
	Low FLOAT(53),
	High FLOAT(53),
	Low2 FLOAT(53),
	High2 FLOAT(53),
	Low3 FLOAT(53),
	High3 FLOAT(53),
	CONSTRAINT PK_Limit PRIMARY KEY (LimitID) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

)
GO

CREATE TABLE TCase (
	CaseNo SMALLINT NOT NULL,
	CaseDesc VARCHAR(50) NOT NULL,
	InitCMDs TEXT,
	CleanupCMDs TEXT,
	CaseRem TEXT,
	FixedSeq BIT,
	CaseID UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
	GroupID UNIQUEIDENTIFIER NOT NULL,
	SubFeatureID UNIQUEIDENTIFIER NOT NULL,
	CaseStatus VARCHAR(50) NOT NULL,
	TestCoverage VARCHAR(50) NOT NULL,
	UCaseID VARCHAR(50),
	rowguid UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
	TraceID INT,
	CONSTRAINT PK_TCase PRIMARY KEY (GroupID, CaseNo) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

)
GO

CREATE TABLE OperationRecords (
	OperationType VARCHAR(50),
	UserID VARCHAR(50),
	Details VARCHAR(2000),
	OperationTime DATETIME DEFAULT (getdate())
)
GO

CREATE TABLE SysSetup (
	SysSetup VARCHAR(250) NOT NULL,
	InspectTool TEXT,
	AreaNo UNIQUEIDENTIFIER NOT NULL,
	SysSetupID UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
	Coordinates TEXT,
	rowguid UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
	CONSTRAINT PK_SysSetup PRIMARY KEY (SysSetup, AreaNo) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

)
GO

CREATE TABLE RefObject (
	RefObjID INT NOT NULL,
	DUTType VARCHAR(255),
	LanguageID INT,
	RefObj IMAGE,
	RefFormat VARCHAR(50),
	AStatus VARCHAR(20),
	ApprovedBy VARCHAR(50),
	ATimeStamp DATETIME,
	ShotAtCondID INT,
	RefRem VARCHAR(255),
	RunMode VARCHAR(50),
	SpecID UNIQUEIDENTIFIER,
	AreaNo UNIQUEIDENTIFIER,
	CONSTRAINT PK_RefObject PRIMARY KEY (RefObjID) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

)
GO

CREATE TABLE AuxCondition (
	ConditionID INT NOT NULL,
	Usernotes VARCHAR(255),
	RunMode VARCHAR(25),
	Feature VARCHAR(25),
	Recipe VARCHAR(25),
	CONSTRAINT PK_AuxCondition PRIMARY KEY (ConditionID) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

)
GO

CREATE TABLE TestCoverage (
	TCoverage VARCHAR(50) NOT NULL,
	rowguid UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
	CONSTRAINT PK_TestCoverage PRIMARY KEY (TCoverage) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

)
GO

CREATE TABLE Products (
	ProductName VARCHAR(50) NOT NULL,
	Invalid INT DEFAULT ((0)),
	CreateTime null DEFAULT (getdate()),
	CONSTRAINT PK__Products__DD5A978B79E80B25 PRIMARY KEY (ProductName) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

)
GO

CREATE TABLE Macro (
	MacroID VARCHAR(150) NOT NULL,
	LanguageID INT NOT NULL,
	DUTType VARCHAR(20) NOT NULL,
	MCMD TEXT,
	MacroRem TEXT,
	ResultState VARCHAR(50),
	AreaNo UNIQUEIDENTIFIER NOT NULL,
	TimeStamp DATETIME,
	rowguid UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
	CONSTRAINT PK_Macro PRIMARY KEY (MacroID, LanguageID, AreaNo, DUTType) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

)
GO

CREATE TABLE Device (
	DeviceID INT NOT NULL,
	SystemID INT,
	SerialNo VARCHAR(50),
	DUTType VARCHAR(255),
	SWVer VARCHAR(255),
	HWver VARCHAR(255),
	CONSTRAINT PK_Device PRIMARY KEY (DeviceID) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

)
GO

CREATE TABLE Defaults (
	DefaultID INT NOT NULL,
	Phone_Type VARCHAR(50),
	CONSTRAINT PK_Defaults PRIMARY KEY (DefaultID) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

)
GO

CREATE TABLE TGroup (
	GroupNo INT NOT NULL,
	GroupDesc VARCHAR(50) NOT NULL,
	GroupRem TEXT,
	SIMReq TEXT,
	UserSet TEXT,
	Config TEXT,
	Feature VARCHAR(25) NOT NULL,
	Recipe VARCHAR(25) NOT NULL,
	GroupStatus VARCHAR(50),
	CanRunRapt INT,
	CanRunLight INT,
	UserName VARCHAR(50),
	LastUpDate DATETIME,
	AreaNo UNIQUEIDENTIFIER NOT NULL,
	GroupID UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
	rowguid UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
	TraceID INT,
	CONSTRAINT PK_TGroup PRIMARY KEY (GroupNo, AreaNo) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

)
GO

CREATE TABLE Stations (
	StationID INT NOT NULL,
	PcName VARCHAR(100),
	Status INT,
	CONSTRAINT PK__Stations__E0D8A6DD0A1E72EE PRIMARY KEY (StationID) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

)
GO

CREATE TABLE EmergencyRecord (
	type VARCHAR(20),
	level VARCHAR(20),
	source VARCHAR(100),
	detail VARCHAR(2000),
	time DATETIME DEFAULT (getdate())
)
GO

CREATE TABLE Design_history (
	id INT NOT NULL,
	Name VARCHAR(50),
	Date DATETIME,
	Description TEXT,
	CONSTRAINT PK_Design_history PRIMARY KEY (id) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

)
GO

CREATE TABLE ResultSyntax (
	ResultID INT NOT NULL,
	ConditionID INT,
	LimitID INT,
	StepID INT,
	Result FLOAT(53),
	ResultAsText VARCHAR(250),
	TimeStamp DATETIME,
	upsize_ts BINARY(8),
	CurrGroup INT,
	CurrCase INT,
	CurrStep INT,
	CONSTRAINT PK_ResultSyntax PRIMARY KEY (ResultID) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

)
GO

CREATE TABLE TestCondition (
	ConditionID INT NOT NULL,
	DeviceID INT,
	DateAndTime DATETIME,
	StatusIRP VARCHAR(50),
	Temperature REAL,
	FixtureID VARCHAR(50),
	LanguageID INT,
	TD_TestSet_ID INT,
	TD_Server TEXT,
	TD_Domain VARCHAR(250),
	TD_Project VARCHAR(250),
	AreaNo UNIQUEIDENTIFIER,
	TD_TestSetName VARCHAR(250),
	CONSTRAINT PK_TestCondition PRIMARY KEY (ConditionID) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

)
GO

CREATE TABLE SubFeatureDesc (
	FeatureDesc VARCHAR(250) NOT NULL,
	SubFeatureID UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
	rowguid UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
	TraceID INT,
	CONSTRAINT PK_SubFeatureDesc PRIMARY KEY (SubFeatureID) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

)
GO

CREATE TABLE InvalidTest (
	DUTType VARCHAR(50),
	LangType VARCHAR(50),
	LanguageID INT,
	GroupNo INT NOT NULL,
	CaseNo INT NOT NULL,
	StepNo INT NOT NULL,
	Remark VARCHAR(250),
	InvalidID UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
	AreaNo UNIQUEIDENTIFIER NOT NULL,
	rowguid UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
	CONSTRAINT PK_InvalidTest PRIMARY KEY (InvalidID) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

)
GO

CREATE TABLE Tasks (
	TaskID INT NOT NULL,
	Product VARCHAR(50),
	StartWk INT,
	EndWk INT,
	Milestone VARCHAR(50),
	Station_count INT,
	Owner VARCHAR(50),
	IsUpdated INT DEFAULT ((0)),
	Status INT DEFAULT ((0)),
	CONSTRAINT PK__Tasks__7C6949D10559BDD1 PRIMARY KEY (TaskID) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

)
GO


SET ANSI_PADDING OFF
GO