On Saturday 20 June 2015 15:22:52 Joshua Joseph wrote: > On Fri, Jun 19, 2015 at 7:03 PM, Pali Rohár <pali.ro...@gmail.com> > wrote: > > 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. > > I had not thought of that. :) > > > Same for Contact: Does not it make sense to store Kopete::Contact > > representing room? Or do you think it is not needed at all? > > Yes. I will change to Kopete::Contact. >
I think you did not change anything, or yes? Anyway detecting multi user chat messages with that mutually exclusive condition (only one of contact/session or name is set) is really hard to imagine and also write correct select sql statement (it is even possible to write optimal one for SQLite??). Rather use some multi user group chat boolean column (in SQLite there is no boolean, just int). > > > "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? > > Its better to store all with a preset delimiter. > Ok. I think that most kopete protocols (maybe all now??) just set one contact for Kopete::Message::to() value. So I think that in future (when Kopete::Message will be ported to KF5) we could change list to single value. If you think that full list is needed, we can use delimiter and maybe in future change it only to single value. > > > "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? > > Yes, it is integer, as below: > > 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. > "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. > "state" Integer, --(Unknown = 0, Sending = 1, Sent = 2, Error = 3) > "type" Integer--The type of message. (TypeNormal, TypeAction, > TypeFileTransferRequest, TypeVoiceClipRequest) > (Kopete::Message::MessageType) > -- Pali Rohár pali.ro...@gmail.com
signature.asc
Description: This is a digitally signed message part.
_______________________________________________ kopete-devel mailing list kopete-devel@kde.org https://mail.kde.org/mailman/listinfo/kopete-devel
