dystill 0.2 released

By · Published · dystill, e-mail, python

Version (do those really matter anymore? :P) 0.2 of dystill has been released.

This version brings a significant change to dystill. Namely, it breaks the unofficial association between dystill and Postfix that has existed since I first wrote it last year. I did this for a couple of reasons:

  • To hopefully increase adoption. Dystill now (really!) stands independent of any MTA. Use it with whatever you want (sendmail, Qmail, etc). You actually always could, but you'd have to ape some Postfix tables. You don't have to do that anymore.

  • To make it easier to write web-based front-ends to dystill's MySQL database, enabling users to add rules.

This was done by adding an "email" column to the filters table, updating that field with the recipient address, and dropping the old userid field. Also, a "maildirpath" config variable was added to the config, specifying where the maildirs live.

There was also a minor bugfix I came across the other day where certain uncommon (but legal) characters could result in unreadable maildirs.


Do Version Numbers Matter?

By · Published · linux, ramblings

The recent announcement by Linus Torvalds that the next release of Linux will be 3.0 has provoked rather furious discussion around the Internets about whether or not the incrementing of the version number is warranted. Linus himself has said that "absolutely nothing" has changed. "It will get released close enough to the 20-year mark, which is excuse enough for me, although honestly, the real reason is just that I can no longer comfortably count as high as 40."

This got me to thinking about the nature of version numbers. Once upon a time (when versions were driven more by engineers and convention, and less by marketing), a version number meant something. Major, minor, revision. A major new release that modified significant portions of the code from the previous release incremented a major version number. Version numbers less than 0 were beta releases.

Linux has been at 2.x since 1996, and at 2.6.x since 2003. Mac OS has been at 10.x since 2001 (even though the current version of OS X is significantly different from the original release in 2001).

Meanwhile, Google Chrome has blasted through major 11 "versions" in three years. Mozilla is planning to release versions 5, 6, and 7 of Firefox this year. You can't tell me that they are going to change major parts of Firefox three times this year. In this case, version numbers are purely being driven by marketing. They need to "catch up" to Chrome and Internet Explorer.

But we live in a different world now. One where, arguably, version numbers are becoming less and less important. The growth of "app stores," I think, is desensitizing your average user to a version number. While apps in the app store still have versions, I couldn't tell you what "version" any of the apps on my iPhone are (other than the OS), and I bet you can't either. Any of the apps I've installed from the Mac App Store I could not tell you the version of them. I just know that, when I see the number on the icon, I know I need to do updates. The updates happen, and I get a new version with whatever new features are there (or, in the case of the Twitter app, whatever features have been removed).

Then there are web apps which are versionless. What version of Gmail do you use? You don't. You use Gmail. Sure, there's probably a revision number or something in the background, but the user has no clue what version they're using. And they don't need to, because there's no action they need to take.

So version are numbered in a wide variety of ways depending on the product and overall seem to be becoming less important as the growth of broadband, "app stores," web apps, and automatic updates make thinking about version numbers less important. So why does it matter if Linus ups Linux to 3.0? Ultimately, it's just a number.


MySQL mathematical operations and NULL values

By · Published · mysql

So I came across an interesting quirk in MySQL the other day. Let's say you have a table schema and some values that look like this:

+-------------------+------------------+------+-----+---------+-------+
| Field             | Type             | Null | Key | Default | Extra |
+-------------------+------------------+------+-----+---------+-------+
| page_id           | varchar(30)      | YES  |     | NULL    |       |
| clicks            | int(10) unsigned | YES  |     | NULL    |       |
+-------------------+------------------+------+-----+---------+-------+

+---------+--------+
| page_id | clicks |
+---------+--------+
|       1 | NULL   |
+---------+--------+

And then let's say you pass the following SQL statement to MySQL:

update page_click_count set clicks = clicks + 1 where page_id=1;

If you come from a loosely-typed language such as PHP, you would probably expect clicks for page_id 1 to now be 1. But that's not the case in MySQL. After the query is run, the table will still look like this:

+---------+--------+
| page_id | clicks |
+---------+--------+
|       1 | NULL   |
+---------+--------+

Not only does the query fail, but it fails with no warnings given. It appears that mathematical operations on null values silently fail.

