source: trunk/bin/graph @ 15

#!/usr/bin/env python3

import sys
import os
import time
import random
import tempfile
import argparse
import socket
import json

import numpy
import matplotlib.mlab as mlab
import matplotlib.pyplot as plt


VERSION = "{DEVELOPMENT}"
if VERSION == "{DEVELOPMENT}":
    script_dir = '.'
    try:
        script_dir = os.path.dirname(os.path.realpath(__file__))
    except:
        try:
            script_dir = os.path.dirname(os.path.abspath(sys.argv[0]))
        except:
            pass
    sys.path.append("%s/../lib" % script_dir)

from nanownlib import *
from nanownlib.stats import *
import nanownlib.storage


parser = argparse.ArgumentParser(
    description="")
parser.add_argument('db_file', default=None,
                    help='')
options = parser.parse_args()
db = nanownlib.storage.db(options.db_file)


def differences(db, unusual_case, rtt_type='packet'):
    ret_val = [s['unusual_'+rtt_type]-s['other_'+rtt_type] for s in db.subseries('train', unusual_case)]
    ret_val += [s['unusual_'+rtt_type]-s['other_'+rtt_type] for s in db.subseries('test', unusual_case)]
    return ret_val

def null_differences(db, unusual_case, rtt_type='packet'):
    ret_val = [s['unusual_'+rtt_type]-s['other_'+rtt_type] for s in db.subseries('train_null', unusual_case)]
    return ret_val


def timeSeries(db, probe_type, unusual_case):
    cursor = db.conn.cursor()
    query="""
      SELECT time_of_day,packet_rtt AS uc,(SELECT avg(packet_rtt) FROM probes,analysis
                                           WHERE analysis.probe_id=probes.id AND probes.test_case!=:unusual_case AND probes.type=:probe_type AND sample=u.sample) AS oc
      FROM (SELECT time_of_day,probes.sample,packet_rtt FROM probes,analysis 
                                           WHERE analysis.probe_id=probes.id AND probes.test_case =:unusual_case AND probes.type=:probe_type) u
    """
    
    params = {"probe_type":probe_type,"unusual_case":unusual_case}
    cursor.execute(query, params)
    for row in cursor:
        yield {'time_of_day':row['time_of_day'],unusual_case:row['uc'],'other_cases':row['oc']}
#samples,derived,null_derived = parse_data(input1)

#trust = trustValues(derived, sum)
#weights = linearWeights(derived, trust, 0.25)
#print('(test): %f' % weightedMean(derived,weights))

diffs = list(differences(db, 'long'))
reported_diffs = list(differences(db, 'long', 'reported'))
#shorts = [s['packet_rtt'] for s in samples.values() if s['test_case']=='short']
#longs = [s['packet_rtt'] for s in samples.values() if s['test_case']=='long']


def basicStatistics():
    print('packet_rtt diff mean: %f' % statistics.mean(diffs))
    print('packet_rtt diff median: %f' % statistics.median(diffs))
    print('packet_rtt diff midhinge: %f' % midsummary(diffs))
    print('packet_rtt diff trimean: %f' % trimean(diffs))
    print('packet_rtt diff quadsummary: %f' % quadsummary(diffs))
    print('packet_rtt diff ubersummary: %f' % ubersummary(diffs))
    print('packet_rtt diff septasummary: %f' % septasummary(diffs))
    print('packet_rtt diff MAD: %f' % mad(diffs))
    try:
        print('reported diff trimean: %f' % trimean(reported_diffs))
        print('reported diff quadsummary: %f' % quadsummary(reported_diffs))
        print('reported diff ubersummary: %f' % ubersummary(reported_diffs))
        print('reported diff septasummary: %f' % septasummary(reported_diffs))
        print('reported diff MAD: %f' % mad(reported_diffs))

        #import cProfile
        #start = time.time()
        #kresults = kfilter({},diffs)
        #print('packet_rtt diff kfilter: ', numpy.mean(kresults['est']), kresults['var'])
        #print('packet_rtt diff kfilter: ', kresults['est'][-1], kresults['var'][-1])
        #kresults = kfilter({},reported_diffs)
        #print('reported diff kfilter: ', numpy.mean(kresults['est']), kresults['var'][-1])
        #print('reported diff kfilter: ', kresults['est'][-1], kresults['var'][-1])
        #print("kfilter time: %f" % (time.time()-start))
    except:
        pass

    #print('tsval diff mean: %f' % numpy.mean(differences(db, 'long', 'tsval')))
    #print('tsval null diff mean: %f' % numpy.mean(null_differences(db, 'long', 'tsval')))
    #print('tsval diff weighted mean: %f' % tsvalwmean(db.subseries('train','long')+db.subseries('test','long')))
    #print('tsval null diff weighted mean: %f' % tsvalwmean(db.subseries('train_null','long')))



