Contents

• PostgreSQL Database Tables
• TC_ROOMS Table
• TC_USERS Table
• TC_MESSAGES Table
• TC_TIMELOG Table
• TC_MSGARCHIVE Table
• JM Table
• Sample SQL Queries for the JM Table
• All Instant Messages Sent by a Specific User
• All Instant Messages Received by a Specific User
• All Instant Messages that Contain a Specific Word
• All Instant Messages Conversations and Chat Rooms from a Specific Date

PostgreSQL Database Tables

---

This chapter provides information about the PostgreSQL database tables that are created in your schema to support the IM and Presence server.

Note	By default, IM and Presence generates 27 tables in the PostgreSQL database but at present it only uses the tables described in this module.

• TC_ROOMS Table
• TC_USERS Table
• TC_MESSAGES Table
• TC_TIMELOG Table
• TC_MSGARCHIVE Table
• JM Table

TC_ROOMS Table

The TC_ROOMS table contains information for group chat rooms.

Column Name	Seawall Type	Datatype	Not Null	Description
ROOM_JID	SC_STRING	VARCHAR (3071)	Yes	The ID of the room.
CREATOR_JID	SC_STRING	VARCHAR (3071)	Yes	The ID of the user who created the room.
SUBJECT	SC_STRING	VARCHAR (255)	Yes	The current subject for the room.
TYPE	SC_STRING	VARCHAR (32)	Yes	The constraint check_type. This value must be either "ad-hoc" or "persistent".
CONFIG	SC_TEXT	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	SC_TEXT	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	SC_LONG	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	SC_LONG	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	Seawall Type	Datatype	Not Null	Description
ROOM_JID	SC_STRING	VARCHAR (3071)	Yes	The ID of the room.
REAL_JID	SC_STRING	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	SC_STRING	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	SC_STRING	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	SC_STRING	VARCHAR (3071)	Yes	The ID of the room, plus the alternate name for the user. The format is room@tc-server/nick.
REASON	SC_STRING	VARCHAR (255)	Yes	The reason entered when the user's affiliation was last changed.
INITIATOR_JID	SC_STRING	VARCHAR (3071)	Yes	The ID of the room in which the configuration change occurred.

TC_MESSAGES Table

The TC_MESSAGES table contains messages that are sent in group chat rooms.

Column Name	Seawall Type	Datatype	Not Null	Description
MSG_ID	SC_LONG	BIGINT	Yes	The ID of the message. The MSG_ID is a unique identifier for each message per chat room; it is not globally unique.
ROOM_JID	SC_STRING	VARCHAR (3071)	Yes	The ID of the room to which the message was sent.
STAMP	SC_TIMESTAMP	TIMESTAMP	Yes	The date and time the message was sent.
MSG	SC_TEXT	TEXT	Yes	The entire message.

TC_TIMELOG Table

The TC_TIMELOG table contains the time that users enter and exit specific group chat rooms. This table may be used in conjunction with the other TC tables to recreate group chat conversations and to determine which users viewed the conversations.

Column Name	Seawall Type	Datatype	Not Null	Description
REAL_JID	SC_STRING	VARCHAR (3071)	Yes	The ID of the user who is entering or leaving the room.
NICK_JID	SC_STRING	VARCHAR (3071)	Yes	The ID of the room, plus the alternate name for the user. The format is room@tc-server/nick.
DIRECTION	SC_STRING	VARCHAR (1)	Yes	Indicates whether the user entered (E) or left (L) the room. Constrained to the values "E" and "L".
STAMP	SC_TIMESTAMP	TIMESTAMP	Yes	The date and time at which the user entered or left the room.

TC_MSGARCHIVE Table

The TC_MSGARCHIVE table stores messages and associated information for group chat rooms.

Note

This table archives all messages if you turn on group chat on IM and Presence. Select the option Archive all room messages on Cisco Unified CM IM and Presence Administration > Messaging > Conferencing and Persistent Chat. See the Deployment Guide for IM and Presence for information on the group chat feature.

Column Name	Seawall Type	Datatype	Not Null	Description
MSG_ID	SC_LONG	BIGINT	Yes	A unique identifier for the message.
TO_JID	SC_STRING	VARCHAR (3071)	Yes	The ID of the room that received the message.
FROM_JID	SC_STRING	VARCHAR (3071)	Yes	The ID of the user who sent the message.
NICK_JID	SC_STRING	VARCHAR (3071)	Yes	The ID of the room, plus the alternate name of the sender; for example: room@conference.exmpl.com/nick
SENT_DATE	SC_TIMESTAMP	TIMESTAMP	Yes	The date on which the message was sent.
MSG_TYPE	SC_STRING	VARCHAR (1)	Yes	The first character of the type attribute of the message. The possible values are "c" (chat), "n" (normal), "g" (groupchat), "h" (headline), and "e" (error).
BODY_LEN	SC_INTEGER	INT	Yes	The length in characters of the message body.
MESSAGE_LEN	SC_INTEGER	INT	Yes	The length in characters of the message, including the subject and body.
BODY_STRING	SC_STRING	VARCHAR (4000)	Yes	The message body.
MESSAGE_ STRING	SC_STRING	VARCHAR (4000)	Yes	The entire raw packet.
BODY_TEXT	SC_TEXT	TEXT	Yes	If the message body exceeds 4000 characters, it is stored in this field rather than the BODY_STRING field.
MESSAGE_TEXT	SC_TEXT	TEXT	Yes	If the entire raw packet exceeds 4000 characters, it is stored in this column rather than in the MESSAGE_STRING column.
SUBJECT	SC_STRING	VARCHAR (255)	Yes	The subject of a message.

