Re: GSoC History plugin

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.


>
> 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.


> 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.
   "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" --The type of message. (TypeNormal, TypeAction,
TypeFileTransferRequest, TypeVoiceClipRequest)
(Kopete::Message::MessageType)

--
Thanks,
Joshua

_______________________________________________
kopete-devel mailing list
[email protected]
https://mail.kde.org/mailman/listinfo/kopete-devel