TXT

Drupal Contribution: Generate vancode using mysql alone

Submitted by Anonymous on Thu, 04/08/2010 - 17:42

I once had to do a migration entirely on mysql and find myself spending a quite fair amount of time digging about the vancode thing. and then I thought why not sharing ? so here it is... two mysql functions do the magic..

/**

	*

	*/

	DELIMITER $$

	

	USE `%new_db%`$$

	

	DROP FUNCTION IF EXISTS `int2vancode`$$

	

	CREATE FUNCTION `int2vancode`(_max INT) RETURNS VARCHAR(255) CHARSET latin1

	BEGIN

	        /* int2vancode as seen on drupal :P */ 

	        /* Convert it again to base 36 */

	        SET @__num = (SELECT CONV(_max ,10,36));

	        RETURN (SELECT CONCAT(CHAR(CHAR_LENGTH( @__num) + ORD(0) -1),@__num));

	END$$

	

	USE `%new_db%`$$

	

	DROP FUNCTION IF EXISTS `vancode2int`$$

	

	CREATE FUNCTION `vancode2int`() RETURNS INT(11)

	BEGIN

	        /* Get the latest Thread ID from comments */

	        SET @__max_thread = (SELECT CASE WHEN ISNULL(MAX(thread)) OR  MAX(thread) = '' THEN 0 ELSE MAX(thread) END FROM comments);

	        /* Clean Thread ID <?php rtrin(@__max_thread,'/'); ?>*/

	        SET @__clean_max_thread = (SELECT SUBSTRING(@__max_thread, 1 ,CASE WHEN CHAR_LENGTH(@__max_thread) = 1 THEN 1 ELSE CHAR_LENGTH(@__max_thread)-1 END));

	        /* vancode2int as it was on drupal */  

	        RETURN (SELECT CONV(SUBSTRING(@__clean_max_thread,CASE WHEN CHAR_LENGTH(@__clean_max_thread) = 1 THEN 1 ELSE 2 END),36,10));       

	END$$

	

	DELIMITER ;

and here's how to use them

/* insert comments from system X into drupal */

	%new_db% = drupal database

	%old_db% your old DB

	%[new|old]_p% prefix

	

	  /* Only valid drupal users are allowed to comment */

	    INSERT INTO %new_db%.%new_p%comments

	        (

	            nid,

	            uid,

	            COMMENT,

	            TIMESTAMP,

	            STATUS,

	            thread

	        )

	        SELECT

	            @_nid,

	            d6.uid,

	            tc.commen,

	            tc.addtime,

	            CASE WHEN tc.is_approved = 'Yes' THEN 0 ELSE 1 END,

	            CONCAT(int2vancode(vancode2int() + 1),'/')

	        FROM

	            %old_db%.comments tc,

	            %new_db%.%new_p%users d6

	        WHERE

	            tc.uid = d6.uid AND

	            tc.vid = new.tangle_vid;

you can safely add a new index to you drupal comments.thread column to speed things up... this is a single level solution (ie no multi-level comments, but can be easily tweaked)