DBIx::Simple::Class - Advanced object construction for DBIx::Simple!
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.
#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);
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);
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.
WHERE
{}
package My::PublishedNote; sub WHERE { {data_type => 'note',published=>1 } }; #... use My::PublishedNote; #somewhere in your application my $note = My::PublishedNote->select(id=>12345);
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".
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.
$_CHECKS
sub CHECKS{$_CHECKS}
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'}
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}.
$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'
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
Shortcut for $self->dbix->dbh.
$self->dbix->dbh
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
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.
$self->TABLE
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;
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.
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
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.
new()
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;
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.
BUILD
$class->SUPER::BUILD
package User; # your declarations here __PACKAGE__->BUILD(); 1;
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.
undef
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 ) |);
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".
$dbix->select($table,$columns,$where)->object($class)
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".
SELECT
# Build your WHERE using an SQL::Abstract structure: my $user = MYDLjE::M::User->select(id => $user_id);
my $note = MyNote->create( title=>'My Title', description =>'This is a great story.' );
Inserts a new record and returns the new object.
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(); }
An alias for "select_by_pk".
my $user = My::User->find(1234);
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);
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.
INSERT
UPDATE
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();
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;
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;
There is no delete method. This is on purpose. You may have different notions of delete().
delete
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;
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);
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);
Please look at the test file t/01-dbix-simple-class.t of the distribution for a wealth of examples.
t/01-dbix-simple-class.t
Красимир Беров, <berov at cpan.org>
<berov at cpan.org>
Jos Boumans for Params::Check
Juerd Waalboer for DBIx::Simple
Nate Wiger and all contributors for SQL::Abstract
DBIx::Simple, DBI, DBD::SQLite (for running tests only)
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.
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/
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 use the functionalities provided by this distribution in a Mojolicious application try Mojolicious::Plugin::DSC.
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.
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.