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
|
-- $Id: library.sql,v 1.10 2008-03-25 16:00:43 emmert Exp $
-- =================================================================
-- Dependencies:
--
-- :import feature from sequence
-- :import synonym from sequence
-- :import cvterm from cv
-- :import pub from pub
-- :import organism from organism
-- =================================================================
-- ================================================
-- TABLE: library
-- ================================================
create table library (
library_id serial not null,
primary key (library_id),
organism_id int not null,
foreign key (organism_id) references organism (organism_id),
name varchar(255),
uniquename text not null,
type_id int not null,
foreign key (type_id) references cvterm (cvterm_id),
is_obsolete int not null default 0,
timeaccessioned timestamp not null default current_timestamp,
timelastmodified timestamp not null default current_timestamp,
constraint library_c1 unique (organism_id,uniquename,type_id)
);
create index library_name_ind1 on library(name);
create index library_idx1 on library (organism_id);
create index library_idx2 on library (type_id);
create index library_idx3 on library (uniquename);
COMMENT ON COLUMN library.type_id IS 'The type_id foreign key links
to a controlled vocabulary of library types. Examples of this would be: "cDNA_library" or "genomic_library"';
-- ================================================
-- TABLE: library_synonym
-- ================================================
create table library_synonym (
library_synonym_id serial not null,
primary key (library_synonym_id),
synonym_id int not null,
foreign key (synonym_id) references synonym (synonym_id) on delete cascade INITIALLY DEFERRED,
library_id int not null,
foreign key (library_id) references library (library_id) on delete cascade INITIALLY DEFERRED,
pub_id int not null,
foreign key (pub_id) references pub (pub_id) on delete cascade INITIALLY DEFERRED,
is_current boolean not null default 'true',
is_internal boolean not null default 'false',
constraint library_synonym_c1 unique (synonym_id,library_id,pub_id)
);
create index library_synonym_idx1 on library_synonym (synonym_id);
create index library_synonym_idx2 on library_synonym (library_id);
create index library_synonym_idx3 on library_synonym (pub_id);
COMMENT ON COLUMN library_synonym.is_current IS 'The is_current bit indicates whether the linked synonym is the current -official- symbol for the linked library.';
COMMENT ON COLUMN library_synonym.pub_id IS 'The pub_id link is for
relating the usage of a given synonym to the publication in which it was used.';
COMMENT ON COLUMN library_synonym.is_internal IS 'Typically a synonym
exists so that somebody querying the database with an obsolete name
can find the object they are looking for under its current name. If
the synonym has been used publicly and deliberately (e.g. in a paper), it my also be listed in reports as a synonym. If the synonym was not used deliberately (e.g., there was a typo which went public), then the is_internal bit may be set to "true" so that it is known that the synonym is "internal" and should be queryable but should not be listed in reports as a valid synonym.';
-- ================================================
-- TABLE: library_pub
-- ================================================
create table library_pub (
library_pub_id serial not null,
primary key (library_pub_id),
library_id int not null,
foreign key (library_id) references library (library_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 library_pub_c1 unique (library_id,pub_id)
);
create index library_pub_idx1 on library_pub (library_id);
create index library_pub_idx2 on library_pub (pub_id);
-- ================================================
-- TABLE: libraryprop
-- ================================================
create table libraryprop (
libraryprop_id serial not null,
primary key (libraryprop_id),
library_id int not null,
foreign key (library_id) references library (library_id) on delete cascade INITIALLY DEFERRED,
type_id int not null,
foreign key (type_id) references cvterm (cvterm_id),
value text null,
rank int not null default 0,
constraint libraryprop_c1 unique (library_id,type_id,rank)
);
create index libraryprop_idx1 on libraryprop (library_id);
create index libraryprop_idx2 on libraryprop (type_id);
-- ================================================
-- TABLE: libraryprop_pub
-- ================================================
create table libraryprop_pub (
libraryprop_pub_id serial not null,
primary key (libraryprop_pub_id),
libraryprop_id int not null,
foreign key (libraryprop_id) references libraryprop (libraryprop_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 libraryprop_pub_c1 unique (libraryprop_id,pub_id)
);
create index libraryprop_pub_idx1 on libraryprop_pub (libraryprop_id);
create index libraryprop_pub_idx2 on libraryprop_pub (pub_id);
-- ================================================
-- TABLE: library_cvterm
-- ================================================
create table library_cvterm (
library_cvterm_id serial not null,
primary key (library_cvterm_id),
library_id int not null,
foreign key (library_id) references library (library_id) on delete cascade INITIALLY DEFERRED,
cvterm_id int not null,
foreign key (cvterm_id) references cvterm (cvterm_id),
pub_id int not null,
foreign key (pub_id) references pub (pub_id),
constraint library_cvterm_c1 unique (library_id,cvterm_id,pub_id)
);
create index library_cvterm_idx1 on library_cvterm (library_id);
create index library_cvterm_idx2 on library_cvterm (cvterm_id);
create index library_cvterm_idx3 on library_cvterm (pub_id);
COMMENT ON TABLE library_cvterm IS 'The table library_cvterm links a library to controlled vocabularies which describe the library. For instance, there might be a link to the anatomy cv for "head" or "testes" for a head or testes library.';
-- ================================================
-- TABLE: library_feature
-- ================================================
create table library_feature (
library_feature_id serial not null,
primary key (library_feature_id),
library_id int not null,
foreign key (library_id) references library (library_id) on delete cascade INITIALLY DEFERRED,
feature_id int not null,
foreign key (feature_id) references feature (feature_id) on delete cascade INITIALLY DEFERRED,
constraint library_feature_c1 unique (library_id,feature_id)
);
create index library_feature_idx1 on library_feature (library_id);
create index library_feature_idx2 on library_feature (feature_id);
COMMENT ON TABLE library_feature IS 'library_feature links a library to the clones which are contained in the library. Examples of such linked features might be "cDNA_clone" or "genomic_clone".';
-- ================================================
-- TABLE: library_dbxref
-- ================================================
create table library_dbxref (
library_dbxref_id serial not null,
primary key (library_dbxref_id),
library_id int not null,
foreign key (library_id) references library (library_id) on delete cascade INITIALLY DEFERRED,
dbxref_id int not null,
foreign key (dbxref_id) references dbxref (dbxref_id) on delete cascade INITIALLY DEFERRED,
is_current boolean not null default 'true',
constraint library_dbxref_c1 unique (library_id,dbxref_id)
);
create index library_dbxref_idx1 on library_dbxref (library_id);
create index library_dbxref_idx2 on library_dbxref (dbxref_id);
|
