talkingCode

Bounding box drag-select for tables; Javascript, Prototype

*sigh*. I had a table, a lovely table. I wanted to be able to drag a bounding box around rows in the table. Looking around, there didn’t seem to be much by way of existing support, so I wrote this fine bounding box library. Well, it’s not actually that fine, but it does at least work in a cross-browser way. (Tested in FF/Linux, IE6/Linux, Opera/Linux, IE7/Win – IE7 has some minor alignment issues). I’m using it on tables here, but it’ll work (as far as I know) for any element.

The magic code to do what you see there is (with apologies for the fromCharCode: Javascript + WordPress = Death):

<script type="text/javascript" src="/script/prototype.js"></script>
<script type="text/javascript" src="/script/boundingbox.js"></script>
<script>
  // Button actions
  function doStuff()
  {
    var b = new BoundingBox();
    clearCells();
    updateLabel("Click and drag to select items");
    b.startSelection(releaseCallback);
  }

  function doStuff2()
  {
    var b = new BoundingBox();
    clearCells();
    updateLabel("Click mouse on canvas to start selection");
    b.startTwoClickSelection(firstClickCallback, releaseCallback);
  };

  // Callbacks
  function firstClickCallback()
  {
    updateLabel("Click mouse on canvas again to stop selection");
  };

  function releaseCallback(selected)
  {
    for (var i=0; i<selected.length; i++)
    {
      $(selected[i]).setStyle({
         background: "#FF9999"
       });
    }
    updateLabel("Click a button to begin selection");
  };

  // Utility functions:
  function clearCells()
  {
    $$("td[bandable]").each(function (data) {
      data.setStyle({
        background: "#99FF99"
    })});
  }

  function updateLabel(text)
  {
    var o = String.fromCharCode(60);
    var c = String.fromCharCode(62);
    var label = o + "label" + c;
    label += text;
    label += o + "/label" + c;
    $("label_div").firstDescendant().replace(label);
  };
</script>
<input onclick="doStuff()" type="button" value="Drag-Select Mode" />
<input onclick="doStuff2()" type="button" value="2-Click Mode" />

Note that the callback function is invoked with an array of the selected elements’ ids when the mouse has been released. I’ve hooked this up to change the element colours in the little script, but you could equally:

  function releaseCallback(selected)
  {
    new Ajax.Request('/some/form.php', { method: 'post',
                            parameters: {'ids[]':selected} });
  }

if you wanted to send the data back to a form somewhere.

This code is still a bit rough around the edges – YMMV. Props, as ever, to the good people at Prototype without whose magic this wouldn’t work.

Import into MySQL from CSV/Excel, Ruby

You have some data in a CSV file (or a spreadsheet that you’ve dumped to CSV) that you’d like to load into a MySQL database. Nothing very interesting to say about this except that faffing looking up both sets of docs is tedious for what are quite simple bits of code and a fairly occasional task. (If you spend a lot of your time dealing with CSV / Excel files, you’ve probably made some bad decisions in life, but at least you’ll know this by heart )

The source file is a list of resistor values – column headings ‘Value’ (read product code), ‘Resistor A’, ‘Resistor B’, ‘Resistor C’. The CSV looks something like:

0,1.1,27,180
7,4.7,22,47
21,2.2,18,4.7

The table looks something like:

CREATE TABLE resistor_configs (
  id INT AUTO_INCREMENT,
  value INT,
  resistor_a FLOAT,
  resistor_b FLOAT,
  resistor_c FLOAT,
  PRIMARY KEY  (id)
) DEFAULT CHARSET=utf8

…and the Ruby runs as follows:

#!/usr/bin/ruby
#
require 'csv'
require 'mysql'

my = Mysql::init()
# You can do any SSL stuff before the real_connect
# args: hostname, username, password, database
my.real_connect("localhost", "root", "", "products_development")

my.query("DELETE FROM resistor_configs")

