=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.