InnoDB Plugin Doc

The InnoDB Plugin manual is now available on the MySQL web site.

More on Single-Action UIs

In my post Deconstructing the iPod Shuffle UI, I talked a bit about the notion of a limited UI where you really only do one thing -- in that case, click the button on the headphones.

Every now and then, I rediscover the infrared remote that goes with my iMac, and realize that for many mundane tasks, the remote does everything I need. (Mira lets me assign different actions for the remote button for specific applications. Candelair works around a Snow Leopard issue with Mira.)

For example, with a new album in a photo library, you might want to Next-Next-Next through each photo, and if one is particularly bad, trash it; if one is particularly good, mark it with a high rank or a coloured label; otherwise, leave it as-is and proceed to the next one. The up/down/left/right/play buttons on the remote can accomodate those actions comfortably. All the photo software offers keyboard shortcuts for these actions, that can be assigned to the remote buttons. So this brain-intensive task can be accomplished while sitting back and taking in the view on a large screen.

One thing had been bothering me about the UI for the iPod Touch. If I'm listening to music with the screen turned off, say in the car or the office, and I'm interrupted (want to stop the music suddenly) or annoyed (want to immediately skip to the next song), normally this requires turning on the screen (Home button or power button), and unlocking the iPod by swiping before the music controls are available. I should have realized Apple would bring the "single-action UI" to the rescue. Instead of unlocking the whole unit, once it is powered on in a locked state, 2 more presses of the Home button bring up music controls that let you pause or skip without unlocking. It's almost iPod Shuffle-like. Now if only there were a way to assign star ratings without unlocking.

Many other day-to-day activities could benefit from this same kind of UI, in particular with the remote. You've got a lot of objects to consider or evaluate, and a limited number of actions for each one, including a default of "do nothing at all". Someone could play through a set of songs and give high / medium / low rating to each one, or trash it. A programmer could scroll through a list of bugs or functional specs, and for each one indicate "closed", "waiting for more information", "this does not apply to me", etc. That may be the way of the future, where carpal tunnel and hunched shoulders are just a distant memory.

Snow Leopard upgrade

I finally upgraded the main iMac to Snow Leopard. For the first time ever, an upgrade actually resulted in more free space, an extra 6 GB worth. The main features that I notice are fairly minor -- the ability to view stacks on the dock using a normal icon instead of the smashed-together icons of the apps in the folder; the ability to have the time announced every 15, 30, or 60 minutes by the computer voice.

I didn't notice any increase in speed, but maybe that's because Adblock in Safari went away until I installed a new version of that extension. Also I made the mistake of enabling Spotlight in hopes of getting "search entire message" working again in Mail.app. Although I think I've turned Spotlight off again, I'm always suspicious that it might still be active when I hear the drive whirring when I think the system should be idle.

My only real software incompatibility was with MediaWiki (the software that powers Wikipedia). It wouldn't run after the upgrade, because the newer PHP with Snow Leopard resulted in syntax errors in the MediaWiki PHP code. I upgraded MySQL because I was running an old PowerPC-based version via emulation. But I hadn't exported the wiki data, so I had to fall back to the older MySQL. When I updated the MediaWiki code to get past the PHP errors, I discovered that upgrading from such an old codebase meant running an upgrade script to change around the MySQL tables, and this script is also in PHP. And for some reason, the PHP script can't connect to the database. Since so many things needed to be updated at once, the cause could be related to PHP, or MySQL, or maybe that I had installed them by a different method before (the Marc Liyanage "entropy" packages). For example, after installing the latest MySQL through the official installer, mysqld now restarts when I kill it, which I don't think was the case previously.

Update 1

The Canon scanner software with my Pixma MX330 all-in-one printer refuses to start after the Snow Leopard upgrade. I can still scan using a dialog that opens up within Print & Fax preference pane for the printer, but only with barebones options. The recommended troubleshooting from Apple (reset the printing system, remove and re-add the printer) doesn't work. The latest software from Canon, supposed to be Snow Leopard-compatible, also doesn't help.

