event 테이블 추가

[aanoaa]

opencloset/donation#107

에서 필요로 추가합니다.

• 쿠폰 예약
• 쿠폰 사용
• 쿠폰 통계
• 3회 이상 대여 할인 계산
  여기에서 쿠폰을 사용하지 않고 몇회째 대여인지 계산함
• 쿠폰이름 표시하는 곳
  • 시간표
  • 주문서(쿠폰 이름 표시)
• 예약확인(monitor revervation)

등에서 coupon.desc 에 이벤트명을 기록하고 사용하고 있는데,
이러한 것들을 대체할 수 있습니다.

이벤트에 대한 상세 스펙을 정의해서 넣고
이벤트와 관련된 곳에서 해당 이벤트 id 를 참조해서 사용할 예정입니다.

이벤트에 대한 관리용 UI 도 필요할 수 있습니다.
이것을 활용하면 취업날개를 제외하고 통계 추가 작업을 자동화 할 수도 있을 것 같습니다.

[aanoaa]

일단은 이렇습니다.
구체화되면서 바뀔 여지가 많습니다.

• id
• name
• desc
• host or sponsor
• year 이건 좀 애매할 수 있는게 end_date 가 없는거면 year 도 정의할 수 없음 업데이트 해줄게 아니라면
• nth (회차) - 같은 기관/목적으로 하는 이벤트인데 몇번째인지
• start_date
• end_date
• create_date
• update_date

Table 1: opencloset.event table

id	name	desc	host or sponsor	year	nth	start-date	end-date	created-date	update-date
1	event-1	description of event-1	city of seoul	2018	1	2018-11-01	2018-12-31	2018-10-13	2018-10-13

[aanoaa]

DROP TABLE IF EXISTS `event`;
CREATE TABLE `event` (
  `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`         VARCHAR(32) NOT NULL,
  `desc`         TEXT DEFAULT NULL,
  `sponsor`      VARCHAR(128) DEFAULT NULL,
  `year`         INT(11) DEFAULT 0 COMMENT '연도; 애매하면 0',
  `nth`          INT(11) DEFAULT 1 COMMENT '회차; 회차와 연도로 그룹화; 2018년 2회차; 2019년 1회차',
  `start_date`   DATETIME DEFAULT NULL,
  `end_date`     DATETIME DEFAULT NULL,
  `create_date`  DATETIME DEFAULT NULL,
  `update_date`  DATETIME DEFAULT NULL,

  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

좀 애매한게 연도가 애매해서 0으로 했는데 흥해서 2회차가 열렸다면?
나중에 생각하겠습니다.

[aanoaa]

당장에 모든 곳에 event 테이블이 쓰이기 보다는 기증도메인에 우선 쓰였다가,
점점 사용하는 곳을 넓히는 것이 어떨까 싶습니다.

[aanoaa]

ALTER TABLE `donation_form`
  ADD CONSTRAINT `fk_donation_form2` FOREIGN KEY (`event_id`) REFERENCES `event` (`id`) ON DELETE CASCADE;

쿼리를 실행하면,

mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1292 (22007) at line 22: Incorrect datetime value: '0000-00-00 00:00:00' for column 'birth_date' at row 533

와 같은 에러가 발생합니다.

mysql> SELECT COUNT(*) FROM donation_form WHERE birth_date = '0000-00-00 00:00:00';
+----------+
| COUNT(*) |
+----------+
|       18 |
+----------+

왜 birth_date 에 값이 '0000-00-00 00:00:00' 으로 들어간 건이지 파악해봐야 합니다.

[aanoaa]

생년월일을 0000-00-00 으로 입력했을때에는 오류가 발생합니다.
최근에 발생한 건은 2018-08-25 17:39:33 입니다.

최근 배포가 5월달 이었으니 지금도 발생하고 있습니다.

NULL 아니면 제대로 된 값이 들어갈 것으로 여겼지만 그렇지 않은 것 같습니다.

optional 에 qr/\d{4}-\d{2}-\d{2}/ regex 체크를 하고 있는데,
더 정밀한 validation 이 필요할 것 같습니다.

[aanoaa]

단순히 0000-00-00 00:00:00 을 NULL 로 변경하고 진행하려고 했습니다.

mysql> UPDATE donation_form SET birth_date = NULL WHERE birth_date = '0000-00-00 00:00:00';
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'birth_date' at row 1

이것도 안됩니다.
맥부기라서 안되는건가..(근데 mysql server 는 docker 로 띄웠고 linux 와 같은버전을 사용하고 있습니다.)

[aanoaa]

null 값을 허용하고 있고 이미 null 로 들어간 row 가 많은데 왜?

+-----------------+------------------+------+-----+---------+----------------+
| Field           | Type             | Null | Key | Default | Extra          |
+-----------------+------------------+------+-----+---------+----------------+
| birth_date      | datetime         | YES  |     | NULL    |                |
+-----------------+------------------+------+-----+---------+----------------+

[aanoaa]

nullable datetime 타입의 컬럼을 null 로 변경할 수 없습니다.
mysql server 는 5.7.19 이고 mysqlclient 는 mysql Ver 8.0.12 for osx10.13 on x86_64 (Homebrew) 입니다.

[aanoaa]

WHERE 절이 문제인데 이게 좀 희한합니다.
SELECT 구문에서는 동작하는데 UPDATE 일때에는 에러가 발생합니다.
맥부기라서 그런건가..

UPDATE `donation_form` SET `birth_date` = NULL WHERE id = 645;
Query OK, 1 row affected (0.01 sec)

# error
UPDATE `donation_form` SET `birth_date` = NULL WHERE birth_date = '0000-00-00 00:00:00';

# error
UPDATE `donation_form` SET `birth_date` = NULL WHERE birth_date = '0000-00-00';

# OK
SELECT id, create_date, birth_date FROM `donation_form` WHERE birth_date = '0000-00-00 00:00:00' LIMIT 1;

[aanoaa]

mysql> UPDATE `donation_form` SET `birth_date` = NULL WHERE `id` IN (SELECT `id` FROM `donation_form` WHERE `birth_date` = '0000-00-00 00:00:00');
UPDATE `donation_form` SET `birth_date` = NULL WHERE `id` IN (SELECT `id` FROM `donation_form` WHERE `birth_date` = '0000-00-00 00:00:00');
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'birth_date' at row 1

[aanoaa]

0000-00-00 00:00:00 으로 들어가 있던 값들을 모두 NULL 로 변경하고(어찌어찌했음),
CONSTRAINTS 설정을 위해 alter table 구문을 실행했는데,

ERROR 1292 (22007) at line 22: Incorrect datetime value: '1991-00-00 00:00:00' for column 'birth_date' at row 1061

이러한 에러가 발생합니다.
mysql 버전에 따라서 datetime 컬럼의 값을 검증하는 기능이 추가된게 아닌가 싶습니다.
애초에 쓰레기 값이 들어간 것이 잘못된 것이기는 합니다.
2017-06-29 13:26:02 에 들어간 데이터 입니다.

[aanoaa]

그렇다면 얼마나 많은 쓰레기값이 들어가 있는지 확인을 해보고자 했습니다.
perl 이 얼마나 좋냐면, 컬럼에 쓰레기 값이 들어가 있으면 DateTime object 로 inflate 될때에 undef 값으로 됩니다.

my $form = $schema->resultset('DonationForm')->find({ id => 1061 });
$form->get_column('birth_date'); # '1991-00-00 00:00:00'
$form->birth_date;               # undef

[aanoaa]

하고자 했던 것은 donation_form 테이블에 event_id 컬럼을 추가하고 외래키 설정을 걸어주는 것 이었습니다.
그 과정에서 datetime 컬럼에 들어있는 쓰레기 값들 때문에 제대로 진행되고 있지 않습니다.

모든 쓰레기값을 찾아서 NULL 값으로 변경하면 될 것 같습니다.
쓰레기값이 들어가지 못하도록 input 값에 대한 validation 이 더 강화되면 좋겠습니다.

[aanoaa]

쓰레기 값은 0000-00-00 00:00:00 19개와 1991-00-00 00:00:00 한개 입니다.

[aanoaa]

datetime 컬럼의 쓰레기 값은 아래 쿼리로 변환할 수 있습니다.

https://stackoverflow.com/a/37780259

UPDATE donation_form SET birth_date = NULL WHERE CAST(birth_date AS CHAR(20)) = '0000-00-00 00:00:00';
UPDATE donation_form SET birth_date = NULL WHERE CAST(birth_date AS CHAR(20)) = '1991-00-00 00:00:00';

[aanoaa]

OpenCloset-Schema-0.058.tar.gz 배포하였습니다.