http://wiki.docking.org/index.php?title=ZINC_ideas&feed=atom&action=historyZINC ideas - Revision history2024-03-28T11:19:18ZRevision history for this page on the wikiMediaWiki 1.39.1http://wiki.docking.org/index.php?title=ZINC_ideas&diff=7261&oldid=prevFrodo: Created page with "<pre> Materialized view for Bioactivity Materialized view for all bioactivity allows for the tables to still remain separate, but the data to be queryable from a single sour..."2014-03-18T17:02:52Z<p>Created page with "<pre> Materialized view for Bioactivity Materialized view for all bioactivity allows for the tables to still remain separate, but the data to be queryable from a single sour..."</p>
<p><b>New page</b></p><div><pre><br />
Materialized view for Bioactivity<br />
<br />
Materialized view for all bioactivity allows for the tables to still remain separate, but the data to be queryable from a single source. However, this cannot be fast on commit, so DBMS_MVIEW needs to be called manually – via a trigger (on update of any of the 4 tables) or on a cron.<br />
<br />
CREATE MATERIALIZED VIEW "CHEMAXON"."BIOACTIVITY”<br />
BUILD IMMEDIATE USING INDEX REFRESH ON DEMAND FORCE WITH PRIMARY KEY DISABLE QUERY REWRITE AS SELECT BIO_NUMBERS.ID_STRUC_FK,<br />
TARGET.TARGET_NAME,<br />
BIO_NUMBERS.IC50,<br />
BIO_NUMBERS.CHEMBL_ID,<br />
BIOACTIVITY_TYPE.ACTIVITY_DESC,<br />
TARGET_SRC.SOURCE,<br />
TARGET_SRC.FREE_FLAG,<br />
TARGET.SWISSPROT,<br />
TARGET.CHEMBL,<br />
TARGET.UNIPROT,<br />
TARGET.TARGET_SRC_FK<br />
FROM BIO_NUMBERS INNER JOIN TARGET ON BIO_NUMBERS.TARGET_FK = TARGET.TARGET_ID<br />
INNER JOIN BIOACTIVITY_TYPE ON TARGET.ACTIVITY_TYPE = BIOACTIVITY_TYPE.BIOACT_TYPE_ID<br />
INNER JOIN TARGET_SRC ON TARGET.TARGET_SRC_FK = TARGET_SRC.TARGET_SRC_ID;<br />
<br />
Protomer Table population<br />
<br />
Program written in Groovy and implemented via IJC because it was easier. In production this will be done via PL/SQL.<br />
<br />
Basic program:<br />
<br />
For molecule in Structures table:<br />
Calculate all pKas between 4.5 and 9<br />
Insert original structure into Protomer table, with PH_REF=1<br />
If pKa exists between 4.5 and 9 then<br />
Sort pKas in order.<br />
Determine halfway point between 4.5, pKa1, pKa2....pKan, 9.<br />
For each halfway point, calculate the major microspecies<br />
[check that microspecies is not the same as the original]<br />
Toggle PH_HI/MID/LO=1 for each range halfway point falls in<br />
Put pH of halfway point into PH_MAX_OCCUPANCY column<br />
Insert microspecies and relevant data into Protomer table<br />
<br />
The code is protomer_population.txt in the ZINC dropbox.<br />
<br />
<br />
Things to include in ZINC13<br />
<br />
Common name searching (captopril, viagra, etc)<br />
<br />
</pre></div>Frodo