Namespaced SQL Columns in Queries

One of the things I really like about the C# language is the wide range of database ORM and data mapper packages available. In a nutshell an ORM can help remove the need to write SQL queries and stored procedures in your database, instead you tell it what the “model” of your data is (i.e. the schema of the tables and how they relate to each other) and the ORM constructs SQL queries to fetch, update and add data. Data mappers do much the same thing.

A simple example would probably be helpful. This is psuedo-code, it’s not from a real ORM:

// create the connection to the database which we have modelled
MyDatabaseModel myDb = new MyDatabaseModel("localhost", "username", "password");

// get all records from the MyTableType table and cast them to MyTableType objects
IEnumerable allTheThings = myDb.MyTableType.Get();

// create a new MyTableType object
MyTableType newThing = new MyTableType {
Column1 = "Hello";
Column2 = "World";
};

// insert it into the database
myDb.MyTableType.Insert(newThing);

You can imagine this saves a LOT of time and effort, particularly for applications with a simple database schema. I’ve used several ORMs including Entity Framework (big, powerful, complex) and Linq2SQL (small, quick, easy, only works with SQL Server). I do want to try some others, in particular Rob Conery’s massive.

In PHP there are ORM packages available, too, although I admit I’ve not used any of them. Sometimes, however, you need to inject a bit of cleverness into your database access code to help speed up development, but don’t have time (or inclination) to rewrite the system around an ORM.

Repetitive SQL

This was the situation I found myself in the other night. I had a medium-sized application with lots of queries, and some tables that were being accessed a lot. I found I had very repetitive SQL strings like this:

select t1.col1, t1.col2, t2.col1, t2.col2
from table1 t1
inner join table2 t2 on t2.parentid = t1.id

Fortunately I’d put all the queries into two sets of files:

  • The classes for my types, each one of which had its own Get(), Save() and Delete() methods
  • Manager classes which handle loading of lists of object, or loading sets of related objects

The problem was I had used the same column names across many tables. This code:

select t1.col1, t1.col2, t2.col1, t2.col2

Gives a set of results that looks like this:

col1 col2 col1 col2
Table 1, Row 1, Col 1 Table 1, Row 2, Col 2 Table 2, Row 1, Col 1 Table 2, Row 2, Col 2

Which “col1″ column belongs to table1, and which to table2? I was in a pickle, and no mistake. What I needed was a way to namespace the columns I returned.

Namespacing

Namespacing is simply a way to group related functionality together in a system. You can imagine a folder structure on a hard drive being like many namespaces:

/My Documents/
/My Documents/Photos/
/My Documents/Photos/Holidays/
/My Documents/Music/
/My Documents/Music/County-AND-Western/

You know where all the music of both types (country AND western) are because the folder structure clearly shows you the way.

In C# the different levels in a namespace are separated by a dot, for example:

MyApp.UI.Helpers.DateHelpers

I thought I could use this same naming convention for my queries. Also the SQL language itself uses dots to denote the boundary between a table and its columns:

select table1.*
from table1
where table1.id = 123

After a bit of experimentation I found that as long as the column names were put in quotes I could return pretty much anything I wanted. For example:

select t1.id as 'this.is.my.test'
from table1 t1

So I realised I could namespace all the columns by the table they belong to, like this:

select
t1.col1 as 'table1.col1', t1.col2 as 'table1.col2',
t2.col1 as 'table2.col1', t2.col2 as 'table2.col2'
from table1 t1
inner join table2 t2 on t2.parentid = t1.id

Now the result set I get back looks like this:

table1.col1 table1.col2 table2.col1 table2.col2
Table 1, Row 1, Col 1 Table 1, Row 2, Col 2 Table 2, Row 1, Col 1 Table 2, Row 2, Col 2

Much better! I can query the columns for each table safely.

Making namespaced column queries easy to use

Now I’d proved the concept I did some work to make it easier to use these namespaced columns. Firstly I added a new method to each of my classes which returned a string which I could use in queries.

As this application is a plugin for WordPress I am using the global $wpdb object to do my database access. Here is PHP for a small, simple class:

class MyType {

// the properties of this class
var $id;
var $name;

// returns a string for all the columns in this class
function ColumnQuery( $prefix ) {
$s = "%1.id as '%2.id', %1.name as '%2.name'";
$s = str_replace( "%1", $prefix, $s );
$s = str_replace( "%2", "MyType", $s );
return $s;
}

// loads the row with the given ID
function Load( $id ) {
global $wpdb;
$sql = $wpdb->prepare( "select
" . MyType::ColumnQuery( "t1" ) . "
from {$wpdb->prefix}MyType t1
where b.id = %d;",
$id );
$row = $wpdb->get_row( $sql, ARRAY_A );
$this->MapRow( $row );
return $this;
}

// maps a database row to the current object
function MapRow( $row ) {
$this->id = $row["MyType.id"];
$this->name = $row["MyType.name"];
}
}

// create a new instance of a MyType object
$myType = new MyType();

// populate the details of the $myType variable with the details from row 123
$myType.Load(123);

One really important think to note is that I use the ARRAY_A parameter in the $wpdb->get_row() method. This means that the columns are returned as an associative array, which is the only way I could access the namespaced columns because of the dots in them.

Something has just been dropped in my brain…

Actually, and this has only just struck me, I could probably use a different character than a dot and use the default column methods. Here’s quick psuedo-code using underscores instead of dots:

// returns a string for all the columns in this class
function ColumnQuery( $prefix ) {
$s = "%1.id as '%2_id', %1.name as '%2_name'";
$s = str_replace( "%1", $prefix, $s );
$s = str_replace( "%2", "MyType", $s );
return $s;
}

// maps a database row to the current object
function MapRow( $row ) {
$this->id = $row->MyType_id;
$this->name = $row->MyType_name;
}

Hmm, I might have to go back and do that as it’s a bit cleaner…

Table variables

The $prefix parameter in the ColumnQuery method means I can set what the variable for the table is, for example:

$sql = "select
" . MyType::ColumnQuery( "t1" ) . "
from {$wpdb->prefix}MyType t1";

In this case the table variable is “t1″. The resulting SQL string will be:

select t1.id as 'MyType.id', t1.name as 'MyType.name'
from MyType t1

Related tables

That’s great for single tables, but the real power comes when you use this structure for multiple related tables.

Imagine we have two related tables like this:

  1. Child
  2. Parent

These tables are related using a foreign key; a parent_id column in the Child table which stored the id of the Parent.

Whenever we load a Child object we also want to know the details of the parent. This is the kind of thing that an ORM system eats for breakfast. Using our new namespaced column query it’s also pretty easy. Here are the classes for the Child and Parent type:

class Child {

// the properties of this class
var $id;
var $name;
var $parent_id;
var $parent;

// returns a string for all the columns in this class
function ColumnQuery( $prefix ) {
$s = "%1.id as '%2.id', %1.name as '%2.name', %1.parent_id as '%2.parent_id'";
$s = str_replace( "%1", $prefix, $s );
$s = str_replace( "%2", "Child", $s );
return $s;
}

// maps a database row to the current object
function MapRow( $row ) {
$this->id = $row["Child.id"];
$this->name = $row["Child.name"];
$this->parent_id = $row["Child.parent_id"];

// if we have columns from the parent table then populate the parent object
if ( $row["Parent.id"] != "" ) {
$this->Parent = new Parent();
$this->Parent->MapRow( $row );
}
}
}

class Child {

// the properties of this class
var $id;
var $name;

// returns a string for all the columns in this class
function ColumnQuery( $prefix ) {
$s = "%1.id as '%2.id', %1.name as '%2.name'";
$s = str_replace( "%1", $prefix, $s );
$s = str_replace( "%2", "Parent", $s );
return $s;
}

// maps a database row to the current object
function MapRow( $row ) {
$this->id = $row["Parent.id"];
$this->name = $row["Parent.name"];
}
}

This is the magic bit:

// if we have columns from the parent table then populate the parent object
if ( $row["Parent.id"] != "" ) {
$this->Parent = new Parent();
$this->Parent->MapRow( $row );
}

If the system detects that we have columns from the parent table then we can fully populate the parent property. Here’s the Load() method for the Child class that would load and populate a Child instance with the details of its Parent:

class Child {
function Load( $id ) {
global $wpdb;
$sql = $wpdb->prepare( "select
" . Child::ColumnQuery( "c" ) . ",
" . Parent::ColumnQuery( "p" ) . "
from {$wpdb->prefix}Child c
inner join {$wpdb->prefix}Parent p on p.id = c.parent_id
where c.id = %d;",
$id );
$row = $wpdb->get_row( $sql, ARRAY_A );
$this->MapRow( $row );
return $this;
}

So we would load a child object like this:

$child = new Child();
$child->Load( 123 );

And we can access details of the child and it’s parent like this:

print "ID: " . $child->id . "
";
print "Name: " . $child->name . "
";
print "Parent Name: " . $child->parent->name;

The same approach can be used when loading multiple rows. For example here’s some SQL to load all children:

$sql = $wpdb->prepare( "select
" . Child::ColumnQuery( "c" ) . ",
" . Parent::ColumnQuery( "p" ) . "
from {$wpdb->prefix}Child c
inner join {$wpdb->prefix}Parent p on p.id = c.parent_id;";

Wrapping up

This technique helped me out of a little hole this week, hopefully it can help you as well.

Responsified and updateified

That old proverb about the cobblers children going barefoot is true. For too long this site has been neglected and unloved. Well, no more! I’ve realigned (not redesigned) this site to take advantage of modern web development techniques. Of course, I’m talking about responsive design.

It’s not a radical departure from what was there before, and talking advantage of the excellent Bones starter theme meant the whole thing was done in a few hours. And even though I haven’t paid too much attention to performance I’ve still ended up with a respectable payload for the homepage:

Homepage payload

And even YSlow gives me a B grade:

YSlow grade B

I’ve also updated to WordPress 3.7, named in honour of the great Count Basie. The update was, it has to be said, not as slick as usual. I had “out of memory” problems which weren’t that hard to fix, but did cause me some stress.

WordPress 3.7 is the first version with auto-updating built in, so hopefully my installation will keep itself up to date and in tip-top condition while I’m asleep.

Missing the important stuff

A few days ago I found out a very good friend of mine had died several months ago. I hadn’t heard about it and, of course, I had missed his funeral. It was very upsetting news, especially because I had found out so long after the fact.

His passing had been announced on Facebook, but as I hardly go on that site (in fact I wonder why I have an account at all) I missed it. In just a couple of weeks those messages had dropped off my timeline, replaced by reams of banal tripe. The important things, in this case literally life and death, drowned out by the noise.

I can’t rant too much about this, I’m as guilty as anyone for posting (to Twitter, mainly) more than my fair share of trite nothings. It’s easy to be shallow online, and really hard to be genuine and heartfelt. For true relationships you need, at least occassionally, physical interaction. Words on a screen are a poor reflection of real life.

I’m not sure what the answer is. If I come off Facebook entirely then I may miss important news. But if I stay I may miss it too, unless I dedicate my life to keeping up with the firehose of messages.

Gah. I guess I just miss my friend.

Bad, good and great programmers

A recent tweet by Cory House started my brain a-twitching:

One of my new favourite interview questions: What is the difference between a bad, good and great programmer? Which are you and why?

I’ve worked with all three kinds, and thinking about the question of what the differences are I realised (not for the first time) that pure coding skill is nowhere near all the story. This blog post explores what else may put you in one category or another. First some assumptions:

  • As this is an interview question I took “programmer” to mean “one who works in a software development team”
  • The basics of being a decent employee – turning up on time, dressed appropriately and at least partially sober – are a given
  • The programmer can actually code to a decent level, “decent” being whatever is appropriate to fulfil the job requirements
  • The employer doesn’t just want a code monkey. I hope and trust that there are very few companies who want programmers to just “shut up and write the code”, and if you find yourself working for one of those then get out as soon as you can.
  • I’m talking to traditional programmers (C#, PHP, Python, Ruby, JavaScript etc) rather than front-end (HTML, CSS) but many of the points are applicable for any type of developer

Bad programmers

Generally, bad programmers are easy to spot. They write code that generally works (if they didn’t they’d be out of a job), but suffer from many or all of these issues:

  • They have a bad attitude towards non-geeks, whether non-technical colleagues or users
  • They have a bad attitude towards other programmers whose views don’t closely match their own
  • They know it all, or at least everything that matters
  • They are opinionated about tech; either wanting to use cutting edge stuff because it’s cool (whether or not it gets the job done) or they only trust very established things and refuse to look at new tech
  • Their communication skills are poor, and they have little desire to improve
  • They may fulfil specification requirements but don’t go any further than coding for exactly what is written
  • Their sense of humour is non-existent, or is harsh and rude
  • They don’t ask questions, and don’t make suggestions

It’s important to take all these points on balance. Some people may be shy, which could mean they don’t ask questions or make suggestions. But a good manager or lead developer will help staff to fulfil their potential, for example by encouraging the quieter members of the team to contribute.

The code from a bad programmer may also have these hallmarks:

  • Lack of comments, or comments that state the obvious and don’t tell you *why* they coded something a particular way
  • Obtuse, gnarly, over-abstracted, over-complicated architecture (at any level: method, class, subsystem or app)
  • Inconsistent or otherwise poorly-considered naming of variables, methods and classes
  • Lack of attention to detail, such as proper sanitisation of inputs for security
  • Copy-and-paste-itis. Bonus points if it’s clear they didn’t understand how the copied code works.

You get the picture. We’ve all written code like this at some point and we should be careful not to think of a programmer with less experience, or one who is struggling with a complex task, as a bad programmer. Bad programmers have a bad attitude, and even if they can mask that attitude for a while, eventually the mask will slip.

Good programmers

Good programmers, like bad, are known by their attitude as well as their technical skill. Note that a good programmer may not have as powerful code-fu as a bad programmer, but their attitude and “soft” skills more than make up for any technical shortcoming. (This is not to say that someone who doesn’t know XML from XTC can be classed as a good programmer; the basics mentioned above still apply.)

You’ll know a good programmer because:

  • They have a good, helpful and professional attitude towards colleagues and customers/users
  • They have respect for other programmers and are willing to listen – and when necessary respectfully disagree – to other points of view
  • They have a good sense of humour and contribute to a good atmosphere in the team
  • They exhibit attention to detail, and can spot the holes and inconsistencies in a specification
  • They are respected and liked by other members of the team
  • They don’t make rash decisions and have learned to weigh the pros and cons of different technologies
  • They can put themselves into the users’ shoes and make good decisions about how their code affects the user interface and experience
  • They have good communication skills, and avoid jargon wherever possible when communicating non-technical people

These kind of people make a team better, and so help an organisation to be better. But it’s not just soft skills, they also have a good grasp of technology:

  • Their code is readable and well-formatted, adhering to any coding standards adopted by the team
  • They choose sensible, descriptive and unambiguous names for classes, methods, properties etc
  • Their comments are actually useful: not just telling you what a method does, but why it is used – and any non-obvious things you may need to watch out for
  • Their architecture choices (at method, class, subsystem or any other app level) are clear, well-abstracted and only as complicated as it needs to be. My rule of thumb for “good” architecture: either you would have written it the same way, or you see why they did it their way and don’t need to change it much.

No doubt anyone with a bit of experience working in a development team can think of several more hallmarks of good and bad programmers. But what about great programmers?

Great programmers

While many people would roughly agree what makes a programmer good or bad, deciding what criteria to use to distinguish the greats ones is much more subjective. Here are a few points I can think of that, in addition to everything I noted for a good programmer, would class a programmer as great:

  • They are aware not just of the users’ needs and the specification, but the organisational environment. They can guide the development of an application so that it serves wider organisational purposes: in the case of a business to serve users and be commercially successful.
  • Their communication and “people” skills are highly developed; for example they can explain complex technical concepts to non-geeks in an understandable, even entertaining, way
  • They are known in the team as the person who will help you out with a problem: partly because they have the experience and knowledge, but also because they make themselves available to help others
  • They keep themselves up to date with technology – not just their own area of expertise but the wider world of development, UI, UX, project management and business
  • Their thinking in terms of abstracting code takes into consideration not just the next 3 months but the next 3 years (that *almost* means they can tell the future)

In short a great programmer has an expansive view of technology, of people, of the future of the organisation and applications they work on. They are part psychologist, part entrepreneur, part designer, part storyteller.

A couple of years or so experience and taking pride in what you do will take you from being a bad programmer to being a good one. But to be a great programmer takes a special kind of person. The kind that, eventually, will do something really special. Like write a great book, start a great company, write an amazing Open Source project.

There’s lots I haven’t mentioned here (such as writing testable code).For more on this subject check out these links: some are articles and essays, some are great programmers.

A note to myself

Nothing to see here, please move along!

Wakefield Cathedral: http://www.youtube.com/watch?v=B8JdEr_At4k

Jerusalem: http://www.youtube.com/watch?v=8RZLoiMiv38

BibleMap: http://biblemap.org/

Bible GeoCoding: http://www.openbible.info/geo/

May the Lord,
Mighty God,
Bless and keep you forever.
Grant you peace,
Perfect peace,
Courage in every endeavor.
Lift up your eyes and see His face,
Keep His grace forever.
May the Lord,
Mighty God,
Bless and keep you forever.