There are a couple of ways around this. The first and most obvious is to set NOT NULL and a default value on the column. In the example above, this would work. The NULL value in that field becomes a 0 and you can to normal mathematical operations on it. But what happens if, for whatever reason, you can't do that? We actually have this situation in a few places at dealnews, where NULL represents a distinct value of that field that is different from 0. In this case, you can use COALESCE() to fill in the appropriate value for the field.

update page_click_count set clicks = coalesce(clicks, 0) + 1 where page_id=1;

Edit: Brian Moon informs me that this is actually part of the SQL specification. So hooray for specifications. Still, it's kind of arcane; in working with MySQL (and PHP) for a decade now, this is the first time I've ever actually encountered this. Hopefully this helps someone who was as confused as I was.


Interview Questions for Programmers

By · Published · php, ramblings, business

Over the years, I've seen a number of blog posts relating to common questions that should be asked of programmers. Obviously, this is going to depend on exactly what position you are hiring for, but there are some good "gateway" questions that can be used to determine whether or not an applicant you are interviewing can ... well ... even program at all. If they even have the mindset that makes a good developer.

A common one I've seen tossed around is Fizz Buzz. The challenge goes something like this:

Write a program that prints the numbers from 1 to 100. But for multiples of three print “Fizz” instead of the number and for the multiples of five print “Buzz”. For numbers which are multiples of both three and five print “FizzBuzz”.

Now, to anyone who even has a basic understanding of programming, this is super simple to solve using a modulus operator. But apparently many people applying for even entry-level development jobs cannot solve this problem. According to the article linked above, even one "senior developer" took 15 minutes to solve this problem.

Earlier today, a friend posted something on Facebook that inspired what I think it another good, intermediate to difficult level programming question that also looks for pattern recognition. The relevant part of the post began by stating: "This year July has 5 Fridays 5 Saturdays and 5 Sundays." There is the question! It would go something like this:

The month of July 2011 has 5 Fridays, 5 Saturdays and 5 Sundays. Calculate the next 50 times there will be a month that has 5 Fridays, 5 Saturdays and 5 Sundays.

Woah, so how to go about solving this problem? Well, look at a picture of July 2011. Notice something interesting about this month in relation to the question? This month has 31 days (the most any month can have), begins on a Friday and ends on a Sunday. And that's the solution! It's any month with 31 days that begins on a Friday!

With this in mind, it's pretty easy to come up with a PHP solution:

<?php
$count = 0;
$num_found = array();

while(count($num_found) < 50) {
    $count++;
    $ts = strtotime("$count months");

    if(date("t", $ts) == 31 && date("N", strtotime(date("Y-m-01", $ts))) == 5) {
        $num_found[] = date("F Y", $ts);
    }
}

print_r($num_found);
?>

Note that I make use of PHP's strtotime function, because it is the Swiss Army Knife of date manipulation in PHP. This would need to be adapted for use in another language.

So now tell me: what are some other questions you've been asked or asked in an interview?


Xcode 4

By · Published · apple, microsoft, objective-c, ramblings, windows, xcode, mac, osx

So today, out of nowhere, Xcode 4 finally landed as an official release. After seemingly forever in beta, and me quipping more than once about it's similarity to Duke Nukem Forever, Apple finally pulled the trigger and released it. But something changed.

Xcode now has a price. And that has left me, as both a Mac user and a Mac developer, with a lot of questions.

It's either $4.99 if you're not a registered, paid Apple developer, or free if you are a registered, paid Apple developer (with all its $99 per year price tag glory). Supposedly there's some crazy accounting reason that they have to charge for it. This, of course, leaves open the possibility that Xcode will soon be free again once OS X 10.7 arrives. But, it also leaves open the possibility that Xcode will no longer be distributed with OS X and will always have a price tag. It may not even stay $4.99. It may be $49.99 or $499.99.

There are additional questions, too. Does this mean that Apple is still distributing Xcode as a bundle with GNU GCC? Because there are things (such as MacPorts) that rely on the underlying foundation provided by the developer bundle that don't actually use Xcode. Before, those were completely free. Now, they cost $4.99 unless they have split the underlying compiler from the IDE. And if they are still distributing it with GCC, that leads to all kinds of crazy interesting licensing questions.

