Friday, July 06, 2007

A first stab at a persistent data model for the sample application - Mapping a 1:n relationship

In my previous posting I introduced a scenario to which I want to apply Vanatec OpenAccess (VOA) as an example for an O/R Mapper. I want to see how it can help me reducing the effort to invest into a data access layer. And I want to see how persistent objects can be handled in business logic and fronend. Developing a solution for a concrete scenario will make it easier for me to excersice the different facettes of OpenAccess, I think.

Mapping a 1:n relationship

Despite want I wrote at the end of that posting, though, I will not yet tackle the architecture of the Learnbox application. Rather I first want to explore what VOA offers to make 1:n relationships between domain model entities persistent. Take for example the relationship between a set of cards and its index cards:


Thinking object oriented I would use some kind of collection to keep all the references to IndexCard objects in a CardSet object, e.g.

    1     public class CardSet

    2     {

    3         public List<IndexCard> indexcards;

    4     }


    6     public class IndexCard

    7     {

    8     }

However the VOA documentation tells me, I cannot use generic collection types directly. Instead I need to revert to their respective interfaces, e.g. IList<T> for List<T>. The reason for this: VOA will fill an IList<T> field with its own implementation of the interface, i.e. some kind of prefab persistent collection. I think, that´s fine for me for the moment at least. So my first shot at the above small data model is this:


Now I can write some code to check how easy retrieving object along such relationships is. Here´s my initial VS solution:


And here´s the database model that VOA generated from my two persistent classes:


As you can see, VOA generated a special join table (card_set_index_card) to map the relationship between CardSpace and IndexCard objects which I expressed using an IList:

  • There are two foreign key columns, one for each table of the relationship.
  • And there is a sequence number field which keeps track of the position of an object in a relationship. Is it the first, second or last element in the IList<T>?
  • The parent table foreign key (card_set_id) and the sequence number together form the primary key for the join table. Thus the same object (represented by the child table foreign key (index_card_id)) can exist several times within the same IList<T>.

Interesting - but why did VOA generate such a table at all? Because a join table is the most general means to model relationships other than 1:1. Had I designed the database schema by hand, I would have put a foreign key into index_card referring to a card_set row directly. But that way each index card would have been bound to exactly one card set. That´s not what the object model says, though. The same IndexCard object can possibly belong to several CardSet parent objects. So the mapping VOA chose is not only the most general one, but also the most true. Well then, let´s stick with it at least for now.

In my code of course this all does not concern me. Adding and retrieving objects is as easy as can be:

    1 using System;

    2 using System.Collections.Generic;

    3 using System.Text;


    5 namespace test_datamodel

    6 {

    7     class Program

    8     {

    9         static void Main(string[] args)

   10         {

   11             // filling the database

   12             using (OpenAccess.IObjectScope os = OpenAccess.Database.Get("LearnboxDBConnection").GetObjectScope())

   13             {

   14                 os.Transaction.Begin();


   16                 lb.datamodel.CardSet cs;

   17                 lb.datamodel.IndexCard ic;



   20                 cs = new lb.datamodel.CardSet();

   21                 cs.Name = "German";


   23                 ic = new lb.datamodel.IndexCard();

   24                 ic.Question = "Haus, das";

   25                 ic.Answer = "house";

   26                 cs.IndexCards.Add(ic);


   38                 os.Add(cs);



   41                 cs = new lb.datamodel.CardSet();

   42                 cs.Name = "French";


   62                 os.Transaction.Commit();

   63             }



   66             // retrieving data

   67             using (OpenAccess.IObjectScope os = OpenAccess.Database.Get("LearnboxDBConnection").GetObjectScope())

   68             {

   69                 OpenAccess.IQuery q = os.GetOqlQuery("select * from CardSetExtent");

   70                 OpenAccess.IQueryResult qr = q.Execute();

   71                 foreach (lb.datamodel.CardSet cs in qr)

   72                 {

   73                     Console.WriteLine("{0}", cs.Name);

   74                     foreach (lb.datamodel.IndexCard ic in cs.IndexCards)

   75                     {

   76                         Console.WriteLine("  {0} : {1}", ic.Question, ic.Answer);

   77                     }

   78                 }

   79             }

   80         }

   81     }

   82 }

Here´s the output:

 And here´s the proof, VOA uses its own collection data type for the IList<T> field in CardSet: a TrackedGenericList.


Removing objects related to each other

