Day 12-2 : 솔직히 본인 테이블에 더미 데이터 1,000 개 넣어본 사람? 없으면 보자, 프로시저로 더미 데이터 생성하기

Day 12-2 : 솔직히 본인 테이블에 더미 데이터 1,000 개 넣어본 사람? 없으면 보자, 프로시저로 더미 데이터 생성하기

2023. 6. 8. 11:42ㆍInternational Sign Lang 프로젝트/백엔드

728x90

[Day 12-1] CSRF 와 CORS 는 매우 연관이 깊다?!

Day 11 : 싸우자 해커야! CSRF, HttpOnly, CSP 대응하기 Day 10 : MySQL2 오류, "Client does not support authentication protocol requested by server" 해결 방법 Day 9 : 에러나면 손모가지 날라가붕께 더보기 Day 8 : 가상 DOM 생성

xpmxf4.tistory.com

위 글을 보고 오시는 것을 추천드립니다!

1. 오늘의 주제

하루에 하나만 했으면 참 좋았을 텐데

처음으로 하루에 2 개의 글을 써보네요...

각설하고 이번에 해야 할 일은 통계 API 만들기입니다!

2. 주제에 대한 간략한 설명

ISL의 가장 큰 핵심 기능은 나라에다가 개인이 법규를 날린다는 것이지만

그와 동시에 또 중요한 핵심은 나 외에 다른 사람들은 어디에다가 법규를 날리냐를

보여주는 것입니다.

통계 API들이 바로 이 부분을 해결해 줄 것입니다!

법규에 대한 통계를 내는 데 있어서 저는 기간으로 기준을 내

총 4 가지로 설계했습니다.

일간
주간
월간
전체 기간

위 업무는 크게 2 가지의 태스크로 쪼갤 수 있습니다.

DBMS 에다가 쿼리를 날릴 쿼리문 작성
사용자의 요청에만 DBMS 에다가 쿼리를 날리도록 하는 API 작성.

3. 코드로 실제로 구현해 보기

먼저 쿼리문 작성하는 것을 바로 볼까요?

-- 주간 통계
SELECT to_country, COUNT(*) AS count
FROM isl
WHERE date >= DATE_SUB(NOW(), INTERVAL 1 WEEK)
GROUP BY to_country
ORDER BY count DESC
LIMIT 5;

제가 작성해아 할 쿼리들은 다 비슷해서

그중 하나인 주간 통계 SQL 쿼리문을 가져왔습니다.

그리고 이를 API에 붙여보도록 하겠습니다!

아래 코드는 일단 너무 길고 더러운 코드임을 미리 알려드려요!

엄청 가볍게 넘어가주세요!

router.get('/top5', () => {
    const queryAll = `SELECT to_country, COUNT(*) AS count FROM isl GROUP BY to_country ORDER BY count DESC LIMIT 5`;
    const queryMonthly = `SELECT to_country, COUNT(*) AS count, MONTH(date) AS month, YEAR(date) AS year FROM isl WHERE date >= DATE_SUB(NOW(), INTERVAL 1 MONTH) GROUP BY to_country, month, year ORDER BY count DESC LIMIT 5`;
    const queryWeekly = `SELECT to_country, COUNT(*) AS count, WEEK(date) AS week, YEAR(date) AS year FROM isl WHERE date >= DATE_SUB(NOW(), INTERVAL 1 WEEK) GROUP BY to_country, week, year ORDER BY count DESC LIMIT 5`;
    const queryDaily = `SELECT to_country, COUNT(*) AS count, DATE(date) AS date FROM isl WHERE DATE(date) = CURDATE() GROUP BY to_country, date ORDER BY count DESC LIMIT 5`;

    const result = {};

    db.query(queryAll, (err, rows) => {
        if (err) {
            console.log('error executing query : ', err);
            result.all = []
        } else {
            result.all = rows;
        }
    })

    db.query(queryMonthly, (err, rows) => {
        if (err) {
            console.log('error executing query : ', err);
            result.monthly = []
        } else {
            result.monthly = rows;
        }
    })

    db.query(queryWeekly, (err, rows) => {
        if (err) {
            console.log('error executing query : ', err);
            result.weekly = []
        } else {
            result.weekly = rows;
        }
    })

    db.query(queryDaily, (err, rows) => {
        if (err) {
            console.log('error executing query : ', err);
            result.daily = []
        } else {
            result.daily = rows;
        }
    })

    res.json(result);
})

module.exprots = router;

이걸 보셨다면 아마 몇몇 분들은 이런 말씀을 하실 거예요.

저거 안될 텐데? 쟤 뭐 하냐? 그리고 코드도 너무 더러워.

맞습니다. 위 코드는 정말 더럽고,

심지어 작동도 하지 않습니다.

이 지적을 한번 뜯어보자면

작동을 안 한다.
위 코드는 작동을 안 하게 됩니다.
그 이유는 router.get 안에서 비동기로 DB 에다가 쿼리를 날리게 되는 형태입니다만,
쿼리의 결과물이 오기도 전에 res.json(result) 코드가 작동해
빈 json 객체가 프론트엔드로 전달이 될 겁니다.
코드가 너무 더럽다.
쿼리를 미리 작성하고 변수에 저장한 뒤 활용하는 형태이지만
위처럼 긴 문자열이 코드 안에 있다면 너무 보기가 싫은,
지적에 나온 대로 너무 더럽습니다.

그렇다면 이 2 가지를 개선한 코드로 바로 넘어가 보겠습니다!

4. 코드 개선하기

먼저 더러운 코드를 개선해 보겠습니다!

사람마다 개선하는 방법을 다르게 생각하겠지만

저의 경우에는 앞으로도 SQL 쿼리를 추가로 생성할 일이 있다고 생각했습니다.

그래서 별도의 폴더와 파일들로 쿼리문들을 관리하자는 생각을 하게 되었고,

위처럼 구분을 하고 파일을 읽어오는 식으로 구현했습니다!

const queryAll = readQueryFromFile('../sql/query_all.sql');
const queryMonthly = readQueryFromFile('../sql/query_monthly.sql');
const queryWeekly = readQueryFromFile('../sql/query_weekly.sql');
const queryDaily = readQueryFromFile('../sql/query_daily.sql');

훨씬 깔끔하죠!

다음은 비동기 처리입니다!

위 코드는 사실 Promise Chaining을 활용해도 해결이 가능합니다!

하지만 4개의 쿼리문을 일일이 실행, 결괏값을 변수에 저장 등등을

프로미스로 체이닝을 하게 된다면 코드가 다시 한번 더럽게 됩니다.

그래서 Promise와 async/await을 통해 이쁘지만 목적을 달성하는,

두 마리 토끼를 전부 다 잡는 코드를 짜봤습니다!

// wrap query execution with Promise
const executeQuery = query => {
    return new Promise((res, rej) => {
        db.query(query, (err, rows) => {
            if (err) {
                rej(err);
            } else {
                res(rows);
            }
        })
    })
}

// read query from sql file
const readQueryFromFile = fileName => {
    const filePath = path.join(__dirname, fileName);
    return fs.readFileSync(filePath, 'utf-8');
}

router.get('/top5', async (req, res) => {
    try {
        const queryAll = readQueryFromFile('../sql/query_all.sql');
        const queryMonthly = readQueryFromFile('../sql/query_monthly.sql');
        const queryWeekly = readQueryFromFile('../sql/query_weekly.sql');
        const queryDaily = readQueryFromFile('../sql/query_daily.sql');

        const result = {};

        result.all = await executeQuery(queryAll);
        result.monthly = await executeQuery(queryMonthly);
        result.weekly = await executeQuery(queryWeekly);
        result.daily = await executeQuery(queryDaily);

        // cache deactivate
        res.set('Cache-Control', 'no-cache, no-store, must-revalidate');
        res.set('Pragma', 'no-cache');
        res.set('Expires', '0');

        res.json(result);
    } catch (err) {
        res.status(500).json({
            error: err
        })
    }
})

module.exports = router;

5. 아직 한 발 남았다...

위 처럼 쿼리문을 짜면서 작동하는 쿼리문인지

실제로 DBMS에 날려보다가 문득 이런 생각이 들었습니다.

통계 쿼리문이 틀리지는 않은 거 같은데... 대량의 데이터가 있어야 좀 실제 상황 같지 않을까?

