Beckshome.com: Thomas Beck's Blog

I’m sure if I had a nickel for each time a software project was impacted by introducing production volume data into the testing life cycle either too late, or worse even – not at all, I’d be a rich man and wouldn’t be writing this blog entry. When you think about it, it’s really no wonder that we find ourselves in this situation. Developers new to the craft have no experience to draw on dealing with millions of rows of data. Experienced developers and DBAs often pass on war stories of hand crafted scripts and the perils of migrating data from production to lower environments, further reinforcing the belief that emulating production volumes of data is work restricted to the gods of IT.

Taking this trend a step further, applications are often exposed for the briefest of periods to data resembling production volume data during the test cycle. Even then, the data reflects yesterday’s production volumes and not next year’s volumes. Furthermore, testing of certain other functions is restricted because these functions either deal with new data where there is, as of yet, no production data or they deal with other external systems that have test data that’s out-of-sync with the data of the system under test.

Does preparing production volume data really need to be this difficult?
No!

This entry deals with the use of Red Gate Software’s SQL Data Generator product to generate a large volume of data for a simple test database. Why would one choose to use a product such as SQL Data Generator to generate test data instead of using alternate methods such as copying production data or creating custom data generation scripts? There are several reasons:

1. Obfuscation of personally identifiable information (PII) from production data is a painful process. See point #2
2. Syncing data between systems is a very painful process. More so if the obfuscated data from step #1 is used across systems
3. By using production data, you’re only testing for current capacity, not for planned capacity 3, 6, or 12 months from now
4. If the application or elements of the application are new, you may not have any relevant data to test with at all
5. Writing custom data generation scripts is either (i) a one-off process that yields brittle scripts tied to a particular version of the schema; (ii) an exercise in re-inventing the wheel since commercial tools have already been built to do this.

Sample Data Model

Our data model is simple enough to be readily understandable while still presenting a couple of challenges that will illustrate some of the features of the SQL Data Generator tool. The data model serves as the backend for an online travelling site that collects and manages community-driven travel recommendations. Think about the book 1000 Places to See Before You Die as a Web2.0’ish site. Users can enter new tours / places, bundle similar or geographically close tours into tour packages, and provide user-specific tags for both tours and packages.

There are several characteristics of this data model that are somewhat challenging and provide an opportunity to illustrate some of SQL Data Generator’s more advanced options. These features are:

• Both the package and tour ids are unique identifiers (GUIDs). They are referenced by the ContributorId in the Tags table but there is no foreign key constraint. That is, a ContributionId is a GUID which may match up with either a tour id or a package id.
• The sequence numbers within the TourPackages table represent the visual display order of the tours within the package. Therefore the sequence numbers cannot be random and must cycle through each of the tours in a package without repeating within that package.
• The data generated for the model has to follow statistical distributions representative of the production environment, such as:
  • There should be 5 times as many tours as users with the number of tours per user following a normal statistical distribution between 1 and 10 tours.
  • The total number of packages should be 40% of the total number of tours. Tours distribution amongst the packages should be random
  • Total tags should be 60% of total tours. The vast majority of these tags (almost 10-to-1) should be attributed to tours. The remained are attributed to tour packages.

Basic SQL Data Generator Capabilities

Creating a project with SQL Data Generator is as easy as selecting the database you wish to generate data into.

Once the project is created, SQL generator will infer information about the data based upon the column types and other characteristics. You can then review sample data and tweak the configuration options to meet your needs.

Specifying valid values for the Tour table’s longitude column. Changes to the generator settings are immediately reflected in the sample data set, providing the opportunity to validate the impacts of the changes.

Intermediate SQL Data Generator Capabilities

Specifying the mechanism to determine how many rows to generate is made easy within SQL Data Generator. This enables the data to be generated in proportion to production ratios, as stipulated in our requirements.

These same capabilities allow us to address the requirements around TourPackage sequence numbers by letting SQL Data Generator handle the generation of combinations within the TourId / Package Id composite key space.

Lastly, SQL Data Generator can use alternate generator sources, such as the output of SQL statements or direct input from a CSV file. In our case, this allows us to specify a SQL statement to pull the appropriate Ids from the Tour and Package tables for the reference Id values in the Tag table even though no explicit foreign key relationship is present.

