首页 新闻 搜索 专区 学院

人人网,新浪微博等SNS网站的消息发送机制是怎么做到的?

0
悬赏园豆:10 [已解决问题] 解决于 2013-03-29 00:00

人人网和新浪微博等网站不需要所有用户同时在线。一个用户给另一个用户发送消息或者回复状态、日志等等时,另一个用户可以不是在线状态,他上线之后也可以获得所发送的消息。请问这个是怎么做到的?能否大致说明一下原理?能有Demo型的示例源码就更好了

谢谢

飞鸟_Asuka的主页 飞鸟_Asuka | 菜鸟二级 | 园豆:251
提问于:2013-03-26 14:52
< >
分享
最佳答案
0
--增加短消息
IF OBJECT_ID('dnt_createpm','P') IS NOT NULL
DROP PROC dnt_createpm
GO 
CREATE PROCEDURE dnt_createpm
@pmid int,
@msgfrom nvarchar(20),
@msgto nvarchar(20),
@msgfromid int,
@msgtoid int,
@folder smallint=0,--草稿箱
@new int=0,
@subject nvarchar(60),
@postdatetime datetime,
@message ntext,
@savetosentbox smallint=1
AS
IF @folder<>0    --不为0时是保存至草稿箱
    BEGIN
        SET @msgfrom=@msgto   --发给自己的,因此发件人和收件人是一个
    END
ELSE             --若草稿箱为0时,是发给其他人的,更新,通知用户有新消息
    BEGIN
        UPDATE [dnt_users] 
        SET [newpmcount]=ABS(ISNULL([newpmcount],0)*1)+1,[newpm] = 1 
        WHERE [uid]=@msgtoid
    END
    
INSERT INTO [dnt_pms] 
    ([msgfrom],[msgfromid],[msgto],[msgtoid],[folder],[new],[subject],[postdatetime],[message])
VALUES
    (@msgfrom,@msgfromid,@msgto,@msgtoid,@folder,@new,@subject,@postdatetime,@message)
    
SELECT SCOPE_IDENTITY() AS 'pmid'
IF @savetosentbox=1 AND @folder=0   --用户发送信息时勾选保存至发稿箱,
    BEGIN
        INSERT INTO [dnt_pms]
            ([msgfrom],[msgfromid],[msgto],[msgtoid],[folder],[new],[subject],[postdatetime],[message])
        VALUES
            (@msgfrom,@msgfromid,@msgto,@msgtoid,1,@new,@subject,@postdatetime,@message)
    END
GO 
--查询短消息个数
IF OBJECT_ID('dnt_getpmcount','P') IS NOT NULL
DROP PROC dnt_getpmcount
GO 
CREATE PROCEDURE dnt_getpmcount
@userid int,
@folder int=0,
@state int=-1
AS
IF @folder=-1
    BEGIN
      SELECT COUNT(pmid) AS [pmcount] FROM [dnt_pms] WHERE ([msgtoid]=@userid AND [folder]=0) OR ([msgfromid] = @userid AND [folder] = 1) OR ([msgfromid] = @userid AND [folder] = 2)
    END
ELSE
    BEGIN
        IF @folder=0
            BEGIN
                IF @state=-1
                    BEGIN
                        SELECT COUNT(pmid) AS [pmcount] FROM [dnt_pms] WHERE [msgtoid]=@userid AND [folder]=@folder
                    END
                ELSE IF @state=2
                    BEGIN
                        SELECT COUNT(pmid) AS [pmcount] FROM [dnt_pms] WHERE [msgtoid]=@userid AND [folder]=@folder AND [new]=1 AND GETDATE()-[postdatetime]<3
                    END
                ELSE
                    BEGIN
                        SELECT COUNT(pmid) AS [pmcount] FROM [dnt_pms] WHERE [msgtoid]=@userid AND [folder]=@folder AND [new]=@state
                    END
            END
        ELSE
            BEGIN
                IF @state=-1
                    BEGIN
                        SELECT COUNT(pmid) AS [pmcount] FROM [dnt_pms] WHERE [msgfromid]=@userid AND [folder]=@folder
                    END
                ELSE IF @state=2
                    BEGIN
                        SELECT COUNT(pmid) AS [pmcount] FROM [dnt_pms] WHERE [msgfromid]=@userid AND [folder]=@folder AND [new]=1 AND GETDATE()-[postdatetime]<3
                    END
                ELSE
                    BEGIN
                        SELECT COUNT(pmid) AS [pmcount] FROM [dnt_pms] WHERE [msgfromid]=@userid AND [folder]=@folder AND [new]=@state
                    END
            END
    END
