Sam Wilson's Journal

In which I try to implement column sorting in WebDB, but end up building a general system for persisting query string variables between HTTP requests. Sort of.

In the old, Zend-based, WebDB, column sort order was passed as a $_GET parameter and then stored in per-table variables in $_SESSION. In the index controller, both were checked when a table was loaded:

/* application/controllers/IndexController.php */
$orderby = ($this->_hasParam('orderby')) ? $this->_getParam('orderby') : $_SESSION['ordering'][$this->tableName]['orderby'];
$orderdir = ($this->_hasParam('orderdir')) ? $this->_getParam('orderdir') : $_SESSION['ordering'][$this->tableName]['orderdir'];

The advantage, of course, being that a user could sort by a column, go off somewhere else (into a record to edit it, say), and come back to the table view and have the previous sort order be remembered. Good. But annoying, too, because a table could be sorted without a sort directive being present in the URI. Not good, because the URI doesn’t reflect exactly the resource that one is looking at; one can’t bookmark that page, for instance.

We need the best of both:

1. remembering the users’ sort preferences between requests; and
2. ensuring that the current URI is an accurate identifier for the current resource.

The obvious answer seems to be to store sort directives in the session, and redirect requests to a proper, complete, URI if the current one doesn’t already contain those directives. Sounds good.

However, before jumping in and coding that: the same ideas apply to the filter variables, which are currently passed as $_GET parameters. So could we generalize this idea of storing parameters in $_SESSION but only ever using them from $_GET? (Oh, and I should point out that the validation of these parameters happens after this, and we’re not worried about that right now.) Can this be done in a way so that the rest of the application doesn’t need to know anything about the session stuff, and can just use $_GET['foo'] and href="?foo=bar" in whatever way it wants?

A first draft:

// To be called from Controller_WeBDB::before()
if (count($_GET)>0)
{
	$_SESSION['qs'] = $_GET;
}
elseif (isset($_SESSION['qs']) && count($_SESSION['qs'])>0)
{
	$query = URL::query($_SESSION['qs']);
	$_SESSION['qs'] = array();
	$uri = URL::base(FALSE, TRUE).$this->request->uri.$query;
	$this->request->redirect($uri);
}

Which sort of works, except that it’s not possible to specify only some of the parameters (i.e. either load them all from $_GET or all from $_SESSION, which isn’t what we want).

A final draft (a new method in Controller_WebDB, called from Controller_WebDB::before()):

/**
 * Save and load query string (i.e. `$_GET`) variables from the `$_SESSION`.
 * The idea is to carry query string variables between requests, even
 * when those variables have been omitted in the URI.
 *
 * 1. If a request has query string parameters, they are saved to
 *    `$_SESSION['qs']`, merging with whatever is already there.
 * 2. If there are parameters saved in `$_SESSION['qs']`, and if they're
 *    not already in the query string, add them and redirect the request to
 *    the resulting URI.
 *
 * @return void
 */
private function _query_string_session()
{
	// Save the query string, adding to what's already saved.
	if (count($_GET)>0)
	{
		$existing_saved = (isset($_SESSION['qs'])) ? $_SESSION['qs'] : array();
		$_SESSION['qs'] = array_merge($existing_saved, $_GET);
	}
 
	// Load query string variables, unless they're already present.
	if (isset($_SESSION['qs']) && count($_SESSION['qs'])>0)
	{
		$has_new = FALSE; // Whether there's anything in SESSION that's not in GET
		foreach ($_SESSION['qs'] as $key=>$val)
		{
			if (!isset($_GET[$key]))
			{
				$_GET[$key] = $val;
				$has_new = TRUE;
			}
		}
		if ($has_new)
		{
			$query = URL::query($_SESSION['qs']);
			$_SESSION['qs'] = array();
			$uri = URL::base(FALSE, TRUE).$this->request->uri.$query;
			$this->request->redirect($uri);
		}
	}
}

To clear a parameter, it obviously has to actually be set, and not just omitted from the URI. Is this going to be a problem?

The other glaring issue with the above code seems to be the insecurity in storing unvalidated content in $_SESSION, but as this content is only ever being fed back into the URI, I don’t think there’s too much of a problem there. Nowhere else in WebDB will use $_SESSION['qs'] — everything will only use $_GET, and be responsible for validation.

Well, I seem to have got somewhere with the issue of saving $_GET parameters between requests, but nowhere yet with what I started out with: sorting the columns! However, this is now going to be a pretty straight-forward matter of adding links to column headers that toggle ?orderby=asc etc. I’ll get to it next first thing next week.

[Keywords: columns, Kohana, redirecting, sessions, sorting, URI, WebDB, Zend] [No comments] [Permanent link]

Thanks to the wonderful people on Stackoverflow, I’m making some progress with figuring out how some geographic information is saved in a DB2 database that I’m working with.