Advanced SQL Data Generator Capabilities

Custom generators can be created for use with the SQL Data Generator. This enables domain specific data to be generated and the generators to be re-used across multiple projects. Custom generators are written in .NET code by implementing one of RedGate’s generator interfaces. Although this is not particularly difficult, it is beyond the scope of this post.

Generating Data

Once the generation options are specified in accordance with the requirements, the only thing left to do is generate the data. The data population action plan gives you an idea of what data will be going where.

Running the generation script against a local SQL Server Express installation on a small (one processor, 2 GB RAM) VMWare machine, SQL Data Generator was able to generate 420,000 records across 5 tables in less than 1 minute, yielding a total database size of about 400 MB.

Other SQL Data Generator Capabilities

At this point in this blog entry, I’m hoping you’re at least starting to believe that data generation can be fast and easy. There are several other benefits to data generation with SQL Data Generator that weren’t covered here:

• Project seamlessly incorporates changes to the underlying schema
• The SQL Data Generator project file (extension “.sqlgen”) can be version controlled in conjunction with the scripts to create the database, providing the ability to create and fully populate current and historic versions of the database to align with application code changes.
• If the seed numbers are not changed, the data generated is exactly the same across generations. If you need new / different data, change the seed number.

Related Links

• SQL Data Generator Home Page – http://www.red-gate.com/products/sql_data_generator/index.htm
• SQL Data Generator Documentation – http://www.red-gate.com/supportcenter/Content.aspx?p=SQL%20Data%20Generator&c=SQL_Data_Generator/help/1.2/SDG_Getting_Started.htm&toc=SQL_Data_Generator/help/1.2/toc.htm
• SQL Data Generators Community Site – http://www.codeplex.com/SDGGenerators
• SQL Data Generator Project File and SQL Scripts for this Article – http://s3.beckshome.com/20090704-Generating-Data-Source-Files.zip

I was performing functional tests on my models that employed Attachment_Fu this morning and thought it would be worthwhile to share the code since it was a bit of a hassle pulling it together. Kudos to Mike Subelsky for his introduction to functional testing Attachment_Fu. It got me going in the right direction. What proved difficult once again was the multi-model controller. Once I got over that hump, I was on my way. As you can see from all the detail in the HTTP POST below, that was not an entirely easy task.

class ProductsControllerTest < Test::Unit::TestCase
...
def test_create_with_user
num_products = Product.count
imgdata = fixture_file_upload('/files/image.png', 'image/png')
audiodata = fixture_file_upload('/files/sound.mp3', 'audio/mpeg')
post :create, {:product => {
:name => "Widget",
:description => "A small tool-like item",
:weight => "3",
:price => "19.99",
:language_id => "1"
},
:image => {:uploaded_data => imgdata},
:audio => {:uploaded_data => audiodata} ,
:html => { :multipart => true }
},
{:user_id => users(:valid_active_user).id}
assert_response :redirect
assert_redirected_to :action => 'show'
assert_equal num_products + 1, Product.count
end
...
end

Continuing my Rails on Windows thread, I’m going to spend a bit of time on something that’s brought me both some substantial gains and some minor woes lately, running the Attachment_Fu plugin on Windows. I’ll start off with some general Attachment_Fu information and then get into some of the quirks, which are, as expected, mostly specific to the Windows environment.

First, for those not in the know, Attachment_Fu is a Rails plugin that allows you to store binary data (e.g. images, video, documents) and associate it with other models in your Rails application. Metadata (content type, size, height, width) about the attachment is stored in a separate model. Attachment_Fu’s sweet spot is handling images. It can handle automatic image conversion and thumbnailing using a number of popular image processors such as ImageScience, RMagick, or minmagick. Although not provided, you can imagine that Attachment_Fu might be extended to handle other types of binary processing utilities such as PDF converters or audio/video transcoding software. The other very cool thing about Attachment_Fu is that it provides support for pluggable persistence mechanisms. Out of the box, it allows for storage on the file system, as binary information in a database or on Amazon’s S3 storage service.

