osdb-1-claude-opusplan/docs/planning/specs/03-import-fruit-db.md

Spec: 03 · Import Old Obstsorten DB

Taiga Story: #3 · 03-import-old-obstsorten-db Status: Ready Branch: feature/03-import-fruit-db Depends on: 02-manage-fruits (fruits, fruit_synonyms, fruit_images tables)

---

1. Goal

Populate the fruits, fruit_synonyms, and fruit_images tables from the legacy XML export and image files. Delivered as a repeatable SQL/Go/Python script that can be re-run safely (delete-before-insert per fruit).

---

2. Background / Scope / Context / Constraints

• Source XML: 03-data/obstsorten.xml
• Image directories:
  • Fruit images: 03-data/einzelfruechte/ (use files containing _s0 before extension)
  • Flower images: 03-data/blueten/ (use _s0 files)
  • Tree images: 03-data/baume/ (use _s0 files)
• Image filename pattern: {id}_*_s0.{ext} — prefix = fruit id
• Type mapping from XML typ field → fruit_type enum (see below)
• created_at sourced from XML added date field (fallback: NOW())
• OSDB number = XML id field
• Script must be idempotent: delete existing images and synonyms for each fruit before re-inserting

Type Mapping

XML typ	DB fruit_type
x	Apfelsorten (Alle Obstsorten → map to most common or skip if no direct match)
a	Apfelsorten
bq	Birnensorten (Birnen- und Quittensorten → split not possible in enum, use Birnensorten)
b	Birnensorten
w	Wein
q	Quittensorten
aprpfi	Aprikosen
apr	Aprikosen
pfi	Pfirsiche
mirren	Mirabellen
mir	Mirabellen
ren	Renekloden
pflzwe	Pflaumen
pfl	Pflaumen
zwe	Zwetschen
k	Sauerkirschen
bo	Brombeeren
bob	Brombeeren
boe	Erdbeeren
boh	Himbeeren
boj	Johannisbeeren
bos	Stachelbeeren
wei	Wein

Note on ambiguous types (x, bq, aprpfi, mirren, pflzwe, bo, k): these map to aggregate categories not directly in the enum. Use the primary type as shown above. Log a warning for each skipped or coerced row.

---

3. Necessary Refactorings

• Confirm fruit_images.image_type accepts 'fruit', 'flower', 'tree' (already in migration from story #2).
• Leave title blank as specified.

---

4. Related Data Model

Tables consumed (no new migrations):

• fruits — insert/upsert per osdb_number
• fruit_synonyms — delete cascade then re-insert per fruit
• fruit_images — delete per fruit then re-insert

---

5. Affected Places / Related Components

Script: scripts/import_fruits.py

Recommended language: Python (XML parsing, file I/O, psycopg2).

scripts/
  import_fruits.py     ← main import script
  README_import.md     ← how to run

Steps:

1. Parse 03-data/obstsorten.xml with xml.etree.ElementTree
2. For each <sorte> element: a. Map typ → enum value (log+skip if unmappable) b. Split synonym by comma → list of synonyms c. DELETE FROM fruit_synonyms WHERE fruit_id = (SELECT id FROM fruits WHERE osdb_number = id_val) d. DELETE FROM fruit_images WHERE fruit_id = ... e. UPSERT into fruits (INSERT … ON CONFLICT (osdb_number) DO UPDATE) f. INSERT synonyms g. Find matching images in each image directory (glob {id}_*_s0.*) h. INSERT into fruit_images for each found image
3. Log counts: fruits upserted, synonyms inserted, images inserted, warnings

DB connection from DATABASE_URL env var.

---

6. Out of Scope

• Importing publications (story #5)
• Modifying the fruit CRUD API
• GUI trigger for import

---

7. Verification

Check	Expected
Script runs without error on full dataset	Exit 0
Re-run is idempotent	Same row counts; no duplicate key errors
Spot-check: known fruit by osdb_number	Correct name, type, synonyms in DB
At least one image per fruit (if source exists)	fruit_images rows with non-null data
Unknown typ values	Logged as warnings, fruit still inserted with best-guess type
GET /api/v1/fruits after import	Returns populated list

---

8. Open Questions

#	Question	Impact
1	Exact XML element/attribute names in obstsorten.xml?	Parser field mapping
2	What to do with typ = 'x' (Alle Obstsorten)? Skip, or map to first enum value?	Data integrity
3	Are there fruits without any matching image file?	Expected → just 0 images
4	added date field format in XML?	created_at parse
5	Should bq, aprpfi, mirren, pflzwe, bo, k map as above or produce error?	Type fidelity