워크숍 등록 규칙 보호하기

소개

REPAIR CIRCLE은 실습 중심의 수리 워크숍을 운영합니다. 접수 도구로 이미 예약을 기록하고 실제 참가 명단을 표시할 수 있습니다. 그러나 데이터베이스는 아직 중복 등록이나 잘못된 참조를 거부하지 않습니다. 목표는 이 접수 도구를 계속 사용할 수 있게 유지하면서 데이터베이스가 등록 관계를 강제하도록 만드는 것입니다.

기본적인 터미널 탐색과 MySQL의 기본 SELECT, INSERT, UPDATE 지식이 필요합니다. 제약 조건, 되돌릴 수 있는 시험에 사용하는 트랜잭션, ALTER TABLE은 여기서 설명합니다. MySQL과 합성 워크숍 데이터는 준비되어 있습니다. 전체 과정에서 Terminal을 사용하세요. 제공된 클라이언트는 이 과정의 MySQL 데이터베이스에 연결됩니다.

필요한 등록을 추가하고 registrations의 필요한 구조를 변경할 수 있으며, SQL은 rules.sql에 저장합니다. 원래 ID, 사람, 세션 제목, 그룹 인원수, 메모, 제공된 클라이언트를 보존하세요. 한 사람이 여러 세션에 참여할 수 있고, 서로 다른 사람의 이름이 같을 수도 있습니다. 이 프로젝트는 정원을 제한하거나 동시 좌석 배정을 처리하지 않습니다.

접수 도구를 사용하고 중복 문제 확인하기

이 단계에서는 정상 예약을 기록하고 실제 관계를 읽은 다음, 잘못된 레코드를 남기지 않고 빠진 규칙을 확인합니다. Terminal에서 시작하세요. cd는 디렉터리를 변경합니다. 잘못된 디렉터리에 들어갔다면 이 절대 경로를 다시 사용하세요:

cd /home/labex/project/sign-up-desk

./desk는 이 디렉터리에 제공된 등록 클라이언트입니다. ./는 현재 디렉터리를 뜻합니다. roster는 실제 MySQL 테이블을 읽으며, 미리 계산된 답을 표시하지 않습니다:

./desk roster

원래 등록 두 건이 나와야 합니다. 등록 501은 사람 101인 Mara Lin을 세션 11인 Lamp Repair에 연결하며, 좌석은 하나이고 메모는 Window seat입니다. 등록 502는 사람 102인 Alex Kim을 세션 12인 Bike Patch에 연결하며, 좌석은 두 개이고 메모는 Bring partner입니다. 이 원래 관계와 값을 유지하세요.

새 요청은 사람 104인 Sam Reed가 세션 11에 좌석 하나로 참여하는 것입니다. book은 사람 ID, 이벤트 ID, 그룹 인원수를 이 순서로 받습니다. 이 적법한 예약을 저장하세요:

./desk book 104 11 1

./desk roster

두 사람 모두 Lamp Repair에 참여하지만, 세 번째 등록은 Mara가 아니라 Sam의 것입니다. 클라이언트는 데이터베이스에 저장된 ID를 반환합니다. 새 ID를 직접 고르거나 외울 필요는 없습니다.

./db는 이 과정의 MySQL 클라이언트 연결을 엽니다. 이미지에 있는 별개의 mysql 명령 대신 이것을 사용하세요. --table은 읽기 쉬운 표 출력을 요청하고, -e는 따옴표 안의 SQL을 실행한 뒤 연결을 닫습니다. 따옴표는 공백과 세미콜론을 하나의 인수로 묶습니다. 두 부모 테이블을 읽어 보세요:

./db --table -e "SELECT id,name FROM participants ORDER BY id; SELECT id,title FROM sessions ORDER BY id;"

사람 102와 103의 이름은 모두 Alex Kim입니다. ID가 다르므로 서로 다른 사람입니다. 이름을 등록의 고유 식별자로 삼아서는 안 됩니다. 세 테이블의 관계는 다음과 같습니다:

업무 규칙상 (participant_id,session_id) 쌍마다 등록은 하나만 허용되며, 좌석 수는 양수이고 부모 ID는 존재해야 합니다. 기존 예약이 참조하는 사람/세션 ID는 삭제하거나 변경할 수 없어야 합니다. 사용하지 않는 세션은 필요할 때 계속 삭제할 수 있어야 합니다.

스키마를 보호하기 전에 현재 빠져 있는 중복 규칙을 시험하세요. 트랜잭션은 한 연결에서 데이터 변경을 묶습니다. START TRANSACTION은 시험을 시작하고, ROLLBACK은 INSERT를 저장하는 대신 취소합니다. 다음 단일 명령은 Mara의 Lamp Repair 예약을 의도적으로 한 번 더 시도하고, 해당 쌍의 수를 센 뒤 롤백합니다. 여러 줄로 된 따옴표 안의 내용도 여전히 하나의 셸 인수를 이룹니다:

./db --table -e "
START TRANSACTION;
INSERT INTO registrations(participant_id,session_id,seats) VALUES(101,11,1);
SELECT COUNT(*) AS matching_registrations FROM registrations WHERE participant_id=101 AND session_id=11;
ROLLBACK;"

개수는 2입니다. 데이터베이스가 중복을 허용한 것입니다. 이는 연결 실패가 아니라 실제 취약점입니다. 이 시험은 ALTER가 아니라 INSERT를 사용합니다. 롤백 후에도 Mara의 원래 예약과 Sam의 새 요청은 그대로 남습니다:

./desk roster

등록은 여전히 세 건이어야 합니다. 일반 명령에서 경로를 잘못 입력했다면 절대 경로 cd로 돌아가세요. ls로 이 디렉터리의 파일을 나열할 수 있습니다. 입력 오류를 복구하려고 사람을 삭제하거나, 등록 테이블을 비우거나, 데이터베이스를 초기화하지 마세요.

관계를 강제하고 적법한 예약 유지하기

이 단계에서는 스키마 변경 하나를 저장해 기존 테이블에 적용하고, 잘못된 요청은 거부하면서 유용한 예약은 계속 동작함을 확인합니다. 같은 Terminal 디렉터리에서 시작하세요.

제약 조건은 데이터 쓰기에 적용되는 데이터베이스 규칙이며, ./desk 이외의 클라이언트에서 쓰는 데이터에도 적용됩니다. 복합 UNIQUE 제약 조건은 같은 사람이 다른 세션에 참여하는 것을 막지 않으면서 동일한 쌍의 반복을 거부합니다. FOREIGN KEY는 참조 열을 부모의 기본 키에 연결합니다. RESTRICT는 예약이 부모를 참조하는 동안 삭제나 ID 변경을 거부하며, 예약을 자동으로 삭제하지 않습니다.

CHECK (seats > 0)는 0과 음수를 거부합니다. SQL NULL은 값이 없음을 뜻합니다. CHECK의 평가 결과가 알 수 없음이면, CHECK만으로는 NULL을 거부하지 않습니다. 따라서 좌석 수와 두 참조에 NOT NULL도 필요합니다. MODIFY는 열 형식을 다시 명시하고 그 요구 사항을 추가합니다. 제공된 세 테이블은 모두 이미 InnoDB를 사용합니다. InnoDB는 이러한 관계를 강제하는 스토리지 엔진입니다.

ALTER TABLE은 기존 테이블을 변경합니다. MySQL은 스키마 변경을 암묵적으로 커밋합니다. 이 ALTER를 시험용 트랜잭션으로 감싸고 ROLLBACK으로 취소할 수 있으리라 기대하지 마세요. 이미 유효한 레코드에 단일 ALTER 문을 적용해 ID와 메모를 보존합니다.

rules.sql에는 registrations에 대한 일반적인 ALTER TABLE 문만 저장하며 테이블 이름에 데이터베이스 한정자를 붙이지 마세요. USE, 셸 명령, 다른 객체의 변경은 이 파일의 범위 밖입니다. 트리거를 추가하거나 부모 테이블 정의를 변경하지 마세요. Linux 셸에서 >는 대상 파일을 덮어쓰고, <<'SQL'은 내용을 그대로 받는 here-document를 시작합니다. 마지막에 한 줄에 단독으로 쓰인 SQL까지 전체 블록을 붙여 넣으세요. 이 cat 명령을 MySQL 프롬프트에 입력하지 마세요:

cat > rules.sql <<'SQL'
ALTER TABLE registrations
  MODIFY participant_id INT NOT NULL,
  MODIFY session_id INT NOT NULL,
  MODIFY seats INT NOT NULL,
  ADD CONSTRAINT one_person_event UNIQUE (participant_id, session_id),
  ADD CONSTRAINT known_person FOREIGN KEY (participant_id)
    REFERENCES participants(id) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT known_event FOREIGN KEY (session_id)
    REFERENCES sessions(id) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT positive_group CHECK (seats > 0);
SQL

셸이 계속 기다리면 빠진 마지막 SQL 줄을 입력하세요. 파일을 잘못 입력했다면 전체 블록을 반복해 덮어쓰세요. 제약 조건 이름은 MySQL 오류 메시지에서 규칙을 식별하는 데 사용되며, 정확히 그 이름을 쓰는 것 자체가 업무 결과는 아닙니다.

<는 저장한 파일을 클라이언트의 SQL 입력으로 제공합니다. 한 번 적용하세요:

./db < rules.sql

성공하면 출력이 없는 것이 정상입니다. 파일 이름이 틀렸다면 ls를 사용해 경로를 수정하고 다시 시도하세요. 이 단일 ALTER에 구문 오류가 있으면 MySQL이 거부합니다. 저장한 전체 블록을 수정한 뒤 다시 시도하세요. ALTER가 성공하면 다시 적용하지 마세요. 제약 조건이 이미 존재합니다. 두 번째 실행의 중복 제약 조건 오류는 데이터가 사라졌다는 뜻이 아닙니다.