CSV.open('/tmp/resistors.csv', 'r') do |row|
  # No escaping here, because I trust the input file. You may not
  my.query("INSERT INTO resistor_configs" +
               "(value, resistor_a, resistor_b, resistor_c)" +
               "VALUES (#{row[0]}, #{row[1]}," +
               "#{row[2]}, #{row[3]})")
end

There you have it. Nothing very clever, but easier to copy and paste than to read the fine manual.

Getting started with Haskell

All the cool kids, it seems, are using Haskell these days. Or at least, the two or three people with whom I talk about software on a regular basis. Functional programming is fun. It makes you think a little differently about the world, and serves as a decent substitute for a unicorn chaser if you’ve been writing PHP all day.

I remember almost the first snippet of code we saw on our computer science course was the following to count the number of items in a list:

count [] = 0
count (x:xs) = 1 + count(xs)

which, for a guy who’d only ever really written code in BASIC before starting the course, was a bit of an eye opener. I think it was probably three or four lectures in before we had to work out the code for printing out all the permutations of a list:

> permute [1,2,3]
[[1,2,3],[2,1,3],[2,3,1],[1,3,2],[3,1,2],[3,2,1]]

After much blood, sweat, and tears we’d eventually come to the realisation that:

insert x [] = [[x]]
insert x (y:ys) = (x:y:ys) : map (\z -> y:z) (insert x ys)

permute [] = [[]]
permute (x:xs) = concat (map (insert x) (permute xs))

would suffice (where concat flattens a list and map has the usual meaning). At first glance, it all looks pretty daunting, but after a while one comes to understand what it means for the type of map to be (a -> b) -> [a] -> [b], and starts to appreciate the compact elegance of the code that’s produced in functional programming.

Ugly implementation details? You betcha…

apt-get install hugs
apt-get install haskell98-tutorial
cat > hello.hs <<END
#!/usr/bin/runhugs +l
main :: IO()
main = putStr "Hello World!\n"
END
chmod 755 hello.hs
./hello.hs

(N.B. hugs with the ‘+l’ switch is sensitive about filenames. Non ‘.hs’ files require lines of Haskell to begin ‘>’). It’s often more comfortable to develop these things interactively. Booting into a hugs session and typing

> :load hello.hs

will import the functions defined in hello.hs into your session.

Over the coming weeks, I hope to be honing my skills and infuriating my coworkers by replacing bits of critical infrastructure with Haskell scripts. I’ll let you know how that goes.

Byte-packing / reading binary data in Ruby

A lot of high-level languages don’t have wonderful native support for bit-level manipulation of data, so when you find yourself having to implement a proprietary wire protocol or parse a custom file header, you often feel a little lost. Fortunately you’re not the first person to feel lost, and some kind person has, by and large, already solved the problem for you. In Java, for example, there’s the fine commons.io library (in fact, all the commons libraries are pretty fine), and in Ruby you have BitStruct:

# Syntax here is
#    parsed-datatype
#    :symbol_name for the parsed data
#    field size in bits
#    comment
class BinaryHeader < BitStruct
  default_options :endian => :native
  char     :id, 4*8, "File ID"
  char     :format_string, 4*8, "Format String"
  unsigned :remainder, 32, "Remaining bytes"
  unsigned :trackId, 32, "Track ID"
  unsigned :formatId, 32, "Format ID"
  unsigned :codecId, 32, "Codec ID"
  unsigned :major, 16, "Major Version"
  unsigned :minor, 16, "Minor Version"
  unsigned :validation, 32, "Validation Data"
  unsigned :size, 32, "File Size"
end

There are a number of ways you can find binary data on your hands. You could read directly from a file / socket, you could generate some, or you could receive it Base64 encoded from a Webservices request:

def get_header(file)
  header_bytes_base64 = @webservice.getFileHeader(file.trackId)
  header_bytes = Base64.decode64(header_bytes_base64)
  if header_bytes.size != HEADER_SIZE
    puts "Invalid header size: #{header_bytes.size}"
    return nil
  end
  header = BinaryHeader.new(header_bytes)
  puts "Got Header:"
  puts header.inspect_detailed
  if header.id != "FILE"
    puts "Unknown file ID: #{id}"
    return nil
  end
  if header.format_string != "MP3X"
    puts "Unknown file format: #{format}"
    return nil
  end
  return header
