Scott Hanselman

Hackers can kill Diabetics with Insulin Pumps from a half mile away - Um, no. Facts vs. Journalistic Fear mongering

August 5, '11 Comments [54] Posted in Diabetes
Sponsored By

UPDATE: Jay Radcliffe, the researcher discussed in this post, has emailed me, a little upset. In the interest of transparency I've included our email thread at the end of this post so that Jay's perspective on any inaccuracies may be seen. I encourage you to draw your own conclusions.

There's a story making the rounds on Twitter right now. Engadget "reports" researcher sees security issue with wireless insulin pumps, hackers could cause lethal doses.

Wait till you see what researcher and diabetic Jay Radcliffe cooked up for the Black Hat Technical Security Conference. Radcliffe figures an attacker could hack an insulin pump connected to a wireless glucose monitor and deliver lethal doses of the sugar-regulating hormone.

First, a little on my background. I've been Type 1 diabetic for 17 years. I've worn an insulin pump 24 hours a day, 7 days a week for over 11 years and a continuous glucose meter non-stop for over 5 years. I also wrote one of the first portable glucoses management systems for the original PalmPilot over 10 years ago and successfully sold it to a health management company. (Archive.org link) I also interfaced it (albeit with wires) to a number of portable glucose meters, also a first.

Engadget's is a mostly reasonable headline and accurate explanation as they say he "figures an attacker could..." However, Computerworld really goes all out with the scare tactics with Black Hat: Lethal Hack and wireless attack on insulin pumps to kill people.

Like something straight out of science fiction, an attacker with a powerful antenna could be up to a half mile away from a victim yet launch a wireless hack to remotely control an insulin pump and potentially kill the victim.

The only thing that saves this initial paragraph is "potentially." The link that is getting the most Tweets is VentureBeat's "Excuse me while I turn off your insulin pump," a blog post that is rife with inaccuracies (not to mention a lot of misspellings). Here's just a few.

  • "Insulin pumps use wireless sensors that detect blood sugar levels and then communicate the data to a screen on the insulin pump."
    • Way too broad. Pumps don't. Some CGMs (continuous glucose meters) communicate with special integrated pumps. The most popular integrated system is a Medtronic Paradigm. Most other CGM system have a separate "screen" device that's separate from the pump.
  • "The sensor has to run on a 1.5-volt watch battery for two years."
    • Nope. The Medtronic receiving sensor needs to be charged ever 3 to 6 days. The pump battery is usually a AAA that lasts a few weeks.
      UPDATE: The Dexcom receiver is recharged every 3 days but the body transmitter is warrented for a year with a small watch battery.

One useful paragraph in the VentureBeat post points out again that Jerome wasn't able to decode the message. Here, emphasis mine.

Then Radcliffe went through the process of deciphering what the wireless transmissions meant. These transmissions are not encrypted, since the devices have to be really cheap. The tranmissions [sic] are only 76 bits and they travel at more than 8,000 bits per second. To review the signal, Radcliffe captured the signal with a $10 radio frequency circuit board and then used an oscilloscope to analzye [sic] the bits.

He captured two 9-millisecond transmissions that were five minutes apart. But they came out looking like gibberish. He caputred [sic] more transmissions. About 80 percent of the transmissions had some of the same bits. He reached out to Texas Instruments for help but didn’t have much luck. He told the TI people what he was doing and they decided not to help him.

That was as far as he got on deciphering the wireless signal from the sensor, since there was no documentation that really helped him there. He couldn’t understand what the signal said, but he didn’t need to do that. So he tried to jam the signals to see if he could stop the transmitter. With a quarter of a mile, he figured out he could indeed mess up the transmitter via a denial of service attack, or flooding it with false data.

Now, to the security issue. One has to read these articles and blog posts very carefully. It's easy Link Bait to say "A hacker can kill diabetics wirelessly without them knowing it!" (I assume we'd figure it out at some point, though.) While Jerome Radcliffe, the gentleman who did the proof of concept, is no doubt very clever, the folks who are blogging this fear mongering should do their homework and read the details. Jerome is presenting some of his findings at the BlackHat conference. Here's his abstract with emphasis mine. Note also that SCADA means "supervisory control and data acquisition." He's saying that we "cyborgdiabetics" (my term) are human control and data acquisition systems as data-in/control-out controls our health, well-being and ultimately our lives.

