The Legend of Knifebroom

Today, I'm going to tell you the story of the sentient smoke detectors and being named Knifebroom that I created from normal household items.

It all starts when I'm suddenly shaken from my sleep by my wife, who has informed me that a smoke detector is chirping. I'm still not entire sure what reality means at this point because it's around 5:00 AM, so I attempt to determine what it means to chirp.

In short order, the second alarm begins to chirp shortly after the first. I assume now that they have attained some form of consciousness and are communicating with each other. I have now perceived that they are electronic devices, so I have now decided that the manufacturer has created some sort of artificial life form that can emote via chirps.

Meanwhile, I fashion a Knifebroom:

It's constructed with novelty duct tape from a White Elephant party last December for work, a butter knife, and our broom.

Knifebroom is very effective at opening the swinging battery compartment door to the smoke detector, but ineffective at removing the actual battery. Knifebroom's performance leaves me conflicted, and in my fugue state I consider executing Knifebroom from treason. I ultimately spare its life.

Our bed is lofted and I can reach one smoke detector. I remove its battery and determine that it is still chirping at me. This either indicates that it's connected to mains or it has a secondary power source, possibly zero-point. I accidentally unscrew it from its ceiling mount and determine that it simply runs on electricity from the ceiling. Thoughts of wrapping scissor handles in some sort of plastic to serve as an insulator while I cut it from its socket enter my mind but are urgently flushed out by my returning sense of reason.

The second smoke detector is out of reach and Knifebroom simply offers its encouragement to rectify the chirping situation. Rua and I decide to scavenge the bowels of our apartment building for a ladder or other debris to climb on. There happens to be a ladder.

WHAT DO YOU DO? TAKE LADDER

Transporting the ladder through the entire building is a five minute affair and at this point I have finally rejected the non-reality of my dream state and replace the batteries in the smoke detectors with precision. The final smoke detector lets out one last chirp as it drinks the electrons from a new battery, after which we place the ladder back in its original location and deal with what just happened.

The End?

Deploying a single page portfolio page with Wordpress and the Bootstrap Framework

We recently moved to Des Moines, Iowa and my wife has been job hunting. She's had a portfolio online using Behance ProSite, and the predefined templates are visually good. But if you really need to curate your content, they don't offer any options for real markup, and you have to really fight the admin panel to make it look good.

My wife was really stuck on the idea of a single page site with a static header for navigation to each section. If you're thinking about doing this, just use Bootstrap. Their implementation of a Scroll Spy plugin is the easiest to implement that I've seen and like most grid frameworks it eliminates all the repeated word of setting up the page as fluid, non-fluid, multiple columns, etc. It's really a bit of overkill for a simpler site like this, but still pretty effective.

Also a bit of overkill is Wordpress to managing the content. All it's really being used is for adding posts with images to query in the template. No posts are actually being used and the blog isn't even rendered.

The actual portfolio section of the page is a custom JS slider I wrote from scratch. I opted for multiple sliders using a single viewport. Sections on the right are automatically created from posts with a given category, using attached images. Images are automatically resized to fit the slider and pop out into a lightbox.

The code's pretty sloppy and the server side portions are just in the template files and functions.php. At any rate, I have it hosted at Github and it might serve as a good example of something like this for someone else.

See my wife's site at ruaarnold.com and follow her on twitter.

Hooch

I wrote a microframework for PHP a while back because other PHP frameworks are usually too big and clumsy, and using vanilla PHP is obviously an excercise in pain sooner or later.

I've decided to throw it up on Github, and called it Hooch.

Here's the basic usage:

require_once 'Twig/Autoloader.php';
require_once 'hooch.php';

Twig_Autoloader::register();
$loader = new Twig_Loader_Filesystem('templates');
$twig = new Twig_Environment($loader, array('debug' => true,
    'strict_variables' => true));

// Assuming ../ is a non-publicly accessible path,
// put your DB info there.
$config = parse_ini_file("../config.ini", false);

// Instantiate the App class. Handles path routing and such.
$app = new App(true);

