[현장 강의록 2] 클라이언트 데이터베이스, SQLite 로 게임 데이터 읽기

C:\SQLite> sqlite3 ClientGameData.db
SQLite version 3.x
Enter ".help" for instructions
sqlite> .databases
seq  name   file
0    main   C:\SQLite\ClientGameData.db

sqlite> CREATE TABLE "Characters" (
   "ID" INTEGER,
   "Name" TEXT,
   "AttackPoint" INTEGER,
   "HealthPoint" INTEGER,
   PRIMARY KEY("ID")
);
sqlite> .tables
Characters
sqlite> INSERT INTO Characters (ID, Name, AttackPoint, HealthPoint) VALUES (1, '제임스', 4, 4);
sqlite> INSERT INTO Characters (ID, Name, AttackPoint, HealthPoint) VALUES (2, '루이스', 5, 6);
sqlite> SELECT Name, AttackPoint, HealthPoint FROM Characters;
제임스|4|4
루이스|5|6
sqlite> SELECT Name, AttackPoint, HealthPoint FROM Characters WHERE ID=1;
제임스|4|4

public class CharacterRow
{
    [PrimaryKey]
    public int ID { get; set; }
    public string Name { get; set; }
    public int AttackPoint { get; set; }
    public int HealthPoint { get; set; }
}

public List<CharacterRow> GetCharacter(int id)
{
    SQLiteConnection conn = GetConnection();
    List<CharacterRow> result = null;
    try
    {
        SQLiteCommand command = conn.CreateCommand(string.Format(
            "SELECT ID, Name, AttackPoint, HealthPoint FROM Characters WHERE ID={0};", id));
        result = command.ExecuteQuery<CharacterRow>();
    }
    catch (Exception ee)
    {
        Debug.LogError(ee.Message);
        Debug.LogError(ee.StackTrace);
    }
    return result;
}

// GameScene.cs
List<GameDB.CharacterRow> result = gameDB.GetCharacter(2);
if (result != null && result.Count > 0)
{
    heroCharacter.SetName(result[0].Name);
}

// Character.cs
public void SetName(string characterName)
{
    myNameText.text = characterName;
}

List<CharacterRow> preLoadedCharacters = new List<CharacterRow>();

void Start()
{
    SQLiteCommand command = GetConnection().CreateCommand(
        "SELECT ID, Name, AttackPoint, HealthPoint FROM Characters;");
    preLoadedCharacters.AddRange(command.ExecuteQuery<CharacterRow>());
}

public CharacterRow FindCharacter(int id)
{
    CharacterRow cached = preLoadedCharacters.Find(row => row.ID == id);
    if (cached != null) return cached;          // 메모리에 있으면 그대로
    List<CharacterRow> rows = GetCharacter(id);  // 없을 때 DB 에 묻는다
    return rows != null && rows.Count > 0 ? rows[0] : null;
}

private static GameDB SingletonInstance = null;

public static GameDB GetSingleton()
{
    if (SingletonInstance == null)
    {
        SingletonInstance = new GameDB();
    }
    return SingletonInstance;
}

// 쓰는 쪽 (GameScene.cs)
List<GameDB.CharacterRow> result = GameDB.GetSingleton().GetCharacter(2);

public Character characterPrefab;

void Start()
{
    float x = -4f;
    foreach (GameDB.CharacterRow row in GameDB.GetSingleton().GetAllCharacters())
    {
        Character c = Instantiate(characterPrefab, new Vector3(x, 0, 0), Quaternion.identity);
        c.SetName(row.Name);
        c.SetAttackPoint(row.AttackPoint);
        c.SetHealthPoint(row.HealthPoint);
        x += 2f;
    }
}