Attackmonkey Blog

Over the years (and it's been quite a few years now) I've come to realise that one of the most important skills a developer can have is also probably of the most underrated. The ability to methodically investigate and get to the bottom of a problem.

The ability to really dig into an issue and try and track down what's going on is a really important skill for a developer to have. Often you might be working on something that was written by someone who no longer works at a company so you have no way of knowing why something was done, or in some cases how. The issue might be super obscure, or be very hard to replicate, but if you can doggedly Sherlock the shit out of your problem, you'll eventually get to the bottom of the issue.

All to often I've seen people just have a quick look at the code, stick a question on SO and forget about it, when they could just spend a bit of time digging into the issue to try and work out the solution. If nothing else, it'll help you to understand the bit of code you're working on.

So remember, if you hit a problem, do a bit of investigating!

If you're doing a one to one translated language site, you'll want to be able to switch the language. You could do this by setting a cookie and reloading the page, but that's crap for SEO, and means users can't share links to the pages in the different languages.

Conventional wisdom states that you should include the language in the URL in some fashion. One option is to include it in the querystring (don't laugh, I've seen this done in the wild). DON'T!!! You then have to remember to pass the variable around everywhere, and it's easy to forget, so the selected language randomly gets lost, and the user switches language without meaning to. You can also have different subdomains/domains for each language, but that's a bit faffy to set up and configure, especially if you're continually adding new languages.

Ideally, you want to make the language code part of the URL. I've seen site that add it to END of the URL, like "/my-page/de/", this is also not great. It makes the URLs harder to hack around. Take "/about-us/awards/" for example, if you knock off the awards part, you know you're getting the about us page. If you have the language at the end, if you don't leave the language on the end when you hack off the awards part, you get the about us page in English, not German.

For this reason, you should always include the language at the START of the URL, that way you can hack around to your heart's content, and the language will be kept!

If you're using Umbraco, you can do clever things with UrlProviders and Resolvers to automatically include the language in the URL without much extra work (I'll blog about this at a later date).

I've not blogged for a while, and I've decided to doa series of shorter posts highlighting some useful techniques I've come across recently. Here's the first one!

In Umbraco, you may often have some task that manipulates content/media etc via the various service APIs. For example you might have a scheduled import task that imports pages froma feed, or a task that pulls in content or modifies content based on things happening in a thiord party system.

By default, all actions carried out using the services are flagged against user 0 (the default Admin user). This is fine, but it means it's hard to tell what changes were made by your tasks, and which were made by the ACTUAL administrator.

Most ofthe Save and publish methods have an additional parameter for User ID, which can be the ID of any back office user. The fun part, is that the user doesn't have to actually be able to access the back office. So you can create a user, called something like "Task User" (you could even create a different user for each task if you wanted), and ythen disable it's Umbraco access with the check box. Then update all of your code to include the user ID.

So this:

Services.ContentService.Publish(myContent);

Would become something like this:

Services.ContentService.Publish(myContent, TaskHelper.TaskUser);

This means you can now pin down in the audit trail if content issues were caused by actual CMS users, or by one of your tasks!

Why did I start doing this? One of my clients inherited a site that had a public page that had a link to call a controller that deleted ALL of the content of a specific type, that no one new about until they had a pen test done, and the testers clicked on the link, deleting a sizeable chunk of important content on the site. The delete by content type method also permanently deleted the pages in question (but not the sub-pages). As we didn't know the page existed, or that the pen test was being run, it took us a while to track down the culprit. Had we had actions flagged against a task user, we'd have known straight away where to look!

One thing to note, if you are manipulating content through the APIs, you should keep it to a minimum to avoid clogging up your versions table. Only update stuff if you need to. If you do need to use lots of updates, it's worth installing something like FALM Housekeeping, or Unversion to keep things under control.

Recently I was tasked with doing some Crazy Assed Shit™ with Umbraco Contour. Note, this applies to old school Contour, the techniques outlined here won't work on Forms, as some of the required information is no longer in the database.

The client wanted to be able to export ALL of the forms on the site, merged into one giant CSV file. With the columns across the top of the spreadsheet. So effectively the output would be something like:

Form Name, Record ID, Date Submitted, Field 1, Field 2, Field 3, etc

Now, the requirement also stated that fields with the same caption should be merged together into one column.

Looking at the APIs, there are two options:

• Use the library methods. There were three issues with this, one it returns XML, and two the caption names that are stored in the XML are only correct at the time of saving. If the field name has been updated, the caption will be wrong, so you'll end up with extra columns that shouldn't be there, finally, you have to get the results one form at a time, so there would be some horrific logic around grouping the fields.
• Use the database driven API methods. This solves the stale caption names issue, but again you have to grab the results one form at a time, and the API is REALLY database intensive, which makes it pretty slow for deaing with large numbers of records at once.

So after scratching my head for a bit to think of a better way of doing it, preferebly in a single database call, I decided that you could do the whole thing, in one go (ish), using a pivot query. This is a special kind of query that allows you to turn rows into columns, they're commonly used in Excel and SQL Server reporting.

