I do have some technical gripes with MySQL, though:

• InnoDB doesn’t support fulltext indexes, so I’ve forced to give up referential integrity for any table that needs fulltext searching.
• The mysql(1) command-line tool doesn’t allow setting user variables on the command line when invoking a script.

The first is too big a problem for me to take on right now, but the second looked like it just needed a bit of perl magic, so I wrote a wrapper script:

runmysql

This script scans each command-line option for a string beginning with “@”, and pulls it out of the arguments before passing the rest on to mysql. Any option in the format @name=value will be converted to a SQL variable declaration and passed to mysql; any other option gets passed through unmodified.

Example

Let’s say that you have a SQL script named getstuff.sql:

use mydatabase;

select * from Stuff where type=@type;

You can invoke this script like this:

runmysql @type=foo -X < getstuff.sql

The script will run the command “mysql -X” (the “-X” means XML output — it’s just there to demonstrate how other options get passed through), and prepend a declaration to the SQL script, so that MySQL actually sees this:

set @type = 'foo';
use mydatabase;

select * from Stuff where type=@type;

That’s it, really. Of course, it’s not that useful in a trivial script like this, but for scripts that load data from many different files (for example), it makes life much simpler.

Similar entities

Imagine that you want to deal with a set of geographical entities, similar to what I handle in OurAirports:

• cities
• airports
• navigation transmitters
• points of interest (e.g. buildings, etc.)

The normal object-oriented approach would be to create a base class such as “Location” with all the properties these have in common (unique id, latitude, longitude, elevation, name, description, etc.) and then create subclasses adding additional information for each one (such as frequencies for the navigation transmitters, or identifiers for the airport). The normal naive SQL approach, on the other hand, is to repeat the information in four separate tables.

The base table

SQL can, however, handle polymorphism in a fairly elegant way — I think database people call these “disjoint subtypes”, but feel free to ignore that term. Instead of a base class, we create a base table (I’m using the MySQL dialect, but most SQLs should have similar capabilities):

create table Locations (
  id bigint primary key auto_increment,
  type enum ('airport', 'city', 'poi', 'navaid') not null,
  latitude_deg double not null,
  longitude_deg double not null,
  elevation_m int not null,
  title varchar(128) not null,
  description text not null,
  # add indices as needed
);

(A purer implementation would use a separate LocationTypes table for flexibility, rather than the enum value — I’ll probably do that in OurAirports, but it would make these examples a bit more verbose.)

The subtype tables

Next, create a table for each of your subtypes, where the primary key is also a foreign key pointing to the Locations table:

create table Airports (
  id bigint primary key,
  iati_code char(3),
  icao_code char(4),
  local_code varchar(4),
  # etc.
  foreign key id references Locations(id)
);

Using the same id for the base and derived table ensures that each entity has an id that is unique across all subtables, and simplifies SQL statements for accessing and modifying the tables.

Polymorphism

Now, when you want to deal with locations in general, regardless of subtype, simply query the Locations table:

select L.* from Locations L where latitude_deg > 60;

You can update base information without even knowing the subtype:

update Locations set latitude_deg=45.5 where id=111;

When you want to deal with a specific kind of location, do a simple join:

select L.*, A.* from Locations L 
join Airports A on L.id=A.id
where A.latitude_deg > 60;

Views

To make this even easier, define a view containing the join:

drop view if exists AirportsView;
create view AirportsView as
select * from Locations L 
join Airports A on L.id=A.id;

Now you can simply query AirportsView as if it were a single table:

select * from AirportsView where latitude_deg>60;

Inserting and deleting

Inserting is slightly more complex, because it involves two steps: first, create the base entry, then link to it from the derived entry:

insert into Locations 
 (latitude_deg, longitude_deg, elevation_m, name) 
values
 (45.5, -75.5, 100, 'Sample Airport');

insert into Airports (id, iata_code) values
(last_insert_id(), 'AAA');

Stored procedures and/or triggers can automate this fairly nicely, if you don’t want to have to embed too much database logic in application code. Deletion similarly requires two steps:

delete from Airports where id=111;

delete from Locations where id=111;

A simple post-deletion trigger for Airports, though, could automate this and avoid the risk of mistakes in source code.

Eating the dogfood

Unfortunately, I didn’t design OurAirports this way from the start, so I have a bit of refactoring to do. When I’m finished, however, I expect mapping to be much simpler, since I will no longer have to execute 5 or 6 separate queries to find all the icons to display on a map. If anyone has any better ideas, please let me know; otherwise, stay tuned.