JM Table

The JM table stores conversations and associated information for the Message Archiver component. The Message Archiver component provides the native compliance functionality on IM and Presence.

Column Name	Seawall Type	Datatype	Not Null	Description
TO_JID	SC_STRING	VARCHAR (3071)	Yes	The ID of the user who is sending the message being archived.
FROM_JID	SC_STRING	VARCHAR (3071)	Yes	The ID of the user who is receiving the message being archived.
SENT_DATE	SC_TIMESTAMP	TIMESTAMP	Yes	The date the message was sent.
SUBJECT	SC_STRING	VARCHAR (128)	Yes	The subject line of the message that is being archived.
THREAD_ID	SC_STRING	VARCHAR (128)	Yes	The thread ID of the message that is being archived.
MSG_TYPE	SC_STRING	VARCHAR (1)	Yes	The first character of the message's type attribute. The possible values are "c" (chat), "n" (normal), "g" (groupchat), "h" (headline), and "e" (error).
DIRECTION	SC_STRING	VARCHAR (1)	Yes	Indicates whether the message is "O" (outgoing) or "I" (incoming”). If the message is sent between users on the same server, it will be logged twice: once as Outgoing and once as Incoming.
BODY_LEN	SC_INTEGER	INT	Yes	The number of characters in the message body.
MESSAGE_LEN	SC_INTEGER	INT	Yes	The number of characters in the message, including the subject and the body.
BODY_STRING	SC_STRING	VARCHAR (4000)	Yes	The message body.
MESSAGE_ STRING	SC_STRING	VARCHAR (4000)	Yes	The entire raw packet.
BODY_TEXT	SC_TEXT	TEXT	Yes	If the message body exceeds 4000 characters, it is stored in this field rather than the BODY_STRING field.
MESSAGE_TEXT	SC_TEXT	TEXT	Yes	If the entire raw packet exceeds 4000 characters, it is stored in this field rather than in the MESSAGE_STRING field.
HISTORY_FLAG	SC_STRING	VARCHAR (1)	Yes	Used when room history messages are sent to new participants (upon entering an existing room). This allows you to distinguish between messages received while actively participating in a room and those received as part of a history push. The latter message type is flagged with HISTORY_FLAG="H" in the database. Otherwise, this column is set to "N."

• Sample SQL Queries for the JM Table

Sample SQL Queries for the JM Table

This section contains some sample SQL queries that you can run on the JM table to extract specific information. The following queries select all columns from the table but you can be more selective about which information you want to include in your SQL queries.

• All Instant Messages Sent by a Specific User
• All Instant Messages Received by a Specific User
• All Instant Messages that Contain a Specific Word
• All Instant Messages Conversations and Chat Rooms from a Specific Date

All Instant Messages Sent by a Specific User

The following SQL query returns all instant messages sent by a specific user:

SELECT to_jid, sent_date, subject, thread_id, msg_type, direction, body_len, message_len,

body_string, message_string, body_text, message_text, history_flag

FROM jm

WHERE from_jid like 'bob@cisco.com%';

All Instant Messages Received by a Specific User

The following SQL query returns all instant messages received by a specific user:

SELECT from_jid, sent_date, subject, thread_id, msg_type, direction, body_len,

message_len, body_string, message_string, body_text, message_text, history_flag

FROM jm

WHERE to_jid like 'bob@cisco.com%';

All Instant Messages that Contain a Specific Word

The following SQL query returns all instant messages that contain a specific word:

SELECT to_jid, from_jid, sent_date, subject, thread_id, msg_type, direction, body_len,

message_len, body_string, message_string, body_text, message_text, history_flag

FROM jm

WHERE LOWER(body_string) like LOWER('%hello%');

All Instant Messages Conversations and Chat Rooms from a Specific Date

The following SQL query returns all instant messages, conversations and chat rooms from a specific date:

SELECT to_jid, from_jid, sent_date, subject, thread_id, msg_type, direction, body_len,

message_len, body_string, message_string, body_text, message_text, history_flag

FROM jm

WHERE CAST(sent_date AS Character(32)) like '2011-01-31%';