Configure the SQL databases

Loading

Default setup

In the default setup, Apache Rave uses a file-based H2 database. Apache Rave and Apache Shindig run in the same Apache Tomcat server as separate web applications but share data, so we use H2's Automatic Mixed Mode.

NOTICE upgrading to Rave 0.21+

As of Rave 0.21 the database access configuration has been changed and moved to application container provided JNDI resources. When upgrading to 0.21+ make sure to update and merge with the default provided portal.properties and rave.shindig.properties files, which now define the new and required JNDI resource lookup names to use:

portal.properties

rave.dataSource.name=jdbc/ravePortalDB

rave.shindig.properties

rave.dataSource.name=jdbc/raveShindigDB

Filling the default database

All schemes are generated using JPA annotations. The H2 database is populated with low level SQL queries using the DataSourcePopulator which is configured as Spring bean. These queries are not guaranteed to work for a different database.

<bean id="dataSourcePopulator" class="org.apache.rave.jdbc.util.DataSourcePopulator">
    <property name="executeScriptQuery" value="SELECT * FROM widget"/>
    <property name="scriptLocations">
        <list>
            <value>classpath:initial_data.sql</value>
        </list>
    </property>
</bean>

If the query in executeScriptQuery returns no error and no results, the database will be populated with the contents of scriptLocations.

Access the default database

When the application is running, the H2 databases can be accessed through a web interface. In the default setup this is configured using a Spring bean:

<bean id="org.h2.tools.Server-WebServer" class="org.h2.tools.Server"
      factory-method="createWebServer" depends-on="dataSource"
      init-method="start" lazy-init="false" destroy-method="stop">
    <constructor-arg value="-web,-webPort,11111"/>
</bean>

Both Apache Rave and Apache Shindig are accessible from the database: http://localhost:11111. The default username is sa, password is local, and JDBC URL is jdbc:h2:file:///tmp/rave_db. In Windows the JDBC URL is jdbc:h2:file://c:/tmp/rave_db if you run Apache Rave from the c: drive.

You can change the file location for both databases by editing Apache Rave's top level pom.xml file. Change the property rave.database.location to the desired value.

Setup a different database

First start with extending Rave.

Make sure the JDBC driver ends up in the container classpath as the database access is provided by the application container through JNDI resource lookup, so add it to a common lib directory of the application container. If you use the Cargo plugin to run or package your application container setup, replace the dependencies for the com.h2database.h2:jar with your own JDBC driver jar like in the rave-portal pom.xml (note: 2 times!).

If you remove the H2 JDBC driver from the classpath, you need to override the dataContext.xml Spring configuration file. Remove the configuration for the H2 Web console. This is the bean with class org.h2.tools.Server.

Then customize the properties for the portal and Shindig to use the type of database of your choice. The default portal properties can be found in rave-portal-resources/src/main/resources/portal.properties, the default Shindig properties in rave-providers/rave-opensocial-provider/rave-opensocial-server/rave-shindig/src/main/resources/rave.shindig.properties. Note: only the database type specific properties, like the jpa.jpaVendorAdapter.database can be configured in these properties files.

The database connection parameters itself need to be configured in your project pom.xml (accessible by the Cargo plugin for its run or package goal).

The default connection properties are configured in the main Rave project pom.xml as follows:

    <!-- Rave default jdbc datasource configuration -->

    <!-- The location of Rave's H2 file DB. No trailing / -->
    <rave.database.location>/tmp/rave_db</rave.database.location>
    <rave.dataSource.driver>org.h2.Driver</rave.dataSource.driver>
    <rave.dataSource.username>sa</rave.dataSource.username>
    <rave.dataSource.password>local</rave.dataSource.password>
    <rave.dataSource.url>jdbc:h2:${rave.database.location};AUTO_SERVER=TRUE</rave.dataSource.url>

    <!-- Rave Portal default jdbc datasource configuration: using rave default configuration shared with Shindig -->
    <portal.dataSource.driver>${rave.dataSource.driver}</portal.dataSource.driver>
    <portal.dataSource.url>${rave.dataSource.url}</portal.dataSource.url>
    <portal.dataSource.username>${rave.dataSource.username}</portal.dataSource.username>
    <portal.dataSource.password>${rave.dataSource.password}</portal.dataSource.password>

  <!-- Rave Shindig default jdbc datasource configuration: using rave default configuration shared with Portal -->
    <shindig.dataSource.driver>${rave.dataSource.driver}</shindig.dataSource.driver>
    <shindig.dataSource.url>${rave.dataSource.url}</shindig.dataSource.url>
    <shindig.dataSource.username>${rave.dataSource.username}</shindig.dataSource.username>
    <shindig.dataSource.password>${rave.dataSource.password}</shindig.dataSource.password>