def exampleBoxTestHistogram(low,high):
    num_bins = 300
    all = db.subseries('train','long')+db.subseries('test','long')
    s   = [s['other_packet'] for s in all]
    l   = [s['unusual_packet'] for s in all]

    s_low,s_high = numpy.percentile(s, (low,high))
    l_low,l_high = numpy.percentile(l, (low,high))

    s.sort()
    cut_off_low = s[int(len(diffs)*0.002)]
    cut_off_high = s[int(len(diffs)*0.998)]
    
    plt.clf()
    # the histogram of the data
    #n, bins, patches = plt.hist(s, num_bins, normed=1, color='blue', histtype='step', alpha=0.8,
    #                            label='Test Case 1')
    #n, bins, patches = plt.hist(l, num_bins, normed=1, color='red', histtype='step', alpha=0.8,
    #                            label='Test Case 2')
    #
    n, bins, patches = plt.hist((s,l), num_bins, normed=1, color=('blue','red'), histtype='step', alpha=0.8,
                                 label=('Test Case 1','Test Case 2'), range=(cut_off_low,cut_off_high))

    from matplotlib.patches import FancyBboxPatch
    currentAxis = plt.gca()
    currentAxis.add_patch(FancyBboxPatch((s_low, 0), s_high-s_low, 0.0001, boxstyle='square', facecolor="blue", alpha=0.4))
    currentAxis.add_patch(FancyBboxPatch((l_low, 0), l_high-l_low, 0.0001, boxstyle='square', facecolor="red", alpha=0.4))

    
    plt.xlabel('RTT Difference')
    plt.ylabel('Probability')
    #plt.title(r'Box Test Example - Overlapping Boxes')

    # Tweak spacing to prevent clipping of ylabel
    plt.subplots_adjust(left=0.15)
    plt.legend()
    plt.show()
    #plt.savefig('paper/graphs/dists-vs-dist-of-diffs2.svg')


#exampleBoxTestHistogram(6,8)


def testKalman4D(params=None):
    from pykalman import KalmanFilter
    train = db.subseries('train','long', offset=0)
    test = db.subseries('test','long', offset=0)
    null = db.subseries('train_null','long', offset=0)
    measurements = numpy.asarray([(s['unusual_packet'],s['other_packet'],s['unusual_tsval'],s['other_tsval']) for s in (train+test)])
    null_measurements = numpy.asarray([(s['unusual_packet'],s['other_packet'],s['unusual_tsval'],s['other_tsval']) for s in null])
    
    if params == None:
        kf = KalmanFilter(n_dim_obs=4, n_dim_state=4,
                          initial_state_mean=[quadsummary([s['unusual_packet'] for s in train]),
                                              quadsummary([s['other_packet'] for s in train]),
                                              numpy.mean([s['unusual_tsval'] for s in train]),
                                              numpy.mean([s['other_tsval'] for s in train])])
        kf = KalmanFilter(n_dim_obs=4, n_dim_state=4)
        
        start=time.time()
        kf = kf.em(measurements[0:len(train)]+null_measurements[0:50000], n_iter=10,
                   em_vars=('transition_matrices',
                            'observation_matrices',
                            'transition_offsets',
                            'observation_offsets',
                            'transition_covariance',
                            'observation_covariance',
                            'initial_state_mean',
                            'initial_state_covariance'))
        params = {'transition_matrices': kf.transition_matrices.tolist(),
                  'observation_matrices': kf.observation_matrices.tolist(),
                  'transition_offsets': kf.transition_offsets.tolist(),
                  'observation_offsets': kf.observation_offsets.tolist(),
                  'transition_covariance': kf.transition_covariance.tolist(),
                  'observation_covariance': kf.observation_covariance.tolist(),
                  'initial_state_mean': kf.initial_state_mean.tolist(),
                  'initial_state_covariance': kf.initial_state_covariance.tolist()}
        print("Learned Params:\n")
        import pprint
        pprint.pprint(params)
        print("pykalman em time: %f" % (time.time()-start))
        
    #kf = KalmanFilter(n_dim_obs=2, n_dim_state=2, **params)

    num_obs=5000
    for offset in range(50000,100000+num_obs,num_obs):
        start=time.time()
        m = measurements[offset:offset+num_obs]
        #params['initial_state_mean']=[quadsummary([s[0] for s in m]),
        #                              quadsummary([s[1] for s in m]),
        #                              numpy.mean([s[2] for s in m]),
        #                              numpy.mean([s[3] for s in m])]
        kf = KalmanFilter(n_dim_obs=4, n_dim_state=4, **params)
        (smoothed_state_means, smoothed_state_covariances) = kf.smooth(m)
        #print("pykalman smooth time: %f" % (time.time()-start))
        up = numpy.mean([m[0] for m in smoothed_state_means])
        op = numpy.mean([m[1] for m in smoothed_state_means])
        #print("packet_rtt pykalman final:", smoothed_state_means[-1][0]-smoothed_state_means[-1][1])
        print("packet_rtt pykalman mean:", up-op)
        print("packet_rtt mean:", numpy.mean([s[0]-s[1] for s in m]))
        #up = numpy.mean([m[2] for m in smoothed_state_means])
        #op = numpy.mean([m[3] for m in smoothed_state_means])
        #print("tsval_rtt pykalman final:", smoothed_state_means[-1][2]-smoothed_state_means[-1][3])
        #print("tsval_rtt pykalman mean:", up-op)
        #print("tsval_rtt mean:", numpy.mean([s[2]-s[3] for s in m]))

    for offset in range(0,len(null_measurements)+num_obs,num_obs):
        start=time.time()
        m = null_measurements[offset:offset+num_obs]
        #params['initial_state_mean']=[quadsummary([s[0] for s in m]),
        #                              quadsummary([s[1] for s in m]),
        #                              numpy.mean([s[2] for s in m]),
        #                              numpy.mean([s[3] for s in m])]
        kf = KalmanFilter(n_dim_obs=4, n_dim_state=4, **params)
        (smoothed_state_means, smoothed_state_covariances) = kf.smooth(m)
        up = numpy.mean([m[0] for m in smoothed_state_means])
        op = numpy.mean([m[1] for m in smoothed_state_means])
        #print("null packet_rtt pykalman final:", smoothed_state_means[-1][0]-smoothed_state_means[-1][1])
        print("null packet_rtt pykalman mean:", up-op)
        print("null packet_rtt mean:", numpy.mean([s[0]-s[1] for s in m]))
        #up = numpy.mean([m[2] for m in smoothed_state_means])
        #op = numpy.mean([m[3] for m in smoothed_state_means])
        #print("null tsval_rtt pykalman final:", smoothed_state_means[-1][2]-smoothed_state_means[-1][3])
        #print("null tsval_rtt pykalman mean:", up-op)
        #print("null tsval_rtt mean:", numpy.mean([s[2]-s[3] for s in m]))

        
