Biannual mileage total update – #cycling #green #money

I’ve updated my spreadsheet and my biannual cycling mileage totals are:
2008 (H2) = 1600 miles
2009 (H1) = 1100 miles
2009 (H2) = 1400 miles
2010 (H1) = 1500 miles
2010 (H2) = 3500 miles
2011 (H1) = 3700 miles
2011 (H2) = 3600 miles

So I’m doing around 7000 miles per year on my bicycle rather than in a car, saving me a fortune!

See last post about this here

Light snow, to ride or not? #cycling #snow #winter

This morning I looked out onto the back garden and couldn’t even see the mess that I call my lawn, or the path to my “bike” shed

So I went out with a view of assessing whether it was suitable for commuting to work, it didn’t look good to start with

A closer look it wasn’t that deep

The garden dwellers weren’t happy either

Anyway, I decided I’d go for it and I wasn’t on my own either

My road was pretty virgin snow as nobody uses it, I failed to take a photo, I was having too much fun.

Busier side roads were more passable

The main roads were clear more or less and I arrived safely and happy I’d braved it and not resorted to using the car

(shame about that one, the lens must have misted up on my phone)

I’m looking forward to the Friday night ride home now, ride safe, have fun.

Abrasion puncture from damaged tyre #cycling

I’ve had three punctures in short succession from abrasions with the tyre where the surface is damaged (cut, sliced, worn, not sure sure which).
So I was going through these:

Like this:

(I’m proud I did a nice job of that one)
The Park Tool VP-1 Patch Kit does a pretty good job, no chalk or crayon though, so the child in me was disappointed.

I ordered some new tyres and whilst I was waiting for them I stuck patches (pre glued kind) on the abrasions to last me a couple of hundred miles:

I was impressed by the Super Patch kit by Park Tool, very sticky, so care required to not stick it to itself. They really protected the inner tube until my new tyre arrived.
I’m a vulcanising solution man for permanent repairs as the puncture repair at the top shows, but these pre glued ones seemed better fitted to this task (and quicker).

SQL Server: Updating XML fields – XPath, XQuery – #sql #sqlserver #xml #xpath #xquery

Further to my post SQL Server: Querying XML fields – XPath, XQuery, I’d like to expand on this and cover updating XML fields too.

Unfortunately it appears you can only do one update at at a time, so often looping is required, extracting data into temporary tables or complex XPath.
I’m going to cover the later as it’s one that isn’t so well written about.

The great thing about SQL Server is you can use the XPath String Functions, for example I’ve used the concatenation and substring functions before and here is a simple example to show how they can be used. Of note is also the neat way to include SQL variables into the XPath.

-- Create a temporary table for the example, drop if already exists

BEGIN TRY
DROP TABLE #T1
END TRY
BEGIN CATCH
END CATCH

CREATE TABLE #T1
(Val XML)

-- Stick in some XML data

INSERT INTO #T1 (Val) VALUES ('<root xmlns="http://tim.xsd">
  <branch>
    <leaf name="displayname" type="String">cycling</leaf>
    <leaf name="alternativename" type="String">is sometimes fun</leaf>
  </branch>
</root>')
INSERT INTO #T1 (Val) VALUES ('<root xmlns="http://tim.xsd">
  <branch>
    <leaf name="displayname" type="String">running</leaf>
    <leaf name="alternativename" type="String">is sometimes fun</leaf>
  </branch>
</root>')

-- Update the cycling row using a SQL variable and some functions (concat and substring)

DECLARE @timvar VARCHAR(MAX)
SELECT @timvar = ' is always '

UPDATE #T1 SET Val.modify('declare namespace TIM="http://tim.xsd";
  replace value of (/TIM:root/TIM:branch/TIM:leaf[@name="alternativename"]/text())[1]
  with fn:concat(
    "Everybody knows that ",
    (/TIM:root/TIM:branch/TIM:leaf[@name="displayname"]/text())[1],
    sql:variable("@timvar"),
    fn:substring((/TIM:root/TIM:branch/TIM:leaf[@name="alternativename"]/text())[1], 14, 3))
