root/stats/trunk/feedtree_stats.py

import MySQLdb
from duration import *

DEFAULT_WINDOW = 3600 * 12 # 12 hours

def bytify(n):
    n = float(n)
    units=('B','K','M','G')
    u=0
    while n > 1024.0 and u < len(units)-1:
       u += 1
       n /= 1024.0
    return "%.2f%s" % (n, units[u])


def nodes(password, age=DEFAULT_WINDOW):
    db = MySQLdb.connect('stats.feedtree.net', 'feedtree', password)
    db.select_db('feedtree')
    cu = db.cursor()
    cu.execute('''
        select distinct nodeid from reports where NOW() - INTERVAL %d SECOND < ts 
        ;
    ''' % age);
    ids = [x[0] for x in cu.fetchall()]
    
    data = {}
    
    for i in ids:
        cu.execute('''
            select version,uptime,UNIX_TIMESTAMP(ts),memsize,ip,app,svn from reports where nodeid = '%s' order by ts desc limit 1
            ;
        ''' % i)
        r = cu.fetchone()
        
        ts = r[2]

        data[i] = {
            'nodeid': i,
            'version': r[0],
            'uptime': r[1]/1000,
            'upstr': duration(r[1]/1000, SHORT),
            'ts': ts,
            'memsize':r[3],
            'mem':bytify(r[3]),
            'ip': r[4],
            'app': r[5] or 'proxy',
            'svn': r[6] or '',
        }

        cu.execute('''
            select topic,entries,polling from feeds 
            where nodeid = '%s' and ts = FROM_UNIXTIME(%d)
            ;
        ''' % (i, ts))

        feeds = []

        for f in cu.fetchall():
            feeds.append({
                'topic': f[0],
                'entries': f[1],
                'polling': f[2]
            })

        data[i]['feeds'] = feeds

        scribes = {}
        
        cu.execute('''
            select topic,parent from scribes
            where nodeid = '%s' and ts = FROM_UNIXTIME(%d)
            ;
        ''' % (i, ts))

        for s in cu.fetchall():
            scribes[s[0]] = s[1]
        
        data[i]['scribes'] = scribes

    return data