JpaTicketRegistry

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:p="http://www.springframework.org/schema/p"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="
         http://www.springframework.org/schema/beans
         http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
         http://www.springframework.org/schema/tx
         http://www.springframework.org/schema/tx/spring-tx-3.0.xsd" >

  <description>
    Configuration for the Jpa TicketRegistry which stores the tickets in a
database and cleans them out at specified intervals.
  </description>

  <!-- Ticket Registry -->
  <bean id="ticketRegistry" class="org.jasig.cas.ticket.registry.JpaTicketRegistry" />

  <!--
    Injects EntityManager/Factory instances into beans with
    @PersistenceUnit and @PersistenceContext
  -->
  <bean class="org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor"/>

  <!-- Use the following for versions prior to 3.4.11 -->
  <!--
  <bean id="ticketRegistry"
    class="org.jasig.cas.ticket.registry.JpaTicketRegistry">
    <constructor-arg index="0" ref="entityManagerFactory" />
  </bean>
  -->

  <bean id="entityManagerFactory"
    class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="dataSource" ref="dataSource"/>
    <property name="jpaVendorAdapter">
      <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
        <property name="generateDdl" value="true"/>
        <property name="showSql" value="true" />
      </bean>
    </property>
    <property name="jpaProperties">
      <props>
        <!-- Use MySQLDialect at your own risk.  See MySQL section below for details. -->
        <prop key="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</prop>
		<!-- For MySQL 5.x, use the following instead
		<prop key="hibernate.dialect">org.hibernate.dialect.MySQL5InnoDBDialect</prop>
		-->
		<prop key="hibernate.hbm2ddl.auto">update</prop>

      </props>
    </property>
  </bean>

  <bean id="transactionManager"
    class="org.springframework.orm.jpa.JpaTransactionManager"
    p:entityManagerFactory-ref="entityManagerFactory" />

  <tx:annotation-driven transaction-manager="transactionManager" />

  <bean id="dataSource"
    class="org.apache.commons.dbcp.BasicDataSource"
    p:driverClassName="com.mysql.jdbc.Driver"
    p:url="jdbc:mysql://localhost:3306/test?autoReconnect=true"
    p:password="notthepassword"
    p:username="mysql_user" />

  <!-- TICKET REGISTRY CLEANER -->
  <bean id="ticketRegistryCleaner"
    class="org.jasig.cas.ticket.registry.support.DefaultTicketRegistryCleaner"
    p:ticketRegistry-ref="ticketRegistry"
    p:lock-ref="cleanerLock" />

  <!--
   Use JpaLockingStrategy for 3.4.11 and later.
   This bean is only needed for HA setups where multiple nodes are attempting
   cleanup on a shared database, but it doesn't substantially impact performance
   and is easy to setup and is therefore recommended for all JpaTicketRegistry deployments.
   This component automatically creates the LOCKS table so no further configuration
   is required.
  -->
  <bean id="cleanerLock"
    class="org.jasig.cas.ticket.registry.support.JpaLockingStrategy"
    p:uniqueId="${host.name}"
    p:applicationId="cas-ticket-registry-cleaner" />

  <!--
    Use JdbcLockingStrategy on 3.4.10 and before for HA setups where multiple nodes are attempting
    cleanup on a shared database.  This bean requires additional configuration:
     1. Creation of LOCKS table (see below)
     2. Setting ticket.cleaner.database.platform property
    Note that the ticket.cleaner.database.platform property should be set in cas.properties
    or some other properties file that is loaded by a PropertyPlaceholderConfigurer bean in
    the Spring context.
    Allowed values for ticket.cleaner.database.platform:
      * SqlServer for Microsoft SQL Server
      * HSQL for HSQLDB
      * SQL92 for any platform that supports the SQL-92 FOR UPDATE clause. (e.g. PostgreSQL, Oracle)
  -->
  <!--
  <bean id="cleanerLock"
    class="org.jasig.cas.ticket.registry.support.JdbcLockingStrategy"
    p:uniqueId="${host.name}"
    p:platform="${ticket.cleaner.database.platform}"
    p:applicationId="cas-ticket-registry-cleaner"
    p:dataSource-ref="dataSource" />
  -->

  <bean id="ticketRegistryCleanerJobDetail"
    class="org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean"
    p:targetObject-ref="ticketRegistryCleaner"
    p:targetMethod="clean" />

  <bean id="periodicTicketRegistryCleanerTrigger"
    class="org.springframework.scheduling.quartz.SimpleTriggerBean"
    p:jobDetail-ref="ticketRegistryCleanerJobDetail"
    p:startDelay="20000"
    p:repeatInterval="1800000" />
