UDF thumbnailer - Revision history

Codeman at 17:56, 16 November 2017

2017-11-16T17:56:23Z

← Older revision		Revision as of 12:56, 16 November 2017
Line 34:		Line 34:
	FROM `file`		FROM `file`
	WHERE `file`.`fileid` = NEW.`fileid`;		WHERE `file`.`fileid` = NEW.`fileid`;
	END$$		END;$$

	DELIMITER ;		DELIMITER ;

Codeman: Updating info about configuring and troubleshooting the UDF Thumbnailer

2016-10-17T15:54:25Z

Updating info about configuring and troubleshooting the UDF Thumbnailer

@@ Line 14: / Line 14: @@
 # Load the UDF functions and initialize the UDF
 <source lang="mysql">
-CREATE FUNCTION udf_loadini RETURNS INTEGER SONAME 'udf_thumbnail.dll';
-CREATE FUNCTION udf_getenvvar RETURNS STRING SONAME 'udf_thumbnail.dll';
+    USE itrackpro; # Or itrackenterprise or whatever
-CREATE FUNCTION udf_email RETURNS STRING SONAME 'udf_thumbnail.dll';
+    CREATE FUNCTION udf_getenvvar RETURNS STRING SONAME 'udf_thumbnail.dll';
-CREATE FUNCTION udf_thumbnail RETURNS STRING SONAME 'udf_thumbnail.dll';
+    CREATE FUNCTION udf_loadini RETURNS INT SONAME 'udf_thumbnail.dll';
-SELECT udf_loadini();
+    CREATE FUNCTION udf_thumbnail RETURNS STRING SONAME 'udf_thumbnail.dll';
 </source>
 <ol start="8">
@@ Line 54: / Line 55: @@
 === 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.
+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? ===
@@ Line 71: / Line 72: @@
 SELECT udf_thumbnail(f_get_attachment_data(`fileid`),`name`, 600, 600) FROM `file` WHERE fileid = <fileid>;
 </source>
-The results should show up in the folder that the images are set to go to.

Codeman: /* Install Instructions */

2016-10-17T15:15:07Z

Install Instructions

← Older revision		Revision as of 10:15, 17 October 2016
Line 14:		Line 14:
	# Load the UDF functions and initialize the UDF		# Load the UDF functions and initialize the UDF
	<source lang="mysql">		<source lang="mysql">
	CREATE FUNCTION udf_loadini RETURNS INTEGER SONAME '~~udf_isoft~~.dll';		CREATE FUNCTION udf_loadini RETURNS INTEGER SONAME 'udf_thumbnail.dll';
	CREATE FUNCTION udf_getenvvar RETURNS STRING SONAME '~~udf_isoft~~.dll';		CREATE FUNCTION udf_getenvvar RETURNS STRING SONAME 'udf_thumbnail.dll';
	CREATE FUNCTION udf_email RETURNS STRING SONAME '~~udf_isoft~~.dll';		CREATE FUNCTION udf_email RETURNS STRING SONAME 'udf_thumbnail.dll';
	CREATE FUNCTION udf_thumbnail RETURNS STRING SONAME '~~udf_isoft~~.dll';		CREATE FUNCTION udf_thumbnail RETURNS STRING SONAME 'udf_thumbnail.dll';
	SELECT udf_loadini();		SELECT udf_loadini();
	</source>		</source>

Codeman: Helpful troubleshooting tips

2016-09-14T21:14:45Z

Helpful troubleshooting tips

