BT Retail quietly trials CGNAT on some of their customer connections

Posted on by

According to this news story on ISPreview.co.uk, BT Retail are quietly trialling Carrier Grade NAT on some of their customer connections,  This is the first of the ‘Big 5’ that are known to be doing so, not long after the Plusnet (also owned by BT) trial took place.  To me, this suggests that we are now at the point where even the big ISPs no longer have the IPv4 addresses to allocate to customers, and the article suggests that up nine other customers will be sharing a single address, if you have been placed on this trial.

The worry here is that all of the Big 5 will just use this as an excuse to delay IPv6 adoption further, when what we really need is mass adoption of IPv6 folllowed by, if and only if necessary, CGNAT for all the “legacy” applications still left on the Internet.

BT’s official FAQ on the matter can be found here.

Adventures in Ticketing

Posted on by

This is still a work in progress – you have been warned!  (Please note I’m not necessarily recommending you code the SQL in this way either, since there are far more optimal ways of doing it in a real program, but it helps to do it this way to explain what I’m doing.)

As you may have noticed from this post, the Association of Train Operating Companies have released their fares data as a series of text files in a ZIP file under a Creative Commons licence.  Not one to pass up a challenge like this, I quickly downloaded the file and then went about seeing what I could make of it.  Just over a week later, I had created a working, if not entirely finished and polished, Python script to import those data files into a PostgreSQL database.  (I will open source this tool and release it to the world once it’s finished, but it’s really only usable by me currently.  More news on that when I’ve finished it)

So, now I have a database that is 4GiB in size and 58 tables in size, so what can I do with it?  Well, the obvious answer would be to find a fare from A to B.  Easy, you’d think.  Wrong!

First of all, let me include a small glossary of railway fares terms for you:

  • NLC – National Location Code. A four-character code, usually numeric but not always, which identifies either a particular station or other place that can issue tickets.
  • RJIS – Rail Journey Information System.  The big railway industry database, run by Fujitsu, that the ATOC-provided files are sourced from.  Basically the same set of files are sent to the various ticket issuing systems in use, but obviously they are updated more than once every three months.
  • TOC – Train Operating Company
  • Flow – A journey between a given origin and destination is known as a “flow”.  The fare for each flow is assigned to a particular TOC or other similar body permitted to set fares (such as a Passenger Transport Executive such as West Yorkshire Metro, GMPTE in Manchester, etc.) or other body such as Transport for London.  It is important to note that not every journey between two points on the UK rail network is individually priced.
  • CRS code – A CRS code is a three-character (usually three letters) code which identifies a station.  These usually follow the station name, for example LDS is Leeds and BRI is Bristol Temple Meads.
  • Station Cluster – A station cluster is a group of stations which belong in a group for the purposes of pricing up a flow.

So let’s attempt to find a fare.

Let’s pick two stations at random, a good distance apart, say Taunton to Leeds.  The obvious choice is to look up those two stations in the database and see what it tells us.  According to the flow_f table, I need to specify an origin and a destination.  These are stored in the database as NLCs, so I need to convert Taunton and Leeds into NLCs.  To do that I need to query the location_l table…

SELECT description,nlc_code FROM location_l WHERE description='LEEDS'
OR description='TAUNTON';

That didn’t work!  Why not?  Well, the answer is a bit subtle.  Every record in the data files is fixed width, which means you (at the very least) have to strip spaces from it, and at worst get rid of superfluous dots at the end of it.  Madness.  Let’s try again…

SELECT description,nlc_code FROM location_l WHERE description LIKE 'LEEDS%'
OR description LIKE 'TAUNTON%';

That’s better.  But wait a minute, I’ve got multiple records?  What on earth … ?

"TAUNTON.        ";"3471"
"TAUNTON STN FRCT";"7434"
"LEEDS.          ";"8487"
"LEEDS + BUS     ";"H975"
"TAUNTON+BUS     ";"J945"
"LEEDS BUS       ";"K202"
"LEEDS BRD AIRBUS";"K650"
"LEEDS FEST BUS  ";"K684"

OK, this isn’t ideal, but it does at least provide the information we wanted – TAUNTON. (with the dot) is NLC 3471, and LEEDS. (also with the dot) is NLC 8487.  So now we can query the flow_f table with the right information.  So, let’s have a go …

SELECT * FROM flow_f, flow_t WHERE flow_f.flow_id=flow_t.flow_id AND
origin_code='3471' AND destination_code='8487';

