chat server: what's the best(optimized) way to save a conversation log

Question

I'm building a simple chat server in java , where users can have private conversations with each other. I want to save those conversation at a server level(not on client side) so I can list them to the users as a conversation log service. I'm also using MySQL as a database in my software.

What I'm looking is an optimized way to save those conversations and also a fast way to list them later. So far I've thought on 2 implementations.

1. using the MySQL database and

   a) save the conversation in a row as a text, but the problem is that some conversations are very huge(lots of chars) and I might have problems saving the entire conversation

   b) save every line of the conversation in a row, but this way speed problems can appear when I want to list the entire conversation

2. saving every conversation in a separated text file, but I'm afraid read/write problems can appear, especially when the users(clients) are writing(sending text) very fast.

Thanks

Answer 1

This is big architectural problem, you know. Companies like facebook and twitter spent lots of time and money to solve your problem in robust way. If your chat server is simple (As you've written), use 1.b way, but make an abstraction layer (something like saveConversation, getConversation). If in future speed wouldn't satisfy you, think about more efficient representation, like NoSQL database (LevelDB or something like this). Don't think about performance now, make a prototype with good abstraction and pluggable architecture.

Answer 2

None of the proposed ways are the best optimized way to store it. The best way is to use strip segments of dynamic tables on the basis of conversations.

This is a prototype with the paradigms :-

1. To do a conversation with multiple participants.
2. Option to Acknowledge Chats.
3. Option for Actions updates.
4. Option to post Attachments.
5. Option to share Map Coordinates like you current location.
6. Option to Leave a chat.

Types

Coordinates(
  lat FLOAT( 10, 6 ) NOT NULL ,
  lng FLOAT( 10, 6 ) NOT NULL 
)

Schema

dbo.User(UserId,<..Details..>)

dbo.Conversation(ConversationId,Title,CreationDate,LastActivityDate)

Index : LastActivityDate, ConversationId

dbo.Participants(ConversationId,UserId)

collection of tables - Dynamically created

msg.msg_*ConversationId*(MessageId,FromUserId,Message,Attachment,LocationCoordinates,Action,LogDate,AcknowledgeDate)

Abstraction

usp_TouchMessage(@Which):
   if (not exists (select 1 from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA+'.'+TABLE_NAME = 'msg.msg_'+CAST(@Which as nvarchar(max)))
   begin
        @dynamic_sql = 'create table msg.msg_'+CAST(@Which as nvarchar(max))+' (MessageId uniqueidentifier,FromUserId uniqueidentifier,Message nvarchar(max),Attachment BLOB,LocationCoordinates Coordinates,Action nvarchar(max),LogDate DateTime, AcknowledgeDate DateTime);';
        exec(@dynamic_sql);
        @dynamic_sql = 'CREATE TRIGGER msg.trig_msg_'+CAST(@Which as nvarchar(max))+'  ON msg.msg_'+CAST(@Which as nvarchar(max))+' AFTER INSERT AS Delete From msg.msg_'+CAST(@Which as nvarchar(max))+' where MessageId in (Select MessageId,ROW_NUMBER() OVER (order by LogDate Desc,AcknowledgeDate Desc,MessageId Desc) RN from msg.msg_'+CAST(@Which as nvarchar(max))+') where RN>=5000';
        exec(@dynamic_sql);

     end

usp_GetParticipants(@Where) :
    Select User.UserId,User.FullName 
          from Participants 
                inner join Users on (
                        Participants.UserId = Users.UserId 
                    And Participants.ConversationId = @Where
                );


usp_AddParticipants(@Who, @Where) :
    insert into Participants(ConversationId,UserId) values (@Where, @Who);
    @action = ( select User.FullName + ' has joined the chat' from User where User.UserId = @Who );
    update Conversation set LastActivityDate = GETUTCDATE() where ConversationId = @conversation_id;
    exec usp_TouchMessage(@Where);
    @dynamic_sql = 'insert into msg.msg_'+CAST(@Where as nvarchar(max))+' (MessageId,FromUserId,Action,LogDate) values (NewId(),@Who,@Action,GETUTCDATE())';
    sp_executesql @dynamic_sql, N'@Who uniqueidentifier,@Action nvarchar(max)', @Who = @Who, @Action = @Action;


usp_GetConversationList() :
    With Participation As (
        Select User.UserId,User.FullName,Participants.ConversationId 
          from Participants 
                inner join Users on (Participants.UserId = Users.UserId)
    ) select Conversation.*,
            (STUFF((
                    SELECT ',' + CAST([FullName] AS nvarchar(max)) 
                      FROM Participation 
                     WHERE (Participation.ConversationId = Conversation.ConversationId) 
              FOR XML PATH ('')
             ),1,1,'')) ParticipantList
        from Conversation 
    order by Conversation.LastActivityDate;


usp_GetConversationById(@Which,@Lite = 0,@Page = 1) :
    Select User.UserId,User.FullName 
          from Participants
            inner join Users on (Participants.UserId = Users.UserId and Participants.ConversationId = @Which);
    @dynamic_sql = 'select * from
                        (select u.UserId,u.FullName,m.MessageId,'
                                +(case when @Lite=1 then 'm.Message,m.LocationCoordinates,m.Attachment,m.Action,' else '' end)
                                +'m.LogDate,m.AcknowledgeDate, ROW_NUMBER() Over (order by m.LogDate Desc,m.AcknowledgeDate Desc,m.MessageId Desc) RN
                          From msg.msg_'+CAST(@Which AS nvarchar(max))+' m 
                            inner join User u on (m.FromUserId = u.UserId) 
                        ) tmp 
                    where RN Between ((@Page-1)*20+1) AND (@Page*20+1)
                ';
    sp_executesql @dynamic_sql, N'@Page bigint', @Page = @Page;
    If @Page = 1 And @Lite=0
    begin
        @dynamic_sql = 'update msg.msg_'+CAST(@Which as nvarchar(max))+' Set AcknowledgeDate = GETUTCDATE() where AcknowledgeDate is null and FromUserId <> @Who';
        sp_executesql @dynamic_sql, N'@Who uniqueidentifier', @Who = @Who;
    end

usp_GetConversation(@Who,@WithWhome,@Lite = 0,@Page = 1) :
    @conversation_id = (
        Select top 1 ConversationId 
          from Participants self 
            inner join Participants partner 
                on ( self.ConversationId = partner.ConversationId and self.UserId = @Who and partner.UserId = @WithWhome)
            where (Select count(1) from Participants where ConversationId = self.ConversationId) = 2
    );  
    if(@conversation_id is not null)
    then
        exec usp_GetConversationById(@conversation_id, @Lite, @Page);
    end


usp_PostConversationById(@Who,@Which,@WhatMessage,@WhichLocation,@WhatAttachment) :
    update Conversation set LastActivityDate = GETUTCDATE() where ConversationId = @Which;
    exec usp_TouchMessage(@Which);
    @dynamic_sql = 'insert into msg.msg_'+CAST(@Which as nvarchar(max))+' (MessageId,FromUserId,Message,Attachment,LocationCoordinates,LogDate) values (NewId(),@Who,@WhatMessage,@WhichLocation,@WhatAttachment,GETUTCDATE())';
    sp_executesql @dynamic_sql, N'@Who uniqueidentifier,@WithWhome uniqueidentifier,@WhatMessage nvarchar(max),@WhichLocation Coordinates,@WhatAttachment BLOB', @Who = @Who, @WhatMessage = @WhatMessage, @WhichLocation = @WhichLocation, @WhatAttachment = @WhatAttachment;


usp_PostConversation(@Who,@WithWhome,@WhatMessage,@WhichLocation,@WhatAttachment) :
    @conversation_id = (
        Select top 1 ConversationId 
          from Participants self 
            inner join Participants partner 
                on ( self.ConversationId = partner.ConversationId and self.UserId = @Who and partner.UserId = @WithWhome)
            where (Select count(1) from Participants where ConversationId = self.ConversationId) = 2
    );  
    if(@conversation_id is not null)
    then
        @conversation_id = newid()
        insert into Conversation(ConversationId,CreationDate) values (@conversation_id,GETUTCDATE());
        exec usp_AddParticipants(@Who,@conversation_id);
        exec usp_AddParticipants(@WithWhome,@conversation_id);
    end
    exec usp_PostConversationById(@Who,@conversation_id,@WhatMessage,@WhichLocation,@WhatAttachment);


usp_UpdateConversationAlias(@Who,@Which,@WithWhat) :
    @action = ( select User.FullName + ' has changed title'+isnull(' from <b>'+Conversation.Title+'</b>','')+isnull(' to <b>'+@WithWhat+'</b>','') from User inner join Conversation on (Conversation.ConversationId = @Which and User.UserId = @Who));
    update Conversation set LastActivityDate = GETUTCDATE(), Title = @WithWhat where ConversationId = @conversation_id;
    if (not exists (select 1 from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA+'.'+TABLE_NAME = 'msg.msg_'+CAST(@Which as nvarchar(max)))
    exec usp_TouchMessage(@Which);
    @dynamic_sql = 'insert into msg.msg_'+CAST(@Which as nvarchar(max))+' (MessageId,FromUserId,Action,LogDate) values (NewId(),@Who,@Action,GETUTCDATE())';
    sp_executesql @dynamic_sql, N'@Who uniqueidentifier,@Action nvarchar(max)', @Who = @Who, @Action = @Action;


usp_LeaveConversation(@Who,@Which) :
    delete from Participants where ConversationId = @Where and UserId = @Who;
    if(not exists (Select 1 From Participants Where ConversationId = @Which))
    begin
        @dynamic_sql = 'drop table msg.msg_'+CAST(@Which as nvarchar(max))+';
        exec @dynamic_sql;
        delete from Conversation where ConversationId = @Which;
    end
    else
    begin
        @action = ( select User.FullName + ' has left the chat' from User where User.UserId = @Who );
        update Conversation set LastActivityDate = GETUTCDATE() where ConversationId = @conversation_id;
        exec usp_TouchMessage(@Which);
        @dynamic_sql = 'insert into msg.msg_'+CAST(@Which as nvarchar(max))+' (MessageId,FromUserId,Action,LogDate) values (NewId(),@Who,@Action,GETUTCDATE())';
        sp_executesql @dynamic_sql, N'@Who uniqueidentifier,@Action nvarchar(max)', @Who = @Who, @Action = @Action;
    end