idmax.sql

来自「这是一个关于论坛的一点小的源代码」· SQL 代码 · 共 1,958 行 · 第 1/5 页

GO
CREATE PROCEDURE [dbo].[Max_GetLinkedApplications]
AS

SELECT * FROM Max_LinkedApplications
GO
PRINT N'正在创建 [dbo].[Max_GetMedalLogsBySearch]'
GO
CREATE PROCEDURE [dbo].[Max_GetMedalLogsBySearch]
	@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 ='MedalID IN (SELECT MedalID from Max_Medals where MedalName 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_MedalLogs','*',@Condition,'LogID',1
	IF(@Condition<>'')
	EXEC('SELECT COUNT(1) TotalRecord FROM Max_MedalLogs WHERE ' +@Condition) 
	ELSE
	EXEC('SELECT COUNT(1) TotalRecord FROM Max_MedalLogs')
GO
PRINT N'正在创建 [dbo].[Max_GetErrorPasswordLogsBySearch]'
GO
CREATE PROCEDURE [dbo].[Max_GetErrorPasswordLogsBySearch]
	@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)--输入的错误密码
				SET @Condition = 'ErrorPassword like ''%'+@Keyword+'%'''
			ELSE IF(@ActionType=3)--日期
				SET @Condition = 'CreateDate like ''%'+@Keyword+'%'''
		END
	EXECUTE Max_Common_GetRecordsByPage @PageIndex,@PageSize,'Max_ErrorPasswordLogs','*',@Condition,'LogID',1

	IF(@Condition<>'')
	EXEC('SELECT COUNT(1) TotalRecord FROM Max_ErrorPasswordLogs WHERE ' +@Condition) 
	ELSE
	EXEC('SELECT COUNT(1) TotalRecord FROM Max_ErrorPasswordLogs')
GO
PRINT N'正在创建 [dbo].[Max_GetAllIPAddressesToRoles]'
GO
-- =============================================
-- Author:		<sek>
-- Create date: <2007/1/31>
-- Description:	<>
-- =============================================
CREATE PROCEDURE [dbo].[Max_GetAllIPAddressesToRoles]
AS
	SET NOCOUNT ON 
	SELECT * FROM [Max_IPAddressesToRoles]
	RETURN
GO
PRINT N'正在创建 [dbo].[Max_Split_nvarchar]'
GO
CREATE FUNCTION [dbo].[Max_Split_nvarchar]
(
	@text nvarchar(4000),
	@Separator nvarchar(2) = N','
)
RETURNS @ItemTable TABLE 
(
	id int identity(1,1),
	item nvarchar(256) 
)
AS
BEGIN

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

	RETURN;
