2 DROP TABLE ga_study_snp CASCADE;
3 DROP TABLE ga_study_subpart CASCADE;
4 DROP TABLE ga_study CASCADE;
7 CREATE TABLE ga_study (
8 ga_id int NOT NULL PRIMARY KEY,
9 short_name TEXT NOT NULL,
10 full_name TEXT NOT NULL,
11 description TEXT NOT NULL DEFAULT '',
12 date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
15 CREATE UNIQUE INDEX ON ga_study(short_name);
17 CREATE TABLE ga_study_subpart (
18 ga_subpart_id INT NOT NULL PRIMARY KEY,
19 ga_id INT NOT NULL REFERENCES ga_study,
20 short_name TEXT NOT NULL,
21 full_name TEXT NOT NULL,
22 description TEXT NOT NULL DEFAULT ''
25 CREATE UNIQUE INDEX ON ga_study_subpart(ga_id,short_name);
26 CREATE INDEX ON ga_study_subpart(ga_id);
28 CREATE TABLE ga_study_snp (
30 ga_subpart_id INT NOT NULL REFERENCES ga_study_subpart,
31 pvalue double precision,
32 qvalue double precision,
34 significant BOOLEAN DEFAULT FALSE
37 CREATE UNIQUE INDEX ON ga_study_snp(snp_id,ga_subpart_id);
38 CREATE INDEX ON ga_study_snp(snp_id);
39 CREATE INDEX ON ga_study_snp(fdr);
40 CREATE INDEX ON ga_study_snp(qvalue);
41 CREATE INDEX ON ga_study_snp(pvalue);
44 CREATE OR REPLACE VIEW ga_snp AS
45 SELECT gss.snp_id AS snp_id,
46 gs.short_name AS study_name,
47 gssp.short_name AS subpart_name,
51 gss.significant AS significant
53 JOIN ga_study_subpart gssp ON gss.ga_subpart_id = gssp.ga_subpart_id
54 JOIN ga_study gs ON gssp.ga_id=gs.ga_id;