代码之家 › 专栏 › 技术社区 › David Tonhofer

Prolog to SQL:有什么方法可以改进单元测试的SQL代码并优雅地修复一个边缘案例?

prolog unit-testing sql

1

David Tonhofer · 技术社区 · 6 年前

灵感来源于StackOverflow问题:

Find mutual element in different facts in swi-prolog

我们有以下的

问题陈述

给出了一个“演员主演电影”的数据库 (例如,starsin是连接演员“bob”和电影“a”的关系)
starsin(a,bob).
starsin(c,bob).

starsin(a,maria).
starsin(b,maria).
starsin(c,maria).

starsin(a,george).
starsin(b,george).
starsin(c,george).
starsin(d,george).
给定一组电影米 ,找到那些在米 .

这个问题最初是问Prolog的。

Prolog解决方案

在Prolog中,一个优雅的解决方案涉及到谓词 setof/3 , 它将可能的变量实例化收集到一个集合中(实际上是没有重复值):

actors_appearing_in_movies(MovIn,ActOut) :-
    setof(
        Ax,
        MovAx^(setof(Mx,starsin(Mx,Ax),MovAx), subset(MovIn,MovAx)),
        ActOut
    ).

我不想详细讨论这个问题,但让我们看看测试代码,这是这里感兴趣的。下面是五个测试用例:

actors_appearing_in_movies([],ActOut),permutation([bob, george, maria],ActOut),!. 
actors_appearing_in_movies([a],ActOut),permutation([bob, george, maria],ActOut),!.
actors_appearing_in_movies([a,b],ActOut),permutation([george, maria],ActOut),!.
actors_appearing_in_movies([a,b,c],ActOut),permutation([george, maria],ActOut),!.
actors_appearing_in_movies([a,b,c,d],ActOut),permutation([george],ActOut),!.

测试是对谓词的调用 actors_appearing_in_movies/2 ,即电影的输入列表(例如。 [a,b] )它捕获了演员 ActOut .

接下来,我们只需要测试 阿克图 是预期的一组参与者,例如:

