"""Mapping a vertical table as a dictionary.

This example illustrates accessing and modifying a "vertical" (or
"properties", or pivoted) table via a dict-like interface.  These are tables
that store free-form object properties as rows instead of columns.  For
example, instead of::

  # A regular ("horizontal") table has columns for 'species' and 'size'
  Table('animal', metadata,
        Column('id', Integer, primary_key=True),
        Column('species', Unicode),
        Column('size', Unicode))

A vertical table models this as two tables: one table for the base or parent
entity, and another related table holding key/value pairs::

  Table('animal', metadata,
        Column('id', Integer, primary_key=True))

  # The properties table will have one row for a 'species' value, and
  # another row for the 'size' value.
  Table('properties', metadata
        Column('animal_id', Integer, ForeignKey('animal.id'),
               primary_key=True),
        Column('key', UnicodeText),
        Column('value', UnicodeText))

Because the key/value pairs in a vertical scheme are not fixed in advance,
accessing them like a Python dict can be very convenient.  The example below
can be used with many common vertical schemas as-is or with minor adaptations.

"""

class VerticalProperty(object):
    """A key/value pair.

    This class models rows in the vertical table.
    """

    def __init__(self, key, value):
        self.key = key
        self.value = value

    def __repr__(self):
        return '<%s %r=%r>' % (self.__class__.__name__, self.key, self.value)


class VerticalPropertyDictMixin(object):
    """Adds obj[key] access to a mapped class.

    This is a mixin class.  It can be inherited from directly, or included
    with multiple inheritence.

    Classes using this mixin must define two class properties::

    _property_type:
      The mapped type of the vertical key/value pair instances.  Will be
      invoked with two positional arugments: key, value

    _property_mapping:
      A string, the name of the Python attribute holding a dict-based
      relationship of _property_type instances.

    Using the VerticalProperty class above as an example,::

      class MyObj(VerticalPropertyDictMixin):
          _property_type = VerticalProperty
          _property_mapping = 'props'

      mapper(MyObj, sometable, properties={
        'props': relationship(VerticalProperty,
                          collection_class=attribute_mapped_collection('key'))})

    Dict-like access to MyObj is proxied through to the 'props' relationship::

      myobj['key'] = 'value'
      # ...is shorthand for:
      myobj.props['key'] = VerticalProperty('key', 'value')

      myobj['key'] = 'updated value']
      # ...is shorthand for:
      myobj.props['key'].value = 'updated value'

      print myobj['key']
      # ...is shorthand for:
      print myobj.props['key'].value

    """

    _property_type = VerticalProperty
    _property_mapping = None

    __map = property(lambda self: getattr(self, self._property_mapping))

    def __getitem__(self, key):
        return self.__map[key].value

    def __setitem__(self, key, value):
        property = self.__map.get(key, None)
        if property is None:
            self.__map[key] = self._property_type(key, value)
        else:
            property.value = value

    def __delitem__(self, key):
        del self.__map[key]

    def __contains__(self, key):
        return key in self.__map

    # Implement other dict methods to taste.  Here are some examples:
    def keys(self):
        return self.__map.keys()

    def values(self):
        return [prop.value for prop in self.__map.values()]

    def items(self):
        return [(key, prop.value) for key, prop in self.__map.items()]

    def __iter__(self):
        return iter(self.keys())


if __name__ == '__main__':
    from sqlalchemy import (MetaData, Table, Column, Integer, Unicode,
        ForeignKey, UnicodeText, and_, not_, create_engine)
    from sqlalchemy.orm import mapper, relationship, Session
    from sqlalchemy.orm.collections import attribute_mapped_collection

    metadata = MetaData()

    # Here we have named animals, and a collection of facts about them.
    animals = Table('animal', metadata,
                    Column('id', Integer, primary_key=True),
                    Column('name', Unicode(100)))

    facts = Table('facts', metadata,
                  Column('animal_id', Integer, ForeignKey('animal.id'),
                         primary_key=True),
                  Column('key', Unicode(64), primary_key=True),
                  Column('value', UnicodeText, default=None),)

    class AnimalFact(VerticalProperty):
        """A fact about an animal."""

    class Animal(VerticalPropertyDictMixin):
        """An animal.

        Animal facts are available via the 'facts' property or by using
        dict-like accessors on an Animal instance::

          cat['color'] = 'calico'
          # or, equivalently:
          cat.facts['color'] = AnimalFact('color', 'calico')
        """

        _property_type = AnimalFact
        _property_mapping = 'facts'

        def __init__(self, name):
            self.name = name

        def __repr__(self):
            return '<%s %r>' % (self.__class__.__name__, self.name)


    mapper(Animal, animals, properties={
        'facts': relationship(
            AnimalFact, backref='animal',
            collection_class=attribute_mapped_collection('key')),
        })
    mapper(AnimalFact, facts)

    engine = create_engine("sqlite://")
    metadata.create_all(engine)
    session = Session(bind=engine)

    stoat = Animal('stoat')
    stoat['color'] = 'reddish'
    stoat['cuteness'] = 'somewhat'

    # dict-like assignment transparently creates entries in the
    # stoat.facts collection:
    print(stoat.facts['color'])

    session.add(stoat)
    session.commit()

    critter = session.query(Animal).filter(Animal.name == 'stoat').one()
    print(critter['color'])
    print(critter['cuteness'])

    critter['cuteness'] = 'very'

    print('changing cuteness:')
    engine.echo = True
    session.commit()
    engine.echo = False

    marten = Animal('marten')
    marten['color'] = 'brown'
    marten['cuteness'] = 'somewhat'
    session.add(marten)

    shrew = Animal('shrew')
    shrew['cuteness'] = 'somewhat'
    shrew['poisonous-part'] = 'saliva'
    session.add(shrew)

    loris = Animal('slow loris')
    loris['cuteness'] = 'fairly'
    loris['poisonous-part'] = 'elbows'
    session.add(loris)
    session.commit()

    q = (session.query(Animal).
         filter(Animal.facts.any(
           and_(AnimalFact.key == 'color',
                AnimalFact.value == 'reddish'))))
    print('reddish animals', q.all())

    # Save some typing by wrapping that up in a function:
    with_characteristic = lambda key, value: and_(AnimalFact.key == key,
                                                  AnimalFact.value == value)

    q = (session.query(Animal).
         filter(Animal.facts.any(
           with_characteristic('color', 'brown'))))
    print('brown animals', q.all())

    q = (session.query(Animal).
           filter(not_(Animal.facts.any(
                         with_characteristic('poisonous-part', 'elbows')))))
    print('animals without poisonous-part == elbows', q.all())

    q = (session.query(Animal).
         filter(Animal.facts.any(AnimalFact.value == 'somewhat')))
    print('any animal with any .value of "somewhat"', q.all())

    # Facts can be queried as well.
    q = (session.query(AnimalFact).
         filter(with_characteristic('cuteness', 'very')))
    print('just the facts', q.all())