Month: July 2008

BBC News: BT broadband and Tiscali

BT seem to play on ‘the safe default choice for the nation’ image while charging high prices and miking it for all they can. For years they had ‘technical problems’ in letting anyone else put equipment in to the exhanges that got handed to them after privatisation. There were in the strange situation of being the only organisation broadband suppliers could use to provide the connection to user’s homes (due to the previously mentioned reason), setting the price to their monopoly service and also being one of the consumer broadband suppliers itself. Conflict of interest?

And don’t get me started on when they forced everyone on the landline (telephone) rental to move to their ‘line rental plus inclusive calls package’. They did this, they claimed, because most customers would be better off. Great, write to them and tell them so, don’t force everyone to move to a more expensive package. At the time (this was 2002 or there abouts) many third party companies were offering cheap call packages (pay a few quid a month and most calls were cheaper), BT doing this meant people were now paying for one of these packages automatically and without choice, BT’s one, and therefore kill the competetion overnight.

Where was I? Oh yes, BBC News are reporting that BT have written to all of Tiscali’s customers about speculation that Tiscali may be sold in the near future. Now Tiscali are crap, we all know this. And BT and incredibly expensive, we know this too (they play on the fact many people automatically go to them for broadband because they get their phone via BT already). But where did BT get the Tiscali’s customer list from. They claim through a external source. Of course, not through their BT Openreach/wholesale Units who Tiscali have to use for the physcial wires to their customer’s homes.

What is the point of a blog if not to rant (badly)?

Posted on by chriskeene

SQL grouping mulitple values in to one SELECT field

SQL is one of those things I always need to understand more than I do. Sometimes odd little problems can seem impossible with my limited understanding of the language.

One such hit this me this morning. We wanted a report from our Institutional Repository (a online archive of the University’s research publications). The report was to include title, journal title, type (journal article, book, etc), author(s) and departments for each item in the report.

Anyone with a basic understanding of relational databases will be able to picture the main table of publications, with another table for publication authors – one record for each author of each publication, with the publication’s id number to link them to the item in question. The same set up for the departments associated with a particular item.

With basic SQL, it would be easy to return a list which included multiple entries for items with multi authors, i.e. an item with three authors would be repeated three times in the list, each showing a different author. An item with three authors and two departments would be repeated six times to cover all combinations of authors and departments. Not very desirable. We could also provide a list which only shows each item once (as required) but only show one author and department (ie by using GROUP BY and perhaps a function to select the first author). Again would be less than ideal.

A bit of googling showed creating an agregate function in postgress (we are using mysql), and this solution which seems to use a tempory table.

The solution in the end was much more simpler, and involves using a function called group_concat.

The final SQL looked like this:

SELECT a.eprintid, a.type, a.title, a.publication,
GROUP_CONCAT(c.creators_given, " ", c.creators_family) as Authors,
GROUP_CONCAT(d.depts) as Departments
FROM `archive` a, `archive_creators` c, `archive_depts` d
WHERE a.eprintid = c.eprintid
AND a.eprintid = d.eprintid
GROUP BY c.eprintid, d.eprintid

The two group_concats will produce a list of authors (and a list of departments) both comma separated. The author’s is a little more complex as the name is within two separate fields, though this isn’t a problem, we just need to specify both with a space in between to make them readable.

Posted on by chriskeene 3

Brighton New England Quarter

A few years a go I created some new pages on the ‘New England Quater’, on the site of some old railway works next to Brighton station and near me. Originally the pages just had some photos of how the site looked before work started, and at various stages since (stupidly I normally took this late in the day as the sun was setting which produced poor photos), however at the time I couldn’t find any website providing general information on the project so provided some background and useful links. Since then there has been much more information put on to the web (and lot of information removed as well).

Anyway, I write this as I’ve noticed that the council have put out a report this year reviewing the current state of play, see the link ‘briefing note 2008’.

Posted on by chriskeene 2

RIOJA – Repository Interface for Overlaid Journal Archives – live blog

Living Blogging http://www.ucl.ac.uk/ls/rioja/ see http://is.gd/N8T or , in fact, see below…

Update (8 July 2008):

This is my first attempt at using coveritlive.com (as used very sucessfully many times by Andy Powell at Eduserv). On the whole thought it was sucessful, and the software was great.

Few points: The software doesn’t seem to allow you to edit entries, I guess this keeps with the spirit of liveblogging but can be a pain when you notice a mistake just after sending a message. In fact trying to take notes and listen to what is being said lead to quite a few typos etc, the small font on the screen – further away on my lap than it would be on a desk – didn’t help. Secondly, difficult to let people know you are doing this (the other extreme would be to bombared mailing lists announcing your intentions), so some who may have had an interest in keeping an eye on the days event would not have been aware of this.

For me as a reader, I think I find the more traditional blog post of these events more useful as a (slightly rough) written report of the different talks. However, I was keen to try out this software and knew that someone else (who is far better at it than me) was doing this. At the recent Talis Xiphos research day there was a combination of the two (here and here), and I thought this worked pretty well for those watching the day from afar.

Posted on by chriskeene 3