Skip to content
CLEARER IDEAS. BETTER EXECUTION.PROGRAMMING · CREATIVE AI · WORKFLOWS
Menu & topics
Programming

Reject a Stale Project Edit With One Conditional SQLite Update

Original code, local tests and clear limits for SQLite optimistic version conditional update. Separate observed results from proposed integration.

A geometric drawing under blue and amber translucent overlays, with a brass clip and two pencils on a desk
A geometric drawing under blue and amber translucent overlays, with a brass clip and two pencils on a desk.
The short answerA conditional SQLite update can reject an edit based on an old project version. This original example tests the revision condition, then extends it to two connections and an explicit pending-transaction guard. Fourteen local test methods passed; no live editor, simultaneous writer benchmark or distributed transaction was tested.

One project row, two edits, and an overwritten revision

The original fixture starts with project 1 containing body original and version 1. Calling broken_save with body A changes that row. Calling it again with body B changes it again, leaving body B and version 3. That result is recorded by test_baseline_overwrites. The second call has no way to express that it was prepared from the earlier version. It selects the row by ID alone, so an application that uses this statement cannot distinguish a deliberate edit of the latest revision from an edit based on stale text.

A proposed editor can make the problem easier to picture: two editing screens both load version 1, one prepares A, and the other prepares B. We did not build those screens or observe two people editing a real project. The original tests use sequential calls on one in-memory connection. We then added a separate file-backed suite with two connections, where both read version 1 before the first save. That is a controlled interleaving of reads and writes, not a test of simultaneous threads, lock contention or network latency.

The corrected contract requires the caller to supply expected_version as well as the project ID and new body. A successful save says that the row matched that expected version when the conditional statement ran. A rejected stale save leaves the body already stored by the earlier save. This answers a specific question about revision ownership. It does not decide which text is better, authenticate the editor, or merge two edits. Those decisions belong in a caller that can preserve the unsaved text and explain the available choices.

Put the revision condition inside the update statement

The fixture schema has an integer primary-key ID, a text body and an integer version. The corrected statement sets body to a parameter and increments version by one, but only where both id and version match their supplied parameters. Those conditions are part of the same UPDATE statement. The implementation does not first select the version, compare it in Python, and then perform an unconditional write. A separate preliminary read can be useful for display, but it cannot replace the condition on the write that is supposed to reject a stale revision.

An UPDATE statement applies its changes to rows selected by its WHERE expression. Including the expected version in that condition makes the application revision test part of the statement. SQLite UPDATE reference

The helper inspects the cursor rowcount and returns whether it equals 1. For this statement and fixture, a matched project row advances its version, and a stale or absent row does not match. The original test_current_version_updates calls save_revision for project 1, expected version 1 and body A; it receives True and then reads back A with version 2. Do not generalize this boolean into a universal interpretation of every SQL cursor. It is the return contract of this helper around this conditional statement, verified by its own row assertions.

Values are passed with placeholders rather than assembled into SQL text. In test_text_parameters, the new body contains an apostrophe in photo's caption and is stored as that text. The parameter is project content, not part of the SQL syntax. The ID and expected version are also bound values. The function deliberately accepts text as the project body; it does not parse that text into a scene description or validate a JSON schema. A proposed project editor needing structured content should validate its document separately before requesting this database save.

project_versions.py

def broken_save(db,ident,body):
    with db:db.execute('UPDATE projects SET body=?,version=version+1 WHERE id=?',(body,ident))

def save_revision(db,ident,expected_version,body):
    if db.in_transaction:
        raise ValueError('Supply a connection without a pending transaction')
    if type(expected_version) is not int or expected_version < 1:
        raise ValueError('Expected a positive revision integer')
    if not isinstance(body,str):raise TypeError('Expected project text')
    with db:
        changed=db.execute('UPDATE projects SET body=?,version=version+1 WHERE id=? AND version=?',
                           (body,ident,expected_version)).rowcount
    return changed==1