As a diabetic, I have two devices attached to me at all times; an insulin pump and a continuous glucose monitor. This combination of devices turns me into a Human SCADA system; in fact, much of the hardware used in these devices are also used in Industrial SCADA equipment. I was inspired to attempt to hack these medical devices after a presentation on hardware hacking at DEF CON in 2009. Both of the systems have proprietary wireless communication methods.

Could their communication methods be reverse engineered? Could a device be created to perform injection attacks? Manipulation of a diabetic's insulin, directly or indirectly, could result in significant health risks and even death. My weapons in the battle: Arduino, Ham Radios, Bus Pirate, Oscilloscope, Soldering Iron, and a hacker's intuition.

After investing months of spare time and an immense amount of caffeine, I have not accomplished my mission. The journey, however, has been an immeasurable learning experience - from propriety protocols to hardware interfacing-and I will focus on the ups and downs of this project, including the technical issues, the lessons learned, and information discovered, in this presentation "Breaking the Human SCADA System."

Just to be clear, Jerome has not yet successfully wirelessly hacked an insulin pump.

UPDATE: See below email thread. Jerome says he can change settings and pause the pump. This may be via the USB wireless interface one uses to backup settings and send their blood sugar to their doctor. That's an educated guess on my part.

He's made initial steps to sniff wireless traffic from the pump. I realize, as I hope you do, that his abstract isn't complete. Hopefully a more complete presentation is forthcoming. I suspect he's exploiting the remote control feature of a pump. This is a key fob that looks like a car alarm beeper that some pump users use to discretely give themselves insulin doses. However, I feel the need to point out as a pump wearer myself that:

  • Not every Insulin Pump has a remote control feature.
  • Not every remote-controllable insulin pump has that feature turned on. Mine does not, for example.

In this AP article reposted at NPR called Insulin Pumps, Monitors Vulnerable To Hacking they give us more of the puzzle which confirms that Jerome was - in at least one hack attempt - using the optional remote control feature of the pump. A feature that few turn on. Their tech is a little off as well with talk of a 'USB device,' probably an Arduino with an RF shield.

Radcliffe wears an insulin pump that can be used with a special remote control to administer insulin. He found that the pump can be reprogrammed to respond to a stranger's remote. All he needed was a USB device that can be easily obtained from eBay or medical supply companies. Radcliffe also applied his skill for eavesdropping on computer traffic. By looking at the data being transmitted from the computer with the USB device to the insulin pump, he could instruct the USB device to tell the pump what to do.

Finally, another piece of the puzzle is found at SCMagazine's scary "Black Hat: Insulin pumps can be hacked" article where they open with:

"A Type 1 diabetic said Thursday that hackers can remotely change his insulin pump to levels that could kill him."

ZOMG! Someone can remotely control my insulin pump? They continue...

"Radcliffe, now 33, explained that all he requires to perpetrate the hack is the target pump's serial number."

Oh, you mean the serial number that I use to pair with the transmitter to use the highly touted remote control function? This is like saying "I can open your garage door with a 3rd party garage door opener. Just give me the numbers off the side of your unit..."

What Jerome has done, however, is posed a valid question and opened a door that all techie diabetics knew was open. It is however, an obvious question for any connected device. Anyone who has ever seen OnStar start a car remotely knows that there's a possibility that a bad guy could do the same thing.

For example, literally last month I personally exchanged emails with a friendly hacker who successfully hacked the web services for the Filtrete Touchscreen WiFi-enabled Thermostat. Harmless? Perhaps, but his hack could successfully remotely control a furnace or AC in the house of anyone with this device. Any control device that's connected to the "web" or even "the air," in the case of insulin pumps, is potentially open for attack.

I appreciate the message that Jerome is trying to get out there. Wireless medical devices need to be designed with security in mind. I don't appreciate blogs and "news" organizations inaccurately scaring folks into thinking this is a credible threat.

