System should work even if two batches of updates happened within the same millisecond

[simonw]

tricky problem has emerged while testing: SQLite is fast. My tests are trying to find everything that changed since an operation that took place within the same millisecond, which is failing because of:

        sql = textwrap.dedent(
            f"""
            SELECT {select_clause}
            FROM "_chronicle_{table_name}" chronicle
            LEFT JOIN "{table_name}" t ON {join_on}
            WHERE chronicle.updated_ms > ?
            ORDER BY chronicle.updated_ms
            LIMIT {batch_size}
            """
        )
        rows = cursor.execute(sql, (since,)).fetchall()

Going for WHERE chronicle.updated_ms > ? misses events which happened within > the same ms - which is clearly something that needs to be accounted for!

Originally posted by @simonw in #2 (comment)

[simonw]

The solution is to use a version column which increments every time, using max(version) + 1 - with an index to make both this increment and the lookups of rows that have changed since version X as fast as possible.