Stored procs in Grails; TinyURL codes Teil zwei

I almost didn’t post this because it is so simple (not a surprise with grails), but it’s usually these little things that tend to help someone out. So if you read the first post, you might have been wondering how you could execute it in grails. The following is the approach I took:

  1. import groovy.sql.Sql
  2. class HomeController {
  3.     def dataSource
  4.  
  5.     def create = {
  6.         def code = new Sql(dataSource).firstRow("call next_activity_code")?.new_code
  7.         // Do something extremely fun and exciting with the new activity code.
  8.     }
  9. }

It really doesn’t need much of an explanation. We use grails to inject the dataSource so that we can use Groovy’s Sql class. Then, using the Sql class we call the proc as a normal query and then just pick out the column for which we are looking. In this case “new_code” because it is the only column ;). HTH.

BTW, does anyone know how concurrency works with stored procs in, say, MySQL? If two calls are made at the same time will the first call block?

Update: Testing

So you say, “oh that’s great, but how do I mock groovy.sql.Sql for testing.” Like so:

  1. import grails.test.*
  2. import groovy.sql.Sql
  3. import javax.sql.DataSource
  4.  
  5. class HomeControllerTests extends ControllerUnitTestCase {
  6.     protected void setUp() {
  7.         super.setUp()
  8.         HomeController.metaClass.createLink = {map -> "http://cookingwithgas.com/"}
  9.  
  10.         Sql.metaClass.constructor = {DataSource ds -> [firstRow: {query -> [new_code: "b7GG"]}]}
  11.     }
  12. }

Here we just override the constructor to return a map instead of a Sql instance. The map has one key, firstRow, with a closure for a value. Because groovy is so groovy, when calling sql.firstRow it will actually call the closure for the firstRow key. Evil innit?

Tip for using HTTPBuilder in Grails

Builders in groovy are great. HTTP Builder could be one of my favorites, when it comes to integrating with third-party apis. Especially with the extremely simple to use RESTClient builder.

I’m using it on my latest project and am pulling the dependencies into the project using the Ivy plugin. I did run into a little snag where one of HTTP Builder’s dependencies, xml-apis, causes grails to choke when loading. I haven’t looked, but I’m sure there is already a version of xml-apis being loaded. A quick way to fix this is to use the following as the dependency declaration in your ivy.xml:

  1. <dependency org="org.codehaus.groovy" name="http-builder" rev="0.5.0-SNAPSHOT" conf="runtime">
  2.     <exclude org="xml-apis" module="xml-apis" name="*" type="*" ext="*" conf="" matcher="exact"/>
  3. </dependency>

Right, HTH. Carry on.

Create your own TinyURL-like codes

The explanation

So this is probably the most SQL I’ve written in, well, probably ever. It is certainly the first stored procedure I’ve written and stored function as well. It may not be what your DBA would write, but I’m not your DBA.

Anyway, the goal was to be able to create codes for URLs that would be as short as possible, while maintaining sequence and being completely unique. When disseminating the URL in places where there are character limitations (think twitter) every character counts. So we want our codes to be as short as possible for as long as possible.

I usually use a numeric database id column to pass around in URLs in order to pull up a certain record on a given page. So say I was using grails, I would have a URL like http://host.com/activity/1 which would map to the record with an id of 1 in the activity table. The problem with this approach is that by the time you hit 10 records you have added another character. If you hit a very low number of 100,000 records you have added 5 characters. In essence you only have 10 permutations per column. If we want to keep short urls for a long time we need a different counting system. Hex is nice, but still too short so I went with a base-62 system. The sequence is 0-9A-Za-z. This way each column can represent 62 unique records, a two character code can represent 3,844 records, a three character code could represent 238,328 records, etc.

So, that’s the idea. Fairly simple, but pretty powerful in context. Here’s the implementation as a stored procedure and a stored function. BTW, this was written against MySQL. I have no idea whether or not it would be valid in another DB.

