1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
|
-- ==========================================
-- Chado project module. Used primarily by other Chado modules to
-- group experiments, stocks, and so forth that are associated with
-- eachother administratively or organizationally.
--
-- =================================================================
-- Dependencies:
--
-- :import cvterm from cv
-- :import pub from pub
-- :import contact from contact
-- :import dbxref from db
-- :import analysis from companalysis
-- :import feature from sequence
-- :import stock from stock
-- =================================================================
-- ================================================
-- TABLE: project
-- ================================================
create table project (
project_id bigserial not null,
primary key (project_id),
name varchar(255) not null,
description text,
constraint project_c1 unique (name)
);
COMMENT ON TABLE project IS
'A project is some kind of planned endeavor. Used primarily by other
Chado modules to group experiments, stocks, and so forth that are
associated with eachother administratively or organizationally.';
-- ================================================
-- TABLE: projectprop
-- ================================================
CREATE TABLE projectprop (
projectprop_id bigserial NOT NULL,
PRIMARY KEY (projectprop_id),
project_id bigint NOT NULL,
FOREIGN KEY (project_id) REFERENCES project (project_id) ON DELETE CASCADE,
type_id bigint NOT NULL,
FOREIGN KEY (type_id) REFERENCES cvterm (cvterm_id) ON DELETE CASCADE,
value text,
rank int not null default 0,
CONSTRAINT projectprop_c1 UNIQUE (project_id, type_id, rank)
);
COMMENT ON TABLE project IS
'Standard Chado flexible property table for projects.';
-- ================================================
-- TABLE: project_relationship
-- ================================================
CREATE TABLE project_relationship (
project_relationship_id bigserial NOT NULL,
PRIMARY KEY (project_relationship_id),
subject_project_id bigint NOT NULL,
FOREIGN KEY (subject_project_id) REFERENCES project (project_id) ON DELETE CASCADE,
object_project_id bigint NOT NULL,
FOREIGN KEY (object_project_id) REFERENCES project (project_id) ON DELETE CASCADE,
type_id bigint NOT NULL,
FOREIGN KEY (type_id) REFERENCES cvterm (cvterm_id) ON DELETE RESTRICT,
CONSTRAINT project_relationship_c1 UNIQUE (subject_project_id, object_project_id, type_id)
);
COMMENT ON TABLE project_relationship IS
'Linking table for relating projects to each other. For example, a
given project could be composed of several smaller subprojects';
COMMENT ON COLUMN project_relationship.type_id IS
'The cvterm type of the relationship being stated, such as "part of".';
-- ================================================
-- TABLE: project_pub
-- ================================================
create table project_pub (
project_pub_id bigserial not null,
primary key (project_pub_id),
project_id bigint not null,
foreign key (project_id) references project (project_id) on delete cascade INITIALLY DEFERRED,
pub_id bigint not null,
foreign key (pub_id) references pub (pub_id) on delete cascade INITIALLY DEFERRED,
constraint project_pub_c1 unique (project_id,pub_id)
);
create index project_pub_idx1 on project_pub (project_id);
create index project_pub_idx2 on project_pub (pub_id);
COMMENT ON TABLE project_pub IS 'Linking table for associating projects and publications.';
-- ================================================
-- TABLE: project_contact
-- ================================================
create table project_contact (
project_contact_id bigserial not null,
primary key (project_contact_id),
project_id bigint not null,
foreign key (project_id) references project (project_id) on delete cascade INITIALLY DEFERRED,
contact_id bigint not null,
foreign key (contact_id) references contact (contact_id) on delete cascade INITIALLY DEFERRED,
constraint project_contact_c1 unique (project_id,contact_id)
);
create index project_contact_idx1 on project_contact (project_id);
create index project_contact_idx2 on project_contact (contact_id);
COMMENT ON TABLE project_contact IS 'Linking table for associating projects and contacts.';
-- ================================================
-- TABLE: project_dbxref
-- ================================================
create table project_dbxref (
project_dbxref_id bigserial not null,
project_id bigint not null,
dbxref_id bigint not null,
is_current boolean not null default 'true',
primary key (project_dbxref_id),
foreign key (dbxref_id) references dbxref (dbxref_id) on delete cascade INITIALLY DEFERRED,
foreign key (project_id) references project (project_id) on delete cascade INITIALLY DEFERRED,
constraint project_dbxref_c1 unique (project_id,dbxref_id)
);
create index project_dbxref_idx1 on project_dbxref (project_id);
create index project_dbxref_idx2 on project_dbxref (dbxref_id);
COMMENT ON TABLE project_dbxref IS 'project_dbxref links a project to dbxrefs.';
COMMENT ON COLUMN project_dbxref.is_current IS 'The is_current boolean indicates whether the linked dbxref is the current -official- dbxref for the linked project.';
-- ================================================
-- TABLE: project_analysis
-- ================================================
create table project_analysis (
project_analysis_id bigserial not null,
primary key (project_analysis_id),
project_id bigint not null,
foreign key (project_id) references project (project_id) on delete cascade INITIALLY DEFERRED,
analysis_id bigint not null,
foreign key (analysis_id) references analysis (analysis_id) on delete cascade INITIALLY DEFERRED,
rank int not null default 0,
constraint project_analysis_c1 unique (project_id,analysis_id)
);
create index project_analysis_idx1 on project_analysis (project_id);
create index project_analysis_idx2 on project_analysis (analysis_id);
COMMENT ON TABLE project_analysis IS 'Links an analysis to a project that may contain multiple analyses.
The rank column can be used to specify a simple ordering in which analyses were executed.';
-- ================================================
-- TABLE: project_feature
-- ================================================
CREATE TABLE project_feature (
project_feature_id bigserial primary key NOT NULL,
feature_id bigint NOT NULL,
project_id bigint NOT NULL,
CONSTRAINT project_feature_c1 UNIQUE (feature_id, project_id),
FOREIGN KEY (feature_id) REFERENCES feature(feature_id) ON DELETE CASCADE,
FOREIGN KEY (project_id) REFERENCES project(project_id) ON DELETE CASCADE
);
CREATE INDEX project_feature_idx1 ON project_feature USING btree (feature_id);
CREATE INDEX project_feature_idx2 ON project_feature USING btree (project_id);
COMMENT ON TABLE project_feature IS 'This table is intended associate records in the feature table with a project.';
-- ================================================
-- TABLE: project_stock
-- ================================================
CREATE TABLE project_stock (
project_stock_id bigserial primary key NOT NULL,
stock_id bigint NOT NULL,
project_id bigint NOT NULL,
CONSTRAINT project_stock_c1 UNIQUE (stock_id, project_id),
FOREIGN KEY (stock_id) REFERENCES stock(stock_id) ON DELETE CASCADE,
FOREIGN KEY (project_id) REFERENCES project(project_id) ON DELETE CASCADE
);
CREATE INDEX project_stock_idx1 ON project_stock USING btree (stock_id);
CREATE INDEX project_stock_idx2 ON project_stock USING btree (project_id);
COMMENT ON TABLE project_stock IS 'This table is intended associate records in the stock table with a project.';
|
