idmax.sql

这是一个关于论坛的一点小的源代码

	SELECT * FROM Max_SystemMessages 
	WHERE  EndTime>getdate() 
	ORDER BY MessageID DESC
ELSE
SELECT TOP 1 * FROM Max_SystemMessages ORDER BY MessageID DESC
GO
PRINT N'正在创建 [dbo].[Max_GetAdminLoginLogsBySearch]'
GO
CREATE PROC [dbo].[Max_GetAdminLoginLogsBySearch]
	@PageIndex int,
	@PageSize int,
	@Keyword nvarchar(64),
	@ActionType tinyint
AS
	DECLARE @Condition nvarchar(4000)
	IF(@Keyword='')
		SET @Condition ='';
	ELSE
		BEGIN
			IF(@ActionType=1)--按用户名或用户昵称
				SET @Condition = 'UserID In (SELECT u.UserID FROM Max_Users u inner join Max_UserProfiles p on u.UserID=p.UserID Where u.UserName like ''%'+@Keyword+'%'' or p.NickName like ''%'+@Keyword+'%'')'
			ELSE IF (@ActionType=2)--用户IP
				SET @Condition = 'UserIP LIKE '''+@Keyword+'%'''
			ELSE IF(@ActionType=3)--输入的错误密码
				SET @Condition = 'ErrorPassword like ''%'+@Keyword+'%'''
			ELSE IF(@ActionType=4)--日期
				SET @Condition = 'CreateDate like ''%'+@Keyword+'%'''
		END
	EXECUTE Max_Common_GetRecordsByPage @PageIndex,@PageSize,'Max_AdminLoginLogs','*',@Condition,'LogID',1

	IF(@Condition<>'')
	EXEC('SELECT COUNT(1) TotalRecord FROM Max_AdminLoginLogs WHERE ' +@Condition) 
	ELSE
	EXEC('SELECT COUNT(1) TotalRecord FROM Max_AdminLoginLogs')
GO
PRINT N'正在创建 [dbo].[Max_GetChangeNickNameLogsBySearch]'
GO
CREATE PROCEDURE [dbo].[Max_GetChangeNickNameLogsBySearch]
	@PageIndex int,
	@PageSize int,
	@Keyword nvarchar(64),
	@ActionType tinyint
AS
	DECLARE @Condition nvarchar(256)
	IF(@Keyword='')
		SET @Condition ='';
	ELSE
		BEGIN
			IF(@ActionType=1)--按用户名或用户昵称
				SET @Condition = 'UserID In (SELECT u.UserID FROM Max_Users u inner join Max_UserProfiles p on u.UserID=p.UserID Where u.UserName like ''%'+@Keyword+'%'' or p.NickName like ''%'+@Keyword+'%'')'
			ELSE IF(@ActionType=2)--更改之前昵称
				SET @Condition ='OldNickName like ''%'+@Keyword+'%'''
			ELSE IF(@ActionType=3)--更改日期
				SET @Condition = 'CreateDate like ''%'+@Keyword+'%'''
		END
	EXECUTE Max_Common_GetRecordsByPage @PageIndex,@PageSize,'Max_ChangeNickNameLogs','*',@Condition,'LogID',1

	IF(@Condition<>'')
	EXEC('SELECT COUNT(1) TotalRecord FROM Max_ChangeNickNameLogs WHERE ' +@Condition) 
	ELSE
	EXEC('SELECT COUNT(1) TotalRecord FROM Max_ChangeNickNameLogs')