end

Problem solved.

JSON “RPC” from Perl to Ruby / Webrick

So I have a bunch of software I’d written in Ruby because it was “The Right Choice™” and I need to make it talk to a stack of software I’d written in Perl (because “I Was An Idiot™”). Specifically, I need the Perl to be able to call the Ruby. I had a quick dig around, and there’s a Perl module Inline::Ruby which, on the face of it, would do the job. Unfortunately, Inline::Ruby is version 0.0.2 software and “Doesn’t Work So Good™”. Not only that, but I’d really like some persistence in the Ruby code so that I don’t have to new up the state every time I make a call. What this calls for, then, is some IPC

Linux IPC comes in three varieties – Sockets, Files, and Shared Memory. Files is obviously a pretty poor idea in that you’ll either be polling a lot or writing nasty dnotify stuff. Shared Memory is okay but extremely unportable. Sockets are a pretty good idea, and if you choose an IP socket you get the advantage that you can run the communicating processes on different machines (assuming they don’t need to share other local resources).

So you’ve selected TCP/IP as a transport, but you’ve then got all sorts of irritating high-level protocol implementation to do. Unless…

#!/usr/bin/ruby
# A Simple Webserver for JSON RPC

require 'webrick'
require 'json'
require 'yaml'
include WEBrick
include YAML

server = HTTPServer.new(:Port => 8000)
server.mount_proc("/rhapsodise") do |request, response|
  response['Content-Type'] = "application/json"
  response.body = handle_json_request(request)
end

trap("INT") { server.shutdown }

def handle_json_request(request)
  object = JSON.parse(request.body)
  YAML.dump(object)
end

server.start

and then…

#!/usr/bin/perl -w
#
# A Simple Perl Client for JSON RPC

use strict;
use JSON;
use LWP;

my $actionurl = "http://localhost:8000/rhapsodise";

my $ua = LWP::UserAgent->new();
$ua->agent("JSONClient/0.1");

my $object = { test => "fish",
               wibble => ["meep", "flange" ] };

my $json = objToJson($object);
print "$json\n";
my $req = HTTP::Request->new(POST => $actionurl);
$req->content_type('application/json');
$req->content($json);

my $res = $ua->request($req);
if ($res->is_success)
{
  print "Succeeded:\n" . $res->content;
  my $result = jsonToObj($res->content);
}
else
{
  print "Failed\n";
}

… et voila. For less code than it might cost you to bind the socket in C(!) you’ve got a nice, portable way of making Ruby calls from your Perl through the Webrick webserver. Even better, you get to use Perl and JSON, as exhorted in my previous post. A dash more code for the server, and it’s very nearly useful:

def error_object(message)
  return { :error => message }.to_json
end

def handle_json_request(request)
  command = JSON.parse(request.body)
  if command["method"] == nil
    return error_object("No method suppled")
  end
  if command["method"] =~ /^rd_/
    return self.send(command["method"], command)
  end
  return error_object("No matching method")
end

def method_missing(m, *args)
  return error_object("Invalid command: #{m}")
end

Notice that I’m prefixing my RPC-able methods with ‘rd_’. This probably isn’t much more secure than not bothering, but is a useful kind of Hungarian Notation for the methods. I’m not worrying too much about security on this one since I write both ends and I trust the link across which the packets run – you’d need to take appropriate precautions if that wasn’t true.

Perl and JSON: heaven =~ m/made/ ?

If you read the above as ‘Heaven Made’, I’m afraid you lose code Dingbats. Try harder.

