The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.

NAME

DBIx::Skinny - simple DBI wrapper/ORMapper

SYNOPSIS

create your db model base class.

package Your::Model;
use DBIx::Skinny connect_info => {
    dsn => 'dbi:SQLite:',
    username => '',
    password => '',
};
1;

create your db schema class. See DBIx::Skinny::Schema for docs on defining schema class.

package Your::Model::Schema;
use DBIx::Skinny::Schema;

install_table user => schema {
    pk 'id';
    columns qw/
        id
        name
    /;
};
1;

in your script.

use Your::Model;

my $skinny = Your::Model->new;
# insert new record.
my $row = $skinny->insert('user',
    {
        id   => 1,
    }
);
$row->update({name => 'nekokak'});

$row = $skinny->search_by_sql(q{SELECT id, name FROM user WHERE id = ?}, [ 1 ]);
$row->delete('user');

DESCRIPTION

DBIx::Skinny is simple DBI wrapper and simple O/R Mapper. It aims to be lightweight, with minimal dependencies so it's easier to install.

ARCHITECTURE

DBIx::Skinny classes are comprised of three distinct components:

MODEL

The model is where you say

package MyApp::Model;
use DBIx::Skinny;

This is the entry point to using DBIx::Skinny. You connect, insert, update, delete, select stuff using this object.

SCHEMA

The schema is a simple class that describes your table definitions. Note that this is different from DBIx::Class terms. DBIC's schema is equivalent to DBIx::Skinny's model + schema, where the actual schema information is scattered across the result classes.

In DBIx::Skinny, you simply use DBIx::Skinny::Schema's domain specific languaage to define a set of tables

package MyApp::Model::Schema;
use DBIx::Skinny::Schema;

install_table $table_name => schema {
    pk $primary_key_column;
    columns qw(
        column1
        column2
        column3
    );
}

... and other tables ...

ROW

Unlike DBIx::Class, you don't need to have a set of classes that represent a row type (i.e. "result" classes in DBIC terms). In DBIx::Skinny, the row objects are blessed into anonymous classes that inherit from DBIx::Skinny::Row, so you don't have to create these classes if you just want to use some simple queries.

If you want to define methods to be performed by your row objects, simply create a row class like so:

package MyApp::Model::Row::CamelizedTableName;
use base qw(DBIx::Skinny::Row);

Note that your table name will be camelized using String::CamelCase.

METHODS

DBIx::Skinny provides a number of methods to all your classes,

$skinny->new([\%connection_info])

create your skinny instance. It is possible to use it even by the class method.

$connection_info is optional argment.

When $connection_info is specified, new method connect new DB connection from $connection_info.

When $connection_info is not specified, it becomes use already setup connection or it doesn't do at all.

example:

my $db = Your::Model->new;

or

# connect new database connection.
my $db = Your::Model->new(+{
    dsn      => $dsn,
    username => $username,
    password => $password,
    connect_options => $connect_options,
});

or

my $dbh = DBI->connect();
my $db = Your::Model->new(+{
    dbh => $dbh,
});
$skinny->insert($table_name, \%row_data)

insert new record and get inserted row object.

if insert to table has auto increment then return $row object with fill in key column by last_insert_id.

example:

my $row = Your::Model->insert('user',{
    id   => 1,
    name => 'nekokak',
});
say $row->id; # show last_insert_id()

or

my $db = Your::Model->new;
my $row = $db->insert('user',{
    id   => 1,
    name => 'nekokak',
});
$skinny->create($table_name, \%row_data)

insert method alias.

$skinny->replace($table_name, \%row_data)

The data that already exists is replaced.

example:

Your::Model->replace('user',{
    id   => 1,
    name => 'tokuhirom',
});

or

my $db = Your::Model->new;
my $row = $db->replace('user',{
    id   => 1,
    name => 'tokuhirom',
});
$skinny->bulk_insert($table_name, \@rows_data)

Accepts either an arrayref of hashrefs. each hashref should be a structure suitable forsubmitting to a Your::Model->insert(...) method.

insert many record by bulk.

example:

Your::Model->bulk_insert('user',[
    {
        id   => 1,
        name => 'nekokak',
    },
    {
        id   => 2,
        name => 'yappo',
    },
    {
        id   => 3,
        name => 'walf443',
    },
]);
$skinny->update($table_name, \%update_row_data, [\%update_condition])

$update_condition is optional argment.

update record.

example:

