Merge lp:~canonical-platform-qa/qakit/landings-reporting into lp:qakit

[Allan LeSage]

Data gathering from Trello for our silo board.

Data gathering from Trello for our silo board.

Gather cards and actions, then compute silos with timings.

NOTE that the reporting half will follow, also note that you'll need pymongo to test.

[Allan LeSage (allanlesage)]

O one more thing: we're going to do away with the "week" data item for silos, as it's proving unreliable under international pressures--instead we'll do this on the reporting side, providing queries for arbitrary start-and-end times. (In a subsequent MP.)

[lp:~canonical-platform-qa/qakit/landings-reporting]

21. By Allan LeSage on 2015-10-07

Remove an outdated script.

[Christopher Lee (veebers)]

Hi Allan,

I had an initial look over the code making some comments. I'll come back and do a more indepth review once the questions posed have been answered and when I have my head fully around what the code is doing.

review: Needs Fixing

[Allan LeSage (allanlesage)]

Thanks for the detailed review Chris! Replied inline.

[lp:~canonical-platform-qa/qakit/landings-reporting]

22. By Allan LeSage on 2015-10-07

Specify what's being created or moved in function name.

23. By Allan LeSage on 2015-10-07

Result instead of passed_or_failed.

24. By Allan LeSage on 2015-10-07

Explanatory comment for 'actions'.

25. By Allan LeSage on 2015-10-07

Explanatory comment for TypeError on None result action.

26. By Allan LeSage on 2015-10-07

Collapse duration exceptions, preserving comments.

27. By Allan LeSage on 2015-10-07

Awkward language but less abstract for compute_silo_timings.

28. By Allan LeSage on 2015-10-07

Amend a comment on mongodb array upsert.

29. By Allan LeSage on 2015-10-07

Fix flake8 error on long line string.

30. By Allan LeSage on 2015-10-07

More specific exception on first run finding no actions.

31. By Allan LeSage on 2015-10-07

Add docstring for since.

32. By Allan LeSage on 2015-10-07

Standardize on line-ending parens.

33. By Allan LeSage on 2015-10-07

Docstring clarification on return for get_cards_and_actions.

34. By Allan LeSage on 2015-10-07

Remove unused function queue_to_list.

35. By Allan LeSage on 2015-10-07

Amend a get_cards_actions Trello docstring, correct a flake8 error.

36. By Allan LeSage on 2015-10-07

Add Trello API reference.

[Christopher Lee (veebers)]

Looking good, just a couple of comments inline.

review: Needs Fixing

[Allan LeSage (allanlesage)]

Thanks again for the review, comments below, pushing now.

[lp:~canonical-platform-qa/qakit/landings-reporting]

37. By Allan LeSage on 2015-10-08

Indicate 'card' as subject of relevant function names.

38. By Allan LeSage on 2015-10-08

Adjust import to avoid config collision.

39. By Allan LeSage on 2015-10-08

Move 'since' arg validation to own function.

40. By Allan LeSage on 2015-10-08

Encapsulate logging setup.

[Sergio Cazzolato (sergio-j-cazzolato)]

Some minor comments inline

review: Needs Fixing

[lp:~canonical-platform-qa/qakit/landings-reporting]

41. By Allan LeSage on 2015-10-08

Expand exception logging around mongo errors.

42. By Allan LeSage on 2015-10-08

De-Noneing sinces.

43. By Allan LeSage on 2015-10-08

Geek out on exceptions instead of Nones.

[Allan LeSage (allanlesage)]

Shifted to raising exceptions--what do you guys think? Actually auditioning this for your opinion. IMO it's maybe better to return None a level below, would make the big assembly function more succinct. Meanwhile Sergio I think I've served your other requests, see comments inline.

[Christopher Lee (veebers)]

Comment about the exception handling.

review: Needs Fixing

[lp:~canonical-platform-qa/qakit/landings-reporting]

44. By Allan LeSage on 2015-10-13

Backed off exception-raising, preferring returning Nones as the client level, added tests.

[Allan LeSage (allanlesage)]

Add some unit testing :) , also add logging and prefer to return Nones (to raising exceptions) at least at client-visible level.

[lp:~canonical-platform-qa/qakit/landings-reporting]

45. By Allan LeSage on 2015-10-13

Unit tests not executable.

46. By Allan LeSage on 2015-10-13

Log for no result datetime per veebers' req.

47. By Allan LeSage on 2015-10-13

Comment clean-up.

48. By Allan LeSage on 2015-10-14

Some updates and test-fixes for oversights.

[Christopher Lee (veebers)]

Some very minor comments, but otherwise looks good.

The testing comes very close in some places to just testing that the mock is correct but otherwise tests the functions logic.

review: Needs Fixing

[lp:~canonical-platform-qa/qakit/landings-reporting]

49. By Allan LeSage on 2015-10-14

Collapse TRELLO config refs per veebers' suggestion.

50. By Allan LeSage on 2015-10-14

Remove parse_date_str ftn.

[Allan LeSage (allanlesage)]

Thanks Chris, made your suggested changes--I'd like to use *sections* for the config stuff which will make all clearer, but because this affects brendand I'd like to defer for a future MP.

[Christopher Lee (veebers)]

Makes sense. LGTM

review: Approve

[Sergio Cazzolato (sergio-j-cazzolato)]

Looks good.

review: Approve