#testKalman4D(echo_vm_5k)



def testKalman(params=None):
    from pykalman import AdditiveUnscentedKalmanFilter,KalmanFilter
    train = db.subseries('train','long', offset=0)
    test = db.subseries('test','long', offset=0)
    measurements = numpy.asarray([(s['unusual_packet'],s['other_packet']) for s in (train+test)])

    #kf = KalmanFilter(transition_matrices = [[1, 1], [0, 1]], observation_matrices = [[0.1, 0.5], [-0.3, 0.0]])
    kf = KalmanFilter(n_dim_obs=2, n_dim_state=2,
                      initial_state_mean=[quadsummary([s['unusual_packet'] for s in train]),
                                          quadsummary([s['other_packet'] for s in train])])
    #kf = AdditiveUnscentedKalmanFilter(n_dim_obs=2, n_dim_state=2)

    if params == None:
        start=time.time()
        kf = kf.em(measurements[0:len(train)], n_iter=10,
                   em_vars=('transition_matrices',
                            'observation_matrices',
                            'transition_offsets',
                            'observation_offsets',
                            'transition_covariance',
                            'observation_covariance',
                            'initial_state_covariance'))
        params = {'transition_matrices': kf.transition_matrices.tolist(),
                  'observation_matrices': kf.observation_matrices.tolist(),
                  'transition_offsets': kf.transition_offsets.tolist(),
                  'observation_offsets': kf.observation_offsets.tolist(),
                  'transition_covariance': kf.transition_covariance.tolist(),
                  'observation_covariance': kf.observation_covariance.tolist(),
                  'initial_state_mean': kf.initial_state_mean.tolist(),
                  'initial_state_covariance': kf.initial_state_covariance.tolist()}
        print("Learned Params:\n")
        import pprint
        pprint.pprint(params)
        print("pykalman em time: %f" % (time.time()-start))
        
    #kf = KalmanFilter(n_dim_obs=2, n_dim_state=2, **params)

    num_obs=10000
    for offset in range(50000,100000+num_obs,num_obs):
        start=time.time()
        kf = KalmanFilter(n_dim_obs=2, n_dim_state=2, **params)
        m = measurements[offset:offset+num_obs]
        (smoothed_state_means, smoothed_state_covariances) = kf.smooth(m)
        print("pykalman smooth time: %f" % (time.time()-start))
        up = numpy.mean([m[0] for m in smoothed_state_means])
        op = numpy.mean([m[1] for m in smoothed_state_means])
        print("packet_rtt pykalman final:", smoothed_state_means[-1][0]-smoothed_state_means[-1][1])
        print("packet_rtt pykalman mean:", up-op)
        print("packet_rtt mean:", numpy.mean([s[0]-s[1] for s in m]))


