Re: GSoC History plugin

On Friday 19 June 2015 16:54:57 Joshua Joseph wrote:
> On Fri, Jun 19, 2015 at 10:33 AM, Pali Rohár <[email protected]> wrote:
> 
> > On Friday 19 June 2015 09:59:17 Joshua Joseph wrote:
> > > On Thu, Jun 18, 2015 at 10:07 PM, Pali Rohár <[email protected]>
> > 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
[email protected]
_______________________________________________
kopete-devel mailing list
[email protected]
https://mail.kde.org/mailman/listinfo/kopete-devel