On Oct 5, 2009, at 5:01 AM, Tim Bunce wrote:
We already have the example of DBI transaction support; savepoints
are just
an extension of that.
Well, as you said earlier David, "Transactions <> Savepoints."
No, but they're closely related.
Commit and rollback are methods partly to support drivers that emulate
transactions n some way, partly to ensure client and server state stay
in sync, and partly to allow, at least in theory, the use of
transaction
managers.
The same would apply to savepoints, which are perhaps best thought of
as subtransaction markers.
I'd be interested if someone could do the research to list what
databases support savepoints and what syntax they use for the main
statements.
DBIx::Class has done this for a lot of databases. Check out
http://dev.catalyst.perl.org/repos/bast/DBIx-Class/0.08/trunk/lib/DBIx/Class/Storage/DBI/
A grep in there finds support in MySQL, PostgreSQL, MSSQL, and Oracle:
MSSQL:
SAVE TRANSACTION $name;
ROLLBACK TRANSACTION $name;
MySQL:
SAVEPOINT $name;
RELEASE SAVEPOINT $name;
ROLLBACK TO SAVEPOINT $name;
Oracle:
SAVEPOINT $name;
ROLLBACK TO SAVEPOINT $name;
Pg:
$dbh->pg_savepoint($name);
$dbh->pg_release($name);
$dbh->pg_rollback_to($name);
DBD::Pg is the only one to offer an API for it in the database handle.
It looks like it uses a protocol-level command to do the work, but the
database itself supports the SQL-standard interface that's identical
to what mysql supports. Oracle's implementation is standard, except
for the lack of a release command, which is apparently implicit.
MSSQL's interface also lacks a release, and the names for the commands
are non-compliant.
To me, this stuff is so closely related to transaction management that
there ought to be a uniform interface for it in the DBI. If DBI2
doesn't have an API for transactions, then I wouldn't argue to add
savepoints there. But it sure makes sense to me for the DBI.
Best,
David
