column_mapped_collection, etc. can change keys after DB roundtrip

Working off of the example at:

http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_relation_collections_dictcollections

Specifically, the line:

item.notes['color']('color') = Note('color', 'blue')

I realized that one could do the following (forgive the verbosity, but it's a full example that could be executed):

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.orm.collections import column_mapped_collection
from sqlalchemy.util import *

engine = create_engine("sqlite://", echo=False)
Session = sessionmaker(bind=engine, autoflush=True, transactional=True)
session = Session()
metadata = MetaData()

table_withdict = Table("withdict", metadata,
        Column("id", Integer, primary_key=True),
        Column("name", String(255)))

table_kv = Table( "key_value", metadata,
        Column("id", Integer, primary_key=True),
        Column("withdict_id", Integer, ForeignKey("withdict.id")),
        Column("key", String(255)),
        Column("value", String (255))
        )

metadata.create_all( engine )

class ClassWithDict(object):
    def __init__(self, name):
        self.name = name

class KeyValue(object):
    def __init__(self, key, value):
        self.key = key
        self.value = value

mapper(KeyValue, table_kv)

mapper(ClassWithDict, table_withdict, properties={
    "kv_dict":relation(KeyValue, 
        collection_class=column_mapped_collection(table_kv.c.key))
})

md = ClassWithDict("foo")
md.kv_dict[gets ignored"]("this) = KeyValue("1", "one")
md.kv_dict[gets ignored also"]("this) = KeyValue("2", "two")

print md.kv_dict

session.save(md)
session.commit()
session.clear()

md = session.query(ClassWithDict).filter(ClassWithDict.name == "foo").one()
print md.kv_dict

Output:

{'this gets ignored': <main.KeyValue object at 0xb7a6fbac>, 'this gets ignored also': <main.KeyValue object at 0xb7a7450c>}

{u'1': <main.KeyValue object at 0xb7a7a72c>, u'2': <main.KeyValue object at 0xb7a7a6ec>}

This seems broken. I think that the underlying idea might have to change. Something like (keeping the above example classes):

mapper(ClassWithDict, table_withdict, properties={
    "kv_dict":relation(KeyValue, colleciton_class=something_dict_collection(
        key_from_entity=lambda kv: kv.key,
        value_from_entity=lambda kv: kv.value,
        entity_from_key_value=lambda k, v: KeyValue(k, v)
    ))
})

The backing dict's get/set would look something like:

#...
    def __setitem__(self, key, value):
        self._actual_backing_dict[key](key) = self.entity_from_key_value(key, value)

    def __getitem__(self, key):
        self.value_from_entity(self._actual_backing_dict[key](key))

And "key_from_entity" would be used when constructing the _actual_backing_dict out of the entities from the DB.

The main downside to this is that you can't get at any nifty functionality on the KeyValue class - you're limited to accessing the value. I think that's the tradeoff you have to make to keep the semantics sorted out. The current behavior could be maintained by doing:

def setKeyAndReturn(key, kv):
    kv.key = key
    return kv

mapper(ClassWithDict, table_withdict, properties={
    "kv_dict":relation(KeyValue, colleciton_class=something_dict_collection(
        key_from_entity=lambda kv: kv.key,
        value_from_entity=lambda kv: kv,
        entity_from_key_value=setKeyAndReturn
    ))
})

Thanks for all the hard work - SA is a great product! Looking forward to the 0.4 full release. :)

--Eli Stevens

Comments (1)