rmc_index

--
-- Name: citation cid_key; Type: CONSTRAINT; Schema: rmc; Owner: -
--

-- update before indexing

UPDATE RMC_TEMP.SDFILE SET molecule = mol_from_smiles(smiles::cstring);
create index rmc_molecule_index on RMC_TEMP.SDFILE using gist(molecule);

ALTER TABLE ONLY RMC_TEMP.citation
    ADD CONSTRAINT cid_key PRIMARY KEY (cid);

ALTER TABLE ONLY RMC_TEMP.datapoint
    ADD CONSTRAINT datapont_pkey PRIMARY KEY (did);

ALTER TABLE ONLY RMC_TEMP.assay
    ADD CONSTRAINT key PRIMARY KEY (aid);

ALTER TABLE ONLY RMC_TEMP.fact
    ADD CONSTRAINT pkey PRIMARY KEY (rxid);

ALTER TABLE ONLY RMC_TEMP.sdfile
    ADD CONSTRAINT sdfile_key PRIMARY KEY (xrn);

ALTER TABLE ONLY RMC_TEMP.target
    ADD CONSTRAINT target_key PRIMARY KEY (tid);

CREATE INDEX citation_doi_idx ON RMC_TEMP.citation USING btree (doi);
CREATE INDEX citation_pn_idx ON RMC_TEMP.citation USING btree (pn);
CREATE INDEX datapoint_citation_idx ON RMC_TEMP.datapoint USING btree (citation);
CREATE INDEX datapoint_substance_idx ON RMC_TEMP.datapoint USING btree (substance);
CREATE INDEX datapoint_target_idx ON RMC_TEMP.datapoint USING btree (target);
CREATE INDEX datapoint_paureus_idx ON RMC_TEMP.datapoint USING btree (paureus);
CREATE INDEX datapoint_bspecie_idx ON RMC_TEMP.datapoint USING btree (bspecie);
CREATE INDEX datapoint_vtype_idx ON RMC_TEMP.datapoint USING btree (vtype);
CREATE INDEX fact_inchi_idx ON RMC_TEMP.fact USING btree (inchi);
CREATE INDEX fact_cn_idx ON RMC_TEMP.fact USING btree (cn);
CREATE INDEX fact_clphase_idx ON RMC_TEMP.fact USING btree (clphase);
CREATE INDEX sdfile_inchi_idx ON RMC_TEMP.sdfile USING btree (inchi);
CREATE INDEX sdfile_cn_idx ON RMC_TEMP.sdfile USING btree (cn);
CREATE INDEX target_tuniprot_idx ON RMC_TEMP.target USING gin(tuniprot);

ALTER TABLE RMC_TEMP.datapoint add constraint assay_link     foreign key (assay)     references RMC_TEMP.assay(aid);
ALTER TABLE RMC_TEMP.datapoint add constraint substance_link foreign key (substance) references RMC_TEMP.sdfile(xrn);
ALTER TABLE RMC_TEMP.datapoint add constraint target_link    foreign key (target)    references RMC_TEMP.target(tid);

-- 
-- bug in RMC data causes this to fail
--
-- ALTER TABLE RMC_TEMP.datapoint add constraint citation_link  foreign key (citation)  references RMC_TEMP.citation(cid);