Links: 12-16-2007

Links: 12-12-2007

SQL: getting the count of the result of a derived table

Recording this for posterity: let’s say you’re working on a store and you’ve got a database table that stores orders and a database table that stores customers and that you want to get a count of all the customers who’ve ordered more than 10 times. You’re going to write a query that looks like this:

SELECT customerID FROM orders GROUP BY customerID HAVING COUNT(*) > 10

to get a list of all the customerID’s who’ve ordered more than ten times and a query like this:

SELECT COUNT(*) FROM (SELECT customerID FROM orders GROUP BY customerID HAVING COUNT(*) > 10) as n

to get a count of those customers. Just sayin’.

Dates, Milliseconds, Java and Firebug

In Clearspace we store dates using their millisecond representation rather than as a full fledged date in database tables because we don’t want to have to worry about how each database represents dates, but that means that whenever you’re looking at a Clearspace database table, you see values like this:


instead of something like this:

2007-03-01 23:59:59.000

which makes debugging dates a little harder. Firebug and JavaScript to the rescue! Open Firebug, click into the console area and then type this:

new Date(1172822399000);

and then click ‘run’ and voila. You’ve got the date. Have I told you how much I love Firebug?

Books: Better Off

A couple of quotes from the book “Better Off: Flipping The Switch On Technology” that I thought were important enough to note here:

… tricks like these remove much of the onus from manual labor and add to the sense of physical effort a much finer satisfaction: the magisterial feeling that comes with wielding means precisely fitted to ends. Here, perhaps, is the first of all lessions in the use of power, whether technological or physiological: trimming back the means until only the essential remain; weeding out obstructions, man-made or not, to our goals.

(page 27)

Modern technology, I suspect, far from being neutral in its effects, has more than one purpose or built-in tendency: besides reducing the need for physical effort (a kind of material surrender), it helps us avoid the need for cooperation or social flexibility (a kind of social or metaphysical surrender). All too readily it countermands the uncertainty that goes with Gelassenheit. Cars, telephones, message machines, caller ID, and e-mail grant us unprecendented powers to associate with whom we want, when we want, to the degree we want, under the terms we want, finessing and filtering out those we don’t want — and thin out the possibilities of social growth accordingly.

(page 80)

… in true leisure there is mastery. If the enemy of self-direction was passion and impulse, its ally was quiet repose, mindfulness, perceptivity. Yet the act of reflection transcended the rational; it followed a course that could not be entirely foreseen, yielding conclusions that could not be reached if too deliberately pursued.

(page 133)

For those who would outstep and outsmart machines, a broad suggestion: remember the principle of minimation. Technology undoubtedly has, and will always have, some role in making life easier or better, so one shouldn’t exclude it. But the role is supplemental. Technology serves us, not we technology. The principle of minimation can be roughly stated thus: other things equal, it is better to find a non-technological solution than a technological one, or failing that, a less technological solution than a more technological one.

(page 229)

Creating a Firefox Sidebar for Clearspace: Part II

It looks like it was almost 2 months ago that I wrote a blog post about the Clearspace plugin for Firefox (called Clearfox), promising that I would follow up with the details on the JavaScript side of the project. I guess time flies when you’re having fun.

Getting started on the JavaScript sidebar was easy. The Mozilla folks have a nice document here that shows you how to get a really simple sidebar created, but like a lot of things in software, the last 20% of the features take 80% of the time. It’s also worth noting that Firefox extensions are deployed as an XPI file, themselves nothing more than glorified zip files, so if you’re curious about how an extension (say Firebug, LiveHTTPHeaders or Bookmarks), you can download the extension, unzip it’s contents and then poke around to your hearts content. It’s just like viewing source on HTML, which is something that other browser extensions don’t offer out of the box. Here are couple things that either weren’t documented in the above document or caused me to repeatedly bang my head against the wall.

Adding Your Icon

