On Thursday 22 June 2017 03:59:11 Paulo Lieuthier wrote: > 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.
Adding another unique key just increase size of index and therefore any
operations with index would be slower.
Composed primary key (from id and subconversation_id) in Conversations
is mapped 1:1 to table Messages.
Therefore creating primary key just via "id" and making
"subconversation_id" as optional has the same result. And it has only
one primary key. Because one message cannot have two different "id" or
two different "subconversation_id".
> > 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?
It does not have to. I wrote two scenarios.
> 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.
No, it does not support it. But there is some jabber XEP which allows
editing sent messages and for such functionality it is needed to store
protocol specific identifier.
> 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.
This opens another questions... how to handle collisions? which has
function to use? or which technique? some perfect hashing? Looks like
hashing just adds another layer of problems.
> Also, if the primary key is not integer, SQLite creates
> a "rowid" integer field automatically [2].
Yes.
> > 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?
Not supported by Kopete yet, but either Kopete::Session or
Kopete::Message would needs to be extended for it.
> (**) 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.
The best way is to create mapping table from SQL values to Kopete values
(e.g. enum, array).
> 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
--
Pali Rohár
pali.ro...@gmail.com
signature.asc
Description: This is a digitally signed message part.