my $update_row_count = Your::Model->update('user',{
    name => 'nomaneko',
},{ id => 1 });

or

# see) DBIx::Skinny::Row's POD
my $row = Your::Model->single('user',{id => 1});
$row->update({name => 'nomaneko'});
$skinny->update_by_sql($sql, [\@bind_values])

update record by specific sql. return update row count.

example:

my $update_row_count = Your::Model->update_by_sql(
    q{UPDATE user SET name = ?},
    ['nomaneko']
);
$skinny->delete($table, \%delete_condition)

delete record. return delete row count.

example:

my $delete_row_count = Your::Model->delete('user',{
    id => 1,
});

or

# see) DBIx::Skinny::Row's POD
my $row = Your::Model->single('user', {id => 1});
$row->delete
$skinny->delete_by_sql($sql, \@bind_values)

delete record by specific sql. return delete row count.

example:

my $delete_row_count = Your::Model->delete_by_sql(
    q{DELETE FROM user WHERE id = ?},
    [1]
});
$skinny->find_or_create($table, \%values)

create record if not exsists record.

return DBIx::Skinny::Row's instance object.

example:

my $row = Your::Model->find_or_create('usr',{
    id   => 1,
    name => 'nekokak',
});

NOTICE: find_or_create has bug.

reproduction example:

my $row = Your::Model->find_or_create('user',{
    id   => 1,
    name => undef,
});

In this case, it becomes an error by insert.

If you want to do the same thing in this case,

my $row = Your::Model->single('user', {
    id   => 1,
    name => \'IS NULL',
})
unless ($row) {
    Your::Model->insert('user', {
        id => 1,
    });
}

Because the interchangeable rear side is lost, it doesn't mend.

$skinny->find_or_insert($table, \%values)

find_or_create method alias.

$skinny->search($table_name, [\%search_condition, [\%search_attr]])

simple search method. search method get DBIx::Skinny::Iterator's instance object.

see DBIx::Skinny::Iterator

get iterator:

my $itr = Your::Model->search('user',{id => 1},{order_by => 'id'});

get rows:

my @rows = Your::Model->search('user',{id => 1},{order_by => 'id'});

See "ATTRIBUTES" for more information for \%search_attr.

$skinny->search_rs($table_name, [\%search_condition, [\%search_attr]])

simple search method. search_rs method always get DBIx::Skinny::Iterator's instance object.

This method does the same exact thing as search() except it will always return a iterator, even in list context.

$skinny->single($table_name, \%search_condition)

get one record. give back one case of the beginning when it is acquired plural records by single method.

my $row = Your::Model->single('user',{id =>1});
$skinny->resultset(\%options)

resultset case:

my $rs = Your::Model->resultset(
    {
        select => [qw/id name/],
        from   => [qw/user/],
    }
);
$rs->add_where('name' => {op => 'like', value => "%neko%"});
$rs->limit(10);
$rs->offset(10);
$rs->order({ column => 'id', desc => 'DESC' });
my $itr = $rs->retrieve;
$skinny->count($table_name, $target_column, [\%search_condition])

get simple count

my $cnt = Your::Model->count('user' => 'id', {age => 30});
$skinny->search_named($sql, [\%bind_values, [\@sql_parts, [$table_name]]])

execute named query

my $itr = Your::Model->search_named(q{SELECT * FROM user WHERE id = :id}, {id => 1});

If you give ArrayRef to value, that is expanded to "(?,?,?,?)" in SQL. It's useful in case use IN statement.

# SELECT * FROM user WHERE id IN (?,?,?);
# bind [1,2,3]
my $itr = Your::Model->search_named(q{SELECT * FROM user WHERE id IN :ids}, {ids => [1, 2, 3]});

If you give \@sql_parts,

# SELECT * FROM user WHERE id IN (?,?,?) AND unsubscribed_at IS NOT NULL;
# bind [1,2,3]
my $itr = Your::Model->search_named(q{SELECT * FROM user WHERE id IN :ids %s}, {ids => [1, 2, 3]}, ['AND unsubscribed_at IS NOT NULL']);

If you give table_name. It is assumed the hint that makes DBIx::Skinny::Row's Object.

$skinny->search_by_sql($sql, [\@bind_vlues, [$table_name]])

execute your SQL

my $itr = Your::Model->search_by_sql(q{
    SELECT
        id, name
    FROM
        user
    WHERE
        id = ?
},[ 1 ]);