#testKalman(ten_iter)


def getTCPTSPrecision():
    cursor = db.conn.cursor()
    query="""SELECT tcpts_mean FROM meta"""
    cursor.execute(query)
    row = cursor.fetchone()
    if row:
        return row[0]
    return None


def tsFilteredHistogram():
    tcpts_precision = getTCPTSPrecision()
    
    num_bins = 500
    all = db.subseries('train','long')+db.subseries('test','long')
    diffs     = [s['unusual_packet']-s['other_packet'] for s in all]
    ts0_diffs = [s['unusual_packet']-s['other_packet'] for s in all if s['unusual_tsval']-s['other_tsval'] == 0]
    #ts1_diffs = [s['unusual_packet']-s['other_packet'] for s in all if abs(s['unusual_tsval']-s['other_tsval']) > 0]
    #ts2_diffs = [s['unusual_packet']-s['other_packet'] for s in all if abs(round((s['unusual_tsval']-s['other_tsval'])/tcpts_precision)) <= 1.0]
    ts1_diffs = [s['unusual_packet']-s['other_packet'] for s in all if abs(int(round((s['unusual_tsval']-s['other_tsval'])/tcpts_precision))) == 1]
    ts2_diffs = [s['unusual_packet']-s['other_packet'] for s in all if abs(int(round((s['unusual_tsval']-s['other_tsval'])/tcpts_precision))) >= 2]
    #ts3_diffs = [s['unusual_packet']-s['other_packet'] for s in all if abs(int(round((s['unusual_tsval']-s['other_tsval'])/tcpts_precision))) == 3]
    #ts4_diffs = [s['unusual_packet']-s['other_packet'] for s in all if abs(int(round((s['unusual_tsval']-s['other_tsval'])/tcpts_precision))) == 4]

    #ts_mode = statistics.mode([s['unusual_tsval'] for s in all]+[s['other_tsval'] for s in all])
    #ts_diff_mode = statistics.mode([s['unusual_tsval']-s['other_tsval'] for s in all])
    #ts_common_mode = [s['unusual_packet']-s['other_packet'] for s in all if s['unusual_tsval']<=ts_mode and s['other_tsval']<=ts_mode]
    #ts_common_diff_mode = [s['unusual_packet']-s['other_packet'] for s in all if s['unusual_tsval']-s['other_tsval']==ts_diff_mode]

    #print('packet_rtt diff quadsummary: %f' % quadsummary(diffs))
    #print('packet_rtt tsval diff=0 quadsummary: %f' % quadsummary(ts0_diffs))
    #print('packet_rtt tsval diff>0 quadsummary: %f' % quadsummary(ts1_diffs))
    #print('packet_rtt tsval diff<=1 quadsummary: %f' % quadsummary(ts2_diffs))
    #print('packet_rtt tsval mode quadsummary: %f' % quadsummary(ts_common_mode))
    #print(len(diffs), len(ts0_diffs)+len(ts1_diffs))
    diffs.sort()
    cut_off_low = diffs[int(len(diffs)*0.008)]
    cut_off_high = diffs[int(len(diffs)*0.992)]

    plt.clf()
    # the histogram of the data
    n, bins, patches = plt.hist(diffs, num_bins, normed=0, color='black', histtype='step', alpha=0.8,
                                range=(cut_off_low,cut_off_high), label='All Packets')
    n, bins, patches = plt.hist(ts0_diffs, num_bins, normed=0, color='blue', histtype='step', alpha=0.8,
                                range=(cut_off_low,cut_off_high), label='TSval Difference == 0')
    n, bins, patches = plt.hist(ts1_diffs, num_bins, normed=0, color='orange', histtype='step', alpha=0.8,
                                range=(cut_off_low,cut_off_high), label='TSval Difference == 1')
    n, bins, patches = plt.hist(ts2_diffs, num_bins, normed=0, color='red', histtype='step', alpha=0.8,
                                range=(cut_off_low,cut_off_high), label='TSval Difference >= 2')
    #n, bins, patches = plt.hist(ts3_diffs, num_bins, normed=0, color='red', histtype='step', alpha=0.8,
    #                            range=(cut_off_low,cut_off_high), label='tsval diff == 3')
    #n, bins, patches = plt.hist(ts4_diffs, num_bins, normed=0, color='brown', histtype='step', alpha=0.8,
    #                            range=(cut_off_low,cut_off_high), label='tsval diff == 4')
    #n, bins, patches = plt.hist(ts_common_mode, num_bins, normed=0, color='green', histtype='step', alpha=0.8,
    #                            range=(cut_off_low,cut_off_high), label='tsval common mode')
    #n, bins, patches = plt.hist(ts_common_diff_mode, num_bins, normed=0, color='green', histtype='step', alpha=0.8,
    #                            range=(cut_off_low,cut_off_high), label='tsval common diff mode')
    plt.xlabel('RTT Difference')
    #plt.ylabel('Probability')
    #plt.title(r'Histogram - distribution of differences by tsval')

    # Tweak spacing to prevent clipping of ylabel
    plt.subplots_adjust(left=0.15)
    plt.legend()
    plt.show()
    #plt.savefig('paper/graphs/dists-vs-dist-of-diffs2.svg')

