From 9612a14433017a940cb34f070609ea96870a4351 Mon Sep 17 00:00:00 2001
From: Don Armstrong <don@donarmstrong.com>
Date: Wed, 21 Mar 2012 15:43:26 -0700
Subject: [PATCH] add chr_pos support

---
 schema/extra_schema/association_results.sql | 42 ++++++++++++++++++---
 1 file changed, 37 insertions(+), 5 deletions(-)

diff --git a/schema/extra_schema/association_results.sql b/schema/extra_schema/association_results.sql
index 822387b..0aa6ba1 100644
--- a/schema/extra_schema/association_results.sql
+++ b/schema/extra_schema/association_results.sql
@@ -1,11 +1,12 @@
 DROP VIEW ga_snp;
+DROP VIEW ga_chr_pos;
 DROP TABLE ga_study_snp CASCADE;
 DROP TABLE ga_study_subpart CASCADE;
 DROP TABLE ga_study CASCADE;
 
 
 CREATE TABLE ga_study (
-       ga_id       int NOT NULL PRIMARY KEY,
+       ga_id       SERIAL PRIMARY KEY,
        short_name  TEXT NOT NULL,
        full_name   TEXT NOT NULL,
        description TEXT NOT NULL DEFAULT '',
@@ -15,8 +16,8 @@ CREATE TABLE ga_study (
 CREATE UNIQUE INDEX ON ga_study(short_name);
 
 CREATE TABLE ga_study_subpart (
-       ga_subpart_id INT NOT NULL PRIMARY KEY,
-       ga_id INT NOT NULL REFERENCES ga_study,
+       ga_subpart_id SERIAL PRIMARY KEY,
+       ga_id INT NOT NULL REFERENCES ga_study ON DELETE CASCADE,
        short_name TEXT NOT NULL,
        full_name TEXT NOT NULL,
        description TEXT NOT NULL DEFAULT ''
@@ -26,8 +27,8 @@ CREATE UNIQUE INDEX ON ga_study_subpart(ga_id,short_name);
 CREATE INDEX ON ga_study_subpart(ga_id);
 
 CREATE TABLE ga_study_snp (
-       snp_id int NOT NULL,
-       ga_subpart_id INT NOT NULL REFERENCES ga_study_subpart,
+       snp_id INT NOT NULL,
+       ga_subpart_id INT NOT NULL REFERENCES ga_study_subpart ON DELETE CASCADE,
        pvalue double precision,
        qvalue double precision,
        fdr double precision,
@@ -40,6 +41,23 @@ CREATE INDEX ON ga_study_snp(fdr);
 CREATE INDEX ON ga_study_snp(qvalue);
 CREATE INDEX ON ga_study_snp(pvalue);
 
+CREATE TABLE ga_study_chr_pos (
+       chr varchar NOT NULL,
+       pos int NOT NULL,
+       ga_subpart_id INT NOT NULL REFERENCES ga_study_subpart ON DELETE CASCADE,
+       pvalue double precision,
+       qvalue double precision,
+       fdr double precision,
+       significant BOOLEAN DEFAULT FALSE
+);
+
+CREATE UNIQUE INDEX ON ga_study_chr_pos(chr,pos,ga_subpart_id);
+CREATE INDEX ON ga_study_chr_pos(chr,pos);
+CREATE INDEX ON ga_study_chr_pos(fdr);
+CREATE INDEX ON ga_study_chr_pos(qvalue);
+CREATE INDEX ON ga_study_chr_pos(pvalue);
+
+
 
 CREATE OR REPLACE VIEW ga_snp AS
        SELECT gss.snp_id AS snp_id,
@@ -52,3 +70,17 @@ CREATE OR REPLACE VIEW ga_snp AS
               FROM ga_study_snp gss
          JOIN ga_study_subpart gssp ON gss.ga_subpart_id = gssp.ga_subpart_id
          JOIN ga_study gs ON gssp.ga_id=gs.ga_id;
+
+CREATE OR REPLACE VIEW ga_chr_pos AS
+       SELECT gsc.chr AS chr,
+              gsc.pos AS pos,
+              gs.short_name AS study_name,
+              gssp.short_name AS subpart_name,
+              gsc.pvalue AS pvalue,
+              gsc.qvalue AS qvalue,
+              gsc.fdr AS fdr,
+              gsc.significant AS significant
+              FROM ga_study_chr_pos gsc
+         JOIN ga_study_subpart gssp ON gsc.ga_subpart_id = gssp.ga_subpart_id
+         JOIN ga_study gs ON gssp.ga_id=gs.ga_id;
+
-- 
2.39.2