Tidbits from gallery.chaostangent.com

These are some of the neater parts of gallery.chaostangent.com that don't warrant a full exploration on their own but serve the goal of making the application more streamlined. I've crafted these examples to be focused so they don't contain superfluous details like error checking, timestamp columns and the like.

Database

The gallery schema is as follows:

CREATE TABLE IF NOT EXISTS `galleries` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `left` int(10) unsigned NOT NULL default '0',
  `right` int(10) unsigned NOT NULL default '0',
  `parent` int(10) unsigned NOT NULL default '0',
  `title` tinytext NOT NULL,
  `directory` tinytext NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `parent` (`parent`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

This covers both the Modified Preorder Tree Traversal (`left` and `right` columns) model as well as the more standard hierarchical model (`parent` column). I'm still undecided as to whether indexing the `left` and `right` columns provides any benefits. Most of the queries on the gallery table involve getting the direct children of a particular node; the breadcrumb trail at the top of the page however is built using the `left` and `right` columns:

SELECT * FROM `galleries` WHERE (`left` >= ?) AND (`right` <= ?) ORDER BY `left`

Doing a multi-column index in MySQL works from the left column onwards, so for the above query, indexing on `left` and `right` would be a benefit. However when inserting and deleting nodes, queries are done singularly e.g. one for `left` and one for `right` which having an index on one and not the other may turn out to be detrimental in terms of update times. I could always do two indexes:

ALTER TABLE `galleries` ADD INDEX ( `left` , `right` ) ;
ALTER TABLE `galleries` ADD INDEX ( `right` , `left` ) ;

This runs the risk though of having a table that's more index than data. I haven't done a full benchmark of the different queries for each scenario but I would imagine only for large trees would indexing provide any tangible benefit.

The two database functions which do a lot of the heavy lifting for the gallery table are insertion and deletion:

CREATE FUNCTION `addGallery`(_parent INT, _title TEXT, _directory TEXT) RETURNS int(11)
BEGIN
  SELECT `left`, `right` INTO @pleft, @pright FROM `galleries` WHERE id = _parent LIMIT 1;
  UPDATE `galleries` SET `right` = `right` + 2 WHERE `right` > (@pright - 1);
  UPDATE `galleries` SET `left` = `left` + 2 WHERE `left` > (@pright - 1);
  INSERT INTO `galleries` (`left`, `right`, parent, title, directory) VALUES (@pright, (@pright + 1), _parent, _title, _directory);
  RETURN LAST_INSERT_ID();
END

CREATE FUNCTION `deleteGallery`(_id INT) RETURNS int(11)
BEGIN
  SELECT `left`, `right` INTO @left, @right FROM `galleries` WHERE id = _id;
  DELETE FROM `images` WHERE gallery_id = _id;
  DELETE FROM `galleries` WHERE id = _id;
  SELECT ROW_COUNT() INTO @ret;
  UPDATE `galleries` SET `right` = (`right` - 2) WHERE `right` > @right;
  UPDATE `galleries` SET `left` = (`left` - 2) WHERE `left` > @left;
  RETURN @ret;
END

I've yet to find a good way of labelling an SQL function's parameters as they usually have an identical name to columns I'm using within queries. The add function can insert a node anywhere, whereas the delete function can only consistently delete leaf nodes. One of the major drawbacks to MPTT model is that moving existing nodes about the tree or deleting subtrees is tricky as it involves either retaining a lot of data or re-keying the entire table after the operation, neither of which are ideal. There's nothing complex going on in the functions, once you get your head around how MPTT works these should become self-explanatory. The added benefit of wrapping these operations up are that they're treated as transactional which saves an extra two queries ("START TRANSACTION" and "COMMIT / ROLLBACK") if these were being done in code.

Images

One of the many great things about the Zend Framework is that the developers have managed to streamline file uploading for forms which means there's no more explicit checking of error conditions, temporary files and whatnot - getting an image from the user into the application is now relatively painless. On a pre-insert hook for an Image model I do some sanity checks (directories writeable etc.) then do a simple getimagesize() and filesize() to grab the file's important measurements. Once the database row has been inserted, I have a post-insert hook that generates all the different versions - the reason this is post rather than pre is that versions are named according to the ID of the image in the database, the uploaded image meanwhile retains its original filename wherever possible.

The image function I always seem to use is "fit to area": you have a dimension that you'd like an image to fit within and retain its original proportions:

private function fitToArea($width, $height, $target)
{
	$newWidth = $newHeight = $target;
	if($width > $height)
	{
		$newHeight = round(($height / $width) * $target);
	}
	elseif($height > $width)
	{
		$newWidth = round(($width / $height) * $target);
	}

	return array(
		0 => $newWidth, "width" => $newWidth,
		1 => $newHeight, "height" => $newHeight
	);
}

Variations of this can be made for performing "fit to width" and "fit to height" or what gallery.chaostangent.com used to do which was absolutely square thumbnails. The above could be boiled down into a couple of ternary operations but I like to keep it expanded and easy to follow.

The physical resizing of the image is done one of two ways: ImageMagick or GD. The former is the preferred method but the latter is more widely supported and is cross platform due to its nature as a PHP module rather than an external executable (N.B. I'm aware there exists an ImageMagick module for PHP but have not used it as in theory it has all the problems of GD in terms of memory usage and time-outs so when I use "ImageMagick" here, I'm referring to the command line executable). The ImageMagick command which does the work is:

convert "filename"[0] -strip -resize widthxheight -sharpen 0x1.0 -quality quality -colorspace RGB "target"

There are a few non-standard parts in there:

• -strip gets rid of superfluous information (EXIF, comments, colour profiles etc.)
• -colorspace forces the resultant image into RGB which is supported across all browsers, JPGs can also be CMYK which is a bit iffy with browser support
• -sharpen image convolution which sharpens the image, sharpening should always be done on any image reduction
• [0] this selects the first frame of what could be a multi-frame image (animated GIF or PNG), some versions of ImageMagick will force animated file type creation, overriding whatever you may have in the target if this isn't present

Doing this with GD takes a lot more code which means a lot more chances for errors to crop up. The first thing you have to do is get the image type so you can load it into GD's proprietary format, you can get this with getimagesize() then using one of the imagecreatefromx() functions. Once you have that, you have to check if the image is true colour - 8bit PNGs and GIFs use palettes which make resizing/resampling ugly:

if(!imageistruecolor($res))
{
	$tc = imagecreatetruecolor($imageInfo[0], $imageInfo[1]);
	imagecopy($tc, $res, 0, 0, 0, 0, $imageInfo[0], $imageInfo[1]);
	imagedestroy($res);

	$res = $tc;
	$tc = null;
}

I believe this was originally taken from a PHP.net comment so kudos to the original author. Once you're sure you have a true colour image:

$tRes = imagecreatetruecolor($width, $height);
imagecopyresampled($tRes, $res, 0, 0, 0, 0,
	$width, $height, $imageInfo[0], $imageInfo[1]);

This copies and resamples the image to the desired size. $width and $height are the target sizes while $imageInfo contains the original image dimensions. At this point you can output to a JPG and be done with it, however I believe in the benefits of sharpening which lamentably GD does not have an in-built function for. In comes image convolutions:

imageconvolution($tRes, array(
	array(-1,-1,-1), array(-1,20,-1), array(-1,-1,-1)
), 12, 0);

The array notion is a little annoying but essentially this applies a matrix, divisor and offset to the image (every pixel for every channel) which accents the edges making the image appear crisper. Depending on the types of images you're dealing with will define which central number and divisor you use but I recommend playing with the values to get the best result. Convolving an image is not a cheap operation and for large images this can be lengthy and computationally intensive; there is also the problem that the imageconvolution() function didn't exist prior to PHP 5.1 so if you're using an earlier version (and not matter what people say, PHP 4 is still in use) then you're out of luck unless you want to do the convolution by hand using imagecolorat().

JavaScript

Apart from the image addition page, there is only a smattering of JavaScript throughout the site to enhance certain aspects. The possibility exists for me to do AJAX calls for galleries so that a user never has to reload the page however the payload for an AJAX request isn't going to be much more than for a full page request - if the design was more complex then there would be an argument for it however as it is, there isn't the justification for either loading the HTML directly or loading XML or JSON and transforming that on the page. I did end up adding a small bit of JavaScript to vertically centre the images within a gallery as CSS doesn't do this reliably:

$$("#gallery li a img").each(function(s) {
	s.setStyle({
		// quick and dirty vertical centering
		marginTop: Math.round((175 - s.getHeight()) / 2)+"px"
	});
});

This uses Prototype's selection function, ordinarily by the point I reach this function I've already assigned #gallery to a variable which means I can do a scoped selection (e.g. variable.select("li a img") rather than using $$()). I hard code the value just for expediency, you could just as easily find out the height of the containing li element using s.up("li").getHeight() however for large pages of images this could be slow as you're then doing an extra DOM call per image.

As I mentioned before SWFUpload requires a lot of JavaScript upfront to make it play nice - I usually create an object with all the SWFUpload function hooks and then just fill them in as and when I require them. This means I can have a skeleton object which I can drag and drop into any project where I'm using SWFUpload. I find it useful to set the debug function to output to the Firebug console (console.log) and to turn on debugging so I know what's going on. Thankfully the library comes with several helpers which cover just about everything you could want to do with it: speed, cookie and queue integrate well and do what you would expect. One of the most helpful functions I wrote concerned converting from bytes into a more sensible denomination (kilobytes, megabytes, gigabytes) dependent on the value provided:

var fileSize = function(sizeInBytes)
{
	if(sizeInBytes > 1073741824)
	{
		return Math.round((sizeInBytes * 100) / 1073741824) / 100 + " GB";
	}
	else if(sizeInBytes > 1048576)
	{
		return Math.round((sizeInBytes * 100) / 1048576) / 100 + " MB";
	}
	else if(sizeInBytes > 1024)
	{
		return Math.round(sizeInBytes / 1024) + " KB";
	}
	return sizeInBytes + " B";
};

It takes account of JavaScript's lack of a fully featured round() function and multiplies and divides accordingly. This works in numerous places such as totally up the selected file sizes and the current speed of the upload.

Conclusion

There are a raft of other parts to gallery.chaostangent.com which merit exploring but are more intrinsically tied to the context of the site rather than the above which are useful in isolation.