back to the index

Armin Ronacher

Index // Archive // Projects // About

Wiki Models with SQLAlchemy

written by Armin Ronacher, on Thursday, November 22, 2007 0:34.

I was working on an example application for the upcoming Werkzeug release and decided to make a wiki for that purpose. (The main reason is that I found the Creoleparser on the pypi index and thought that it would integrate into a genshi powered wiki well)

Now wikis have an interesting data structure. Basically you have pages and revisions, where a revision is bound to exactly one page. Simple to model and my database definition looks like that: 

page_table = Table('pages', metadata,
    Column('page_id', Integer, primary_key=True),
    Column('name', String(60), unique=True)
)

revision_table = Table('revisions', metadata,
    Column('revision_id', Integer, primary_key=True),
    Column('page_id', Integer, ForeignKey('pages.page_id')),
    Column('timestamp', DateTime),
    Column('text', String),
    Column('change_note', String(200))
)
Very simple schema and you can easily bind it to two classes: Very simple schema and you can easily bind it to two classes: 
class Revision(object):
    def __init__(self, page, text, change_note='', timestamp=None):
        if isinstance(page, (int, long)):
            self.page_id = page
        else:
            self.page = page
        self.text = text
        self.change_note = change_note
        self.timestamp = timestamp or datetime.utcnow()

    def render(self, request=None):
        """Render the page text into a genshi stream."""
        if request is None:
            request = get_request()
            if request is None:
                raise RuntimeError('rendering requires request context')
        return parse_creole(request, self.text)

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

    @property
    def title(self):
        return self.name.replace('_', ' ')

Session.mapper(Revision, revision_table)
Session.mapper(Page, page_table, properties=dict(
    revisions=relation(Revision, backref='page', order_by=[desc(Revision.revision_id)])
))
Works pretty well but as soon as you start putting your stuff into the template it feels unpythonic :-) You basically have to provide always two objects, the revision and the page. Why not combine that into one model? First I removed my database tables again and combined that into one table but then I found out that you can map joins to classes in SQLAlchemy. That and Python's ability to do multiple inheritance gives me the ability to combine both tables into one class: Works pretty well but as soon as you start putting your stuff into the template it feels unpythonic :-) You basically have to provide always two objects, the revision and the page. Why not combine that into one model? First I removed my database tables again and combined that into one table but then I found out that you can map joins to classes in SQLAlchemy. That and Python's ability to do multiple inheritance gives me the ability to combine both tables into one class: 
class RevisionedPage(Page, Revision):
    pass

Session.mapper(RevisionedPage, join(page_table, revision_table), properties=dict(
    page_id=[page_table.c.page_id, revision_table.c.page_id],
))
Very nice for the templates and also easy to query. It looks like a normal python class. I haven't tried if that also works for write access, but I doubt it. Because of that I have added an exception to the __init__ method to avoid creating pages and revisions via the RevisionedPage object. Very nice for the templates and also easy to query. It looks like a normal python class. I haven't tried if that also works for write access, but I doubt it. Because of that I have added an exception to the __init__ method to avoid creating pages and revisions via the RevisionedPage object.

The full example can be found in the simplewiki sources.

Tagged as: python, sqlalchemy, werkzeug

Comments

  1. Hi, any special reason not to use Jinja for the templates in this example ?

    —  k4ml on Saturday, November 24, 2007 6:20 #

  2. First of all creoleparser uses genshi internally so it plays nice with genshi itself of course. On the other hand I want to show that you can combine werkzeug with any library out there not just the pocoo libraries. :-)

    Regards,
    Armin

    —  Armin Ronacher on Saturday, November 24, 2007 19:01 #

  3. Thank you for writing this bit, it does help conceptualising the thing. I saw how Django does that, I read the definitions of Active Record and Data Mapper, and your piece gives a clear Python example on how to do the second with SQLAlchemy.

    Don’t want to say a word on Wikicreole. Rather writing reStructuredText alone than struggling with brackets! End of sarcasm :) Best regards, Wok

    —  Wok on Saturday, January 5, 2008 6:32 #

Leave a Reply