Is SQL obsolete?

Lately I've been studying databases and SQL. Not long ago I purchased a large corpus of financial data (historical stock market indices), which came with a front-end application to output the numbers in various ways for easy importation into other software. What’s going on behind the scenes, most likely, is that a relational database is being queried and various views are being produced as flat tables—there's nothing surprising there. But using this software has made me want to know more about database theory, and to understand SQL better, since the latter is basically the lingua franca of databases in the corporate world and everywhere else.

Hence the question in the title of this blog entry. RDMSs are a fact of life—almost universally agreed upon as the best way to store large data collections—but the way we use them has changed: with increased computer power we are now able to query a database in far more complex ways, and far more frequently; this is what makes data mining possible. And general-purpose programming languages have changed equally radically since SQL was developed. In short: if SQL was developed to support a certain kind of database access, a kind that was not easy to do with the programming languages of the time, and a kind that is not much like the kind of access we need now, has its time passed? And if the answer is “yes,” what comes next?

One fact that supports a “yes” vote: First, not only have programming languages changed, but the environments in which we use them have, as well. The designers of SQL didn’t anticipate point-and-click interfaces, let alone the WWW; they probably expected front-end interfaces of some kind, but were thinking only of the green-screen mainframe fill-and-submit forms of the 1970s. The typical web application is a mess. It’s a Frankenstein’s monster of barely adequate tools assembled into an effective but painfully ugly whole: peek behind the curtain of the Great and Powerful Oz of your typical e-commerce site or a Web-based ERP system sitting on a corporate intranet, and you’ll see dynamic HTML full of JavaScript, presenting to the user some buttons that execute PHP code, which queries an RDMS using SQL code, which was written inline in the PHP and is then handed off to the server as a string variable. It works, but, again, it’s a crude, ugly mess. This is the reason behind the push towards more integrated development approaches for Web 2.0 applications—I’m thinking of Ruby on Rails as a particularly fine example, though as I mentioned in the previous post here, I’m leaning towards Catalyst or perhaps a Python-based solution for my own work.

Another point: the languages in which better database front-ends can and should be written lend themselves naturally to direct access to the database. I’ll anticipate objections to the point in the last paragraph by saying that SQL has changed over the past 40 years. Stored procedures are a major difference, and one that can, in part, avoid the clunkiness of having to pass every query to the database as a long sequence of string literals. Extensions like Transact-SQL make stored procedures able to process data in a much smarter way, reducing the number of individual queries the front end has to do. One result is a greatly reduced load on the network between the server and the client, which I'm not saying isn't important! But, in terms of abstraction, stored procedures, I argue, are the wrong direction. (T-SQL in particular falls far short of being a modern programming language: it looks like nothing so much as Fortran77. Wikipedia points out—damning with faint praise!—that it’s Turing-complete. A ball of yarn and a Sharpie are also Turing-complete, but that doesn't mean I'm going to write code to access a database with them.) A far better design is to build the functionality of SQL directly into the front-end language. This is a simple but incredibly powerful idea. Modern programming languages have the capability (i.e., which older languages did not) to effortlessly create and manipulate data structures of essentially unlimited complexity. There’s no reason why the programmer couldn’t specify queries and retrieve data via a library that translates native-language idioms into SQL transparently; going a step further, I don’t see why a language library shouldn’t allow access to the RDMS server directly, exposing the results of a query as a data structure native to the language, without SQL in the middle at all. Neither of these is pie-in-the-sky stuff: for implementations of the former idea, see Rails’ Active Record Query interface, Python’s SQLAlchemy, or Django. Nobody seems to have actually implemented the complete elimination of SQL yet, but there’s one brief but thought-provoking discussion here. And Microsoft's LINQ goes a long way towards breaking down the distinction between different kinds of data storage, providing a unified interface; as do the various wizards in Visual Studio 2010 which allow you to just drag and drop tables from Access into your project.

