Tuesday, February 17, 2009

Stored Procedure to Flip Staging Tables

We have several applications that are relatively data intensive. For the most part, the applications run just fine when executing simple queries against indexed tables. The problem comes when we need to aggregate data for reporting or perform queries against cross-joined or unioned tables. As a result, sometimes we create data tables that are periodically populated with the results of the slow cross-join or union and are subsequently indexed to optimize performance for future queries.


That's all well and good, but you can't really fill that table while the application is running queries against it, so what do you do? Well, you create a staging table, fill it, and the point your application at your new table, right?


Something I picked up from partitioning very large table during my data warehousing days is the idea that I can point a view at a table and treat it like a table and it will be almost as performant as the table with its indexes. Thus, if I want to have a staging table and a production table, I don't have to put any intelligence in my application; rather, I can leave the staging and swapping to the database.


For example, if I have a very large table of invoices (which I would, of course, love to have), then I could create two tables called Invoice1 and Invoice2. I could then point the view Invoice at Invoice1 and InvoiceStaging at Invoice2. Thus, any procedure or application that needs to build my staging table can do so always by referencing InvoiceStaging and my production table will always be referenced with Invoice.


The only difficulty really is switching the view to have it point to the new table and to point the staging view to the old table. I wrote a stored procedure that would look into a settings table, determine the current production and staging tables, swap them, and write the new settings back to the settings table. It was fine really and I didn't have any trouble with it, but one day I had an idea. I wrote this stored procedure to handle my switching for me and it does it all with nothing more than some of the system tables.
CREATE PROCEDURE [dbo].[FlipStagingTables]
(
@TableName VARCHAR(255)
)
AS

DECLARE @Active VARCHAR(255);
DECLARE @Staging VARCHAR(255);

SELECT
@Active = MAX(CASE v.name WHEN @TableName + 'Staging' THEN t.name ELSE NULL END),
@Staging = MAX(CASE v.name WHEN @TableName THEN t.name ELSE NULL END)
FROM sysdepends d
INNER JOIN sysobjects v
ON d.id = v.id
INNER JOIN sysobjects t
ON d.depid = t.id
WHERE v.name IN (@TableName, @TableName + 'Staging');

IF @Active IS NULL OR @Staging IS NULL
SELECT @Active = @TableName + '1', @Staging = @TableName + '2';

IF OBJECT_ID(@TableName) IS NOT NULL
EXEC('ALTER VIEW ' + @TableName + ' AS SELECT * FROM ' + @Active);
ELSE
EXEC('CREATE VIEW ' + @TableName + ' AS SELECT * FROM ' + @Active);

IF OBJECT_ID(@TableName + 'Staging') IS NOT NULL
EXEC('ALTER VIEW ' + @TableName + 'Staging AS SELECT * FROM ' + @Staging);
ELSE
EXEC('CREATE VIEW ' + @TableName + 'Staging AS SELECT * FROM ' + @Staging);

EXEC('TRUNCATE TABLE ' + @Staging);

Friday, February 13, 2009

Should an Astronaut Keep a Pistol for Propulsion

I was trying to find some group matrix ranking and estimation stuff I found one time in graduate school. I remember working on this project that led to the discovery. It was sort of a group assignment and I had done similar things 4 or 5 times throughout my education. So, I started searching for the documentation and ran across several examples.

The one I did in graduate school was something about some NASA astronauts were stranded on the moon and they had 25 things. We were to individually rank the items and then rank them as a group so that we could see whether our individual rankings or our group rankings were more similar to the expert rankings. The experts, of course, were NASA astronauts.

Today, I found another example about surviving in the Canadian wilderness. In this case, the expert was a US Army Survival School instructor.

In both cases, there is "a loaded .45 caliber handgun."

