Jive Forums 4.2 Database Schema Guide

Introduction

This document outlines the data type conventions and tables in the Jive Forums database schema. Some information, like column indexes and foreign keys, is omitted. For this, please read the individual schema of the database you're interested in. Data types may also differ somewhat in each individual database schema.

Data Type Conventions

Database Tables

Below is a description of each of the tables in the Jive Forums database schema. A yellow row denotes a primary key and a green color represents something new or changed since Jive Forums 3.x. A red color represents something that's been deleted since Jive Forums 3.x.

Database Name Since
jiveAnnounce 4.0
jiveAnnounceProp 4.0
jiveAnswer 4.1
jiveAttachment (modified)  
jiveAttachData 4.0
jiveAttachmentProp  
jiveAvatar 4.1
jiveAvatarProp 4.1
jiveAvatarUser 4.1
jiveBatchWatch 4.0
jiveCategory  
jiveCategoryProp  
jiveForum (modified)  
jiveForumProp  
jiveGroup  
jiveGroupPerm  
jiveGroupProp  
jiveGroupUser  
jiveHTTPReadStatSession 4.0
jiveID  
jiveMessage (modified)  
jiveMessageProp  
jiveModeration  
jiveNNTPReadStatSession 4.0
jivePMessage 4.0
jivePMessageFldr 4.0
jivePMessageProp 4.0
jivePoll 4.0
jivePollOption 4.0
jivePollVote 4.0
jiveQuestion 4.1
jiveQuestionProp 4.1
jiveProperty 4.0
jiveRating 4.0
jiveRatingType 4.0
jiveReadStat 4.0
jiveReadStatSession 4.0
jiveReadTracker  
jiveReward  
jiveSearch 4.0
jiveSearchClick 4.0
jiveSearchCriteria 4.0
jiveStatusLevel 4.1
jiveStatusLevelProp 4.1
jiveThread  
jiveThreadProp  
jiveUser  
jiveUserPerm  
jiveUserProp  
jiveUserReward  
jiveUserRoster  
jiveViewCount 4.0
jiveWatch  

jiveAnnounce (Announcements)
Column Name Type Length Description
announcementID NUMBER n/a Announcement ID (Primary Key)
objectType NUMBER n/a Object type associated with the announcement
objectID NUMBER n/a Object ID the announcement is associated with.
userID NUMBER n/a User who created the announcement.
subject VARCHAR 255 The subject of the announcement.
body LONG VARCHAR/CLOB/TEXT n/a The body of the announcement.
startDate NUMBER n/a Start date
endDate NUMBER n/a End date
 top of page


jiveAnnounceProp (Announcement Properties)
Column Name Type Length Description
announcementID NUMBER n/a Announcement ID (Primary Key)
name NUMBER n/a Property name (Primary Key)
propValue VARCHAR 4000 Property value.
 top of page


jiveAnswer (Question answers)
Column Name Type Length Description
threadID NUMBER n/a ID of the answer's thread (Primary Key)
messageID NUMBER n/a ID of the answer's message (Primary Key)
forumID NUMBER n/a ID of the answer's forum
userID NUMBER n/a ID of the answer's user (can be NULL)
helpfulAnswer NUMBER n/a Indicates if the answer was helpful (boolean 1 or 0)
correctAnswer NUMBER n/a Indicates if the answer was correct (boolean 1 or 0)
 top of page


jiveAttachment (Attachment Meta-Data)
Column Name Type Length Description
attachmentID NUMBER n/a Attachment ID (Primary Key).
objectType NUMBER n/a Type of object the attachment belongs to.
objectID (was: messageID) NUMBER n/a ID of object the attachment belongs to.
filename VARCHAR 255 Name of attachment.
filesize NUMBER n/a Size of attachment in bytes.
contentType VARCHAR 50 MIME-type of attachment.
creationDate NUMBER n/a Creation date.
modificationDate NUMBER n/a Last modified date.
 top of page


jiveAttachData (Binary Attachment Data)
Column Name Type Length Description
attachmentID NUMBER n/a Attachment ID (Primary Key).
attachmentData BLOB n/a Binary data of the attachment.
 top of page


jiveAttachmentProp (Attachment Properties)
Column Name Type Length Description
attachmentID (Primary Key) NUMBER n/a Attachment ID.
name (Primary Key) VARCHAR 255 Poperty name.
propValue VARCHAR 4000 Property value.
 top of page


