coolqlite.experimental¶

coolqlite.experimental.transactions¶

For advanced usage, let’s make sure you understand savepoints first.

About Savepoints¶

You shouldn’t just think of savepoints as “named, nested commits”, because the terminology is slightly different. You should get comfortable with the official sqlite documentation for savepoints.

Essentially, savepoints are a stack of frames, each holding some amount of database changes. The first created savepoint is the “outermost” savepoint, the most recently created savepoint is the “innermost” savepoint, and the ones in between are “outer” and “inner” relative to each other.

When you want to okay your changes, instead of “committing”, you “release” that frame. If there’s an outer frame, that means the changes are now “merged” into that frame, like removing a fence sectioning off a field. If the frame you released is already the outermost, last one, then those changes are written to the database.

With plain BEGIN..COMMIT transactions, we tend to think of rollbacks as “cancelling”. And indeed, a vanilla ROLLBACK on its own will cancel everything. But when we use rollback to SAVEPOINT_NAME, it’s more like “restarting” that savepoint. All changes within that frame are discarded, but the frame remains active! For clarity, we refer to this as “rolling back to after” the savepoint.

You can still have the “cancellation” behavior by immediately releasing a just-rolled-back savepoint. We call this “rolling back before” the savepoint.

Now that we understand how savepoints work in sqlite, let’s see how they work in coolqlite.

Advanced Usage¶

Warning

This part of the API is somewhat experimental.

We try to map an active savepoint to a context manager with-block 1:1.

This means that if you do anything that would make the savepoint no longer active (releasing it, rolling back before it, rolling back before or after an outer savepoint), this should be viewed as an “early exit” from that with-block.

This is accomplished with a TransactionControlException.

Isn’t it bad to use exceptions for control flow?

Because Exceptions aren’t reflected in a function’s signature in many languages, using them for control flow is considered surprising and hidden behavior.

Here it’s not as hidden and surprising, because this is only ever raised by methods you yourself called in ALL_CAPS. Also, control flow only ever jumps to after the with-block holding the context manager you called that method on.

Also, it is the only way to model a conditional early exit from a context manager– that sounds a lot like what an exception does to me.

Just think of it as a special return that is scoped to that with-block instead of a function.

Warning

This means it is always incorrect to catch a TransactionControlException without re-raising it.

You may need to handle this specially if writing your own context manager or catching exceptions that happen within a Savepoint context manager.

An innermost savepoint can do the following:

Roll back to after the savepoint was started, discarding all changes since then, but staying within the savepoint.
Roll back to before the savepoint was started, discarding all changes since then, and exiting / erasing that savepoint
Release the savepoint, committing changes to the database and exiting / erasing this savepoint.

Following the 1:1 rule, this means that 2 and 3 should exit the with-block, but 1 should stay in it.

With this in mind, let’s do a similar database setup.

import sqlite3
import coolqlite
import coolqlite.transactions

db = coolqlite.connect(":memory:")
db.query(t"""create table Names
         ( name TEXT NOT NULL UNIQUE ON CONFLICT ROLLBACK
         ) strict""").close()

def has_name(name: str) -> bool:
    return 1 == db.colquery(t"select count(*) from Names where name = {name}").one()

def insert_name(name: str):
    db.query(t"insert into Names values ({name})").close()

Scenario 1, rolling back to after, is straightforward:

ni = "The Knights Who Say Ni"
new_name = "The Knights Who Say I'm Not Writing All That"
with db.with_EXPERIMENTAL_SAVEPOINT() as sp:
    insert_name(ni)
    assert has_name(ni)

    print("We are no longer the Knights Who Say Ni.")
    sp.rollback_to_after_this_innermost()
    insert_name(new_name)

    assert not has_name(ni) and has_name(new_name)  # inside...

assert not has_name(ni) and has_name(new_name)  # and out.

You’ll understand why the method is called that later.

Scenario 2, rolling back before, is a little more unusual. Since it means exiting the savepoint, we exit the with-block early with an exception. Because that happens, the method is in ALL_CAPS to call out how it’s different.

