Database: Frequently Asked Questions

Do you use any database frameworks?

We use Spring JDBC templates for direct database access. We almost always do that in a DAO (Data Access Object).

Can I have some example of common usages of Spring JdbcTemplate?

Here is a simple example using Spring JdbcTemplate:

private static final String REMOVE_THING = "DELETE FROM jiveThing WHERE thingID = ?";
 public void removeThing(long id) throws DAOException {
     try {
         getSimpleJdbcTemplate().update(REMOVE_THING, id);
     }
     catch (DataAccessException e) {
         Log.error(e.getMessage(), e);
         throw new DAOException(e);
     }
 }

Here is an example using Spring RowMapper:

private static final String GET_THING = "SELECT t.thingID, t.name FROM jiveThing t WHERE t.thingID = ? ";
 public Thing getThing(long thingID) {
   Thing thing = null;
   try {
       thing = getSimpleJdbcTemplate()
                  .queryForObject(GET_THING, new ThingRowMapper(), 0, thingID);
    }
     catch (EmptyResultDataAccessException e) {
        // ignore, thing may not exist          
    }
     catch (DataAccessException e) {
        Log.error(e.getMessage(), e);
        throw new DAOException(e);
    }
    return descriptor;
 }
 private static final class ThingRowMapper implements ParameterizedRowMapper<Thing> {
    public Thing mapRow(ResultSet rs, int rowNum) throws SQLException {
        long thingID = rs.getLong(1);
        String name = rs.getString(2);
        return new Thing(thingID, name);
    }
 }

Do we use transactions?

Transactions are currently disabled in 2.5.0 and earlier. The JDBC connection is set with auto-commit to true.

Are there plans to support transactions?

This is planned for the next major release.

This will be through Spring transactions using annotations. You can plan for this support already by adding an @Transactional annotation on your method on one of your manager methods. Do not add this at the DAO layer. Most of these annotations are already in place in our code and inspecting the managers can give insight into what methods will be transactional moving forward.

What's the best developer reference to the database?

The best base reference is the jive.xml file from which .sql scripts are created. This represents the logical schema that our sqlgen tool uses to generate physical schemas for each database platform.

Do you use any unique data type conventions?

Date column type support varies widely across databases. Therefore, Jive SBS 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 a numeric values: 0 for false and 1 for true.

To report dates out of PostgreSQL you can use the following function:

select (timestamp 'epoch' + u.creationDate/1000 * interval '1 second') as joinDate from jiveUser u

What is sqlgen?

This is an internal tool for generating database schemas from an abstract platform-agnostic specification. sqlgen scripts are XML files that define the schemas and upgrade actions. sqlgen generates the appropriate DDL or DML for the target platform. The best reference is jive.xml, the file sqlgen uses to generate its output. Here is an example table creation snippet from the jive.xml file:

<table name="jiveContainerAprvr" description="Container approvers.">
     <column name="containerType" type="int" nullable="false" description="Container Type."/>
     <column name="containerID" type="bigint" nullable="false" description="Container ID."/>
     <column name="userID" type="bigint" nullable="false" description="Approver User ID."/>
     <index type="primary" name="jiveCAprvr_pk" column="containerType,containerID,userID"/>
 </table>

<schema name="AddDocumentStatusColumn">
    <alter table="jiveDocVersion" type="add" description="Add a status column">
       <column name="status" type="smallint" nullable="false" default="2"/>
    </alter>
 </schema>

How do I create a blob/clob in sqlgen?

If you create a string or binary type, sqlgen automaticly converts it to a TEXT/BLOB if it exceeds the varchar limit on that DB.  So you should be able to specify a vachar(65000) and have it work for what you need.

XML would look like this:

<column name="body" type="text" nullable="true" index_none="true" unicode="true" description="body of content"/>

What are some best practices around databases?

If you don't use Spring JDBCTemplate, you have to use the ConnectionManager to open and close connections.

// Always grab your connections in a try/(catch)/finally
 try {
   con = ConnectionManager.getConnection();
   . . .
 } finally {
   ConnectionManager.close(pstmt, con);
 }

Use the SequenceManager to generate IDs to get a new unique value for your primary key.

SequenceManager.nextID(JiveConstants.ATTACHMENT)

When using methods that return column data typed as Object, such as the following:

ResultSet.getObject()mysql>drop database clearspace;
mysql>create database clearspace;
mysql>use clearspace; (very important)
mysql>source \path\to\database\scripts\included\in\the\source\distro\database\jive_clearspace_community_mysql.sql (no semi-colon for this command)

Or when using the Spring JDBC template methods queryForList and queryForMap without a row mapper, always cast numeric columns to Number then use the appropriate method to convert the value into the expected data type. This is because some database drivers do not correctly map column types to the expected Java type; for example, Oracle will map all numeric values to BigDecimal.

int intValue = ((Number) theMap.get("int_column")).intValue();

How do I switch databases from, say, MySQL to Oracle?

1. Edit  jive_startup.xml located in jiveHome and set the  <setup> element value to false.
2. Restart the application and run through the setup wizard — one of which will be the database settings where you can select a different database. For more information, see Configuring application with the Setup wizard.
3. If you're only switching the name of the database but not the type of database, then just open jive_startup.xml and change the database name there directly. There's no need to run through the setup screens again, provided you've already followed the steps above for creating the database tables by running the source command — or the equivalent for the DB you're using.