Image

Integrazione Di Pentaho Server Con PostgreSQL (parte 3)

Integrando Pentaho Server con PostgreSQL.

1. Prerequisiti necessari:

2. Fare il login con l'utente "root"

sudo su 

3. Efettuare il login con l'utente "tomcat":

su tomcat

4. Configurare l'accesso del Pentaho Server 9 al database "hibernate"

Sarà necessario editare il file "applicationContext-spring-security-hibernate.properties" sostituendo tutto il contenuto con le linee in basso:

vim /srv/pentaho-server/pentaho-solutions/system/applicationContext-spring-security-hibernate.properties
jdbc.driver=org.postgresql.Driver
jdbc.url=jdbc:postgresql://localhost:5432/hibernate
jdbc.username=hibuser
jdbc.password=password
hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

5. Configurare l'accesso del Pentaho Server 9 al database "jackrabbit"

Sarà ancora necessario editare il file "repository.xml" effettuando tutte le modifiche manualmente e com estrema attenzione usando come riferimento l'esempio in basso:

NB: le modifiche consistono nel commentare e decommentare i blocchi usando le tag del formato XML

"<!--" par aprire il commento e "-->" per chiudere il commento.

È possibile identificare i blocchi interessati dalle linee "--[Decommentare]--" e "--[Commentare]--".

vim /srv/pentaho-server/pentaho-solutions/system/jackrabbit/repository.xml
##Filesystem
--[Decommentare]--
  <FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
    <param name="driver" value="org.postgresql.Driver"/>
    <param name="url" value="jdbc:postgresql://localhost:5432/jackrabbit"/>
    <param name="user" value="jcr_user"/>
    <param name="password" value="password"/>
    <param name="schema" value="postgresql"/>
    <param name="schemaObjectPrefix" value="fs_repos_"/>
  </FileSystem>
--[Commentare]--
  <!--  <FileSystem class="org.apache.jackrabbit.core.fs.local.LocalFileSystem">
          <param name="path" value="${rep.home}/repository"/>
        </FileSystem>
  -->
##Datastore
--[Decommentare]--
  <DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore">
    <param name="url" value="jdbc:postgresql://localhost:5432/jackrabbit"/>
    <param name="driver" value="org.postgresql.Driver"/>
    <param name="user" value="jcr_user"/>
    <param name="password" value="password"/>
    <param name="databaseType" value="postgresql"/>
    <param name="minRecordLength" value="1024"/>
    <param name="maxConnections" value="3"/>
    <param name="copyWhenReading" value="true"/>
    <param name="tablePrefix" value=""/>
    <param name="schemaObjectPrefix" value="ds_repos_"/>
  </DataStore>
--[Commentare]--
  <!--
    <DataStore class="org.apache.jackrabbit.core.data.FileDataStore"/>
  -->
##FileSystem WorkSpace:
--[Decommentare]--
  <FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
    <param name="driver" value="org.postgresql.Driver"/>
    <param name="url" value="jdbc:postgresql://localhost:5432/jackrabbit"/>
    <param name="user" value="jcr_user"/>
    <param name="password" value="password"/>
    <param name="schema" value="postgresql"/>
    <param name="schemaObjectPrefix" value="fs_ws_"/>
  </FileSystem>
--[Commentare]--
  <!--
    <FileSystem class="org.apache.jackrabbit.core.fs.local.LocalFileSystem">
    <param name="path" value="${wsp.home}"/>
  </FileSystem>
  -->
## PersistenceManager:
--[Decommentare]--
    <PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.PostgreSQLPersistenceManager">
      <param name="url" value="jdbc:postgresql://localhost:5432/jackrabbit"/>
      <param name="driver" value="org.postgresql.Driver"/>
      <param name="user" value="jcr_user"/>
      <param name="password" value="password"/>
      <param name="schema" value="postgresql"/>
      <param name="schemaObjectPrefix" value="${wsp.name}_pm_ws_"/>
    </PersistenceManager>
--[Commentare]--
    <!--
      <PersistenceManager class="org.apache.jackrabbit.core.persistence.pool.H2PersistenceManager">
        <param name="url" value="jdbc:h2:${wsp.home}/db"/>
        <param name="schemaObjectPrefix" value="${wsp.name}_"/>
      </PersistenceManager>
    -->
