Merge lp:~jibel/canonical-qa-tracking/isotesters_breakdown into lp:canonical-qa-tracking

[Jean-Baptiste Lallement]

[Brian Murray (brian-murray)]

python-psycopg2 is the more commonly used python module to connect to postgresql databases and I'd prefer we use that one although I don't think it'll make much difference.

review: Needs Fixing

[lp:~jibel/canonical-qa-tracking/isotesters_breakdown]

513. By Jean-Baptiste Lallement on 2011-04-11

* port to psycopg2.
* added port parameter
* pass variable using execute rather than string interpolation

[Jean-Baptiste Lallement (jibel)]

I also removed the miserable string interpolation to pass the argument to the query.

Unmerged revisions

513. By Jean-Baptiste Lallement on 2011-04-11

* port to psycopg2.
* added port parameter
* pass variable using execute rather than string interpolation

512. By Jean-Baptiste Lallement on 2011-04-07

* Added a script to report the repartition between Canonical vs Community
  for ISO Testing

=== modified file 'sru-tools/thankyou-testers'
--- sru-tools/thankyou-testers	2010-09-24 10:13:11 +0000
+++ sru-tools/thankyou-testers	2011-04-11 22:23:36 +0000
@@ -119,6 +119,8 @@
             xlpm_m = xlpm_pattern.search(xlpm_header)
             if xlpm_m:
                 modifier=xlpm_m.groupdict() # {display_name, lp_id}
+            else:
+                continue
             action_date = time.mktime(sent_date)
             try:
                 srubugs[bug_id].append((sender[0], sender[1], modifier, action_date))
@@ -218,6 +220,9 @@
                     person = lp.people[personId]
                 except KeyError:
                     continue
