优化存储在SQLite中的数据如何连接多个联系人？

Question

我在存储不同元素之间的联系。我想删除某些类型的元素，并存储被消除元素互连的元素的新触点。在

问题背景

想象一下这个问题。你有一个水分子，它和其他分子接触（如果是氢键，我的水周围可能有4个其他分子）。如下图所示（A、B、C、D是其他一些原子，点表示接触）。在

 A   B
 |   |
 H   H
  . .
   O
  / \
 H   H
 .   .
 C   D

我有关于所有点的信息，我需要消除中心的水，并创建描述A-C、A-D、A-B、B-C、B-D和C-D接触的记录

数据库结构

目前，我在数据库中有以下结构：

表atoms：

• "id" integer PRIMARY KEY,
• "amino" char(3) NOT NULL,（HOH表示水或其他值）
• 识别原子的其他列

表contacts：

• "acceptor_id" integer NOT NULL,（靠近氢的原子，这里是C或D）
• "donor_id" integer NOT NULL,（这里是A或B）
• "directness" char(1) NOT NULL,（对于直接的，应该是D，对于水介导的，应该是W）
• 有关接触的其他列，例如距离

编辑： 在前面描述的案例中，数据会是怎样的。在

atoms：

^{pr2}$

contacts：

donor_id|acceptor_id|directness
1        4           D
1        5           D
2        1           D
3        1           D

我要做的是

contacts：

donor_id|acceptor_id|directness
3        4           W            <- B-C
3        5           W            <- B-D
2        4           W            <- A-C
2        5           W            <- A-D
2        3           X            <- A-B    (These last two rows are escaping me,
4        5           X            <- C-D     there could be also row D-C, but not
                                             both C-D and D-C. A char 'X' could 
                                             be used to mark "no donor/acceptor")

当前解决方案（不足）

现在，我要检查所有有donor.amino = "HOH"的联系人。在这个示例中，这将从C和D中选择联系人。对于每个选定的联系人，我查找与当前选定联系人中的acceptor_id相同的联系人。根据这些信息，我创建了新联系人。最后，我删除了所有的联系人。在

这样，我显然无法创建C-D和A-B联系人（其他4个都可以）。在

如果我尝试一种类似的方法-试图找到两个具有相同捐赠者身份证的联系人，我最终会得到重复的联系人（C-D和D-C）。在

有没有一种简单的方法来检索所有六个联系人而不重复？在

我梦想着一个一页长的SQL查询，它只检索这六个想要的行。：—）
不过，任何其他想法都是受欢迎的。在

最好保存捐赠者的信息（如有可能），但并非绝对必要。在

非常感谢你们所有读到这个问题的人。在

Answer 1

嗯，很难在评论中提供例子，我决定发表一个答案：

如果必须遵循原始数据，则无法区分第一个结构和第二个结构的数据。应该有一个附加的分组条件来删除第一个和第二个结构之间的方向。在

sqlite> create table atoms (id INT, atom TEXT, amino TEXT);
sqlite> insert into atoms VALUES (1, 'O', 'HOH');
sqlite> insert into atoms VALUES (2, 'A', 'ARG');
sqlite> insert into atoms VALUES (3, 'B', 'CYS');
sqlite> insert into atoms VALUES (4, 'C', 'SER');
sqlite> insert into atoms VALUES (5, 'D', 'ARG');
sqlite> insert into atoms VALUES (6, 'O1', 'HOH');
sqlite> insert into atoms VALUES (7, 'A1', 'ARG');
sqlite> insert into atoms VALUES (8, 'B1', 'CYS');
sqlite> insert into atoms VALUES (9, 'C1', 'SER');
sqlite> insert into atoms VALUES (10, 'D1', 'ARG');
sqlite> select * from atoms;
1|O|HOH
2|A|ARG
3|B|CYS
4|C|SER
5|D|ARG
6|O1|HOH
7|A1|ARG
8|B1|CYS
9|C1|SER
10|D1|ARG

以下是原始数据：

^{pr2}$

问题如下：

select
    c1.donor_id, c2.acceptor_id, 'W' as directness
from
    contacts c1, contacts c2, atoms a
where
    c1.acceptor_id = c2.donor_id
    and c1.acceptor_id=a.id
    and a.amino='HOH'
UNION ALL
select
    c1.donor_id, c2.donor_id, 'X' as directness
from
    contacts c1, contacts c2, atoms a
where
    c1.acceptor_id = c2.acceptor_id
    and c1.acceptor_id=a.id
    and a.amino='HOH'
    and c1.donor_id < c2.donor_id
UNION ALL
select
    c1.acceptor_id, c2.acceptor_id, 'X' as directness
from
    contacts c1, contacts c2, atoms a
where
    c1.donor_id = c2.donor_id
    and c1.donor_id=a.id
    and a.amino='HOH'
    and c1.acceptor_id < c2.acceptor_id;

结果如下：

donor_id    acceptor_id  directness
            -       
2           4            W
2           5            W
3           4            W
3           5            W
7           9            W
7           10           W
8           9            W
8           10           W
2           3            X
7           8            X
4           5            X
9           10           X

Answer 2

你的解释有一个困难。在

首先是一个有向图，其中每条边代表一个连接X=>；Y，其中X是施主，Y是受主。表atoms是该图的SQL表示。在

你似乎想要的是一些无方向的东西。所以X-Y连接意味着X和Y通过水分子（当然也可以是其他物种）连接，但X和Y可以是供体或受体。由于这个原因，您的上一个表有一个歧义（您注意到），因此一些链接可能会出现在任何一个地方。在我看来，这意味着你最后一列中的列标题donor_id和{}没有你解释过的任何含义。当然，这可能是我的困惑。在

如果您想要的是一个包含所有6个链接的表，每个链接都是一行，但是不要太担心跟踪捐赠者/接受者的内容，那么在sqlite3中，这对我很有用：

 create temporary view hoh_view as 
 select donor_id as id, atoms.id as hoh_id from contacts, atoms 
       where acceptor_id=atoms.id and atoms.amino='HOH' 
 union select acceptor_id as id, atoms.id as hoh_id from contacts, atoms 
       where donor_id=atoms.id and atoms.amino='HOH';

 select a.id, b.id from hoh_view as a, hoh_view as b 
       where a.id > b.id and a.hoh_id=b.hoh_id;

我用了一个临时的视角让事情更清楚。如果愿意，可以将所有这些都放入一个大查询中，方法是将对hoh_view的每个引用替换为第一个查询。我觉得有点恶心，也许有办法把它整理一下。在

如果你想跟踪供体/受体的关系，你需要解释当两个氨基酸都是受体或供体时（即例子中的最后两行），你是如何决定怎么做的。在

如果这不符合你的要求，也许我可以把它修好。在