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. > > > "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. > > > "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 > _______________________________________________ > kopete-devel mailing list > kopete-devel@kde.org > https://mail.kde.org/mailman/listinfo/kopete-devel > -- Thanks, Joshua
_______________________________________________ kopete-devel mailing list kopete-devel@kde.org https://mail.kde.org/mailman/listinfo/kopete-devel