it turns out that, rather than using the spatial functionality of DB2 (which would make sense, but considering the source of this database, I wouldn’t ask for too much), coordinate pairs are being stored as Morton codes in a 16-byte Character column.

So far, I’ve sorted out decoding the coordinate values, and can shuffle points predictably on my test map; now I’m just trying to figure out what grid that these coordinates are on. I’m fairly certain (not that I know anything about these things) that it’s somehow related to the MGA grid, because 0 east is at almost exactly 114°, which is where MGA zone 50 starts (the ‘almost’ comes from my point-and-click measurement technique).

But I don’t know where I’m going next; I’ll leave it till the morning.

* * *

My test code for this has been in PHP, and I needed something to convert big numbers between bases; hadn’t realised that the BC math library didn’t include base_convert(), so I found the one below.

/**
 * Convert an arbitrary-length number between arbitrary bases.
 * Copied, and very slightly translated, from
 * http://www.technischedaten.de/pmwiki2/pmwiki.php?n=Php.BaseConvert
 *
 * @param $value
 * @param $from_base
 * @param $to_base
 * @return string
 */
function bc_base_convert($value, $from_base, $to_base)
{
	$valid_digits = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
	if (min($from_base, $to_base) < 2)
	{
		trigger_error('Bad Format min: 2', E_USER_ERROR);
	}
	if (max($from_base, $to_base) > strlen($valid_digits))
	{
		trigger_error('Bad Format max: ' . strlen($valid_digits), E_USER_ERROR);
	}
	$dezi = '0';
	$level = 0;
	$result = '';
	$value = trim(strval($value), "\r\n\t +");
	$sign = ('-' === $value{0}) ? '-' : '';
	$value = ltrim($value, "-0");
	$len = strlen($value);
	for ($i = 0; $i < $len; $i++)
	{
		$wert = strpos($valid_digits, $value{$len - 1 - $i});
		if (FALSE === $wert)
		{
			trigger_error('Bad Char in input 1', E_USER_ERROR);
		}
		if ($wert >= $from_base)
		{
			trigger_error('Bad character in input 2', E_USER_ERROR);
		}
		$dezi = bcadd($dezi, bcmul(bcpow($from_base, $i), $wert));
	}
	if ($to_base == 10)
	{
		return $sign . $dezi; // Shortcut for base 10
	}
	while (1 !== bccomp(bcpow($to_base, $level++), $dezi));
	for ($i = $level - 2; $i >= 0; $i--)
	{
		$factor = bcpow($to_base, $i);
		$zahl = bcdiv($dezi, $factor, 0);
		$dezi = bcmod($dezi, $factor);
		$result .= $valid_digits{$zahl};
	}
	$result = empty($result) ? '0' : $result;
	return $sign . $result;
}

[Keywords: bc_base_convert, GIS, MGA, Morton codes, PHP, UTM, Work, Z-order (curve)] [No comments] [Permanent link]

For the past month or so I have been working on rebuilding my little database interface — called WebDB — as a Kohana module. It’s now ready for a first alpha release; you can find it at github.com/samwilson/kohana_webdb. I’ll be putting a demo up soon, and hopefully getting a project set up for it at dev.kohanaframework.org/projects.

This is a project that I’ve been working on, now and then, for the last four or five years. It started off as a bunch of poorly-designed PHP scripts, morphed for a little while into a PEAR-based thing called Cawitm (the ‘Constraint-Aware Web Interface For Mysql’!), then through a year’s trouble as a Zend app, and has finally found its home as a Kohana module. Hence, this ‘first’ release is v.4.0a.

There are a whole bunch of things that I’m planning on adding. Obvious things, like exporting and column sorting (actually, the code for those two is done; I’ll be adding it in the next couple of weeks) as well as some less-than-obvious ones like a calendar view of any date column, graphing (with GraphViz) self-referential tables, and a map view of any table with latitude and longitude columns. If anyone’s got any other thoughts, I’d love to hear them.

[Keywords: databases, Kohana, WebDB] [No comments] [Permanent link]

This morning I’m returning to work on some code that I haven’t touched for a few months, and I’ve been rather dreading getting back to it. I’ve forgotten all the details; never knew many of them anyway. I was working with someone else on this, and so have to get my head around their work too. And of course, all I’m trying to do is modify one little (well, constrained to one package) part of the program.

How to not only get over this being an annoying chore, but actually get excited again about the program? Start at the top, read through all the javadocs, read through the code, and as I go make the documentation better. Write the story of the program — the narrative of the code. Documentation, in my view, should not only be useful and readable and thoroughly explain the code, it should actually be interesting to read!

This is the essence, I feel, of Literate Programming. I know there was much more to Knuth’s idea than verbose code commentary, but the idea of being able to sit down and read a clear human-language expository of a programme is very powerful. The documentation that is produced is a hypertext, but the nodes of it should be complete, interesting, explanations of how the code works and why it was written the way it was.

[Keywords: documentation, Donald Knuth, javadoc, Literate Programming] [No comments] [Permanent link]