2
package DBIx::SearchBuilder::Handle::SQLite;
3
use DBIx::SearchBuilder::Handle;
4
@ISA = qw(DBIx::SearchBuilder::Handle);
6
use vars qw($VERSION @ISA $DBIHandle $DEBUG);
11
DBIx::SearchBuilder::Handle::SQLite -- A SQLite specific Handle object
18
This module provides a subclass of DBIx::SearchBuilder::Handle that
19
compensates for some of the idiosyncrasies of SQLite.
29
Takes a table name as the first argument and assumes that the rest of the arguments
30
are an array of key-value pairs to be inserted.
32
If the insert succeeds, returns the id of the insert, otherwise, returns
33
a Class::ReturnValue object with the error reported.
40
my %args = ( id => undef, @_);
41
# We really don't want an empty id
43
my $sth = $self->SUPER::Insert($table, %args);
46
# If we have set an id, then we want to use that, otherwise, we want to lookup the last _new_ rowid
47
$self->{'id'}= $args{'id'} || $self->dbh->func('last_insert_rowid');
49
warn "$self no row id returned on row creation" unless ($self->{'id'});
50
return( $self->{'id'}); #Add Succeded. return the id
58
Returns undef, since SQLite's searches are not case sensitive by default
73
=head2 DistinctCount STATEMENTREF
75
takes an incomplete SQL SELECT statement and massages it to return a DISTINCT result count
82
my $statementref = shift;
84
# Wrapper select query in a subselect as Oracle doesn't allow
85
# DISTINCT against CLOB/BLOB column types.
86
$$statementref = "SELECT count(*) FROM (SELECT DISTINCT main.id FROM $$statementref )";
95
Adjusts syntax of join queries for SQLite.
100
# SELECT DISTINCT main.* FROM (Groups main LEFT JOIN Principals Principals_2 ON ( main.id = Principals_2.id)) , GroupMembers GroupMembers_1 WHERE ((GroupMembers_1.MemberId = '70')) AND ((Principals_2.Disabled = '0')) AND ((main.Domain = 'UserDefined')) AND ((main.id = GroupMembers_1.GroupId))
101
# ORDER BY main.Name ASC
103
# SELECT DISTINCT main.* FROM Groups main LEFT JOIN Principals Principals_2 ON ( main.id = Principals_2.id) , GroupMembers GroupMembers_1 WHERE ((GroupMembers_1.MemberId = '70')) AND ((Principals_2.Disabled = '0')) AND ((main.Domain = 'UserDefined')) AND ((main.id = GroupMembers_1.GroupId)) ORDER BY main.Name ASC
110
$seen_aliases{'main'} = 1;
112
# We don't want to get tripped up on a dependency on a simple alias.
113
foreach my $alias ( @{ $sb->{'aliases'}} ) {
114
if ( $alias =~ /^(.*?)\s+(.*?)$/ ) {
115
$seen_aliases{$2} = 1;
119
my $join_clause = $sb->{'table'} . " main ";
121
my @keys = ( keys %{ $sb->{'left_joins'} } );
124
while ( my $join = shift @keys ) {
125
if ( ! $sb->{'left_joins'}{$join}{'depends_on'} || $seen_aliases{ $sb->{'left_joins'}{$join}{'depends_on'} } ) {
126
#$join_clause = "(" . $join_clause;
128
$sb->{'left_joins'}{$join}{'alias_string'} . " ON (";
131
values %{ $sb->{'left_joins'}{$join}{'criteria'} } );
132
$join_clause .= ") ";
134
$seen_aliases{$join} = 1;
137
push ( @keys, $join );
138
die "Unsatisfied dependency chain in Joins @keys"
143
return ( join ( ", ", ( $join_clause, @{ $sb->{'aliases'} } ) ) );
153
Jesse Vincent, jesse@fsck.com
157
perl(1), DBIx::SearchBuilder