Checksum Arcanius

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.

Team 492, the Titan Robotics Club, allied with teams 1595 and 604 to go undefeated throughout Saturday’s elimination matches at the FIRST Robotics Competition’s Pacific Northwest Regional. There was one tie, when our robot mysteriously stopped working – props to our alliance for carrying the weight on that one. Nevertheless, the TRC and our alliance partners claimed the win in dominating fashion. Now we have shipped the robot off to Atlanta, Georgia for the wordwide championship event. But first, there is the small obstacle of fundraising for the $5000 entry fee to that event. So if you know anyone that is looking to get their name attached to the hottest thing from the Northwest since Microsoft went public, let them know that the Titan Robotics Club would be glad to take on their name for some sponsorship.

After a day of competition, the TRC remains the only undefeated team at the Pacific Northwest Regional. We’ve had a few close calls, winning one game by 1 point and another by 3 points, but we’ve always managed to hang on and get the all-important W. While there is no web cast of the even this year, you can watch other similar regionals by going to NASA’s Robotics Website. Or if following team 492 is what you want to do, you can watch the match results and team rankings by following the links. We have three more qualification matches tomorrow, and anything can happen, so wish us luck!

For the first time since getting the cable modem installed, my IP address changed. The result was an extremely unreachable host.
With me in Portland with the Titan Robotics Club for the FIRST Robotics Competition, figuring out what exactly the problem was, then fixing it, was not a trvial task. Fortunately for me and for anyone else using SilverFir.net, my wonderful mother was local to the server and after restarting the server to no avail, she was able to confirm that it was indeed the IP address that had changed. A quick trip over to No-IP.com and I had the DNS switched over (changes go active worldwide in under 5 minutes!), but then there seemed to be a few latent problems. Apache didn’t start properly, leading me to expect that the server was still down. But then later, when I impulsively checked my email, and found that my mail server was working just find, I thought, “Wait a moment now…” An SSH session proved fruitful as well, and a quick reboot saw SilverFir.net back to full potential in under a minute. Just Lovely.

Steve Kelly writes a good article on the subject for the Seattle Times. How anybody could still like this guy is a mystery to me. He lies as easily as he speaks, and he looks out only for #1. This should be a warning to any institution that for whatever reason might consider hiring him. Soiling and destroying college football programs seems to be his M.O. – and he is very effective. I wouldn’t expect him to be any less skilled with a pro program either. At least the 49ers got it. Too bad Hedges didn’t.

What happened to the days when Washington coaches said they loved the team, and meant it; When fired, they left the program alone because that was what was best for the program instead of turning around and suing it because there are some deep pockets there and your million dollar sallary wasn’t enough. What happened to the days of Jim Lambright? Oh yeah… Barbara Hedges… *Sigh*

I recently “upgraded” my desktop computer to a development environment, specifically for the task of working on the TRC scouting app, which was a fairly large project. The hardware did not change for this upgrade (although I have recently added hardware, but that is another story). Instead the main additions were gVim, SmartFTP, and some Firefox extensions. While I normally do most of my coding projects on my laptop (my “productivity” machine), I was finding the screen resolution and touch pad too confining and was literally feeling stifled by the system for the size of the project I wanted to take on. So I put a two-monitor, 4-disk RAID, Gigabyte Dual Channel PC3200 DDR RAM, P4E 2.8GHz HT with 1MB Cache on the job. And it did the job well.

But, I began to become concerned about the differences in extensions between my three main installations of Firefox – on my laptop, at work, and on my desktop. So, to help rectify things, I thought I would share here what extensions I have installed on this, my desktop machine from which I publish tonight. Then, I can post comments on what I have on my other machines, and bring them to some happy middle place, so I have more or less the same base functionality, with whatever additional functionality might be useful on each computer. As an added bonus, you too can comment on what extensions you use, and why. Lets call the effect “Synergy…” or something like that.
Read the rest of this entry »