Frankly, the majority of customer problem descriptions I’ve seen and heard — including those I’ve been involved in — have been either poorly thought out or complete B.S. I know I won’t always be allowed to use this, but as a consultant and as a poor fool who still dreams of his own startup, I’ve written a checklist of six criteria that a real customer problem must meet:

1. It uses the customer’s language, not yours — if any words or ideas need to be defined or explained, then it’s probably not really a customer problem.
2. It describes a business need — it should not mention the proposed technical solution (such as a social network, CMS, etc.).
3. It is recognizable — the customer is already familiar with the problem or with a very similar one, and doesn’t need to be convinced that the problem exists (though she might not be aware of its severity).
4. It is quantifiable — even if you can’t assign a number yet, it is the kind of problem that has a cost expressed in concrete units such as money, time, subscriptions, support calls, page views, etc.
5. It is compelling — you can demonstrate potential benefits, savings, etc. that justify the time, cost, and effort for the customer to try to solve it.
6. It is succinct — you can describe summarize the problem meaningfully in a single, short sentence (of course, you’re always free to elaborate it somewhere else).

The passing grade on this checklist is 100%. If only one checklist item is missing, the problem is likely just wishful thinking — if your product or project succeeds, it will be despite your idea of the problem, not because of it.

Note: It’s actually easy to come up with problem descriptions that meet all six criteria; what’s hard is coming up with problem descriptions that meet all six criteria and have credible technical solutions.

Installing the printer in Windows Vista

Installing the printer in Windows Vista wasn’t too difficult. I followed the instruction not to plug in the USB cable until asked, then inserted the supplied CD-ROM and authorized Vista to run the setup.exe program. I had a click through a few screens, then I plugged in the the USB cable, let it autodetect the printer, and left it running over supper. The whole process took less than 15 minutes. When I came back in, it was finished, and I just had to dodge the ads attached to the end of the installation program. I think my non-computer-literate older relatives could have managed fine without any help from me.

Installing the printer in Ubuntu Linux

I turned on the computer. The HP C4280 appeared in the printer list.

Prime time

So who’s not ready for Prime Time on the desktop? No TV show on Prime Time is without flaws, and no OS is without flaws — Ubuntu still has trouble with some wireless networking cards, and pretty-much 100% of the tech support calls we made at XML 2007 were for Mac notebooks (Windows and Linux notebooks just worked, every time) — but Ubuntu makes it hard to argue that somehow Windows and Mac are good enough for the desktop, while Linux isn’t.

http://www.feliciano.de/Webgalerie/bilder/Italy/une/yiwul/
http://www.unduetretoccaate.it/codice/aseje/wocobo/
http://www.altaiseer-eg.com/ar/articles/jed/umut/

At first, I thought they might be a kind of link spam — some sites display recent searches — but when I checked one of the URLs, I found something totally unexpected:

php echo md5("just_a_test");?>

They’re all the same. This is almost certainly related to passwords: is there a known flaw in a PHP content-management system like Drupal, or in the PHP API for a search engine like Lucene, where this would do some damage, or is it just a test probing for weaknesses? Is the PHP code supposed to be served up literally like that, or should I be seeing the MD5 instead?

Uptime

My ISP set up the server for me last summer with a bare-bones Ubuntu distro, then I installed the extra packages I needed using aptitude over ssh. Since then, I’ve done many Ubuntu in-place upgrades, rolled out hundreds of changes and upgrades to the web apps and dozens to the database schema (some very significant), and upgraded WordPress n-teen times. Check this out:

$ uptime
 13:08:31 up 175 days, 10:02,  1 user,  load average: 0.23, 0.06, 0.02

That’s right — since my ISP first set up the server with a basic Ubuntu system, I’ve never had to restart it. In fact, if Apache and mod_php (PHP5) had ‘uptime’ commands, they’d show almost the same amount of time, since I restarted them only to make configuration changes in the first few days of setting up the server (unless apt stopped them to install a newer version during one of my upgrades). I’ve restarted MySQL more recently, but again, only to experiment with configuration changes (especially for fulltext).

-1 for being cool, +10 for having a life

Using reliable old technologies like Linux, Apache, MySQL, and PHP doesn’t win any cool points, but it certainly makes maintaining a web server and its applications easy. I can go on vacation, for example, without worrying about being able to get online to fix or restart my server every couple of days. I don’t have to stay up until 3:00 am on Sunday night so that I can take the server offline to roll out new software versions or bug fixes (aptitude installs any security fixes in place). I spend lots of time with my family. I go to my kids’ school concerts. I learned banjo and mandolin (why not, since I have the free time?).

