Skip to content

Latest commit

 

History

History
470 lines (297 loc) · 16 KB

README.pod

File metadata and controls

470 lines (297 loc) · 16 KB
Raw

NAME

Kappa - super-light ORM

SYNOPSIS

use Kappa;
use DBI;
my $dbh = DBI->connect($dsn, $id, $pw);
my $db = Kappa->new($dbh);
my $row_obj = $db->select('SOME_TABLE', { id => 123 });
print $row_obj->id, $row_obj->value;

DESCRIPTION

Kappa is a super-light ORM. You can use this module without defining schema-class and if you want to define table-related logic, you can define table-class for each table.

METHODS

new($dbh, [$options_href])

create instance.

available options are as follows.

  • row_namespace (string, default 'Kappa::Row') : namespace for row object.

  • table_namespace (string, default 'Kappa') : namespace for table class.

  • iterator_namespace (string, default 'Kappa::Iterator') : namespace for iterator.

  • row_object_enable (BOOL, default 1(TRUE)) : Row object is generated or not

my $dbh = DBI->connect($dsn, $user, $pass);
my $db = Kappa->new($dbh, {
    row_namespace      => 'MyProj::Row',
    table_namespace    => 'MyProj::Table',
    iterator_namespace => 'MyProj::Iterator',
});

model($table_name)

create instance for defined table class. if table class for $table_name is not found, return default class.

my $db = Kappa->new($dbh, {
    table_namespace => 'MyProj::Table',
});
my $db_for_order = $db->model('Order'); #return table MyProj::Table::Order table class(if defined)

In this case, Instance of MyProj::Table::Order will be returned. If MyProj::Table::Order is not defined, return MyProj::Table instance if defiend MyProj::Table and if not defined both of them, return Kappa instance.

row_object_enable($status)

$status: BOOL enable or disable making row object. if return value is required, this value is guard object.

my $db = Kappa->new($dbh);
{
    my $guard = $db->row_object_enable(0); #set false to row_object_enable
    my $row = $db->select('SOME_TABLE', { id => 123 }); # $row is not row_object (returns hashref in this case)
}
my $row = $db->select('SOME_TABLE', { id => 123 }) # row object is returned.(row_object_enable is currently TRUE)

METHODS FROM PARENT CLASS(SQL::Executor)

folowing methods are delived from SQL::Executor. Methods named select*_itr return Iterator using Kappa::Iterator(by default it is the same as SQL::Executor::Iterator), and other select* methods return Row object(Kappa::Row or child of the one).

select($table_name, $where, $option)

return one Row object if scalar context is expected, or in array context, return arrays of Row objects. like this,

my $db = Kappa->new($dbh);
my @rows = $db->select('SOME_TABLE', { value => 'aaa' }); # return Row objects
my $row = $db->select('SOME_TABLE', { value => 'aaa' });  # return a Row object

if table class is defined and select is called from table class, parameter $table_name is optional. like this,

my $db = Kappa->new($dbh, { table_namespace => 'MyProj::Table'});
my $db_for_sometable = $db->model('SOME_TABLE');
my @rows1 = $db_for_sometable->select({ value => 'aaa' }); #omit $table_name
my @rows2 = $db_for_sometable->select('SOME_TABLE', { value => 'aaa' }); #you can also specify table name

input parameter $where accepts hash_ref, array_ref, or SQL::Maker::Condition instance. see SQL::Maker select for details. input parameter $options is the same as SQL::Maker's one.

select_row($table_name, $where, $option)

return one Row object. if found more than one row, return the first one.

if table class is defined and select is called from table class, parameter $table_name is optional. like this,

my $db = Kappa->new($dbh, { table_namespace => 'MyProj::Table'});
my $db_for_sometable = $db->model('SOME_TABLE');
my $row1 = $db_for_sometable->select_row({ value => 'aaa' }); #omit $table_name
my $row2 = $db_for_sometable->select_row('SOME_TABLE', { value => 'aaa' }); #you can also specify table name

select_all($table_name, $where, $option)