##FileSystem Version:
--[Decommentare]--
    <FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
      <param name="driver" value="org.postgresql.Driver"/>
      <param name="url" value="jdbc:postgresql://localhost:5432/jackrabbit"/>
      <param name="user" value="jcr_user"/>
      <param name="password" value="password"/>
      <param name="schema" value="postgresql"/>
      <param name="schemaObjectPrefix" value="fs_ver_"/>
    </FileSystem>
--[Commentare]--
    <!--
      <FileSystem class="org.apache.jackrabbit.core.fs.local.LocalFileSystem">
        <param name="path" value="${rep.home}/version" />
      </FileSystem>
    -->
##PersistenceManager Version:
--[Decommentare]--
      <PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.PostgreSQLPersistenceManager">
        <param name="url" value="jdbc:postgresql://localhost:5432/jackrabbit"/>
        <param name="driver" value="org.postgresql.Driver"/>
        <param name="user" value="jcr_user"/>
        <param name="password" value="password"/>
        <param name="schema" value="postgresql"/>
        <param name="schemaObjectPrefix" value="pm_ver_"/>
      </PersistenceManager>
--[Commentare]--
    <!--
      <PersistenceManager class="org.apache.jackrabbit.core.persistence.pool.H2PersistenceManager">
        <param name="url" value="jdbc:h2:${rep.home}/version/db"/>
        <param name="schemaObjectPrefix" value="version_"/>
      </PersistenceManager>
    -->

6. Configurazione per indicare a "hibernate" di usare il database, che è stato creato nel PostgreSQL.

Modifichiamo il file "hibernate-settings.xml":

vim /srv/pentaho-server/pentaho-solutions/system/hibernate/hibernate-settings.xml

e sostituire la linea:

<config-file>system/hibernate/hsql.hibernate.cfg.xml</config-file>

con la seguente linea:

<config-file>system/hibernate/postgresql.hibernate.cfg.xml</config-file>

7. Configurazione di "hibernate" con i parametri del database nel PostgreSQL.

Verificare se il file "postgresql.hibernate.cfg.xml" sia uguale all'esempio sotto:

vim /srv/pentaho-server/pentaho-solutions/system/hibernate/postgresql.hibernate.cfg.xml
<!--  Postgres 9 Configuration -->
<property name="connection.driver_class">org.postgresql.Driver</property>
<property name="connection.url">jdbc:postgresql://localhost:5432/hibernate</property>
<property name="dialect">org.hibernate.dialect.PostgreSQLDialect</property>
<property name="connection.username">hibuser</property>
<property name="connection.password">password</property>
<property name="connection.pool_size">10</property>
<property name="show_sql">false</property>
<property name="hibernate.jdbc.use_streams_for_binary">true</property>

8. Configurazione del driver JDBC per la connessione dei databases creati nel PostgreSQL.

Modificare il file "jdbc.properties":

vim /srv/pentaho-server/pentaho-solutions/system/simple-jndi/jdbc.properties

Cancellando tutte le linee "decommentate" per poterle sostituire con le linee in basso:

Hibernate/type=javax.sql.DataSource
Hibernate/driver=org.postgresql.Driver
Hibernate/url=jdbc:postgresql://localhost:5432/hibernate
Hibernate/user=hibuser
Hibernate/password=password
Quartz/type=javax.sql.DataSource
Quartz/driver=org.postgresql.Driver
Quartz/url=jdbc:postgresql://localhost:5432/quartz
Quartz/user=pentaho_user
Quartz/password=password

9. Configurare i parametri dei Datasources editando il file "context.xml":

vim /srv/pentaho-server/tomcat/webapps/pentaho/META-INF/context.xml

lasciandolo simile al contenuto in basso:

<Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource"
    factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxTotal="2000" maxIdle="5"
    maxWaitMillis="10000" username="hibuser" password="password"
    driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/hibernate"
    validationQuery="select 1" />

<Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"
    factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxTotal="2000" maxIdle="5"
    maxWaitMillis="10000" username="pentaho_user" password="password"
    driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/quartz"
    validationQuery="select 1"/>

10. Configuriamo il driver JDBC per accedere ai datasources del database PostgreSQL.

Modificare il file "applicationContext-spring-security-jdbc.properties" lasciandolo simile al blocco in basso:

