use strict;
use warnings;
use Test::More skip_all => 'it requires mysql database';
use FindBin;
use lib "$FindBin::Bin/../lib";
BEGIN {
use_ok('DBIx::ActiveRecord');
use_ok('DBIx::ActiveRecord::Model');
};
=pod
create database ar_test;
use ar_test;
CREATE TABLE users (
id serial NOT NULL,
name varchar(50) NOT NULL,
profile text,
blood_type varchar(2),
deleted bool,
created_at datetime NOT NULL,
updated_at datetime NOT NULL
) ENGINE=InnoDB;
CREATE TABLE posts (
id serial NOT NULL,
user_id bigint NOT NULL,
title varchar(255) NOT NULL,
content text,
created_at datetime NOT NULL,
updated_at datetime NOT NULL
) ENGINE=InnoDB;
CREATE TABLE comments (
id serial NOT NULL,
post_id bigint NOT NULL,
user_id bigint NOT NULL,
content text,
created_at datetime NOT NULL,
updated_at datetime NOT NULL
) ENGINE=InnoDB;
=cut
package User;
use base 'DBIx::ActiveRecord::Model';
__PACKAGE__->table('users');
__PACKAGE__->columns(qw/id name profile blood_type deleted created_at updated_at/);
__PACKAGE__->primary_keys(qw/id/);
__PACKAGE__->has_many(posts => 'Post');
__PACKAGE__->has_one(post => 'Post');
# end User Model
package Post;
use base 'DBIx::ActiveRecord::Model';
__PACKAGE__->table('posts');
__PACKAGE__->columns(qw/id user_id title content created_at updated_at/);
__PACKAGE__->primary_keys(qw/id/);
__PACKAGE__->belongs_to(user => 'User');
__PACKAGE__->has_many(comments => 'Comment');
# end Post Model
package Comment;
use base 'DBIx::ActiveRecord::Model';
__PACKAGE__->table('comments');
__PACKAGE__->columns(qw/id post_id user_id content created_at updated_at/);
__PACKAGE__->primary_keys(qw/id/);
# end Comment Model
package main;
DBIx::ActiveRecord->connect("dbi:mysql:ar_test", 'root', 'root', {});
{
# set up
User->unscoped->delete_all;
ok 1;
}
{
# basic CRUD
my $u = User->new({name => 'hoge', profile => 'hogehoge'});
is $u->name, 'hoge';
is $u->profile, 'hogehoge';
$u->name('hoge2');
is $u->name, 'hoge2';
$u->name();
is $u->name, 'hoge2';
$u->name(undef);
is $u->name, undef;
$u->name('hoge');
ok !$u->id;
$u->save; # insert!
ok $u->id;
$u->save; # update!
ok $u->id;
my $us = User->all;
is @$us, 1;
is $us->[0]->name, 'hoge';
is $us->[0]->profile, 'hogehoge';
$u = $us->[0];
$u->name('hoge2');
$u->save; # update!
$us = User->all;
is @$us, 1;
is $us->[0]->name, 'hoge2';
is $us->[0]->profile, 'hogehoge';
$us->[0]->delete;
$us = User->all;
is @$us, 0;
}
{
# created_at, updated_at
my $u = User->new({name => 'test'});
ok !$u->created_at;
ok !$u->updated_at;
$u->save;
ok $u->created_at;
ok $u->updated_at;
is $u->created_at, $u->updated_at;
sleep(1);
$u->name("test2");
$u->save;
ok $u->created_at;
ok $u->updated_at;
ok $u->created_at ne $u->updated_at;
}
{
# scoped searches
User->create({name => 'hoge'});
User->create({name => 'fuga'});
User->create({name => 'hoge2', profile => 'a'});
my $s = User->eq(name => 'hoge');
my $us = $s->all;
is @$us, 1;
is $us->[0]->name, 'hoge';
is $s->to_sql, "SELECT * FROM users WHERE name = ?";
$s = User->eq(name => 'hoge2')->eq(profile => 'a');
$us = $s->all;
is @$us, 1;
is $us->[0]->name, 'hoge2';
is $s->to_sql, "SELECT * FROM users WHERE name = ? AND profile = ?";
$s = User->in(id => [1,2,3])->not_null('profile')->contains(profile => 'a');
$s->all;
is $s->to_sql, "SELECT * FROM users WHERE id IN (?, ?, ?) AND profile IS NOT NULL AND profile LIKE ?";
}
{
# scope
User->default_scope(sub{ shift->ne(deleted => 1) });
User->scope(type_a => sub{ shift->eq(blood_type => 'A') });
User->scope(type_a_or_b => sub{ shift->in(blood_type => ['A', 'B']) });
is(User->scoped->to_sql, "SELECT * FROM users WHERE deleted != ?");
User->delete_all;
User->new({deleted => 1, name => 'deleted user'})->save;
my $us = User->all;
is @$us, 0;
ok 1;
User->type_a->type_a_or_b->all;
is(User->type_a->to_sql, "SELECT * FROM users WHERE deleted != ? AND blood_type = ?");
is(User->type_a_or_b->to_sql, "SELECT * FROM users WHERE deleted != ? AND blood_type IN (?, ?)");
is(User->type_a_or_b->type_a->to_sql, "SELECT * FROM users WHERE deleted != ? AND blood_type IN (?, ?) AND blood_type = ?");
}
{
# association - belongs_to
my $u = User->new({name => 'aaa'});
$u->save;
my $p = Post->new({user_id => $u->id, title => 'aaa title'});
$p->save;
my $s = $p->user;
ok 1;
}
{
# association - has_many
my $u = User->new({name => 'aaa'});
$u->save;
my $s = $u->posts;
is $s->to_sql, "SELECT * FROM posts WHERE user_id = ?";
is_deeply [$s->_binds], [$u->id];
ok 1;
}
{
# association - has_one
my $u = User->new({name => 'aaa'});
$u->save;
my $post = $u->post;
ok 1;
}
{
# joins
my $s = User->joins('posts')->merge(Post->eq(title => 'aaa'));
$s->all;
is $s->to_sql, "SELECT me.* FROM users me LEFT JOIN posts posts ON posts.user_id = me.id WHERE me.deleted != ? AND posts.title = ?";
}
{
# select
my $s = User->select("id", "name")->in(id => [1,2,3]);
$s->all;
is $s->to_sql, "SELECT id, name FROM users WHERE deleted != ? AND id IN (?, ?, ?)";
# join and select
$s = User->joins('posts')->merge(Post->eq(title => 'aaa'))->select("id", "name")->in(id => [1,2,3]);
$s->all;
is $s->to_sql, "SELECT me.id, me.name FROM users me LEFT JOIN posts posts ON posts.user_id = me.id WHERE me.deleted != ? AND posts.title = ? AND me.id IN (?, ?, ?)";
}
{
# order, group, limit, offset
my $s = User->desc("created_at")->asc("id");
is $s->to_sql, "SELECT * FROM users WHERE deleted != ? ORDER BY created_at DESC, id";
$s->all;
$s = User->group("blood_type");
is $s->to_sql, "SELECT * FROM users WHERE deleted != ? GROUP BY blood_type";
$s->all;
$s = User->limit(5)->offset(2);
is $s->to_sql, "SELECT * FROM users WHERE deleted != ? LIMIT ? OFFSET ?";
$s->all;
$s = User->eq(id => 1)->lock;
is $s->to_sql, "SELECT * FROM users WHERE deleted != ? AND id = ? FOR UPDATE";
$s->all;
}
{
User->first;
User->last;
ok 1;
}
{
# transaction
User->transaction(sub {
});
User->transaction(sub {
die;
});
}
{
# scope cache
# print STDERR "*** cache test ***\n";
User->all;
User->all;
# print STDERR "*** all only ***\n";
my $s = User->scoped;
$s->all;
$s->first;
$s->last;
# print STDERR "*** first, last, all ***\n";
$s = User->scoped;
$s->first;
$s->last;
$s->first;
$s->last;
$s->all;
$s->all;
# print STDERR "*** new scope! ***\n";
$s->eq(id => 1)->all;
ok 1;
}
# includes
{
# print STDERR "*** includes user => posts ***\n";
User->unscoped->delete_all;
Post->unscoped->delete_all;
my $u1 = User->new({name => 'hoge', deleted => 0});
my $u2 = User->new({name => 'fuga', deleted => 0});
$u1->save;
$u2->save;
Post->new({title => 'hoge 01', user_id => $u1->id})->save;
Post->new({title => 'hoge 02', user_id => $u1->id})->save;
Post->new({title => 'hoge 03', user_id => $u1->id})->save;
Post->new({title => 'hoge 04', user_id => $u1->id})->save;
Post->new({title => 'fuga 01', user_id => $u2->id})->save;
Post->new({title => 'fuga 02', user_id => $u2->id})->save;
my $us = User->includes('posts')->all;
is @{$us->[0]->posts->all}, 4;
is @{$us->[1]->posts->all}, 2;
ok 1;
}
{
# array operator
ok @{User->scoped};
my $users = User->includes('posts');
foreach my $u (@$users) {
foreach my $p (@{$u->posts}) {
ok $u;
ok $p;
}
}
ok 1;
}
{
# nested joins
my $users = User->joins('posts', 'comments');
ok @{$users};
is $users->to_sql, "SELECT me.* FROM users me LEFT JOIN posts posts ON posts.user_id = me.id LEFT JOIN comments comments ON comments.post_id = posts.id WHERE me.deleted != ?";
}
{
# nested includes
User->unscoped->delete_all;
Post->unscoped->delete_all;
Comment->delete_all;
my $u1 = User->create({name => 'hoge', deleted => 0});
my $u2 = User->create({name => 'fuga', deleted => 0});
my $p1 = Post->create({user_id => $u1->id, title => "hoge 01"});
Comment->create({content => 'hoge 01 com1', post_id => $p1->id, user_id => $u2->id});
Comment->create({content => 'hoge 01 com2', post_id => $p1->id, user_id => $u2->id});
Comment->create({content => 'hoge 01 com3', post_id => $p1->id, user_id => $u2->id});
Comment->create({content => 'hoge 01 com4', post_id => $p1->id, user_id => $u2->id});
my $p2 = Post->create({user_id => $u1->id, title => "hoge 02"});
Comment->create({content => 'hoge 02 com1', post_id => $p2->id, user_id => $u2->id});
Comment->create({content => 'hoge 02 com2', post_id => $p2->id, user_id => $u2->id});
my $p3 = Post->create({user_id => $u2->id, title => "fuga 01"});
Comment->create({content => 'fuga 01 com1', post_id => $p3->id, user_id => $u1->id});
Comment->create({content => 'fuga 01 com2', post_id => $p3->id, user_id => $u1->id});
my $users = User->includes('posts', 'comments');
ok @{$users};
}
{
# count
Post->count;
Post->eq(title => 5)->count;
Post->joins('user')->merge(User->eq(id => 3))->count;
}
done_testing;