jiveAvatar (Avatar definitions)
Column Name Type Length Description
avatarID (Primary Key) NUMBER n/a Avatar ID.
modValue NUMBER n/a The moderation value for the avatar
ownerID NUMBER n/a The ID of the person who created the avatar.
 top of page


jiveAvatarProp (Avatar Properties)
Column Name Type Length Description
avatarID (Primary Key) NUMBER n/a Avatar ID.
name (Primary Key) VARCHAR 255 Poperty name.
propValue VARCHAR 4000 Property value.
 top of page


jiveAvatarUser (User avatars)
Column Name Type Length Description
avatarID (Primary Key) NUMBER n/a Avatar ID.
userID (Primary Key) NUMBER n/a ID of the user associated with the avatar.
 top of page


jiveBatchWatch (Batch Email Watches)
Column Name Type Length Description
userID NUMBER n/a The user associated with the batch watch.
frequency VARCHAR 50 CRON expression for how often batch emails should be sent.
prevEmailDate NUMBER n/a Date that last batch email was sent.
 top of page


jiveCategory (Category Data)
Column Name Type Length Description
categoryID NUMBER n/a Category ID (Primary Key)
name VARCHAR 255 Category name.
description VARCHAR 4000 Category description.
creationDate NUMBER n/a Creation date.
modificationDate NUMBER n/a Last modified date.
lft NUMBER n/a The "left" value for category in the category tree structure.
rgt NUMBER n/a The "right" value for category in the category tree structure.
 top of page


jiveCategoryProp (Category Properties)
Column Name Type Length Description
categoryID NUMBER n/a Category ID.
name VARCHAR 255 Property name.
propValue VARCHAR 4000 Property value.
 top of page


jiveForum (Forum Data)
Column Name Type Length Description
forumID NUMBER n/a Forum ID (Primary Key)
name VARCHAR 255 Forum name.
nntpName VARCHAR 255 NNTP name (unique).
description VARCHAR 4000 Forum description.
modDefaultThreadVal NUMBER n/a Default moderation value for new threads.
modMinThreadVal NUMBER n/a Field no longer used and has been removed.
modDefaultMsgVal NUMBER n/a Default moderation value upon message creation
modMinMsgVal NUMBER n/a Field no longer used and has been removed.
creationDate NUMBER n/a Creation date
modificationDate NUMBER n/a Last modified date
categoryIndex NUMBER n/a The index of the forum in the category (used for sorting the forums in a category).
categoryID NUMBER n/a ID of the category that the forum belongs to.
forumIndexCounter NUMBER n/a Highest forum index value from the jiveMessage table, used to sequentially mark messages in a forum.
 top of page


jiveForumProp (Forum Properties)
Column Name Type Length Description
forumID NUMBER n/a Forum ID.
name VARCHAR 255 Property name.
propValue VARCHAR 4000 Property value.
 top of page


jiveGroup (Group Data)
Column Name Type Length Description
groupID NUMBER n/a Group ID (Primary Key).
name VARCHAR 255 Group name.
description VARCHAR 4000 Group description.
creationDate NUMBER n/a Creation date.
modificationDate NUMBER n/a Last modified date.
 top of page


jiveGroupPerm (Group Permissions)
Column Name Type Length Description
objectType NUMBER n/a Object type (e.g., Forum, Message, Thread).
objectID NUMBER n/a ID of the object referenced type the objectType column.
groupID NUMBER n/a Group ID.
permissionType NUMBER n/a The type of perm - additive or negative.
permission NUMBER n/a Permission value (bitmask of all permissions the group has on the object).
 top of page


jiveGroupProp (Group Properties)
Column Name Type Length Description
groupID NUMBER n/a Group ID.
name VARCHAR 100 Property name.
propValue VARCHAR 4000 Property value.
 top of page


jiveGroupUser (Maps Users to Groups)
Column Name Type Length Description
groupID NUMBER n/a Group ID (Primary Key).
userID NUMBER n/a User ID (Primary Key).
administrator NUMBER n/a Adminstrator (Boolean) (Primary Key).
 top of page


jiveHTTPReadStatSession (HTTP-Specific Read Stats Info)
Column Name Type Length Description
referrer VARCHAR 255 The web referrer (URL) for the session.
userAgent VARCHAR 255 The visitor's user agent (browser, OS ver, etc).
IP VARCHAR 16 The visitor's IP.
country VARCHAR 4 The country code of the visitor.
bytesSent NUMBER n/a The number of bytes transferred over the session.
sessionID NUMBER n/a ID of the read stat session.
 top of page


jiveID (Used for Unique ID Sequence Generation)
Column Name Type Length Description
idType NUMBER n/a ID type (e.g., Forum, Thread, Message, Group, User) (Primary Key).
id NUMBER n/a Next available block of IDs (used for database-independent ID sequence generator).
 top of page



jiveMessage (Message Data)
Column Name Type Length Description
messageID NUMBER n/a Message ID (Primary Key).
parentMessageID NUMBER n/a Parent message ID.
threadID NUMBER n/a ID of thread to which message belongs.
forumID NUMBER n/a ID of forum to which message belongs.
forumIndex NUMBER n/a An increasing sequence value for messages in the forum; primarily used for NNTP.
userID NUMBER n/a User ID of message's author.
subject VARCHAR 255 Subject of message.
body LONG VARCHAR/CLOB/TEXT n/a Message body.
modValue NUMBER n/a Moderation value of message.
rewardPoints NUMBER n/a Reward points for message (used for incentive schemes for posting).
creationDate NUMBER n/a Creation date.
modificationDate NUMBER n/a Last modified date.
 top of page


jiveMessageProp (Message Properties)
Column Name Type Length Description
messageID NUMBER n/a Message ID.
name VARCHAR 100 Property name.
propValue VARCHAR 4000 Property value.
 top of page


jiveModeration (Audit Table for Moderation Decisions)
Column Name Type Length Description
objectID NUMBER n/a Object ID of object which has had a change in moderation value.
objectType NUMBER n/a Object Type (e.g., Message, Thread).
userID NUMBER n/a User ID of user who made moderation decision.
modDate NUMBER n/a Date of modification/decision.
modValue NUMBER n/a Resulting moderation value.
 top of page


jiveNNTPReadStatSession (NNTP-Specific Read Stats Info)
Column Name Type Length Description
creationDate NUMBER n/a The date the session was created.
endDate NUMBER n/a The date the session ended.
bytesReceived NUMBER n/a The number of bytes sent out for the entirety of the session.
bytesSent NUMBER n/a The number of bytes transferred over the session.
IP VARCHAR 16 The visitor's IP.
country VARCHAR 4 The country code of the visitor.
sessionID NUMBER n/a ID of the read stat session.
 top of page


jivePMessage (Private Messages)
Column Name Type Length Description
pMessaegID NUMBER n/a Private message ID (Primary Key).
ownerID NUMBER n/a The user whose mailbox the private message is in. For example, the ownerID is different from the recipient ID when the message is a draft.
senderID NUMBER n/a The user who sent the private message.
recipientID NUMBER n/a The user who received the private message.
subject VARCHAR 255 The subject of the private message.
body LONG VARCHAR/CLOB/TEXT n/a The body of the private message.
readStatus NUMBER n/a The read status - read or unread.
folderID NUMBER n/a The ID of the folder the message lives in.
pMessageDate NUMBER n/a The private message creation date.
 top of page


jivePMessageFldr (Private Message Folders)
Column Name Type Length Description
folderID NUMBER n/a Private message folder ID (Primary Key).
userID NUMBER n/a Owner of the folder.
name VARCHAR 255 Name of the folder.
 top of page


jivePMessageProp (Private Message Properties)
Column Name Type Length Description
pMessageID NUMBER n/a Private message ID (Primary Key).
name VARCHAR 100 Property name.
propValue VARCHAR 4000 Property value.
 top of page


jivePoll (Polls)
Column Name Type Length Description
pollID NUMBER n/a Poll ID (Primary Key)
objectType NUMBER n/a Object type associated with the poll.
objectID NUMBER n/a Object ID the poll is associated with.
userID NUMBER n/a The user who posted the poll or NULL if it was anonymous.
name VARCHAR 255 Poll Name
description VARCHAR 4000 Poll Description
pollMode NUMBER n/a A mask for various poll options.
creationDate NUMBER n/a Creation date.
modificationDate NUMBER n/a Last modified date.
startDate NUMBER n/a The date the poll becomes active.
endDate NUMBER n/a The date the poll becomes inactive.
expireDate NUMBER n/a The date the poll will be deleted from the system.
 top of page


jivePollOption (Poll options)
Column Name Type Length Description
pollID NUMBER n/a Poll ID (Primary Key).
optionIndex NUMBER n/a The index of the poll option for ordering (Primary Key).
optionText VARCHAR 255 The name of the option.
 top of page


jivePollVote (Stores Votes in Polls)
Column Name Type Length Description
pollID NUMBER n/a Poll ID (Primary Key).
userID NUMBER n/a The user who voted.
guestID VARCHAR 255 A unique identifier for a guest to prevent double voting.
optionIndex NUMBER n/a The index of the option the poll is tied to.
voteDate NUMBER n/a The date the vote was made.
 top of page


jiveQuestion (Questions)
Column Name Type Length Description
threadID NUMBER n/a ID of the thread associated with this thread (Primary Key).
forumID NUMBER n/a The ID of the forum the question is in.
userID NUMBER n/a The user who created the question.
creationDate NUMBER n/a Creation date of the question.
resolutionDate NUMBER n/a The date the question was resolved (can be NULL).
resolutionState NUMBER n/a The state the question is in.
 top of page


jiveQuestionProp (Question Properties)
Column Name Type Length Description
questionID NUMBER n/a Question ID (Primary Key).
name VARCHAR 100 Property name.
propValue VARCHAR 4000 Property value.
 top of page


jiveProperty (Jive Configuration Data)
Column Name Type Length Description
name VARCHAR 100 Property name (Primary Key).
propValue VARCHAR 4000 Property value.
 top of page


jiveRating (Object Ratings)
Column Name Type Length Description
objectID NUMBER n/a ID of the object the rating is associated with.
objectType NUMBER n/a Type of object the rating is associated with.
userID NUMBER n/a The user making the rating.
score NUMBER n/a The score of the rating.
 top of page


jiveRatingType (Rating Types)
Column Name Type Length Description
score NUMBER n/a The score value of the rating.
description VARCHAR 255 The description of the rating, ie "Helpful".
 top of page


jiveReadStat (Read Statistics)
Column Name Type Length Description
readStatID NUMBER n/a ID of the read stat.
userID NUMBER n/a ID of the user making the read.
objectType NUMBER n/a Type of object being read.
objectID NUMBER n/a The ID of the object being read.
creationDate NUMBER n/a The date of the object read.
sessionID NUMBER n/a The ID of the session where the read stat occurred.
 top of page


jiveReadStatSession (Read Statistics Sessions)
Column Name Type Length Description
sessionID NUMBER n/a ID of the read stat session.
visitorID VARCHAR 32 A unique identifier for a user or a guest.
creationDate NUMBER n/a The date the session was created.
 top of page


jiveReadTracker (Tracks Which Messages a User has Read)
Column Name Type Length Description
userID NUMBER n/a User ID of reader (Primary Key).
objectType NUMBER n/a Type of Object read (thread, message, etc.) (Primary Key).
objectID NUMBER n/a ID of object read (Primary Key).
readDate NUMBER n/a Date read.
 top of page



jiveReward (Tracks Reward Points Given)
Column Name Type Length Description
userID NUMBER n/a User ID.
creationDate NUMBER n/a Creation date.
rewardPoints NUMBER n/a Reward points.
messageID NUMBER n/a Message ID for which points were awarded.
threadID NUMBER n/a Thread ID for which points were awarded.
 top of page


jiveSearch (Logs all Searches)
Column Name Type Length Description
searchID NUMBER n/a Search ID (Primary Key).
searchType NUMBER n/a The type of search. For Jive Forums this will always be the same value.
userID NUMBER n/a ID of the user initiating the search
query TEXT n/a The search query that was executed.
searchDuration NUMBER n/a Time in milliseconds of search execution.
numResults NUMBER n/a The number of search results returned.
searchDate NUMBER n/a The date the search was made.
 top of page


jiveSearchClick (Logs all Clicks on Search Results)
Column Name Type Length Description
searchID NUMBER n/a Search ID (Primary Key).
messageID NUMBER n/a The ID of the message that was clicked.
clickDate NUMBER n/a The date the result was clicked.
 top of page


jiveSearchCriteria (Logs all Search Criteria)
Column Name Type Length Description
searchID NUMBER n/a Search ID.
criteriaName VARCHAR 100 The name of the search criteria.
criteriaValue VARCHAR 4000 The value of the search criteria.
 top of page


