ReferenceSet should allow arbitrary qualifiers for WHERE clause

[Philipp von Weitershausen]

When building one-to-many or many-to-many relations, it is not uncommon to have relation qualifiers.

Consider the Company + Employee example from the tutorial. The Employees table might have an extra 'type' field indicating whether the employee is a manager, production line worker or contractor. So instead of/in addition to having the Company.employees property list all employees, one might want Company.managers, Company.workers, etc. properties. Thanks to BoundReferenceSet's find() method, it is already possible to build such properties in a read-only way:

>>> class Company(object):
... __storm_table__ = "company"
... id = Int(primary=True)
... employees = ReferenceSet(id, Employee.company_id)
...
... @property
... def managers(self):
... return self.employees.find(type=3)

This won't allow you to say mycompany.managers.add(bob), however. I therefore suggest extending ReferenceSet and related classes to accept **kw where appropriate, e.g.:

  managers = ReferenceSet(id, Employee.company_id, type=3)

[Philipp von Weitershausen (philikon)]

I will be giving the implementation a shot https://code.launchpad.net/~philikon/storm/qualified-reference

[James Henstridge (jamesh)]

Related to bug 415757. I mainly touched on References there, but the same thing is useful for ReferenceSets, and has some of the same concerns.

[James Henstridge (jamesh)]

Here are some of the concerns from the other bug that apply:

Imagine the following set of classes (syntax is up in the air, but should get the point across):

    class Bar(object):
        __storm_table__ = "bar"
        id = Int(primary = True)
        foo_id = Int()
        qualifier = Int()

    class Foo(Storm):
        __storm_table__ = "foo"
        id = Int(primary=True)
        bars = ConstrainedReferenceSet(id, Bar.foo_id, Bar.quaifier == 42)

1. If we issue the command "foo.bars.add(bar)", is it responsible for setting bar.qualifier? If not, should it try to verify that bar.qualifier is set appropriately, or leave that to database level constraints?

2. What is the result of the following statements:
    foo = Foo()
    bar = Bar()
    bar.qualifier = 42
    foo.bars.add(bar)
    bar.qualifier = 0
    del bar
    store.add(foo)
    store.flush()

3. What should "foo.bars.remove(bar)" do? The two possibilities are to set bar.foo_id to None (what happens with unconstrained reference sets), or change bar.qualifier (to what?).

[Philipp von Weitershausen (philikon)]

Yes, this is indeed the bug 415757 equivalent for ReferenceSet. As for the syntax, Jamu suggested to support both qualifier = 42 and Bar.qualifier == 42 in analogy to e.g. ResultSet.set(). This is now implemented on my qualified-reference branch, though for ReferenceSet only. I wasn't aware of bug 415757 and hadn't even considered that it might be useful for Reference too.

Regarding your concerns: My main use case is for many-to-many relationships in which the qualifier is on the link table. I think it's pretty clear what ReferenceSet.add() and ReferenceSet.remove() should do in these cases, namely create or remove entries in the link table with the respective qualifier value. Existing entries shouldn't be touched (neither be modified nor deleted). As for one-to-many relations or even a Reference, I suggest to do (and document) the following:

1. foo.bars.add(bar) will set bar.qualifier appropriately (because it would also set bar.foo_id). It is debatable what should happen if bar.qualifier is already set (IOW not None). I think the value should be overwritten.

2. This is a good question. I don't know enough of Storm's internals to predict how the Store will behave. But from a user's point of view, they shot themselves in the foot: foo.bars would not contain bar, though bar would probably still be written to the database, albeit with qualifier=0. The question is, would foo.bars still hold a reference to bar or is the Store clever enough to figure that out? If the latter, then I think the behaviour is consistent and predictable.

3. AFAIK foo.bars.remove(bar) right now sets bar.foo_id=None so it would make sense to set bar.qualifier=None as well. I don't know what would happen with unconstrained reference sets... wouldn't that be a problem for bar.foo_id=None already?

[Philipp von Weitershausen (philikon)]

I've sort of given up on implementing this in Storm proper. I really only need this for the many-to-many case which is much simpler in terms of design decisions and edge cases. Instead I've implemented the many-to-many case only in a private subclass of ReferenceSet. I'm attaching the source code, feel free to make use of it.

[Philipp von Weitershausen (philikon)]

... and here's the test for reference.py from comment #5.