#tsFilteredHistogram()


def exampleSummaryHistogram():
    num_bins = 300
    all = db.subseries('train','long')+db.subseries('test','long')
    diffs     = [s['unusual_packet']-s['other_packet'] for s in all]

    diffs.sort()
    cut_off_low = diffs[int(len(diffs)*0.003)]
    cut_off_high = diffs[int(len(diffs)*0.997)]

    plt.clf()
    # the histogram of the data
    n, bins, patches = plt.hist(diffs, num_bins, normed=0, color='black', histtype='step', alpha=0.8,
                                range=(cut_off_low,cut_off_high), label='all')

    plt.xlabel('RTT Difference')
    plt.ylabel('Probability')
    #plt.title(r'Histogram - distribution of differences by tsval')

    w = 25
    l1,r1,l2,r2,l3,r3 = numpy.percentile(diffs, (50-w,50+w,50-w/2,50+w/2,(50-w)/2,(50+w)/2+50))
    #plt.plot([l1, 0], [l1, 0.0001], "k--")
    #plt.plot([r1, 0], [r1, 0.0001], "k--")
    from matplotlib.patches import FancyBboxPatch
    currentAxis = plt.gca()
    currentAxis.add_patch(FancyBboxPatch((l1, 0), 2500, 5000, boxstyle='square', facecolor="blue", alpha=0.4, edgecolor='none'))
    currentAxis.add_patch(FancyBboxPatch((r1, 0), 2500, 5000, boxstyle='square', facecolor="blue", alpha=0.4, edgecolor='none'))
    currentAxis.add_patch(FancyBboxPatch((l2, 0), 2500, 5000, boxstyle='square', facecolor="green", alpha=0.4, edgecolor='none'))
    currentAxis.add_patch(FancyBboxPatch((r2, 0), 2500, 5000, boxstyle='square', facecolor="green", alpha=0.4, edgecolor='none'))
    currentAxis.add_patch(FancyBboxPatch((l3, 0), 2500, 5000, boxstyle='square', facecolor="green", alpha=0.4, edgecolor='none'))
    currentAxis.add_patch(FancyBboxPatch((r3, 0), 2500, 5000, boxstyle='square', facecolor="green", alpha=0.4, edgecolor='none'))
    currentAxis.add_patch(FancyBboxPatch((50, 0), 2500, 5000, boxstyle='square', facecolor="black", alpha=0.4, edgecolor='none'))
    currentAxis.add_patch(FancyBboxPatch((numpy.mean((l1,r1,l2,r2)), 0), 2500, 5000, boxstyle='square', facecolor="red", alpha=0.4, edgecolor='none'))
    #currentAxis.add_patch(FancyBboxPatch((numpy.mean((1000)), 0), 1500, 5000, boxstyle='square', facecolor="black", alpha=0.4, edgecolor='none'))

    # Tweak spacing to prevent clipping of ylabel
    plt.subplots_adjust(left=0.15)
    #plt.legend()
    plt.show()
    #plt.savefig('paper/graphs/dists-vs-dist-of-diffs2.svg')

#exampleSummaryHistogram()



#all_data = longs+shorts
#all_data.sort()
#cut_off_low = all_data[0]
#cut_off_high = all_data[int(len(all_data)*0.997)]