We don't know what brand pump was experimented on, and fortunately the gentleman isn't giving away the technical details. If you are a diabetic on a pump who is concerned about this kind of thing, my suggestion is to turn off your pump's remote control feature (which is likely off anyway) and turn off your sensor radio when you are not wearing your CGM. Most of all, don't panic. Call the manufacturer and express your concern. In my experience, pump manufacturers do not mess around with this stuff. I'm not overly concerned.

All this said, I'd love to have him on my podcast. If you're reading this and you're Jerome Radcliffe, give me a holler and let's talk tech.

Of course, all this talk would be moot if we cured diabetes. In encourage you to give a Tax Deductable Donation to the American Diabetes association: http://hanselman.com/fightdiabetes/donate

Also, feel free to show people my "I am Diabetic. Here's how it works" educational video on YouTube with details on how I setup a pump and continuous glucose monitoring system every 3 days. http://hnsl.mn/iamdiabetic takes you right to the YouTube video.

UPDATE: In the interest in full disclosure, here is my email thread with Jay. As I've said, I'm happy to update the article, as am I doing here, with all perspectives. This was as much a blog post about the media and that meta-point as it was about the tech. Given that I had to piece this post together from several other posts and articles just to get an idea of what the big picture is, kind of makes my point about the problems of hyperbole in the media. Again, my concern is more about sensationalism than it is about the tech. I have no doubt a pump CAN be hacked. Any connected device can be hacked.

Here's our thread from earliest to latest:


From: Jerome Radcliffe

I *can* hack an insulin pump. I can suspend it, change all the settings remotely. I did that on stage. I'm quite disappointed that you did not verify any of the information in your article. People do die from hypoglycemia. Is it an extreme example? Yes. It needs to be. These devices need to be researched for security flaws. To talk about why someone might hack a pump misses the point.


From: Scott Hanselman

I'm sorry, I only found the articles I linked to, plus the abstract that said you hadn't. I tried to verify everything to the best of my ability to Google. Would you send me some newer links and I'llr update my post? My post was meant as an analysis of the news coverage more than the attack. Send me new info?

Thanks!


From: Jerome Radcliffe

I understand your position. but as a blogger/journalist there is a certain level of responsibility to publishing facts. You come off as hypocritical blaming the media for being inaccurate on diabetics being killed by pumps, and write a piece riddled with inaccuracies on my research.
1. There is a CGM that runs on a 1.5v battery for two years. You state that my research is wrong. It is not.
2. Check CBS in las Vegas's web site. They have a video of the demo. Several media outlets reported that demo.
My name is fairly unique and my email address is easy to acquire. I would have rather you contact me for clarification rather then publish a critique of my research that is far from accurate.


From: Scott Hanselman

A random blogger and a trained journalist are certainly different things, I'm sure we can agree on. I do certainly want to improve the post and add the facts and am more than happy to do so.
I'm not sure where I said "your research is wrong" in my post, but I will re-check it. Again, most of my post is quotes from actual journalists who presumably interviewed you and I quoted them. I also quoted your black hat abstract.
I searched twitter for Jay and Jerome Radcliffe but didn't find you and wasn't able to find your blog, I suppose because of the flood of new links and stories.
This CGM runs for 2 years without recharging? Perhaps I'm confused about semantics. I've had a number of CGMs, some 1.5V and all the ones with embedded batteries needed recharging. I'll check around. Again, however, my assertion wasn't against you at all, rather the journalists whose stories were inaccurate.
I feel like we are getting off on the wrong foot here. I thought I wrote a post about how other journalists and bloggers were sensationalistic and inaccurate in their coverage. My post isn't meant as, nor should it read as, a personal attack on your hard work. As I said earlier, I'm more than happy to make updates and edits and fall on my sword with any inaccuracies. I'm even happy to post our email exchange.
Be well!


From: Jerome Radcliffe