Perl is a nice language and JSON is a good way to structure data if you’re about to send over your favourite IPC mechanism. And there are, it seems, many similarities between the way JSON structures data and the way Perl structures data. Unfortunately, I don’t have an awful lot to write on the topic, so as well as briefly introducing the basics of creating JSON in Perl I will, with the irrefutable logic of a professor of philosophy and all the religious sensitivity of a Dan Brown novel, be examining the evidence for Larry Wall being God.

We’re going to start with a motivating example. I want to represent the following JSON object in Perl:

{
  keywords:['cat', 'fish', 'pig'],
  somekey: 'somevalue',
  someobject: {
    nested: 'value',
    another: 'nestedvalue',
    a_number: 4
  }
  simple:true
}

I don’t know if you’ve come across JSON before, but it’s basically the way Javascript (ECMAScript) represents objects. Your modelling tools there are strings, numbers and booleans, as you might expect, and some collecting constructs. Specifically, JSON has a way to associate string keys with values – within the ‘{}’, thing on the left of the ‘:’ is the key and the thing on the right is the value, with commas seperating key-value pairs – and a way to create lists of values – inside the ‘[]‘, again seperated by commas.

Now, if you’re familiar with Hashes and Lists (or Arrays), you’ll see that JSON’s collection structures are exactly those. How do we represent those in Perl? Well, we use ‘{}’ for a hash and ‘[]‘ for a list. What’s more, we use commas to separate are values. I know what you’re thinking – “It’s mere coincidence”. The kind of coincidence that sees the Earth just the right temperature to support life, as a function of its being in just the right place in our solar system? Yes. _That_ kind of mere coincidence.

So how, naively, would I go about representing the above in Perl?

my $object = {
  keywords => ['cat', 'fish', 'pig'],
  somekey => 'somevalue',
  someobject => {
    nested => 'value',
    another => 'nestedvalue',
    a_number => 4
  }
  simple => true
};

But all I’ve done so far is replace the ‘:’ with ‘=>’. It can’t be that simple, can it? That would be too easy. Almost as though, at the start of the universe, someone had determined that JSON and Perl would have basically the same syntax. Could it be that this synergy is part of God’s great plan? Well, not quite. Turns out you can’t just whack ‘true’ in there for your boolean value. I’ve defined a little function here to return ‘true’:

sub json_true()
{
  return bless ( {value => "true"}, "JSON::NotString" );
}

That’s ugly as sin, I know. (AS SIN! And it’s BLESSED! This conspiracy writes itself.) Anyway. Why would a God, in designing this synergy, allow such evil? Here I distract you by shouting “Look out! Earthquake!”, say “Natural Evil” three times, and we sweep that under the carpet.

The only piece missing from this puzzle are the final bits of fairy dust that’ll actually let you convert JSON to a format suitable for sending over the wire. May I introduce:

use JSON;

my $obj = { test => 'object' };
my $json_string = objToJson($obj);
my $rebuilt_object = jsonToObj($json_string);

Simple as. But… is Larry Wall God? Could anyone but the supreme being have designed this happy coincidence into the starting conditions of the universe? That a humble pre-WWW scripting language would turn out to have approximately similar syntax to one of the high-level interchange protocols powering the modern web? Well, if Larry Wall were God I’d certainly be disappointed, and that’s to say nothing of the fuss that world religions would kick up. And, in fairness, this isn’t a coincidence that has to have been orchestrated from the dawn of the universe. You’d really only need to start thinking about it in… say… 1962.

Maybe Larry isn’t God. Maybe he’s just Al Gore.

Drag and Drop table rows with AJAX, Scriptaculous and Prototype (Part II)

Last time, we saw how to render the UI for drag and drop table rows. In this post we’ll have a look at making the reordering that the user selects persistent, so that when they come back to the page (having cleared their cookies – this won’t be some session[] hack) they see the ordering they selected. What’s more, the Ajax stuff even works in Internet Explorer this time (assuming you’re using lists, not tables). If you’ve already worked it out, please stop reading now

I said last time that

new Ajax.Request('/some/action/url',
    { method:'post', parameters:Sortable.serialize('table1') })

