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
190
191
192
193
|
-- $Id: expression.sql,v 1.14 2007-03-23 15:18:02 scottcain Exp $
-- ==========================================
-- Chado expression module
--
-- =================================================================
-- Dependencies:
--
-- :import feature from sequence
-- :import cvterm from cv
-- :import pub from pub
-- =================================================================
-- ================================================
-- TABLE: expression
-- ================================================
create table expression (
expression_id serial not null,
primary key (expression_id),
uniquename text not null,
md5checksum character(32),
description text,
constraint expression_c1 unique(uniquename)
);
COMMENT ON TABLE expression IS 'The expression table is essentially a bridge table.';
-- ================================================
-- TABLE: expression_cvterm
-- ================================================
create table expression_cvterm (
expression_cvterm_id serial not null,
primary key (expression_cvterm_id),
expression_id int not null,
foreign key (expression_id) references expression (expression_id) on delete cascade INITIALLY DEFERRED,
cvterm_id int not null,
foreign key (cvterm_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
rank int not null default 0,
cvterm_type_id int not null,
foreign key (cvterm_type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
constraint expression_cvterm_c1 unique(expression_id,cvterm_id,cvterm_type_id)
);
create index expression_cvterm_idx1 on expression_cvterm (expression_id);
create index expression_cvterm_idx2 on expression_cvterm (cvterm_id);
create index expression_cvterm_idx3 on expression_cvterm (cvterm_type_id);
--================================================
-- TABLE: expression_cvtermprop
-- ================================================
create table expression_cvtermprop (
expression_cvtermprop_id serial not null,
primary key (expression_cvtermprop_id),
expression_cvterm_id int not null,
foreign key (expression_cvterm_id) references expression_cvterm (expression_cvterm_id) on delete cascade INITIALLY DEFERRED,
type_id int not null,
foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
value text null,
rank int not null default 0,
constraint expression_cvtermprop_c1 unique (expression_cvterm_id,type_id,rank)
);
create index expression_cvtermprop_idx1 on expression_cvtermprop (expression_cvterm_id);
create index expression_cvtermprop_idx2 on expression_cvtermprop (type_id);
COMMENT ON TABLE expression_cvtermprop IS 'Extensible properties for
expression to cvterm associations. Examples: qualifiers.';
COMMENT ON COLUMN expression_cvtermprop.type_id IS 'The name of the
property/slot is a cvterm. The meaning of the property is defined in
that cvterm. For example, cvterms may come from the FlyBase miscellaneous cv.';
COMMENT ON COLUMN expression_cvtermprop.value IS 'The value of the
property, represented as text. Numeric values are converted to their
text representation. This is less efficient than using native database
types, but is easier to query.';
COMMENT ON COLUMN expression_cvtermprop.rank IS 'Property-Value
ordering. Any expression_cvterm can have multiple values for any particular
property type - these are ordered in a list using rank, counting from
zero. For properties that are single-valued rather than multi-valued,
the default 0 value should be used.';
-- ================================================
-- TABLE: expressionprop
-- ================================================
create table expressionprop (
expressionprop_id serial not null,
primary key (expressionprop_id),
expression_id int not null,
foreign key (expression_id) references expression (expression_id) on delete cascade INITIALLY DEFERRED,
type_id int not null,
foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
value text null,
rank int not null default 0,
constraint expressionprop_c1 unique (expression_id,type_id,rank)
);
create index expressionprop_idx1 on expressionprop (expression_id);
create index expressionprop_idx2 on expressionprop (type_id);
-- ================================================
-- TABLE: expression_pub
-- ================================================
create table expression_pub (
expression_pub_id serial not null,
primary key (expression_pub_id),
expression_id int not null,
foreign key (expression_id) references expression (expression_id) on delete cascade INITIALLY DEFERRED,
pub_id int not null,
foreign key (pub_id) references pub (pub_id) on delete cascade INITIALLY DEFERRED,
constraint expression_pub_c1 unique(expression_id,pub_id)
);
create index expression_pub_idx1 on expression_pub (expression_id);
create index expression_pub_idx2 on expression_pub (pub_id);
-- ================================================
-- TABLE: feature_expression
-- ================================================
create table feature_expression (
feature_expression_id serial not null,
primary key (feature_expression_id),
expression_id int not null,
foreign key (expression_id) references expression (expression_id) on delete cascade INITIALLY DEFERRED,
feature_id int not null,
foreign key (feature_id) references feature (feature_id) on delete cascade INITIALLY DEFERRED,
pub_id int not null,
foreign key (pub_id) references pub (pub_id) on delete cascade INITIALLY DEFERRED,
constraint feature_expression_c1 unique(expression_id,feature_id,pub_id)
);
create index feature_expression_idx1 on feature_expression (expression_id);
create index feature_expression_idx2 on feature_expression (feature_id);
create index feature_expression_idx3 on feature_expression (pub_id);
-- ================================================
-- TABLE: feature_expressionprop
-- ================================================
create table feature_expressionprop (
feature_expressionprop_id serial not null,
primary key (feature_expressionprop_id),
feature_expression_id int not null,
foreign key (feature_expression_id) references feature_expression (feature_expression_id) on delete cascade INITIALLY DEFERRED,
type_id int not null,
foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
value text null,
rank int not null default 0,
constraint feature_expressionprop_c1 unique (feature_expression_id,type_id,rank)
);
create index feature_expressionprop_idx1 on feature_expressionprop (feature_expression_id);
create index feature_expressionprop_idx2 on feature_expressionprop (type_id);
COMMENT ON TABLE feature_expressionprop IS 'Extensible properties for
feature_expression (comments, for example). Modeled on feature_cvtermprop.';
-- ================================================
-- TABLE: eimage
-- ================================================
create table eimage (
eimage_id serial not null,
primary key (eimage_id),
eimage_data text,
eimage_type varchar(255) not null,
image_uri varchar(255)
);
COMMENT ON COLUMN eimage.eimage_data IS 'We expect images in eimage_data (e.g. JPEGs) to be uuencoded.';
COMMENT ON COLUMN eimage.eimage_type IS 'Describes the type of data in eimage_data.';
-- ================================================
-- TABLE: expression_image
-- ================================================
create table expression_image (
expression_image_id serial not null,
primary key (expression_image_id),
expression_id int not null,
foreign key (expression_id) references expression (expression_id) on delete cascade INITIALLY DEFERRED,
eimage_id int not null,
foreign key (eimage_id) references eimage (eimage_id) on delete cascade INITIALLY DEFERRED,
constraint expression_image_c1 unique(expression_id,eimage_id)
);
create index expression_image_idx1 on expression_image (expression_id);
create index expression_image_idx2 on expression_image (eimage_id);
|
