3 DROP TABLE ga_study_snp CASCADE;
4 DROP TABLE ga_study_subpart CASCADE;
5 DROP TABLE ga_study CASCADE;
8 CREATE TABLE ga_study (
9 ga_id SERIAL PRIMARY KEY,
10 short_name TEXT NOT NULL,
11 full_name TEXT NOT NULL,
12 description TEXT NOT NULL DEFAULT '',
13 date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
16 CREATE UNIQUE INDEX ON ga_study(short_name);
18 CREATE TABLE ga_study_subpart (
19 ga_subpart_id SERIAL PRIMARY KEY,
20 ga_id INT NOT NULL REFERENCES ga_study ON DELETE CASCADE,
21 short_name TEXT NOT NULL,
22 full_name TEXT NOT NULL,
23 description TEXT NOT NULL DEFAULT ''
26 CREATE UNIQUE INDEX ON ga_study_subpart(ga_id,short_name);
27 CREATE INDEX ON ga_study_subpart(ga_id);
29 CREATE TABLE ga_study_snp (
31 ga_subpart_id INT NOT NULL REFERENCES ga_study_subpart ON DELETE CASCADE,
32 pvalue double precision,
33 qvalue double precision,
35 significant BOOLEAN DEFAULT FALSE
38 CREATE UNIQUE INDEX ON ga_study_snp(snp_id,ga_subpart_id);
39 CREATE INDEX ON ga_study_snp(snp_id);
40 CREATE INDEX ON ga_study_snp(fdr);
41 CREATE INDEX ON ga_study_snp(qvalue);
42 CREATE INDEX ON ga_study_snp(pvalue);
44 CREATE TABLE ga_study_chr_pos (
47 ga_subpart_id INT NOT NULL REFERENCES ga_study_subpart ON DELETE CASCADE,
48 pvalue double precision,
49 qvalue double precision,
51 significant BOOLEAN DEFAULT FALSE
54 CREATE UNIQUE INDEX ON ga_study_chr_pos(chr,pos,ga_subpart_id);
55 CREATE INDEX ON ga_study_chr_pos(chr,pos);
56 CREATE INDEX ON ga_study_chr_pos(fdr);
57 CREATE INDEX ON ga_study_chr_pos(qvalue);
58 CREATE INDEX ON ga_study_chr_pos(pvalue);
62 CREATE OR REPLACE VIEW ga_snp AS
63 SELECT gss.snp_id AS snp_id,
64 gs.short_name AS study_name,
65 gssp.short_name AS subpart_name,
69 gss.significant AS significant
71 JOIN ga_study_subpart gssp ON gss.ga_subpart_id = gssp.ga_subpart_id
72 JOIN ga_study gs ON gssp.ga_id=gs.ga_id;
74 CREATE OR REPLACE VIEW ga_chr_pos AS
75 SELECT gsc.chr AS chr,
77 gs.short_name AS study_name,
78 gssp.short_name AS subpart_name,
82 gsc.significant AS significant
83 FROM ga_study_chr_pos gsc
84 JOIN ga_study_subpart gssp ON gsc.ga_subpart_id = gssp.ga_subpart_id
85 JOIN ga_study gs ON gssp.ga_id=gs.ga_id;