DBIx::Skinny::Manual::EN::Intro - Intro to DBIx::Skinny
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!
Let's work with an example project called Proj. Your Skinny model for this project would be:
Proj
package Proj::Model; use DBIx::Skinny conect_info => +{ dsn => 'dbi:SQLite:', username => '', password => '', }; 1;
If you want to directly use DSN files then you can create your model like this:
package Proj::Model; use DBIx::Skinny; 1;
Proj::Model->connect_info(....);
Proj::Model->connect(....);
my $model = Proj::Model->new($connection_info);
Proj::Model->set_dbh($dbh);
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.
Automatic processing of columns is achieved via install_utf8_columns:
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.
name
user
To enable inflate/deflate processing on the columns created_at and updated_at you can do the following:
created_at
updated_at
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;
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
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.
my $model = Proj::Model->new; $model->do(); Proj::Model->do()
connect_info is used to set the database connection info:
connect_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:
connect_options
RaiseError: 1
PrintError: 0
AutoCommit: 1
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, }, });
Proj::Model->reconnect({ dsn => 'dbi:mysql:test', username => 'username', password => 'password' connect_options => +{ RaiseError => 1, PrintError => 0, AutoCommit => 1, }, });
my $dbh = Proj::Model->dbh;
Model->do is a shortcut for $dbh->do
Model->do
$dbh->do
Proj::Model->do(q{ CREATE TABLE foo ( id INT, name TEXT ) });
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:
->insert()
print $row->name; # nekokak print $row->mail; # nekokak _at_ gmail.com my $row = Proj::Model->create('user',{ name => 'nekokak', mail => 'nekokak _at_ gmail.com', });
Proj::Model->update('user', {name => 'yappo'}, {id => 1})
You can also call the ->update method on the Row object returned from insert:
->update
my $row = Proj::Model->insert('user',{ name => 'nekokak', mail => 'nekokak _at_ gmail.com', }); $row->update({name => 'yappo'});
Proj::Model->delete('user', {id => 1}); my $row = Proj::Model->insert('user',{ name => 'nekokak', mail => 'nekokak _at_ gmail.com', }); $row->delete;
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
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', });
Used when you want a single row:
my $row = Proj::Model->single('user',{name => 'nekokak'});
my $itr = Proj::Model->search('user', { name => 'nekokak', }, { } );
See DBIx::Class::Manual::EN::Resultset for more information on the $where clause.
$where
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');
Typical DBI-style placeholders:
my $itr = Proj::Model->search_by_sql(q{SELECT * FROM user WHERE id = ?}, [1], 'user');
my $count = Porj::Model->count('user' , 'id', {name => 'nekokak'});
Please see DBIx::Skinny::Manual::EN::Resultset
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.
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
package Proj::Model::Row::User; use strict; use warnings; use utf8; use base 'DBIx::Skinny::Row'; sub foo { say 'foo'; } 1;
$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;
To install DBIx::Skinny, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::Skinny
CPAN shell
perl -MCPAN -e shell install DBIx::Skinny
For more information on module installation, please visit the detailed CPAN module installation guide.