Hibernate Community • View topic - Extra columns on a mapping table, should I argue to drop em?

View unanswered posts | View active topics

Board index » Projects » Search, Validator, Shards

All times are UTC - 5 hours [ DST ]

Extra columns on a mapping table, should I argue to drop em?

Page 1 of 1

[ 7 posts ]

Previous topic | Next topic

Author

Message

gabrielh

Post subject: Extra columns on a mapping table, should I argue to drop em?

Posted: Tue Mar 22, 2005 12:51 pm

Newbie

Joined: Wed Feb 23, 2005 11:57 am
Posts: 15

I have a many-to-many association between two tables, but the association table has some extra columns (apart from the foreign keys). I have read: http://www.hibernate.org/118.html#A11 and understand how to implement it.

The tables were put there by the DBA for "versioning" (chg_ts=change timestamp), audit (chg_userid) and soft deletes (deleted_bln). This is basically what the tables look like:

Code:

(Providers)
provider_id
name
chg_ts
chg_userid
deleted_bln

(Hospitals)
hospital_id
hospital_name
chg_ts
chg_userid
deleted_bln

(ProviderHospitals)
provider_id
hospital_id
chg_ts
chg_userid
deleted_bln

These columns are NON NULL.

In order to do this I would have to create a "ProviderHospital" class that contains a hospital. The Provider class would then have a set of ProviderHospitals (instead of Hospitals). *yuk*

This kind of whores up the object model, should I try to argue that we don't need these extra columns on the mapping table.

Does anyone know more about databases so I could argue, or have run into this problem and decided on some trade-off.

What if those fields could be null and a trigger could populate them?

Thanks for any input,
-gabe

Top

greg_barton

Post subject:

Posted: Tue Mar 22, 2005 1:33 pm

Pro

Joined: Tue Aug 26, 2003 1:24 pm
Posts: 213
Location: Richardson, TX

If these fields are common throughout the data model use an Interceptor to set them automatically. Check out these examples:

Infrastructure for auditable records
http://hibernate.org/48.html

A more complex history setup:
http://hibernate.org/195.html

Your case is simpler than both of these, methinks, but the concept is the same. When operating on the POJOS, before you flush or save them you modify the extra columns.

About the soft deletes, try this: If you want a session that never returns soft deleted entries use an interceptor that catches "deleted" entries before they're instantiated. I've never done this, but it should be possible. You might be able to change the delete behavior in the same way.

Top

gabrielh

Post subject: Just the mapping tables.

Posted: Fri Mar 25, 2005 2:16 pm

Newbie

Joined: Wed Feb 23, 2005 11:57 am
Posts: 15

Thanks for the reply! I've been looking into the audit interceptor stuff and its cool, but it wont solve my problem with the mapping table, providerHospitals.

There are at least 3 options, I can see:

(1) Interceptor (or event listeners): Intercept an insert on a table, and fill in the extra columns, possibly do an update instead of delete (to undelete). I'm not sure this is possible, since the interceptor works higher up in the object model. With the event model, the only way to veto a change is to throw an exception which causes the transaction to rollback (i.e. you can't veto and continue).

(2) Have a trigger insert the versioning field or unique identifier in the database.

(3) Make the object model suck and use a composite element with a many-to-one mapping. So essentially we have this extra "relationship" object, Provider-> ProviderHospital -> Hospital. And we have to maintain it.

#1 is probably the middle-ground, but i'm not sure its possible to capture a delete and do an update instead, is it possible to intercept an insert and add columns to it?
#2 is the cleanest option for everyone outside the database.
#3 is the cleanest option for the database, sucks for everyone else.

If we want soft delete's on provider hospital relationship we would need to:
- keep state about a deleted provider hospital relationship, in the object model, i.e. #3 (trying to avoid this)
- look up when we save the hospital memberships, which ones have changed, i.e #1. I hope were aren't soft deleting alot of entries, I can't imagine thousands of selects and inserts can be good.

Furthermore, if we do #3, we must disable the :deleted filter and get deleted objects, then we have to check everywhere before inserting to see if we should instead setDeleted(false) on an existing object.

So, should I try to fight the DBA, and drop these extra columns on the mapping (providerHospital) table?

