Monday, November 17, 2008

Using Grails to Store Generic Data Sets

TASK: Build a generic data structure to represent grid data, then use the Grails framework to implement it.

At a high level, what is needed is a set of tables that can hold sets of varying numbered rows and columns. This means we have collections of grids representing the individual data sets. Each grid has a collection of rows (we’ll call them results for this discussion) and data columns. Each result has a collection of data points. And finally, each datapoint is associated with a data column.

I’ve been using Grails now for about a month and I’ve found the documentation to be lacking in comparison with other frameworks that I’ve used. So hopefully this example of representing result sets will be usefull to others who, like myself, learn best from checking out example code and applying it to the task at hand. For the purposes of this example, I am using Grails 1.0.3, MySQL 5.0, NetBeans 6.5, and Navicat 8 Lite for MySQL (very helpful for setting up MySQL for those who aren't keen on command-line stuff). I won't get into the whole setup of the Grails plugin for NetBeans and I'll assume that you already have your MySQL database created. The tables will be automagically generated by Grails so all you need is an empty database.

First, the domain classes:

class DataRow {
int Id
int DataSetId
static hasMany = [results:DataPoint]

class DataPoint {
int Id
DataPointColumn Column
String Val


class DataPointColumn {
int Id
String ColumnName

And now the code to save your data. For this example, you will have some java structure that stores results rows and columns. I won't muddy the waters here with my implementation of that since this is supposed to be about Grails. Suffice it to say that you will need to have some mechanism of retrieving a collection of column names, and each data item should be associated with a column.
IResults res = <execute some query>
String[] column_names = res.getColumnNames()

for (Result r : res.getResults())
def savedResults = new DataRow()
savedResults.DataSetId = res.getId()

for (int i=0; i< column_names.length; i++)
def col = DataPointColumn.find("from DataPointColumn as dp where dp.columnName=?",[column_names[i]])
if (col==null){
col = new DataPointColumn()
col.ColumnName = column_names[i]
String val = r.getDatapointValue(column_names[i]);

def dp = new DataPoint()
dp.Val = val
dp.Column = col


} // Save the row

This is actually a slight modification to the actual code that I implemented so that my business-specific names and code are left out. The most interesting piece of the code (IMHO) is the addTo* function. There's actually a nice explanation of how that works on the Grails site (Grails: addTo*). If you absolutely must use java (I'm trying to hide my .NET bias here, really) Grails is a good MVC framework to use. Any changes to the domain model are automatically implemented in the database. One thing you will find usefull in tracking down errors is this code snippet:
(b is some domain object)

if( ! ) {
b.errors.each {
println it

As of right now, Grails does not have any built-in debugging support, which means that you are limmited to spitting out error descriptions. Hopefully someday it will be possible to step through a Grails app, but for the same price as this short example, you get a pretty slick development environment and framework.

Wednesday, November 12, 2008

PADR, PADL and PADC for SQL Server

Thanks to Igor Nikiforov for these very helpful UDFs. One of the biggest things I have on my wish list for SQL Server is more support for string functions. Here's the link...


Thursday, November 6, 2008

Can Anybody Learn To Sing?

In a prior life, before I discovered how cool it is to play with computers, I was a music teacher - arguably a pretty darn good one. Specifically, I was a voice teacher with a masters degree in Vocal Pedagogy from New England Conservatory of Music and an undergrad in Music Education. Fresh out of school I was convinced that I could teach *anybody* to sing. I had the theory, the technique and the practical experience. That, combined with a lack of money, fueled my desire to take on any and all who would fancy themselves a singer.

Yeah, about that...

To this day, I still cannot explain why there are some people who, knowing that they can't carry a tune in a handbasket, cling to the hope that they can learn to sing as easily as learning the fingerings on a piano. To borrow a phrase, "To those that have will be given more, and those that don't have - try gardening". Ok, I'm paraphrasing.

Here are my supporting arguments:

The voice is deeply connected to a person's identity.
For a person to even stand a chance at changing the way their voice sounds, they have to be able to acknowledge that there is something that needs to be changed. Right off the bat, that eliminates most who would endeavor through voice lessons. The simple reason is the diva/divo complex that brought them there in the first place.

There is no way to directly affect the mechanism by which we sing
Let me say that again: The musculature involved in making vocal sounds, for the most part, is beyond the level of conscious control. The task of the voice teacher is to provide an environment to which the vocal apparatus will reflexively respond. Because we are talking about muscle memorization, the process can take a very long time. It has been my experience that most would-be singers don't have the patience to stick it out. This is assuming that there are no external forces that would get in their way such as smoking, drinking, pregnancy, old age. And yes, I had at least a couple of students from each of these groups. Lovely people. No chance of singing.

The human lifespan is at best 120 years.
The length of time it would take a person with no ear to learn to sing is far longer than most would stick to it. Nuff said.

Damn it Jim, I'm a voice teacher, not a doctor...
Or something like that. There are some people who have physical characteristics which make it impossible for them to produce the kind of vocal sounds that they would hope to achieve. For example, I had one gentleman who actually had a lot of musical ability being a fairly accomplished guitarist. The problem was that he was also heavily into weight lifting and, being a rather short and stocky individual, had the sort of build that made it impossible to put his arms by his side. That is to say, his arms, when at rest, were always in a position that made him look like he was getting ready for an old-style western quick-draw shoot out. Because of that, he could never seem to relax enough to allow the vocal apparatus to operate freely.

That was just one case. Others either smoked so much that they could neither breathe properly nor overcome the swelling and irritation of the vocal folds to make a focused sound. Then there was one poor woman who was pregnant. Now let me be clear. Being a man, I have no direct insight as to what she was going through. But I can only imagine the discomfort she felt when I asked her to take a deep breath, knowing full well that the tiny human in her abdomen had no intention of allowing her mom's diaphragm to descend more than a couple of inches.

I welcome any and all arguments. Even though I've moved on professionally to other things, I still find the science of vocal pedagogy fascinating and enjoy discussing it.

Avoid Inserting Duplicate Records

Here's a quick solution to the problem where you don't want to insert duplicate records into a SQL Server table. Of course, the easier way to do this is to set up a constraint, but there are times when you can't do that. For example, if you have a table where you are using a deleted flag as opposed to doing hard deletes. If you have foreign keys to deal with, I would suggest creating a table variable (again, assuming you are using SQL Server) and inserting the denormalized records there, and then doing the insert as listed below. You could probably do it in one statement, but just because you *can* do something doesn't necessarily mean you should. The two step process would be easier to read and more clearly indicates the intent of the code. Comments are great, but code should be self-documenting.

SELECT field_1, field_2, field_3
SELECT DISTINCT B.comp_1, B.comp_2, B.comp_3
WHERE B.comp_1 = LU.comp_1 And
B.comp_2 = LU.comp_2 And
B.comp_3 = LU.comp_3

Hope this helps.