was the answer. To explain why, we’ll need a bit of preamble. A good general strategy for writing software when you’re not sure what you’re doing is to assume that whatever you’re trying to write has already been written. In this case, we know that we need to have the order of the rows stored in the database, so lets assume I have a database table from which I’m generating my entries:

CREATE TABLE my_table (
  id INT AUTO_INCREMENT,
  column1_value TEXT,
  column2_value TEXT,
  order INT,
  PRIMARY KEY (id)
);

I’m going to be generating the table using the results from the following query:

SELECT id, column1_value, column2_value
FROM my_table
ORDER BY order;

We’ll use table rows for this next bit, but you could use <li>s or whatever works for your browser. In whatever your favourite language is (let’s pretend you like Ruby/Rails/RHTML):

<% for row in rows %>
  <tr id="row_<%= row.id %>">
    <td><%=h row.column1_value %></td>
    <td><%=h row.column2_value %></td>
  </tr>
<% end %>

For balance, let’s pretend you like PHP too (disclosure – I don’t, nor do I really know PHP):

<?php
while ($row = mysqli_fetch_array($result))
{
?>
  <tr id="row_<?php echo $row["id"]; ?>">
    <td><?php echo $row["column1_value"]; ?></td>
    <td><?php echo $row["column2_value"]; ?></td>
  </tr>
<?php>
}
?>

So we’ve displayed the elements we want to be able reorder, sorted by the order specified in the database table. Making a call to Sortable.create as per the previous post will allow the user to reorder it, but what we really want to so is save the ordering. Sortable.create allows us to specify a callback function which will be executed every time the user changes the ordering (as soon as they release the mouse):

Sortable.create("table1", {tag:"tr", containment:["table1","table2"], onUpdate:sendUpdate})

So all we have to do is ensure that ‘sendUpdate’ sends the new order back to the user. At this point, installing FireBug is a really good idea. If you visit, for example, the previous post and enable FireBug, you can use the console tab to execute JavaScript on the page. Try the following:

>>> Sortable.serialize("table1")
"table1[]=1&table1[]=2"
>>> Sortable.serialize("table2")
"table2[]=1&table2[]=2&table2[]=3"

The Sortable.serialize function turns our rows into a string suitable for form POST / GET. You’ll notice that I gave my table rows “id”s like “row_1″ and “row_2″. If you name the tags you want sorting in the same way, the result of the Sortable.serialize call will be a POST / GET array whose elements are the “id” numbers of your sortable elements and whose order is the order that the elements appear after the user has made their change.

Prototype provides the Ajax.Request function for making XmlHttpRequests. We can use this to make our ‘sendUpdate’ function:

function sendUpdate(updatedElement)
{
  new Ajax.Request("/my/action/url.php", {
method:"post", parameters:Sortable.serialize(updatedElement.id)
})
}

Now, when the user changes the ordering, our “url.php” script will receive a POST from their browser containing an array whose name is the ID of the updated element and whose values are the rows. Back on the server side, then:

def action_function
  table1 = params[:table1]
  for i in (0..(table1.size - 1))
      t = TableRow.find(table1[i])
      t.update_attributes(:order => i)
  end
end

or, for devilment

function action_function()
{
  global $link;
  $table1 = $_POST["table1"]
  for ($i=0; $i<count($table1); $i++)
  {
    $link->query("UPDATE my_table SET order=$i WHERE id="
               . mysql_escape($link, $table1[$i]))
  }
}

Hopefully that’s all perfectly clear and mostly correct. Please let me know if not.

Drag and Drop table rows with AJAX, Scriptaculous and Prototype

* UPDATE: This’ll only work in Firefox (at time of writing. Other browsers may catch up)

Tables are like sooo last century, I know. Sometimes, though, you have data that is genuinely tabular and that you want the user to be able to reorder. I had this most recently with a tabular todo list that I was writing where I wanted the user to be able to priority order the actions by drag and drop.