</beans>

<dependencies>
  <dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-core</artifactId>
    <version>${hibernate.core.version}</version>
    <scope>runtime</scope>
  </dependency>

  <dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-entitymanager</artifactId>
    <version>${hibernate.core.version}</version>
    <scope>runtime</scope>
  </dependency>
</dependencies>

<properties>
  <!--
    The following version is for demonstration only.
    Use the version defined in the properties section of the CAS root pom.xml file.
  -->
  <hibernate.core.version>3.6.0.Final</hibernate.core.version>
</properties>

<dependencies>
  <dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-core</artifactId>
    <version>${hibernate.core.version}</version>
    <scope>runtime</scope>
  </dependency>

  <dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-entitymanager</artifactId>
    <version>${hibernate.core.version}</version>
    <scope>runtime</scope>
  </dependency>
</dependencies>

mysql> show index from SERVICETICKET where column_name='ticketGrantingTicket_ID';
+---------------+------------+--------------------+--------------+-------------------------+
| Table         | Non_unique | Key_name           | Seq_in_index | Column_name             |
+---------------+------------+--------------------+--------------+-------------------------+
| SERVICETICKET |          1 | FK7645ADE132A2C0E5 |            1 | ticketGrantingTicket_ID |
+---------------+------------+--------------------+--------------+-------------------------+
-----------+-------------+----------+--------+------+------------+---------+
 Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
-----------+-------------+----------+--------+------+------------+---------+
 A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

mysql> show index from TICKETGRANTINGTICKET where column_name='ticketGrantingTicket_ID';
+----------------------+------------+--------------------+--------------+-------------------------+
| Table                | Non_unique | Key_name           | Seq_in_index | Column_name             |
+----------------------+------------+--------------------+--------------+-------------------------+
| TICKETGRANTINGTICKET |          1 | FKB4C4CDDE32A2C0E5 |            1 | ticketGrantingTicket_ID |
+----------------------+------------+--------------------+--------------+-------------------------+
-----------+-------------+----------+--------+------+------------+---------+
 Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
-----------+-------------+----------+--------+------+------------+---------+
 A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

CREATE INDEX ST_TGT_FK_I ON SERVICETICKET (ticketGrantingTicket_ID);

CREATE INDEX ST_TGT_FK_I ON TICKETGRANTINGTICKET (ticketGrantingTicket_ID);

CREATE INDEX "ST_TGT_FK_I"
  ON SERVICETICKET ("TICKETGRANTINGTICKET_ID")
  COMPUTE STATISTICS;

/** Create index on TGT self-referential foreign-key constraint */
CREATE INDEX "TGT_TGT_FK_I"
  ON TICKETGRANTINGTICKET ("TICKETGRANTINGTICKET_ID")
  COMPUTE STATISTICS;

CREATE TABLE locks (
 application_id VARCHAR(50) NOT NULL,
 unique_id VARCHAR(50) NULL,
 expiration_date TIMESTAMP NULL
);
ALTER TABLE locks ADD CONSTRAINT pk_locks
 PRIMARY KEY (application_id);

<bean
  id="dataSource"
  class="com.mchange.v2.c3p0.ComboPooledDataSource"
  p:driverClass="${database.driverClass}"
  p:jdbcUrl="${database.url}"
  p:user="${database.user}"
  p:password="${database.password}"
  p:initialPoolSize="${database.pool.minSize}"
  p:minPoolSize="${database.pool.minSize}"
  p:maxPoolSize="${database.pool.maxSize}"
  p:maxIdleTimeExcessConnections="${database.pool.maxIdleTime}"
  p:checkoutTimeout="${database.pool.maxWait}"
  p:acquireIncrement="${database.pool.acquireIncrement}"
  p:acquireRetryAttempts="${database.pool.acquireRetryAttempts}"
  p:acquireRetryDelay="${database.pool.acquireRetryDelay}"
  p:idleConnectionTestPeriod="${database.pool.idleConnectionTestPeriod}"
  p:preferredTestQuery="${database.pool.connectionHealthQuery}"
  />

 # == Basic database connection pool configuration ==
