Wiki

Clone wiki

rum / AdvancedDataMapping

In the following, I will show the use of server side stored procedures together mapping composite in SQLAlchemy.

The presented appplication is about occasions, persons, and participations of these persons on these occasions. Usually, it is quite nice to get some statistical overview about the size of the occasions, how may persons will come definitely come, how many participations were canceled.

Before I begin, I show, how it should look like:

Screenshot

Disclaimer: These graphics doesn't contain any real data: Both the numbers and the labels are just examples.

As can be seen, the graphic represents some database report you typically wouldn't expect in classical CRUD applications. However, it can be seen, that using the RUM framework, it can be implemented and the result will be a very rich site:

  • it can be queried
  • it can be sorted as all other index views by clicking on the head of some column
  • it is deeply linked with the other resources, exposed by RUM. This uses the routes we have set up for each resource
  • the widgets used in this report, are reused, when showing an occasion
  • it uses the RUM policy
  • resource/row level: automatically
  • column level: explicitely when generating the view
  • just a minimum of html code
  • the numbers are generated by the database
  • minimal amount of traffic between database and application
  • optimized queries using indices
  • it is even possible to sort the rows for larger result sets by one of these properties

First, we introduce some server side stored procedures:

CREATE FUNCTION occ_number_of_xxx_invitations(integer, integer) RETURNS bigint
    LANGUAGE sql
    AS $_$
    select count(1) from participation where participation_status_id=$2 and occasion_id=$1;
$_$;
CREATE FUNCTION occ_number_of_xxx_invitations_companions(integer, integer) RETURNS bigint
    LANGUAGE sql STABLE
    AS $_$
    select coalesce(sum(number_of_companions), 0) from participation where participation_status_id=$2 and occasion_id=$1;
$_$;

Then we introduce some composite class, which will give statistics about the number of participations of invited persons and about the number of their companions.

class NumberOfParticipations(object):
    def __init__(self, participations, companions):
        self.participations = participations
        self.companions = companions
    def __composite_values__(self):
        return self.participations, self.companions
    def __set_composite_values__(self, participations, companions):
        self.participations = participations
        self.companions = companions
    def __eq__(self, other):
        return other is not None and\
            self.participations == other.participations and\
            self.companions == other.companions
    def __unicode__(self):
        if self.companions > 0:
            return "%s + %s" % (self.participations, self.companions)
        else:
            return unicode(self.participations)

We continue by writing a function, which construct such properties for our database. We need this property several times for different participation stati. This participation stati are identified by some non numeric code, which still exists for legacy reasons in the database.

from sqlalchemy.orm import composite
def number_of_invitations_property(old_code):
        new_id=DBSession.execute(
            select([t_participation_status.c.participation_status_id],
                t_participation_status.c.old_code==old_code)
        ).scalar()
        assert new_id is not None, repr(old_code)
        def code_to_name(code):
            if code==' ':
                return 'unkown'
            else:
                return code
        expr=func.occ_number_of_xxx_invitations(t_occasion.c.occasion_id, new_id)
        expr2=func.occ_number_of_xxx_invitations_companions(t_occasion.c.occasion_id, new_id)
        res=column_property(
            expr.label(
                "number_of_invitations_"+code_to_name(old_code)))
        res=composite(
            NumberOfParticipations,
            expr.label(
                "number_of_invitations_"+code_to_name(old_code)),
            expr2.label(
                "number_of_invitations_"+code_to_name(old_code)+"_companions")
            
            )
        res.new_id=new_id
        res.old_code=old_code
        return res

This construction not only constructs this composite properties, but also attaches some meta information like the participation_status_id to them. We will reuse that later.

Next we map several properties of our class using that function:

mapper(Occasion, t_occasion, polymorphic_on=t_occasion.c.discriminator, properties=dict(
        ....
        participations=relation(Participation, passive_deletes=True, viewonly=True),
        organizers=relation(Person, secondary=t_participation, primaryjoin=
            and_(t_occasion.c.occasion_id==t_participation.c.occasion_id, t_participation.c.organizer_status_id!=None),
            secondaryjoin=t_participation.c.person_id==t_person.c.person_id, viewonly=True),
        number_of_accepted_invitations = number_of_invitations_property('Z'),
        number_of_just_sent_invitations = number_of_invitations_property('E'),
        number_of_remindered_invitations = number_of_invitations_property('R'),
        number_of_invitations_with_unknown_status = number_of_invitations_property(' '),
        number_of_cancelations = number_of_invitations_property('A'),
    ))

We make sure, that RUM constructs the right fields:

@get_fieldfactory.when("isinstance(self, SAFieldFactory) and "
    "isinstance(attr, ColumnProperty) and "
    "attr.key.find('number')>=0"
)
def _field_factory_number_of_properties(self, resource, attr, args):
    args.update(
        read_only = True,
        sortable = True
    )
    return fields.Field

The composite is sorted lexicographically in SQL.

