added_ms and updated_ms columns are not reliable in face of operations like INSERT OR REPLACE INTO #7
Description
Original title: Using insert-or-replace sets added_ms and updated_ms to the same value
Tip
See this comment for a summary of this issue, it got very long!
I just noticed that on this demo:
- https://demos.datasette.cloud/data?sql=select+document_number%2C+added_ms%2C+updated_ms%2C+version%2C+deleted+from+_chronicle_documents+where+added_ms+%21%3D+updated_ms - 0 results
- From https://demos.datasette.cloud/data/_chronicle_documents and https://demos.datasette.cloud/data/documents
That's populated by this script https://github.com/simonw/federal-register-to-datasette/blob/fb848b0e05ff79ca60a9d9d8adb0c9a36a938751/fetch_documents.py which makes this API call:
body = {
"rows": documents,
"replace": True,
}
req = urllib.request.Request(
"https://demos.datasette.cloud/data/documents/-/insert",
data=json.dumps(body).encode(),
headers=headers,
method="POST",
)Which results in a call to the sqlite-utils method .insert_all(..., replace=True)
Which does this: https://github.com/simonw/sqlite-utils/blob/88bd37220593f46ad2221601d6724dd0198400ad/sqlite_utils/db.py#L2983-L2988
INSERT OR REPLACE INTO documents ...