如何在DBI中重复使用WHERE子句逻辑？

Question

如何在DBI中重复使用WHERE子句逻辑？

4

免责声明：我第一次使用DBI。

我有一个MySQL表格，里面有很多索引字段（f1、f2、f3等），这些字段被长时间运行的进程用来生成WHERE子句，这些进程迭代数据库的块，执行各种清理和测试操作。

目前这个代码的版本工作方式如下：

sub get_list_of_ids() {
    my ($value1, $value2, $value3...) = @_;

    my $stmt = 'SELECT * FROM files WHERE 1';
    my @args;

    if (defined($value1)) {
        $stmt .= ' AND f1 = ?';
        push(@args, $value1);
    }
    # Repeat for all the different fields and values

    my $select_sth = $dbh->prepare($stmt) or die $dbh->errstr;
    $select_sth->execute(@args) or die $select_sth->errstr;

    my @result;
    while (my $array = $select_sth->fetch) {
        push(@result, $$array[0]);
    }
    return \@result;
}

sub function_A() {
    my ($value1, $value2, $value3...) = @_;

    my $id_aref = get_list_of_ids($value1, $value2, $value3...);
    foreach my $id (@$id_aref) {
        # Do something with $id
        # And something else with $id
    }
}

sub function_B() {
    my ($value1, $value2, $value3...) = @_;

    my $id_aref = get_list_of_ids($value1, $value2, $value3...);
    foreach my $id (@$id_aref) {
        # Do something different with $id
        # Maybe even delete the row
    }
}

无论如何，我将要在数据库中插入更多的行，但我清楚上述代码无法扩展。我可以根据其他语言想到几种解决方法。在Perl中处理它的最佳方式是什么？

需要注意的关键点是：get_list_of_ids（）中的逻辑过于冗长，无法在每个函数中复制；而选择行上的操作非常多样化。

提前致谢。

- Anon Gordon

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Schwern · Accepted Answer

我猜你所说的“扩展”是指维护方面而非性能。

你代码的关键变化是将参数作为列/值对传递，而不是一组带有假定列集的值列表。这将使你的代码能够处理任何你可能添加的新列。

DBI->selectcol_arrayref 既方便又速度较快，因为它是用 C 编写的。

如果在 connect 调用中打开 RaiseError，DBI 将在错误时抛出异常，而不必一直编写 or die ...。你应该这样做。

最后，由于我们从可能不受信任的用户输入编写 SQL，我已经小心翼翼地转义了列名。

其余内容在 this Etherpad 中解释，你可以逐步观看你的代码被转换。

sub get_ids {
    my %search = @_;

    my $sql = 'SELECT id FROM files';

    if( keys %search ) {
        $sql .= " WHERE ";
        $sql .= join " AND ", map { "$_ = ?" }
                              map { $dbh->quote_identifier($_) }
                              keys %search;
    }

    return $dbh->selectcol_arrayref($sql, undef, values %search);
}

my $ids = get_ids( foo => 42, bar => 23 );

如果您期望get_ids返回一个巨大的列表，太多以至于无法在内存中保存，那么您可以返回语句句柄并使用它进行迭代，而不是提取整个数组并将其存储在内存中。

sub get_ids {
    my %search = @_;

    my $sql = 'SELECT id FROM files';

    if( keys %search ) {
        $sql .= " WHERE ";
        $sql .= join " AND ", map { "$_ = ?" }
                              map { $dbh->quote_identifier($_) }
                              keys %search;
    }

    my $sth = $dbh->prepare($sql);
    $sth->execute(values %search);
    return $sth;
}

my $sth = get_ids( foo => 42, bar => 23 );
while( my $id = $sth->fetch ) {
    ...
}

在数组上下文中返回ID列表，或在标量上下文中返回语句句柄，可以将这两种方法结合起来使用。请注意保留HTML标签。

sub get_ids {
    my %search = @_;

    my $sql = 'SELECT id FROM files';

    if( keys %search ) {
        $sql .= " WHERE ";
        $sql .= join " AND ", map { "$_ = ?" }
                              map { $dbh->quote_identifier($_) }
                              keys %search;
    }

    # Convenient for small lists.
    if( wantarray ) {
        my $ids = $dbh->selectcol_arrayref($sql, undef, values %search);
        return @$ids;
    }
    # Efficient for large ones.
    else {
        my $sth = $dbh->prepare($sql);
        $sth->execute(values %search);
        return $sth;
    }
}

my $sth = get_ids( foo => 42, bar => 23 );
while( my $id = $sth->fetch ) {
    ...
}

my @ids = get_ids( baz => 99 );

最终，您将希望停止手动编写SQL，并使用对象关系映射器（ORM），例如DBIx::Class。 ORM的主要优点之一是非常灵活，可以为您完成上述操作。 DBIx :: Class可以返回简单的结果列表或非常强大的迭代器。迭代器是惰性的，它不会执行查询，直到您开始获取行，允许您根据需要更改查询，而不必使提取例程复杂化。

my $ids = get_ids( foo => 23, bar => 42 );
$ids->rows(20)->all;  # equivalent to adding LIMIT 20