Announcement Announcement Module
Collapse
No announcement yet.
Dynamic-runtime Hibernate schema change Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Dynamic-runtime Hibernate schema change

    Hopefully this post makes sense and someone can assist me. I have also posted this in the Hibernate User's forum as it is specifically a Hibernate question, however I'm attempting to use Spring's support functionality.


    My organization partitions its data into multiple schema's. In my example we would have a single table, defined as follows:

    Code:
    <?xml version="1.0"?>
    <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
    <hibernate-mapping package="my.package.domain.model">
        <class name="Person" table="PERSON" lazy="false" >
            <id name="id" column="ID" type="java.lang.String" >
                <generator class="assigned" />
            </id>
    
            <property name="givenName" column="GIVEN_NAME" type="java.lang.String" not-null="true" />
    
            <property name="surname" column="SURNAME" type="java.lang.String" not-null="true" />
            
            <property name="birthDate" column="BIRTH_DATE" type="java.util.Date" not-null="true" />
            
        </class>
    </hibernate-mapping>

    This table would actually exist in 5 schemas. Depending on some property of the object, for this example we'll use birth dates, the object will be persisted to a specific schema.

    ie:
    if the person's brithday is before 1950, it goes in "SCHEMA_A"
    if the person's birthday is after 1950 but before 1970, it goes in "SCHEMA_B",
    etc.

    Now, since we're using Spring & Hibernate, we want to have the following spring bean definitions:

    Code:
    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
    <beans>
        <bean id="DataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
            <property name="jndiName" value="jdbc/MyDataSource" />
        </bean>
    
        <bean id="MySessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
            <property name="mappingResources">
                <list>
                    <value>Person.hbm.xml</value>
                    <value>PersonSchemaMap.hbm.xml</value>
                </list>
            </property>
            <property name="hibernateProperties">
                <props>
                    <prop key="hibernate.dialect">org.hibernate.dialect.DB2Dialect</prop>
                    <prop key="hibernate.default_schema">DEFAULT</prop>
                    <prop key="hibernate.show_sql">false</prop>
                    <prop key="hibernate.format_sql">false</prop>
                    <prop key="hibernate.query.substitutions">true 'Y', false 'N', yes 'Y', no 'N'</prop>
                </props>
            </property>
            <property name="dataSource">
                <ref bean="MyDataSource" />
            </property>
        </bean>
    
        <bean id="PersonDao" class="my.package.dao.hibernate.HibernatePersonDao" >
            <!-- Extends Spring's HibernateDaoSupport class -->        
            <property name="sessionFactory" ref="MySessionFactory" />
        </bean>
    
    </beans>
    The Crux of the problem:

    All of the Schemas exist in the same Database, so the same SessionFactory should suffice. Except that when defined, the SessionFactory's schema is set and cannot be changed.

    Some gotchas: The DAO's all extend the HibernateDaoSupport class of Spring, which allows us to properly manage transactions and isolation levels, in a declarative manner. Therefore we can't go arbitrarily creating new SessionFactories (via programmatic interface), or we'll break the Spring support for this.

    We can't use multiple SessionFactory instances, as this would require we define multiple DAO bean instances, which in turn would require that anything using it know which bean-id to look for (Kinda defeats the purpose, no?)

    What I'm really looking for is some way to...

    intercept the session after the call to a "getHibernateTemplate()" method (eg: save) is made,
    change the setting of the schema on it,
    allow the original requested functionality to perform,
    return the schema value to the previous setting,
    return to the DAO that made the call to "getHibernateTemplate()" method.


    But all of that without breaking the transaction/isolation support and keeping it platform independent. I know that Hibernate has a project in the works for supporting something like this (Hibernate Shards) but I can't use a beta codebase for anything I'm doing.

    Does this make sense? Is it possible with the current Spring/Hibernate frameworks, or am I out of luck til Hibernate Shards is complete?

    Thanks for any help...

    -B
    Last edited by BStopp; Dec 6th, 2007, 07:27 AM.

  • #2
    To be honest, I believe that person who invented this partitioning tables between schemas has to be immediately fired. BTW, which DB do you use?

    But anyway, have you considered possibility to use AbstractRoutingDataSource from Spring? It seems to me that it may help. to some extent.

    Regards,
    Oleksandr

    Originally posted by BStopp View Post
    Hopefully this post makes sense and someone can assist me. I have also posted this in the Hibernate User's forum as it is specifically a Hibernate question, however I'm attempting to use Spring's support functionality.


    My organization partitions its data into multiple schema's. In my example we would have a single table, defined as follows:

    Code:
    <?xml version="1.0"?>
    <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
    <hibernate-mapping package="my.package.domain.model">
        <class name="Person" table="PERSON" lazy="false" >
            <id name="id" column="ID" type="java.lang.String" >
                <generator class="assigned" />
            </id>
    
            <property name="givenName" column="GIVEN_NAME" type="java.lang.String" not-null="true" />
    
            <property name="surname" column="SURNAME" type="java.lang.String" not-null="true" />
            
            <property name="birthDate" column="BIRTH_DATE" type="java.util.Date" not-null="true" />
            
        </class>
    </hibernate-mapping>

    This table would actually exist in 5 schemas. Depending on some property of the object, for this example we'll use birth dates, the object will be persisted to a specific schema.

    ie:
    if the person's brithday is before 1950, it goes in "SCHEMA_A"
    if the person's birthday is after 1950 but before 1970, it goes in "SCHEMA_B",
    etc.

    Now, since we're using Spring & Hibernate, we want to have the following spring bean definitions:

    Code:
    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
    <beans>
        <bean id="DataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
            <property name="jndiName" value="jdbc/MyDataSource" />
        </bean>
    
        <bean id="MySessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
            <property name="mappingResources">
                <list>
                    <value>Person.hbm.xml</value>
                    <value>PersonSchemaMap.hbm.xml</value>
                </list>
            </property>
            <property name="hibernateProperties">
                <props>
                    <prop key="hibernate.dialect">org.hibernate.dialect.DB2Dialect</prop>
                    <prop key="hibernate.default_schema">DEFAULT</prop>
                    <prop key="hibernate.show_sql">false</prop>
                    <prop key="hibernate.format_sql">false</prop>
                    <prop key="hibernate.query.substitutions">true 'Y', false 'N', yes 'Y', no 'N'</prop>
                </props>
            </property>
            <property name="dataSource">
                <ref bean="MyDataSource" />
            </property>
        </bean>
    
        <bean id="PersonDao" class="my.package.dao.hibernate.HibernatePersonDao" >
            <!-- Extends Spring's HibernateDaoSupport class -->        
            <property name="sessionFactory" ref="MySessionFactory" />
        </bean>
    
    </beans>
    The Crux of the problem:

    All of the Schemas exist in the same Database, so the same SessionFactory should suffice. Except that when defined, the SessionFactory's schema is set and cannot be changed.

    Some gotchas: The DAO's all extend the HibernateDaoSupport class of Spring, which allows us to properly manage transactions and isolation levels, in a declarative manner. Therefore we can't go arbitrarily creating new SessionFactories (via programmatic interface), or we'll break the Spring support for this.

    We can't use multiple SessionFactory instances, as this would require we define multiple DAO bean instances, which in turn would require that anything using it know which bean-id to look for (Kinda defeats the purpose, no?)

    What I'm really looking for is some way to...

    intercept the session after the call to a "getHibernateTemplate()" method (eg: save) is made,
    change the setting of the schema on it,
    allow the original requested functionality to perform,
    return the schema value to the previous setting,
    return to the DAO that made the call to "getHibernateTemplate()" method.


    But all of that without breaking the transaction/isolation support and keeping it platform independent. I know that Hibernate has a project in the works for supporting something like this (Hibernate Shards) but I can't use a beta codebase for anything I'm doing.

    Does this make sense? Is it possible with the current Spring/Hibernate frameworks, or am I out of luck til Hibernate Shards is complete?

    Thanks for any help...

    -B

    Comment


    • #3
      Unfortunately...

      Originally posted by al0 View Post
      To be honest, I believe that person who invented this partitioning tables between schemas has to be immediately fired. BTW, which DB do you use?

      Regards,
      Oleksandr
      We're using DB2.

      There is actually, (imo) valid reason for partitioning of tables: Customer A's data can't coexist with Customer B's data. All customers have rights to Application-X which accesses the data. Being able to support a dynamic change of the schema at run time allows us the luxury of only having to write code once.

      I looked into the AbstractRoutingDataSource, and unfortunately found some problems with the design that prevent its use...

      First, it assumes you change connections to access the data. Connections themselves don't define a change in schema, they just define change in database instance. Don't get me wrong, I know you can define a default schema for a given user, but I'm not sure if you can with a connection URL. Even if you could....

      This would require that we define a connection pool for each schema option. At current, this wouldn't be unwieldy, but it could get significantly out of hand later on (If we had 100 customers).

      Hopefully this doesn't come off as a flame response. I'm trying to provide information on why this doesn't work, in order let everyone understand my situation and the kind of solution I'm looking for.

      Thanks for the info in any case.

      -B

      Comment


      • #4
        Hello,

        really, you recent description is quite different from original one.
        Originally, you have provided example with birthdate as partitioning key, now you say that data in each schema belongs to a separate customer (which may mean that that not each table has partitioning key stored in this table), so it is (or at least need not to be) real partitioning.

        Can data from different customer coexist in the same schema is a big question, in case of Oracle I would prefer fain-grained access control over such separation, but I do not know if DB2 has equivalent mechanism.

        Really, RoutingDataSource was developed as response to needs very similar to yours (if not identical), if you would make a search in this forum, you would find old (I guess about year old) posts related to this problem.

        And I would ensure you that 100 schemas would pose much greater threat to maintainability then 100 connection pools as soon as you consider (inevitable) schemas evolution (structures and reference data).

        And I dare to disagree that connection represents database instance. Typically (if not always) connection is established to some DB instance with some credentials which in turn define the schema(s) for this connection.

        And for most connection pools you have to specify in the configuration not only DB URL, but as well user and password. Yes, method getConnection(user,password) exists in the javax.sql.DataSource interface, but AFAIK Spring does not use it (almost).

        If you do not want to use AbstractRoutingDatasource you may consider Spring UserCredentialsDataSourceAdapter. It may be used as far as you can ensure that credentials are thread-bound.

        Another possiblity, if you use c3p0 as connection pool is to use ConnectionCustomizer that would execute "set schema REQUIRED_SCHEMA" on connection checkout (but in this case you need connect to the DB with very powerful credentials).

        Regards,
        Oleksandr


        Originally posted by BStopp View Post
        We're using DB2.

        There is actually, (imo) valid reason for partitioning of tables: Customer A's data can't coexist with Customer B's data. All customers have rights to Application-X which accesses the data. Being able to support a dynamic change of the schema at run time allows us the luxury of only having to write code once.

        I looked into the AbstractRoutingDataSource, and unfortunately found some problems with the design that prevent its use...

        First, it assumes you change connections to access the data. Connections themselves don't define a change in schema, they just define change in database instance. Don't get me wrong, I know you can define a default schema for a given user, but I'm not sure if you can with a connection URL. Even if you could....

        This would require that we define a connection pool for each schema option. At current, this wouldn't be unwieldy, but it could get significantly out of hand later on (If we had 100 customers).

        Hopefully this doesn't come off as a flame response. I'm trying to provide information on why this doesn't work, in order let everyone understand my situation and the kind of solution I'm looking for.

        Thanks for the info in any case.

        -B

        Comment


        • #5
          Hello,

          one other possibility is to take a look on Hibernate Shards, while it iwas not designed for exactly your purpose, it is quite possible that it would be in some use for you.

          Regards,
          Oleksandr

          Comment

          Working...
          X