Part of the reason for the continued use of explicit SQL code constructed by a front-end app and submitted as strings, I suspect (again, sidestepping the speed issue) has to do with security. Stored procedures provide security via encapsulation; explicit code can be more easily checked for attempted exploits such as SQL injection. But the latter is an issue precisely because of the exposure of the database structure at an unnecessarily high level; and the former is what object-oriented languages are all about. (Again, a design feature that SQL needed to provide at the time of its conception but which has now been implemented with far more versatility and elegance in programming languages: OOP and other techniques preach the gospel of “maximize cohesion, minimize coupling” to improve not just design and maintainability of software but also data security.)

I want to make clear that I’m not advocating the elimination of relational databases as such. (Even though arguably most current implementations don’t really follow relational database theory in practice.) What I don’t like is the crude way that we interact with them. I want to see programmer-space access that goes beyond building SQL code with string manipulation operators, then handing it off to the database; I want to see end-user-space access that goes beyond clunky wizards that build a limited subset of SQL queries (always almost, but not quite, the one you really need). The relational model has been adopted almost universally as the standard way to store data sets of arbitrary size, for very good reasons that held true thirty years ago and still hold true today. But let’s make sure our reasons for the way we interact with the relational model still hold true as well.

Does your installer do dependency checking?

As I threatened to do a few posts ago, I've been working on some graphical applications written in Perl and using the Simple DirectMedia Layer. I got a bit of practice code written, but not tested, a few days ago—a 3-D plot of a trig function—only to find that the software that allows Perl code to talk to the SDL libraries wasn't actually installed on my machine. OK, I thought, so that's not part of the default Perl install on OS X—I'll just find it and install it.

