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

CREATE FUNCTION udf_loadini RETURNS INTEGER SONAME 'udf_thumbnail.dll';
CREATE FUNCTION udf_getenvvar RETURNS STRING SONAME 'udf_thumbnail.dll';
CREATE FUNCTION udf_email RETURNS STRING SONAME 'udf_thumbnail.dll';
CREATE FUNCTION udf_thumbnail RETURNS STRING SONAME 'udf_thumbnail.dll';
SELECT udf_loadini();

1. Set the maximum height and width in the following queries (in the "SELECT udf_thumbnail" section), then run in SQLYog:

DELIMITER $$
CREATE TRIGGER `t_filechunk_after_insert` AFTER INSERT ON `filechunk` 
    FOR EACH ROW BEGIN
	# Verify the whole file is finished uploading
	DECLARE `strReturn` VARCHAR(255);
	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
		'File upload is not complete, skipping for now')
		INTO `strReturn`
	FROM `file`
	WHERE `file`.`fileid` = NEW.`fileid`;
END;
$$
DELIMITER ;

`file`.`name`,

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' 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>;

The results should show up in the folder that the images are set to go to.