DBIx::SearchBuilder - Encapsulate SQL queries and rows in simple perl objects

  use DBIx::SearchBuilder;

  package My::Things;
  use base qw/DBIx::SearchBuilder/;

  sub _Init {
      my $self = shift;
      $self->Table('Things');
      return $self->SUPER::_Init(@_);
  }

  sub NewItem {
      my $self = shift;
      # MyThing is a subclass of DBIx::SearchBuilder::Record
      return(MyThing->new);
  }

  package main;

  use DBIx::SearchBuilder::Handle;
  my $handle = DBIx::SearchBuilder::Handle->new();
  $handle->Connect( Driver => 'SQLite', Database => "my_test_db" );

  my $sb = My::Things->new( Handle => $handle );

  $sb->Limit( FIELD => "column_1", VALUE => "matchstring" );

  while ( my $record = $sb->Next ) {
      print $record->my_column_name();
  }

This module provides an object-oriented mechanism for retrieving and updating data in a DBI-accesible database.

In order to use this module, you should create a subclass of CWDBIx::SearchBuilder and a subclass of CWDBIx::SearchBuilder::Record for each table that you wish to access. (See the documentation of CWDBIx::SearchBuilder::Record for more information on subclassing it.)

Your CWDBIx::SearchBuilder subclass must override CWNewItem, and probably should override at least CW_Init also; at the very least, CW_Init should probably call CW_Handle and CW_Table to set the database handle (a CWDBIx::SearchBuilder::Handle object) and table name for the class. You can try to override just about every other method here, as long as you think you know what you are doing.

Each method has a lower case alias; '_' is used to separate words. For example, the method CWRedoSearch has the alias CWredo_search.

Creates a new SearchBuilder object and immediately calls CW_Init with the same parameters that were passed to CWnew. If you haven't overridden CW_Init in your subclass, this means that you should pass in a CWDBIx::SearchBuilder::Handle (or one of its subclasses) like this:

   my $sb = My::DBIx::SearchBuilder::Subclass->new( Handle => $handle );

However, if your subclass overrides _Init you do not need to take a Handle argument, as long as your subclass returns an appropriate handle object from the CW_Handle method. This is useful if you want all of your SearchBuilder objects to use a shared global handle and don't want to have to explicitly pass it in each time, for example.

This method is called by CWnew with whatever arguments were passed to CWnew. By default, it takes a CWDBIx::SearchBuilder::Handle object as a CWHandle argument, although this is not necessary if your subclass overrides CW_Handle.

This completely erases all the data in the SearchBuilder object. It's useful if a subclass is doing funky stuff to keep track of a search and wants to reset the SearchBuilder data without losing its own data; it's probably cleaner to accomplish that in a different way, though.

Returns copy of the current object with all search restrictions.

Returns list of the object's fields that should be copied.

If your subclass store references in the object that should be copied while clonning then you probably want override this method and add own values to the list.

Get or set this object's DBIx::SearchBuilder::Handle object.

This internal private method actually executes the search on the database; it is called automatically the first time that you actually need results (such as a call to CWNext).

Adds a record object to this collection.

This private internal method returns the number of Record objects saved as a result of the last query.

This internal private method actually executes a counting operation on the database; it is used by CWCount and CWCountAll.

This routine takes a reference to a scalar containing an SQL statement. It massages the statement to limit the returned rows to only CW$self->RowsPerPage rows, skipping CW$self->FirstRow rows. (That is, if rows are numbered starting from 0, row number CW$self->FirstRow will be the first row returned.) Note that it probably makes no sense to set these variables unless you are also enforcing an ordering on the rows (with CWOrderByCols, say).

This routine takes a reference to a scalar containing an SQL statement. It massages the statement to ensure a distinct result set is returned.

Build up all of the joins we need to perform this query.

Returns true if this SearchBuilder will be joining multiple tables together.

If we've limited down this search, return true. Otherwise, return false.

Builds a query string for a SELECT rows from Tables statement for this SearchBuilder object

Builds a SELECT statement to find the number of rows this SearchBuilder object would find.

Returns the next row from the set as an object of the type defined by sub NewItem. When the complete set has been iterated through, returns undef and resets the search such that the following call to Next will start over with the first item retrieved from the database.

Starts the recordset counter over from the first item. The next time you call Next, you'll get the first item returned by the database, as if you'd just started iterating through the result set.

Takes an integer, n. Sets the record counter to n. the next time you call Next, you'll get the nth item.

Returns the first item

Returns the last item

Return a refernece to an array containing all objects found by this search.

NewItem must be subclassed. It is used by DBIx::SearchBuilder to create record objects for each row returned from the database.

Takes no arguments. Tells DBIx::SearchBuilder that the next time it's asked for a record, it should requery the database

