The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
DBIx-Simple-Class-1.009
River stage one • 1 direct dependent • 1 total dependent
2 ++
/ DBIx::Simple::Class

NAME

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

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

To easily generate entire classes and save them on disk for later usage and customization please use DBIx::Simple::Class::Schema which comes with this distribution.

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

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 :)).

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

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;

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 "BUILD" or not build a class before instanciating it. We also use it to call "query" in DBIx::Simple instead of "query" in DBIx::Simple::Class when appropriate. See "query" in DBIx::Simple::Class.

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

Context aware constructor. Very convenient to use with named queries. Accepts exactly the same arguments as "query" in DBIx::Simple. Depending on context fetches and returns an instance or fetches and returns a list of instances of your class on success. Returns 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 "query" in DBIx::Simple. 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
        )
  |);

select

Context aware constructor. Does the same as $dbix->select($table,$columns,$where)->object($class). 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 or list of objects depending on context. Executes an SQL query based on the parameters. These parameters are used to construct the WHERE clause for the SQL SELECT statement. Prepends the class "WHERE" clause defined by you to the parameters. If a row is found, puts it in "data".

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

create

  my $note = MyNote->create(
      title=>'My Title',
      description =>'This is a great story.'
    );

Inserts a new record and returns the new object.

select_by_pk

Constructor. Retrieves a row from the "TABLE" by "PRIMARY_KEY". Returns an instance of your class. If the underlying database query was not succesfull "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();
    }

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

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

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:

SEE ALSO

DBIx::Simple, Params::Check and DBI are used directly and we depend on them. 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.

DBIx::Simple::Result::RowObject, DBIx::Simple::OO, Ima::DBI, DBIx::Class, Data::ObjectDriver,Class::DBI, Class::DBI::Lite https://github.com/kberov/MYDLjE

To easily generate entire classes and save them on disk for later usage and customization please use DBIx::Simple::Class::Schema which comes with this distribution.

To use the functionalities provided by this distribution in a Mojolicious application try Mojolicious::Plugin::DSC.

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.