Friday, January 11, 2008

SQL GROUP BY Best Practice

SQL's GROUP BY clause can be an increadibly powerful tool for generating report data. It can, however, be a little tricky to get just right.

What it does is to "summarise" a set of data using a simple aggregate function (COUNT, MAX, MIN, SUM, etc...). In the multidimensional DB world (Oracle Express) we refer to these as "rollup axis" or "dimensions".

For example:

SELECT id, MAX(time), COUNT(id)
FROM table_1
GROUP BY id

Will display the greatest time for each id in the table and will give you a count of the number of times an entry with a unique ID exists in that table.

One of my co-workers pointed out a best practice that I'd totally forgotten about:

ALWAYS PUT THE AGGREGATES TO THE RIGHT OF THE LABELS AND MAKE SURE EXACTLY THE SAME LABELS APPEAR IN THE GROUP BY.

Tuesday, January 8, 2008

From C to Shining C

I remember back when I first started really programming in university how tough it was to get my head around pointers. Then, at about week 3, my brain kicked in and it all made sense. Sadly, many people don't get that ephiphany and go on to great success as Java programmers creating all sorts of enterprise applications.

Many universities noticed this trend and decided that pointers were too hard a concept to teach in a CS program and so they switched from C as the core CS language to Java.

In VB/Java/Python, you can glue three strings together by going:

str = "Foo" + " " + "Bar"
print str


But in C, you have to go:

char * foo = "Foo";
char * space = " ";
char * bar = "Bar";
int nLenFoo = strnlen(foo, 20);
int nLenSpace = strnlen(space, 20);
int nLenBar = strnlen(bar, 20);
int nBuffSize = nLenFoo + nLenSpace + nLenBar + 1;

char * buff = (char *) malloc (nBuffSize * sizeof (char))

if (0 == buff) exit(1);

char * tmp_buff = buff;

if (tmp_buff != memcpy(tmp_buff, foo, nLenFoo))
exit(1);

tmp_buff += nLenFoo;

if (tmp_buff != memcpy(tmp_buff, space, nLenSpace))
exit(1);

tmp_buff += nLenSpace;

if (tmp_buff != memcpy(tmp_buff, bar, nLenBar))
exit(1);

fprintf(stdout, buff);


Why is this so much more difficult to do in C then it is in VB? Because string concatenation is NOT a simple problem!

Slashdot has an article up today about the downsides to teaching a generation of programmers to use Java instead of C. It's main thrust is that Java programmers can't build new computer systems.

Freakin' D'uh!

I remember having heated conversations as a student advocate at Dalhousie University with a number of professors regarding the issue of teaching Java as a first language. Their argument was that we had an 80% drop out rate from first year to second year and we needed to figure out how to carry more students forward. I felt at the time (as I do now) that you achieve this goal by teaching the basics BETTER not by dumbing it down and carying the hopeless ones forward anyways (who then struggle in the OS class, or in the AI class or in the DB class or in the Algorithms class, etc...). I even made the argument that all code should be taught in assembly and that even C was too high a level to teach CS at.

C has always been, and will always be, the basic language of computer scientists. It is the closest thing we have to easily understandable machine language. It's biggest deficiency is also its biggest asset: it so terribly easy to shoot yourself in the foot with C and so terribly difficult to do many trivial things (like string manipulation).

But that's because C shows you the real nuts and bolts of what's going on.

There are many things that can go wrong in the process of assigning three seperate blocks of memory to be held in a third block. And the way that Java/VB get around this is the same way that President's Choice gets around not being able to cook: someone else does it for them and they simply have to "re-heat" the code. Under the covers, the VB/Java syntax is doing exactly the same thing that the C code is. It's just hidden from the end programmer (much like how much of a computer program is hidden from the end user).

VB/JAVA programmers look at the system from an end-user point of view, rather then from the computer's point of view. When it comes to taking someone else's ideas and putting them into the computer, that's what VB/JAVA types are good at (and there's a lot of work around for doing exactly that). But they rely upon the C programmers who implemented the lower-level libraries that actually DO the operation that the VB/JAVA programmer wants to do.

Students who aspire to be real computer science professionals, be they termed software engineers or systems analysts or software developers or simple programmers, MUST understand the intricacies of the memory heap, I/O interupts and CPU instructions if they want to be able to craft and improve the underlying computer systems in the future.

And frankly computers don't run on Java: programmers do.

Now we just have to invent an ultra-productive do-everything lanuage called BEER.

Monday, January 7, 2008

Verbosity

