load-to-sqlite

name	load-to-sqlite
description	Use when you need to load a taxonomy into SQLite — create table, import data, support upsert/replace/append modes, type columns appropriately.

Load Taxonomy to SQLite

Load a taxonomy into SQLite with full schema generation, type inference, and multiple load modes.

When to use

• "Load the taxonomy into SQLite"
• "I want this in a local .db file"
• "Create a SQLite database with the taxonomy"

Inputs to gather

• Database file path: Absolute path to .db file (will be created if missing).
• Table name: What to call the table.
• Taxonomy file path: Absolute path to CSV or auto-discover from data/<name>/<name>.csv.
• Load mode: replace, upsert, append.
• Strict mode?: Use STRICT tables (SQLite ≥3.37) for stronger type enforcement.

Procedure

1. Check SQLite version: Run sqlite3 --version; if ≥3.37, offer STRICT table option (recommended).
2. Generate DDL: Infer column types from CSV:
   • code → TEXT PRIMARY KEY.
   • label / name → TEXT.
   • Numeric columns → INTEGER or REAL.
   • Timestamps → TEXT (ISO 8601 string) or INTEGER (Unix epoch).
   • parent_code → TEXT with FOREIGN KEY (SQLite supports FK but must enable PRAGMA foreign_keys = ON).
   • For STRICT tables, append STRICT; to the CREATE TABLE statement.
3. Show user the DDL for approval.
4. Handle load mode:
   • replace: DELETE FROM <table>; .import --csv <csv_path> <table>
   • upsert: Use Python sqlite3 or raw SQL INSERT … ON CONFLICT(code) DO UPDATE SET … (SQLite 3.24+).
   • append: .import --csv <csv_path> <table> directly.
5. Enable constraints: Run PRAGMA foreign_keys = ON; before any load if hierarchical.
6. Load via sqlite3 CLI (fast for CSV) or Python sqlite3 module (for typed inserts and error handling).
7. Log the operation: Write to state/loads/<timestamp>-<filename>.log with timestamp, mode, row count, status.

Output / side effects

• SQLite database with populated table.
• Load logged to state/loads/<timestamp>-<filename>.log.

Safety / constraints

• STRICT tables: Recommended for type safety on SQLite ≥3.37; omit for older versions.
• Foreign key support: Must explicitly PRAGMA foreign_keys = ON; to enforce; disabled by default.
• Type flexibility: SQLite is dynamically typed; STRICT mitigates but doesn't guarantee type discipline.