SQLite::Abstract - Object oriented wrapper for SQLite2

 use SQLite::Abstract;

 


 my $db = SQLite::Abstract->new("database name");
 my $db = SQLite::Abstract->new(
    {
        DB => "database name",
        DSN => "dbi:SQLite2:dbname", 
        TABLE => "tablename",
    }
 );

 $db->create_table($tablename,<<SQ);
      id INTEGER PRIMARY KEY,
      name VARCHAR(255) NOT NULL,
      password VARCHAR(255) NOT NULL,	
 SQ

 


 $db->table("tablename");

 $db->insert(\@fields, \@data);
 $db->insert(['name', 'password'], [['user1', 'password1'], ['user2', 'password2']]);

 $db->update(q/password = 'w0rdpass' where name = 'guest'/);

 $db->select(q/select name, password from tablename limit 0,2/);
 $db->select(q/* limit 0,2/);
 $db->select(q/ALL limit 0,2/);
 $db->select_name_password(q/limit 0,2/);
 $db->select_name(q/limit 0,2/);

 while ( $name = $db->select_name ) {
    print "$name\n";
 }

 # slurping mode 
 for ( @names = $db->select_name ) {
    print "name: $_\n";
 }

 while ( $row = $db->select ) {
    print "name: $row->[1] password: $row->[2]\n";
 }

 $db->count;
 $db->count(q/where name like 'user%'/);
 $db->sum(q/where name like '%name'/);

 $db->delete(q/where password like 'password'/);
 $db->delete_all();

 $db->drop_table;




 

package SQLite::Abstract;

use strict;
use warnings;
use Carp;
use DBI;

our $VERSION = '0.14';

my $td = {
    week =>
      { 0, 'Sun', 1, 'Mon', 2, 'Tue', 3, 'Wed', 4, 'Thu', 5, 'Fri', 6, 'Sat' },
    month => {
        '01' => 'Jan',
        '02' => 'Feb',
        '03' => 'Mar',
        '04' => 'Apr',
        '05' => 'May',
        '06' => 'Jun',
        '07' => 'Jul',
        '08' => 'Aug',
        '09' => 'Sep',
        '10' => 'Oct',
        '11' => 'Nov',
        '12' => 'Dec',
    }
};

sub new {
    my $class = shift;
    my $data  = shift;
    my $self;
    my $attrs = {
        AutoCommit => 0,
        PrintError => 0,
        RaiseError => 1,
    };

    if ( not ref $data eq 'HASH' ) {
        my $dbtype    = shift;
        my $tablename = shift;
        $self->{'dbname'}    = $data;
        $self->{'dbtype'}    = $dbtype ? $dbtype : "dbi:SQLite2:dbname";
        $self->{'tablename'} = $tablename;
        $self->{'attrs'}     = $attrs;
    }
    else {
        $self = {
            dbname    => $data->{'DB'},
            dbtype    => $data->{'DSN'},
            tablename => $data->{'TABLE'},
            attrs     => ref $data->{'attrs'} eq 'HASH'
            ? $data->{'attrs'}
            : $attrs
        };
    }

    $class eq __PACKAGE__
      or croak _err_msg("constructor not called as class method");

    -e $self->{'dbname'}
      or croak _err_msg("no database defined");
    -f $self->{'dbname'}
      or croak _err_msg("no such database $self->{dbname}");

    $self->{'dbh'} = DBI->connect( qq/$self->{dbtype}=$self->{dbname}/,
        q//, q//, $self->{'attrs'} );

    $self->{'BEGIN'}    = sub { $self->{'dbh'}->begin_work };
    $self->{'COMMIT'}   = sub { $self->{'dbh'}->commit };
    $self->{'ROLLBACK'} = sub { $self->{'dbh'}->rollback };

    return bless $self, $class;

}

sub table : lvalue {
    $_[1]
      ? $_[0]->{q{tablename}} =
        $_[1]
      : $_[0]->{q{tablename}};
}

sub tables {
    my $self = shift;
    no strict 'refs';

    map { $_->[0] } $self->select(
        q/
                        SELECT name FROM 
                                (SELECT * FROM sqlite_master UNION ALL
                                SELECT * FROM sqlite_temp_master)
                        WHERE type='table'
                        ORDER BY name;
        /
    );
}

sub localtime {
    my $self = shift;
    my $ps   = shift;

    if ($ps) {
        my @td = split q/ /, $self->select(
            q/
        	        	select strftime("%w %m %d %H:%M:%S %Y",'now','localtime');
    	    	/
        )->[0];
        $td[0] = $td->{q/week/}{ $td[0] };
        $td[1] = $td->{q/month/}{ $td[1] };

        return join q/ /, @td;
    }

    return join q/ /, map { $_->[0] } $self->select(
        q/
				select datetime('now','localtime');
		/
    );
}

sub time {
    my $self = shift;

    return join q/ /, map { $_->[0] } $self->select(
        q/
        	        	select strftime("%s",'now');
    	    	/
    );

}

sub time_ahead {
    my $self  = shift;
    my $ahead = "@_";

    return join q/ /, map { $_->[0] } $self->select(
        qq/
       		       		select datetime('now','localtime',$ahead);
		/
    );

}

sub create_table {
    my $self      = shift;
    my $tablename = shift || $self->{tablename};

    $self->do(
        qq/
				CREATE TABLE $tablename ( @_ );
		/
    );
}

sub alter {
    my $self  = shift;
    my $query = shift;

    $self->_check_table;
    $self->do(
        qq/
				ALTER TABLE $self->{tablename} $query;
		/
    );
}

sub drop_table {
    my $self      = shift;
    my $tablename = shift || $self->{tablename};

    $self->do(
        qq/
				DROP TABLE $tablename; 
				VACUUM;
		/
    );
}

sub insert {
    my $self    = shift;
    my $columns = $_[0];
    my $data    = $_[1] || $_[0];
    my $sth     = q{};
    my $counter = 0;

    $self->_check_table;

    #~ $self->{q{BEGIN}}->();

    if ( @_ == 2 ) {
        my $prep_columns = join( ',', @$columns );
        my $prep_data = join( ',', split '', ( '?' x @$columns ) );
        $sth = $self->{q{dbh}}->prepare(
            qq/
						INSERT INTO $self->{tablename} ($prep_columns) 
						VALUES ($prep_data);
				/
        );
    }
    else {
        my $prep_data = join( ',', ( split '', ( '?' x @{ $data->[0] } ) ) );
        $sth = $self->{q{dbh}}->prepare(
            qq/
						INSERT INTO $self->{tablename} VALUES ($prep_data);
				/
        );
    }

    for (@$data) {
        $sth->execute(@$_) and $counter++;
    }

    $self->_END_;

    return $counter;
}

sub replace {
    my $self  = shift;
    my $query = shift;

    $self->_check_table;
    $self->do(
        qq/
				REPLACE INTO $self->{tablename} $query;
		/
    );
}

sub create_view {
    my $self      = shift;
    my $view_name = shift;
    my $select    = shift;

    $self->_check_table;
    $self->do(
        qq/
				CREATE VIEW $view_name AS $select;
		/
    );
}

sub drop_view {
    my $self      = shift;
    my $view_name = shift;

    $self->_check_table;
    $self->do(
        qq/
				DROP VIEW $view_name;
		/
    );
}

sub delete {
    my $self  = shift;
    my $query = shift;

    $self->_check_table;
    $self->do(
        qq/
				DELETE FROM $self->{tablename} $query;
		/
    );
}

sub delete_all {
    shift->delete(q/WHERE 1=1/);
}

sub update {
    my $self  = shift;
    my $query = shift;

    $self->_check_table;
    $self->do(
        qq/
				UPDATE $self->{tablename} SET $query;
		/
    );
}

sub select {
    my $self  = shift;
    my $query = shift;
    my $type  = shift;
    my $result;

    $self->_check_table;

    $query ||= qq/SELECT * FROM $self->{q{tablename}}/;
    $query =~ s/^\s*(\w*\s*ALL\s*)/ * /i;

    if ( not $query =~ /^\s*SELECT\s+/i ) {
        if ( $query =~ /^\s*\*/ ) {
            $query =~ s/^\s*\*(.*)/SELECT * FROM 
								$self->{q{tablename}} $1/;
        }
        else {
            $query =~ s/^\s*(\w+(\s*,\s*\w+)*)/SELECT $1 FROM 
								$self->{q{tablename}} /;
        }
    }

    local $self->{q{dbh}}->{q{RaiseError}} = 1;

    if (wantarray) {
        if ( my @data = @{ $self->{q{dbh}}->selectall_arrayref($query) } ) {
            $self->_END_;
            @data = map { $_->[0] } @data
              if ref $type eq 'SCALAR';
            return @data;
        }
    }
    else {
        local $_ = join( '_', $query );
        if ( not $self->{$_} ) {
            $self->{$_} = $self->{q{dbh}}->prepare($query);
            $self->{$_}->execute;
            $result = [ $self->{$_}->fetchrow_array ];
        }
        else {
            $result = [ $self->{$_}->fetchrow_array ];
        }

        $self->{q/select/}->{q/last/} = $result;

        return @$result
          ? ref $type eq 'SCALAR'
             ? $result->[0]
             : $result
          : undef $self->{$_};
    }
}

