faceless houses and forging foreign keys
Sunday, January 22 2006
I was driving out to the Catskill Animal Sanctuary today to drop off a CD and as I headed down Old Stage Road I couldn't help but notice the sorry state of the architecture there. Old Stage runs parallel to and just east of Route 9W through one of the most economically-marginal parts of eastern Ulster County, and typical of such places, the architecture tends to be a hodgepodge of styles crowded close to the road on undersized lots. Here and there you'll see a ranch or an isolated McMansion and there's even be the occasionally older house of respectable vintage. But mostly what you see are prefab boxes only slightly more stately than mobile homes. These typically have squat rooflines and a glaring absence of windows. What makes an absence of windows particularly noticeable is when an entire side of a house is windowless. It's like looking at a head that lacks a face. In clear testament to a high background level of site-planning ignorance in this country, such windowless walls are just as likely to face south as north, depending on whatever orientation was dictated by the vector of the road and the shape of the lot. New rule: houses in northern temperate zones must have at least one window on the south-facing side. Disobeying this rule is inexcusable site-planning malpractice.
This evening I continued work on a general-purpose web-based SQL front end, using txtSQL as a backend. txtSQL is an amazingly good SQL given that it fits in only 188K of interpreted PHP script. Its weakness is also its strength; unlike, say, the C++ that drives Linux and most of Mozilla, PHP is a very accessible language that I intimately understand. I can get in there and modify it to suit whatever my particular needs happen to be. So today, for example, when my web-based front end needed a way for me to state foreign key relationships between tables, I was able to figure out a way to add it. I did it in a manner similar to the one nature uses when it fashions new proteins out of existing ones; I duplicated the part of the code that handled the MAX value for an integer field type and then modified both the parser and the field object handler of this duplicated code so that it would respond to the keyword FOREIGN as in the following example:
In this case, my crude syntactic advance allows me to forge a connection between a column in the table transaction_item and a primary key of the foreign table transaction. It also allows me to connect the column product_id to the primary key of the table product. Unlike in (say) Microsoft SQL, this version of the syntax doesn't allow me to choose what column I'm connecting to in the foreign table; it has to have the same name as the one in this table. In practice, though, this isn't much of a restriction (and if it should prove to be, I can modify txtSQL further later).
$sql->query('CREATE TABLE transaction_item (
transaction_item_id primary key permanent int auto_increment,
price money)') ;
Using this relationship system, I was able to modify my PHP-based HTML front end so that when you enter data for a table that refers to another (foreign) table, instead of having to type the ID of the item in that foreign table, you're presented with a dropdown list of items to select. This was the beginning of what I hoped would be a wave of major advances to my tool, allowing me to turn it into a robust (but user-friendly) general-purpose SQL front end.
For linking purposes this article's URL is:feedback
previous | next