also adding missing executable flag to faf-stats-components related to #141. Signed-off-by: Richard Marko <rmarko(a)redhat.com&gt; --- bin/faf-stats-components | 106 +++----------------------------------------- faf.spec.in | 1 + pyfaf/Makefile.am | 1 + pyfaf/__init__.py | 1 + pyfaf/queries.py | 113 +++++++++++++++++++++++++++++++++++++++++++++++ 5 files changed, 123 insertions(+), 99 deletions(-) mode change 100644 => 100755 bin/faf-stats-components create mode 100644 pyfaf/queries.py diff --git a/bin/faf-stats-components b/bin/faf-stats-components old mode 100644 new mode 100755 index 7c81099..47bd70d --- a/bin/faf-stats-components +++ b/bin/faf-stats-components @@ -2,96 +2,9 @@ import datetime import pyfaf -from pyfaf.storage.opsys import (OpSys, - OpSysRelease, - OpSysComponent) - -from pyfaf.storage.report import (Report, - ReportHistoryDaily, ReportHistoryWeekly, - ReportHistoryMonthly) - -from sqlalchemy import func, desc - from django.core.urlresolvers import reverse from django.contrib.sites.models import Site - -def get_history_target(target='daily'): - ''' - Return tuple of `ReportHistory(Daily|Weekly|Monthly)` and - proper date field `ReportHistory(Daily.day|Weekly.week|Monthly.month)` - according to `target` parameter which should be one of - `daily|weekly|monthly` or shortened version `d|w|m`. - ''' - if target == 'd' or target == 'daily': - return (ReportHistoryDaily, ReportHistoryDaily.day) - - if target == 'w' or target == 'weekly': - return (ReportHistoryWeekly, ReportHistoryWeekly.week) - - return (ReportHistoryMonthly, ReportHistoryMonthly.month) - -def query_releases(db, opsys_name, opsys_version=None): - ''' - Return query of `OpSysRelease` records for passed `opsys_name` - and optionaly filtered by `opsys_version`. - ''' - opsysquery = (db.session.query(OpSysRelease) - .join(OpSys) - .filter(OpSys.name == opsys_name)) - - if opsys_version: - opsysquery = opsysquery.filter(OpSysRelease.version == opsys_version) - - return opsysquery - -def query_release_ids(db, opsys_name, opsys_version=None): - ''' - Return list of `OpSysRelease` ids for passed `opsys_name` and optionaly - filtered by `opsys_version`. - ''' - return [opsysrelease.id for opsysrelease in - query_releases(db, opsys_name, opsys_version).all()] - -def query_history_sum(db, opsys_name, opsys_version, history='daily'): - ''' - Return query summing ReportHistory(Daily|Weekly|Monthly) - records for `opsys_name` and `opsys_version`. - ''' - - opsysrelease_ids = query_release_ids(db, opsys_name, opsys_version) - hist_table, hist_field = get_history_target(history) - - hist_sum = ( - db.session.query(func.sum(hist_table.count).label('cnt')) - .filter(hist_table.opsysrelease_id.in_(opsysrelease_ids))) - - return hist_sum - -def query_reports_per_component(db, opsys_name, opsys_version, - history='daily'): - ''' - Return query for `OpSysComponent` and number of reports this - component received. - - It's possible to filter the results by `opsys_name` and - `opsys_version`. - ''' - - opsysrelease_ids = query_release_ids(db, opsys_name, opsys_version) - hist_table, hist_field = get_history_target(history) - - comps = ( - db.session.query(OpSysComponent, - func.sum(hist_table.count).label('cnt')) - .join(Report) - .join(hist_table) - .filter(hist_table.opsysrelease_id.in_(opsysrelease_ids)) - .group_by(OpSysComponent) - .order_by(desc('cnt'))) - - return comps - if __name__ == "__main__": parser = pyfaf.argparse.ArgumentParser(description='Component statistics.') parser.add_argument('--opsys', help='OpSys name', default='Fedora') @@ -110,12 +23,13 @@ if __name__ == "__main__": site = Site.objects.get_current() server_url = 'http://{0}'.format(site.domain) - history = 'daily' - hist_table, hist_field = get_history_target(history) - total = query_history_sum(db, args.opsys, args.version) + hist_table, hist_field = pyfaf.queries.get_history_target(history) + total = pyfaf.queries.query_history_sum(db, args.opsys, args.version) + + comps = pyfaf.queries.query_report_count_per_component( + db, args.opsys, args.version, history) - comps = query_reports_per_component(db, args.opsys, args.version, history) if args.last: now = datetime.datetime.now() since = now - datetime.timedelta(days=int(args.last)) @@ -130,14 +44,8 @@ if __name__ == "__main__": if num >= limit: break - reports = ( - db.session.query(Report, - func.sum(hist_table.count).label('cnt')) - .join(hist_table) - .join(OpSysComponent) - .filter(OpSysComponent.id == comp.id) - .group_by(Report) - .order_by(desc('cnt'))) + reports = pyfaf.queries.query_report_stats_per_component( + db, comp, history) if args.last: reports = reports.filter(hist_field >= since) diff --git a/faf.spec.in b/faf.spec.in index a61b227..6effe3e 100644 --- a/faf.spec.in +++ b/faf.spec.in @@ -275,6 +275,7 @@ fi %{python_sitelib}/pyfaf/config.py* %{python_sitelib}/pyfaf/cluster.py* %{python_sitelib}/pyfaf/__init__.py* +%{python_sitelib}/pyfaf/queries.py* %{python_sitelib}/pyfaf/kb.py* %{python_sitelib}/pyfaf/libsolv.py* %{python_sitelib}/pyfaf/obs.py* diff --git a/pyfaf/Makefile.am b/pyfaf/Makefile.am index f72e30c..533ccd4 100644 --- a/pyfaf/Makefile.am +++ b/pyfaf/Makefile.am @@ -6,6 +6,7 @@ pyfaf_PYTHON = \ bugzilla.py \ cluster.py \ common.py \ + queries.py \ kb.py \ libsolv.py \ obs.py \ diff --git a/pyfaf/__init__.py b/pyfaf/__init__.py index 9e995cc..600b50a 100644 --- a/pyfaf/__init__.py +++ b/pyfaf/__init__.py @@ -27,4 +27,5 @@ from . import retrace from . import obs from . import cluster from . import kb +from . import queries import sys diff --git a/pyfaf/queries.py b/pyfaf/queries.py new file mode 100644 index 0000000..5abac79 --- /dev/null +++ b/pyfaf/queries.py @@ -0,0 +1,113 @@ +from pyfaf.storage.opsys import (OpSys, + OpSysRelease, + OpSysReleaseComponent, + OpSysComponent) + +from pyfaf.storage.report import (Report, + ReportHistoryDaily, ReportHistoryWeekly, + ReportHistoryMonthly) + +from sqlalchemy import func, desc + + +def get_history_target(target='daily'): + ''' + Return tuple of `ReportHistory(Daily|Weekly|Monthly)` and + proper date field `ReportHistory(Daily.day|Weekly.week|Monthly.month)` + according to `target` parameter which should be one of + `daily|weekly|monthly` or shortened version `d|w|m`. + ''' + if target == 'd' or target == 'daily': + return (ReportHistoryDaily, ReportHistoryDaily.day) + + if target == 'w' or target == 'weekly': + return (ReportHistoryWeekly, ReportHistoryWeekly.week) + + return (ReportHistoryMonthly, ReportHistoryMonthly.month) + + +def query_releases(db, opsys_name=None, opsys_version=None): + ''' + Return query of `OpSysRelease` records optionaly filtered + by `opsys_name` and `opsys_version`. + ''' + opsysquery = ( + db.session.query(OpSysRelease) + .join(OpSys)) + + if opsys_name: + opsysquery = opsysquery.filter(OpSys.name == opsys_name) + + if opsys_version: + opsysquery = opsysquery.filter(OpSysRelease.version == opsys_version) + + return opsysquery + + +def query_release_ids(db, opsys_name=None, opsys_version=None): + ''' + Return list of `OpSysRelease` ids optionaly filtered + by `opsys_name` and `opsys_version`. + ''' + return [opsysrelease.id for opsysrelease in + query_releases(db, opsys_name, opsys_version).all()] + + +def query_history_sum(db, opsys_name=None, opsys_version=None, + history='daily'): + ''' + Return query summing ReportHistory(Daily|Weekly|Monthly) + records optinaly filtered by `opsys_name` and `opsys_version`. + ''' + + opsysrelease_ids = query_release_ids(db, opsys_name, opsys_version) + hist_table, hist_field = get_history_target(history) + hist_sum = db.session.query(func.sum(hist_table.count).label('cnt')) + if opsysrelease_ids: + hist_sum = hist_sum.filter( + hist_table.opsysrelease_id.in_(opsysrelease_ids)) + + return hist_sum + + +def query_report_count_per_component(db, opsys_name=None, opsys_version=None, + history='daily'): + ''' + Return query for `OpSysComponent` and number of reports this + component received. + + It's possible to filter the results by `opsys_name` and + `opsys_version`. + ''' + + opsysrelease_ids = query_release_ids(db, opsys_name, opsys_version) + hist_table, hist_field = get_history_target(history) + + comps = ( + db.session.query(OpSysComponent, + func.sum(hist_table.count).label('cnt')) + .join(Report) + .join(hist_table) + .group_by(OpSysComponent) + .order_by(desc('cnt'))) + + if opsysrelease_ids: + comps = comps.filter(hist_table.opsysrelease_id.in_(opsysrelease_ids)) + + return comps + + +def query_report_stats_per_component(db, component, history='daily'): + ''' + Return query with reports for `component` along with + summed counts from `history` table (one of daily/weekly/monthly). + ''' + hist_table, hist_field = get_history_target(history) + + return (db.session.query(Report, + func.sum(hist_table.count).label('cnt')) + .join(hist_table) + .join(OpSysComponent) + .filter(OpSysComponent.id == component.id) + .group_by(Report) + .order_by(desc('cnt'))) -- 1.8.1.4