Anytime you publish, blog or newspaper, you should be responsible for the content. There is no difference between a trained journalist and a blogger. You can't duck your own criticism of responsible reporting because you feel like your [sic] just a random blogger. The fact you are so critical of my work, you have been getting a lot of press. Your article was in the Slashdot headline, which is one of the most popular sites on the Internet. The fact is your article was highly critical of my work, and highly inaccurate. Even after I specifically told you about the inaccuracies in your writing you have not corrected them.
It's really hard for me not to be offended in this case. For the last three days I have had to answer people's questions, many have cited your article based from the Slashdot coverage.


Related Links

About Scott

Scott Hanselman is a former professor, former Chief Architect in finance, now speaker, consultant, father, diabetic, and Microsoft employee. He is a failed stand-up comic, a cornrower, and a book author.

facebook twitter subscribe
About   Newsletter
Sponsored By
Hosting By
Dedicated Windows Server Hosting by SherWeb

Review: Microsoft Touch Mouse for Windows 7

August 3, '11 Comments [45] Posted in Reviews
Sponsored By

Microosft Touch Mouse for Windows 7First, a disclaimer. I work for Microsoft on the Web Platform team. That said, I don't know anyone in hardware. My opinions are my own. I don't have any vested interest in this mouse and I paid for it myself. In fact, I pre-ordered it in February.

I'm a hardware fanboy. I've tried dozens of keyboards and mice, and many webcams. Getting a new mouse or a new keyboard is a great inexpensive way to fundamentally change your computing experience. It's like a new pair of pants. Sometimes it just makes you happy to refresh the stuff you use every day.

I REALLY want to like the Microsoft Touch Mouse. The idea is a great one. Take a mouse that works like you'd think a mouse should, then add a number of multi-touch gestures so your mouse is everything a mouse is PLUS basic gestures you use with a touchpad or phone.

Packaging and Hardware

The packaging and OOBE (Out of Box Experience) is cherry. It's really nice to see Microsoft getting packaging right in a way that isn't a copy of Apple's packaging. The package opens up with a multi-fold lid like a treasure chest to expose the mouse beneath a clear cover. There's even a little magnet to hold the lid down. Inside the lid is directions on how to use the primary gestures.

You open the box by pulling the lid all the way off and releasing the mouse from it's stand. There's a nano receiver and batteries are included. While the nano receiver has a home inside the base of the mouse for storage, it's unfortunate that there isn't an option for using Bluetooth as I need another nano receiver like I need a hole in the head. All up, the packaging is primo.

The mouse itself is pleasingly heavy. I suspect if I opened it up I'd find a small lead weight inside to give it just a smidge of heft.

Tracking

The tracking uses the newer BlueTrack system rather than the Red-light system most of my mice use. It's also extremely responsive and tracks on darn near anything. I don't need to use a mousepad with this mouse, but I always use a WowPad because they make any mouse work better. It really tracks perfectly, as it should. I haven't have any issues with poor tracking mice in the last several years since the optical-super-laser-whatever technology came out and this mouse is no exception.

Gestures and Software

The software is a new tab called Touch within the Mouse control panel. It's got a nice side-car window with a lopping video showing you how to do each gesture. It's a little subtle, but you can play each video with the little blue play button on the left (they look like bullet points.)

Gestures supported are:

  • One finger scroll pan (vertically and horizontally)
  • One thumb swipe - A forward/back gesture, just like the thumb forward and back buttons on most mice (and many keyboards!) these days. Works in browsers, Outlook, OneNote and any other software that listens for forward/back.
  • Two finger swipe - This is really useful, in fact. Up is maximize, down is minimize, left and right are snap to the sides.
  • Three finger swipe - Gives you an "exposé" style super dashboard of all your applications. They call it "instant viewer." More on that in a second.

Here's a look at the software:

The Microsoft Touch Mouse Control Panel

Instant Viewer is what you get with a three finger swipe. It looks like this. The idea is, swipe, click to task switch, and it works great, it just is a little awkward to three finger swipe. Fortunately, 4 fingers or basically the whole hand works too, so a big swipe up is much more comfortable.

An arranged grid of all my open windows

Scrolling