jiveStatusLevel (Status Levels)
Column Name Type Length Description
statusLevelID NUMBER n/a ID of the status level (Primary Key).
name VARCHAR 255 Status level name.
description VARCHAR 4000 Status level description.
forumID NUMBER n/a The ID of the forum the question is in.
imagePath VARCHAR 255 Path to an image for this status level (not null).
largeImagePath VARCHAR 255 Path to a large image for this status level (null).
minPoints NUMBER n/a The minimum points for this status level.
maxPoints NUMBER n/a The minimum points for this status level.
groupID NUMBER n/a The ID of the group associated with this status level.
 top of page


jiveStatusLevelProp (Status Level Properties)
Column Name Type Length Description
statusLevelID NUMBER n/a Status Level ID (Primary Key).
name VARCHAR 100 Property name.
propValue VARCHAR 4000 Property value.
 top of page


jiveThread (Thread Data)
Column Name Type Length Description
threadID NUMBER n/a Thread ID (Primary Key).
forumID NUMBER n/a ID of forum that thread belongs to.
rootMessageID NUMBER n/a ID of root message in thread.
modValue NUMBER n/a Moderation Value of Thread
rewardPoints NUMBER n/a Reward Points available in this Thread
creationDate NUMBER n/a Creation date.
modificationDate NUMBER n/a Last modified date.
 top of page


jiveThreadProp (Thread Properties)
Column Name Type Length Description
threadID NUMBER n/a Thread ID.
name VARCHAR 100 Property name.
propValue VARCHAR 4000 Property value.
 top of page


jiveUser (User Data)
Column Name Type Length Description
userID NUMBER n/a User ID (Primary Key).
username VARCHAR 30 Username.
passwordHash VARCHAR 32 Password Data (hex-encoded MD5 hash of actual password).
name VARCHAR 100 Full name.
nameVisible NUMBER n/a Privacy flag for showing name to other users (Boolean).
email VARCHAR 100 Email address.
emailVisible NUMBER n/a Privacy flag for showing email to other users (Boolean).
creationDate NUMBER n/a Creation date.
modificationDate NUMBER n/a Last modified date.
 top of page


jiveUserPerm (Permissions for Users)
Column Name Type Length Description
objectType NUMBER n/a Type of object (system, category, forum).
objectID NUMBER n/a ID of the object being referenced by the objectType column.
userID NUMBER n/a The ID of the user being granted the permission.
permissionType NUMBER n/a Indicates the type of perm - additive or negative.
permission NUMBER n/a Bitmask of permissions the user has on the object.
 top of page


jiveUserProp (User Properties)
Column Name Type Length Description
userID NUMBER n/a User ID (Primary Key).
name VARCHAR 100 Property name.
propValue VARCHAR 4000 Property value.
 top of page


jiveUserReward (Tracks a User's Reward Points)
Column Name Type Length Description
userID NUMBER n/a User ID (Primary Key).
rewardPoints NUMBER n/a Reward points for user.
 top of page


jiveUserRoster (Buddy List Data -- For Future Use)
Column Name Type Length Description
userID NUMBER n/a User ID (Primary Key).
subUserID NUMBER n/a User ID of roster member (Primary Key).
 top of page


jiveViewCount (Tracks Number of Reads on Categories, Threads, etc)
Column Name Type Length Description
objectID NUMBER n/a ID of the object (Primary Key).
objectType NUMBER n/a The type of the object (Primary Key).
parentObjectID NUMBER n/a ID of the object that this object "belongs" to. For example, a thread belongs to a forum, a forum belongs to a category, etc.
viewCount NUMBER n/a Number of times the object has been read.
 top of page


jiveWatch (Tracks Users Watching Categories, Forums, Threads, Users)
Column Name Type Length Description
userID NUMBER n/a ID of user who is watching (Primary Key).
objectID NUMBER n/a ID of the object being watched (Primary Key).
objectType NUMBER n/a The type of the object being watched (forum, thread, category, user) (Primay Key).
watchType NUMBER n/a Type of watch (normal watch, email watch, batch email watch) (Primary Key).
expirable NUMBER n/a Flag to indicate if the watch be automatically deleted/expired (Boolean).
 top of page






































Copyright © Jive Software, 1999-2004