As you can see from above, you can provide separate configurations for Rave Portal or Rave Shindig, although in the default configuration both use the same.

Sample values

MySQL

JDBC driver

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.15</version>
</dependency>

Properties

Apache Rave portal:

jpa.jpaDialect=org.springframework.orm.jpa.DefaultJpaDialect
jpa.jpaVendorAdapter.databasePlatform=org.apache.openjpa.jdbc.sql.MySQLDictionary
jpa.jpaVendorAdapter.database=MYSQL

OpenSocial data (Apache Shindig):

jpa.jpaDialect=org.springframework.orm.jpa.DefaultJpaDialect
jpa.jpaVendorAdapter.databasePlatform=org.apache.openjpa.jdbc.sql.MySQLDictionary
jpa.jpaVendorAdapter.database=MYSQL

pom.xml properties:

    <rave.dataSource.driver>com.mysql.jdbc.Driver</rave.dataSource.driver>
    <rave.dataSource.username>rave</rave.dataSource.username>
    <rave.dataSource.password>rave</rave.dataSource.password>
    <rave.dataSource.url>jdbc:mysql://localhost:3306/rave</rave.dataSource.url>

Notes

The DataSourcePopulator uses a single statement to execute all queries in the configured SQL files. The MySQL JDBC driver does not allow this by default, unless you add allowMultiQueries=true to the driver URL.

PostgreSQL

JDBC driver

<dependency>
    <groupId>postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>9.0-801.jdbc4</version>
</dependency>

Properties

Apache Rave portal:

jpa.jpaDialect=org.springframework.orm.jpa.DefaultJpaDialect
jpa.jpaVendorAdapter.databasePlatform=org.apache.openjpa.jdbc.sql.PostgresDictionary
jpa.jpaVendorAdapter.database=POSTGRESQL

OpenSocial data (Apache Shindig):

jpa.jpaDialect=org.springframework.orm.jpa.DefaultJpaDialect
jpa.jpaVendorAdapter.databasePlatform=org.apache.openjpa.jdbc.sql.PostgresDictionary
jpa.jpaVendorAdapter.database=POSTGRESQL

pom.xml properties:

    <rave.dataSource.driver>org.postgresql.Driver</rave.dataSource.driver>
    <rave.dataSource.username>rave</rave.dataSource.username>
    <rave.dataSource.password>rave</rave.dataSource.password>
    <rave.dataSource.url>jdbc:postgresql://localhost:5432/rave</rave.dataSource.url>

Notes

  • The DataSourcePopulator cannot handle a PostgreSQL database that has not yet been initialized.
  • The syntax for setting variables in SQL queries in PostgreSQL is different from the syntax in H2 databases. Therefore the default data cannot be loaded using the initial_data.sql file.

Oracle 10g

JDBC driver

Install the Oracle JDBC driver in the common/lib directory of your Apache Tomcat instance. This driver is not available in a public Maven repository.

Properties

Apache Rave portal:

jpa.jpaDialect=org.springframework.orm.jpa.DefaultJpaDialect
jpa.jpaVendorAdapter.databasePlatform=org.apache.openjpa.jdbc.sql.OracleDictionary
jpa.jpaVendorAdapter.database=ORACLE

OpenSocial data (Apache Shindig):

jpa.jpaDialect=org.springframework.orm.jpa.DefaultJpaDialect
jpa.jpaVendorAdapter.databasePlatform=org.apache.openjpa.jdbc.sql.OracleDictionary
jpa.jpaVendorAdapter.database=ORACLE

