Personal Conversation Schema proposal

[Vekseid]

Thought about putting this into a PR but am not sure if you plan to make a 2.0 install file. So for now I'll just put this in an issue and close it in favor of #1980 after this has been reviewed and stuff can be submitted.

public function table_personal_conversations()
{
    return $this->table->create_table('{db_prefix}personal_conversations',
        array(
            array('name' => 'id_pc',             'type' => 'int', 'size' => 10, 'unsigned' => true, 'auto' => true),
            array('name' => 'id_member_started', 'type' => 'int', 'size' => 10, 'unsigned' => true, 'default' => 0),
            array('name' => 'id_member_updated', 'type' => 'int', 'size' => 10, 'unsigned' => true, 'default' => 0),
            array('name' => 'time_updated',      'type' => 'int', 'size' => 10, 'unsigned' => true, 'default' => 0),
            array('name' => 'reply_count',       'type' => 'int', 'size' => 10, 'unsigned' => true, 'default' => 0),
            array('name' => 'id_msg_first',      'type' => 'int', 'size' => 10, 'unsigned' => true, 'default' => 0),
            array('name' => 'time_msg_first',    'type' => 'int', 'size' => 10, 'unsigned' => true, 'default' => 0),
            array('name' => 'id_msg_last',       'type' => 'int', 'size' => 10, 'unsigned' => true, 'default' => 0),
            array('name' => 'time_msg_last',     'type' => 'int', 'size' => 10, 'unsigned' => true, 'default' => 0),
            array('name' => 'locked',            'type' => 'tinyint', 'size' => 3, 'unsigned' => true, 'default' => 0),
            array('name' => 'invites_open',      'type' => 'tinyint', 'size' => 3, 'unsigned' => true, 'default' => 0),
            array('name' => 'can_kick',      'type' => 'tinyint', 'size' => 3, 'unsigned' => true, 'default' => 0),
        ),
        array(
            array('name' => 'id_pc',      'columns' => array('id_pm'), 'type' => 'primary'),
            array('name' => 'id_member',  'columns' => array('id_member_started'), 'type' => 'key'),
            array('name' => 'time_first',    'columns' => array('time_msg_first'), 'type' => 'key'),
            array('name' => 'time_last',    'columns' => array('time_msg_last'), 'type' => 'key'),
        ),
        array(),
        'ignore'
    );
}

public function table_pc_recipients()
{
    return $this->table->create_table('{db_prefix}pc_recipients',
        array(
            array('name' => 'id_pc',     'type' => 'int', 'size' => 10, 'unsigned' => true, 'default' => 0),
            array('name' => 'id_member', 'type' => 'mediumint', 'size' => 8, 'unsigned' => true, 'default' => 0),
            array('name' => 'labels',    'type' => 'varchar', 'size' => 255, 'default' => -1),
            array('name' => 'time_read', 'type' => 'int', 'size' => 10, 'unsigned' => true, 'default' => 0),
            array('name' => 'last_read', 'type' => 'int', 'size' => 10, 'unsigned' => true, 'default' => 0),
            array('name' => 'status',    'type' => 'tinyint', 'size' => 3, 'unsigned' => true, 'default' => 0),
        ),
        array(
            array('name' => 'id_pc_member', 'columns' => array('id_pc', 'id_member'), 'type' => 'primary'),
            array('name' => 'id_member_pc', 'columns' => array('id_member', 'status', 'id_pc'), 'type' => 'unique'),
        ),
        array(),
        'ignore'
    );
}

public function table_pc_messages()
{
    return $this->table->create_table('{db_prefix}pc_messages',
        array(
            array('name' => 'id_pm',          'type' => 'int', 'size' => 10, 'unsigned' => true, 'auto' => true),
            array('name' => 'id_pc',          'type' => 'int', 'size' => 10, 'unsigned' => true, 'default' => 0),
            array('name' => 'poster_time',     'type' => 'int', 'size' => 10, 'unsigned' => true, 'default' => 0),
            array('name' => 'id_member',       'type' => 'int', 'size' => 10, 'unsigned' => true, 'default' => 0),
            array('name' => 'id_pm_modified',  'type' => 'int', 'size' => 10, 'unsigned' => true, 'default' => 0),
            array('name' => 'subject',         'type' => 'varchar', 'size' => 255, 'default' => ''),
            array('name' => 'poster_name',     'type' => 'varchar', 'size' => 255, 'default' => ''),
            array('name' => 'poster_email',    'type' => 'varchar', 'size' => 255, 'default' => ''),
            array('name' => 'poster_ip',       'type' => 'varchar', 'size' => 255, 'default' => ''),
            array('name' => 'smileys_enabled', 'type' => 'tinyint', 'size' => 4, 'default' => 1),
            array('name' => 'modified_time',   'type' => 'int', 'size' => 10, 'unsigned' => true, 'default' => 0),
            array('name' => 'modified_name',   'type' => 'varchar', 'size' => 255, 'default' => ''),
            array('name' => 'body',            'type' => 'mediumtext'),
            array('name' => 'icon',            'type' => 'varchar', 'size' => 16, 'default' => 'xx'),
        ),
        array(
            array('name' => 'id_pm',        'columns' => array('id_pm'), 'type' => 'primary'),
            array('name' => 'id_pc',        'columns' => array('id_pc', 'id_pm'), 'type' => 'unique'),
            array('name' => 'id_member',     'columns' => array('id_member', 'id_pm'), 'type' => 'unique'),
            array('name' => 'ip_index',      'columns' => array('poster_ip(15)', 'id_topic'), 'type' => 'key'),
            array('name' => 'related_ip',    'columns' => array('id_member', 'poster_ip', 'id_pm'), 'type' => 'key'),
        ),
        array(),
        'ignore'
    );
}

