Scrum/sql/scrum.sql

/*
Navicat MySQL Data Transfer

Source Server         : localhost
Source Server Version : 50709
Source Host           : localhost:3306
Source Database       : scrum

Target Server Type    : MYSQL
Target Server Version : 50709
File Encoding         : 65001

Date: 2016-03-11 20:19:15
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `log`
-- ----------------------------
DROP TABLE IF EXISTS `log`;
CREATE TABLE `log` (
  `id` int(12) NOT NULL AUTO_INCREMENT COMMENT '日志ID',
  `task_id` int(8) NOT NULL COMMENT '任务ID',
  `task_name` varchar(100) NOT NULL COMMENT '任务名称',
  `info` varchar(500) DEFAULT NULL COMMENT '日志内容',
  `time` datetime NOT NULL COMMENT '变化时间',
  `author` int(8) NOT NULL COMMENT '修改人',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of log
-- ----------------------------

-- ----------------------------
-- Table structure for `member`
-- ----------------------------
DROP TABLE IF EXISTS `member`;
CREATE TABLE `member` (
  `id` int(8) NOT NULL AUTO_INCREMENT COMMENT '人员ID',
  `name` varchar(10) NOT NULL COMMENT '人员名称',
  `remark` varchar(200) NOT NULL COMMENT '人员备注',
  `mail` varchar(20) NOT NULL COMMENT '人员邮箱',
  `status` tinyint(2) NOT NULL COMMENT '人员状态',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of member
-- ----------------------------

-- ----------------------------
-- Table structure for `program`
-- ----------------------------
DROP TABLE IF EXISTS `program`;
CREATE TABLE `program` (
  `id` int(8) NOT NULL AUTO_INCREMENT COMMENT '项目ID',
  `name` varchar(100) NOT NULL COMMENT '项目名称',
  `status` tinyint(2) NOT NULL COMMENT '项目状态',
  `member_id` int(8) NOT NULL COMMENT '创建者ID',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of program
-- ----------------------------

-- ----------------------------
-- Table structure for `task`
-- ----------------------------
DROP TABLE IF EXISTS `task`;
CREATE TABLE `task` (
  `id` int(8) NOT NULL AUTO_INCREMENT COMMENT '任务ID',
  `name` varchar(100) NOT NULL COMMENT '任务名称',
  `deadline` datetime NOT NULL COMMENT '任务deadline',
  `remark` varchar(200) DEFAULT NULL COMMENT '任务说明',
  `status` tinyint(2) NOT NULL COMMENT '任务状态',
  `member_id` int(8) NOT NULL COMMENT '归属人员',
  `program_id` int(8) NOT NULL COMMENT '归属项目',
  `author_id` int(8) NOT NULL COMMENT '任务作者/修改者',
  PRIMARY KEY (`id`),
  KEY `member_id` (`member_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of task
-- ----------------------------
INSERT INTO `task` VALUES ('1', '名字', '2016-03-16 00:00:13', '备注', '2', '5', '1', '5');
DROP TRIGGER IF EXISTS `loging`;
DELIMITER ;;
CREATE TRIGGER `loging` AFTER UPDATE ON `task` FOR EACH ROW BEGIN
    IF NEW.name != OLD.name
    THEN
        INSERT INTO log(
            task_id,
            task_name,
            info,
            time,
            author)
        values(
            NEW.id,
            NEW.name,
            concat('任务名称变更：' , OLD.name , '→' , NEW.name),
            NOW(),
            NEW.author_id);
    END IF;

    IF NEW.deadline != OLD.deadline
    THEN
        INSERT INTO log(
            task_id,
            task_name,
            info,
            time,
            author)
        values(
            NEW.id,
            NEW.name,
            concat('任务时间变更：' , OLD.deadline , '→' , NEW.deadline),
            NOW(),
            NEW.author_id);
    END IF;

    IF NEW.status != OLD.status
    THEN
        INSERT INTO log(
            task_id,
            task_name,
            info,
            time,
            author)
        values(
            NEW.id,
            NEW.name,
            concat('任务状态变更：' , OLD.status , '→' , NEW.status),
            NOW(),
            NEW.author_id);
    END IF;

    IF NEW.member_id != OLD.member_id
    THEN
        INSERT INTO log(
            task_id,
            task_name,
            info,
            time,
            author)
        values(
            NEW.id,
            NEW.name,
            concat('任务成员变更：' , OLD.member_id , '→' , NEW.member_id),
            NOW(),
            NEW.author_id);
    END IF;
END
;;
DELIMITER ;