Here's the part I'm having trouble getting past. Sometimes the scrolling just doesn't register. Often it registers on the second or third flick. I can't tell if this is hardware or software, but it's not cool. Maybe it's me. Things that you touch, whether they are phones, touchpads or mice should always work, every time, exactly. I think that one of the reasons that iPhones and capacitive screens are so successful is because of their responsiveness. Early PDAs suffered from that, tap, tap, tap, react cycle. Sometimes unresponsiveness is perceived unconsciously and other times it finds its way into your conscious brain. I was/am far too aware of scrolling gestures not registering far too often.

That said, the other gestures (two and three fingers) work EVERY time. I've really tested it with gentle touch, hard touch, etc and the two finger gestures ALWAYS work. I suspect there is some kind of scrolling driver bug going on here as the hardware seems very reliable. I'd love to hear from someone on the team if there is a known scrolling issue.

The other issue with scrolling is that you have to move your finger about a 1/2 cm before a scroll registers. I think this is to make the mouse not be too jittery. If it scrolled every time you moved at all, I suspect that would be irritating. That said, this should absolutely be a setting I can control.

This might very well be "be design." But when you make something called a "Touch Mouse" in a world where iPads and Windows Phones have pixel-perfect scrolling, the comparisons will be drawn. I want to casually touch and scroll without thinking. Perhaps even a few pixels. The illusion is broken when a touch has to be a half-flick.

The scrolling also has built in inertia. If you flick it fast, it'll scroll fast and then slow down. This is nice because it feels like a tablet device where you can "throw" a browser page and it'll accelerate and decelerate in a natural way. This "inertial scroll" could really be the killer feature if the actual first touch that starts the scroll was more reliable.

Limitations

I assumed that the middle area of the mouse would register as a Middle Click. It doesn't, so this is a 2 button mouse. This is almost a deal breaker for me because I use the middle click all the time when browsing  to open new tabs in the background or to close a tab without switching to it. If you rely on the middle mouse button (not everyone does) then be aware. You may not miss it until it's gone.

Conclusion

This is a multitasker's mouse. If you have a great mouse that you like, should you switch? No. But if you are in the market for a mouse AND you are a person with MANY windows open or a person with multiple monitors I can see how the window management features would be really useful.

However, for me, I'm on the fence if this scrolling issue is a deal breaker or not. If there's an upcoming driver update that really nails the scrolling sensitivity down then I'll recommend this mouse wholeheartedly.  Until then, I think I'll alternate between this one and my trusty and wonderful Microsoft Arc Mouse, which rocks completely.

Related Links

About Scott

Scott Hanselman is a former professor, former Chief Architect in finance, now speaker, consultant, father, diabetic, and Microsoft employee. He is a failed stand-up comic, a cornrower, and a book author.

facebook twitter subscribe
About   Newsletter
Sponsored By
Hosting By
Dedicated Windows Server Hosting by SherWeb

Making awesome Wedding documents using OpenType Ligatures and Stylistic Sets in Microsoft Word 2010 and Gabriola

August 2, '11 Comments [20] Posted in Tools
Sponsored By

ligaturesmallMy brother-in-law is getting married in a few weeks and  since I'm the default IT guy for the family, I'm making CDs with CD-TEXT for the guests amongst other things. One of those other things is doing the Agenda for the day as well as the Menus that appear on the guests' tables. Anyone can bang out a simple document in word and/or pick a wacky font, but I wanted to try something a little different.

Microsoft Word 2010 includes advanced support for OpenType Fonts, not just TrueType fonts. One of the cool things in OpenType and Word is the support for ligatures. From Wikipedia:

"In writing and typography, a ligature occurs where two or more graphemes are joined as a single glyph."

For example, here's lowercase "fi" and lowercase "fl" first without ligatures, and then with:

The characters fi and fl without ligatures, then with

See how the two letters flow together with ligatures? Here's more examples with words like "office," "afflict," and "fine flavor."

More fi and fl words with ligatures applied

