"new()"
Currently, new() takes no arguments and constructs an empty object. You normally shouldn't need to call
this directly.
"get_dbh()"
This method should return a database handler, as returned by DBI->connect. The default implementation
croaks, so you need to overwrite it in your subclasses. To write it only once, you can use an
intermediate object.
"table($table_name)"
Call this method in each derived package to inform DBIx::OO of the table that you wish that package to
use.
__PACKAGE__->table('Users')
"columns(group[=>cols,...])"
Sets/retrieves the columns of the current package.
Similarly to Class::DBI, DBIx::OO uses a sort of column grouping. The 'P' group is always the primary
key. The 'E' group is the essential group--which will be fetched whenever the object is first
instantiated. You can specify any other groups names here, and they will simply group retrieval of
columns.
Example:
__PACKAGE__->columns(P => [ 'id' ],
E => [ 'name', 'description' ],
X => [ 'c1', 'big_content1', 'big_title1' ],
Y => [ 'c2', 'big_content2', 'big_title2' ]);
The above code defines 4 groups. When an object is first instantiated, it will fetch 'id', 'name' and
'description'. When you say $obj->c1, it will fetch 'c1, 'big_content1' and 'big_title1', because they
are in the same group. When you say $obj->c2 it will fetch 'c2', 'big_content2' and 'big_title2'.
That's pretty much like Class::DBI.
To retrieve columns, you pass a group name.
Notes
• Class::DBI allows you to call columns() multiple times, passing one group at a time. Our module
should allow this too, but it's untested and might be buggy. We suggest defining all groups in one
shot, like the example above.
• Group 'P' is required. I mean that. We won't guess the primary key column like Class::DBI does.
"clone_columns(@except)"
Though public, it's likely you won't need this function. It returns a list of column names that would be
cloned in a clone() operation. By default it excludes any columns in the "P" group (primary keys) but
you can pass a list of other names to exclude as well.
"defaults(%hash)"
Using this function you can declare some default values for your columns. They will be used unless
alternative values are specified when a record is inserted (e.g. with create()). Example:
__PACKAGE__->defaults(created => ['now()'],
hidden => 1,
modified_by => \&get_current_user_id);
You can specify any scalar supported by SQL::Abstract's insert operation. For instance, an array
reference specifies literal SQL (won't be quoted). Additionally, you can pass code references, in which
case the subroutine will be called right when the data is inserted and its return value will be used.
"get(field_name[,field_name,...])"
Retrieves the value of one or more columns. If you pass more column names, it will return an array of
values, in the right order.
"set(field=>value[,field=>value,...])"
Sets one or more columns to the specified value(s).
This function calls "before_set" right before modifying the object data, passing a hash reference to the
new values.
"before_set"
By default this function does nothing. It will be called by the framework right before setting column
values. A hash reference with columns to be set will be passed. You can modify this hash if you wish.
For example, assuming you have an Users table with a MD5 password and you want to create the MD5 right
when the column is set, you can do this:
package Users;
...
sub before_set {
my ($self, $h, $is_create) = @_;
if (exists $h->{password}) {
$h->{password} = make_md5_passwd($h->{password});
}
}
my $u = Users->retrieve('foo');
$u->password('foobar');
print $u->password;
# be8cd58c70ad7dc935802fdb051869fe
The $is_create argument will be true (1) if this function is called as a result of a create() command.
"id()"
Returns the value(s) of the primary key(s). If the primary key consists of more columns, this method
will return an array with the values, in the order the PK column names were specified.
Currently this is equivalent to $self->get(@{ $self->columns('P') }).
"transaction_start()","transaction_rollback()","transaction_commit()"
Use these functions to start, commit or rollback a DB transaction. These simply call begin_work,
rollback and commit methods on the DB handle returned by get_dbh().
"get_accessor_name()"
There are a few column names that we can't allow as accessor names. This function receives a column name
and returns the name of the accessor for that field. By default it prefixes forbidden names with 'col_'.
The forbidden names are:
- id
- can
- our
- columns
- table
- get
- set
- count
If you don't like this behavior you can override this function in your classes to return something else.
However, be very careful about allowing any the above forbidden names as accessors--basically nothing
will work.
"get_fk_name"
This function returns the name of a foreign key accessor, as defined by has_a/has_many. The default
returns "fk_$name"--thus prepending "fk_".
If you want the Class::DBI behavior, you can override this function in your derived module:
sub get_fk_name { return $_[1]; }
(the first argument will be object ref. or package)
I think the Class::DBI model is unwise. Many times I found my columns inflated to objects when I was in
fact expecting to get an ID. Having the code do implicit work for you is nice, but you can spend hours
debugging when it gets it wrong--which is why, DBIx::OO will by default prepend a "fk_" to foreign
objects accessors. You'll get use to it.
"has_a/has_many"
__PACKAGE__->has_a(name, type[, mapping[, order ]]);
__PACKAGE__->has_many(name, type[, mapping[, order[, limit[, offset ]]]]);
Creates a relationship between two packages. In the simplest form, you call:
__PACKAGE__->has_a(user => Users);
This declaration creates a relation between __PACKAGE__ (assuming it has a column named 'user') and
'Users' package. It is assuming that 'user' from the current package points to the primary key of the
Users package.
The declaration creates a method named 'fk_user', which you can call in order to retrieve the pointed
object. Example:
package Pages;
use base 'MyDB';
__PACKAGE__->columns('P' => [ 'id' ],
'E' => [ 'user', ... ]);
__PACKAGE__->has_a(user => 'Users');
my $p = Pages->retrieve(1);
my $u = $p->fk_user;
print $u->first_name;
In more complex cases, you might need to point to a different field than the primary key of the target
package. You can call it like this:
Users->has_many(pages => Pages, 'user');
my $u = Users->retrieve('foo');
my @pages = @{ $u->fk_pages };
The above specifies that an User has many pages, and that they are determined by mapping the 'user' field
of the Pages package to the primarykey of the "Users" package.
has_many() also defines an utility function that allows us to easily count the number of rows in the
referenced table, without retrieving their data. Example:
print $u->count_pages;
You can specify an WHERE clause too, in SQL::Abstract syntax:
print $u->count_pages(keywords => { -like => '%dhtml%' });
The above returns the number of DHTML pages that belong to the user.
In even more complex cases, you want to map one or more arbitrary columns of one package to columns of
another package, so you can pass a hash reference that describes the column mapping:
## FIXME: find a good example
has_many() is very similar to has_a, but the accessor it creates simply returns multiple values (as an
array ref). We can pass some arguments too, either to has_a/has_many declarations, or to the accessor.
@pages = @{ $u->fk_pages('created', 10, 5) }
The above will retrieve the user's pages ordered by 'created', starting at OFFSET 5 and LIMIT-ing to 10
results.
You can use has_a even if there's not a direct mapping. Example, a page can have multiple revisions, but
we can also easily access the first/last revision:
Pages->has_many(revisions => 'Revisions', 'page');
Pages->has_a(first_revision => 'Revisions', 'page', 'created');
Pages->has_a(last_revision => 'Revisions', 'page', '^created');
has_a() will LIMIT the result to one. Ordering the results by 'created', we make sure that we actually
retrieve what we need. Note that by prefixing the column name with a '^' character, we're asking the
module to do a DESC ordering.
(Of course, it's a lot faster if we had first_revision and last_revision as columns in the Pages table
that link to Revision id, but we just wanted to point out that the above is possible ;-)
Joinhas_a() will additionally create a join function. It allows you to select data from 2 tables using a
single SQL query. Example:
package MyDB::Users;
MyDB::Users->table('Users');
MyDB::Users->has_a(profile => 'Profiles');
package MyDB::Profiles;
MyDB::Profiles->table('Profiles');
@data = Users->search_join_profile;
foreach (@data) {
my $user = $_->{Users}; # the key is the SQL B<table> name
my $profile = $_->{Profiles};
print $user->id, " has address: ", $profile->address;
}
The above only does 1 SELECT. Note that the join search function returns an array of hashes that map
from the SQL table name to the DBIx::OO instance.
You can pass additional WHERE, ORDER, LIMIT and OFFSET clauses to the join functions as well:
@data = Users->search_join_profile({ 'Users.last_name' => 'Doe' },
'Users.nickname',
10);
The above fetches the first 10 members of the Doe family ordered by nickname.
Due to lack of support from SQL::Abstract side, the JOIN is actually a select like this:
SELECT ... FROM table1, table2 WHERE table1.foreign = table2.id
In the future I hope to add better support for this, that is, use "INNER JOIN" and eventually support
other JOIN types as well.
Notes
1. The "fk_" accessors will actually retrieve data at each call. Therefore:
$p1 = $user->fk_pages;
$p2 = $user->fk_pages;
will retrieve 2 different arrays, containing different sets of objects (even if they point to the
same records), hitting the database twice. This is subject to change, but for now you have to be
careful about this. It's best to keep a reference to the returned object(s) rather than calling
fk_pages() all over the place.
2. has_many() creates accessors that select multiple objects. The database will be hit once, though,
and multiple objects are created from the returned data. If this isn't desirable, feel free to LIMIT
your results.
"might_have()"
Alias to has_a().
"has_mapping(name,type,maptype,map1,map2,order,limit,offset)"
You can use has_mapping to map one object to another using an intermediate table. You can have these
tables:
Users: id, first_name, etc.
Groups: id, description, etc.
Users_To_Groups: user, group
This is quite classical, I suppose, to declare many-to-many relationships. The Users_To_Groups contains
records that map one user to one group. To get the ID-s of all groups that a certain user belongs to,
you would say:
SELECT group FROM Users_To_Group where user = '$user'
But since you usually need the Group objects directly, you could speed things up with a join:
SELECT Groups.id, Groups.description, ... FROM Groups, Users_To_Groups
WHERE Users_To_Groups.group = Groups.id
AND Users_To_Groups.user = '$user';
The relationship declared with has_mapping() does exactly that. You would call it like this:
package Users;
__PACKAGE__->table('Users');
__PACKAGE__->columns(P => [ 'id' ], ...);
__PACKAGE__->has_mapping(groups, 'Groups',
'Users_To_Groups', 'user', 'group');
package Groups;
__PACKAGE__->table('Groups');
__PACKAGE__->columns(P => [ 'id' ], ...);
# You can get the reverse mapping as well:
__PACKAGE__->has_mapping(users, 'Users',
'Users_To_Groups', 'group', 'user');
package Users_To_Groups;
__PACKAGE__->table('Users_To_Groups');
__PACKAGE__->columns(P => [ 'user', 'group' ]);
Note that Users_To_Groups has a multiple primary key. This isn't required, but you should at least have
an unique index for the (user, group) pair.
Arguments
I started with an example because the function itself is quite complicated. Here are arguments
documentation:
name
This is used to name the accessors. By default we will prepend a "fk_" (see get_fk_name).
type
The type of the target objects.
maptype
The mapping object type. This is the name of the object that maps one type to another. Even though
you'll probably never need to instantiate such an object, it still has to be declared.
map1
Specifies how we map from current package (__PACKAGE__) to the "maptype" object. This can be a
scalar or an hash ref. If it's a scalar, we will assume that __PACKAGE__ has a simple primary key
(not multiple) and "map1" is the name of the column from "maptype" that we should map this key to.
If it's a hash reference, it should directly specify the mapping; the keys will be taken from
__PACKAGE__ and the values from "maptype". If that sounds horrible, check the example below.
map2
Similar to "map1", but "map2" specifies the mapping from "maptype" to the target "type". If a
scalar, it will be the name of the column from "maptype" that maps to the primary key of the target
package (assumed to be a simple primary key). If a hash reference, it specifies the full mapping.
order, limit, offset
Similar to has_many, these can specify default ORDER BY and/or LIMIT/OFFSET clauses for the resulted
query.
Example
Here's the mapping overview:
map1 map2
__PACKAGE__ ===> C<maptype> ===> C<type>
current package table that holds the target package
the mapping
"create"
my $u = Users->create({ id => 'foo',
first_name => 'John',
last_name => 'Doe' });
Creates a new record and stores it in the database. Returns the newly created object. We recommend
passing a hash reference, but you can pass a hash by value as well.
clone(@except)
Clones an object, returning a hash (reference) suitable for create(). Here's how you would call it:
my $val = $page->clone;
my $new_page = Pages->create($val);
Or, supposing you don't want to copy the value of the "created" field:
my $val = $page->clone('created');
my $new_page = Pages->create($val);
"init_from_data($data)"
Initializes one or more objects from the given data. $data can be a hashref (in which case a single
object will be created and returned) or an arrayref (multiple objects will be created and returned as an
array reference).
The hashes simply contain the data, as retrieved from the database. That is, map column name to field
value.
This method is convenient in those cases where you already have the data (suppose you SELECT-ed it in a
different way than using DBIx::OO) and want to initialize DBIx::OO objects without the penalty of going
through the DB again.
"retrieve"
my $u = Users->retrieve('foo');
Retrieves an object from the database. You need to pass its ID (the value of the primary key). If the
primary key consists on more columns, you can pass the values in order as an array, or you can pass a
hash reference.
Returns undef if no objects were found.
"search($where,$order,$limit,$offset)"
$a = Users->search({ created => [ '>=', '2006-01-01 00:00:00' ]});
Searches the database and returns an array of objects that match the search criteria. All arguments are
optional. If you pass no arguments, it will return an array containing all objects in the DB. The
syntax of $where and $order are described in SQL::Abstract.
In scalar context it will return a reference to the array.
The $limit and $offset arguments are added by DBIx::OO and allow you to limit/paginate your query.
UPDATE 0.0.7:
Certain queries are difficult to express in SQL::Abstract syntax. The search accepts a literal WHERE
clause too, but until version 0.0.7 there was no way to specify bind variables. For example, now you can
do this:
@admins = Users->search("mode & ? <> 0 and created > ?",
undef, undef, undef,
MODE_FLAGS->{admin},
strftime('%Y-%m-%d', localtime)).
In order to pass bind variables, you must pass order, limit and offset (give undef if you don't care
about them) and add your bind variables immediately after.
"retrieve_all()"retrieve_all() is an alias to search() -- since with no arguments it fetches all objects.
"update"
$u->set(first_name => 'Foo',
last_name => 'Bar');
$u->update;
Saves any modified columns to the database.
"delete"
$u = Users->retrieve('foo');
$u->delete;
Removes the object's record from the database. Note that the Perl object remains intact and you can
actually revive it (if you're not losing it) using undelete().
"undelete"
$u = Users->retrieve('foo');
$u->delete; # record's gone
$u->undelete; # resurrected
This function can "ressurect" an object that has been deleted (that is, it re-INSERT-s the record into
the database), provided that you still have a reference to the object. I'm not sure how useful it is,
but it helped me test the delete() function. :-)
Other (useless) thing you can do with it is manually emulating the create() function:
$u = new Users;
$u->{values}{id} = 'foo';
$u->first_name('Foo');
$u->last_name('Bar');
$u->undelete;
Note we can't call the column accessors, nor use set/get, before we have a primary key.
This method is not too useful in itself, but it helps understanding the internals of DBIx::OO. If you
want to read more about this, see "under the hood".
"revert",or"discard_changes"
$u = Users->retrieve('foo');
$u->first_name(undef);
$u->revert;
Discards any changes to the object, reverting to the state in the database. Note this doesn't SELECT new
data, it just reverts to values saved in the "modified" hash. See "under the hood" for more info.
"discard_changes()" is an alias to "revert()".
get_sql_abstract
Returns the instance of SQL::Abstract::WithLimit (our custom derivative) suitable for generating SQL.
This is cached (will be created only the first time get_sql_abstract is called).
count
Returns the result of an SQL COUNT(*) for the specified where clause. Call this as a package method, for
example:
$number_of_romanians = Users->count({ country => 'RO' });
The argument is an SQL::Abstract where clause.
"disable_fk_checks()","enable_fk_checks()"
Enable or disable foreign key checks in the backend DB server. These are hard-coded in MySQL syntax for
now so be careful not to use them with other servers. ;-)
"autocreate(@packages)"
You can use this facility to automatically create / upgrade your database. It takes a very simple
(rudimentary even) approach, but we found it to be useful. Here's the "big" idea.
package MyDB::Users;
use base 'MyDB';
__PACKAGE__->table('Users');
__PACKAGE__->columns(P => [ 'id' ],
E => [qw/ first_name last_name /]);
sub get_autocreate_data {q{
#### (users:0) ####
CREATE TABLE Users ( id VARCHAR(32) NOT NULL PRIMARY KEY,
first_name VARCHAR(64),
last_name VARCHAR(64) );
# you can put Perl comments too.
CREATE INDEX idx_Users_first_name ON Users(first_name)
}}
OK, now you can write this make_database.pl script:
/usr/bin/perl -w
use MyDB;
MyDB->autocreate(qw( MyDB::Users ));
When you run this script the first time, it will create the Users table. (An internal _dbix_oo_versions
table gets created as well; we're using it inside DBIx::OO in order to keep track of existing table
versions). Note that if you run it again, it doesn't do anything--the database is up to date.
Later. You sold a billion copies of your software, customers are happy but they are crying loud for an
"email" field in their user profiles, also wondering what was your idea to index on first_name and not on
last_name! In order to make it easy for them to upgrade their databases, you need to modify MyDB::Users.
Besides declaring the 'email' column using __PACKAGE__->columns, append the following to your
get_autocreate_data section:
#### (users:1) ####
# (note that we incremented the version number)
# add the 'email' field
ALTER TABLE Users ADD (email VARCHAR(128));
# index it
CREATE UNIQUE INDEX idx_Users_email ON Users(email);
# and add that last_name index
CREATE INDEX idx_Users_last_name ON Users(last_name);
Now you can just tell your users to run make_database.pl again and everything gets updated.
The #### (foo:N) #### syntax is meant simply to declare an ID and a version number. "foo" can be
anything you want -- it doesn't have to be the table name. You can actually create multiple tables, if
you need to.
autopopulate
This is supposed to initialize tables. Untested and may not work -- don't use it.
get_autocreate_data
See the documentation of "autocreate".
Install Now
PNG Icons
Emojis