GO
PRINT N'正在创建 [dbo].[Max_Split_ntext_long]'
GO
CREATE FUNCTION [dbo].[Max_Split_ntext_long]
(
	@text ntext,
	@Separator nvarchar(2) = N','
)
RETURNS @ItemTable TABLE 
(
	id int identity(1,1),
	item nvarchar(4000) 
)
AS
BEGIN

	DECLARE @s nvarchar(4000), @i int, @j int   
	SELECT @s = SUBSTRING(@text, 1, 4000), @i=1 
	IF (@s = '')
		INSERT @ItemTable VALUES ('')
	ELSE BEGIN  
		WHILE @s <> '' BEGIN

			IF len(@s) = 4000   
				SELECT @j = 4000 - CHARINDEX(@Separator, REVERSE(@s)), @i = @i + @j + 1, @s = LEFT(@s, @j)
			ELSE     
				SELECT @i = @i + 4000, @j = LEN(@s)

			INSERT @ItemTable
			SELECT SUBSTRING(@s, ID ,CHARINDEX(@Separator, @s + @Separator, ID) - ID)   
			FROM Max_Identities_4000 WITH (NOLOCK)
			WHERE ID <= @j + 1 AND CHARINDEX(@Separator, @Separator + @s, ID) - ID = 0   

			SELECT @s = SUBSTRING(@text, @i, 4000)

		END   
	END
	RETURN;
END
GO
PRINT N'正在创建 [dbo].[Max_CreateContactGroup]'
GO
-- =============================================
-- Author:		<sek>
-- Create date: <2006/12/11>
-- Description:	<创建新组>
-- =============================================
CREATE PROCEDURE [dbo].[Max_CreateContactGroup]
	@GroupName nvarchar(64),
	@UserID int
AS
	SET NOCOUNT ON
	IF EXISTS(SELECT * FROM [Max_ContactGroups] where GroupName=@GroupName AND UserID=@UserID)
	   RETURN (3)
	ELSE
	BEGIN
	INSERT INTO [dbo].[Max_ContactGroups] (
	[GroupName],
	[UserID]
) VALUES (
	@GroupName,
	@UserID
)
	RETURN (0)
	END
GO
PRINT N'正在创建 [dbo].[Max_CreateDefaultEmoticon]'
GO
-- =============================================
-- Author:		sek
-- Create date: 2007/5/21
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Max_CreateDefaultEmoticon]
	@Shortcut nvarchar(64), 
	@FileName nvarchar(256)
AS
	SET NOCOUNT ON
	IF EXISTS (SELECT * FROM [Max_DefaultEmoticons] WITH(NOLOCK) WHERE (Shortcut=@Shortcut AND @Shortcut<>'') OR FileName=@FileName)
		RETURN (5)
	
	DECLARE @SortOrder int
	SELECT @SortOrder=MAX(SortOrder) FROM [Max_DefaultEmoticons] WITH(NOLOCK)
	
	IF @SortOrder IS NULL
		SET @SortOrder=0
	
	INSERT INTO [Max_DefaultEmoticons](
	Shortcut,
	FileName,
	SortOrder
	) VALUES (
	@Shortcut,
	@FileName,
	@SortOrder+1
	)
	
	RETURN (0)
