Persist Social Data

When using the Appkit Social module, an Appkit application can save social data (comments, bookmarks, and saved search queries) for logged in users. By default, the application saves the data in an in-memory database, the contents of which are lost when the application restarts.

You can 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 need to 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 the following steps, which are explained in more detail below:

  1. Declare a Maven POM dependency in pom.xml to specify the database for storing the metadata that the Social module collects.

  2. Configure a persistence.xml file with the appropriate elements, including those for database connections.

  3. 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 the following POM dependency in pom.xml:

<dependency>
    <groupId>twigkit</groupId>
    <artifactId>twigkit.social.db.derby.memory</artifactId>
    <version>${project.parent.version}</version>
</dependency>
Note
If pom.xml doesn’t contain the dependency for Derby, then the app project hasn’t 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.derby</artifactId>
    <version>${project.parent.version}</version>
</dependency>

Create and configure persistence.xml

You’ll need to 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 doesn’t 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.1.1. Declare the POM dependency

To persist social metadata in a disk-based Derby database, change the POM dependency to:

MySQL:

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

Oracle:

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

SQL Server:

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

1.1.2. Create and configure persistence.xml

You’ll need to 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.

1.1.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" />

Persist Social module data in Fusion

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

  1. Configure Fusion.

  2. (Only for apps created with prior versions of App Studio or Appkit) Upgrade to the latest version of Appkit.

  3. 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 the following simple steps:

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

    <!-- Hibernate c3p0 connection pooling -->
    <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 version 4.1.7.Final (legacy) or 5.2.2.Final.

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

    <!-- 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.