On Friday 19 June 2015 09:59:17 Joshua Joseph wrote: > On Thu, Jun 18, 2015 at 10:07 PM, Pali Rohár <pali.ro...@gmail.com> wrote: > > > > > > I get it now. > > > > > > We will have to store the contactId() also. I will need to get good > > > column names > > > to avoid confusion with the foreign key columns. > > > > > > > See my yesterday email where I proposed to store pluginId() as protocol, > > accounId() as account, contactId() as contact and from/to values to be > > protocol dependent. > > > > And another question: Do we need to separate table for group chat > > messages? Cannot we use some chat session identifier for each message? > > > > > That would also work. See my schema below for a one table based approach. > > > > If for each message we store these data: > > > > protocol independent: > > > > * protocol - Kopete::Protocol::pluginId() - not null > > * account - Kopete::Protocol::accoundId() - not null > > * direction - Kopete::Message::direction() - not null > > * contact - Kopete::Protocol::contactId() > > > > protocol dependent (all strings): > > > > * session - session identifier > > * session_name - human readable name of session > > * from - from contact identifier > > * from_name - human readable from contact name > > * to - to contact identifier > > * to_name - human readable to contact name > > > > See this schema. I think it will capture all of the above. > For protocol, account and contact fields, I have used Text columns. > I am also adding a message_type column, so that we can keep track of special > messages such as room events (user join, quit etc). Do you think that that > will be > necessary? >
I think this is handled by Kopete::Message::MessageDirection::Internal: http://api.kde.org/4.x-api/kdenetwork-apidocs/kopete/libkopete/html/classKopete_1_1Message.html Maybe it make sense to add GUI option to enable/disable logging of internal messages. For somebody it could be useless and just take space on disk. For somebody else it could be useful to see when contact sent some file or left/joined group chat... (and when you are sending schema or part of source code via email, please do not wrap schema lines as it is hard to read) > --messages table > CREATE TABLE "messages" ( > "message_id" Integer Primary Key Autoincrement Not Null --Unique message > identifier > "timestamp" Text --When the message was handled > "message" Text --HTML containing the message contents > "protocol" Text Not Null --Protocol used (Kopete::Protocol::pluginId()) > "account" Text Not Null --Account used (Kopete::Account::accountId()) > "direction" Integer Not Null --(Inbound = 0, Outbound=1, Internal=2) > (Kopete::Message::MessageDirection) > "importance" Integer -- (Low, Normal, Highlight) (Kopete::Message) > (Kopete::Message::MessageImportance) > "contact" Text -- The local contact used in this message (if > applicable). (Kopete::Contact::ContactId()). If present, we know we are in > single user mode. > "subject" Text --If applicable, this will store the subject of the > message > "session" Text -- Internal session identifier. If this is provided, then > we know we are in multi user mode. > "session_name" Text -- If in multi user mode, a human readable name for > the session. > "from" Text --Internal identifier for the message sender > "from_name" Text --Human readable name of the message sender > "to" Text --Internal identifier for the message recipient > "to_name" Text --Human readable name of the message recipient. > "message_type" --The type of message. (TypeNormal, TypeAction, > TypeFileTransferRequest, TypeVoiceClipRequest) > (Kopete::Message::MessageType) > ) Why message_ prefix (for message_type and message_id) if other columns which you are propose do not have message_ prefix? And there is missing Kopete::Message::MessageState property. Timestamp should be integer as SQLite does not have dedicated DATE type and I do not thing that it can use indexes on string date formats for fast search (e.g all messages which were sent in specific day). Anyway, I think that current solution could work now. What can be useful space optimization: There will be more times rows with duplicate string columns (protocol, account, contact, session, *_name). Maybe it could make sense to create new tables for it and reference integer foreign keys from message tables. But I do not know if this will have some performance benefit for SQLite as then you will have to do lot of joins. Probably somebody who know SQLite better could comment this... > > > > > > > > > then I think it it should work for both single user and multi user chat. > > In this case either "contact" or "session" needs to be provided to will > > be able to know to which "view" message belongs. "contact" can be used > > for single user chat (there is no problem) and room based multi user > > chat. And session (unique string identifier) can be used for multi user > > chats without rooms (e.g. Skype-like). > > Have you considered to use "contact" column also for room-based group chat? Does it have any problems? > > Columns from/to can be used to store protocol dependent information > > about sender/receiver (e.g full JID for jabber) or full of contacts in > > multi user chat... > > > > Do not remember that messages are changing their state! StateSending > > will be later changed to StateSent or StateError. The only way how to > > track this information is Kopete::Message::id() function. But id is > > unique only for one running kopete instance (not after quit and start!). > > So this information cannot be stored into database, but is it needed to > > track number of message row in table (which is unique) with in-memory > > Kopete::Message::id(). > > > > > > > > -- Pali Rohár pali.ro...@gmail.com _______________________________________________ kopete-devel mailing list kopete-devel@kde.org https://mail.kde.org/mailman/listinfo/kopete-devel