The problem with MediaWiki boiled down to a config setting for PHP that needed to be applied or reapplied after going to Snow Leopard, 3 instances in /etc/php.ini of the path to mysql.sock:

http://maestric.com/doc/mac/apache_php_mysql_snow_leopard

pdo_mysql.default_socket=/tmp/mysql.sock
mysql.default_socket = /tmp/mysql.sock
mysqli.default_socket = /tmp/mysql.sock

This particular item was difficult to track down. Most of the advice that I found re: Snow Leopard upgrades was to make sure this setting was in place:

mysql.default_port = 3306

Once the socket path was set up, it was a simple matter to run update.php and upgrade the MediaWiki schema, dump and re-import the data from the old PPC installation of MySQL to the native Intel version, and start the wiki going again. Thanks to Chris Jones of Oracle and Johannes Schluter, ah, also now of Oracle, for the help. It was getting a bit embarrassing, when I wanted to look up something that was on the wiki, having to read through the mysqldump file!

Follow me on Twitter

I'm Max Webster there.

Ten Years Gone

I've been pretty quiet lately, because I'm in a transitional period. After 10 years on documentation for Oracle Database and other enterprise server products, I'm switching to the InnoDB group that already works with MySQL. New development environments, new customers, it's an exciting time!

A decade seems to be the right timeframe for me. It was 10 years at IBM before that. Check back in 2019, I'm sure there'll be something new then too.

Deconstructing "Everything is UNIX"

From Linux magazine, an article by Jeremy Zawodny: Everything is UNIX.

For me, this is an example of the "Miller meme" from Repo Man. "Suppose you're thinkin' about a plate o' shrimp. Suddenly someone'll say, like, "plate," or "shrimp," or "plate o' shrimp" out of the blue, no explanation." You go through life thinking you'll find something better than UNIX. The man pages still have the same bad examples and incomplete option descriptions as in 1984. The window systems aren't up to snuff for desktop use. People are still finding performance bottlenecks due to system architecture, whichever architecture your favourite UNIX flavour uses.

And then you realize, there really is not much more. Everywhere you look, the same point is reinforced over and over. Windows Vista comes along, OS X is built on a UNIX base, and you get past your long-standing resentment about X11. You read the documentation for the latest and greatest web framework, and you think to yourself, this isn't any more usable than those thrown-together man pages. Regardless of the fact that no system incorporates every possible performance boost, the systems taking advantage of the latest and greatest hardware are often descendants of UNIX; and if you're looking for mainframe-style reliability, again you're probably tinkering around in the UNIX space.

I find it interesting that UC Berkeley interrupts their Scheme-based intro to programming languages with an interlude to consider UNIX shell scripting. If the problem by combining a set of well-understood and reliable steps, don't reinvent the wheel. That's a good lesson for people developing frameworks and class libraries.

In the sample Google interview questions floating around the web, one recurring theme I've noticed is how to solve a fairly large-scale problem involving string and file manipulation. To which the obvious answer, IMHO, is to use the basic UNIX commands up to and including simple Perl scripts. For something quick and general-purpose, you're not going to implement a better string-finding algorithm than grep or a better simple sort than sort. Or a better I/O pipeline than UNIX pipes, or better filesystem traversal than find, or a better use of a huge memory space than a simple Perl hash. If you're writing complicated custom code, you'd better be solving harder problems than those.

(Hat tip to Tim Bray for the article.)

The Humble PL/SQL Dot

Like many other languages, PL/SQL has its own "dot notation". If we assume that most people can intuit or easily look up things like the syntax for '''IF/THEN/ELSIF''', that means that first-timer users might quickly run into dots and want to understand their significance.

The authoritative docs on the dots is in the Oracle Database 11g PL/SQL Language Reference, in particular Appendix B, How PL/SQL Resolves Identifier Names. As we can see from these index entries, the subject is mentioned here and there throughout the manual:

dot notation, 1.2.5.2, B.2
for collection methods, 5.10
for global variables, 4.3.8.3
for package contents, 10.5