Now, here's my beef (there must be a beef . . . otherwise, what would I be blogging about?). I don't believe that the people who identified the "gold standard" rankings were actually experts. I think they're somewhat clever, but not NASA astronauts and not US Army survival instructors. In fairness, I don't believe that the official versions of these tasks make the claim that there were ever any experts involved, but most versions offer some fictitious evaluator to give clout to the "correct" answers.

So, here's why I doubt the expert claims. I don't believe that a US Army survival instructor would list a pistol as the 9th out of 12 items in terms of importance and certainly not because "although a pistol could be used in hunting, it would take an expert marksman to kill an animal with it. Then the animal would have to be transported to the crash site, which could prove difficult to impossible depending on its size."

Let's keep in mind that an army survival instructor has probably gone through at least the S part of S.E.R.E. During this training, they learn to kill animals with sticks and rocks. To be sure, if you can club a critter with a stick, you can shoot the little guy and eat him there or at camp.

Second, I doubt the army instructor would agree that "the pistol also has some serious disadvantages. Anger, frustration, impatience, irritability, and lapses of rationality may increase as the group awaits rescue. The availability of a lethal weapon is a danger to the group under these conditions."

I mean, here's a guy who has probably spent a fair amount of time in really crappy situations with a bunch of people who had a bunch of guns. If someone is going to flip his lid and start killin' folks, he's not really going to need the pistol to do so. Just bring it.

Of course, I disagree with a lot of the valuation assessments on most of the items, but I'm harping on the pistol thing for a good reason. Not because I like guns and not because I'm a proud gun toting American, but because I was really irritated when I ranked a pistol among the least important things in space but my group chastised me when they ranked it in the top 5!

Now, first of all, I realize that a gun can be fired in space, but I also realize that there's a low probability of having a target at which to fire the gun. The reason my group (and NASA . . . purportedly) wanted to keep the gun around was as a means of propulsion. After finding this again today, I've done a little math. I'll admit that my physics is a little rusty these days, I'm pretty sure that the numbers are pretty close.

So, pretty much the biggest bullet I could find for a .45 was 230 grain. It'll leave a Glock at 880 ft/s. 1 grain = 1/7000 lbs. Momentum = mass * velocity. Thus, the momentum of the bullet leaving the gun shot by a guy standing on the moon would be 28.9 pound foot per second. If a guy my size (180 lbs) was in a space suit (180 lbs according to NASA . . . no, really) that was designed for walking on the moon (the floating in space suit is much heavier), said guy would weigh 260 lbs carrying 2 lbs worth of gun for a total of 262 lbs.

According to Newton's third law of motion, the gun firing the bullet must exert equal force against the shooter (id est, 28.9 lb ft/s). If we divide that by the weight of the shooter and his junk, we get about .11 ft/s or 6.62 feet per minute or .0752 miles per hour. Any way you look at it, it'll take a long time to get around the moon with a handgun.

Wednesday, February 11, 2009

What We Can Learn From Michael Phelps

Now, I don't smoke pot, but this seems to be a relevant point. Michael Phelps has lost numerous endorsement contracts and has been suspended from competitive swimming as a result of the surfacing of an incriminating picture of him hitting a bong at a party (and I suppose we just presume there's marijuana in it . . . but whatever).