Thanks for any and all input, corrections, etc.
-gabe

Top

greg_barton

Post subject:

Posted: Fri Mar 25, 2005 4:18 pm

Pro

Joined: Tue Aug 26, 2003 1:24 pm
Posts: 213
Location: Richardson, TX

I'm actually doing this right now with the "hybrid" approach. An Interceptor is handling the ubiquitous fields (creation and edit dates, creation and edit user) while lazy delete is handled by tight control over queries. All of the following methods are in a persistence utility class that controls access to Hibernate.

Here's the Interceptor:

Code:

class WritableInterceptor implements Interceptor {

        private UserVO user;
        private Date date = new Date();
        
        LogisticsWritableInterceptor() {}
        
        public void setUser(UserVO user) {
            this.user = user;
        }
        
        public int[] findDirty(
            Object entity, 
            Serializable id, 
            Object[] currentState, 
            Object[] previousState, 
            String[] propertyNames, 
            Type[] types) {
            return null;
        }
        
        public Object instantiate(
            Class clazz, 
            Serializable id){
            return null;
        }
        
        public Boolean isUnsaved(Object entity) {
            return null;
        }

        public void onDelete(
            Object entity, 
            Serializable id, 
            Object[] state, 
            String[] propertyNames, 
            Type[] types
        ) {
            //Do nothing
        }
        
        public boolean onFlushDirty(
            Object entity, 
            Serializable id, 
            Object[] currentState, 
            Object[] previousState, 
            String[] propertyNames, 
            Type[] types
        ) {
            if(this.user == null) {
                throw new IllegalStateException("User not set!");
            }
            
            boolean modified = false;
            
            for(int i = 0; i < propertyNames.length; i++) {
                if(propertyNames[i].equals(PersistenceConstants.EDIT_DATE)) {
                    currentState[i] = this.date;
                    modified = true;
                } else if(propertyNames[i].equals(PersistenceConstants.EDIT_USER)) {
                    currentState[i] = this.user;
                    modified = true;
                }
            }
            
            return modified;
        }
        
        public boolean onLoad(
            Object entity, 
            Serializable id, 
            Object[] state, 
            String[] propertyNames, 
            Type[] types
        ) {
            return false;
        }
        
        public boolean onSave(
            Object entity, 
            Serializable id, 
            Object[] state, 
            String[] propertyNames, 
            Type[] types
        ) {
            if(this.user == null) {
                throw new IllegalStateException("User not set!");
            }

            boolean modified = false;
            
            for(int i = 0; i < propertyNames.length; i++) {
                if(propertyNames[i].equals(PersistenceConstants.CREATE_DATE)) {
                    state[i] = this.date;
                    modified = true;
                } else if(propertyNames[i].equals(PersistenceConstants.CREATE_USER)) {
                    state[i] = this.user;
                    modified = true;
                } else if(propertyNames[i].equals(PersistenceConstants.STATUS)) {
                    state[i] = new Byte(PersistenceConstants.ACTIVE_STATUS);
                    modified = true;
                }
                
            }
            
            return modified;
        }
        
        public void postFlush(Iterator entities) {
            //Do nothing
        }
        
        public void preFlush(Iterator entities) {
            //Do nothing
        }
    }

Creation of the Session must be controlled, and I get the editing User from the session and put it in the Interceptor before releasing it to the wild:

Code:

    protected Session getWritableSession(int userId) {

        String method = "getSession";
        try {
            SessionFactory factory = new Configuration().buildSessionFactory();
            
            WritableInterceptor interceptor = new WritableInterceptor();
            
            Session session = 
                factory.openSession(
                    connection, 
                    interceptor
                );
            
            //Get user.  This is possible because the interceptor does not
            //need the user set for reading data, only writing
            UserVO user = (UserVO)session.get(
                    UserVO.class, 
                    new Integer(userId)
                 );
            
            interceptor.setUser(user);
            
            return session;
            
        } catch(HibernateException he) {
            //PANIC!!!
        }
    }

You can extend this to other ubiquitous fields.

For lazy delete I tightly control how queries are done. My current client only wants to do Criteria queries, so I can do this. :) Basically I've got one method for submitting a Criteria:

Code:

    protected List get(Criteria criteria) throws HibernateException {
        
        return criteria.add( 
            Expression.eq(
                "status", 
                new Integer(PersistenceConstants.ACTIVE_STATUS)
            )
        ).list();
    }

This may not be doable for everyone as it rather restricts the usage of Hibernate, but it fits for my client. (All that matters, right? :P ) Also, this is with Hibernate 2.1. 3 might have a better solution available, but I don't know it well enough to be sure.

Top

gabrielh

Post subject: More details?

Posted: Fri Mar 25, 2005 7:27 pm

Newbie

Joined: Wed Feb 23, 2005 11:57 am
Posts: 15

Thanks again for replying. We actually do use an interceptor to tag the objects with the userid, and timestamp, already. We also use a deleted filter too. My question applies only to the mapping table. I will try to explain in greater detail below.

Say I have my two objects:
Provider class

Code:

long id
String name
String chg_userid
Date chg_ts
boolean deleted_bln
Set<Hospital> hospitals

Hospital class

Code:

long hospital_id
String hospital_name
String chg_userid
Date chg_ts
boolean deleted_bln

And the mapping files (I've simplified them for this example):
Provider.hbm.xml

Code:

<hibernate-mapping>
    <class name="Provider" table="Providers">
        <id name="id" type="long">
            <generator class="native"/>
        </id>
        
        <version column="chg_ts" name="changeTimestamp" type="timestamp" />
        <property name="changeUserId" type="string"/>
      
        <property name="name" type="string" />
        <property name="deleted" column="deleted_bln" type="yes_no"/>
         <set name="hospitals" table="ProviderHospitals">
           <key column="provider_id"/>
           <many-to-many column="hospital_id" class="Hospital"/>
       </set>        
    </class>
</hibernate-mapping>

Hospital.hbm.xml

Code:

<hibernate-mapping>
   
    <class name="Hospital" table="Hospitals">
        <id name="id" type="long" column="hospital_id" >
           <generator class="assigned"/>
        </id>
        <version column="chg_ts" name="changeTimestamp" type="timestamp" />

        <property name="name" type="string" />
        <property name="deleted" column="deleted_bln" type="yes_no"/>
    </class>
   
</hibernate-mapping>

This doesn't work because you have extra columns in the providerHospitals mapping table:
ProviderHospitals table

Code:

provider_id
hospital_id
chg_ts (*)
chg_userid (*)
deleted_bln (*)

If I take out the (*) columns, this example works.

Otherwise I would need a ProviderHospital "relationship" object in between.
ProviderHospital class

Code:

Hospital hospital
String chg_userid;
Date chg_ts;
boolean deleted_bln

and a composite-element:

Code:

<set name="providerHospitals">
    <key column=""/>
    <composite-element class="ProviderHospital">
        <property name="chg_userid" />
        <property name="chg_ts" type="timestamp" />
        <property name="deleted" column="deleted_bln" type="yes_no"/>
        <many-to-one name="hospital" class="Hospital"/>
    </composite-element>
</set>

my provider class would then change to have:
Set<ProviderHospital> hospitals?

I don't want this ProviderHospital object, it seems really ugly to have this. So I am considering arguing that we don't do soft deletes on this mapping table, or track userid, and just do a versioning (timestamp) trigger.
The provider table will have the chg_userid set anyway, and soft deleting a hospital membership seems unnecessary.

An I missing anything?
Thanks again,
-gabe

Top

gabrielh

Post subject:

Posted: Mon Mar 28, 2005 2:15 pm

Newbie

Joined: Wed Feb 23, 2005 11:57 am
Posts: 15

bump for love.

Top

greg_barton

Post subject:

Posted: Mon Mar 28, 2005 2:38 pm

Pro

Joined: Tue Aug 26, 2003 1:24 pm
Posts: 213
Location: Richardson, TX

Don't think you're missing anything. Just do a cascade delete when physically deleting the related records.

I can't help you on that "love bumping" business, though. :P

Top

Page 1 of 1

[ 7 posts ]

Board index » Projects » Search, Validator, Shards

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum