Checksum Arcanius

I ran into another dilemma in SQL-land today. Working on a money-management application, I had three tables that I wanted to gather information from. This normally leads to some relatively straightforward SQL:

SELECT account.name, contact.name, date, amount, type
FROM transactions, accounts, contacts
WHERE account_id = accounts.id
AND contact_id = contacts.id;

However, this returned results only for transactions that had both matching accounts and matching contacts, while what I wanted was a single record for each transaction, regardless of matching accounts or contacts. For two tables, this would be straightforward with a LEFT JOIN:

SELECT account.name, date, amount, type
FROM transactions LEFT JOIN accounts
ON account_id = accounts.id;

Which would return a row for every entry in the transactions table even if no matching account was found. I tried extrapolating that two three tables, like this:

SELECT account.name, contact.name, date, amount, type
FROM FROM transactions LEFT JOIN accounts, contacts
WHERE account_id = accounts.id
AND contact_id = contacts.id;

However, this didn’t do what I wanted, and I was getting multiple entries for each transaction. So I googled for help, and Google delivered: Left Joins to link three or more tables. From the information in this article, I was able to develop the SQL code that did exactly what I wanted:

SELECT `accounts`.`name` AS account_name,
`contacts`.`name` AS contact_name,
`date`, `amount`, `type`, `detail`, `memo`, `cleared`
FROM (`transactions` LEFT JOIN `accounts` ON `accounts`.`id` = `account_id`)
LEFT JOIN `contacts` ON `contacts`.`id` = `contact_id`
WHERE `parent_id` = 0

This returns a single row for each entry in transactions, even if matching accounts and contacts are not found. Esentially, it is a nested LEFT JOIN. First, MySQL left joins transactions and accounts, then it takes this result and similarly left joins contacts. This way, every row in the leftmost table (transactions) is preserved. I might have simply linked to the site, but I didn’t like the naming scheme in the examples on that site (crypic names like bdg and dom don’t earn accolades from me), and I came up with a better article title, “Nested”.

Saturday night, I attended Colin’s LAN party. I expected to make only a few hours’ appearance – it had started at noon, and I had things planned for the next day. Then I played Homeworld 2, and I stayed until 6:00 am, when I fell asleep, not to be cognizant again until about 11:30. In Homeworld 2, there is a bit too much going on to really get your head around it all, as you try to control hundreds of fighters, resource gatherers, factory ships, frigates, corvettes, destoryers, and battleships. For an even crazier trip, try out the Warlords modification, which esentially makes the game into a giant Star Wars battlefield and can easily overcome even the most powerful modern computers. Its great fun, and has, of course, rekindled my interest in a much grander scale strategy game idea that I’ve tossed around for sometime.

Like all spam, referer spam takes what could be useful – in this case, information where people come from to a site – and make it mostly useless – just more links for google to index. However, one of the unique aspects of referer spam also makes it easier to counter than many other types of spam. Generally, referer spam must surpass a threshold – usually, the top ten referrers, to be listed at all on a site. This means that instead of spreading out the referrers, referrer spam generally all points to one place. Which makes it easy to implement a simple anti-referer-spam script like the one I came up with to help fight referer spam on Theo’s blog:

// Ryan's anti-spam hack starts here
$spam_words = file('spam_words');
if(isset($_SERVER['HTTP_REFERER']))
{
foreach($spam_words as $spam_word)
{
if(stristr($_SERVER['HTTP_REFERER'],rtrim($spam_word)) !== false)
{
die("You look like you're trying to refer spam this site with this word: $spam_word".
'If not, sorry for the inconvenience and please '.
'<a href="'.$_SERVER['PHP_SELF'].'">click here to continue</a>');
}
}
}
// End Ryan's anti-spam hack

Then, as a companion, a post-facto script that can be called from the command line or from the web:

<?php
require('./conf/_config.php');
mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
mysql_select_db(DB_NAME);
$spam_words = file('spam_words');
foreach($spam_words as $spam_word)
{
// echo $spam_word;
$spam_word = rtrim($spam_word);
$sql = "DELETE FROM `evo_hitlog` WHERE `referingURL` LIKE '%$spam_word%'";
echo $sql . "n";
mysql_query($sql);
}
?>