=== added directory 'qakit/metrics'
=== added file 'qakit/metrics/landings.py'
--- qakit/metrics/landings.py	1970-01-01 00:00:00 +0000
+++ qakit/metrics/landings.py	2015-10-14 17:47:11 +0000
@@ -0,0 +1,391 @@
+#!/usr/bin/python3
+# UESQA Metrics
+# Copyright (C) 2015 Canonical
+#
+# This program is free software: you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation, either version 3 of the License, or
+# (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program.  If not, see <http://www.gnu.org/licenses/>.
+
+import argparse
+import configparser
+import datetime
+import dateutil.parser
+import logging
+import sys
+
+import pymongo
+
+import qakit.config as qakit_config
+import qakit.metrics.trello as trello
+import qakit.trello as trelloapi
+import qakit.metrics.util as util
+
+logger = logging.getLogger(__name__)
+
+
+def upsert_silo(trello_db, silo):
+    """Insert a silo into our db.
+
+    :param trello_db: pymongo.MongoClient db for silos
+    :param silo: computed silo
+
+    """
+    try:
+        return trello_db.silos.update(
+            {'id': silo['id']},
+            silo,
+            upsert=True)
+    except pymongo.errors.PyMongoError as e:
+        logger.error(e)
+
+
+def get_card_created_action(trello_db, card_id):
+    """Return the 'action' by which the given card was created.
+
+    'Actions' come from the Trello API where they're represented in
+    JSON; we store them in our db unmodified.  Actions have types,
+    users, data, etc. to describe all the actions possible by a user
+    in the Trello UI.  Here we return the card's action of type
+    'createCard'--worth noting that Trello only returns modifications
+    and comments by default.
+
+    :param trello_db: pymongo.MongoClient db for Trello items
+    :param card_id: Trello card id string
+    :raises ValueError: if card creation action not found--this can 
+        happen if our database begins after this card was created, e.g.
+
+    """
+    created_action = trello_db.actions.find_one(
+        {'data.card.id': card_id,
+         'type': 'createCard'})
+    if not created_action:
+        # this could happen if our db has an incomplete set of
+        # actions, e.g. starting after this card was created
+        raise ValueError(
+            "Create action for card {} not found.".format(card_id))
+    return created_action
+
+
+def get_card_created_datetime(trello_db, card_id):
+    """Return the datetime of a card's 'createCard' action.
+
+    :param trello_db: pymongo.MongoClient db for Trello items
+    :param card_id: Trello card id string
+
+    """
+    try:
+        created_action = get_card_created_action(trello_db, card_id)
+    except ValueError:
+        # this could happen if our db has an incomplete set of
+        # actions, e.g. starting after this card was created
+        logger.info(
+            "Create action for card {} not found.".format(card_id))
+        return None
+    return dateutil.parser.parse(created_action['date'])
+
+
+def get_card_result_action(trello_db, card_id):
+    """Return the 'action' of moving to 'Passed' or 'Failed' column.
+
+    'Actions' come from the Trello API where they're represented in
+    JSON; we store them in our db unmodified.  Actions have types,
+    users, data, etc. to describe all the actions possible by a user
+    in the Trello UI.  We're most interested in the card-moving
+    actions which result in a card being moved from one list ("under
+    testing") to another ("Passed" or "Failed").
+
+    Silo cards should have only one of these results but we can't
+    prevent QEs from making a mistake by moving the card from one lane
+    to another, e.g., so just use the last reported result.
+
+    :param trello_db: pymongo.MongoClient db for Trello items
+    :param card_id: Trello card id string
+    :raises ValueError: if card result not found, e.g. still under
+        testing
+
+    """
+    result_action = trello_db.actions.find_one(
+        {'data.card.id': card_id,
+         'data.listAfter.name': {'$in': ['Passed', 'Failed']}},
+        sort=[('date', pymongo.DESCENDING)])
+    if not result_action:
+        raise ValueError(
+            "Result action for card {} not found".format(card_id))
+    return result_action
+
+
+def get_card_under_testing_action(trello_db, card_id):
+    """Return the 'action' of moving to "Under Testing" column.
+
+    'Actions' come from the Trello API where they're represented in
+    JSON; we store them in our db unmodified.  Actions have types,
+    users, data, etc. to describe all the actions possible by a user
+    in the Trello UI.  We're most interested in the card-moving
+    actions which result in a card being moved from one list ("Ready
+    for Testing") to another ("Under Testing").
+
+    Silo cards should have only one of these results but we can't
+    prevent QEs from making a mistake by moving the card from one lane
+    to another, e.g., so just use the last reported result.
+
+    :param trello_db: pymongo.MongoClient db for Trello items
+    :param card_id: Trello card id string
+    :raises ValueError: if card 'Under Testing' action not found, e.g. 
+        not yet under testing
+
+    """
+    under_testing_action = trello_db.actions.find_one(
+        {'data.card.id': card_id,
+         'data.listAfter.name': "Under Testing"},
+        sort=[('date', pymongo.DESCENDING)])
+    if not under_testing_action:
+        raise ValueError(
+            "'Under Testing' action for card {} not found".format(card_id))
+    return under_testing_action
+
+
+def get_card_under_testing_datetime(trello_db, card_id):
+    """Return the datetime that the given card came under testing.
+
+    :param trello_db: pymongo.MongoClient db for Trello items
+    :param card_id: Trello card id string
+
+    """
+    try:
+        action = get_card_under_testing_action(trello_db, card_id)
+    except ValueError:
+        # no result yet
+        logger.debug(
+            "'Under Testing' action for card {} not found.".format(card_id))
+        return None
+    return dateutil.parser.parse(action['date'])
+
+
+def get_card_result(trello_db, card_id):
+    """Return passed or failed status string for a silo card.
+
+    :param trello_db: pymongo.MongoClient db for Trello items
+    :param card_id: Trello card id string
+
+    """
+    try:
+        action = get_card_result_action(trello_db, card_id)
+    except ValueError:
+        # no result yet
+        logger.debug(
+            "No result found for card {}.".format(card_id))
+        return None
+    return action['data']['listAfter']['name']
+
+
+def get_card_result_datetime(trello_db, card_id):
+    """Return the datetime that the given card passed or failed.
+
+    :param trello_db: pymongo.MongoClient db for Trello items
+    :param card_id: Trello card id string
+
+    """
+    try:
+        action = get_card_result_action(trello_db, card_id)
+    except ValueError:
+        # no result yet
+        logger.debug(
+            "No result found for card {}.".format(card_id))
+        return None
+    return dateutil.parser.parse(action['date'])
+
+
+def get_card_week_number(trello_db, card_id):
+    """Return the week number for the card's result.
+
+    Cards belong to week for which a passed or failed result was
+    reported.
+
+    :param trello_db: pymongo.MongoClient db for Trello items
+    :param card_id: Trello card id string
+
+    """
+    result_datetime = get_card_result_datetime(trello_db, card_id)
+    if not result_datetime:
+        # no result yet
+        logger.debug(
+            "No result found for card {}.".format(card_id))
+        return None
+    return int(result_datetime.strftime("%W"))
+
+
+def get_card_year(trello_db, card_id):
+    """Return the year for the card's result.
+
+    :param trello_db: pymongo.MongoClient db for Trello items
+    :param card_id: Trello card id string
+
+    """
+    result_datetime = get_card_result_datetime(trello_db, card_id)
+    if not result_datetime:
+        # no result yet
+        logger.debug(
+            "No result found for card {}.".format(card_id))
+        return None
+    return int(result_datetime.strftime("%Y"))
+
+
+def get_card_testing_duration(trello_db, card_id):
+    """Return a silo's testing duration in seconds.
+
+    :param trello_db: pymongo.MongoClient db for Trello items
+    :param card_id: Trello card id string
+
+    """
+    under_testing_datetime = get_card_under_testing_datetime(
+        trello_db, card_id)
+    result_datetime = get_card_result_datetime(trello_db, card_id)
+    if not under_testing_datetime or not result_datetime:
+        logger.debug("Card {} has no testing duration.".format(card_id))
+        return None
+    duration = result_datetime - under_testing_datetime
+    return duration.total_seconds()
+
+
+def get_card_total_duration(trello_db, card_id):
+    """Get a silo's time from creation to passed or failed in seconds.
+
+    :param trello_db: pymongo.MongoClient db for Trello items
+    :param card_id: Trello card id string
+
+    """
+    created_datetime = get_card_created_datetime(trello_db, card_id)
+    result_datetime = get_card_result_datetime(
+        trello_db,
+        card_id)
+    if not created_datetime or not result_datetime:
+        logger.debug("Card {} has no total duration.".format(card_id))
+        return None
+    total_duration = result_datetime - created_datetime
+    return total_duration.total_seconds()
+
+
+def compute_silo_timing(trello_db, card):
+    """Return a silo dict with timings, looking up actions in trello_db.
+
+    :param trello_db: pymongo.MongoClient db for Trello items
+    :param card: Trello dict of silo card
+
+    """
+    logger.info("Computing silo timings for card {}.".format(card['id']))
+    silo = {'id': card['id'],
+            'name': card['name'],
+            'url': card['url']}
+    silo['created_datetime'] = get_card_created_datetime(trello_db, card['id'])
+    silo['result_datetime'] = get_card_result_datetime(trello_db, card['id'])
+    silo['week_number'] = get_card_week_number(trello_db, card['id'])
+    silo['year'] = get_card_year(trello_db, card['id'])
+    silo['total_duration'] = get_card_total_duration(
+        trello_db, card['id'])
+    silo['testing_duration'] = get_card_testing_duration(
+        trello_db, card['id'])
+    silo['result'] = get_card_result(trello_db, card['id'])
+    return silo
+
+
+def compute_silos_timing(trello_db, cards=None):
+    """Compute silo timings for the given cards and store in a db.
+
+    :param trello_db: pymongo.MongoClient db in which to store silos
+    :param cards: list of card dicts as returned by Trello
+
+    """
+    if cards is None:
+        cards = trello_db.cards.find()
+    for card in cards:
+        silo = compute_silo_timing(trello_db, card)
+        upsert_silo(trello_db, silo)
+
+
+def ensure_unique_indexes(trello_db):
+    """Ensure that our card, action, and silo indexes exist.
+
+    We're being paranoid--this should've been taken care of in setup
+    and we're using upserts everywhere--but we don't want to insert
+    duplicates.  NOTE: if this errors it means a duplicate exists,
+    needs to be fixed in the db.
+
+    :param trello_db: pymongo.MongoClient db for Trello items
+
+    """
+    trello_db.cards.create_index('id', unique=True)
+    trello_db.actions.create_index('id', unique=True)
+    trello_db.silos.create_index('id', unique=True)
+
+
+def _read_config(config_filepath):
+    """Parse the config at the given filepath, returning a config dict."""
+    config_file = configparser.ConfigParser()
+    config_file.read(config_filepath)
+    config = {}
+    for var_name in ('TRELLO_APP_KEY', 'TRELLO_TOKEN', 'TRELLO_BOARD_ID'):
+        new_var_name = var_name.lower().replace('trello_', '')
+        config[new_var_name] = config_file['DEFAULT'][var_name]
+    return config
+
+
+def _parse_arguments():
+    parser = argparse.ArgumentParser(
+        "Retrieve silos from Ops Trello board, storing in a Mongo db.")
+    parser.add_argument(
+        '--since',
+        required=False,
+        help=("retrieve actions and cards modified since the given date; "
+              "defaults to last known action in database."),
+        default=None,
+        type=str)
+    return parser.parse_args()
+
+
+def _validate_since_arg(args):
+    """Validate format of 'since' arg or set to 2014 as necessary.
+
+    :param args: argparse namespace
+
+    """
+    if not args.since:
+        args.since = datetime.datetime(2014, 1, 1)
+    else:
+        try:
+            args.since = datetime.datetime.strptime(args.since, "%Y-%m-%d")
+        except ValueError:
+            raise argparse.ArgumentError(
+                None,
+                "'since' must be in YYYY-MM-DD format")
+
+
+def main():
+    util.setup_logging()
+    config_dict = _read_config(qakit_config.get_config_file_location())
+    args = _parse_arguments()
+    _validate_since_arg(args)
+    conn = pymongo.MongoClient()
+    trello_db = conn.trello
+    ensure_unique_indexes(trello_db)
+    trello_session = trelloapi.TrelloApi(
+        app_key=config_dict['app_key'],
+        token=config_dict['token'])
+    cards = trello.retrieve_cards_and_actions(
+        trello_db,
+        trello_session,
+        config_dict['board_id'],
+        since=args.since)
+    compute_silos_timing(trello_db, cards)
+
+
+if __name__ == '__main__':
+    sys.exit(main())

