1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153

# 言語: perl
# バージョン: 5.36以上
# 依存: Moo, Carp

package QueryBuilder;
use v5.36;
use Moo;
use Carp qw(croak);

has _table       => (is => 'rw');
has _columns     => (is => 'rw', default => sub { [] });
has _joins       => (is => 'rw', default => sub { [] });
has _conditions  => (is => 'rw', default => sub { [] });
has _group_by    => (is => 'rw', default => sub { [] });
has _having      => (is => 'rw', default => sub { [] });
has _orders      => (is => 'rw', default => sub { [] });
has _limit       => (is => 'rw');
has _offset      => (is => 'rw');
has _bind_values => (is => 'rw', default => sub { [] });

sub from ($self, $table) {
    croak "Table name cannot be empty" unless $table;
    $self->_table($table);
    return $self;
}

sub select ($self, @columns) {
    push $self->_columns->@*, @columns;
    return $self;
}

sub join ($self, $table, $on_left, $on_right, $type = 'INNER') {
    croak "Join table cannot be empty" unless $table;
    croak "Join condition required" unless $on_left && $on_right;
    push $self->_joins->@*, {
        type     => $type,
        table    => $table,
        on_left  => $on_left,
        on_right => $on_right,
    };
    return $self;
}

sub left_join ($self, $table, $on_left, $on_right) {
    return $self->join($table, $on_left, $on_right, 'LEFT');
}

sub where ($self, $column, $value) {
    croak "Where column cannot be empty" unless $column;
    push $self->_conditions->@*, { column => $column };
    push $self->_bind_values->@*, $value;
    return $self;
}

sub group_by ($self, @columns) {
    push $self->_group_by->@*, @columns;
    return $self;
}

sub having ($self, $column, $op, $value) {
    croak "Having requires GROUP BY" unless $self->_group_by->@*;
    push $self->_having->@*, { column => $column, op => $op };
    push $self->_bind_values->@*, $value;
    return $self;
}

sub order_by ($self, $column, $dir = 'ASC') {
    croak "Invalid order direction: $dir" unless $dir =~ /^(ASC|DESC)$/i;
    push $self->_orders->@*, { column => $column, dir => uc($dir) };
    return $self;
}

sub limit ($self, $count) {
    croak "Limit must be positive integer" unless $count && $count > 0;
    $self->_limit($count);
    return $self;
}

sub offset ($self, $count) {
    croak "Offset requires LIMIT" unless $self->_limit;
    croak "Offset must be non-negative integer" unless defined $count && $count >= 0;
    $self->_offset($count);
    return $self;
}

sub build ($self) {
    # バリデーション
    croak "Table not specified. Call from() first." unless $self->_table;
    
    my @columns = $self->_columns->@*;
    my $cols = @columns ? CORE::join(', ', @columns) : '*';
    
    my $sql = "SELECT $cols FROM " . $self->_table;
    
    # JOIN
    for my $j ($self->_joins->@*) {
        $sql .= " $j->{type} JOIN $j->{table} ON $j->{on_left} = $j->{on_right}";
    }
    
    # WHERE
    if ($self->_conditions->@*) {
        my @wheres = map { "$_->{column} = ?" } $self->_conditions->@*;
        $sql .= " WHERE " . CORE::join(' AND ', @wheres);
    }
    
    # GROUP BY
    if ($self->_group_by->@*) {
        $sql .= " GROUP BY " . CORE::join(', ', $self->_group_by->@*);
    }
    
    # HAVING
    if ($self->_having->@*) {
        my @havings = map { "$_->{column} $_->{op} ?" } $self->_having->@*;
        $sql .= " HAVING " . CORE::join(' AND ', @havings);
    }
    
    # ORDER BY
    if ($self->_orders->@*) {
        my @orders = map { "$_->{column} $_->{dir}" } $self->_orders->@*;
        $sql .= " ORDER BY " . CORE::join(', ', @orders);
    }
    
    # LIMIT / OFFSET
    if ($self->_limit) {
        $sql .= " LIMIT " . $self->_limit;
        if (defined $self->_offset) {
            $sql .= " OFFSET " . $self->_offset;
        }
    }
    
    return $sql;
}

sub bind_values ($self) {
    return $self->_bind_values->@*;
}