그래서 더미 데이터를 한 1,000개 정도 넣어보자 라는 생각을 했습니다.

하지만 일일히 1,000개를 넣는 건 정말 말이 안 됩니다.

응당 개발자라면 더미 데이터를 집어넣는 것을 구현해 보자라 생각하다

과거 학교에서 배운 프로시저를 생각했습니다!

SQL 프로시저(Stored Procedure)는 SQL에서 사용할 수 있는 강력한 도구 중 하나로,
특정 작업을 수행하기 위한 일련의 SQL 명령문을 하나로 묶은 것을 말합니다.
Stored Procedure는 효율적인 데이터 처리와 코드의 재사용성, 보안성을 높이기 위해 사용됩니다.
마치 일반 언어의 함수와 같은 개념인거죠.

프로시저의 장점
코드 재사용: 한 번 작성된 프로시저는 반복적으로 호출되어 사용할 수 있습니다.
성능 향상: 프로시저는 DBMS(데이터베이스 관리 시스템)에서 컴파일된 상태로 저장되므로, 호출 시 빠르게 실행됩니다.
보안: 데이터베이스에 직접 접근하는 것보다 프로시저를 통해 데이터를 처리하는 것이 안전합니다.

-- 문법
CREATE PROCEDURE procedure_name ([parameter1 [type1], ...])
  [LANGUAGE {SQL | language_name}]
  [SQL DATA ACCESS {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}]
  [COMMENT 'string']
  BEGIN
    sql_statement;
  END;


-- 예시
DELIMITER $$

CREATE PROCEDURE GetStudentCount()
BEGIN
  SELECT COUNT(*) FROM students;
END $$

DELIMITER ;

-- 호출
CALL GetStudentCount();

즉 프로시저가 지금 저의 상황을 해결해 줄 수 있는 솔루션 중 하나이겠네요!

6. 이제 진짜 마지막! 찐찐막!

여기까지 읽느라 지칠 여러분을 위해 바로 프로시저 생성 코드와

작동법으로 마무리하겠습니다! ㅠㅠ

DELIMITER //

CREATE PROCEDURE generate_data(IN num_rows INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE countries CHAR(70) DEFAULT 'AR,AU,BR,CA,CN,FR,DE,IN,ID,IT,JP,MX,RU,SA,ZA,KR,TR,GB,US';
  WHILE i < num_rows DO
    INSERT INTO isl (from_country, to_country, date)
    VALUES (
        SUBSTRING_INDEX(SUBSTRING_INDEX(countries, ',', 1 + FLOOR(RAND() * 19)), ',', -1),
        SUBSTRING_INDEX(SUBSTRING_INDEX(countries, ',', 1 + FLOOR(RAND() * 19)), ',', -1),
        DATE_ADD(NOW(), INTERVAL - FLOOR(RAND() * 365) DAY)
    );
    SET i = i + 1;
  END WHILE;
END //

DELIMITER ;

마무리

언제나 계획한 거보다 실제로 하다 보면 새로운 고민들이 떠오르지만

잘 해결돼서 다행이고 재미있습니다! ㅎㅎ

피드백은 언제나 환영입니다!

GitHub - xpmxf4/ISL_FrontEnd

Contribute to xpmxf4/ISL_FrontEnd development by creating an account on GitHub.

github.com

GitHub - xpmxf4/ISL_BackEnd

Contribute to xpmxf4/ISL_BackEnd development by creating an account on GitHub.

github.com

만약 보신다면 스타도...! ㅎㅎ

728x90

'International Sign Lang 프로젝트 > 백엔드' 카테고리의 다른 글

Day 14 : 사공이 많으면 배가 산으로 간다, Lambda + Express 에서 CORS 처리하는 법 (0)	2023.06.19
Day 13 : ??? : 나 serverless deploy 쳐본 사람이야 (0)	2023.06.15
Day 12-1 CSRF 와 CORS 는 매우 연관이 깊다?! (0)	2023.06.08
Day 11 : 싸우자 해커야! CSRF, HttpOnly, CSP 대응하기 (0)	2023.06.02
Day 9 : 에러나면 손모가지 날라가붕께 (2)	2023.05.30

블로그이름을고민하다보니이거제목의길이가어디까지일지궁금하게되어이렇게해봤습니다