Configuring Repository and External Datasources

Configuring Repository and External Datasources

When upgrading to 5.4 or beyond, configured database settings from JBoss will automatically be copied across into their relevant Jetty files.

Percussion database connections are in two parts, there is the JNDI datasource, and the Connection. 

Note that the database user configured in Rhythmyx for database access should be assigned to db_owner role in MSSQL or connect & resource permission in ORACLE. 

The JNDI datasource defines the login and connection details to the database server, while the Connection defines which Schema/Database within the instance that Percussion will connect to.

There is always one default Connection, typically called "PercussionData" or "RhythmyxData" , that the system uses for the Content Repository database.

Additional database connections can be set up for Percussion to be able to access external datasources that can then be accessed in templates.  Additional database connections are also used with Database Publishing to be able to publish to external database tables.

Please note that there can be more than one connection pointing to the same datasource.

Connection and datasource information in jetty is stored in two files: jetty/base/etc/perc-ds.xml and jetty/base/etc/perc-ds.properties.

The perc-ds.xml file

The perc-ds.xml file has an XML element for each JNDI datasource.  The datasource name is specified as the second <Arg> element and all other properties are referenced by an index id that refers to a configuration set in perc-ds.properties.

In Jetty, we are now using HikariCP for our connection pools, which has improved performance greatly from the old database connection pool used in the legacy JBoss configuration.  

For more information on Hikari Configuration settings, visit the HikariCP wiki: https://github.com/brettwooldridge/HikariCP/wiki/Configuration

Example jetty/base/etc/perc-ds.xml
<? xml version = "1.0" encoding = "utf-8" ?>
 
<!DOCTYPE Configure
   PUBLIC "-//Jetty//Configure//EN" "http://www.eclipse.org/jetty/configure_9_3.dtd">
< Configure class = "org.eclipse.jetty.server.Server" id = "Server" >
 
 
    < New class = "org.eclipse.jetty.plus.jndi.Resource" id = "perc.ds.1" >
       < Arg />  
       < Arg >java:jdbc/RhythmyxData</ Arg
       < Arg >    
          < New class = "com.zaxxer.hikari.HikariDataSource" >     
             < Arg >        
                < New class = "com.zaxxer.hikari.HikariConfig" >           
                   < Set name = "poolName" >Hikari < Property name = "perc.ds.1.name" /> Pool</ Set >           
                   < Set name = "connectionTimeout" >120000</ Set >           
                   < Set name = "minimumIdle" >
                      < Property default = "50" name = "perc.ds.1.connections.min" />
                   </ Set >           
                   < Set name = "maximumPoolSize" >
                      < Property default = "50" name = "perc.ds.1.connections.max" />
                   </ Set >           
                   < Set name = "idleTimeout" >
                      < Property default = "600000" name = "perc.ds.1.idle.ms" />
                   </ Set >         
                   < Set name = "transactionIsolation" >TRANSACTION_READ_COMMITTED</ Set >       
                   < Set name = "ConnectionTestQuery" >         
                      < Call class = "com.percussion.utils.container.jetty.PSJettyUtils" name = "connectionTestQuery" >
                         < Arg >
                            < Property name = "perc.ds.1.driver.name" />
                         </ Arg >
                         < Arg >
                            < Property name = "perc.ds.1.connectiontest" />
                         </ Arg >           
                      </ Call >         
                   </ Set >           
                   < Set name = "driverClassName" >
                      < Property name = "perc.ds.1.driver.class" />
                   </ Set >            
                   < Set name = "jdbcUrl" >jdbc:< Property name = "perc.ds.1.driver.name" />:< Property name = "perc.ds.1.server" />
                   </ Set >           
                   < Set name = "username" >
                      < Property name = "perc.ds.1.uid" />
                   </ Set >           
                   < Set name = "password" >            
                      < Call class = "com.percussion.utils.container.jetty.PSJettyUtils" name = "decrypt" >            
                         < Arg >
                            < Property name = "perc.ds.1.pwd" />
                         </ Arg >            
                         < Arg >
                            < Property name = "perc.ds.1.pwd.encrypted" />
                         </ Arg >            
                     </ Call >      
                   </ Set >      
                </ New
             </ Arg >
     
          </ New >
       </ Arg >
    </ New >
    < New class = "org.eclipse.jetty.plus.jndi.Resource" id = "perc.ds.2" >
       < Arg />
       < Arg >java:jdbc/RxData2</ Arg >
       < Arg >
          < New class = "com.zaxxer.hikari.HikariDataSource" >
             < Arg >  
                < New class = "com.zaxxer.hikari.HikariConfig" >      
                   < Set name = "poolName" >Hikari < Property name = "perc.ds.2.name" /> Pool</ Set >     
                   < Set name = "connectionTimeout" >120000</ Set >     
                   < Set name = "minimumIdle" >
                      < Property default = "50" name = "perc.ds.2.connections.min" />
                   </ Set >      
                   < Set name = "maximumPoolSize" >
                      < Property default = "50" name = "perc.ds.2.connections.max" />
                   </ Set >      
                   < Set name = "idleTimeout" >
                      < Property default = "600000" name = "perc.ds.2.idle.ms" />
                   </ Set >       
                   < Set name = "transactionIsolation" >TRANSACTION_READ_COMMITTED</ Set >       
                   < Set name = "ConnectionTestQuery" >       
                      < Call class = "com.percussion.utils.container.jetty.PSJettyUtils" name = "connectionTestQuery" >
                         < Arg >
                            < Property name = "perc.ds.2.driver.name" />
                         </ Arg >
                         < Arg >
                            < Property name = "perc.ds.2.connectiontest" />
                         </ Arg >
              
                      </ Call >         
                   </ Set >           
                   < Set name = "driverClassName" >
                      < Property name = "perc.ds.2.driver.class" />
                   </ Set >          
                   < Set name = "jdbcUrl" >jdbc:< Property name = "perc.ds.2.driver.name" />:< Property name = "perc.ds.2.server" />
                   </ Set >         
                   < Set name = "username" >
                      < Property name = "perc.ds.2.uid" />
                   </ Set >         
                   < Set name = "password" >          
                      < Call class = "com.percussion.utils.container.jetty.PSJettyUtils" name = "decrypt" >           
                         < Arg >
                            < Property name = "perc.ds.2.pwd" />
                         </ Arg >           
                         < Arg >
                            < Property name = "perc.ds.2.pwd.encrypted" />
                         </ Arg >         
                      </ Call >       
                   </ Set >     
                </ New >  
             </ Arg >
          </ New >
       </ Arg >
    </ New >
