LuckyDonkey

My ADSL hasn't been perfect at my home for a year or so now. Basically it looses the connection. Sometimes once a week, sometimes it's 4 or five times a day. I've been too lazy to sort it out because it just seemed easier to reboot the modem, wait 30 seconds and carry on with my life. I've had ADSL nightmares before where I spent hours on hold waiting to talk to a support serf and I haven't been keen to repeat this.

A friend of mine coincidentally asked me if I knew anything about a provider he was thinking of joining. He started to bemoan his provider and the ancient wiring in his london flat. So I replied and tried not to think about my problems. As often happens I couldn't stop thinking of my own problems! I stumbled upon RouterStats. It's a cool little win32 app that basically knows how to read you routers stats page and display a little graph. I ran it on my mac using VMWare Fusion and sure enough it showed my modems 'noise ratio' getting lower and lower toward the magic 6db mark where most routers just give up and drop their ADSL connection.

A this point I decided that I couldn't be bothered to run this App (and a resident copy of windows JUST so I could look at my router stats. So I turned to python to scrape my router page

 
#script to scrape the stats from a Netgear DG834GT
import urllib2
import re
from BeautifulSoup import BeautifulSoup
from time import sleep
from datetime import datetime
 
base_path="http://192.168.1.1/"
monitor_page='stattbl.htm'
username='admin'
password = 'password'
top_level_url = base_path
 
#register an opener with urllib2 which knows how to respond to basic
#authentication requests from a webserver
password_mgr = urllib2.HTTPPasswordMgrWithDefaultRealm()
password_mgr.add_password(None, top_level_url, username, password)
handler = urllib2.HTTPBasicAuthHandler(password_mgr)
opener = urllib2.build_opener(handler)
urllib2.install_opener(opener)
 
#re to extract the number section from a string in the form xxx.xx db
dbre=re.compile(r"(?P<num>[\d\.]+)\s*db")
 
#for ever, ask for the stats page, extract the data and print it,
#it's  not pretty code but I am scraping a bloody router stats
#page so WHO CARES
while True:
    response = urllib2.urlopen(base_path+monitor_page)
    html= response.read()
    soup = BeautifulSoup(html)
    tables= soup.body.table.findAll('table')
    tds = tables[0].findAll('td')
 
    tx = tds[13].span.contents[0]
    rx = tds[14].span.contents[0]
 
    def extract(node):
        s  =dbre.match(node).group('num')
        return float(s)
 
    tds = tables[1].findAll('td')
    att_down = extract(tds[7].span.contents[0])
    att_up = extract(tds[8].span.contents[0])
 
    nm_down = extract(tds[10].span.contents[0])
    nm_up = extract(tds[11].span.contents[0])
 
    now = datetime.now()
    print now.strftime("%H:%M:%S"), "atd:", att_down, "atu:",\
        att_up, "nd:", nm_down, "nu:", nm_up, "rx:", rx, "tx:", tx
    sleep(5)
 

Every five seconds this prints out the stats for my router

13:44:18 atd: 21.0 atu: 22.0 nd: 13.0 nu: 26.0 rx: 10138 tx: 4138
13:44:23 atd: 21.0 atu: 22.0 nd: 13.0 nu: 26.0 rx: 10138 tx: 4138
13:44:29 atd: 21.0 atu: 22.0 nd: 13.0 nu: 26.0 rx: 10137 tx: 4138
13:44:34 atd: 21.0 atu: 22.0 nd: 13.0 nu: 26.0 rx: 10136 tx: 4137
13:44:39 atd: 21.0 atu: 22.0 nd: 13.0 nu: 26.0 rx: 10135 tx: 4137
13:44:44 atd: 21.0 atu: 22.0 nd: 13.0 nu: 26.0 rx: 10135 tx: 4137
13:44:50 atd: 21.0 atu: 22.0 nd: 13.0 nu: 26.0 rx: 10134 tx: 4136
13:44:55 atd: 21.0 atu: 22.0 nd: 13.0 nu: 26.0 rx: 10133 tx: 4136
13:45:00 atd: 21.0 atu: 22.0 nd: 13.0 nu: 26.0 rx: 10132 tx: 4136
13:45:05 atd: 21.0 atu: 22.0 nd: 13.0 nu: 26.0 rx: 10131 tx: 4135

Well I left this running and guess what? When the phone rang my noise ratio dropped... and if it was already low my router dropped the connection. If I even picked up my phone the noise ratio dropped. After poking around with the wiring it turns out one of the phones in the house didn't have an ADSL filter so it was spewing noise on to the telephone line in the frequency range ADSL uses! Doh. Without the filter my best noise ratio was 9db, with the filter it never drops below 12db.

I've been hearing about DTrace for years now and I kind of knew what it was and that I would like to have it. In the back of my mind I've been waiting for it to appear in FreeBSD (although this seems to be faltering a little) so I could play with it.

Well one of the engineers from Sun that gave birth to DTrace has done a google tech talk about DTrace and it's very interesting indeed. Highlights for me were the amazing instrumentation of Python which I will definitely use. He also muses on the lack of support for DTrace on linux and tries to gather a posse of developers from google to kick it off, porting DTrace to Windows and how he can't find a good book on programming.

In my previous post I wanted to count the number of gigs that have or will be staged at a venue. But if a venue has no gigs associated it didn't appear in the results. Well to fix this I need to join the Venues table making sure that every venue appears in the output (even if there are no gigs there):

SELECT venues.name, COUNT(gigs.venueid)
FROM venues LEFT OUTER JOIN gigs
ON gigs.venueid = venues.id
GROUP BY venues.name
ORDER BY venues.name

The JOIN joins venues and gigs where gigs.venueid equals venues.id but the LEFT OUTER statement makes sure that all rows in the left table (venues) appear in the output table. There is an excellent description of how joins work in wikipedia of course. Any row in the left table (venues) that doesn't 'join' anything in the right tables (gigs) is added with NULL values for right table columns. We don't really care about this because we are only SELECTing the venue name and the count of gigs that use that venue (so we don't need any potentially NULL column entries)

Anyway, how do we get this in to SQLAlchemy:

select([Venue.c.name, func.count(Gig.c.venueid)],
        from_obj=[venues_table.outerjoin(gig_table)],
        group_by=[Venue.c.name, Venue.c.id],
        order_by=[Venue.c.name]).execute().fetchall()

I learnt something pretty cool while working this out. You can ask SQLAlchemy to printout what query it is going to execute:

print select([Venue.c.name, func.count(Gig.c.venueid)],
        from_obj=[venues_table.outerjoin(gig_table)],
        group_by=[Venue.c.name, Venue.c.id],
        order_by=[Venue.c.name])

and you get

SELECT venues.name, count(gigs.venueid)
FROM venues LEFT OUTER JOIN gigs
ON venues.id = gigs.venueid
GROUP BY venues.name, venues.id
ORDER BY venues.name

which I think you can agree is basically identical to the required statement. Using this feature I could test out SQLAlchemy syntax and see if it was getting close to the raw SQL. This helped me a lot.

I think the SQLAlchemy syntax here is particularly intuitive. The keyword parameter from_obj is particularly pleasing as it really describes what I am doing in my head. I'm taking the venue name and it's count from the set 'venues joined with gigs'. SQLAlchemy can easily work out that venues are joined to gigs by the foreign key in each gig row which referes to a venue. So there is no need for me to specify HOW to join (the ON clause in SQL).