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 compatible with Windows. To use the UDF on linux, go to /data/tmp/banff_src/Thumbnail/UDF Thumbnailer and type `make`, then move udf_isoft.so to the correct mysql plugin directory.

Install Instructions

1. Download udf_trigger_thumbnail_32.zip or udf_trigger_thumnail_64.zip from here.
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. Load the UDF functions and initialize the UDF

    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`, 800, 800 ), # You can change the "800s" to whatever dimensions you want for the maximum height and width of the thumbnail
                    '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.

Troubleshooting

If the UDF Thumbnailer is working correctly then whenever you add an image to a part or vehicle in ITrack it should pump out the image to the specified folder in the file system as well. Here are some troubleshooting tips to help you if that doesn't happen:

Does the mysql user have permission to write to the file system?

The user will need some global permissions to write to the file system. I'm not sure exactly which ones they'll need, but things like 'Execute' and 'File' would be a good guess. Whatever the case, if they have no global privileges and only have database level permissions to itrackpro or itrackenterprise, that is not going to be enough.

Does the folder you are writing to have proper permissions?

You can open the properties of the folder itself and check, but another useful trick is to try writing the results of a query to a file in the same folder that all of the image backups are going to.

SELECT partnum FROM inventory WHERE partnum = 1
INTO OUTFILE "C:/udf_thumbnails/test.csv"
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Running a single file through the UDF Thumbnailer

One way to test the thumbnailer itself is to run a single file through it:

SELECT udf_thumbnail(f_get_attachment_data(`fileid`),`name`, 600, 600) FROM `file` WHERE fileid = <fileid>;

Running this test will likely get the image to show up in MySQL's data directory, not the directory specified in isoft_udf.ini, even so, this is a valid test as to whether or not the user has correct permissions to write to the file system.

The images save in the MySQL data directory instead of the directory specified in isoft_udf.ini

The trigger for the UDF thumbnailer (t_filechunk_after_insert) references an environment variable. You'll want to make sure the environment variable is set up. Go to 'Computer' and right click on it, then go to 'Properties'. Navigate to 'Advanced system settings >> Environment Variables'. Under the System Variables area (the bottom one) click 'New', and put in this information:

• Variable name: UDF_IMAGEDIR
• Variable value: (The direct path to the folder where the images should be stored, for example: C:\udf_thumbnails. Do not use quotes)