« Newtonian Philosophy | Main | The Too-Long Meme »
January 28, 2005
Will and The Glory of SQL
Nobody knows what’s going on with Will. I’m worried. He sent me a kind of freaked out email, and no one has seen him since.
SQL
In thinking about things, I guess I’ve come to appreciate the additional power relational databases have when they have stored procedures, views, and triggers. Here’s a piece of SQL I wrote tonight, which I’m particularly proud of:
CREATE VIEW web_clanpeople AS SELECT p.firstname, p.nickname, CASE p.want_privacy WHEN FALSE THEN p.lastname ELSE repeat('*', length(p.lastname)) END AS lastname, p.email, p.want_email, COALESCE(c.clan, 'Unclanned People') AS clan FROM people AS p LEFT OUTER JOIN clanpeople AS c ON (p.firstname = c.firstname AND p.lastname = c.lastname) ORDER BY c.clan, p.lastname, p.firstname;
What this monster does, is basically, create a table for the front page of Story to Tell. It does this by doing a bunch of cool things:
- It conceals the last name, using a case statement conditioned on their want_privacy, building a string of repeated “*”s the appropriate length.
- It does an outer join on the people, so that everyone on STT.org gets an entry here.
- It replaces the NULL which would be there from the outer join with “Unclanned People,” which is how it’s supposed to appear.
- It discards the “want_privacy” bit, which is irrelevent for showing that page.
- It sorts on the clan, then the last name, then the first name, yielding the ideal order.
Now I can build a fairly stupid script for showing the main page, where before, there was actually logic in the presentation layer. Now it’s going to be implemented with a simple SELECT being unpackaged in a loop, with different clan names producing new H2 elements.
Tomorrow I’m going to make a view for the other SQL-driven page, which just shows the people who have local pages. Then I’m going to replace all of the storytotell.org scripts with these ones, and worry about the admin interface which I’ve ignored for so long. Mod_Ruby is going to be a big factor in the new system.
Posted by FusionGyro at January 28, 2005 12:32 AM
Trackback Pings
TrackBack URL for this entry:
http://www.clanspum.net/~fusion/blog/admin/mt-tb.cgi/87