</ Configure >

 

The perc-ds.properties file

The values for all perc.ds.x property entries are stored in perc-ds.properties, located at {InstallDir}/jetty/base/etc.

Only one connection is labeled with the default=Y flag.  This is the content repository connection that contains the Rhythmyx database that will be started with when Rhythmyx starts.

Example perc-ds.properties
#Datasource 1 Information
perc.ds.1.name=jdbc/RhythmyxData
perc.ds.1.server=//localhost
perc.ds.1.uid=sa
perc.ds.1.pwd=342323da9342
perc.ds.1.pwd.encrypted=Y
perc.ds.1.driver.name=jtds\:sqlserver
perc.ds.1.driver.class=net.sourceforge.jtds.jdbc.Driver
 
perc.ds.1.idle.ms=900000
perc.ds.1.connections.min=5
perc.ds.1.connections.max=100
 
 
# Connection 1 Information
perc.conn.1.name=RhythmyxData
perc.conn.1.ds=jdbc/RhythmyxData
perc.conn.1.db=RX732UP
perc.conn.1.schema=dbo
perc.conn.1.default=Y
 
 
# Connection 2 Information
perc.ds.2.name=jdbc/RxData2
perc.ds.2.server=//server2
perc.ds.2.uid=sa
perc.ds.2.pwd=7cf3be70d83a6948
perc.ds.2.pwd.encrypted=Y
perc.ds.2.driver.name=jtds\:sqlserver
perc.ds.2.driver.class=net.sourceforge.jtds.jdbc.Driver
perc.ds.2.idle.ms=900000
perc.ds.2.connections.min=5
perc.ds.2.connections.max=100
 
 
# Connection 2 Information
perc.conn.2.name=TESTING
perc.conn.2.ds=jdbc/RxData2
perc.conn.2.db=TESTER
perc.conn.2.schema=dbo

 

Changing or setting password

If the password encrypted flag is set e.g. perc.ds.1.pwd.encrypted=Y , the password is expected to be encrypted and the server will attempt to decrypt it.  If you remove this flag, then you can add the password in plain text.  When the server next starts up it will automatically encrypt the password and change the encrypted flag to Y.

JBOSS Equivalent files

Datasource information

In JBoss, the datasource information was stored in the AppServer/server/rx/deploy/rx-ds.xml file.  

Example Jboss AppServer/server/rx/deploy/rx-ds.xml
<? xml version = "1.0" encoding = "utf-8" ?>
< datasources >
    < local-tx-datasource >
       < jndi-name >jdbc/RhythmyxData</ jndi-name >
       < connection-url >jdbc:jtds:sqlserver://localhost</ connection-url >
       < driver-class >net.sourceforge.jtds.jdbc.Driver</ driver-class >
       < security-domain >rx.datasource.jdbc_RhythmyxData</ security-domain >
       < min-pool-size >5</ min-pool-size >
       < max-pool-size >100</ max-pool-size >
       < idle-timeout-minutes >15</ idle-timeout-minutes >
       < background-validation >true</ background-validation >
       < background-validation-millis >60000</ background-validation-millis >
       < validate-on-match >true</ validate-on-match >
       < metadata >
          < type-mapping >MS SQLSERVER2000</ type-mapping >
       </ metadata >
    </ local-tx-datasource >
    < local-tx-datasource >
       < jndi-name >jdbc/RxData2</ jndi-name >
       < connection-url >jdbc:jtds:sqlserver://localhost</ connection-url >
       < driver-class >net.sourceforge.jtds.jdbc.Driver</ driver-class >
       < security-domain >rx.datasource.jdbc_RxData2</ security-domain >
       < min-pool-size >5</ min-pool-size >
       < max-pool-size >100</ max-pool-size >
       < idle-timeout-minutes >15</ idle-timeout-minutes >
       < background-validation >true</ background-validation >
       < background-validation-millis >60000</ background-validation-millis >
       < validate-on-match >true</ validate-on-match >
       < metadata >
          < type-mapping >MS SQLSERVER2000</ type-mapping >
       </ metadata >
    </ local-tx-datasource >
</ datasources >

 

If <user-name> and <password> is added to this file instead of <security-domain>, on server startup the <user-name> and <password> is converted to <security-domain>, additionally the password is encrypted and placed in AppServer/server/rx/conf/login-config.xml

Example login information in login-config.xml
< application-policy name = "rx.datasource.jdbc_RhythmyxData" >
       < authentication >
          < login-module code = "com.percussion.services.security.loginmods.PSSecureIdentityLoginModule" flag = "required" >
             < module-option name = "username" >rhythmyx</ module-option >
             < module-option name = "password" >7cf3be70d83a6948</ module-option >
             < module-option name = "managedConnectionFactoryName" >jboss.jca:service=LocalTxCM,name=jdbc/RhythmyxData</ module-option >
          </ login-module >
       </ authentication >
    </ application-policy >

Leave a comment

*
*