We would like to display each of this pair of numbers in a special widget, showing something like "participations + companions". Of course, if the user click on the number of participations, he will get listed all numbers of participations for that occasion with the corresponding status. If he clicks on the number of companions, he will get the subset of participation with companying persons.

We realize that using a toscawidget:

class ParticipationStatusLink(Widget):
    params = ['participation_status_id', 'field']
    template = "genshi:owconf.templates.participation_status_link"
    css = [CSSLink(modname = "owconf", filename='public/css/statuslink.css')]
    def update_params(self, d):
        super(ParticipationStatusLink, self).update_params(d)
        participation_status_id=getattr(Occasion, d.field.name).property.new_id
        status_query=Query(
            rum_eq('participation_status_id', participation_status_id)
        )
        status_query_companions=Query(
            rum_and([
            rum_eq('participation_status_id', participation_status_id),
            rum_greater('number_of_companions', 0)
            ])
        )
        occasion = d.value
        statistics = getattr(d.value, d.field.name)
        d.number_of_participations = statistics.participations
        d.number_of_companions = statistics.companions
        d.link_participations=app.url_for(resource=Participation, 
            parent_obj=occasion, 
            remote_name='participations',
            _memory=False,**status_query.as_flat_dict())
        d.link_companions=app.url_for(resource=Participation, 
            parent_obj=occasion, 
            remote_name='participations',
            _memory=False,**status_query_companions.as_flat_dict())

As you can see, we construct the links for several queries. We'll pass these links to the template:

<span xmlns="http://www.w3.org/1999/xhtml"
	  xmlns:py="http://genshi.edgewall.org/"
 	  class="${css_class}">
      <a href="${link_participations}" class="participations">${number_of_participations}</a>
      <a href="${link_companions}" class="companions" py:if="number_of_companions>0">+${number_of_companions}</a>
</span>

We put some colors into the widget.

#rum-content .participationstatuslink a.participations{
    color:#336699;
}
#rum-content .participationstatuslink a.companions{
    color:#FF3333;
}

We register the widget as default show widget for the corresponding properties

@get_view.when("isinstance(self, WidgetFactory) "
     "and isinstance(attr, fields.Field) and attr.name.startswith('number_of_') "
     "and action in ['inline_show']"
)
def _get_number_of_show_widget(self, resource, 
    parent, remote_name, attr, action, args):
    args['field']=attr  
    return ParticipationStatusLink

We build a special controller, inheriting form RUMs CRUDController, but having an action more.

class OccasionController(CRUDController):    
    @formats('html')
    @resource_action('collection', 'GET')
    def participation_index(self, resource):
        query = self.repository.make_query(self.request.GET)
        query = self.app.policy.filter(resource, query)
        if query:
            if self.get_format(self.routes)!='csv':
                if query.limit is None:
                    
                    query = query.clone(
                        limit=self.default_limit(resource)
                        #
                        )
                elif query.limit > self.app.config.get('max_page_size', 100):
                    raise HTTPBadRequest(
                        _(u"Too many results per page requested")
                        ).exception
        items = self.repository.select(query)
        return {
            'items': items,
            'query': query,
            }

for cls in [Occasion, Workshop, RipGroup]:
    ControllerFactory.register(OccasionController, cls)

This works just like the usual index action (we should maybe extract that logic from index).

Finally, we set up a view, containing a table, that will just display these numbers:

@get_view.when("isinstance(self, WidgetFactory) and "
    "attr is None and action=='participation_index' and "
    "issubclass(resource, Occasion)"
)
def _get_grid_for_participation_index(self, resource, parent, remote_name, attr, action,
                        args):
    occ_properties=[p for p in dir(Occasion) if p.startswith('number_of')]
    occ_properties=[getattr(Occasion, p) for p in occ_properties]
    occ_properties=[p.property for p in occ_properties if hasattr(p, 'property')]
    occ_properties=[p for p in occ_properties if isinstance(p, ColumnProperty)]
    occ_properties=[p for p in occ_properties if hasattr(p, "old_code")]
    occ_properties=dict([(p.key, p.old_code) for p in occ_properties])
    fields_with_old_codes=["number_of_accepted_invitations",
                           "number_of_just_sent_invitations",
                           "number_of_remindered_invitations",
                           "number_of_invitations_with_unknown_status",
                           "number_of_cancelations"]

    fields_with_old_codes=[(f, occ_properties[f]) for f in fields_with_old_codes]
    
    
    allowed_fields=self._allowed_fields_for_resource(resource, action)
    allowed_fields=dict([(f.name, f) for f in allowed_fields])
    
    widgets=[(allowed_fields.get(attr, None), self(resource, parent, remote_name, attr, 'inline_index'))
        for attr in ['code', 'title']]


    widgets=[(f,w) for (f,w) in widgets if f is not None and w is not None]
    for (f_name, status) in fields_with_old_codes:
        f=allowed_fields.get(f_name, None)
        if not f is None:
            widgets.append((f, ParticipationStatusLink(field=f)))

    fields=[to_column(f, w) for (f, w) in widgets]
    args['fields']=fields
    return RumDataGrid

Updated