DISCLAIMER: These notes are written by a SHRINE developer to provide assistance to users of database systems that are not currently officially supported within the SHRINE test infrastructure.

Oracle

I was able to make this work with AWS RDS Oracle SE One 11.2.0.4.v4 . 

Schema

Find oracle.ddl for the adapter and qep audit schemas, and the DSA schema:

https://open.med.harvard.edu/stash/projects/SHRINE/repos/shrine/browse/adapter/adapter-service/src/main/sql/oracle.ddl?at=tags/1.20.1&raw

https://open.med.harvard.edu/stash/projects/SHRINE/repos/shrine/browse/qep/service/src/main/sql/oracle.ddl?at=tags/1.20.1&raw

https://open.med.harvard.edu/stash/projects/SHRINE/repos/shrine/browse/apps/steward-app/src/main/sql/oracle.ddl?at=tags/1.20.1&raw

 

Add the oracle DB driver .jar from http://www.oracle.com/technetwork/apps-tech/jdbc-112010-090769.html to /opt/shrine/tomcat/lib/ (We can't ship this due to Oracle's licensing incompatibility with open source support.)

Tomcat db connection pool

Replace tomcat's db connection pool config for the qep and adapter audit databases from mysql to oracle in /opt/shrine/tomcat/conf/Catalina/localhost/shrine.xml as follows:

<Resource name="jdbc/adapterAuditDB" auth="Container" type="javax.sql.DataSource"
 maxActive="100" maxIdle="30" maxWait="10000"
 username="CHANGEME" password="CHANGEME" driverClassName="oracle.jdbc.OracleDriver"
 url="jdbc:oracle:thin:@CHANGEDNSNAME:1521:ORCL"
 testOnBorrow="true" validationQuery="SELECT 1 FROM DUAL"/>
<Resource name="jdbc/qepAuditDB" auth="Container" type="javax.sql.DataSource"
 maxActive="100" maxIdle="30" maxWait="10000"
 username="CHANGEME" password="CHANGEME" driverClassName="oracle.jdbc.OracleDriver"
 url="jdbc:oracle:thin:@CHANGEDNSNAME:1521:ORCL"
 testOnBorrow="true" validationQuery="SELECT 1 FROM DUAL"/>

Replace tomcat's db connection pool config for the DSA from mysql to oracle in /opt/shrine/tomcat/conf/Catalina/localhost/steward.xml as follows:

<Resource name="jdbc/stewardDB" auth="Container" type="javax.sql.DataSource"
 maxActive="100" maxIdle="30" maxWait="10000"
 username="CHANGEME" password="CHANGEME" driverClassName="oracle.jdbc.OracleDriver"
 url="jdbc:oracle:thin:@CHANGEDNSNAME:1521:ORCL"
 testOnBorrow="true" validationQuery="SELECT 1 FROM DUAL"/>

Slick driver 

Change the slick profile in /opt/shrine/tomcat/lib/shrine.conf by setting the properties

shrine.queryEntryPoint.audit.database.slickProfileClassName = "freeslick.OracleProfile$" 
shrine.adapter.audit.database.slickProfileClassName = "freeslick.OracleProfile$" 

Change the slick profile in /opt/shrine/tomcat/lib/steward.conf by setting the property

shrine.steward.database.slickProfileClassName = "freeslick.OracleProfile$"

mssqlserver

I was able to make this work with AWS RDS Microsoft SQL Server Express 11.00.5058.0.v1

Schema

Find mssql.ddl for the adapter and qep audit schemas, and the DSA schema:

https://open.med.harvard.edu/stash/projects/SHRINE/repos/shrine/browse/adapter/adapter-service/src/main/sql/mssql.ddl?at=tags/1.20.1&raw

https://open.med.harvard.edu/stash/projects/SHRINE/repos/shrine/browse/qep/service/src/main/sql/mssql.ddl?at=tags/1.20.1&raw

https://open.med.harvard.edu/stash/projects/SHRINE/repos/shrine/browse/apps/steward-app/src/main/sql/sqlserver.ddl?at=tags/1.20.1&raw

 

Add the jtds-1.2.7.jar from http://sourceforge.net/projects/jtds/files/jtds/1.2.7/ to /opt/shrine/tomcat/lib/ 

Tomcat db connection pool

Replace tomcat's db connection pool config for the qep and adapter audit databases from mysql to mssqlserver in /opt/shrine/tomcat/conf/Catalina/localhost/shrine.xml as follows:

<Resource name="jdbc/adapterAuditDB" auth="Container" type="javax.sql.DataSource"
 maxActive="100" maxIdle="30" maxWait="10000"
 username="CHANGEME" password="CHANGEME" driverClassName="net.sourceforge.jtds.jdbc.Driver"
 url="jdbc:jtds:sqlserver://CHANGEDNSNAME:1433/adapterAuditDB"
 testOnBorrow="true" validationQuery="SELECT 1"/>
 <Resource name="jdbc/qepAuditDB" auth="Container" type="javax.sql.DataSource"
 maxActive="100" maxIdle="30" maxWait="10000"
 username="CHANGEME" password="CHANGEME" driverClassName="net.sourceforge.jtds.jdbc.Driver"
 url="jdbc:jtds:sqlserver://CHANGEDNSNAME:1433/qepAuditDB"
 testOnBorrow="true" validationQuery="SELECT 1"/>

Replace tomcat's db connection pool config for the DSA from mysql to mssqlserver in /opt/shrine/tomcat/conf/Catalina/localhost/steward.xml as follows:

<Resource name="jdbc/stewardDB" auth="Container" type="javax.sql.DataSource"
 maxActive="100" maxIdle="30" maxWait="10000"
 username="CHANGEME" password="CHANGEME" driverClassName="net.sourceforge.jtds.jdbc.Driver"
 url="jdbc:jtds:sqlserver://CHANGEDNSNAME:1433/stewardDB"
 testOnBorrow="true" validationQuery="SELECT 1"/>

Slick driver 

Change the slick profile in /opt/shrine/tomcat/lib/shrine.conf by setting the properties

shrine.queryEntryPoint.audit.database.slickProfileClassName = "freeslick.MSSQLServerProfile$" 
shrine.adapter.audit.database.slickProfileClassName = "freeslick.MSSQLServerProfile$" 

Change the slick profile in /opt/shrine/tomcat/lib/steward.conf by setting the property

shrine.steward.database.slickProfileClassName = "freeslick.MSSQLServerProfile$"
  • No labels