VmsDatabase.cs

이 파일의 문서화 페이지로 가기

using System.IO;
using Microsoft.Data.Sqlite;
 
namespace DreamineVMS.Web.Services.Auth;

public sealed class VmsDatabase
{
    private readonly string _connectionString;

    public VmsDatabase()
    {
        var dbPath = Path.Combine(AppContext.BaseDirectory, "vms.db");
        _connectionString = $"Data Source={dbPath}";
        Initialize();
    }

    public SqliteConnection Open()
    {
        var conn = new SqliteConnection(_connectionString);
        conn.Open();
        return conn;
    }

    private void Initialize()
    {
        using var conn = Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = """
            CREATE TABLE IF NOT EXISTS users (
                id           TEXT PRIMARY KEY,
                email        TEXT UNIQUE NOT NULL,
                display_name TEXT NOT NULL,
                public_slug  TEXT UNIQUE NOT NULL,
                password_hash TEXT NOT NULL,
                password_salt TEXT NOT NULL,
                created_at   TEXT NOT NULL
            );
 
            CREATE TABLE IF NOT EXISTS cameras (
                id             TEXT PRIMARY KEY,
                tenant_id      TEXT NOT NULL,
                name           TEXT NOT NULL,
                host           TEXT NOT NULL,
                rtsp_url       TEXT NOT NULL,
                display_order  INTEGER NOT NULL DEFAULT 0,
                enabled        INTEGER NOT NULL DEFAULT 1,
                auto_reconnect INTEGER NOT NULL DEFAULT 1,
                is_public      INTEGER NOT NULL DEFAULT 0
            );
            """;
        cmd.ExecuteNonQuery();
 
        // 기존 DB 마이그레이션 (컬럼이 이미 있으면 무시)
        foreach (var ddl in new[]
        {
            "ALTER TABLE cameras ADD COLUMN is_public INTEGER NOT NULL DEFAULT 0",
            "ALTER TABLE users ADD COLUMN live_layout TEXT NOT NULL DEFAULT 'auto'",
            "ALTER TABLE users ADD COLUMN og_title TEXT NOT NULL DEFAULT ''",
            "ALTER TABLE users ADD COLUMN og_description TEXT NOT NULL DEFAULT ''",
            "ALTER TABLE users ADD COLUMN og_image TEXT NOT NULL DEFAULT ''"
        })
        {
            using var alter = conn.CreateCommand();
            alter.CommandText = ddl;
            try { alter.ExecuteNonQuery(); } catch { }
        }
    }
}