NAME
    SQLite::Abstract - Object oriented wrapper for SQLite2

SYNOPSIS
     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;
 
DESCRIPTION
    SQLite::Abstract is abstract level above DBD::SQLite. This package aims
    at intuitional SQLite database manipulation. It pretends to be the
    easiest sql class.

METHODS
    "new"
        The constructor takes database name which must be existing file. The
        $dbh attributes can be set through 'attrs' structure with the
        extended version of the constructor:

          $sql = SQLite::Abstract->new( 
             {
                DB => $database,
                DSN => 'dbi:SQLite2:dbname',
                attrs => {
                    AutoCommit => 0,
                    PrintError => 1,
                    RaiseError => 1,
             }
          );

        Use either the short version (database name as scalar argument) or
        anonymous hash with DB and DSN which are mandatory keys:

         $sql = SQLite::Abstract->new($database);
         $sql = SQLite::Abstract->new(
            {
               DB => $database,
                   DSN => 'dbi:SQLite2:dbname',
            }
         );
 
        Returns object if the database connection (SQLite2 DSN by default)
        is set successfully.

  SQL Table Methods
    "table"
        Accessor and mutator for the default table. This is the table which
        all methods use by default.

         $sql->table(); # returns the default table name
         $sql->table($tablename); # sets and returns the default table name
         $sq->table = $tablename;

    "create_table"
        Creates table.

         $sql->create_table($tablename, <<QUOTE);

          id INTEGER PRIMARY KEY,
          ...
          ...
 
         QUOTE

        which is equivalent to:

         $sql->do(<<QUOTE);

          CREATE TABLE tablename (
              id INTEGER PRIMARY KEY,
                  ...
                  ...
          )

         QUOTE

        Returns true on success. Returns undef on failure or raises fatal
        error exception according to $dbh "RaiseError" attribute.

    "drop_table";
        Deletes table. Like all methods works on the dafault table unless
        explicitly given table name.

         $sql->drop_table(); # drops the default table
         $sql->drop_table($tablename); 

        Returns true on success. Returns undef on failure or raises fatal
        error exception according to $dbh "RaiseError" attribute.

  SQL Query Methods
    "insert"
        Inserts data. Takes array references, the columns and the data to be
        inserted into these columns. The data array (which must be array of
        array references) can be given alone in which case each element is
        expected to have refer to the same number as the columns in the
        default sql table. Returns the number of affected rows. Returns
        false unless inserted rows. Returns undef on failure or raises fatal
        error exception according to $dbh "RaiseError" attribute.

         # talbe with two columns:
         @data = (['col_r11', 'col_r12'], ['col_r21', 'col_r22'])
 
         $sql->insert(\@cols, \@data);
         $sql->insert(\@data);

    "update"
        Updates records. Takes sql query. Returns the number of affected
        rows. Returns undef on failure or raises fatal error exception
        according to $dbh "RaiseError" attribute.

         $sql->update(q/name = 'system' WHERE .../);
         $sql->update(q/user = '...'/);

    "delete"
        Deletes records. Takes sql query. Returns the number of affected
        rows. Returns undef on failure or raises fatal error exception
        according to $dbh "RaiseError" attribute.

         $sql->delete(q/where id <= 100000/);

    "delete_all"
        Implements delete method on all records.

          $sql->delete_all();
          $sql->delete(q/where 1 = 1/);

        Returns the number of affected rows. Returns undef on failure or
        raises fatal error exception according to $dbh "RaiseError"
        attribute.

    "select"
        Implements select query. Returns all results (slurping mode) or one
        row at a time depending on the context. In list context $dbh
        "selectall_arrayref" is called which returns array reference with
        references to each fetched row. In scalar content $dbh
        "fetchrow_array" is called which returns the next row. Note that
        each query has its own statement handle. Table columns' names put
        after the method can generate select methods with the proper sql
        syntax.

         @AoA_result = $sql->select('all limit 1,10'); 
         $AR_result  = $sql->select('all limit 1,10');

         @users = $sql->select_users('limit 1,10'); # all in not AoAref
         $users = $sql->select_users('limit 1,10'); # single row
         @AoA_users_fname_lname = $sql->select_users_fname_lname; # all in AoAref
         @users = $sql->select('SELECT users FROM tablename LIMIT 1,10'); # also possible
 
         # list context usage 
         for( $sql->select_username ){
                print "username: $_ \n";
         }

         # scalar context usage
         while( $username = $sql->select_username ){
                print "username: $username\n";
         }

         while( $user = $sql->select_fname_lname ){
                print "fname: $user->[0] lname: $user->[1];
         }
 
        Returns array containing array references to each row in list
        context. In scalar context returns result as string if one column
        selected, otherwise reference to the row fetched. Returns undef at
        the end while in scalat context. Returns undef on failure or raises
        fatal error exception according to $dbh "RaiseError" attribute.

    "count"
        Implements rows counting. Returns undef on failure or raises fatal
        error exception according to $dbh "RaiseError" attribute.

         $sql->count;
         $sql->count(q/SELECT count(*) FROM tablename/);

    "sum"
        Implements "count" method.

    "do"
        Calls $dbh "do" method. Useful for *non*-"SELECT" arbitrary
        statements which will not be executed repeatedly. Returns undef on
        failure or raises fatal error exception according to $dbh
        "RaiseError" attribute.

  Error handling method
    "err"
        Returns the last $dbh error message ("errstr").

         $sql->do(q/some sql query/);
         $sql->err and die $sql->err;
 
SEE ALSO
    DBI DBD::SQLite2

BUGS
    Please report any bugs or feature requests to vidul@cpan.org.

AUTHOR
    Vidul Nikolaev Petrov, vidul@cpan.org

COPYRIGHT AND LICENSE
    Copyright 2006 by Vidul Nikolaev Petrov

    This library is free software; you can redistribute it and/or modify it
    under the same terms as Perl itself.