code generation routine

generate_code.sql

CREATE DEFINER=`root`@`localhost` PROCEDURE `generate_code`(
									IN __table_name VARCHAR(255),
									IN __pk VARCHAR(255),
									IN __id VARCHAR(255),
									IN __ingore_fields TEXT
								)
BEGIN
	DECLARE __database, __table VARCHAR(129);
	DECLARE __column_list, __column, __query, __query_select, __query_join, __query_where TEXT;
    
    SET __database = ( SELECT DATABASE() );
	SET __column_list  = (
								SELECT 
									GROUP_CONCAT(`COLUMN_NAME` SEPARATOR '`,`')
								FROM 
									`information_schema`.`COLUMNS`
								WHERE 
									`TABLE_SCHEMA` = __database
									AND 
									`TABLE_NAME` = __table_name
							);
	SET __column_list = CONCAT('`', __column_list, '`');
	SET __query_select = '';
	
	SET __query_join = '';
    
	WHILE LENGTH(__column_list) > 1 DO
		SET __column = 	( SELECT SUBSTRING_INDEX ( __column_list, ',', 1 ) );	
		
        SET __query_select = (
			SELECT 
				IF(LOCATE( REPLACE(__column,'`',''), __ingore_fields) = 0,
					CONCAT(__query_select,'IF(COALESCE(t1.',__column,',0) = COALESCE(t2.',__column,',0),t1.',__column,',GROUP_CONCAT(t2.',__column,', " ndryshuar ne : ", t1.',__column,')) AS ',__column,' , '),
                    CONCAT(__query_select,' ' )
				)
		);
		
		SET __query_join = (
			SELECT 
				IF(LOCATE( REPLACE(__column,'`',''), __ingore_fields) = 0,
					CONCAT(__query_join, ' t1.',__column,' <> t2.',__column,' OR '),
                    CONCAT(__query_join,' ' )
                )
        );

		SET __column_list = ( SELECT SUBSTRING(__column_list FROM CHAR_LENGTH(SUBSTRING_INDEX(__column_list , ',', 1)) + 2 ) );
	END WHILE;
    
	SET __query_select = SUBSTRING(__query_select, 1, LENGTH(__query_select) - 2) ;
    
    SET __query_select = CONCAT(
		'SELECT 
		t2.action_datetime, 
		t2.action, 
		t1.revision as \'row id\',',__query_select);
    
    SET __query_join = SUBSTRING(__query_join, 1, LENGTH(__query_join) - 3) ;
    
    SET __query_join = CONCAT(
		'FROM 
		',__table_name,' as t1 
		INNER JOIN ',__table_name,' AS t2 
			ON t1.',__pk,' = t2.',__pk,' AND (', __query_join, ')');
    
    SET __query_where = CONCAT(
		' WHERE 
		-- t2.revision = t1.revision + 1
		-- AND
		t1.action = \'update\'
		AND
		t1.pid = \'',__id,'\'	
        GROUP BY t1.action_datetime
		ORDER BY 
		t1.pid ASC, 
		t2.revision ASC');
    
	SET @__query =  CONCAT(__query_select, __query_join, __query_where);


    PREPARE stmt FROM @__query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END