GO
PRINT N'正在创建 [dbo].[Max_ChangeNickNameLogs]'
GO
CREATE TABLE [dbo].[Max_ChangeNickNameLogs]
(
[LogID] [int] NOT NULL IDENTITY(1, 1),
[UserID] [int] NOT NULL,
[UserName] [nvarchar] (64) COLLATE Chinese_PRC_CI_AS_WS NOT NULL,
[OldNickName] [nvarchar] (64) COLLATE Chinese_PRC_CI_AS_WS NOT NULL,
[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_Max_ChangeUserNameLogs_CreateDate] DEFAULT (getdate())
) ON [PRIMARY]
GO
PRINT N'正在 [dbo].[Max_ChangeNickNameLogs] 上创建主键 [PK_Max_ChangeNickNameLogs]'
GO
ALTER TABLE [dbo].[Max_ChangeNickNameLogs] ADD CONSTRAINT [PK_Max_ChangeNickNameLogs] PRIMARY KEY CLUSTERED  ([LogID]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[Max_UpdateLinkedApplication]'
GO
CREATE PROCEDURE [dbo].[Max_UpdateLinkedApplication]
	@ApplicationID NVARCHAR(64),
	@ApplicationUrl NVARCHAR(256),
	@ApplicationName NVARCHAR(256)
AS

UPDATE Max_LinkedApplications SET [Url]=@ApplicationUrl,DisplayName=@ApplicationName
WHERE ApplicationID = @ApplicationID
IF @@ROWCOUNT>0
RETURN 0
ELSE 
RETURN -1
GO
PRINT N'正在创建 [dbo].[Max_GetSystemMessage]'
GO
CREATE PROC Max_GetSystemMessage
@MessageID int
AS

SELECT * FROM Max_SystemMessages WHERE MessageID=@MessageID
GO
PRINT N'正在创建 [dbo].[Max_UpdateSystemMessage]'
GO
CREATE PROCEDURE [dbo].[Max_UpdateSystemMessage]
	@Roles NVARCHAR(100),
	@Subject NVARCHAR(512) ,
	@Content NTEXT,
	@CreateDate DATETIME,
	@EndTime DATETIME,
	@MessageID INT
AS
	UPDATE Max_SystemMessages
	SET Roles=@Roles,Subject=@Subject,Content=@Content,
		EndTime=@EndTime 
	WHERE MessageID=@MessageID
	
	IF(@@ROWCOUNT>0)
		RETURN 0
	ELSE
		RETURN -1
GO
PRINT N'正在创建 [dbo].[Max_GetSystemMessagesByPage]'
GO
Create PROCEDURE [dbo].[Max_GetSystemMessagesByPage]
    @PageIndex INT,
    @PageSize INT
AS
	EXECUTE Max_Common_GetRecordsByPage @PageIndex, @PageSize, 'Max_SystemMessages', '*' , '', N'[MessageID]', 1
	--查询当前所在目录的短消息总数
	SELECT COUNT(1) FROM Max_SystemMessages
GO
PRINT N'正在创建 [dbo].[Max_DeleteContactGroup]'
GO
-- =============================================
-- Author:		<sek>
-- Create date: <2006/12/11>
-- Description:	<删除分组>
-- =============================================
CREATE PROCEDURE [dbo].[Max_DeleteContactGroup]
	@GroupID int,
	@UserID int
AS
	SET NOCOUNT ON 
	delete Max_ContactGroups where GroupID = @GroupID AND UserID = @UserID;
		IF @@ROWCOUNT > 0
			RETURN (0);
		ELSE
			RETURN (1);
GO
PRINT N'正在创建 [dbo].[Max_IPAddressesToRoles]'
GO
CREATE TABLE [dbo].[Max_IPAddressesToRoles]
(
[MappedRoleID] [int] NOT NULL,
[IPAddresses] [ntext] COLLATE Chinese_PRC_CI_AS_WS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
PRINT N'正在 [dbo].[Max_IPAddressesToRoles] 上创建主键 [PK_Max_IPAddressesToRoles]'
GO
ALTER TABLE [dbo].[Max_IPAddressesToRoles] ADD CONSTRAINT [PK_Max_IPAddressesToRoles] PRIMARY KEY CLUSTERED  ([MappedRoleID]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[Max_DeleteIPAddressesToRole]'
GO
-- =============================================
-- Author:		<sek>
-- Create date: <2007/1/31>
-- Description:	<>
-- =============================================
CREATE PROCEDURE [dbo].[Max_DeleteIPAddressesToRole]
	@RoleID int
AS
	SET NOCOUNT ON
	DELETE [Max_IPAddressesToRoles] WHERE MappedRoleID=@RoleID
	
	IF @@ROWCOUNT<>0
		RETURN (0)
	ELSE
		RETURN (1)
GO
PRINT N'正在创建 [dbo].[Max_Split_varchar]'
GO
CREATE FUNCTION [dbo].[Max_Split_varchar]
(
	@text varchar(8000),
	@Separator nvarchar(2) = ','
)
RETURNS @ItemTable TABLE 
(
	id int identity(1,1),
	item varchar(256) 
)
AS
BEGIN

	INSERT @ItemTable
	SELECT SUBSTRING(@text, ID ,CHARINDEX(@Separator, @text + @Separator, ID) - ID)   
	FROM Max_Identities_8000 WITH (NOLOCK)
	WHERE ID <= LEN(@text) + 1 AND CHARINDEX(@Separator, @Separator + @text, ID) - ID = 0   

	RETURN;
END
GO
PRINT N'正在创建 [dbo].[Max_CreateOrUpdateIPAddressesToRole]'
GO
-- =============================================
-- Author:		<sek>
-- Create date: <2007/1/31>
-- Description:	<>
-- =============================================
CREATE PROCEDURE [dbo].[Max_CreateOrUpdateIPAddressesToRole]
	@MappedRoleID int,
	@IpAddresses ntext
AS
	SET NOCOUNT ON 
	IF EXISTS (SELECT * FROM [Max_IPAddressesToRoles] WHERE MappedRoleID=@MappedRoleID)
		UPDATE [Max_IPAddressesToRoles] SET IPAddresses=@IpAddresses WHERE MappedRoleID=@MappedRoleID
	ELSE
		INSERT INTO [Max_IPAddressesToRoles](MappedRoleID,IPAddresses) VALUES(@MappedRoleID,@IpAddresses)
	
	IF @@ROWCOUNT>0
		RETURN (0)
	ELSE
		RETURN (-1)
GO
PRINT N'正在创建 [dbo].[Max_GetUserInvite]'
GO
-- =============================================
-- Author:		<zzbird>
-- Create date: <2007/1/15>
-- Description:	<>
-- =============================================
CREATE PROCEDURE [dbo].[Max_GetUserInvite]
	@UserID int
AS
BEGIN

	SET NOCOUNT ON;
	SELECT * FROM [Max_UserInvites] WITH (NOLOCK) WHERE UserID = @UserID;

END
GO
PRINT N'正在创建 [dbo].[Max_GetRoleLogsBySearch]'
GO
CREATE PROCEDURE [dbo].[Max_GetRoleLogsBySearch]
	@PageIndex int,
	@PageSize int,
	@Keyword nvarchar(64),
	@ActionType tinyint
AS
	DECLARE @Condition nvarchar(256)
	IF(@Keyword='')
		SET @Condition ='';
	ELSE
		BEGIN
			IF(@ActionType=1)--按用户名或用户昵称
				SET @Condition = 'UserID In (SELECT u.UserID FROM Max_Users u inner join Max_UserProfiles p on u.UserID=p.UserID Where u.UserName like ''%'+@Keyword+'%'' or p.NickName like ''%'+@Keyword+'%'')'
			ELSE IF(@ActionType=2)--角色名称
				SET @Condition ='RoleID IN (SELECT RoleID from Max_Roles where RoleName like ''%'+@Keyword+'%'')'
			ELSE IF(@ActionType=3)--日期
				SET @Condition = 'CreateDate like ''%'+@Keyword+'%'''
			ELSE IF(@ActionType=4)--IP
				SET @Condition = 'IP like ''%'+@Keyword+'%'''
			ELSE IF (@ActionType=5)--IP
				SET @Condition = 'Handlers In (SELECT u.UserID FROM Max_Users u inner join Max_UserProfiles p on u.UserID=p.UserID Where u.UserName like ''%'+@Keyword+'%'' or p.NickName like ''%'+@Keyword+'%'')'
			
		END
	EXECUTE Max_Common_GetRecordsByPage @PageIndex,@PageSize,'Max_RoleLogs','*',@Condition,'LogID',1
	IF(@Condition<>'')
	EXEC('SELECT COUNT(1) TotalRecord FROM Max_RoleLogs WHERE ' +@Condition) 
	ELSE
	EXEC('SELECT COUNT(1) TotalRecord FROM Max_RoleLogs')
GO
PRINT N'正在创建 [dbo].[Max_GetContactGroups]'
GO
-- =============================================
-- Author:		<sek>
-- Create date: <2006/12/11>
-- Description:	<返回所有分组>
-- =============================================
CREATE PROCEDURE [dbo].[Max_GetContactGroups]
	@UserID int
AS
	SET NOCOUNT ON
	SELECT * FROM [Max_ContactGroups] WHERE UserID=@UserID 
	RETURN
GO
PRINT N'正在创建 [dbo].[System_Max_Permissions]'
GO
CREATE TABLE [dbo].[System_Max_Permissions]
(
[SchemeID] [int] NOT NULL,
[RoleID] [int] NOT NULL,
[PermissionItems] [ntext] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]