rave-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ja...@apache.org
Subject svn commit: r1292356 - /incubator/rave/site/trunk/content/rave/documentation/configure-database.mdtext
Date Wed, 22 Feb 2012 15:52:53 GMT
Author: jasha
Date: Wed Feb 22 15:52:53 2012
New Revision: 1292356

URL: http://svn.apache.org/viewvc?rev=1292356&view=rev
Log:
RAVE-482 Hints for Oracle databases. SQL script made by Groningen University (added with their
permission)

Modified:
    incubator/rave/site/trunk/content/rave/documentation/configure-database.mdtext

Modified: incubator/rave/site/trunk/content/rave/documentation/configure-database.mdtext
URL: http://svn.apache.org/viewvc/incubator/rave/site/trunk/content/rave/documentation/configure-database.mdtext?rev=1292356&r1=1292355&r2=1292356&view=diff
==============================================================================
--- incubator/rave/site/trunk/content/rave/documentation/configure-database.mdtext (original)
+++ incubator/rave/site/trunk/content/rave/documentation/configure-database.mdtext Wed Feb
22 15:52:53 2012
@@ -131,7 +131,533 @@ Rave Shindig:
   - 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
+Rave portal:
+
+    portal.dataSource.url=jdbc:oracle:thin:@localhost:1521:raveportal
+    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
+
+Rave Shindig:
+
+    rave-shindig.dataSource.url=jdbc:oracle:thin:@localhost:1521:raveportal
+    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('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
\ No newline at end of file
+  [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



Mime
View raw message