Frodo: Created page with "

 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

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..."

New page

<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 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.

CREATE MATERIALIZED VIEW "CHEMAXON"."BIOACTIVITY”
BUILD IMMEDIATE USING INDEX REFRESH ON DEMAND FORCE WITH PRIMARY KEY DISABLE QUERY REWRITE AS SELECT BIO_NUMBERS.ID_STRUC_FK,
TARGET.TARGET_NAME,
BIO_NUMBERS.IC50,
BIO_NUMBERS.CHEMBL_ID,
BIOACTIVITY_TYPE.ACTIVITY_DESC,
TARGET_SRC.SOURCE,
TARGET_SRC.FREE_FLAG,
TARGET.SWISSPROT,
TARGET.CHEMBL,
TARGET.UNIPROT,
TARGET.TARGET_SRC_FK
FROM BIO_NUMBERS INNER JOIN TARGET ON BIO_NUMBERS.TARGET_FK = TARGET.TARGET_ID
INNER JOIN BIOACTIVITY_TYPE ON TARGET.ACTIVITY_TYPE = BIOACTIVITY_TYPE.BIOACT_TYPE_ID
INNER JOIN TARGET_SRC ON TARGET.TARGET_SRC_FK = TARGET_SRC.TARGET_SRC_ID;

Protomer Table population

Program written in Groovy and implemented via IJC because it was easier. In production this will be done via PL/SQL.

Basic program:

For molecule in Structures table:
Calculate all pKas between 4.5 and 9
Insert original structure into Protomer table, with PH_REF=1
If pKa exists between 4.5 and 9 then
Sort pKas in order.
Determine halfway point between 4.5, pKa1, pKa2....pKan, 9.
For each halfway point, calculate the major microspecies
[check that microspecies is not the same as the original]
Toggle PH_HI/MID/LO=1 for each range halfway point falls in
Put pH of halfway point into PH_MAX_OCCUPANCY column
Insert microspecies and relevant data into Protomer table

The code is protomer_population.txt in the ZINC dropbox.

Things to include in ZINC13

Common name searching (captopril, viagra, etc)

</pre>

ZINC ideas - Revision history

Frodo: Created page with "
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..."