It’s the developer, not the language

And yes, my PHP web app is easy to maintain and extend, because I designed it to be that way (I can often implement, test and roll out new features in a matter of minutes, even when they require database schema changes) — it’s the developer, not the programming language, that determines the quality and maintainability of an app. A lot of newbies use PHP, so there’s a lot of bad PHP out there, but the same can be said for any language, even Ruby.

1. Google Maps uses a Mercator Projection, grossly distorting the northern and southern parts of the world, and cutting off the area near the poles so that a few of the Antarctic airports don’t show up on my maps at all. I can understand the reasons for their choice, with simple panning and tile paging and a rectangular area, but it can make things look pretty silly sometimes (such as Greenland and Africa appearing the same size).
2. Google Maps does not provide an API call to draw a great-circle path. This seems to me to be almost a no-brainer, and it’s especially important in a Mercator projection, where the apparently straight paths drawn by the API are anything but (especially east-west). After messing with some out-of-date third-party libraries, I finally found some JavaScript at one site that does a good job on efficient, approximate great-circle paths, and am waiting to hear from the author about terms for reuse. Google might want to just go ahead and add this, though.

Aviation charts mostly use a Lambert conformal conic projection, which ensures that distances are preserved (any two points the same distance apart on the chart are the same distance apart in the real world); however, by definition this projection can’t show more than half the world at once, and generally shows much less than that, so it wouldn’t work for something like Google Maps.

Update: Nope, my solution won’t work. As Christian Matthies points out in the comments, it is possible to spoof the HTTP Host header as well (his link in the comment is broken because of an extra comma, but this one works). As a kludge, browsers could be modified to prevent Host header spoofing, but (a) it would take a long time to deploy to the world at large, and (b) it would be only a bandaid for a much bigger problem.

Summary: While there’s no way to protect browsers against the DNS rebinding attack, you can protect web sites and web services by forcing them to check the HTTP Host header with every request. This is easy to do for RESTful services going through a regular web server like Apache — you get it by default with virtual hosts — but might be trickier for WS-* services.

If you or your company is using HTTP-based web services (either WS-* or REST), you might be in trouble — a new exploit allows a web site from outside your firewall to use a web browser as a proxy to read any web site or service inside your firewall.

Artur Bergman at O’Reilly has a posting on the DNS rebinding (aka anti-DNS-pinning) attack that works against all major browsers, including all versions of Firefox and MSIE. There’s no obvious general fix for this, though there’s a Firefox extension that helps a tiny bit.

1. Keep all the database code together. Put all your database calls into a single source file if you can — functions like mysqli_query (PHP) should never appear anywhere else but in this file — and create neutral functions like get_member() or delete_cart() for the rest of your code to call. The reason for this is not so that you can switch databases in the future (that’s easy enough to fix), but so that you can easily do a search/replace when you rename or modify tables. If all your database code is in the same place, your application will be orders of magnitude easier to maintain and upgrade a few months from now. Seriously.

2. Make an extra database for junk. If your hosting account allows more than one database, create at least two, say “foo” and “foo_cache” — put all the tables you need to back up into the first one, and all the stuff you don’t need to back up (views, caching tables, session states, etc.) into the second. Write a SQL script to automatically regenerate any required tables in “foo_cache” when you restore. That way, you won’t waste time and bandwidth every day backing up megabytes or gigabytes of stuff you don’t need and can easily regenerate.

3. Make GET harmless. If you use HTTP GET (e.g. $_GET in PHP) to do things like deleting or modifying records, bad things will happen to your application — search engines will start randomly changing your database by following links (robots.txt might not be enough to protect you), browsers will delete records by trying to precache pages, etc. Always use POST (normally from a form button) for anything that can make a change. More here.

Dare Obasanjo, smart code guy and occasional punching bag for the anti-Microsoft people, is collecting lists of Three Things I Learned About Software In College. I posted mine in a comment on his blog, but decided to reproduce them here. Note that these are not lessons you learned 10 or 20 years later, but what you discovered back then.

I coded a lot in university — some of it for pay — but fortunately, I didn’t study computer science or engineering. Here are my major lessons:

1. Readable code goes further and survives longer than optimized code, especially once you’re no longer the one maintaining it (or if you have to come back to it two years later).

2. If you write code that makes you feel like a genius, throw it out — you’ll realize later that it’s crap. If you write code that makes you feel like a competent tradesman, you’re on the right track.

3. No matter how smart you are, everyone — even the most incompetent loser of a coder — knows at least one thing you don’t. It’s a good idea to listen.

