sqlserver_taskvision_create_db.sql

TaskVision 应用程序展示了用于个人和公司相关任务管理的一个完整的 n 层应用程序。该示例附带了用于运行某个私有任务服务器应用程序的随时可运行的示范客户端、全部客户端源代码

USE [master]

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'TaskVision')
BEGIN
DECLARE @spid smallint
DECLARE @sql varchar(4000)

DECLARE crsr CURSOR FAST_FORWARD FOR
	SELECT spid FROM sysprocesses p INNER JOIN sysdatabases d ON d.[name] = 'TaskVision' AND p.dbid = d.dbid

OPEN crsr
FETCH NEXT FROM crsr INTO @spid

WHILE @@FETCH_STATUS != -1
BEGIN
	SET @sql = 'KILL ' + CAST(@spid AS varchar)
	EXEC(@sql)
	FETCH NEXT FROM crsr INTO @spid
END

CLOSE crsr
DEALLOCATE crsr

DROP DATABASE [TaskVision]
END
GO

CREATE DATABASE [TaskVision]
GO


USE [TaskVision]

CREATE TABLE [Priorities] (
	[PriorityID] int IDENTITY (1, 1) NOT NULL,
	[PriorityText] varchar(50) NOT NULL 
) ON [PRIMARY]

CREATE TABLE [Projects] (
	[ProjectID] int IDENTITY (1, 1) NOT NULL,
	[ProjectName] varchar(20) NOT NULL,
	[ProjectDescription] varchar(100) NOT NULL,
	[IsDeleted] bit NOT NULL,
	[DateCreated] datetime NOT NULL 
) ON [PRIMARY]

CREATE TABLE [Statuses] (
	[StatusID] int IDENTITY (1, 1) NOT NULL,
	[StatusText] varchar(50) NOT NULL 
) ON [PRIMARY]

CREATE TABLE [TaskHistory] (
	[TaskHistoryID] int IDENTITY (1, 1) NOT NULL,
	[TaskID] int NULL,
	[ProjectID] int NULL,
	[ModifiedBy] int NULL,
	[AssignedTo] int NULL,
	[TaskSummary] varchar(70) NULL,
	[TaskDescription] varchar(500) NULL,
	[PriorityID] int NULL,
	[StatusID] int NULL,
	[Progress] int NULL,
	[IsDeleted] bit NULL,
	[DateDue] datetime NULL,
	[DateModified] datetime NULL,
	[DateCreated] datetime NULL 
) ON [PRIMARY]

CREATE TABLE [Tasks] (
	[TaskID] int IDENTITY (1, 1) NOT NULL,
	[ProjectID] int NOT NULL,
	[ModifiedBy] int NOT NULL,
	[AssignedTo] int NOT NULL,
	[TaskSummary] varchar(70) NOT NULL,
	[TaskDescription] varchar(500) NOT NULL,
	[PriorityID] int NOT NULL,
	[StatusID] int NOT NULL,
	[Progress] int NOT NULL,
	[IsDeleted] bit NOT NULL,
	[DateDue] datetime NOT NULL,
	[DateModified] datetime NOT NULL,
	[DateCreated] datetime NOT NULL 
) ON [PRIMARY]

CREATE TABLE [Users] (
	[UserID] int IDENTITY (1, 1) NOT NULL,
	[UserName] varchar(16) NOT NULL,
	[UserPassword] varchar(16) NOT NULL,
	[UserFullName] varchar(50) NOT NULL,
	[UserEmail] varchar(50) NOT NULL,
	[IsAccountLocked] bit NOT NULL,
	[IsAdministrator] bit NOT NULL,
	[DateCreated] datetime NOT NULL 
) ON [PRIMARY]


declare @hostName as varchar(25);
set @hostname = HOST_NAME() + '\ASPNET'

if not exists (select * from master.dbo.syslogins where loginname = @hostname)
	exec sp_grantlogin @hostname

if not exists (select * from dbo.sysusers where name = N'ASPNET' and uid < 16382)
	EXEC sp_grantdbaccess @hostname, N'ASPNET'


ALTER TABLE [Priorities] WITH NOCHECK ADD 
	CONSTRAINT [PK_Priorities] PRIMARY KEY  CLUSTERED 
	(
		[PriorityID]
	)  ON [PRIMARY]

ALTER TABLE [Projects] WITH NOCHECK ADD 
	CONSTRAINT [DF_Projects_IsDeleted] DEFAULT (0) FOR [IsDeleted],
	CONSTRAINT [DF_Projects_DateCreated] DEFAULT (getdate()) FOR [DateCreated],
	CONSTRAINT [PK_Projects] PRIMARY KEY  CLUSTERED 
	(
		[ProjectID]
	)  ON [PRIMARY] 

ALTER TABLE [Statuses] WITH NOCHECK ADD 
	CONSTRAINT [PK_Statuses] PRIMARY KEY  CLUSTERED 
	(
		[StatusID]
	)  ON [PRIMARY]

