BEGIN
DECLARE Done INT DEFAULT 0;
DECLARE v_tablename VARCHAR(50);
DECLARE rs CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'wd' AND TABLE_NAME REGEXP '^(page_).*_.*';
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done=1;
OPEN rs;
FETCH NEXT FROM rs INTO v_tablename;
REPEAT
IF NOT Done THEN
set @sqllang = CONCAT("ALTER TABLE " , v_tablename , " ADD `creatTime` varchar (30) GENERATED ALWAYS AS (replace(json_extract(info, '$.creatTime' ),'\"','' ) ) VIRTUAL");
PREPARE stmt FROM @sqllang;
EXECUTE stmt;
set @sqllang = CONCAT("ALTER TABLE " , v_tablename , " ADD `lastScanTime` varchar (30) GENERATED ALWAYS AS (replace(json_extract(info, '$.lastScanTime' ),'\"','') ) VIRTUAL");
PREPARE stmt FROM @sqllang;
EXECUTE stmt;
set @sqllang = CONCAT("ALTER TABLE " , v_tablename , " ADD `statusCode` INT(6) GENERATED ALWAYS AS (json_extract(info, '$.statusCode' )) VIRTUAL");
PREPARE stmt FROM @sqllang;
EXECUTE stmt;
set @sqllang = CONCAT("ALTER TABLE " , v_tablename , " ADD `pageType` varchar(15) GENERATED ALWAYS AS (replace(json_extract(info, '$.pageType' ),'\"','') ) VIRTUAL");
PREPARE stmt FROM @sqllang;
EXECUTE stmt;
set @sqllang = CONCAT("CREATE INDEX idx_createTime ON " , v_tablename , "(creatTime)");
PREPARE stmt FROM @sqllang;
EXECUTE stmt;
set @sqllang = CONCAT("CREATE INDEX idx_lastScanTime ON ",v_tablename,"(lastScanTime)");
PREPARE stmt FROM @sqllang;
EXECUTE stmt;
set @sqllang = CONCAT("CREATE INDEX idx_statusCode ON " , v_tablename , "(statusCode)");
PREPARE stmt FROM @sqllang;
EXECUTE stmt;
set @sqllang = CONCAT("CREATE INDEX idx_pageType ON " , v_tablename , "(pageType)");
PREPARE stmt FROM @sqllang;
EXECUTE stmt;
END IF;
FETCH NEXT FROM rs INTO v_tablename;
UNTIL Done END REPEAT;
CLOSE rs;
end
CREATE DEFINER = 'root'@'%'
PROCEDURE wd.dropColumnAndIndex()
BEGIN
DECLARE Done INT DEFAULT 0;
DECLARE v_tablename VARCHAR(50);
DECLARE v_row int DEFAULT 0;
DECLARE rs CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'wd' AND TABLE_NAME REGEXP '^(page_).*_.*';
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done=1;
OPEN rs;
FETCH NEXT FROM rs INTO v_tablename;
REPEAT
IF NOT Done THEN
set @sqllang = CONCAT("SELECT count(1) into @row FROM INFORMATION_SCHEMA.statistics WHERE table_schema= 'wd' AND table_name= '" ,v_tablename,"' AND INDEX_NAME= 'idx_createTime'");
PREPARE stmt FROM @sqllang;
EXECUTE stmt;
if (@row > 0) then
set @sqllang = CONCAT("ALTER TABLE " , v_tablename , " DROP INDEX `idx_createTime` ");
PREPARE stmt FROM @sqllang;
EXECUTE stmt;
end if;
set @sqlexist = CONCAT("SELECT count(1) into @row FROM INFORMATION_SCHEMA.statistics WHERE table_schema= 'wd' AND table_name= '" ,v_tablename,"' AND INDEX_NAME= 'idx_lastScanTime'");
PREPARE stmt FROM @sqlexist;
EXECUTE stmt;
if (@row > 0) then
set @sqllang = CONCAT("ALTER TABLE " , v_tablename , " DROP INDEX `idx_lastScanTime` ");
PREPARE stmt FROM @sqllang;
EXECUTE stmt;
end if;
set @sqlexist = CONCAT("SELECT count(1) into @row FROM INFORMATION_SCHEMA.statistics WHERE table_schema= 'wd' AND table_name= '" ,v_tablename,"' AND INDEX_NAME= 'idx_statusCode'");
PREPARE stmt FROM @sqlexist;
EXECUTE stmt;
if (@row > 0) then
set @sqllang = CONCAT("ALTER TABLE " , v_tablename , " DROP INDEX `idx_statusCode` ");
PREPARE stmt FROM @sqllang;
EXECUTE stmt;
end if;
set @sqlexist = CONCAT("SELECT count(1) into @row FROM INFORMATION_SCHEMA.Columns WHERE table_schema= 'wd' AND table_name= '" ,v_tablename,"' AND column_name= 'creatTime'");
PREPARE stmt FROM @sqlexist;
EXECUTE stmt;
if (@row > 0) then
set @sqllang = CONCAT("ALTER TABLE " , v_tablename , " drop column creatTime ");
PREPARE stmt FROM @sqllang;
EXECUTE stmt;
end if;
set @sqlexist = CONCAT("SELECT count(1) into @row FROM INFORMATION_SCHEMA.Columns WHERE table_schema= 'wd' AND table_name= '" ,v_tablename,"' AND column_name= 'lastScanTime'");
PREPARE stmt FROM @sqlexist;
EXECUTE stmt;
if (@row > 0) then
set @sqllang = CONCAT("ALTER TABLE " , v_tablename , " DROP COLUMN lastScanTime ");
PREPARE stmt FROM @sqllang;
EXECUTE stmt;
end if;
set @sqlexist = CONCAT("SELECT count(1) into @row FROM INFORMATION_SCHEMA.Columns WHERE table_schema= 'wd' AND table_name= '" ,v_tablename,"' AND column_name= 'statusCode'");
PREPARE stmt FROM @sqlexist;
EXECUTE stmt;
if (@row > 0) then
set @sqllang = CONCAT("ALTER TABLE " , v_tablename , " DROP COLUMN statusCode ");
PREPARE stmt FROM @sqllang;
EXECUTE stmt;
end if;
END IF;
FETCH NEXT FROM rs INTO v_tablename;
UNTIL Done END REPEAT;
CLOSE rs;
end
