Using database views of structure tables

    Introduction

    {primary} This page is very technical, and assumes knowledge of database systems. If you are an end user you can ignore it.

    A nice feature in the Instant JChem Desktop is the ability to handle views of JChem cartridge tables/indexes. In Instant JChem the entities that constitute the data trees, can be constructed from database tables or views. Prior to IJC 5.6 structure entities had to be based on database tables whilst non-structural entities could be based on tables or views. Since IJC 5.6 views can also be use used for structure entities. View provide more flexibility because they can combined data from multiple tables, filter data and perform transforms such as pivots. Note however that IJC handles views as read-only - you cannot modify data in an entity if you are using a view.

    To help you to understand and get using the structure based views as quickly as possible this guide is presented as a simple generic example of how you might implement and use these features. We use the demo Wombat data as an example. This has been imported into Oracle, with the structure table being handled as a standard table with a JChem cartridge index.

    1. A simple view

    Here we generate a view of the structure table that just provides a simple filter on the data. No other tables are involved. We generate the view like this:

    CREATE VIEW V_WOMBAT1 AS SELECT * FROM WOMBAT WHERE ID > 100

    Once done we can fire up IJC (note: you must re-connect to the database to pick up the newly created view) and then promote the view to an entity. It is detected as a structure entity, structures are handled as expected and you can perform structure searches. You'll even notice that the molweight and formula fields are present, even though they are not part of the view definition.

    2. A simple join

    Now lets assume that we have another table that we want to include in the results. This table has a one-to-one relationship to the WOMBAT table and has a column called WOMBAT_ID that is the foreign key relationship to the ID column in the WOMBAT table. We can create a view that combines these two tables like this:

    CREATE VIEW V_WOMBAT2 AS
SELECT W.ID, W.STRUCTURE, W.WID, W.SMDL_ID, W.SMDL_IDX, W.EST_LOGKOW, W.EST_LOGWSOL,
X.COL1, X.COL2
FROM WOMBAT W
INNER JOIN SOME_TABLE X ON W.ID = X.WOMBAT_ID

    {primary} This is a hypothetical example - there is no suitable table in the Wombat data.

    Now you would be able to promote this view as structure entity, and it would behave as in the previous example, but you would now also be seeing the columns from the SOME_TABLE table.

    3. A more complex join - the magical IJC_JCX_ROWID column

    Now let's assume we have a more complicated join. We want to join data from the structure table and the assay table. These tables have a one-to-many relationship. We create the view like this:

    CREATE OR REPLACE VIEW V_WOMBAT3 AS
  SELECT A.ID AS AID, A.TYPE, A.TARGET_NAME, A.VALUE, A.BIO_SPECIES,
    W.ID AS WID, W.STRUCTURE, W.ROWID AS IJC_JCX_ROWID, W.EST_LOGKOW, W.EST_LOGWSOL
  FROM WOMBAT_ACT_LIST A
  JOIN WOMBAT W ON W.ID = A.WOMBAT_ID

    On the face of it this looks pretty similar to the previous example. But there is an important difference. This is that the structure table is on the right hand side of the join, not the left hand side. This has the significant consequence that the ROWIDs of the view are those of the assay table, not the structure table. ROWIDs are magical row identifiers created by Oracle and used by JChem cartridge. IJC need to know the ROWIDs for the cartridge index to operated correctly (the reasons are complex, and not relevant here). The view will contain ROWIDs, but the wrong ones.

    To address this IJC looks for a particular column in the view, named IJC_JCX_ROWID, and if this column is present it uses this for the ROWIDs. You will notice that in the view definition we create this column using the ROWID values from the wombat structure table. This trick allows IJC to handle the view correctly.

    If this was not done, the view would not be handled correctly in IJC. The likely symptom is empty values for every row. If you know you have a scenario like this, or see this behaviour then you probably need add this IJC_JCX_ROWID column to the view.

    {primary} The column just needs to be present in the view. It does not need to be promoted as a field.

    Different types of structure tables

    JChem supports three types of structure table (type 2 and 3 are only supported in Oracle):

    1. JChemBase tables

    2. Cartridge indexes using a JChemBase table

    3. Cartridge indexes using a standard (or regular) table

      The above description has assumed that you are using a view of the third type of table. This is the most common and most useful type of cartridge table. But views are also supported for Cartridge indexes using a JChemBase table and should behave in a similar manner.

    Views are also supported for JChemBase tables, but these are generally less useful as JChem does not support structure search of views of JChemBase tables. The structures will display correctly, but you will not be able to search them. Hence views of JChemBase table should generally be avoided.

    Summary

    To summarise: You can now promote a database view to a structure entity in Instant JChem. Currently, there are these known limitations on this functionality:

    • Your view can only contain a single structure table - more than one can cause confusion.

    • Your structure table cannot be remote or accessed over a database link (Oracle only).

    • Structure search on views of JChemBase tables is not supported.