Yet another post in a neglected series of riveting relational databauchery.
This past week I've been doing some maintenance work on the execrable system that runs the College (bear with me, I'm working on a Google bomb). This system happens to have a web-based interface that allows staff and students to view final marks, paystubs, class schedules, and so forth.
If you've ever dabbled in web applications, you may know of some of the popular development platforms: PHP (which powers Facebook, Digg, What He Said, and the College's public site), ASP.NET (MySpace, Lego), JSP (Globe and Mail), Ruby on Rails (mostly apps from the folks that created RoR in the first place), and a zillion others.
These technologies query relational databases in order to render the HTML web pages that you see when you visit the site. None of them are built in to the relational database itself, using the SQL query language from within to spit out the web's angle-bracket-laden content.
But that's exactly how the College's self-service site works.
It appears to have been built atop relational-database-maker Oracle's HTML DB product, which features PL/SQL as its programming language -- a bondage-and-discipline language that requires you to first declare that you will later again declare your intention to define a value as being equal to 3.
Unfortunately, declarative query languages like SQL don't easily jump through the hoops and loops required to emit web pages. For example, a single section of code to extract a list of students based on whether the user is an instructor, an advisor, or both, and whether the user wishes to view students that are instructed, advised, or both, looks something like this.
IF student is instructed and advised THEN IF user is instructor and advisor THEN 30-line SQL query to retrieve students that are both instructed and advised by this advisor and instructor ELSIF user is advisor THEN Same 30-line query, with one change to look for instructed students that are only advised. ELSE Once again, the same 30 lines, this time looking for instructed students that are just instructed. END IF; ELSIF student is advised THEN IF user is instructor and advisor THEN Yet another copy of the 30-line SQL query to retrieve students advised by this instructor/advisor. ELSIF user is advisor THEN You guessed it. ELSE Hoo boy. END IF; ELSIF student is instructed THEN IF user is instructor and advisor THEN This is getting tedious. ELSIF user is advisor THEN And annoying. ELSE Time for a break, what's new on Reddit? END IF; ELSE A secret none-of-the-above option that appears here requires another 30 lines. END IF;
Leaving aside that most of that doesn't even make sense -- I lost an hour to wondering how a student could only be advised and not instructed -- it's just plain hideous. Some 300 lines of code are involved, all but 30 or so seemingly redundant.
This was not an isolated case. Determining whether a user is an instructor or an advisor uses the same pattern. The PL/SQL "logic" that determines whether a user's account and PIN are valid is a multi-screen riot.
Conclusion: keep the code out of the database.
Update, Jan. 28
Of course, I managed to screw up the example code so that the
ELSE clause of each decision would never execute. It's fixed now, just in case anybody out there was planning on implementing their own twisted instructor/advisor system.
- Geof Harries on 20080125.Friday:
Amen to that brother. Do your very best at all times to respect the layers: structure, content, presentation and behaviour (relevant terms when it comes to web apps).
- Dave on 20080125.Friday:
- AOF on 20080330.Sunday:
Was this stuff *generated* from some higher level tool or did somebody really sit down and write this... erm.. ?code?
- Dave on 20080331.Monday:
I wondered if it was auto-generated too. But much of it leaves the distinct impression of a human's touch, and judging by the comments, bug fixes are performed on this code rather than at a higher level.