vim /srv/pentaho-server/pentaho-solutions/system/applicationContext-spring-security-jdbc.properties
# For full set of properties see http://commons.apache.org/proper/commons-dbcp/configuration.html
# Please notice: this is only for Hypersonic. Update this for any other database you are using
# The fully qualified Java class name of the JDBC driver to be used
datasource.driver.classname=org.postgresql.Driver

# The connection URL to be passed to our JDBC driver to establish a connection
datasource.url=jdbc:postgresql://localhost:5432/userdb

# The connection username to be passed to our JDBC driver to establish a connection
datasource.username=hibuser

# The connection password to be passed to our JDBC driver to establish a connection
datasource.password=password

# The SQL query that will be used to validate connections from this pool before returning them to the caller.
# This query must be an SELECT statement that returns at least one row.
# HSQLDB: SELECT 1 FROM INFORMATION_SCHEMA.SYSTEM_USERS
# MySQL, H2, MS-SQL, POSTGRESQL, SQLite: SELECT 1
# ORACLE: SELECT 1 FROM DUAL
datasource.validation.query=SELECT 1

# the maximum number of milliseconds that the pool will wait (when there are no available connections)
# for a connection to be returned before throwing an exception, or <= 0 to wait indefinitely. Default value is -1
datasource.pool.max.wait=-1

# The maximum number of active connections that can be allocated from this pool at the same time, or negative for no limit. Default value is 8
datasource.pool.max.active=100

# The maximum number of connections that can remain idle in the pool, without extra ones being destroyed, or negative for no limit. Default value is 8
datasource.max.idle=50

# The minimum number of active connections that can remain idle in the pool, without extra ones being created when the evictor runs, or 0 to create none. Default value is 0
datasource.min.idle=0

11. Disattivare l'avvio del database "Samples" e tutti gli altri contenenti gli "esempi".

Modificare il file "pentaho-spring-beans.xml":

vim /srv/pentaho-server/pentaho-solutions/system/pentaho-spring-beans.xml

commentando la linea in basso:

<import resource="GettingStartedDB-spring.xml" /> <!--Remove this line to unhook the Getting Started DB -->

lasciandola come l'esempio in basso:

<!--<import resource="GettingStartedDB-spring.xml" /> Remove this line to unhook the Getting Started DB -->

12. Disattivare del database HSQLDB di default del Pentaho Server 9.
Sarà necessario modificare il file "web.xml" facendo tutte le alterazioni manualmente e com estrema attenzione e usando come riferimento l'esempio in basso:

N.B.: le modifiche consistono nel commentare e decommentare alcuni blocchi usando le tags del formato XML:

"<!--" per aprire il commento e "-->" per chiudere il commento.
vim /srv/pentaho-server/tomcat/webapps/pentaho/WEB-INF/web.xml
<!-- [BEGIN HSQLDB DATABASES]
<context-param>
    <param-name>hsqldb-databases</param-name>
    <param-value>[email protected]/../data/hsqldb/sampledata,[email protected]/../data/hsqldb/hibernate,[email protected]/../data/hsqldb/quartz</param-value>
</context-param>
[END HSQLDB DATABASES] -->

<!--
<filter>
<filter-name>SystemStatusFilter</filter-name>
    <filter-class>org.pentaho.platform.web.http.filters.SystemStatusFilter</filter-class>
    <init-param>
        <param-name>initFailurePage</param-name>
        <param-value>InitFailure</param-value>
        <description>This page is displayed if the PentahoSystem fails to properly initialize.</description>
    </init-param>
</filter>
-->

<!--
<filter-mapping>
    <filter-name>SystemStatusFilter</filter-name>
    <url-pattern>/*</url-pattern>
</filter-mapping>
-->

<!-- [BEGIN HSQLDB STARTER]
<listener>
    <listener-class>org.pentaho.platform.web.http.context.HsqldbStartupListener</listener-class>
</listener>
[END HSQLDB STARTER] -->

13. Aggiornare i permessi della directory "/srv/pentaho-server":

chown -R tomcat.tomcat /srv/pentaho-server

14. Riavviare pentaho-server:

/srv/pentaho-server/start-pentaho.sh

15. Monitorare i log del Pentaho Server 9 per verificare che non ci siano problemi all'avvio:

tail -f /srv/pentaho-server/tomcat/logs/pentaho.log

16. Per interrompere il monitoramento dei log del Pentaho Server 9:

ctrl+c

17. Verificare se tutto funziona correttamente puntando il browser all'indirizzo:

http://<IP_server>:8080/pentaho/Login