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:

     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. Update the import in __init__.py to use the new module we just created.

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
    from pyramid.config import Configurator

from sqlalchemy import engine_from_config

from .sqltraversal import (
    DBSession,
    Base,
    root_factory,
    )


def main(global_config, **settings):
    engine = engine_from_config(settings, 'sqlalchemy.')
    DBSession.configure(bind=engine)
    Base.metadata.bind = engine

    config = Configurator(settings=settings,
                          root_factory=root_factory)
    config.include('pyramid_jinja2')
    config.scan('.views')
    return config.make_wsgi_app()

  4. 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)

  5. Our sqladdcontent/tutorial/views.py is almost unchanged from the version in the 5: Adding Resources To Hierarchies 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)

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

  7. Create this snippet in 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 %}

  8. Add 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 %}

  9. Add 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 %}

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

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

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

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

  12. Modify the initialize_db.py script.

     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
    import os
import sys
import transaction

from sqlalchemy import engine_from_config

from pyramid.paster import (
    get_appsettings,
    setup_logging,
    )

from .sqltraversal import (
    DBSession,
    Node,
    Base,
    )

from .models import (
    Document,
    Folder,
    )


def usage(argv):
    cmd = os.path.basename(argv[0])
    print('usage: %s <config_uri>\n'
          '(example: "%s development.ini")' % (cmd, cmd))
    sys.exit(1)


def main(argv=sys.argv):
    if len(argv) != 2:
        usage(argv)
    config_uri = argv[1]
    setup_logging(config_uri)
    settings = get_appsettings(config_uri)
    engine = engine_from_config(settings, 'sqlalchemy.')
    DBSession.configure(bind=engine)
    Base.metadata.create_all(engine)

    with transaction.manager:
        root = Folder(name='', title='My SQLTraversal Root')
        DBSession.add(root)
        f1 = root['f1'] = Folder(title='Folder 1')
        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.