[필기노트] 2021-07-17 SQL, Cookie, Sequelize Migrate

-- DB 생성 

CREATE TABLE Users(
    userId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    email varchar(255) NOT NULL UNIQUE,
    nickname varchar(255) NOT NULL UNIQUE,
    password varchar(255) NOT NULL,
    profileImg varchar(1000),
    createdAt Datetime NOT NULL,
    updatedAt Datetime NOT NULL
);

CREATE TABLE Posts(
    postId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    userId int(11) NOT NULL,
    reBlog int(11),
    title varchar(255),
    content varchar(3000),
    createdAt DateTime NOT NULL,
    updatedAt DateTime NOT NULL,
    FOREIGN KEY (userId) REFERENCES Users(userId) ON DELETE CASCADE,
    FOREIGN KEY (reBlog) REFERENCES Posts(postId) ON DELETE SET NULL,
);

CREATE TABLE Images(
    imageId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    postId int(11) NOT NULL,
    img varchar(1000) NOT NULL,
    createdAt DateTime NOT NULL,
    updatedAt DateTime NOT NULL,
    FOREIGN KEY (postId) REFERENCES Posts(postId) ON DELETE CASCADE
);

CREATE TABLE Tags(
    tagId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    postId int(11) NOT NULL,
    tag varchar(100) NOT NULL,
    createdAt DateTime NOT NULL,
    updatedAt DateTime NOT NULL,
    FOREIGN KEY (postId) REFERENCES Posts(postId) ON DELETE CASCADE
);

CREATE TABLE Favorites(
    favoriteId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    userId int(11) NOT NULL,
    postId int(11) NOT NULL,
    createdAt DateTime NOT NULL,
    updatedAt DateTime NOT NULL,
    FOREIGN KEY (userId) REFERENCES Users(userId) ON DELETE CASCADE,
    FOREIGN KEY (postId) REFERENCES Posts(postId) ON DELETE CASCADE
);

CREATE TABLE Follows(
    followId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    followUserId int(11) NOT NULL,
    followerUserId int(11) NOT NULL,
    createdAt DateTime NOT NULL,
    updatedAt DateTime NOT NULL,
    FOREIGN KEY (followUserId) REFERENCES Users(userId) ON DELETE CASCADE,
    FOREIGN KEY (followerUserId) REFERENCES Users(userId) ON DELETE CASCADE
);

CREATE TABLE Alarms(
    alarmId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    giverUserId int(11) NOT NULL,
    receiverUserId int(11) NOT NULL,
    type TINYINT UNSIGNED NOT NULL,
    createdAt DateTime NOT NULL,
    updatedAt DateTime NOT NULL,
    FOREIGN KEY (giverUserId) REFERENCES Users(userId) ON DELETE CASCADE,
    FOREIGN KEY (receiverUserId) REFERENCES Users(userId) ON DELETE CASCADE
);

 

 

 

-- 쿠키
 쿠키명=<쿠키값>
  - 기본적인 쿠키의 값
  - mycookie=test 또는 name=zerocho와 같이 설정한다.
 Expires=<날짜>
  - 만료 기한, 이 기한이 지나면 쿠키가 제거된다. 기본값은 클라이언트가 종료될 때까지 존재한다.
 Max-age=<초>
  - Expires와 비슷하지만 날짜 대신 초를 입력할 수 있다.
  - 해당 초가 지나면 쿠키가 제거된다.
  - Expires보다 우선한다.
 Domain=<도메인명>
  - 쿠키가 전송될 도메인을 특정할 수 있다.
  - 기본 값은 현재 도메인
 Path=<URL>
  - 쿠키가 전송될 URL을 특정할 수 있다
  - 기본값은 '/'이고, 이 경우 모든 URL에서 쿠키를 전송할 수 있다.
 Secure
  - HTTPS일 경우에만 쿠키가 전송된다.
 HttpOnly
  - 설정 시 자바스크립트에서 쿠키에 접근할 수 없다.
  - 쿠키 조작을 방지하기 위해 설정하는 것이 좋습니다.


-- 세션
 - 쿠키에 이름을 담아서 보내는 대신, 숫자 값을 보낸다.
 - 사용자의 이름과 만료 시간은 지정된 인덱스 아래에 name, expires로 정의한다.


-- express 쿠키 전송 (cookie-parser)
res.cookie(<name>, <val>) : name의 이름으로 val에 데이터를 넣고 Cookie를 전송한다
 - res.send()가 실행되지 않았다면 쿠키가 저장되지 않는다.
 - res.status(400)과 같이 상태코드에 상관 없이 send만 실행될 경우 쿠키가 전달된다.
 - name에는 띄어쓰기를 사용해도 가능하다.

쿠키는 브라우저에서 소유하고 있기만 해도 Headers에 삽입되어 서버에 자동으로 전송된다.


== DB 임시 데이터 삽입

# DB User 생성
insert into Users (email, nickname, password, createdAt, updatedAt) values ("shyu23@gmail.com", "123","1313", NOW(), NOW());
insert into Users (email, nickname, password, createdAt, updatedAt) values ("karlidis1@naver.com", "124","1414",NOW(), NOW());
insert into Users (email, nickname, password, createdAt, updatedAt) values ("archea@gmail.com", "1234","14141414",NOW(), NOW());
insert into Users (email, nickname, password, createdAt, updatedAt) values ("league1113@gmail.com", "4321","41414141",NOW(), NOW());

# DB Post 생성 
insert into Posts (userId, title, content, createdAt, updatedAt) values (1, "K K K K Title", "my Love K K K K",NOW(),NOW());
insert into Posts (userId, title, content, createdAt, updatedAt) values (1, "Opera Holic", "show me the money",NOW(),NOW());
insert into Posts (userId, title, content, createdAt, updatedAt) values (1, "Fance", "travel France",NOW(),NOW());
insert into Posts (userId, title, content, createdAt, updatedAt) values (2, "black number", "kobare",NOW(),NOW());
insert into Posts (userId, title, content, createdAt, updatedAt) values (2, "Lemon", "kenshi",NOW(),NOW());

# DB Favorite 생성
insert into Favorites (userId, postId, createdAt, updatedAt) values (1,1,NOW(),NOW());
insert into Favorites (userId, postId, createdAt, updatedAt) values (1,2,NOW(),NOW());
insert into Favorites (userId, postId, createdAt, updatedAt) values (1,3,NOW(),NOW());
insert into Favorites (userId, postId, createdAt, updatedAt) values (2,2,NOW(),NOW());
insert into Favorites (userId, postId, createdAt, updatedAt) values (2,4,NOW(),NOW());

# DB Image 생성
insert into Images (postId, img, createdAt, updatedAt) values 
(1, "https://ac.namu.la/20210608/c163d40a5a686667362966e8fdcb4f46a1aa9d36023a88cf119b782944787561.png", NOW(),NOW());
insert into Images (postId, img, createdAt, updatedAt) values 
(1, "https://ac.namu.la/20210608/c163d40a5a686667362966e8fdcb4f46a1aa9d36023a88cf119b782944787562.png", NOW(),NOW());
insert into Images (postId, img, createdAt, updatedAt) values 
(2, "https://ac.namu.la/20210608/c163d40a5a686667362966e8fdcb4f46a1aa9d36023a88cf119b782944787563.png", NOW(),NOW());
insert into Images (postId, img, createdAt, updatedAt) values 
(3, "https://ac.namu.la/20210608/c163d40a5a686667362966e8fdcb4f46a1aa9d36023a88cf119b782944787564.png", NOW(),NOW());
insert into Images (postId, img, createdAt, updatedAt) values 
(3, "https://ac.namu.la/20210608/c163d40a5a686667362966e8fdcb4f46a1aa9d36023a88cf119b782944787565.png", NOW(),NOW());

# DB Tag 생성
insert into Tags (postId, tag, createdAt, updatedAt) values (1, "Scream", NOW(),NOW());
insert into Tags (postId, tag, createdAt, updatedAt) values (1, "namu", NOW(),NOW());
insert into Tags (postId, tag, createdAt, updatedAt) values (1, "nyang", NOW(),NOW());
insert into Tags (postId, tag, createdAt, updatedAt) values (2, "aeong", NOW(),NOW());
insert into Tags (postId, tag, createdAt, updatedAt) values (2, "namu", NOW(),NOW());
insert into Tags (postId, tag, createdAt, updatedAt) values (2, "France", NOW(),NOW());
insert into Tags (postId, tag, createdAt, updatedAt) values (2, "Lemon", NOW(),NOW());
insert into Tags (postId, tag, createdAt, updatedAt) values (3, "aeong", NOW(),NOW());
insert into Tags (postId, tag, createdAt, updatedAt) values (4, "aeong", NOW(),NOW());

# DB Follow 생성
insert into Follows (followUserId, followerUserId, createdAt, updatedAt) values (1, 2, NOW(),NOW());
insert into Follows (followUserId, followerUserId, createdAt, updatedAt) values (1, 3, NOW(),NOW());
insert into Follows (followUserId, followerUserId, createdAt, updatedAt) values (1, 4, NOW(),NOW());
insert into Follows (followUserId, followerUserId, createdAt, updatedAt) values (2, 3, NOW(),NOW());
insert into Follows (followUserId, followerUserId, createdAt, updatedAt) values (2, 4, NOW(),NOW());
insert into Follows (followUserId, followerUserId, createdAt, updatedAt) values (3, 1, NOW(),NOW());
insert into Follows (followUserId, followerUserId, createdAt, updatedAt) values (3, 2, NOW(),NOW());

# DB Alarm 생성
insert into Alarms (giverUserId, receiverUserId, type,  createdAt, updatedAt) values (1, 2, 1,  NOW(),NOW());
insert into Alarms (giverUserId, receiverUserId, type,  createdAt, updatedAt) values (1, 3, 1,  NOW(),NOW());
insert into Alarms (giverUserId, receiverUserId, type,  createdAt, updatedAt) values (1, 4, 1,  NOW(),NOW());
insert into Alarms (giverUserId, receiverUserId, type,  createdAt, updatedAt) values (2, 3, 1,  NOW(),NOW());
insert into Alarms (giverUserId, receiverUserId, type,  createdAt, updatedAt) values (2, 4, 1,  NOW(),NOW());
insert into Alarms (giverUserId, receiverUserId, type,  createdAt, updatedAt) values (3, 1, 1,  NOW(),NOW());
insert into Alarms (giverUserId, receiverUserId, type,  createdAt, updatedAt) values (3, 2, 1,  NOW(),NOW());
insert into Alarms (giverUserId, receiverUserId, type,  createdAt, updatedAt) values (1, 2, 3,  NOW(),NOW());
insert into Alarms (giverUserId, receiverUserId, type,  createdAt, updatedAt) values (1, 2, 3,  NOW(),NOW());
insert into Alarms (giverUserId, receiverUserId, type,  createdAt, updatedAt) values (3, 2, 3,  NOW(),NOW());
insert into Alarms (giverUserId, receiverUserId, type,  createdAt, updatedAt) values (4, 2, 3,  NOW(),NOW());





-- Sequelize의 migrations 및 models를 작성하는 순서 ☆
1. migrations의 기본키를 id가 아닌 테이블의 기본키 이름으로 변경한다
 ex) Favorites테이블 : favoriteId

2. migrations의 null설정을 추가해준다.
 ex) allowNull: false,

3. migrations의 세세한 변수 타입을 지정한다.
 ex) type: Sequelize.STRING(3000),

4. migrations의 외래키 관계를 정의한다.
 ex) references: {
         model: 'Posts',
         key: 'postId'
      },
     onDelete: 'cascade',

5. models의 데이터 타입과 변수들을 migrations 폴더와 동일하게 지정한다.
 ex) type: DataTypes.STRING(3000)

6. models의 기본키 설정 및 required 설정을 정의한다.
 ex) primaryKey:true,

7. models의 외래키 설정을 정의한다.
ex) Posts.associate = function (models) {
        models.Posts.hasMany(models.Users, {
            foreignKey:'userId',
            onDelete:'cascade',
        })
    }

select * from information_schema.table_constraints where table_name = "Posts";
 - 연결된 테이블의 외래키설정을 알아볼 수 있다.

alter table Posts drop FOREIGN KEY Posts_ibfk_2;
 - 설정된 외래키 Posts_ibfk_2를 삭제한다.



-- 정규표현식 
 - ?= : 전방 탐색 패턴, 등호(=) 다음에 일치할 텍스트가 오는 하위 표현식
email: 기본적인 이메일 검색
password : 최소한 1번의 특수문자와 숫자,영문자를 6~20글자 까지 허용한다.
nickname : 한글, 숫자, 영문자를 3~ 20글자까지 허용한다.





-- 팔로우를 추가하는 방법
1. 현재 추가하려는 followerUserId가 존재하는지 확인한다.
2. followUserId가 followerUserId를 이미 추가하지 않았는지 확인한다.
3. follows 테이블에 데이터를 삽입한다.


1. 
SELECT
CASE WHEN 7 IN (SELECT userId FROM Users) THEN "TRUE" ELSE "FALSE" END AS isExist;
 - 해당하는 userId가 존재한다면 TRUE, 없을경우 FALSE

2.
SELECT COALESCE(MIN('Y'), 'N') AS CHK
FROM Follows
WHERE EXISTS ( SELECT 1 
                     FROM Follows 
                     WHERE followUserId = 1 AND followerUserId = 7);
 - FollowUserId가 1이고 followerUserId가 7일 경우 Y 아닐 경우 N


3.
SELECT
CASE WHEN 4 IN (SELECT userId FROM Users) THEN "Y" ELSE "N" END AS isExist,
COALESCE(MIN('Y'), 'N') AS Following
FROM Follows
WHERE EXISTS ( SELECT 1 
             FROM Follows 
             WHERE followUserId = 1 AND followerUserId = 4);
 - userId=3이 존재할 경우 TRUE
 - followUserId=5 AND followerUserId=3 일 경우 TRUE
 - 즉, userId가 존재하고, 팔로워가 등록되어 있지 않은 경우는
   - isExist="TRUE", Following="FALSE"


COALESCE(A, 'A의 값이 없는 경우의 값')
 - 처음으로 NULL이 아닌 컬럼 값을 만나면 그 컬럼 값을 리턴한다.

express.req.method : 현재 호출된 router의 Method 형식을 출력한다.
 ex) DELETE
express.req.baseUrl : 현재 호출된 router의 주소를 출력한다.
 ex) /api/follow

 

 

-- /api/post/posts SQL문 작성

1. Posts 테이블을 가지고와 모든 데이터를 출력한다.
SELECT * FROM Posts;

2. Users 테이블을 가지고와 모든 데이터를 출력한다.
SELECT * FROM Users;

3.Users 와 Posts를 JOIN해 userId가 같은 경우만 출력하도록 설정한다.
SELECT u.userId, u.nickname, u.profileImg, p.postId, p.title, p.content, p.createdAt
FROM Posts AS p
JOIN Users AS u
on p.userId = u.userId;

4. favorites 테이블에서 userId를 지정해 해당하는 postId만 출력하도록 한다.
SELECT postId FROM Favorites WHERE userId=1;

5. favorites 테이블을 CASE WHEN 으로 TRUE FALSE를 출력한다.
SELECT u.userId, u.nickname, u.profileImg, p.postId, p.title, p.content, 
CASE WHEN p.postId IN (SELECT postId FROM Favorites WHERE userId=1) THEN "Y" ELSE "N" END AS favorite,
p.createdAt
FROM Posts AS p
JOIN Users AS u
on p.userId = u.userId;

6. favorites 테이블에서 reaction을 가져오기 위해 지정한 postId의 갯수를 출력한다.
SELECT COUNT(*) AS reactionCount
FROM Favorites
WHERE postId=2;

