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 Python dictionary behavior to RDBMS data, using built-in SQLAlchemy relationships. This lets us define our own kinds of containers and 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-like behavior in sqladdcontent/tutorial/sqltraversal.py:

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

  3. Update the import in __init__.py to use the new module we just created.

     1from pyramid.config import Configurator
 2
 3from sqlalchemy import engine_from_config
 4
 5from .sqltraversal import (
 6    DBSession,
 7    Base,
 8    root_factory,
 9    )
10
11
12def main(global_config, **settings):
13    engine = engine_from_config(settings, 'sqlalchemy.')
14    DBSession.configure(bind=engine)
15    Base.metadata.bind = engine
16
17    config = Configurator(settings=settings,
18                          root_factory=root_factory)
19    config.include('pyramid_jinja2')
20    config.scan('.views')
21    return config.make_wsgi_app()

  4. sqladdcontent/tutorial/models.py is very simple, with the heavy lifting moved to the common module:

     1from sqlalchemy import (
 2    Column,
 3    Integer,
 4    Text,
 5    ForeignKey,
 6    )
 7
 8from .sqltraversal import Node
 9
10
11class Folder(Node):
12    __tablename__ = 'folder'
13    id = Column(Integer, ForeignKey('node.id'), primary_key=True)
14    title = Column(Text)
15
16
17class Document(Node):
18    __tablename__ = 'document'
19    id = Column(Integer, ForeignKey('node.id'), primary_key=True)
20    title = Column(Text)

  5. Our sqladdcontent/tutorial/views.py is almost unchanged from the version in the 5: Adding Resources To Hierarchies step:

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

  6. Our templates are all unchanged from 5: Adding Resources To Hierarchies. Let's bring them back by copying them from the addcontent/tutorial/templates directory to sqladdcontent/tutorial/templates/. Make a re-usable snippet in sqladdcontent/tutorial/templates/addform.jinja2 for adding content:

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

  7. Create this snippet in sqladdcontent/tutorial/templates/root.jinja2:

     1{% extends "templates/layout.jinja2" %}
 2{% block content %}
 3
 4    <h2>{{ context.title }}</h2>
 5    <p>The root might have some other text.</p>
 6    {% include "templates/contents.jinja2" %}
 7
 8    {% include "templates/addform.jinja2" %}
 9
10{% endblock content %}

  8. Add a view template for folder at sqladdcontent/tutorial/templates/folder.jinja2:

    1{% extends "templates/layout.jinja2" %}
2{% block content %}
3
4    <h2>{{ context.title }}</h2>
5    {% include "templates/contents.jinja2" %}
6
7    {% include "templates/addform.jinja2" %}
8
9{% endblock content %}

  9. Add a view template for document at sqladdcontent/tutorial/templates/document.jinja2:

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

  10. Add a view template for contents at sqladdcontent/tutorial/templates/contents.jinja2:

    1<h4>Contents</h4>
2<ul>
3    {% for child in context.values() %}
4        <li>
5            <a href="{{ request.resource_url(child) }}">{{ child.title }}</a>
6        </li>
7    {% endfor %}
8</ul>

  11. Update breadcrumbs at sqladdcontent/tutorial/templates/breadcrumbs.jinja2:

    1{% for p in view.parents %}
2<span>
3  <a href="{{ request.resource_url(p) }}">{{ p.title }}</a> >>
4</span>
5{% endfor %}

  12. Modify the initialize_db.py script.

     1import os
 2import sys
 3import transaction
 4
 5from sqlalchemy import engine_from_config
 6
 7from pyramid.paster import (
 8    get_appsettings,
 9    setup_logging,
10    )
11
12from .sqltraversal import (
13    DBSession,
14    Node,
15    Base,
16    )
17
18from .models import (
19    Document,
20    Folder,
21    )
22
23
24def usage(argv):
25    cmd = os.path.basename(argv[0])
26    print('usage: %s <config_uri>\n'
27          '(example: "%s development.ini")' % (cmd, cmd))
28    sys.exit(1)
29
30
31def main(argv=sys.argv):
32    if len(argv) != 2:
33        usage(argv)
34    config_uri = argv[1]
35    setup_logging(config_uri)
36    settings = get_appsettings(config_uri)
37    engine = engine_from_config(settings, 'sqlalchemy.')
38    DBSession.configure(bind=engine)
39    Base.metadata.create_all(engine)
40
41    with transaction.manager:
42        root = Folder(name='', title='My SQLTraversal Root')
43        DBSession.add(root)
44        f1 = root['f1'] = Folder(title='Folder 1')
45        f1['da'] = Document(title='Document A')

  13. Update the database by running the script.

    $ $VENV/bin/initialize_tutorial_db development.ini

  14. Run your Pyramid application with:

    $ $VENV/bin/pserve development.ini --reload

  15. 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.