How do I create self-relationships in polymorphic inheritance in Elixir and Pylons?

Question

I am new to programming and am following the example in the Pylons documentation on creating a Wiki. The database I want to link to the wiki was created with Elixir so I rewrote the Wiki database schema and have continued from there.

In the wiki there is a requirement for a Navigation table which is inherited by Pages and Sections. A section can have many pages, while a page can only have one section. In addition, each sibling node can be chain-referenced to each other.

So:

• Nav has "section" (OneToMany) and "before" (OneToOne - to reference preceeding node)
• Page has "section" (ManyToOne - many pages in one section) and inherits "before"
• Section inherits all from Nav

The code I've written looks like this:

class Nav(Entity):
    using_options(inheritance='multi')
    name = Field(Unicode(30), default=u'Untitled Node')
    path = Field(Unicode(255), default=u'')
    section = OneToMany('Page', inverse='section')
    after = OneToOne('Nav', inverse='before')
    before = OneToMany('Nav', inverse='after')

class Page(Nav):
    using_options(inheritance='multi')
    content = Field(UnicodeText, nullable=False)
    posted = Field(DateTime, default=now())
    title = Field(Unicode(255), default=u'Untitled Page')
    heading = Field(Unicode(255))
    tags = ManyToMany('Tag')
    comments = OneToMany('Comment')
    section = ManyToOne('Nav', inverse='section')

class Section(Nav):
    using_options(inheritance='multi')

Errors received on this:

sqlalchemy.exc.OperationalError: (OperationalError) table nav has no column named aftr_id u'INSERT INTO nav (name, path, aftr_id, row_type) VALUES (?, ?, ?, ?)'

I've also tried:

   before = ManyToMany('Nav', inverse='before')

on Nav in the hopes this might break the problem, but also not.

The original SQLAlchemy code from the tutorial for these declarations is as follows:

nav_table = schema.Table('nav', meta.metadata,
    schema.Column('id', types.Integer(), 
        schema.Sequence('nav_id_seq', optional=True), primary_key=True),
    schema.Column('name', types.Unicode(255), default=u'Untitled Node'),
    schema.Column('path', types.Unicode(255), default=u''),
    schema.Column('section', types.Integer(), schema.ForeignKey('nav.id')),
    schema.Column('before', types.Integer(), default=None),
    schema.Column('type', types.String(30), nullable=False)
)

page_table = schema.Table('page', meta.metadata,
    schema.Column('id', types.Integer, schema.ForeignKey('nav.id'), primary_key=True),
    schema.Column('content', types.Text(), nullable=False),
    schema.Column('posted', types.DateTime(), default=now),
    schema.Column('title', types.Unicode(255), default=u'Untitled Page'),
    schema.Column('heading', types.Unicode(255)),
)

section_table = sa.Table('section', meta.metadata, 
    schema.Column('id', types.Integer, 
        schema.ForeignKey('nav.id'), primary_key=True),
)

orm.mapper(Nav, nav_table, polymorphic_on=nav_table.c.type, polymorphic_identity='nav')
orm.mapper(Section, section_table, inherits=Nav, polymorphic_identity='section')
orm.mapper(Page, page_table, inherits=Nav, polymorphic_identity='page', properties={
    'comments':orm.relation(Comment, backref='page', cascade='all'),
    'tags':orm.relation(Tag, secondary=pagetag_table)
})

Any help is much appreciated.

Answer 1

I think that your model is mostly correct. The only thing I found is the link section from Nav->Page and back:

class Nav(Entity):
    section = OneToMany('Page', inverse='section')
class Page(Nav):
    section = ManyToOne('Nav', inverse='section')

The tutorial just that the Section (not Page) is the parent (class Nav), so you should instead have:

class Nav(Entity):
    section = ManyToOne('Section')
# and optionally inverse
class Section(Nav):
    children = ManyToOne('Nav')

Basically, to make a model clear, see Section as a Directory, where Page is like a File. Both of them have a (parent) section and, assuming that they are sorted in some way, also have the before reference.

The part with before and after looks correct to me. So the only thing remaining is that you database schema does not reflect the object model. Can you re-create the db model? Or at least post resulting db scripts as well?