Now, people all over the world are looking at Michael Phelps like they did when that Dell kid (you know . . . dude, you're getting a Dell) got in trouble. People are like, "man, Michael, why would you ruin your life like that?"

What if we're looking at it wrong. Maybe we shouldn't think that Phelps is a fuck up; rather, that Phelps is not a fuck up. What if all that stuff they've always told us about marijuana isn't really true? What if there are more functioning potheads than there are functioning alcoholics? What if you can smoke pot and still be the most awarded Olympian of all time? Perhaps a little pot from time to time doesn't actually ruin your life and turn you into an absentminded vegetable.

Who'd've thunk it? A true Olympic endorsement for marijuana. And honestly, you can't tell me that you didn't know the Dell kid was a pothead? I mean, that's why everybody liked him! He acted like a pothead. He acted like a pothead at his audition and Dell hired him because he seemed cool and hip and people would relate. Then, when we found out he actually did smoke, we fired him!

And for my final point . . . marijuana is great for the snack food industry.

Monday, January 19, 2009

Stop Using My Social Security Number as a Password

I've got another quick security post today. I'm getting really tired of people using my social security number like it's a password. Social Security wasn't invented to provide companies with a means of authentication. The SSN was never supposed to be a secret; it was designed to be an identifier. It's the one thing that's supposed to follow you throughout your life no matter what you do to your name or address.

However, now banks, cellphone providers, and the like use the SSN as though it's the ubiquitous epitome of "shared secret." The fact is that your social security number isn't really all that secure and if it is ever compromised, you can't change it. Imagine this. Let's say you're on the phone with your bank and they ask for your authentication password and you tell them, "it's peanut butter." Someone could hear you! Fortunately, if they do, you can call back in privacy and say, "I don't want my password to be peanut butter; that's been compromised. Please change it to french fries." Now, your password is secure again. Now, imagine you call your bank and they ask, "what're the last four digits of your social security number?" You say, "1234." Right then, you realize someone is writing down (or recording) everything you're saying. Sorry, but you're shit out of luck.

So what is the social security number for? Like I said, it's an identifier. It's a way that any company can keep you separated from all of the other entities in their database. Nobody will ever have the same social security number as you. That makes it less like a password (well, not at all a password) but rather a username. You're looking at dpatrickcaldwell.wordpress.com. dpatrickcaldwell is my username. Imagine what a dip stick you'd think me to be if my password was also dpatrickcaldwell. I invite you to try it . . . it's not dpatrickcaldwell (nor is it the last four digits of my social security number).

I know there are several readers (and judging by my statistics . . . about 3) who are now thinking, "well, what should I do then? Everybody wants it!" Tell them no. Say, "I cannot allow you to use my social security number as my password. I will alternatively provide a PIN or a pass phrase." Eventually, they'll understand that you're serious and they'll use something else or I'd recommend you find yourself another financial institution. Hell, if enough of us walk into our banks, sing a bar of Alice's Restaurant, change our authentication method to something other than our socials, and walk out . . . they just might think it's a movement . . . and friends, that's just what it is. The Anti Security Massacre Movement.

Please insist on using something other than your social security number for authentication and you'll find that your risk of identity theft will drop considerably.

Security Liars! Don't Email My Password to Me. WTF?

I have 3 levels of security when it comes to my passwords. The first level is the one I use for all of my banking institutions. It is long and complex and I change it relatively frequently. The second is the middle tier password and I use it for websites that I really don't want people getting into like my email. The third is the password I use for all of that crap that I don't really care that much about like facebook and sites with security I feel can't be trusted.

Today, I signed up for a new site. It qualified as a middle tier site with regard to the personal information they'd undoubtedly be storing. It also passed the other tests leaving me relatively certain that they'd be protective over my security. They use HTTPS, they have the VeriSign approved logo, and all appears well and good. I signed up, created a username, and put my middle tier password in the box. I hit submit and about 30 seconds later, my confirmation email appeared in my inbox.

It was the standard confirmation email. Welcome Patrick. We're glad to have you. Keep your username filed away for future reference. Your password is . . . WTF? My password? Why in the name of all things holy and good did they email my password to me? I typed the damned thing in there twice? I obviously knew what it was. What kind of idiots do they have workin' over there?

As my brain flooded with questions, a few important things stood out. If they emailed my password, did they email it before they stored it (not likely) or are they storing it in plain text (likely . . . and stupid)? How am I ever supposed to trust this company with my credit card information if I can't trust them with my password? And why did they even bother going through all of the effort to get an SSL certificate to secure my HTTP post if they were just going to send my damned password out in an email?

As a programmer, I know that there are two potential cases: first, they're too dumb to know any better or second, they're liars. By virtue of the fact that they actually did bother to get an SSL cert, I can only presume that they're just security liars. They know they need to secure the site and I'm sure they believe that they should protect your password, but they obviously aren't. I am very disappointed and I wish I could have my password back before some rogue developer over there decides he (or she) wants to publish the entire password database on the internet.

Wednesday, December 17, 2008

Using the Proxy Pattern to Write to Multiple TextWriters

I was working on a data synchronizing application the other day.  I needed to write to a file for the export, write to a string builder for logging and analysis, and write to the console for debugging.  I know that it's pretty common that I'll need to write to more than 1 text stream at the same time, so I figured I could write a quick proxy application to write to a collection of TextWriters.Please comment on this post and let me know how this TextWriterProxy article helped you.Here's what I came up with:
    1 using System.Collections.Generic;
    2 using System.Text;
    3 using System.IO;
    4 
    5 namespace ESG.Utilities
    6 {
    7     public class TextWriterProxy : TextWriter
    8     {
    9         // store TextWriters here
   10         private List<TextWriter> _writers = new List<TextWriter>();
   11 
   12         #region Properties
   13 
   14         /// <summary>
   15         /// This property returns Encoding.Default.  The TextWriters in the
   16         /// TextWriterProxy collection can have any encoding.  However, this
   17         /// property is required.
   18         /// </summary>
   19         public override Encoding Encoding { get { return Encoding.Default; } }
   20 
   21         /// <summary>
   22         /// Gets or sets the line terminator string used by the TextWriters in
   23         /// the TextWriterProxy collection.
   24         /// </summary>
   25         public override string NewLine
   26         {
   27             get
   28             {
   29                 return base.NewLine;
   30             }
   31 
   32             set
   33             {
   34                 foreach (TextWriter tw in _writers)
   35                     tw.NewLine = value;
   36 
   37                 base.NewLine = value;
   38             }
   39         }
   40 
   41         #endregion
   42 
   43         #region Methods
   44 
   45         /// <summary>
   46         /// Add a new TextWriter to the TextWriterProxy collection.  Setting properties 
   47         /// or calling methods on the TextWriterProxy will perform the same action on 
   48         /// each TextWriter in the collection.
   49         /// </summary>
   50         /// <param name="writer">The TextWriter to add to the collection</param>
   51         public void Add(TextWriter writer)
   52         {
   53             // don't add a TextWriter that's already in the collection
   54             if (!_writers.Contains(writer))
   55                 _writers.Add(writer);
   56         }
   57 
   58         /// <summary>
   59         /// Remove a TextWriter from the TextWriterProxy collection.
   60         /// </summary>
   61         /// <param name="writer">The TextWriter to remove from the collection</param>
   62         /// <returns>True if the TextWriter was found and removed; False if not.</returns>
   63         public bool Remove(TextWriter writer)
   64         {
   65             return _writers.Remove(writer);
   66         }
   67 
   68 
   69         // this is the only Write method that needs to be overridden
   70         // because all of the Write methods in a TextWriter ultimately
   71         // end up calling Write(char)
   72 
   73         /// <summary>
   74         /// Write a character to the text stream of each TextWriter in the 
   75         /// TextWriterProxy collection.
   76         /// </summary>
   77         /// <param name="value">The char to write</param>
   78         public override void Write(char value)
   79         {
   80             foreach (TextWriter tw in _writers)
   81                 tw.Write(value);
   82 
   83             base.Write(value);
   84         }
   85 
   86         /// <summary>
   87         /// Closes the TextWriters in the TextWriterProxy as well as the 
   88         /// TextWriterProxy instance and releases any system resources
   89         /// associated with them.
   90         /// </summary>
   91         public override void Close()
   92         {
   93             foreach (TextWriter tw in _writers)
   94                 tw.Close();
   95 
   96             base.Close();
   97         }
   98 
   99         /// <summary>
  100         /// Releases all resources used by the TextWriterProxy and by the 
  101         /// TextWriters in the TextWriterProxy collection. 
  102         /// </summary>
  103         /// <param name="disposing">Pertains only to the TextWriterProxy instance: 
  104         /// true to release both managed and unmanaged resources; false to release 
  105         /// only unmanaged resources.</param>
  106         protected override void Dispose(bool disposing)
  107         {
  108             foreach (TextWriter tw in _writers)
  109                 tw.Dispose();
  110 
  111             base.Dispose(disposing);
  112         }
  113 
  114         /// <summary>
  115         /// Clears all buffers for each TextWriter in the TextWriterProxy 
  116         /// collection and causes all buffered data to be written
  117         /// to the underlying device.
  118         /// </summary>
  119         public override void Flush()
  120         {
  121             foreach (TextWriter tw in _writers)
  122                 tw.Flush();
  123 
  124             base.Flush();
  125         }
  126 
  127         #endregion
  128     }
  129 }

So far, it works great. It cleans up a lot of my code and gives me the option to write to any number of TextWriters with only one call. Further, if you are calling a method that takes a TextWriter as a parameter, you can pass the TextWriterProxy to it because it extends the TextWriter class. Here's what the usage syntax looks like:
    1 // create a TextWriterProxy instance
    2 TextWriterProxy proxy = new TextWriterProxy();
    3 
    4 // add the Console.Out TextWriter
    5 proxy.Add(Console.Out);
    6 
    7 // you can still write directly to console
    8 Console.WriteLine(string.Empty.PadRight(80, '='));
    9 
   10 // add a StreamWriter for a FileStream
   11 FileStream fs = new FileStream("C:\\TestExportFileAutoGen.abx", FileMode.Create);
   12 StreamWriter resultWriter = new StreamWriter(fs);
   13 proxy.Add(resultWriter);
   14 
   15 // add a StringWriter for a StringBuilder
   16 StringBuilder sb = new StringBuilder();
   17 StringWriter resultStringWriter = new StringWriter(sb);
   18 proxy.Add(resultStringWriter);
   19 
   20 // call a method that takes a TextWriter
   21 ClientSync.GenerateSessionDataExport("Sync.ServerExport", proxy);
   22 
   23 // write directly to the TextWriterProxy
   24 proxy.WriteLine("Export Complete!");
   25 
   26 // close all of my writers
   27 proxy.Close();

And there you have it. A TextWriterProxy class to write to multiple TextWriters at once.

Thursday, December 4, 2008

An Online Image Thumbnailer

Hey folks,

I've published a small online image thumbnail utility. If it proves useful (without bogging down our servers), I'll leave it up for public consumption. If you're interested in reading a little more about it, you can find the story below. If you'd just like to see the utility, visit D. Patrick Caldwell's Image Thumbnailer.

So, there I was, trying to get a background off of my Picasa Web Albums to put on my iPhone. In the iPhone Safari browser, you can save images by holding your finger on the image until a save dialog box comes up. Problem is, Picasa has somehow (somewhy) disabled it. So, I figured a bookmarklet would help me out. I could then link directly to the image and all of the Picasa scripts would be gone.

I found a few bookmarklets that displayed all of the images in the page, but they all open in the current window and I wanted a new window (and some nice formatting wouldn't hurt either). So, I looked for an online image thumbnailer and couldn't find one. About 30 minutes later, I had a thumbnailer. An hour after that, I had my bookmarklets (and a new background incidentally). I added a little error handling and some logging and had a fully functional service in about 2 hours.

Then I spent about 8 hours styling the welcome page :).

In any event, here's what my Thumbnailer does. I can take any image anywhere on the net (well, most anywhere . . . I have to have access to the image), and produce a thumbnail with one or two constraints: maximum width and maximum height. Here's an example of the same image scaled to 4 different heights:








The same thing works for widths:








Finally, you can specify both width and height and it'll use the most restrictive parameter.