The code

  1. DELIMITER $$
  2.  
  3. DROP PROCEDURE IF EXISTS `next_activity_code`$$
  4.  
  5. CREATE PROCEDURE `next_activity_code`()
  6. BEGIN
  7.   Declare ascii_code int(5);
  8.   declare new_code varchar(10);
  9.   declare current_code varchar(10);
  10.   Declare current_code_length int(10);
  11.  
  12.   SELECT id AS `code` FROM `code` INTO current_code;
  13.   SET current_code_length = character_length(current_code);
  14.   SET ascii_code = ord(substring(current_code, -1));
  15.   case ascii_code
  16.     when 122 then
  17.       begin
  18.         declare maxed bool DEFAULT 1;
  19.         declare current_ascii_code int;
  20.         Declare counter int DEFAULT current_code_length - 1;
  21.  
  22.         find_unmaxed_column: while counter > 0 do
  23.           SET current_ascii_code = ord(substring(current_code, counter, 1));
  24.           IF current_ascii_code != 122 then
  25.             SET maxed = 0;
  26.             SELECT next_char(current_ascii_code) INTO new_code;
  27.             SET new_code = concat(substring(current_code, counter - 1, 1), new_code, repeat(char(48), current_code_length - counter));
  28.             leave find_unmaxed_column;
  29.           end IF;
  30.           SET counter = counter - 1;
  31.         end while;
  32.  
  33.         IF maxed then
  34.           SET new_code = repeat(char(48), current_code_length + 1);
  35.         end IF;
  36.       end;
  37.     else
  38.       begin
  39.         SELECT next_char(ascii_code) INTO new_code;
  40.         SET new_code = concat(substring(current_code, 1, current_code_length - 1), new_code);
  41.       end;
  42.   end case;
  43.  
  44.   UPDATE `code` SET id = new_code WHERE id = current_code;
  45.   SELECT new_code;
  46. END$$
  47.  
  48. DELIMITER ;
  49.  


  1.  
  2. DELIMITER $$
  3.  
  4. DROP FUNCTION IF EXISTS `twitrunner`.`next_char`$$
  5.  
  6. CREATE FUNCTION `next_char`(current_code int) RETURNS varchar(1) DETERMINISTIC
  7.     BEGIN
  8.     case current_code
  9.       when 57 then
  10.         begin
  11.           RETURN char(65);
  12.         end;
  13.       when 90 then
  14.         begin
  15.           RETURN char(97);
  16.         end;
  17.       when 122 then
  18.         begin
  19.           RETURN NULL;
  20.         end;
  21.       else
  22.         begin
  23.           RETURN char(current_code + 1);
  24.         end;
  25.     End Case;
  26.   END$$
  27.  
  28. DELIMITER ;
  29.  


The breakdown

The “current code” is kept in a table with one column and one row. We look up the code from the table in order to generate the next code in the sequence. However, since we have a character sequence and not a numerical sequence, we can’t just do an increment and be done. In order to be able to run some math on the code we break down parts of the code into it’s ascii integer code equivalent when needed. The ranges that we chose for our character sequence as [character:ascii] are [0-9:48-57], [A-Z:65-90], and [a-z:97-122].

With that knowledge the rest of the logic should be pretty straight forward. The basic flow is to check the last character to see if it can be incremented. If it can we increment it. If it cannot be incremented (i.e. it’s the 62nd character in our sequence), then we loop backwards through the code until we find a character that can be incremented. If we find a character that can be incremented, it is incremented and then every character following it in the code is reset to the first character in the sequence. If we don’t find a character that can be incremented it means that every column has been maxed out and we need to start a new one. In this case all columns are reset to the first character in the sequence and a new character is added to the end of the code. Like I said earlier, pretty simple, but also powerful.

Hopefully this was useful. If not, I hope it was at least entertaining ;).

Custom implicit variables in GSP’s

I really should start every post with “groovy is a really swell language”. You can do some pretty cool things with it.

Anyway, I had a desire to have an variable implicitly available in all of my GSP’s so that I didn’t have to wrap certain blocks in a tag that made the variable available. There were a couple of reasons for this. The first is that if an exception was thrown in the block of code, the stack trace would indicate that it was coming from my custom tag. More importantly however it was a real pain in the tuckus to have to litter my GSP’s with a tag that simply introduced a variable into the scope. After several attempts to hijack the page scope before the page was rendered I settled on the following:

  1. GroovyPage.metaClass.getCurrentUser = {-> ctx.authenzedManager.currentUser() }

