?? createtasklistdbsql7.sql
字號:
CREATE DATABASE TaskList
GO
USE TaskList
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TaskListNames]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[TaskListNames] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (255) NOT NULL
)
END
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TaskListUsers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[TaskListUsers] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Username] [varchar] (50) NOT NULL CONSTRAINT [DF_TaskListUsers_Username] DEFAULT (''),
[PasswordHash] [varchar] (50) NOT NULL CONSTRAINT [DF_TaskListUsers_PasswordHash] DEFAULT (''),
[IsManager] [bit] NOT NULL CONSTRAINT [DF_TaskListUsers_IsManager] DEFAULT (0),
[IsAdministrator] [bit] NOT NULL CONSTRAINT [DF_TaskListUsers_IsAdministrator] DEFAULT (0),
[StartupViewOption] [varchar] (20) NOT NULL CONSTRAINT [DF_TaskListUsers_StartupViewOption] DEFAULT ('LastTaskList'),
[StartupViewArgument] [int] NULL ,
[ItemsToDisplay] [varchar] (20) NOT NULL CONSTRAINT [DF_TaskListUsers_ItemsToDisplay] DEFAULT ('All'),
[ShouldNotify] [bit] NOT NULL CONSTRAINT [DF_TaskListUsers_ShouldNotify] DEFAULT (0),
[NotifyPeriod] [int] NOT NULL CONSTRAINT [DF_TaskListUsers_NotifyPeriod] DEFAULT (24),
[LastNotified] [datetime] NOT NULL CONSTRAINT [DF__tasklistu__LastN__095F58DF] DEFAULT (getdate())
)
END
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TaskListItems]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[TaskListItems] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[TaskListID] [int] NOT NULL ,
[OwnerUserID] [int] NOT NULL ,
[CreatedDate] [datetime] NOT NULL ,
[ModifiedDate] [datetime] NOT NULL ,
[Subject] [varchar] (255) NULL ,
[Body] [varchar] (5000) NULL ,
[Status] [int] NOT NULL CONSTRAINT [DF_TaskListItems_Status] DEFAULT (0),
[CategoryID] [int] NULL ,
)
END
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TaskListCategories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[TaskListCategories] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Description] [varchar] (50) NOT NULL ,
[IconUrl] [varchar] (500) NULL
)
END
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TaskListStatusShowPreferences]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[TaskListStatusShowPreferences] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [int] NOT NULL ,
[StatusID] [int] NOT NULL
)
END
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TaskListAssignments]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[TaskListAssignments] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [int] NOT NULL ,
[TaskListID] [int] NOT NULL ,
)
END
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TaskListStatus]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[TaskListStatus] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[StatusDescription] [varchar] (50) NOT NULL CONSTRAINT [DF_TaskListStatus_StatusDescription] DEFAULT (''),
[StatusValue] [int] NOT NULL CONSTRAINT [DF_TaskListStatus_StatusValue] DEFAULT (0),
[StatusFontFlags] [varchar] (500) NOT NULL CONSTRAINT [DF_TaskListStatus_StatusFontFlags] DEFAULT (''),
[DefaultSortOrder] [int] NOT NULL CONSTRAINT [DF_TaskListStatus_DefaultSortOrder] DEFAULT (0)
)
END
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TaskListSortOrderPreferences]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[TaskListSortOrderPreferences] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [int] NOT NULL ,
[StatusID] [int] NOT NULL ,
[SortOrder] [int] NOT NULL
)
END
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TaskListNotification]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[TaskListNotification] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[NotificationEmail] [varchar] (5000) NOT NULL
)
END
GO
-- =============================================
-- TaskList_Add
-- =============================================
-- First checks to see if the user specified is valid, then
-- adds a Task List Item to the TaskListItems table.
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'TaskList_Add'
AND type = 'P')
DROP PROCEDURE TaskList_Add
GO
CREATE PROCEDURE TaskList_Add
@Username VarChar(50),
@PasswordHash VarChar(50),
@TaskListID int,
@Subject VarChar(255),
@Body VarChar(5000),
@CreatedDate DateTime,
@ModifiedDate DateTime,
@Status Int,
@CategoryID int
AS
DECLARE @CurrentUserID int
SET @CurrentUserID = NULL
-- Check to make sure this is a valid user, and that they have access to this task list.
SELECT @CurrentUserID=TaskListUsers.ID
FROM TaskListUsers
LEFT JOIN TaskListAssignments
ON TaskListAssignments.UserID = TaskListUsers.ID AND TaskListAssignments.TaskListID = @TaskListID
WHERE Username = @Username AND PasswordHash = @PasswordHash AND TaskListAssignments.ID IS NOT NULL;
IF (@CurrentUserID IS NULL) RETURN 1;
INSERT INTO TaskListItems
(TaskListID, OwnerUserID, Subject, Body, CreatedDate, ModifiedDate, Status, CategoryID)
VALUES
(@TaskListID, @CurrentUserID, @Subject, @Body, @CreatedDate, @ModifiedDate, @Status, @CategoryID);
IF @@Error <> 0 RETURN 1;
GO
-- =============================================
-- Tasklist_AddCategory
-- =============================================
-- creating the store procedure
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'Tasklist_AddCategory'
AND type = 'P')
DROP PROCEDURE Tasklist_AddCategory
GO
CREATE PROCEDURE Tasklist_AddCategory
@Description VarChar(25),
@IconUrl VarChar(500)
AS
INSERT INTO TaskListCategories(Description, IconUrl) VALUES (@Description, @IconUrl)
RETURN @@Identity;
GO
-- =============================================
-- TaskList_ChangeStartupTaskListID
-- =============================================
-- Sets the default task list to display when the user logs in
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'TaskList_ChangeStartupTaskListID'
AND type = 'P')
DROP PROCEDURE TaskList_ChangeStartupTaskListID
GO
CREATE PROCEDURE TaskList_ChangeStartupTaskListID
@Username VarChar(20),
@PasswordHash VarChar(50),
@TaskListID int
AS
DECLARE @CurrentUserID int
SET @CurrentUserID = null
SELECT @CurrentUserID = ID FROM TaskListUsers WHERE Username = @Username AND PasswordHash = @PasswordHash
IF (@CurrentUserID IS NULL) Return 1;
UPDATE TaskListUsers SET StartupViewArgument = @TaskListID WHERE ID = @CurrentUserID
GO
-- =============================================
-- TaskList_AddStatusShowPreference
-- =============================================
-- Adds a status id to the list of ids that are acceptable to show for this user
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'TaskList_AddStatusShowPreference'
AND type = 'P')
DROP PROCEDURE TaskList_AddStatusShowPreference
GO
CREATE PROCEDURE TaskList_AddStatusShowPreference
@Username VarChar(50),
@PasswordHash VarChar(50),
@StatusID int
AS
DECLARE @CurrentUserID int, @StatusCount int
SET @CurrentUserID = null;
SET @StatusCount = 0;
--Check to make sure this is a valid user
SELECT @CurrentUserID = ID FROM TaskListUsers WHERE Username = @Username AND PasswordHash = @PasswordHash
IF (@CurrentUserID IS NULL) Return 1;
--Check to make sure this status isn't already on there
SELECT @StatusCount = Count(ID) FROM TaskListStatusShowPreferences
WHERE UserID = @CurrentUserID AND StatusID = @StatusID
IF (@StatusCount > 0) Return 1;
--Insert the status item
INSERT INTO TaskListStatusShowPreferences (UserID, StatusID)
VALUES (@CurrentUserID, @StatusID)
GO
-- =============================================
-- TaskList_AddStatusType
-- =============================================
-- Adds a status type to the database
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'TaskList_AddStatusType'
AND type = 'P')
DROP PROCEDURE TaskList_AddStatusType
GO
CREATE PROCEDURE TaskList_AddStatusType
@Description VarChar(25),
@StatusFontFlags VarChar(500)
AS
DECLARE @SortOrder Integer
Select @SortOrder = Max(DefaultSortOrder) FROM TaskListStatus;
SET @SortOrder = @SortOrder + 1;
INSERT INTO TaskListStatus
(StatusDescription, StatusFontFlags, DefaultSortOrder)
VALUES
(@Description, @StatusFontFlags, @SortOrder)
RETURN @@Identity;
Go
-- =============================================
-- TaskList_AddUser
-- =============================================
-- Creates a new user
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'TaskList_AddUser'
AND type = 'P')
DROP PROCEDURE TaskList_AddUser
GO
CREATE PROCEDURE TaskList_AddUser
@Username VarChar(20),
@PasswordHash VarChar(50),
@NewUserName VarChar(20),
@NewPasswordHash VarChar(50),
@IsManager Bit,
@IsAdministrator Bit
AS
DECLARE @CurrentUserID int, @NewUserID int
SET @CurrentUserID = NULL
SELECT @CurrentUserID = ID
FROM TaskListUsers
WHERE Username = @Username AND PasswordHash = @PasswordHash AND IsAdministrator = 1;
IF (@CurrentUserID IS NULL)
RETURN 1; --Invalid user
Begin Transaction CreateUser;
INSERT INTO TaskListUsers (Username, PasswordHash, IsManager, IsAdministrator)
VALUES (@NewUserName, @NewPasswordHash, @IsManager, @IsAdministrator);
IF @@Error <> 0 BEGIN Rollback Transaction CreateUser; Return 1; END
SET @NewUserID = @@Identity;
INSERT INTO TaskListSortOrderPreferences (UserID, StatusID, SortOrder)
SELECT @NewUserID, TaskListStatus.ID, TaskListStatus.DefaultSortOrder
FROM TaskListStatus
IF @@Error <> 0 BEGIN Rollback Transaction CreateUser; Return 1; END
INSERT INTO TaskListStatusShowPreferences (UserID, StatusID)
SELECT UserID, StatusID
FROM TaskListSortOrderPreferences
WHERE UserID = @NewUserID
IF @@Error <> 0 BEGIN Rollback Transaction CreateUser; Return 1; END
Commit Transaction CreateUser;
GO
-- =============================================
-- TaskList_AddUserToTaskList
-- =============================================
-- Adds a user to the TaskListAssignments table, granting them permission to see a task list
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'TaskList_AddUserToTaskList'
AND type = 'P')
DROP PROCEDURE TaskList_AddUserToTaskList
GO
CREATE PROCEDURE TaskList_AddUserToTaskList
@Username VarChar(20),
@PasswordHash VarChar(50),
@UserID int,
@TaskListID int
AS
DECLARE @CurrentUserID int, @ExistingUserID int
SET @CurrentUserID = NULL
SET @ExistingUserID = NULL
--First, check to make sure this is a valid user, and that they have manager priviledges
SELECT @CurrentUserID = ID FROM TaskListUsers
WHERE Username = @Username AND PasswordHash = @PasswordHash AND IsManager = 1;
IF (@CurrentUserID IS NULL) Return 1;
--Make sure that this user isn't already in the TaskListAssignments table
SELECT @ExistingUserID = UserID FROM TaskListAssignments WHERE UserID = @UserID AND TaskListID = @TaskListID
IF (@ExistingUserID IS NOT NULL) Return 1; --User is already in
--Finally, add the user to the TaskListAssignments table
INSERT INTO TaskListAssignments (UserID, TaskListID) VALUES (@UserID, @TaskListID);
IF @@Error <> 0 Return 1;
GO
-- =============================================
-- TaskList_ChangeStartupTaskListID
-- =============================================
-- Sets the default task list to display when the user logs in
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'TaskList_ChangeStartupTaskListID'
AND type = 'P')
DROP PROCEDURE TaskList_ChangeStartupTaskListID
GO
CREATE PROCEDURE TaskList_ChangeStartupTaskListID
@Username VarChar(20),
@PasswordHash VarChar(50),
@TaskListID int
AS
DECLARE @CurrentUserID int
SET @CurrentUserID = null
--Check to make sure this is a valid user, and that they have access to this task list
SELECT @CurrentUserID = TaskListUsers.ID
FROM TaskListUsers
LEFT JOIN TaskListAssignments AS Ta ON Ta.UserID = TaskListUsers.ID AND Ta.TaskListID = @TaskListID
WHERE Username = @Username AND PasswordHash = @PasswordHash AND Ta.ID IS NOT NULL;
IF (@CurrentUserID IS NULL) Return 1;
UPDATE TaskListUsers SET StartupViewArgument = @TaskListID WHERE ID = @CurrentUserID
GO
-- =============================================
-- TaskList_ChangeStartupViewOption
-- =============================================
-- Changes the method the application uses to decide what
-- task list to show the user when they first log in
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'TaskList_ChangeStartupViewOption'
AND type = 'P')
DROP PROCEDURE TaskList_ChangeStartupViewOption
GO
CREATE PROCEDURE TaskList_ChangeStartupViewOption
@Username VarChar(20),
@PasswordHash VarChar(50),
@StartupViewOption VarChar(50)
AS
DECLARE @CurrentUserID int
SET @CurrentUserID = NULL
SELECT @CurrentUserID = ID FROM TaskListUsers WHERE Username = @Username AND PasswordHash = @PasswordHash
IF (@CurrentUserID IS NULL) Return 1;
IF @StartupViewOption = 'LastTaskList'
UPDATE TaskListUsers SET StartupViewOption = 'LastTaskList' WHERE ID = @CurrentUserID
ELSE
UPDATE TaskListUsers SET StartupViewOption = 'SpecificTaskList' WHERE ID = @CurrentUserID
GO
-- =============================================
-- TaskList_ClearStatusShowPreferences
-- =============================================
-- Clears the list of status id's to show for this user
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'TaskList_ClearStatusShowPreferences'
AND type = 'P')
DROP PROCEDURE TaskList_ClearStatusShowPreferences
GO
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -