Jive Forums 3 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 Type Conventions

Date column type support varies widely across databases. Therefore, Jive Forums specially encodes dates as VARCHAR values. Each date is a Java long value which is 0-padded to 15 characters. The long value is the internal representation of Java Date objects, which can be obtained with code such as the following: 

long currentDate = new Date().getTime();

Boolean values are always represented a numeric values: 0 for false and 1 for true.


Database Tables

Below is a description of each of the tables in the Jive Forums database schema. A yellow row denotes a primary key.


jiveAttachment (Message attachments)
Column Name Type Length Description
attachmentID NUMBER n/a Attachment ID (Primary Key)
messageID NUMBER n/a Message ID associated with this Attachment
filename VARCHAR 255 Name of File Attachment
filesize NUMBER n/a Size of Attachment in Bytes
contentType VARCHAR 50 MIME-type of Attachment
creationDate VARCHAR 15 Creation Date
modificationDate VARCHAR 15 Last Modified Date
 top of page


jiveAttachmentProp (name-value associations for an Attachment)
Column Name Type Length Description
attachmentID NUMBER n/a Attachment ID
name VARCHAR 255 Attachment Property Name
propValue VARCHAR 4000 Attachment Property Value
 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 VARCHAR 15 Creation Date
modificationDate VARCHAR 15 Last Modified Date
lft NUMBER n/a The "left" value for category in the column tree.
rgt NUMBER n/a The "right" value for category in the column tree.
 top of page


jiveCategoryProp (name-value associations for a Category)
Column Name Type Length Description
categoryID NUMBER n/a Category ID
name VARCHAR 255 Category Property Name
propValue VARCHAR 4000 Category 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
description VARCHAR 4000 Forum Description
modDefaultThreadVal NUMBER n/a Default Moderation Value upon Thread Creation
modMinThreadVal NUMBER n/a Field no longer used and will be removed in a future release.
modDefaultMsgVal NUMBER n/a Default Moderation Value upon Message Creation
modMinMsgVal NUMBER n/a Field no longer used and will be removed in a future release.
creationDate VARCHAR 15 Creation Date
modificationDate VARCHAR 15 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.
 top of page


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


jiveGroup (user 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 VARCHAR 15 Creation Date
modificationDate VARCHAR 15 Last Modified Date
 top of page


jiveGroupPerm (permission for a Group in a Forum)
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
permission NUMBER n/a Permission Value that Group has for Forum
 top of page


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


jiveGroupUser (associates Users with 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


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 ID�s (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
userID NUMBER n/a User ID of Message�s Author
subject VARCHAR 255 Subject Line 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 of Message (Used For Incentive Schemes for Posting)
creationDate VARCHAR 15 Creation Date
modificationDate VARCHAR 15 Last Modified Date
 top of page


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


jiveModeration (audit table of 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., Forum, Message, Thread)
userID NUMBER n/a User ID of user who made moderation decisions
modDate VARCHAR 15 Date of Modification/Decision
modValue NUMBER n/a Resulting Moderation Value
 top of page


jiveReadTracker
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, attachment) (Primary Key)
objectID NUMBER n/a ID of Object read (Primary Key)
readDate VARCHAR 15 Date read
 top of page



jiveReward (tracks Reward Points posting incentives)
Column Name Type Length Description
userID NUMBER n/a User ID
creationDate VARCHAR 15 Creation Date
rewardPoints NUMBER n/a Reward Points
messageID NUMBER n/a Message ID for which these points were awarded
threadID NUMBER n/a Thread ID for which these points were awarded
 top of page


jiveUserReward (track user's reward points)
Column Name Type Length Description
userID NUMBER n/a User ID (Primary Key)
rewardPoints NUMBER n/a Reward points for this user (Primary Key)
 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 this Thread to which this Thread belongs
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 VARCHAR 15 Creation Date
modificationDate VARCHAR 15 Modification Date
 top of page


jiveThreadProp (name-value associations for a Thread)
Column Name Type Length Description
threadID NUMBER n/a Thread ID
name VARCHAR 100 Thread Property Name
propValue VARCHAR 4000 Thread 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 (MD5 hash)
name VARCHAR 100 Name
nameVisible NUMBER n/a Is Name Visible (Boolean)
email VARCHAR 100 Email Address
emailVisible NUMBER n/a Is Email Visible (Boolean)
creationDate VARCHAR 15 Creation Date
modificationDate VARCHAR 15 Last Modified Date
 top of page


jiveUserPerm (association of Users with Permissions in Forums)
Column Name Type Length Description
objectType NUMBER n/a Type of Object read (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.
permission NUMBER n/a Permission that User has in Forum
 top of page


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


jiveUserRoster (for future use in implementing buddy rosters or lists)
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


jiveWatch (tracks Users watching categories, forums, threads, etc.)
Column Name Type Length Description
userID NUMBER n/a ID of User who is watching
objectID NUMBER n/a ID of the object being watched
objectType NUMBER n/a The type of the object being watch (forum, thread, category)
watchType NUMBER n/a Type of Watch. (normal watch or an email watch)
expirable NUMBER n/a Will the watch be automatically deleted/expired (Boolean)
 top of page






































Copyright © Jive Software, 1998-2003