Edit me on GitHub

8: SQL Traversal and Adding Content

Traverse through a resource tree of data stored in an RDBMS, adding folders and documents at any point.

Background

We now have SQLAlchemy providing us a persistent root. How do we arrange an infinitely-nested URL space where URL segments point to instances of our classes, nested inside of other instances?

SQLAlchemy, as mentioned previously, uses the adjacency list relationship to allow self-joining in a table. This allows a resource to store the identifier of its parent. With this we can make a generic “Node” model in SQLAlchemy which holds the parts needed by Pyramid’s traversal.

In a nutshell, we are giving RDBMS data Python dictionary behavior, using built-in SQLAlchemy relationships. This lets us define our own kinds of containers and own kinds of types, nested in any way we like.

Goals

Steps

  1. We are going to use the previous step as our starting point:

    $ cd ..; cp -r sqlroot sqladdcontent; cd sqladdcontent
    $ $VENV/bin/python setup.py develop
    
  2. Make a Python module for a generic Node base class that gives us traversal-y behavior in sqladdcontent/tutorial/sqltraveral.py:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    from sqlalchemy import (
        Column,
        Integer,
        Unicode,
        ForeignKey,
        String
        )
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy.orm import (
        scoped_session,
        sessionmaker,
        relationship,
        backref
        )
    from sqlalchemy.orm.exc import NoResultFound
    from sqlalchemy.util import classproperty
    from zope.sqlalchemy import ZopeTransactionExtension
    
    DBSession = scoped_session(
        sessionmaker(extension=ZopeTransactionExtension()))
    Base = declarative_base()
    
    
    def u(s):
        # Backwards compatibility for Python 3 not having unicode()
        try:
            return unicode(s)
        except NameError:
            return str(s)
    
    
    def root_factory(request):
        return DBSession.query(Node).filter_by(parent_id=None).one()
    
    
    class Node(Base):
        __tablename__ = 'node'
        id = Column(Integer, primary_key=True)
        name = Column(Unicode(50), nullable=False)
        parent_id = Column(Integer, ForeignKey('node.id'))
        children = relationship("Node",
                                backref=backref('parent', remote_side=[id])
        )
        type = Column(String(50))
    
        @classproperty
        def __mapper_args__(cls):
            return dict(
                polymorphic_on='type',
                polymorphic_identity=cls.__name__.lower(),
                with_polymorphic='*',
            )
    
        def __setitem__(self, key, node):
            node.name = u(key)
            if self.id is None:
                DBSession.flush()
            node.parent_id = self.id
            DBSession.add(node)
            DBSession.flush()
    
        def __getitem__(self, key):
            try:
                return DBSession.query(Node).filter_by(
                    name=key, parent=self).one()
            except NoResultFound:
                raise KeyError(key)
    
        def values(self):
            return DBSession.query(Node).filter_by(parent=self)
    
        @property
        def __name__(self):
            return self.name
    
        @property
        def __parent__(self):
            return self.parent
    
  3. sqladdcontent/tutorial/models.py is very simple,

    with the heavy lifting moved to the common module:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    from sqlalchemy import (
        Column,
        Integer,
        Text,
        ForeignKey,
        )
    
    from .sqltraversal import Node
    
    
    class Folder(Node):
        __tablename__ = 'folder'
        id = Column(Integer, ForeignKey('node.id'), primary_key=True)
        title = Column(Text)
    
    
    class Document(Node):
        __tablename__ = 'document'
        id = Column(Integer, ForeignKey('node.id'), primary_key=True)
        title = Column(Text)
    
  4. Our sqladdcontent/tutorial/views.py is almost unchanged from the version in the addcontent step:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    from random import randint
    
    from pyramid.httpexceptions import HTTPFound
    from pyramid.location import lineage
    from pyramid.view import view_config
    
    from .models import (
        Folder,
        Document
        )
    
    
    class TutorialViews(object):
        def __init__(self, context, request):
            self.context = context
            self.request = request
            self.parents = reversed(list(lineage(context)))
    
        @view_config(renderer="templates/root.jinja2",
                     context=Folder, custom_predicates=[lambda c, r: c is r.root])
        def root(self):
            page_title = 'Quick Tutorial: Root'
            return dict(page_title=page_title)
    
        @view_config(renderer="templates/folder.jinja2",
                     context=Folder)
        def folder(self):
            page_title = 'Quick Tutorial: Folder'
            return dict(page_title=page_title)
    
        @view_config(name="add_folder", context=Folder)
        def add_folder(self):
            # Make a new Folder
            title = self.request.POST['folder_title']
            name = str(randint(0, 999999))
            new_folder = self.context[name] = Folder(title=title)
    
            # Redirect to the new folder
            url = self.request.resource_url(new_folder)
            return HTTPFound(location=url)
    
        @view_config(name="add_document", context=Folder)
        def add_document(self):
            # Make a new Document
            title = self.request.POST['document_title']
            name = str(randint(0, 999999))
            new_document = self.context[name] = Document(title=title)
    
            # Redirect to the new document
            url = self.request.resource_url(new_document)
            return HTTPFound(location=url)
    
        @view_config(renderer="templates/document.jinja2",
                     context=Document)
        def document(self):
            page_title = 'Quick Tutorial: Document'
            return dict(page_title=page_title)
    
  5. Our templates are all unchanged from addcontent. Let’s bring them back. Make a re-usable snippet in sqladdcontent/tutorial/templates/addform.jinja2 for adding content:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    <p>
        <form class="form-inline"
              action="{{ request.resource_url(context, 'add_folder') }}"
              method="POST">
            <div class="form-group">
                <input class="form-control" name="folder_title"
                       placeholder="New folder title..."/>
            </div>
            <input type="submit" class="btn" value="Add Folder"/>
        </form>
    </p>
    <p>
        <form class="form-inline"
              action="{{ request.resource_url(context, 'add_document') }}"
              method="POST">
            <div class="form-group">
                <input class="form-control" name="document_title"
                       placeholder="New document title..."/>
            </div>
            <input type="submit" class="btn" value="Add Document"/>
        </form>
    </p>
    
  6. Need this snippet added to sqladdcontent/tutorial/templates/root.jinja2:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    {% extends "templates/layout.jinja2" %}
    {% block content %}
    
        <h2>{{ context.title }}</h2>
        <p>The root might have some other text.</p>
        {% include "templates/contents.jinja2" %}
    
        {% include "templates/addform.jinja2" %}
    
    {% endblock content %}
    
  7. Need a view template for folder at sqladdcontent/tutorial/templates/folder.jinja2:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    {% extends "templates/layout.jinja2" %}
    {% block content %}
    
        <h2>{{ context.title }}</h2>
        {% include "templates/contents.jinja2" %}
    
        {% include "templates/addform.jinja2" %}
    
    {% endblock content %}
    
  8. Also need a view template for document at sqladdcontent/tutorial/templates/document.jinja2:

    1
    2
    3
    4
    5
    6
    7
    {% extends "templates/layout.jinja2" %}
    {% block content %}
    
        <h2>{{ context.title }}</h2>
        <p>A document might have some body text.</p>
    
    {% endblock content %}
    
  9. Run your Pyramid application with:

    $ $VENV/bin/pserve development.ini --reload
    
  10. Open http://localhost:6543/ in your browser.

Analysis

If we consider our views and templates as the bulk of our business logic when handling web interactions, then this was an intriguing step. We had no changes to our templates from the addcontent and zodb steps, and almost no change to the views. We made a one-line change when creating a new object. We also had to “stack” an extra @view_config (although that can be solved in other ways.)

We gained a resource tree that gave us hierarchies. And for the most part, these are already full-fledged “resources” in Pyramid:

  • Traverse through a tree and match a view on a content type
  • Know how to get to the parents of any resource (even if outside the current URL)
  • All the traversal-oriented view predicates apply
  • Ability to generate full URLs for any resource in the system

Even better, the data for the resource tree is stored in a table separate from the core business data. Equally, the ORM code for moving through the tree is in a separate module. You can stare at the data and the code for your business objects and ignore the the Pyramid part.

This is most useful for projects starting with a blank slate, with no existing data or schemas they have to adhere to. Retrofitting a tree on non-tree data is possible, but harder.

Table Of Contents

Previous topic

7: RDBMS Root Factories

Next topic

Pyramid Add-On Developer Tutorial