search

UMCreadme / Read_Me_Node.js

노드 저장소
0 stars 2 forks source link

:sparkles: [FEAT] 더미데이터 쌓기 #71

Closed SuHyeon00 closed 1 month ago

SuHyeon00 commented 1 month ago

:sparkles: 어떤 기능인가요?

:memo: 작업 상세 내용

:globe_with_meridians: 참고할만한 자료(선택)

SuHyeon00 commented 1 month ago

유저 더미데이터 쌓기

DROP PROCEDURE IF EXISTS InsertUsers;
-- 100개의 사용자 데이터를 반복적으로 삽입하는 저장 프로시저
DELIMITER $$

CREATE PROCEDURE InsertUsers()
BEGIN
    DECLARE i INT DEFAULT 1;

    WHILE i <= 100 DO
            INSERT INTO USERS (unique_id, email, nickname, account, image_url, provider)
            VALUES (CONCAT(i, 'uniqueid'), CONCAT('user', i, '@example.com'), CONCAT('nickname', i), CONCAT('account', i), 'https://readme-image.s3.ap-northeast-2.amazonaws.com/profile/default-profile.png', 'kakao');
            SET i = i + 1;
        END WHILE;
END $$

DELIMITER ;

-- 저장 프로시저 실행
CALL InsertUsers();

유저 선호 카테고리 쌓기

DROP PROCEDURE IF EXISTS getRandomCategoryIds;
-- 카테고리 리스트를 랜덤하게 생성하는 MySQL 함수
DELIMITER $$

CREATE FUNCTION getRandomCategoryIds()
    RETURNS JSON
    DETERMINISTIC
BEGIN
    DECLARE categoryIds JSON DEFAULT JSON_ARRAY();
    DECLARE i INT DEFAULT 1;
    DECLARE count INT;

    SET count = FLOOR(4 + RAND() * 5);

    WHILE i <= count DO
            SET categoryIds = JSON_ARRAY_APPEND(categoryIds, '$', FLOOR(1 + RAND() * 21));
            SET i = i + 1;
        END WHILE;

    RETURN categoryIds;
END $$

DELIMITER ;

DROP PROCEDURE IF EXISTS InsertUserFavorites;
-- 사용자 4번부터 103번까지의 선호 카테고리를 USER_FAVORITE 테이블에 삽입하는 저장 프로시저
DELIMITER $$

CREATE PROCEDURE InsertUserFavorites()
BEGIN
    DECLARE user_idx INT DEFAULT 4;
    DECLARE category_idx INT;
    DECLARE category_list JSON;
    DECLARE category_count INT;

    WHILE user_idx <= 103 DO
            SET category_list = getRandomCategoryIds();
            SET category_count = JSON_LENGTH(category_list);
            SET category_idx = 0;

            WHILE category_idx < category_count DO
                    INSERT INTO USER_FAVORITE (user_id, category_id)
                    VALUES (user_idx, JSON_UNQUOTE(JSON_EXTRACT(category_list, CONCAT('$[', category_idx, ']'))));
                    SET category_idx = category_idx + 1;
                END WHILE;

            SET user_idx = user_idx + 1;
        END WHILE;
END $$

DELIMITER ;

-- 저장 프로시저 실행
CALL InsertUserFavorites();
SuHyeon00 commented 1 month ago

쇼츠 더미데이터 생성

-- 프로시저 정의 시작
DELIMITER $$

CREATE PROCEDURE insert_1000_shorts()
BEGIN
    DECLARE v_user_id BIGINT;
    DECLARE v_book_id BIGINT;
    DECLARE v_tag VARCHAR(100);
    DECLARE v_num_tags INT;
    DECLARE v_tags TEXT;
    DECLARE tag1 VARCHAR(30) DEFAULT '판타지소설추천';
    DECLARE tag2 VARCHAR(30) DEFAULT '이건볼수밖에없';
    DECLARE tag3 VARCHAR(30) DEFAULT '사건은다가와아오에';
    DECLARE tag4 VARCHAR(30) DEFAULT '필독도서';
    DECLARE i INT DEFAULT 0;

    WHILE i < 1000 DO
            -- 랜덤한 user_id (3 ~ 104)
            SET v_user_id = FLOOR(3 + (RAND() * (104 - 3 + 1)));

            -- 랜덤한 book_id (1 ~ 2894)
            SET v_book_id = FLOOR(1 + (RAND() * (2894 - 1 + 1)));

            -- 랜덤하게 선택할 태그 수 (0 ~ 3)
            SET v_num_tags = FLOOR(RAND() * 4);

            -- 초기화
            SET v_tags = '';

            -- 태그를 랜덤하게 선택
            IF v_num_tags > 0 THEN
                -- 태그 1 선택
                IF RAND() < 0.5 THEN
                    SET v_tags = CONCAT(v_tags, tag1, '|');
                END IF;
                -- 태그 2 선택
                IF RAND() < 0.5 THEN
                    SET v_tags = CONCAT(v_tags, tag2, '|');
                END IF;
                -- 태그 3 선택
                IF RAND() < 0.5 THEN
                    SET v_tags = CONCAT(v_tags, tag3, '|');
                END IF;
                -- 태그 4 선택
                IF RAND() < 0.5 THEN
                    SET v_tags = CONCAT(v_tags, tag4, '|');
                END IF;

                -- 마지막 구분자 제거
                SET v_tags = TRIM(TRAILING '|' FROM v_tags);
            END IF;

            INSERT INTO SHORTS (user_id, book_id, image_url, phrase, title, content, tag)
            VALUES (
                       v_user_id,
                       v_book_id,
                       'https://readme-image.s3.ap-northeast-2.amazonaws.com/shorts/1722013550442_shorts-image-ex.jpg', -- 고정된 이미지 URL
            CONCAT('Sample phrase ', i),  -- phrase
            CONCAT('Sample title ', i),   -- title
            CONCAT('Sample content ', i), -- content
            v_tags
        );

        -- 반복 카운터 증가
        SET i = i + 1;
    END WHILE;