There is an abundance of information already written about Attachment_Fu so to avoid re-inventing the wheel, I’ll provide what I found to be the best sources of information to start.

• Mike Clark’s tutorial is the gold standard introduction to using Attachment_Fu. The code is simplistic but rock solid. It covers using both the file system and S3 for storage and will get you up and running on Attachment_Fu in no time.

• Some posts on the Attachment_Fu message board provide a solution to associating the attachment model with another model (i.e. making it an attachment to something). The posts provide both the controller and the view code for uploading the initial attachment and rendering it. Handling the attachment relationship in your MVC is going to be a fairly common requirement and most Attachment_Fu users will benefit from these posts.

For my part, I’m going to provide some controller source code for updating the attachment when you have a relationship with another model (an extension of the second item above) since this is one area that wasn’t covered well anywhere else and might save you some time in your travels. In the code below, my main model is the product and the image is the model where a photo and thumbnail are stored using Attachment_Fu.

class ProductController < ApplicationController

def update     @product = Product.find(params[:id])
# Load up product categories for the view
@all_categories = Category.find(:all, ::order=>"name_en")
if @product.update_attributes(params[:product])
if !params[:image][:uploaded_data].blank?
# My product only has one image / thumbnail, I'll destroy 'each'
# wait 3 # See quirk no.1 below
@product.images.each {|img| img.destroy}
@image = @product.images ||= Image.new
@image = @product.images.build(params[:image])
@image.save
end
flash[:notice] = 'Product was successfully updated.'
redirect_to :action => 'show', :id => @product
else
render :action => 'edit'
end
end

end

The links above, in combination with my snippet, should get you through creating an attachment and handling CRUD for an attachment and its parent model from a single view. Now comes the Windows quirkiness. Not knowing to expect these Attachment_Fu quirks and then having to root out the cause of the behavior took up a lot of time. It turns out that most of I found that most of the quirks are documented in some way, shape, or form. I’ve pulled together a list of the quirks as well as some best practice workarounds.

• When running Attachment_Fu on Windows, the most commonly accounted problem is the “Size Is Not Included In List” validation error. It appears that no amount of fixing in the Ruby code is going to help here since it appears to be a Windows file system issue. The workaround is really simple, just add a wait x statement before your attachment processing and things will be golden. The x (which denotes seconds) time will vary based upon the size of the attachments you are processing. Bigger attachments require more of a wait. Also, be sure to comment this code out in production since this is a Windows only issue.

7/19/2007 Update – Rick suggested using RUBY_PLATFORM to determine if the wait should be invoked. I tested this and it worked as suggested

• When you invoke the destroy method on your attachment using Attachment_Fu on Windows, your models reference to the attachment will be deleted but the physical attachments themselves will not be deleted if you have persisted them to the file system. If you look at the Attachment_Fu source code or your log files, you’ll see that Attachment_Fu assumes that you are using a UNIX-based system and executes UNIX commands like rm to remove these files. These commands will obviously not work in a Windows environment, leaving you with a bunch of zombie files. This should not be a problem if you use a database or S3 persistence mechanism since these mechanisms are independent of the operating system.

7/19/2007 Update – Rick corrected me. He is indeed calling the OS safe FileUtils.rm in the file system backend. It still isn’t working though – at least on my machine.

• My last Windows specific quirk is actually an Internet Explorer issue. If your attachments are images, you may have problems with uploading JPEG’s using the default Attachment_Fu plugin. From what I’ve been able to determine, if you upload a JPEG from IE with a file extension of .JPEG, IE will set the MIME type to image/pjpeg for a progressive JPEG. However, if the image extension is simply .jpg, IE will set the MIME type to image/jpg. This MIME type, however, is not included in the default list of content types accepted by Attachment_Fu. My suggestion is to add this type to the list in the source code until Rick can get around to modifying the source.

7/19/2007 Update – The MIME type was added to source. For reference, Rick suggested that this could have been done without changing the source simply by adding
Technoweenie::AttachmentFu.content_types << ‘image/jpg

