[odb-users] Best Practice deleting persistent objects

[Boris Kolpackov]

Hi Steven,

Steven Côté <steven.cote at gmail.com> writes:

> I have a persistent class representing a "user" and another persistent
> class representing a "user group". The user group is basically just a name
> and a list of "user" objects.
> 
> Running through the schema generator, this gives me three tables; user,
> group and group_member. The third table just contains the mapping between
> users and groups.

So you have something like this:

#pragma db object
class user
{
  ...
};

#pragma db object
class group
{
  ...

  std::vector<user*> member;
};


> The question came when I tried to delete a user that was a member of a
> group. This threw a "FOREIGN KEY constraint failed" exception because now
> there was an entry in the group_member table to a user that didn't exist.
> 
> So, my question is how is this best handled using odb?

Ok, let me discuss all the possible ways to handle this:

0. Naturally, the best approach is not to have this problem in the first
   place ;-). The idea is to put the pointer (aka foreign key) into the
   user and not the group class. We can still have the container of
   pointers in group using the inverse relationship (see the manual for
   details on the inverse relationships):

   class group;

   #pragma db object
   class user
   {
     group* belongs;
   };

   #pragma db object
   class group
   {
     ...
 
     #pragma db inverse(belongs)
     std::vector<user*> member;
   };

   Now when we delete the user object, the foreign key gets deleted as
   well.

   The main shortcoming of this approach is that we now have the same
   problem if we try to delete the group. This, however, we can handle
   using one of the next methods. To put it another way, you would want
   to use this method for objects that you delete most often (most likely
   user in your case) since it doesn't cost anything.

1. The most obvious way to handle this is to delete the user entries
   from the affected group(s) before deleting the user:

   user& u = ... // User to delete.
   group& g = *u.belongs; // Group to which it belongs.

   // Find the user in g.members and delete it.
   //
   for (...)
   {
   }

   db.update (g); // Update the group.
   db.erase (u);  // Delete the user.
   
   As you might have noticed, here we assume that there is a way to
   get from the user object to its group (inverse pointer).

   The for loop might not be the best way to do it if this kind of
   operation is performed a lot in your application. In this case
   a container like Boost multi-index might be a better option so
   that you could delete an entry given the user id.

2. Finally, for the next release of ODB (2.4.0) we have added the
   ON DELETE CASCADE support. Now you will be able to do:

   #pragma db object
   class group
   {
     ...

     #pragma db on_delete(cascade)
     std::vector<user*> member;
   }; 
   
   See section 14.4.15 in the (2.4.0) manual for more information
   on this feature.

   The pre-release for 2.4.0 is available here:

   http://codesynthesis.com/~boris/tmp/odb/pre-release/

Let me know if something doesn't make sense.

Boris