Cross Tab/Pivot Queries

Been Busy:

I've been pretty busy of late with things not related to this general thread of gaining an understanding of MVC and all that it entails. One of the things I've been working on has been pulling user response data out of some survey software we're using as part of a clinical study at my real job. The project is to show the efficacy of the Text4Baby Project. The tool we used to collect the data is a modified version of Select Survey .Net (Which I highly recommend) The canned reports didn't output what we needed especially after the modifications we made.

Whut They Wanted:

It has a very relational structure in the SelectSurvey database and is a bit difficult to get data out of. One of the things the people doing the study (Real scientists) wanted was that the data be in a structure sort of like this.

RespondentId	Q1Answer	Q2Answer	Q3Answer	QNAnswer...
#1	Yes	No	Some Times	Twice
#2	Yes	Yes	Never	More than 10
#3	No	No	Never	Got Caught

It didn't look like that at all in the SelectSurvey database. It turned out to be fairly difficult to get it all worked out. For me anyway.

A bit of background:

I was riding on the shoulders of giants (Mark K. mostly) who had done a bunch of related preliminary work in figuring out the SelectSurvey database for us so it was much easier to do than if I had to do it all on my own. Additionally, many moons ago I'd come up with a way to put these column names into the SelectSurvey application using a field they already had there. It's called 'item_alias' but was intended for a different purpose. When I originally thought up the concept I had contacted the makers of SelectSurvey with my idea for a work around and suggest they add a new field to the application. A year or so later I contacted them to see how it was coming and they quoted back my hack as the suggested solution. kay-sara-sara.

The last time I wanted to do a pivot query in SQL Server it wasn't available. I'd gotten the concept from MS Access back when I was doing a lot of work with it. I couldn't understand why it wasn't available in these big database engines when little MS Access had it. Well, that was a long time ago and it could now be done in SQL Server.

Finding a Solution:

When Googling for how to do this in SQL Server. One of the first posts on the subject I found was this one: Pivot tables in SQL Server. A simple sample.: It's a pretty good example of what Pivot queries are used for and getting a handle on the general concept.

In the examples the poster was pretty much doing what I needed except I had a lot more column names than days of the week. Typing all of those into a query, and getting them right, seemed a bit daunting. What I wanted was for those column names to be dynamic and pulled out of the records themselves in that 'item_alias' field I mentioned previously.

I found how to do that in the response to this post: SQL Server PIVOT Column Data on stackoverflow.