pom.xml properties:

    <rave.dataSource.driver>oracle.jdbc.OracleDriver</rave.dataSource.driver>
    <rave.dataSource.username>rave</rave.dataSource.username>
    <rave.dataSource.password>rave</rave.dataSource.password>
    <rave.dataSource.url>jdbc:oracle:thin:@localhost:1521:rave</rave.dataSource.url>

Initial data

The syntax for setting variables in SQL queries in Oracle is different from the syntax in H2 databases. Therefore the default data cannot be loaded using the initial_data.sql file. OpenJPA can create the schema. With help from Oracle SQL Developer the minimal set of necessary data can be inserted. The script below inserts the Apache Rave portal sequences, the page layouts and granted permissions:

SET serveroutput ON
SET echo OFF
DECLARE
  page_seq               VARCHAR2(128) := 'page';
  page_layout_seq        VARCHAR2(128) := 'page_layout';
  region_seq             VARCHAR2(128) := 'region';
  region_widget_seq      VARCHAR2(128) := 'region_widget';
  user_seq               VARCHAR2(128) := 'person';
  person_association_seq VARCHAR2(128) := 'person_association';
  groups_seq             VARCHAR2(128) := 'groups';
  group_members_seq      VARCHAR2(128) := 'group_members';
  widget_seq             VARCHAR2(128) := 'widget';
  granted_authority_seq  VARCHAR2(128) := 'granted_authority';
  widget_comment_seq     VARCHAR2(128) := 'widget_comment';
  widget_rating_seq      VARCHAR2(128) := 'widget_rating';
  portal_preference_seq  VARCHAR2(128) := 'portal_preference';
  tag_seq                VARCHAR2(128) := 'tag';
  widget_tag_seq         VARCHAR2(128) := 'widget_tag';
  category_seq           VARCHAR2(128) := 'category';
  page_type_seq          VARCHAR2(128) := 'page_type';
  user_authority_id      NUMBER;
  col_id                 NUMBER;