return array of Row objects.

if table class is defined and select is called from table class, parameter $table_name is optional. like this,

my $db = Kappa->new($dbh, { table_namespace => 'MyProj::Table'});
my $db_for_sometable = $db->model('SOME_TABLE');
my @rows1 = $db_for_sometable->select_all({ value => 'aaa' }); #omit $table_name
my @rows2 = $db_for_sometable->select_all('SOME_TABLE', { value => 'aaa' }); #you can also specify table name

select_itr($table_name, $where, $option)

return iterator that contains Row object. Iterator is instance of Kappa::Iterator

my $db = Kappa->new($dbh, { table_namespace => 'MyProj::Table'});
my $itr = $db->select_itr({ value => 'aaa' });
while ( my $row = $itr->next ) { # $row is Row object
    ...# using Row object
}

if table class is defined and select is called from table class, parameter $table_name is optional. like this,

my $db = Kappa->new($dbh, { table_namespace => 'MyProj::Table'});
my $db_for_sometable = $db->model('SOME_TABLE');
my $itr1 = $db_for_sometable->select_itr({ value => 'aaa' }); #omit $table_name
my $itr2 = $db_for_sometable->select_itr('SOME_TABLE', { value => 'aaa' }); #you can also specify table name

select_named($sql, $params_href, $table_name)

run select by sql using named placeholder. In scalar context, return one row object. In array context, return array of row objects.

my $db = Kappa->new($dbh, { table_namespace => 'MyProj::Table'});
my $row = $db->select_named('SELECT id, value FROM SOME_TABLE WHERE value = :value', { value => 'aaa' });

if table class is defined and SQL is written in __DATA__ section. $sql is omittable and used method name as SQL name.

package My::Table::Class;
sub using_select_named {
    my ($self, $id) = @_;
    return $self->select_named({ id => $id });# '@@ using_select_name' is used
}
1;
__DATA__
@@ using_select_named
SELECT * FROM MyTable WHERE id = :id;

select_row_named($sql, $params_href, $table_name)

run select by sql using named placeholder. and return one row object.

my $db = Kappa->new($dbh, { table_namespace => 'MyProj::Table'});
my $row = $db->select_row_named('SELECT id, value FROM SOME_TABLE WHERE value = :value', { value => 'aaa' });

if table class is defined and SQL is written in __DATA__ section. $sql is omittable and used method name as SQL name.

select_all_named($sql, $params_href, $table_name)

run select by sql using named placeholder. and return array of row objects.

my $db = Kappa->new($dbh, { table_namespace => 'MyProj::Table'});
my @rows = $db->select_all_named('SELECT id, value FROM SOME_TABLE WHERE value = :value', { value => 'aaa' });

if table class is defined and SQL is written in __DATA__ section. $sql is omittable and used method name as SQL name.

select_itr_named($sql, $params_href, $table_name)

run select by sql using named placeholder. and return iterator that contains row objects. Iterator is instance of Kappa::Iterator

my $db = Kappa->new($dbh, { table_namespace => 'MyProj::Table'});
my $itr = $db->select_itr_named('SELECT id, value FROM SOME_TABLE WHERE value = :value', { value => 'aaa' });
while ( my $row = $itr->next ) { # $row is row object
    ... #using row object
}

if table class is defined and SQL is written in __DATA__ section. $sql is omittable and used method name as SQL name.

select_by_sql($sql, \@binds, $table_name)

run select by sql using normal placeholder('?').

if table class is defined and SQL is written in __DATA__ section. $sql is omittable and used method name as SQL name.

select_row_by_sql($sql, \@binds, $table_name)

run select by sql using normal placeholder('?').

if table class is defined and SQL is written in __DATA__ section. $sql is omittable and used method name as SQL name.

select_all_by_sql($sql, \@binds, $table_name)

run select by sql using normal placeholder('?').

if table class is defined and SQL is written in __DATA__ section. $sql is omittable and used method name as SQL name.

select_itr_by_sql($sql, \@binds, $table_name)

