Database schemas

Here you can find a high-level overview of the Jive database shemas.

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.

The overview of the schemas is provided below.

Core application database

The application database provides storage for core content activity, and optionally, user data. You can find the database schema 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.
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();

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

Analytics database

You can find the database schema 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.

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, for example, the usernames of users.

Date and time values are stored as GMT+0, 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.

For more information about the schema design, see Analytics DB Data Model in the Jive Plugin Developer space on Worx.

Note: Databases for the Analytics feature support only the Oracle and PostgreSQL DBMSes.
Attention:

When you provision the database you are using for analytics data, make 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 language:

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.

You can find the database schema 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.