')
WHERE Val.query('declare namespace TIM="http://tim.xsd";(/TIM:root/TIM:branch[1]/TIM:leaf[@name="displayname"]/text())[1]')
.value('.', 'NVARCHAR(MAX)') = 'cycling'

-- Check the result

SELECT * FROM #T1

And the result is:

<root xmlns="http://tim.xsd">
  <branch>
    <leaf name="displayname" type="String">cycling</leaf>
    <leaf name="alternativename" type="String">Everybody knows that cycling is always fun</leaf>
  </branch>
</root>

<root xmlns="http://tim.xsd">
  <branch>
    <leaf name="displayname" type="String">running</leaf>
    <leaf name="alternativename" type="String">is sometimes fun</leaf>
  </branch>
</root>

So you want a Media Server? Here’s how to… #music #video #images #linux #mediatomb #samba

Want to share photos, music and videos and not have to sync it to the sky or pay for the privilege?
Then read on…

I have two laptops, a tablet and a capable mobile phone in the house now, so the PC (Windows XP Pro) is sitting idle, “why not turn it into a media server?” I thought, but I didn’t want to shell out for any special software or a newer version of Windows.

It turns out all you need is Ubuntu (or Debian) Linux and nothing else!
It is also free and the recent versions contain MediaTomb; the key piece of software that’s needed.

Get the software

For proof of concept purposes I installed this within Oracle VM VirtualBox (which is free) so I could fiddle without actually destroying my Windows PC installation (yet). So I first downloaded this from https://www.virtualbox.org/wiki/Downloads and installed it.
(version 4.1.8 in my case)

Next I downloaded Ubuntu (which is free) from http://www.ubuntu.com/download/ubuntu/download
(version 11.10 32bit in my case, get the 64bit version if you have more than 3.7GB of RAM)

If you want to install on the PC directly then you will need to burn the Ubuntu .ISO image file to a CD or DVD. For an install into VirtualBox I skipped this step.

Install Ubuntu

For PC install you boot from the CD/DVD you’ve burnt; install as you would any operating system.

For VirtualBox, create the VM ready for Ubuntu:

Then start it and select the .ISO image file:

Then follow the installation instructions (for either PC or VirtualBox).

Notes on installation

The only thing I would like to draw attention to is the MP3 plugin, I’d install that:

Configuration and service installation

OK, so MediaTomb isn’t installed as such, so you need to install it, fortunately this is easy.
Select the Ubuntu Software Centre:

Then search for MediaTomb by typing into the search box, it will dynamically search for it, select it like so, then click install:

Once installed you will need to make a couple of configuration changes. Open the Dash:

Then search for terminal:

Click on Terminal to open it.
Then to open the config file in a text editor, type

sudo gedit /etc/mediatomb/config.xml

Now enable the user interface, change the setting for <ui enabled=”no“…> to <ui enabled=”yes“…>.
Next change <virtual-layout type=”builtin“> to <virtual-layout type=”disabled“> otherwise you will get 5 of everything, this is something you can lookup if you want to.
If you want to stream to a PS3 then you will want to enable transcoding, I don’t have one, but the option to change is <transcoding enabled=”no“> to <transcoding enabled=”yes“>.

Save and close.

You can now either restart Ubuntu or restart the service from the command prompt:

sudo /etc/init.d/mediatomb restart

Configure content locations

First off you need somewhere to store the content, so create some new folders and give them appropriate security permissions, to do this open terminal again and type the following:

sudo mkdir /mymediaserver
sudo mkdir /mymediaserver/music
sudo mkdir /mymediaserver/video
sudo mkdir /mymediaserver/images
sudo chmod -R 775 /mymediaserver

The Linux gurus will want to get their filesystems in order, but the rest of us will have one filesystem and this will be fine.

Now register these locations in MediaTomb, search for MediaTomb from the Dash:

Open MediaTomb, it will open in FireFox most probably:

Click Filesystem then expand mymediaserver in the tree:

Now select each subfolder in turn then click the “add as autoscan dir” icon
You will want to choose appropriate settings, but I expect you will want to recursively scan the folder for changes every 30 minutes, like so:

Don’t forget to click Set each time.

Now disable the user interface again.
Open the config file in a text editor again via

sudo gedit /etc/mediatomb/config.xml

Change the setting for <ui enabled=”yes“…> to <ui enabled=”no“…>.

Save and close.

Samba (optional?)

We need some way to get new files into MediaTomb, a Windows File Share is perfect for seamless drag and drop of content, so I’ll go with that. Samba is the service to install, but there are a fair few steps, so keep with me, back in Ubuntu Software Centre search for samba and install the SMB server:

Once installed we add the users, search the Dash for User Accounts:

Open User Accounts:

Click the Unlock button before you start otherwise you won’t be able to make changes.
Click the + (Create a user) button, make it a standard user:

Then click Create. Unfortunately there’s more to do, we next need to enable the user, click on Account disabled:


Select Set a password now (if not already selected).
Enter a password and click Change.
Note: Unfortunately Microsoft have made some changes introduced in Windows 7 Home editions that make using a user name and password beyond the average user (i.e. there is no UI to change this setting), but if the user name and password you use for Windows are configured in Samba then you will be fine, so I will go for this approach for now.
Do this for every user you want, if two PCs have the same logon details then duplication is not required.
Linux is strict on security, so we need these users to be in the same group. To do this open Terminal again and type the following for each user:

sudo usermod -G sambashare <user>

Now to setup the security that Samba needs on the file locations via this sambashare group, type the following:

sudo chown -R :sambashare /mymediaserver

Now to configure Samba, open the config file via:

sudo gedit /etc/samba/smb.conf

Add the lines to the end of the file:

[music]
	path = /mymediaserver/music
	writeable = yes
	browseable = yes
	guest ok = no
	read only = no
	valid users = @sambashare
	force create mode = 0661
	force directory mode = 0775
	force group = sambashare

(repeat that for the other folders as required)

Save and close.

The final piece for Samba is to register the user with Samba via smbpasswd:

sudo smbpasswd -a <user>

Nearly there, just a quick note for VirtualBox, there is a network setting change required.
You will need “Bridged Adapter” and a Promiscuous Mode of “Allow All”:

Restart and verify

You can now either restart Ubuntu or restart the services from the command prompt:

sudo /etc/init.d/mediatomb restart
sudo /etc/init.d/smbd restart

You should be able to see the UPnP work its magic from a Windows machine under the Network part of Windows Explorer:

The MYMEDIASERVER (or indeed whatever you called your machine) – that is Samba working (assuming you configured it)
The MediaTomb – that is MediaTomb working

Add Media (requires Samba)

Open MYMEDIASERVER (or whatever you called it)
And drag and drop the files you want into music, video or images as required.
Note: Depending on settings chosen above it may take some time for the new content to be available through MediaTomb.

Stream it

Open MediaTomb from Windows Explorer / Windows Media player and enjoy

Security notes

The assumptions I’ve made are that you are behind a firewall in a nicely secure private LAN.
All the security settings above are my choice, you should review your security and choose your own options, if you are at all concerned then you need to seek professional help (or an IT professional). Remember to regularly backup your files and verify the backups too.

Join The Times and their cycle safety campaign #cyclesafety #cycling

I am a “cyclist” #cycling

Thanks to Dave for this one, I thought I’d share it.

http://www.xtranormal.com/xtraplayr/12674956/cycling-explained

I must be a “biker” as I only have two bikes and they are certainly not feather weights, neither am I for that matter.

Sheet, sheet, sheet #funny #excel

I’m not one for commenting on computer jokes, but today I burst out laughing in the office.

I quote
“Sheet… sheet, sheet, sheet, ah, that one…”
“Which?”
“That sheet!”
“OK, so now we’ve got the sheet, what do we do with him?”