The final helper passed the original 9 in-memory methods after the transaction guard was added. A further 5 methods used two connections to one owned temporary database file, in sequential interleavings. Environment: Python 3.12.3 on Windows, SQLite 3.45.1. The original and final source revisions and actual run records are retained.

Give the helper ownership of a clean connection transaction

The final implementation begins by checking db.in_transaction. If the supplied connection already has a pending transaction, save_revision raises ValueError before entering its own connection context. We added this boundary after examining what the helper promises to own. Without it, a function that uses a connection context can inadvertently include work the caller had already started. This helper is intended to manage its own save on an otherwise idle connection. It is not a general nested-transaction component or a drop-in operation inside any existing application transaction.

The new test_pending_caller_transaction_is_rejected creates an uncommitted row in an audit table through connection A, then calls save_revision on that same connection. The call raises ValueError. A remains in its transaction, while connection B sees zero audit rows and still sees the original project at version 1. The test finally rolls back A. This demonstrates the boundary in a specific fixture: the helper does not commit the caller's pending audit write or change the project as a side effect of rejecting the call.

If your application intentionally wants an audit insertion and project save in one larger transaction, this wrapper is the wrong interface for that composition. A different function would need to make transaction ownership explicit, leaving commit and rollback to the caller or implementing an appropriate separately tested arrangement. Removing the guard without redesigning that contract would discard the behavior just verified. The check also does not make a connection safe for arbitrary cross-thread sharing. Our additional suite uses separate connections and sequential operations, with no claims about concurrent access to one connection.

Read success, stale rejection, and refresh as distinct outcomes

The original stale-version test first saves A from version 1, then attempts to save B using expected version 1 again. The second call returns False, and reading the row still yields A with version 2. That preservation assertion matters as much as the boolean. A function could return False after changing the wrong row, so a useful regression test reads the resulting state instead of trusting the return value alone. In this fixture, the losing edit B is not stored anywhere by the helper; retaining it is the caller's responsibility.

A separate original test demonstrates a later save after refreshing the version. After A advances the row to version 2, a call using expected version 2 can store B and advance the row to version 3. The helper has not merged A and B. The caller has supplied a new expected revision. In a proposed editor, that should follow a visible decision about the current document and local changes, rather than an automatic hidden retry that fetches the latest version solely to overwrite it with the old body.

The missing-ID case also returns False. In the original test, ID 9 does not exist, and the helper reports no successful update. Its boolean does not distinguish that condition from a stale version of an existing project. A product might need different messages for deleted records, stale revisions or access restrictions. Designing that response requires additional reads and authorization rules with their own timing and privacy considerations. The current return value is deliberately smaller: it tells the caller whether this conditional save succeeded, not the complete reason every unsuccessful save failed.

What the original nine methods cover

The nine original methods cover the unguarded overwrite, a current-version save, a stale-version refusal, a refreshed-version save, a missing ID, a boolean version, a non-string body, an apostrophe-containing body and an empty body. The suite creates a fresh in-memory database for each method and registers connection cleanup. It was rerun after adding the pending-transaction guard to the helper. The current execution record therefore belongs to the final source hash, rather than the earlier implementation whose original source and results are retained as history.

The expected version must have type int and be at least 1. Using type rather than an isinstance-only check rejects True even though Python booleans have an integer relationship. test_bool_version_rejected expects ValueError for that supplied value. The body must be a string, and test_nonstring_body_rejected passes a dictionary to confirm TypeError. These are selected boundary cases, not evidence that every possible connection, ID, body size or malformed input has been validated. The helper leaves ID type and existence behavior largely to the statement and database contract.

An empty body is allowed: test_empty_body_is_allowed expects the save to succeed. That is an intentional distinction between text type validation and product content validation. A proposed image-project editor may require a non-empty prompt, a document schema or a maximum length before saving. Those rules are not implemented by this database wrapper, and an empty string must not be described as automatically rejected. Keep the conditional revision check independent of those decisions so that a future content rule can be tested without obscuring what a stale save is supposed to do.

