/ 
DBSimple-0.050_00
/ DBSimple::Table

NAME

DBSimple::Table - the interface to tables in the DBSimple abstraction

SYNOPSIS

use DBSimple::Table;
my $table = new DBSimple::Table('my_table', [ qw(
	NAME        user_name       char:20NK
	BALANCE     account_balance real:N
	LASTPAID    last_payment    real
	ACCTYPE     account_type    char:2
)]);
my $indexfield = $table->field('user_name');
my $db = &dbdef;	# get an open DBSimple::DB object
$table->bind($db);
sub dbfind {
	map { bless { %$_ } } $table->select($indexfield, \@_);
}
.. etc

DESCRIPTION

The intention here is to provide a simple, portable access mechanism to database tables, such that the script writer does not need to know what type of database the table is stored in, nor on what machine it is hosted. THE MECHANISMS DESCRIBED HERE ARE LIKELY TO CHANGE so don't get too wedded to them unless you are happy to keep any necessary maintenance and extension to yourself.

You acquire a DBSimple::Table object by supplying the table name and definitions for the fields - see DBSimple::Field->scandef for a definition of the field declaration format.

If you have a collection of identical tables, supply the undefined value as the table name, then for each table clone the DBSimple::Table object supplying the tablename at this point. bind a database only to the named clone.

You won't be able to do anything with your DBSimple::Table object until you bind a database to it - this should be an object returned by DBSimple::DB->new. Even then, you'll still need to open the database before you can access the table.

That done, you can do (or if you can't, you'll be able to eventually) everything you want to on the database. You can refer to fields throughout either by the DBSimple::Field object or by its local name; all values you supply or get returned will be the actual value (string, number etc) cleansed of any munging required to fit it in the database; NULL values are translated to undef.

I've tried to make the return types useful, but they may not always be as expected.

See the FUTURE section below for some indication of how things will change.

CONSTRUCTOR

new ( tablename, fieldarrayref )

Creates and returns a new DBSimple::Table object. If the tablename is undefined, this is purely a table format specification: see clone for a way of using this for multiple identical tables. The fieldarrayref is a reference to a list with three elements per field: the fieldname, the localname, and the type - see DBSimple::Field->scandef() for a fuller description. You need to bind the DBSimple::Table object before you can use it.

METHODS

Doing things with the object

clone ( [ tablename ] )

Creates and returns a deep copy of the object. If a tablename is supplied, and the table name of the original object is undefined, the clone will acquire this as its name. To be honest, using this to get a named copy of an unnamed table format specification is about the only use for this method I can think of.

bind ( database )

database should be a database implementation class derived from DBSimple::DB. This tells the table how to do everything it needs to do. You'll still need to open the database before you can actually do it though.

unbind

Theoretically, this unbinds a previously bound database so that you can re-bind a different one. I've never used it, so don't be surprised if it doesn't work. I suspect it isn't terribly useful though.

name

Returns the name of the table.

fields

Returns a reference to an array of the DBSimple::Field objects defined for this table.

field ( localname )

Returns the DBSimple::Field object for the requested localname.

fieldindex ( localname | fieldobject )

Returns the index in the fields list for the requested field.

Doing things with the whole table

exists ( )

True if the table exists, else false.

recreate ( dataref )

Rebuilds an entire table: the table is dropped if it exists, then recreated to have all the fields it is defined with, then the dataref data is inserted into it. dataref is of the same format required for the insert method.

drop ( )

Drops the table. The table must exist.

create ( )

Creates the table with the fields it is defined to have. If order is important, the order of fields will be the same as the order they were specified for the table definition.

Doing things with rows of the table

insert ( [ fieldarrayref, ] dataref )

Inserts new rows into the database. dataref is a reference to an array of rows; each row is a reference either to an array of field values or to a hash of localnames (which may be blessed into some package). If the fieldarrayref is provided, it should be a list of field localnames or DBSimple::Field objects, else the full list of fields for the table is assumed. Each row in the dataref should supply one value for each field to be inserted, with undefined values being inserted as NULL values.

delete ( where ... )

Deletes rows from the table that match a condition. See where below for details of how to specify a condition.

update ( field, value, where ... )