I tend to get teased a bit about my use of words that are a little, well, *bigger* then average. I use obviate and plethora and vitiate in regular conversation, partly because I'm a geek, but partly for what I feel are two very important reasons:

1) I understand the word and it is the most accurate word to describe what it is I'm trying to say.

2) Long words have a unique cadence that imparts an important rhythm to a sentence.

Not everything can or should be said tersely and professionally. Sometimes thoughts need that rhythm, that cadence, that verbal colour, to communicate the passion with which a thought is held or the nuance involved in its entirety.

There is an art to speech which needs to be preserved and ofttimes that means preserving not just the essence of a message, but it's entirety. Dumbing it down, or tightening it up may make for more effective communication but much less rich art...and I've a theory that we need a lot more art and a lot less simple clarity in our lives.

Steve Yegge has a theory about blog posting size that follows along the same line of thought. In his article, he's talking about commentors mentioning that his posts are too long and that they contain a lot of extraneous information. His point is that instead of writing journalistic-style aricles he's writing thought-provoking essays and that in order to get the thoughts out properly on paper, he has to write more to do it.

Big words fall into the same category. They make the information more complex, more rich and more comprehensive. They make it more beautiful and give it a richer aesthetic. In short, big words, or verbiage maximus
make thoughts more interesting.

And after all, isn't the whole point of writing to put down on paper something that needs to be recorded?

Saturday, January 5, 2008

Inaugural Veritas

I've never published a blog before.

I've never even kept a journal for more then 5 minutes without getting distracted by something shiny.

But I've come to the realisation that the next evolution in prevelant prognostication is to start writing down these thoughts so that I can start to share them in a more coherent manner. I hope that you will bear with me while I stumble through learning to write better and I hope that I can offer something to you in return for your patience.

In Ferro Veritas is Latin for "in the sword is truth". To me it means that there is a certain universal truth and understanding that has, at its base, a deep foundation in practicality. "If it's stupid, but it works, it's not stupid." I'm likely to say a lot of stupid things, but hopefully the thoughts underneath those words will shed some light on the subjects in a meaningful way. I hope the blog title will subtly encourage a deeper consideration for what I'm trying to say.

As a bit of history, I'm an Olympic-style fencing instructor, a former infantry soldier, an avid outdoorsman and a software developer. I grew up in Toronto, Ontario, Canada, did my university education at Dalhousie University in Halifax, NS, live in Carstairs, Alberta and work in Calgary, Alberta. I'm also a husband and father. Hopefully, there's some useful nugget of wisdom buried amidst all that responsibility and history.

Soldiers like to play in the physical and revel in a tough challenge. They know that actions have predictable responses and if we just think through our mechanism, we can achieve something impossible. Soldiers also have an innate fascination with history and philosphy (as they are often living it or living at its mercy). I hope to share the soldiers unique way of looking at an extreme challenge as both an opportunity to test oneself and as an opportunity to choose how something great happens. I am also hoping to fill some virtual pages with discussions of history and philosophy.

Fencers like to win and know that winning comes from hard work, dedication, exploitation and luck. I have learned a lot about the nature of achievement from fencing and I hope to share what I have learned so that others can benefit from this knowledge. Some of the most profound articles I am hoping to write stem from some experience or other I have had coaching kids in fencing. I am also hoping to write a few technical articles on the nature of fencing technique and its correct application on the piste.

Outdoorsman know that even the most uncomfortable situations can have an element of fun in them. From an environmental perspective, I hope to share some of my thoughts on the interconnection we have with others and with our planet. But I also live in a rural area so that relationship with the land is truly inconnected: we have certain things that we need to take from the land and some things that we need to oppose Mother Nature on. Be warned: my thoughts on the environment are not necessarily aligned with the modern environmental movement.

Software developers know that all problems have eventual solutions and that most problems needing solutions are not technical, but individual. This is my profession, so I hope to share with you the bits of programming and design experience I have acquired over the years. And yes, that means I hope to expose my inner nerd and talk about math and stats and philosphy and technology. Be prepared for formulae, logic and derivations as well as algorithms and technical findings.

As a husband and father I live with the most increadible woman and have the most profound responsibilty. My wife has taught me more about life then any other experience I have ever had and my new son seems to be continuing that tradition. But these are mostly private lessons that I think each man has to go through on his own and things that each of us have to experience ourselves before we can make sense of them. I am not planning to blog much about my family, but what I do hope to say, I hope to say profoundly.

That is me in a nutshell. I have done a lot, but I hope that the stupid mistakes I have made in the past can help you avoid them in the future.

So, let's see how this blogging goes.