sub last {
    shift->{q/select/}->{q/last/};
}

sub count {
    my $self  = shift;
    my $query = shift;

    $self->_check_table;

    my $count = $query
      ? $self->{q{dbh}}->selectall_arrayref(
        qq/
						SELECT count(*) FROM $self->{tablename} $query
				/
      )
      : $self->{q{dbh}}->selectall_arrayref(
        qq/
						SELECT count(*) FROM $self->{tablename}
				/
      );

    $self->_END_;

    return $count->[0][0];
}

sub sum {
    shift->count(@_);
}

sub _END_ {
    my $self = shift;

    $self->{q{COMMIT}}->();
    $self->{dbh}->errstr
      and eval { $self->{q{ROLLBACK}}->() };
}

sub do {
    shift->_do_(@_);
}

sub _do_ {
    my $self  = shift;
    my $query = "@_";
    my $affected;

    eval {
        $affected = $self->{q{dbh}}->do($query);
        $self->{q{COMMIT}}->();
    };

    if ( $self->{q{dbh}}->errstr ) {
        $self->err = $@;
        eval { $self->{q{ROLLBACK}}->() };
        if ( $self->{q{dbh}}->{q{RaiseError}} ) {
            croak $self->err;
        }
        else {
            carp $self->err;
            return undef;
        }
    }
    else {
        return $affected == 0
          ? "0E0"
          : $affected;
    }
}

sub _check_table {
    my $self = shift;
    $self->{q{tablename}}
      or croak _err_msg("missing table name");
}

sub _err_msg {
    __PACKAGE__ . q/:/ . (caller)[2] . q/:/ . __LINE__ . q/: / . "@_";
}

sub err : lvalue {
    $_[1]
      ? $_[0]->{q{err}} = $_[1]
      : $_[0]->{q{err}};
}

sub AUTOLOAD {
    my $self  = shift;
    my $query = shift;
    my $tmp   = shift;

    $tmp and $query = $tmp;
    $query or $query = q{};

    our $AUTOLOAD;
    ( my $method = $AUTOLOAD ) =~ s/.*:://s;

    if ( $method =~ /^select_(\w+)/i ) {
        my $fields = join ',', split /_/, $1;
        if ($fields) {
            $query =~ s/^\s*SELECT.+?FROM\s+\S+//;
            return $fields !~ /\,/
              ? $self->select( "$fields $query", \$0 )
              : $self->select("$fields $query");
        }
    }

    croak _err_msg("method $method does not exist");
}

sub DESTROY {
    my $self = shift;
    $self->{q{dbh}} = undef;
    $self = undef;
}

1

__END__