# ビルダーをリセット
sub reset ($self) {
    $self->_table(undef);
    $self->_columns([]);
    $self->_joins([]);
    $self->_conditions([]);
    $self->_group_by([]);
    $self->_having([]);
    $self->_orders([]);
    $self->_limit(undef);
    $self->_offset(undef);
    $self->_bind_values([]);
    return $self;
}

1;

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111

#!/usr/bin/env perl
# 言語: perl
# バージョン: 5.36以上
# 依存: Moo, Test::More, Test::Exception

use v5.36;
use Test::More;
use Test::Exception;
use lib 'lib';
use QueryBuilder;

subtest 'Simple SELECT' => sub {
    my $builder = QueryBuilder->new->from('users');
    is $builder->build, 'SELECT * FROM users';
};

subtest 'SELECT with columns' => sub {
    my $builder = QueryBuilder->new
        ->select('id', 'name')
        ->from('users');
    is $builder->build, 'SELECT id, name FROM users';
};

subtest 'WHERE clause' => sub {
    my $builder = QueryBuilder->new
        ->from('users')
        ->where('status', 'active');
    
    is $builder->build, 'SELECT * FROM users WHERE status = ?';
    is_deeply [$builder->bind_values], ['active'];
};

subtest 'Multiple WHERE conditions' => sub {
    my $builder = QueryBuilder->new
        ->from('users')
        ->where('status', 'active')
        ->where('role', 'admin');
    
    is $builder->build, 'SELECT * FROM users WHERE status = ? AND role = ?';
    is_deeply [$builder->bind_values], ['active', 'admin'];
};

subtest 'JOIN clause' => sub {
    my $builder = QueryBuilder->new
        ->from('users')
        ->join('orders', 'users.id', 'orders.user_id');
    
    like $builder->build, qr/INNER JOIN orders ON users\.id = orders\.user_id/;
};

subtest 'LEFT JOIN clause' => sub {
    my $builder = QueryBuilder->new
        ->from('users')
        ->left_join('orders', 'users.id', 'orders.user_id');
    
    like $builder->build, qr/LEFT JOIN orders ON users\.id = orders\.user_id/;
};

subtest 'ORDER BY' => sub {
    my $builder = QueryBuilder->new
        ->from('users')
        ->order_by('created_at', 'DESC');
    
    like $builder->build, qr/ORDER BY created_at DESC/;
};

subtest 'LIMIT and OFFSET' => sub {
    my $builder = QueryBuilder->new
        ->from('users')
        ->limit(10)
        ->offset(20);
    
    like $builder->build, qr/LIMIT 10 OFFSET 20/;
};

subtest 'GROUP BY and HAVING' => sub {
    my $builder = QueryBuilder->new
        ->select('user_id', 'COUNT(*)')
        ->from('orders')
        ->group_by('user_id')
        ->having('COUNT(*)', '>', 5);
    
    like $builder->build, qr/GROUP BY user_id HAVING COUNT\(\*\) > \?/;
    is_deeply [$builder->bind_values], [5];
};

subtest 'Validation: table required' => sub {
    my $builder = QueryBuilder->new;
    throws_ok { $builder->build } qr/Table not specified/;
};

subtest 'Validation: invalid order direction' => sub {
    my $builder = QueryBuilder->new->from('users');
    throws_ok { $builder->order_by('id', 'INVALID') } qr/Invalid order direction/;
};

subtest 'Validation: offset requires limit' => sub {
    my $builder = QueryBuilder->new->from('users');
    throws_ok { $builder->offset(10) } qr/Offset requires LIMIT/;
};

subtest 'Reset builder' => sub {
    my $builder = QueryBuilder->new
        ->from('users')
        ->where('status', 'active');
    
    $builder->reset;
    throws_ok { $builder->build } qr/Table not specified/;
};

done_testing;

1

$ prove -v t/query_builder.t

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

t/query_builder.t ..
    ok 1 - Simple SELECT
    ok 2 - SELECT with columns
    ok 3 - WHERE clause
    ok 4 - Multiple WHERE conditions
    ok 5 - JOIN clause
    ok 6 - LEFT JOIN clause
    ok 7 - ORDER BY
    ok 8 - LIMIT and OFFSET
    ok 9 - GROUP BY and HAVING
    ok 10 - Validation: table required
    ok 11 - Validation: invalid order direction
    ok 12 - Validation: offset requires limit
    ok 13 - Reset builder
ok
All tests successful.