4.primarykeys-update_proc_v001.sql

CRM源码This file describes some issues that should be implemented in future and how it should be imple

DELIMITER //

-- USE qwoss
-- //

DROP PROCEDURE IF EXISTS QX_UPDATE_PKEY
//

CREATE PROCEDURE QX_UPDATE_PKEY()
    BEGIN

	-- select all tables 
	DECLARE done int DEFAULT 0;
	DECLARE id int;
	DECLARE tbl varchar(255);
	DECLARE col varchar(255);
	DECLARE stm varchar(4000) CHARACTER SET utf8;
	DECLARE maxkey int;
	DECLARE curkey int;
	DECLARE increment int;
	DECLARE cur CURSOR FOR
		select tables.table_name, COLUMNS.COLUMN_NAME from 
		  information_schema.tables
			INNER JOIN information_schema.table_constraints 
				ON tables.table_name = table_constraints.table_name
			INNER JOIN information_schema.key_column_usage
				ON  tables.table_name = key_column_usage.table_name 
				AND table_constraints.constraint_name = key_column_usage.constraint_name
			INNER JOIN information_schema.columns
				ON  tables.table_name = columns.table_name
				AND key_column_usage.ordinal_position = columns.ordinal_position
		where tables.table_schema = 'qwoss'
			AND tables.table_type = 'BASE TABLE'
			AND table_constraints.constraint_type = 'PRIMARY KEY'
			AND key_column_usage.ordinal_position = 1
			AND columns.column_type like 'int%';
	DECLARE cur2 CURSOR FOR
		select tablename, keyvalue
		from qx_maxkeys; 

	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

	drop temporary table if exists qx_maxkeys;
	create temporary table qx_maxkeys( tablename varchar(255) unique, keyvalue int );
	
	OPEN cur;
	
	-- fill temporary table qx_maxkeys
	FETCH cur INTO tbl, col;
	WHILE done = 0 DO
		SET @state=CONCAT('INSERT INTO qx_maxkeys ','SELECT ''',tbl,''' AS tablename, MAX(',col,') as keyvalue FROM ',tbl); 
		PREPARE stmt FROM @state;
		EXECUTE stmt;
		
		-- PRINT 'Select table: ' + @tbl
		-- PRINT 'Sql: ' + @stm
		FETCH cur INTO tbl, col;
	END WHILE;
	SET done = 0;
	CLOSE cur;

-- ---------------

	OPEN cur2;
	FETCH cur2 INTO tbl, maxkey;
	WHILE done = 0 DO
		SET curkey = 0;
		-- if not found, curkey doesn't change
		select keyvalue from qx_keys where tablename = tbl into curkey;

		IF ISNULL(maxkey) = 1 THEN 
		SET maxkey = 0;
		END IF;

		SET increment = maxkey - curkey;
		
		select tbl, increment, maxkey, curkey;	
		IF increment > 0 THEN 
		CALL qx_next_key(tbl, increment, curkey);
		END IF;
		
		SET done = 0;
		FETCH cur2 INTO tbl, maxkey;
	END WHILE;
	
	CLOSE cur2;

-- 	drop temporary table if exists qx_maxkeys;
    END
//


call qx_update_pkey()
//