Note: If you want to record your own list of three things, please leave it as a comment to Dare’s original posting, not here.

Summary: You can’t partition a web site’s users into discrete groups by language.

I don’t worry much about Wikipedia’s objectivity or reliability — no sources (especially not newspapers or Britannica) are objective or reliable, and at least Wikipedia preserves its conflicts and controversies in comments and edit history — but I do have one bit problem with the project: WHY THE *^%*& DON”T THEY HAVE SINGLE-SIGNON?

I usually edit in English, but I can also make at least minor contributions to Wikipedia in French, German, Spanish, Italian, and Latin, and sometimes also contribute to Wikimedia. Every one of those requires me to create a separate account! It is absurd that my username and password for en.wikipedia.org won’t work for fr.wikipedia.org.

Don’t make this mistake with your own webapps, kids. Lots of people in the world are comfortable working in more than one language, even if they’re not fluent in all. It’s good to make a site available in more than one language, but don’t expect language to partition your users into discrete groups. Don’t lock them into a single language with a cookie, or limit their accounts to one language domain — multilingualism is extremely common around the world, even in the U.S. (how many American users would want to be able to use a site in English and Spanish if given the opportunity?)

Granted, anonymity has its ugly sides, like the disgusting online threats against Kathy Sierra and online abuse of Maryam Scoble, but it’s also sometimes the only conduit around the abusive authority of a government, employer, or even one’s peer group. As even Western democratic governments have become more authoritarian since 9/11, keeping these conduits open is more important than ever.

Granted, 99% or more of anonymous information is simply stupid or malicious, but if that’s the cost of freedom, it’s a relatively small cost to pay compared to the sacrifices our ancestors made to win us the freedoms in the first place.

I cannot help you much with making your presentation interesting, but at a minimum, you want it not to suck — “suck” is what happens when you annoy dozens or hundreds of people by making them wait 15 minutes while you deal with easily-avoidable technical problems. Incompatibilities between laptop computers and projectors are still common with all types of hardware and operating systems, so it is never safe to assume that your computer will work this time, even if it has in the past: I’ve seen Linux, Windows, and Mac users, in roughly equal proportions, all fall flat on their posteriors muttering phrases like “but it’s always worked before…” Things will go wrong, but you can minimize the damage by following some simple guidelines:

• Carry an extra copy of your presentation on a CD-ROM or USB memory stick. If you have a last-minute technical problem, you can always borrow another computer and finish your presentation after only a very brief delay. Mailing a copy to yourself at a webmail address is also a good idea.
• A screen resolution of 1024×768 is usually safe. Higher resolutions may or may not work, depending on the projector, so know how to change your resolution quickly if you need to. Seriously: practice changing your resolution at home.
• Disabling screensavers and screen blanking will improve your chances of a successful presentation.
• Have a backup plan if network connectivity slows down or fails (e.g. a local demo) — even if it tests OK beforehand, it might not work when there are 100 other people in the same room, using the same hub, during your presentation.
• Start all programs (web browsers, editors, live demos, etc.) and open all windows you need before you start, and then switch to them as you need them. Murphy’s law clearly states that trying to launch a program during your presentation will fail in the worst possible way.
• If you are using programs other than a slide presentation (such as a text editor with source code, or a web browser), set the fonts to a much larger size than normal so that the audience can read them. 18 point text is the absolute minimum, and 24 point is generally better; 12 point text or smaller is completely unreadable, especially for audience members near the back.
• Make sure that your battery is fully charged, even if you plan to plug in your notebook during the presentation.
• Create a separate profile or account on your computer for presentations, so that all your regular icons, bookmarks, etc. are not sitting on the screen in public view, an IM window doesn’t pop open in the middle of the presentation, etc.

I know that this is obvious, but almost every failed presentation I’ve seen failed because the presenter didn’t follow one of these steps. Go figure.

It turns out that this technique, called a gap buffer [wikipedia], is one of the cornerstones of text editors like Gnu Emacs. I’ve been using Emacs for 20 years and have contributed to the main distribution (see derived.el), but never bothered to look at the C code long enough to discover this particular technique. There’s surprisingly little information online — if anyone’s ever bothered to do testing for the optimum gap size, etc., it’s not showing up in Google — but it’s still nice to experience the joy and excitement of a new (to me), simple algorithm that solves a common problem well.

Does anyone have pointers to more detailed research on gap buffers? It seems to me that they’d have applications far beyond text editing, including (perhaps) storing compiled tree data (aka binary xml) on disk.