[Bug 45023] TestBot Object Relational Mapper issues

[WineHQ Bugzilla]

https://bugs.winehq.org/show_bug.cgi?id=45023

--- Comment #6 from François Gouget  ---
19. Only one Detailref can point to a table (otherwise expect inconsistencies)

The TestBot hasa  Users table (e.g. fgouget), a Roles table (e.g. wine-devel),
and a UserRoles table which describes the roles that each user has (e.g.
[fgouget, wine-devel]).

One can access a user's roles, for instance:
$User->Roles->GetItem("wine-devel"). But there is no support for accessing a
role's users: $Role->Users->GetItem("fgouget") fails because there is no Users
field.

Adding a Users field is possible but it does not work right. For instance:

my $UR = CreateUsers()->GetItem("fgouget")->Roles->GetItem("wine-devel");
  print "Full key=", $UR->GetFullKey(), "\n";
  -> fgouget#@#wine-devel      # This is correct

my $UR = CreateRoles()->GetItem("wine-devel")->Users->GetItem("fgouget");
  print "RU=<undef>\n" if (!defined $RU);
  -> RU=<undef>                # Wrong!!! [1]

my $Users = CreateRoles()->GetItem("wine-devel")->Users;
  map { print $_->GetFullKey(), "\n" } @{$Users->GetItems()};
  -> wine-devel#@#wine-devel   # Wrong!!! [2]

The trouble is that whenever a UserRole is retrieved from a Collection its
declaration is:

my @PropertyDescriptors = (
    CreateItemrefPropertyDescriptor("Role", "Role", 1,  1, &CreateRoles,
["RoleName"]),
  );

That's because it is assumed that in that case the UserName field in comes from
the parent User Item and thus will be present in as master column.

* This is why trying to retrieve the UserRole item by its UserName field in [1]
does not work. There is no UserName field, neither as a regular field, nor as a
master column.

* This will also break the database saves since the SQL query will not include
the required UserName field. (Note: reads work, the SQL query just returns all
the entries with the specified RoleName field, and we create a bunch of
UserRole objects where only the RoleName field is set).

* This is also why the full key is wrong in [2]: RoleName ends up as both a
master column and a regular field so the full key is 'wine-devel' (master
column) + '#@#' + 'wine-devel' (regular field).

* Note that even if the UserName field was somehow added back, the full key
would end up being 'wine-devel#@#fgouget' instead of 'fgouget#@#wine-devel'.
This issue will remain as long as the first component of the full key comes
from the parent Item.

* This also breaks the scope lookups since they depend on the full key. The
consequence is code that expects the same object to be returned no matter which
path is taken will get two different objects with all the implied state
inconsistencies.