forked from AcaMate/AcaMate_DB
117 lines
3.6 KiB
SQL
117 lines
3.6 KiB
SQL
CREATE TABLE payload (
|
|
pid CHAR(22) NOT NULL PRIMARY KEY ,
|
|
title VARCHAR(100) NOT NULL ,
|
|
subtitle VARCHAR(100) ,
|
|
body VARCHAR(255) NOT NULL ,
|
|
alert_yn BOOLEAN NOT NULL DEFAULT TRUE,
|
|
category CHAR(4) NOT NULL ,
|
|
content TEXT
|
|
);
|
|
|
|
|
|
CREATE TABLE push_cabinet (
|
|
uid CHAR(6) NOT NULL ,
|
|
pid CHAR(22) NOT NULL ,
|
|
send_date DATETIME NOT NULL ,
|
|
check_yn BOOLEAN NOT NULL DEFAULT FALSE,
|
|
PRIMARY KEY (uid, pid),
|
|
CONSTRAINT fk_pushcabinet_uid_to_user FOREIGN KEY (uid)
|
|
REFERENCES user(uid)
|
|
ON UPDATE CASCADE
|
|
ON DELETE CASCADE,
|
|
CONSTRAINT fk_pushcabinet_pid_to_payload FOREIGN KEY (pid)
|
|
REFERENCES payload(pid)
|
|
ON UPDATE CASCADE
|
|
ON DELETE CASCADE
|
|
);
|
|
|
|
|
|
|
|
INSERT INTO payload(pid, title, subtitle, body, category, content)
|
|
VALUES (
|
|
'AP202503054Q81114002W7',
|
|
'PUSH 확인 TEST',
|
|
'PUSH 확인 TEST - SUB',
|
|
'PUSH를 테스트하기 위해서 작성된 내용입니다. TEST 메시지입나다.',
|
|
'PC00',
|
|
NULL
|
|
);
|
|
|
|
|
|
|
|
INSERT INTO payload(pid, title, subtitle, body, category, content)
|
|
VALUES (
|
|
'AP202503051S41516009J9',
|
|
'PUSH 확인 TEST2',
|
|
NULL,
|
|
'PUSH를 테스트하기 위해서 작성된 두번째 내용입니다. 두번째 TEST 메시지입나다.',
|
|
'PC00',
|
|
NULL
|
|
);
|
|
|
|
|
|
|
|
ALTER TABLE push_cabinet DROP FOREIGN KEY fk_pushcabinet_uid_to_user;
|
|
|
|
ALTER TABLE push_cabinet
|
|
ADD CONSTRAINT fk_pushcabinet_uid_to_user
|
|
FOREIGN KEY (uid) REFERENCES user(uid)
|
|
ON UPDATE CASCADE ON DELETE CASCADE;
|
|
|
|
|
|
CREATE TABLE log_push (
|
|
bid CHAR(6) NOT NULL ,
|
|
pid CHAR(22) NOT NULL ,
|
|
create_date DATETIME NOT NULL ,
|
|
modify_date DATETIME NOT NULL ,
|
|
create_uid VARCHAR(70) NOT NULL ,
|
|
modify_uid VARCHAR(70) NOT NULL ,
|
|
PRIMARY KEY (bid, pid),
|
|
CONSTRAINT fk_logpush_to_payload FOREIGN KEY (bid, pid)
|
|
REFERENCES payload(bid, pid)
|
|
ON UPDATE CASCADE
|
|
ON DELETE CASCADE
|
|
);
|
|
|
|
|
|
|
|
|
|
SELECT cabinet.uid, cabinet.pid, cabinet.send_date, payload.title
|
|
FROM
|
|
(select push_cabinet.pid, push_cabinet.uid, push_cabinet.send_date FROM push_cabinet where uid = 'AMsean.kim20241201') As cabinet
|
|
inner JOIN
|
|
(select payload.title, payload.pid FROM payload) as payload
|
|
on cabinet.pid = payload.pid
|
|
order by send_date
|
|
;
|
|
|
|
|
|
CREATE TABLE push_cabinet
|
|
(
|
|
id INT AUTO_INCREMENT PRIMARY KEY, -- 대리 키로 추가
|
|
uid VARCHAR(70) NOT NULL,
|
|
bid CHAR(6) DEFAULT 'AA0000' NOT NULL,
|
|
pid CHAR(22) NOT NULL,
|
|
send_date DATETIME NOT NULL,
|
|
check_yn TINYINT(1) DEFAULT 0 NOT NULL,
|
|
content TEXT, -- 커스텀 이벤트 내용 저장 (필요 시 TEXT나 VARCHAR로 조정)
|
|
|
|
CONSTRAINT fk_pushcabinet_bid_to_academy
|
|
FOREIGN KEY (bid) REFERENCES academy (bid)
|
|
ON UPDATE CASCADE ON DELETE CASCADE,
|
|
CONSTRAINT fk_pushcabinet_payload
|
|
FOREIGN KEY (bid, pid) REFERENCES payload (bid, pid)
|
|
ON UPDATE CASCADE ON DELETE CASCADE
|
|
);
|
|
|
|
-- 조회 성능 향상을 위해 pid 컬럼에 대한 인덱스 추가 (필요 시 uid, send_date 등의 인덱스도 고려)
|
|
CREATE INDEX idx_pushcabinet_pid ON push_cabinet (pid);
|
|
|
|
ALTER TABLE push_cabinet
|
|
ADD CONSTRAINT fk_pushcabinet_bid_to_academy
|
|
FOREIGN KEY (bid) REFERENCES academy (bid)
|
|
ON UPDATE CASCADE ON DELETE CASCADE;
|
|
ALTER TABLE push_cabinet
|
|
ADD CONSTRAINT fk_pushcabinet_payload
|
|
FOREIGN KEY (bid, pid) REFERENCES payload (bid, pid)
|
|
ON UPDATE CASCADE ON DELETE CASCADE; |