I've been tasked with reverse engineering Hibernate mappings from a large, legacy, sql server database. The database makes extensive use of check constraints (several per table) and indexes, and while I would argue that these are DBA concerns, we're required to preserve them in the Hibernate mappings.

Using Hibernate Tools for Eclipse, I can successfully reverse engineer our tables into .hbm.xml files; however, none of the check constraints or indexes are present. I know that it's possible to manually add both check constraints and indexes directly to the mapping, e.g.:


We have hundreds of tables and thousands of indexes/check constraints, so adding these by hand to the reverse engineered mappings isn't really feasible.

Another Hibernate user mentioned using templates to do this, but it's my understanding that templates are only useful for modifying the format of the generated artifacts or substituting values for known properties. Hibernate seems completely unaware of the check constraints and indexes when it looks at the table, so I don't understand how templates would help.

Is it possible to reverse engineer indexes/check constraints with Hibernate Tools?

Try to use
JdbcReader reader = JdbcReaderFactory.newJDBCReader(...)
MetaDataDialect dialect = JdbcReader.getMetaDataDialect();
and use
dialect.getIndexInfo(String catalog, String schema, String table)
to obtain indexes for each table. Use the information in custom templates.

what purpose would they play in the mapping file of a database that already exist ? those aren't used by hibernate for naything but creating the database schema - which is not relevant in this case.

Thanks, Dmitry. To clarify (because I'm a bit of a Hibernate noob), are you suggesting that I implement a custom reversing strategy and use this there? Currently, I'm doing it all via Eclipse -- I create a cfg.xml file, a reveng.xml file, a console configuration, and a hibernate code gen configuration -- I don't do anything programmatically.

Yes, you're 100% correct. These will only be useful when going from a Hibernate mapping back to a database schema. In our case, we (unfortunately) won't really be using Hibernate for anything other than obtaining a model representation of our database. We could just as easily use a custom XML file, but I suggested we use Hibernate in case they have an opportunity to use it for DAO generation, etc. in the future.

Segphault,
I meant you need to create your own Util class which you'll use in your custom template.(look at http://docs.jboss.org/tools/2.1.0.Beta1 ... bmtemplate section 4.5.2)
The class will provide the indeces information for each table.
And the pseudo code I provided should be in Util class.

yes, but why do you need this information or DAO generation etc. ?

We don't need the indices and constraints for DAO generation, only for schema generation.

We have a very old (~10 years) app supports both SQL Server and Oracle. Currently, the team maintains two sets of creation scripts for everything -- one for SQL Server, one for Oracle. The goal here is simply to eliminate the scripts and maintain a single common model (Hibernate mappings) that can be used to create our tables for both SQL Server and Oracle. We need the schemas that we generate from the Hibernate mappings to match the original tables exactly, so it is important that we include the constraints and indices in the model. Does that make sense?

so you want the reverse engineering to generated hbm.xml you can use to generate the exact same database structure with again ?

Okey - I get the usecase; but we don't read all that data (some of it is not even available via JDBC metadata).

And even if read in there is no guarantee that it will be possible to effectively generate the exact same db structure again.

Thus it will depend from usecase to usecase and you would need to extend the templates and do something like what dgeraskov was mentioning.

Yep -- it's an odd use case. I shouldn't have used the word exact -- we are hoping for "very close" :)