// Retrieve the base URL path from the config file.
// You must set this if you want anything to work at all.
// basePath should be equal to the base path of the URL.
// For example, if it's http://127.0.0.1/~test/, set basePath to
// /~test/
$app->basePath = $config['basePath'];

// These are things we will use in every template, more or less.
$twig->addGlobal('basePath', $basePath);

// Use a Preprocessor to implement authentication, etc.
// This is a bad example, but you get it. You're smart.
class AuthProcessor extends Preprocessor {
    public function test($path) {
        global $authenticated;
        return $authenticated;
    }
}
// If you're not using a preprocessor, omit this line.
$app->preprocess(new AuthProcessor());


// Handle the home page. True anonymous functions require
// PHP 5.3.0 and above.

$app->get('/', function($args) use ($twig) {
    $template = $twig->loadTemplate('home.html');
    return $template->render(array('someVal' => true));
});
// Look, even PHP can have moustaches!


// If you're on an older version of PHP:
function second_page($args) {
    global $twig;
    $template = $twig->loadTemplate('person.html');
    return $template->render(array('person' => $args['name']));
}

$app->get('/person/:name', second_page);

// Handle POST requests
// Here's an example of a redirect as well.
$app->post('/save', function ($args) use ($twig, $app) {
    if (isset($_POST['name'])) {
        $app->seeother('/person/' + $_POST['name']);
    } else {
        // Throw a 404.
        $app->notFound();
    }
});

// Need to return some JSON for an AJAX call?

$app->post('/saveAJAX', function ($args) use ($app) {
    return $app->apiSimple(function() use ($args) {
        return array(
            'test' => 1,
            'another-test' => 2
            );
    });
});


// And the thing that makes it all happen:
$app->serve();

You'll also need an .htaccess to redirect everything to your index.php file. This one works:

RewriteEngine on
RewriteBase /
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule . index.php [L]

typeto.me

Daniel Drucker insisted that there should be a web version of talk, so we made something like it. typeto.me is built with Node.js, Coffeescript, socket.io, and works in most modern browsers.

Daniel also posted it to Hacker News, so go vote it up!

dqprintf

Part of my job involves performing data transformations using tables loaded via CSVs, which themselves were created from tab-delimited reports. Nasty.

Making something usable out of these involves a nice handful of PL/PGSQL functions. These functions involve arguments specifying table names and other values that are used to build dynamic queries that either perform INSERT ... SELECT FROM, or loop through result sets because the reports require some amount of contextual processing before performing an INSERT. Blech.

The two main functions that make this all possible are quote_ident and quote_literal. They ensure you're not just blindly concatenating illegal identifier names into your queries. Please don't just type in " || table_name || ". It hurts.

Unfortunately, your function will inevitably end up looking a little like this:

iQuery := $Q$
    SELECT
        $Q$ || quote_ident(some_column) || $Q$ || $Q$ ||
            quote_literal(some_value) || $Q$ || '-1'
    FROM $Q$ || quote_ident(some_table) || $Q$
    WHERE another_column = $Q$ || quote_literal(another_value) || $Q$
$Q$;

FOR mRow IN EXECUTE iQuery
LOOP
...

Gross, but this isn't a new problem, so here's a simple solution.

I adapted this function a bit to make dqprintf, a simple sprintf-like function to create dynamic queries:

CREATE OR REPLACE FUNCTION dqprintf(fmt text, VARIADIC args anyarray)
RETURNS text
LANGUAGE PLPGSQL AS $$
DECLARE
        argcnt int = 1;
        chrcnt int = 0;
        fmtlen int;
        CHR text;
        NEXT_CHR text;
        output text = '';
        curarg text;

