Jifty::DBI::Handle::Pg - A Postgres specific Handle object

  Jifty::DBI::Handle::Pg - A Postgres specific Handle object

package Jifty::DBI::Handle::Pg;
use strict;

use vars qw($VERSION @ISA $DBIHandle $DEBUG);
use base qw(Jifty::DBI::Handle);

use strict;

sub connect {
    my $self = shift;

    $self->SUPER::connect(@_);
    $self->simple_query("SET TIME ZONE 'GMT'");
    $self->simple_query("SET DATESTYLE TO 'ISO'");
    $self->auto_commit(1);
    return ($DBIHandle);
}

sub insert {
    my $self  = shift;
    my $table = shift;
    my %args  = (@_);
    my $sth   = $self->SUPER::insert( $table, %args );

    unless ($sth) {
        return ($sth);
    }

    if ( $args{'id'} || $args{'Id'} ) {
        $self->{'id'} = $args{'id'} || $args{'Id'};
        return ( $self->{'id'} );
    }

    my $sequence_name = $self->id_sequence_name($table);
    unless ($sequence_name) { return ($sequence_name) }   # Class::ReturnValue
    my $seqsth = $self->dbh->prepare(
        qq{SELECT CURRVAL('} . $sequence_name . qq{')} );
    $seqsth->execute;
    $self->{'id'} = $seqsth->fetchrow_array();

    return ( $self->{'id'} );
}

sub id_sequence_name {
    my $self  = shift;
    my $table = shift;

    return $self->{'_sequences'}{$table}
        if ( exists $self->{'_sequences'}{$table} );

    #Lets get the id of that row we just inserted
    my $seq;
    my $colinfosth = $self->dbh->column_info( undef, undef, lc($table), '%' );
    while ( my $foo = $colinfosth->fetchrow_hashref ) {

        # Regexp from DBIx::Class's Pg handle. Thanks to Marcus Ramberg
        if ( defined $foo->{'COLUMN_DEF'}
            && $foo->{'COLUMN_DEF'}
            =~ m!^nextval\(+'"?([^"']+)"?'(::(?:text|regclass)\))+!i )
        {
            return $self->{'_sequences'}{$table} = $1;
        }

    }
    my $ret = Class::ReturnValue->new();
    $ret->as_error(
        errno        => '-1',
        message      => "Found no sequence for $table",
        do_backtrace => undef
    );
    return ( $ret->return_value );

}

sub blob_params {
    my $self = shift;
    my $name = shift;
    my $type = shift;

    # Don't assign to key 'value' as it is defined later.
    return ( { pg_type => DBD::Pg::PG_BYTEA() } )
        if $type =~ /^(?:blob|bytea)$/;
    return ( {} );
}

sub apply_limits {
    my $self         = shift;
    my $statementref = shift;
    my $per_page     = shift;
    my $first        = shift;

    my $limit_clause = '';

    if ($per_page) {
        $limit_clause = " LIMIT ";
        $limit_clause .= $per_page;
        if ( $first && $first != 0 ) {
            $limit_clause .= " OFFSET $first";
        }
    }

    $$statementref .= $limit_clause;

}

sub _make_clause_case_insensitive {
    my $self     = shift;
    my $column   = shift;
    my $operator = shift;
    my $value    = shift;

    if ( $self->_case_insensitivity_valid( $column, $operator, $value ) ) {
        $column = "LOWER($column)";
        if ( $operator =~ /^(IN|=)$/i and ref($value) eq 'ARRAY' ) {
            $value = [ map {"LOWER($_)"} @$value ];
        } else {
            $value = "LOWER($value)";
        }
    }
    return ( $column, $operator, $value );
}

sub distinct_query {
    my $self         = shift;
    my $statementref = shift;
    my $collection   = shift;
    my $table        = $collection->table;

    if (grep {
            ( defined $_->{'alias'} and $_->{'alias'} ne 'main' )
                || defined $_->{'function'}
        } @{ $collection->order_by }
        )
    {

        # If we are ordering by something not in 'main', we need to GROUP
        # BY and adjust the ORDER_BY accordingly
        local $collection->{group_by}
            = [ @{ $collection->{group_by} || [] }, { column => 'id' } ];
        local $collection->{order_by} = [
            map {
                my $alias = $_->{alias} || '';
                my $column = $_->{column};
                if ($column =~ /\W/) {
                    warn "Possible SQL injection in column '$column' in order_by\n";
                    next;
                }
                $alias .= '.' if $alias;

                ( ( !$alias or $alias eq 'main.' ) and $column eq 'id' )
                    ? $_
                    : { %{$_}, column => undef, function => "min($alias$column)" }
                } @{ $collection->{order_by} }
        ];
        my $group = $collection->_group_clause;
        my $order = $collection->_order_clause;
        $$statementref
            = "SELECT "
            . $collection->query_columns
            . " FROM ( SELECT main.id FROM $$statementref $group $order ) distinctquery, $table main WHERE (main.id = distinctquery.id)";
    } else {
        $$statementref
            = "SELECT DISTINCT "
            . $collection->query_columns
            . " FROM $$statementref";
        $$statementref .= $collection->_group_clause;
        $$statementref .= $collection->_order_clause;
    }
}

sub canonical_true { 't' }

sub canonical_false { 'f' }

1;

__END__