import requests
from datetime import datetime
import mailbox
import re
import os
import tempfile
def get_done_date(bug_num):
    CACHE_DIR = os.path.expanduser("~") + "/.cache/rfs_bugs/"
    def get_from_cache():
        if os.path.exists(" ".format(CACHE_DIR, bug_num)):
            with open(" ".format(CACHE_DIR, bug_num)) as f:
                return datetime.strptime(f.readlines()[0].rstrip(), "%Y-%m-%d").date()
        else:
            return None
    done_date = get_from_cache()
    if done_date is not None:
        return done_date
    else:
        r = requests.get("https://bugs.debian.org/cgi-bin/bugreport.cgi?mbox=yes;bug= ;mboxstatus=yes".format(self._num))
        d = try_header(r.text)
        if d is None:
            d = try_cc(r.text)
        if d is None:
            d = try_body(r.text)
        if d is not None:
            with open(" ".format(CACHE_DIR, bug_num), "w") as f:
                f.write(" ".format(d.date()))
        else:
            return None
        return d.date()
    def try_body(text):
        reg = "\(at\s.+\)\s+by\sbugs\.debian\.org;\s(\d 1,2 \s\w\w\w\s\d\d\d\d)"
        handle, name = tempfile.mkstemp()
        with open(name, "w") as f:
            f.write(text.encode('latin-1'))
        mbox = mailbox.mbox(name)
        for i in mbox.items():
            if i[1].is_multipart():
                for m in i[1].get_payload():
                    if "close" in str(m) or "done" in str(m):
                        try:
                            result = re.search(reg, i[1]['Received'])
                            return datetime.strptime(result.group(1), "%d %b %Y")
                        except:
                            return None
            else:
                if "close" in i[1].get_payload() or "done" in i[1].get_payload():
                    try:
                        result = re.search(reg, i[1]['Received'])
                        return datetime.strptime(result.group(1), "%d %b %Y")
                    except:
                        return None
        return None
    def try_header(text):
        reg = "Received:\s\(at\s\d\d\d\d\d\d-(close done)\)\s+by.+"
        try:
            result = re.search(reg, r.text)
            line = result.group(0)
            reg2 = "\d 1,2 \s\w\w\w\s\d\d\d\d"
            result = re.search(reg2, line)
            d = datetime.strptime(result.group(0), "%d %b %Y")
            return d
        except:
            return None
    def try_cc(text):
        reg = "\(at\s.+\)\s+by\sbugs\.debian\.org;\s(\d 1,2 \s\w\w\w\s\d\d\d\d)"
        handle, name = tempfile.mkstemp()
        with open(name, "w") as f:
            f.write(text.encode('latin-1'))
        mbox = mailbox.mbox(name)
        for i in mbox.items():
            if ('CC' in i[1] and "done" in i[1]['CC']) or ('To' in i[1] and "done" in i[1]['To']):
                try:
                    result = re.search(reg, i[1]['Received'])
                    return datetime.strptime(result.group(1), "%d %b %Y")
                except:
                    return None
if __name__ == "__main__":
    print get_done_date(752210)

CREATE SERVER docker_containers FOREIGN DATA WRAPPER multicorn options (
    wrapper 'dockerfdw.wrappers.containers.ContainerFdw');
CREATE SERVER docker_image FOREIGN DATA WRAPPER multicorn options (
    wrapper 'dockerfdw.wrappers.images.ImageFdw');

CREATE foreign table docker_containers (
    "id"          TEXT,
    "image"       TEXT,
    "name"        TEXT,
    "names"       TEXT[],
    "privileged"  BOOLEAN,
    "ip"          TEXT,
    "bridge"      TEXT,
    "running"     BOOLEAN,
    "pid"         INT,
    "exit_code"   INT,
    "command"     TEXT[]
) server docker_containers options (
    host 'unix:///run/docker.sock'
);
CREATE foreign table docker_images (
    "id"              TEXT,
    "architecture"    TEXT,
    "author"          TEXT,
    "comment"         TEXT,
    "parent"          TEXT,
    "tags"            TEXT[]
) server docker_image options (
    host 'unix:///run/docker.sock'
);

SELECT docker_containers.ip, docker_containers.names, docker_images.tags
  FROM docker_containers
  RIGHT JOIN docker_images
  ON docker_containers.image=docker_images.id;

     ip                   names                               tags                   
