Re: Chat history progress - GSoC 2017

Paulo Lieuthier <[email protected]> Wed, 21 Jun 2017 22:59:11 -0300
Newsgroups	gmane.comp.kde.devel.kopete
Message-ID	<[email protected]>
On 21-06-2017 11:45, Pali Rohár wrote:
> On Tuesday 20 June 2017 08:38:01 Paulo Lieuthier wrote:
>> On 20-06-2017 05:20, Pali Rohár wrote:
>>> On Tuesday 20 June 2017 01:24:10 Paulo Lieuthier wrote:
>>>> The new iteration:
>>>>
>>>> Conversations:
>>>> * id
>>>> * subconversation_id
>>>> * account
>>>> * type (1:1, group, channel)
>>>> * entity_identifier (id of contact, group or channel)
>>>> * entity_display_name
>>>>
>>>> Messages:
>>>> * id
>>>> * conversation_id
>>>> * subconversation_id
>>>> * timestamp
>>>> * sender_id (contact id, if available)
>>>> * sender_display_name (sender name, if name not available through id)
>>>> * type (text, event, file, voice clip)
>>>> * content (html)
>>>> * importance
>>>> * state
>>>
>>> This looks like a synthetic split. Via "Conversations" there are stored
>>> two relations (id + subconversation_id), so it in final it N:M relation
>>> via two tables?
>>
>> I'm thinking of using two tables, or maybe setting both id and
>> subconversation_id as the primary key.
> 
> Why then it is needed to have two primary keys in table? Is not primary
> key mean to be already unique?

As far as I know, there is no conceptual issue in using more than one 
field as the primary key. Two fields that have distinct information and 
together can uniquely identify a record. We can use another table for 
that, but there may be no need for that.

Of course, if you prefer simple, explicit primary keys we'll go that 
way. Please note the compound key is for the conversations table, not 
for messages.

> Seems you have a problem with mapping real data from received/sent
> message to database structure.
> 
> Either you have numeric identifier, internal database 32/64bit int which
> is unique for every message and has no meaning for Kopete and message
> itself. And then it is primary key and nothing like two primary keys are
> needed.

Why does the identifier need to have meaning for Kopete? I'm trying to 
build a schema that is as agnostic as possible to implementation 
details. If it's capable of representing conversations with contacts 
from different accounts and keep sane history, Kopete should find its 
way to make that useful.

The most common use cases I can imagine are opening a chat window and 
scrolling up and searching for a text in all history. For that I don't 
think there is need to have a identifier meaningful for Kopete. Please 
help me if I'm not seeing the obvious here.

One use case I can imagine Kopete making use of a identifier is when 
deleting a message. I'm not sure if Kopete supports it right now. It 
would then need to map its message to the record in database. Of course 
we can add fields like "external_id", which doesn't have to be a primary 
key.

> Or you have protocol specific string identifiers (one, two?) with
> arbitrary length, but such thing cannot be used as useful id, neither as
> primary identifier for larger databases (where are tons of messages). I
> do not know limits of SQLite, but I know that e.g. MySQL has about 700
> bytes limit for index. So identifiers cannot be larger.

700 bytes looks like a incredibly large limit to me. But I agree strings 
should not be used as keys. Anyway, we can always hash them if we need. 
Also, if the primary key is not integer, SQLite creates a "rowid" 
integer field automatically [2].

> So for schema you need to describe what those columns means. And how are
> mapped properties from Kopete::Message to that database schema.

Conversations:
* id (integer, primary key)
* subconversation_id (string) (*)
* account (Kopete::Account::accountId)
* type (1:1, group, channel) (**)
* entity_identifier (Kopete::Contact::contactId, Kopete::Group::groupId)
* entity_display_name (string, if name not available through identifier)

Messages:
* id (integer, primary key)
* conversation_id (foreign key)
* subconversation_id (foreign key)
* timestamp
* sender_id (Kopete::Account::accountId)
* sender_display_name (sender name, if name not available through id)
* type (Kopete::Message::MessageType)
* content (string)
* importance (Kopete::Message::MessageImportance)
* state (Kopete::Message::MessageState)

(*) How can the history plugin know the conversarion specifier (Jabber's 
roster, or different chat session identifier). The plugins will have to 
expose that information through the chat session manager, right?

(**) Kopete::ChatSession::Form has something similar, but not quite: 
only "small" and "chatroom" types ("small" includes 1:1). If there is no 
enum for that, I could create one in the plugin.

I'm beginning starting the implementation of the SQL-based backend, 
while still working in the façade abstraction (the last review request 
[1] still needs proper testing and probably has bugs).

Paulo

[1] https://git.reviewboard.kde.org/r/130164/
[2] https://www.sqlite.org/lang_createtable.html#rowid
signature.asc (application/pgp-signature, 833 B)