ALTER TABLE [TaskHistory] WITH NOCHECK ADD 
	CONSTRAINT [PK_TaskHistory] PRIMARY KEY  CLUSTERED 
	(
		[TaskHistoryID]
	)  ON [PRIMARY]

ALTER TABLE [Tasks] WITH NOCHECK ADD 
	CONSTRAINT [DF_Tasks_PercentComplete] DEFAULT (0) FOR [Progress],
	CONSTRAINT [DF_Tasks_Deleted] DEFAULT (0) FOR [IsDeleted],
	CONSTRAINT [DF_ChangeNotes_DateModified] DEFAULT (getdate()) FOR [DateModified],
	CONSTRAINT [DF_ChangeNotes_DateCreated] DEFAULT (getdate()) FOR [DateCreated],
	CONSTRAINT [PK_ChangeNotes] PRIMARY KEY  CLUSTERED 
	(
		[TaskID]
	)  ON [PRIMARY]

ALTER TABLE [Users] WITH NOCHECK ADD 
	CONSTRAINT [DF_Users_Disabled] DEFAULT (0) FOR [IsAccountLocked],
	CONSTRAINT [DF_Users_Administrator] DEFAULT (0) FOR [IsAdministrator],
	CONSTRAINT [DF_Users_DateCreated] DEFAULT (getdate()) FOR [DateCreated],
	CONSTRAINT [PK_Users] PRIMARY KEY  CLUSTERED 
	(
		[UserID]
	)  ON [PRIMARY] ,
	CONSTRAINT [IX_Users] UNIQUE  NONCLUSTERED 
	(
		[UserName]
	)  ON [PRIMARY]

ALTER TABLE [TaskHistory] ADD 
	CONSTRAINT [FK_TaskHistory_Tasks] FOREIGN KEY 
	(
		[TaskID]
	) REFERENCES [dbo].[Tasks] (
		[TaskID]
	) ON DELETE CASCADE 

ALTER TABLE [Tasks] ADD 
	CONSTRAINT [FK_ChangeNotes_Priorities] FOREIGN KEY 
	(
		[PriorityID]
	) REFERENCES [dbo].[Priorities] (
		[PriorityID]
	),
	CONSTRAINT [FK_ChangeNotes_Statuses] FOREIGN KEY 
	(
		[StatusID]
	) REFERENCES [dbo].[Statuses] (
		[StatusID]
	),
	CONSTRAINT [FK_TaskChanges_Projects] FOREIGN KEY 
	(
		[ProjectID]
	) REFERENCES [dbo].[Projects] (
		[ProjectID]
	) ON DELETE CASCADE ,
	CONSTRAINT [FK_Tasks_Users_AssignedTo] FOREIGN KEY 
	(
		[AssignedTo]
	) REFERENCES [dbo].[Users] (
		[UserID]
	),
	CONSTRAINT [FK_Tasks_Users_ModifiedBy] FOREIGN KEY 
	(
		[ModifiedBy]
	) REFERENCES [dbo].[Users] (
		[UserID]
	)
	
GO



CREATE PROCEDURE [AuthenticateUser]
(
@UserName varchar(16),
@Password varchar(16)
)
AS  
SELECT  UserID
FROM    Users
WHERE   (UserName = @UserName AND cast(UserPassword as varbinary) = cast(@Password as varbinary) AND IsAccountLocked = 0)
GO
GRANT  EXECUTE  ON [AuthenticateUser]  TO [ASPNET];
GO


CREATE PROCEDURE [DeleteUser]
(
@UserID int
)
AS
DELETE Users WHERE UserID = @UserID
GO
GRANT  EXECUTE  ON [DeleteUser]  TO [ASPNET];
GO


CREATE PROCEDURE [GetOneTask]
(
@TaskID integer
)
AS
SELECT Tasks.TaskID, Tasks.ProjectID, Tasks.ModifiedBy, Tasks.AssignedTo, Tasks.TaskSummary, Tasks.TaskDescription, Tasks.PriorityID, Tasks.StatusID, Tasks.Progress, Tasks.IsDeleted, Tasks.DateDue, Tasks.DateModified, Tasks.DateCreated, Priorities.PriorityText, Statuses.StatusText, Users.UserFullName As AssignedToText, u.UserFullName As ModifiedByText 
FROM Tasks 
JOIN Priorities ON Tasks.PriorityID = Priorities.PriorityID 
JOIN Users ON Tasks.AssignedTo = Users.UserID 
JOIN Users u ON Tasks.ModifiedBy = u.UserID 
JOIN Statuses ON Tasks.StatusID = Statuses.StatusID 
WHERE Tasks.TaskID = @TaskID And Tasks.IsDeleted = 0
GO
GRANT  EXECUTE  ON [GetOneTask]  TO [ASPNET];
GO


CREATE PROCEDURE [GetPriorities] 
AS
SET NOCOUNT ON;
SELECT PriorityID, PriorityText FROM Priorities
GO