Hibernate Search, Entities, and SQL VIEWs

Question

I have a table that maintains rows of products that are for sale (tbl_products) using PostgreSQL 9.1. There are also several other tables that maintain ratings on the items, comments, etc. We're using JPA/Hibernate for ORM in a Seam application, and have the appropriate entities wired up properly. In an effort to provide better listings of these items, I've created a SQL VIEW (v_product_summary) that aggregates some of the basic product data (name, description, price, etc.) with data from the other tables (number of comments, average rating, etc.). This provides a nice concise view of the data, and I've created a corresponding JPA entity object that provides read-only access to the view data.

Everything is working fine with respect to running JPQL queries on either the Product object (tbl_products) or the ProductSummary (v_product_summary) objects. However, we'd like to provide a richer search experience using Hibernate Search and Lucene. The issue we're running into, though, is how do we query the ProductSummary objects using Hibernate Search? They're not indexed upon creation, because they're never really "created". They're obtained as read-only objects from the v_product_summary VIEW. An index entry is only created on Product when it's persisted to the database, and not for ProductSummary since it's never persisted.

Our thought is that we should be able to:

1. Persist our Product object to the database
2. Immediately query the corresponding ProductSummary object using the product's ID
3. Manually update the Hibernate Search index for the ProductSummary object

Is this possible? Is this even a good idea? I can see there will be a performance impact since we're executing a query for the ProductSummary object every time a new Product is persisted. However, products are not added to the database at a high volume, so I don't think this will be a huge issue.

We'd really like to find a better, more efficient way of accomplishing this. Can anyone provide any tips or recommendations? If we do go the route of updating the search index manually, is that even doable? Can anyone provide a resource explaining how we can add a single ProductSummary to the index?

Any help you can provide is GREATLY appreciated.

Orthopterous

I also have the samilar requirement. can you share how to handle it? Make index on a view, but when insert a new record into the table associated with the view, the index can't be updated automatically. – Warman

Answer 1

If I understand the question correctly, you're trying to do the normal thing of persisting an object and indexing it at that point, but you're dealing with 2 separate objects.

I find myself doing kludgey things in Hibernate all the time, it feels like it almost demands it of you. Yes, there'd be a performance impact, and as you say, it is probably not a big deal, so it might be worth profiling.

A part of me remembers there's a way you can refresh the object upon write, and wonders if there's a way you can wrap the Product and the ProductSummary and tweak the mapping so that you read part and write part of it (waves hands on syntax and mapping). Or create a Hibernate-facing object with readonly fields that can be split and merged into your two objects. I don't know if your design allows Hibernate-only objects, it's a common idiom in my system.

Either way could be useful if you had a lot of objects in this situation, if this is the only object you're searching in this way, your 3 steps look much clearer.

As for the syntax for adding an object manually, I think you're looking for something like this, after your fetch:

FullTextSession textSession = Search.getFullTextSession(session);
textSession.index(myProductSummary);

Was that all you wanted?

Antiscorbutic

Answer 2

Since you are using postgresql, you could insert to the view and use a rule to redirect the insert to the appropriate table.

A postgresql rule is a way to change the query just before it gets executed. I used it in an application which needed a change in schema but required the old queries to still work for a little while.

You can check out the documentation about rules on insert queries on the postgresql site

Since you'll be inserting and updating to the view, hibernate search will work as usual.

EDIT

An easier strategy. You could insert and update ProductSummary when doing so on Product and tell PostgreSQL to ignore the inserts, updates and deletes on the view.

On the database side"

create RULE dontinsert AS ON insert to v_product_summary do instead nothing
create RULE dontupdate AS ON update to v_product_summary do instead nothing
create RULE dontdelete AS ON delete to v_product_summary do instead nothing

But I guess you will need to hack a little, since the jdbc call executeUpdate will return 0, and hibernate will probably freak.

Imperfect

Answer 3

Technically I think this would be possible, but I think your entire efficiency dilemma might be better solved using something like memcached, therefore making performance less of an issue, and perhaps increasing code maintainability depending on how you currently have it implemented at statement level. By updating the search index manually, do you mean the database index? That is not recommended, and I'm not sure if it's even doable. Why not index them on creation?

Vannessavanni

No, I was referring to the Lucene index. It is automatically updated when using JPA/Hibernate to persist an object to the database. However, in this case I'm not persisting the object that maps to the VIEW, so the search index on the VIEW object is never updated. I was looking for a way to accomplish that so I can search for the VIEW objects. – Orthopterous