permutation([george, maria],ActOut)`

“是 阿克图 列表的排列 [george,maria] ?.

如果调用成功(思考,不返回 false ),测试通过。

终点站 ! 是cut运算符,用于告诉Prolog引擎不要再次尝试寻找更多的解决方案,因为我们在这一点上做得很好。

注意,对于 空电影集 ,我们得到 所有的演员 . 这可以说是正确的: 每一个演员都出演空场的所有电影( Vacuous Truth ).

现在在SQL中。

这个问题完全是在关系代数领域,还有SQL,所以让我们试试这个。这里,我使用的是MySQL。

首先,建立事实。

DROP TABLE IF EXISTS starsin;

CREATE TABLE starsin (movie CHAR(20) NOT NULL, actor CHAR(20) NOT NULL);

INSERT INTO starsin VALUES
   ( "a" , "bob" ),
   ( "c" , "bob" ),
   ( "a" , "maria" ),
   ( "b" , "maria" ),
   ( "c" , "maria" ),
   ( "a" , "george" ),
   ( "b" , "george" ),
   ( "c" , "george" ),
   ( "d",  "george" );

关于 提供电影 作为输入,以 (临时)桌子听起来很自然。在MySQL中,“临时表”是会话的本地表。很好。

DROP TABLE IF EXISTS movies_in;
CREATE TEMPORARY TABLE movies_in (movie CHAR(20) NOT NULL);
INSERT INTO movies_in VALUES ("a"), ("b");

方法:

现在可以通过为每个参与者获取电影用 movies_in 一个演员出演过的电影 (通过内部连接为每个参与者创建),然后计算(对于每个参与者)是否结果集至少具有与该集相同的条目数 电影 .

出于实际原因,将查询包装为过程。一个 delimiter 在这里很有用:

DELIMITER $$

DROP PROCEDURE IF EXISTS actors_appearing_in_movies;

CREATE PROCEDURE actors_appearing_in_movies()
BEGIN

SELECT 
     d.actor 
   FROM 
     starsin d, movies_in q
   WHERE 
     d.movie = q.movie 
   GROUP BY 
     actor 
   HAVING 
     COUNT(*) >= (SELECT COUNT(*) FROM movies_in);

END$$

DELIMITER ;

快跑!

出现问题A:

有没有比编辑+复制粘贴表创建代码更好的方法, 发行a CALL 然后“亲手”检查结果?

DROP TABLE IF EXISTS movies_in;
CREATE TEMPORARY TABLE movies_in (movie CHAR(20) NOT NULL);
CALL actors_appearing_in_movies();

空的集合!

出现问题B:

以上不是我想要的,我想要“所有的演员”,同样为Prolog解决方案。由于我不想在代码上附加奇怪的边缘情况异常,所以我的方法必须错了。有没有一个自然涵盖了这一情况,但不会变得太复杂? T-SQL 而且PostgreSQL一行程序也很好!

其他测试用例产生预期数据:

DROP TABLE IF EXISTS movies_in;
CREATE TEMPORARY TABLE movies_in (movie CHAR(20) NOT NULL);
INSERT INTO movies_in VALUES ("a"), ("b");
CALL actors_appearing_in_movies();
+--------+
| actor  |
+--------+
| george |
| maria  |
+--------+

DROP TABLE IF EXISTS movies_in;
CREATE TEMPORARY TABLE movies_in (movie CHAR(20) NOT NULL);
INSERT INTO movies_in VALUES ("a"), ("b"), ("c");
CALL actors_appearing_in_movies();
+--------+
| actor  |
+--------+
| george |
| maria  |
+--------+

DROP TABLE IF EXISTS movies_in;
CREATE TEMPORARY TABLE movies_in (movie CHAR(20) NOT NULL);
INSERT INTO movies_in VALUES ("a"), ("b"), ("c"), ("d");
CALL actors_appearing_in_movies();
+--------+
| actor  |
+--------+
| george |
+--------+

0 回复 | 直到 6 年前

1

David Tonhofer 6 年前

给定一组电影M,找出那些在M的所有电影中主演的演员。

我会用:

select si.actor
from starsin si
where si.movie in (<M>)
group by si.actor
having count(*) = <n>;

如果你必须处理一个空的集合,那么你需要一个 left join :

select a.actor
from actors a left join
     starsin si
     on a.actor = si.actor and si.movie in (<M>)
group by a.actor
having count(si.movie) = <n>;

<n> 这是电影的数量 <M> .

更新:扩展形式的第二种方法

create or replace temporary table 
   actor (actor char(20) primary key)
   as select distinct actor from starsin;

select 
   a.actor,
   si.actor,si.movie  -- left in for docu
from 
   actor a left join starsin si
     on a.actor = si.actor 
        and si.movie in (select * from movies_in)
group 
   by a.actor
having
   count(si.movie) = (select count(*) from movies_in);

然后是空的 movies_in :

+--------+-------+-------+
| actor  | actor | movie |
+--------+-------+-------+
| bob    | NULL  | NULL  |
| george | NULL  | NULL  |
| maria  | NULL  | NULL  |
+--------+-------+-------+

为了这个 电影 例如:

+-------+
| movie |
+-------+
| a     |
| b     |
+-------+

movie 这是小组的第一名:

+--------+--------+-------+
| actor  | actor  | movie |
+--------+--------+-------+
| george | george | a     |
| maria  | maria  | a     |
+--------+--------+-------+

2

0

David Tonhofer 6 年前

下面的解决方案涉及计数和 UPDATE

写在这里: A Simple Relational Database Operation

我们正在使用 MariaDB / MySQL SQL语言。 T-SQL 或 PL/SQL 更完整。

注意,SQL没有可以传递给过程的向量数据类型。没有这个就得工作。

以表格形式输入事实:

CREATE OR REPLACE TABLE starsin 
   (movie CHAR(20) NOT NULL, actor CHAR(20) NOT NULL, 
    PRIMARY KEY (movie, actor));

INSERT INTO starsin VALUES
   ( "a" , "bob" ),
   ( "c" , "bob" ),
   ( "a" , "maria" ),
   ( "b" , "maria" ),
   ( "c" , "maria" ),
   ( "a" , "george" ),
   ( "b" , "george" ),
   ( "c" , "george" ),
   ( "d",  "george" );

输入一个计算解的过程,实际上。。。打印出来。

DELIMITER $$

CREATE OR REPLACE PROCEDURE actors_appearing_in_movies()
BEGIN

   -- collect all the actors
   CREATE OR REPLACE TEMPORARY TABLE tmp_actor (actor CHAR(20) PRIMARY KEY)
     AS SELECT DISTINCT actor from starsin;

   -- table of "all actors x (input movies + '--' placeholder)"
   -- (combinations that are needed for an actor to show up in the result)
   -- and a flag indicating whether that combination shows up for real
   CREATE OR REPLACE TEMPORARY TABLE tmp_needed 
     (actor CHAR(20), 
      movie CHAR(20), 
      actual TINYINT NOT NULL DEFAULT 0,
     PRIMARY KEY (actor, movie))
   AS 
     (SELECT ta.actor, mi.movie FROM tmp_actor ta, movies_in mi)
     UNION
     (SELECT ta.actor, "--" FROM tmp_actor ta);

   -- SELECT * FROM tmp_needed;

   -- Mark those (actor, movie) combinations which actually exist
   -- with a numeric 1
   UPDATE tmp_needed tn SET actual = 1 WHERE EXISTS
      (SELECT * FROM starsin si WHERE
             si.actor = tn.actor AND si.movie = tn.movie);

   -- SELECT * FROM tmp_needed;

   -- The result is the set of actors in "tmp_needed" which have as many
   -- entries flagged "actual" as there are entries in "movies_in"

   SELECT actor FROM tmp_needed GROUP BY actor 
      HAVING SUM(actual) = (SELECT COUNT(*) FROM movies_in);

END$$

DELIMITER ;

测试

MariaDB没有现成的单元测试框架,所以我们 “手工测试”并编写一个过程,我们手动检查。变量参数不存在,矢量数据类型不存在。让我们接受多达4部电影作为输入,并手动检查结果。

DELIMITER $$

CREATE OR REPLACE PROCEDURE 
   test_movies(IN m1 CHAR(20),IN m2 CHAR(20),IN m3 CHAR(20),IN m4 CHAR(20))
BEGIN
   CREATE OR REPLACE TEMPORARY TABLE movies_in (movie CHAR(20) PRIMARY KEY);   
   CREATE OR REPLACE TEMPORARY TABLE args (movie CHAR(20));
   INSERT INTO args VALUES (m1),(m2),(m3),(m4); -- contains duplicates and NULLs
   INSERT INTO movies_in (SELECT DISTINCT movie FROM args WHERE movie IS NOT NULL); -- clean
   DROP TABLE args;   
   CALL actors_appearing_in_movies();        
END$$

DELIMITER ;

以上通过所有手动测试,特别是:

CALL test_movies(NULL,NULL,NULL,NULL);

+--------+
| actor  |
+--------+
| bob    |
| george |
| maria  |
+--------+
3 rows in set (0.003 sec)

例如,对于 CALL test_movies("a","b",NULL,NULL);

首先在输入集中的所有电影中设置一个包含所有演员的表,包括由占位符表示的“不存在”电影 -- .

+--------+--------+-------+
| actual | actor  | movie |
+--------+--------+-------+
|      0 | bob    | --    |
|      0 | bob    | a     |
|      0 | bob    | b     |
|      0 | george | --    |
|      0 | george | a     |
|      0 | george | b     |
|      0 | maria  | --    |
|      0 | maria  | a     |
|      0 | maria  | b     |
+--------+--------+-------+

然后用1标记那些行,其中actor-movie组合实际上存在于 starsin .

+--------+--------+-------+
| actual | actor  | movie |
+--------+--------+-------+
|      0 | bob    | --    |
|      1 | bob    | a     |
|      0 | bob    | b     |
|      0 | george | --    |
|      1 | george | a     |
|      1 | george | b     |
|      0 | maria  | --    |
|      1 | maria  | a     |
|      1 | maria  | b     |
+--------+--------+-------+

如果 SUM(actual) 等于 input movies表中的条目数(不能更大),因为这意味着 actor确实出现在input movies表的所有movies中。在特殊情况下表为空,演员电影组合表将只包含

+--------+--------+-------+
| actual | actor  | movie |
+--------+--------+-------+
|      0 | bob    | --    |
|      0 | george | --    |
|      0 | maria  | --    |
+--------+--------+-------+

所以所有的演员都会被选中,这就是我们想要的。