← Older revision		Revision as of 16:14, 14 September 2016
Line 49:		Line 49:
	* 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.		* 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.		* '''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.
			<source lang='mysql'>
			SELECT partnum FROM inventory WHERE partnum = 1
			INTO OUTFILE "C:/udf_thumbnails/test.csv"
			FIELDS TERMINATED BY ','
			ENCLOSED BY '"'
			LINES TERMINATED BY '\n';
			</source>

			=== Running a single file through the UDF Thumbnailer ===
			One way to test the thumbnailer itself is to run a single file through it:
			<source lang='mysql'>
			SELECT udf_thumbnail(f_get_attachment_data(`fileid`),`name`, 600, 600) FROM `file` WHERE fileid = <fileid>;
			</source>
			The results should show up in the folder that the images are set to go to.

Codeman: Updating this page to reflect a more current place to download the file

2016-08-09T14:05:56Z

Updating this page to reflect a more current place to download the file

← Older revision		Revision as of 09:05, 9 August 2016
Line 6:		Line 6:

	==Install Instructions==		==Install Instructions==
	# Download udf_trigger_thumbnail_32.zip or udf_trigger_thumnail_64.zip from http://dl.isoftdata.com		# Download udf_trigger_thumbnail_32.zip or udf_trigger_thumnail_64.zip from [http://dl.isoftdata.com/udf_thumbnail_trigger_64.zip here].
	# Extract the zip file to a temporary directory.		# Extract the zip file to a temporary directory.
	# Copy DevIL.dll and ILU.dll to C:\Program Files\MySQL\MySQL Server<whatever>\bin		# Copy DevIL.dll and ILU.dll to C:\Program Files\MySQL\MySQL Server<whatever>\bin

Broy: /* Install Instructions */ Added the queries to actually load the UDF functions into MySQL

2016-03-03T22:15:56Z

Install Instructions: Added the queries to actually load the UDF functions into MySQL

← Older revision		Revision as of 17:15, 3 March 2016
Line 12:		Line 12:
	# Copy isoft_udf.ini to the MySQL datadir (Probably C:\ProgramData\MySQL\MySQL Server<whatever>\data by default)		# Copy isoft_udf.ini to the MySQL datadir (Probably C:\ProgramData\MySQL\MySQL Server<whatever>\data by default)
	# 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!'''		# 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!'''
			# Load the UDF functions and initialize the UDF
			<source lang="mysql">
			CREATE FUNCTION udf_loadini RETURNS INTEGER SONAME 'udf_isoft.dll';
			CREATE FUNCTION udf_getenvvar RETURNS STRING SONAME 'udf_isoft.dll';
			CREATE FUNCTION udf_email RETURNS STRING SONAME 'udf_isoft.dll';
			CREATE FUNCTION udf_thumbnail RETURNS STRING SONAME 'udf_isoft.dll';
			SELECT udf_loadini();
			</source>

	# Set the maximum height and width in the following queries (in the "SELECT udf_thumbnail" section), then run in SQLYog:		# Set the maximum height and width in the following queries (in the "SELECT udf_thumbnail" section), then run in SQLYog:
	<source lang="mysql">DELIMITER $$		<source lang="mysql">DELIMITER $$

Jmckinstry: Updating to have a non-broken trigger

2014-12-08T22:30:21Z

Updating to have a non-broken trigger

