Checksum Arcanius

Need I say More?

I rest my case.

Update: Of course, thats only if it isn’t enabled. I’m actually quite happy with the antispam features on WordPress 1.5 these days.

Beth’s Birthday was yesterday, and the party, beginning with something that looked like it would be kinda lame, turned into somethat that was quite cool. Instead of barhopping, which I had decided not to participate in, the party ended up at Katie’s place, where we enjoyed some wonderful chocolate cake, a game of Apples to Apples (where I got kicked out for cheating, even though it was I that caught myself…), and some interesting questions getting to know everyone a little better. So Beth now has a car, a little bit of money for gas (thanks to yours truly), and has joined the ranks of the 22-year-olds. Welcome!

Of course, I stayed at the party until about 3:00, which, since I was already sleep deprived, meant that today was… interesting, let me say.

Scott is back; hiked Tiger Mt; only one small bird at top.

(just in case I posed under this title before)

Its almost 5:00am and I’m still up. What is wrong with me? Aughhhhhhu

…and I even took the time to categorize this post!

Fellow Class of 2001 IS alum Courtney was back in town after a week in Bahamas for the last Sprng Break of her undergraduate career. We decided to hang ut and catch up, and it turned out to be fun and strangely enlightening exchanging news and stories about people we jointly knew. After a ordering out some wonderfully American food from a local french fine dining resturant, I watched the Sonics beat the Magic with Craig before settling in to watch the most recent Terminator. Good times.

I have never entirely shared Erik’s passion to provide internet filter bypassing services to the masses, but it is something that is important to him, and I am one who strongly believes that, in general, more information is good. On the other hand, I can understand the district’s filtering for, say, pornography or pirating sites, or anything else that many taxpayers (who support the schools and pay for things like the internet to the school) would generally object to funding access to.

But why, for example, are email sites filtered? In this digital age, filtering email is like breaking a tennis player’s legs and expecthing her to win. Email is vital, especially to clubs like the TRC where it is the primary form of communication. Blocking email at school is a good way of ensuring that team leaders are kept out of the loop until they get home, not the most ideal of situations. Nevertheless, the long and short of it is that while I don’t share Erik’s passion, I don’t mind it, and I certianly don’t mind spreading the word the the wonderful Bellevue School District has suspended Erik’s student account while investigating his site, FreedomDown.net.

I suggest that you go read about it at Erik’s blog, “Unknown Rebel.“

I just learned how to use the SQL ‘CASE’ statement, and my respect for Relational Databases and SQL just about tripled.

At issue was the TRC scouting program – I had implemented a system to extract all sorts of information from the data that the scouts had input, including average scoring per game. It was especially nice that we could sort based on any category. Then I added average score of the alliances per game, but I did not know how to fold that into the same SQL statement, because team colors switched between red and blue between matches. There was no single column to sum or average. So what was I to do? The solution I came up with worked well enough to win the PNW regional at least – it was to write sepearte select statements to extract the alliances’ average scores in the following manner.

First I would get the initial data dump (I know this is insecure; you can be quiet and sit down now, thank you):

$sql = "SELECT `teams`.`team_number`, `team_name`,
`carries_initial_tetra`, `teams`.`notes`,
COUNT(*) AS 'matches',
MAX(`caps`) AS 'max_caps',
MIN(`caps`) AS 'min_caps',
AVG(`caps`) AS 'avg_caps',
AVG(`contains`) AS 'avg_cont',
MAX(`auto_points`) AS 'max_auto',
MIN(`auto_points`) AS 'min_auto',
AVG(`auto_points`) AS 'avg_auto',
(AVG(`auto_points`) + AVG(`contains`) + 3 * AVG(`caps`)) AS 'avg_scoring'
FROM `teams`, `team_match`
WHERE `teams`.`team_number` = `team_match`.`team_number`
GROUP BY `team_number` ORDER BY $orderby";

Then for each alliance score I ran another SQL query (this code was more or less repeated four times):

$points = 0;
$sql = "SELECT sum(`red_score`) AS 'red_points'
FROM `matches`, `team_match`
WHERE `matches`.`match_number` = `team_match`.`match_number`
AND `alliance_color` = 'red'
AND `team_number` = '{$row['team_number']}'";
$result2 = mysql_query($sql);
if(!$result2) print mysql_errno() . ': ' . mysql_error() . "n";
else $row2 = mysql_fetch_assoc($result2);
$points += $row2['red_points'];
mysql_free_result($result2);


While this worked, it didn’t allow sorting based on the average score column, which was unfortunate but didn’t end up mattering. But the Championship (aka “Nationals”) is a whole diofferent ball game. We’ll need better analysis tools. And I had heard about this ‘CASE’ statement, and thought it might be able to help me out. So today, I learned myself how to use it. And it is truly extraordinary how much simpler it is. Check it out:


$sql = "SELECT `teams`.`team_number`, `team_name`,
`carries_initial_tetra`, `teams`.`notes`,
COUNT(*) AS 'matches',
MAX(`caps`) AS 'max_caps',
MIN(`caps`) AS 'min_caps',
AVG(`caps`) AS 'avg_caps',
AVG(`contains`) AS 'avg_cont',
MAX(`auto_points`) AS 'max_auto',
MIN(`auto_points`) AS 'min_auto',
AVG(`auto_points`) AS 'avg_auto',
(AVG(`auto_points`) + AVG(`contains`) + 3 * AVG(`caps`)) AS 'avg_scoring',
AVG(CASE WHEN `alliance_color` = 'red' THEN `red_score` ELSE `blue_score` END) AS 'avg_points',
AVG(CASE WHEN `alliance_color` = 'blue' THEN `red_score` ELSE `blue_score` END) AS 'opp_points',
AVG(CASE WHEN `alliance_color` = 'red' THEN `red_score`-`blue_score` ELSE `blue_score`-`red_score` END) AS 'diff_score'
FROM `teams`, `team_match`, `matches`
WHERE `teams`.`team_number` = `team_match`.`team_number`
AND `matches`.`match_number` = `team_match`.`match_number`
GROUP BY `team_number` ORDER BY $orderby";


As you may be able to see, the CASE statement allows me to do a conditional sum. And now I can sort, and yes, the TRC does appear at the top, yes, yes indeed.