[cisco-voip] IM&Presence Persistent Chat My Rooms

[Brian Meade]

I'll probably post it on my GitHub once I get it cleaned up a bit!

On Tue, Dec 17, 2019, 2:40 PM Anthony Holloway <
avholloway+cisco-voip at gmail.com> wrote:

> All this mention of the Python file, and yet, no Python file.  What gives
> Brian?  ;)
>
> Thanks for the update!
>
> On Tue, Dec 17, 2019 at 1:36 PM Brian Meade <bmeade90 at vt.edu> wrote:
>
>> Just to update everyone on this, these are stored in the pep_node and
>> pep_node_items tables on the actual IM&Presence server.
>>
>> I was able to find that documented here-
>> https://bst.cloudapps.cisco.com/bugsearch/bug/CSCvm46828
>>
>> I was able to use a Python script to export the contents of pep_node and
>> pep_node_items and then insert those records into the new IM&Presence
>> cluster.  The pep_node table references the xcp_user_id which is different
>> than the actual user ID so you have to export those mappings from the old
>> cluster and then find the new xcp_user_id's on the new cluster.  XCP Router
>> must also be restarted after doing the Inserts.
>>
>> I was able to write a Python script to handle the export/import.
>>
>> On Wed, Dec 4, 2019 at 5:17 PM Anthony Holloway <
>> avholloway+cisco-voip at gmail.com> wrote:
>>
>>> It does seem to be held in the external database.  The IM&P server might
>>> just be compiling the my rooms list dynamically for the user at login,
>>> versus it being held in any one spot.
>>>
>>> TC_ROOMS Table
>>>
>>> The TC_ROOMS table contains information for group chat rooms.
>>>
>>> Column Name
>>>
>>> Postgres Datatype
>>>
>>> Oracle Datatype
>>>
>>> Microsoft SQL Datatype
>>>
>>> Not Null
>>>
>>> Description
>>>
>>> ROOM_JID
>>>
>>> VARCHAR (3071)
>>>
>>> VARCHAR2 (3071)
>>>
>>> varchar (3071)
>>>
>>> Yes
>>>
>>> The ID of the room.
>>>
>>> CREATOR_JID
>>>
>>> VARCHAR (3071)
>>>
>>> VARCHAR2 (3071)
>>>
>>> varchar (3071)
>>>
>>> Yes
>>>
>>> The ID of the user who created the room.
>>>
>>> SUBJECT
>>>
>>> VARCHAR (255)
>>>
>>> VARCHAR2 (255)
>>>
>>> varchar (255)
>>>
>>> Yes
>>>
>>> The current subject for the room.
>>>
>>> TYPE
>>>
>>> VARCHAR (32)
>>>
>>> VARCHAR2 (32)
>>>
>>> varchar (32)
>>>
>>> Yes
>>>
>>> The constraint check_type. This value must be either "ad-hoc" or
>>> "persistent".
>>>
>>> CONFIG
>>>
>>> TEXT
>>>
>>> CLOB
>>>
>>> text
>>>
>>> Yes
>>>
>>> The entire packet from the last time the room was configured. This
>>> information enables the room to be reconfigured when the room is recreated
>>> (for example, at start-up).
>>>
>>> SPACKET
>>>
>>> TEXT
>>>
>>> CLOB
>>>
>>> text
>>>
>>> Yes
>>>
>>> The entire packet from the last time the subject was set for the room.
>>> This information enables the room subject to be displayed when the room is
>>> recreated.
>>>
>>> START_MSG_ID
>>>
>>> BIGINT
>>>
>>> NUMBER (19)
>>>
>>> bigint
>>>
>>> Yes
>>>
>>> A sequence number that is used to populate the MSG_ID column in the
>>> TC_MSGARCHIVE table.
>>>
>>> Do not modify this value.
>>>
>>> NEXT_MSG_ID
>>>
>>> BIGINT
>>>
>>> NUMBER (19)
>>>
>>> bigint
>>>
>>> Yes
>>>
>>> A sequence number that is used to populate the MSG_ID column in the
>>> TC_MSGARCHIVE table.
>>>
>>> Do not modify this value.
>>> TC_USERS Table
>>>
>>> The TC_USERS table contains roles and affiliations, alternate names, and
>>> other data associated with group chat room users.
>>>
>>> Column Name
>>>
>>> Postgres Datatype
>>>
>>> Oracle Datatype
>>>
>>> Microsoft SQL Datatype
>>>
>>> Not Null
>>>
>>> Description
>>>
>>> ROOM_JID
>>>
>>> VARCHAR (3071)
>>>
>>> VARCHAR2 (3071)
>>>
>>> varchar (3071)
>>>
>>> Yes
>>>
>>> The ID of the room.
>>>
>>> REAL_JID
>>>
>>> VARCHAR (3071)
>>>
>>> VARCHAR2 (3071)
>>>
>>> varchar (3071)
>>>
>>> Yes
>>>
>>> The ID of a user in the room. This value is the actual ID of the user,
>>> rather than an alternate name.
>>>
>>> ROLE
>>>
>>> VARCHAR (32)
>>>
>>> VARCHAR2 (32)
>>>
>>> varchar (32)
>>>
>>> Yes
>>>
>>> The role of the user in the room. This value is constrained to one of
>>> the following: "none", "hidden", "visitor", "participant", or
>>> "moderator".
>>>
>>> AFFILIATION
>>>
>>> VARCHAR (32)
>>>
>>> VARCHAR2 (32)
>>>
>>> varchar (32)
>>>
>>> Yes
>>>
>>> The affiliation of the user in the room. This value is constrained to
>>> one of the following: "none", "outcast", "member", "admin", or "owner".
>>>
>>> NICK_JID
>>>
>>> VARCHAR (3071)
>>>
>>> VARCHAR2 (3071)
>>>
>>> varchar (3071)
>>>
>>> Yes
>>>
>>> The ID of the room, plus the alternate name for the user. The format is
>>> room at tc-server/nick.
>>>
>>> REASON
>>>
>>> VARCHAR (255)
>>>
>>> VARCHAR2 (255)
>>>
>>> varchar (255)
>>>
>>> Yes
>>>
>>> The reason entered when the user's affiliation was last changed.
>>>
>>> INITIATOR_JID
>>>
>>> VARCHAR (3071)
>>>
>>> VARCHAR2 (3071)
>>>
>>> varchar (3071)
>>>
>>> Yes
>>>
>>> The ID of the room in which the configuration change occurred.
>>>
>>> Source:
>>> https://www.cisco.com/c/en/us/td/docs/voice_ip_comm/cucm/im_presence/database_setup/11_5_1/cup0_b_database-setup-guide-imp-115/cup0_b_database-setup-guide-imp-115_chapter_0100.html#CUP0_RF_TCBD4E17_00
>>>
>>>
>>>
>>>
>>>
>>> On Wed, Dec 4, 2019 at 3:40 PM Brian Meade <bmeade90 at vt.edu> wrote:
>>>
>>>> Does anyone know where the My Rooms information is stored?  It doesn't
>>>> seem to be in the persistent chat database or stored locally on the
>>>> client.  Is this stored on IM&P somewhere?
>>>> _______________________________________________
>>>> cisco-voip mailing list
>>>> cisco-voip at puck.nether.net
>>>> https://puck.nether.net/mailman/listinfo/cisco-voip
>>>>
>>>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://puck.nether.net/pipermail/cisco-voip/attachments/20191231/b57623c6/attachment.htm>