File size: 8,954 Bytes
781b9f7 74688c8 781b9f7 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 | """SQLite database connection, schema initialization, and migrations."""
from __future__ import annotations
import os
import sqlite3
from contextlib import contextmanager
from pathlib import Path
from typing import Generator
DB_PATH = Path(os.environ.get("TTR_DB_PATH", "data/world.db"))
SCHEMA_SQL = """
PRAGMA journal_mode=WAL;
PRAGMA foreign_keys=ON;
PRAGMA synchronous=NORMAL;
CREATE TABLE IF NOT EXISTS world_state (
id INTEGER PRIMARY KEY DEFAULT 1,
world_name TEXT NOT NULL DEFAULT 'Aether Garden',
founding_date TEXT NOT NULL,
current_day INTEGER NOT NULL DEFAULT 1,
total_entities INTEGER NOT NULL DEFAULT 0,
total_interactions INTEGER NOT NULL DEFAULT 0,
total_events INTEGER NOT NULL DEFAULT 0,
last_simulation_run TEXT,
active_world_event TEXT,
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE TABLE IF NOT EXISTS locations (
id INTEGER PRIMARY KEY AUTOINCREMENT,
slug TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
short_description TEXT NOT NULL,
full_lore TEXT NOT NULL,
vibe_tags TEXT NOT NULL,
special_property TEXT NOT NULL,
aesthetic_description TEXT NOT NULL,
map_x REAL NOT NULL,
map_y REAL NOT NULL,
glow_color TEXT NOT NULL,
entity_count INTEGER NOT NULL DEFAULT 0,
interaction_multiplier REAL NOT NULL DEFAULT 1.0
);
CREATE TABLE IF NOT EXISTS entities (
id TEXT NOT NULL PRIMARY KEY,
name TEXT NOT NULL,
display_name TEXT NOT NULL,
type TEXT NOT NULL CHECK(type IN ('character','creature','object','place')),
input_description TEXT NOT NULL,
appearance TEXT NOT NULL,
backstory TEXT NOT NULL,
personality_traits TEXT NOT NULL,
primary_goal TEXT NOT NULL,
secondary_goal TEXT NOT NULL,
primary_fear TEXT NOT NULL,
speech_style TEXT NOT NULL,
greeting TEXT NOT NULL,
location_id INTEGER NOT NULL REFERENCES locations(id),
created_at TEXT NOT NULL DEFAULT (datetime('now')),
created_by_session TEXT,
days_in_realm INTEGER NOT NULL DEFAULT 0,
memory_summary TEXT NOT NULL DEFAULT '',
relationship_count INTEGER NOT NULL DEFAULT 0,
interaction_count INTEGER NOT NULL DEFAULT 0,
last_active TEXT NOT NULL DEFAULT (datetime('now')),
status TEXT NOT NULL DEFAULT 'active'
CHECK(status IN ('active','dormant','legendary')),
tags TEXT NOT NULL DEFAULT '[]',
secret_name TEXT,
arrival_note TEXT NOT NULL,
wisdom_unlocked INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_entities_location ON entities(location_id);
CREATE INDEX IF NOT EXISTS idx_entities_status ON entities(status);
CREATE INDEX IF NOT EXISTS idx_entities_created ON entities(created_at);
CREATE INDEX IF NOT EXISTS idx_entities_active ON entities(last_active);
CREATE TABLE IF NOT EXISTS relationships (
id INTEGER PRIMARY KEY AUTOINCREMENT,
entity_a_id TEXT NOT NULL REFERENCES entities(id),
entity_b_id TEXT NOT NULL REFERENCES entities(id),
relationship_type TEXT NOT NULL,
description TEXT NOT NULL,
strength INTEGER NOT NULL DEFAULT 1 CHECK(strength BETWEEN 1 AND 5),
formed_at TEXT NOT NULL DEFAULT (datetime('now')),
formed_on_day INTEGER NOT NULL DEFAULT 1,
last_interaction TEXT NOT NULL DEFAULT (datetime('now')),
UNIQUE(entity_a_id, entity_b_id),
CHECK(entity_a_id < entity_b_id)
);
CREATE INDEX IF NOT EXISTS idx_rel_a ON relationships(entity_a_id);
CREATE INDEX IF NOT EXISTS idx_rel_b ON relationships(entity_b_id);
CREATE TABLE IF NOT EXISTS interactions (
id INTEGER PRIMARY KEY AUTOINCREMENT,
entity_a_id TEXT NOT NULL REFERENCES entities(id),
entity_b_id TEXT NOT NULL REFERENCES entities(id),
location_id INTEGER NOT NULL REFERENCES locations(id),
interaction_type TEXT NOT NULL,
description TEXT NOT NULL,
notable_outcome TEXT,
book_of_ages_entry TEXT NOT NULL,
timestamp TEXT NOT NULL DEFAULT (datetime('now')),
world_day INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_int_timestamp ON interactions(timestamp);
CREATE INDEX IF NOT EXISTS idx_int_a ON interactions(entity_a_id);
CREATE INDEX IF NOT EXISTS idx_int_b ON interactions(entity_b_id);
CREATE INDEX IF NOT EXISTS idx_int_day ON interactions(world_day);
CREATE TABLE IF NOT EXISTS world_events (
id INTEGER PRIMARY KEY AUTOINCREMENT,
event_type TEXT NOT NULL,
title TEXT NOT NULL,
description TEXT NOT NULL,
affected_locations TEXT NOT NULL,
entity_effect TEXT NOT NULL,
book_of_ages_entry TEXT NOT NULL,
mystery_hook TEXT,
timestamp TEXT NOT NULL DEFAULT (datetime('now')),
world_day INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_events_day ON world_events(world_day);
CREATE TABLE IF NOT EXISTS book_of_ages (
id INTEGER PRIMARY KEY AUTOINCREMENT,
world_day INTEGER NOT NULL,
entry_type TEXT NOT NULL CHECK(entry_type IN
('arrival','interaction','world_event','milestone','dream_fragment')),
title TEXT,
content TEXT NOT NULL,
entity_ids TEXT NOT NULL DEFAULT '[]',
location_id INTEGER REFERENCES locations(id),
timestamp TEXT NOT NULL DEFAULT (datetime('now')),
is_milestone INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_boa_day ON book_of_ages(world_day);
CREATE INDEX IF NOT EXISTS idx_boa_type ON book_of_ages(entry_type);
CREATE TABLE IF NOT EXISTS sessions (
session_id TEXT NOT NULL PRIMARY KEY,
last_creation TEXT,
creation_count INTEGER NOT NULL DEFAULT 0,
first_seen TEXT NOT NULL DEFAULT (datetime('now')),
last_seen TEXT NOT NULL DEFAULT (datetime('now'))
);
"""
def get_connection() -> sqlite3.Connection:
DB_PATH.parent.mkdir(parents=True, exist_ok=True)
conn = sqlite3.connect(DB_PATH, check_same_thread=False)
conn.row_factory = sqlite3.Row
conn.execute("PRAGMA journal_mode=WAL")
conn.execute("PRAGMA foreign_keys=ON")
return conn
@contextmanager
def db_session() -> Generator[sqlite3.Connection, None, None]:
conn = get_connection()
try:
yield conn
conn.commit()
except Exception:
conn.rollback()
raise
finally:
conn.close()
def init_database() -> None:
with db_session() as conn:
conn.executescript(SCHEMA_SQL)
row = conn.execute("SELECT id FROM world_state WHERE id = 1").fetchone()
if row is None:
conn.execute(
"""
INSERT INTO world_state (id, founding_date, current_day)
VALUES (1, datetime('now'), 1)
"""
)
# Safe migrations (idempotent)
_safe_migrate(conn)
# Init extension tables
from world.quests import init_quests_table
from world.presence import init_presence_table
init_quests_table()
init_presence_table()
def ensure_database_ready() -> None:
"""Restore (if configured), create schema, and seed locations. Idempotent."""
from persistence.backup import database_is_valid, restore_database
from world.seed_data import seed_locations
from world.locations import update_entity_counts
DB_PATH.parent.mkdir(parents=True, exist_ok=True)
if not database_is_valid():
try:
restore_database()
except Exception as exc:
print(f"[db] restore failed: {exc}")
if DB_PATH.exists() and not database_is_valid():
DB_PATH.unlink(missing_ok=True)
init_database()
with db_session() as conn:
seed_locations(conn)
update_entity_counts()
if not database_is_valid():
raise RuntimeError(f"Database failed to initialize at {DB_PATH.resolve()}")
def _safe_migrate(conn) -> None:
"""Add new columns/indexes that may not exist in older DB files."""
migrations = [
"ALTER TABLE entities ADD COLUMN portrait_url TEXT",
]
for sql in migrations:
try:
conn.execute(sql)
except Exception:
pass # Column already exists
def get_world_state() -> dict:
with db_session() as conn:
row = conn.execute("SELECT * FROM world_state WHERE id = 1").fetchone()
return dict(row) if row else {}
|