database.dialect=org.hibernate.dialect.PostgreSQLDialect
database.driverClass=org.postgresql.Driver
database.url=jdbc:postgresql://somehost.vt.edu/cas?ssl=true
database.user=somebody
database.password=meaningless
database.pool.minSize=6
database.pool.maxSize=18

# Maximum amount of time to wait in ms for a connection to become
# available when the pool is exhausted
database.pool.maxWait=10000

# Amount of time in seconds after which idle connections
# in excess of minimum size are pruned.
database.pool.maxIdleTime=120

# Number of connections to obtain on pool exhaustion condition.
# The maximum pool size is always respected when acquiring
# new connections.
database.pool.acquireIncrement=6

# == Connection testing settings ==

# Period in s at which a health query will be issued on idle
# connections to determine connection liveliness.
database.pool.idleConnectionTestPeriod=30

# Query executed periodically to test health
database.pool.connectionHealthQuery=select 1

# == Database recovery settings ==

# Number of times to retry acquiring a _new_ connection
# when an error is encountered during acquisition.
database.pool.acquireRetryAttempts=5

# Amount of time in ms to wait between successive aquire retry attempts.
database.pool.acquireRetryDelay=2000

<dependencies>
  <dependency>
    <groupId>c3p0</groupId>
    <artifactId>c3p0</artifactId>
    <version>0.9.1.2</version>
    <scope>runtime</scope>
  </dependency>
</dependencies>

<prop key="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</prop>

<!-- OR for MySQL 5.x use the following instead -->
<prop key="hibernate.dialect">org.hibernate.dialect.MySQL5InnoDBDialect</prop>

mysql> use cas;
Database changed
mysql> show tables;
+-----------------------+
| Tables_in_cas         |
+-----------------------+
| LOCKS                 |
| RegisteredServiceImpl |
| SERVICETICKET         |
| TICKETGRANTINGTICKET  |
| rs_attributes         |
+-----------------------+
5 rows in set (0.00 sec)

mysql> show columns from SERVICETICKET;
+-------------------------+--------------+------+-----+---------+-------+
| Field                   | Type         | Null | Key | Default | Extra |
+-------------------------+--------------+------+-----+---------+-------+
| ID                      | varchar(255) | NO   | PRI | NULL    |       |
| NUMBER_OF_TIMES_USED    | int(11)      | YES  |     | NULL    |       |
| CREATION_TIME           | bigint(20)   | YES  |     | NULL    |       |
| EXPIRATION_POLICY       | longblob     | NO   |     | NULL    |       |
| LAST_TIME_USED          | bigint(20)   | YES  |     | NULL    |       |
| PREVIOUS_LAST_TIME_USED | bigint(20)   | YES  |     | NULL    |       |
| FROM_NEW_LOGIN          | bit(1)       | NO   |     | NULL    |       |
| TICKET_ALREADY_GRANTED  | bit(1)       | NO   |     | NULL    |       |
| SERVICE                 | longblob     | NO   |     | NULL    |       |
| ticketGrantingTicket_ID | varchar(255) | YES  | MUL | NULL    |       |
+-------------------------+--------------+------+-----+---------+-------+
10 rows in set (0.00 sec)

mysql> show columns from TICKETGRANTINGTICKET;
+----------------------------+--------------+------+-----+---------+-------+
| Field                      | Type         | Null | Key | Default | Extra |
+----------------------------+--------------+------+-----+---------+-------+
| ID                         | varchar(255) | NO   | PRI | NULL    |       |
| NUMBER_OF_TIMES_USED       | int(11)      | YES  |     | NULL    |       |
| CREATION_TIME              | bigint(20)   | YES  |     | NULL    |       |
| EXPIRATION_POLICY          | longblob     | YES  |     | NULL    |       |
| LAST_TIME_USED             | bigint(20)   | YES  |     | NULL    |       |
| PREVIOUS_LAST_TIME_USED    | bigint(20)   | YES  |     | NULL    |       |
| AUTHENTICATION             | longblob     | NO   |     | NULL    |       |
| EXPIRED                    | bit(1)       | NO   |     | NULL    |       |
| SERVICES_GRANTED_ACCESS_TO | blob         | YES  |     | NULL    |       |
| ticketGrantingTicket_ID    | varchar(255) | YES  | MUL | NULL    |       |
+----------------------------+--------------+------+-----+---------+-------+
10 rows in set (0.00 sec)

ALTER TABLE TICKETGRANTINGTICKET MODIFY SERVICES_GRANTED_ACCESS_TO LONGBLOB;

lower-case-table-names = 1