2 new commits in galaxy-central: https://bitbucket.org/galaxy/galaxy-central/commits/9304293c4eb1/ Changeset: 9304293c4eb1 User: jmchilton Date: 2014-06-10 22:01:38 Summary: Add utility for debugging sqlalchemy queries in db_shell.py. Should aid in interactively building up queries. Affected #: 1 file diff -r 24c5a89db2634fcd5339205164f64e3d7a396003 -r 9304293c4eb1af6b0682dc479923adea5180145f scripts/db_shell.py --- a/scripts/db_shell.py +++ b/scripts/db_shell.py @@ -36,3 +36,72 @@ from galaxy.model.mapping import init sa_session = init( '/tmp/', db_url ).context from galaxy.model import * + +# Helper function for debugging sqlalchemy queries... +# http://stackoverflow.com/questions/5631078/sqlalchemy-print-the-actual-query +import decimal +import datetime + + +def printquery(statement, bind=None): + """ + print a query, with values filled in + for debugging purposes *only* + for security, you should always separate queries from their values + please also note that this function is quite slow + """ + import sqlalchemy.orm + if isinstance(statement, sqlalchemy.orm.Query): + if bind is None: + bind = statement.session.get_bind( + statement._mapper_zero_or_none() + ) + statement = statement.statement + elif bind is None: + bind = statement.bind + + dialect = bind.dialect + compiler = statement._compiler(dialect) + + class LiteralCompiler(compiler.__class__): + def visit_bindparam( + self, bindparam, within_columns_clause=False, + literal_binds=False, **kwargs + ): + return super(LiteralCompiler, self).render_literal_bindparam( + bindparam, + within_columns_clause=within_columns_clause, + literal_binds=literal_binds, + **kwargs + ) + + def render_literal_value(self, value, type_): + """Render the value of a bind parameter as a quoted literal. + + This is used for statement sections that do not accept bind paramters + on the target driver/database. + + This should be implemented by subclasses using the quoting services + of the DBAPI. + + """ + if isinstance(value, basestring): + value = value.replace("'", "''") + return "'%s'" % value + elif value is None: + return "NULL" + elif isinstance(value, (float, int, long)): + return repr(value) + elif isinstance(value, decimal.Decimal): + return str(value) + elif isinstance(value, datetime.datetime): + return "TO_DATE('%s','YYYY-MM-DD HH24:MI:SS')" % value.strftime("%Y-%m-%d %H:%M:%S") + + else: + raise NotImplementedError( + "Don't know how to literal-quote value %r" % value + ) + + compiler = LiteralCompiler(dialect, statement) + print compiler.process(statement) + https://bitbucket.org/galaxy/galaxy-central/commits/9a905e98e155/ Changeset: 9a905e98e155 User: jmchilton Date: 2014-06-10 22:01:38 Summary: Allow injection of high-level helper utility into dynamic destination rules. Helper currently has methods methods for determining users recent usage - job count and sum of job runtime - filterable by user_email, job creation and update time, destination id, job states (only 'ok' job state would make sense for runtime - only way Galaxy has that data). Goal here is to allow dynamic destination to assign higher priorities to users who have not used Galaxy recently. Example usage here : https://gist.github.com/jmchilton/a1b4f0bd3ce7189c0caf. Affected #: 2 files diff -r 9304293c4eb1af6b0682dc479923adea5180145f -r 9a905e98e1550314cf821a99c2adc1b00a4eed83 lib/galaxy/jobs/mapper.py --- a/lib/galaxy/jobs/mapper.py +++ b/lib/galaxy/jobs/mapper.py @@ -5,6 +5,7 @@ log = logging.getLogger( __name__ ) import galaxy.jobs.rules +from .rule_helper import RuleHelper DYNAMIC_RUNNER_NAME = "dynamic" DYNAMIC_DESTINATION_ID = "dynamic_legacy_from_url" @@ -56,12 +57,13 @@ def __invoke_expand_function( self, expand_function ): function_arg_names = inspect.getargspec( expand_function ).args - + app = self.job_wrapper.app possible_args = { "job_id" : self.job_wrapper.job_id, "tool" : self.job_wrapper.tool, "tool_id" : self.job_wrapper.tool.id, "job_wrapper" : self.job_wrapper, - "app" : self.job_wrapper.app } + "rule_helper": RuleHelper( app ), + "app" : app } actual_args = {} diff -r 9304293c4eb1af6b0682dc479923adea5180145f -r 9a905e98e1550314cf821a99c2adc1b00a4eed83 lib/galaxy/jobs/rule_helper.py --- /dev/null +++ b/lib/galaxy/jobs/rule_helper.py @@ -0,0 +1,87 @@ +from datetime import datetime + +from sqlalchemy import ( + and_, + func +) + +from galaxy import model + +import logging +log = logging.getLogger( __name__ ) + + +class RuleHelper( object ): + """ Utillity to allow job rules to interface cleanly with the rest of + Galaxy and shield them from low-level details of models, metrics, etc.... + + Currently focus is on figuring out job statistics for a given user, but + could interface with other stuff as well. + """ + + def __init__( self, app ): + self.app = app + + def job_count( + self, + **kwds + ): + query = self.query( model.Job ) + return self._filter_job_query( query, **kwds ).count() + + def sum_job_runtime( + self, + **kwds + ): + # TODO: Consider sum_core_hours or something that scales runtime by + # by calculated cores per job. + query = self.metric_query( + select=func.sum( model.JobMetricNumeric.table.c.metric_value ), + metric_name="runtime_seconds", + plugin="core", + ) + query = query.join( model.Job ) + return float( self._filter_job_query( query, **kwds ).first()[ 0 ] ) + + def metric_query( self, select, metric_name, plugin, numeric=True ): + metric_class = model.JobMetricNumeric if numeric else model.JobMetricText + query = self.query( select ) + query = query.filter( metric_class.table.c.plugin == plugin ) + query = query.filter( metric_class.table.c.metric_name == metric_name ) + return query + + def query( self, select_expression ): + return self.app.model.context.query( select_expression ) + + def _filter_job_query( + self, + query, + for_user_email=None, + for_destination=None, + for_job_states=None, + created_in_last=None, + updated_in_last=None, + ): + query = query.join( model.User ) + if for_user_email is not None: + query = query.filter( model.User.table.c.email == for_user_email ) + + if for_destination is not None: + query = query.filter( model.Job.table.c.destination_id == for_destination ) + + if created_in_last is not None: + end_date = datetime.now() + start_date = end_date - created_in_last + query = query.filter( model.Job.table.c.create_time >= start_date ) + + if updated_in_last is not None: + end_date = datetime.now() + start_date = end_date - updated_in_last + log.info( end_date ) + log.info( start_date ) + query = query.filter( model.Job.table.c.update_time >= start_date ) + + if for_job_states is not None: + query = query.filter( model.Job.table.c.state.in_( for_job_states ) ) + + return query 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.