Then, the admin can simply periodically check their top referer output, and if they see a bad guy creeping up, add an appropriate word to their spam_words file and run the killoldspam.php script. The bad stuff goes away and can never come back. Best of all, its not very intrusive to the average visitor, even when they are accidently flagged. Javascript or a meta or http redirect could make it less annoying still.

Not long ago, I “upgraded” to Trillian 3.0 Basic, since Trillian 2.0 Pro was crashing when I started it up. However, Trillian 3 is awful:

1. It isn’t skinnable. While its default skin in not bad, it wasn’t as good or nearly as compact as my skin of choice, Beacon Pro. Of course, the “Pro” version allows skins, but more on that later.
2. It takes forever to load. Ok, not quite forever, but it does take at least 5 times as long, and perhaps 10 times as long to load. Since Trillian 2.o Pro did everything I wanted it to, the extra time waiting for it to start – disk reading and processor fully utilized the entire time – did not make me happy.
3. It doesn’t take plug-ins. This is probably something that their “Pro” version allows, but all I wanted was HTML profiles – nothing else remotely interested me. I’m not going to shell out another $25 just so I can get one plugin and one skin.
4. Which brings me to the last topic, the one year deal-io. I paid $25. Most places that I do that are nice and give lifetime upgrades. Not Ceurelian Studios. This fact – which to their credit they do not try to hide in any way – almost made me balk at buying the Pro version, but it ended up being a good decision when I did. However, if they want people to buy 3.0 Pro, their shareware offering in 3.0 Basic really doesn’t do the selling job very well.

So last night, I downgraded to Trillian 2.0 – and in the process, upgraded my IM experience. It isn’t crashing anymore, loads quickly, has my favorite skin, and I can view HTML profiles as they were meant to be viewed. Good stuff.

If web standards really are all they are cracked up to be, then why is it that only Internet Explorer does what I expect when I try to adjust the layout of this site? Why will I have to spend frustrated hours reading through examples to get the menu on the left? What am I missing about positioning?

Three recent kernel panics on sf2, the server behind most of silverfir.net, had me concerned. I googled the problem, found that it was linked to Kernel version 2.6.10, which I was running, and so it became clear the an upgrade to Kernel 2.6.11 was in order. Being the master that I sometimes am, I performed the upgrade nearly flawlessly, and sf2 is now running with the new kernel. Let me know if you notice anything weird. Assuming that no problems are seen, I will make the new kernel permanent (and probably delete the old 2.6 kernel as well).

One of the great things about some software packages is the immense amount of user interface and even behavioral customization one can perform on them. While not as desireable as a program that works exactly how you want it to out of the box, customization is really the only way to go when a more complex software package becomes sophisticated enough to be used for a variety of purposes. It is the only way to possibly please everyone.

I have been very impressed with the customization available in some programs recently. Just now, getting Microsoft Outlook to display messages exactly how I wanted them took a little bit of searching, but all of the customizations I wanted were available, and I am quite happy with their layout now.

Another program that has consistently impressed me with customize-ability is gVim, the graphical version of Vim for Windows. I really need to make an entire write-up on this one because I have been working at getting its layout more to my liking for several months now, making slow but very real progress. Since I use gVim extensively both at home and at work, I probably have some reconcilliation to do to make my editing experience more seamless. All that gVim needs, in my opinion, to be the perfect editor, is slightly better integration with the Windows Paradigm (ie, better knowledge of things like the user’s home directory), and real support for tabbed editing. However, even without these features, gVim is still so much better for me than the next best I’ve tried (Programmer’s Notepad and Notepad++) that it remains my preferred editor for almost all situations. Think macros, and no other editor stands a chance to Vim. And I’m certain that I’ve only scratched the surface.

Finally, the mother of all customizable music applications has been my preferred computer music player for at least a year now – FooBar2000. Check it out.