July 2011

You are currently browsing the monthly archive for July 2011.

Here’s a little SQL gymnastics used to parse and query against a predictably-formatted long text field.

The challenge: We have a single text field that contains a list person_id-to-area mappings, and we need the query the database for a list of those people with their names.

For example, this it the query used to fetch the mapping field:

select value  -- a "text" field (e.g. not a varchar) 
from setting		
where setting_name = 'InstructorAreaMap'

And that query returns “42|Accounting\r\n51|Marketing\r\n69|Strategy“. But what we want is a set of records that contains not only the Person ID and Area, but also the name of the person from the Person table, where Person ID is the key.

We can do this in a single query like so:

  1. Convert the text field into a varchar that we can use for replace operations.
  2. Replace the delimiters with xml tags.
  3. Convert the result into an XML data type.
  4. Select against that XML.
  5. Join in the Person table and select the result.

Step 2 builds an XML string that looks like “<r><i>42</i><a>Marketing</a></r><r><i>51</i><a>Accounting</a></r><r><i>69</i><a>Strategy</a></r>“, and Step 4 essentially converts that into a joinable table that looks like this:

42	Marketing
51	Accounting
70	Strategy

After this it’s easy to get the result we need:

Accounting     Fred Flintstone  51
Marketing      George Jetson    42
Communication  Barney Fife      70

Here’s the SQL query:

select assignments.area, p.display_name name, p.person_id
from (

	-- STEP 4: 
	-- Create a table of person_id (i), area (a) pairs from an xml string 
	-- in the form "<r><i>42</i><a>Area</a></r><r><i>..." called "assignments" 
	select c.value('(i)[1]', 'int') as person_id,
		c.value('(a)[1]', 'varchar(50)') as area
	from (

		-- STEP 3:
		-- select an XML type called "data" in the above form using 
		-- information in the "value" field of the "InstructorAreaMap" setting
		select cast (

			-- STEP 2: 
			replace( '<r><i>' + 
				replace( 

					-- STEP 1: 
					-- Convert the text field into something we can use 
					SUBSTRING( convert( varchar(max), value ), 1, DATALENGTH(value) ), -- search
					char(13)+char(10), -- find
					'</a></r><r><i>' ) -- replace with
				+ '</a></r>', -- outer serach
				'|', -- outer find
				'</i><a>' ) -- outer replace with
			as xml ) data

		from setting
		where setting_name = 'InstructorAreaMap'
	) as t

	cross apply data.nodes('r') as t2(c)
) as assignments

-- STEP 5:
-- Join against the Person table to get the name. 
inner join person p
	on p.person_id = assignments.person_id
order by area, name
Share

The goal was to crawl on the cheap, and what’s cheaper than popsicle sticks craft sticks and fishing line? Next we’ll wire up a 555 circuit so it can roam untethered, but until then, here’s how to make one of your own. But first the video! :) (UPDATE: Details about the untethered “version 2″ with the 555 timer circuits can be found at StickBot V2.0 - Untethered!.)

Supplies

  • 4 Popsicle craft sticks (like Popsicle brand ice pop sticks)
  • 3 small eye screws
  • A few feet of mono-filament (fishing line). We used 10 lb test.
  • 3 pipe cleaners (one is just decorative)
  • 1 small cable tie
  • A bit of masking tape
  • 1 mini micro hobby servo

How to Make It

  1. Body: Stack four popsicle craft sticks, and drill three pilot holes through all of them — one in the center and one at each end. Three of the sticks will be the legs, and one will be the body.
  2. Joints: Arrange the legs on top of the body, and fasten them together with three small eye screws. On most eye screws, the threads will not go all the way to the top, so the legs should be free to move back and forth.
  3. Muscle: With a small cable tie (and possibly a dab of glue), fasten a 3.7g mini micro hobby servo to the body stick, centered between the middle and hind legs, with the motor shaft at the rear. Attach a servo arm so that it points out like the legs when the motor is in its center position. (You can get these motors on ebay for a couple bucks.)
  4. Tendons:Cut six lengths of monofilament, each about 9 inches long. For each line, tie a knot into one end, and thread it from the bottom through the hole at the end of the leg. The knot should be big enough that it won’t slip through the hole. Thread the mono-filament from the legs as follows (in this order):
    • Front left: Left to right, though the center eye, and through the right end of the servo horn.
    • Front right: Right to left, though the center eye, and through the left end of the servo horn.
    • Back left: Left to right, though the center eye, and through the left end of the servo horn.
    • Back right: Right to left, though the center eye, and through the right end of the servo horn.
    • Middle left: Left to right, though the front eye, and through the right end of the servo horn.
    • Middle right: Right to left, though the front eye, and through the left end of the servo horn.
  5. Adjustment: Carefully pull each line snug so that the legs are all perpendicular to the body, and tape them down to the servo horn. Trim off the ends, leaving an inch or so for later adjustment or tightening.
  6. Legs: Cut some pipe cleaners into six 3-inch lengths and wrap each one around the end of a leg. Bend the legs so that they all touch the surface, and are angled toward the back of the crawler. It can take a little time to get it just right, and you’ll probably want to adjust it when you get the motor hooked up.
  7. Antennae: Add some antennae if you wish by wrapping a couple 5-inch lengths of pipe cleaner to the front legs.
  8. Brain: Power the servo with an Arduino, Basic Stamp, or other micro controller, and program it to turn left and right continuously. There’s a sample sketch below.

A Simple Arduino Sketch

#include <Servo.h>

#define SERVO_PIN       9    // what pin is the servo on?
#define LEFT_EXTENT     0    // how far left should the servo go?
#define RIGHT_EXTENT    180  // how far right?
#define PAUSE           500  // how many milliseconds between steps?

Servo myservo;

void setup() {
  myservo.attach(SERVO_PIN);
}

void loop() {
  myservo.write( LEFT_EXTENT );
  delay(PAUSE);
  myservo.write( RIGHT_EXTENT );
  delay(PAUSE);
}

Video Music Credits

The music in the video is by Morusque (CC BY-NC): http://ccmixter.org/files/Nurykabe/32448

Updated Name

I realized that Popsicle was actually a registered brand name and not just a common word, so in order to avoid any confusion or trouble, I changed this little guy’s name to StickBot. This project does not (and never did) have anything to do with Popsicle brand ice pops. In fact, I’m not even sure the craft sticks I uses were actually from Popsicle brand ice pops. So my sincere apologies to the Popsicle people; I hope you continue to let me eat your ice pops because life would simply not be the same without them!

Share

In celebration of Independence Day here in the United States of America, here’s a great 10-minute narrated animation that reviews how the nation got to where it is today. Read the instructions, click the X, then click Play.

http://www.animatedatlas.com/movie.html

Videos are available for sale at http://www.animatedatlas.com/

Share