Published at 12:21AM on the 4th of October, 2014 | Author: Timothy Harding

DevLog 2: Rollback Support Redux

Finally made some updates to the sites backend and interface. I'll start with the backend and move forward from there.

Website's Backend Code

A good friend of mine (whose knowledge of CS has far eclipsed my own since we last hung out, pictured below) suggested I switch the entire database to InnoDB, which I talked about it's benefits here, and create a sort of ghost table to house the FULLTEXT search index. After that, a simple trigger can make sure the two tables are always in sync.

it is an image

I decided against the trigger solution for now, opting for a stored procedure and manually calling it after the database 'commit' command. I couldn't really figure out how the trigger would work if I did a rollback, as myISM databases do not support this feature.

BEGIN

	DELETE FROM itunpro_posts_search
		WHERE post_id = update_post_id;
	INSERT INTO itunpro_posts_search
		SELECT * FROM itunpro_posts
		WHERE post_id = update_post_id;

END

I also removed the useless AUTONUM PK from the tags table. When I originally designed the site I was very much a newbie and made the mistake of putting a numbered PK on everything, regardless of the need. I would then worry that the numbers were out of sync, if truncating the table would wreck havoc, and how to reset the AUTONUM value if needed.

it is an image

It took a DA veteran to knock that behavior out of me (pictured above), but the website's older design lived on for years. After finally removing the 'tag_id' column, I was amused that the website required zero alterations to play with the new table design. It was as if I decided it wasn't needed ages ago, but never committed to removing the column.

CREATE TABLE `itunpro_tags` (
  `post_id` int(11) NOT NULL,
  `tag_text` varchar(64) NOT NULL,
  PRIMARY KEY (`post_id`,`tag_text`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I also removed a preview mechanic for the post submissions, as it was causing me no end of grief. I'm going to attempt to put it back in with javascript/jquery. But that is about it for changes to the backend. Nothing drastic.

Front end

Only a few changes here. Since I designed the original javascript, I've learned about this fancy newfangled contraption called jquery. It really isn't that new though, so I was apparently just being blind to the benefits. I took a horrendous bit of code (note the testing for nodes, neighbors, parents, children. because each browser computes objects, parents, and children slightly differently):

//Iterate through the images
for( i=0; i < document.images.length; i++) {

	//Get the height and width values of current image
	width = document.images[i].width;
	height = document.images[i].height;
	
	//If our parent is a post_image, then set the height
	if (document.images[i].parentNode.className == "post_image") {
	
		document.images[i].parentNode.style.height = height + 12 + "px";
	
	
	} else {
	//Else If our parent's parent is a post image, we have an enlargeable image on our hands
		if (document.images[i].parentNode.parentNode.className == "post_image") {
		
			//Set the height like before
			document.images[i].parentNode.parentNode.style.height = height + 12 + "px";
		
			//Get our parent node in a var
			var mylist=document.images[i].parentNode.parentNode;
			
			//Iterate through the child nodes until we find post_image_enlarge
			for (j=0; j < mylist.childNodes.length; j++){
				//Set the offset to get the picture to the farthest right of the image
				if (mylist.childNodes[j].className == "post_image_enlarge") {
					offset = Math.ceil((mylist.clientWidth - width) / 2) + width - 95;
					mylist.childNodes[j].style.left =  offset + "px";
				}
			}
		}
	}
}

And turned it into this:

$('div.post_image').each(function() {
	var img = $(this).find('img');
	var enlarge = $(this).find('div');
	enlarge.width((enlarge.outerWidth(true) - img.width())/2 + img.width());

});

Hopefully I'll be able to make similar changes around the website. Let me know if you see anything falling apart and thanks for reading :)
Tags: DevLog, FULLTEXT, InnoDB, JavaScript, JQuery, MyISAM, MySQL, PHP, Transactions, Web Design
Back ArrowVanuatu Afternoon Timelapse