[emanuele45]

Honestly I'm not sure why the install has the version number in it, it should not...
Anyway, the 1_1 will become 2_0 and that's all there is to it.

I like the schema, it's something like what I started in the past, but never finished.
Mine was a bit more convoluted, with the option to "invite" new members to a convo, but the intentional limitation of allowing them to see only the messages since their joining.
Have you considered inviting/kicking/leaving conversations?
I think it may be worth a try. Yes, it makes things a bit more complex, but in today chat systems, it would look natural I guess.
I would suggest something along the line:

public function table_pc_participation_log()
{
    return $this->table->create_table('{db_prefix}pc_participation_log',
        array(
            array('name' => 'id_pc',          'type' => 'int', 'size' => 10, 'unsigned' => true, 'default' => 0),
            array('name' => 'id_member',       'type' => 'int', 'size' => 10, 'unsigned' => true, 'default' => 0),
            array('name' => 'id_pm',  'type' => 'int', 'size' => 10, 'unsigned' => true, 'default' => 0),
            array('name' => 'action',         'type' => 'varchar', 'size' => 255, 'default' => ''),
            array('name' => 'action_time',   'type' => 'int', 'size' => 10, 'unsigned' => true, 'default' => 0),
        ),
        array(
            array('name' => 'action_index',        'columns' => array('id_pc', 'id_member', 'id_pm', 'action'), 'type' => 'key'),
            array('name' => 'ids_index',        'columns' => array('id_pc', 'id_member', 'id_pm'), 'type' => 'key'),
        ),
        array(),
        'ignore'
    );
}

Just a stub, I didn't put that much thought in it.
"action" would be some keyword like (joined, left, kicked, invited, accepted). The reason for the indexes... I'm not even sure action_index is really needed, while the other should help with the JOIN on other tables (I guess).
If you think it's something worth the time, then I would say the only one allowed to invite/kick should be the owner of the conversation that is the initiator, otherwise it would require another layer of permissions assignable at the conversation level that I feel would become quite easily a mess...

That said, I'm not sure if what I proposed is really in scope of the "conversations" that could happen via "personal messages" in a forum.

[emanuele45]

Dammit, sorry for closing it!
I counted the wrong number of tabs and hit enter by mistake...

[Vekseid]

Have you considered inviting/kicking/leaving conversations?

Yes, the invites_open determines if the author or others can invite. 0 - only admins with override can invite. 1 - only author or admins with override can invite. 2 - any conversation member can invite.

The status column determines if a conversation has been left/hidden, stickied, or ignored.

Another column, can_kick, could allow the author to kick people or not if they have the permission. Again, 0 - only for admins with the override permission, 1 - the author can kick participants.

Voting for kicks/invites should be a 2.1 thing. We're already well in advance of Xenforo's offering here.

A part of me wants to also denormalize the labels system, as it does rather suck.

[emanuele45]

/me is blind xD

hmm... I'm not convinced about giving special permissions in this case to admins. The reason is mostly psychological: every so often people participating in forums raise the question "can the admin see my conversations?" (to which the answer is "anybody with access to the database can see them"), mentioning admins with the risk of the word slip into the documentation or somewhere would make it confusing (I know... I'm also pretty sure the intention is not to have the fact that admins can manage members, but I'm also pretty sure one way or another it will end up around) for people that will start thinking admin can manage the participants to any conversation, and as a consequence they can see the conversations.
And anyway, the permission for admins would apply only to the conversations they are participating to, so it would create two types of conversations (those in which an admin con "do something" and all the others), making it even more confusing.

And the reason for the log, was to actually show when a certain person was invited/kicked/etc from the conversation, to make it more transparent to all the participants.

[emanuele45]

A part of me wants to also denormalize the labels system, as it does rather suck.

heh.
Well... since the direction is towards conversations, TBH I'm not even sure labels are actually useful at all. And anyway I wonder if they should (still) apply to each single message, or to a conversation.
This is another can of worms. 😆

[Spuds]

A part of me wants to also denormalize the labels system, as it does rather suck.

That is the nicest thing ever said about the labels system !

[Vekseid]

TBH I'm not even sure labels are actually useful at all.

My forum members would castrate you for saying such a thing. We are going on twelve million pms.

A threadmark/bookmark system for individual posts/pms would be a different concept. It's really popular and I've been asked to extend XF's bookmarks system to PMs on my Xenforo forum.

[emanuele45]

Vekseid wrote on 03/11/2019 02:39:

TBH I'm not even sure labels are actually useful at all. My forum members would castrate you for saying such a thing.

LOL /me runs!