Views

Traditionally, in database land, views are queries defined at the database level that act like read-only tables. They allow reuse of common queries across an application, and are very suitable for reporting.

Using SQLAlchemy this traditional approach can be used, but a more dynamic approach is possible as well. We can map arbitrary queries to an object, and then visualize these objects with Camelot.

The model to start from

In the example movie project, we can take three parts of the model : Person, Movie and VisitorReport:

class Person( Party ):
    """Person represents natural persons
    """
    using_options( tablename = 'person' )
    party_id = Field( camelot.types.PrimaryKey(),
                      ForeignKey('party.id'), 
                      primary_key = True )
    __mapper_args__ = {'polymorphic_identity': u'person'}
    first_name = Field( Unicode( 40 ), required = True )
    last_name = Field( Unicode( 40 ), required = True )

There is a relation between Person and Movie through the director attribute:

class Movie( Entity ):

    __tablename__ = 'movies'
    
    title = Column( sqlalchemy.types.Unicode(60), nullable = False )
    short_description = Column( sqlalchemy.types.Unicode(512) )
    releasedate = Column( sqlalchemy.types.Date )
    genre = Column( sqlalchemy.types.Unicode(15) )
    rating = Column( camelot.types.Rating() )
    #
    # All relation types are covered with their own editor
    #
    director = ManyToOne('Person')
    cast = OneToMany('Cast')
    visitor_reports = OneToMany('VisitorReport', cascade='delete')
    tags = ManyToMany('Tag',
                      tablename = 'tags_movies__movies_tags',
                      local_colname = 'tags_id',
                      remote_colname = 'movies_id' )

And a relation between Movie and VisitorReport:

class VisitorReport(Entity):
    
    __tablename__ = 'visitor_report'
    
    date = Column( sqlalchemy.types.Date, 
                   nullable = False, 
                   default = datetime.date.today )
    visitors = Column( sqlalchemy.types.Integer, 
                       nullable = False, 
                       default = 0 )
    movie = ManyToOne( 'Movie', required = True )

Definition of the view

Suppose, we now want to display a table with the total numbers of visitors for all movies of a director.

We first define a plain old Python class that represents the expected results :

class VisitorsPerDirector(object):
    
    class Admin(EntityAdmin):
        verbose_name = _('Visitors per director')
        list_display = table.Table( [ table.ColumnGroup( _('Name and Visitors'), ['first_name', 'last_name', 'visitors'] ),
                                      table.ColumnGroup( _('Official'), ['birthdate', 'social_security_number', 'passport_number'] ) ]
                                    )
# end column group

Then define a function that maps the query that calculates those results to the plain old Python object :

def setup_views():
    from sqlalchemy.sql import select, func, and_
    from sqlalchemy.orm import mapper
 
    from camelot.model.party import Person
    from camelot_example.model import Movie, VisitorReport
        
    s = select([Person.party_id,
                Person.first_name.label('first_name'),
                Person.last_name.label('last_name'),
                Person.birthdate.label('birthdate'),
                Person.social_security_number.label('social_security_number'),
                Person.passport_number.label('passport_number'),
                func.sum( VisitorReport.visitors ).label('visitors'),],
                whereclause = and_( Person.party_id == Movie.director_party_id,
                                    Movie.id == VisitorReport.movie_id),
                group_by = [ Person.party_id, 
                             Person.first_name, 
                             Person.last_name,
                             Person.birthdate,
                             Person.social_security_number,
                             Person.passport_number, ] )
                            
    s=s.alias('visitors_per_director')
    
    mapper( VisitorsPerDirector, s, always_refresh=True )

Put all this in a file called view.py

Put into action

Then make sure the plain old Python object is mapped to the query, just after the Elixir model has been setup, by modifying the setup_model function in settings.py:

    def setup_model():
        from sqlalchemy.orm import configure_mappers
        from camelot.core.sql import metadata
        metadata.bind = settings.ENGINE()
        import camelot.model.party
        import camelot.model.authentication
        import camelot.model.i18n
        import camelot.model.fixture
        import camelot.model.memento
        import camelot.model.batch_job
        import camelot_example.model
        #
        # create the tables for all models, configure mappers first, to make
        # sure all deferred properties have been handled, as those could
        # create tables or columns
        #
        configure_mappers()
        metadata.create_all()
        from camelot.model.authentication import update_last_login
        #update_last_login()
        # 
        # Load sample data with the fixure mechanism
        #
        from camelot_example.fixtures import load_movie_fixtures
        load_movie_fixtures()
        #
        # setup the views
        #
        from camelot_example.view import setup_views
        setup_views()

And add the plain old Python object to a section in the ApplicationAdmin:

    def get_sections(self):
        
        from camelot.model.batch_job import BatchJob
        from camelot.model.memento import Memento
        from camelot.model.party import ( Person, Organization, 
                                          PartyCategory )
        from camelot.model.i18n import Translation
        from camelot.model.batch_job import BatchJob, BatchJobType
        
        from camelot_example.model import Movie, Tag, VisitorReport
        from camelot_example.view import VisitorsPerDirector
# begin import action
        from camelot_example.importer import ImportCovers
# end import action
        
        return [
# begin section with action
                Section( _('Movies'),
                         self,
                         Icon('tango/22x22/mimetypes/x-office-presentation.png'),
                         items = [ Movie, 
                                   Tag, 
                                   VisitorReport, 
#                                   VisitorsPerDirector,
                                   ImportCovers() ]),
# end section with action
                Section( _('Relation'),
                         self,
                         Icon('tango/22x22/apps/system-users.png'),
                         items = [ Person, 
                                   Organization,
                                   PartyCategory ]),
                Section( _('Configuration'),
                         self,
                         Icon('tango/22x22/categories/preferences-system.png'),
                         items = [ Memento, 
                                   Translation,
                                   BatchJobType,
                                   BatchJob 
                                   ])
                ]