But I think the worst part is that there is now a barrier to entry, however low, to being a developer on a platform that is already a minority in market share. I can't understand how Apple potentially believes that it is good and right to trade short term profits for long term growth in the number of potential developers. For the future of the Mac platform, I sure hope this isn't their line of reasoning.

So, let me tell you a little story.

My first dabbling in programming came courtesy of QuickBASIC back in the MS-DOS and Windows 3.1 days. This was the late 80s or early 90s, so I would have been 10 or 11 at the time. I stumbled across the Qbasic environment included with MS-DOS by accident and found Nibbles. And, after playing it, I discovered that I could change things by making changes to the strange text presented on the screen. I could change colors and speeds. But it would be a couple of years before I really understood what I was doing.

When Windows 95 came out (and along with it, Visual Basic 4), I talked my parents into getting me a copy. I don't remember how much it cost but it was probably a lot because it was one of the few Christmas presents I got that year. But boy did I run with it. I've periodically felt guilty over that expense because I didn't actually make anything really useful with it, but it was instrumental in furthering my education. Now I could do things on my computer far beyond what poor ol' Qbasic was capable of. So I wrote lots of silly little programs. I put together a "family newsletter" one year that was installed and ran as a piece of software. I was pretty proud of that. I even wrote some software for my high school as part of a software development and AP Computer Science courses.

Eventually, I would move on to other things. Other versions of Visual Basic, Java, C, a brief foray into LISP and Forth-based languages for programming MUDs, and eventually web programming. First in Perl, then in PHP. I even landed my first paying programming job while still in high school, writing applications for a local transit contractor. At first, these were Visual Basic applications. But by the time I left (August of 2000) everything was going to the web and so were we.

But I can trace everything - my entire career, and my consuming passion for software engineering - back to Qbasic and Nibbles. A silly little game about a block snake, and a free development environment included with the operating system. Had I not stumbled on Qbasic and Nibbles, there's a chance I would never have been a developer.

This is not about $4.99. I spend more on coffee in a week than that. My worry is about that 11 year old kid out there somewhere who may never get the opportunity to stumble across Xcode or the sample applications in /Developer and realize the raw power they possess. This is an area where Apple, a company with billions in cash on hand, should be happy to show a loss. It would be to the benefit of their platform, both now and in the future.

One of the great benefits of the Mac platform has been it's low barriers of entry to developers. Sure, one could argue that the hardware is more expensive (and I could counter-argue that, for the quality of the equipment you are getting a bargain), but the development tools have always been freely available online and included with the machine. You could dabble in programming to your heart's content. Sure, if you want to put something in the app store(s), you had to pay for admission, but there was nothing stopping you from getting all the way to that point, or even distributing your creations on your own.

But this new trend of charging for the development tools - even if it is a paltry sum - sends, to me, a worrying signal about the course Apple intends to tread. They've now moved the gate from the last step to the first step. It's a course that Microsoft, as above, once tread.

Microsoft? They now give away a version of Visual Studio for free.


BASH Quickie: Backing Up MySQL Databases

By · Published · linux, mysql, bash

In some ways, after years of doing programming and scripting, I'm now sort of rediscovering the power of the shell.

Tonight, I was working on my server and remembered that I needed to start backing up my MySQL databases (which you do also ... right?). So instead of writing a script to do that, with a little research, I was able to come up with a way to:

  1. Dump each database to a separate SQL file, with a timestamp.

  2. bzip the file.

  3. Keep 5 days worth of backups for each database, rotating the oldest backup off.

Here's what I came up with:

cd /backup/mysql; 
for i in $(mysql -BNe 'show databases' -u root -p<password>); do 
    mysqldump -u root -p<password> $i | bzip2 > $i-`date +"%Y%m%d"`.sql.bz2; 
    rm -rf $i-`date -d "-5 day" +"%Y%m%d"`.sql.bz2; 
done > /dev/null 2>&1

Shoved that in my crontab. Works great. Linux rocks.


Automatically Setting Adium IM Status with AppleScript

By · Published · apple, applescript, mac, osx

I have more than 20 various IM accounts set up in Adium on my Macintosh. But during the day, the only one I really want to be active is the one I use for work. The remainder, I want to leave logged in, but showing as away with a warning not to bother me unless it is important. But half the time I forget to set all those accounts as away, or I forget to set the work one as available, or some other issue that would arise out of a manual process interferes and too often it doesn't get done.

