Title: Configure the databases
Notice:    Licensed to the Apache Software Foundation (ASF) under one
           or more contributor license agreements.  See the NOTICE file
           distributed with this work for additional information
           regarding copyright ownership.  The ASF licenses this file
           to you under the Apache License, Version 2.0 (the
           "License"); you may not use this file except in compliance
           with the License.  You may obtain a copy of the License at
           .
             http://www.apache.org/licenses/LICENSE-2.0
           .
           Unless required by applicable law or agreed to in writing,
           software distributed under the License is distributed on an
           "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
           KIND, either express or implied.  See the License for the
           specific language governing permissions and limitations
           under the License.

## Default setup
In the default setup, Apache Rave uses a file-based [H2 database][1]. 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][2].

### 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][3].  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][4].

Make sure the JDBC driver ends up in the classpath. Either add it to a common lib directory of the application container or add its dependency to the pom of the several modules.

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 database of your choice.
The default portal properties can be found in `rave-portal/src/main/resources/portal.properties`, the default Shindig properties in `rave-shindig/src/main/resource/rave.shindig.properties`.


## Sample values 

### MySQL

#### JDBC driver

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

#### Properties
Apache Rave portal:

    portal.dataSource.url=jdbc:mysql://localhost:3306/rave
    portal.dataSource.driver=com.mysql.jdbc.Driver
    portal.dataSource.username=rave
    portal.dataSource.password=rave
    
    portal.jpaDialect=org.springframework.orm.jpa.DefaultJpaDialect
    portal.jpaVendorAdapter.databasePlatform=org.apache.openjpa.jdbc.sql.MySQLDictionary
    portal.jpaVendorAdapter.database=MYSQL

OpenSocial data (Apache Shindig):

    rave-shindig.dataSource.url=jdbc:mysql://localhost:3306/rave
    rave-shindig.dataSource.driver=com.mysql.jdbc.Driver
    rave-shindig.dataSource.username=rave
    rave-shindig.dataSource.password=rave
    
    rave-shindig.jpaDialect=org.springframework.orm.jpa.DefaultJpaDialect
    rave-shindig.jpaVendorAdapter.databasePlatform=org.apache.openjpa.jdbc.sql.MySQLDictionary
    rave-shindig.jpaVendorAdapter.database=MYSQL

#### 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:

    portal.dataSource.url=jdbc:postgresql://localhost:5432/rave
    portal.dataSource.driver=org.postgresql.Driver
    portal.dataSource.username=rave
    portal.dataSource.password=rave
    
    portal.jpaDialect=org.springframework.orm.jpa.DefaultJpaDialect
    portal.jpaVendorAdapter.databasePlatform=org.apache.openjpa.jdbc.sql.PostgresDictionary
    portal.jpaVendorAdapter.database=POSTGRESQL

OpenSocial data (Apache Shindig):

    rave-shindig.dataSource.url=jdbc:postgresql://localhost:5432/rave
    rave-shindig.dataSource.driver=org.postgresql.Driver
    rave-shindig.dataSource.username=rave
    rave-shindig.dataSource.password=rave
    
    rave-shindig.jpaDialect=org.springframework.orm.jpa.DefaultJpaDialect
    rave-shindig.jpaVendorAdapter.databasePlatform=org.apache.openjpa.jdbc.sql.PostgresDictionary
    rave-shindig.jpaVendorAdapter.database=POSTGRESQL

#### 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][5] in the lib directory of your Apache Tomcat instance. This driver is not available in a public Maven repository.

#### Properties
Apache Rave portal:

    portal.dataSource.url=jdbc:oracle:thin:@localhost:1521:rave
    portal.dataSource.driver=oracle.jdbc.OracleDriver
    portal.dataSource.username=rave
    portal.dataSource.password=rave

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

OpenSocial data (Apache Shindig):

    rave-shindig.dataSource.url=jdbc:oracle:thin:@localhost:1521:rave
    rave-shindig.dataSource.driver=oracle.jdbc.OracleDriver
    rave-shindig.dataSource.username=rave
    rave-shindig.dataSource.password=rave

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

#### 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][6] 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;


  [1]: http://www.h2database.com/
  [2]: http://www.h2database.com/html/features.html#auto_mixed_mode
  [3]: http://localhost:11111
  [4]: rave-extensions.html
  [5]: http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html
  [6]: http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html