=== added directory 'qakit/metrics/tests'
=== added file 'qakit/metrics/tests/test_landings.py'
--- qakit/metrics/tests/test_landings.py	1970-01-01 00:00:00 +0000
+++ qakit/metrics/tests/test_landings.py	2015-10-14 17:47:11 +0000
@@ -0,0 +1,342 @@
+# UESQA Metrics
+# Copyright (C) 2015 Canonical
+#
+# This program is free software: you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation, either version 3 of the License, or
+# (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program.  If not, see <http://www.gnu.org/licenses/>.
+
+import datetime
+import unittest
+from unittest import mock
+
+import dateutil
+from bson import ObjectId
+
+import qakit.metrics.landings as landings
+
+
+CREATED = {
+    "_id": ObjectId("559d3b9a6f9289b4a62909fd"),
+    "id": "559c033efbfb3ecbc9f725b6",
+    "type": "createCard",
+    "memberCreator": {
+        "id": "53bfc3936a7d7c888e46f4cd",
+        "fullName": "Jean-Baptiste Lallement",
+        "username": "jeanbaptistelallement",
+        "initials": "JBL",
+        "avatarHash": None
+    },
+    "date": "2015-07-07T16:50:06.409Z",
+    "data": {
+        "board": {
+            "id": "53fc6641728df958a48bfbe1",
+            "shortLink": "AE3swczu",
+            "name": ("QA Testing Requests - for questions ping EU: "
+                     "jibel, US: jfunk - or ubuntu-qa on #ubuntu-ci-eng")
+        },
+        "card": {
+            "name": "ubuntu/landing-026 - messaging-app : boiko",
+            "id": "559c033efbfb3ecbc9f725b5",
+            "shortLink": "7cOV5cYP",
+            "idShort": 1865
+        },
+        "list": {
+            "id": "53fc6641728df958a48bfbe2",
+            "name": "Need QA Sign-off"
+        }
+    },
+    "idMemberCreator": "53bfc3936a7d7c888e46f4cd"
+}
+
+UNDER_TESTING = {
+    "_id": ObjectId("559d3b9a6f9289b4a62909f4"),
+    "id": "559c50bfb233bddda9939772",
+    "type": "updateCard",
+    "memberCreator": {
+        "id": "510c44e279f3442c56000417",
+        "fullName": "Allan LeSage",
+        "username": "allanlesage",
+        "initials": "AL",
+        "avatarHash": "5c61e74f355449f6c7cfc35880e6a49f"
+    },
+    "date": "2015-07-07T22:20:47.469Z",
+    "data": {
+        "board": {
+            "id": "53fc6641728df958a48bfbe1",
+            "shortLink": "AE3swczu",
+            "name": ("QA Testing Requests - for questions ping EU: "
+                     "jibel, US: jfunk - or ubuntu-qa on #ubuntu-ci-eng")
+        },
+        "card": {
+            "name": "ubuntu/landing-026 - messaging-app : boiko",
+            "id": "559c033efbfb3ecbc9f725b5",
+            "shortLink": "7cOV5cYP",
+            "idList": "53fc6641728df958a48bfbe3",
+            "idShort": 1865
+        },
+        "listBefore": {
+            "id": "552bcb7e2bc13035850abeae",
+            "name": "Ready for Testing"
+        },
+        "listAfter": {
+            "id": "53fc6641728df958a48bfbe3",
+            "name": "Under Testing"
+        },
+        "old": {
+            "idList": "552bcb7e2bc13035850abeae"
+        }
+    },
+    "idMemberCreator": "510c44e279f3442c56000417"
+}
+
+PASSED = {
+    "_id": ObjectId("559d3b9a6f9289b4a62909ed"),
+    "id": "559d24feed5efa44c31650c0",
+    "type": "updateCard",
+    "memberCreator": {
+        "id": "53bfc49275645e39d37a1046",
+        "fullName": "Víctor R. Ruiz",
+        "username": "victorrruiz",
+        "initials": "VR",
+        "avatarHash": "c15c5a5db02d89903f2233d88ad778d5"
+    },
+    "date": "2015-07-08T13:26:22.006Z",
+    "data": {
+        "board": {
+            "id": "53fc6641728df958a48bfbe1",
+            "shortLink": "AE3swczu",
+            "name": ("QA Testing Requests - for questions ping EU: "
+                     "jibel, US: jfunk - or ubuntu-qa on #ubuntu-ci-eng")
+        },
+        "card": {
+            "name": "ubuntu/landing-026 - messaging-app : boiko",
+            "id": "559c033efbfb3ecbc9f725b5",
+            "shortLink": "7cOV5cYP",
+            "idList": "53fc6641728df958a48bfbe4",
+            "idShort": 1865
+        },
+        "listBefore": {
+            "id": "53fc6641728df958a48bfbe3",
+            "name": "Under Testing"
+        },
+        "listAfter": {
+            "id": "53fc6641728df958a48bfbe4",
+            "name": "Passed"
+        },
+        "old": {
+            "idList": "53fc6641728df958a48bfbe3"
+        }
+    },
+    "idMemberCreator": "53bfc49275645e39d37a1046"
+}
+
+
+class GetCardCreatedActionTestCase(unittest.TestCase):
+
+    def test_vanilla(self):
+        db = mock.Mock(
+            actions=mock.Mock(
+                find_one=mock.Mock(return_value=CREATED)))
+        self.assertEqual(
+            CREATED,
+            landings.get_card_created_action(db, 'fake-card-id'))
+
+    def test_no_created_action_raises(self):
+        db = mock.Mock(
+            actions=mock.Mock(
+                find_one=mock.Mock(return_value=None)))
+        with self.assertRaises(ValueError):
+            landings.get_card_created_action(db, 'fake-card-id')
+
+
[e-mail]('qakit.metrics.landings.get_card_created_action')
+class GetCardCreatedDatetimeTestCase(unittest.TestCase):
+
+    def test_vanilla(self, mock_get_card_created_action):
+        mock_get_card_created_action.return_value = CREATED
+        self.assertEqual(
+            datetime.datetime(
+                2015, 7, 7, 16, 50, 6, 409000, tzinfo=dateutil.tz.tzutc()),
+            landings.get_card_created_datetime('fake-db', 'fake-card-id'))
+
+    def test_no_created_datetime_raises(self, mock_get_card_created_action):
+        mock_get_card_created_action.side_effect = ValueError
+        self.assertEqual(
+            None,
+            landings.get_card_created_datetime('fake-db', 'fake-card-id'))
+
+
+class GetCardResultActionTestCase(unittest.TestCase):
+
+    def test_vanilla(self):
+        db = mock.Mock(
+            actions=mock.Mock(
+                find_one=mock.Mock(return_value=PASSED)))
+        action = landings.get_card_result_action(db, 'fake-card-id')
+        self.assertEqual(PASSED, action)
+
+    def test_raise_if_action_not_found(self):
+        db = mock.Mock(
+            actions=mock.Mock(
+                find_one=mock.Mock(return_value=None)))
+        with self.assertRaises(ValueError):
+            landings.get_card_result_action(db, 'fake-card-id')
+
+
[e-mail]('qakit.metrics.landings.get_card_result_action')
+class GetCardResultDatetimeTestCase(unittest.TestCase):
+
+    def test_vanilla(self, mock_get_card_result_action):
+        mock_get_card_result_action.return_value = PASSED
+        self.assertEqual(
+            datetime.datetime(
+                2015, 7, 8, 13, 26, 22, 6000, tzinfo=dateutil.tz.tzutc()),
+            landings.get_card_result_datetime('fake-db', 'fake-card-id'))
+
+    def test_no_result_datetime_if_no_result(self, mock_get_card_result_action):
+        mock_get_card_result_action.side_effect = ValueError
+        self.assertEqual(
+            None,
+            landings.get_card_result_datetime('fake-db', 'fake-card-id'))
+
+
+class GetCardUnderTestingActionTestCase(unittest.TestCase):
+
+    def test_vanilla(self):
+        db = mock.Mock(
+            actions=mock.Mock(
+                find_one=mock.Mock(return_value=UNDER_TESTING)))
+        action = landings.get_card_under_testing_action(db, 'fake-card-id')
+        self.assertEqual(UNDER_TESTING, action)
+
+    def test_raise_if_action_not_found(self):
+        db = mock.Mock(
+            actions=mock.Mock(
+                find_one=mock.Mock(return_value=None)))
+        with self.assertRaises(ValueError):
+            landings.get_card_under_testing_action(db, 'fake-card-id')
+
+
[e-mail]('qakit.metrics.landings.get_card_under_testing_action')
+class GetCardUnderTestingDatetimeTestCase(unittest.TestCase):
+
+    def test_vanilla(self, mock_get_card_under_testing_action):
+        mock_get_card_under_testing_action.return_value = PASSED
+        self.assertEqual(
+            datetime.datetime(
+                2015, 7, 8, 13, 26, 22, 6000, tzinfo=dateutil.tz.tzutc()),
+            landings.get_card_under_testing_datetime('fake-db', 'fake-card-id'))
+
+    def test_no_under_testing_datetime_if_never_under_testing(
+            self, mock_get_card_under_testing_action):
+        mock_get_card_under_testing_action.side_effect = ValueError
+        self.assertEqual(
+            None,
+            landings.get_card_under_testing_datetime('fake-db', 'fake-card-id'))
+
+
[e-mail]('qakit.metrics.landings.get_card_result_action')
+class GetCardResultTestCase(unittest.TestCase):
+
+    def test_vanilla(self, mock_get_card_result_action):
+        mock_get_card_result_action.return_value = PASSED
+        self.assertEqual("Passed", landings.get_card_result(
+            'fake-db', 'fake-card-id'))
+
+    def test_no_result_if_no_result(self, mock_get_card_result_action):
+        mock_get_card_result_action.side_effect = ValueError
+        self.assertEqual(None, landings.get_card_result(
+            'fake-db', 'fake-card-id'))
+
+
[e-mail]('qakit.metrics.landings.get_card_result_datetime')
+class GetCardWeekNumberTestCase(unittest.TestCase):
+
+    def test_vanilla(self, mock_get_card_result_datetime):
+        mock_get_card_result_datetime.return_value = datetime.datetime(
+            2015, 7, 8, 13, 26, 22, 6000, tzinfo=dateutil.tz.tzutc())
+        self.assertEqual(
+            27,
+            landings.get_card_week_number('fake-db', 'fake-card-id'))
+
+    def test_no_week_number_if_no_result(self, mock_get_card_result_datetime):
+        mock_get_card_result_datetime.return_value = None
+        self.assertEqual(None, landings.get_card_week_number(
+            'fake-db', 'fake-card-id'))
+
+
[e-mail]('qakit.metrics.landings.get_card_result_datetime')
+class GetCardYearTestCase(unittest.TestCase):
+
+    def test_vanilla(self, mock_get_card_result_datetime):
+        mock_get_card_result_datetime.return_value = datetime.datetime(
+            2015, 7, 8, 13, 26, 22, 6000, tzinfo=dateutil.tz.tzutc())
+        self.assertEqual(
+            2015,
+            landings.get_card_year('fake-db', 'fake-card-id'))
+
+    def test_no_year_if_no_result(self, mock_get_card_result_datetime):
+        mock_get_card_result_datetime.return_value = None
+        self.assertEqual(None, landings.get_card_year(
+            'fake-db', 'fake-card-id'))
+
+
[e-mail]('qakit.metrics.landings.get_card_result_datetime')
[e-mail]('qakit.metrics.landings.get_card_under_testing_datetime')
+class GetCardTestingDurationTestCase(unittest.TestCase):
+
+    def test_vanilla(
+            self,
+            mock_get_card_under_testing_datetime,
+            mock_get_card_result_datetime):
+        mock_get_card_under_testing_datetime.return_value = datetime.datetime(
+            2015, 7, 8, 13, 25, 22, 6000, tzinfo=dateutil.tz.tzutc())
+        mock_get_card_result_datetime.return_value = datetime.datetime(
+            2015, 7, 8, 13, 26, 22, 6000, tzinfo=dateutil.tz.tzutc())
+        self.assertEqual(60.0, landings.get_card_testing_duration(
+            'fake-db', 'fake-card-id'))
+
+    def test_no_under_testing_datetime(
+            self,
+            mock_get_card_under_testing_datetime,
+            mock_get_card_result_datetime):
+        mock_get_card_under_testing_datetime.return_value = datetime.datetime(
+            2015, 7, 8, 13, 25, 22, 6000, tzinfo=dateutil.tz.tzutc())
+        mock_get_card_result_datetime.return_value = None
+        self.assertEqual(None, landings.get_card_testing_duration(
+            'fake-db', 'fake-card-id'))
+
+
[e-mail]('qakit.metrics.landings.get_card_result_datetime')
[e-mail]('qakit.metrics.landings.get_card_created_datetime')
+class GetCardTotalDurationTestCase(unittest.TestCase):
+
+    def test_vanilla(
+            self,
+            mock_get_card_created_datetime,
+            mock_get_card_result_datetime):
+        mock_get_card_created_datetime.return_value = datetime.datetime(
+            2015, 7, 8, 13, 25, 22, 6000, tzinfo=dateutil.tz.tzutc())
+        mock_get_card_result_datetime.return_value = datetime.datetime(
+            2015, 7, 8, 13, 26, 22, 6000, tzinfo=dateutil.tz.tzutc())
+        self.assertEqual(60.0, landings.get_card_total_duration(
+            'fake-db', 'fake-card-id'))
+
+    def test_no_under_testing_datetime(
+            self,
+            mock_get_card_created_datetime,
+            mock_get_card_result_datetime):
+        mock_get_card_created_datetime.return_value = datetime.datetime(
+            2015, 7, 8, 13, 25, 22, 6000, tzinfo=dateutil.tz.tzutc())
+        mock_get_card_result_datetime.return_value = None
+        self.assertEqual(None, landings.get_card_total_duration(
+            'fake-db', 'fake-card-id'))

