udd/udd/popcon_gatherer.py

#!/usr/bin/env python

"""
This script imports the popcon data into the database
See http://popcon.debian.org/
"""

import aux
import sys
import gzip
from gatherer import gatherer
import re

def get_gatherer(connection, config, source):
  return popcon_gatherer(connection, config, source)

class popcon_gatherer(gatherer):
  def __init__(self, connection, config, source):
    gatherer.__init__(self, connection, config, source)

    self.assert_my_config('path', 'table', 'packages-table', 'schema')

  def tables(self):
    ret = []
    for sub in ('', '_src', '_src_average'):
      ret.append(self.my_config['table'] + sub)
    return ret

  def run(self):
    my_config = self.my_config

    table = my_config['table']
    table_src = table + "_src"
    table_src_average = table + "_src_average"

    cur = self.cursor()

    cur.execute("PREPARE pop_insert AS INSERT INTO %s (package, insts, vote, olde, recent, nofiles) VALUES ($1, $2, $3, $4, $5, $6)" % (table))

    popcon = gzip.open(my_config['path'])

    cur.execute("DELETE FROM " + table)
    cur.execute("DELETE FROM " + table_src)
    cur.execute("DELETE FROM " + table_src_average)

    # used for ignoring ubuntu's broken popcon lines
    ascii_match = re.compile("^[A-Za-z0-9-.+_]+$")

    linenr = 0
    d = {}
    for line in popcon:
      linenr += 1
      name, data = line.split(None, 1)
      if name == "Submissions:":
        d['data'] = int(data)
        cur.execute("INSERT INTO " + table + " (package, vote) VALUES ('_submissions', %(data)s)", d)
        continue
      try:
        (name, vote, old, recent, nofiles) = data.split()
        d['name'] = name
        for k in ['vote', 'old', 'recent', 'nofiles']:
          exec '%s = int(%s)' % (k,k)
          exec 'd["%s"] = %s' % (k,k)
        d['insts'] = vote + old + recent + nofiles
        if ascii_match.match(name) == None:
#         print "%s:%d - illegal package name %s" % (my_config['path'], linenr, line)
          continue
        query = "EXECUTE pop_insert(%(name)s, %(insts)s, %(vote)s, %(old)s, %(recent)s, %(nofiles)s)"
        cur.execute(query, d)
      except ValueError:
        continue

    cur.execute("DEALLOCATE pop_insert")

    #calculate _src and _src_avg
    cur.execute("""
    INSERT INTO %(table)s_src (source, insts, vote, olde, recent, nofiles)
      SELECT DISTINCT pkgs.source, max(insts) AS insts, max(vote) AS vote,
        max(olde) AS old, max(recent) AS recent, max(nofiles) as nofiles
      FROM %(table)s, %(packages-table)s_summary AS pkgs
      WHERE %(table)s.package = pkgs.package
      GROUP BY pkgs.source;
      """ % my_config)
    cur.execute("""
    INSERT INTO %(table)s_src_average (source, insts, vote, olde, recent,
        nofiles)
      SELECT pkgs.source, avg(insts) AS insts, avg(vote) AS vote,
        avg(olde) AS old, avg(recent) AS recent, avg(nofiles) as nofiles
      FROM %(table)s, %(packages-table)s_summary AS pkgs
      WHERE %(table)s.package = pkgs.package
      GROUP BY pkgs.source;
      """ % my_config)

if __name__ == '__main__':
  main()
