juliaandClaude Sonnet 4.6
1070ba3ac1
Analyzes all Taiga stories, maps hard/soft dependencies, and writes full specs (Goal, Data Model, Components, Verification, Open Questions) for every story before any implementation begins. Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
136 lines
4.6 KiB
Markdown
136 lines
4.6 KiB
Markdown
# 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 |
|