Easier said than done! First I had to get the entire developer kit (2+ Gb of downloads, and a couple of hours to install) from Apple. (I can see excluding the GUI developer tools, XCode and friends, from a default OS X install—but why the command-line C compiler and a basic set of libraries and header files? They'd take up a negligible amount of disk space.)

Next came installing the actual SDL libraries, then the Perl-to-SDL interface. I first tried Fink, which aims to provide one-stop shopping for .deb packages à la the various GUI frontends to apt in different Linux distros. Fink presents you with a window of available packages (which updates every time you open it, naturally) and downloads the ones you choose either as an OS X binary if one exists or as source if necessary (then configuring the latter to build under Darwin, compiling it, etc.). Fink has worked well for me in the past, but in this case though the list of available packages included SDL, it didn't include SDL-Perl. Fine, I thought, I'll tell Fink to install SDL, then install SDL-Perl via the command-line CPAN installer module included with Perl. The former worked OK; the latter didn't. The CPAN installer module couldn't deal with the many modules that were prerequisites to installing SDL-Perl, so I gave up and started doing everything by hand. This quickly led me into a "dependency hell" wherein every tarball I downloaded seemed to require that something else already be installed. About here is when I started to feel like banging my head against the keyboard. THIS SHOULDN'T HAPPEN. The problem of package dependencies on Unix-like systems was solved 15 years ago when the first Debian Linux distribution was released. I remember installing Debian 1.something on my first Linux box, which had previously been running Red Hat with its markedly inferior .rpm package system, and marvelling at how smoothly the install process went despite my idiosyncratic selection of software from the packages available.

Then I tried MacPorts, a similar front end to open-source software, mostly command-line and X Window stuff configured for Darwin. After installing it from a .pkg file (a self-installing Mac OS X package format) I typed just one line in a terminal window (MacPorts so far has no GUI front end):

sudo port install p5-sdl_perl

and was rewarded with a couple of hundred lines of beee-you-dee-ful status info scrolling by; extracting this, checksumming that, and compiling the other thing, as MacPorts automagically set up the necessary prerequisites and finally SDL-Perl itself. When I tried to run my Perl script again, it Just Worked.^1,2

Moral: No matter whether your software is the most user-friendly GUI application ever, or some kind of arcane tool only to be used by superhackers; if you don't implement (at the very least) dependency checking or (preferably) automatic dependency resolution, you deserve forty lashes with a wet noodle, as Ann Landers used to say.

1. Well, almost—I had to tell Perl where to find the new modules, but that was both trivial and something I needed to know how to do anyway. [back]
2. Check it out! (Not impressed yet? Hey, it's just proof of concept.) [back]

Stymied by horrible interface design, not for the last time

I had an experience last night that was a textbook example of how not to design a user interface for a consumer product. (I've been told this post is too long: I think the anecdote is revealing, but skip to the last paragraph if you just want to read the moral.)

I was trying to connect a new DVD player to a high-definition flatscreen TV. The DVD player box said that it did automatic upconversion of DVDs (which normally, of course, are not high-def) to the highest-quality HD protocol, 1080p, so I was hopeful that the HDMI connection would provide excellent video quality.

I expected to be able to just connect the HDMI cable to both devices, set the TV to accept an input from the HDMI port, tweak a few setup parameters, and be off and running. Of course it didn't work that way. No picture. The DVD player had a series of LEDs on the front indicating what type of signal was being sent over the cable at that moment, and a button labeled "HDMI", duplicated on the remote, to select one. I tried pressing that button repeatedly. Still nothing. HDMI is a handshaking (i.e., two-way) protocol which should gracefully degrade if necessary down from 1080p down to 720i (DVD quality), with stops in between if the hardware is capable of it, and I expected at some point the DVD player and TV player would find a protocol on which they could agree; but apparently they didn't. I say "apparently" because neither device offered any kind of error indication other than a black screen!

I looked in the manual, and found that I was supposed to manually select HDMI output in a setup menu. In order to see the menu, I hooked up the player to the composite video/left-right audio jacks on the TV, and was able to get a (relatively low-quality) picture. I changed the output setting to HDMI, and changed the TV input back to match. Still no picture. I pressed the "HDMI" button on the front of the DVD player a few times, and watched the LEDs cycle through the available HDMI video options, but with no change (the TV displayed nothing but "HDMI Input," which apparently meant "I'm waiting for HDMI input" rather than "I detect it").

I went back to the composite connection on the TV and looked at the output setting in the DVD player setup menu again. "Composite" was selected—"HDMI" was still available, but was not selected as I had left it before. I made sure I was pressing the right button to save the settings when I left the setup menu, rather than exiting without actually making changes. I was. I tried the same routine a few more times, but the "HDMI" setting for DVD output just wouldn't take.

So here are a few tough questions:

• Why couldn't the DVD player designers have included a single LED, maybe a red/green indicator of whether HDMI handshaking was successful or not?
• Why an HDMI button on the machine (and the remote!) at all? The player ought to automatically output the highest quality signal that it can, at all times.
• Similarly, why an output selection menu on the DVD player? Why can't all outputs on the player be active at all times?
• Finally, why couldn't the TV, with much more real estate for messages, display something like "No compatible HDMI signal found" to assist with troubleshooting?

As it was, I made an educated guess, based on the limited evidence available, that the DVD player was faulty right out of the box and unable to actually generate an HDMI signal (not at all far-fetched given the general cheapness of consumer electronics these days): perhaps a poorly soldered connection to the output jack on the circuit board inside. But the frustrating hour spent arriving at that educated guess was worthy of user-interface guru Donald Norman, whose discussions of confusing electronics in cars, door handles shaped so that you don't know whether to push or pull them, and much more, are still 100% relevant twenty years after he began publishing them (and should be required reading for anyone involved in the design of end-user software or consumer products).

Why did I tell this long story on a blog primarily about software design for financial analysis? Bottom line: design affects productivity. The way things work affects what the user can get out of them, and that's always going to be true, whether you're producing an iPhone app, a spreadsheet, a rich web application, or whatever the Next Big Thing in tech is.

Let’s Retire the Forms-Based Interface

[Originally published July 11, 2007.]

I bet not too many people reading this have ever used a terminal hooked up to a minicomputer, and even fewer have used a forms-based interface on a terminal of that kind. But this kind of human-computer interaction is still a commonplace event in the business world: legacy IBM mini systems are everywhere. (If Y2K couldn’t kill them, nothing can.) People call this model “green-screen technology,” and they mean it pejoratively—it’s something archaic, clunky, and generally inferior to “modern” user interfaces.

That charge is mostly accurate. Though green-screen technology has its place (and to say what that place is, would be getting off-track, but it does have one) there’s no denying that it’s old-fashioned. But let me point out that so much of the Web consists of exactly that sort of thing. Web pages with fill-out boxes, check boxes, and radio buttons are even called “forms,” in obvious acknowledgement; so are Visual Basic application windows. The metaphor works, sort of: you fill in a form and then click “submit” (or press “Enter” on your IBM terminal’s keyboard—ever wonder why Windows PC keyboards say “Enter” instead of “Return”? now you know) which is like sending in a (paper) form to an office somewhere. Then you get an answer back—the results of a computation, a database query, etc.¹

None of this is shocking. What is shocking is how much modern software exists whose interface is still essentially forms-based, yet which pretends to be interactive. They’re two very, very different interface paradigms. Interactivity in software comes from more than just adding buttons & windows to a forms-based interface. I.e., if your idea of successful HCI consists of a modal window in which the user fills in a bunch of fields and presses a button, whereupon a new modal window pops up containing a report, then you’re not only putting lipstick on a pig but also being just plain dishonest: you’re selling ’70s tech as if it were something new. Way too many commercial products that are essentially prettified database frontends (which isn’t a bad thing in itself) are designed with this mentality—that all you, the user, ever do with a computer is run an offline query (and maybe a batch of them if you’re a power user). ("But I'm not a fish!")

Now think about actual interactivity, the thing that microcomputers give us (or at least were supposed to, back around 1980). This is the state where not just all the data you’re working with but also the operations on that data and their results are fully accessible at all times, within reason. It’s the guiding mentality behind WYSIWIG in word processors, for example, as opposed to typesetting software like nroff or TeX (in which you write your document as a text file with interpolated commands, then submit that file to a program which outputs a proof). Another great example is Excel, which is nothing like programming numerical computations in a traditional programming language (for Excel is a programming tool—it has more in common with friendly interpreted language environments like the old 8-bit BASICs than with much application software). You see all your numbers in front of you, and by clicking in a cell or pressing a magic keystroke you can see all the operations on them (i.e. formulas), or the results of those operations. And you have total freedom to change or transform the data or the operations in realtime. There’s no modality to speak of.

Again, because this is the critical idea: you can’t just base an interface on pulling stuff out, changing it, and then resubmitting (putting it back in), and call it interactive. True interactivity requires non-modality of not just operations but also data: that is, all the data should be accessible all the time. Jeff Atwood wrote a great blog post about taking incremental search beyond the dataspace into the commandspace (pace Emacs). I’d like to see a lot more development of and experimentation with interfaces that use this kind of dynamic filtering to perform search, Neuromancer-style n-dimensional visualization of the dataspace, or a combination of both. Imagine this: instead of filling out a form and hitting “search,” you type (or click on) your parameters and watch a nebula of data dynamically shade itself as you type, with color and transparency indicating the sets involved and their relevance rating²—sort of a 3-D mixture of Venn diagrams and PivotTables.³ Or... remember the holodeck-furniture-database-search scene from the Star Trek episode “Schisms”?⁴

1. Actually I like to think of this not so much as a “sending a form into a government office” model of computing as a “Wizard of Oz” model. You make your request of the Great and Powerful Oz and hope he gives you back something you can use.[back]
2. C’mon, let’s use those alpha channels and all that other pretty stuff that modern graphics hardware can do for something other than another variation on Doom! [back]
3. But please don’t call it “drilling down!” That’s not what that means, but I’ll save that for another entry. [back]
4. Why is this not required watching for budding interaction designers and database programmers? [back]

Why Web Applications Are Broken

[Originally posted April 1st, 2007. No foolin’!]

I’ve been thinking of large websites with heavy back ends (Amazon being the canonical example) as applications for a long time now. So I have a bit of a so-what reaction when I hear people talking about a paradigm shift to applications in the browser. I want to ask, don’t you remember what Scott McNealy was saying in every interview in the late ’90s—Sun’s slogan “the network is the computer”? Turns out the people promoting a web-based thin-client model ten years ago were just way ahead of their time; it took technologies like Ajax and proof-of-concept apps like GMail and Google Maps to make the idea concrete. The reason I’m underwhelmed is not so much that something old has been dressed up and called the latest thing (what else is marketing about?), but more that there’s a fundamental change that needs to happen before apps in a browser will work. This isn’t a technological barrier—more precisely, it isn’t just a technological barrier, but also (more challenging!) one of human-computer interaction and design.

The problem is this: as it stands, the web browser as an environment for applications is almost irredeemably broken. We’re used to thinking of the navigation controls (buttons, bookmarks menu, etc.) in the browser as first-class controls, while the widgets in the window are second-class. If you get somewhere you don’t want to be in the browser, you don’t hunt through the window for an escape hatch to the previous page provided by the site designer—you just click the “back” button. (But [consider this] does “forward” ever do anything useful or predictable?) But in doing that you’ve made a conscious choice between two different interfaces—that of the browser and that of the page. Which interface does what? Giving the page its own controls is like giving the road its own steering wheel.

(Actually, the “back” button has been broken since day 1 [or at least since the first time I used the Web, in 1994, via Mosaic]. Here’s an example.

Start at page A and click a link to go to page B. Then click a link to go to page C. Then click the “back” button twice to return to the home page, A. Click on a link to go to page D. Now try to return to page B via the “back” button. You won’t be able to! As the history menu will indicate, the browser remembers only that you visited A. The interface is broken because it’s unnecessarily confusing. The “back” button is trying to serve two different and incompatible purposes: it’s supposed to mean both “undo” and “go to a higher level in the hierarchy.” The latter doesn’t work, because a fundamental principle of Web ontology¹ is that the web is a network, not a hierarchy. There’s only incidentally an “up” in hypertextspace! Further, if the browser saved your entire surfing history [for this session], and if “back” also meant “up a level,” what would it mean to click “back” while viewing a child page (e.g. C above)? Would you end up at B or D? ² Clearly the only workable solution is for “back” to mean “undo,” and for the browser history to show every page visited, in parity. Or is it workable? It’d be nice for “forward” to mean “redo.” But what does it mean [just to give one of many available troubling examples] to undo the submission of a form?)

Perhaps the real problem is deeper. The web browser as such is a tool for accessing data. It may have grown far beyond its origins as a graphical Gopher, but it’s still, at heart, just a way to navigate a topology of discrete records (pages) in a huge (non-relational) database (the ’Net). But now we’re asked to think of the browser also as an environment in which to run applications. Applications and data, despite the promises of object-oriented programming (irrelevant anyway, since that’s a methodology of software architecture, not interface architecture³), are two entirely different kinds of entities. This means that one program that does both is inevitably going to have, as I just noted, an inconsistent, confusing, unfriendly interface. Blurring the distinction between applications and data under present interface standards only makes things worse. Why not remove the controls entirely and make the browser into essentially a sort of terminal emulator window for remote applications? Or why not go all the way in the other direction and make everything you work with on the computer part of a unified, modeless, totally data-centric interface, like Swyftware and the Canon Cat? (Actually, I’m less than half joking with that last rhetorical question—Jef Raskin’s legacy is the only viable hope I’ve yet seen for a truly new and truly better approach to the UI.)

Jesse James Garrett’s whitepaper that introduced the term “Ajax” posed as an important open question “Does Ajax break the back button?” I’d turn that around: Does the back button break Ajax? That is, is the Web 0.9 interface of the browser a vestigial impediment to writing applications that run well (meaning at the same usability level as traditional non-Web-based applications) in the browser window?

1. E.g., as articulated in Chapter 1 of the Polar Bear Book. [back]
2. The mirror image of this problem afflicts the implementation of the cd command in bash (the standard shell on Linux). If you are currently in directory X and follow symbolic link S to directory Y, then enter the command “cd ..”, you end up not in the parent of Y but in X again! There is no way to get to Z, Y's parent, without explicitly specifying more of its path than just “..” (i.e. “parent of current”). This is broken beyond belief. Look in any documentation for any command-line interface that includes the cd command (MS-DOS, VMS, Unix shells, whatever) and I guarantee you won’t find “cd ..” explained as meaning “undo.” For it to behave as such is horrifyingly inconsistent. “..” means “up one level in the hierarchy.” Symbolic links explicitly break the hierarchy, but that’s OK: they’re understood to be “hyperspace” shortcuts, like the Secret Passage across the board in Clue that takes the player on a third-dimensional trip outside the board-game Flatland. [back]
3. And the dangers of the tendency of programmers, and of companies headed by programmers, to conflate the two are legion. [back]