收发信息数据库以及操作

/**/ /*
建表和建存储过程
*/

-- 用户表
CREATE   TABLE   [ t_User ]
(
[ userID ]   INT   IDENTITY ( 1 , 1 ), -- 自动增长
[ userAccount ]   VARCHAR ( 15 ), -- 用户账号,主键
[ userPassword ]   CHAR ( 32 ), -- 用户密码,最好经过加密，比如流行的MD5
[ userName ]   NVARCHAR ( 5 ) -- 用户真实姓名
--   ..其他信息
PRIMARY   KEY ( [ userID ] ),
UNIQUE ( [ userAccount ] )
)  ON   [ PRIMARY ]
-- 消息表
CREATE   TABLE   [ t_Message ]
(
[ messageID ]   BIGINT   IDENTITY ( 1 , 1 ), -- 编号,主键,自动增长
[ messagePostUserID ]   INT , -- 发消息人,外键关联t_User表的UserID
[ messageReceiveUserID ]   INT , -- 收消息人,外键关联t_User表的UserID
[ messageTitle ]   NVARCHAR ( 25 ), -- 消息标题
[ messageBody ]   NTEXT , -- 消息主体，该字段类型视消息主体内容大小而定，如果特别大，则设为NTEXT,否则用NVARCHAR即可
[ messageTime ]   DATETIME   DEFAULT   GETDATE (), -- 发消息时间,默认值为当前系统的时间
[ isRead ]   BIT   DEFAULT   0 , -- 收消息人是否阅读过
[ isPostUserDelete ]   BIT   DEFAULT   0 , -- 发消息人是否删除
[ isReceiveUserDelete ]   BIT   DEFAULT   0 -- 收消息人是否删除
PRIMARY   KEY ( [ messageID ] ),
FOREIGN   KEY ( [ messagePostUserID ] )  REFERENCES   [ t_User ] ( [ userID ] ),
FOREIGN   KEY ( [ messageReceiveUserID ] )  REFERENCES   [ t_User ] ( [ userID ] )
)  ON   [ PRIMARY ]
-- 附件表
CREATE   TABLE   [ t_Message_Attachment ]
(
[ attachmentID ]   BIGINT   IDENTITY ( 1 , 1 ), -- 编号，主键,自动增长
[ messageID ]   BIGINT , -- 外键，关联消息表中的messageID
[ AttachFile ]   BINARY -- 附件（需转换为二进制）
FOREIGN   KEY ( [ messageID ] )  REFERENCES   [ t_Message ] ( [ messageID ] )  ON   DELETE   CASCADE
)  ON   [ PRIMARY ]
GO

-- 用户查看自己所发消息列表的存储过程
CREATE   PROCEDURE  usp_ViewMessageListByPostUser
(
@userAccount   VARCHAR ( 15 ) -- 用户账号
)
AS
BEGIN
SELECT   [ m ] . [ messageID ] , [ m ] . [ messageTitle ] , [ m ] . [ messageTime ] , [ m ] . [ isRead ] , [ m ] . [ messagePostUserID ] , [ u2 ] . [ userName ]
FROM   [ t_Message ]   [ m ]   JOIN   [ t_User ]   [ u ]   ON   [ m ] . [ messagePostUserID ] = [ u ] . [ userID ]
LEFT   JOIN   [ t_User ]   [ u2 ]   ON   [ m ] . [ messageReceiveUserID ] = [ u2 ] . [ userID ]
WHERE   [ u ] . [ userAccount ] = @userAccount   AND   [ m ] . [ isPostUserDelete ] = 0
END
GO
-- 用户查看自己所收消息列表的存储过程
CREATE   PROCEDURE  usp_ViewMessageListByReceiveUser
(
@userAccount   VARCHAR ( 15 ) -- 用户账号
)
AS
BEGIN
SELECT   [ m ] . [ messageID ] , [ m ] . [ messageTitle ] , [ m ] . [ messageTime ] , [ m ] . [ isRead ] , [ m ] . [ messagePostUserID ] , [ u2 ] . [ userName ]
FROM   [ t_Message ]   [ m ]   JOIN   [ t_User ]   [ u ]   ON   [ m ] . [ messageReceiveUserID ] = [ u ] . [ userID ]
LEFT   JOIN   [ t_User ]   [ u2 ]   ON   [ m ] . [ messagePostUserID ] = [ u2 ] . [ userID ]
WHERE   [ u ] . [ userAccount ] = @userAccount   AND   [ m ] . [ isReceiveUserDelete ] = 0
END
GO
-- 查看消息具体内容(不包括附件)存储过程
CREATE   PROCEDURE  usp_ViewMessageDetail
(
@messageID   BIGINT , -- 消息编号
@isReceiveUser   BIT = 0 -- 是否收件人阅读
)
AS
BEGIN
SET  XACT_ABORT  ON
BEGIN   TRAN -- 采用事务操作
IF   @isReceiveUser = 1   AND   EXISTS ( SELECT   1   FROM   [ t_Message ]   WHERE   [ messageID ] = @messageID   AND   [ isRead ] = 0 )
    BEGIN
        UPDATE   [ t_Message ]   SET  isRead = 1   WHERE   [ messageID ] = @messageID   AND   [ isRead ] = 0
    END
