🎯 학습목표

이전 데이터베이스 학습에 이어서 MySQL을 실습해보자.

• docker 컨테이너로 MySQL을 설치하고 PC방 관리자 DB를 설계해보자.
• MySQL 어댑터로 Node.js와 DB를 연결해서 데이터를 생성하고 삭제하는 등 명령을 주고 받아보자.

🚫 SQL을 처음 사용해봤기에 잘못된 코드일 수 있습니다...(돌아는 갑니다..😅)

🌱 개념학습

MySQL 어댑터

• 데이터베이스와 상호 작용하는데 사용되는 소프트웨어 모듈이다.
• nodeJS 같은 프로그래밍 언어에서 MySQL 데이터베이스에 엑세스하는데 사용된다.
• MySQL 어댑터로 데이터베이스에 연결하여 데이터를 조회, 삽입, 업테이트 및 삭제를 할 수 있다.
• Node.js에서 사용되는 MySQL 어댑터로는 mysql 모듈이 대표적이다.

mysql 모듈

mysql 모듈은 Node.js에서 MySQL 데이터베이스에 엑세스하는 데 사용되는 공식 어댑터다.
이 모듈을 통해 MySQL 서버와 통신하고 데이터베이스에 쿼리를 실행할 수 있는 인터페이스를 제공 받는다.

DB Connection

Node.js에서 MySQL 데이터베이스와 상호작용하는 것을 뜻하며,
mysql모듈에서 제공하는 'createConnection()' 메서드를 사용하여 MySQL 서버와 연결 할 수 있다.

const mysql = require("mysql");

// 연결 정보 셋팅
const connection = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword",
  database: "yourdatabase",
});

// 연결 메서드 실행
connection.connect((err) => {
  if (err) {
    console.error("Error connecting to MySQL database: " + err.stack);
    return;
  }

  console.log(
    "Connected to MySQL database with connection id " + connection.threadId
  );

  // 테이블 생성
  const sql =
    "CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), email VARCHAR(255))";
  connection.query(sql, (err, result) => {
    if (err) throw err;
    console.log("Table created successfully");
  });
});

인덱스

인덱스란 DB에서 데이터를 검색하거나 정렬할 때 빠르게 처리할 수 있도록 돕는 자료 구조다.

Qurey

MySQL 데이터베이스와 연결이 설정되면,
qurey() 메서드를 사용하여 데이터를 조회, 삽입, 업데이트, 삭제 등의 작업을 수행할 수 있다.

SELECT

데이터를 '조회' 하는 쿼리.

const sql = "SELECT * FROM customers";
connection.query(sql, (err, result) => {
  if (err) throw err;
  console.log(result);
});

UPDATE

데이터를 '업데이트' 하는 쿼리.

const sql = "UPDATE customers SET name = ? WHERE id = ?";
const values = ["Jane Doe", 1];
connection.query(sql, values, (err, result) => {
  if (err) throw err;
  console.log("Number of records updated: " + result.affectedRows);
  // affectedRows 업데이트된 레코드 수 확인
});

INSERT

데이터를 '삽입' 하는 쿼리.

const sql = "INSERT INTO customers (name, email) VALUES (?, ?)";
const values = ["John Doe", "john@example.com"];
connection.query(sql, values, (err, result) => {
  if (err) throw err;
  console.log("Number of records inserted: " + result.affectedRows);
  // affectedRows 삽입된 레코드 수 확인
});

DELETE

데이터를 '삭제' 하는 쿼리.

const sql = "DELETE FROM customers WHERE id = ?";
const values = [1];
connection.query(sql, values, (err, result) => {
  if (err) throw err;
  console.log("Number of records deleted: " + result.affectedRows);
});

ON UPDATE CASCADE & ON DELETE CASCADE

ON UPDATE CASCADE와 ON DELETE CASCADE는 데이터베이스에서 외래키 제약 조건을 설정할 때 사용되는 옵션이다.

ON UPDATE CASCADE는 참조하는 테이블의 기본 키 값이 변경될 때,
이에 연관된 다른 테이블의 외래키 값을 자동으로 갱신하는 옵션이다.
예를 들어, 부서 테이블과 직원 테이블이 있을 때, 직원 테이블의 부서 ID가 부서 테이블의 기본 키를 참조하는 외래키일 경우,
부서 테이블에서 부서 ID가 변경될 때, 직원 테이블의 부서 ID도 자동으로 변경된다.

ON DELETE CASCADE는 참조하는 테이블의 기본 키 값이 삭제될 때,
이에 연관된 다른 테이블의 외래키 값을 자동으로 삭제하는 옵션이다.
예를 들어, 부서 테이블과 직원 테이블이 있을 때, 부서 테이블의 부서 ID가 기본 키이고,
직원 테이블의 부서 ID가 부서 테이블의 기본 키를 참조하는 외래키일 경우,
부서 테이블에서 부서를 삭제할 때, 해당 부서에 소속된 직원도 자동으로 삭제된다.