Below, you’ll see four tables. You can drag items back and forth between tables 1 and 2, and between tables 3 and 4, but not 1 and 3 or 2 and 4 or 1 and 4 or 2 and 3. I’ve enabled the ‘ghosting’ effect on tables 1 and 2, but not on tables 3 and 4 – ghosting appears to screw up the layout a little once you’ve done some dragging.

Neat. The question is how, right? The answer is Sciptaculous, which is a handy Javascript library for Ajax and special effects that takes most of the misery and the having-to-know-any-javascript out of writing dynamic web pages.

<!-- Javascript Includes -->
<script type="text/javascript" src="/script/prototype.js"></script>
<script type="text/javascript" src="/script/scriptaculous.js"></script>
<!-- Standard HTML -->
<table>
<tr><td>
<p>Table 1</p>
<table padding="10" border="1" bgcolor="#aaaaff">
<tr><th>Col1</th><th>Col2</th></tr>
<tbody id="table1">
<tr id="row_1"><td>Value1Table1</td><td>Value2Table1</td></tr>
<tr id="row_2"><td>Value3Table1</td><td>Value4Table1</td></tr>
</tbody>
</table>
</td>
<td>
<p>Table 3</p>
<table border="1" bgcolor="#aaaaff">
<tr><th>Col1</th><th>Col2</th></tr>
<tbody id="table3">
<tr id="row_1"><td>Value1Table3</td><td>Value2Table3</td></tr>
<tr id="row_2"><td>Value3Table3</td><td>Value4Table3</td></tr>
</tbody>
</table>
</td>
</tr>
<tr>
<td>
<p>Table 2</p>
<table border="1" bgcolor="#aaaaff">
<tr><th>Col1</th><th>Col2</th></tr>
<tbody id="table2">
<tr id="row_1"><td>Value1Table2</td><td>Value2Table2</td></tr>
<tr id="row_2"><td>Value3Table2</td><td>Value4Table2</td></tr>
<tr id="row_3"><td>Value5Table2</td><td>Value6Table2</td></tr>
</tbody>
</table>
</td><td>
<p>Table 4</p>
<table border="1" bgcolor="#aaaaff">
<tr><th>Col1</th><th>Col2</th></tr>
<tbody id="table4">
<tr id="row_1"><td>Value1Table4</td><td>Value2Table4</td></tr>
<tr id="row_2"><td>Value3Table4</td><td>Value4Table4</td></tr>
<tr id="row_3"><td>Value5Table4</td><td>Value6Table4</td></tr>
</tbody>
</table>
</td></tr></table>
<!-- Scriptaculous Magic -->
<script>
Sortable.create("table1", { tag:"tr", containment:["table1", "table2"], ghosting:true })
Sortable.create("table2", { tag:"tr", containment:["table1", "table2"], ghosting:true })
Sortable.create("table3", { tag:"tr", containment:["table3", "table4"]})
Sortable.create("table4", { tag:"tr", containment:["table3", "table4"]})
</script>

(sed -e ‘s/</\&lt;/g;s/>/\&gt;/g’ is my new bff)

Simple, right? Only slight oddness is the use of the <tbody> tag, which is a kink of the Scriptaculous library. In the call to ‘Sortable.create’, we’ve passed the “id” of the <tbody> element in as the first parameter, identified <tr> tags as the things we want to be able to reorder with the ‘tag’ option, specified the Sortable elements between which rows can be dragged with the ‘containment’ option, and turned on the ghosting effect in the obvious way. Only gotchas are that the includes at the top have to be done in the order shown, and that the calls to Sortable.create have to appear after the element definitions (not only of the Sortable in question, but also of any elements identified in the ‘containment’ option).
But you’re thinking “this is worse than useless – when I refresh the page, the sorting resets”. And you’d be right. This is where Prototype comes in. A copy of Prototype is included when you download Scriptaculous, and it implements all the handy little Ajax bits. This post is getting a bit long now, so next time I’ll explain why:

new Ajax.Request('/some/action/url', {
method:'post', parameters:Sortable.serialize('table1')
})

is basically all you need to know to be able to persist the reordering.