=== added file 'qakit/metrics/trello.py'
--- qakit/metrics/trello.py	1970-01-01 00:00:00 +0000
+++ qakit/metrics/trello.py	2015-10-14 17:47:11 +0000
@@ -0,0 +1,209 @@
+# UESQA Metrics
+# Copyright (C) 2015 Canonical
+#
+# This program is free software: you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation, either version 3 of the License, or
+# (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program.  If not, see <http://www.gnu.org/licenses/>.
+
+
+import logging
+
+import pymongo
+
+logger = logging.getLogger(__name__)
+
+
+def upsert_card(trello_db, card):
+    """Insert a Trello card into our db.
+
+    :param trello_db: pymongo.MongoClient db for Trello items
+    :param action: Trello-reported action to upsert
+
+    """
+    try:
+        trello_db.cards.update(
+            {'id': card['id']},
+            card,
+            upsert=True)
+    except pymongo.errors.PyMongoError as e:
+        logger.error(e)
+
+
+def upsert_action(trello_db, action):
+    """Insert a Trello action into our db.
+
+    :param trello_db: pymongo.MongoClient db for Trello items
+    :param action: Trello-reported action to upsert
+
+    """
+    try:
+        return trello_db.actions.update(
+            {'id': action['id']},
+            action,
+            upsert=True)
+    except pymongo.errors.PyMongoError as e:
+        logger.error(e)
+
+
+def upsert_actions(trello_db, actions):
+    """Insert a list of Trello silo actions into our trello_db.
+
+    :param trello_db: pymongo.MongoClient db for Trello items
+    :param actions: Trello-reported actions to upsert
+
+    """
+    # TODO: does mongo make it possible to upsert the array whole?
+    for action in actions:
+        try:
+            upsert_action(trello_db, action)
+        except pymongo.errors.PyMongoError as e:
+            logger.error(e)
+
+
+def get_last_action_datetime(trello_db):
+    """Return the datetime of the last known action in our db.
+
+    :param trello_db: pymongo.MongoClient db for Trello items
+
+    """
+    last_action = trello_db.actions.find_one(
+        {},
+        sort=[('date', pymongo.DESCENDING)])
+    try:
+        return last_action['date']
+    except TypeError:
+        # first run we have no actions, e.g.
+        return None
+
+
+def get_boards_actions_count(trello_session, board_id, since=None):
+    """Return number of actions available for given board_id.
+
+    NOTE that 1000 is Trello's max, likely means that there are more.
+
+    :param trello_session: TrelloSession to query
+    :param board_id: Trello board id string
+    :param since: datetime to query, defaults to None
+
+    """
+    return trello_session.get_boards_action(
+        board_id,
+        limit=1000,
+        trello_format='count',
+        since=since)['_value']
+
+
+def get_board_actions_by_card(trello_db,
+                              trello_session,
+                              board_id,
+                              since=None):
+    """Crawl a board's cards, retrieving actions and storing in db.
+
+    Trello API limits us to 1K actions when querying by board--use
+    this when spooling up a db from scratch, e.g., or when auditing.
+
+    :param trello_db: pymongo.MongoClient db for Trello items
+    :param trello_session: TrelloSession to query
+    :param board_id: Trello board id string
+    :param since: datetime from which to query, defaults to None
+
+    """
+    cards = trello_session.get_boards_card(
+        board_id,
+        filter='all',
+        since=since)
+    for card in cards:
+        actions = trello_session.get_cards_actions(
+            card['id'],
+            filter=("addMemberToCard,removeMemberFromCard,"
+                    "commentCard,updateCard,createCard"),
+            since=since)
+        logger.debug('Card "{}" has {} actions.'.format(
+            card['name'], len(actions)))
+        upsert_actions(trello_db, actions)
+
+
+def retrieve_board_cards(trello_db, trello_session, board_id, since=None):
+    """Retrieve cards associated with a board and store in trello_db.
+
+    :param trello_db: pymongo.MongoClient db for Trello items
+    :param trello_session: TrelloSession to query
+    :param board_id: Trello board id string
+    :param since: datetime from which to query, defaults to None
+
+    """
+    if not since:
+        since = get_last_action_datetime(trello_db)
+    cards = trello_session.get_boards_card(
+        board_id,
+        filter='all',
+        since=since)
+    logger.info("Board {} contains {} cards.".format(board_id, len(cards)))
+    for card in cards:
+        upsert_card(trello_db, card)
+    return cards
+
+
+def retrieve_board_actions(trello_db, trello_session, board_id, since=None):
+    """Get actions associated with a board from Trello and store in trello_db.
+
+    :param trello_db: pymongo.MongoClient db for Trello items
+    :param trello_session: TrelloSession to query
+    :param board_id: Trello board id string
+    :param since: datetime to query, defaults to None
+
+    """
+    if not since:
+        since = get_last_action_datetime(trello_db)
+        logger.debug("Retr{}.".format(since))
+    if get_boards_actions_count(
+            trello_session,
+            board_id,
+            since=since) == 1000:
+        logger.info("More than 1000 actions found; crawling cards "
+                    "individually to get a complete set of actions.")
+        get_board_actions_by_card(trello_db, trello_session, board_id, since)
+    else:
+        actions = trello_session.get_boards_action(
+            board_id,
+            since=since)
+        logger.info("Found {} actions since {}.".format(
+            len(actions), since))
+        for action in actions:
+            upsert_action(trello_db, action)
+
+
+def retrieve_cards_and_actions(trello_db,
+                               trello_session,
+                               board_id,
+                               since=None):
+    """Get a board's cards and actions, store in trello_db, and return cards.
+
+    :param trello_db: pymongo.MongoClient db for Trello items
+    :param trello_session: TrelloSession to query
+    :param board_id: Trello board id string
+    :param since: datetime to query, defaults to None; if None get all actions
+        and cards since the last known action in our db
+
+    """
+    if not since:
+        since = get_last_action_datetime(trello_db)
+        logger.debug("Last action occurred {}.".format(since))
+    logger.debug("Retrieving cards and actions for board {} since {}.".format(
+        board_id, since))
+    cards = retrieve_board_cards(
+        trello_db,
+        trello_session,
+        board_id,
+        since=since)
+    retrieve_board_actions(trello_db, trello_session, board_id, since=since)
+    return cards