즉, ON UPDATE CASCADE와 ON DELETE CASCADE는 참조하는 테이블의 데이터가 변경되거나 삭제될 때,
이에 연관된 다른 테이블의 데이터도 자동으로 변경 또는 삭제되도록 하는 옵션이다.
이를 통해 데이터 일관성을 유지할 수 있다.

트랜잭션(Transaction)

트랜잭션(Transaction)은 데이터베이스의 상태를 변화시키기 위해 수행하는 작업의 단위를 의미한다.
일련의 작업들이 하나의 논리적인 단위로 묶여서 전부 실행되거나 전부 실행되지 않아야 하는 것을 보장한다.

트랜잭션은 보통 데이터베이스에서 데이터를 변경하는 작업을 할 때 사용된다.
예를 들어, 고객 정보를 추가하고 그에 대한 주문 정보를 추가하는 경우, 이 작업들은 하나의 논리적인 단위로 묶여서 실행되어야 한다.
만약 이 중 하나의 작업이 실패하면 다른 작업도 취소되어야 하며,
모든 작업이 성공적으로 수행되어야 데이터베이스의 일관성이 유지된다.

트랜잭션은 ACID 원칙을 준수해야 한다.

• 원자성(Atomicity) : 트랜잭션의 모든 작업이 전부 실행되거나 전부 실행되지 않아야 한다.
• 일관성(Consistency) : 트랜잭션의 실행 결과는 일관성이 있어야 한다.
• 격리성(Isolation) : 트랜잭션이 다른 트랜잭션에 영향을 미치지 않아야 한다.
• 지속성(Durability) : 트랜잭션이 완료된 후에는 영구적으로 데이터베이스에 적용되어야 한다.

트랜잭션은 데이터베이스에서 지원하는 기능으로, 일반적으로 SQL 명령어를 사용하여 구현된다.
MySQL에서는 BEGIN, COMMIT, ROLLBACK 등의 명령어를 사용하여 트랜잭션을 처리할 수 있다.

🔥 구현해보기

👨‍🎓 구현 목표

• docker 기반의 MySQL에 PC방 관리 DATABASE를 생성한다.
• PC방에 손님이 방문하면 방문 시간을 체크하고 사용가능한 PC를 랜덤으로 할당한다.
• 손님은 고유 ID를 보유하며 각 PC도 고유 ID를 가진다.
• 손님이 사용을 종료하면 종료시간이 확인된다.
• 사용 종료된 PC는 다시 사용 가능한 PC로 상태를 변경한다.

1. Create DATABASE

Docker에 생성한 MySQL에 root로 접속한다.

docker exec -it sarang-mysql bash

새로운 데이터베이스 PcManagerDB를 생성한다.

create database PcManagerDB;

사용자에게 PcManagerDB 사용권한을 부여한다.

grant all privileges on PcManagerDB.* to sarang@'%';
flush privileges;

2. Create Connection with DB by Node.js

MySQL 어댑터인 mysql 모듈을 다운로드 받는다.

npm install mysql

mysql 모듈을 사용하여 PcManagerDB와 연결한다.

const mysql = require("mysql");

const connection = mysql.createConnection({
  host: "localhost",
  user: "sarang",
  password: "1234",
  database: "PcManagerDB",
  port: "3307",
});

connection.connect((err) => {
  if (err) {
    console.error("Error connecting to MySQL database : " + err.stack);
  }
  return;
});

3. Create Table

테이블 구상

테이블 생성

// customer 테이블 생성 쿼리
const createCustomerTable = `
    CREATE TABLE customer (
      ID INT AUTO_INCREMENT PRIMARY KEY, // AUTO_INCREMENT : 자동으로 ID를 1부터 할당
      START TIME,
      END TIME,
      PC_ID INT
    )`;

// pc 테이블 생성 쿼리
const createPcTable = `
    CREATE TABLE pc (
      PC_ID INT PRIMARY KEY,
      customer_ID INT,
      STATUS VARCHAR(10)
    )`;

// 외래키 설정 쿼리
const addForeignKey = `ALTER TABLE pc ADD CONSTRAINT fk_customer_id FOREIGN KEY (customer_ID) REFERENCES customer(ID)`;

// 테이블 존재 확인 쿼리
const showCustomerTable = `SHOW TABLES LIKE 'customer';`;
const showPcTable = `SHOW TABLES LIKE 'pc';`;

// customer table 생성 쿼리 실행
connection.query(showCustomerTable, (err, res, fields) => {
  if (err) throw err;

  if (res.length === 0) {
    connection.query(createCustomerTable, (err, res, fields) => {
      if (err) throw err;
      console.log("customer 테이블 생성 완료");
    });
  } else {
    console.log("customer 테이블 존재함");
  }
});

