=encoding utf8
=head1 NAME
DBIx::Simple::Class - Advanced object construction for DBIx::Simple!
=head1 DESCRIPTION
DBIx::Simple::Class is a database table/row abstraction. At the same
time it is not just a fancy representation of a table row like
L<DBIx::Simple::Result::RowObject>. Using this module will make your
code more organized, clean and reliable
(separation of concerns + input-validation).
You will even get some more performance over plain DBIx::Simple
while keeping its' sexy features when you need them. Last but not
least, this module has no other non-CORE Perl 5.10.1 dependencies besides
L<DBIx::Simple> and L<DBI>. See below for details.
To easily generate entire classes and save them on disk for later usage
and customization please use L<DBIx::Simple::Class::Schema> which comes
with this distribution.
=head1 SYNOPSIS
#1. In your class representing a description of a row in a database table or view
package My::Model::AdminUser;
#You can extend your own common base class that extends DBIx::Simple::Class
use base qw(My::Model);#My::Model is your base class.
#table name used in generated SQL
sub TABLE { 'users' } #or: use constant TABLE =>'users';
sub COLUMNS {[qw(id group_id login_name login_password first_name last_name)]}
#used to validate params to auto-generated field-setters/getters
sub CHECKS{{
id => { allow => qr/^\d+$/x },
group_id => { allow => qr/^1$/x, default=>1 },#admin group_id
login_name => {required => 1, allow => qr/^\p{IsAlnum}{4,12}$/x},
first_name =>{required => 1, allow => \&avery_complex_check},
last_name =>{ allow => sub {
#less complex inline check that modifies the input value
#see Params::Check::allow and Params::Check::check
}
}
#...
}}
#select only rows (instantiate objects) that meet a requirement
sub WHERE { group_id=> 1}
1;#end of My::Model::AdminUser
#2. In a start-up script or subroutine instantiate DBIx::Simple once
DBIx::Simple::Class->dbix( DBIx::Simple->connect(...) );
#3. ...and use it everywhere in your application
use My::Model::AdminUser;
my $user = $dbix->select(
My::Model::AdminUser->TABLE, '*', {login_name => 'fred'}
)->object('My::Model::AdminUser')
#or better (if SQL::Abstract is installed)
my $user = My::Model::AdminUser->select(login_name => 'fred'); #this is cleaner
#or simply get the admin-user with id=2
My::Model::AdminUser->find(2);
#then...
$user->first_name('Fred')->last_name('Flintstone'); #chainable setters
$user->save; #update row
#add new
my $user = My::Model::AdminUser->new(
login_name => 'петър',
first_name => 'Петър',
last_name =>'Павлов'
);
$user->save();#insert new user
print "new user has id:".$user->id;
#select many users
my $class = 'My::Model::AdminUser';
my @admins = $dbix->select(
$class->TABLE,
$class->COLUMNS,
$class->WHERE
)->objects($class);
#or
my @admins = $dbix->query(
$VERY_COMPLEX_SQL, @bind_variables
)->objects($class);
=head1 CONSTANTS
=head2 TABLE
You B<must> define it in your subclass. This is the table where
your object will store its data. Must return a string - the table name.
It is used internally in L</select> L</update> and L</insert> when saving object data.
And with little imagination you could put here some complex SQL or
an already prepared view:
(SELECT * FROM users WHERE column1='something' column2='other')
Of course rows from such "table" can not be updated without overriding L</update>.
sub TABLE { 'users' }
#using DBIx::Simple select() or query()
dbix->select($class->TABLE, $class->COLUMNS, {%{$class->WHERE}, %$where})->object($class);
=head2 WHERE
A HASHREF suitable for passing to L<DBIx::Simple/select>.
It is also used internally in L</select> and L</update>.
Values can be only simple scalars (which may be valid SQL).
Default C<WHERE> clause for your class. Empty "C<{}>" by default.
This constant is optional.
package My::PublishedNote;
sub WHERE { {data_type => 'note',published=>1 } };
#...
use My::PublishedNote;
#somewhere in your application
my $note = My::PublishedNote->select(id=>12345);
=head2 COLUMNS
You B<must> define it in your subclass.
It must return an ARRAYREF with table columns to which the data is written.
It is used in L<DBIx::Simple/select> when retrieving a row from the database
and when saving object data. This list is also used to generate specific
getters and setters for each data-field.
sub COLUMNS { [qw(id cid user_id tstamp sessiondata)] };
# in select()
dbix->select($class->TABLE, $class->COLUMNS, {%{$class->WHERE}, %$where})->object($class);
In case you have table columns that collide with some of the methods defined in this class like L</data>,
L</save> etc., you can define aliases that will be used as method names.
See L</ALIASES>.
=head2 CHECKS
You B<must> define this subroutine/constant in your class and put in it your
C<$_CHECKS>.
C<$_CHECKS> is a HASHREF that must conform to the syntax supported by L<Params::Check/Template>.
sub CHECKS{$_CHECKS}
=head2 PRIMARY_KEY
The column that will be used to uniquely recognise your object from others
in the same table. Default: 'id'.
use constant PRIMARY_KEY => 'product_id';
#or simply
sub PRIMARY_KEY {'product_id'}
=head2 ALIASES
In case you have table columns that collide with some of the package methods like L</data>,
L</save> etc., you can define aliases that will be used as method names.
You are free to define your own getters/setter for fields. They will not be overridden.
All they need to do is to check the validity of the input and put the changed value in
C<$self-E<gt>{data}>.
#in you class
package My::Collision;
use base qw(DBIx::Simple::Class);
use constant TABLE => 'collision';
use constant COLUMNS => [qw(id data)];
use constant WHERE => {};
use constant ALIASES => {data => 'column_data'};
#CHECKS are on columns
use constant CHECKS => {
id => {allow => qr/^\d+$/x},
data => {default => '',} #that's ok
};
1;
#usage
my $coll = My::Collision->new(data => 'some text');
#or
my $coll = My::Collision->query('select * from collision where id=1');
$coll->column_data('changed')->save;
#or
$coll->data(data=>'changed')->save;
#...
$coll->column_data; #returns 'changed'
=head1 ATTRIBUTES
=head2 dbix
This is a class attribute, shared among all subclasses of DBIx::Simple::Class.
This is an L<DBIx::Simple> instance and (as you guessed) provides direct access
to the current DBIx::Simple instance (with L<SQL::Abstract> support eventually :)).
Copy/paste/override this method in your base classes
if you need more instances/connections per application.
This is already done for you if you used L<DBIx::Simple::Class::Schema> to generate
your schema and table classes.
My->dbix( DBIx::Simple->connect($bar_dsn) );
Your->dbix( DBIx::Simple->connect($foo_dsn) );
#later in My::Note
$self->dbix->query(...); #$bar_dsn instance
#or
__PACKAGE__->dbix->query(...); #$bar_dsn instance
#in Your::Note...
$self->dbix->query(...); #$foo_dsn instance
=head2 dbh
Shortcut for C<$self-E<gt>dbix-E<gt>dbh>.
=head2 DEBUG
Flag to enable/disable debug warnings and prepared SQL dumps.
Influences all DBIx::Simple::Class subclasses.
DBIx::Simple::Class->DEBUG(1);
My::Note->find(2)->title('Better Title')->save;
# see in the log what methods are generated for your columns
#and what SQL is thrown to the database.
DBIx::Simple::Class->DEBUG(0);#enough
=head2 QUOTE_IDENTIFIERS
Class attribute. If set to a true value your columns and table names will
be quoted upon first instantiation of your subclass. When you call
C<$self-E<gt>TABLE> it will return a quoted table-name using
L<DBI/quote_identifier>. Same will happen with L</COLUMNS> and L</WHERE>.
This is needed when you have tables and columns with invalid identifier names.
package MyGoups;
use base 'DBIx::Simple::Class';
sub TABLE {'my groups'}#problem - invalid identifier name
sub COLUMNS {['id','group']}#problem - collides with 'group by'
__PACKAGE__->QUOTE_IDENTIFIERS(1);#no problem now
#just works
MyGoups->find(2)->group('name_second')->update;
=head2 is_base_class
Class attribute.
Returns true if the class on which is called is a direct child of
DBIx::Simple::Class.
Using this method we decide to L</BUILD> or not build a class before
instanciating it. We also use it to call L<DBIx::Simple/query>
instead of L<DBIx::Simple::Class/query> when appropriate.
See L<DBIx::Simple::Class/query>.
=head1 METHODS
=head2 new
Constructor.
Accessors listed in COLUMNS are generated on first object construction.
On any subsequent call field-accessors are not generated.
Accepts named parameters or a HASHREF containing named parameters.
Sets the passed parameters as fields (if they exist) as column names.
my $user = My::User->new(
login_name => 'fred',
first_name => 'Fred',
last_name =>'Flintstone');
my $user = My::User->new({
login_name => 'fred',
first_name => 'Fred',
last_name =>'Flintstone'
});#HASHREF accepted too
=head2 new_from_dbix_simple
A constructor called in L<DBIx::Simple/object> and
L<DBIx::Simple/objects>. Basically makes the same as C<new()> without
checking the validity of the field values since they come from the
database and should be valid.
You will never ever need to call this directly but this example is provided
to show how the DBIx::Simple::Class interacts with L<DBIx::Simple>.
See L<DBIx::Simple/Advanced_object_construction>.
my $class = 'My::Model::AdminUser';
# ARRAY (context aware)
my @admins = $dbix->select(
$class->TABLE,
$class->COLUMNS,
$class->WHERE
)->objects($class);
# ARRAYREF (context aware)
my $admins = $dbix->select(
$class->TABLE,
$class->COLUMNS,
$class->WHERE
)->objects($class);
#one row
my $admin = $class->select(id=>123});#see below
My::User->query('SELECT * FROM users WHERE id=?',22)->login_name;
#The above is about 3 times faster than this below
$dbix->query('SELECT * FROM users WHERE id=?',2)
->object(':RowObject')->login_name;
=head2 BUILD
Class method.
This is your real class builder. It is called in constructors only once before L<bless>.
It creates your accessors. Quotes identifiers if needed. You can inject your logic here
if you override this method. In your C<BUILD> do not forget to call
C<$class-E<gt>SUPER::BUILD>. Take a look at the source of this class to make
sure what exactly you need to do. You can also call this method at the end
of your class definition. It will not be called again.
package User;
# your declarations here
__PACKAGE__->BUILD();
1;
=head2 query
Context aware constructor. Very convenient to use with named queries.
Accepts exactly the same arguments as L<DBIx::Simple/query>.
Depending on context fetches and returns an instance or I<fetches and returns a list>
of instances of your class on success.
Returns C<undef> otherwise.
my $user = My::User->query(
'SELECT ' . join (',',My::User->COLUMNS)
. ' FROM ' . My::User->TABLE.' WHERE id=? and disabled=?', 12345, 0);
#or
my $sql = My::User->SQL('A_COMPLEX_SELECT'). ' AND id=?'
my $user = My::User->query($sql,12345)
#or...
my $sql = 'SELECT * FROM users WHERE country_id=?';
#same as $dbix->query($sql,'fr')->objects('My::User');
my @french_users = My::User->query($sql,'fr');
#same as $dbix->query($sql,'fr')->object('My::User');
my $french_user = My::User->query($sql,'fr');
If used by your base class this method does not create a new instance.
In this case it is the same as calling L<DBIx::Simple/query>.
See examples in the distribution directory.
use My;#your base class
My->query(qq|
CREATE TABLE IF NOT EXISTS groups(
id INTEGER PRIMARY KEY AUTOINCREMENT,
group_name VARCHAR(12),
"foo-bar" VARCHAR(13),
data TEXT
)
|);
=head2 select
Context aware constructor. Does the same as
C<$dbix-E<gt>select($table,$columns,$where)-E<gt>object($class)>.
Note that L<SQL::Abstract> B<must be installed>. This is the only method
that requires it. Have in mind that our L</query> is faster than this
and you can use named queries via L</SQL>.
Instantiates an object or list of objects depending on context.
Executes an SQL query based on the parameters.
These parameters are used to construct the C<WHERE> clause for the SQL C<SELECT>
statement. Prepends the class L</WHERE> clause defined by you to the parameters.
If a row is found, puts it in L</data>.
# Build your WHERE using an SQL::Abstract structure:
my $user = MYDLjE::M::User->select(id => $user_id);
=head2 create
my $note = MyNote->create(
title=>'My Title',
description =>'This is a great story.'
);
Inserts a new record and returns the new object.
=head2 select_by_pk
Constructor.
Retrieves a row from the L</TABLE> by L</PRIMARY_KEY>.
Returns an instance of your class.
If the underlying database query was not succesfull L</data> will return undef.
my $user = My::User->select_by_pk(1234);
unless($user->data){
$user->data(name=>'Fred Flintstone', username=>'fred' );
my $id = $user->insert();
}
=head2 find
An alias for L</select_by_pk>.
my $user = My::User->find(1234);
=head2 data
Common getter/setter for all L</COLUMNS>.
Uses internally the specific field getter/setter for each field.
Returns a HASHREF - name/value pairs of the fields.
$self->data(title=>'My Title', description =>'This is a great story.');
my $hash = $self->data;
#or
$self->data($self->dbix->select(TABLE, COLUMNS, $where)->hash);
=head2 save
Intelligent saver. If the object is fresh
( not instantiated via L</new_from_dbix_simple> and L</select>) prepares and
executes an C<INSERT> statement, otherwise preforms an C<UPDATE>.
L</TABLE> and L</COLUMNS> are used to construct the SQL.
Optionally accepts a HASH or HASHREF with column/values pairs.
L</data> is stored as a row in L</TABLE>.
Returns the value of the internally performed operation. See below.
my $note = MyNote->new(title=>'My Title', description =>'This is a great story.');
#do something more...
my $id = $note->save; #insert
#later..
my $ok = $note->title('Your Title')->save;#update
$note->save(description=>$note->description.'.. I forgot something');
#add new record from a web-form
MyNote->new->save($validated_form_data);
MyNote->new($validated_form_data)->save();
=head2 insert
Used internally in L</save>. Can be used when you are sure your object is
not present in the table. Returns the value of the object's L</PRIMARY_KEY>
on success. See L<DBIx::Simple/last_insert_id>.
my $note = MyNote->new(
title=>'My Title',
description =>'This is a great story.'
);
#do something more...
my $last_insert_id = $note->insert;
=head2 update
Used internally in L</save>. Can be used when you are sure your object is
retrieved from the table. Returns true on success.
use My::Model::AdminUser;
my $user = $dbix->query(
'SELECT * FROM users WHERE login_name=?', 'fred'
)->object('My::Model::AdminUser')
$user->first_name('Fred')->last_name('Flintstone');
$user->update;
=head2 delete
There is no C<delete> method. This is on purpose.
You may have different notions of C<delete()>.
For each of your subclasses or in one base class for your project that
inherits from DBIx::Simple::Class you can define your C<delete()> method.
It's easy.
package My::Model
use base qw(DBIx::Simple::Class);
sub delete {
my $self = shift;
my $pk = $self->PRIMARY_KEY;
$self->dbix->query('DELETE FROM '.$self->TABLE." WHERE $pk=?", $self->$pk);
}
#...
package My::Model::User
use base qw(My::Model);
sub WHERE { deleted => 0 }
#...
#a different deleting
sub delete {
$_[0]->deleted(1)->update;#set deleted column to 1
}
1;
#explicit suicide
$user->dbix->query('DELETE FROM users WHERE id=?',$user->id);
#resurrect
$user->insert;
=head2 SQL
A getter/setter for custom SQL code (named queries).
Class method.
You can add key/value pairs in your class and then use them in your application.
The values can be simple strings or subroutine references.
There are already some pre-made entries in DBIx::Simple::Class that you can
use as example implementations. Look at the source for details.
The subroutine references are executed/evaluated only once and their output is
cached for performance.
package My::SiteUser;
use base qw(My::User);#a subclass of DBIx::Simple::Class or My
sub WHERE { {disabled => 0, group_id => 2} }
#these could be very complex and retrieved from a file where you keep them!
__PACKAGE__->SQL(
GUEST => 'SELECT * FROM users WHERE login_name = \'guest\'',
DISABLED => sub{
'SELECT * FROM'.__PACKAGE__->TABLE.' WHERE disabled=?';
}
LAST_N_REGISTERED => __PACKAGE__->SQL('SELECT')
.' order by id desc LIMIT ?, ?'
);
1;
# in your application
$SU ='My::SiteUser';
my $guest = $SU->query($SU->SQL('GUEST'));
my @members = $SU->query($SU->SQL('SELECT'));#allll ;)
my @disabled = $SU->query($SU->SQL('DISABLED'), 1);
my @enabled = $SU->query($SU->SQL('DISABLED'), 0);
=head2 SQL_LIMIT
Produces and returns a LIMIT clause SQL piece.
Currently only MySQL, PostgreSQL and SQLite are supported but writing
your own should be fairly easy. See L<SQL::Abstract::Limit>.
# LIMIT 2
my $two_users = $dbix->query(
$CLASS->SQL('SELECT'). 'AND group_id=? ORDER BY id ASC '.$CLASS->SQL_LIMIT(2),
$group->id
)->objects($CLASS);
# LIMIT 2 OFFSET 2
my $second_two_users = $dbix->query(
$CLASS->SQL('SELECT'). 'AND group_id=? ORDER BY id ASC '.$CLASS->SQL_LIMIT(2,2),
$group->id
)->objects($CLASS);
# LIMIT 2 OFFSET 4
my $third_two_users = $dbix->query(
$CLASS->SQL('SELECT'). 'AND group_id=? ORDER BY id ASC '.$CLASS->SQL_LIMIT(2,4),
$group->id
)->objects($CLASS);
=head1 EXAMPLES
Please look at the test file C<t/01-dbix-simple-class.t> of the distribution
for a wealth of examples.
=head1 AUTHOR
Красимир Беров, C<< <berov at cpan.org> >>
=head1 CREDITS
Jos Boumans for Params::Check
Juerd Waalboer for DBIx::Simple
Nate Wiger and all contributors for SQL::Abstract
=head1 DEPENDENCIES
L<DBIx::Simple>, L<DBI>, L<DBD::SQLite> (for running tests only)
=head1 BUGS
Please report any bugs or feature requests to
L<https://github.com/kberov/DBIx--Simple--Class/issues>.
I will be notified, and then you'll
automatically be notified of progress on your bug as I make changes.
=head1 SUPPORT
You can find documentation for this module with the perldoc command.
perldoc DBIx::Simple::Class
You can also look for information at:
=over 4
=item * The project wiki
L<https://github.com/kberov/DBIx--Simple--Class/wiki>
=item * AnnoCPAN: Annotated CPAN documentation
L<http://annocpan.org/dist/DBIx-Simple-Class>
=item * CPAN Ratings
L<http://cpanratings.perl.org/d/DBIx-Simple-Class>
=item * Search CPAN
L<http://search.cpan.org/dist/DBIx-Simple-Class/>
=back
=head1 SEE ALSO
L<DBIx::Simple>, L<Params::Check> and L<DBI> are used directly and we depend on them.
L<SQL::Abstract> is nice to have but not mandatory.
The modules below are used to cherry-pick ideas and re-implement some of them
in this package.
L<DBIx::Simple::Result::RowObject>, L<DBIx::Simple::OO>,
L<Ima::DBI>, L<DBIx::Class>, L<Data::ObjectDriver>,L<Class::DBI>,
L<Class::DBI::Lite>
L<https://github.com/kberov/MYDLjE>
To easily generate entire classes and save them on disk for later usage
and customization please use L<DBIx::Simple::Class::Schema> which comes
with this distribution.
To use the functionalities provided by this distribution
in a L<Mojolicious> application try L<Mojolicious::Plugin::DSC>.
=head1 LICENSE AND COPYRIGHT
Copyright 2012 - 2013 Красимир Беров (Krasimir Berov).
This program is free software, you can redistribute it and/or modify it under
the terms of the Artistic License version 2.0.
See http://www.opensource.org/licenses/artistic-license-2.0 for more information.