Nothing!  Why ever not?  The answer to this a little less than simple.  As I mentioned above, a flow is not the same as a journey.  In other words, not all journeys have an individually priced flow.  In the old days of the paper fares manuals, this was generally dealt with in the book by having a ‘related station’ – in other words, if you couldn’t find the fare explicitly listed from A to B in the listing for station A, you looked it up from a related station (which would be nearby).  All that is gone out of the window and replaced with something massively more complex – the idea of the Station Cluster.  Hold on tight … !

What we need to do next is to find out if NLC 3471 (Taunton) and NLC 8487 are in any station clusters.  If they are, we then need to repeat the above query replacing the station NLCs with the cluster NLCs.  So let’s have a grapple around the station_clusters table to see if we can find what we want:

SELECT cluster_nlc,cluster_id FROM station_clusters WHERE cluster_nlc='3471'
OR cluster_nlc='8487' ORDER BY cluster_nlc;

So let’s see what that query gave us:

"3471";"Q824"
"3471";"Q942"
"3471";"S327"
"3471";"Q903"
"3471";"R070"
"3471";"R409"
"3471";"Q391"
"3471";"Q909"
"3471";"T104"
"3471";"Q814"
"3471";"Q715"
"3471";"T204"
"3471";"R708"
"3471";"T327"
"3471";"T406"
"3471";"T511"
"8487";"Q864"
"8487";"Q865"
"8487";"Q903"
"8487";"Q931"
"8487";"Q937"
"8487";"Q938"
"8487";"Q964"
"8487";"R629"
"8487";"R639"
"8487";"R640"
"8487";"R705"
"8487";"S031"
"8487";"S205"
"8487";"T007"
"8487";"T018"
"8487";"T020"
"8487";"T027"
"8487";"T029"
"8487";"GLA1"
"8487";"T143"
"8487";"T229"
"8487";"T129"
"8487";"Q475"
"8487";"Q614"
"8487";"Q661"
"8487";"Q696"
"8487";"Q716"
"8487";"Q814"
"8487";"Q816"
"8487";"Q819"
"8487";"Q822"
"8487";"Q824"
"8487";"Q844"
"8487";"Q845"
"8487";"Q846"
"8487";"Q847"
"8487";"Q850"

Erk!  What is all this lot?  Well, the first column is the NLC of the station we’re looking for, and the second column is a list of all the station clusters that that NLC is in.  And you’ll notice there’s loads of them…

Thankfully there’s a clever bit of SQL that can do this task for me.  Try this …

SELECT <some fields> FROM flow_f, flow_t WHERE flow_f.flow_id=flow_t.flow_id AND
flow_f.origin_code IN (SELECT cluster_id FROM station_clusters
WHERE cluster_nlc='3471') AND flow_f.destination_code IN
(SELECT cluster_id FROM station_clusters WHERE cluster_nlc='8487');

To be continued…

Open rail fares data now available from ATOC

Posted on by

After many years of trying and wrangling with ATOC from various people, there now exists a page http://data.atoc.org/fares-data from which you can actually download the fares data without having to shell out £15 or so for the Avantix Traveller CD from TSO, and/or the National Fares Manuals on paper before that.

This is great news at last though, even if it is currently only on a trial basis.  Right, where’s my web browser?

And now the fun begins…

Posted on by

Today marks the firing of the starting gun in the next phase of IPv4 exhaustion.  To date, running NATs on the ISP side has generally been the preserve of the mobile operators, where running servers generally isn’t something you’d want or need to do over a mobile connection.  However, something new happened today – Plusnet, the Sheffield-based (and BT Group owned) ISP announced they are going to run a 3-week trial of CGNAT, or Carrier Grade NAT.

Some more info on that can be found here:

http://www.ispreview.co.uk/index.php/2013/01/isp-plusnet-trials-controversial-ipv4-address-sharing-as-ipv6-alternative.html
http://www.thinkbroadband.com/news/5658-plusnet-in-trial-of-carrier-grade-nat-to-conserve-ipv4-addresses.html
http://www.theregister.co.uk/2013/01/15/ipv4_nat/

