root/docs/kan/080127-sql-declare/main.txt

SQL::Declare

<div style="text-align: right">
ふしはらかん
</div>
----
*PerlのOR mapper色々
- DBIx::Class
- Class::DBI
- Data::ObjectDriver
- Jifty::DBI
- etc
----
*DBIx::Classの不満点
- 複雑な検索条件がカオス
- キャッシュがめんどくさい
- 拡張やhookが曲者(Class::C3)
----
ためしに作ってみよう
----
*SQLAlchemy
- python のOR mapper
- SQLの記述をpythonのcodeっぽく書く機能がある
----
SQL
>||
SELECT 
    user.*, address.* 
FROM 
    user, address
WHERE
    user.name LIKE 'j%'            AND
    user.id   =    address.user_id AND
    (
        address.email_address = 'foo@example.com' OR
        address.email_address = 'bar@example.com'
    ) 
----
DBIx::Class(SQL::Abstract)
>||
({
    'user.name' => { 'LIKE' => 'j%' },
    'user.id'    => 'address.user_id',
    -or => [
        'address.email_address' => 'foo@example.com',
        'address.email_address' => 'bar@example.com',
    ],
},
{
    form => [qw/user address/],
    select => [qw/user.* address.*/],
}
||<
----
SQLAlchemy
>||
select([user, address], and_(user.c.name.like('j%'), user.c.id==address.c.user_id,
    or_(address.c.email_address=='foo@example.com', address.c.email_address=='bar@example.com'))
||<
----
SQL::Declare
>||
Select([$user,$address],
    and_(
        $user->c->name->like('j%'),
        $user->c->id==$address->c->user_id,
        or_(
            $address->email_address=='foo@example.com',
            $address->email_address=='bar@example.com'
        ),
    ),
);
||<
----
*利点
- 自然に書ける部分が増える
- schemaのmetadataと組み合わせてチェックとか
----
SQL::Abstract
>||
'user.name' => \'user.email'
||<

SQL::Declare
>||
$user->c->name == $user->c->email
||<
----
SQL::Abstract
>||
'user.email' => \'IS NULL'
||<

SQL::Declare
>||
$user->c->email == Null
||<
----
SQL::Abstract
>||
'user.age' => { '>' => 20 }
||<

SQL::Declare
>||
$user->c->age > 20
||<
----
schema meta data
>||
my $users = Table(user => [
    Column('id',    'integer',     'auto_incerement'),
    Column('name',  'string'),
    Column('email', 'string'),
    Column('birthday', 'date'),
]);

$user->c->id==10
 # => user.id = 10

$user->c->name=='kan'
 # => user.name IS 'kan'
||<
----
use overloadで比較演算子をオーバーロード
>||
use overload(
    '>' => sub { $_[0]->op('>', $_[1]) },
    ...
);
----
||<
*今後の展開
- joinなどに対応
- OR mapper部分も作ってみる
||<
----
ご清聴ありがとうございました

<a href="http://coderepos.org/share/browser/lang/perl/SQL-Declare">http://coderepos.org/share/browser/lang/perl/SQL-Declare</a>