def plotSingleProbe(probe_id=None):
    if probe_id == None:
        cursor = db.conn.cursor()
        query="""SELECT probe_id FROM analysis WHERE suspect='' ORDER BY probe_id DESC limit 1 OFFSET 10"""
        cursor.execute(query)
        probe_id = cursor.fetchone()[0]
    
    cursor = db.conn.cursor()
    query="""SELECT observed,payload_len FROM packets WHERE probe_id=? AND sent=1"""
    cursor.execute(query, (probe_id,))
    pkts = cursor.fetchall()
    sent_payload = [row[0] for row in pkts if row[1] != 0]
    sent_other = [row[0] for row in pkts if row[1] == 0]
    
    query="""SELECT observed,payload_len FROM packets WHERE probe_id=? AND sent=0"""
    cursor.execute(query, (probe_id,))
    pkts = cursor.fetchall()
    rcvd_payload = [row[0] for row in pkts if row[1] != 0]
    rcvd_other = [row[0] for row in pkts if row[1] == 0]
    
    #query="""SELECT reported,time_of_day FROM probes WHERE id=?"""
    #cursor.execute(query, (probe_id,))
    #reported,tod = cursor.fetchone()
    #userspace_times = [sent_times[0]-reported/3.0, sent_times[0]+reported]

    print("single probe counts:",len(sent_payload),len(sent_other),len(rcvd_payload),len(rcvd_other))
    plt.clf()
    plt.title("Single HTTP Request - Packet Times")
    sp = plt.eventplot(sent_payload, colors=('red',), lineoffsets=8, linewidths=2, alpha=0.6,label='sent')
    so = plt.eventplot(sent_other, colors=('red',), lineoffsets=6, linewidths=2, alpha=0.6,label='sent')
    rp = plt.eventplot(rcvd_payload, colors=('blue',), lineoffsets=4, linewidths=2, alpha=0.6,label='received')
    ro = plt.eventplot(rcvd_other, colors=('blue',), lineoffsets=2, linewidths=2, alpha=0.6,label='received')
    #plt.legend((s,r), ('sent','received'))
    #plt.savefig('../img/http-packet-times.svg')
    plt.show()

#plotSingleProbe()


def graphTestResults():
    basename = os.path.basename(options.db_file)
    basename,ext = os.path.splitext(basename)

    chartname = "/home/tim/blindspot/research/timing-analysis/paper/figures/results/%s.svg" % (basename)
    print(chartname)
    
    plt.clf()
    plt.title("Test Results")
    plt.xlabel('sample size')
    plt.ylabel('error rate')
    legend = []
    colors = ['red','blue','green','purple','orange','black','brown']
    color_id = 0

    cursor = db.conn.cursor()
    query = """
      SELECT classifier FROM classifier_results GROUP BY classifier ORDER BY classifier;
    """
    cursor.execute(query)
    classifiers = []
    for c in cursor:
        classifiers.append(c[0])

    best_obs = []
    best_error = []
    max_obs = 0
    for classifier in classifiers:
        query="""
        SELECT params,num_observations FROM classifier_results 
        WHERE trial_type='test'
         AND classifier=:classifier
         AND (false_positives+false_negatives)/2.0 < 5.0 
        ORDER BY num_observations,(false_positives+false_negatives) 
        LIMIT 1
        """
        cursor.execute(query, {'classifier':classifier})
        row = cursor.fetchone()
        if row == None:
            query="""
            SELECT params,(false_positives+false_negatives)/2 FROM classifier_results 
            WHERE trial_type='test' and classifier=:classifier
            ORDER BY (false_positives+false_negatives),num_observations
            LIMIT 1
            """
            cursor.execute(query, {'classifier':classifier})
            row = cursor.fetchone()
            if row == None:
                sys.stderr.write("WARN: couldn't find test results for classifier '%s'.\n" % classifier)
                continue

            best_error.append((row[1], classifier))
        else:
            best_obs.append((row[1], classifier))

        best_params = row[0]
        query="""
        SELECT num_observations,(false_positives+false_negatives)/2.0 FROM classifier_results 
        WHERE trial_type='test'
         AND classifier=:classifier
         AND params=:params 
        ORDER BY num_observations
        """
        cursor.execute(query, {'classifier':classifier,'params':best_params})

        num_obs = []
        performance = []
        for row in cursor:
            max_obs = max(max_obs, row[0])
            num_obs.append(row[0])
            performance.append(row[1])
        #print(num_obs,performance)
        path = plt.scatter(num_obs, performance, color=colors[color_id], s=4, alpha=0.8, linewidths=3.0)
        plt.plot(num_obs, performance, color=colors[color_id], alpha=0.8)
        legend.append((classifier,path))
        color_id = (color_id+1) % len(colors)

    best_obs.sort()
    best_error.sort()
    winner = None
    for bo in best_obs:
        sys.stdout.write("%d obs / %s" % bo)
        if winner == None:
            sys.stdout.write(" (winner)")
            winner = bo
        print()
        
    for be in best_error:
        sys.stdout.write("%f%% error / %s" % be)
        if winner == None:
            sys.stdout.write(" (winner)")
            winner = be
        print()
    
    plt.legend([l[1] for l in legend], [l[0] for l in legend], scatterpoints=1, fontsize='x-small')
    plt.plot([0, max_obs], [5.0, 5.0], "k--")
    plt.xlabel('Number of Observations')
    plt.ylabel('Error Rate')
    plt.savefig(chartname)
    #plt.show()
    
graphTestResults()

sys.exit(0)

short_overtime = [(sample['time_of_day'],sample['short']) for sample in timeSeries(db,'train','short')]
long_overtime = [(sample['time_of_day'],sample['long']) for sample in timeSeries(db,'train','long')]
diff_overtime = [(sample['time_of_day'],sample['long']-sample['other_cases']) for sample in timeSeries(db,'train','long')]
short_overtime.sort()
long_overtime.sort()
diff_overtime.sort()

