DBIx::SearchBuilder::Handle::SQLite - A SQLite specific Handle object

DBIx-SearchBuilder documentation Contained in the DBIx-SearchBuilder distribution.

Index

Code Index:

NAME

Top

  DBIx::SearchBuilder::Handle::SQLite -- A SQLite specific Handle object

SYNOPSIS

Top

DESCRIPTION

Top

This module provides a subclass of DBIx::SearchBuilder::Handle that compensates for some of the idiosyncrasies of SQLite.

METHODS

Top

DatabaseVersion

Returns the version of the SQLite library which is used, e.g., "2.8.0". SQLite can only return short variant.

Insert

Takes a table name as the first argument and assumes that the rest of the arguments are an array of key-value pairs to be inserted.

If the insert succeeds, returns the id of the insert, otherwise, returns a Class::ReturnValue object with the error reported.

CaseSensitive

Returns undef, since SQLite's searches are not case sensitive by default

DistinctCount STATEMENTREF

takes an incomplete SQL SELECT statement and massages it to return a DISTINCT result count

AUTHOR

Top

Jesse Vincent, jesse@fsck.com

SEE ALSO

Top

perl(1), DBIx::SearchBuilder

DBIx-SearchBuilder documentation Contained in the DBIx-SearchBuilder distribution. 
package DBIx::SearchBuilder::Handle::SQLite;

use strict;
use warnings;

use base qw(DBIx::SearchBuilder::Handle);


sub DatabaseVersion {
    my $self = shift;
    return '' unless $self->dbh;
    return $self->dbh->{sqlite_version} || '';
}


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

    return $self->dbh->func('last_insert_rowid');

    # XXX: this is workaround nesty sqlite problem that
    # last_insert_rowid in transaction is inaccurrate with multiple
    # inserts.

    return $self->dbh->func('last_insert_rowid')
        unless $self->TransactionDepth;

    # XXX: is the name of the column always id ?

    my $ret = $self->FetchResult("select max(id) from $table");
    return $ret;
}

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

    my %args = ( id => undef, @_);
    # We really don't want an empty id

    my $sth = $self->SUPER::Insert($table, %args);
    return unless $sth;

    # If we have set an id, then we want to use that, otherwise, we want to lookup the last _new_ rowid
    $self->{'id'}= $args{'id'} || $self->_last_insert_rowid($table);

    warn "$self no row id returned on row creation" unless ($self->{'id'});
    return( $self->{'id'}); #Add Succeded. return the id
  }




sub CaseSensitive {
    my $self = shift;
    return(1);
}

sub BinarySafeBLOBs { 
    return undef;
}

sub DistinctQuery {
    my $self = shift;
    my $statementref = shift;
    my $sb = shift;

    return $self->SUPER::DistinctQuery( $statementref, $sb, @_ )
        if $sb->_OrderClause !~ /(?<!main)\./;

    local $sb->{'group_by'} = [{FIELD => 'id'}];
    local $sb->{'order_by'} = [
        map {
            ($_->{'ALIAS'}||'') ne "main"
            ? { %{$_}, FIELD => ((($_->{'ORDER'}||'') =~ /^des/i)?'MAX':'MIN') ."(".$_->{FIELD}.")" }
            : $_
        }
        @{$sb->{'order_by'}}
    ];
    $$statementref = "SELECT main.* FROM $$statementref";
    $$statementref .= $sb->_GroupClause;
    $$statementref .= $sb->_OrderClause;
}


sub DistinctCount {
    my $self = shift;
    my $statementref = shift;

    $$statementref = "SELECT count(*) FROM (SELECT DISTINCT main.id FROM $$statementref )";
}

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

    my $cache = \%DBIx::SearchBuilder::Handle::FIELDS_IN_TABLE;
    unless ( $cache->{lc $table} ) {
        my $info = $self->dbh->selectall_arrayref("PRAGMA table_info('$table')")
            or return ();

        foreach my $e ( @$info ) {
            push @{ $cache->{ lc $table } ||= [] }, lc $e->[1];
        }
    }

    return @{ $cache->{ lc $table } || [] };
}


1;

__END__