SQLite unit testing NHibernate generated cascade relationships

Question

The following is some background info on this post. You can just skip to the question if you like:

In this excellent article (http://ayende.com/Blog/archive/2009/04/28/nhibernate-unit-testing.aspx) the author contends that "When using NHibernate we generally want to test only three things:
1) that properties are persisted,
2) that cascade works as expected 3) that queries return the correct result. -) that mapping is complete & correct (implied)

My take is that he goes on to say that SQLite can and should be the unit test tool of choice to do all of the above. It should be noted that the author seems to be one of more experienced and skilled NHib developers out there, and though he doesn't expressly say so in the article, he implies in a question later that the domain can and should be handling some of SQLite's shortcomings.

QUESTION:

How do you use SQLite to test cascade relationships, especially given that it does not check foreign key constraints. How do you test your model to make sure foreign key constraints will not be a db issue.

Here are some units tests I came up with to test cascade behavior. The model is simply a Department that can have zero to many StaffMembers, with cascade set to NONE.

    [Test]
    public void CascadeSaveIsNone_NewDepartmentWithFetchedStaff_CanSaveDepartment()
    {
        _newDept.AddStaff(_fetchedStaff);
        Assert.That(_newDept.IsTransient(), Is.True);

        _reposDept.SaveOrUpdate(_newDept);
        _reposDept.DbContext.CommitChanges();

        Assert.That(_newDept.IsTransient(), Is.False);
    }

    [Test]
    public void CascadeSaveIsNone_NewDepartmentWithFetchedStaff_CannotSaveNewStaff()
    {
        _newDept.AddStaff(_newStaff);
        Assert.That(_newDept.IsTransient(), Is.True);
        Assert.That(_newStaff.IsTransient(), Is.True);

        _reposDept.SaveOrUpdate(_newDept);
        _reposDept.DbContext.CommitChanges();

        Assert.That(_newDept.IsTransient(), Is.False);
        Assert.That(_newStaff.IsTransient(), Is.True);
    }

    [Test]
    public void CascadeDeleteIsNone_FetchedDepartmentWithFetchedStaff_Error()
    {
        _fetchedDept.AddStaff(_fetchedStaff);
        _reposDept.SaveOrUpdate(_fetchedDept);
        _reposStaff.DbContext.CommitChanges();

        _reposDept.Delete(_fetchedDept);
        var ex = Assert.Throws<GenericADOException>(() => _reposDept.DbContext.CommitChanges());

        Console.WriteLine(ex.Message);
        Assert.That(ex.Message, Text.Contains("could not delete:"));
        Console.WriteLine(ex.InnerException.Message);
        Assert.That(ex.InnerException.Message, Text.Contains("The DELETE statement conflicted with the REFERENCE constraint"));
    }

    [Test]
    public void Nullable_NewDepartmentWithNoStaff_CanSaveDepartment()
    {
        Assert.That(_newDept.Staff.Count(), Is.EqualTo(0));

        var fetched = _reposDept.SaveOrUpdate(_newDept);
        Assert.That(fetched.IsTransient(), Is.EqualTo(false));
        Assert.That(fetched.Staff.Count(), Is.EqualTo(0));
    }

The third test, ".._FetchedDepartmentWithFetchedStaff_Error" works against Sql Server, but not SQLite since the latter does not check foreign key constraints.

Here are tests for the other side of the relationship; a StaffMember can have one Department, with cascade set to NONE.

    [Test]
    public void CascadeSaveIsNone_NewStaffWithFetchedDepartment_CanSaveStaff()
    {
        _newStaff.Department = _fetchedDept;
        _reposStaff.SaveOrUpdate(_newStaff);
        _reposStaff.DbContext.CommitChanges();

        Assert.That(_newStaff.Id, Is.GreaterThan(0));
    }

    [Test]
    public void CascadeSaveIsNone_NewStaffWithNewDepartment_Error()
    {
        _newStaff.Department = _newDept;
        Assert.That(_newStaff.IsTransient(), Is.True);

        var ex = Assert.Throws<PropertyValueException>(() => _reposStaff.SaveOrUpdate(_newStaff));
        Console.WriteLine(ex.Message);
        Assert.That(ex.Message, Text.Contains("not-null property references a null or transient value"));
    }

    [Test]
    public void CascadeDeleteIsNone_FetchedStaffWithFetchedDepartment_DeletesTheStaff_DoesNotDeleteTheDepartment()
    {
        _newStaff.Department = _fetchedDept;
        _reposStaff.SaveOrUpdate(_newStaff);
        _reposStaff.DbContext.CommitChanges();

        _reposStaff.Delete(_newStaff);
        Assert.That(_reposStaff.Get(_newStaff.Id), Is.Null);
        Assert.That(_reposDept.Get(_fetchedDept.Id), Is.EqualTo(_fetchedDept));
    }

    [Test]
    public void NotNullable_NewStaffWithUnknownDepartment_Error()
    {
        var noDept = new Department("no department");
        _newStaff.Department = noDept;

        var ex = Assert.Throws<PropertyValueException>(() => _reposStaff.SaveOrUpdate(_newStaff));
        Console.WriteLine(ex.Message);
        Assert.That(ex.Message, Text.Contains("not-null property references a null or transient"));
    }

    [Test]
    public void NotNullable_NewStaffWithNullDepartment_Error()
    {
        var noDept = new Department("no department");
        _newStaff.Department = noDept;

        var ex = Assert.Throws<PropertyValueException>(() => _reposStaff.SaveOrUpdate(_newStaff));
        Console.WriteLine(ex.Message);
        Assert.That(ex.Message, Text.Contains("not-null property references a null or transient"));
    }

These tests succed against Sql Server and SQLite. Can I trust the SQLite tests? Are these worthwhile tests?

Cheers,
Berryl

Answer 1

As i understand the article it is about testing the NHibernate Mapping. In my opinion this has nothing to do with db related issues but with testing the nhibernate attributes you set in your mapping. There is no need to assert that it is not possible to create invalid data: you only have to proof that your code creates the desired result and/or checks the things you want to check. You can test cascade, cascade-delete and delete-orphan. whatever you want the way you do it in the tests working with sqlite. But the third test tries to test the constraint, which is nothing nhibernate worries about.

If you want to test your Db contraints you should indeed use your production db and not sqlite. You could do it with or without hibernate but this has nothing to do with your mapping. If you on the other hand really want a workarround for your Foreign Key tests with SQLite you could try to use this foreign_key_trigger_generator. I haven't tried but it seems to generate before-insert-triggers that assure the existance of the referenced Pk. Maybe you could write a comment wheather this tool is usefull.