END
GO
PRINT N'正在创建 [dbo].[Max_Users]'
GO
CREATE TABLE [dbo].[Max_Users]
(
[UserID] [int] NOT NULL IDENTITY(1, 1),
[UserName] [nvarchar] (64) COLLATE Chinese_PRC_CI_AS_WS NOT NULL,
[Password] [nvarchar] (64) COLLATE Chinese_PRC_CI_AS_WS NOT NULL,
[PasswordFormat] [tinyint] NOT NULL,
[Question] [tinyint] NOT NULL,
[Answer] [nvarchar] (128) COLLATE Chinese_PRC_CI_AS_WS NOT NULL,
[AnswerFormat] [tinyint] NOT NULL,
[FailedPasswordAttemptCount] [int] NOT NULL CONSTRAINT [DF_Max_Users_FailedPasswordAttemptCount] DEFAULT ((0)),
[FailedPasswordAttemptWindowStart] [datetime] NOT NULL CONSTRAINT [DF_Max_Users_FailedPasswordAttemptWindowStart] DEFAULT (getdate()),
[FailedAnswerAttemptCount] [int] NOT NULL CONSTRAINT [DF_Max_Users_FailedAnswerAttemptCount] DEFAULT ((0)),
[FailedAnswerAttemptWindowStart] [datetime] NOT NULL CONSTRAINT [DF_Max_Users_FailedAnswerAttemptWindowStart] DEFAULT (getdate()),
[IsActive] [bit] NOT NULL CONSTRAINT [DF_Max_Users_IsActive] DEFAULT ((1))
) ON [PRIMARY]
GO
PRINT N'正在 [dbo].[Max_Users] 上创建主键 [PK_Max_Users]'
GO
ALTER TABLE [dbo].[Max_Users] ADD CONSTRAINT [PK_Max_Users] PRIMARY KEY CLUSTERED  ([UserID]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[Max_AddUserInvite]'
GO
--------- By 帅帅 2007/09/11 ------
CREATE PROCEDURE [dbo].[Max_AddUserInvite]
	@UserIDs varchar(8000), 
	@InvitedCount int
AS
DECLARE @Table_User table(UserID INT,InvitedCount INT,SpareInviteCount INT)
DECLARE @Table_NotUser table(UserID INT,InvitedCount INT,SpareInviteCount INT)
DECLARE @Table table(UserID INT,InvitedCount INT,SpareInviteCount INT)

INSERT @Table
(UserID,InvitedCount,SpareInviteCount)
SELECT U.item,@InvitedCount,@InvitedCount FROM Max_Split_nvarchar(@UserIDs,',') AS U
INNER JOIN Max_Users M--已存在的用户
ON U.item=M.UserID

---如果用户有邀请
INSERT @Table_User
(UserID,InvitedCount,SpareInviteCount)
SELECT U.UserID,U.InvitedCount,U.SpareInviteCount FROM Max_UserInvites AS I 
INNER JOIN @Table AS U 
ON  I.UserID=U.UserID

---如果用户没有邀请
INSERT @Table_NotUser
(UserID,InvitedCount,SpareInviteCount)
SELECT U.UserID,@InvitedCount,@InvitedCount FROM @Table AS U
WHERE U.UserID NOT IN(SELECT UserID FROM @Table_User)


BEGIN TRANSACTION
--如果有邀请，就更新
UPDATE Max_UserInvites SET InvitedCount=InvitedCount+@InvitedCount, SpareInviteCount=SpareInviteCount+@InvitedCount WHERE UserID IN(SELECT UserID FROM @Table_User)

IF @@ERROR<>0
BEGIN
	ROLLBACK TRANSACTION 
END

UPDATE Max_UserInvites SET InvitedCount=0 WHERE InvitedCount<0
IF @@ERROR<>0
BEGIN
	ROLLBACK TRANSACTION 
END
UPDATE Max_UserInvites SET SpareInviteCount=0 WHERE SpareInviteCount<0
IF @@ERROR<>0
BEGIN
	ROLLBACK TRANSACTION 
END
---如果没有邀请就添加
INSERT Max_UserInvites 
	(UserID,InvitedCount,
	 SpareInviteCount)
	SELECT UserID,InvitedCount,
	InvitedCount
	FROM @Table_NotUser
IF @@ERROR<>0
BEGIN
	ROLLBACK TRANSACTION 
END
COMMIT TRANSACTION
RETURN (0)
GO
PRINT N'正在创建 [dbo].[Max_Roles]'
GO
CREATE TABLE [dbo].[Max_Roles]
(
[RoleID] [int] NOT NULL IDENTITY(1, 1),
[RoleName] [nvarchar] (256) COLLATE Chinese_PRC_CI_AS_WS NOT NULL,
[InternalName] [nvarchar] (512) COLLATE Chinese_PRC_CI_AS_WS NOT NULL CONSTRAINT [DF_Max_Roles_InternalName] DEFAULT (''),
[IsHidden] [bit] NOT NULL CONSTRAINT [DF_Max_Roles_IsHidden] DEFAULT ((0)),
[LogoUrl] [nvarchar] (512) COLLATE Chinese_PRC_CI_AS_WS NOT NULL CONSTRAINT [DF_Max_Roles_LogoUrl] DEFAULT (''),
[DisplayColor] [nvarchar] (16) COLLATE Chinese_PRC_CI_AS_WS NOT NULL CONSTRAINT [DF_Max_Roles__DisplayColor] DEFAULT (''),
[SortOrder] [int] NOT NULL CONSTRAINT [DF_Max_Roles_SortOrder] DEFAULT ((0))
) ON [PRIMARY]
GO
PRINT N'正在 [dbo].[Max_Roles] 上创建主键 [PK_Max_Roles]'
GO
ALTER TABLE [dbo].[Max_Roles] ADD CONSTRAINT [PK_Max_Roles] PRIMARY KEY CLUSTERED  ([RoleID]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[Max_Roles] 的索引 [IX_Max_Roles_RoleName]'
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Max_Roles_RoleName] ON [dbo].[Max_Roles] ([RoleName]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[Max_Roles] 的索引 [IX_Max_Roles]'
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Max_Roles] ON [dbo].[Max_Roles] ([SortOrder]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[Max_UsersInRoles]'
GO
CREATE TABLE [dbo].[Max_UsersInRoles]
(
[UserID] [int] NOT NULL,
[RoleID] [int] NOT NULL,
[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_Max_UsersInRoles_CreateDate] DEFAULT (getdate()),
[EnabledDate] [datetime] NOT NULL CONSTRAINT [DF_Max_UsersInRoles_EnabledDate] DEFAULT (getdate()),
[ExpiresDate] [datetime] NOT NULL CONSTRAINT [DF_Max_UsersInRoles_ExpiresDate] DEFAULT ('9999-12-31 23:59:59'),
[IsAutomaticAdd] [bit] NOT NULL CONSTRAINT [DF_Max_UsersInRoles_IsAutomaticAdd] DEFAULT ((1))
) ON [PRIMARY]
GO
PRINT N'正在 [dbo].[Max_UsersInRoles] 上创建主键 [PK_Max_UsersInRoles]'
GO
ALTER TABLE [dbo].[Max_UsersInRoles] ADD CONSTRAINT [PK_Max_UsersInRoles] PRIMARY KEY CLUSTERED  ([UserID], [RoleID]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[Max_UsersInRoles] 的索引 [IX_Max_UsersInRoles_RoleList]'
GO
CREATE NONCLUSTERED INDEX [IX_Max_UsersInRoles_RoleList] ON [dbo].[Max_UsersInRoles] ([RoleID], [CreateDate]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[Max_UsersInRoles] 的索引 [IX_Max_UsersInRoles_ExpiresDate]'
GO
CREATE NONCLUSTERED INDEX [IX_Max_UsersInRoles_ExpiresDate] ON [dbo].[Max_UsersInRoles] ([ExpiresDate]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[Max_SignInLog]'
GO
CREATE TABLE [dbo].[Max_SignInLog]
(
[LogID] [bigint] NOT NULL IDENTITY(1, 1),
[UserID] [int] NOT NULL,
[UserName] [nvarchar] (64) COLLATE Chinese_PRC_CI_AS_WS NOT NULL,
[Password] [nvarchar] (64) COLLATE Chinese_PRC_CI_AS_WS NOT NULL,
[IsUseUserID] [bit] NOT NULL CONSTRAINT [DF_Max_SignInLog_IsUseUserID] DEFAULT ((0)),
[LoginMode] [tinyint] NOT NULL CONSTRAINT [DF_Max_SignInLog_LoginMode] DEFAULT ((0)),
[IPAddress] [varchar] (15) COLLATE Chinese_PRC_CI_AS_WS NOT NULL CONSTRAINT [DF_Max_SignInLog_IPAddress] DEFAULT ('0.0.0.0'),
[UserAgent] [nvarchar] (128) COLLATE Chinese_PRC_CI_AS_WS NOT NULL CONSTRAINT [DF_Max_SignInLog_UserAgent] DEFAULT (''),
[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_Max_SignInLog_CreateDate] DEFAULT (getdate())
) ON [PRIMARY]
GO
PRINT N'正在 [dbo].[Max_SignInLog] 上创建主键 [PK_Max_SignInLog]'
GO
ALTER TABLE [dbo].[Max_SignInLog] ADD CONSTRAINT [PK_Max_SignInLog] PRIMARY KEY CLUSTERED  ([LogID]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[Max_InviteSerials]'
GO
CREATE TABLE [dbo].[Max_InviteSerials]
(
[Serial] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Max_InviteSerials_Serial] DEFAULT (newid()),
[UserID] [int] NOT NULL,
[ToEmail] [nvarchar] (128) NULL,
[ToUserID] [int] NOT NULL CONSTRAINT [DF_Max_InviteSerials_ToUserID] DEFAULT ((0)),
[ToUserNickName] [nvarchar] (64) COLLATE Chinese_PRC_CI_AS_WS NOT NULL CONSTRAINT [DF_Max_InviteSerials_ToUserNickName] DEFAULT (N''),
[IsAccept] [bit] NULL,
[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_Max_InviteSerials_CreateDate] DEFAULT (getdate()),
[ExpiresDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
PRINT N'正在 [dbo].[Max_InviteSerials] 上创建主键 [PK_Max_InviteSerials]'
GO
ALTER TABLE [dbo].[Max_InviteSerials] ADD CONSTRAINT [PK_Max_InviteSerials] PRIMARY KEY CLUSTERED  ([Serial]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[Max_InviteSerials] 的索引 [IX_Max_InviteSerials_Expires]'
GO
CREATE NONCLUSTERED INDEX [IX_Max_InviteSerials_Expires] ON [dbo].[Max_InviteSerials] ([ExpiresDate]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[Max_ValidateCodes]'
GO
CREATE TABLE [dbo].[Max_ValidateCodes]
(
[CodeID] [int] NOT NULL IDENTITY(1, 1),
[CodeName] [nvarchar] (64) COLLATE Chinese_PRC_CI_AS_WS NOT NULL,
[CodeLanguage] [tinyint] NOT NULL,
[CodeContent] [ntext] COLLATE Chinese_PRC_CI_AS_WS NOT NULL,
[Copyright] [nvarchar] (256) COLLATE Chinese_PRC_CI_AS_WS NOT NULL,
[SortOrder] [int] NOT NULL CONSTRAINT [DF_Max_ValidateCodes_difficulty] DEFAULT ((0))
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
PRINT N'正在 [dbo].[Max_ValidateCodes] 上创建主键 [PK_Max_ValidateCodes]'
GO
ALTER TABLE [dbo].[Max_ValidateCodes] ADD CONSTRAINT [PK_Max_ValidateCodes] PRIMARY KEY CLUSTERED  ([CodeID]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[Max_CreateValidateCode]'
GO
CREATE PROCEDURE [dbo].[Max_CreateValidateCode]
	@CodeName NVARCHAR(64),
	@CodeLanguage TINYINT,
	@CodeContent ntext,
	@CopyRight NVARCHAR(256),
	@CodeID INT OUTPUT--输出参数，供程序使用
	
AS
DECLARE @SortOrder INT
EXECUTE Max_Common_GetSortOrder 'Max_ValidateCodes','',@SortOrder output
INSERT INTO Max_ValidateCodes(CodeName,CodeLanguage,CodeContent,Copyright,SortOrder)
VALUES (@CodeName,@CodeLanguage,@CodeContent,@CopyRight,@SortOrder)
IF @@ROWCOUNT>0
BEGIN
SELECT @CodeID =@@identity
RETURN 0
END
ELSE 
RETURN (-1)
GO
PRINT N'正在创建 [dbo].[Max_DiskFiles]'
GO
CREATE TABLE [dbo].[Max_DiskFiles]
(
[DiskFileID] [int] NOT NULL IDENTITY(1, 1),
[DirectoryID] [int] NOT NULL,
[FileID] [int] NOT NULL,
[FileName] [nvarchar] (256) COLLATE Chinese_PRC_CI_AS_WS NOT NULL,
[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_Max_DiskFiles_CreateDate] DEFAULT (getdate()),
[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_Max_DiskFiles_UpdateDate] DEFAULT (getdate()),
[ExProFormat] [nvarchar] (2048) COLLATE Chinese_PRC_CI_AS_WS NULL
) ON [PRIMARY]
GO
PRINT N'正在 [dbo].[Max_DiskFiles] 上创建主键 [PK_Max_DiskFiles]'
GO
ALTER TABLE [dbo].[Max_DiskFiles] ADD CONSTRAINT [PK_Max_DiskFiles] PRIMARY KEY CLUSTERED  ([DiskFileID]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[Max_DiskFiles] 的索引 [IX_Max_DiskFiles_List]'
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Max_DiskFiles_List] ON [dbo].[Max_DiskFiles] ([DirectoryID], [FileName]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[Max_DiskFiles] 的索引 [IX_Max_DiskFiles_FileID]'
GO
CREATE NONCLUSTERED INDEX [IX_Max_DiskFiles_FileID] ON [dbo].[Max_DiskFiles] ([FileID]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[Max_Messages]'
GO
CREATE TABLE [dbo].[Max_Messages]
(
[UserID] [int] NOT NULL,
[MessageID] [int] NOT NULL IDENTITY(1, 1),
[DirectoryType] [tinyint] NOT NULL,
[OtherUserID] [int] NOT NULL,
[OtherNickName] [nvarchar] (64) COLLATE Chinese_PRC_CI_AS_WS NOT NULL,
[MessageType] [tinyint] NOT NULL CONSTRAINT [DF_Max_Messages_MessageType] DEFAULT ((0)),
[Subject] [nvarchar] (512) COLLATE Chinese_PRC_CI_AS_WS NOT NULL,
[Content] [ntext] COLLATE Chinese_PRC_CI_AS_WS NOT NULL,
[ContentFormat] [tinyint] NOT NULL CONSTRAINT [DF_Max_Messages_ContentFormat] DEFAULT ((10)),
[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_Max_Messages_CreateDate] DEFAULT (getdate())
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
PRINT N'正在 [dbo].[Max_Messages] 上创建主键 [PK_Max_Messages]'
GO
ALTER TABLE [dbo].[Max_Messages] ADD CONSTRAINT [PK_Max_Messages] PRIMARY KEY CLUSTERED  ([UserID], [MessageID]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[Max_Messages] 的索引 [IX_Max_Messages_List]'
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Max_Messages_List] ON [dbo].[Max_Messages] ([UserID], [DirectoryType], [MessageType], [MessageID]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[Max_UserOptions]'
GO
CREATE TABLE [dbo].[Max_UserOptions]
(
[UserID] [int] NOT NULL,
[ThemeID] [nvarchar] (256) COLLATE Chinese_PRC_CI_AS_WS NOT NULL CONSTRAINT [DF_Max_UserOptions_ThemeID] DEFAULT (''),
[LanguageID] [nvarchar] (256) COLLATE Chinese_PRC_CI_AS_WS NOT NULL CONSTRAINT [DF_Max_UserOptions_LanguageID] DEFAULT (''),
[TimeZone] [smallint] NOT NULL CONSTRAINT [DF_Max_UserOptions_TimeZone] DEFAULT ((9999)),
[DateFormat] [smallint] NOT NULL CONSTRAINT [DF_Max_UserOptions_DateFormat] DEFAULT ((-1)),
[TimeFormat] [smallint] NOT NULL CONSTRAINT [DF_Max_UserOptions_TimeFormat] DEFAULT ((-1)),
[MessageSound] [nvarchar] (256) NOT NULL CONSTRAINT [DF_Max_UserOptions_MessageSound] DEFAULT ((-1)),
[ExtendedOptions] [nvarchar] (4000) NULL
) ON [PRIMARY]
GO
PRINT N'正在 [dbo].[Max_UserOptions] 上创建主键 [PK_Max_UserOptions]'
GO
ALTER TABLE [dbo].[Max_UserOptions] ADD CONSTRAINT [PK_Max_UserOptions] PRIMARY KEY CLUSTERED  ([UserID]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[System_Max_Advertisements]'
GO
CREATE TABLE [dbo].[System_Max_Advertisements]
(
[AdvertisementID] [int] NOT NULL IDENTITY(1, 1),
[AdvertisementName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS_WS NOT NULL,
[AdvertisementCatalogID] [int] NOT NULL,
[AdvertisementType] [tinyint] NOT NULL,
[Advert