END $$

DELIMITER ;

CALL insert_1000_shorts();
Sunho12 commented 1 month ago

팔로워 더미 데이터 생성

DELIMITER //

CREATE PROCEDURE InsertUniqueRandomFollows()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE random_user_id INT;
    DECLARE random_follower INT;
    DECLARE exit_flag BOOLEAN DEFAULT FALSE;

    WHILE i <= 200 DO
        -- Generate random user_id and follower within the range
        SET random_user_id = FLOOR(3 + (RAND() * (102 - 3)));
        SET random_follower = FLOOR(3 + (RAND() * (102 - 3)));

        -- Check if the combination already exists
        IF NOT EXISTS (
            SELECT 1 FROM FOLLOW 
            WHERE user_id = random_user_id
            AND follower = 3
        ) THEN
            -- Insert into FOLLOW table if unique
            INSERT INTO FOLLOW (user_id, follower) VALUES (random_user_id, random_follower);
            SET i = i + 1;
        END IF;
    END WHILE;
END //

DELIMITER ;

CALL InsertUniqueRandomFollows();
SuHyeon00 commented 1 month ago

책 더미데이터 생성 코드 (service.js)

export const createDummyBookDataService = async () => {
    // 알라딘 상품 리스트 조회 API 호출
    let type = "Bestseller";
    let start = 1;

    // API 호출
    while(start <= 20){
        let url = `http://www.aladin.co.kr/ttb/api/ItemList.aspx?ttbkey=${process.env.TTB_KEY}&QueryType=${type}&MaxResults=50&start=${start}&SearchTarget=FOREIGN&output=js&Version=20131101`;
        axios.get(url)
            .then(response => {
            // JSON 데이터 파싱 후 DTO로 변환
            const bookDataList = response.data.item;
            const bookList = bookListInfoDto(bookDataList);

            // 카테고리 ID 수정
            bookList.forEach(async book => {
                let bookId = await getBookIdByISBN(book.ISBN);
                if(!bookId) {
                    // 책이 DB에 없을 경우 책 저장
                    let categoryId = await getCategoryIdByAladinCid(parseInt(book.category_id));
                    if(categoryId) {
                        book.category_id = categoryId;
                        await createBook(book);
                    }
                }
            });
        }).catch(error => {
            console.error('Error fetching data from API:', error);
        });
        start++;
    }
};
SuHyeon00 commented 1 month ago

책 더미데이터 생성에 쓰이는 dto

import { BaseError } from "../../config/error.js";
import { status } from "../../config/response.status.js";

export const bookListInfoDto = (data) => {
    const mallType = ['BOOK', 'EBOOK', 'FOREIGN'];
    const result = data.map(book => {
        if (!mallType.includes(book.mallType)) {
            return null;
        }

        if(book.mallType === 'EBOOK') {
            book.title = `[EBOOK] ${book.title}`;
        }

        const author = book.author.split(' (지은이)')[0].trim();

        return {
            "ISBN": book.isbn13 ? book.isbn13 : book.isbn,
            "title": book.title,
            "category_id": book.categoryId,
            "image_url": book.cover,
            "author": author,
            "link": book.link
        }
    })

    return result;
};
SuHyeon00 commented 1 month ago

찜한 쇼츠 더미데이터 생성

DROP PROCEDURE IF EXISTS InsertUniqueRandomLikeShorts;

DELIMITER //

CREATE PROCEDURE InsertUniqueRandomLikeShorts()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE random_user_id INT;
    DECLARE random_shorts_id INT;
    DECLARE exit_flag BOOLEAN DEFAULT FALSE;

    WHILE i <= 200 DO
            -- Generate random user_id and follower within the range
            SET random_user_id = FLOOR(3 + (RAND() * (102 - 3)));
            SET random_shorts_id = FLOOR(1021 + (RAND() * (2327 - 1021)));

            -- Check if the combination already exists
            IF NOT EXISTS (
                SELECT 1 FROM LIKE_SHORTS
                WHERE user_id = random_user_id
                  AND shorts_id = random_shorts_id
            ) THEN
                -- Insert into FOLLOW table if unique
                INSERT INTO LIKE_SHORTS (user_id, shorts_id) VALUES (random_user_id, random_shorts_id);
                SET i = i + 1;
            END IF;
        END WHILE;
END //

DELIMITER ;

CALL InsertUniqueRandomLikeShorts();