Developers of Sympa

[IKEDA Soji <address@concealed>]

On Thu, 22 Aug 2013 00:43:02 +0900
IKEDA Soji <address@concealed> wrote:
>                              I'll post correct code after some days.
Attached below.  Some remarks:

- PREPARE may be implemented on server-side (PostgreSQL, Oracle,
  ...), hence connection loss breaks SQL session so that statement
  handles cached on client-side no longer will be usable.  So we
  shouldn't provide "cached" set of functions and "safe connection"
  set separately:  We may provide exactly two sets:

    $handle = $database->prepare_query_handle_safely($statement, ...);
    $databse->execute_safely($handle, ...);

    $handle = $database->prepare_query_handle($statement, ...);
    $handle->execute(...);

- We must RTFM.  Prototype won't work on methods; assigning local @_
  will work.


Regards,

--- Soji

sub get_query_handle {
        my ($self, $query, %types) = @_;

        my $handle = $self->{dbh}->prepare($query);
        return undef unless $handle;

        # Bind parameters with special types.
        # Since this may be done only once when handle is prepared, dummy
        # parameter value '0' will be used.
        foreach my $i (sort keys %types) {
                next unless defined $types{$i};
                return undef
                        unless $handle->bind_param($i, '0', $types{$i});
        }
        # Since some DBDs don't implement "ParamTypes" and/or "Statement"
        # handle attributes, save them into private attributes.
        $handle->{private_Sympa_statement} = $query;
        $handle->{private_Sympa_types} = \%types if %types;

        return $handle;
}

sub prepare_query_handle_safely {
        my ($self, $query, %types) = @_;

        # Check connectivity.  When it failed, try to establish new session.
        if ($self->{dbh}->ping) {
                # Use cached handle if any.
                return $self->{cache}->{$query} if $self->{cache}->{$query};
        } else {
                # Discard dead caches then reconnect.
                map { $_->finish } values %{$self->{cache} || {}};
                delete $self->{cache};
                $self->connect(keep_trying => 1);
        }

        # Prepare statement.  Failure means syntax error or binding error,
        # because connectivity has already been checked.
        return
                $self->{cache}->{$query} =
                $self->get_query_handle($query, %types);
}

sub execute_safely {
        # Don't break @_ so that $handle may be updated.
        my ($self, $handle, @params) = @_;

        # Execute.  When failure caused by lost connection, try to establish
        # new session.
        my $rv = $handle->execute(@params);
        return $rv if defined $rv;           # success.
        return undef if $self->{dbh}->ping;  # other than lost connection.

        # Discard dead caches then reconnect.
        map { $_->finish } values %{$self->{cache} || {}};
        delete $self->{cache};
        $self->connect(keep_trying => 1);

        # Retry preparing and executing.
        my $query = $handle->{private_Sympa_statement};
        my %types = %{$handle->{private_Sympa_types} || {}};
        $handle = $self->get_query_handle($query, %types);
        return undef unless $handle;

        $self->{cache}->{$query} = $handle; # update cache.
        $_[1] = $handle; # update handle on caller side.
        return $handle->execute(@params);
}

$$