=== added file 'qakit/metrics/util.py'
--- qakit/metrics/util.py	1970-01-01 00:00:00 +0000
+++ qakit/metrics/util.py	2015-10-14 17:47:11 +0000
@@ -0,0 +1,81 @@
+
+# UEQA KPIs
+# Copyright (C) 2015 Canonical
+#
+# This program is free software: you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation, either version 3 of the License, or
+# (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program.  If not, see <http://www.gnu.org/licenses/>.
+
+import functools
+import sys
+import time
+
+import logging
+
+
+LOG_FILEPATH = '/var/log/qakit.log'
+
+
+def setup_logging(log_filepath=LOG_FILEPATH):
+    """Set up qakit logging.
+
+    :param log_filepath: filepath for system log, suggest /var/log/qakit.log
+
+    """
+    formatter = logging.Formatter('%(asctime)s - %(levelname)s - %(message)s')
+    file_handler = logging.FileHandler(log_filepath)
+    file_handler.setLevel(logging.DEBUG)
+    file_handler.setFormatter(formatter)
+    stdout_handler = logging.StreamHandler(sys.stdout)
+    stdout_handler.setLevel(logging.DEBUG)
+    stdout_handler.setFormatter(formatter)
+    logger = logging.getLogger()
+    logger.setLevel(logging.DEBUG)
+    logger.addHandler(stdout_handler)
+    logger.addHandler(file_handler)
+
+
+def retry(ExceptionToCheck, tries=4, delay=2, backoff=2, logger=None):
+    """Retry calling the decorated function using an exponential backoff.
+
+    http://www.saltycrane.com/blog/2009/11/trying-out-retry-decorator-python/
+    Original from: http://wiki.python.org/moin/PythonDecoratorLibrary#Retry
+
+    :param ExceptionToCheck: the exception to check. may be a tuple of
+        exceptions to check
+    :param tries: number of times to try (not retry) before giving up
+    :param delay: initial delay between retries in seconds
+    :param backoff: backoff multiplier e.g. value of 2 will double the delay
+        each retry
+    :param logger: logger.Logger to use.
+
+    """
+    def deco_retry(f):
+        @functools.wraps(f)
+        def f_retry(*args, **kwargs):
+            for i in range(tries):
+                try:
+                    return f(*args, **kwargs)
+                except ExceptionToCheck as e:
+                    d = delay * backoff ** i
+                    if logger:
+                        logger.debug(
+                            "%s, retrying in %d seconds. . . ." % (e, d))
+                    time.sleep(d)
+            return f(*args, **kwargs)
+        return f_retry  # true decorator
+    return deco_retry
+
+
+def get_week_number(datetime_):
+    """Return the week of the year from a datetime."""
+    return int(datetime_.strftime('%W'))