Enter AppleScript. I whipped up a surprisingly easy AppleScript to do just this:

tell application "Adium"
    go away with message "Working. Please do not disturb unless necessary."
    go available first account
end tell

Because the work account is the first one, this makes it easy. It just sets all accounts as away and then sets the work one available. I shove this in my MarcoPolo ruleset to fire when I arrive at the office.

The script to reverse the change when I leave is even easier. This is fired when I leave the office:

tell application "Adium" to go available

Automatically Provisioning Polycom Phones

By · Published · apache, asterisk, linux, networking, php, rewrite

The goal of this project were twofold:

  1. To completely eliminate the need for me to touch the phone to provision it. I want to be able to create a profile for it in the database, then simply plug the phone in and let it do the rest. And...

  2. To eliminate per-phone physical configuration files stored on the server. The configuration files should be generated on the fly when the phone requests them.

So the flow of what happens is this:

  1. I create a profile for the phone in the database, then plug the phone in.

  2. Phone boots initially, receives server from DHCP option 66. Script on the server hands out the correct provisioning path for that model of phone. Reboots with new provisioning information.

  3. Phone boots with new provisioning information, begins downloading update SIP application and BootROM. Reboots.

  4. Phone boots again, connects to Asterisk.

At this rate, provisioning a phone for a new employee is simply me entering the new extension and MAC address into an admin screen, and giving them the phone. It's pretty neat.

*Note: *there are some areas where this is intentionally vague, as I've tried to avoid revealing too much about our private corporate administrative structure. If something here doesn't make sense or you're curious, post a comment. I'll answer as best I can.

Creating the initial configs

I used the standard download of firmware and configs from Polycom to seed a base directory. This directory, on my server, is /www/asterisk/prov/polycom_ipXXX, where XXX in the phone model. Right now we deploy the IP-330, IP-331 and IP-4000. While right now the IP-330 and IP-331 can use the same firmware and configs, since the IP-330 has been discontinued they will probably diverge sometime in the not too near future.

With the base configs in place, this is where mod_rewrite comes into play. I added the following rewrite rules to the Apache configs:

RewriteEngine on
RewriteRule ^/000000000000\.cfg /index.php
RewriteRule /prov/[^/]+/([^/]+)-phone\.cfg /provision.php?mac=$1 [L]
RewriteRule /prov/polycom_[^/]+/[^/]+-directory\.xml /prov/polycom_directory.php`

RewriteCond %{THE_REQUEST} ^PUT*
RewriteRule /prov/[^/]+/([^/]+)\.log /prov/polycom_log.php?file=$1`

To understand what these do, you will need to take apart the anatomy of a Polycom boot request. It requests the following files in this order: whichever bootrom.ld image it's using, [mac-address].cfg if it exists or 000000000000.cfg otherwise, the sip.ld image, [mac-address]-phone.cfg, [mac-address]-web.cfg, and [mac-address]-directory.xml. So, we're going to rewrite some of these requests to our scripts instead.

Generating configs on the fly

We're going to skip the first rewrite rule (we'll talk about that one in a little bit since it has to do with plug-in auto provisioning). The one we're concerned with is the next one, which rewrites [mac-address]-phone.cfg requests to our provisioning script. So each request to that file is actually rewritten to provision.php?mac=[mac-address].

Now, in the database, we're keeping track of what kind of phone it is (an IP-330, IP-331 or IP-4000), so when a request hits the script, we look up in the database what kind of phone we're dealing with based on the MAC address, and use the variables from the database to fill in a template file containing exactly what that phone needs to configure itself.

For example, the base template file for the IP-330 looks something like this:

<sip>
<userinfo>
<server
<?php foreach($phone as $key => $p) { ?>
voIpProt.server.<?php echo $key+1 ?>.address="<?php echo $p["host"] ?>"
voIpProt.server.<?php echo $key+1 ?>.expires="3600"
voIpProt.server.<?php echo $key+1 ?>.transport="UDPOnly"
<?php } ?>
/>