Five additional methods use two connections to one owned file

The additional ConnectionTests fixture creates a temporary directory beneath the owned example directory and opens two SQLite connections to project.sqlite3 inside it. Setup creates the projects and audit tables and commits the initial row. Cleanup closes both connections before removing the temporary directory, and the cleanup target is checked to remain beneath the owned directory. This is a local database file made for the tests. No production project database, WordPress data or external application file is opened or changed by the fixture.

In test_two_readers_one_stale_save, both A and B first read original at version 1. A then saves A from version 1 successfully; B tries B from version 1 and receives False. Both connections subsequently read A at version 2. The sequencing is visible in the test source. It demonstrates that the expected-version condition still does its job when the stale value came from a different connection to the same file. It does not put two UPDATE calls in flight simultaneously, hold an explicit read transaction open, or measure how SQLite resolves competing writer locks.

The test_second_connection_can_refresh_then_save lets B read version 2 after A has saved, then save B with that version. Connection A reads back B at version 3. Alongside the pending-transaction case, two more methods define same-body behavior: a current save of original over original still increments the version, while a stale save of A over already-stored A is refused. These are useful policy edges. Success means an accepted revision operation, not necessarily changed text, and failure is still a revision mismatch even if the proposed body happens to equal the stored body.

Design a proposed conflict response around the unsaved text

For a proposed editing interface, keep localText and baseVersion together from the moment the document is loaded. A save request would carry the project ID, that base version and the proposed body. If the helper returns False, keep localText available instead of replacing the editor contents with the latest database body. The user may want to copy a paragraph, inspect differences or abandon the edit. None of that UI is supplied by the SQLite function. The important design consequence is that a refused save is not permission to discard the work that was never stored.

A proposed conflict view can show three roles: the version originally loaded, the current stored document and the local unsaved document. Obtaining the original document may require retaining it when editing begins; the helper stores only the current row and does not maintain a revision history. Likewise, displaying a comparison does not implement a merge algorithm. Decide which materials the application actually keeps before offering a restore or compare action. Do not promise that the database can recover a prior body merely because its current version integer has increased.

An explicit reload action and an explicit overwrite-after-review action are different choices. A reload might discard local changes after the user confirms that intention. A save based on the newly read version would be another conditional operation using that version, and it can still lose the next revision race. The database helper should not silently convert every False into a fresh expected version. Such a retry would bypass the very information the caller supplied about which revision it edited. Our tests demonstrate refreshed saving, not a recommendation to automate that product decision.

Keep project-row revisions separate from image-file replacement

A project document may refer to an image or manifest stored outside SQLite. The conditional update only controls the projects row in this fixture. It does not make a database commit and a filesystem replacement one atomic operation. A proposed application must decide when a completed image becomes addressable, when the project row starts referring to it, and what to do with a completed file whose row update is refused. Those decisions are outside the current schema, which contains only ID, body and version and no actual image asset records.

One proposed design uses a new immutable asset reference in the edited document and conditionally stores that document after the file is ready. If the row update fails, the newly created asset might need a retention or cleanup policy. That is a design exercise, not a tested recovery system in this project. Keep the original asset reference until the user's revision decision is settled. Avoid treating a completed file write as evidence that the user's project edit was accepted, because file completion and expected-version matching answer different questions.

The separate guide to staging an output file before replacement addresses incomplete file contents. It can inform that proposed asset-writing step, while this article supplies the row revision condition. Neither helper by itself provides an all-or-nothing database-plus-filesystem transaction. If your application needs such a property, write down the exact failure points and add integration tests for the chosen design. Do not infer it from a successful unit test on either component or from seeing the image appear in a preview before the project save has been checked.

Reproduce the checks and preserve the exact code revision