-------------+-----------------------------+-----------------------------------------
                                              ruby:latest 
                                              paultag/vcs-mirror:latest 
                /de-openstates-to-ocd         sunlightlabs/scrapers-us-state:latest 
                /ny-openstates-to-ocd         sunlightlabs/scrapers-us-state:latest 
                /ar-openstates-to-ocd         sunlightlabs/scrapers-us-state:latest 
 172.17.0.47    /ms-openstates-to-ocd         sunlightlabs/scrapers-us-state:latest 
 172.17.0.46    /nc-openstates-to-ocd         sunlightlabs/scrapers-us-state:latest 
                /ia-openstates-to-ocd         sunlightlabs/scrapers-us-state:latest 
                /az-openstates-to-ocd         sunlightlabs/scrapers-us-state:latest 
                /oh-openstates-to-ocd         sunlightlabs/scrapers-us-state:latest 
                /va-openstates-to-ocd         sunlightlabs/scrapers-us-state:latest 
 172.17.0.41    /wa-openstates-to-ocd         sunlightlabs/scrapers-us-state:latest 
                /jovial_poincare              <none>:<none> 
                /jolly_goldstine              <none>:<none> 
                /cranky_torvalds              <none>:<none> 
                /backstabbing_wilson          <none>:<none> 
                /desperate_hoover             <none>:<none> 
                /backstabbing_ardinghelli     <none>:<none> 
                /cocky_feynman                <none>:<none> 
                                              paultag/postgres:latest 
                                              debian:testing 
                                              paultag/crank:latest 
                                              <none>:<none> 
                                              <none>:<none> 
                /stupefied_fermat             hackerschool/doorbot:latest 
                /focused_euclid               debian:unstable 
                /focused_babbage              debian:unstable 
                /clever_torvalds              debian:unstable 
                /stoic_tesla                  debian:unstable 
                /evil_torvalds                debian:unstable 
                /foo                          debian:unstable 
(31 rows)

DELETE FROM docker_containers;

DELETE 1

INSERT INTO docker_containers (name, image) VALUES ('hello', 'debian:unstable') RETURNING id;

                                id                                
------------------------------------------------------------------
 0a903dcf5ae10ee1923064e25ab0f46e0debd513f54860beb44b2a187643ff05
INSERT 0 1
(1 row)

SELECT ip FROM docker_containers WHERE id='0a903dcf5ae10ee1923064e25ab0f46e0debd513f54860beb44b2a187643ff05';

     ip      
-------------
 172.17.0.12
(1 row)

Age  Dust Number  Title
37   0    757966  RFS: lutris/0.3.5-1 [ITP]
1    0    762015  RFS: s3fs-fuse/1.78-1 [ITP #601789] -- FUSE-based file system backed by Amazon S3
81   0    753110  RFS: mrrescue/1.02c-1 [ITP]
456  0    712787  RFS: distkeys/1.0-1 [ITP] -- distribute SSH keys
120  1    748878  RFS: mwc/1.7.2-1 [ITP] -- Powerful website-tracking tool
1    1    762012  RFS: fadecut/0.1.4-1
3    1    761687  RFS: abraca/0.8.0+dfsg-1 -- Simple and powerful graphical client for XMMS2
35   2    758163  RFS: kcm-ufw/0.4.3-1 ITP
3    2    761636  RFS: raceintospace/1.1+dfsg1-1 [ITP]
....
....

#!/usr/bin/python
import SOAPpy
import time
from datetime import date, timedelta, datetime
url = 'http://bugs.debian.org/cgi-bin/soap.cgi'
namespace = 'Debbugs/SOAP'
server = SOAPpy.SOAPProxy(url, namespace)
class RFS(object):
    def __init__(self, obj):
        self._obj = obj
        self._last_modified = date.fromtimestamp(obj.log_modified)
        self._date = date.fromtimestamp(obj.date)
        if self._obj.pending != 'done':
            self._pending = "pending"
            self._dust = abs(date.today() - self._last_modified).days
        else:
            self._pending = "done"
            self._dust = abs(self._date - self._last_modified).days
        today = date.today()
        self._age = abs(today - self._date).days
    @property
    def status(self):
        return self._pending
    @property
    def date(self):
        return self._date
    @property
    def last_modified(self):
        return self._last_modified
    @property
    def subject(self):
        return self._obj.subject
    @property
    def bug_number(self):
        return self._obj.bug_num
    @property
    def age(self):
        return self._age
    @property
    def dust(self):
        return self._dust
    def __str__(self):
        return "  subject:   age:  dust: ".format(self._obj.bug_num, self._obj.subject, self._age, self._dust)
if __name__ == "__main__":
    bugi = server.get_bugs("package", "sponsorship-requests", "status", "open")
    buglist = [RFS(b.value) for b in server.get_status(bugi).item]
    buglist_sorted_by_dust = sorted(buglist, key=lambda x: x.dust, reverse=False)
    print("Age  Dust Number  Title")
    for i in buglist_sorted_by_dust:
        print(" :<4   :<4   :<7   ".format(i.age, i.dust, i.bug_number, i.subject))