If $opt_table_info is specified, it set table infomation to result iterator. So, you can use table row class to search_by_sql result.

$skinny->txn_scope

get transaction scope object.

do {
    my $txn = Your::Model->txn_scope;

    $row->update({foo => 'bar'});

    $txn->commit;
}

An alternative way of transaction handling based on DBIx::Skinny::Transaction.

If an exception occurs, or the guard object otherwise leaves the scope before $txn->commit is called, the transaction will be rolled back by an explicit "txn_rollback" call. In essence this is akin to using a "txn_begin"/"txn_commit" pair, without having to worry about calling "txn_rollback" at the right places. Note that since there is no defined code closure, there will be no retries and other magic upon database disconnection.

$skinny->hash_to_row($table_name, $row_data_hash_ref)

make DBIx::Skinny::Row's class from hash_ref.

my $row = Your::Model->hash_to_row('user',
    {
        id   => 1,
        name => 'lestrrat',
    }
);
$skinny->data2itr($table_name, \@rows_data)

DBIx::Skinny::Iterator is made based on \@rows_data.

my $itr = Your::Model->data2itr('user',[
    {
        id   => 1,
        name => 'nekokak',
    },
    {
        id   => 2,
        name => 'yappo',
    },
    {
        id   => 3,
        name => 'walf43',
    },
]);

my $row = $itr->first;
$row->insert; # inser data.
$skinny->find_or_new($table_name, \%row_data)

Find an existing record from database.

If none exists, instantiate a new row object and return it.

The object will not be saved into your storage until you call "insert" in DBIx::Skinny::Row on it.

my $row = Your::Model->find_or_new('user',{name => 'nekokak'});
$skinny->do($sql, [$option, $bind_values])

execute your query.

See) http://search.cpan.org/dist/DBI/DBI.pm#do

$skinny->dbh

get database handle.

$skinny->connect([\%connection_info])

connect database handle.

If you give \%connection_info, create new database connection.

$skinny->reconnect(\%connection_info)

re connect database handle.

If you give \%connection_info, create new database connection.

$skinny->disconnect()

Disconnects from the currently connected database.

$skinny->suppress_row_objects($flag)

set row object creation mode.

ATTRIBUTES

order_by
{ order_by => [ { id => 'desc' } ] }
# or
{ order_by => { id => 'desc' } }
# or 
{ order_by => 'name' }
for_update
{ for_update => 1 }

ENVIRONMENT VARIABLES

SKINNY_PROFILE

for debugging sql.

see DBIx::Skinny::Profile

$ SKINNY_PROFILE=1 perl ./your_script.pl

SKINNY_TRACE

for debugging sql.

see DBIx::Skinny::Profiler::Trace

$ SKINNY_TRACE=1 perl ./your_script.pl

TRIGGER

my $row = $db->insert($args);
# pre_insert: ($db, $args, $table_name)
# post_insert: ($db, $row, $table_name)

my $updated_rows_count = $db->update($args);
my $updated_rows_count = $row->update(); # example $args: +{ id => $row->id }
# pre_update: ($db, $args, $table_name)
# post_update: ($db, $updated_rows_count, $table_name)

my $deleted_rows_count = $db->delete($args);
my $deleted_rows_count = $row->delete(); # example $args: +{ id => $row->id }
# pre_delete: ($db, $args, $table_name)
# post_delete: ($db, $deleted_rows_count, $table_name)

BUGS AND LIMITATIONS

No bugs have been reported.

AUTHOR

Atsushi Kobayashi <nekokak __at__ gmail.com>

CONTRIBUTORS

walf443 : Keiji Yoshimi

TBONE : Terrence Brannon

nekoya : Ryo Miyake

oinume: Kazuhiro Oinuma

fujiwara: Shunichiro Fujiwara

pjam: Tomoyuki Misonou

magicalhat

Makamaka Hannyaharamitu

nihen: Masahiro Chiba

lestrrat: Daisuke Maki

tokuhirom: Tokuhiro Matsuno

SUPPORT

irc: #dbix-skinny@irc.perl.org

ML: http://groups.google.com/group/dbix-skinny

REPOSITORY

git clone git://github.com/nekokak/p5-dbix-skinny.git  

LICENCE AND COPYRIGHT

Copyright (c) 2010, Atsushi Kobayashi <nekokak __at__ gmail.com>. All rights reserved.

This module is free software; you can redistribute it and/or modify it under the same terms as Perl itself. See perlartistic.