// pc table 생성 쿼리 실행
connection.query(showPcTable, (err, res, fields) => {
  if (err) throw err;
  if (res.length === 0) {
    connection.query(createPcTable, (err, res, fields) => {
      if (err) throw err;
      console.log("pc 테이블 생성 완료");

      // pc테이블 customer_ID에 외래키 제약조건 추가
      connection.query(addForeignKey, (err, res, fields) => {
        if (err) throw err;
      });
    });
  } else console.log("pc 테이블 존재함");
});

4. Using Query (Data Management)

PC ID 할당

사용가능한 PC는 16대를 고정으로 가진다고 가정한다.

const insertPcData = () => {
  const values = [];
  for (let i = 1; i < 17; i++) {
    values.push([i]);
  }

  const addPcDataQuery = "INSERT INTO pc (PC_ID) VALUES ?";

  connection.query(addPcDataQuery, [values], (err, res, fields) => {
    if (err) {
      console.error(err);
      return;
    }
    console.log("Pc 16대가 세팅되었습니다.");
  });
};

사용 가능한 PC 확인 함수

// 현재 사용 가능한 PC 리스트 호출
const showPcStatus = () => {
  const selectAllPcQuery = "SELECT * FROM pc WHERE STATUS IS NULL";

  connection.query(selectAllPcQuery, (err, res, fields) => {
    if (err) return;
    const pcIds = res.map((v) => v.PC_ID);
    console.log(JSON.stringify(pcIds));
  });
};

new 고객 삽입

// 새로운 손님이 오면 ID 부여하고 사용 가능 PC중 랜점으로 PC_ID 선정
const insertNewCustomer = () => {
  const insertNewCustomerQuery = `INSERT INTO customer (PC_ID, START) VALUES (?, CURRENT_TIME())`;

  // ORDER BY RAND() 결과를 랜덤으로 정렬하는 쿼리 LIMIT 1 은 결과를 1개만 리턴 (랜덤값에 활용하는 쿼리)
  const selectAvailablePcQuery = `SELECT PC_ID FROM pc WHERE STATUS IS NULL ORDER BY RAND() LIMIT 1`;
  const updatePcCusIdQuery = `UPDATE pc SET CUSTOMER_ID = ?, STATUS = 'using' WHERE PC_ID = ?`;

  connection.beginTransaction((err) => {
    if (err) throw err;

    // 사용 가능 PC가 있는지 PC Table status를 보고 확인한다.
    connection.query(selectAvailablePcQuery, (err, res) => {
      if (err) rollbackAndThrowError();

      // 모든 PC가 status = 'using' 이면 종료
      if (res.length === 0) {
        console.log("모든 PC가 사용중입니다.");
        return connection.rollback(() => {
          connection.end();
        });
      }

      // ORDER BY RAND() 로 가져온 랜덤 PC_ID 값
      const randomPcId = res[0].PC_ID;

      connection.query(insertNewCustomerQuery, [randomPcId], (err, res) => {
        if (err) rollbackAndThrowError();

        // 새로 추가된 고객 ID
        const getCustomerId = res.insertId;

        connection.query(
          updatePcCusIdQuery,
          [getCustomerId, randomPcId],
          (err, res) => {
            if (err) rollbackAndThrowError();

            connection.commit((err) => {
              if (err) rollbackAndThrowError();
            });
          }
        );
        console.log(`${randomPcId}번 자리에 앉으세요 : #${getCustomerId}`);
        showPcStatus();
      });
    });
  });
};

특정 고객 사용 중지

// 특정 고객이 PC사용을 중지하면 특정 고객의 PC_ID를 리셋하고 pc테이블의 PC_ID의 고객정보과 상태도 NULL로 업데이트한다.
const stopCustomer = (id) => {
  const pcIdFromCustomer = `SELECT PC_ID FROM customer WHERE ID = ?`;
  const stopCustomerQuery = `UPDATE customer SET PC_ID = NULL, END = CURRENT_TIME() WHERE ID = ?`;
  const updatePcQuery = `UPDATE pc SET STATUS = NULL, customer_ID = NULL WHERE PC_ID = ?`;

  connection.beginTransaction((err) => {
    if (err) throw err;

    // 인자값 ID(고객)의 PC_ID를 조회한다.
    connection.query(pcIdFromCustomer, [id], (err, res) => {
      if (err) rollbackAndThrowError();

      // 조회된 PC_ID 저장
      const pcId = res[0].PC_ID;

      // 인자값 ID(고객)의 PC_ID를 NULL로 변경 (사용중지)
      connection.query(stopCustomerQuery, [id], (err, res) => {
        if (err) rollbackAndThrowError();

        // 위에서 저장한 인자값 ID(고객)의 PC_ID를 key로 갖는 pc 테이블의 데이터 업데이트
        connection.query(updatePcQuery, [pcId], (err, res) => {
          if (err) rollbackAndThrowError();

          connection.commit((err) => {
            if (err) {
              return connection.rollback(() => {
                throw err;
              });
            }
            console.log(`이제 ${pcId}번 자리가 비었습니다.`);
            showPcStatus();
          });
        });
      });
    });
  });
};