Persist Social Data

Table of Contents

Requirements
Persist data in a relational database
Persist Social module data in Fusion
Troubleshooting database configuration
- Connection issues
Related articles

When using the Appkit Social module, an Appkit application can save social data (comments, bookmarks, and saved search queries) for logged in users. You can store the social data in a Fusion collection, have the application save the data in an in-memory database, the contents of which are lost when the application restarts, or configure Appkit to persist social data to a database that uses disk storage.

Requirements

Any JDBC-compliant relational database (such as Oracle, MySQL, or SQL Server) or a Fusion deployment
Network access from the Appkit application to the relational database or to Fusion

The database doesn’t have to be empty because Appkit prefixes all of its tables with a twigkit identifier. But for simplicity, we recommend using a separate schema, because you probably won’t must join Appkit tables with tables in other schemas.

Persist data in a relational database

Perform the tasks in this section to persist Appkit social data in a relational database.

Overview of procedure

To configure a database for persistence on disk, perform these steps, which are explained in more detail below:

Declare a Maven POM dependency in pom.xml to specify the database for storing the metadata that the Social module collects.
Configure a persistence.xml file with the appropriate elements, including those for database connections.
If necessary, perform other steps as required for specific databases.

The sections that follow describe these steps in more detail for the default Derby database and for other databases.

Use Derby

Declare a POM dependency

To persist social data in a disk-based database, declare a POM dependency in the file pom.xml, which is located at the root level of a project.

By default, the Appkit Social module uses the lightweight, in-memory Derby database to persist social data, as described in this POM dependency in pom.xml:

<dependency>
    <groupId>twigkit</groupId>
    <artifactId>twigkit.social.provider.db.derby.memory</artifactId>
    <version>${project.parent.version}</version>
</dependency>

If pom.xml does not contain the dependency for Derby, then the app project has not been modified to support the Social module. Perform [those steps] before proceeding.

To persist social metadata in a disk-based Derby database, declare this POM dependency:

<dependency>
    <groupId>twigkit</groupId>
    <artifactId>twigkit.social.db.provider.derby</artifactId>
    <version>${project.parent.version}</version>
</dependency>

Create and configure persistence.xml

You will must create and configure a persistence.xml file with the appropriate elements, including those for database connections.

Create the persistence.xml file here:
src/main/resources/META-INF/persistence.xml

See the Derby-memory and Derby examples.

Specify the type of Hibernate ID generator mappings

Hibernate ID generator mappings changed in Hibernate 5. Ensure use of the correct mappings as follows:

Apps created with versions of Appkit that precede version 4.2.

Ensure that both pre- and post-Hibernate 5 IDs are generated using the old mapping. Add this property to the persistence.xml file:
```
<property name="hibernate.id.new_generator_mappings" value="false" />
```
Apps created with Appkit 4.2 or later.

Apps can use new ID generator mappings. Add this property to the persistence.xml file:
```
<property name="hibernate.id.new_generator_mappings" value="true" />
```

Specify the Derby system directory

You can specify the Derby system directory, which is the directory that will contain the social database, by adding this flag to your Java options:

-Dderby.system.home=/my/derby/directory

If the system directory that you specify with derby.system.home does not exist at startup, Derby creates the directory automatically.

Use a different relational database

You can configure Appkit to persist social data in an on-disk JDBC-compliant relational database other than Derby (such as Oracle, MySQL, or SQL Server).

Other pre-configured versions are also available, including:

db.mysql
db.oracle
db.sqlserver

..1. Declare the POM dependency

To persist social metadata in a JDBC-compliant relational database, change the POM dependency to:

MySQL:

<dependency>
    <groupId>twigkit</groupId>
    <artifactId>twigkit.social.provider.db.mysql</artifactId>
    <version>${ppv}</version>
</dependency>

Oracle:

<dependency>
    <groupId>twigkit</groupId>
    <artifactId>twigkit.social.provider.db.oracle</artifactId>
    <version>${ppv}</version>
</dependency>

SQL Server:

<dependency>
    <groupId>twigkit</groupId>
    <artifactId>twigkit.social.provider.db.sqlserver</artifactId>
    <version>${ppv}</version>
</dependency>

..2. Create and configure persistence.xml

You will must create and configure a persistence.xml file with the appropriate elements, including those for database connections.

Create the persistence.xml file here:
src/main/resources/META-INF/persistence.xml

View examples of persistence.xml files for MySQL, Oracle, and SQL Server.

..3. Specify the type of Hibernate ID generator mappings

Hibernate ID generator mappings changed in Hibernate 5. Ensure use of the correct mappings as follows:

Apps created with versions of Appkit that precede version 4.2.

Ensure that both pre- and post-Hibernate 5 IDs are generated using the old mapping. Add this property to the persistence.xml file:
```
<property name="hibernate.id.new_generator_mappings" value="false" />
```
Apps created with Appkit 4.2 or later.

Apps can use new ID generator mappings. Add this property to the persistence.xml file:
```
<property name="hibernate.id.new_generator_mappings" value="true" />
```

Perform the tasks in this section to persist Appkit Social module data in Fusion.

Configure Fusion.
(Only for apps created with prior versions of App Studio or Appkit) Upgrade to the latest version of Appkit.
Enable social features.

Troubleshooting database configuration

Use these tips to troubleshoot problems with database configuration.

Connection issues

Many databases require the application to manage the connections, refreshing them and maintaining enough open connections to service the load on the database. In some cases the default connection management provided is inadequate for production setups.

If you notice bad performance, connections going stale, and associated errors (usually intermittently) the default connection pooling is probably inadequate for your environment.

To remedy this situation you can use a third-party connection pooling technology. We recommend 'C3P0', which can be configured with these simple steps:

Add the dependency for Hibernate c3p0 to the pom.xml:
```

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-c3p0</artifactId>
    <version>X.X.X.Final</version>
</dependency>
```
The version of Hibernate c3p0 you should use depends on the version of Appkit you are using. To begin with, try Hibernate version 4.1.7.Final (legacy) or 5.2.2.Final.

Add the configuration for C3P0 to the persistence.xml file:

<!-- c3p0 connection pool settings -->
<property name="hibernate.connection.provider_class" value="org.hibernate.connection.C3P0ConnectionProvider" />
<property name="hibernate.c3p0.max_size" value="100" />
<property name="hibernate.c3p0.min_size" value="0" />
<property name="hibernate.c3p0.acquire_increment" value="1" />
<property name="hibernate.c3p0.idle_test_period" value="300" />
<property name="hibernate.c3p0.max_statements" value="0" />
<property name="hibernate.c3p0.timeout" value="100" />

The settings above should be adequate for a standard Appkit application using the Social module, but they can be adjusted as desired.

Enable Social Features