NAME

DBIx::Simple::Class - Advanced object construction for DBIx::Simple!

DESCRIPTION

This module is written to replace most of the abstraction stuff from the base model class in the MYDLjE project on github, but can be used independently as well.

The class provides useful methods which simplify representing rows from tables as Perl objects and modifying them. It is not intended to be a full featured ORM. It does not provide relational mapping (yet...). This is left to the developers using this class.

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 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 dependencies besides DBIx::Simple and DBI. See below for details.

SYNOPSIS

#1. In your class representing a description of a row in a database table or view
package My::Model::AdminUser;
#this is your base class. You can put your own common class that extends it here
use base qw(DBIx::Simple::Class);

#sql to be used as table
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 or your own field-setters
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);

CONSTANTS

TABLE

You 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 "select" "update" and "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 "update".

sub TABLE { 'users' }
#using DBIx::Simple select() or query()
dbix->select($class->TABLE, $class->COLUMNS, {%{$class->WHERE}, %$where})->object($class);

WHERE

A HASHREF suitable for passing to "select" in DBIx::Simple. It is also used internally in "select" and "update". Values can be only simple scalars (which may be valid SQL).

Default WHERE clause for your class. Empty "{}" 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);

COLUMNS

You must define it in your subclass. It must return an ARRAYREF with table columns to which the data is written. It is used in "select" in DBIx::Simple 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 "data", "save" etc., you can define aliases that will be used as method names. See "ALIASES".

CHECKS

You must define this subroutine/constant in your class and put in it your $_CHECKS. $_CHECKS is a HASHREF that must conform to the syntax supported by "Template" in Params::Check.

sub CHECKS{$_CHECKS}

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'}

ALIASES

In case you have table columns that collide with some of the package methods like "data", "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 $self->{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'

ATTRIBUTES

dbix

This is a class attribute, shared among all subclasses of DBIx::Simple::Class. This is an DBIx::Simple instance and (as you guessed) provides direct access to the current DBIx::Simple instance (with SQL::Abstract support eventually :)).

DBIx::Simple::Class->dbix( DBIx::Simple->connect(...) );
#later in My::Note
$self->dbix->query(...);#same instance
#or
__PACKAGE__->dbix->query(...);#same instance
dbix->query(...);#same instance

dbh

Shortcut for $self->dbix->dbh.

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

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 $self->TABLE it will return a quoted table-name using "quote_identifier" in DBI. Same will happen with "COLUMNS" and "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;

METHODS

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

new_from_dbix_simple

A constructor called in "object" in DBIx::Simple and "objects" in DBIx::Simple. Basically makes the same as 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 DBIx::Simple. See "Advanced_object_construction" in DBIx::Simple.

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;

BUILD

Class method. This is your real class builder. It is called in constructors only once before bless. It creates your accessors. Quotes identifiers if needed. You can inject your logic here if you override this method. In your BUILD do not forget to call $class->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;

query

A convenient wrapper for $dbix->query($SQL,@bind)->object($class) and constructor. Very convenient to use with named queries. Accepts exactly the same arguments as "query" in DBIx::Simple. Returns an instance of your class on success or 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)

select

A convenient wrapper for $dbix->select($table,$columns,$where)->object($class) and constructor. Note that SQL::Abstract must be installed. This is the only method that requires it. Have in mind that our "query" is faster than this and you can use named queries via "SQL".

Instantiates an object from a saved in the database row by constructing and executing an SQL query based on the parameters. These parameters are used to construct the WHERE clause for the SQL SELECT statement. Prepends the "WHERE" clause defined by you to the parameters. If a row is found, puts it in "data". Returns an instance of your class on success or undef otherwise.

# Build your WHERE using an SQL::Abstract structure:
my $user = MYDLjE::M::User->select(id => $user_id);

select_by_pk

Constructor. Retrieves a row from the "TABLE" by "PRIMARY_KEY". Returns an instance of your class on success or undef otherwise.

my $user = My::User->select_by_pk(1234);

find

An alias for "select_by_pk".

my $user = My::User->find(1234);

data

Common getter/setter for all "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);

save

Intelligent saver. If the object is fresh ( not instantiated via "new_from_dbix_simple" and "select") prepares and executes an INSERT statement, otherwise preforms an UPDATE. "TABLE" and "COLUMNS" are used to construct the SQL. Optionally accepts a HASH or HASHREF with column/values pairs. "data" is stored as a row in "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();

insert

Used internally in "save". Can be used when you are sure your object is not present in the table. Returns the value of the object's "PRIMARY_KEY" on success. See "last_insert_id" in DBIx::Simple.

my $note = MyNote->new(title=>'My Title', description =>'This is a great story.');
#do something more...
my $last_insert_id = $note->insert;

update

Used internally in "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;

delete

There is no delete method. This is on purpose. You may have different notions of delete().

For each of your subclasses or in one base class for your project that inherits from DBIx::Simple::Class you can define your delete() method. It's easy.

package My::Model
use base qw(DBIx::Simple::Class);

sub delete {
  my $pk = $self->PRIMARY_KEY;
  my $self = shift;
  $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;

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 this base 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
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);

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 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);

EXAMPLES

Please look at the test file t/01-dbix-simple-class.t of the distribution for a wealth of examples.

AUTHOR

Красимир Беров, <berov at cpan.org>

CREDITS

Jos Boumans for Params::Check

Juerd Waalboer for DBIx::Simple

Nate Wiger and all contributors for SQL::Abstract

DEPENDENCIES

DBIx::Simple, DBI, DBD::SQLite (for running tests only)

BUGS

Please report any bugs or feature requests to 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.

SUPPORT

You can find documentation for this module with the perldoc command.

perldoc DBIx::Simple::Class

You can also look for information at:

The project wiki

https://github.com/kberov/DBIx--Simple--Class/wiki
AnnoCPAN: Annotated CPAN documentation

http://annocpan.org/dist/DBIx-Simple-Class
CPAN Ratings

http://cpanratings.perl.org/d/DBIx-Simple-Class
Search CPAN

http://search.cpan.org/dist/DBIx-Simple-Class/

LICENSE AND COPYRIGHT

This program is free software; you can redistribute it and/or modify it under the terms of either: the GNU General Public License as published by the Free Software Foundation; or the Artistic License.

See http://dev.perl.org/licenses/ for more information.

To install DBIx::Simple::Class, copy and paste the appropriate command in to your terminal.

cpanm

cpanm DBIx::Simple::Class

CPAN shell

perl -MCPAN -e shell
install DBIx::Simple::Class

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)