You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@rave.apache.org by ja...@apache.org on 2012/02/22 16:52:53 UTC
svn commit: r1292356 -
/incubator/rave/site/trunk/content/rave/documentation/configure-database.mdtext
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