How to Fix Shared Progress Issues Across Different Difficulty Levels in SQLite

1. Preventing Data Conflicts with a Composite Primary Key

Problem Previously, the level and quizGroup were not distinctly separated in the database. As a result, saving new progress would unintentionally overwrite existing data from other levels.

Solution I updated the progress table to use a composite primary key consisting of both level and quizGroup. This ensures that progress is managed independently for each specific level and group.

SQL

CREATE TABLE IF NOT EXISTS progress (
    level TEXT,
    quizGroup TEXT,
    lastQuestionIndex INTEGER,
    PRIMARY KEY (level, quizGroup)
);

2. Improving the Data Saving Mechanism

Updating Progress for a Specific Level and Group In the saveProgress method, I updated the SQL query to include both level and quizGroup in the WHERE clause. This ensures we only update the progress for the targeted level and group.

Swift

let updateQuery = "UPDATE progress SET lastQuestionIndex = ? WHERE level = ? AND quizGroup = ?;"

Applying Accurate Filtering When Fetching Progress

Swift

let selectQuery = "SELECT lastQuestionIndex FROM progress WHERE level = ? AND quizGroup = ?;"

Using an UPSERT Pattern: Inserting New Data if No Rows Were Updated If the update query doesn't affect any rows (meaning the data doesn't exist yet), it falls back to inserting a new record.

Swift

if sqlite3_changes(db) == 0 {
    insertProgress(level: level, quizGroup: quizGroup, index: index)
}

3. Results and Improvements

• Independent Progress Tracking: Progress is now managed independently per level. For example, even if a user reaches question #3 in the N1 level, their N2 level progress will still start from the beginning.

• No More Overwritten Data: Data is accurately saved for specific levels and groups without overwriting existing records.

• Data Consistency: By carefully separating the UPDATE and INSERT logic, we maintain data consistency and prevent any unexpected data loss.

레벨과 퀴즈 그룹을 기본 키로 설정하여 데이터 충돌 방지

문제점

기존에는 레벨과 퀴즈 그룹이 정확히 구별되지 않아 새로운 진행도를 저장할 때 기존 데이터가 덮어씌워지는 문제가 있었습니다.

해결 방법

progress 테이블의 기본 키를 level과 quizGroup의 조합으로 설정하여, 레벨별로 진행도를 독립적으로 관리하도록 변경하였습니다.

CREATE TABLE IF NOT EXISTS progress (
    level TEXT,
    quizGroup TEXT,
    lastQuestionIndex INTEGER,
    PRIMARY KEY (level, quizGroup)
);

데이터 저장 방식 개선

업데이트 시 특정 레벨과 그룹에 대해서만 진행도 변경

saveProgress 메서드에서 WHERE 절에 level과 quizGroup을 포함하여 특정 레벨과 그룹에 대해서만 진행도를 변경하도록 설정하였습니다.

let updateQuery = "UPDATE progress SET lastQuestionIndex = ? WHERE level = ? AND quizGroup = ?;"

진행도 조회 시 정확한 필터링 적용

let selectQuery = "SELECT lastQuestionIndex FROM progress WHERE level = ? AND quizGroup = ?;"

변경된 데이터가 없으면 새로운 데이터를 삽입하는 UPSERT 패턴 사용

if sqlite3_changes(db) == 0 {
    insertProgress(level: level, quizGroup: quizGroup, index: index)
}

결과 및 개선점

• 레벨별로 진행도를 독립적으로 관리할 수 있어, 예를 들어 N1에서 3번 문제까지 진행했더라도 N2에서는 처음부터 시작할 수 있음.
• 데이터가 덮어씌워지지 않고, 특정 레벨과 그룹에 대해 정확하게 저장됨.
• 업데이트와 삽입을 분리하여 데이터 일관성 유지 및 예기치 않은 데이터 덮어씌우기 방지.