Open Ligatures are really visible (and mind-blowing if you've expected fonts to work a certain way for 20 years like me) in complex scripts like Gabriola, one of the many new fonts that comes with Windows 7. The Gabriola font is filled with advanced Open Type features.

The letterforms will change based on the context of the other letters around them. For example, notice how the second m in the word murmur gets out of the way of the r that would otherwise encroach on its space? It's different from the first m.

The word "murmur" in Gabriola

This is just the default behavior, but with Word 2010 you can control it from the Advanced Tab in the Font Menu. For example, I wanted to create a wedding menu so here's the first few lines with the defaults for Gabriola:

image

Now, I'll right click on Menu and select Font, then Advanced. There are a number of Stylistic Sets, depending on which font you're using. I liked the look of Set 6.

The Advanced Font Menu in Word 2010

Next,  for the second and third lines I changed to Stylistic Set 5 with Contextual Alternates to give me more options. Compare the two side by side! So much has changed, some subtle, some not. Notice the captial S's, the lowered C, the additions to the L and the tail on the small n and p?

The menu with the default sets The same menu lines with a nicer stylistic set

 

There's a lot of choices that you can make to get the look you want, and of course, this is just the Gabriola font we've see so far!

Magic Unicorns

The font will even change based on where it appears on a line, for example, the word amazon at the start, middle and end (taken from this article by John Hudson, the font designer):

 

Amazon in three styles

Additionally, this all happens as you type. You have to see it to believe it. I made an animated GIF at the top for you to get the idea. This might seem very weird to English/Latin speakers, but if you have ever typed in Hindi or Arabic you're already used to dynamic ligatures as you type.

Word 2010 also support different modes for numbering, like Old-Style Number Forms. Notice how the numbers' baselines are different on the second line and their sizes have been adjusted?

1234567 first aligned to the top, then along a baseline. Note the smaller "2"

All very cool stuff just sitting there in Word 2010 for you to use. Enjoy!

All very cool stuff just sitting there in Word 2010 for you to use. Enjoy!

About Scott

Scott Hanselman is a former professor, former Chief Architect in finance, now speaker, consultant, father, diabetic, and Microsoft employee. He is a failed stand-up comic, a cornrower, and a book author.

facebook twitter subscribe
About   Newsletter
Sponsored By
Hosting By
Dedicated Windows Server Hosting by SherWeb

Unix Fight! - Sed, Grep, Awk, Cut and Pulling Groups out of a PowerShell Regular Expression Capture

August 1, '11 Comments [15] Posted in PowerShell
Sponsored By

There's a wonderful old programmers joke I've told for years:

"You've got a problem, and you've decided to use regular expressions to solve it.

Ok, now you've got two problems..."

A friend of mine was talking on a social network and said something like:

"That decade I spent in the Windows world stunted my growth. one teeny-tiny unix command grabbed certain values from an XML doc for me."

Now, of course, I took this immediately as a personal challenge and rose up in a rit of fealous jage and defended my employer. Nah, not really as I worked at Nike on Unix for a number of years and I get the power of sed and awk and what not. However, he said XML, and well, PowerShell rocks XML.

Because it's a dynamic language, you can refer to XML nodes just like this:

$a = ([xml](new-object net.webclient).downloadstring("http://feeds.feedburner.com/Hanselminutes"))
$a.rss.channel.item

The first line gets the feed and the second line gets all the items.

However, turns out my friend was actually trying to retrieve values within poorly-formed XML fragments within a larger SQL dump file. There's three kinds of XML. Well-formed, valid, and crap. He was sifting through crap for some values. Basically he had this crazy text file with some fragments of XML within it and wanted the values in-between elements: "<FancyPants>He wants this value</FancyPants>."

Something like this:

grep "<FancyPants>.*<.FancyPants>" test.txt | sed -e "s/^.*<FancyPants/<FancyPants/" | cut -f2 -d">"| cut -f1 -d"<" > fancyresults.txt

I'm old, but I'm not an expert in grep and sed so I'm sure there are ways he could have done it more tersely. There always is, right? With regular expressions, sometimes someone just types $@($*@)$(*@)(@*)@*(%@%# and Shakespeare pops out. You never know.

There's also a lot of different ways to do this in PowerShell, but since he used RegExes, who am I to disagree?

First, here's the one line answer.

cat test.txt | foreach-object {$null = $_ -match '<FancyPants>(?<x>.*)<.FancyPants>'; $matches.x}

But I thought I'd also sort them, remove duplicates...

cat test.txt | foreach-object {$null = $_ -match '<FancyPants>(?<x>.*)<.FancyPants>'; $matches.x} | sort | get-unique

But foreach-object can be aliased as % and get-unique can be just "gu" so the final answer is:

cat test.txt | % {$null = $_ -match '<FancyPants>(?<x>.*)<.FancyPants>';$matches.x} | sort | gu

I think we can agree at they are both hard to read. I still love PowerShell.

Related Links:

About Scott

Scott Hanselman is a former professor, former Chief Architect in finance, now speaker, consultant, father, diabetic, and Microsoft employee. He is a failed stand-up comic, a cornrower, and a book author.

facebook twitter subscribe
About   Newsletter
Sponsored By
Hosting By
Dedicated Windows Server Hosting by SherWeb

Entity Framework Code First Migrations: Alpha - NuGet Package of the Week #10

July 28, '11 Comments [47] Posted in Data | NuGet | NuGetPOW
Sponsored By

Hot on the heels of my RFC blog post on product versioning, the Entity Framework team has released Entity Framework 4.1 Code First Migrations: August 2011 CTP. Cool. And it's July, too!

Or my preferred product name, "Migrating Magic Unicorns 0.5." It's probably best to think of this as 0.5 Alpha Migrations for EF but that's my guess at a name and not nearly as descriptive.

I showed early daily builds of EF Migrations at a few conferences recently, and encouraged folks to comment on the ADO.NET team blog. Now they've released bits for us to play with. This initial CTP is available via NuGet as the EntityFramework.SqlMigrations package.

EntityFramework.SqlMigrations - 0.5.10727.0

Here's the general idea. Be aware that this is NOT specific to the Web. You can do this in a Console App or whatever. I just like Hello World Web applications.

Short Walkthrough

Make a new ASP.NET MVC app. Go to Manage NuGet Packages from References and search for EntityFramework.SqlMigrations. Note the dependency it has. It'll upgrade your project's EntityFramework package as well.

Entity Framework SQL Migrations In NuGet

Now, make a new class with a simple model:

namespace MvcApplication15.Models
{
public class Person
{
public int PersonId { get; set; }
public string Name { get; set; }
}

public class DemoContext : DbContext
{
public DbSet<Person> People { get; set; }
}
}

Scaffold out a quick Person Controller...

Add Controller

Visit it at /Person and make a few People.

 Index - Windows Internet Explorer (58)

The database that was created looks like this. There's a People table and the First column that is an nvarchar.

image

Now, let's add an Email field. I'll update the Person class to include Email:

public class Person
{
public int ID { get; set; }
public string Name { get; set; }
public string Email { get; set; }
}

Then, I'll compile and from the console inside Visual Studio, I'll do this:

PM> update-database
No pending custom scripts found.
Ensuring database matches current model.
- Performing automatic upgrade of database.
- Starting rebuilding table [dbo].[EdmMetadata]...
- Caution: Changing any part of an object name could break scripts and stored procedures.
- Starting rebuilding table [dbo].[People]...
- Caution: Changing any part of an object name could break scripts and stored procedures.
- Update complete.

I typed update-database, that's all. This is an automatic migration. See how the system compare the .NET type and the database and did what needed to be done:

image

Now, let's rename Email to EmailAddress. If I change the Person...

public class Person
{
public int ID { get; set; }
public string Name { get; set; }
public string EmailAddress { get; set; }
}

And type update-database...

PM> update-database
No pending custom scripts found.
Ensuring database matches current model.
- Performing automatic upgrade of database.
Update-Database : - .Net SqlClient Data Provider: Msg 50000, Level 16, State 127, Line 6 Rows were detected. The schema update is terminating because data loss might occur.
At line:1 char:16
+ Update-Database <<<<
+ CategoryInfo : NotSpecified: (:) [Update-Database], Exception
+ FullyQualifiedErrorId : UnhandledException,System.Data.Entity.Migrations.Commands.MigrateCommand

I'm told that data loss may occur. It can't tell that I want to rename that column or not. It doesn't know what it was before and what it wants to be. Maybe I want to drop Email and add EmailAddress? Who knows. Let me be explicit and give Migrations more context.

PM> Update-Database -Renames:"Person.Email=>Person.EmailAddress"
No pending custom scripts found.
Ensuring database matches current model.
- Performing automatic upgrade of database.
- The following operation was generated from a refactoring log file d5598498-a656-4ccd-1e93-bea562ab6e31
- Rename [dbo].[People].[Email] to EmailAddress
- Caution: Changing any part of an object name could break scripts and stored procedures.
- Update complete.

I'm not sure if I like that Renames: syntax. I'm sure the team would be interested in your opinion. But that works, as I can see in the database.

image

What Changes Can Migrations Detect Automatically?

From the ADO.NET blog:

Here is the full list of changes that migrations can take care of automatically:

  • Adding a property or class
    • Nullable columns will be assigned a value of null for any existing rows of data
    • Non-Nullable columns will be assigned the CLR default for the given data type for any existing rows of data
  • Renaming a property or class
    • See ‘Renaming Properties & Classes’ for the additional steps required here
  • Renaming an underlying column/table without renaming the property/class
    (Using data annotations or the fluent API)
    • Migrations can automatically detect these renames without additional input
  • Removing a property
    • See ‘Automatic Migrations with Data Loss’ section for more information

Moving to Staging/Production/etc

Once dev is correct, when you want to move to production, you would generate a script for your other database by doing a diff between what that DB looks like and what the code looks like.

For example, -script generates a script I can run myself with osql.exe or whatever.

update-database -Script -ConnectionString "SERVER=.\SQLEXPRESS;Database=PersonProd;Trusted_Connection=true;"

Which spits out something like (but more complex than) this:

CREATE TABLE [dbo].[People] (
[ID] INT IDENTITY (1, 1) NOT NULL,
[Name] NVARCHAR (MAX) NULL,
[EmailAddress] NVARCHAR (MAX) NULL,
PRIMARY KEY CLUSTERED ([ID] ASC)
);

Call for Feedback

The EF Team has also this specific call for feedback when it comes to Custom Scripts:

Call for Feedback: From what we are seeing in our own internal use we don’t anticipate that custom scripts will be required very often. However, our efforts are somewhat sheltered from the ‘real world’ so we would love feedback on situations where you need to use custom scripts. In particular we are interested if there are significant scenarios where a code based alternative to writing raw SQL would be beneficial.

Leave your thoughts in my comments, or theirs and I'll make sure the right people get it.

Enjoy!

P.S. Note the EntityFramework.SqlMigrations NuGet package's exposed version number. It's 0.5.10727.0. ;)