SELECT   [ m ] . * , [ u ] . [ userName ]   FROM   [ t_Message ]   [ m ]
LEFT   JOIN   [ t_User ]   [ u ]   ON   CASE   @isReceiveUser   WHEN   0   THEN   [ m ] . [ messagePostUserID ]   ELSE   [ m ] . [ messageReceiveUserID ]   END = [ u ] . [ userID ]
WHERE   [ m ] . [ messageID ] = @messageID
COMMIT   TRAN
SET  XACT_ABORT  OFF
END
GO
-- 查看消息附件
CREATE   PROCEDURE  usp_ViewMessageAttachment
(
@messageID   BIGINT -- 消息编号
)
AS
BEGIN
SELECT   *   FROM   [ t_Message_Attachment ]   WHERE   [ messageID ] = @messageID   ORDER   BY  attachmentID  ASC
END
GO

/**/ /*
使用操作
*/
-- 插入用户A和B
INSERT   INTO   [ t_User ] ( [ userAccount ] , [ userPassword ] , [ userName ] )
SELECT   ' A ' , ' E10ADC3949BA59ABBE56E057F20F883E ' , ' 张三 '   UNION   ALL
SELECT   ' B ' , ' E10ADC3949BA59ABBE56E057F20F883E ' , ' 李四 '   UNION   ALL
SELECT   ' C ' , ' E10ADC3949BA59ABBE56E057F20F883E ' , ' 王五 '

-- 插入条消息
INSERT   INTO   [ t_Message ] ( [ messagePostUserID ] , [ messageReceiveUserID ] , [ messageTitle ] , [ messageBody ] )
VALUES ( 1 , 2 , ' 你好 ' , ' 好久不见，近来可好 ' ) -- A给B发消息
INSERT   INTO   [ t_Message ] ( [ messagePostUserID ] , [ messageReceiveUserID ] , [ messageTitle ] , [ messageBody ] )
VALUES ( 2 , 1 , ' Re:你好 ' , ' 是啊，好久不见，凑合活呗 ' ) -- B回消息给A
INSERT   INTO   [ t_Message ] ( [ messagePostUserID ] , [ messageReceiveUserID ] , [ messageTitle ] , [ messageBody ] )
VALUES ( 1 , 2 , ' 再次发送 ' , ' 好久不见，看来小日子挺滋润的哦 ' ) -- A又发了一条给B
INSERT   INTO   [ t_Message ] ( [ messagePostUserID ] , [ messageReceiveUserID ] , [ messageTitle ] , [ messageBody ] )
VALUES ( 3 , 2 , ' 老兄你好 ' , ' 我是王五啊 ' ) -- C给B发了一条
INSERT   INTO   [ t_Message ] ( [ messagePostUserID ] , [ messageReceiveUserID ] , [ messageTitle ] , [ messageBody ] )
VALUES ( 1 , 3 , ' 我是张三 ' , ' 你是王五吗？我是张三啊 ' ) -- A给C发了一条


