Hi everyone,<br><br>To follow up on my previous email, I am sending the script I used, the structure of my table, as well as the querry I use to search in my new table:<br><br>The Script used to create the Table Turn:<br><br>
<br>USE PdBase;<br><br>###############This is the main script which creates the _TURN table#############<br><br>DROP TABLE IF EXISTS `mohaddez_HE`.`_TURNS_tmp_CAN`;<br><br>CREATE TEMPORARY TABLE `mohaddez_HE`.`_TURNS_tmp_CAN`<br>
(<br>SELECT MMS_ENTRY.id AS pdb,<br> STRUCT_CONF.struct_conf_key AS id, <br> STRUCT_CONF.id AS name,<br> ENTITY_POLY.pdbx_seq_one_letter_code_can AS sequence, <br> MMS_ENTRY.entry_key, <br> STRUCT_CONF.beg_label_seq_id AS begin, <br>
STRUCT_CONF.end_label_seq_id AS end, <br> STRUCT_CONF.end_label_seq_id-STRUCT_CONF.beg_label_seq_id+1 AS length,<br> STRUCT_CONF.beg_auth_comp_id AS first_residu,<br> STRUCT_CONF.beg_label_asym_id AS chain <br>
FROM ENTITY_POLY, STRUCT_CONF, MMS_ENTRY, STRUCT_ASYM <br>WHERE <br> MMS_ENTRY.entry_key = ENTITY_POLY.entry_key AND <br> MMS_ENTRY.entry_key = STRUCT_CONF.entry_key AND <br> MMS_ENTRY.entry_key = STRUCT_ASYM.entry_key AND<br>
STRUCT_CONF.conf_type_id = 'TURN_P' AND<br> STRUCT_ASYM.id = STRUCT_CONF.beg_label_asym_id AND<br> STRUCT_ASYM.entity_id = ENTITY_POLY.entity_id<br>); <br><br>###########The rest of the script makes modification to the original script########################<br>
<br>UPDATE mohaddez_HE._TURNS_tmp_CAN SET Sequence = REPLACE(Sequence,'\n','');<br>DROP TABLE IF EXISTS `mohaddez_HE`.`_TURNS_tmp_CAN_RESTRICTED`; <br>##############################################################################<br>
######create the new table TURNS_tmp_CAN_RESTRICTED#####<br>Sequence = between Begin and ENd<br>####################################################################################<br><br><br>CREATE TABLE `mohaddez_HE`.`_TURNS_tmp_CAN_RESTRICTED` <br>
(<br>SELECT mohaddez_HE._TURNS_tmp_CAN.pdb,<br> mohaddez_HE._TURNS_tmp_CAN.id, <br> mohaddez_HE._TURNS_tmp_CAN.name,<br> SUBSTRING( mohaddez_HE._TURNS_tmp_CAN.sequence, <br> mohaddez_HE._TURNS_tmp_CAN.begin, <br>
mohaddez_HE._TURNS_tmp_CAN.end - mohaddez_HE._TURNS_tmp_CAN.begin + 1<br> ) AS sequence, <br> mohaddez_HE._TURNS_tmp_CAN.entry_key, <br> mohaddez_HE._TURNS_tmp_CAN.begin, <br> mohaddez_HE._TURNS_tmp_CAN.end, <br>
mohaddez_HE._TURNS_tmp_CAN.length, <br> mohaddez_HE._TURNS_tmp_CAN.first_residu,<br> mohaddez_HE._TURNS_tmp_CAN.chain<br>FROM mohaddez_HE._TURNS_tmp_CAN <br>);<br>############################################################################################<br>
<br><br><br>DROP TABLE `mohaddez_HE`.`_TURNS_tmp_CAN`;<br>DROP TABLE IF EXISTS `mohaddez_HE`.`_TURN`;<br>RENAME TABLE `mohaddez_HE`.`_TURNS_tmp_CAN_RESTRICTED` TO `mohaddez_HE`.`_TURN`;<br><br>####################################################################################<br>
######changing the types of the fields#####<br>####################################################################################<br>ALTER TABLE `mohaddez_HE`.`_TURN` CHANGE `begin` `begin` SMALLINT NULL DEFAULT NULL; <br>
ALTER TABLE `mohaddez_HE`.`_TURN` CHANGE `end` `end` SMALLINT NULL DEFAULT NULL;<br>ALTER TABLE `mohaddez_HE`.`_TURN` CHANGE `length` `length` SMALLINT NULL DEFAULT NULL;<br>ALTER TABLE `mohaddez_HE`.`_TURN` CHANGE `pdb` `pdb` VARCHAR( 4 ) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL; <br>
ALTER TABLE `mohaddez_HE`.`_TURN` CHANGE `name` `name` VARCHAR( 11 ) CHARACTER SET ascii COLLATE ascii_general_ci NULL DEFAULT NULL; <br>ALTER TABLE `mohaddez_HE`.`_TURN` CHANGE `first_residu` `first_residu` VARCHAR( 3 ) CHARACTER SET ascii COLLATE ascii_general_ci NULL DEFAULT NULL; <br>
ALTER TABLE `mohaddez_HE`.`_TURN` CHANGE `sequence` `sequence` LONGTEXT CHARACTER SET ascii COLLATE ascii_general_ci NULL DEFAULT NULL ;<br>ALTER TABLE `mohaddez_HE`.`_TURN` ADD `is_in_surface` BINARY DEFAULT '0' NOT NULL ;<br>
ALTER TABLE `mohaddez_HE`.`_TURN` ADD `distance_to_surface` FLOAT NOT NULL DEFAULT '0';<br><br>###################### ##############################################################<br>###################order the table by pdb#########################<br>
<br>CREATE TEMPORARY TABLE `mohaddez_HE`.`_TURN_PDB_tmp` AS <br> SELECT sequence, GROUP_CONCAT( DISTINCT (pdb) ) AS pdbs, count( * ) AS pdbscount<br> FROM mohaddez_HE._TURN<br> GROUP BY sequence;<br>
ALTER TABLE `mohaddez_HE`.`_TURN_PDB_tmp` ADD INDEX ( `sequence` ( 10 ) ); <br>##########################################################<br>#######<br><br>DROP TABLE IF EXISTS `mohaddez_HE`.`_TURN_WITH_PDBS_tmp`;<br>CREATE TABLE `mohaddez_HE`.`_TURN_WITH_PDBS_tmp`<br>
AS SELECT mohaddez_HE._TURN.*, mohaddez_HE._TURN_PDB_tmp.pdbs, mohaddez_HE._TURN_PDB_tmp.pdbscount AS pdbcount<br>FROM mohaddez_HE._TURN, mohaddez_HE._TURN_PDB_tmp<br>WHERE mohaddez_HE._TURN.sequence=mohaddez_HE._TURN_PDB_tmp.sequence;<br>
<br><br>DROP TABLE IF EXISTS `mohaddez_HE`.`_TURN`; <br>RENAME TABLE `mohaddez_HE`.`_TURN_WITH_PDBS_tmp` TO `mohaddez_HE`.`_TURN`;<br><br>###############################################################################################################################<br>
<br><br>My Turn Table looks like this:<br><br>+---------------------+--------------+------+-----+---------+-------+<br>| Field | Type | Null | Key | Default | Extra |<br>+---------------------+--------------+------+-----+---------+-------+<br>
| pdb | varchar(4) | | | | |<br>| id | int(11) | | PRI | 0 | |<br>| name | varchar(11) | YES | | NULL | |<br>| sequence | longtext | YES | | NULL | |<br>
| entry_key | int(11) | | | 0 | |<br>| begin | smallint(6) | YES | | NULL | |<br>| end | smallint(6) | YES | | NULL | |<br>| length | smallint(6) | YES | | NULL | |<br>
| first_residu | char(3) | YES | | NULL | |<br>| chain | varchar(255) | YES | | NULL | |<br>| is_in_surface | binary(1) | | | 0 | |<br>| distance_to_surface | float | | | 0 | |<br>
| pdbs | longtext | YES | | NULL | |<br>| pdbcount | bigint(21) | | | 0 | |<br>+---------------------+--------------+------+-----+---------+-------+<br><br>
<br><br>Th Querry: <br>select pdb,id, name, sequence, entry_key, begin, end, length,first_residue, chain from _TURN where pdb ="2pab";<br>The result:<br><br>+------+--------+---------+----------+-----------+-------+------+--------+--------------+-------+<br>
| pdb | id | name | sequence | entry_key | begin | end | length | first_residu | chain |<br>+------+--------+---------+----------+-----------+-------+------+--------+--------------+-------+<br>| 2pab | 609473 | TURN_P1 | SESG | 30320 | 50 | 53 | 4 | SER | A |<br>
| 2pab | 609475 | TURN_P2 | SESG | 30320 | 50 | 53 | 4 | SER | C |<br>+------+--------+---------+----------+-----------+-------+------+--------+--------------+-------+<br><br><br>However after I check the pdb site mysql, I have noticed that there are definitely more than 1 turn there: <a href="http://www.rcsb.org/pdb/explore/remediatedSequence.do?structureId=2PAB">http://www.rcsb.org/pdb/explore/remediatedSequence.do?structureId=2PAB</a>. I have found other pdbs will similar problem<br>
<br>Also from the result above it can be observed that both rows are the identical, however this is normal , since there are 2 chains.<br><br><br>I definitely appreciate your help with this, since all my work will be based on this table. I will also investigate the _BETA table to see if I do find similar inconsistencies.<br>
<br>Thank you for your time!<br><br>best regards,<br><br>zia<br><br><br><br><br>