BEGIN
  dbms_output.put_line('Apache Rave Initialisation script starting');
  BEGIN
    dbms_output.put_line('Dropping table RAVE_PORTAL_SEQUENCES');
    EXECUTE IMMEDIATE 'DROP TABLE RAVE_PORTAL_SEQUENCES';
  EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Error ' || SQLCODE || ' - ' || SQLERRM);
  END;
  BEGIN
    dbms_output.put_line('Creating table RAVE_PORTAL_SEQUENCES');
    EXECUTE IMMEDIATE 'CREATE TABLE RAVE_PORTAL_SEQUENCES (seq_name VARCHAR(255) PRIMARY KEY NOT NULL,seq_count NUMBER(19))';
  EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Error' || SQLCODE || ' - ' || SQLERRM);
  END;
  BEGIN
    dbms_output.put_line('Inserting RAVE_PORTAL_SEQUENCES values');
    INSERT
    INTO RAVE_PORTAL_SEQUENCES
      (
        seq_name,
        seq_count
      )
      VALUES
      (
        page_seq,
        1
      );
    INSERT
    INTO RAVE_PORTAL_SEQUENCES
      (
        seq_name,
        seq_count
      )
      VALUES
      (
        page_layout_seq,
        1
      );
    INSERT
    INTO RAVE_PORTAL_SEQUENCES
      (
        seq_name,
        seq_count
      )
      VALUES
      (
        region_seq,
        1
      );
    INSERT
    INTO RAVE_PORTAL_SEQUENCES
      (
        seq_name,
        seq_count
      )
      VALUES
      (
        region_widget_seq,
        1
      );
    INSERT
    INTO RAVE_PORTAL_SEQUENCES
      (
        seq_name,
        seq_count
      )
      VALUES
      (
        'region_widget_preference',
        1
      );
    INSERT
    INTO RAVE_PORTAL_SEQUENCES
      (
        seq_name,
        seq_count
      )
      VALUES
      (
        user_seq,
        1
      );
    INSERT
    INTO RAVE_PORTAL_SEQUENCES
      (
        seq_name,
        seq_count
      )
      VALUES
      (
        person_association_seq,
        1
      );
    INSERT
    INTO RAVE_PORTAL_SEQUENCES
      (
        seq_name,
        seq_count
      )
      VALUES
      (
        groups_seq,
        1
      );
    INSERT
    INTO RAVE_PORTAL_SEQUENCES
      (
        seq_name,
        seq_count
      )
      VALUES
      (
        group_members_seq,
        1
      );
    INSERT
    INTO RAVE_PORTAL_SEQUENCES
      (
        seq_name,
        seq_count
      )
      VALUES
      (
        widget_seq,
        1
      );
    INSERT
    INTO RAVE_PORTAL_SEQUENCES
      (
        seq_name,
        seq_count
      )
      VALUES
      (
        widget_comment_seq,
        1
      );
    INSERT
    INTO RAVE_PORTAL_SEQUENCES
      (
        seq_name,
        seq_count
      )
      VALUES
      (
        widget_rating_seq,
        1
      );
    INSERT
    INTO RAVE_PORTAL_SEQUENCES
      (
        seq_name,
        seq_count
      )
      VALUES
      (
        granted_authority_seq,
        1
      );
    INSERT
    INTO RAVE_PORTAL_SEQUENCES
      (
        seq_name,
        seq_count
      )
      VALUES
      (
        portal_preference_seq,
        1
      );
    INSERT INTO RAVE_PORTAL_SEQUENCES
      (seq_name, seq_count
      ) VALUES
      (tag_seq, 1
      );
    INSERT
    INTO RAVE_PORTAL_SEQUENCES
      (
        seq_name,
        seq_count
      )
      VALUES
      (
        widget_tag_seq,
        1
      );
    INSERT
    INTO RAVE_PORTAL_SEQUENCES
      (
        seq_name,
        seq_count
      )
      VALUES
      (
        category_seq,
        1
      );
    INSERT
    INTO RAVE_PORTAL_SEQUENCES
      (
        seq_name,
        seq_count
      )
      VALUES
      (
        page_type_seq,
        1
      );
  EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line
    (
      'Error' || SQLCODE || ' - ' || SQLERRM
    )
    ;
  END;
  BEGIN
    /* USER */
    dbms_output.put_line
    (
      'Inserting granted_authority USER value'
    )
    ;
    SELECT seq_count
    INTO user_authority_id
    FROM RAVE_PORTAL_SEQUENCES
    WHERE seq_name = granted_authority_seq;
    INSERT
    INTO granted_authority
      (
        entity_id,
        authority,
        default_for_new_user
      )
      VALUES
      (
        user_authority_id,
        'ROLE_USER',
        1
      );
    UPDATE RAVE_PORTAL_SEQUENCES
    SET seq_count  = (seq_count + 1)
    WHERE seq_name = granted_authority_seq;
    dbms_output.put_line('Inserting granted_authority ADMIN value');
    /* ADMIN */
    SELECT seq_count
    INTO user_authority_id
    FROM RAVE_PORTAL_SEQUENCES
    WHERE seq_name = granted_authority_seq;
    INSERT
    INTO granted_authority
      (
        entity_id,
        authority,
        default_for_new_user
      )
      VALUES
      (
        user_authority_id,
        'ROLE_ADMIN',
        0
      );
    UPDATE RAVE_PORTAL_SEQUENCES
    SET seq_count  = (seq_count + 1)
    WHERE seq_name = granted_authority_seq;
  EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line ( 'Error' || SQLCODE || ' - ' || SQLERRM ) ;
  END;
  BEGIN
    dbms_output.put_line('Inserting page_layout values');
    SELECT seq_count
    INTO col_id
    FROM RAVE_PORTAL_SEQUENCES
    WHERE seq_name = page_layout_seq;
    INSERT
    INTO page_layout
      (
        entity_id,
        code,
        number_of_regions,
        render_sequence
      )
      VALUES
      (
        col_id,
        'columns_1',
        1,
        0
      );
    UPDATE RAVE_PORTAL_SEQUENCES
    SET seq_count  = (seq_count + 1)
    WHERE seq_name = page_layout_seq;
    SELECT seq_count
    INTO col_id
    FROM RAVE_PORTAL_SEQUENCES
    WHERE seq_name = page_layout_seq;
    INSERT
    INTO page_layout
      (
        entity_id,
        code,
        number_of_regions,
        render_sequence,
        user_selectable
      )
      VALUES
      (
        col_id,
        'columns_2',
        2,
        1,
        1
      );
    UPDATE RAVE_PORTAL_SEQUENCES
    SET seq_count  = (seq_count + 1)
    WHERE seq_name = page_layout_seq;
    SELECT seq_count
    INTO col_id
    FROM RAVE_PORTAL_SEQUENCES
    WHERE seq_name = page_layout_seq;
    INSERT
    INTO page_layout
      (
        entity_id,
        code,
        number_of_regions,
        render_sequence,
        user_selectable
      )
      VALUES
      (
        col_id,
        'columns_2wn',
        2,
        2,
        1
      );
    UPDATE RAVE_PORTAL_SEQUENCES
    SET seq_count  = (seq_count + 1)
    WHERE seq_name = page_layout_seq;
    SELECT seq_count
    INTO col_id
    FROM RAVE_PORTAL_SEQUENCES
    WHERE seq_name = page_layout_seq;
    INSERT
    INTO page_layout
      (
        entity_id,
        code,
        number_of_regions,
        render_sequence,
        user_selectable
      )
      VALUES
      (
        col_id,
        'columns_3',
        3,
        3,
        1
      );
    UPDATE RAVE_PORTAL_SEQUENCES
    SET seq_count  = (seq_count + 1)
    WHERE seq_name = page_layout_seq;
    SELECT seq_count
    INTO col_id
    FROM RAVE_PORTAL_SEQUENCES
    WHERE seq_name = page_layout_seq;
    INSERT
    INTO page_layout
      (
        entity_id,
        code,
        number_of_regions,
        render_sequence,
        user_selectable
      )
      VALUES
      (
        col_id,
        'columns_3nwn',
        3,
        4,
        1
      );
    UPDATE RAVE_PORTAL_SEQUENCES
    SET seq_count  = (seq_count + 1)
    WHERE seq_name = page_layout_seq;
    SELECT seq_count
    INTO col_id
    FROM RAVE_PORTAL_SEQUENCES
    WHERE seq_name = page_layout_seq;
    INSERT
    INTO page_layout
      (
        entity_id,
        code,
        number_of_regions,
        render_sequence,
        user_selectable
      )
      VALUES
      (
        col_id,
        'columns_3_newuser',
        3,
        5,
        1
      );
    UPDATE RAVE_PORTAL_SEQUENCES
    SET seq_count  = (seq_count + 1)
    WHERE seq_name = page_layout_seq;
    SELECT seq_count
    INTO col_id
    FROM RAVE_PORTAL_SEQUENCES
    WHERE seq_name = page_layout_seq;
    INSERT
    INTO page_layout
      (
        entity_id,
        code,
        number_of_regions,
        render_sequence,
        user_selectable
      )
      VALUES
      (
        col_id,
        'columns_4',
        4,
        6,
        1
      );
    UPDATE RAVE_PORTAL_SEQUENCES
    SET seq_count  = (seq_count + 1)
    WHERE seq_name = page_layout_seq;
    SELECT seq_count
    INTO col_id
    FROM RAVE_PORTAL_SEQUENCES
    WHERE seq_name = page_layout_seq;
    INSERT
    INTO page_layout
      (
        entity_id,
        code,
        number_of_regions,
        render_sequence,
        user_selectable
      )
      VALUES
      (
        col_id,
        'columns_3nwn_1_bottom',
        4,
        7,
        1
      );
    UPDATE RAVE_PORTAL_SEQUENCES
    SET seq_count  = (seq_count + 1)
    WHERE seq_name = page_layout_seq;
  EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line ( 'Error' || SQLCODE || ' - ' || SQLERRM ) ;
  END;
END;