When I was in charge of the PL/SQL docs, I rewrote that Appendix B to try and make it more helpful, to give more examples and state what kinds of problems you could avoid by knowing this information.

Today, as a PL/SQL programmer, I would go even farther in simplifying the conceptual information in plain English, and positioning the knowledge as important for troubleshooting. Something like...

Names that use PL/SQL dot notation can have many different meanings, such as a procedure inside a package, a column inside a table, or an object owned by another schema. In code that you write or inherit, you might use one of these idioms extensively. Which can make for a nasty surprise when your code stops working because someone creates a new table, schema, package, etc. with the same name as one of your dotted names. So, read that Appendix B to understand all the variations and the precedence rules.

Some additional tips I'll pass on where you can add or remove dots to get out of trouble:

If you are coming from Perl, where you use an expression like string1 . string2 to concatenate, the corresponding PL/SQL expression is string1 || string2.

When you want to refer to two items in different scopes that happen to have the same name, use dot notation for one of the references. For example, if your PL/SQL procedure accepts a parameter ID and then queries a table that has a column ID, the query won't work properly when you use a WHERE clause like WHERE id = id. Instead, write it as WHERE id = procedure_name.id.

If you are writing code for use with the PL/SQL web toolkit, you'll find the URLs are simpler and cleaner if you can keep dots out of them. That means using standalone procedures where you might normally use a packaged procedure, because the package notation would put the whole package.procedure name in the URL. You can get the best of both worlds by coding up the package as usual, then making a standalone procedure that simply called the packaged procedure.

vi, Still Relevant

I thought this was a good summary of why vi (or more accurately vim) is still a good choice for editing today:

Why, oh WHY, do those #?@! nutheads use vi?

One trick I learned from this article that I hadn't known: keep the cursor on the same line, but position that line at the top, middle, or bottom of the script via 'zt', 'zz', and 'zb' respectively. I am always ending up with the cursor at the bottom of the screen while runnning macros, and I want to look ahead to the next N lines, but all the other movement commands like Ctrl-d, H/M/L, etc. actually move the cursor. zt is a fast way to tell how many more times you'll want to run the same macro, for macros that process the current line and then move down one.

The Humble PL/SQL Exception (Part 1a) - The Structure of Stored Subprograms

As I said in my previous post, The Humble PL/SQL Exception (Part 1) - The Disappearing RETURN, there are a lot of nuances surrounding exception handling. That post attracted some comments that I thought deserved a followup post rather than just another comment in response.

oraclenerd said (excerpted):

I'm going to have to disagree with you on the internal procedure (in the declaration section) paradigm. What about testing?
...
Now you have 2000 lines in your declaration section and 400 or so in the body. (I've seen it...really, I've seen it). Then you want to change one of those internal procedures...you can't test it without testing the entire thing.

This is to me one of the conundrums with any programming language; PL/SQL just has its own unique aspects.

Any set of procedures can be a testing challenge, since you can think of calling a procedure saying "do this", whereas you can think of calling a function as asking "tell me what would happen if you did this". Sure, it's easier to test a function, because you just have an in-memory return value that you can compare against some expected value, and don't have to worry about changing data by accident or doing rollbacks.

In the situations where I would use the technique of lifting code directly into internal procedures, the code is typically very short and easily verifiable: select count(*) into... followed by an IF test; a sequence of simple assignments that would be cumbersome to turn into one function per assignment; blocks of code that have already been verified in toto by virtue of tests run against the outermost procedure or function; that sort of thing.

After the code is modularized this way:

• The outermost procedure can often be improved and/or optimized simply by reordering the inner procedure calls. For example, to do all the "should I quit early" tests before doing any substantial work. Or to put "set up complicated string value" right next to "use that string value".


• Debugging steps such as removing blocks of code can be performed by commenting out single lines. (Much appreciated if those blocks of code themselves contain multi-line /* */ comment blocks.)


• If a logic problem does turn up in the procedure, it's easier to figure out the part to look at if it has its own descriptive name. And a tool like ctags makes it handy to jump directly to that inner procedure.

