On Fri, Jun 19, 2015 at 10:33 AM, Pali Rohár <pali.ro...@gmail.com> wrote:
> On Friday 19 June 2015 09:59:17 Joshua Joseph wrote: > > On Thu, Jun 18, 2015 at 10:07 PM, Pali Rohár <pali.ro...@gmail.com> > 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 kopete-devel@kde.org https://mail.kde.org/mailman/listinfo/kopete-devel