Updates a field in the table to a new value, in each row that matches a condition. The field specified may be a localname or a DBSimple::Field object. The value must be a constant - you can't use this to set a value that depends on the row found. See where below for details of how to specify a condition.

select ( where ... )

Selects all fields from those rows that match a condition. Returns an array of results, each result a reference to a hash in which the keys are the local names of the DBSimple::Fields. If called in a scalar context, a reference to the array is returned. See where below for details of how to specify a condition.

selectf ( fieldarrayref, where ... )

Selects the requested fields from those rows that match a condition. Return values are as for select. You can specify the fields either by their localname, or by the full DBSimple::Field object.

selectmax ( field, where ... )

Selects all fields from the row for which the requested field has the greatest value (either numerically or in ASCII order), of those rows that also match the condition. The field can be specified either as a localname or as a DBSimple::Field object. Return values are as for select - you get back an array of one result, or a reference to that array.

like ( field, match )

Selects all fields from rows where the requested field matches the match string. The field is expected to be a text field, and can be specified either as a localname or as a DBSimple::Field object. This does standard SQL LIKE-matching, with return values as for select.

likef ( fieldarrayref, field, match )

Selects the requested fields from rows where the requested field matches the match string. Each field can be specified either as a localname or as a DBSimple::Field object. Return values are as for like.

clike ( field, match )

Selects all fields from rows where the requested field matches the match string in a case-insensitive match. The field is expected to be a text field, and can be specified either as a localname or as a DBSimple::Field object. Depending on the database implementation, this functionality may be missing, or implemented as a full select followed by post-processing to pick the matches. Return values are as for select.

clikef ( fieldarrayref, field, match )

As clike when you want to specify which fields are returned.

Specifying conditions ('where' clauses)

The conditions arguments currently severely restrict the range of queries you can do, which is one reason why there all those different methods above rather than a single select() method. Each pair consists of a field specifier and a match specifier. The field specifier can in all cases be either a DBSimple::Field object or a localname; the match specifier can be either a scalar (string, number or undef as appropriate) or a reference to an array of values.

If the match specifier is an arrayref, the field is permitted to match any of the supplied values; if a scalar, the field must contain exactly that value. Each field/value pair must be satisfied simultaneously. Thus a 'where' set of

( 'name' => 'hugo', 'email' => [ 'hv', 'root' ] )

could result in SQL like any of these:

.. where NAME = 'hugo' and EMAIL in ('hv', 'root')
.. where NAME = 'hugo' and (EMAIL = 'hv' or EMAIL = 'root')
.. where EMAIL = 'hv' or EMAIL = 'root' and NAME = 'hugo'

Note that the latter will work correctly only if the SQL parser works left to right (as Msql's does).

At the point this mechanism is redesigned (as it must be), it may be possible to support the current behaviour (since the new behaviour will probably take a single DBSimple::Where object or somesuch, whereas the current one always takes an even number of arguments), but don't count on it.

FUTURE

Coo, lots. Redesign the data access mechanism - there shouldn't be separate routines for each different type of selection, instead there should be some sort of Where object you can build up piecemeal, that can give access to the full power of the SQL-engine you are using. That ain't gonna be easy - just thinking about joins and nested selects is likely to make my head hurt.

The fieldname lookup is a broken concept too - you can speed things up some by using field to get the field objects, but the code here still has to check each one in case it needs upgrading from a localname. Of course, that also means it needs to know where the field specifiers are in the arguments, even inside data structures, which limits the flexibility of the routines (though the @cond[_half @cond] is a middling cool hack). Which is, of course, why we have the silly separate routines in the first place.

It's a major hassle having to copy all the arguments before doing the lookup, but beware that it's necessary: if an argument was supplied as a literal, trying to upgrade it in situ in @_ will die with an 'attempt to modify read-only value' error. (Yes, I tried. Twice.)

A hash of fieldnames to lookup DBSimple::Field objects would be some improvement, even though there shouldn't be enough fields in a table to make it that relevant.

I could go on ...

HISTORY

v0.03 Hugo 29/3/97

cleaned up some for external release
documented C<where>

v0.02 Hugo 25/2/97

remove 'dropif' method; add 'exists' method
amended documentation of 'insert' method

v0.01 written by Hugo before 15/11/96