A Monster Of An SQL Query

So i'm in the middle of working on a web project. At some point I'll be switching over to using home brewed blogging software on my main website and I'll eventually figure out how to cross-post to here or something. Whatever.

My point is that I've been sharpening up my SQL skills. Today, over the course of about five hours I taught myself how to make the following SQL query. What does this sucker do? It let's me take three different tables from my database, grab values from all of them and then sum up some of them but not others.

What's the context? It's the main query I needed to figure out how to write in order to get the Leaderboard for S.Rex War to work the way I envisioned. Yes that's a rather mysterious comment. If I had coded up the blog-lite software I could be blogging this as I go along. Maybe I'll do that tomorrow. It shouldn't take me very long at all to do.

SELECT FullName, Wins, Losses, Games, (Wins/Games) AS Ratio, UserJoinDate FROM (SELECT CONCAT(User.FirstName, " ", User.LastName) AS FullName, SUM(IF(STRCMP(Game.GameStatus,'1'), 0, 1)) AS Wins, SUM(IF(STRCMP(Game.GameStatus,'2'), 0, 1)) AS Losses, SUM(1) AS Games, UserAction.ActionDate As UserJoinDate FROM User INNER JOIN Game ON User.UserKey=Game.UserKey INNER JOIN UserAction ON User.UserKey=UserAction.UserKey WHERE UserAction.ActionType='Create' GROUP BY User.UserKey) AS LeaderBoard

Sunny Weather Makes An Encore