As it turns out every page extends GroovyPage which is just a groovy script. So you can add methods using it’s metaClass to add all kinds of functionality. This might be a good way for plugins to add sort of helper methods, since, unlike tags, it will actually return a value and not write it to out. Anyway, by adding getCurrenUser I can now call ${currentUser.whatever} from my GSP. It makes me happy.

Rock over London. Rock on Chicago.

Hibernate events plugin v0.3

Nothing new in this release, but it does now work with JDK 5 and 1.4.2! And they all rejoiced….yea

The zip: http://thegioraproject.com/files/grails/plugins/grails-hibernate-events-0.3.zip

Image attachments in grails using ImageMagick

I’ll admit it, I used ImageMagick for my image manipulation instead of Java and I’ll show you how I did it.

I tried using the image tools plugin. It’s terrific. It is easy to install and simple to use. I couldn’t ask for anything more. The only problem is that the default interpolation algorithm (nearest neighbor) doesn’t create the greatest images when scaling. This is a JAI problem. So I did some trial and error and found that bicubic interpolation is much better; not great, but better. It was slow. This is a JAI problem. It got even slower when I tried a multi pass approach to scaling the image. It was just too slow for the image quality that it was producing. It seemed I had a problem with JAI.

On a past RoR project I used a plugin that facilitated file uploads on models. It had a built-in thumbnailing feature that used ImageMagick for image processing. I had a decent experience with it, so I decided to give it a shot.

Pros: It is noticeably faster , the image quality is superb, and it supports modifying and saving gifs straight up. You can even scale a jpeg and save it as a gif if you so desired. Note that I wasn’t using mediaLib which speeds up processing, though quality is actually the biggest sticking point.

Con: I’m interfacing ImageMagick using Groovy’s String.execute() method (a wrapper for ProcessBuilder). Not that this is an ImageMagick problem, as there is a JMagick library that uses JNI to interop. However, judging from some reports on the intartubes, there haven’t been too many success stories.

If your still interested here’s how it went down:

1. Installed ImageMagick

Downloaded and installed ImageMagick

2. Installed Hibernate Events Plugin

  1. grails install-plugin http://thegioraproject.com/files/grails/plugins/grails-hibernate-events-0.2.zip

3. Created a generic Attachment Domain class

  1. import grails.util.GrailsUtil
  2. import org.springframework.web.multipart.MultipartFile
  3. import org.codehaus.groovy.grails.commons.ConfigurationHolder
  4.  
  5. class Attachment {
  6.     //Allows us to have spring bind the file automatically. <input name="attachment.file" type="file" />
  7.     MultipartFile file
  8.     //Save file metadata to the database
  9.     String contentType
  10.     String fileName
  11.     Long size    
  12.  
  13.     //Don’t try to save the MultipartFile.  Hibernate will barf all over you.
  14.     static transients = ["file"]
  15.  
  16.     //Location for saving files i.e. /home/me/app_files/attachments/
  17.     def baseDir = { ConfigurationHolder.config.paperclip.files.dir[GrailsUtil.environment] }
  18.  
  19.     //Use Hibernate Events Plugin to save the file after it’s metadata has been saved.
  20.     def afterInsert = {
  21.         writeFile()
  22.     }
  23.  
  24.     //Create a unique directory to store the file based on the saved id. Produces a two deep
  25.     //directory to stave off the 32000 hard link limit on ext3 filesystem.  So the path might look
  26.     //like 0000/0001/filename.extension
  27.     //see: http://thegioraproject.com/2008/03/02/workaround-for-subdirectory-limit-on-ext3-filesystem/
  28.     def directory = {
  29.         def stringId = id.toString()
  30.         def fullId = stringId.padLeft(8, "0")
  31.         [fullId[0..3], fullId[4..7]].join("/")
  32.     }
  33.  
  34.     //Joins the directory and filename to create a path.  Useful for creating links on views: can call
  35.     //attachment.path() to produce href.
  36.     def path = {
  37.         [directory(), fileName].join("/")
  38.     }
  39.  
  40.     //Location on disk to write the file to.
  41.     def absolutePath = {
  42.         [baseDir(), path()].join("/")
  43.     }
  44.  
  45.     //Writes the file to the disk making sure all parent directories are present
  46.     protected void writeFile() {
  47.         def destination = new File(absolutePath())
  48.         destination.mkdirs()
  49.         file.transferTo(destination)
  50.     }
  51.  
  52.     //When Spring binds the file to the object we set the metadata for the file on the object.
  53.     //Eliminates some setter code.
  54.     public void setFile(MultipartFile upload) {
  55.         file = upload
  56.         contentType = upload.contentType
  57.         fileName = upload.originalFilename
  58.         size = upload.size
  59.     }
  60. }

