2 new commits in galaxy-central: https://bitbucket.org/galaxy/galaxy-central/commits/e7f83c2921c8/ Changeset: e7f83c2921c8 User: jmchilton Date: 2015-02-06 02:27:53+00:00 Summary: PEP-8 fixes for reports controllers and app. Affected #: 5 files diff -r 17ed2655b3052c047aba60013e0f9bb173ebee90 -r e7f83c2921c86bedea95425c213e4a467e64f0f3 lib/galaxy/webapps/reports/app.py --- a/lib/galaxy/webapps/reports/app.py +++ b/lib/galaxy/webapps/reports/app.py @@ -1,7 +1,9 @@ -import sys, config +import sys +import config import galaxy.model from galaxy.web import security + class UniverseApplication( object ): """Encapsulates the state of a Universe application""" def __init__( self, **kwargs ): @@ -20,8 +22,9 @@ self.model = galaxy.model.mapping.init( self.config.file_path, db_url, self.config.database_engine_options, - create_tables = True ) + create_tables=True ) # Security helper self.security = security.SecurityHelper( id_secret=self.config.id_secret ) + def shutdown( self ): pass diff -r 17ed2655b3052c047aba60013e0f9bb173ebee90 -r e7f83c2921c86bedea95425c213e4a467e64f0f3 lib/galaxy/webapps/reports/controllers/jobs.py --- a/lib/galaxy/webapps/reports/controllers/jobs.py +++ b/lib/galaxy/webapps/reports/controllers/jobs.py @@ -1,9 +1,8 @@ -import calendar, operator, os, socket +import calendar from datetime import datetime, date, timedelta -from time import mktime, strftime, localtime from galaxy.web.base.controller import BaseUIController, web from galaxy import model, util -from galaxy.web.framework.helpers import time_ago, iff, grids +from galaxy.web.framework.helpers import grids from galaxy.model.orm import and_, not_, or_ import pkg_resources pkg_resources.require( "SQLAlchemy >= 0.4" ) @@ -11,37 +10,53 @@ import logging log = logging.getLogger( __name__ ) + class SpecifiedDateListGrid( grids.Grid ): + class JobIdColumn( grids.IntegerColumn ): + def get_value( self, trans, grid, job ): return job.id + class StateColumn( grids.TextColumn ): + def get_value( self, trans, grid, job ): return '<div class="count-box state-color-%s">%s</div>' % ( job.state, job.state ) + def filter( self, trans, user, query, column_filter ): if column_filter == 'Unfinished': return query.filter( not_( or_( model.Job.table.c.state == model.Job.states.OK, model.Job.table.c.state == model.Job.states.ERROR, model.Job.table.c.state == model.Job.states.DELETED ) ) ) return query + class ToolColumn( grids.TextColumn ): + def get_value( self, trans, grid, job ): return job.tool_id + class CreateTimeColumn( grids.DateTimeColumn ): + def get_value( self, trans, grid, job ): return job.create_time.strftime("%b %d, %Y, %H:%M:%S") + class UserColumn( grids.GridColumn ): + def get_value( self, trans, grid, job ): if job.user: return job.user.email return 'anonymous' + class EmailColumn( grids.GridColumn ): + def filter( self, trans, user, query, column_filter ): if column_filter == 'All': return query return query.filter( and_( model.Job.table.c.user_id == model.User.table.c.id, model.User.table.c.email == column_filter ) ) + class SpecifiedDateColumn( grids.GridColumn ): + def filter( self, trans, user, query, column_filter ): if column_filter == 'All': return query @@ -57,7 +72,7 @@ end_date = start_date + timedelta( days=calendar.monthrange( year, month )[1] ) return query.filter( and_( self.model_class.table.c.create_time >= start_date, - self.model_class.table.c.create_time < end_date ) ) + self.model_class.table.c.create_time < end_date ) ) # Grid definition use_async = False @@ -72,8 +87,8 @@ attach_popup=False, filterable="advanced" ), StateColumn( "State", - key="state", - attach_popup=False ), + key="state", + attach_popup=False ), ToolColumn( "Tool Id", key="tool_id", link=( lambda item: dict( operation="tool_per_month", id=item.id, webapp="reports" ) ), @@ -105,7 +120,7 @@ visible=False, filterable="standard" ) ) standard_filters = [] - default_filter = { 'specified_date' : 'All' } + default_filter = { 'specified_date': 'All' } num_rows_per_page = 50 preserve_state = False use_paging = True @@ -119,7 +134,9 @@ .filter( model.Job.table.c.user_id != monitor_user_id )\ .enable_eagerloads( False ) + class Jobs( BaseUIController ): + """ Class contains functions for querying data requested by user via the webapp. It exposes the functions and responds to requests with the filled .mako templates. @@ -198,15 +215,14 @@ month_label = start_date.strftime( "%B" ) year_label = start_date.strftime( "%Y" ) - month_jobs = sa.select( ( sa.func.date( model.Job.table.c.create_time ).label( 'date' ), - sa.func.count( model.Job.table.c.id ).label( 'total_jobs' ) ), - whereclause=sa.and_( model.Job.table.c.user_id != monitor_user_id, - model.Job.table.c.create_time >= start_date, - model.Job.table.c.create_time < end_date ), - from_obj=[ model.Job.table ], - group_by=[ 'date' ], - order_by=[ sa.desc( 'date' ) ] ) + sa.func.count( model.Job.table.c.id ).label( 'total_jobs' ) ), + whereclause=sa.and_( model.Job.table.c.user_id != monitor_user_id, + model.Job.table.c.create_time >= start_date, + model.Job.table.c.create_time < end_date ), + from_obj=[ model.Job.table ], + group_by=[ 'date' ], + order_by=[ sa.desc( 'date' ) ] ) jobs = [] for row in month_jobs.execute(): @@ -214,7 +230,7 @@ row.date.strftime( "%d" ), row.total_jobs, row.date - ) ) + ) ) return trans.fill_template( '/webapps/reports/jobs_specified_month_all.mako', month_label=month_label, year_label=year_label, @@ -222,6 +238,7 @@ jobs=jobs, is_user_jobs_only=monitor_user_id, message=message ) + @web.expose def specified_month_in_error( self, trans, **kwd ): """ @@ -244,14 +261,14 @@ year_label = start_date.strftime( "%Y" ) month_jobs_in_error = sa.select( ( sa.func.date( model.Job.table.c.create_time ).label( 'date' ), - sa.func.count( model.Job.table.c.id ).label( 'total_jobs' ) ), - whereclause=sa.and_( model.Job.table.c.user_id != monitor_user_id, - model.Job.table.c.state == 'error', - model.Job.table.c.create_time >= start_date, - model.Job.table.c.create_time < end_date ), - from_obj=[ model.Job.table ], - group_by=[ 'date' ], - order_by=[ sa.desc( 'date' ) ] ) + sa.func.count( model.Job.table.c.id ).label( 'total_jobs' ) ), + whereclause=sa.and_( model.Job.table.c.user_id != monitor_user_id, + model.Job.table.c.state == 'error', + model.Job.table.c.create_time >= start_date, + model.Job.table.c.create_time < end_date ), + from_obj=[ model.Job.table ], + group_by=[ 'date' ], + order_by=[ sa.desc( 'date' ) ] ) jobs = [] for row in month_jobs_in_error.execute(): @@ -266,6 +283,7 @@ jobs=jobs, message=message, is_user_jobs_only=monitor_user_id ) + @web.expose def per_month_all( self, trans, **kwd ): """ @@ -280,25 +298,26 @@ monitor_user_id = get_monitor_id( trans, monitor_email ) jobs_by_month = sa.select( ( sa.func.date_trunc( 'month', model.Job.table.c.create_time ).label( 'date' ), - sa.func.count( model.Job.table.c.id ).label( 'total_jobs' ) ), - whereclause=model.Job.table.c.user_id != monitor_user_id, - from_obj=[ model.Job.table ], - group_by=[ 'date' ], - order_by=[ sa.desc( 'date' ) ] ) + sa.func.count( model.Job.table.c.id ).label( 'total_jobs' ) ), + whereclause=model.Job.table.c.user_id != monitor_user_id, + from_obj=[ model.Job.table ], + group_by=[ 'date' ], + order_by=[ sa.desc( 'date' ) ] ) jobs = [] for row in jobs_by_month.execute(): - jobs.append( ( - row.date.strftime( "%Y-%m" ), - row.total_jobs, - row.date.strftime( "%B" ), - row.date.strftime( "%y" ) - ) ) + jobs.append( ( + row.date.strftime( "%Y-%m" ), + row.total_jobs, + row.date.strftime( "%B" ), + row.date.strftime( "%y" ) + ) ) return trans.fill_template( '/webapps/reports/jobs_per_month_all.mako', jobs=jobs, is_user_jobs_only=monitor_user_id, message=message ) + @web.expose def per_month_in_error( self, trans, **kwd ): """ @@ -314,11 +333,11 @@ jobs_in_error_by_month = sa.select( ( sa.func.date_trunc( 'month', sa.func.date( model.Job.table.c.create_time ) ).label( 'date' ), sa.func.count( model.Job.table.c.id ).label( 'total_jobs' ) ), - whereclause=sa.and_ ( model.Job.table.c.state == 'error', - model.Job.table.c.user_id != monitor_user_id ), - from_obj=[ model.Job.table ], - group_by=[ sa.func.date_trunc( 'month', sa.func.date( model.Job.table.c.create_time ) ) ], - order_by=[ sa.desc( 'date' ) ] ) + whereclause=sa.and_( model.Job.table.c.state == 'error', + model.Job.table.c.user_id != monitor_user_id ), + from_obj=[ model.Job.table ], + group_by=[ sa.func.date_trunc( 'month', sa.func.date( model.Job.table.c.create_time ) ) ], + order_by=[ sa.desc( 'date' ) ] ) jobs = [] for row in jobs_in_error_by_month.execute(): @@ -339,19 +358,19 @@ jobs = [] jobs_per_user = sa.select( ( model.User.table.c.email.label( 'user_email' ), - sa.func.count( model.Job.table.c.id ).label( 'total_jobs' ) ), - from_obj=[ sa.outerjoin( model.Job.table, model.User.table ) ], - group_by=[ 'user_email' ], - order_by=[ sa.desc( 'total_jobs' ), 'user_email' ] ) + sa.func.count( model.Job.table.c.id ).label( 'total_jobs' ) ), + from_obj=[ sa.outerjoin( model.Job.table, model.User.table ) ], + group_by=[ 'user_email' ], + order_by=[ sa.desc( 'total_jobs' ), 'user_email' ] ) for row in jobs_per_user.execute(): - if ( row.user_email == None ): - jobs.append ( ( 'Anonymous', - row.total_jobs ) ) + if ( row.user_email is None ): + jobs.append( ( 'Anonymous', + row.total_jobs ) ) elif ( row.user_email == monitor_email ): continue else: jobs.append( ( row.user_email, - row.total_jobs ) ) + row.total_jobs ) ) return trans.fill_template( '/webapps/reports/jobs_per_user.mako', jobs=jobs, message=message ) @@ -364,8 +383,8 @@ q = sa.select( ( sa.func.date_trunc( 'month', sa.func.date( model.Job.table.c.create_time ) ).label( 'date' ), sa.func.count( model.Job.table.c.id ).label( 'total_jobs' ) ), whereclause=sa.and_( model.Job.table.c.session_id == model.GalaxySession.table.c.id, - model.GalaxySession.table.c.user_id == model.User.table.c.id, - model.User.table.c.email == email + model.GalaxySession.table.c.user_id == model.User.table.c.id, + model.User.table.c.email == email ), from_obj=[ sa.join( model.Job.table, model.User.table ) ], group_by=[ sa.func.date_trunc( 'month', sa.func.date( model.Job.table.c.create_time ) ) ], @@ -420,7 +439,7 @@ q = sa.select( ( sa.func.date_trunc( 'month', sa.func.date( model.Job.table.c.create_time ) ).label( 'date' ), sa.func.count( model.Job.table.c.id ).label( 'total_jobs' ) ), whereclause=sa.and_( model.Job.table.c.tool_id == tool_id, - model.Job.table.c.user_id != monitor_user_id ), + model.Job.table.c.user_id != monitor_user_id ), from_obj=[ model.Job.table ], group_by=[ sa.func.date_trunc( 'month', sa.func.date( model.Job.table.c.create_time ) ) ], order_by=[ sa.desc( 'date' ) ] ) @@ -439,7 +458,6 @@ @web.expose def job_info( self, trans, **kwd ): - params = util.Params( kwd ) message = '' job = trans.sa_session.query( model.Job ) \ .get( trans.security.decode_id( kwd.get( 'id', '' ) ) ) @@ -447,19 +465,21 @@ job=job, message=message ) -## ---- Utility methods ------------------------------------------------------- +# ---- Utility methods ------------------------------------------------------- + def get_job( trans, id ): return trans.sa_session.query( trans.model.Job ).get( trans.security.decode_id( id ) ) + def get_monitor_id( trans, monitor_email ): """ A convenience method to obtain the monitor job id. """ monitor_user_id = None monitor_row = trans.sa_session.query( trans.model.User.table.c.id ) \ - .filter( trans.model.User.table.c.email == monitor_email ) \ - .first() + .filter( trans.model.User.table.c.email == monitor_email ) \ + .first() if monitor_row is not None: monitor_user_id = monitor_row[0] return monitor_user_id diff -r 17ed2655b3052c047aba60013e0f9bb173ebee90 -r e7f83c2921c86bedea95425c213e4a467e64f0f3 lib/galaxy/webapps/reports/controllers/sample_tracking.py --- a/lib/galaxy/webapps/reports/controllers/sample_tracking.py +++ b/lib/galaxy/webapps/reports/controllers/sample_tracking.py @@ -1,35 +1,45 @@ -import calendar, operator, os, socket -from datetime import datetime, date, timedelta -from time import mktime, strftime, localtime +import calendar +from datetime import date, timedelta from galaxy.web.base.controller import BaseUIController, web from galaxy import model, util -from galaxy.web.framework.helpers import time_ago, iff, grids -from galaxy.model.orm import and_, not_, or_ +from galaxy.web.framework.helpers import grids +from galaxy.model.orm import and_ import pkg_resources pkg_resources.require( "SQLAlchemy >= 0.4" ) import sqlalchemy as sa import logging log = logging.getLogger( __name__ ) + class SpecifiedDateListGrid( grids.Grid ): + class RequestNameColumn( grids.TextColumn ): + def get_value( self, trans, grid, request ): return request.name + class CreateTimeColumn( grids.DateTimeColumn ): + def get_value( self, trans, grid, request ): return request.create_time + class UserColumn( grids.TextColumn ): + def get_value( self, trans, grid, request ): if request.user: return request.user.email return 'unknown' + class EmailColumn( grids.GridColumn ): + def filter( self, trans, user, query, column_filter ): if column_filter == 'All': return query return query.filter( and_( model.Request.table.c.user_id == model.User.table.c.id, model.User.table.c.email == column_filter ) ) + class SpecifiedDateColumn( grids.GridColumn ): + def filter( self, trans, user, query, column_filter ): if column_filter == 'All': return query @@ -54,14 +64,13 @@ use_async = False model_class = model.Request title = "Sequencing Requests" - template='/webapps/reports/grid.mako' + template = '/webapps/reports/grid.mako' default_sort_key = "name" columns = [ RequestNameColumn( "Name", - key="name", - #link=( lambda item: dict( operation="workflow_info", id=item.id, webapp="reports" ) ), - attach_popup=False, - filterable="advanced" ), + key="name", + attach_popup=False, + filterable="advanced" ), CreateTimeColumn( "Creation Time", key="create_time", attach_popup=False ), @@ -85,15 +94,17 @@ visible=False, filterable="standard" ) ) standard_filters = [] - default_filter = { 'specified_date' : 'All' } + default_filter = { 'specified_date': 'All' } num_rows_per_page = 50 preserve_state = False use_paging = True + def build_initial_query( self, trans, **kwd ): return trans.sa_session.query( self.model_class ) \ .join( model.User ) \ .enable_eagerloads( False ) + class SampleTracking( BaseUIController ): specified_date_list_grid = SpecifiedDateListGrid() @@ -124,19 +135,19 @@ if request.user: kwd[ 'email' ] = request.user.email else: - kwd[ 'email' ] = None # For anonymous users ( shouldn't happen with requests ) + kwd[ 'email' ] = None # For anonymous users ( shouldn't happen with requests ) return trans.response.send_redirect( web.url_for( controller='sample_tracking', action='user_per_month', **kwd ) ) return self.specified_date_list_grid( trans, **kwd ) + @web.expose def per_month_all( self, trans, **kwd ): - params = util.Params( kwd ) message = '' - q = sa.select( ( sa.func.date_trunc( 'month', sa.func.date( model.Request.table.c.create_time ) ).label( 'date' ),sa.func.count( model.Request.table.c.id ).label( 'total' ) ), - from_obj = [ sa.outerjoin( model.Request.table, model.User.table ) ], - group_by = [ sa.func.date_trunc( 'month', sa.func.date( model.Request.table.c.create_time ) ) ], - order_by = [ sa.desc( 'date' ) ] ) + q = sa.select( ( sa.func.date_trunc( 'month', sa.func.date( model.Request.table.c.create_time ) ).label( 'date' ), sa.func.count( model.Request.table.c.id ).label( 'total' ) ), + from_obj=[ sa.outerjoin( model.Request.table, model.User.table ) ], + group_by=[ sa.func.date_trunc( 'month', sa.func.date( model.Request.table.c.create_time ) ) ], + order_by=[ sa.desc( 'date' ) ] ) requests = [] for row in q.execute(): requests.append( ( row.date.strftime( "%Y-%m" ), @@ -146,20 +157,21 @@ return trans.fill_template( '/webapps/reports/requests_per_month_all.mako', requests=requests, message=message ) + @web.expose def per_user( self, trans, **kwd ): - params = util.Params( kwd ) message = '' requests = [] q = sa.select( ( model.User.table.c.email.label( 'user_email' ), sa.func.count( model.Request.table.c.id ).label( 'total' ) ), - from_obj = [ sa.outerjoin( model.Request.table, model.User.table ) ], - group_by = [ 'user_email' ], - order_by = [ sa.desc( 'total' ), 'user_email' ] ) + from_obj=[ sa.outerjoin( model.Request.table, model.User.table ) ], + group_by=[ 'user_email' ], + order_by=[ sa.desc( 'total' ), 'user_email' ] ) for row in q.execute(): requests.append( ( row.user_email, row.total ) ) return trans.fill_template( '/webapps/reports/requests_per_user.mako', requests=requests, message=message ) + @web.expose def user_per_month( self, trans, **kwd ): params = util.Params( kwd ) @@ -168,10 +180,10 @@ user_id = trans.security.decode_id( params.get( 'id', '' ) ) q = sa.select( ( sa.func.date_trunc( 'month', sa.func.date( model.Request.table.c.create_time ) ).label( 'date' ), sa.func.count( model.Request.table.c.id ).label( 'total' ) ), - whereclause = model.Request.table.c.user_id == user_id, - from_obj = [ model.Request.table ], - group_by = [ sa.func.date_trunc( 'month', sa.func.date( model.Request.table.c.create_time ) ) ], - order_by = [ sa.desc( 'date' ) ] ) + whereclause=model.Request.table.c.user_id == user_id, + from_obj=[ model.Request.table ], + group_by=[ sa.func.date_trunc( 'month', sa.func.date( model.Request.table.c.create_time ) ) ], + order_by=[ sa.desc( 'date' ) ] ) requests = [] for row in q.execute(): requests.append( ( row.date.strftime( "%Y-%m" ), @@ -183,7 +195,8 @@ requests=requests, message=message ) -## ---- Utility methods ------------------------------------------------------- +# ---- Utility methods ------------------------------------------------------- + def get_request( trans, id ): return trans.sa_session.query( trans.model.Workflow ).get( trans.security.decode_id( id ) ) diff -r 17ed2655b3052c047aba60013e0f9bb173ebee90 -r e7f83c2921c86bedea95425c213e4a467e64f0f3 lib/galaxy/webapps/reports/controllers/users.py --- a/lib/galaxy/webapps/reports/controllers/users.py +++ b/lib/galaxy/webapps/reports/controllers/users.py @@ -1,10 +1,9 @@ from datetime import datetime, date, timedelta -from time import strftime -import calendar, operator +import calendar +import operator from galaxy.web.base.controller import BaseUIController, web import galaxy.model from galaxy import util -from galaxy.model.orm import and_, not_, or_ import pkg_resources pkg_resources.require( "SQLAlchemy >= 0.4" ) import sqlalchemy as sa @@ -13,20 +12,23 @@ log = logging.getLogger( __name__ ) + class Users( BaseUIController ): + @web.expose def registered_users( self, trans, **kwd ): message = escape( util.restore_text( kwd.get( 'message', '' ) ) ) num_users = trans.sa_session.query( galaxy.model.User ).count() return trans.fill_template( '/webapps/reports/registered_users.mako', num_users=num_users, message=message ) + @web.expose def registered_users_per_month( self, trans, **kwd ): message = escape( util.restore_text( kwd.get( 'message', '' ) ) ) q = sa.select( ( sa.func.date_trunc( 'month', sa.func.date( galaxy.model.User.table.c.create_time ) ).label( 'date' ), sa.func.count( galaxy.model.User.table.c.id ).label( 'num_users' ) ), - from_obj = [ galaxy.model.User.table ], - group_by = [ sa.func.date_trunc( 'month', sa.func.date( galaxy.model.User.table.c.create_time ) ) ], - order_by = [ sa.desc( 'date' ) ] ) + from_obj=[ galaxy.model.User.table ], + group_by=[ sa.func.date_trunc( 'month', sa.func.date( galaxy.model.User.table.c.create_time ) ) ], + order_by=[ sa.desc( 'date' ) ] ) users = [] for row in q.execute(): users.append( ( row.date.strftime( "%Y-%m" ), @@ -36,6 +38,7 @@ return trans.fill_template( '/webapps/reports/registered_users_per_month.mako', users=users, message=message ) + @web.expose def specified_month( self, trans, **kwd ): message = escape( util.restore_text( kwd.get( 'message', '' ) ) ) @@ -49,11 +52,11 @@ year_label = start_date.strftime( "%Y" ) q = sa.select( ( sa.func.date_trunc( 'day', sa.func.date( galaxy.model.User.table.c.create_time ) ).label( 'date' ), sa.func.count( galaxy.model.User.table.c.id ).label( 'num_users' ) ), - whereclause = sa.and_( galaxy.model.User.table.c.create_time >= start_date, - galaxy.model.User.table.c.create_time < end_date ), - from_obj = [ galaxy.model.User.table ], - group_by = [ sa.func.date_trunc( 'day', sa.func.date( galaxy.model.User.table.c.create_time ) ) ], - order_by = [ sa.desc( 'date' ) ] ) + whereclause=sa.and_( galaxy.model.User.table.c.create_time >= start_date, + galaxy.model.User.table.c.create_time < end_date ), + from_obj=[ galaxy.model.User.table ], + group_by=[ sa.func.date_trunc( 'day', sa.func.date( galaxy.model.User.table.c.create_time ) ) ], + order_by=[ sa.desc( 'date' ) ] ) users = [] for row in q.execute(): users.append( ( row.date.strftime( "%Y-%m-%d" ), @@ -66,6 +69,7 @@ month=month, users=users, message=message ) + @web.expose def specified_date( self, trans, **kwd ): message = escape( util.restore_text( kwd.get( 'message', '' ) ) ) @@ -80,10 +84,10 @@ year_label = start_date.strftime( "%Y" ) q = sa.select( ( sa.func.date_trunc( 'day', sa.func.date( galaxy.model.User.table.c.create_time ) ).label( 'date' ), galaxy.model.User.table.c.email ), - whereclause = sa.and_( galaxy.model.User.table.c.create_time >= start_date, - galaxy.model.User.table.c.create_time < end_date ), - from_obj = [ galaxy.model.User.table ], - order_by = [ galaxy.model.User.table.c.email ] ) + whereclause=sa.and_( galaxy.model.User.table.c.create_time >= start_date, + galaxy.model.User.table.c.create_time < end_date ), + from_obj=[ galaxy.model.User.table ], + order_by=[ galaxy.model.User.table.c.email ] ) users = [] for row in q.execute(): users.append( ( row.email ) ) @@ -95,6 +99,7 @@ day_of_month=day_of_month, users=users, message=message ) + @web.expose def last_access_date( self, trans, **kwd ): message = escape( util.restore_text( kwd.get( 'message', '' ) ) ) @@ -102,10 +107,9 @@ if not not_logged_in_for_days: not_logged_in_for_days = 0 cutoff_time = datetime.utcnow() - timedelta( days=int( not_logged_in_for_days ) ) - now = strftime( "%Y-%m-%d %H:%M:%S" ) users = [] for user in trans.sa_session.query( galaxy.model.User ) \ - .filter( galaxy.model.User.table.c.deleted==False ) \ + .filter( galaxy.model.User.table.c.deleted == False ) \ .order_by( galaxy.model.User.table.c.email ): if user.galaxy_sessions: last_galaxy_session = user.galaxy_sessions[ 0 ] diff -r 17ed2655b3052c047aba60013e0f9bb173ebee90 -r e7f83c2921c86bedea95425c213e4a467e64f0f3 lib/galaxy/webapps/reports/controllers/workflows.py --- a/lib/galaxy/webapps/reports/controllers/workflows.py +++ b/lib/galaxy/webapps/reports/controllers/workflows.py @@ -15,20 +15,24 @@ class SpecifiedDateListGrid( grids.Grid ): class WorkflowNameColumn( grids.TextColumn ): + def get_value( self, trans, grid, stored_workflow ): return stored_workflow.name class CreateTimeColumn( grids.DateTimeColumn ): + def get_value( self, trans, grid, stored_workflow ): return stored_workflow.create_time class UserColumn( grids.TextColumn ): + def get_value( self, trans, grid, stored_workflow ): if stored_workflow.user: return stored_workflow.user.email return 'unknown' class EmailColumn( grids.GridColumn ): + def filter( self, trans, user, query, column_filter ): if column_filter == 'All': return query @@ -36,6 +40,7 @@ model.User.table.c.email == column_filter ) ) class SpecifiedDateColumn( grids.GridColumn ): + def filter( self, trans, user, query, column_filter ): if column_filter == 'All': return query @@ -60,12 +65,11 @@ use_async = False model_class = model.StoredWorkflow title = "Workflows" - template='/webapps/reports/grid.mako' + template = '/webapps/reports/grid.mako' default_sort_key = "name" columns = [ WorkflowNameColumn( "Name", key="name", - #link=( lambda item: dict( operation="workflow_info", id=item.id, webapp="reports" ) ), attach_popup=False, filterable="advanced" ), CreateTimeColumn( "Creation Time", @@ -91,10 +95,11 @@ visible=False, filterable="standard" ) ) standard_filters = [] - default_filter = { 'specified_date' : 'All' } + default_filter = { 'specified_date': 'All' } num_rows_per_page = 50 preserve_state = False use_paging = True + def build_initial_query( self, trans, **kwd ): return trans.sa_session.query( self.model_class ) \ .join( model.User ) \ @@ -131,7 +136,7 @@ if workflow.user: kwd[ 'email' ] = workflow.user.email else: - kwd[ 'email' ] = None # For anonymous users ( shouldn't happen with workflows ) + kwd[ 'email' ] = None # For anonymous users ( shouldn't happen with workflows ) return trans.response.send_redirect( web.url_for( controller='workflows', action='user_per_month', **kwd ) ) @@ -140,10 +145,10 @@ @web.expose def per_month_all( self, trans, **kwd ): message = '' - q = sa.select( ( sa.func.date_trunc( 'month', sa.func.date( model.StoredWorkflow.table.c.create_time ) ).label( 'date' ),sa.func.count( model.StoredWorkflow.table.c.id ).label( 'total_workflows' ) ), - from_obj = [ sa.outerjoin( model.StoredWorkflow.table, model.User.table ) ], - group_by = [ sa.func.date_trunc( 'month', sa.func.date( model.StoredWorkflow.table.c.create_time ) ) ], - order_by = [ sa.desc( 'date' ) ] ) + q = sa.select( ( sa.func.date_trunc( 'month', sa.func.date( model.StoredWorkflow.table.c.create_time ) ).label( 'date' ), sa.func.count( model.StoredWorkflow.table.c.id ).label( 'total_workflows' ) ), + from_obj=[ sa.outerjoin( model.StoredWorkflow.table, model.User.table ) ], + group_by=[ sa.func.date_trunc( 'month', sa.func.date( model.StoredWorkflow.table.c.create_time ) ) ], + order_by=[ sa.desc( 'date' ) ] ) workflows = [] for row in q.execute(): workflows.append( ( row.date.strftime( "%Y-%m" ), @@ -160,9 +165,9 @@ workflows = [] q = sa.select( ( model.User.table.c.email.label( 'user_email' ), sa.func.count( model.StoredWorkflow.table.c.id ).label( 'total_workflows' ) ), - from_obj = [ sa.outerjoin( model.StoredWorkflow.table, model.User.table ) ], - group_by = [ 'user_email' ], - order_by = [ sa.desc( 'total_workflows' ), 'user_email' ] ) + from_obj=[ sa.outerjoin( model.StoredWorkflow.table, model.User.table ) ], + group_by=[ 'user_email' ], + order_by=[ sa.desc( 'total_workflows' ), 'user_email' ] ) for row in q.execute(): workflows.append( ( row.user_email, row.total_workflows ) ) @@ -176,10 +181,10 @@ user_id = trans.security.decode_id( params.get( 'id', '' ) ) q = sa.select( ( sa.func.date_trunc( 'month', sa.func.date( model.StoredWorkflow.table.c.create_time ) ).label( 'date' ), sa.func.count( model.StoredWorkflow.table.c.id ).label( 'total_workflows' ) ), - whereclause = model.StoredWorkflow.table.c.user_id == user_id, - from_obj = [ model.StoredWorkflow.table ], - group_by = [ sa.func.date_trunc( 'month', sa.func.date( model.StoredWorkflow.table.c.create_time ) ) ], - order_by = [ sa.desc( 'date' ) ] ) + whereclause=model.StoredWorkflow.table.c.user_id == user_id, + from_obj=[ model.StoredWorkflow.table ], + group_by=[ sa.func.date_trunc( 'month', sa.func.date( model.StoredWorkflow.table.c.create_time ) ) ], + order_by=[ sa.desc( 'date' ) ] ) workflows = [] for row in q.execute(): workflows.append( ( row.date.strftime( "%Y-%m" ), @@ -191,7 +196,8 @@ workflows=workflows, message=message ) -## ---- Utility methods ------------------------------------------------------- +# ---- Utility methods ------------------------------------------------------- + def get_workflow( trans, id ): return trans.sa_session.query( trans.model.Workflow ).get( trans.security.decode_id( id ) ) https://bitbucket.org/galaxy/galaxy-central/commits/ce2776f88e9d/ Changeset: ce2776f88e9d User: jmchilton Date: 2015-02-06 02:27:53+00:00 Summary: Fix reports webapp for MySQL. Big thanks for Eric Enns and Fernandez Edgar for contributing patches to the galaxy-dev list. Affected #: 6 files diff -r e7f83c2921c86bedea95425c213e4a467e64f0f3 -r ce2776f88e9db53886b8879a948b19ce2a513835 lib/galaxy/webapps/reports/app.py --- a/lib/galaxy/webapps/reports/app.py +++ b/lib/galaxy/webapps/reports/app.py @@ -23,6 +23,7 @@ db_url, self.config.database_engine_options, create_tables=True ) + self.targets_mysql = 'mysql' in self.config.database_connection # Security helper self.security = security.SecurityHelper( id_secret=self.config.id_secret ) diff -r e7f83c2921c86bedea95425c213e4a467e64f0f3 -r ce2776f88e9db53886b8879a948b19ce2a513835 lib/galaxy/webapps/reports/controllers/jobs.py --- a/lib/galaxy/webapps/reports/controllers/jobs.py +++ b/lib/galaxy/webapps/reports/controllers/jobs.py @@ -7,6 +7,8 @@ import pkg_resources pkg_resources.require( "SQLAlchemy >= 0.4" ) import sqlalchemy as sa +from galaxy.webapps.reports.controllers.query import ReportQueryBuilder + import logging log = logging.getLogger( __name__ ) @@ -135,7 +137,7 @@ .enable_eagerloads( False ) -class Jobs( BaseUIController ): +class Jobs( BaseUIController, ReportQueryBuilder ): """ Class contains functions for querying data requested by user via the webapp. It exposes the functions and @@ -297,11 +299,11 @@ # In case we don't know which is the monitor user we will query for all jobs monitor_user_id = get_monitor_id( trans, monitor_email ) - jobs_by_month = sa.select( ( sa.func.date_trunc( 'month', model.Job.table.c.create_time ).label( 'date' ), + jobs_by_month = sa.select( ( self.select_month( model.Job.table.c.create_time ).label( 'date' ), sa.func.count( model.Job.table.c.id ).label( 'total_jobs' ) ), whereclause=model.Job.table.c.user_id != monitor_user_id, from_obj=[ model.Job.table ], - group_by=[ 'date' ], + group_by=self.group_by_month( model.Job.table.c.create_time ), order_by=[ sa.desc( 'date' ) ] ) jobs = [] @@ -331,12 +333,12 @@ # In case we don't know which is the monitor user we will query for all jobs monitor_user_id = get_monitor_id( trans, monitor_email ) - jobs_in_error_by_month = sa.select( ( sa.func.date_trunc( 'month', sa.func.date( model.Job.table.c.create_time ) ).label( 'date' ), + jobs_in_error_by_month = sa.select( ( self.select_month( model.Job.table.c.create_time ).label( 'date' ), sa.func.count( model.Job.table.c.id ).label( 'total_jobs' ) ), whereclause=sa.and_( model.Job.table.c.state == 'error', model.Job.table.c.user_id != monitor_user_id ), from_obj=[ model.Job.table ], - group_by=[ sa.func.date_trunc( 'month', sa.func.date( model.Job.table.c.create_time ) ) ], + group_by=self.group_by_month( model.Job.table.c.create_time ), order_by=[ sa.desc( 'date' ) ] ) jobs = [] @@ -380,14 +382,14 @@ params = util.Params( kwd ) message = '' email = util.restore_text( params.get( 'email', '' ) ) - q = sa.select( ( sa.func.date_trunc( 'month', sa.func.date( model.Job.table.c.create_time ) ).label( 'date' ), + q = sa.select( ( self.select_month( model.Job.table.c.create_time ).label( 'date' ), sa.func.count( model.Job.table.c.id ).label( 'total_jobs' ) ), whereclause=sa.and_( model.Job.table.c.session_id == model.GalaxySession.table.c.id, model.GalaxySession.table.c.user_id == model.User.table.c.id, model.User.table.c.email == email ), from_obj=[ sa.join( model.Job.table, model.User.table ) ], - group_by=[ sa.func.date_trunc( 'month', sa.func.date( model.Job.table.c.create_time ) ) ], + group_by=self.group_by_month( model.Job.table.c.create_time ), order_by=[ sa.desc( 'date' ) ] ) jobs = [] for row in q.execute(): @@ -436,12 +438,12 @@ tool_id = params.get( 'tool_id', 'Add a column1' ) specified_date = params.get( 'specified_date', datetime.utcnow().strftime( "%Y-%m-%d" ) ) - q = sa.select( ( sa.func.date_trunc( 'month', sa.func.date( model.Job.table.c.create_time ) ).label( 'date' ), + q = sa.select( ( self.select_month( model.Job.table.c.create_time ).label( 'date' ), sa.func.count( model.Job.table.c.id ).label( 'total_jobs' ) ), whereclause=sa.and_( model.Job.table.c.tool_id == tool_id, model.Job.table.c.user_id != monitor_user_id ), from_obj=[ model.Job.table ], - group_by=[ sa.func.date_trunc( 'month', sa.func.date( model.Job.table.c.create_time ) ) ], + group_by=self.group_by_month( model.Job.table.c.create_time ), order_by=[ sa.desc( 'date' ) ] ) jobs = [] for row in q.execute(): diff -r e7f83c2921c86bedea95425c213e4a467e64f0f3 -r ce2776f88e9db53886b8879a948b19ce2a513835 lib/galaxy/webapps/reports/controllers/query.py --- /dev/null +++ b/lib/galaxy/webapps/reports/controllers/query.py @@ -0,0 +1,32 @@ +""" Mixin to help build advanced queries for reports interface. +""" +import pkg_resources +pkg_resources.require( "SQLAlchemy >= 0.4" ) +import sqlalchemy as sa + + +class ReportQueryBuilder: + + def group_by_month(self, column): + if self.app.targets_mysql: + return [ sa.func.year( column ), sa.func.month( sa.func.date( column ) ) ] + else: + return [ sa.func.date_trunc( 'month', sa.func.date( column ) ) ] + + def select_month(self, column): + if self.app.targets_mysql: + return sa.func.date( column ) + else: + return sa.func.date_trunc( 'month', sa.func.date( column ) ) + + def group_by_day(self, column): + if self.app.targets_mysql: + return [ sa.func.day( sa.func.date( column ) ) ] + else: + return [ sa.func.date_trunc( 'day', sa.func.date( column ) ) ] + + def select_day(self, column): + if self.app.targets_mysql: + return sa.func.date( column ) + else: + return sa.func.date_trunc( 'day', sa.func.date( column ) ) diff -r e7f83c2921c86bedea95425c213e4a467e64f0f3 -r ce2776f88e9db53886b8879a948b19ce2a513835 lib/galaxy/webapps/reports/controllers/sample_tracking.py --- a/lib/galaxy/webapps/reports/controllers/sample_tracking.py +++ b/lib/galaxy/webapps/reports/controllers/sample_tracking.py @@ -7,6 +7,8 @@ import pkg_resources pkg_resources.require( "SQLAlchemy >= 0.4" ) import sqlalchemy as sa +from galaxy.webapps.reports.controllers.query import ReportQueryBuilder + import logging log = logging.getLogger( __name__ ) @@ -105,7 +107,7 @@ .enable_eagerloads( False ) -class SampleTracking( BaseUIController ): +class SampleTracking( BaseUIController, ReportQueryBuilder ): specified_date_list_grid = SpecifiedDateListGrid() @@ -144,9 +146,10 @@ @web.expose def per_month_all( self, trans, **kwd ): message = '' - q = sa.select( ( sa.func.date_trunc( 'month', sa.func.date( model.Request.table.c.create_time ) ).label( 'date' ), sa.func.count( model.Request.table.c.id ).label( 'total' ) ), + q = sa.select( ( self.select_month( model.Request.table.c.create_time ).label( 'date' ), + sa.func.count( model.Request.table.c.id ).label( 'total' ) ), from_obj=[ sa.outerjoin( model.Request.table, model.User.table ) ], - group_by=[ sa.func.date_trunc( 'month', sa.func.date( model.Request.table.c.create_time ) ) ], + group_by=self.group_by_month( model.Request.table.c.create_time ), order_by=[ sa.desc( 'date' ) ] ) requests = [] for row in q.execute(): @@ -178,11 +181,11 @@ message = '' email = util.restore_text( params.get( 'email', '' ) ) user_id = trans.security.decode_id( params.get( 'id', '' ) ) - q = sa.select( ( sa.func.date_trunc( 'month', sa.func.date( model.Request.table.c.create_time ) ).label( 'date' ), + q = sa.select( ( self.select_month( model.Request.table.c.create_time ).label( 'date' ), sa.func.count( model.Request.table.c.id ).label( 'total' ) ), whereclause=model.Request.table.c.user_id == user_id, from_obj=[ model.Request.table ], - group_by=[ sa.func.date_trunc( 'month', sa.func.date( model.Request.table.c.create_time ) ) ], + group_by=self.group_by_month( model.Request.table.c.create_time ), order_by=[ sa.desc( 'date' ) ] ) requests = [] for row in q.execute(): diff -r e7f83c2921c86bedea95425c213e4a467e64f0f3 -r ce2776f88e9db53886b8879a948b19ce2a513835 lib/galaxy/webapps/reports/controllers/users.py --- a/lib/galaxy/webapps/reports/controllers/users.py +++ b/lib/galaxy/webapps/reports/controllers/users.py @@ -7,13 +7,14 @@ import pkg_resources pkg_resources.require( "SQLAlchemy >= 0.4" ) import sqlalchemy as sa +from galaxy.webapps.reports.controllers.query import ReportQueryBuilder import logging from markupsafe import escape log = logging.getLogger( __name__ ) -class Users( BaseUIController ): +class Users( BaseUIController, ReportQueryBuilder ): @web.expose def registered_users( self, trans, **kwd ): @@ -24,10 +25,10 @@ @web.expose def registered_users_per_month( self, trans, **kwd ): message = escape( util.restore_text( kwd.get( 'message', '' ) ) ) - q = sa.select( ( sa.func.date_trunc( 'month', sa.func.date( galaxy.model.User.table.c.create_time ) ).label( 'date' ), + q = sa.select( ( self.select_month( galaxy.model.User.table.c.create_time ).label( 'date' ), sa.func.count( galaxy.model.User.table.c.id ).label( 'num_users' ) ), from_obj=[ galaxy.model.User.table ], - group_by=[ sa.func.date_trunc( 'month', sa.func.date( galaxy.model.User.table.c.create_time ) ) ], + group_by=self.group_by_month( galaxy.model.User.table.c.create_time ), order_by=[ sa.desc( 'date' ) ] ) users = [] for row in q.execute(): @@ -50,12 +51,12 @@ end_date = start_date + timedelta( days=calendar.monthrange( year, month )[1] ) month_label = start_date.strftime( "%B" ) year_label = start_date.strftime( "%Y" ) - q = sa.select( ( sa.func.date_trunc( 'day', sa.func.date( galaxy.model.User.table.c.create_time ) ).label( 'date' ), + q = sa.select( ( self.select_day( galaxy.model.User.table.c.create_time ).label( 'date' ), sa.func.count( galaxy.model.User.table.c.id ).label( 'num_users' ) ), whereclause=sa.and_( galaxy.model.User.table.c.create_time >= start_date, galaxy.model.User.table.c.create_time < end_date ), from_obj=[ galaxy.model.User.table ], - group_by=[ sa.func.date_trunc( 'day', sa.func.date( galaxy.model.User.table.c.create_time ) ) ], + group_by=self.group_by_day( galaxy.model.User.table.c.create_time ), order_by=[ sa.desc( 'date' ) ] ) users = [] for row in q.execute(): @@ -82,7 +83,7 @@ day_label = start_date.strftime( "%A" ) month_label = start_date.strftime( "%B" ) year_label = start_date.strftime( "%Y" ) - q = sa.select( ( sa.func.date_trunc( 'day', sa.func.date( galaxy.model.User.table.c.create_time ) ).label( 'date' ), + q = sa.select( ( self.select_day( galaxy.model.User.table.c.create_time ).label( 'date' ), galaxy.model.User.table.c.email ), whereclause=sa.and_( galaxy.model.User.table.c.create_time >= start_date, galaxy.model.User.table.c.create_time < end_date ), diff -r e7f83c2921c86bedea95425c213e4a467e64f0f3 -r ce2776f88e9db53886b8879a948b19ce2a513835 lib/galaxy/webapps/reports/controllers/workflows.py --- a/lib/galaxy/webapps/reports/controllers/workflows.py +++ b/lib/galaxy/webapps/reports/controllers/workflows.py @@ -7,6 +7,7 @@ from galaxy.web.framework.helpers import grids eggs.require( "SQLAlchemy >= 0.4" ) import sqlalchemy as sa +from galaxy.webapps.reports.controllers.query import ReportQueryBuilder import logging log = logging.getLogger( __name__ ) @@ -106,7 +107,7 @@ .enable_eagerloads( False ) -class Workflows( BaseUIController ): +class Workflows( BaseUIController, ReportQueryBuilder ): specified_date_list_grid = SpecifiedDateListGrid() @@ -145,9 +146,9 @@ @web.expose def per_month_all( self, trans, **kwd ): message = '' - q = sa.select( ( sa.func.date_trunc( 'month', sa.func.date( model.StoredWorkflow.table.c.create_time ) ).label( 'date' ), sa.func.count( model.StoredWorkflow.table.c.id ).label( 'total_workflows' ) ), + q = sa.select( ( self.select_month( model.StoredWorkflow.table.c.create_time ).label( 'date' ), sa.func.count( model.StoredWorkflow.table.c.id ).label( 'total_workflows' ) ), from_obj=[ sa.outerjoin( model.StoredWorkflow.table, model.User.table ) ], - group_by=[ sa.func.date_trunc( 'month', sa.func.date( model.StoredWorkflow.table.c.create_time ) ) ], + group_by=self.group_by_month( model.StoredWorkflow.table.c.create_time ), order_by=[ sa.desc( 'date' ) ] ) workflows = [] for row in q.execute(): @@ -179,11 +180,11 @@ message = '' email = util.restore_text( params.get( 'email', '' ) ) user_id = trans.security.decode_id( params.get( 'id', '' ) ) - q = sa.select( ( sa.func.date_trunc( 'month', sa.func.date( model.StoredWorkflow.table.c.create_time ) ).label( 'date' ), + q = sa.select( ( self.select_month( model.StoredWorkflow.table.c.create_time ).label( 'date' ), sa.func.count( model.StoredWorkflow.table.c.id ).label( 'total_workflows' ) ), whereclause=model.StoredWorkflow.table.c.user_id == user_id, from_obj=[ model.StoredWorkflow.table ], - group_by=[ sa.func.date_trunc( 'month', sa.func.date( model.StoredWorkflow.table.c.create_time ) ) ], + group_by=self.group_by_month( model.StoredWorkflow.table.c.create_time ), order_by=[ sa.desc( 'date' ) ] ) workflows = [] for row in q.execute(): Repository URL: https://bitbucket.org/galaxy/galaxy-central/ -- This is a commit notification from bitbucket.org. You are receiving this because you have the service enabled, addressing the recipient of this email.