When I originally wrote about the plug-in, a number of people asked “why?” I think one of most important things about browser plug-ins is that they bring your web application (in this case Clearspace) front and center in your customers everyday browsing experience. Front and center in Firefox means that your plug-in sits right next to the Back | Forward | Reload | Stop | Home button in the navigation bar. If you want to put a 24×24 picture of yourself in that spot, be my guest. Since the purpose of Clearfox was to get embed Clearspace in the browser, I chose (wisely I think) to use the Clearspace logo. This was one of the simpler things to accomplish. I added the following element to the overlay.xul:

<toolbox id="navigator-toolbox">
    <toolbarpalette id="BrowserToolbarPalette">
      <toolbarbutton id="clearfox-button" class="clearfoxbutton-1 chromeclass-toolbar-additional"
                     observes="viewClearfoxSidebar" />

and then in a CSS file (that you also reference in overlay.xul), I added this:

#clearfox-button {
  list-style-image: url("chrome://clearfox/skin/clearfox.png");
  -moz-image-region: rect(0px 24px 24px 0px);

Finally, you’ll need to have execute some JavaScript when Firefox loads:

var toolbox = document.getElementById("navigator-toolbox");
var toolboxDocument = toolbox.ownerDocument;
var hasClearfoxButton = false;
for (var i = 0; i < toolbox.childNodes.length; ++i) {
  var toolbar = toolbox.childNodes[i];
  if (toolbar.localName == "toolbar" && toolbar.getAttribute("customizable") == "true" ) {
    if (toolbar.currentSet.indexOf("clearfox-button") > -1)
      hasClearfoxButton = true;
if (!hasClearfoxButton) {
  for (var i = 0; i < toolbox.childNodes.length; ++i) {
    toolbar = toolbox.childNodes[i];
    if (toolbar.localName == "toolbar" &&  toolbar.getAttribute("customizable") == "true"
      && == "nav-bar") {
      var newSet = "";
      var child = toolbar.firstChild;
      while (child) {
        if (!hasClearfoxButton && ("clearfox-button" ||"urlbar-container")) {
          newSet += "clearfox-button,";
          hasClearfoxButton = true;
        newSet += + ",";
        child = child.nextSibling;
      newSet = newSet.substring(0, newSet.length-1);
      toolbar.currentSet = newSet;
      toolbar.setAttribute("currentset", newSet);
      toolboxDocument.persist(, "currentset");

The key takeaways: the ID of the toolbarbutton element is used in the CSS declaration, the list-style-image property is used to specify the image you want for the button and the observes attribute points to the ID of the broadcaster element, which is used for showing / hiding the sidebar. I'm not all that good with Fireworks / Photoshop so I didn't go the extra mile to create a separate image to show when a user mouses over the button (check out the the excellent bookmarks extension for an example), but adding a mouseover / hover image is as simple as adding another CSS property:

clearfox-button:hover {
  list-style-image: url("chrome://clearfox/skin/clearfox-hover.png");

Loading Sidebar Content

I think I mentioned this when I wrote the original post, but the thing that really got me started with the Firefox extension was looking at the source for the twitbin Firefox extension. When I checked out the source for that extension, I was stunned to learn that they were simply loading up an HTML page from their server and then refreshing the page every couple minutes using an AJAX request. I thought it must have been way more complex than that, but HTML + JavaScript + CSS with a little bit of XUL sprinkled in and you're golden. In the broadcaster element (mentioned above), you add an attribute 'oncommand' that tells Firefox what JavaScript method(s) you want invoked when the user clicks on your button; in Clearfox I use that hook to load the HTML content from Clearspace. The JavaScript looks like this:

var sidebar = top.document.getElementById("sidebar");
  Clearfox.clearfoxContentLoaded, false);


Once the content has loaded the DOMContentLoaded event is fired and then Clearfox runs the clearfoxContentLoaded method. I initially tried loading the page and then re-loading that same page every couple minutes: I was cheating and trying not have to do the AJAX part. I added a meta refresh tag to the page to have it reload every n minutes, but the DOMContentLoaded event was only fired the first time the page was loaded. Key takeaway: if you rely on DOMContentLoaded, you're only going to get the event once, even if your page reloads.

Opening New Tabs

You can run your own little application over in the sidebar if you want to, never opening new tabs or doing anything in the main window, but the main point of the Clearfox extension was to give users the ability to see new content in Clearspace and then able to view the full thread, document or blog post in their main browser window as a new tab. There's no way you can create new tabs in JavaScript outside of XUL, you have to be inside XUL to create a tab so the DOMContentLoaded event is important because this is where the links are all rewritten to open new tabs rather than work as normal links. There are two parts to the clearfoxContentLoaded method: the first rewrites all the links so that clicking on a link in the sidebar opens a new tab, the second adds listeners to the document so that when new content is added via AJAX, the same first part is repeated. The link conversion looks like this:

var sidebar = top.document.getElementById("sidebar");
var doc = sidebar.contentDocument;
var all_links = new Array();
var links = doc.evaluate("//a", doc, null, XPathResult.ANY_TYPE, null);
var link = links.iterateNext();
while (link) {
  link = links.iterateNext();
for (var i = 0; i < all_links.length; i++) {
  link = all_links[i];
  if (!link.hasAttribute('onclick') &&
    link.hasAttribute('href') &&
    (link.hasAttribute('class') && link.getAttribute('class').indexOf('tabbable') > -1)
  ) {
    var target = link.getAttribute('href');
    link.setAttribute('onclick', 'return false;');
    link.addEventListener('click', Clearfox.clearfoxCreateOpenFunction(target), true);

In English, get all the links that don't have an onclick attribute and add an onclick event, whose callback creates a new tab:

clearfoxCreateOpenFunction: function(url) {
  return function() {
    gBrowser.selectedTab = gBrowser.addTab(url);

The second part of the clearfoxContentLoaded method listens for any modifications (anytime something is added to the document via an AJAX call, I remove the last n rows, so in this case I listen for the removal of nodes) to the document and adds handlers for the modification event:

var sidebar = top.document.getElementById("sidebar");
var table = sidebar.contentDocument.getElementById("clearfox-hidden-content");
table.addEventListener("DOMNodeRemoved", Clearfox.clearfoxConvertLinks, false);


Two things about debugging your extension: a) none of the tools you've got installed for debugging JavaScript / CSS (cough! Firebug cough!) will work in the sidebar window and b) your only other option is to resort to the Firefox equivalent of Java's System.out.println:

function logClearfoxMsg(message) {
  var consoleService = Components.classes[";1"].getService(Components.interfaces.nsIConsoleService);
  consoleService.logStringMessage("Clearfox: " + message);

and then to log a message:

logClearfoxMsg('your advertisement here');

To see the log message, go to Tools --> Error Console.

Showing The Sidebar When Firefox Opens

I spent way more time than I should have working on this last part: when you first install the extension, you're asked to restart Firefox, which you do and then you see the nice Clearspace button, you click on it and the sidebar opens and you're happy. Then a couple days later when you need to restart Firefox again (for whatever reason), you'll notice that the sidebar is open but that no content shows and you're sad. You probably didn't take it personally, but I did and I wanted to figure out why Firefox wouldn't load up the content if the sidebar was already open. There actually is onload attribute that I tried using in the sidebar.xul and that didn't work for reasons I won't get into here. What ended up doing the trick for me (and I really think it's a trick but I couldn't get anything else to work and this was just about the last option I had) was to check to see if the sidebar was open when Firefox was loading:

var broadcaster = top.document.getElementById('viewClearfoxSidebar');
  if (broadcaster.hasAttribute('checked')) {

and then, if it is open, *forcing* the sidebar to open again:

toggleSidebar('viewClearfoxSidebar', true);

For whatever reason, this was the only way I could get the rest of my code to work, but work it did. And now I'm happy.

And I hope you are too. If you have any questions about creating a Firefox sidebar, shoot me an email, I'll be glad to help. If you want to see all the code, you can download the Clearfox source over on the Jive Software Community site. All the JavaScript / XUL code I discussed in this post is in the 'xpi' directory off the root.