You can see the Jive database schemas here:
Note: 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.
Core Application Database
The application database provides
storage for core content activity, and optionally, user data.
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. See the Database Configuration and Best Practices
section for more database-related information.
Date column type support varies widely across databases. Therefore,
the application specially encodes dates as 64-bit numbers. 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();
are always represented as numeric values: 0 for false and 1 for true.
The schema is comprised of a fact table that represents the events in Jive, and
corresponding dimension tables that represent the actors and objects that take part
in those events. Each column in the fact table contains a key that relates to an
entry in the corresponding dimension table. For example, the user_id column contains
ids that can be found in the jivedw_user table. A basic query against the analytics
schema will be a SELECT from the fact table, optionally performing INNER JOINS
against the dimension tables. The dimension tables are used both to constrain the
results of the query based on their attributes and to provide attribute data, e.g.,
the usernames of users.
Date/time values are stored as GMT -- in other words, the time zone is not
stored. Applications using this data must make time zone conversions as an
offset of GMT. Dates in the analytics database are stored using the timestamp
data type of the DBMS.
When you provision the database you'll be using for analytics
data, be sure that it supports stored procedures. For example, on PostgreSQL you can
use the CREATE LANGUAGE command on your server to register the needed procedural
CREATE LANGUAGE plpgsql
Activity Engine Database
The Activity Engine schema stores the data that manages the activity streams,
trending content, people and relevance. This functions as the backend storage
for the Activity Engine Service.