2

If I have a legacy database with no referential-integrity or keys and it uses stored procedures for all external access is there any point in using nHibernate to persist entities (object-graphs)?

Plus, the SP's not only contain CRUD operations but business logic as well...

I'm starting to think sticking with a custom ado.net DAL would be easier :(

Cheers

Ollie

AwkwardCoder
  • 24,893
  • 27
  • 82
  • 152
  • No primary keys either? – Mauricio Scheffer Aug 26 '09 at 16:22
  • No keys, no indexes, no identity columns, no foreign key constraints - pretty much one of the worst legacy DB's i've seen for a while and I don't want to change anything in the DB for fear of breaking something I don't know or have control over... :) – AwkwardCoder Aug 26 '09 at 16:26

2 Answers2

1

You most likely CAN. But you probably shouldn't :-)

Hibernate does not care about referential integrity per se; while it obviously needs to have some sort of link between associated tables, it does not matter whether actual FK constraint exists. For example, if Product is mapped as many-to-one to Vendor, PRODUCTS table should have some sort of VENDOR_ID in it but it doesn't have to be a FK.

Depending on your SP signatures, you may or may not be able to use them as custom CRUD in your mappings; if SPs indeed have business logic in them that is applied during all CRUD operations, that may be your first potential problem.

Finally, if your SPs are indeed used for ALL CRUD operations (including all possible queries) it's probably just not worth it to try and introduce Hibernate to the mix - you'll gain pretty much nothing and you'll have a yet another layer to deal with.

ChssPly76
  • 99,456
  • 24
  • 206
  • 195
0

okay, an example of the problem is this:

A SP uses a sql statement similar to the following to select the next Id to be inserted into the 'Id' column of a table (this column is just an int column but NOT an identity column),

statement: 'select @cus_id = max(id) + 1 from customers',

so once the next id is calculated it's inserted into table A with other data, then a row is inserted into table B where there is ref to table A (no foreign key constraint) on another column from table A, then finally a row is inserted into table C using the same ref to table A.

When I mapped this into NH using the fluent NH the map generated a correct 'insert' sql statement for the first table but when the second table was mapped as a 'Reference' an 'update' sql statement was generated, I was expecting to see an 'insert' statement...

Now the fact there is no identity columns, no keys and no referential-integrity means to me that I can't guarantee relationship are one-to-one, one-to-many etc...

If this is true, how can NH (fluent) configured either...

Cheers

Ollie

AwkwardCoder
  • 24,893
  • 27
  • 82
  • 152
  • You seem to be confusing db constraints / id generation with Hibernate mappings. You _DO_ need to have ids mapped in Hibernate; but they don't have to rely on identity column (you can use "assigned" or "increment" generators, for example) and Hibernate doesn't care about PK / FK constraints. That said, selecting max(id) as you've shown below is highly problematic unless you can guarantee order of execution (you're bound to get id collisions from concurrent transactions sooner or later if you don't) – ChssPly76 Aug 26 '09 at 21:26
  • I'm aware of the issue of using 'select max(id)'. As for the confusion on NH, I know you can specify 'assigned' or 'increment' generators for identity columns. What I'm confused about is the fact that in general one is advised (by the docs) to use 'References' instead of 'HasOne' and the fact when I do, why does it generate 'update' statements instead of 'insert' statements for table that are related to the initial insert – AwkwardCoder Aug 26 '09 at 21:58
  • I'm a Hibernate user, so the terminology might be a little disconnected here. "HasOne" means embedded / component? That would be mapped to the same table and so is not relevant here. Possible reasons why Hibernate might issue an update instead of insert are (a) your association mapping is wrong; or (b) ids on B / C entities are mapped incorrectly causing Hibernate to assume the entity already exists (this often happens with "assigned" generators). – ChssPly76 Aug 26 '09 at 22:18
  • okay, I'm away from the code at the moment, but it's sounding as though it is a problem with the 'assigned' generators. Thanks for the info, when I'm at the code tomorrow I'll try and provide an example – AwkwardCoder Aug 26 '09 at 22:23
  • Also, I do understand the what 'component' means in NH – AwkwardCoder Aug 26 '09 at 22:25