批量造完备的测试数据

系统开发完毕后，需要完备测试系统页面的逻辑功能，一条条考虑各种情况往数据库里插数据太麻烦了，为此我写了一个存储过程批量造数据，例子如下，共有三张表wideip，pool，status表

1.其中一个wideip包含多个pool(一对多)

2.status表是一个字典表有初始数据，wideip,pool里都有status_id外键，对应于status表里的id。

  初始值为

  

INSERT INTO `status` VALUES ('0', 'available');
INSERT INTO `status` VALUES ('1', 'offline');
INSERT INTO `status` VALUES ('2', 'unknown');
INSERT INTO `status` VALUES ('3', 'unavilable');

3.pool里的wideip_id是一个外键关联了wideip的id

4.enable标识wideip和pool的开关，只有0,1两个状态

对于wideip来说，enable(2种变化)和status_id(4种变化)属性是可变的，因此完备的情况有2x4种

对于某个wideip下面的pool来说，enable(2种变化)和status_id(4种变化)属性是可变的，因此完备的情况有2x4种xwideip的变化=2x4x2x4=64种

造数据的过程如下

1.先清空wideip和pool表，恢复自增为1

2.每插一个wideip,针对这个wideip造出8个完备的pool记录

表结构代码：

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for pool
-- ----------------------------
CREATE TABLE `pool` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `wideip_id` int(11) NOT NULL,
  `enable` tinyint(4) NOT NULL,
  `status_id` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `wideip_pool_fk` (`wideip_id`),
  KEY `status_pool_fk` (`status_id`),
  CONSTRAINT `status_pool_fk` FOREIGN KEY (`status_id`) REFERENCES `status` (`id`),
  CONSTRAINT `wideip_pool_fk` FOREIGN KEY (`wideip_id`) REFERENCES `wideip` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for status
-- ----------------------------
CREATE TABLE `status` (
  `id` tinyint(4) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for wideip
-- ----------------------------
CREATE TABLE `wideip` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `enable` tinyint(4) NOT NULL,
  `status_id` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `status_id` (`status_id`),
  CONSTRAINT `wideip_ibfk_1` FOREIGN KEY (`status_id`) REFERENCES `status` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
INSERT INTO `status` VALUES ('0', 'available');
INSERT INTO `status` VALUES ('1', 'offline');
INSERT INTO `status` VALUES ('2', 'unknown');
INSERT INTO `status` VALUES ('3', 'unavilable');

造数据的存储过程

/*清空所有数据数据*/
drop procedure if exists clear_all_data;
delimiter ;;
create procedure clear_all_data()
BEGIN
     /*清空数据*/
  delete from wideip;
  /*让自增从1*/
  alter table wideip auto_increment 1;
  /*清空pool数据*/
    delete from pool;
    /*让pool自增从1*/
    alter table pool auto_increment 1;
END;;
delimiter ;


drop procedure if exists make_pool_data;
/*造每个wideip对于的pool数据*/
delimiter ;;
create procedure make_pool_data(wideipId int)
BEGIN
  declare d_enable int default 0;
  declare d_status_id int default 0;
  declare isError int default 0;
     declare Done int default 0;
     declare i int default 0;

        /* 声明游标 */
  DECLARE rs CURSOR FOR select id from status order by id;

    /* 异常处理 */
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
  DECLARE CONTINUE HANDLER FOR 1146 SET isError=1;

    REPEAT
            /* 打开游标 */
        OPEN rs;
        REPEAT
            /*取status_id*/
            FETCH rs INTO d_status_id;
            IF Done!=1 THEN
                select d_enable,d_status_id;
                set i=i+1;
                insert into pool(name,enable,status_id,wideip_id) values(concat("name",wideipId,"_",i),d_enable,d_status_id,wideipId);
            END IF;
        UNTIL d_status_id is NULL or Done=1 END REPEAT;
        CLOSE rs;
        set d_enable=d_enable+1;
        set Done=0;
    UNTIL d_enable >= 2 END REPEAT;
END;;
delimiter ;


drop procedure if exists make_wideip_data;
/*造wideip的数据并调用 make_pool_data */
delimiter ;;
create procedure make_wideip_data()
BEGIN
  declare d_enable int default 0;
  declare d_status_id int default 0;
  declare isError int default 0;
     declare Done int default 0;
     declare i int default 0;
     declare newWideipId int default 0;

        /* 声明游标 */
  DECLARE rs CURSOR FOR select id from status order by id;

    /* 异常处理 */
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
  DECLARE CONTINUE HANDLER FOR 1146 SET isError=1;

    REPEAT
            /* 打开游标 */
        OPEN rs;
        REPEAT
            /*取status_id*/
            FETCH rs INTO d_status_id;
            IF Done!=1 THEN
                select d_enable,d_status_id;
                set i=i+1;
                insert into wideip(name,enable,status_id) values(concat("name",i),d_enable,d_status_id);
                set newWideipId=last_insert_id();
             call make_pool_data(newWideipId);
            END IF;
        UNTIL d_status_id is NULL or Done=1 END REPEAT;
        CLOSE rs;
        set d_enable=d_enable+1;
        set Done=0;
    UNTIL d_enable >= 2 END REPEAT;
END;;
delimiter ;

end