[odb-users] Best Practice deleting persistent objects

[Per Edin]

Solution 0 limits the number of groups a user can be in to at most 1.

Perhaps the first issue is to determine if a user shall be able to be
in 1 or more groups? :)

On Thu, Apr 3, 2014 at 5:03 PM, Boris Kolpackov <boris at codesynthesis.com> wrote:
> 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
>