+                except AttributeError, e:
+                    print e
+                    continue
                 if person:
                     logging.debug("= Found: [%s]	%s" % (v[2]['id'],person.name))
                     people[person.name] =  {

=== added file 'statistic-scripts/isotesters_breakdown.py'
--- statistic-scripts/isotesters_breakdown.py	1970-01-01 00:00:00 +0000
+++ statistic-scripts/isotesters_breakdown.py	2011-04-11 22:23:36 +0000
@@ -0,0 +1,200 @@
+#!/usr/bin/python
+# Copyright 2011 Canonical, Ltd
+# Author: Jean-Baptiste Lallement <[e-mail]>
+#
+# Licensed under the GNU General Public License, version 3.
+#
+# This script retrieves the list of iso testers for a milestone and splits the
+# the results between tests done by Canonical vs Community for each flavor
+
+
+from launchpadlib.launchpad import Launchpad
+import logging
+import sys
+import psycopg2
+from optparse import OptionParser
+
+logging.basicConfig(level=logging.DEBUG)
+
+lp = None
+
+db_name="qatracker"
+
+def lp_init():
+    """ init the connection to lp"""
+    return Launchpad.login_with("lookup-canonical-employee", "production")
+
+def db_init(db_host, db_name, db_user, db_password, db_port = "5432"):
+    """ init the connection to the database"""
+    try:
+        db=psycopg2.connect(user=db_user, password=db_password, host=db_host,
+                            port=db_port, database=db_name)
+        cursor=db.cursor()
+    except:
+        sys.exit("Failed to connect to the DB")
+
+    return (db, cursor)
+
+
+def get_isotesters(cursor, milestone_title):
+    """ Returns the list of testers for a milestone corresponding to
+    the milestone_title.
+    Partial matching on the title is used to return more than one milestone
+
+    :param milestone_title: Title of the milestone following 'like' syntax
+
+    :return: List of testers
+    """
+
+    sql = """SELECT m.title
+,SUBSTRING(CASE WHEN p.title like 'Ubuntu Studio%%' then 'UbuntuStudio' ELSE p.title END FROM '^[^ ]*')
+,u.name
+,u.mail
+,s.value
+,COUNT('*')
+FROM qatracker_product p
+JOIN qatracker_build b ON b.productid = p.id
+JOIN qatracker_milestone m ON b.milestoneid = m.id
+JOIN qatracker_testcase t ON t.productid = p.id
+JOIN qatracker_result r ON ( r.testcaseid = t.id AND r.buildid = b.id )
+JOIN users u ON ( u.uid = r.reporterid )
+LEFT JOIN qawebsite_user_setting_info s ON ( s.userid = u.uid AND s.settingid = 1 )
+WHERE m.siteid = 1
+AND p.title not like 'Upgrade%%'
+AND p.title not like 'Generic%%'
+AND m.title ilike %s
+GROUP BY m.id, m.title, p.title, u.name, u.mail, s.value
+ORDER BY m.id,p.title"""
+
+    cursor.execute(sql, (milestone_title +'%',) )
+    return cursor.fetchall()
+
+def isCanonicalEmployee(person):
+    """ Return true if we think the person is a Canonical employee
+
+    We assume that any member of a canonical team is a canonical employee
+    """
+    canonical_teams = ('canonical', 'landscape')
+    for tm in person.memberships_details:
+        for ct in canonical_teams:
+            if ct in tm.team.name:
+                return True
+    return False
+
+def resolve_employees(testers):
+    """ Tries to guess if a user is a canonical employee """
+    global lp
+    result = []
+    users = {}
+    for row in testers:
+        #(milestone, distro, name, mail, lpid, testcount) = line.strip().split(';')
+        (milestone, distro, name, mail, lpid, testcount) = row
+        if name not in users:
+            users[name] = False
+            person = None
+            try:
+                person = lp.people[lpid]
+            except:
+                logging.debug("No person found with id [%s]" % lpid)
+
+            if not person:
+                try:
+                    persons = lp.people.findPerson(text = mail)
+                    person = persons[0]
+                    lpid = person.name
+                except:
+                    logging.debug("No person found with email [%s]" % mail)
+
+            if person:
+                users[name] = isCanonicalEmployee(person)
+
+        result.append((milestone, distro, name, mail, lpid, testcount, users[name]))
+    return result
+
+def print_report(testers):
+    """Print the report for a list of testers
+
+    :param testers: list of testers with employee status set
+    """
+
+    report = {}
+    distros = []
+
+    for row in testers:
+        (milestone, distro, name, mail, lpid, testcount, iscanonical) = row
+
+        if milestone not in report:
+            report[milestone] = {}
+
+        if not distro in distros:
+            distros.append(distro)
+
+        if not distro in report[milestone]:
+            report[milestone][distro] = [0,0]
+
+        if iscanonical:
+            report[milestone][distro][0] += int(testcount)
+        else:
+            report[milestone][distro][1] += int(testcount)
+
+
+    print "Milestone;%s;;;;;" % ";;;;;".join(sorted(distros))
+    print ";" + "Canonical;Community;Total;%Canonical;%Community;" * len(distro)
+
+    for milestone, row in sorted(report.iteritems()):
+        sys.stdout.write("%s;" % milestone)
+        for distro in sorted(distros):
+            try:
+                res = row[distro]
+                sys.stdout.write("%d;%d;%d;%.2f;%.2f;" % (res[0],
+                                                      res[1],
+                                                      res[0]+res[1],
+                                                      res[0]*100.0/(res[0]+res[1]),
+                                                      res[1]*100.0/(res[0]+res[1])))
+            except KeyError:
+                sys.stdout.write("0;" * 5)
+
+        print
+
+
+def main():
+    """ Main loop"""
+    global lp
+    global db_host, db_name, db_username, db_password
+
+    usage = "Usage: %prog [options] milestone_title"
+
+    parser = OptionParser(usage=usage)
+
+    parser.add_option('-U', '--username', dest="db_user" , help='database Username')
+    parser.add_option('-P', '--password', dest="db_password" , help='database Username')
+    parser.add_option('-H', '--host', dest="db_host" , help='database Username')
+    parser.add_option('-p', '--port', dest="db_port" , default="5432", help='database port')
+
+    (options, args) = parser.parse_args()
+
+    if len(args) != 1:
+        parser.error("Incorrect number of arguments")
+
+    milestone_title = args[0]
+
+    db_host = options.db_host
+    db_user= options.db_user
+    db_password = options.db_password
+    db_port = options.db_port
+
+
+    if not db_host or not db_user or not db_password:
+        parser.error('DB Hostname, username and password are mandatory')
+
+    lp = lp_init()
+    (db, cursor) = db_init(db_host, db_name, db_user, db_password, db_port)
+    testers = get_isotesters(cursor, milestone_title)
+    cursor.close()
+    db.close()
+
+    employees = resolve_employees(testers)
+    print_report(employees)
+
+if __name__ == "__main__":
+    main()