Save project_versions.py, test_example.py and test_connections.py in one writable directory. The original suite uses :memory: and the added suite creates an owned temporary directory under the test module's directory. Read that setup before running it in a location where file creation is restricted. The helper source includes both broken_save for the reproduced baseline and save_revision for the revised contract. Do not connect the baseline function to a real editor: its purpose here is to show the missing expected-version condition with a deliberately small fixture.

From your copied directory, run python -m unittest discover -s . -p test_example.py -v and then python -m unittest discover -s . -p test_connections.py -v. These are reproduction commands; our saved execution records contain the actual interpreter and absolute discovery paths used locally. The original suite has 9 methods and the additional suite has 5, for 14 distinct methods in the final example. Re-running the original nine after a code change is relevant verification, but it does not increase the number of distinct cases to 23.

The recorded environment is Python 3.12.3 on Windows with SQLite 3.45.1. Source hashes bind the helper and the accompanying test modules to their respective run records. Those records are local evidence of execution, not a certificate that the application is correct under every environment. If you modify transaction ownership, the WHERE condition or the version-increment rule, rerun the tests that describe those changes. A result from the old code cannot approve a new statement simply because the function name or filename remains the same.

A project revision is not a global clock

The version integer belongs to a project row. In the fixture it starts at 1 because setup inserts that value, and each accepted update adds one. It is not the SQLite library version, a wall-clock timestamp or an image-generation attempt count. A proposed application with several project rows would need to compare a supplied version with the row selected by the same project ID. Version 2 of one document says nothing about whether another document at version 2 contains the same content or was saved at the same time.

This distinction also explains why the current same-body test advances the integer. The contract records an accepted save operation, rather than using text equality to decide whether a new revision exists. If a product wants to skip unchanged saves, it must define that policy and its interaction with stale requests. The existing stale-same-body test still refuses a request based on version 1 after the stored row has reached version 2. Changing that behavior to success would change the response contract even if no body bytes were rewritten. Keep such a change explicit and test the caller response as well as the final row.

Separate the next concurrency experiment from the one already run

The next useful experiment might use simultaneous writers or an explicitly held transaction, but that would test a different part of the system. Define the connection settings, transaction mode, operation order and expected lock-handling behavior before claiming a result. A locked database response is not automatically a stale-version refusal, and a retry policy for transient access would need to preserve the original expected version where that is still the intended contract. We have not measured lock timeouts, throughput, latency or any distributed editing scenario in these local checks.

The final helper assumes it receives a connection with no pending transaction and then owns its one save operation. Its early guard makes that assumption visible, and the two-connection suite tests that a pending caller write is not committed by a rejected call. A larger service may choose a different ownership model. That change should be made deliberately with a separate API boundary rather than by passing a busy shared connection and hoping the context manager will infer whose work should commit.

The lead photograph uses a geometric drawing with blue and amber tracing overlays, a brass clip and pencils to illustrate separate versions of a project. It is not a screenshot of a conflict interface or evidence of two real editors. The runnable output is the set of database row assertions in the supplied files. If you adapt this project into an editor, retain the exact starting version alongside the unsaved text, verify the conditional-save result, and give the user a concrete decision when that result is False.

test_example.py

