# SQLite Tips

Gotchas encountered in this codebase. Review before writing or modifying SQLite queries.

## INSERT OR REPLACE is a DELETE + INSERT

`INSERT OR REPLACE` (and its alias `REPLACE INTO`) deletes the conflicting row and inserts a new one. Any columns not included in the INSERT list are reset to their defaults (typically NULL).

**Symptom:** A column set by a separate write path (e.g. `set_card_issue_number`) is silently wiped to NULL by the next sync cycle's upsert.

**Fix:** Use `INSERT ... ON CONFLICT(pk) DO UPDATE SET col = excluded.col` and explicitly list only the columns the upsert should touch. Columns managed by other code paths are left untouched.

```sql
-- BAD: wipes github_issue_number set elsewhere
INSERT OR REPLACE INTO cards (card_id, name, status) VALUES (?1, ?2, ?3);

-- GOOD: preserves columns not in the SET list
INSERT INTO cards (card_id, name, status) VALUES (?1, ?2, ?3)
ON CONFLICT(card_id) DO UPDATE SET
    name = excluded.name,
    status = excluded.status;
```

## DELETE + re-INSERT wipes metadata columns

A common "upsert children" pattern — `DELETE FROM notes WHERE card_id = ?` then re-INSERT — destroys any metadata added to existing rows (e.g. `synced_at` timestamps tracking whether a note was pushed to an external system).

**Fix:** Diff the incoming list against existing rows. Delete only removed entries, insert only new ones, leave unchanged rows untouched.

## refinery migrations: CRLF line endings cause DivergentVersion panic on Windows

The `refinery` library computes a checksum over the raw SQL file content. On Windows, git's `core.autocrlf` setting may cause migration files to be written to the working copy with CRLF (`\r\n`) line endings, even though git's index stores them as LF (`\n`).

If the migration was first applied when the file had LF content (the normal case), the stored checksum reflects LF. A later working copy write with CRLF produces a different checksum, triggering a `DivergentVersion` error at startup:

```
Error { kind: DivergentVersion(Migration { checksum: 328701637440587379, ... }, Migration { checksum: 17885665493811618216, ... }) }
```

**Fix:** Restore LF endings in the migration file:
```python
with open('V00N__name.sql', 'rb') as f: content = f.read()
with open('V00N__name.sql', 'wb') as f: f.write(content.replace(b'\r\n', b'\n'))
```

After saving, git will show no diff (index and working copy match). The app will start normally.

**Prevention:** Add a `.gitattributes` rule or use `text eol=lf` for SQL migration files to prevent autocrlf conversion. Never edit migration files in Windows tools that silently add CRLF.

## rusqlite: get::<_, Option<String>> fails silently for non-text columns

`row.get::<_, Option<String>>(i)` returns `Err` (not a coerced string) for INTEGER and REAL columns. If the error is swallowed with `.unwrap_or(None)`, every non-text value appears as NULL.

**Fix:** Use `row.get_ref(i)` and match on `ValueRef` variants (`Null`, `Integer`, `Real`, `Text`, `Blob`) to handle all SQLite types correctly.

```rust
match row.get_ref(i) {
    Ok(ValueRef::Integer(n)) => n.to_string(),
    Ok(ValueRef::Real(f))    => f.to_string(),
    Ok(ValueRef::Text(s))    => String::from_utf8_lossy(s).to_string(),
    Ok(ValueRef::Blob(b))    => format!("<blob {} bytes>", b.len()),
    Ok(ValueRef::Null) | Err(_) => "NULL".to_string(),
}
```