You'll see in the response by astander that it's broken down into three parts (The first section with the CREATE TABLE is just populating a table with data so I'm not including that in this discussion)
The first part He creates a variable to store the list of columns in the right structure as indicted in the example in the first list. e.g. "[MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN]" by selecting the column names using a query. (Note the COALESCE thingy) From there, he creates a string with the Pivot query in it and substitutes the results from the prior query as the list of columns to get as the results of that Pivot query.
Then it's executed.

Implementation:

About all I did was copy the text of his post and substitute in the names for the tables and columns in the SelectSurvey database.

The Details:

If you want them, please ask. It's not that easy to write up so I'm not going to put it all together and post it when my only readers at this point are one close friend and my mother. What you'd get would be queries to create the pivot data using the SelectSurvey database and some setup data. to see it work. It would take a little bit of doing because the queries I'd post would have to be modified versions of the ones I'm using that don't include the table(s) we added for our extension of SelectSurvey.

Thanks:

I'd really like to thank the poster of the answer, astande, but I don't have enough points yet on stackoverflow to post a response yet.

*I hope to someday post the entirety of the mentioned modifications we made to SelectSurvey. Actually, we didn't make any changes to the application itself. What we did was built a wrapper around it that allowed us to chain surveys. The respondent would take the first survey which would collect their unique ID. The system would then check the database and figure out which survey(s) the respondent was supposed to take next. All of this may be moot as the version of SelectSuvey.NET we have on hand is a few years old and the basic idea wouldn't work with newer versions.

Understanding Dependency Injection

Based on all the articles and postings I've found via Googling the subject it's my guess that getting a handle on what Dependency Injection can be difficult for some people. For others, not so much. So, I'm going to post some of the resources I've found and add some comments to extend what they have posted to if I feel the need.

Basic Explanations

One of the more basic descriptions I've found is this one in a blog post by Kevin William Pang titled Dependency Injection For Dummies. What I liked about it was how it used simple images to assist in developing mental pictures for the concepts. I do think it came up short in one aspect though. In the "...Why Should I Care?" section he did cover how DI can be used for testing but he didn't provide an image or tie that back to the analogy. I believe it's one of the most useful aspects of DI and worthy of a bit more in-depth explanation.

So here:

How do you test that you can use a car, the dependency in this analogy, to go to the store? You inject a mocked instance of a vehicle. You can think of it as something like a driving simulator.

Totally stolen and then modified graphic
Note the purple, virtual vehicle.

This totally removes the need for a vehicle of any kind to verify that you know how to make a trip to the store via an appropriately configured vehicle.

To add to that, you can inject a mocked instance of the store, the shopping list and the money it will cost you to make the purchase. Then, at the end of this virtual trip to the store, you can test that you came back with all the items on your list.

To stay with the analogy, to do further testing, you can inject a store that doesn't carry 2% milk and then verify that it either returns with no milk or that skim milk is substituted depending on the business logic that is expected to be implemented. You can also substitute a virtual vehicle that shouldn't work, like a riding lawn mower, and check the error response. Alternatively, inject an obscure vehicle, such as a street licensed golf cart, and ensure that it can make the trip to a neighborhood store but fails if there is a highway where the golf cart isn't allowed on the route.

In the comments section, brian goes a little way into making modifications for the classes. As my thinking is along the lines of testing scenarios I'd like to carry that a couple of steps and make it so the Car class would implement alternate modes of transportation as mentioned above and then to add an age attribute to the Person Class and then verify that the target vehicle will ensure there is a place for a child seat for passengers under a given age.

Test Framework Status Update

Just a quick note to let all my reader know what's been happening.

I've made the copy of the Test Framework project, created a new project for testing the Test Framework, put it all in a solution and done all of the anonymizing I can. I can't post the code I have yet because there are still references to the Dependency Injection framework of our internal Enterprise Library.

That means the next step is to bring in the Unity container and rip out the old one.

Wish me luck!

Background of the Test Framework WebTest

Thought I'd give a little credit where due and explain a bit of the life-cycle that the Test Framework has gone through.

The Good Ol' Days

Like a great deal of decent code, it's stolen copied from someone on the web. This holds true with the Test Framework I've been using.

I think the original code for the Test Framework came from this article Selenium browser UnitTesting from TeamCity from the blog Software Creations – Orn Kristjansson. I'm not positive but it looks good. I'm thankful for the original posters work.

When we got the library in house I used it for a little while as it was originally presented before one of the developers, Jared, got his hands on it. He tweaked it to make it more configurable with an in-house Dependency Injection Object Factory thingy**. He also got it to automatically start the the Java engine that Selenium needed at the time instead of using the .BAT file I had been using.

That post mentioned above was from July of 2010. Back then, Selenium worked much differently than it does when I'm posting this. Things changed a lot with the release of 2.0, the WebDriver version. I'm happy with the changes that were made if you don't mind me saying so.

After Selenium 2.0 came out, I went into the Test Framework library and butchered modified it to work with the WebDriver version of Selenium. Part of which was taking out the starting of the Java engine (Which had broken by then) and that external dependency. I also messed up how things worked.

With that experience with the library my confidence in coding grew and I made some modifications to make pointing at different environments that needed to be tested easier to do. Now, only a minor change in one place can change that environment under test as well as the behavior of the test suite. That's to include using different test data against the site under test. Those changes worked but it was a convoluted implementation. Only when I went in to start this rework did I actually see what I had done and then correct it.

What's Changing

As I think I've mentioned before, we're planning on upgrading our in house version of an enterprise library. It includes much of an early release of the MS Enterprise Library plus some internally developed utilities along with an external Dependency Injection/Object Factory thingy**. I've made the command decision to switch over to a newer version of the MS Enterprise Library without the 'porting' to our namespace. That also includes replacing that in-house Dependency Injection Object Factory thingy with Unity.

** "Thingy" That's a very highly respected technical term. You should learn it and use it often.

Converting a project to Unity - Overview

Alternate Title: What a Tangled Web We Weave

I ran the/my first post in this series by a friend that's a college level English teacher. She said I switched between a first person voice and second. I didn't really know what she was talking about until I typed the first sentence of this entry. I switched between, "As the prior post..." and "As my prior post...". I'm pretty sure that's what she was talking about. I should go back and fix that for those that are pained by reading such. Problem is, I don't know which voice I what or should be using. I'll try to stick to first person with this post and see how I like it.

As my earlier post indicates, I've got a lot to take in. Where do I start? Model View Controller (MVC) is a central to premise of what I want to be using but using that how I want to use it is predicated on understanding and using Dependency Injection (DI) with Unity. Mixed in with that is the other parts of the MS Enterprise Library. Those include the Logging block and the

I couldn't find an appropriate image
quickly enough so I went with the default.
Enjoy the puppy.

I'm pretty much stuck with a chicken or an egg. I'll go with the egg this time. (BTW: If you accept evolution and the science it's based on then the egg came first. Think about it.)

My baby step goal here is to replace the Object Factory and DI container in an existing functional test utility library I've been using. We'll call that the Test Framework as that's what the namespace is now. I figure it's a small enough bite I won't totally choke on it.

Some Background

The library was originally created by a developer that is no longer here so that's a resource I don't have available. My primary resource so far for learning how to implement Unity has been the Microsoft Enterprise Library 5.0 - Hands On Lab. Specifically, the Unity section. I've seen others and I hope to provide links to them in the future. I've found a couple that do a good job of explaining DI and why it's useful.

Just what does this Test Framework do?

The Test Framework is a fairly strait forward library for instantiating the Selenium test object and then  tearing it down when the test run is done in the Microsoft Developer Studio test environment. It's well under 300 lines of code in three classes. It's written in C#, as will all the examples I'll be showing. This same library might work with NUnit without modification. I hope to get around to trying that in the not to distant future.

The Test Framework library is included in the test project and then the TestClass inherits from the abstract class WebTestBase. Config files in the test project instruct the DI container how to instantiate the WebTestBase objects which are then used by the test to interact with the web browser of choice.

What this utility does is allow you to externalize the configuration so it can be deployed in different test environments with minimal effort. After setup of the initial configuration I can change which environment I'm testing against with a single change in the main app.config file. I can also add new targets without modifying the code. I just add a new config file for that environment.

Steps

• What I'm going to have to do is create a test project to test and develop the modified Test Framework against.** For a test project, I'll need a website to test against. As of this writing I don't have that decided yet. Maybe it will be very public site like Google.
• Next thing to do is make a copy of the Test Framework project and anonymizing the existing code base. Besides keeping y'all from knowing where I work, it will allow me to distribute it to those that might be interested in incorporating it in their test efforts.
• Bring in the Unity library and other parts of the EntLib as necessary.
• Get a basic implementation using the main app.config.
  The library should work, at least minimally, at this point.
• Do some research on how to structure the configuration files.
  In Lab 3 of the Hands On Labs (link above) for the Unity Block it shows how to use the main app.config file for setting up the container. However, the way we're using our existing DI framework, the main app.config points to a NamingContext.config file that then seems to point at the folder(s) and files where other configuration information is located. I'll get into why I want to keep this shortly.
• Port over some real tests and make sure it's working.

Other things to eventually look at

• Flesh out the library so that it works for all the currently available Selenium browser drivers. Not just the ones I use. (Firefox and IE)
• Using DI to inject the Selenium browser drivers. There may not be a reason to have them hard coded in the first place.

Somewhere in there I'm likely going to have to explain, or at least point to an explanation of, how the MS Test Framework works.

**The Test Framework is not intended to be a standalone utility. It has to be used by a unit test or other automation framework tool. You can actually use Selenium directly in your test project without this utility. This framework is actually built upon another one that I found and hope to link to that wasn't configurable.

Well, that's enough for now. Catch you soon.

Index to my Learning MVC and things that go with it

It all started here with an overview of what I'm trying to learn: Learning MVC and things that go with it
The next first step was: Converting a Project to Unity - Overview

This stuff isn't easy

By "This stuff", I mean the blogging thing. I was working on a different post about how I'm going about getting a handle on Unity 2.0. Some of that includes putting things in context. Without the context what have you got? Something like this quote: ‘You really don’t want a remote control with a digestive system.’ from here. It could be funny unless it's chewing on your arm.
*&#$% It seems to be a circular problem again. How do I undertake this particular task and blog about it when I need to learn how to blog effectively first?

This happens nearly every time I try to write up something that's semi-complex or beyond. Especially when it's not an assignment. How do I set the scope? How do I go about imparting this knowledge without spending too much of my time while still keeping it useful? Part of that is knowing my audience.

Who exactly is my audience?  (Well, I don't have one yet so I can't base my aim on that.) What do think they should already know? What do I expect them to learn and what kind of knowledge/information do I hope to convey in my post? I've got to find the right balance of dragging people that don't really have the background yet, have a mixed level of knowledge like myself and then that of those that already have a grip on all the base technologies and are looking to expand what they already know. (Or are bored and just need a laugh)**

Looks like I'm going to have to make a couple SWAG's (Scientific Wild A$$ Guesses) and get on with it.

Well, here's one decision. I'm going to make the assumption that the people that are on this blog got here searched for it and have an idea of what they're looking for. That means they know how to use a search engine so I don't have to go into fine detail on everything. I'm still going to point them at resources I've found but I'm not going to search out and evaluate a whole slew of them just to get a point across. On top of that, there is the comment section where people can ask questions and post links to other resources they've found.

Another decision is that I'll try and use the "Tell them what you're going to do. Do it. Tell them what you did." structure. I kind of started that with the first MVC post. That's pretty high level so it looks like I'm going to need a more granular Tell Them post for my next entry.

So, the next post will be a short road map of my first little step(s) down this road along with some of the background information.

CRAP! I just realized that the next post should be one that has the links to the other posts. An index of the progress so readers can jump to a specific topic. I haven't even started and I'm already wrong. What a way to start.

If someone want's to point me at some easy to digest information on effective blogging then please do so. That and other writing and style suggestions are welcomed.

** If you're a member of this last group then please post any helpful information you can. I'm not sure of the best practices so please spread the wisdom.

Learning MVC and things that go with it

I've been trying to get a handle on newer Microsoft .NET technologies of late. There's a lot to go around. Poking around in all of the new stuff is fun but also frustrating. I've decided to focus on a few of the many technologies out there. Maybe someday I'll go into the decision making process, if I can remember what that was, but not today. In the meantime I'll just talk about what the decisions were.

Before I get into those decisions I'm going to spout a bit about how difficult this endeavor has been so far.

There are a few hindrances for me in picking this stuff up. The least of which is NOT that I'm supposed to be working on and producing on some other things at the same time I'm trying to move forward with this stuff. It's kind of like trying to fix the leaks in the boat you're traveling on while it's underway. If you stop bailing too long, you'll sink. If you don't fix the leaks you'll have to keep bailing forever. That and they'll likely just get worse as time passes. Spending time in dry-dock is out of the question for the time being so the only choice is to do it in my "spare time".

Add to that, I don't have the development or education background to jump right in with this. Though I've been writing code of one type or another for going on decades now, I've gotten by without learning some of the deep down concepts. I'm a QA engineer and didn't need to know that much until now. I've been standing on the shoulders of others and getting by just copying patterns of existing code without truly understand what was going on underneath. I can't get away with that technique and learn this stuff. Those shoulders simply aren't there/here anymore. Besides, I need to get to know this and now is as good a time as it will ever be.

So, a quick list of the technologies:

• .NET 4 and later.
• .NET MVC (Model View Controller) with Razor.
• Microsoft Enterprise Library (MS EntLib) latest version.
• MS EntLib Unity for Dependency Injection.
• Test Driven Development (TDD).
• LINQ for data retrieval.
• MS Entity Framework.
• Selenium 2.0.

Part of the reason for the desire to learn this is that we need to do some upgrading at work. Some of the technology we're using is circa 2003. It's solid but it's got us stuck in a difficult place. We need to escape some libraries we're using but there never seems to be enough time to research how to go about making that transition. While I was looking into some technologies (Am I saying that too much?) for a personal project I made the point of choosing ones that would dovetail with the work environment. I've pretty much got buy-in from a key group of people at work so I'm going with it.

So, that's a rundown of what I think I'm doing. It's a simple list of destinations. Maybe some maps and some of that other planning stuff to get to those destinations in the next post.