Can I use nHibernate with a legacy-database with no referential-integrity?

Question

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

Answer 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.

Answer 2

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

Answer 3

Okay so I have the following mapping files, I've missed a lot of the columns out as they are mapping successfully:

public sealed class CustomerMap : ClassMap<Customer>, IMap
{
    public CustomerMap()
    {
        WithTable("customers");
        Not.LazyLoad();
        Id(x => x.Id).GeneratedBy.Increment();
        Map(x => x.Username);
        ...
        ...
        ...            
        HasOne(x => x.CustomerNew).Cascade.All().WithForeignKey("Username").;
        HasOne(x => x.CustomerSurvey).Cascade.All().WithForeignKey("Username");
    }
}

public sealed class CustomerNewMap : ClassMap<CustomerNew>, IMap
{
    public CustomerNewMap()
    {
        WithTable("customers_NEW");
        Not.LazyLoad();
        Id(x => x.Username).GeneratedBy.Assigned();
    }
}

public sealed class CustomerSurveyMap : ClassMap<CustomerSurvey>, IMap
{
    public CustomerSurveyMap()
    {
        WithTable("customer_survey");
        Not.LazyLoad();
        Id(x => x.Username).GeneratedBy.Assigned();
    }
}

And the entities look like this:

public class Customer : IEquatable<Customer>
{
    public virtual int Id { get; set; }
    public virtual string Username { get; set; }

    public virtual CustomerNew CustomerNew { get; set; }
    public virtual CustomerSurvey CustomerSurvey { get; set; }
}

So when a Customer entity is saved via NH an 'INSERT' statement is generated for the Customer entity but for the CustomerSurvey & CustomerNew entities 'UPDATE' statements are generated which causes the save to fail.

What could be wrong with the mapping?

NH debug sql output taht is failing:

NHibernate: INSERT INTO customers (Username, Password, Company, FirstName, LastName, JobTitle, Dept, Userphone, Userfax, email_address, City, Country, Comments, doc_author, usage_counter, last_research_accessdate, sales_id, salesperson_guess, business_sector, service_level, disclaimer_accepted, disclaimer_accepted_date, tech_contact, TechPhone, comments_support, web_browser, web_access, web_access_guess, Platform, modification_date, revision_no, ClientStatus, ReferTo, marketing_mailshot, marketing_mailshot_id, salesnav_transfer, creation_date, approval_date, registration_status, investor_category_id, Id) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24, @p25, @p26, @p27, @p28, @p29, @p30, @p31, @p32, @p33, @p34, @p35, @p36, @p37, @p38, @p39, @p40); @p0 = 'Test-bed93f34-c', @p1 = 'rbsfm', @p2 = 'Test-bed93f34-c', @p3 = 'Test-bed93f34-c', @p4 = 'Test-bed93f34-c', @p5 = 'Test-bed93f34-c', @p6 = 'Test-bed93f34-c', @p7 = 'Test-bed93f34-c', @p8 = '', @p9 = 'Test-bed93f34-c', @p10 = 'Test-bed93f34-c', @p11 = 'Tes', @p12 = '', @p13 = '0', @p14 = '', @p15 = '', @p16 = '-1', @p17 = 'Test-bed93f34-c', @p18 = '', @p19 = '', @p20 = '1', @p21 = '27/08/2009 14:03:14', @p22 = '', @p23 = '', @p24 = '', @p25 = '', @p26 = '', @p27 = '', @p28 = '', @p29 = '', @p30 = '', @p31 = '7', @p32 = '218', @p33 = '', @p34 = '', @p35 = '', @p36 = '27/08/2009 14:03:14', @p37 = '', @p38 = 'USR', @p39 = 'rbs', @p40 = '7561'

NHibernate: UPDATE customers_NEW SET Company = @p0, Firstname = @p1, Lastname = @p2, JobTitle = @p3, Dept = @p4, Userphone = @p5, email_address = @p6, City = @p7, Country = @p8, SalesPerson = @p9, investor_category_id = @p10 WHERE Username = @p11; @p0 = 'Test-bed93f34-c', @p1 = 'Test-bed93f34-c', @p2 = 'Test-bed93f34-c', @p3 = 'Test-bed93f34-c', @p4 = 'Test-bed93f34-c', @p5 = 'Test-bed93f34-c', @p6 = 'Test-bed93f34-c', @p7 = 'Test-bed93f34-c', @p8 = 'Tes', @p9 = 'Test-bed93f34-c', @p10 = 'rbs', @p11 = 'Test-bed93f34-c'

Thanks in advance

Ollie