translation-pack

Pipeline that consolidates French locale strings from AzerothCore (AC) and MaNGOS Two (mangostwo) into a single, versioned store, so they can eventually be matched against a live CMaNGOS Classic database and turned into ready-to-run UPDATE statements.

data/ is entirely git-ignored: data/source (cloned repos), data/parsed (intermediate JSONL) and data/work (the sqlite store) are all regenerated by running the pipeline locally.

Pipeline overview

download_data.sh
        │
        ▼
data/source/<repo>/...                       (raw SQL dumps, per core)
        │
        ▼
parsing/<source>_processing/parse_source.py  (sqlglot: SQL -> JSONL)
        │
        ▼
data/parsed/<repo>/<table>[_locale].jsonl
        │
        ▼
parsing/build_translation.py                 (join locale+original by id,
        │                                      per parsing/<source>_processing/mapping.yaml)
        ▼
parsing/cmangos_db/fetch_data.py             (live MySQL -> base rows only)
        │
        ▼
data/work/translation_pack.db (sqlite)
  sources / source_entries / source_entry_history / canonical_matches

1. Fetch raw sources

./download_data.sh

Sparse-checkouts pinned commits of azerothcore/azerothcore-wotlk (data/sql/base/db_world/) and mangostwo/database (World/Setup/FullDB/, Translations/Translations/French/) into data/source/.

2. Parse raw SQL into JSONL

.venv/bin/python src/translation_pack/parsing/azerothcore_processing/parse_source.py
.venv/bin/python src/translation_pack/parsing/mangostwo_processing/parse_source.py

Each script uses sqlglot to parse INSERT/UPDATE statements (parsing/utils.py: parse_inserts / parse_updates) and writes one {"table", "row", "source": {repo_url, repo_slug, commit_hash, file}} JSON object per line to data/parsed/<repo>/<table>[_locale].jsonl. AzerothCore locale rows are filtered to Locale == 'frFR' at parse time.

Both scripts also (re)generate mapping_template.yaml next to themselves — a best-effort column-mapping guess for review. This is not consumed by the pipeline; the hand-curated mapping.yaml in the same folder is what build_translation.py actually reads. Never edit mapping_template.yaml expecting it to change pipeline behavior — copy what you need into mapping.yaml instead.

3. Join locale + original rows and store them

.venv/bin/python -m translation_pack.parsing.build_translation

For each table in azerothcore_processing/mapping.yaml and mangostwo_processing/mapping.yaml, joins the locale JSONL and the original (English) JSONL by their configured id columns, and upserts one source_entries row per matched, non-empty (id, column) pair for sources "azerothcore" and "mangostwo".

4. Fetch CMaNGOS Classic reference rows

.venv/bin/python -m translation_pack.parsing.cmangos_db.fetch_data

Connects to a live CMaNGOS Classic MySQL database (credentials via .env, see settings.py: MySQLSettings) using parsing/cmangos_db/table_mapping.yaml

• mapping.yaml, and upserts base (English) rows as source "cmangos_classic". text_fr is intentionally left NULL — filling it in is the job of the (not yet implemented) matching layer.

5. Explore the store in a browser

.venv/bin/fastapi dev src/translation_pack/webapp/main.py

Opens a read-only explorer at http://127.0.0.1:8000:

• Dashboard — per-source counts, entity type overview, coverage stats.
• Entity types — table of every entity_type with per-source record counts.
• Entity type detail — searchable, paginated list of record ids for that entity type, with which sources have data for each id.
• Record detail — every field for one record id, grouped by source side by side, with per-field version history (source_entry_history).
• Search — free-text search across source_entry/text_en/text_fr.

It's intentionally read-only for now (src/translation_pack/webapp/); manual match curation is meant to be layered on top of these same views once the matching pipeline exists.

Storage model

data/work/translation_pack.db (sqlite, via peewee — database/orm.py):

• sources — one row per data origin: azerothcore, mangostwo, cmangos_classic.
• source_entries — one row per canonical, localizable field snapshot: source, entity_type (canonical concept shared across sources, e.g. creature_template, quest_template), source_table (the actual source-specific table the row came from, for traceability), source_entry (serialized id, e.g. "entry:12345", "menu_id:5&id:2" for composite keys), unified_field, text_en, text_fr, checksum, provenance (JSON list of repo/commit/file), and last_updated. Unique on (source, entity_type, source_entry, unified_field).
• source_entry_history — audit trail. Populated automatically by database/source_entries.py: upsert_source_entries whenever a re-import changes a row's checksum: the previous text/checksum is archived here before the row is updated. Unchanged rows are skipped entirely (no writes, no history).
• canonical_matches — reserved for the matching layer (cmangos_entry ↔ source_entry, match_score, match_type, is_active). Not populated by anything yet.

Re-running any of the parse/build/fetch steps is idempotent: entries are never blindly overwritten, only inserted, versioned-and-updated, or skipped.

Known gaps / attention points

• Matching is not implemented. parsing/compare.py still targets the old (now removed) translation/db_records tables and does not run against the current schema. It needs to be rewritten against source_entries/canonical_matches before use.
• entity_type must match exactly across sources. It is what lets the same conceptual entry from AC/mangostwo/cmangos be joined later. Sources mostly agree on table names already, except AzerothCore, which splits quest_offer_reward/quest_request_items out of quest_template; both are explicitly mapped back to entity_type: quest_template in azerothcore_processing/mapping.yaml. Watch for this pattern when adding new tables.
• cmangos_db/fetch_data.py requires a live, reachable MySQL instance with a wotlkmangos-shaped INFORMATION_SCHEMA (hardcoded TABLE_SCHEMA='wotlkmangos' in _aget_schemas). It cannot run offline.
• Mapping files are hand-curated, not generated. mapping.yaml is meant to be edited manually; only mapping_template.yaml is machine-generated (and overwritten) on each parse run.

Tests

.venv/bin/python -m pytest

Tests use a temporary sqlite file bound to the shared peewee db (see tests/conftest.py) and never touch data/work/translation_pack.db.