import sqlite3,unittest
from project_versions import broken_save,save_revision
class VersionTests(unittest.TestCase):
 def setUp(self):
  self.db=sqlite3.connect(':memory:');self.addCleanup(self.db.close);self.db.execute('CREATE TABLE projects(id INTEGER PRIMARY KEY,body TEXT NOT NULL,version INTEGER NOT NULL)');self.db.execute('INSERT INTO projects VALUES(1,?,1)',('original',));self.db.commit()
 def row(self):return self.db.execute('SELECT body,version FROM projects WHERE id=1').fetchone()
 def test_baseline_overwrites(self):broken_save(self.db,1,'A');broken_save(self.db,1,'B');self.assertEqual(self.row(),('B',3))
 def test_current_version_updates(self):self.assertTrue(save_revision(self.db,1,1,'A'));self.assertEqual(self.row(),('A',2))
 def test_stale_version_refused(self):save_revision(self.db,1,1,'A');self.assertFalse(save_revision(self.db,1,1,'B'));self.assertEqual(self.row(),('A',2))
 def test_refreshed_revision_can_save(self):save_revision(self.db,1,1,'A');self.assertTrue(save_revision(self.db,1,2,'B'));self.assertEqual(self.row(),('B',3))
 def test_missing_id(self):self.assertFalse(save_revision(self.db,9,1,'A'))
 def test_bool_version_rejected(self):
  with self.assertRaises(ValueError):save_revision(self.db,1,True,'A')
 def test_nonstring_body_rejected(self):
  with self.assertRaises(TypeError):save_revision(self.db,1,1,{})
 def test_text_parameters(self):save_revision(self.db,1,1,"photo's caption");self.assertEqual(self.row(),("photo's caption",2))
 def test_empty_body_is_allowed(self):self.assertTrue(save_revision(self.db,1,1,''))

test_connections.py

from pathlib import Path
import unittest,tempfile,sqlite3
from project_versions import save_revision
HERE=Path(__file__).resolve().parent

class ConnectionTests(unittest.TestCase):
    def setUp(self):
        self.temp=tempfile.TemporaryDirectory(dir=HERE,prefix='owned-db-')
        self.path=Path(self.temp.name).resolve()
        assert self.path.is_relative_to(HERE)
        self.addCleanup(self.clean)
        self.a=sqlite3.connect(self.path/'project.sqlite3')
        self.b=sqlite3.connect(self.path/'project.sqlite3')
        self.addCleanup(self.b.close)
        self.addCleanup(self.a.close)
        self.a.execute('CREATE TABLE projects(id INTEGER PRIMARY KEY,body TEXT NOT NULL,version INTEGER NOT NULL)')
        self.a.execute('CREATE TABLE audit(note TEXT)')
        self.a.execute('INSERT INTO projects VALUES(1,?,1)',('original',))
        self.a.commit()

    def clean(self):
        assert self.path.resolve().is_relative_to(HERE)
        self.temp.cleanup()

    def row(self,db):
        return db.execute('SELECT body,version FROM projects WHERE id=1').fetchone()

    def test_two_readers_one_stale_save(self):
        self.assertEqual(self.row(self.a),('original',1))
        self.assertEqual(self.row(self.b),('original',1))
        self.assertTrue(save_revision(self.a,1,1,'A'))
        self.assertFalse(save_revision(self.b,1,1,'B'))
        self.assertEqual(self.row(self.a),('A',2))
        self.assertEqual(self.row(self.b),('A',2))

    def test_second_connection_can_refresh_then_save(self):
        save_revision(self.a,1,1,'A')
        version=self.row(self.b)[1]
        self.assertTrue(save_revision(self.b,1,version,'B'))
        self.assertEqual(self.row(self.a),('B',3))

    def test_pending_caller_transaction_is_rejected(self):
        self.a.execute('INSERT INTO audit VALUES(?)',('pending',))
        with self.assertRaises(ValueError):save_revision(self.a,1,1,'A')
        self.assertTrue(self.a.in_transaction)
        self.assertEqual(self.b.execute('SELECT count(*) FROM audit').fetchone()[0],0)
        self.assertEqual(self.row(self.b),('original',1))
        self.a.rollback()

    def test_same_body_still_advances_current_version(self):
        self.assertTrue(save_revision(self.a,1,1,'original'))
        self.assertEqual(self.row(self.b),('original',2))

    def test_stale_same_body_is_still_rejected(self):
        save_revision(self.a,1,1,'A')
        self.assertFalse(save_revision(self.b,1,1,'A'))
        self.assertEqual(self.row(self.b),('A',2))

Prepared with AI assistance and automated reviews. The original code was run locally within the recorded test scope. The lead image is illustrative; it is not a field photograph or evidence of a browser, spreadsheet or video test.