NAME
DBIx::Skinny::Manual::EN::Intro - Intro to DBIx::Skinny
DESCRIPTION
Caution
DBIx::Skinny runs on MySQL, SQLite, PostgreSQL and Oracle.
However it is designed to be extensible to other databases.
Be aware that APIs may change - this is alpha software!
Base Class Defintions
Let's work with an example project called Proj. Your Skinny model for this project would be:
package Proj::Model;
use DBIx::Skinny conect_info => +{
dsn => 'dbi:SQLite:',
username => '',
password => '',
};
1;DSN file usage
If you want to directly use DSN files then you can create your model like this:
package Proj::Model;
use DBIx::Skinny;
1;->connect_info
Proj::Model->connect_info(....); ->connect
Proj::Model->connect(....);Proj::Model->new($connection_info)
my $model = Proj::Model->new($connection_info); ->set_dbh
Proj::Model->set_dbh($dbh);Schema class definition
The recommended way to define your schema classes is automatically via DBIx::Skinny::Schema::Loader. However, read on for info about manual setup.
package Proj::Model::Schema;
use DBIx::Skinny::Schema;
install_table 'user' => schema {
pk 'id';
columns qw/
id guid login_id login_pw name mail created_at updated_at
/;
};
1;Unlike most OR mappers which require a class for every table, in Skinny you can install all your tables in a single class.
Dealing with multi-byte (utf-8) values for a column
Automatic processing of columns is achieved via install_utf8_columns:
package Proj::Model::Schema;
use DBIx::Skinny::Schema;
install_utf8_columns qw/name/;
install_table 'user' => schema {
pk 'id';
columns qw/
id guid login_id login_pw name mail created_at updated_at
/;
};
1;Note that the name column will be processed automatically for all tables, not just the user table.
Inflate/deflate Processing
To enable inflate/deflate processing on the columns created_at and updated_at you can do the following:
package Proj::Model::Schema;
use DBIx::Skinny::Schema;
use DateTime;
use DateTime::Format::Strptime;
use DateTime::Format::MySQL;
use DateTime::TimeZone;
my $timezone = DateTime::TimeZone->new(name => 'Asia/Tokyo');
install_inflate_rule '^.+_at$' => callback {
inflate {
my $value = shift;
my $dt = DateTime::Format::Strptime->new(
pattern => '%Y-%m-%d %H:%M:%S',
time_zone => $timezone,
)->parse_datetime($value);
return DateTime->from_object( object => $dt );
};
deflate {
my $value = shift;
return DateTime::Format::MySQL->format_datetime($value);
};
};
install_table 'user' => schema {
pk 'id';
columns qw/
id guid login_id login_pw name mail created_at updated_at
/;
};
1;Triggers
Skinny supports insert/update/delete triggers:
package Proj::Model::Schema;
use DBIx::Skinny::Schema;
use DateTime;
install_table 'user' => schema {
pk 'id';
columns qw/
id guid login_id login_pw name mail created_at updated_at
/;
trigger pre_insert => sub {
my ( $class, $args ) = @_;
$args->{created_at} ||= DateTime->now;
};
};
1;The available triggers are:
pre_insert, post_insert, pre_update, post_update, pre_delete, post_delete
Triggers are stackable, meaning you can have several triggers of the same type and they will fire in the order of definition.
new
my $model = Proj::Model->new;
$model->do();
Proj::Model->do()connection_info / connect /reconnect / set_dbh
connection_info
connect_info is used to set the database connection info:
Proj::Model->connection_info({
dsn => 'dbi:mysql:test',
username => 'username',
password => 'password'
connect_options => +{
RaiseError => 1,
PrintError => 0,
AutoCommit => 1,
},
});The default connect_options are:
RaiseError: 1
PrintError: 0
AutoCommit: 1
connect
If you want to explictly connect to the database then:
Proj::Model->connect({
dsn => 'dbi:mysql:test',
username => 'username',
password => 'password'
connect_options => +{
RaiseError => 1,
PrintError => 0,
AutoCommit => 1,
},
});reconnect
Proj::Model->reconnect({
dsn => 'dbi:mysql:test',
username => 'username',
password => 'password'
connect_options => +{
RaiseError => 1,
PrintError => 0,
AutoCommit => 1,
},
});set_dbh
Proj::Model->set_dbh($dbh);dbh
my $dbh = Proj::Model->dbh;do
Model->do is a shortcut for $dbh->do
Proj::Model->do(q{
CREATE TABLE foo (
id INT,
name TEXT
)
});insert / create
To insert records into a table:
my $row = Proj::Model->insert('user',{
name => 'nekokak',
mail => 'nekokak _at_ gmail.com',
});->insert() returns a DBIx::Skinny::Row> class, so you can access columns like so:
print $row->name; # nekokak
print $row->mail; # nekokak _at_ gmail.com
my $row = Proj::Model->create('user',{
name => 'nekokak',
mail => 'nekokak _at_ gmail.com',
});update ( $table, $values_href, $where )
Proj::Model->update('user', {name => 'yappo'}, {id => 1})You can also call the ->update method on the Row object returned from insert:
my $row = Proj::Model->insert('user',{
name => 'nekokak',
mail => 'nekokak _at_ gmail.com',
});
$row->update({name => 'yappo'});delete ( $table, $where )
Proj::Model->delete('user', {id => 1});
my $row = Proj::Model->insert('user',{
name => 'nekokak',
mail => 'nekokak _at_ gmail.com',
});
$row->delete;bulk_insert
To insert multiple rows at once:
Proj::Model->bulk_insert('user',
[
{
name => 'nekokak',
mail => 'nekokak _at_ gmail.com',
},
{
name => 'yappo',
mail => 'yappo _at_ example.com',
},
]
);NOTE: triggers are not supported with bulk inserts
find_or_create / find_or_insert
my $row = Proj::Model->find_or_create('user',{
name => 'nekokak',
mail => 'nekokak _at_ gmail.com',
});
my $row = Proj::Model->find_or_insert('user',{
name => 'nekokak',
mail => 'nekokak _at_ gmail.com',
});single / search / search_named /search_by_sql / count
single
Used when you want a single row:
my $row = Proj::Model->single('user',{name => 'nekokak'});search ( $table, $where, $limit_offset )
my $itr = Proj::Model->search('user',
{
name => 'nekokak',
},
{ }
);See DBIx::Class::Manual::EN::Resultset for more information on the $where clause.
search_named ( $sql, $placeholder_bindings, $sprintf_bindings )
This method supports search with named placeholders:
my $itr = Proj::Model->search_named(q{SELECT * FROM user WHERE id > :id}, {id => 1});It is also possible to interpolate data with sprintf control strings:
my $itr = Proj::Model->search_named(q{SELECT * FROM user WHERE id > :id LIMIT %s}, {id => 1}, [10]);A fourth argument can be used to specify a table for query options. (???)
my $itr = Proj::Model->search_named(q{SELECT * FROM user WHERE id > :id LIMIT %s}, {id => 1}, [10], 'user');search_by_sql
Typical DBI-style placeholders:
my $itr = Proj::Model->search_by_sql(q{SELECT * FROM user WHERE id = ?}, [1], 'user');count ( $table, $count_column, $where )
my $count = Porj::Model->count('user' , 'id', {name => 'nekokak'});resultset
Please see DBIx::Skinny::Manual::EN::Resultset
Transactions
Transactions are easy in Skinny:
my $txn = Proj::Model->txn_scope;
my $row = Proj::Model->single('user', {id => 1});
$row->set({name => 'nekokak'});
$row->update;
$txn->commit;Here's another way:
Proj::Model->txn_begin;
my $row = Proj::Model->single('user', {id => 1});
$row->set({name => 'nekokak'});
$row->update;
Proj::Model->txn_commit;
Proj::Model->txn_end;It is expected that the database support transactions. For MySQL this means using the InnoDB engine.
Mixing in methods
Class level
package Proj::Model;
use DBIx::Skinny;
use DBIx::Skinny::Mixin modules => ['+Mixin::Foo'];
1;
package Mixin::Foo;
sub register_method {
+{
foo => sub { 'foo' },
};
}Now
Proj::Model->foo;is possible
Row object level
package Proj::Model::Row::User;
use strict;
use warnings;
use utf8;
use base 'DBIx::Skinny::Row';
sub foo {
say 'foo';
}
1;Now
$row->foo;is possible.
Here's a more practical example:
package Proj::Model::Row::User;
use base 'DBIx::Skinny::Row';
sub blogs {
my $self = shift;
$self->{skinny}->search('blog',{user_id => $self->id});
}Now you can call:
$user->blogs;