<reg
<?php foreach($phone as $key => $p) { ?>
reg.<?php echo $key+1 ?>.displayName="<?php echo $p["first_name"] ?> <?php echo $p["last_name"] ?>"
reg.<?php echo $key+1 ?>.address="<?php echo $p["name"] ?>"
reg.<?php echo $key+1 ?>.type="private"
reg.<?php echo $key+1 ?>.auth.password="<?php echo $p["secret"] ?>"
reg.<?php echo $key+1 ?>.auth.userId="<?php echo $p["name"] ?>"
reg.<?php echo $key+1 ?>.label="<?php echo $p["first_name"] ?> <?php echo $p["last_name"] ?>"
reg.<?php echo $key+1 ?>.server.1.register="1"
reg.<?php echo $key+1 ?>.server.1.address="<?php echo $p["host"] ?>"
reg.<?php echo $key+1 ?>.server.1.port="5060"
reg.<?php echo $key+1 ?>.server.1.expires="3600"
reg.<?php echo $key+1 ?>.server.1.transport="UDPOnly"
<?php } ?>
/>
</userinfo>
<tcpIpApp>
<sntp
tcpIpApp.sntp.address="pool.ntp.org"
tcpIpApp.sntp.gmtOffset="<?php echo $tz ?>" />
</tcpIpApp>
</sip>

The script outputs this when the phone requests it. Voila. Magic configuration from the database.

There's a little bit more to it than this. A lot of the settings custom to the company and shared among the various phones are in a master dealnews.cfg file, and included with each phone (it was added to the 000000000000.cfg file).

Now, on to the next rule.

Generating the company directory

Polycom phones support directories. There's a way to get this to work with LDAP, but I haven't tackled that yet. So, for now, we generate those dynamically as well when the phone requests any of its *-directory.xml files.

This one's pretty easy since 1) we don't allow the endpoints to customize their directories (yet), and 2) because every phone has the same directory. So all of those requests go to a script that outputs the XML structure for the directory:

<directory>
<item_list>
<?php
if(!empty($extensions)) {
    foreach($extensions as $key => $ext) { ?>
        <item>
            <fn><?php echo $ext["first_name"]?></fn>
            <ln><?php echo $ext["last_name"]?></ln>
            <ct><?php echo $ext["mailbox"]?></ct>
        </item>
    <?php } ?>
<? } ?>
</item_list>
</directory>

We do this for both the 000000000000-directory.xml and the [mac-address]-directory.xml file because one is requested at initial boot (the 000000000000-directory.xml file is intended to be a "seed" directory), whereas subsequent requests are for the MAC address specific file.

Getting the log files

Polycoms log, and occasionally the logs are useful for debug purposes. The phones, by default, will try to upload these logs (using PUT requests if you're provisioning via HTTP like we are). But having the phone fill up a directory full of logs is ungainly. Wouldn't it be better to parse that into the database, where it can be easily queried? And because the log files have standardized names ([mac-address]-boot/app/flash.log), we know what phone they came from.Well, that's what the last two rewrite lines do. We rewrite those PUT requests to a PHP script and parse the data off stdin, adding it to the database.

A little warning about this. Even at low settings Polycom phones are chatty with their logs. You may want to have some kind of cleaning script to remove log entries over X days old.

Passing the initial config via DHCP

At this point, we have a working magic configuration. Phones, once configured, fetch dynamically-generated configuration files that are guaranteed to be as up-to-date as possible. Their directories are generated out of the same database, and log files are added back to the same database. It all works well!

... except that it still requires me to touch the phone. I'm still required to punch into the keypad the provisioning directory to get it going. That sucks. But there's a way around that too!

By default, Polycom phones out of the box look for a provisioning server on DHCP option 66. If they don't find this, they will proceed to boot the default profile thats ships with the phone. It's worth noting that, if you don't pass it in the form of a fully-qualified URL, it will default to TFTP. But you can pass any format you can add to the phone.

if substring(hardware, 1, 3) = 00:04:f2 {
    option tftp-server-name "http://server.com";
}

In this case, what we've done is look for a MAC address in Polycom's space (00:04:f2) and pass it option 66 with our boot server. But, we're passing the same thing no matter what kind of phone it is! How can we tell them apart, especially since, at this point, we don't know the MAC address.

The first rewrite rule handles part of this for us. When the phone receives the server from option 66 and requests 000000000000.cfg from the root directory, we instead forward it on to our index.php file, which handles the initial configuration. Our script looks at the HTTPUSERAGENT, which tells us what kind of phone we're dealing with (they'll contain strings such as "SPIP330", "SPIP331" or "SSIP_4000"). Using that, we selectively give it an initial configuration that tells it the RIGHT place to look.