plt.clf()
plt.title("Packet RTT over time")
plt.xlabel('Time of Day')
plt.ylabel('RTT')
s = plt.scatter([t for t,rtt in short_overtime], [rtt for t,rtt in short_overtime], s=1, color='red', alpha=0.6)
l = plt.scatter([t for t,rtt in long_overtime], [rtt for t,rtt in long_overtime], s=1, color='blue', alpha=0.6)
d = plt.scatter([t for t,rtt in diff_overtime], [rtt for t,rtt in diff_overtime], s=1, color='purple', alpha=0.6)
plt.legend((s,l,d), ('short','long','difference'), scatterpoints=1)
#plt.savefig('paper/figures/comcast-powerboost1.png')
plt.show()



plt.clf()
plt.title("Simple HTTP Request")
plt.xlabel('Time of Day')
plt.ylabel('')
s = plt.scatter(sent_times, [2]*len(sent_times), s=3, color='red', alpha=0.9)
r = plt.scatter(rcvd_times, [1]*len(rcvd_times), s=3, color='blue', alpha=0.9)
plt.legend((s,r), ('sent','received'), scatterpoints=1)
plt.show()

sys.exit(0)
short_overtime,long_overtime,diff_overtime = None,None,None


num_bins = 300
reported_diffs.sort()
cut_off_low = reported_diffs[int(len(diffs)*0.003)]
cut_off_high = reported_diffs[int(len(diffs)*0.997)]

plt.clf()
# the histogram of the data
n, bins, patches = plt.hist(reported_diffs, num_bins, normed=1, color='black', histtype='step', alpha=0.8,
                            range=(cut_off_low,cut_off_high))
plt.xlabel('RTT Difference')
plt.ylabel('Probability')
plt.title(r'Histogram - distribution of differences')

# Tweak spacing to prevent clipping of ylabel
plt.subplots_adjust(left=0.15)
#plt.legend()
plt.show()
#plt.savefig('paper/graphs/dists-vs-dist-of-diffs2.svg')




num_bins = 300
diffs.sort()
cut_off_low = diffs[int(len(diffs)*0.003)]
cut_off_high = diffs[int(len(diffs)*0.997)]

plt.clf()
# the histogram of the data
n, bins, patches = plt.hist(diffs, num_bins, normed=1, color='purple', histtype='step', alpha=0.8,
                            range=(cut_off_low,cut_off_high))
plt.xlabel('RTT Difference')
plt.ylabel('Probability')
plt.title(r'Histogram - distribution of differences')

# Tweak spacing to prevent clipping of ylabel
plt.subplots_adjust(left=0.15)
#plt.legend()
plt.show()
#plt.savefig('paper/graphs/dists-vs-dist-of-diffs2.svg')

sys.exit(0)



num_bins = 150
# the histogram of the data
n, bins, patches = plt.hist((shorts,longs), num_bins, normed=1, label=['short', 'long'], color=['red','blue'], histtype='step', alpha=0.8,
                            range=(cut_off_low,cut_off_high))
#n, bins, patches = plt.hist(shorts2+longs2, num_bins, normed=1, facecolor='blue', histtype='step', alpha=0.3)
# add a 'best fit' line
#y = mlab.normpdf(bins, mu, sigma)
#plt.plot(bins, y, 'r--')
plt.xlabel('packet_rtt')
plt.ylabel('Probability')
plt.title(r'Histogram - RTT short and long')

# Tweak spacing to prevent clipping of ylabel
plt.subplots_adjust(left=0.15)
plt.legend()
#plt.show()
plt.savefig('paper/figures/comcast-powerboost2.svg')




num_trials = 200


subsample_sizes = (50,150,300,500,700,1000,2000,3000,5000,7000,10000,15000,20000)
estimator = functools.partial(boxTest, 0.07, 0.08)
performance = []
for subsample_size in subsample_sizes:
    estimates = bootstrap(derived, subsample_size, num_trials, estimator)
    performance.append(100.0*len([e for e in estimates if e == 1])/num_trials)

null_performance = []
for subsample_size in subsample_sizes:
    null_estimates = bootstrap(null_derived, subsample_size, num_trials, estimator)
    null_performance.append(100.0*len([e for e in null_estimates if e == 0])/num_trials)

plt.clf()
plt.title("boxTest bootstrap")
plt.xlabel('sample size')
plt.ylabel('performance')
plt.scatter(subsample_sizes, performance, s=2, color='red', alpha=0.6)
plt.scatter(subsample_sizes, null_performance, s=2, color='blue', alpha=0.6)
plt.show()