Well, this all looks ok to me so far. But let´s exercise the relationship a little bit more: How to delete an IndexCard? How to reorder the IndexCards of a CardSet?

Deleting an IndexCard should be as easy as removing it from the list of its CardSet:

    1 using (OpenAccess.IObjectScope os = OpenAccess.Database.Get("LearnboxDBConnection").GetObjectScope())

    2 {

    3     os.Transaction.Begin();


    5     OpenAccess.IQuery q = os.GetOqlQuery("select * from CardSetExtent cs where'German'");

    6     OpenAccess.IQueryResult qr = q.Execute();


    8     lb.datamodel.CardSet cs = (lb.datamodel.CardSet)qr[0];

    9     cs.IndexCards.RemoveAt(0);


   11     os.Transaction.Commit();

   12 }

And indeed the IndexCard for "Haus, das" is no longer listed with with the "German" card set:


But is it also removed from the database? Since as long as I don´t retain a reference to the IndexCard elsewhere in my code, the object will be deleted by the garbage collection; it would be nice if the persistent memory behaved in the same way. So let´s have a look:


Upps, the query

OpenAccess.IQuery q = os.GetOqlQuery("select * from IndexCardExtent");

does still return the IndexCard object I removed from the collection. That means, removal from relationships does not mean data is removed completely, too. That must be done explicitly. No kind of garbage collection for persistent memory jumps in to help ;-)

Here´s the right way to do it: Get a reference to the object to delete, remove it from any relationships, then remove it from the database.

    1 lb.datamodel.IndexCard ic;

    2 ic = cs.IndexCards[0];

    3 cs.IndexCards.RemoveAt(0);

    4 os.Remove(ic);

That´s for dependent objects. But what about independent objects like the CardSet. It´s the parent of IndexCard objects which should not exist independent of a CardSet. So when I delete a CardSet all its IndexCards should be deleted too, right?

    1 OpenAccess.IQuery q = os.GetOqlQuery("select * from CardSetExtent cs where'German'");

    2 OpenAccess.IQueryResult qr = q.Execute();


    4 lb.datamodel.CardSet cs = (lb.datamodel.CardSet)qr[0];

    5 os.Remove(cs);

This code however just deletes the CardSet parent and not its children as a query on IndexCard objects shows. But since I did not plan the database schema myself there are surely no referential integrity constraints defined. So how can I set up a "cascading delete" constraint? Do I have to get my hands dirty and touch the database schema? Or can I define such a constraint in the persistent object model?

As it turns out, VOA lets me defined the IndexCard children of a CardSet as being dependent on it, i.e. they will be deleted when their parent is deleted. I just need to adorn the IList<T> field with the [OpenAccess.Depend()] attribute:

    1 [OpenAccess.Persistent]

    2 public class CardSet

    3 {


   13     [OpenAccess.Depend()]

   14     private IList<IndexCard> indexcards;

Great! But of course I need to be careful: If the same dependent object is referenced by more than one parent it will be deleted nevertheless. Hm...

Changing the order of objects in a list

The order of index cards in a compartment of a Learnbox is important. It mirrors my learning progress (or some priority I want to assign certain index cards). That´s why I introduced an Item object in my data model to keep track of the position of index cards in compartments. But as it seems I don´t need such a crutch. As the database schema shows, VOA keeps track of the position of list elements itself with the seq column in the join table.

But I want to quickly verify this. Here´s some code which changes the order of some index cards:

    1 OpenAccess.IQuery q = os.GetOqlQuery("select * from CardSetExtent cs where'German'");

    2 OpenAccess.IQueryResult qr = q.Execute();


    4 lb.datamodel.CardSet cs = (lb.datamodel.CardSet)qr[0];

    5 lb.datamodel.IndexCard ic;

    6 ic = cs.IndexCards[0];

    7 cs.IndexCards.RemoveAt(0);

    8 cs.IndexCards.Add(ic);

The first index card is removed and re-appended at the end of the list. And indeed the order is preserved by VOA as you can see:


"Haus, das" has been the first index card and now is the last one. I think I can update my data model now and make it a little bit simpler:


No need for an Item class anymore. IndexCard objects can be ordered in different ways in Compartments and CardSets without it. Also I removed the aggregation of IndexCards by CardSet. An IndexCard should not automatically be removed if its CardSet is deleted. If it´s used in one or more Learnboxes, why shouldn´t they retain it? Which brings me to business logic in general. I guess I need to think about which business logik rules I need and where to implement them. But more of that in my next posting.