DBD::SQLite，如何通过占位符在查询中传递数组？

Question

让我们有一个表：

sqlite> create table foo (foo int, bar int);
sqlite> insert into foo (foo, bar) values (1,1);
sqlite> insert into foo (foo, bar) values (1,2);
sqlite> insert into foo (foo, bar) values (1,3);

然后选择一些数据：

sqlite> select * from foo where foo = 1 and bar in (1,2,3);
1|1
1|2
1|3

工作正常。现在我尝试使用 DBD::SQLite 1.29：

my $sth = $dbh->prepare('select * from foo where foo = $1 and bar in ($2)');
$sth->execute(1,[1,2,3]);

这给了我空结果。 DBI 跟踪显示第二个占位符已正确绑定到数组，但没有分数。如果我加入字符串中的数组值并传递它，则没有结果。如果我展平数组，我会得到“用 N 个占位符而不是 2 个占位符调用”的可预测错误。

我有点不知所措。还有什么可以尝试的？

更新：好吧，这是一个取自现实世界应用程序的真实示例。

首先，设置：我有几个充满统计数据的表，列数从 10 到 700+ 不等。我正在谈论的查询选择该数据的子集以用于报告目的。不同的报告考虑不同的方面，因此运行不同的查询，每个请求一个或多个。有200多个报告，即200-300个查询。这种方法是为 Postgres 开发的，现在我需要缩小它的规模并使其与 SQLite 一起使用。考虑到所有这些都适用于 Postgres，我不能证明检查所有查询并重写它们是合理的。不利于维护。我可以并且确实使用就地查询调整，例如用 IN () 替换 = ANY ()，这些都是次要方面。

因此，这是我的示例：针对一份报告连续运行 2 个查询：

SELECT SPLIT, syn(SPLIT),
(SELECT COUNT(*) FROM cagent WHERE ACD = $1 AND SPLIT = $2 AND
LOC_ID = ANY ($3) AND LOGID IS NOT NULL AND WORKMODE = 40),
(SELECT COUNT(*) FROM cagent WHERE ACD = $1 AND SPLIT = $2 AND
LOC_ID = ANY ($3) AND LOGID IS NOT NULL AND WORKMODE = 30),
(SELECT COUNT(*) FROM cagent WHERE ACD = $1 AND SPLIT = $2 AND
LOC_ID = ANY ($3) AND LOGID IS NOT NULL AND WORKMODE = 50),
(SELECT COUNT(*) FROM cagent WHERE ACD = $1 AND SPLIT = $2 AND
LOC_ID = ANY ($3) AND LOGID IS NOT NULL AND WORKMODE = 220),
(SELECT COUNT(*) FROM cagent WHERE ACD = $1 AND SPLIT = $2 AND
LOC_ID = ANY ($3) AND LOGID IS NOT NULL),
(SELECT COUNT(*) FROM cagent WHERE ACD = $1 AND SPLIT = $2 AND
LOC_ID = ANY ($3) AND LOGID IS NOT NULL AND WORKMODE = 20),
(SELECT COUNT(*) FROM cagent WHERE ACD = $1 AND SPLIT = $2 AND
LOC_ID = ANY ($3) AND LOGID IS NOT NULL AND WORKMODE = 80)
FROM csplit WHERE ACD = $1 AND SPLIT = $2

SELECT syn(LOGID), syn(LOC_ID), LOGID, EXTENSION, syn(ROLE), PERCENT,
syn(AUXREASON), syn(AWORKMODE), syn(DIRECTION), WORKSKILL, syn(WORKSKLEVEL),
AGTIME FROM cagent WHERE ACD = $1 AND SPLIT = $2 AND LOC_ID = ANY ($3) AND
LOGID IS NOT NULL

这不是最复杂的示例，因为可以在查询的不同位置使用和重用任意数量的输入参数；用通用 ? 占位符替换它们并不是一项简单的任务。针对 Postgres 运行查询的代码看起来像这样（在输入清理等之后）：

sub run_select {
  my ($class, $dbh, $sql, @bind_values) = @_;

  my $sth;
  eval {
    $sth = $dbh->prepare_cached($sql);
    $sth->execute(@bind_values);
  };
  $@ and die "Error executing query: $@";

  my %types;
  {
    my $dbt = $dbh->type_info_all;
    @types{ map { $_->[1] } @$dbt[1..$#$dbt] } =
        map { $_->[0] } @$dbt[1..$#$dbt];
  };

  my @result;

  while (my $row = $sth->fetchrow_arrayref) {
    my $i = 0;
    push @result, [ map { [ $types{${$sth->{TYPE}}[$i++]}, $_ ] } @$row ];
  };

  return \@result;
};

我可以重写查询并直接注入值； SQL 注入并不是太大的威胁，因为所有输入在到达 SQL 引擎之前就已经通过正则表达式模式未被污染。我不想动态重写查询，原因有两个：a）它可能会导致值引用问题，b）它有点扼杀了prepare_cached背后的全部原因。如果每次都发生变化，SQL 引擎无法缓存重用准备好的语句。

正如我所说，上面的代码可以很好地与 Postgres 配合使用。由于SQLite引擎本身显然具有处理数据集的可能性，因此我认为这是DBD::SQLite实现的缺陷。所以真正的问题听起来像：有没有办法用 DBD::SQLite 在占位符中传递数据集？不一定是数组，尽管那是最合乎逻辑的。

Answer 1

试试这个：

my $sth = $dbh->prepare("select * from foo where foo = ? and bar in (?,?,?)";
$sth->execute(1,1,2,3);

您可以使用 x 重复运算符来生成所需数量的 ?：

my $sql = sprintf "select ... and bar in (%s)", join ",", ('?')x@values; 

Answer 2

使用 SQL::Abstract，如下所示：

use strict;
use warnings;
use SQL::Abstract;

my $sqla = SQL::Abstract->new;
my %where  = (
    foo => 1,
    bar => { -in => [1,2,3] }
);

my ($sql, @params) = 
    $sqla->select('foo', '*', \%where);

my $sth = $dbh->prepare($sql);
$sth->execute(@params);