with db.with_EXPERIMENTAL_SAVEPOINT() as sp:
    insert_name("Someone")
    result_of_some_other_check_step = True
    if result_of_some_other_check_step:
        sp.ROLLBACK_TO_BEFORE()
    assert False, "this never runs"

assert not has_name("Someone")

Scenario 3, releasing (like committing), is similar. Because it means you’re “exiting” the savepoint, we exit the with-block early again with an exception.

terry = "Terry Nanny"
with db.with_EXPERIMENTAL_SAVEPOINT() as sp:
    insert_name(terry)

    are_we_good_here = True
    if are_we_good_here:
        sp.RELEASE()
    db.query(t"delete from Names where name = {terry}").close()
    assert False, "the above never runs!"

assert has_name(terry)

Nested Savepoints¶

A savepoint that isn’t the innermost can do all of the above, with one caveat. Let’s go backwards.

Scenario 3, releasing, works similarly when called on an outer savepoint.

with db.with_EXPERIMENTAL_SAVEPOINT() as outer:
    insert_name("Jib")

    with db.with_savepoint():
        insert_name("Jab")

        outer.RELEASE()
        assert False, "this never runs"

    assert False, "this never runs either!"

assert has_name("Jib") and has_name("Jab")

Scenario 2, rolling back before, also similar.

with db.with_EXPERIMENTAL_SAVEPOINT() as outer:
    insert_name(ni)

    with db.with_savepoint() as inner:
        print("We are no longer the Kni... where are we?")
        outer.ROLLBACK_TO_BEFORE()
        assert False, "this never runs!"

    assert False, "this also never runs!"

assert not has_name(ni)

Scenario 1, rolling back after, is where things get tricky.

If you’re nested within an outer savepoint, what would it mean to rollback after it? The outer savepoint keeps going, sure, but that means your nested one should be erased. Sure enough, that means the inner one needs to exit early with an exception.

Note that we’re calling this method on the savepoint we want to reset-after to, not the inner nested one!

bojack = "Bojack Horseman"
mpb = "Mr. Peanutbutter"
# TODO: test if this nests properly
with db.with_EXPERIMENTAL_SAVEPOINT() as outer:
    insert_name(bojack)

    with db.with_savepoint() as inner:
        insert_name(mpb)
        print("What is this, a crossover episode?")
        outer.ROLLBACK_TO_AFTER_NOT_INNERMOST()
        assert False, "this never runs!"

    assert not has_name(bojack) and not has_name(mpb)
    # on second thought, let's not go to camelot.
    insert_name("Something Normal")

assert not has_name(bojack) and not has_name(mpb)
assert has_name("Something Normal")

Caveat: ON CONFLICT ROLLBACK¶

There is one tricky case that we’re still figuring out how to handle well.

We mentioned how an unqualified ROLLBACK will tear down all savepoints. Well, that applies to rollbacks that happen from ON CONFLICT ROLLBACK triggers on unique columns, as well as INSERT OR ROLLBACK queries.

Todo

verify the insert part. Is has to be the same, right?

Recall that in our example we made the name column UNIQUE ON CONFLICT ROLLBACK.

try:
    with db.with_savepoint() as outer:
        insert_name("Pete")
        with db.with_savepoint() as inner:
            insert_name("Repeat")
            insert_name("Repeat")  # uhoh
except sqlite3.IntegrityError:
    print("oops!")
assert not has_name("Pete") and not has_name("Repeat")

This is handled correctly by the exception handler for the savepoint context manager. In fact, only one of them needs to see it to properly invalidate all of the others!

But, what if you somehow kept going in a savepoint with-block?

try:
    with db.with_EXPERIMENTAL_SAVEPOINT() as outer:
        insert_name("Pete")
        try:
            with db.with_savepoint() as inner:
                insert_name("Repeat")
                insert_name("Repeat")  # uhoh
        except sqlite3.IntegrityError:
            print("phew! glad we handled that.")
            # let's start over though--
            outer.rollback_to_after_this_innermost()
except coolqlite.transactions.IncorrectTransactionControlError:
    print("that's kinda tricky to handle, isn't it?")