GO 
--查询
IF OBJECT_ID('dnt_getpmlist','P') IS NOT NULL
DROP PROC dnt_getpmlist
GO
CREATE PROCEDURE dnt_getpmlist
@userid int,
@folder int,
@pagesize int,
@pageindex int,
@inttype int
AS
DECLARE @strSQL varchar(5000)
DECLARE @msgformORtoID varchar(10)
SET @msgformortoid='msgtoid'
IF @folder=1 OR @folder=2
    BEGIN
        SET @msgformortoid='msgfromid'
    END
IF @pageindex = 1
    BEGIN
        IF (@inttype <> 1)
            SET @strSQL = 'SELECT TOP ' + STR(@pagesize) +' [pmid],[msgfrom],[msgfromid],[msgto],[msgtoid],[folder],[new],[subject],[postdatetime],[message] FROM [dnt_pms] WHERE [' + @msgformortoid + ']=' +STR(@userid) + ' AND [folder]=' +STR(@folder)  + ' ORDER BY [pmid] DESC'
        ELSE
            SET @strSQL = 'SELECT TOP ' + STR(@pagesize) +' [pmid],[msgfrom],[msgfromid],[msgto],[msgtoid],[folder],[new],[subject],[postdatetime],[message] FROM [dnt_pms] WHERE [' + @msgformortoid + ']=' +STR(@userid) + ' AND [folder]=' +STR(@folder) + ' ORDER BY [pmid] DESC'
        
    END
ELSE
    BEGIN
        SET @strSQL = 'SELECT TOP ' + STR(@pagesize) +' [pmid],[msgfrom],[msgfromid],[msgto],[msgtoid],[folder],[new],[subject],[postdatetime],[message] FROM [dnt_pms] WHERE [pmid] < (SELECT MIN([pmid]) FROM (SELECT TOP ' + STR((@pageindex-1)*@pagesize) + ' [pmid] FROM [dnt_pms] WHERE [' + @msgformortoid + ']=' +STR(@userid) + ' AND [folder]=' +STR(@folder) + ' ORDER BY [pmid] DESC) AS tblTmp) AND [' + @msgformortoid + ']=' +STR(@userid) + ' AND [folder]=' + STR(@folder) + ' ORDER BY [pmid] DESC'
        IF (@inttype <> 1)
            BEGIN
                SET @strSQL = 'SELECT TOP ' + STR(@pagesize) +' [pmid],[msgfrom],[msgfromid],[msgto],[msgtoid],[folder],[new],[subject],[postdatetime],[message] FROM [dnt_pms] WHERE [pmid] < (SELECT MIN([pmid]) FROM (SELECT TOP ' + STR((@pageindex-1)*@pagesize) + ' [pmid] FROM [dnt_pms] WHERE [' + @msgformortoid + ']=' +STR(@userid) + ' AND [folder]=' +STR(@folder) + ' ORDER BY [pmid] DESC) AS tblTmp) AND [' + @msgformortoid + ']=' +STR(@userid) + ' AND [folder]=' +STR(@folder) +  ' ORDER BY [pmid] DESC'
            END
    END
EXEC(@strSQL)
GO 
收获园豆:5
羽商宫 | 老鸟四级 |园豆:2493 | 2013-03-26 15:22

这个是放在SQL Server里执行么?是不是要先建立数据库啊

飞鸟_Asuka | 园豆:251 (菜鸟二级) | 2013-03-26 15:59

@飞鸟_Asuka: 当然要,这是存储过程

羽商宫 | 园豆:2493 (老鸟四级) | 2013-03-26 16:05
其他回答(3)
0

路过

月下花弄影 | 园豆:312 (菜鸟二级) | 2013-03-26 14:54
0

有没有考虑过在用户上线的时候去数据库里查询自己未读的消息,如果有就提示,没有就作罢

收获园豆:2
ackamen | 园豆:1 (初学一级) | 2013-03-26 14:59

嗯,这是一种方法,我也想到了。只是想集思广益一下看看有没有更好的实现

支持(0) 反对(0) 飞鸟_Asuka | 园豆:251 (菜鸟二级) | 2013-03-26 15:03
0

数据库标识未读的消息,上线后,用长连接取即可,达到和qq消息一个效果。

收获园豆:3
amityat | 园豆:476 (菜鸟二级) | 2013-03-26 15:45
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册