run select by sql using normal placeholder('?').

if table class is defined and SQL is written in __DATA__ section. $sql is omittable and used method name as SQL name.

select_with_fields($table_name, $fields_aref, $where, $option)

same as select but you can specify field name by using $fields_aref. Note that unspecified field value is not returned in row object.

select_row_with_fields($table_name, $fields_aref, $where, $option)

same as select_row but you can specify field name by using $fields_aref.

select_all_with_fields($table_name, $fields_aref, $where, $option)

same as select_all but you can specify field name by using $fields_aref.

select_itr_with_fields($table_name, $fields_aref, $where, $option)

same as select_itr but you can specify field name by using $fields_aref.

insert($table_name, $values)

execute INSERT statment. return value is nothing.

if table class is defined and select is called from table class, parameter $table_name is optional. like this,

my $db = Kappa->new($dbh, { table_namespace => 'MyProj::Table'});
my $db_for_sometable = $db->model('SOME_TABLE');
$db_for_sometable->insert({ id => 123, value => 'aaa' }); #omit $table_name

insert_multi($table_name, @args)

execute bulk insert using SQL::Maker's insert_multi. return value is nothing.

if table class is defined and select is called from table class, parameter $table_name is optional. like this,

my $db = Kappa->new($dbh, { table_namespace => 'MyProj::Table'});
my $db_for_sometable = $db->model('SOME_TABLE');
$db_for_sometable->insert_multi({ id => 123, value => 'aaa' }, { id => 124, value => 'bbb' }); #omit $table_name

insert_on_duplicate($table_name, $insert_href, $update_href)

execute INSERT ... ON DUPLICATE KEY UPDATE using SQL::Maker's insert_on_duplicate. return value is nothing.

if table class is defined and select is called from table class, parameter $table_name is optional. like this,

my $db = Kappa->new($dbh, { table_namespace => 'MyProj::Table'});
my $db_for_sometable = $db->model('SOME_TABLE');
$db_for_sometable->insert_on_duplicate({ id => 123, value => 'aaa' }, { value => 'bbb' }); #omit $table_name

delete($table_name, $where)

execute DELETE statment. return value is nothing.

if table class is defined and select is called from table class, parameter $table_name is optional. like this,

my $db = Kappa->new($dbh, { table_namespace => 'MyProj::Table'});
my $db_for_sometable = $db->model('SOME_TABLE');
$db_for_sometable->delete({ id => 123 }); #omit $table_name

update($table_name, $set, $where)

execute UPDATE statment. return value is nothing.

if table class is defined and select is called from table class, parameter $table_name is optional. like this,

my $db = Kappa->new($dbh, { table_namespace => 'MyProj::Table'});
my $db_for_sometable = $db->model('SOME_TABLE');
$db_for_sometable->update({ value => 'aaa' }, { id => 123 }); #omit $table_name

execute_query($sql, \@binds)

run sql statement and returns statement handler($sth)

if table class is defined and SQL is written in __DATA__ section. $sql is omittable and used method name as SQL name.

execute_query_named($sql, $params_href)

run sql statement with named placeholder and returns statement handler($sth)

if table class is defined and SQL is written in __DATA__ section. $sql is omittable and used method name as SQL name.

sql()

sql_from_data_section($sql_name)

fetch SQL from __DATA__ section in Table class. For example, write SQL in Table class like this

package MyProj::Table::Order;
use parent qw(Kappa);
use strict;
use warnings;

sub select_from_order_no {
    my ($self, $order_no) = @_;
    my $sql = $self->sql_from_data_section('select_from_order_no');
    return $self->select_all_named($sql, { order_no => $order_no });
}
1;
__DATA__

@@ select_from_order_no
SELECT *
  FROM Order
  WHERE order_no = :order_no
;

if $sql_name is omitted, method name is used by default. in this case(in select_from_order_no() method),

my $sql = $self->sql_from_data_section('select_from_order_no');

is same as

my $sql = $self->sql_from_data_section;

sql() is method alias to sql_from_data_section()

