Configuring UGC database access for UGC

UGC uses uses the Storage Layer to access the UGC database, where User Generated Content comments, ratings user data and item statistics are stored. To configure storage of this kind of data in the UGC database, add a Storage element to the Storage Layer configuration file.

Procedure

  1. In the configuration location of this service, open cd_storage_conf.xml for editing.
  2. In the root Storage element, add the following attribute-value pair:
    NamespaceServiceBean="UGCNamespaceService"
  3. In the Storages element, ensure the presence of the following <Storage> element, uncommented:

    If you have a Microsoft SQL Server database:

    <Storage Type="persistence" Id="UNIQUE_ID" dialect="MSSQL" 
    NamespaceServiceBean="UGCNamespaceService"	Class="com.tridion.storage.persistence.JPADAOFactory">
    	<Pool Type="jdbc" Size="5" MonitorInterval="60" IdleTimeout="120" CheckoutTimeout="120" />
    	<DataSource Class="com.microsoft.sqlserver.jdbc.SQLServerDataSource">
    		<Property Name="serverName" Value="${dbhost}" />
    		<Property Name="portNumber" Value="${dbport}" />
    		<Property Name="databaseName" Value="${dbname}" />
    		<Property Name="user" Value="${dbuser}" />
    		<Property Name="password" Value="${dbpassword}" />
    	</DataSource>
    </Storage>
    Alternatively, if you have an Oracle database:
    <Storage Type="persistence" Id="UNIQUE_ID" dialect="ORACLESQL" 
    NamespaceServiceBean="UGCNamespaceService"	Class="com.tridion.storage.persistence.JPADAOFactory">
    	<Pool Type="jdbc" Size="5" MonitorInterval="60" IdleTimeout="120" CheckoutTimeout="120" />
    	<DataSource class="oracle.jdbc.pool.OracleDataSource">
    		<Property Name="driverType" Value="thin" />
    		<Property Name="networkProtocol" Value="tcp" />
    		<Property Name="serverName" Value="${dbhost}" />
    		<Property Name="portNumber" Value="${dbport}" />
    		<Property Name="databaseName" Value="${dbname}" />
    		<Property Name="user" Value="${dbuser}" />
    		<Property Name="password" Value="${dbpassword}" />
    	</DataSource>
    </Storage>

    If you are connecting to Oracle RAC, use the property name serviceName instead of databaseName.

    In either element, set UNIQUE_ID to a string of your choosing that uniquely identifies this storage medium.

  4. Ensure that the properties (the items enclosed in ${ and }) mentioned in this Storage element, such as ${dbhost}, are resolved when you run your PowerShell script. You can set them by defining system environment variables, Java properties, or by passing the properties and their values when you run the script.
  5. Ensure the presence of an ItemTypes element with its defaultStorageId attribute set to the ID of the database.
  6. In the StorageBindings element inside the Storages element, add the following line:
    <Bundle src="ugc_dao_bundle.xml" />
  7. SDL strongly recommends entering any sensitive strings, such as passwords, in encrypted form. You can obtain the encrypted form of a sensitive string by doing the following:
    1. In a command shell, navigate to a location that contains the files udp-core-BUILD.jar and udp-common-util-BUILD.jar, where BUILD is the JAR file's build number. For example, you can find these files on the installation media in the folder Content Delivery\roles\api\rest\java\lib\
    2. Depending on your operating system, enter one of the following commands:
      Windows operating systems
      java -cp udp-core-BUILD.jar;udp-common-util-BUILD.jar com.tridion.crypto.Encrypt INPUT
      Unix operating systems
      java -cp udp-core-BUILD.jar:udp-common-util-BUILD.jar com.tridion.crypto.Encrypt INPUT

      where INPUT is the unencrypted string. (You may wish to redirect output to a file for easy copy-pasting of the tool's response.)

      The tool returns the following kind of response:
      Configuration value = encrypted:9FUJ9CP81Oj63VhnJxcqx//pW3fP4bekeupIexctzcs=

      where the encrypted string is encrypted:9FUJ9CP81Oj63VhnJxcqx//pW3fP4bekeupIexctzcs=

  8. Save and close cd_storage_conf.xml.