Fix INSERT OR REPLACE tracking in sqlite-chronicle#21
Merged
Conversation
INSERT OR REPLACE was incorrectly tracked because SQLite propagates the outer statement's conflict resolution strategy into trigger bodies, turning INSERT OR IGNORE into INSERT OR REPLACE. Additionally, when recursive_triggers is ON (as sqlite-utils enables), the implicit DELETE within REPLACE fires the AFTER DELETE trigger, causing spurious __deleted=1 markings and wasted version numbers. The fix introduces: - A snapshot table (_chronicle_<table>_snapshot) to store old row data - A BEFORE INSERT trigger that snapshots non-PK values before REPLACE's internal delete (old row is still visible at this point) - An AFTER INSERT trigger using INSERT...WHERE NOT EXISTS instead of INSERT OR IGNORE, with logic to handle un-delete, replace-with-change, and fresh insert cases - A WHEN guard on the AFTER DELETE trigger that skips when a snapshot exists (indicating we're inside an INSERT OR REPLACE) https://claude.ai/code/session_01F8BBUJDEQmdTQwjivJFf59
- Add examples/trigger-demos.md: a showboat document with 13 executable demos proving every trigger behavior (basic tracking, conflict resolution propagation, INSERT OR REPLACE variants, compound PKs, UPSERT, recursive_triggers compatibility, snapshot cleanup, etc.) - Update README: remove the INSERT OR REPLACE caveat (now fully supported), describe the four triggers, and regenerate the cog SQL schema block to show the new trigger design https://claude.ai/code/session_01F8BBUJDEQmdTQwjivJFf59
…snapshot_{table}
Avoids naming confusion if someone has an original table named
"snapshot_x" — the chronicle and snapshot helper tables now both
start with the _chronicle_ prefix family.
https://claude.ai/code/session_01F8BBUJDEQmdTQwjivJFf59
Wrap non-PK column references with quote() inside json_array() calls so BLOB values survive JSON serialization. Without this, INSERT OR REPLACE on a table with BLOB columns raised "JSON cannot hold BLOB values". Add three new tests: - test_insert_or_replace_blob_column: BLOB insert, change, no-op - test_insert_or_replace_null_values: NULL handling in change detection - test_insert_or_replace_mixed_types: TEXT, INTEGER, REAL, BLOB, NULL https://claude.ai/code/session_01F8BBUJDEQmdTQwjivJFf59
Owner
Author
|
Extremely useful Showboat demo of the new feature: https://github.com/simonw/sqlite-chronicle/blob/51a3758ba9b9a1f727b03c61db8b083b47a6e526/examples/insert-or-replace.md - includes a clear explanation of the new approach. |
Owner
Author
|
I manually reviewed this all and it looks great. |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
2 participants
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
Summary
This PR fixes sqlite-chronicle to correctly track
INSERT OR REPLACEoperations. Previously, the library required users to useUPSERT(INSERT…ON CONFLICT DO UPDATE) instead ofINSERT OR REPLACEdue to SQLite's conflict resolution propagation into trigger bodies. This change implements proper support forINSERT OR REPLACEby using a snapshot table and careful trigger logic.Key Changes
Snapshot table mechanism: Added
_chronicle_{table_name}_snapshottable to capture the old row state before SQLite's internal DELETE during REPLACE operations.BEFORE INSERT trigger: New trigger captures the current row data (if it exists) into the snapshot table before the REPLACE's implicit delete occurs.
Revised AFTER INSERT trigger: Replaced
INSERT OR IGNOREwithINSERT...WHERE NOT EXISTSto avoid SQLite's conflict resolution propagation. The trigger now:AFTER DELETE trigger guard: Added
WHEN NOT EXISTS(SELECT 1 FROM snapshot...)clause to prevent spurious deletion markers when the DELETE is part of an internal REPLACE operation.Compound primary key support: Snapshot keys use
json_array()for compound PKs and simple CAST for single PKs.Change detection: Uses JSON array representation of non-PK columns to accurately detect whether data actually changed, preventing no-op version bumps.
Upgrade path: Updated
upgrade_chronicle()to handle migration of old trigger names and create the snapshot table.Notable Implementation Details
__added_mstimestamps across REPLACE operations, only updating__updated_msand__versionwhen data actually changes.https://claude.ai/code/session_01F8BBUJDEQmdTQwjivJFf59