On Aug 7, 2011, at 4:54 AM, Arturo Sevilla wrote:
> Hi,
>
> I think I have detected two bugs for PostgreSQL databases. I don't think is a
> different behavior between 0.6 and 0.7 as there is no problem with sqlite.
>
> In the following code I have a very simple model, which contains a helper
> "pre-process" list class, which just transforms strings into the correct
> model object. Very similar to @collection.converter, however it works for
> append and extend. It works without errors (obviously w/o MutableComposite)
> in 0.6 and sqlite in 0.7. But the strange part is that it follows different
> behavior depending on when you add the parent object to the session: if you
> add it after creating the "company" (just like I put it here), then it will
> not insert the "data" object (see the produced log, if you change it to the
> sqlite engine it will work); but if you add and commit it, before and after
> the append operation it will fail with an IntegrityError in both sqlite and
> postgresql (this is why I doubt if it is a different behavior or not).
>
> Either way I think that adding the object, committing it, then adding it
> again, and committing it, although very wasteful, should behave without
> error, specially for occasions when you don't know how your object has been
> handled in relation with your database.
>
> The other bug is just for PostgreSQL and can be reproduced by uncommenting
> the "metadata.create_all()" line of code. It appears that is trying to create
> the data table before company. It doesn't matter in which order are defined
> the same error is raised.
Ran your test, and here are the facts I have about what is below, which appear
to disagree with your observations:
1. the error I am seeing, equally on PG and SQLite, is:
sqlalchemy.exc.IntegrityError: (IntegrityError) null value in column "company"
violates not-null constraint
'INSERT INTO data (data, company) VALUES (%(data)s, %(company)s) RETURNING
data.id' {'company': None, 'data': 'adata'}
There is no other error I can produce, and it's only with the add(). The
reason is simple, which is that your custom list is preventing instrumentation
from working correctly. Without the explicit add(), the "Data" element never
makes it into the Session. With the add(), it makes it in the session but no
history event is apparent allowing the sync from company->data.
2. mutable composite, the point, etc. has nothing to do with anything here.
Removed it all for simplicity, makes no difference.
3. It would be *extremely* unusual/unexplainable if create_all() were actually
failing to see the ForeignKey applied to "data" establishing it as dependent on
"company". That cannot be reproduced at all here.
4. I make extremely scant usage of the @collection decorators, as they are
giving you a window into an already intricate system which has not seen much
customization in practice. The thing you're calling down towards needs to be
instrumented which is not the case here, its just plain list.append() - so I
moved it to _append() which is the one that gets the instrumentation - then
@internally_instrumented to keep it from instrumenting append(). I think if
the collection approach were used fully, *maybe* it could do the use case here
fully, i.e. if __iter__ and friends were also implemented to produce the "view
of strings".
5. The use case of "I'd like a collection that shows off just a string
attribute" is extremely doable via association proxy, which is designed exactly
for this kind of thing. The assoc_proxy.py example attached, in just a few
lines of code, produces a pure view of "a", "b", "c", "d", etc. with the "data"
suffix as well as the existence of Data() entirely concealed.
Assignment/set/remove all work equally well. Documentation for association
proxy was just rewritten two days ago.
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, \
String, ForeignKey
from sqlalchemy.orm import mapper, relationship, sessionmaker
from sqlalchemy.orm import collections
engine = create_engine('postgresql://scott:tiger@localhost/test', echo=True)
metadata = MetaData()
company = Table(
'company',
metadata,
Column('id', Integer, primary_key=True),
Column('name', String, nullable=False),
sqlite_autoincrement=True
)
data = Table(
'data',
metadata,
Column('id', Integer, primary_key=True),
Column('data', String, nullable=False),
Column('company', Integer, ForeignKey('company.id'), nullable=False),
sqlite_autoincrement=True
)
metadata.bind = engine
metadata.drop_all()
metadata.create_all()
class PreprocessList(list):
def __init__(self, items=None):
if items is None:
super(PreprocessList, self).__init__()
else:
super(PreprocessList, self).__init__(
[self.preprocess(item) for item in items]
)
def preprocess(self, value):
return value
def __setitem__(self, key, value):
value = self.preprocess(value)
super(PreprocessList, self).__setitem__(key, value)
# instrument this one to get the actual
# events...
@collections.collection.appender
def _append(self, value):
super(PreprocessList, self).append(value)
# dont instrument append() as collections
# seems convinced doing otherwise...
@collections.collection.internally_instrumented
def append(self, value):
value = self.preprocess(value)
self._append(value)
class AppenderList(PreprocessList):
def preprocess(self, value):
v = Data()
v.data = value + 'data'
return v
def __getitem__(self, key):
value = super(AppenderList, self).__getitem__(key)
return value.data
class Data(object):
pass
class Company(object):
pass
mapper(Company, company, properties={
'id': company.c.id,
'name': company.c.name,
'data': relationship(Data, collection_class=AppenderList),
})
mapper(Data, data)
session = sessionmaker(bind=engine)()
# CHANGE BELOW
c = Company()
c.name = 'company 1'
session.add(c)
session.commit()
# can't do this, the wrapping doesnt
# really work out
#c.data = ["a", "b", "c", "d", "e"]
c.data.append("b")
c.data.append("c")
c.data.append("f")
session.add(c)
session.commit()
# gives you a Data()...
print c.data
# nah...
#c.data.remove("cdata")
session.close()
metadata.drop_all()
print 'ok'
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, \
String, ForeignKey
from sqlalchemy.orm import mapper, relationship, sessionmaker
from sqlalchemy.ext.associationproxy import association_proxy
engine = create_engine('postgresql://scott:tiger@localhost/test', echo=True)
metadata = MetaData()
company = Table(
'company',
metadata,
Column('id', Integer, primary_key=True),
Column('name', String, nullable=False),
sqlite_autoincrement=True
)
data = Table(
'data',
metadata,
Column('id', Integer, primary_key=True),
Column('data', String, nullable=False),
Column('company', Integer, ForeignKey('company.id'), nullable=False),
sqlite_autoincrement=True
)
metadata.bind = engine
metadata.drop_all()
metadata.create_all()
class Data(object):
@property
def data_without_suffix(self):
return self.data[:-4]
@data_without_suffix.setter
def data_without_suffix(self, value):
self.data = value + "data"
def _create_data(value):
v = Data()
v.data = value + "data"
return v
class Company(object):
# note by proxying to data_without_suffix, we are reducing off the
# "data" suffix. If you don't want that, do away with "data_without_suffix"
# and just point it at the "Data.data" attribute.
data = association_proxy("_data", "data_without_suffix", creator=_create_data)
mapper(Company, company, properties={
'id': company.c.id,
'name': company.c.name,
'_data': relationship(Data, cascade="all, delete-orphan"),
})
mapper(Data, data)
session = sessionmaker(bind=engine)()
# CHANGE BELOW
c = Company()
c.name = 'company 1'
session.add(c)
session.commit()
c.data = ["a", "b", "c", "d", "e"]
c.data.append("f")
session.add(c)
session.commit()
print c.data
c.data.remove("c")
print c.data
session.commit()
print c.data
session.close()
metadata.drop_all()
print 'ok'
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
