|
|
|
visualized subqueries
Tuesday, August 25 2009
The other day I'd had a vexing problem to solve with a database-driven website whose architecture I'd cobbled together about six months ago. (I'd had to integrate both Moodle and Xcart, and, as you might imagine, the experience had been much worse than if I'd just scratch-built the functionality those premade web applications provide.) In trying to debug this problem, I'd found myself craving a more complicated version of my MySQL freeform query tool (part of my larger homemade database visualization suite). I'd wanted a tool that could perform secondary queries using (as inputs) rows spit out by the first queries. MySQL supports subqueries, but I'd wanted a more flexible, and, importantly, a more visual system. I'd wanted to see the rows of results interspersed with rows of results from the subqueries so I could see exactly what data was coming out of the earlier query and what that was producing in subsequent queries. A properly built system like this can rapidly reveal faults in PHP-mediated queries, which I'd soon discovered once I'd cobbled together primitive support for visualized subqueries in my query tool. It had allowed me to quickly solve my problem, which was a flaw in a complicated grading algorithm.
That functionality proved so useful that I've been further developing it. Today I put hours of effort into getting this subsquery support so that it could handle an indefinitely-nested series of queries. To achieve this, I had to convert ActSQL, the function that performs a list of queries and then renders a series of HTML-formatted results, into a function capable of recursion. It recognizes {curly-brackets} as the encapsulators of a subquery, and it interprets any string in a subquery beginning with a dollar sign as the name of a field in the record set from the query one level up in the subquery hierarchy. The function performs the subquery multiple times, using the result from each record produced from the parent query. As an illustration, I can now feed the query tool something like:
select * from critter where critter_id<5
{select lifeform_id, name, genus, species, wikipedia_url, description from lifeform where lifeform_id=$lifeform_id}
and get:
You'll note that the headers provide front-end (Javascript-based) sorting of the columns based on their content. Though I created a non-hierarchical front-end recordset sorting system a year and a half ago, the hierarchies in these more complicated results meant I would have to modify the sorting code. I didn't find time to do those modifications today, but you will nevertheless find the sorts are actually functioning correctly on this page. Getting generic hierarchies of row-based data to sort correctly based on clicks to headings and subheadings would be an entirely different (and bigger) programming challenge from the one undertaken today. Today's task consisted mostly of writing a series of parsing functions to do things like isolate a block of bracketed content without disturbing any sub-bracketed content while also ignoring brackets in any quoted passages.
This evening Gretchen made her first pizza in weeks. It was unusually delicious, in large part due to a crust recipe found in Rose Levy Beranbaum's Bread Bible. Though a few years ago Levy Beranbaum was one of Gretchen's culinary heroes, those days are over. While Levy Beranbaum has a few recipes that a vegan can make use of, she's also a big carnivore and, as Gretchen puts it, "if a lot of animal suffering will make food taste a little bit better, she's all for it."
For linking purposes this article's URL is:
http://asecular.com/blog.php?090825 feedback
previous | next |
