Re: [Openinteract-help] SPOPS and database transactions

[Eric V. <er...@te...>]

On Thu, Aug 25, 2005 at 02:29:24PM -0500, Ken Youens-Clark wrote:
> Sorry if this is in the docs somewhere, but is there a recommended way=20
> to use transactions with SPOPS.  I can't find anything in the POD, but=20
> it seems I can grab the "global_datasource_handle" and set=20
> "AutoCommit=3D0" and then call "commit" at the end?

I believe there is some documentation somewhere in the docs, but to get you
started, this is what I use in my SPOPS base class with postgres as a datab=
ase
backend. The global $DBH variable holda the Database Handle as returned by =
DBI
and is set by the global_datasource_handle method.

Whenever you need to do things in a transaction, just call start_transactio=
n()
en commit the transaction with commit(), or call rollback if something went
wrong. This also allows some form of nested transactions. Postgress itself
doesn't, so only the outer transaction is a real transaction. Which is usua=
lly
what you want anyway.

=3Dhead2 start_transaction

Start a transaction

=3Dcut

sub start_transaction {
    # Disable AutoCommit, start transaction
    if ($transactionlevel =3D=3D 0) {
        if (!$DBH->{AutoCommit}) {
            warn "commiting";
            $DBH->commit();
            $transactionlevel =3D 0;
        }
        $DBH->begin_work();
        $DBH->do('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
        $DBH->do('SET CONSTRAINTS ALL DEFERRED');
    }
    $transactionlevel++;
}

=3Dhead2 commit

Commit your changes to the database

=3Dcut

sub commit {
    if ($transactionlevel =3D=3D 1) {
        my $rc =3D eval { $DBH->commit };
        SPOPS::Exception->throw ($DBH->errstr || 'Commit failed') if (!$rc =
|| $@);
        $transactionlevel =3D 0;
    } else {
        if ($transactionlevel > 0) {
            $transactionlevel--;
        }
    }
}

=3Dhead2 rollback

Rollback any changes you made to the database

=3Dcut

sub rollback {
    # Don't do a rollback if we were not in a transaction.
    if (!$DBH->{AutoCommit}) {
        # Enable AutoCommit, ends transaction support
        $DBH->rollback() or
            SPOPS::Exception->throw ($DBH->errstr || 'Rollback failed');
        $transactionlevel =3D 0;
    }
}

--=20
#!perl #                       Life ain't fair, but root passwords help.
# Eric Veldhuyzen                                       http://terra.nu/
$!=3D$;=3D$_+(++$_);($:,$~,$/,$^,$*,$@)=3D$!=3D~                 # eric@ter=
ra.nu
/.(.)...(.)(.)....(.)..(.)..(.)/;`$^$~$/$: $^$*$@$~ $_>&$;` #Perl Monger