BEGIN
        fmtlen = LENGTH(fmt);
        LOOP
                chrcnt = chrcnt + 1;

                -- ran out of format string? bail out
                IF chrcnt > fmtlen THEN
                        EXIT;
                END IF;

                -- grab our char
                CHR = substring(fmt, chrcnt, 1);
                NEXT_CHR = substring(fmt, chrcnt + 1, 1);

                -- %% means output a single %, and skip them
                IF CHR = '%' AND substring(fmt, chrcnt + 1, 1) = '%' THEN
                        output = output || '%';
                        chrcnt = chrcnt + 1;
                        continue;
            END IF;

                -- %i means we're going to quote an identifier
                -- %l means we're going to quote a literal
                -- %s is anything else. Not exactly a printf format.
                IF CHR = '%' THEN
                        curarg := COALESCE(args[argcnt]::text, '');

                        IF NEXT_CHR = 'i' THEN
                            curarg := quote_ident(curarg);
                        ELSIF NEXT_CHR = 'l' THEN
                            curarg := quote_literal(curarg);
                        ELSIF NEXT_CHR <> 's' THEN
                            -- improper format identifier
                            RAISE EXCEPTION 'Incorrect format identifier: %', NEXT_CHR;
                        END IF;

                        output = output || curarg;
                        argcnt = argcnt + 1;
                        chrcnt = chrcnt + 1;
                        CONTINUE;

                END IF;

                -- no special case? output the thing
                output = output || CHR;
        END LOOP;

        RETURN output;
END;
$$;

This will turn the above pipe character mess into something a bit more manageable:

iQuery := $Q$
    SELECT
        %i || %l || '-1'
    FROM %i
    WHERE another_column = %l
$Q$;
FOR mRow IN EXECUTE dqprintf(iQuery, some_column, some_value,
    some_table, another_value)
LOOP
...

A couple of notes. First, every argument needs to be cast to the same type, preferably text. If you're using this in a PL/PGSQL function, you most likely have this covered if these are typed arguments to the function already.

Secondly, error handling isn't terribly robust, so debugging it might be clumsy. You're already used to that, though, if you're writing PL/PGSQL functions.

On DML Logging

If your shop is like ours, your devs working with PostgreSQL want to log UPDATE, INSERT and DELETE operations by users, as a tool to analyze bugs in your software or to audit user activities. Trigger functions immediately jump to mind, and they are rightfully suited to the task, but bring some baggage of their own that may give you some pause if your users or developers are sensitive to the speed of their database queries.

First things first, PL/PGSQL is great. If you absolutely can't write your function in plain SQL, use PL/PGSQL if you can. PL/Python is fun, very powerful, and if you're like me, writing Python is like a Slip 'N Slide that never ends. But for trigger functions on what could be millions of queries, it may give you the gift of a significant performance penalty that will irritate you for around a year before it finally prompts you to complain about it in writing, on the Internet.

Unfortunately for PL/PGSQL, the trigger function's row object doesn't provide any sort of data you can use to find out exactly what fields are contained within the row object. You also can't dynamically refer to these columns in the row variable. You have to infer this from the table you created the trigger on, and write your function appropriately. So, that works...if you're logging a single table. Fast forward to writing trigger functions for 40 or more tables and you see the dilemma.

So, long story made short, PL/Python trigger functions provide rows as a Dict, and it's fairly obvious what to do with it. But then there's a performance hit. It's not even so much the OLTP queries that drag us down as the data loading. Like most people dealing with real data, you have to load quite a bit from time to time, and if you're nice, you won't grab an exclusive lock on the entire table and disable triggers just so you can bring in a few years of history.

You can see where this is going. Every row inserted brings you another single INSERT to your logging table. It doesn't matter what structure you've made for storing this log data, it's going to be painful.

You could include code in the trigger function to turn around and exit if it sees a specially named session-local temporary table. This takes off some of the hit from the INSERTs. But, you're still firing off the Python interpreter 500,000 times if you're creating 500,000 records.

That leaves us with some options.

• Write the trigger function in another PL that isn't as slow. It'll probably still be slow.
• Write the trigger function in C. Without putting buckshot in your own foot.
• Write a function that writes a PL/PGSQL function. Dynamically generate a trigger function for each table that performs the appropriate INSERTS, per-column by effectively unlooping your FOR loop.

The last one is crazy enough that it might work, but I haven't even tried to test it yet.

New site

It's alive. Here's the rundown. The site's running on node.js, written in Coffeescript, served up via Apache and mod_proxy. Posts are statically generated from hybrid JSON/Markdown files.

That's it!