Limitations

This is Alpha, so read the Limitations section. They are putting out rough things like this because they know we want to see bits earlier, but the trade off is limitations. Here's a few. Read the list for the rest.

  • There is no provider model, this release only targets SQL Server, including SQL Azure. SQL Compact and other providers are not supported. We are currently working through what the provider model should look like for migrations.
    • Question: What do you think? How important is this?
  • Migrations currently needs to run in full trust. This isn’t an issue when working inside of Visual Studio but if consuming the migrations assembly from custom code you may want to run in medium trust. We are looking at ways to support this in a later release.
  • This release is only available via NuGet. As we support more scenarios such as team build and an ‘outside of Visual Studio’ command line experience we will also support more installation options.
    • Scott: Limitation? That's lovely! ;)
  • Downgrade is currently not supported. When generating custom scripts you will notice that the script is named ‘Up.sql’ but there is no corresponding ‘Down.sql’. We are planning to add downgrade functionality prior to RTM but it is not available in this release.

I'm glad we're getting migrations. It's been a missing LEGO piece for a while.

Related Links

About Scott

Scott Hanselman is a former professor, former Chief Architect in finance, now speaker, consultant, father, diabetic, and Microsoft employee. He is a failed stand-up comic, a cornrower, and a book author.

facebook twitter subscribe
About   Newsletter
Sponsored By
Hosting By
Dedicated Windows Server Hosting by SherWeb

Disclaimer: The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.