On Friday 19 June 2015 16:54:57 Joshua Joseph wrote: > On Fri, Jun 19, 2015 at 10:33 AM, Pali Rohár <pali.ro...@gmail.com> wrote: > > > 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. > > > > See the schema below, I have updated it. > > > > > 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). > > > > Yes. For other db systems that will be quite doable and fast. >
For SQLite we just need to stay with integer... > > > > > 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. > > > For now we can proceed with a single table for storage. I will need to run > some benchmarks and see if there will be any improvement when using > multiple tables. > Ok, please do it. > > > 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... > > > > I will have to do some research about this. > > > > > > I think we can proceed with this schema: > > --messages table > CREATE TABLE "messages" ( > "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. With this you just kill support for doing session for single user chat. Some protocols (skype, but also jabber -- but not implemented in Kopete) support such thing. So it could be good candidate for future. Same for Contact: Does not it make sense to store Kopete::Contact representing room? Or do you think it is not needed at all? > "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. Kopete::Message::from() and Kopete::Message::to() can represent those from/to values... Just to note that Kopete::Message::to() is list of Kopete::Contact object. It is not single/one Kopete::Contact. Maybe storing it as concatenation with ", " separator? Or totally drop list and just store first Contact? > "state" Integer, --(Unknown = 0, Sending = 1, Sent = 2, Error = 3) > "type" --The type of message. (TypeNormal, TypeAction, > TypeFileTransferRequest, TypeVoiceClipRequest) > (Kopete::Message::MessageType) > "type" is probably integer, right? -- Pali Rohár pali.ro...@gmail.com _______________________________________________ kopete-devel mailing list kopete-devel@kde.org https://mail.kde.org/mailman/listinfo/kopete-devel