The last quirk for my post should be meaningful to all of those using Capistrano, the Rails migration utility. Capistrano manages versions of the application for rollforward / rollback by creating symlinks to previous versions of an application and deploying the most recent version of your entire application tree from your version control system (e.g. Subversion). However, since it’s very unlikely that you are storing all of the attachments for your application under version control, the attachments will be unlinked and no longer available when you migrate a new version of your application to production. To get around this issue, the solution proposed here creates a separate physical directory for the attachments outside of your application’s directory and then updates a symlink from your application’s attachment directory to the separate physical directory every time you migrate.

I’ve been putting a good deal of time recently into converting GeoGlue from .NET to Rails. One of the things that I’m looking to get into the alpha release is the dynamic creation of podcasts. This is really nothing special since a podcast is little more than a special case of an RSS feed that points at external media files (i.e. audio or video).

I plan on covering the audio/video entry in an upcoming post about the nuances of the Attachment_Fu plugin on Windows. In this post, I’m going to just lay out the code for the podcast creation, since this is nothing more that a simple rxml file. I’ve sprinkled in comments liberally but most of the code should be fairly self explanatory to those familiar with Ruby and RSS feeds.

xml.instruct! ::xml, :version=>"1.0", :encoding=>"UTF-8"
xml.rss('version' => '2.0') do
xml.channel do
xml.title @podcast.name
# Self-referencing link
xml.link url_for(:only_path => false)
# Important --> RFC-822 compliant datetime
xml.pubDate(Time.now.strftime("%a, %d %b %Y %H:%M:%S %Z"))
xml.language "en-us"
xml.ttl "40"
# User who caused the feed to be generated
xml.generator User.find(:first, session[:user_id]).name
xml.description @podcast.description
# 'public_filename' is a method from the Attachment_Fu plugin
xml.image do
xml.url url_for(:controller => @podcast.images[0].public_filename, ::only_path => false)
xml.link url_for(:only_path => false)
xml.title @podcast.name
xml.width @podcast.images[0].width
xml.height @podcast.images[0].height
end
@podcast.entries.each do |entry|
xml.item do
xml.title(entry.title)
xml.link(url_for(:controller => entry.audios[0].public_filename, ::only_path => false))
# User who actually generated the media (i.e. audio)
xml.author(entry.user.name)
xml.category "Uncategorized"
xml.guid(url_for(:controller => entry.audios[0].public_filename, ::only_path => false))
xml.description(entry.description)
# Simplification, you should pull from updated_at/updated_on
xml.pubDate(Time.now.strftime("%a, %d %b %Y %H:%M:%S %Z"))
# The enclosure is very important!!
# If you use Attachment_Fu, everything you need is included in the model
xml.enclosure(:type=>entry.audios[0].content_type,
:length=>entry.audios[0].size.to_s,
:url=>url_for(:controller => entry.audios[0].public_filename, ::only_path => false)
)
end
end
end
end

A couple of lessons learned from my experience. Firstly, Apple provides some good resources on generating podcasts. This is especially important since the iTunes crowd is a large and important contingent of the feed consuming world. There are iTunes-specific tags (and a schema) available. These tags are not mandatory (I didn’t use them here) but they will help you produce a richer feed for consumption within iTunes. Secondly, since the RXML file is just another view, make sure to turn off any default layouts that you might have applied to your other views. I’ve included a snippet below to demonstrate how to do this. Check your version of Rails, mileage may vary with exempt_from_layout based upon your release.

class ApplicationController < ActionController::Base   

# Pick a unique cookie name to distinguish our session data from others
session :session_key => '_trunk_session_id'
layout 'default'
exempt_from_layout :rxml
...

end

My final caveat is not to apply forms-based authentication to your podcast (RXML view). Either make the view public or, if you wish to protect it, do so using HTTP Basic authentication instead. If you’re using both forms-based and HTTP Basic authentication, you’ll probably need to sync the two by using a single LDAP repository. That’s fodder for a completely different post.

The recent announcement that Google will support GeoRSS in addition to KML as a data format for geographic content in Google Maps is long overdue. This is one of those rare areas where Google trailed both Microsoft and Yahoo and did not seem at all willing to budge. Google’s announcement also seals the deal on GeoRSS as the way to syndicate geo-specific data. However, despite the obvious importance of GeoRSS, there is little written material on producing GeoRSS feeds.