Todo

Improve the error message on that.

An invalidated savepoint context manager knows to not touch anything on exit. But we can’t stop you from using the methods. For this reason, we encourage you to let those errors bubble up to the outermost savepoint.

We may offer a method to deliberately “jump” to the outermost transaction when this happens, as we can more or less detect when this happens. But I’m still thinking about the API for that.

But even worse is when the context manager doesn’t see it at all:

try:
    with db.with_EXPERIMENTAL_SAVEPOINT() as outer:
        insert_name("Pete")
        with db.with_savepoint() as inner:
            insert_name("Repeat")
            try:
                insert_name("Repeat")  # uhoh
            except sqlite3.IntegrityError:
                print("phew! glad we handled that.")
                # let's start over though--
                outer.rollback_to_after_this_innermost()
except sqlite3.OperationalError:
    print("CRAP WE GOTTA HANDLE THIS")

Todo

I think I can handle that better with similar detection techniques.

class coolqlite.experimental.transactions.AdvancedSavepoint(private, id: str, connec: Connection[__annotationlib_name_1__], parent: AdvancedSavepoint | None)¶

Bases: object

A context manager representing a sqlite savepoint.

Create with the coolqlite.Connection.with_savepoint() method.

Warning

Only use this as a context manager, the methods assume you are and things will break if you don’t.

Warning

Methods in ALL_CAPS use TransactionControlException to work and enforce correctness.

Understand then when writing code within the with block, using other context managers that interact with exceptions, and when using try/except/finally.

In other words, the exceptions they throw are not problems, unless you mishandle them.

The methods are properly type-annotated, so tools should show that code that follows is unreachable.

See the module docs for details.

rollback_to_after_this_innermost()¶

Roll back this, the innermost savepoint, to after it started. This will undo all changes since then.

Note that this doesn’t “re-run” the context manager block, it merely discards previous changes that happened within.

Warning

Using this on anything but the innermost savepoint is incorrect and will raise an IncorrectTransactionControlError.

If you wish to use this on an outer savepoint, see ROLLBACK_TO_AFTER_NOT_INNERMOST().

ROLLBACK_TO_AFTER_NOT_INNERMOST() → Never¶

Roll back this, an outer savepoint, to after it started. Specifically, this will raise an exception that is caught by the context manager one level within this one.

Note that this doesn’t “re-run” the context manager block, it merely discards previous changes that happened within.

See the module docs for details.

Warning

Using this on anything but an outer savepoint is incorrect and will raise an IncorrectTransactionControlError.

If you wish to use this on an inner savepoint, see rollback_to_after_this_innermost().

ROLLBACK_TO_BEFORE() → Never¶

Roll back before this savepoint. Specifically, this will raise an exception that is caught by this context manager.

See the module docs for details.

RELEASE() → Never¶

Release this savepoint. If there are outer savepoints, this “merges” the changes into them. If this is the outermost savepoint, this commits changes to the database. Specifically, this will raise an exception that is caught by this context manager.

See the module docs for details.

exception coolqlite.experimental.transactions.TransactionControlException¶

Bases: CoolqliteError

An exception used when an operation on a savepoint should alter control flow.

Warning

It is always incorrect to catch this without re-raising it.

You may need to handle this specially if writing your own context manager or catching exceptions that happen within a Savepoint context manager.

Isn’t it bad to use exceptions for control flow?

Because Exceptions aren’t reflected in a function’s signature in many languages, it’s considered to be surprising hidden behavior.

It’s not that hidden or surprising, because this is only ever raised by methods you yourself called in ALL_CAPS.

Also, it is the only way to model a conditional early exit from a context manager– that sounds a lot like what an exception does to me.

Warning

To show that this is special, we may change this to inherit from BaseException in the future.

exception coolqlite.experimental.transactions.IncorrectTransactionControlError¶

Bases: CoolqliteError

Something incorrect happened when using savepoints. This should only happen if using the savepoint context manager incorrectly, or otherwise doing something that would break a savepoint correctness guarantee.