The only issue that would cause any problems is that you can't directly do a pivot query without knowing the column names. They HAVE to be hard coded into the query. The only way round that is to use dynamic SQL.

First things first, we're going to create a view that contains all of the raw data that we need to perform our magic. This will make the pivot query MUCH easier to read, and it also provides around a 20% perfomance boost to the final query, as we need to use the data twice (once to get all the captions, and once to actually do the pivot query). Create a new view, and use the following query:

SELECT dbo.UFRecordFields.Record, dbo.UFFields.Caption, dbo.UFRecords.Created AS [Date Submitted], dbo.UFRecords.IP, dbo.UFForms.Name AS [Contour Form Name], dbo.UFForms.Id, 
    CASE WHEN UFFields.PreValueProvider != '00000000-0000-0000-0000-000000000000' THEN STUFF
    ((SELECT     ',' + a.Value
        FROM UFPrevalues a LEFT JOIN
        UFRecordDataString b ON CAST(a.Id AS Nvarchar(255)) = b.Value
        WHERE a.Field = UFFields.Id AND b.[Key] = UFRecordFields.[Key] FOR XML PATH('')), 1, 1, '') ELSE COALESCE (CAST(UFRecordDataBit.[Value] AS Nvarchar(255)), 
        CAST(UFRecordDataDateTime.[Value] AS Nvarchar(255)), CAST(UFRecordDataInteger.[Value] AS NVarchar(255)), CAST(UFRecordDataLongString.[Value] AS nvarchar(MAX)), 
        UFRecordDataString.[Value]) END AS FieldValues
FROM dbo.UFRecordFields LEFT OUTER JOIN
    dbo.UFRecordDataBit ON dbo.UFRecordFields.[Key] = dbo.UFRecordDataBit.[Key] LEFT OUTER JOIN
    dbo.UFRecordDataDateTime ON dbo.UFRecordFields.[Key] = dbo.UFRecordDataDateTime.[Key] LEFT OUTER JOIN
    dbo.UFRecordDataInteger ON dbo.UFRecordFields.[Key] = dbo.UFRecordDataInteger.[Key] LEFT OUTER JOIN
    dbo.UFRecordDataLongString ON dbo.UFRecordFields.[Key] = dbo.UFRecordDataLongString.[Key] LEFT OUTER JOIN
    dbo.UFRecordDataString ON dbo.UFRecordFields.[Key] = dbo.UFRecordDataString.[Key] LEFT OUTER JOIN
    dbo.UFFields ON dbo.UFRecordFields.Field = dbo.UFFields.Id LEFT OUTER JOIN
    dbo.UFRecords ON dbo.UFRecords.Id = dbo.UFRecordFields.Record LEFT OUTER JOIN
    dbo.UFForms ON dbo.UFRecords.Form = dbo.UFForms.Id

