UDF thumbnailer

This page details the use of the MySQL User Defined Function for generating thumbnail images for images-in-the-database users. Some users need digital files to upload to their own website and the like, outside of ITrack's database. The UDF allows them to easily get thumbnails of all of their images when the images are added to ITrack, without the customer needing to do additional work to save the thumbnail in a particular place or manually resize the image to their specifications.

All of the files are available on download.isoftdata.com, in either udf_thumbnail_trigger_32.zip or udf_thumbnail_trigger_64.zip. Please use the version, 32-bit or 64-bit, that corresponds to the MySQL install on the target server (this MAY NOT match the operating system, so please check MySQL; SHOW VARIABLES LIKE 'version_compile%'; will generally be a good guide here).

Note that this install procedure is targeted toward Windows. At this time, we do not have a Linux build of the UDF, but the source code should be compatible for compiling for Linux. The trigger mentions the itrackpro database, but this trigger is product agnostic and can be used with Enterprise without difficulty.

Install Instructions

1. Download udf_trigger_thumbnail_32.zip or udf_trigger_thumnail_64.zip from http://dl.isoftdata.com
2. Extract the zip file to a temporary directory.
3. Copy DevIL.dll and ILU.dll to C:\Program Files\MySQL\MySQL Server<whatever>\bin
4. Copy udf_thumbnail.dll to C:\Program Files\MySQL\MySQL Server<whatever>\lib\plugins (technically, this goes wherever SHOW VARIABLES LIKE 'plugin_dir'; says, however \lib\plugins is pretty much always right).
5. Copy isoft_udf.ini to the MySQL datadir (Probably C:\ProgramData\MySQL\MySQL Server<whatever>\data by default)
6. Edit isoft_udf.ini to point to the place you want images to be saved to. Do NOT put this under a user's personal files, like the desktop; MySQL won't have permission to write files there!
7. Run the following in SQLYog:

   USE itrackpro; # Or itrackenterprise or whatever
   CREATE FUNCTION udf_getenvvar RETURNS STRING SONAME 'udf_thumbnail.dll';
   CREATE FUNCTION udf_loadini RETURNS INT SONAME 'udf_thumbnail.dll';
   CREATE FUNCTION udf_thumbnail RETURNS STRING SONAME 'udf_thumbnail.dll';
   DELIMITER $$
   CREATE
       TRIGGER `t_filechunk_after_insert` AFTER INSERT
       ON `filechunk`
       FOR EACH ROW BEGIN
       DECLARE strReturn VARCHAR(255);
       SELECT IFNULL(udf_getenvvar('UDF_IMAGEDIR'), udf_loadini()) INTO strReturn;
       SELECT IF(UNHEX(MD5(f_get_attachment_data(NEW.`fileid`))) = `file`.`hash`,
               udf_thumbnail(f_get_attachment_data(NEW.`fileid`),
                       `file`.`name`, <maximum width of thumbnail>, <maximum height of thumbnail> ), # Good choices for these values would be something like 300, 600, or 800 
                   'Incomplete Data. Not saving.') INTO strReturn
       FROM `file`
       WHERE `file`.`fileid` = NEW.`fileid`;
       END$$
   DELIMITER ;

8. Close and reopen ITrack (this step may not be necessary, but we believe it will help the UDF function correctly)
9. Save a new image in ITrack to verify it works.
10. (optional) Save out all of their old images as thumbnails:

    SELECT udf_thumbnail(f_get_attachment_data(`fileid`),
                  `name`, <maximum width of thumbnail>, <maximum height of thumbnail> ) FROM `file`;

Things to Keep in Mind

• You can safely use spaces in the paths for the error log and the image thumbnail repository, however, if you do so, DO NOT put quotes around the path. It will work just fine without the quotes.
• DO NOT store the error log or the thumbnail repository under a user-permissioned directory, like My Documents or a user's Desktop. The MySQL service is what saves the thumbnails out, and it won't have permission to write to those locations, so the thumbnailing function will simply fail.