<?php
ob_start();

if(stristr($_SERVER['HTTP_USER_AGENT'], "SPIP_330")) {
include "devices/polycom_ip330_initial.php";
}

if(stristr($_SERVER['HTTP_USER_AGENT'], "SPIP_331")) {
include "devices/polycom_ip331_initial.php";
}

if(stristr($_SERVER['HTTP_USER_AGENT'], "SSIP_4000")) {
include "devices/polycom_ip4000_initial.php";
}

$contents = ob_get_contents();
ob_end_clean();

echo $contents;
?>

These files all contain a variation of my previous auto-provisioning configuration config, which tells it the proper directory to look in for phone-specific configuration.

Now, all you do is plug the phone in, and everything else just happens. A phone admin's dream.

Keeping things up to date

By default, the phones won't check to see if there's new config or updated firmware until you tell them to. But his also means that some things, especially directory changes, won't get picked up with any regularity. A quick change to the configs makes it possible to schedule the phones to look for changes at a certain time:

<provisioning prov.polling.enabled="1" prov.polling.mode="abs" prov.polling.period="86400" prov.polling.time="01:00" />

This causes the phones to look for new configs at 1AM each morning and do whatever they have to with them.

Conclusions

The reason all this is possible is because Polycom's files are 1) easily manipulatable XML, as opposed to the binary configurations used by other manufacturers, and 2) distributed, so that you only need to actually send what you need set, and the phone can get the rest from the defaults. In practice this all works very well, and cut the time it used to take me to configure a phone from 5-10 minutes to about 30 seconds. Basically, as long as it takes me to get the phone off the shelf and punch the MAC address into the admin GUI I wrote.

I don't even need to take it out of the box!


Google Chrome, Mac OS X and Self-Signed SSL Certificates

By · Published · apache, apple, linux, networking, security, mac, osx

I've been using Google Chrome as my primary browser for the last few months. Sorry, Firefox, but with all the stuff I need to work installed, you're so slow as to be unusable. Up to and including having to force-quit at the end of the day. Chrome starts and stops quickly But that's not the purpose of this entry. The purpose is how to live with self-signed SSL certificates and Google Chrome.

Let's say you have a server with a self-signed HTTP SSL certificate. Every time you hit a page, you get a nasty error message. You ignore it once and it's fine for that browsing session. But when you restart, it's back. Unlike Firefox, there's no easy way to say "yes, I know what I'm doing, ignore this." This is an oversight I wish Chromium would correct, but until they do, we have to hack our way around it.

Caveat: these instructions are written for Mac OS X. PC instructions will be slightly different at PCs don't have a keychain, and Google Chrome (unlike Firefox) uses the system keychain.

So here's how to get Google Chrome to play nicely with your self-signed SSL certificate:

  1. On your web server, copy the crt file (in my case, server.crt) over to your Macintosh. I scp'd it to my Desktop for ease of work.
    ** These directions has been updated. Thanks to Josh below for pointing out a slightly easier way.**

  2. In the address bar, click the little lock with the X. This will bring up a small information screen. Click the button that says "Certificate Information."

  3. Click and drag the image to your desktop. It looks like a little certificate.

  4. Double-click it. This will bring up the Keychain Access utility. Enter your password to unlock it.

  5. Be sure you add the certificate to the System keychain, not the login keychain. Click "Always Trust," even though this doesn't seem to do anything.

  6. After it has been added, double-click it. You may have to authenticate again.

  7. Expand the "Trust" section.

  8. "When using this certificate," set to "Always Trust"

That's it! Close Keychain Access and restart Chrome, and your self-signed certificate should be recognized now by the browser.

This is one thing I hope Google/Chromium fixes soon as it should not be this difficult. Self-signed SSL certificates are used *a lot *in the business world, and there should be an easier way for someone who knows what they are doing to be able to ignore this error than copying certificates around and manually adding them to the system keychain.