Thoughts filed in: Innovations

I have an interesting problem, on a data migration project I'm currently working on. I'm importing a large amount of legacy data into Drupal, using the awesome Migrate module (and friends). Migrate is a great tool for the job, but one of its limitations is that it requires the legacy database tables to have non-composite integer primary keys. Unfortunately, most of the tables I'm working with have primary keys that are either composite (i.e. the key is a combination of two or more columns), or non-integer (i.e. strings), or both.

Table with composite primary key.

The simplest solution to this problem would be to add an auto-incrementing integer primary key column to the legacy tables. This would provide the primary key information that Migrate needs in order to do its mapping of legacy IDs to Drupal IDs. But this solution has a serious drawback. In my project, I'm going to have to re-import the legacy data at regular intervals, by deleting and re-creating all the legacy tables. And every time I do this, the auto-incrementing primary keys that get generated could be different. Records may have been deleted upstream, or new records may have been added in between other old records. Auto-increment IDs would, therefore, correspond to different composite legacy primary keys each time I re-imported the data. This would effectively make Migrate's ID mapping tables corrupt.

A better solution is needed. A solution called hashing! Here's what I've come up with:

1. Remove the legacy primary key index from the table.
2. Create a new column on the table, of type BIGINT. A MySQL BIGINT field allocates 64 bits (8 bytes) of space for each value.
3. If the primary key is composite, concatenate the columns of the primary key together (optionally separated by a delimiter).
4. Calculate the SHA1 hash of the concatenated primary key string. An SHA1 hash consists of 40 hexadecimal digits. Since each hex digit stores 2⁴ different values, each hex digit requires 4 bits of storage; therefore 40 hex digits require 160 bits of storage, which is 20 bytes.
5. Convert the numeric hash to a string.
6. Truncate the hash string down to the first 16 hex digits.
7. Convert the hash string back into a number. Each hex digit requires 4 bits of storage; therefore 16 hex digits require 64 bits of storage, which is 8 bytes.
8. Convert the number from hex (base 16) to decimal (base 10).
9. Store the decimal number in your new BIGINT field. You'll find that the number is conveniently just small enough to fit into this 64-bit field.
10. Now that the new BIGINT field is populated with unique values, upgrade it to a primary key field.
11. Add an index that corresponds to the legacy primary key, just to maintain lookup performance (you could make it a unique key, but that's not really necessary).

Table with integer primary key.

The SQL statement that lets you achieve this in MySQL looks like this:

ALTER TABLE people DROP PRIMARY KEY;
ALTER TABLE people ADD id BIGINT UNSIGNED NOT NULL FIRST;
UPDATE people SET id = CONV(SUBSTRING(CAST(SHA(CONCAT(name, ',', city)) AS CHAR), 1, 16), 16, 10);
ALTER TABLE people ADD PRIMARY KEY(id);
ALTER TABLE people ADD INDEX (name, city);

The exercise bike has been around for decades now, and its popularity is testament to the great ideas that it embodies. Want to watch TV in your living room, but feeling guilty about being inside and growing fat all day? Use an exercise bike, and you can burn up calories while enjoying your favourite on-screen entertainment. Feel like some exercise, but unable to step out your front door due to miserable weather, your sick grandma who needs taking care of, or the growing threat of fundamentalist terrorism in your neighbourhood streets? Use an exercise bike, and you can have the wind in your hair without facing the gale outside. Now, how about adding one more great idea to this collection. Want to contribute to clean energy, but still enjoy all those watt-guzzling appliances in your home? Use an electricity-generating exercise bike, and you can become a part of saving the world, by bridging the gap between your quadriceps and the TV. It may seem like a crazy idea, only within the reach of long-haired pizza-eating DIY enthusiasts; but in fact, pedal power is a perfectly logical idea: one that's available commercially for home use by anyone, as well as one that's been adopted for large and well-publicised community events.

The human body is a self-sustaining and self-repairing entity. When you cut your hand, when you blister your foot, or when you burn your tongue, you know — and you take it for granted — that somehow, miraculously, your body will heal itself. All it needs is time. However, there are many injuries that are simply too severe for the body to repair by itself: in these cases, help may be needed in the form of lotions, medicines, or even surgery. Why is this so? With all its vast resources, what is it that the human body finds so difficult and so time-consuming in healing a few simple cuts and bruises? Surely — with a little bit of help, and a lot more conscious concentration — we should be capable of repairing so much more, all by ourselves.

Modern economics is infinitely bizarre. We all know it. I'm not here to prove it; I'm just here to provide one more example of it — and the example is as follows. You're an educated, middle-class professional: you've lived in a developed country your whole life; and now you've moved to a developing country. Your work is 99% carried out online, and your clients live all over the world. So you move to this less-affluent country, and you continue to work for your customers in the First World. Suddenly, your home is a dirt-cheap developing nation, and your income is in the way of formidable developed-nation currency. The result? Well, I'm no economist — so correct me if I'm wrong — but it would seem that the result must be a paradise existence, where you can live like a king and still spend next to nothing! Could this be the next big thing in employment, that we should expect to see happening over the next few years?

For all of the most memorable moments in life — such as exotic vacations, milestone birthday parties, and brushes with fame — we like to have photographs. For some people, photography is an art and a life-long passion: there is great pride to be had in capturing significant occasions on film or in pixels. But for others (such as myself), taking photos can quickly become little more than a bothersome chore, and one that detracts from the very experiences that you're trying to savour and to have a memento of. For those of us in the latter category, wouldn't it be great if our cameras just took all the pictures for us, leaving us free to do other things? Judging by recent developments, this may not be as far off as you think.

I've been working on a project at University, in which my team had to produce a large number of software design documents, which now have to be translated into working code. Wouldn't it be great if I could write just one design specification, and if from that, numerous diagrams and skeleton code could all be auto-generated? What the world needs is a plain-text program design standard.