실제 정의를 읽어 보세요. SHOW CREATE TABLE은 현재 테이블을 알려 줍니다. \G는 결과를 세로로 표시해 전체 정의를 읽기 쉽게 합니다:

./db -e "SHOW CREATE TABLE registrations\G"

참조/수량 열 세 개의 NOT NULL, 두 열로 된 UNIQUE 키, 두 FOREIGN KEY 관계, 양수 좌석 수 CHECK를 찾으세요. 이름이나 형식은 다를 수 있지만 관계는 일치해야 합니다.

이제 일반 클라이언트로 중복 요청을 반복하세요. 이 명령은 ERROR 1062가 포함된 데이터베이스 거부로 실패하는 것이 예상 결과입니다. 다른 행을 만들면 안 됩니다:

./desk book 101 11 1

이 실패는 서버 연결 끊김이 아니라 중복 키 거부입니다. 참가 명단은 계속 사용할 수 있습니다:

./desk roster

실제 접수 데이터에서 시험 후 버릴 데이터 트랜잭션으로 허용되는 두 식별 사례를 확인하세요. Mara는 Bike Patch에 참여할 수 있고, 다른 Alex Kim(103)은 Alex 102와 같은 세션에 참여할 수 있습니다. 1보다 큰 양수 그룹 인원수도 적법합니다:

./db --table -e "
START TRANSACTION;
INSERT INTO registrations(participant_id,session_id,seats) VALUES(101,12,3),(103,12,5);
SELECT participant_id,session_id,seats FROM registrations ORDER BY participant_id,session_id;
ROLLBACK;"

시험 결과에는 서로 다른 식별 조합의 다섯 행이 표시됩니다. 롤백은 시험에서 추가한 두 행만 제거합니다. 앞서 수행한 스키마 변경이나 이미 커밋된 Sam의 요청은 취소하지 않습니다.

거부되는 쓰기의 대표 사례 세 가지를 직접 확인하세요. 아래 명령은 각각 실패하는 것이 예상 결과입니다. 이 명령들에는 복구해야 할 트랜잭션이나 취소해야 할 성공한 변경이 없습니다. MySQL이 잘못된 개별 문장을 거부합니다. 제공된 부모 테이블에는 9999가 없습니다:

./db -e "INSERT INTO registrations(participant_id,session_id,seats) VALUES(9999,11,1);"

알 수 없는 부모 참조를 나타내는 ERROR 1452가 나와야 합니다.

./db -e "INSERT INTO registrations(participant_id,session_id,seats) VALUES(103,11,NULL);"

좌석 수 누락을 나타내는 ERROR 1048이 나와야 합니다. NULL을 0으로 바꿔도 유효한 예약이 되지 않고 CHECK를 위반합니다.

./db -e "DELETE FROM sessions WHERE id=11;"

ERROR 1451이 나와야 합니다. Lamp Repair에는 여전히 예약이 있으므로 세션이 남습니다. 부모를 삭제하거나 연쇄 삭제로 등록을 없애는 방법이 왜 잘못된 지름길인지 보여 줍니다.

마지막으로 되돌릴 수 있는 시험에서 예약이 없는 세션은 계속 삭제할 수 있음을 확인하세요:

./db --table -e "
START TRANSACTION;
DELETE FROM sessions WHERE id=13;
SELECT id,title FROM sessions ORDER BY id;
ROLLBACK;"

이 트랜잭션 안에서는 세션 11과 12만 표시됩니다. 롤백하면 사용하지 않는 세션이 복원됩니다. 최종 인계 상태를 검사하세요:

./db --table -e "SELECT id,title FROM sessions ORDER BY id;"

./desk roster

원래 세션 세 개가 모두 남고, 실제 등록은 정확히 세 건만 남습니다. 원래 두 건과 Sam의 새 요청입니다. 다른 호환 접수 데이터에도 같은 구조 변경을 적용할 수 있도록 rules.sql을 보관하세요. 이는 한 번 수행하는 스키마 마이그레이션이며, 반복 실행해도 같은 결과를 유지하는 가져오기 작업이 아닙니다. 기본 연결은 외래 키를 강제합니다. 해당 검사를 끄는 것은 이 작업의 범위 밖입니다. 제약 조건은 일반 애플리케이션 쓰기를 보호하며, 강제를 의도적으로 끄는 관리자까지 막는 것은 아닙니다.

요약

등록 관계 규칙을 실제 MySQL 제약 조건으로 옮기면서 접수 도구를 계속 사용할 수 있게 유지했습니다. 원래 예약과 메모를 보존하고, 이름이 같거나 여러 세션에 참여하는 적법한 조합은 허용하며, 중복과 잘못된 참조를 거부하고, 참조 중인 레코드의 삭제를 막았습니다.