 -- Name: pmap.sql
 -- Purpose: sql code for generating tables for storing PMAP results
 --
--- $Id: pmap.sql,v 1.1 2005/09/14 22:38:32 mukhyala Exp $
+-- $Id: pmap.sql,v 1.2 2005/11/17 17:10:51 mukhyala Exp $
 -- -----------------------------------------------------------------------------
 
 -- -----------------------------------------------------------------------------

REVOKE ALL ON TABLE pmap_hsp_hsp_id_seq FROM PUBLIC;
GRANT INSERT,UPDATE ON TABLE pmap_hsp_hsp_id_seq TO loader;

CREATE UNIQUE INDEX pmap_hsp_unq ON pmap_hsp USING btree (genasm_id, chr, plus_strand, gstart, gstop, pseq_id, pstart, pstop);
CREATE INDEX pmap_hsp_p_lookup ON pmap_hsp USING btree (pseq_id, pstart, pstop);
CREATE INDEX pmap_hsp_g_lookup ON pmap_hsp USING btree (genasm_id, chr, plus_strand, gstart, gstop);


ALTER TABLE ONLY pmap_hsp



--------------------------------------------------------------------------------
-- Name: pmap_v
-- Purpose: summary of PMAP alignments
-- 

CREATE OR REPLACE VIEW PMAP_V AS
	SELECT a.params_id, a.genasm_id, h.pseq_id, ah.aln_id, min(h.pstart) AS pstart, max(h.pstop) AS pstop, count(*) AS exons, sum(h.pstop - h.pstart + 1) AS aln_length, (sum(h.pstop - h.pstart + 1)::double precision / q.len::double precision * 100::double precision)::integer AS pct_cov, a.ident, ((a.ident / sum(h.pstop - h.pstart + 1))::double precision * 100::double precision)::integer AS pct_ident, h.chr, h.plus_strand, min(h.gstart) AS gstart, max(h.gstop) AS gstop
   FROM pmap_hsp h
   JOIN pmap_alnhsp ah ON h.hsp_id = ah.hsp_id
