Re: Re: A couple of bugs in sqlite DBD/DBI

On Wed, Jul 14, 2004 at 10:17:41AM +0200, Michael Neumann wrote:
> Hm, lots of problems are due to the DBI abstracting too much, e.g. the 
> transaction method. It would be better to implement #transaction inside 
> the DBDs, this way, for Pg it could be written:
> 
>   def transaction
>     start_transaction
>     begin
>       yield self
>       commit
>     rescue Exception
>       rollback
>       raise
>     end
>   end
> 
> where start_transaction starts a new transaction immediatly.

OK. That's the same as DBI does at the moment of course (except, not havin
'start_transaction', it does 'commit' at the top)

> I think it's really better to remove the AutoCommit settings completely.
> Not sure if this works with other DBDs than Pg and Sqlite.

I believe most databases natively have the concept of an 'AutoCommit' mode
which can be configured on their command stream, so it's probably a concept
worth preserving. For example, Mysql does this by sending 'SET AUTOCOMMIT
0|1" (see DBD/Mysql/Mysql.rb, def []=)

> How about 
> databases that do not have an explicit "BEGIN TRANSACTION"? They usually 
> start a new one implicitly after a COMMIT, right? Anyone knowns which 
> database work this way?

Or a similar question is, "when a database has AutoCommit=false set, what
happens if you issue a command *before* issuing BEGIN TRANSACTION?"

This could automatically start a new transaction. Or it could automatically
start a new transaction only if this is a command which modifies the
database. Or it could raise an error.

To be honest, this sort of thing I'm prepared to leave in the realm of
"database-dependent behaviour", because if your database is set to
AutoCommit=false, but you are not using the 'begin'/'commit' methods, then
your code is at fault.

But we *could* normalise this in DBI/DBD (for example, by sending a 'begin'
command automatically if you haven't sent one already)

> In the latter case
> 
>   dbh.transaction do
>     ...
>   end
> 
> would be nearly equivalent to
> 
>   dbh.autocommit = false
>   dbh.commit     # ?
>   begin
>     yield
>     commit
>   resuce ...
>     rollback
>     raise
>   ensure
>     dbh.autocommit = true
>   end
> 
> But how to implement #start_transaction ?

Probably something like:

  def start_transaction
    if @in_transaction
      warn("Nested transactions not supported")
    else
      send("BEGIN TRANSACTION")    # depends on the database
      @in_transaction = true
    end
  end

For those databases which don't actually have a BEGIN command, but the start
of a transaction is implicit, then you don't send anything.

  def commit
    if @in_transaction
      send("COMMIT")
      @in_transaction = false
    else
      warn("Commit outside of a transaction does nothing")
    end
  end

Then you remove all the logic which sends 'BEGIN TRANSACTION' automatically
when a command is sent (DBD::Pg) or immediately the connection is opened
(DBD:SQLite)

Regards,

Brian.