-- Entry level tables for VCS model 
--
--  Revision date 20030806
-- 
-- VCS_MAIN contains
--
--      VSMILES which is the isomeric SMILES of the sample without the 
--      application of business rules, usually a direct conversion from
--      an SD file with mol2smi().
--
--      NSMILES which is the normalized SMILES according to the business
--      rules held in the table TRANSFORM. This may be populated by 
--      updating the column by applying the function vcs_normalize() to the 
--      VSMILES column. If the user chooses to update by applying the 
--      vcs_normalize() function to the vcs_desalt(VSMILES, , , ) this 
--      column will contain the parent SMILES and will be appropriate as 
--      structure source to populate a table of physical properties using
--      the dayprop() function.
--      Structure searching would normally be carried out on this column.
--
--      VCS_ID which is the code used by the supplier to identify that 
--      particular compound
-- 
--      VCS_SUPP which is a unique integer used to identify the supplier
--      or source of the compound this is maintained in the VCS_SUPPLIER 
--      table.
--
--      VCS_SUPP and VCS_ID are the primary key on this table and are 
--      required to form a unique identifier for a sample.
--
--      If a sample is deleted from this table the associated data 
--      are deleted from the VCS_DATA table.
--
--
-- VCS_DATA contains
--
--      VCS_ID and VCS_SUPP defined as for VCS_MAIN and form the foreign key 
--      link to the VCS_MAIN table
--
--      VCS_DATA_NAME which is the name chosen by the supplier for
--      the data type.
--
--      VCS_DATA_VALUE which is the value as VARCHAR2 for that datatype 
--      for that sample identified by the VCS_ID and VCS_SUPP.
--
--      There are no further constraints on this table. Note when the 
--      sample is deleted from the VCS_MAIN table the associated 
--      data in this table are also deleted.
--
-- VCS_COORDS contains
--
--      VCS_ID and VCS_SUPP defined as for VCS_MAIN and form the foreign key 
--      link to the VCS_MAIN table
--
--      VCS_DATA_NAME which is either 2D or 3D for a depiction or a           
--      conformation.
--
--      VCS_DATA_VALUE which is the value as CLOB for that datatype 
--      for that sample identified by the VCS_ID and VCS_SUPP.
--
--      In general it is not forseen that these data will be searched only
--      retrieved. You will need to look at the DBMS_LOB package to 
--      manipulate or select these data outside at the SQL level
--
--      There are no further constraints on this table. Note when the 
--      sample is deleted from the VCS_MAIN table the associated 
--      data in this table are also deleted.
--
-- VCS_SUPPLIER contains
--
--      VCS_SUPP which is the primary key on this table defined as for
--      the VCS_MAIN table. Normally this would be a sequential integer
--      supplied by the user.
--      Removal of a VCS_SUPP value from this table removes all the 
--      associated data in the VCS_MAIN AND VCS_DATA tables. 
--
--      VCS_SUPPLIER_NAME which is the name of the compound supplier
--      or the compound source, chosen by the user. 
--      Users are encouraged to develop local systematic naming rules
--      to allow related sources to be linked.
--
--      VCS_SUPPLIER_CLASS which is a string which allows types of 
--      suppliers to be grouped. It could be used to 'hide' earlier 
--      copies of databases without removing them or identify preferred 
--      suppliers. Default value is 'UNKNOWN' when data is loaded
--      using vcs_loader.
--
--      VCS_SUPPLIER_TIMESTAMP is the system time when the supplier
--      record was created
--
--
--
-- ***********************************************************************
-- *                                                                     *
-- * As there are table dependencies do not alter the order of these     *
-- * actions                                                             *
-- *                                                                     *
-- ***********************************************************************
--

-- drop table VCS_COORDS;
-- drop table VCS_DATA;
-- drop table VCS_MAIN;
-- drop table VCS_SUPPLIER;

create table VCS_SUPPLIER (
VCS_SUPP                NUMBER (6),
VCS_SUPPLIER_NAME       VARCHAR2(100),
VCS_SUPPLIER_CLASS      VARCHAR2(25),
VCS_SUPPLIER_TIMESTAMP  DATE,
constraint VCS_SUPPLIER_PK PRIMARY KEY ( VCS_SUPP )
);

create table VCS_MAIN (
VSMILES                 VARCHAR2(4000),
NSMILES                 VARCHAR2(4000),
VCS_ID                  VARCHAR2(100),
VCS_SUPP                NUMBER (6),
constraint VCS_MAIN_PK PRIMARY KEY ( VCS_SUPP, VCS_ID ),
foreign key (VCS_SUPP)
        REFERENCES VCS_SUPPLIER ( VCS_SUPP )
        ON DELETE CASCADE
);

create table VCS_DATA (
VCS_ID                  VARCHAR2(100),
VCS_SUPP                NUMBER (6),
VCS_DATA_NAME           VARCHAR2(100),
VCS_DATA_VALUE          VARCHAR2(4000),
foreign key ( VCS_SUPP, VCS_ID )
        REFERENCES VCS_MAIN ( VCS_SUPP, VCS_ID )
        ON DELETE CASCADE
);

create table VCS_COORDS (
VCS_ID                  VARCHAR2(100),
VCS_SUPP                NUMBER (6),
VCS_DATA_NAME           VARCHAR2(100),
VCS_DATA_VALUE          CLOB,
foreign key ( VCS_SUPP, VCS_ID )
        REFERENCES VCS_MAIN ( VCS_SUPP, VCS_ID )
        ON DELETE CASCADE
);