I promised a brief tutorial on creating a GeoRSS feed with my post on Yahoo’s Tag Maps. More specifically, my post will focus on a boundary update GeoRSS feed. That is, you pass in the maximum and minimum latitudes and longitudes for your map in question and only data about the points that correspond to that particular latitude / longitude box is actually fetched. Obviously, if the user interacts with the map (i.e. panning or zooming), you can use the map’s API and some AJAX’y goodness to make calls to the GeoRSS feed to pick up a new set of points that correspond to the updated map’s boundaries.

The code below represents the most rudimentary and explicit way to construct a GeoRSS feed using ASP.NET and C#. For the purposes of illustration, no third party GeoRSS libraries are used. It’s all basic I/O, streams, and very manual XML construction. Also note a single monolithic call in Page_Load, lack of exception handling and parameterized queries may or may not be the way you want to do things. Try it out though; it does what it’s supposed to do really well. If you have any comments or corrections, just drop me a line.

I plan on posting a follow up in a couple of days with a live GeoRSS feed. I just need to find a nice sized set of simple data that I can load into a database and point my code at. Expect to see this soon.

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Xml;

public partial class BlogGeoRss : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
this.Response.Clear();
this.Response.ContentType = "text/xml";
this.Response.ContentEncoding = System.Text.Encoding.UTF8;
System.IO.MemoryStream stream = new System.IO.MemoryStream();
XmlTextWriter XMLWrite = new XmlTextWriter(stream, System.Text.Encoding.UTF8);

XMLWrite.WriteStartDocument();
XMLWrite.WriteWhitespace(Environment.NewLine);
XMLWrite.WriteStartElement("rss");
XMLWrite.WriteAttributeString("version", "2.0");
XMLWrite.WriteAttributeString("xmlns:georss", "http://www.georss.org/georss");
XMLWrite.WriteAttributeString("xmlns:gml", "http://www.opengis.net/gml");
XMLWrite.WriteWhitespace(Environment.NewLine);

XMLWrite.WriteStartElement("channel");
XMLWrite.WriteWhitespace(Environment.NewLine);
XMLWrite.WriteElementString("generator", "geoglue.com");
XMLWrite.WriteWhitespace(Environment.NewLine);
XMLWrite.WriteElementString("title", "GeoGlue GeoRSS Feed");
XMLWrite.WriteWhitespace(Environment.NewLine);
XMLWrite.WriteElementString("language", "en-us");
XMLWrite.WriteWhitespace(Environment.NewLine);

// Pick up the query strings for the latitude / longitude boundaries
float UpperBound = 0F, LowerBound = 0F, LeftBound = 0F, RightBound = 0F;
try { UpperBound = float.Parse(Request.QueryString["UpperBound"]); }
catch (Exception ex) { };
try { LowerBound = float.Parse(Request.QueryString["LowerBound"]); }
catch (Exception ex) { };
try { LeftBound = float.Parse(Request.QueryString["LeftBound"]); }
catch (Exception ex) { };
try { RightBound = float.Parse(Request.QueryString["RightBound"]); }
catch (Exception ex) { };

// Build the item nodes for each of the specific tours
SqlCommand cmd = new SqlCommand("SELECT Name, Description, Latitude, Longitude " +
"FROM TOUR WHERE (Latitude < @UpperBound) AND (Latitude > @LowerBound) " +
"AND (Longitude > @LeftBound) AND (Longitude < @RightBound)",
new SqlConnection(ConfigurationManager.ConnectionStrings["GeoGlueDev"].ConnectionString));
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new SqlParameter("@UpperBound", SqlDbType.Float)).Value = UpperBound;
cmd.Parameters.Add(new SqlParameter("@LowerBound", SqlDbType.Float)).Value = LowerBound;
cmd.Parameters.Add(new SqlParameter("@LeftBound", SqlDbType.Float)).Value = LeftBound;
cmd.Parameters.Add(new SqlParameter("@RightBound", SqlDbType.Float)).Value = RightBound;
cmd.Connection.Open();
SqlDataReader dr = cmd .ExecuteReader();