-- A用户查询自己发消息列表
EXEC  usp_ViewMessageListByPostUser  ' A '
-- B用户查询自己收到消息列表
EXEC  usp_ViewMessageListByReceiveUser  ' B '
-- A用户查询编号为1的消息以及附件
EXEC  usp_ViewMessageDetail  1 , 0
EXEC  usp_ViewMessageAttachment  1
-- b用户查询编号为1的消息以及附件
EXEC  usp_ViewMessageDetail  1 , 1
EXEC  usp_ViewMessageAttachment  1


-- A用户再次查询自己发消息列表
EXEC  usp_ViewMessageListByPostUser  ' A '


/**/ /*
结束操作，删除表和存储过程
*/
DROP   PROCEDURE   [ usp_ViewMessageListByPostUser ]
DROP   PROCEDURE   [ usp_ViewMessageListByReceiveUser ]
DROP   PROCEDURE   [ usp_ViewMessageDetail ]
DROP   PROCEDURE   [ usp_ViewMessageAttachment ]
DROP   TABLE   [ t_Message_Attachment ]
DROP   TABLE   [ t_Message ]
DROP   TABLE   [ t_User ]
GO

/**/ /*
messageID            messageTitle              messageTime                                            isRead messagePostUserID userName 
-------------------- ------------------------- ------------------------------------------------------ ------ ----------------- -------- 
1                    你好                        2008-02-29 10:31:07.560                                0      1                 李四
3                    再次发送                      2008-02-29 10:31:07.560                                0      1                 李四
5                    我是张三                      2008-02-29 10:31:07.560                                0      1                 王五

（所影响的行数为 3 行）

messageID            messageTitle              messageTime                                            isRead messagePostUserID userName 
-------------------- ------------------------- ------------------------------------------------------ ------ ----------------- -------- 
1                    你好                        2008-02-29 10:31:07.560                                0      1                 张三
3                    再次发送                      2008-02-29 10:31:07.560                                0      1                 张三
4                    老兄你好                      2008-02-29 10:31:07.560                                0      3                 王五

（所影响的行数为 3 行）

messageID            messagePostUserID messageReceiveUserID messageTitle              messageBody                                                                                                                                                                                                                                                      messageTime                                            isRead isPostUserDelete isReceiveUserDelete userName 
-------------------- ----------------- -------------------- ------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------ ------ ---------------- ------------------- -------- 
1                    1                 2                    你好                        好久不见，近来可好                                                                                                                                                                                                                                                        2008-02-29 10:31:07.560                                0      0                0                   张三

（所影响的行数为 1 行）

attachmentID         messageID            AttachFile 
-------------------- -------------------- ---------- 

（所影响的行数为 0 行）


（所影响的行数为 1 行）

messageID            messagePostUserID messageReceiveUserID messageTitle              messageBody                                                                                                                                                                                                                                                      messageTime                                            isRead isPostUserDelete isReceiveUserDelete userName 
-------------------- ----------------- -------------------- ------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------ ------ ---------------- ------------------- -------- 
1                    1                 2                    你好                        好久不见，近来可好                                                                                                                                                                                                                                                        2008-02-29 10:31:07.560                                1      0                0                   李四

（所影响的行数为 1 行）

attachmentID         messageID            AttachFile 
-------------------- -------------------- ---------- 

（所影响的行数为 0 行）

messageID            messageTitle              messageTime                                            isRead messagePostUserID userName 
-------------------- ------------------------- ------------------------------------------------------ ------ ----------------- -------- 
1                    你好                        2008-02-29 10:31:07.560                                1      1                 李四
3                    再次发送                      2008-02-29 10:31:07.560                                0      1                 李四
5                    我是张三                      2008-02-29 10:31:07.560                                0      1                 王五
*/