Coaster and Flask-Lastuser updates for Eventframe

Kiran Jonnalagadda

unread,

Sep 14, 2012, 1:26:24 PM9/14/12

to hasgee...@googlegroups.com

Hi all,

I just discovered a bit of odd behaviour in SQLAlchemy that affects Eventframe. This is very important if you use Eventframe, so please read.

Eventframe makes use of a concept known as "joined table inheritance" to construct its data types – pages, posts, fragments – off a base "node" type. Eventframe also uses mixin classes defined in coaster.sqlalchemy to make class declarations simpler.

Turns out SQLAlchemy has some odd behaviour in the way mixins work with joined table inheritance. The essential construct is like this:

"""

class NameMixin(object):

id = Column(Integer, primary_key=True)

name = Column(Unicode(250), nullable=False)

title = Column(Unicode(250), nullable=False, default=u'')

class Node(NameMixin, Base):

type = Column(Unicode(20))

__mapper_args__ = {'polymorphic_on': type, 'polymorphic_identity': 'node'}

class Page(Node):

id = Column(Integer, ForeignKey('node.id'), primary_key=True, nullable=False)

__mapper_args__ = {'polymorphic_identity': 'node'}

"""

When a class inherits from another, SQLAlchemy looks for columns in the base class and copies them into the subclass to construct an SQL table. However, when it finds a __mapper_args__ declaration with a polymorphic identity, it does not copy columns. Instead, it runs db queries against both classes, storing part of the data in the base class's table and part in the subclass's table. The way this is handled is completely transparent to you as the developer.

It turns out there is a bug here, or at least some undocumented behaviour. If the base class has columns that it acquired via a mixin, the subclass also gets them. When you write a value into this column, the value is stored to both tables. In the example above, Page is supposed to have a single column, "id". However, it will also get "name" and "title" from NameMixin.

The fix? All columns in a mixin should be declared with @declared_attr. I've just pushed this fix for coaster (http://git.io/fYkVag) and flask-lastuser (http://git.io/dwvgkA). You can safely deploy these changes for any app that uses these libraries.

The one app that is affected is Eventframe. All nodes (except the "node" table itself) now have four extra columns:

created_at
updated_at
name
title

If you update coaster, SQLAlchemy will no longer expect to find these tables. However, all four tables have a NOT NULL constraint, so you can no longer create new nodes. The solution is to drop these columns from these tables:

event
fragment
funnel_link
list
map
page
participant_list
post
redirect

The final kicker: if you use SQLite for development, you can't drop columns. SQLite's ALTER TABLE statement only supports renaming tables and adding new columns. The solution is to dump the data, remove the database, let Eventframe create a new db, edit the SQL dump to drop data for the columns, and re-import. This is messy, but there's no other way.

Let me know if you need help with your deployment.

Best,

Kiran

--

Kiran Jonnalagadda

+91-99452-35123

http://hasgeek.com

Kiran Jonnalagadda

unread,

Sep 14, 2012, 2:42:49 PM9/14/12

to hasgee...@googlegroups.com

On Friday, 14 September 2012 at 10:56 PM, Kiran Jonnalagadda wrote:

class Page(Node):
id = Column(Integer, ForeignKey('node.id'), primary_key=True, nullable=False)
__mapper_args__ = {'polymorphic_identity': 'node'}

Correction: The last line there should be {'polymorphic_identity': 'page'}.

Kiran

Kiran Jonnalagadda

unread,

Sep 14, 2012, 3:39:48 PM9/14/12

to hasgee...@googlegroups.com

I just checked in a helper script to help fix SQLite databases. http://git.io/rStoRg. This works on the expectation that Eventframe can still read the old db but will ignore the removed columns. Instructions for usage (for SQLite development environments only):

cd /path/to/eventframe

python runserver.py # Ensure you have a test.db with all tables

^C

mv eventframe/test.db eventframe/old.db

python runserver.py # Create a new test.db

^C

./refreshdb.py sqlite:///eventframe/old.db sqlite:///eventframe/test.db

python runserver.py # Open in browser and confirm all is okay

Best,

Kiran

--

Kiran Jonnalagadda

+91-99452-35123

http://hasgeek.com

Kiran Jonnalagadda

unread,

Sep 14, 2012, 3:42:05 PM9/14/12

to hasgee...@googlegroups.com

One last update for the night. I've submitted this demo code to Mike Bayer (SQLAlchemy's author) to show what's happening. https://gist.github.com/3723944.

http://twitter.com/jackerhack/status/246684496550850560

Kiran

--

Kiran Jonnalagadda

+91-99452-35123

http://hasgeek.com