I spent the glorious weather yesterday doing some much needed long term work on the back of the house. I ripped up a BUNCH of sod to expand the size of the beds along the northern fence line. With the added square footage I'll be able to move a bunch of plants to the new space and re-arrange things to make the whole yard easier to maintain. I'll also be increasing the space I'm devoting to gardening by at least a third and I can now pretty reliably grow tomatoes, onions, garlic and spinach. I'll be experimenting with Swiss Chard this month (and anything else that doesn't mind being planted in October.)

I also harvested my onions and tomatoes. The onions this year were a bust as I expected, though I did pull ten pounds. The upside is that what I pulled will become the starters for next year's crop. So, they'll be going BACK into the ground in a couple of days, along with some some seeds I'll be planting just to see how growing onions from seed works.

I had expected the tomatoes to be something of a disappointment on account of the wonky weather we've had all year. Surprisingly, I had a fairly reasonable harvest of 15 pounds of red tomatoes and 10 pounds of green tomatoes. The red tomatoes are already mellowing their way into red sauce on my stove top. I'm going to try and turn the greens into tomato relish or something.

So, in the end I got about the same poundage (25 lbs. vs. 30 lbs.) of tomatoes from eight plants as I got from six healthy and three "blossom rot" afflicted plants last year. Of course, 2/5 of this year's poundage is in the form of green tomatoes so technically my yield was a lot lower. It all depends on whether or not I can transform the greens into something useful.

Next year I'll be going back to real tomato cages and giving the plants more room. Something on the order of two square feet per plant. I suspect I'll get much higher yields doing it this way. I still need to figure out how to handle Roma's growth tendencies. It seems like a combination of pruning and vertical support is key.

As for the "blossom rot" there's no sign of it this year. I don't really know what made the difference. I have started amending the soil with Gypsum to provide additional calcium and that could have helped. It's also entirely possible that some random factor that caused the blossom rot last year didn't happen this year.

Aspartame Kills With Sweet Seduction!!

The Backstory

The author of a blog I read recently published a post where he wrote about his Diet Dr. Pepper addiction (a monkey that I too carry on my back) and his concerns about Aspartame poisoning. Apparently, a well meaning friend told him about the internet controversy related to whether or not the methanol generated when Aspartame breaks down is toxic.

The Preface

My initial response when I come across references to one of the many internet hysterias scuttling about the Tubes is to scoff and click on. There's so much innuendo, rumor and distorted argument out there in the Tubes that finding dependable information is well nigh impossible unless you already have a good sense of what the correct answer looks like.

Every now and then, one of these rumors pops up in a locale that's "close enough" to me that I feel comfortable (and maybe even a little obligated) to put together a well thought out counter argument. Of course, when you do that, you always run the risk of offending someone. That's why I always try very hard to not affect the "everyone else is retarded" attitude that seems to infest a lot of the Tubes. Honestly, there are an enormous number of wildly varying opinions and it can be very difficult at times to figure out who's telling the truth.

The Reference Materials

Much of this information is gathered into a single source on Snopes. The first bit of the Snopes page contains that chain email that was the "seed" material for the urban legend.

Then there's links to rebuttals. My preference is the link to a letter published in The Lancet, as its a VERY prestigious medical journal, contains a good bit of technical information and address the actual Tubes related controversy to an extent.

I also relied fairly heavily on some pages on Wikipedia. While "tube-a-pedia" can often be a powerful source for disinformation, it can also be accurate and in the instances I used it for, I think it is.

The Analysis

Aspartame is made of two amino acids (Phenylalanine and Aspartic Acid) that are held together by a "methyl ester" bond. When ingested, Aspartame is split into its constituent amino acids and a methanol molecule is released. The reaction looks like this:



Near as I can tell from a quick perusal of tube-info is that, when in solution, Aspartame is an inherently unstable molecule and the "hydrolysis" reaction is going to happen no matter what.

However, how quickly this reaction occurs is a function of both the acidity and the temperature of the environment.

In a very acidic environment (like a can of diet soda) the molecule is fairly stable and thus useable as a consumer product.

In a neutral environment (like your intestines once your stomach acid's been neutralized or your blood) the molecule is a lot less stable. So, once ingested, it's pretty good bet that some, if not all, of the Aspartame is gonna break down.

So then, we're left with methanol, phenylalanine and aspartic acid. All of these are going to get absorbed into the blood stream and either excreted via the kidneys, metabolized by the body and excreted or (gasp!!) incorporated into your bodies genetic structure!!!

"How is this possible and will I develop mutant powers?", you ask? Well no, because you see, Phenylalanine and Aspartic Acid are both amino acids. Amino acids are the molecules your body uses to make proteins, your DNA and neurotransmitters. So, ingestion of these materials is not inherently bad.

In some cases, the ingestion of a specific amino acid can be harmful if your body lacks the ability to metabolize excess molecules of that type. In the specific case of Phenylalanine, some people cannot metabolize it because their body lacks the necessary enzyme thanks to an inherited genetic defect.

This is a specific metabolic disease and does not mean that any concentration of phenylalanine in the blood is bad. Rather, it is a persistently elevated concentration of phenylalanine in the blood that can cause brain damage.

Then there's the issue of the quantity of these molecules generated by ingesting a can of diet soda. I'm just going to directly quote the letter here:

Although a 330 ml can of aspartame-sweetened soft drink will yield about 20 mg methanol, an equivalent volume of fruit juice produces 40 mg methanol, and an alcoholic beverage about 60-100 mg.

The yield of phenylalanine is about 100 mg for a can of diet soft drink, compared with 300 mg for an egg, 500 mg for a glass of milk, and 900 mg for a large hamburger (1).

Thus, the amount of phenylalanine or methanol ingested from consumption of aspartame is trivial, compared with other dietary sources.

Clinical studies have shown no evidence of toxic effects and no increase in plasma concentrations of methanol, formic acid, or phenylalanine with daily consumption of 50 mg/kg aspartame (equivalent to 17 cans of diet soft drink daily for a 70 kg adult)

Personally, I find the last sentence the most illuminating. The clinical studies they're talking about aren't rocket science. If consuming 50 mg/kg of aspartame caused elevated levels of methanol, formic acid or phenylalanine in the blood stream it would be DEAD SIMPLE to find out.

A group of motivated graduate students could perform this study on themselves with nothing more than some syringes, basic biological laboratory glassware, a centrifuge, a high pressure liquid chromatograph and some college level knowledge.

The Conclusion

So really, that's pretty much it. The idea that you're somehow poisoning yourself by ingesting diet soda sweetened with Aspartame is pretty far fetched.

The potential liability its manufacturers and industrial users would be on the hook for if it did have toxic properties is vastly countered by the extreme ease with which a group of motivated individuals could scientifically prove the existence of these properties if they were, in fact, there. And believe me, there are a lot of motivated individuals out there with access to the actual resources needed.

If you believe that a plausible case for conspiratorial cover-up can be made, then I have a friend in Nigeria with a business proposition I want you to meet.

All this being said, there are a lot of arguments to be made against the consumption of highly processed foods like diet soda. These stem, however, from arguments related to simplicity and the consumption of limited resources. If a caffeine fix is what you need, then there are better ways to go about getting that fix than a can of Diet Dr. Pepper.

Time Is NOT Money

Interesting editorial in the NY Times today (that I don't have to pay to see!!) about the obsession with time and not spending it wisely enough. Oddly enough this happened to dovetail perfectly with a chart I was throwing together using my Excel-Fu to visually demonstrate to my poor beleaguered honey that Daylight Savings Time does In Fact, save energy. He's promised to stop complaining about it if I can prove that it does what it claims to do.

Here's the chart:

Yes there are all sorts of caveats you can use to explain why you as an individual might not save more energy under this scheme, but when you're dealing with the averaged effects of hundreds of millions (if not over a billion) people the savings can add up.

And here's a link to the article.