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? 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. 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. Such thing is unsuitable for protocols where identifier can be arbitrary string, plus which depends on other side (sender). It is really bad if other IM client start to damaging Kopete database history, just because it decide to use larger identifiers... So for schema you need to describe what those columns means. And how are mapped properties from Kopete::Message to that database schema. -- Pali Rohár pali.ro...@gmail.com