handle_exception($sql, $binds_aref, $err_message)

When SQL error occured, this method is call-backed. you can override this method to customize error message and error handling.

DEFINE CUSTOMIZED ROW CLASS

You can define Row class specified in specified in row_namespace at new(). for example, define MyProj::Row::Order like this,

package MyProj::Row::Order;
use parent qw(Kappa::Row);
use strict;
use warnings;

sub price_with_tax {
    my ($self) = @_
    return $self->price * $self->tax;
}

1;

using this row object like this,

my $db = Kappa->new($dbh, { row_namespace => 'MyProj::Row' });
my @rows = $db->select('Order', { customer_name => 'some_customer' });
for my $row ( @rows ) {
    print "$row->product_name : $row->price_with_tax \n"; # enable to use customized method(price_with_tax)
}

What row object can do is only call calling customized method (in this case calling price_with_tax()).

DEFINE CUSTOMIZED TABLE CLASS

You can also define Table class specified in table_namespace at new(). for example, define MyProj::Table::Order like this,

package MyProj::Table::Order;
use parent qw(Kappa);
use strict;
use warnings;

sub select_using_very_complex_sql {
    my($self, $condition_href) = @_;
    my ($sql, @binds)  = $self->_very_complex_sql($condition_href);
    return $self->select_by_sql($sql, \@binds, $self->table_name); #recommend to pass $self->table_name to make row object for this table
}
sub _very_complex_sql { ... }

using this table class like this,

my $db = Kappa->new($dbh, { table_namespace => 'MyProj::Table' });
my $db_for_order = $db->model('Order');
my @rows = $db_for_order->select_using_very_complex_sql($condition_href);

DEFINE CUSTOMIZED ITERATOR

You can also define iterator class specified in iterator_namespace at new(). for example, define MyProj::Iterator::Order like this,

package MyProj::Iterator::Order;
use parent qw(Kappa::Iterator);
use strict;
use warnings;

sub sum_price {
    my($self) = @_;
    my $result = 0;
    while ( my $row = $self->next ) {
        $result += $row->price;
    }
    return $result;
}

using this iterator like this,

my $db = Kappa->new($dbh, { iterator_namespace => 'MyProj::Iterator' });
my $db_for_order = $db->model('Order');
my $itr = $db_for_select->some_order(...);
my $sum_price = $itr->sum_price();

If MyProj::Iterator::Order is not defined, instance of MyProj::Iterator will be returned. And if MyProj::Iterator is not defined, Kappa::Iterator will be returned.

How to use Transaction.

When create instance using connect() method, you can use DBIx::Handler's transaction management,

use Kappa;
my $db = Kappa->connect($dsn, $id, $pass);
my $txn = $db->handler->txn_scope();
$db->insert('SOME_TABLE', { id => 124, value => 'xxxx'} );
$db->insert('SOME_TABLE', { id => 125, value => 'yyy'} );
$txn->commit();

Or You can use DBI's transaction (begin_work and commit).

use DBI;
use Kappa
my $dbh = DBI->connect($dsn, $id, $pass);
my $db = Kappa->new($dbh);
$dbh->begin_work();
$db->insert('SOME_TABLE', { id => 124, value => 'xxxx'} );
$db->insert('SOME_TABLE', { id => 125, value => 'yyy' } );
$dbh->commit();

Or you can also use transaction management modules like DBIx::TransactionManager.

use DBI;
use Kappa;
use DBIx::TransactionManager;
my $dbh = DBI->connect($dsn, $id, $pass);
my $db = Kappa->new($dbh);
my $tm = DBIx::TransactionManager->new($dbh);
my $txn = $tm->txn_scope;
$db->insert('SOME_TABLE', { id => 124, value => 'xxxx'} );
$db->insert('SOME_TABLE', { id => 125, value => 'yyy' } );
$txn->commit;

AUTHOR

Takuya Tsuchida <tsucchi {at} cpan.org>

SEE ALSO

SQL::Executor

LICENSE

Copyright (C) Takuya Tsuchida

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