4. Created an Image Domain class

  1. import grails.util.GrailsUtil
  2. import org.codehaus.groovy.grails.commons.ConfigurationHolder
  3.  
  4. class Image extends Attachment {
  5.     //Root directory of the ImageMagick install determined by the environment.  So if we’re using
  6.     //development it will get the config variable paperclip.magick.dir.development from Config.groovy
  7.     def magickDir = { ConfigurationHolder.config.paperclip.magick.dir[GrailsUtil.environment] }    
  8.  
  9.     //After saving make sure we save the original by calling writeFile() on Attachment,
  10.     //then create the thumbnails
  11.     def afterInsert = {
  12.         writeFile()
  13.         createThumbnails()
  14.     }
  15.  
  16.     //Location on disk to write the thumbnail to
  17.     def absoluteThumbnail = {type ->
  18.         [baseDir(), thumbnail(type)].join("/")
  19.     }
  20.  
  21.     //Kind of like path on Attachment.  Allows us to call attachment.thumbnail("300") on the view to
  22.     //produce an img src.
  23.     def thumbnail = {type ->
  24.         def tokens = fileName.tokenize(".")
  25.         def name = [tokens[0..-2].join("."), type, tokens[-1]].join(".")
  26.         [directory(), name].join("/")
  27.     }
  28.  
  29.     //The meat of it all.  It gets a little lost because there isn’t a lot of code.  So we loop over
  30.     //an array to produce two thumbnails.  One will be 100×100, the other will be 300×300.  However
  31.     //ImageMagick will keep the images aspect ratio using the geometry we supplied.  This method will
  32.     //create a string like "/ImageMagick/convert /path/image.jpg -thumbnail 100×100 /path/image.100.jpg"
  33.     //and then execute it as if you were on the command line.  The ImageMagick command will resize and
  34.     //save the image to the supplied dir.  The execute() call returns a process object which we call
  35.     //waitFor on to make sure the processing finishes before we move on.  We can also get the exit code
  36.     //from the process to handle exceptions.
  37.     //More info ongeometry flags and resizing here: http://www.imagemagick.org/Usage/resize/#resize.
  38.     //It is very flexible.
  39.     protected void createThumbnails() {
  40.         [300, 100].each {
  41.             def process = "${magickDir()}/convert ${absolutePath()} -thumbnail ${it}x${it} ${absoluteThumbnail(it)}".execute()
  42.             process.waitFor()
  43.         }
  44.     }
  45. }

Hopefully the comments explain how everything works together. If you have questions, comments or criticism, please let me know. And yes, I feel a little dirty calling another program in a process, but when it works this well, getting a little dirty feels pretty good.

Image comparison (my youngest, Ariana):

ImageMagick
ImageMagick 100px Thumb

ImageTool (JAI)
ImageTool(JAI) 100px Thumb

ImageMagick
arianamagick300.JPG

ImageTool (JAI)
arianaimagetool300.jpg

Hibernate events plugin v0.2

What, another release already? Yep, we’re releasing another version of the grails hibernate events plugin.  Well, obviously I wasn’t paying attention in grails/hibernate class and it turns out that while it requires a hibernate.cfg.xml to participate in the creation of the SessionFactory, you can still programmatically add event listeners to the SessionFactory after its been created. Big ups to Burt Beckwith for the tip (who, judging by the grails mailing list alone, has every api under the sun memorized!). So here is the hibernate events plugin sans hibernate.cfg.xml:

http://thegioraproject.com/files/grails/plugins/grails-hibernate-events-0.2.zip

And again, any feedback is much appreciated.

Hibernate events plugin

