add association results sql

diff --git a/schema/extra_schema/association_results.sql b/schema/extra_schema/association_results.sql
new file mode 100644 (file)
index 0000000..822387b
--- /dev/null
+++ b/schema/extra_schema/association_results.sql
@@ -0,0 +1,54 @@
+DROP VIEW ga_snp;
+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,
+       short_name  TEXT NOT NULL,
+       full_name   TEXT NOT NULL,
+       description TEXT NOT NULL DEFAULT '',
+       date       TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
+);
+
+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,
+       short_name TEXT NOT NULL,
+       full_name TEXT NOT NULL,
+       description TEXT NOT NULL DEFAULT ''
+);
+
+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,
+       pvalue double precision,
+       qvalue double precision,
+       fdr double precision,
+       significant BOOLEAN DEFAULT FALSE
+);
+
+CREATE UNIQUE INDEX ON ga_study_snp(snp_id,ga_subpart_id);
+CREATE INDEX ON ga_study_snp(snp_id);
+CREATE INDEX ON ga_study_snp(fdr);
+CREATE INDEX ON ga_study_snp(qvalue);
+CREATE INDEX ON ga_study_snp(pvalue);
+
+
+CREATE OR REPLACE VIEW ga_snp AS
+       SELECT gss.snp_id AS snp_id,
+              gs.short_name AS study_name,
+              gssp.short_name AS subpart_name,
+              gss.pvalue AS pvalue,
+              gss.qvalue AS qvalue,
+              gss.fdr AS fdr,
+              gss.significant AS significant
+              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;