1	neronus-guest	900	#!/usr/bin/env python
2
3			"""
4			This script imports the popcon data into the database
5			See http://popcon.debian.org/
6			"""
7
8	neronus-guest	895	import aux
9			import sys
10			import gzip
11	neronus-guest	902	from gatherer import gatherer
12	neronus-guest	951	import re
13	neronus-guest	895
14	neronus-guest	1068	def get_gatherer(connection, config, source):
15			return popcon_gatherer(connection, config, source)
16	neronus-guest	895
17	neronus-guest	902	class popcon_gatherer(gatherer):
18	neronus-guest	1068	def __init__(self, connection, config, source):
19			gatherer.__init__(self, connection, config, source)
20	neronus-guest	895
21	neronus-guest	1068	self.assert_my_config('path', 'table', 'packages-table', 'schema')
22	neronus-guest	895
23	neronus-guest	1106	def tables(self):
24			ret = []
25	neronus-guest	1068	for sub in ('', '_src', '_src_average'):
26	neronus-guest	1106	ret.append(self.my_config['table'] + sub)
27			return ret
28	neronus-guest	895
29	neronus-guest	1068	def run(self):
30			my_config = self.my_config
31
32	neronus-guest	998	table = my_config['table']
33	lucas	1019	table_src = table + "_src"
34	neronus-guest	998	table_src_average = table + "_src_average"
35
36	neronus-guest	902	cur = self.cursor()
37	neronus-guest	895
38	neronus-guest	998	cur.execute("PREPARE pop_insert AS INSERT INTO %s (package, insts, vote, olde, recent, nofiles) VALUES ($1, $2, $3, $4, $5, $6)" % (table))
39	neronus-guest	895
40	neronus-guest	902	popcon = gzip.open(my_config['path'])
41	neronus-guest	895
42	neronus-guest	998	cur.execute("DELETE FROM " + table)
43	lucas	1019	cur.execute("DELETE FROM " + table_src)
44	neronus-guest	998	cur.execute("DELETE FROM " + table_src_average)
45	neronus-guest	903
46	neronus-guest	951	# used for ignoring ubuntu's broken popcon lines
47			ascii_match = re.compile("^[A-Za-z0-9-.+_]+$")
48
49			linenr = 0
50	neronus-guest	1038	d = {}
51	neronus-guest	1036	for line in popcon:
52	neronus-guest	951	linenr += 1
53	neronus-guest	902	name, data = line.split(None, 1)
54			if name == "Submissions:":
55	neronus-guest	1038	d['data'] = int(data)
56			cur.execute("INSERT INTO " + table + " (package, vote) VALUES ('_submissions', %(data)s)", d)
57			continue
58	neronus-guest	902	try:
59			(name, vote, old, recent, nofiles) = data.split()
60	neronus-guest	1038	d['name'] = name
61			for k in ['vote', 'old', 'recent', 'nofiles']:
62			exec '%s = int(%s)' % (k,k)
63			exec 'd["%s"] = %s' % (k,k)
64			d['insts'] = vote + old + recent + nofiles
65	neronus-guest	951	if ascii_match.match(name) == None:
66	lucas	1251	# print "%s:%d - illegal package name %s" % (my_config['path'], linenr, line)
67	neronus-guest	957	continue
68	neronus-guest	1038	query = "EXECUTE pop_insert(%(name)s, %(insts)s, %(vote)s, %(old)s, %(recent)s, %(nofiles)s)"
69			cur.execute(query, d)
70	neronus-guest	902	except ValueError:
71			continue
72	neronus-guest	895
73	neronus-guest	902	cur.execute("DEALLOCATE pop_insert")
74	neronus-guest	895
75	lucas	1019	#calculate _src and _src_avg
76	neronus-guest	998	cur.execute("""
77	kroeckx	1264	INSERT INTO %(table)s_src (source, insts, vote, olde, recent, nofiles)
78			SELECT DISTINCT pkgs.source, max(insts) AS insts, max(vote) AS vote,
79			max(olde) AS old, max(recent) AS recent, max(nofiles) as nofiles
80	lucas	1064	FROM %(table)s, %(packages-table)s_summary AS pkgs
81			WHERE %(table)s.package = pkgs.package
82			GROUP BY pkgs.source;
83	lucas	1111	""" % my_config)
84	neronus-guest	998	cur.execute("""
85	kroeckx	1264	INSERT INTO %(table)s_src_average (source, insts, vote, olde, recent,
86			nofiles)
87			SELECT pkgs.source, avg(insts) AS insts, avg(vote) AS vote,
88			avg(olde) AS old, avg(recent) AS recent, avg(nofiles) as nofiles
89	lucas	1064	FROM %(table)s, %(packages-table)s_summary AS pkgs
90			WHERE %(table)s.package = pkgs.package
91			GROUP BY pkgs.source;
92	lucas	1111	""" % my_config)
93	neronus-guest	998
94	neronus-guest	895	if __name__ == '__main__':
95			main()