-- p_AlterPK
DROP PROCEDURE IF EXISTS p_AlterPK;

delimiter //
CREATE PROCEDURE p_AlterPK ( PkName VARCHAR ( 50 ), TableName VARCHAR ( 50 ), FieldName VARCHAR ( 2000 ), IsClustered INT ) 
 SQL SECURITY INVOKER
BEGIN
DECLARE
	oldPkName VARCHAR ( 30 );
IF
	EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = SCHEMA ( ) AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = TableName ) THEN
	
	SET @sqlcounts = concat( 'ALTER TABLE ', TableName, ' DROP primary key' );
PREPARE stmt 
FROM
	@sqlcounts;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END IF;
IF
	FieldName IS NOT NULL 
	AND FieldName <> 'NULL' 
	AND FieldName <> '' THEN
		
		SET @sqlcounts = concat( 'ALTER TABLE ', TableName, ' ADD primary key(', FieldName, ')' );
	PREPARE stmt 
	FROM
		@sqlcounts;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
	
END IF;

END
//
delimiter ;


-- p_AlterColumn
DROP PROCEDURE IF EXISTS p_AlterColumn;

delimiter //
CREATE PROCEDURE p_AlterColumn (
TableName VARCHAR ( 50 ),
ColumnName VARCHAR ( 50 ),
ColumnType VARCHAR ( 50 ),
ColumnIsNull VARCHAR ( 10 ),
ModifyFlag VARCHAR ( 10 ),
DefaultValue VARCHAR ( 255 ) 
) 
 SQL SECURITY INVOKER
BEGIN
DECLARE
	v_defaultvalue VARCHAR ( 500 );
DECLARE
	v_defaultsql VARCHAR ( 500 );
DECLARE
	v_ColumnType VARCHAR ( 500 );
IF
	ColumnType = 'IMAGE' THEN
	
	SET v_ColumnType = 'LONGBLOB';

ELSEIF ColumnType = 'NCLOB' THEN

SET v_ColumnType = 'LONGTEXT';

ELSEIF ColumnType = 'XMLTYPE' THEN

SET v_ColumnType = 'LONGTEXT';
ELSE 
	SET v_ColumnType = ColumnType;

END IF;

SET v_defaultsql = '';
IF
	DefaultValue IS NOT NULL 
	AND LENGTH( DefaultValue ) > 0 
	AND DefaultValue <> 'NULL' THEN
	IF
		DefaultValue = 'GETDATE()' THEN
			
			SET v_defaultvalue = ' current_timestamp ';
		ELSE
		IF
			INSTR( DefaultValue, '{ts' ) = 1 THEN
				
				SET v_defaultvalue = REPLACE ( REPLACE ( DefaultValue, '{ts', '' ), '}', '' );
			ELSE 
				SET v_defaultvalue = DefaultValue;
			
		END IF;
		
	END IF;
	
	SET v_defaultsql = concat( ' DEFAULT ', v_defaultvalue );
	
END IF;
IF
	SUBSTR( ModifyFlag, 1, 1 ) = '1' 
	AND ColumnType = 'NULL' THEN
		
		SET @ifSql = concat( 'SELECT count(1) into @existcol FROM information_schema.columns WHERE TABLE_SCHEMA = SCHEMA() AND table_name = ''', TableName, ''' AND column_name = ''', ColumnName, ''' ' );
	PREPARE stmt 
	FROM
		@ifSql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
	IF
		@existcol = 1 THEN
			
			SET @sqlcounts = concat( ' ALTER TABLE ', TableName, ' drop column ', ColumnName );
		PREPARE stmt 
		FROM
			@sqlcounts;
		EXECUTE stmt;
		DEALLOCATE PREPARE stmt;
		
	END IF;
	ELSE
	IF
		SUBSTR( ModifyFlag, 3, 1 ) = '1' 
		AND ColumnIsNull = 'NOT NULL' 
		AND ( v_defaultvalue IS NOT NULL AND LENGTH( v_defaultvalue ) > 0 ) THEN
			
			SET @sqlcounts1 = concat( 'UPDATE ', TableName, ' SET ', ColumnName, ' = ', v_defaultvalue, ' WHERE ', ColumnName, ' IS NULL ' );
		PREPARE stmt1 
		FROM
			@sqlcounts1;
		EXECUTE stmt1;
		DEALLOCATE PREPARE stmt1;
		
	END IF;
	
	SET @sqlcounts = concat( 'ALTER TABLE ', TableName, ' modify ', ColumnName, ' ', v_ColumnType, ' ', ColumnIsNull, v_defaultsql, '' );
	PREPARE stmt 
	FROM
		@sqlcounts;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
	
END IF;

END
//
delimiter ;

-- p_ModifyObjectName
DROP PROCEDURE IF EXISTS p_ModifyObjectName;

delimiter //
CREATE PROCEDURE p_ModifyObjectName (
TableName VARCHAR ( 50 ),
ObjectName VARCHAR ( 50 ),
NewObjectName VARCHAR ( 50 ),
ObjectType VARCHAR ( 10 ),
FieldType VARCHAR ( 50 ) 
) 
 SQL SECURITY INVOKER
BEGIN
IF
	ObjectType = 'OBJECT' 
	AND EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = SCHEMA ( ) AND table_name = ObjectName ) THEN
	
	SET @sqlcounts = concat( 'ALTER TABLE ', ObjectName, ' RENAME TO ', NewObjectName );
PREPARE stmt 
FROM
	@sqlcounts;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
ELSE
IF
	ObjectType = 'COLUMN' 
	AND EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = SCHEMA ( ) AND table_name = TableName AND COLUMN_NAME = ObjectName ) THEN
		
		SET @sqlcounts = concat( 'ALTER TABLE ', TableName, ' CHANGE COLUMN ', ObjectName, ' ', NewObjectName, ' ', FieldType );
	PREPARE stmt 
	FROM
		@sqlcounts;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
	
END IF;

END IF;

END
//
delimiter ;


-- p_DropIdx
DROP PROCEDURE IF EXISTS p_DropIdx;

delimiter //
CREATE PROCEDURE p_DropIdx( IdxName VARCHAR ( 50 ), TableName VARCHAR ( 50 ))
 SQL SECURITY INVOKER
BEGIN
DECLARE
 oldIdxName VARCHAR ( 30 );

IF EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = SCHEMA ( ) AND INDEX_NAME = IdxName) THEN

SET @sqlcounts = concat( 'DROP INDEX ', IdxName, ' ON ', TableName);
PREPARE stmt 
FROM
 @sqlcounts;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END IF;

END
//
delimiter ;

-- update mysql.proc
-- update mysql.proc set  SECURITY_type='INVOKER' where db not in ('sys','information_schema','mysql','PERFORMANCE_SCHEMA');