=== modified file 'qakit/trello.py'
--- qakit/trello.py	2015-06-24 15:47:30 +0000
+++ qakit/trello.py	2015-10-14 17:47:11 +0000
@@ -21,7 +21,6 @@
 
 from __future__ import absolute_import
 
-import json
 import requests
 
 
@@ -57,7 +56,8 @@
                         checkItemStates=None,
                         checklists=None,
                         filter=None,
-                        fields=None):
+                        fields=None,
+                        since=None):
         resp = requests.get(
             "https://trello.com/1/boards/{}/cards".format(board_id),
             params=dict(key=self._app_key,
@@ -68,7 +68,8 @@
                         checkItemStates=checkItemStates,
                         checklists=checklists,
                         filter=filter,
-                        fields=fields),
+                        fields=fields,
+                        since=since),
             data=None)
         resp.raise_for_status()
         return resp.json()
@@ -77,7 +78,9 @@
                           board_id,
                           filter=None,
                           fields=None,
-                          limit=None,
+                          limit=1000,
+                          trello_format=None,
+                          since=None,
                           page=None,
                           idModels=None):
         resp = requests.get(
@@ -87,8 +90,31 @@
                         filter=filter,
                         fields=fields,
                         limit=limit,
+                        format=trello_format,
+                        since=since,
                         page=page,
                         idModels=idModels),
             data=None)
         resp.raise_for_status()
         return resp.json()
+
+    def get_cards_actions(self, card_id, filter=None, since=None):
+        """Get actions associated with a card from Trello.
+
+        See https://developers.trello.com/advanced-reference/card#get-1-cards-card-id-or-shortlink-actions  # NOQA
+
+        :param card_id: Trello card ID string
+        :param filter: list of action type strings to retrieve, defaults (in
+            Trello) to commentCard, updateCard
+        :param since: datetime from which to query
+
+        """
+        resp = requests.get(
+            "https://api.trello.com/1/cards/{}/actions".format(card_id),
+            params=dict(
+                key=self._app_key,
+                token=self._token,
+                filter=filter),
+            data=None)
+        resp.raise_for_status()
+        return resp.json()