On a current grails project I had a need to act on my domain classes using hibernate’s persistence lifecycle. However, it turns out that grails only provides three lifecycle methods: beforeInsert, beforeUpdate, beforeDelete. Four if you count onLoad. I was really interested in having an afterInsert method so that I could save a file to the filesystem after it’s metadata had been saved to the database. So I used the hibernate event system to round out the lifecycle methods with seven more hooks:

  • afterInsert
  • afterUpdate
  • afterDelete
  • beforeLoad
  • afterLoad
  • beforeSave
  • afterSave

So if a domain model contains a closure with any of these names they will be called during their turn in the lifecycle. I think they are mostly self explanatory except for before/afterSave. The purpose for these methods is to be called when the persistence call is either an insert or an update. So if you don’t care if the object is being saved for the first time or being updated you can use this method.

Unfortunately the plugin uses a hibernate.cfg.xml, because it is the only way you can participate in the creation of the SessionFactory.

I haven’t decided if I will release the plugin yet. Maybe if there is interest.

Update: Ok, so some interest was expressed. Here it is: http://thegioraproject.com/files/grails/plugins/grails-hibernate-events-0.1.zip. Feedback is always welcome.

Update 2: I’ve added a doc page to the grails wiki.  Not really much is needed, but it could clear things up a bit.  http://docs.codehaus.org/display/GRAILS/Hibernate+Events+Plugin

Have God watch over your processes; or How to keep your app running when your not watching

My latest application at work is a Ruby on Rails(RoR) app, that we just deployed recently. Being a RoR app it hasn’t been so pleasant in production. I’m sure that it is due to my lack of experience in deploying RoR apps, but it has been difficult in any case. It is deployed to a cluster of 3 mongrels behind an apache proxy/load balancer. Just this morning the app became unresponsive as one of the mongrel instances was hung and had the cpu pegged. Not hard to fix, killed the processes and restarted the cluster. More of a nuisance really.

The problem, though, is that if this keeps happening (it most likely will), it would be more than just a nuisance. We needed to find a way to monitor the app to make sure it was always running. Luckily there is a product out there that does more than just monitor your app. Luckily I found God ;). From the God website:

“God is an easy to configure, easy to extend monitoring framework written in Ruby.

Keeping your server processes and tasks running should be a simple part of your deployment process. God aims to be the simplest, most powerful monitoring application available.”

It doesn’t lie. It is one of the best pieces of software that I have used in a little while now. I was up and running in about 30 minutes. Most of that time was spent checking out the different options and whatnot. So now if one of the mongrels fails for some reason, God will start it back up. If one of the mongrels is using too much memory, God will restart it. If one of the mongrels is pegging the processor, God will restart it. You can even watch the logs in real time for each individual mongrel instance, and control God from the command line. There is also a great tutorial on loading God on system startup.

If you’re interested make sure you read through the home page for God to see all the great things it is capable of.

Technorati :

Workaround for subdirectory limit on ext3 filesystem

Just keep in mind that this is a temporary workaround for a problem that should be addressed in a more robust fashion. So on the ext3 filesystem there is a subdirectory limit of 32,000. The problem is that when you reach this limit and you try to add another subdirectory you get a programmer type error: “Too many links”. If you aren’t at the command line and calling mkdir directly this can be very cryptic bubbling up in your programs stack trace. What it means is that there are too many hard links(directories or files) in the directory.

It is a real show stopper, especially if it is the directory to which upload all over your website’s user generated content. Here is a quick workaround until you can solve the problem more gracefully. The limit is for hard links and not soft links (symbolic links). So, what you want to do is:

  1. Create an overflow directory.
  2. Move all of your current subdirectories from the current directory to the overflow directory.
  3. Create a symlink for each subdirectory in the current directory pointing the directory in the overflow directory.

Of course you won’t want to do this by hand, so you might want to whip up a script to do this. Mine was only a few lines of ruby (also note that it was made easier by the fact that the directories were sequential numbers):

  1.     require ‘fileutils’
  2.    old_dir = "/village/data_file/content"
  3.    new_dir = "/village/data_file/overflow"
  4.  
  5.    (0..41671).each do |index|
  6.      dir = File.join(old_dir, index.to_s)
  7.      if File.exist?(dir)
  8.        FileUtils.mv(dir, new_dir)
  9.        FileUtils.ln_sf(File.join(new_dir, index.to_s), dir)
  10.      end
  11.    end