This is Info file pm.info, produced by Makeinfo version 1.68 from the input file bigpm.texi.  File: pm.info, Node: DBIx/SearchBuilder/Handle/Pg, Next: DBIx/SearchBuilder/Handle/mysql, Prev: DBIx/SearchBuilder/Handle/Oracle, Up: Module List Insert ------ Takes a table name as the first argument and assumes that the rest of the arguments are an array of key-value pairs to be inserted.  File: pm.info, Node: DBIx/SearchBuilder/Handle/mysql, Next: DBIx/SearchBuilder/Record, Prev: DBIx/SearchBuilder/Handle/Pg, Up: Module List Insert ------ Takes a table name as the first argument and assumes that the rest of the arguments are an array of key-value pairs to be inserted. a mysql specific Handle object ****************************** NAME ==== DBIx::SearchBuilder::Handle::mysql -- a mysql specific Handle object SYNOPSIS ======== =head1 DESCRIPTION AUTHOR ====== Jesse Vincent, jesse@fsck.com SEE ALSO ======== perl(1), DBIx::SearchBuilder  File: pm.info, Node: DBIx/SearchBuilder/Record, Next: DBIx/SearchProfiles, Prev: DBIx/SearchBuilder/Handle/mysql, Up: Module List Perl extension for subclassing, so you can deal with a Record ************************************************************* NAME ==== DBIx::SearchBuilder::Record - Perl extension for subclassing, so you can deal with a Record SYNOPSIS ======== module MyRecord; use DBIx::SearchBuilder::Record; @ISA = (DBIx::SearchBuilder::Record); sub _Init { my $self = shift; my $DBIxHandle = shift; # A DBIx::SearchBuilder::Handle::foo object for your database $self->_Handle($DBIxHandle); $self->Table("Users"); return($self->SUPER::_Init(@_)); } #Preferred and most efficient way to specify fields attributes in a derived #class, used by the autoloader to construct Attrib and SetAttrib methods. # read: calling $Object->Foo will return the value of this record's Foo column # write: calling $Object->SetFoo with a single value will set Foo's value in # both the loaded object and the database sub _ClassAccessible { { Tofu => { 'read'=>1, 'write'=>1 }, Maz => { 'auto'=>1, }, Roo => { 'read'=>1, 'auto'=>1, 'public'=>1, }, }; } # specifying an _Accessible subroutine in a derived class is depriciated. # only '/' and ',' delimiters work, not full regexes sub _Accessible { my $self = shift; my %Cols = ( id => 'read', # id is an immutable primary key Username => 'read/write', #read/write. Password => 'write', # password. write only. see sub IsPassword Created => 'read' # A created date. read-only ); return $self->SUPER::_Accessible(@_, %Cols); } # A subroutine to check a user's password without ever returning the current password #For security purposes, we didn't expose the Password method above sub IsPassword { my $self = shift; my $try = shift; # note two __s in __Value. Subclasses may muck with _Value, but they should # never touch __Value if ($try eq $self->__Value('Password')) { return (1); } else { return (undef); } } # Override DBIx::SearchBuilder::Create to do some checking on create sub Create { my $self = shift; my %fields = ( UserId => undef, Password => 'default', #Set a default password @_); #Make sure a userid is specified unless ($fields{'UserId'}) { die "No userid specified."; } #Get DBIx::SearchBuilder::Record->Create to do the real work return ($self->SUPER::Create( UserId => $fields{'UserId'}, Password => $fields{'Password'}, Created => time )); } DESCRIPTION =========== DBIx::SearchBuilder::Record is designed to work with DBIx::SearchBuilder. What is it trying to do. ------------------------ DBIx::SB::Record abstracts the agony of writing the common and generally simple SQL statements needed to serialize and De-serialize an object to the database. In a traditional system, you would define various methods on your object 'create', 'find', 'modify', and 'delete' being the most common. In each method you would have a SQL statement like: select * from table where value='blah'; If you wanted to control what data a user could modify, you would have to do some special magic to make accessors do the right thing. Etc. The problem with this approach is that in a majority of the cases, the SQL is incredibly simple and the code from one method/object to the next was basically the same. Enter, DBIx::SearchBuilder::Record. With::Record, you can in the simple case, remove all of that code and replace it by defining two methods and inheriting some code. Its pretty simple, and incredibly powerful. For more complex cases, you can, gasp, do more complicated things by overriding certain methods. Lets stick with the simple case for now. The two methods in question are '_Init' and '_ClassAccessible', all they really do are define some values and send you on your way. As you might have guessed the '_' suggests that these are private methods, they are. They will get called by your record objects constructor. o. '_Init' Defines what table we are talking about, and set a variable to store the database handle. o. '_ClassAccessible Defines what operations may be performed on various data selected from the database. For example you can define fields to be mutable, or immutable, there are a few other options but I don't understand what they do at this time. And really, thats it. So lets have some sample code, but first the example code makes the following assumptions: The database is 'postgres', The host is 'reason', The login name is 'mhat', The database is called 'example', The table is called 'simple', The table looks like so: id integer not NULL, primary_key(id), foo varchar(10), bar varchar(10) [file:///example/Simple.pm] 000: package Simple; 001: use DBIx::SearchBuilder::Record; 002: @ISA = (DBIx::SearchBuilder::Record); This should be pretty obvious, name the package, import ::Record and then define ourself as a subclass of ::Record. 003: 004: sub _Init { 005: my $this = shift; 006: my $handle = shift; 007: 008: $this->_Handle($handle); 009: $this->Table("Simple"); 010: 011: return ($this); 012: } Here we set our handle and table name, while its not obvious so far, we'll see later that $handle (line: 006) gets passed via ::Record::new when a new instance is created. Thats actually an important concept, the DB handle is not bound to a single object but rather, its shared across objects. 013: 014: sub _ClassAccessible { 015: { 016: Foo => { 'read' => 1 }, 017: Bar => { 'read' => 1, 'write' => 1 }, 018: Id => { 'read' => 1 } 019: }; 020: } What's happening might be obvious, but just in case this method is going to return a reference to a hash. That hash is where our columns are defined, as well as what type of operations are acceptable. 021: 022: 1; Its perl, duh. Now, on to the code that will actually *do* something with this object. [file://examples/ex.pl] 000: use DBIx::SearchBuilder::Handle; 001: use Simple; Use two packages, the first is where I get the DB handle from, the latter is the object I just created. 002: 003: my $handle = DBIx::SearchBuilder::Handle->new(); 004: $handle->Connect( 'Driver' => 'Pg', 005: 'Database' => 'test', 006: 'Host' => 'reason', 007: 'User' => 'mhat', 008: 'Password' => "); Creates a new DBIx::SB::Handle, and then connects to the database using that handle. Pretty straight forward, the password " is what I use when there is no password. I could probably leave it blank, but I find it to be more clear to define it. 009: 010: my $s = new Simple($handle); 011: 012: $s->LoadById(1); LoadById is one of four 'LoadBy' methods, as the name suggests it searches for an row in the database that has id='0'. ::SearchBuilder has, what I think is a bug, in that it current requires there to be an id field. More reasonably it also assumes that the id field is unique. LoadById($id) will do undefined things if there is >1 row with the same id. In addition to LoadById, we also have: o. LoadByCol Takes two arguments, a column name and a value. Again, it will do undefined things if you use non-unique things. o. LoadByCols Takes a hash of columns=>values and returns the *first* to match. First is probably lossy across databases vendors. o. LoadFromHash Populates this record with data from a DBIx::SearchBuilder. I'm currently assuming that DBIx::SearchBuilder is what we use in cases where we expect > 1 record. More on this later. Now that we have a populated object, we should do something with it! ::Record automagically generates accessos and mutators for us, so all we need to do is call the methods. Accessors are named (), and Mutators are named Set($). On to the example, just appending this to the code from the last example. 013: 014: print "ID : ", $s->Id(), "\n"; 015: print "Foo : ", $s->Foo(), "\n"; 016: print "Bar : ", $s->Bar(), "\n"; Thats all you have to to get the data, now to change the data! 017: 018: $s->SetBar('NewBar'); Pretty simple! Thats really all there is to it. Set($) returns a boolean and a string describing the problem. Lets look at an example of what will happen if we try to set a 'Id' which we previously defined as read only. 019: my ($res, $str) = $s->SetId('2'); 020: if (! $res) { 021: ## Print the error! 022: print "$str\n"; 023: } The output will be: >> Immutable field Currently Set updates the data in the database as soon as you call it. In the future I hope to extend ::Record to better support transactional operations, such that updates will only happen when "you" say so. Finally, adding a removing records from the database. ::Record provides a Create method which simply takes a hash of key=>value pairs. The keys exactly map to database fields. 023: ## Get a new record object. 024: $s1 = new Simple($handle); 025: $s1->Create('Id' => 4, 026: 'Foo' => 'Foooooo', 027: 'Bar' => 'Barrrrr'); Poof! A new row in the database has been created! Now lets delete the object! 028: 029: $s1 = undef; 030: $s1 = new Simple($handle); 031: $s1->LoadById(4); 032: $s1->Delete(); And its gone. For simple use, thats more or less all there is to it. The next part of this HowTo will discuss using container classes, overloading, and what ever else I think of. METHODS ======= id -- Returns this row's primary key. PrimaryKeys ----------- Matt Knopp owes docs for this function. _AccessibleLoad COLUMN => OPERATIONS, ... ----------------------------------------- _ClassAccessible HASHREF ------------------------ Preferred and most efficient way to specify fields attributes in a derived class. sub _ClassAccessible { { Tofu => { 'read'=>1, 'write'=>1 }, Maz => { 'auto'=>1, }, Roo => { 'read'=>1, 'auto'=>1, 'public'=>1, }, }; } __Value ------- Takes a field name and returns that field's value. Subclasses should never overrid __Value. _Value ------ _Value takes a single column name and returns that column's value for this row. Subclasses can override _Value to insert custom access control. _Set ---- _Set takes a single column name and a single unquoted value. It updates both the in-memory value of this column and the in-database copy. Subclasses can override _Set to insert custom access control. Load ---- Takes a single argument, $id. Calls LoadByRow to retrieve the row whose primary key is $id LoadByCol --------- Takes two arguments, a column and a value. The column can be any table column which contains unique values. Behavior when using a non-unique value is undefined LoadByCols ---------- Takes a hash of columns and values. Loads the first record that matches all keys. LoadById -------- Loads a record by its primary key. TODO: BUG: Column name is currently hard coded to 'id' LoadFromHash ------------ Takes a hashref, such as created by DBIx::SearchBuilder and populates this record's loaded values hash. Create ------ Takes an array of key-value pairs and drops any keys that aren't known as columns for this recordtype Table ----- Returns or sets the name of the current Table _Handle ------- Returns or sets the current DBIx::SearchBuilder::Handle object Private: _DownCaseValuesHash ---------------------------- Takes no parameters and returns no arguments. This private routine iterates through $self->{'values'} and makes sure that all keys are lowercase. AUTHOR ====== Jesse Vincent, Enhancements by Ivan Kohler, Docs by Matt Knopp SEE ALSO ======== perl(1).  File: pm.info, Node: DBIx/SearchProfiles, Next: DBIx/Table, Prev: DBIx/SearchBuilder/Record, Up: Module List Access to SQL database via template query. ****************************************** NAME ==== DBIx::SearchProfiles - Access to SQL database via template query. SYNOPSIS ======== use DBIx::SearchProfiles; my $DB = new DBIx::SearchProfiles( $dbh, $profiles); my $record = $DB->record_get( "customer", 1024 ); my $records = $DB->template_search( "cust_low_balance", { low_balance => 50 } ); $DB->record_insert( "customer", $customer_data ); DESCRIPTION =========== DBIx::SearchProfiles is a module which wraps around a DBI database handle and provides another way than raw SQL to access the database. Its aims is to take the SQL out of the code in well defined and documented search profiles which has easier to maintain than embedded SQL all over the application. Moreover, this decoupling of the application logic from the SQL programming makes it possible to review the SQL by a DBA which might not be a programmer. It may also makes the application's code more obvious and clearer which is a Good Thing (tm) ACCESS METHODS ============== The DBIx::SearchProfiles module offers three method of access to the underlying database : RAW SQL ACCESS This is the lowest level and is thin wrapper around the underlying DBI methods. The caller specifies the SQL statement and the params to use for the query. Ex: $DB->sql_insert( "INSERT INTO customer (?,?,?)", @params ); RECORD ACCESS This class of access generates automatically the SQL statement to use based on the fields present in the table and the fields passed as parameters. This type of access is very handy for insert or update where you don't want to specify all the fields. Ex: $DB->record_insert( "customer", $customer_data ); Where *customer* is the name of the profile definition to use and $customer_data is a reference to an hash which contains the customer's infos. TEMPLATE ACCESS This is the most interesting type of access. The problem with the previous type of access is that it is convenivent and efficient for simple query but when you want something more complex it fails miserably. (Say one where you want other operators than =, and where you are joining 6 tables together) In the template based access, you use a template query in which the parameters will be substituted. The query can be as complex as you want and the parameter subsitutions also. Ex: $DB->template_search( "troublesome_customers", $search_spec ); Each class of access provides 5 methods to access the data. (SQL has an extra one, but its the exception) : * The *_get methods ( `sql_get()', `record_get()' and `template_get()' ) will return only one record in the form of an hash reference. Each keys corresponds to one column of the table. (So two columns must not have the same name.) * The *_search methods (`sql_search()', `record_search()' and `template_search()') will return a reference to an array of hash. Each hash is a table row where the keys are the column's names. Also the `record_search()' and `template_search()' methods have support for limiting the number of rows returned and to results offset. (1-50,51-100,etc). * The *_insert methods are for inserting one record in a table. * The *_update methods are for updating records in a table. * The *_delete methods are for deleting records from the table. INITIALIZATION ============== To get a database search profiles handle, you use the new method. Ex: my $DB = new DBIx::SearchProfiles( $dsn, $profiles ); The $dsn parameter can either be an already connected DBI handle or a reference to an hash which contains three parameters DataSource, UserName and Password which will be used to open one. Note that on destruction, the connection will only be closed if the connection was established by the DBIx::SearchProfiles modules. The $profiles parameter can either be a reference to an hash which contains the search profiles, or the name of a file which will be evaluated and that must return a reference to an hash which will contains the search profiles. Note that whenever the search profiles' file changes on disk, the profiles are reloaded. DBI WRAPPER METHODS =================== commit ------ Simply call commit on the underlying DBI handle. rollback -------- Simply call rollback on the underlying DBI handle. PROFILE DEFINITIONS =================== A search profiles collection is a reference to an hash where each key points ta profile definition. A search profile definition is an hash which contains several elements which will be used to build query automatically. Here is an example profiles : { category => { query => q{ SELECT id,category FROM category WHERE category_id = ? }, params => [ "category_id" ], }, product_srch => { query => q{ SELECT DISTINCT code,code_manu,category_id,category, manufacturer_id,manufacturer, price,description FROM products p ,manufacturer m ,category c WHERE ( ? = -1 OR c.id = ? ) AND ( ? = -1 OR m.id = ? ) AND category_id = c.id AND manufacturer_id = m.id AND ( code = ? OR code_manu = ? OR category LIKE ? OR manufacturer LIKE ? OR description LIKE ? ) }, params => [ qw( category_id category_id manufacturer_id manufacturer_id search search search search search ) ], order => "category_id,manufacturer_id,code", defaults => { category_id => -1, manufacturer_id => -1 }, limit => 25, }, order_items => { fields => [qw( quantity subtotal ) ], keys => [ qw( order_no code ) ], table => "order_items", }, } In this example, you have a simple query profile (category), a complex template search (product_srch) and an example of a profile for record based access. Here is the meaning of the different fields : table (RECORD ACCESS ONLY) The name of the table on which we will operate. keys (RECORD ACCESS ONLY) A reference to an array which contains the name of the fields which are the primary key for the table. fields (RECORD ACCESS ONLY) A reference to an array which contains the name of the fields which are not primary keys in the table. defaults Reference to an hash of parameter defaults. This will be used to complete when no values are present. limit Used by `record_search' and `template_search' as the default number of records to return at a time for this query. max Used by `record_search' and `template_search' as the default maximum total number of records to return for a query. order Used by `record_search' and `template_search' as the default ordering for the query. query (TEMPLATE ACCESS ONLY) This is the query template. It should contains the SQL that will be executed with the standard DBI (?) placeholders embedded in it. params (TEMPLATE ACCESS ONLY) A reference to an array which contains the name of the params that will be substituted in the template. There should be one element for every placeholder in the query. SQL ACCESS METHODS ================== sql_do ( $statement, @params ); ------------------------------- Thin wrapper around DBI do method. The first argument is the SQL to be executed and the remaining arguments are passed as params to the query. sql_get ( $statement, @params ); -------------------------------- This method will execute the SELECT query passed in the first argument using the remaining parameters as placeholder substitutions. It returns an hash ref (or undef if the query didn't match any record) corresponding to the first row returned. sql_search ( $statement, @params ); ----------------------------------- This method will execute the SELECT query passed in the first argument using the remaining parameters as placeholder substitutions. It returns a reference to an array of hash. sql_insert ( $statement, @params ); ----------------------------------- This method will execute the INSERT query passed in the first argument using the remaining parameters as placeholder substitutions. Return value is undefined. sql_update ( $statement, @params ); ----------------------------------- This method will execute the UPDATE query passed in the first argument using the remaining parameters as placeholder substitutions. Return value is undefined. sql_delete ( $statement, @params ); ----------------------------------- This method will execute the DELETE query passed in the first argument using the remaining parameters as placeholder substitutions. Return value is undefined. RECORD BASED ACCESS =================== record_get ( $name, params ); ----------------------------- This method will return an hash reference to a record. The first argument is the name of the profile where the table information will be found. The params argument can either be : ARRAY OR ARRAY REF Each element of the array is mapped to an element of the keys field of the profile. It is an error if the number of elements is different than the number of keys defined in the table. HASH REF The key will be built by using the name of the keys as specified in the keys field of the profile, or by using the defaults hash if present. It is an error if some portion of the key is missing. record_search ( $name, \%params ); ---------------------------------- This method will build a search on the table specified in the profile $name. $params is a reference to an hash where each keys that is present in the fields or keys of the profile will be used as a constraint in the query. The test is for equality, if you want something more complex, use `template_search'. There are a few magic parameters : dbix_sp_order Will override the order clause of the query. If not present the order field of the profile will be used. dbix_sp_limit Limit the number of records returned by the query. If not present the limit field of the profile will be used. dbix_sp_max Set the maximum number of records that the query may fetch, this override the max field of the profile but cannot be set higher. dbix_sp_start If there is a limit set for the query, this parameter will start returning records from that offset in the result. Offset is 0 indexed. The params argument is modified on return. Here is a list of the modified elements : dbix_sp_found The number of record returned. dbix_sp_total The total number of record matching the query. Like all *_search methods `record_search' will return a reference to an array of hash. record_insert ( $name, \%params ); ---------------------------------- This method will insert a record in the table specified by the profile $name. The params argument is a reference to an hash which contains the record data to be inserted. The hash should contains one element for each key specified in the keys field of the profile. Each elements in the fields that is a valid table fields (as specified by the fields element of the profile) will be inserted. Any elements specified defaults and not present in the params hash will also be inserted. Return value is undefined. record_update ( $name, \%params ); ---------------------------------- This method will update a record in the table specified by the profile $name. The params argument is a reference to an hash which contains the record data to be updated. The hash should contains one element for each key specified in the keys field of the profile. Each elements in the fields that is a valid table fields (as specified by the fields element of the profile) will be updated. Any elements specified defaults and not present in the params hash will also be updated. Return value is undefined. record_delete ( $name, $keys ); ------------------------------- This method will delete a record in the table specified by the profile $name. The keys argument is a reference to an hash which contains the keys to the record to delete. The hash should contains one element for each key specified in the keys field of the profile. Return value is undefined. TEMPLATE BASED ACCESS ===================== All of the `template_*' methods accepts two parameters, $name and params. The $name parameter specified the profile to use as a template for the operation (get,search,insert,update or delete). The other parameter is used as substitutions for the placeholders of the template. Those substitutions can be specified in three manners : ARRAY OR ARRAY REF Each element of the array is mapped to an element of the params field of the profile. It is an error if the number of elements is different than the number of params defined in the profile. HASH REF Each substitutions will be mapped to one of the element of the params hash in the order specified by the params element of the profile. If a params element isn't present, a default one will be used. (Either the value specified in the profile's defaults element or NULL). template_get ( $name, params ) ------------------------------ This method will return an hash reference to a record using the profile $name. template_search ( $name, params ) --------------------------------- This method will run a search using the query template specified in the profile named $name and return the results in a reference to an array of hashes. This methods accept the same magic parameters in the %params element as the `record_search' method. It also modifies the same element in %params as that method. template_insert ( $name, params ) --------------------------------- This method will insert a record according to the profile in $name. Normal template substitutions will be used. Return value is undefined. template_update ( $name, params ) --------------------------------- This method will update records according to the profile $name and using standard template's placholders substitutions semantics. Return value is the number of rows updated. template_delete ( $name, params ) --------------------------------- This method will delete records according to the template $name and using regular template's placeholders substitutions semantics. Return value is the number of records deleted. BUGS AND LIMITATIONS ==================== Please report bugs, suggestions, patches and thanks to . The search limitations and offset SQL generation is probably not completely portable. It uses LIMIT and OFFSET which are maybe not supported across SQL92 implementation. (PostgreSQL supports it so...) To find the number of records that will be returned by a query (in *_search) we use `count(*)'. This could cause a number of problems. AUTHOR ====== Copyright (c) 1999 Francis J. Lacoste and iNsu Innovations Inc. All rights reserved. This program is free software; you can redistribute it and/or modify it under the terms as perl itself. SEE ALSO ======== DBIx::Recordset(3) DBI(3) DBIx::UserDB(3)  File: pm.info, Node: DBIx/Table, Next: DBIx/TextIndex, Prev: DBIx/SearchProfiles, Up: Module List Class used to represent DBI database tables. ******************************************** NAME ==== DBIx::Table - Class used to represent DBI database tables. SYNOPSIS ======== To make it useful: ------------------ package SUBCLASS; @ISA = qw(DBIx::Table); sub describe { my($self) = shift; $self->{'table'} = $table_name; $self->{'unique_keys'} = [ [ $column, ... ], ... ]; $self->{'columns'} = { $col_name => { %col_options }, [ ... ] }; [ $self->{'related'} = { $class_name => { %relationship }, [ ... ] }; ] } To use the useful object: ------------------------- $table = load SUBCLASS( db => $dbi_object, [ where => { $column => $value, ... }, ] [ columns => [ $column1, $column2 ], ] [ index => $index, ] [ count => $count, ] [ groupby => $groupby, ] [ orderby => ['+'|'-'] . $column ]); $table = create SUBCLASS( db => $dbi_object); $new_table = $table->load_related( type => $classname, row => $row, [ %where_arguments ] ); $num_rows = $table->num_rows(); $query_rows = $table->query_rows(); $columns = $table->columns(); $db = $table->db(); $level = $table->debug_level( [ level => $level ] ); $value = $table->get( column => $column, [ row => $row ] ); $retval = $table->set( change => { $column => $value, [ ..., ] }, [ row => $row ] ); $retval = $table->refresh( columns = [ $column1, $column2 ], [ row => $row ] ); $retval = $table->commit( [ row => $row ] ); $retval = $table->remove( [ row => $row ] ); $count = $table->count( [ where => { $column => $value, ... } ] DESCRIPTION =========== DBIx::Table is a class designed to act as an abstraction layer around a fairly large subset of SQL queries. It is called 'Table' because it is primarily designed such that a single subclass provides an object-oriented interface to a single database table. The module is flexible enough, though, that it can be used to abstract most any schema in a way that is comfortable to the perl coder who is not familiar with the underlying schema, or even SQL. As the synopsis above points out, this class is not useful when simply used by itself. Instead, it should be subclassed. The subclass follows a particular syntax for describing the structure of the table, which the Table module uses internally to control its behavior. The subclass can then be used to access the underlying data, with the Table module taking care of writing the actual SQL. The current version can write SELECT, UPDATE, INSERT and DELETE statements. Depending on the complexity of the definition, it can also do joins across multiple tables and intelligently load related table objects. The rest of the documentation is split: first, how to create a useful subclass. Second, constructors and access methods on the subclass. Third, some examples. Without further ado... Subclassing ----------- See the perltoot(1) and perlobj(1) manuals if you don't know how to create a class, subclass, or if you don't understand inheritance or overriding inherited functions. Every subclass of DBIx::Table is required to provide a method called "describe", which, at a minimum, needs to provide some clues as to the form of the underlying data. This is done by modifying a few key parts of the data stored in the object itself: *$self->{'table'}* This should contain a string; the name of the table from which data is going to be retrieved. This should be the primary table in the case of complex classes joining from multiple tables - this table name will be used for columns which do not provide any other table name. *$self->{'unique_keys'}* If you plan on using the commit() or remove() methods, you'll need to provide at least one unique key combination. This parameter takes a reference to an array of references to arrays of strings. The listed strings in the second level array are the names of columns which, taken in conjunction, are guaranteed to be unique in the database. These are tried in order, so put them in order of preference. *$self->{'columns'}* This should contain a reference to a hash, where most of the interesting bits of configuration go. The hash referenced should be keyed by column names, and have values consisting of hash references. These nested hashes should contain configuration options for the column in question. This all sounds pretty hairy, but in practice it's really not so bad - see the Examples section below. Here are the available column options: null DBIx::Table only cares if this is defined or not defined. If it is defined, a commit() call will fail if a value for this column is not set() first, or no default is supplied. This only applies to local columns. quoted DBIx::Table only cares if this is defined or not defined. If it is defined, data which is set() to this column will be quoted using $self->{'db'}->quote(), the quote method on the DBI object passed into the constructor. immutable DBIx::Table only cares if this is defined or not defined. If it is defined, trying to set() a value to this column will cause set() to fail. Immutability is, for now at least, implicit on all "foreign" and "special" columns - i.e. you can't update foreign data! autoincrement DBIx::Table only cares if this is defined or not defined. If it is defined, some magic will take place to ensure that, after an INSERT, the autoincremented value is correctly stored in the object. This is probably MySQL specific. default The contents of this parameter will be used by commit() to UPDATE or INSERT data on a column without the null attribute. It will be quoted if the quoted attribute is set. foreign This contains another hash reference. It is used to define the simplest case foreign columns. The hash requires the keys 'table', 'lkey', and 'rkey' - the name of the table to join, relationship column name in the joined table, and relationship column name in the local table, respectively. Optionally, it can also take 'actual_table' and 'actual_column' keys, with their values being the real names of the foreign table and column. This can be used to fetch the same column from a table more than once, based on different WHERE clauses. See the Examples section to visualize this in action. special This contains yet another hash reference (doh!) It is used to define columns which defy the abstraction currently provided by DBIx::Table. Most frequently, this will be any column which has functions act on it, and/or complicated joins. The recognized keys are 'select', 'join', 'where' and 'groupby'. The values for each of these keys are raw SQL pieces, which will be stuffed into the appropriate place in the generated SQL. One important note is that the constructor expects the column name defined in $self->{'columns'} to match the name of the column where this data is returned by the SQL statement, so you will want to always add 'AS column_name' to the end of the select chunk of SQL. And again, check out the Examples to help visualize how this works. *$self->{'related'}* This is not mandatory! If defined, it should contain a reference to a hash, keyed by package names. The values are hash references, keyed by column names from the foreign package, with values being the column name of a local column. This is used by the load_related convenience method - see the description of this method below, as well as the Examples section, to get more of a grasp of how this works. Methods ------- All of the public methods use hash-style arguments. I've tried to be consistent and obvious in the naming of arguments. The only class methods are the constructors, all other methods are strictly object methods (i.e. you can't call SUBCLASS::get(...), you have to call $subclass_object->get(...)). All methods return undef if they fail. General issues aside, here are the descriptions of the specific methods: There are two constructors: load() This is the primary constructor and SELECT statement generator. It takes a bunch of arguments, though only the db argument is mandatory: db This argument should contain a reference to a DBI object. It is assumed to be connected and valid for the lifetime of the object to which it is passed. where This argument should contain a hash reference, with keys being column names and values being the value that column must equal. As a bonus hack, if the value is the string 'IS NULL', it'll work as you want it to (unless you're actually looking for the string 'IS NULL' in the database, in which case you're screwed.) If this argument is excluded, no WHERE clause will be used (all rows will be returned.) columns This argument should contain an array reference. The elements of the array should be column names, or the special string '*'. It is important to realize that columns with the 'foreign' or 'special' attributes are not loaded if this argument is left out of the load() call. The special string '*' is expanded to all columns which are not 'foreign' or 'special'. If this argument is left out, a literal '*' will go in the SQL generated, indicating that all local columns in this table should be loaded. index This argument should contain a numeric scalar, which is used to limit the amount of data stored (but not *queried*!) by the object returned by load(). It is the number of the first row to store, starting from zero. count This argument should contain a numeric scalar, which is also used to limit the amount of data stored (but, again, not queried!) by load(). This is a zero-based count of the maximum number of rows to store. groupby This argument should contain a string scalar. It is the name of a column, and it causes the addition of a GROUP BY column_name clause to the end of the SQL generated. orderby This argument should contain a string scalar, the name of a column which to be used to sort the returned data. It causes the addition of an ORDER BY column_name clause to the SQL generated. Bonus hack: if you prepend a "+" to the column name, the clause has "ASC" appended to it, and if you prepend a "-" to the column name, the clause has "DESC" appended to it. create() This simple constructor builds and returns an empty object. It is useful for inserting new data into a table. It is also useful for creating an empty instance of the object with which to use the count() method. It takes only one argument, 'db', which is identical to the 'db' argument to load(), described above. Two methods for using the data in the object: get() This method is used to fetch data from the object. It takes only two possible arguments; only the 'column' argument is mandatory. column This should contain the name of the column to retrieve. At the moment, you can only retrieve one value at a time. row This should contain the number of the row to retrieve data from. Rows in the object are always indexed by 0. If this argument is excluded, it defaults to 0. set() This method is used to change data in the object. It has only two possible arguments. Only the 'change' argument is mandatory, and the 'row' argument is identical to that described in get() above. change This should contain a hash reference. The keys of the hash should be column names, and the values should be the values you wish to place in those columns. You can change as many columns as you want at once, but remember that 'foreign', 'special' and 'immutable' columns cannot be changed. Three methods can make additional SQL queries based on the current data: refresh() This method can perform additional SELECT queries to the database, using the data already loaded to ensure that the new data relates to the existing row. It takes a mandatory 'columns' argument, an array reference containing column names to load. It can also take a 'row' argument, as described under get() above. commit() This method is responsible for writing UPDATE or INSERT SQL statements. It takes one optional argument, 'row', which is identical to the 'row' argument described under get() above. Please be careful with this method, as it has only been tested for fairly simple cases. remove() This method writes DELETE SQL statements, attempting to remove the current row from the database permanently. It takes one optional argument, 'row', which is identical to the 'row' argument described under get() above. Please be careful with this method, as it has only been tested for fairly simple cases. Several methods can be used to get meta-data about the object, and the data retrieved, and configure behavior of the object: num_rows() Returns the number of rows stored in the current object. query_rows() Returns the number of rows returned by the last query (this may be different from num_rows() if 'index' or 'count' parameters to load() were used). columns() Returns a reference to an array containing the names of all the columns in the object. Not only the ones with data in them, mind you! All column names are returned. db() Returns a reference to the database object that is being used. debug_level() This takes a 'level' parameter, with a numeric value. The range from 0 to 2 is significant: 0 is trivial debugging information, 1 is informational messages, and 2 is errors. Debugging information is only printed (to STDERR) if the debug_level is set to the priority level of the message or lower. This function can also be used with no arguments to return the current debug_level. And a couple of "miscellaneous" utility functions: count() This is a function for generating count(*) style SELECT statements. It does not store the return from the query; instead, it returns it to the caller. It takes an optional 'where' argument, identical to the one described under load(). load_related() This is a utility function for constructing objects from different classes, which are related to the current class. It takes two arguments of its own, but only the 'type' argument is mandatory: type This should contain the name of the class from which to load a new object. row This should contain the row number to which the new object should be related. It defaults to 0. The row number is used in the substitution process described below. All other arguments will be passed on to the load() constructor for the class passed in in 'type'. It is not necessary to provide a 'db' argument; it will simply pass on the one it already has. And finally, the real nicety provided by load_related is that it will check the 'where' argument (if any) and will use the information in $self->{'related'} to substitute values. So you can say where => { 'that_column' => 'this_column' }, and load_related will convert the literal 'this_column' into the current value of this_column. Examples -------- These examples are simple but are designed to show you how this module can be used. They progress from table descriptions to complete subclasses to usage, including showing the SQL that is output. Beginning with two tables (as you would create them with MySQL). First, a simple users table, very straightforward: CREATE TABLE users ( uid INT UNSIGNED NOT NULL AUTO_INCREMENT, email VARCHAR(70) NOT NULL, password VARCHAR(10) NOT NULL, PRIMARY KEY(uid), UNIQUE (uid), ); Second, a feedback table. This table is complexly related to the users table; each user can both send and receive multiple feedback, so both the to_uid and from_uid columns point back to the users table. CREATE TABLE feedback ( fid INT UNSIGNED NOT NULL AUTO_INCREMENT, to_uid INT UNSIGNED NOT NULL, from_uid INT UNSIGNED NOT NULL, time DATETIME NOT NULL, type ENUM('good', 'bad', 'neutral') NOT NULL, text VARCHAR(100) NOT NULL, PRIMARY KEY (fid), INDEX (to_uid), INDEX (from_uid), UNIQUE uid_combo (to_uid, from_uid) ); Okay, now we need to create objects to represent them. For users, I want to be able to fetch the counts of the feedback recieved by the user in question for each of the three feedback types, which requires 'special' columns: package User; use strict; use DBIx::Table; @User::ISA = qw(DBIx::Table); sub describe { my($self) = shift || return(undef); $self->{'table'} = 'users'; $self->{'unique_keys'} = [ ['uid'] ]; $self->{'columns'} = { 'uid' => { 'immutable' => 1, 'autoincrement' => 1, 'default' => 'NULL' }, 'email' => { 'quoted' => 1 }, 'password' => { 'quoted' => 1 }, 'good_fb' => { 'special' => { 'select' => 'count(fb_g.type) AS good_fb', 'join' => 'LEFT JOIN feedback AS fb_g ON (fb_g.type = ' . '\'good\' AND fb_g.to_uid = users.uid)', 'groupby' => 'uid' } }, 'bad_fb' => { 'special' => { 'select' => 'count(fb_b.type) AS bad_fb', 'join' => 'LEFT JOIN feedback AS fb_b ON (fb_b.type = ' . '\'bad\' AND fb_b.to_uid = users.uid)', 'groupby' => 'uid' } }, 'neutral_fb' => { 'special' => { 'select' => 'count(fb_n.type) AS neutral_fb', 'join' => 'LEFT JOIN feedback AS fb_n ON (fb_n.type = ' . '\'neutral\' AND fb_n.to_uid = users.uid)', 'groupby' => 'uid' } } }; $self->{'related'} = { 'Feedback' => { 'from_uid' => 'uid', 'to_uid' => 'uid' } }; } 1; Phew. Now how about the feedback table. In this case, we'd like to be able to fetch the email addresses of both the sender and recipient users in the same query. This can be done with 'foreign' columns. Here's the class: package Feedback; use strict; use DBIx::Table; @Feedback::ISA = qw(DBIx::Table); sub describe { my($self) = shift || return(undef); $self->{'table'} = 'feedback'; $self->{'unique_keys'} = [ ['fid'] ]; $self->{'columns'} = { 'fid' => { 'immutable' => 1, 'autoincrement' => 1, 'default' => 'NULL' }, 'to_uid' => { }, 'from_uid' => { }, 'time' => { 'default' => 'NOW()' }, 'type' => { 'quoted' => 1 }, 'text' => { 'quoted' => 1 }, 'to_email' => { 'foreign' => { 'table' => 'users_to', 'lkey' => 'to_uid', 'rkey' => 'uid', 'actual_table' => 'users', 'actual_column' => 'email' } }, 'from_email' => { 'foreign' => { 'table' => 'users_from', 'lkey' => 'from_uid', 'rkey' => 'uid', 'actual_table' => 'users', 'actual_column' => 'email' } } }; } 1; Using these is simple enough. The simplest case would be: $obj = load User( db => $db ); which generates the SQL: SELECT * from users Not very intersting. How about: $obj = load User db => $db, where => { uid => 1 }, columns => [ '*', 'good_fb', 'bad_fb', 'neutral_fb' ]; Which generates the SQL (formatted for viewing ease): SELECT count(fb_g.type) AS good_fb, count(fb_b.type) AS bad_fb, count(fb_n.type) AS neutral_fb, users.password, users.email, users.uid FROM users LEFT JOIN feedback AS fb_g ON (fb_g.type = 'good' AND fb_g.to_uid = users.uid) LEFT JOIN feedback AS fb_b ON (fb_b.type = 'bad' AND fb_b.to_uid = users.uid) LEFT JOIN feedback AS fb_n ON (fb_n.type = 'neutral' AND fb_n.to_uid = users.uid) WHERE users.uid = 1 GROUP BY uid Let's load up all feedback with the associated e-mail addresses, arranged by descending time sent: $obj = load Feedback db => $db, columns => [ '*', 'to_email', 'from_email' ], orderby => '-time' ; Generates the SQL (again, formatted): SELECT users_to.email AS to_email, users_from.email AS from_email, feedback.type, feedback.vs_fid, feedback.to_uid, feedback.text, feedback.from_uid, feedback.time FROM feedback JOIN users AS users_to JOIN users AS users_from WHERE feedback.to_uid = 2 AND users_to.uid = feedback.to_uid AND users_from.uid = feedback.from_uid ORDER BY feedback.time DESC That seems like enough to get started. BUGS ==== * Autoincrement columns are known to be mySQL specific. There's probably more, since this has really only been tested with MySQL. If anyone tries it with another DBD, I'd love to hear from you! * In a stupid design oversight, the current incarnation can only automatically generate "... WHERE foo = bar ..." in SELECT statements, emphasis on the '='. Sorry! I'll work on it! AUTHOR ====== J. David Lowe, dlowe@pootpoot.com SEE ALSO ======== perl(1)