7. favorites 테이블에서 reaction값을 Posts.postId 별로 뿌려준다.
SELECT u.userId, u.nickname, u.profileImg, p.postId, p.title, p.content, 
CASE WHEN p.postId IN (SELECT postId FROM Favorites WHERE userId=1) THEN "Y" ELSE "N" END AS favorite,
(SELECT COUNT(*) FROM Favorites WHERE postId=p.postId) AS reactionCount,
p.createdAt
FROM Posts AS p
JOIN Users AS u
on p.userId = u.userId;


8. follow 테이블에서 userId를 지정해 TRUE FALSE를 출력한다.
SELECT COALESCE(MIN('Y'), 'N') AS follow
FROM Follows
WHERE EXISTS (SELECT 1
                FROM  Follows
                WHERE followUserId = 1 and followerUserId = 6);


9. 이전코드와 병합한다.
SELECT u.userId, u.nickname, u.profileImg, p.postId, p.title, p.content, 
CASE WHEN p.postId IN (SELECT postId FROM Favorites WHERE userId=1) THEN "Y" ELSE "N" END AS favorite,
(SELECT COALESCE(MIN('Y'), 'N') 
        FROM Follows
        WHERE EXISTS (SELECT 1 FROM  Follows WHERE followUserId = 1 and followerUserId=p.userId)) AS follow,
(SELECT COUNT(*) FROM Favorites WHERE postId=p.postId) AS reactionCount,
p.createdAt
FROM Posts AS p
JOIN Users AS u
on p.userId = u.userId;


10. Posts와 Img테이블을 동시에 가져와 해당하는 postId의 Img만 가져온다.
SELECT postId, img
from Images
where postId = 1;

JOIN으로 데이터를 가져와 보자

SELECT p.*, i.img
FROM Posts AS p
RIGHT JOIN Images AS i
on p.postId = i.postId;

SELECT p.*, i.img
FROM Posts AS p
LEFT JOIN Images AS i
on p.postId = i.postId;


11. Images 조인 추가
SELECT u.userId, u.nickname, u.profileImg, p.postId, p.reBlog, p.title, i.img, p.content, 
CASE WHEN p.postId IN (SELECT postId FROM Favorites WHERE userId=1) THEN "Y" ELSE "N" END AS favorite,
(SELECT COALESCE(MIN('Y'), 'N') 
        FROM Follows
        WHERE EXISTS (SELECT 1 FROM  Follows WHERE followUserId = 1 and followerUserId=p.userId)) AS follow,
(SELECT COUNT(*) FROM Favorites WHERE postId=p.postId) AS reactionCount,
p.createdAt
FROM Posts AS p
JOIN Users AS u
on p.userId = u.userId
LEFT JOIN Images AS i
on p.postId = i.postId;

12. Tags 조인으로 Posts를 기준으로 Tag를 가지고 오도록 설정한다.

SELECT p.*, t.tag
FROM Posts AS p
LEFT JOIN Tags AS t
on p.postId = t.postId

13. 최종으로 합치자.
SELECT u.userId, u.nickname, u.profileImg, p.postId, p.reBlog, p.title, i.img, p.content, t.tag,
CASE WHEN p.postId IN (SELECT postId FROM Favorites WHERE userId=1) THEN "Y" ELSE "N" END AS favorite,
(SELECT COALESCE(MIN('Y'), 'N') 
        FROM Follows
        WHERE EXISTS (SELECT 1 FROM  Follows WHERE followUserId = 1 and followerUserId=p.userId)) AS follow,
(SELECT COUNT(*) FROM Favorites WHERE postId=p.postId) AS reactionCount,
p.createdAt
FROM Posts AS p
JOIN Users AS u
on p.userId = u.userId
LEFT JOIN Images AS i
on p.postId = i.postId
LEFT JOIN Tags AS t
on p.postId = t.postId;

14. tag와 img의 기본키가 곱연산 되는 문제를 해결하자.