subsample_sizes = (50,150,300,400,500,700,1000,2000,3000,4000,5000,7000,10000)
estimator = diffMedian
performance = []
for subsample_size in subsample_sizes:
    estimates = bootstrap(derived, subsample_size, num_trials, estimator)
    performance.append(100.0*len([e for e in estimates if e > expected_mean*0.9 and e < expected_mean*1.1])/num_trials)

plt.clf()
plt.title("diff median bootstrap")
plt.xlabel('sample size')
plt.ylabel('performance')
plt.scatter(subsample_sizes, performance, s=1, color='red', alpha=0.6)
plt.show()




subsample_sizes = (50,150,300,400,500,700,1000,2000,3000,4000,5000,7000,10000)
weight_funcs = (linearWeights, prunedWeights)
for wf in weight_funcs:
    estimator = functools.partial(estimateMean, hypotenuse, wf, 0.40)
    performance = []
    for subsample_size in subsample_sizes:
        estimates = bootstrap(derived, subsample_size, num_trials, estimator)
        performance.append(100.0*len([e for e in estimates if e > expected_mean*0.9 and e < expected_mean*1.1])/num_trials)

    plt.clf()
    plt.title(repr(wf))
    plt.xlabel('sample size')
    plt.ylabel('performance')
    plt.scatter(subsample_sizes, performance, s=1, color='red', alpha=0.6)
    plt.show()



num_bins = 300
# the histogram of the data
n, bins, patches = plt.hist((tsshorts,tslongs), num_bins, normed=1, label=['short', 'long'], color=['red','blue'], histtype='step', alpha=0.8)
#n, bins, patches = plt.hist(shorts2+longs2, num_bins, normed=1, facecolor='blue', histtype='step', alpha=0.3)
# add a 'best fit' line
#y = mlab.normpdf(bins, mu, sigma)
#plt.plot(bins, y, 'r--')
plt.xlabel('packet_rtt')
plt.ylabel('Probability')
plt.title(r'Histogram - tsval_rtt short vs long')

# Tweak spacing to prevent clipping of ylabel
plt.subplots_adjust(left=0.15)
plt.legend()
plt.show()



    
####
#trust_methods = [min,max,sum,difference,product]
trust_methods = [sum,product,hypotenuse]
colors = ['red','blue','green','purple','orange','black']
weight_methods = [prunedWeights, linearWeights]
alphas = [i/100.0 for i in range(0,100,2)]




plt.clf()
plt.title(r'Trust Method Comparison - Linear')
plt.xlabel('Alpha')
plt.ylabel('Mean error')
paths = []
for tm in trust_methods:
    trust = trustValues(derived, tm)
    series = []
    for alpha in alphas:
        weights = linearWeights(derived, trust, alpha)
        series.append(weightedMean(derived, weights) - expected_mean)

    paths.append(plt.scatter(alphas, series, s=1, color=colors[len(paths)],alpha=0.6))

plt.legend(paths, [repr(tm) for tm in trust_methods], scatterpoints=1)
plt.show()



plt.clf()
plt.title(r'Trust Method Comparison - Pruned')
plt.xlabel('Alpha')
plt.ylabel('Mean error')
paths = []
for tm in trust_methods:
    trust = trustValues(derived, tm)
    series = []
    for alpha in alphas:
        weights = prunedWeights(derived, trust, alpha)
        series.append(weightedMean(derived, weights) - expected_mean)

    paths.append(plt.scatter(alphas, series, s=1, color=colors[len(paths)],alpha=0.6))

plt.legend(paths, [repr(tm) for tm in trust_methods], scatterpoints=1)
plt.show()


sys.exit(0)

plt.clf()
plt.title(r'Trust Method Comparison - Inverted')
plt.xlabel('Alpha')
plt.ylabel('Mean error')
paths = []
for tm in trust_methods:
    trust = trustValues(derived, tm)
    series = []
    for alpha in alphas:
        weights = invertedWeights(derived, trust, alpha)
        series.append(weightedMean(derived, weights) - expected_mean)

    paths.append(plt.scatter(alphas, series, s=1, color=colors[len(paths)],alpha=0.6))

plt.legend(paths, [repr(tm) for tm in trust_methods], scatterpoints=1)
plt.show()


plt.clf()
plt.title(r'Trust Method Comparison - Arctangent')
plt.xlabel('Alpha')
plt.ylabel('Mean error')
paths = []
for tm in trust_methods:
    trust = trustValues(derived, tm)
    series = []
    for alpha in alphas:
        weights = arctanWeights(derived, trust, alpha)
        series.append(weightedMean(derived, weights) - expected_mean)

    paths.append(plt.scatter(alphas, series, s=1, color=colors[len(paths)],alpha=0.6))

plt.legend(paths, [repr(tm) for tm in trust_methods], scatterpoints=1)
plt.show()