Of course they were talking about Microsoft Excel Worksheets, but still, I couldn’t contain myself. I never thought Excel would make me smile.

SQL Server: Read only views via GROUP BY and UPDATE FROM pitfalls #sql #sqlserver

I often hit a pitfall when doing an UPDATE FROM when it is from a table INNER JOINed with itself but I only want to update one side of the join. However SQL Server would complain that it doesn’t know which Table_1 is to be considered anyway. Why would I be doing this? Imagine a table with a parent child relationship, so an id column and a parent id column that contains the id of the parent record. If I wanted to update children based on some criteria of the parent I would look at doing an UPDATE FROM with the table inner joining to itself on id equals parent id with the where criteria I wanted.

I get around this by using views, but it can be tricky to make sure I only update the child records and not the parent records, so I employ a mix of table and view. Unfortunately there is no such thing as a read only query or a read only view, but you can do a few tricks to make them non-updatable. For a view a GROUP BY (or DISTINCT) is one way and that is what I’ll show too.

Here are a couple of examples of things that work and things that don’t, unfortunately I came up with the example before this article, so it isn’t specific to a parent child relationship, but still a good example:

CREATE TABLE [dbo].[Table_1](
	[id] [int] NULL,
	[val] [int] NULL
) ON [PRIMARY]
GO

-- Three rows.
INSERT INTO Table_1 (id, val) VALUES (1, 5)
INSERT INTO Table_1 (id, val) VALUES (2, 10)
INSERT INTO Table_1 (id, val) VALUES (3, 15)
GO

CREATE VIEW View_2
AS
SELECT id, val FROM Table_1 GROUP BY id, val
GO

-- Fails, because of GROUP BY, effectively the view is read only, without the group by this would work.
UPDATE View_2
SET val = 1

-- Fails, which Table_1 is to be updated? It doesn't make sense does it?
UPDATE Table_1
SET val = 1
FROM Table_1 t1
INNER JOIN Table_1 t2
ON t1.Id != t2.Id
WHERE t1.id = 1

-- Only 1 row affected, View_2 is not updated but NOT because of GROUP BY, it is not updated because we are updating Table_1
-- The query actually returns two rows (or all three if you see what I mean), try it for yourself.
UPDATE Table_1
SET val = 1
FROM Table_1 t1
INNER JOIN View_2
ON t1.Id != View_2.Id
WHERE t1.id = 1

-- The exception to this is if Table_1 isn't in the FROM part. So the following statement updates all rows even though you might think View_2 should make this fail; nasty!
UPDATE Table_1
SET val = 1
FROM View_2

An alternative to a view is to use the WITH statement, like so:

WITH v AS (SELECT id FROM Table_1)
UPDATE Table_1
SET val = 1
FROM Table_1 t1
INNER JOIN v
ON t1.Id != v.Id
WHERE t1.id = 1

The hill and bicycle verses car rule #cycling #hills #cars #baddriving

Today I formulated the “hill and bicycle verses car” rule, it’s been on my mind for a few months now, but an impatient driver this morning finally spurred me on.

I’ve notice on my commute there are a couple of small hills where my speed drops right down, there are no cycle paths (or footpaths for that matter) so I am stuck in the road, slogging it out up the hill. I’m happy with this, if the road is clear the other way, drivers overtake, fine. If the road is not clear there are two camps drivers fall into:

1) The patient driver. I salute you. For them I try my best to cycle up the hill as quickly as I can. They wait patiently for a space to overtake and they give me hope that one day all drivers will be the same.

2) The impatient driver. They honk their horn, shout abuse, rev their engine loudly etc. For goodness sake, if I could go up the hill quicker or cycle on a cycle path I would, anything to get away from bad drivers like them, however there is no option. My responses to them can vary, but tend to include:
a) Moving further out, thus discouraging dangerous overtaking that they are more likely to do (compared with a patient driver)
b) Slowing down further, I’m not going to bust a gut up a hill for them

To summarise, the patient driver wins.