The problem with a 2000-line declaration section, to me, is no worse than if you go the package route and must fix syntax errors or track down logic errors within a big package body. I use my favorite SQL*Plus hack to push that code off into separate files once it gets too big.

Anyway, my point is not to advocate using this kind of structure for every procedure or even every big procedure, rather to suggest that if you do restructure a procedure this way, using exceptions instead of return can make the work a little simpler, which is not a bad starting point if you are trying to get your head around how the flow control works for exceptions.

---

Brian Tkatch said:

Personally, i have done this (with a PACKAGE though) by RETURNing a value from the PROCEDURE, and then checking it in the main code:

IF Some_check() = 1 THEN RETURN; END IF;

It's not as pretty, but i found it to workout nicely.

Sure, I'm a big fan of function-oriented design; I think it's been sadly overlooked in the rush to make everything object-oriented.

Restructuring the early tests into functions does take a little work. And it requires some design decisions -- use Boolean values, 0/1, or named constants? how to ensure all cases are handled, maybe use the case statement? OK, I made my function return Boolean values and tested the values inside a case statement; but I can't test the function values via SQL queries, and maybe case will throw a runtime exception if some unexpected value like null comes back. The sample code in Brian's comment doesn't suffer from those problems, but this is the kind of thing I could imagine a junior programmer coding too elaborately.

That's not to say that the resulting code is any better or worse, just that it's now subject to potential new bugs and maintenance (have to document return values etc.) by introducing functions. That's the idea behind the use of exceptions in my previous post, to make the restructured code 100% the same as the original, not 99 44/100ths %.

The Humble PL/SQL Exception (Part 1) - The Disappearing RETURN

Exception handling in PL/SQL is a big subject, with a lot of nuances. Still, you have to start somewhere. Let's take one simple use case for exceptions, and see if it leads to some thoughts about best practices. (Hopefully, this is not the last post in this particular series.)

One common pattern I find in PL/SQL procedures is a series of tests early on...

if not_supposed_to_even_be_here() then
 return;
end if;

if no_data_to_process() then
 return;
end if;

if no_parameters_passed() then
 print_basic_page();
 return;
end if;
...

In real life, these tests tend to use hardcoded constants, queries, etc. that clutter up the procedure and make it hard to follow, rather than descriptive names as in the example above. One simple solution is to move the whole block into its own inner procedure:

check_if_supposed_to_be_here();
check_theres_data_to_process();
check_parameters_were_passed();

These procedures, declared with the procedure ... is ... syntax immediately before the begin of the main procedure, can access all the variables from the main procedure, so they typically don't require parameters. In most cases, you can just lift a block of confusing code from the main procedure, and turn it into an inner procedure with a descriptive name.

However, the return statement complicates things. When transplanted into an inner procedure, it loses its mojo. Instead of cutting short the entire procedure, it becomes essentially a no-op, because now it's at the end of a short inner procedure that was about to return anyway, back to the middle of the main procedure. The solution is to use an exception, which requires structuring the whole business like so:

create or replace procedure big_procedure as
  num_rows number;
  exception skip_normal_processing;
  procedure check_data_to_process is
    select count(*) into num_rows from data_table;
    if num_rows = 0 then
      raise skip_normal_processing;
    end if;
  end;
begin
  check_data_to_process();
  ...do all the normal stuff if there really is data to process...
exception
  when skip_normal_processing then null;
end;
/

Now if you detect some condition that means the procedure should bail out, it really will. If you can anticipate that your procedures might get lengthy enough to benefit from using inner procedures this way, you can plan ahead by using exceptions right from the start, instead of starting with return statements and then turning them into exceptions when you restructure the original straight-line procedure.

One thing that still bothers me is the way the control flow jumps around. The calls to the inner procedures jump backwards, and if any "stop! now!" conditions are triggered, control jumps forward all the way to the end of the main procedure. When I visualize such a structure, it reminds me just a little of spaghetti code. I know that on paper, all is as it should be -- all the reusable / modular code is separated out at the front, all the error handling and termination code is separated out at the end. I just would like to see more real-life cases where such structure saves on maintenance and debugging time, before passing final judgment.