Now, the term “carrier grade” has, to date, normally meant something along the lines of big and impressive, and can generally handle thousands or millions of whatever it can do, and generally you’d expect a “carrier grade” product to be somehow better than a product which is “non-carrier grade”.  However, this is probably the one “carrier grade” product that generally makes things worse.  A bit of explanation here – “carrier grade” in this context means that it is suitable for a “carrier”, or telco, to use.  CGNAT is “carrier grade” in the sense that it can handle thousands and thousands of connections at once, and runs on slightly beefier hardware than your average home ADSL router, but the net effect of running CGNAT actually makes the experience worse than it would otherwise have been, since you are now removing the ability for that ISP’s users to do port forwarding through the ISP’s NAT which will almost certainly stop quite a lot of things working, for example anything that requires an incoming port (e.g. a server), or perhaps things like UPnP and even Skype.  This is probably going to cause quite a lot of compaints, depending on how much it breaks, and also means that users will be getting a consderably degraded Internet experience than they already are (since NAT is not exactly how IPv6 was intended to be used in the first place).

Plusnet had an IPv6 trial, which they stopped for some unexplained reason, and so far no word on when or if it will be resurrected.  Rolling out CGNAT and not also rolling out IPv6 seems very short-sighted to me, but more importantly the fact that they are even considering CGNAT at all suggests to me that there is one AS in the UK that could be running dangerously low on its allocated IPv4 addresses (with no prospect of obtaining any more from RIPE).

I shall be watching this space with interest…

 

Instagram forces you to sell your photos?

Posted on by

I find it amazing that people really won’t learn when it comes to “free” web sites that just happen to make money by selling all your data, whether it’s Facebook, Google, or anyone else.  Today, all over the news sites and newspapers, comes the news that Instagram (who are owned by Facebook) are basically going to sell any photos you upload to their web site without your permission, and are changing the terms and conditions to allow this.

Either people really are not concerned that a company is basically selling their personal information and data to advertisers or they haven’t really woken up to the fact that this is what they are doing.  This is precisely the main reason why I basically have cancelled most, if not all, of my social networking accounts because I bothered to read the terms and conditions and saw what they were trying to do with my data!  Copyright law exists to protect owners of “content” – so if I take a photograph of something, the copyright of that photograph belongs to me, and I decide what can and cannot be done with it.  I don’t want to sign it away to some faceless corporation to do what they want with it, all on the basis that they are providing me “free” somewhere to store my photos.

Let’s hope Instagram gets plenty of bad PR from this, because I don’t know what’s going to stop companies from doing this and getting away with it.  Perhaps it’ll need to be some high-profile celebrity or politician having their privacy rights trampled all over before something is done.

Update: Instagram/Facebook now claim they’re not really doing this.  It still doesn’t inspire me with confidence though.

Comet and gone

Posted on by

Today marks the final closing down sales of the UK electrical chain Comet which has, as I write this, has or is about to pull down the shutters on the remaining 49 stores which were open for the final time today before closing down for good.  Whilst obviously it’s sad that so many jobs have been lost, Comet probably wasn’t going to survive long term because there was basically no room for them any more, sandwiched as they were between Dixons Group (owners of Currys/PC World) and the supermarkets, both of which are now selling the cheap electrical goods of the type that Comet was, and so it had to go, in an environment radically different from the original Comet Battery Stores of the 1930s.  Not to mention of course the constant financial problems the company seems to have had in recent years.

RIP Comet Group Ltd (in administration) 1933-2012

6::UK Closes Down

Posted on by

You’ve probably never heard of 6::UK – and that’s pretty much the problem.  They were an organisation that started in 2010 and funded by the Government to the tune of £20,000 to help and assist UK organisations and companies to make the switch to IPv6.  Today, the entire board of 6::UK resigned because they say the Government is basically not backing them.

The hopeless state of IPv6 in the UK is not really something any of us should be proud of, and none of the Big 6 consumer ISPs have rolled it out yet.  There are a few smaller ISPs that have, but in the main the ISPs that control 90-something% of the consumer market just haven’t rolled it out.  My view is that no-one basically wants to make the first move on this, because whoever does, costs themselves money, and it’s the classic chicken-and-egg problem.

Perhaps a nice bit of public embarrassment will get things moving, but based on previous experience, I predict not.  And as the article points out, the British Government should be doing something towards this, especially as other governments around the world are.  Before it’s too late and it costs everyone three times as much to roll it out in a panic rather than do it now.

Update: The Department of Business, Innovation and Skills did bother to comment on this eventually, the text of which you can read on the BBC News article.  I’m not sure if it adds anything, though.