-- ----------------------------
-- Procedure structure for checkCloum
-- ----------------------------
DROP PROCEDURE IF EXISTS `checkCloum`;
DELIMITER ;;
CREATE PROCEDURE `checkCloum`(IN `TableSchema` varchar(50),IN `TableName` varchar(50))
BEGIN
DECLARE Done INT DEFAULT 0;
DECLARE Col VARCHAR(50);
DECLARE Str VARCHAR(255);
/* 声明游标 */
DECLARE rs CURSOR FOR SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE table_schema = TableSchema AND table_name=TableName AND COLUMN_NAME IN ('uid', 'schoolid', 'classid','cuid', 'puid');
/* 异常处理 */
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
/* 打开游标 */
OPEN rs;
/* 是否跳过滤表 */
IF (TableName='message_question') THEN
SELECT CONCAT('Break Create Table:', TableName);
ELSE
/* 复制表结构 */
SET @Str = CONCAT('DROP TABLE IF EXISTS ', TableName);
PREPARE stmt FROM @Str;
EXECUTE stmt;
SET @Str = CONCAT('CREATE TABLE IF NOT EXISTS ', TableName, ' LIKE ', TableSchema, '.', TableName);
PREPARE stmt FROM @Str;
EXECUTE stmt;
FETCH NEXT FROM rs INTO Col;
REPEAT
IF NOT Done THEN
IF (Col='classid') THEN
SET @Str = CONCAT('REPLACE INTO ', TableName, ' SELECT * FROM ', TableSchema, '.', TableName, ' WHERE ', Col, ' IN(SELECT id FROM ` classinfo`)');
ELSE
SET @Str = CONCAT('REPLACE INTO ', TableName, ' SELECT * FROM ', TableSchema, '.', TableName, ' WHERE ', Col, ' IN(SELECT ', Col, ' FROM `user_mapping`)');
END IF;
ELSE
/* 单独处理 schoolinfo */
IF (TableName=' schoolinfo') THEN
SET @Str = CONCAT('REPLACE INTO ', TableName, ' SELECT * FROM ', TableSchema, '.', TableName, ' WHERE id IN(SELECT schoolid FROM `user_mapping`)');
/* 过滤习题选项 */
ELSEIF (TableName='exercises_option') THEN
SET @Str = CONCAT('REPLACE INTO ', TableName, ' SELECT * FROM ', TableSchema, '.', TableName, ' WHERE xtid IN(SELECT id FROM ', TableSchema, '.te_exercises', ' WHERE uid IN (SELECT uid FROM `user_mapping`))');
ELSE
SET @Str = CONCAT('REPLACE INTO ', TableName, ' SELECT * FROM ', TableSchema, '.', TableName);
END IF;
END IF;
PREPARE stmt FROM @str;
EXECUTE stmt;
FETCH NEXT FROM rs INTO Col;
UNTIL Done END REPEAT;
END IF;
/* 关闭游标 */
CLOSE rs;
END
;;
DELIMITER ;
-- ----------------------------
-- Procedure structure for sync_xue
-- ----------------------------
DROP PROCEDURE IF EXISTS `sync_xue`;
DELIMITER ;;
CREATE PROCEDURE `sync_xue`()
BEGIN
DECLARE Done INT DEFAULT 0;
DECLARE TableSchema VARCHAR(50);
DECLARE TableName VARCHAR(50);
DECLARE Str VARCHAR(255);
/* 声明游标 */
DECLARE rs CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'source1_db' AND TABLE_NAME NOT IN ( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'source1_db' AND ( LOCATE('_log', TABLE_NAME) OR LOCATE('_copy', TABLE_NAME) OR LOCATE('_bak', TABLE_NAME) ) ) AND LOCATE('te_', TABLE_NAME);
/* 异常处理 */
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
/* 打开游标 */
OPEN rs;
/* 逐个取出当前记录TABLE_NAME字段的值 */
FETCH NEXT FROM rs INTO TableName;
/* 遍历数据表 */
REPEAT
IF NOT Done THEN
IF (TableName='te_area'||TableName='card_answer') THEN
SELECT CONCAT('Break Table:', TableName);
ELSE
CALL checkCloum('source1_db', TableName);
SELECT TableName;
END IF;
END IF;
FETCH NEXT FROM rs INTO TableName;
UNTIL Done END REPEAT;
/* 关闭游标 */
CLOSE rs;
END
;;
DELIMITER ;
-- ----------------------------
-- Procedure structure for sync_user
-- ----------------------------
DROP PROCEDURE IF EXISTS `sync_user`;
DELIMITER ;;
CREATE PROCEDURE `sync_user`()
BEGIN
DECLARE Done INT DEFAULT 0;
DECLARE TableSchema VARCHAR(50) DEFAULT 'source2_db';
DECLARE TableName VARCHAR(50);
DECLARE Str VARCHAR(255);
/* 声明游标 */
DECLARE rs CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'source2_db' AND TABLE_NAME NOT IN ( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'source2_db' AND ( LOCATE('_log', TABLE_NAME) OR LOCATE('_copy', TABLE_NAME) ) );
/* 异常处理 */
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
/* 创建用户映射表 */
SET @Str = 'DROP TABLE IF EXISTS `user_mapping`';
PREPARE stmt FROM @Str;
EXECUTE stmt;
SET @Str = 'CREATE TABLE `user_mapping` (`uid` bigint(20) NOT NULL,`schoolid` bigint(20) DEFAULT NULL,`cuid` bigint(20) DEFAULT NULL,`puid` bigint(20) DEFAULT NULL,PRIMARY KEY (`uid`),KEY `schoolid` (`schoolid`) USING BTREE) ENGINE=MyISAM DEFAULT CHARSET=utf8';
PREPARE stmt FROM @Str;
EXECUTE stmt;
/* 将处理用户数据加入到当前表中 */
SET @Str = 'REPLACE INTO `user_mapping` SELECT u.uid,IF(s.schoolid, s.schoolid, -1) as schoolid,u.uid as cuid,u.uid as puid FROM source2_db. user u LEFT JOIN source2_db. user_school s ON u.uid = s.uid WHERE u.sys_code = 1';
PREPARE stmt FROM @Str;
EXECUTE stmt;
/* 处理classinfo表 */
/* 复制表结构 */
SET @Str = 'DROP TABLE IF EXISTS ` classinfo`';
PREPARE stmt FROM @Str;
EXECUTE stmt;
SET @Str = CONCAT('CREATE TABLE IF NOT EXISTS ` classinfo` LIKE ', TableSchema, '. classinfo');
PREPARE stmt FROM @Str;
EXECUTE stmt;
SET @Str = CONCAT('REPLACE INTO ` classinfo` SELECT * FROM ', TableSchema, '. classinfo WHERE schoolid IN(SELECT schoolid FROM `user_mapping`)');
PREPARE stmt FROM @Str;
EXECUTE stmt;
/* 打开游标 */
OPEN rs;
/* 逐个取出当前记录TABLE_NAME字段的值 */
FETCH NEXT FROM rs INTO TableName;
/* 遍历数据表 */
REPEAT
IF NOT Done THEN
IF (TableName=' classinfo') THEN
SELECT CONCAT('Break Table:', TableName);
ELSE
CALL checkCloum('source2_db', TableName);
SELECT TableName;
END IF;
END IF;
FETCH NEXT FROM rs INTO TableName;
UNTIL Done END REPEAT;
/* 关闭游标 */
CLOSE rs;
CALL sync_xue();
END
;;
DELIMITER ;