← Older revision		Revision as of 17:30, 8 December 2014
Line 13:		Line 13:
	# 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!'''		# 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!'''
	# Set the maximum height and width in the following queries (in the "SELECT udf_thumbnail" section), then run in SQLYog:		# Set the maximum height and width in the following queries (in the "SELECT udf_thumbnail" section), then run in SQLYog:
			<source lang="mysql">DELIMITER $$
	<source lang="mysql">		CREATE TRIGGER `t_filechunk_after_insert` AFTER INSERT ON `filechunk`
	~~USE itrackpro; # Or itrackenterprise or whatever~~
	DELIMITER $$
	CREATE
	TRIGGER `t_filechunk_after_insert` AFTER INSERT
	ON `filechunk`
	FOR EACH ROW BEGIN		FOR EACH ROW BEGIN
	# Verify the whole file is finished uploading		# Verify the whole file is finished uploading
	DECLARE `strReturn` VARCHAR(255);		DECLARE `strReturn` VARCHAR(255);
	IF (UNHEX(MD5(f_get_attachment_data(NEW.`fileid`))) = `file`.`hash`~~) THEN~~		SELECT IF(UNHEX(MD5(f_get_attachment_data(NEW.`fileid`))) = `file`.`hash`,
	~~SELECT~~ udf_thumbnail(f_get_attachment_data(NEW.`fileid`),		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`.`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`		INTO `strReturn`
	FROM `file`		FROM `file`
	WHERE `file`.`fileid` = NEW.`fileid`;		WHERE `file`.`fileid` = NEW.`fileid`;
	END IF;		END;
	~~END~~$$		$$
	DELIMITER ;		DELIMITER ;</source>
	</source>		`file`.`name`,

	<ol start="8">		<ol start="8">
Line 40:		Line 35:
	</ol>		</ol>
	SELECT udf_thumbnail(f_get_attachment_data(`fileid`),		SELECT udf_thumbnail(f_get_attachment_data(`fileid`),
	`name`, <maximum width of thumbnail>, <maximum height of thumbnail> ) FROM `file`;		`name`, <maximum width of thumbnail>, <maximum height of thumbnail>) FROM `file`;

	==Things to Keep in Mind==		==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.		* 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.		* '''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.

Gevans: /* Install Instructions */

2014-12-08T20:53:05Z

Install Instructions

← Older revision		Revision as of 15:53, 8 December 2014
Line 12:		Line 12:
	# Copy isoft_udf.ini to the MySQL datadir (Probably C:\ProgramData\MySQL\MySQL Server<whatever>\data by default)		# Copy isoft_udf.ini to the MySQL datadir (Probably C:\ProgramData\MySQL\MySQL Server<whatever>\data by default)
	# 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!'''		# 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!'''
	# ~~Run~~ the following in SQLYog:		# Set the maximum height and width in the following queries (in the "SELECT udf_thumbnail" section), then run in SQLYog:

			<source lang="mysql">
	USE itrackpro; # Or itrackenterprise or whatever		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 $$		DELIMITER $$
	CREATE		CREATE
	TRIGGER `t_filechunk_after_insert` AFTER INSERT		TRIGGER `t_filechunk_after_insert` AFTER INSERT
	ON `filechunk`		ON `filechunk`
	FOR EACH ROW BEGIN		FOR EACH ROW BEGIN
	DECLARE strReturn VARCHAR(255);		# Verify the whole file is finished uploading
	~~SELECT IFNULL(udf_getenvvar('UDF_IMAGEDIR'), udf_loadini()) INTO strReturn;~~		DECLARE `strReturn` VARCHAR(255);
	~~SELECT~~ IF(UNHEX(MD5(f_get_attachment_data(NEW.`fileid`))) = `file`.`hash`,		IF (UNHEX(MD5(f_get_attachment_data(NEW.`fileid`))) = `file`.`hash`) THEN
	udf_thumbnail(f_get_attachment_data(NEW.`fileid`),		SELECT 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`.`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		INTO `strReturn`
	FROM `file`		FROM `file`
	WHERE `file`.`fileid` = NEW.`fileid`;		WHERE `file`.`fileid` = NEW.`fileid`;
	END$$		END IF;
	DELIMITER ;		END$$
			DELIMITER ;
			</source>

	<ol start="8">		<ol start="8">

Jmckinstry at 19:21, 28 November 2014

2014-11-28T19:21:57Z

← Older revision		Revision as of 14:21, 28 November 2014
Line 3:		Line 3:
	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).		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~~.		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==		==Install Instructions==

Broy: Create the basic installation documentation for the MySQL UDF thumbnailer.

2014-01-28T23:09:31Z

Create the basic installation documentation for the MySQL UDF thumbnailer.

New page

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==
# Download udf_trigger_thumbnail_32.zip or udf_trigger_thumnail_64.zip from http://dl.isoftdata.com
# Extract the zip file to a temporary directory.
# Copy DevIL.dll and ILU.dll to C:\Program Files\MySQL\MySQL Server<whatever>\bin
# 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).
# Copy isoft_udf.ini to the MySQL datadir (Probably C:\ProgramData\MySQL\MySQL Server<whatever>\data by default)
# 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!'''
# 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 ;

<ol start="8">
<li>Close and reopen ITrack (this step may not be necessary, but we believe it will help the UDF function correctly)</li>
<li>Save a new image in ITrack to verify it works.</li>
<li>(optional) Save out all of their old images as thumbnails:</li>
</ol>
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.