Geek Answers Handbook

Updating data stored in SQLite - how to join multiple contacts?

I keep contacts between different elements. I want to exclude elements of a certain type and store new contacts of elements that were interconnected by the eliminated element.

Problem background

Imagine this problem. You have a water molecule that is in contact with other molecules (if the contact is a hydrogen bond, there may be 4 more molecules around my water). As in the following figure (A, B, C, D are some other atoms, and the points are the contact).

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

I have information about all points, and I need to remove the water in the center and create records describing the contacts AC, AD, AB, BC, BD and CD.

Database structure

I currently have the following structure in the database:

Table 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 )
  • ,

EDIT: , .

atoms:

id|atom|amino
1 | O  | HOH
2 | N  | ARG  <- atom A from image
3 | S  | CYS  <- B 
4 | O  | SER  <- C
5 | N  | ARG  <- D

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, donor_id . . HOH .

, , , C-D A-B ( 4 ).

- , donor_id, (C-D D-C).

?

SQL- , .:-)
.

, ( ), .

, .

+5
2

, , :

, . , .

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

UPD

:

sqlite> .headers ON
sqlite> .mode columns
sqlite> select * from atoms;
id          atom        amino
----------  ----------  ----------
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
sqlite> select * from contacts;
donor_id    acceptor_id  directness
----------  -----------  ----------
1           4            D
1           5            D
2           1            D
3           1            D
6           9            D
6           10           D
7           6            D
8           6            D

:

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
+1

.

, X = > Y, X - , Y - . atoms SQL .

, , , , - . , X-Y , X Y ( , ), X Y . ( ), . , donor_id acceptor_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 . , .

/, , , , (.. ).

, , , , , .

+2

More articles:


All Articles