UnLimit clears all restrictions and causes this object to return all rows in the primary table.

Limit takes a hash of parameters with the following keys:

TABLE

Can be set to something different than this table if a join is wanted (that means we can't do recursive joins as for now).

ALIAS

Unless ALIAS is set, the join criterias will be taken from EXT_LINKFIELD and INT_LINKFIELD and added to the criterias. If ALIAS is set, new criterias about the foreign table will be added.

FIELD

Column to be checked against.

VALUE

Should always be set and will always be quoted.

OPERATOR

OPERATOR is the SQL operator to use for this phrase. Possible choices include: In the case of LIKE, the string is surrounded in % signs. Yes. this is a bug. STARTSWITH is like LIKE, except it only appends a % at the end of the string ENDSWITH is like LIKE, except it prepends a % to the beginning of the string MATCHES is equivalent to the database's LIKE that is, it's actually LIKE, but doesn't surround the string in % signs as LIKE does.

ENTRYAGGREGATOR

Can be AND or OR (or anything else valid to aggregate two clauses in SQL)

CASESENSITIVE

on some databases, such as postgres, setting CASESENSITIVE to 1 will make this search case sensitive

Returns the current object's proposed WHERE clause.

Deprecated.

Replaces the current object's WHERE clause with the string passed as its argument.

Deprecated

Orders the returned results by ALIAS.FIELD ORDER.

Takes a paramhash of ALIAS, FIELD and ORDER. ALIAS defaults to CWmain. FIELD has no default value. ORDER defaults to ASC(ending). DESC(ending) is also a valid value for OrderBy.

FIELD also accepts CWFUNCTION(FIELD) format.

OrderByCols takes an array of paramhashes of the form passed to OrderBy. The result set is ordered by the items in the array.

returns the ORDER BY clause for the search.

Alias for the GroupByCols method.

Each hash contains the keys ALIAS and FIELD. ALIAS defaults to 'main' if ignored.

Private function to return the GROUP BY clause for this query.

Takes the name of a table. Returns the string of a new Alias for that table, which can be used to Join tables or to Limit what gets found by a search.

Join instructs DBIx::SearchBuilder to join two tables.

The standard form takes a param hash with keys ALIAS1, FIELD1, ALIAS2 and FIELD2. ALIAS1 and ALIAS2 are column aliases obtained from CW$self->NewAlias or a CW$self->Limit. FIELD1 and FIELD2 are the fields in ALIAS1 and ALIAS2 that should be linked, respectively. For this type of join, this method has no return value.

Supplying the parameter TYPE => 'left' causes Join to preform a left join. in this case, it takes ALIAS1, FIELD1, TABLE2 and FIELD2. Because of the way that left joins work, this method needs a TABLE for the second field rather than merely an alias. For this type of join, it will return the alias generated by the join.

Instead of ALIAS1/FIELD1, it's possible to specify EXPRESSION, to join ALIAS2/TABLE2 on an arbitrary expression.

Limits the number of rows returned by the database. Optionally, takes an integer which restricts the # of rows returned in a result Returns the number of rows the database should display.

Get or set the first row of the result set the database should return. Takes an optional single integer argrument. Returns the currently set integer first row that the database should return.

Returns the current position in the record set.

Returns the number of records in the set.

Returns the total number of potential records in the set, ignoring any LimitClause.

Returns true if the current row is the last record in the set.

Specify that we want to load the column FIELD.

Other parameters are TABLE ALIAS AND FUNCTION.

Autrijus and Ruslan owe docs.

Specify that we want to load only the columns in LIST

Return a list of fields in TABLE, lowercased.

TODO: Why are they lowercased?

Returns true if TABLE has field FIELD. Return false otherwise

If called with an argument, sets this collection's table.

Always returns this collection's table.

In order to test most of the features of CWDBIx::SearchBuilder, you need to provide CWmake test with a test database. For each DBI driver that you would like to test, set the environment variables CWSB_TEST_FOO, CWSB_TEST_FOO_USER, and CWSB_TEST_FOO_PASS to a database name, database username, and database password, where FOO is the driver name in all uppercase. You can test as many drivers as you like. (The appropriate CWDBD:: module needs to be installed in order for the test to work.) Note that the CWSQLite driver will automatically be tested if CWDBD::Sqlite is installed, using a temporary file as the database. For example:

  SB_TEST_MYSQL=test SB_TEST_MYSQL_USER=root SB_TEST_MYSQL_PASS=foo \
    SB_TEST_PG=test SB_TEST_PG_USER=postgres  make test

Copyright (c) 2001-2005 Jesse Vincent, jesse@fsck.com.

All rights reserved.

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

DBIx::SearchBuilder::Handle, DBIx::SearchBuilder::Record.