while (dr.Read())
{
XMLWrite.WriteStartElement("item");
XMLWrite.WriteWhitespace(Environment.NewLine);
XMLWrite.WriteElementString("title", (string)dr["Name"]);
XMLWrite.WriteWhitespace(Environment.NewLine);
XMLWrite.WriteElementString("description", (string)dr["Description"]);
XMLWrite.WriteWhitespace(Environment.NewLine);
XMLWrite.WriteElementString("georss:point", Convert.ToString(dr["Latitude"]) + " " + Convert.ToString(dr["Longitude"]));
XMLWrite.WriteWhitespace(Environment.NewLine);
XMLWrite.WriteEndElement();
XMLWrite.WriteWhitespace(Environment.NewLine);
}
cmd.Connection.Close();

XMLWrite.WriteEndElement();
XMLWrite.WriteWhitespace(Environment.NewLine);
XMLWrite.WriteEndElement();
XMLWrite.WriteWhitespace(Environment.NewLine);
XMLWrite.WriteEndDocument();
XMLWrite.Flush();

System.IO.StreamReader reader;
stream.Position = 0;
reader = new System.IO.StreamReader(stream);
Byte[] bytes = System.Text.Encoding.UTF8.GetBytes(reader.ReadToEnd());
this.Response.BinaryWrite(bytes);
this.Response.End();
}
}

One of my original intents of registering the beckshome.com domain name was to publish photos of my new baby son or daughter. That was two years and two daughters ago and, until this weekend, photos were nowhere to be found on my blog. I host my blog on the Windows platform and had no desire/time to do any of the following: (a) buy a separate package for image management; (b) cobble together an ASP.NET solution to manage my photos; (c) switch blogging software to a tool like Community Server that has integrated photo management. Furthermore, I already manage my photos on Flickr and I’m more than happy with the service, user experience, and the cost-benefit. What I really needed was a way to integrate my existing Flickr photos into my current .NET-based blog (DasBlog). The pursuit of this goal is what this blog entry is all about.

Being a regular blog reader, what I’ve seen a lot of out there are the Flickr badges. These badges, available in either HTML or Flash versions (like the one in the sidebar of this blog), are pretty slick and can be found pretty much everywhere on the Web. The problem with these badges is that they only offer the opportunity for shallow integration. Click on the badge and bye-bye blog, you’re zipped off to Flickr’s site to look at the photos. Since I aspired to achieve a bit deeper integration, I needed a different approach.

Next thing that I looked into was programmatic access to the Flickr API or a pre-existing solution that I could use wholesale or reproduce with little effort on my part. The Great Flickr Tools Collection has a vast assortment of very interesting tools – none of which quite seemed to meet my needs. I checked out the Flickr.NET API Library, which was written about in a Coding4Fun post and can be found for download here. It’s very well done and, although it probably won’t be the last time I mention this API in my blog, it will be the last time I mention it in this posting.

What I eventually stumbled on was a simple and elegant solution that got me exactly what I wanted by embedding the Flickr slideshow viewer into a custom page on my existing blog. Paul Stamatiou has an excellent post on his blog on how to do just that. By using an iframe and setting some API attributes you can get this up and running very quickly; qualifying this as a super easy hack that just works. Note that only photos marked as public will be displayed.

With the Flickr slideshow viewer up and running, I only needed to add the ability to select between multiple photo groups and I was done. With the actual Flickr viewer taking care of all of the real AJAX work, all that was needed was a bit of light JavaScript to tie this all together. Below you will find the code that does all of the lifting.

<script type="text/javascript">// <![CDATA[
      function changeSlideshow(url, title)
      {
            document.getElementById("SlideShow").src=url;
            document.getElementById("Title").innerHTML=title;
            return false;
      }
// ]]></script>

Clicking on any of the photo group links / thumbnails makes a call to the above function passing the URL for the slideshow in the manner stipulated in Paul’s article for populating the slideshow viewer. The title is also passed so that the title of the page can be updated. You can see this at work on my new photopage. By viewing the page source, you can see the exact mechanism I used to make this work. If you’re have any questions, feel free to drop me a line.