You might get an error about the query, but it should still save (the error is because one of the functions can't be represented in the diagram pane if you have it open). Save the view as "vwContourFieldValues". The resulting view contains ALL of the submitted field values for every submitted record in the database. It also concatenates prevalue fields that allow multiple selections into a single comma separated string.

Now that the view is in place, we can write the SQL to pull out the pivot table. Basically, we will have to do two things. Firstly, pull out all of the column names to use in the query, and secondly build some dynamic SQL to use those column names in the pivot query.

Here's the code:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
 
--Get distinct values of the PIVOT Column (Contour Field Captions) 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
    + QUOTENAME(Caption)
    FROM (
        SELECT DISTINCT Caption
        FROM vwContourFieldValues
    ) AS Courses ORDER BY [Caption]

    SET @DynamicPivotQuery = 
        N'SELECT [Contour Form Name], Record, [Date Submitted], IP, 
        ' + @ColumnName + '
            FROM vwContourFieldValues

            PIVOT(MAX(FieldValues) 
            FOR Caption IN (' + @ColumnName + ')) AS PVTTable
            ORDER BY [Contour Form Name] ASC, [Date Submitted] DESC'

            --Execute the Dynamic Pivot Query
            EXEC sp_executesql @DynamicPivotQuery

Once you have the query written, you can run it, and it will list all of the records in the database, with all of the columns, and all of the records with the same caption will be kept in a single column. You can then output the results to CSV, so the client can open it in Excel and do any crazy data mining or reporting that they desire.

This is a pretty simple example, but you could easily modify the query to filter the forms by date range, or allow them to choose the forms that they want to bulk export.

I've tested this query on a site with around 70 forms, and over 150,000 records, and it took just over two minutes to run. Given the resulting CSV file has something like 200 dynamic columns, that's not too bad. If you run it on a site where the forms share more fields, it's MUCH faster. You could probably make this even faster by using temporary tables, indexed views, or other performance optiomisations.

As I mentioned at the beginning of this article, the code here won't work on Forms, as the Forms themselves are no longer stored in the database. You'd have to do some crazy voodoo involving getting all the forms, and creating a temporary lookup table with all the GUID/Caption pairs for every form on the site. I'm not 100% sure on how you'd get the prevalues back either, as they also don't appear to be in the database any more.

I may try and figure it out though and post an example of how to do this in Forms if I work it out.

In a departure from the usual techy stuff, I'm going to post about something quite close to my heart. Staying positive. I'm quite an upbeat and positive person, but that wasn't always the case. It took borderline work burnout and serious illness to get me to sort my shit out.

So, a bit of background. Back in early 2005, I'd pretty much burnt myself out working myself to the bone as the lead programmer at an ambitious startup, for an extremely demanding boss. I was so miserable, that I seriously considered sacking off web development entirely, and retraining to do something completely different (top of my list was touring road crew, but the missus vetoed that one).

Several of my self employed friends (none of whom work in web) inspired me to try my hand at freelance, it was something that I'd always fancied, but I didn't think I was good enough. After a couple of months, I'd fallen back in live with web dev, and I realised it was the toxic working environment that burnt me out, not the industry. All was good, until I got ill. I had a wracking cough that just wouldn't quit, and I had no energy. For almost a year, I was a complete medical mystery, until in 2006, after some surgical exploration, I was diagnosed with Stage IVB Hodgkins Lymphoma (an immune system Cancer). As bad new goes, that's up there with "yup, your house is built on a spooky burial ground".

It was at this point, I started my journey to being more positive. 12 months of being a medical mystery is RUBBISH. Had I been any good at music, I'd have written some killer blues songs. As I led in bed with the Oncologist explaining my diagnosis, I was actually happy, I think he thought I'd lost the plot. But actually finding out that my illness had a name, and could be fought was enormously liberating. From that point on, I focused on the positives. Throughout the six months of chemo I had, I remained positive and upbeat, much more so than I would have imagined possible. Having a health scare like that really made me focus on what was actually important. I've learnt a lot from the experience, and some of the inspiring people that I met along the way.

Right, enough background! Staying positive is all about realising what's important, what you do and don't have control over, and looking for the opportunity when things go bad.

Realising What's Important

We live in extremely information rich times. Most of us carry devices around in our pockets that are more powerful than the first 7/8 computers I owned, COMBINED. The entirety of the internet is at our fingertips, and it's easy to get overloaded with information, making it hard to focus on what really matters. It works for work as well as personal stuff. Learn to filter out the stuff that doesn't matter and focus on the things that do. Stop spending all your time reading about other people's lives, and concentrate on your own.

With work it's easy to get caught up in the bigger picture and get sidetracked. Break your project down into smaller parts so you can concentrate on the individual pieces and it gets simpler. Also, learn to filter the important stuff from a client brief. It's sometimes easy to get really caught up on a feature that's actually pretty inconsequential.

Control

People have a tendency to stress about things they have absolutely no control over, and worry about some nebulous future event. I find the trick is to focus on what you're doing now. It's also important to realise that there are some things you have very little control over and just accept it and take charge of the things that you can.

One example of this would be me worrying about relapse. I know people who have literally lost their mind worrying about getting ill again after recovering from Cancer. They constantly fret about every little thing they do, terrified that they're making it more likely they'll get sick again. I look at it this way, as long as I avoid anything too stupid (smoking, hanging out in chemical plants without a mask, swimming in Benzene), I'm probably good. I'd rather enjoy the extra time I have, than lock myself up an be paralyzed with fear about being ill again.

You can plan for some eventualities, but accept that sometimes things happen for no real reason!

Look for the Opportunity

One of the things I learnt early on was to look for the positives in the bad. I got diagnosed with Cancer, I could easily have collapsed in a weeping heap and spent the next 6 months rocking backwards and forwards. I admit, I cried a bit, but I also saw my diagnosis as a positive step on the way to recovery.

Been laid off? Take it as a sign that it's time to move on. Upskill and move on to better things, don't waste your energy being bitter and focusing on the past. Project didn't work out as well as hoped? Look for the bits that DID work and take those away as something you can use next time.

Perspective

Finally, one thing that really helps is to have some perspective. For the majority of us, what we do isn't life and death. If a menu doesn't line up perfectly in IE8, no one is going to die (except maybe the IE devs, as they get hunted down by disgruntled web devs, one by one). Sure, you should fix it, but it's not worth getting super stressed over.

Make sure that you take a step back every now and again and have some you time. As an industry, web dev is close to game dev for the amount of crazy overtime that's expected, often down to poor planning or lack of resources. Take a step back every now and again and do something you want to do for you. Since I hit remission, I've tried to do at least one thing a year that I've always wanted to do, and man does it recharge my batteries. I learnt to snowboard at 30 (and I love it), I taught myself SLR photography and I even climbed Mount Fuji in Japan (something I've wanted to do since forever). When I come back from a week on the slopes, my creative batteries are fully recharged, and I feel amazing. It doesn't even have to be that exciting, go for regular walks in the countryside, take up cycling, it all helps :)

I realise that a lot of this is probably obvious to most people, but it's surprising how many people I come across in  web dev who get burnt out, often for the same reasons each time. I hope this helps someone, or at least gives them some food for thought!