4 BEGIN { print "1..1\n" }
8 -- The cvterm module design is based on the ontology
10 -- ================================================
12 -- ================================================
15 cv_id serial not null,
17 cvname varchar not null,
23 -- ================================================
25 -- ================================================
28 cvterm_id serial not null,
29 primary key (cvterm_id),
31 foreign key (cv_id) references cv (cv_id),
32 name varchar(255) not null,
35 foreign key (dbxref_id) references dbxref (dbxref_id),
37 unique(termname, cv_id)
39 create index cvterm_idx1 on cvterm (cv_id);
40 -- the primary dbxref for this term. Other dbxrefs may be cvterm_dbxref
41 -- The unique key on termname, termtype_id ensures that all terms are
42 -- unique within a given cv
45 -- ================================================
46 -- TABLE: cvrelationship
47 -- ================================================
49 create table cvrelationship (
50 cvrelationship_id serial not null,
51 primary key (cvrelationship_id),
52 reltype_id int not null,
53 foreign key (reltype_id) references cvterm (cvterm_id),
54 subjterm_id int not null,
55 foreign key (subjterm_id) references cvterm (cvterm_id),
56 objterm_id int not null,
57 foreign key (objterm_id) references cvterm (cvterm_id),
59 unique(reltype_id, subjterm_id, objterm_id)
61 create index cvrelationship_idx1 on cvrelationship (reltype_id);
62 create index cvrelationship_idx2 on cvrelationship (subjterm_id);
63 create index cvrelationship_idx3 on cvrelationship (objterm_id);
66 -- ================================================
68 -- ================================================
71 cvpath_id serial not null,
72 primary key (cvpath_id),
74 foreign key (reltype_id) references cvterm (cvterm_id),
75 subjterm_id int not null,
76 foreign key (subjterm_id) references cvterm (cvterm_id),
77 objterm_id int not null,
78 foreign key (objterm_id) references cvterm (cvterm_id),
80 foreign key (cv_id) references cv (cv_id),
83 unique (subjterm_id, objterm_id)
85 create index cvpath_idx1 on cvpath (reltype_id);
86 create index cvpath_idx2 on cvpath (subjterm_id);
87 create index cvpath_idx3 on cvpath (objterm_id);
88 create index cvpath_idx4 on cvpath (cv_id);
91 -- ================================================
92 -- TABLE: cvtermsynonym
93 -- ================================================
95 create table cvtermsynonym (
96 cvterm_id int not null,
97 foreign key (cvterm_id) references cvterm (cvterm_id),
98 termsynonym varchar(255) not null,
100 unique(cvterm_id, termsynonym)
102 create index cvterm_synonym_idx1 on cvterm_synonym (cvterm_id);
104 -- ================================================
105 -- TABLE: cvterm_dbxref
106 -- ================================================
108 create table cvterm_dbxref (
109 cvterm_dbxref_id serial not null,
110 primary key (cvterm_dbxref_id),
111 cvterm_id int not null,
112 foreign key (cvterm_id) references cvterm (cvterm_id),
113 dbxref_id int not null,
114 foreign key (dbxref_id) references dbxref (dbxref_id),
116 unique(cvterm_id, dbxref_id)
118 create index cvterm_dbxref_idx1 on cvterm_dbxref (cvterm_id);
119 create index cvterm_dbxref_idx2 on cvterm_dbxref (dbxref_id);
126 $SQL::Translator::DEBUG = 0;
128 my $tr = SQL::Translator->new(parser => "PostgreSQL",
130 #producer => "SQL::Translator::Producer::MySQL::translate"
131 #producer => sub { Dumper($_[1]) }
134 print "not " unless ($tr->translate(\$create));
135 print "ok 1 # pointless test -- plz fix me!\n";
140 -- The cvterm module design is based on the ontology
142 -- ================================================
144 -- ================================================
147 cv_id serial not null,
149 cvname varchar not null,
155 -- ================================================
157 -- ================================================
159 create table cvterm (
160 cvterm_id serial not null,
161 primary key (cvterm_id),
163 foreign key (cv_id) references cv (cv_id),
164 name varchar(255) not null,
166 -- the primary dbxref for this term. Other dbxrefs may be cvterm_dbxref
168 foreign key (dbxref_id) references dbxref (dbxref_id),
170 unique(termname, cv_id)
171 -- The unique key on termname, termtype_id ensures that all terms are
172 -- unique within a given cv
174 create index cvterm_idx1 on cvterm (cv_id);
177 -- ================================================
178 -- TABLE: cvrelationship
179 -- ================================================
181 create table cvrelationship (
182 cvrelationship_id serial not null,
183 primary key (cvrelationship_id),
184 reltype_id int not null,
185 foreign key (reltype_id) references cvterm (cvterm_id),
186 subjterm_id int not null,
187 foreign key (subjterm_id) references cvterm (cvterm_id),
188 objterm_id int not null,
189 foreign key (objterm_id) references cvterm (cvterm_id),
191 unique(reltype_id, subjterm_id, objterm_id)
193 create index cvrelationship_idx1 on cvrelationship (reltype_id);
194 create index cvrelationship_idx2 on cvrelationship (subjterm_id);
195 create index cvrelationship_idx3 on cvrelationship (objterm_id);
198 -- ================================================
200 -- ================================================
202 create table cvpath (
203 cvpath_id serial not null,
204 primary key (cvpath_id),
206 foreign key (reltype_id) references cvterm (cvterm_id),
207 subjterm_id int not null,
208 foreign key (subjterm_id) references cvterm (cvterm_id),
209 objterm_id int not null,
210 foreign key (objterm_id) references cvterm (cvterm_id),
212 foreign key (cv_id) references cv (cv_id),
215 unique (subjterm_id, objterm_id)
217 create index cvpath_idx1 on cvpath (reltype_id);
218 create index cvpath_idx2 on cvpath (subjterm_id);
219 create index cvpath_idx3 on cvpath (objterm_id);
220 create index cvpath_idx4 on cvpath (cv_id);
223 -- ================================================
224 -- TABLE: cvtermsynonym
225 -- ================================================
227 create table cvtermsynonym (
228 cvterm_id int not null,
229 foreign key (cvterm_id) references cvterm (cvterm_id),
230 termsynonym varchar(255) not null,
232 unique(cvterm_id, termsynonym)
234 create index cvterm_synonym_idx1 on cvterm_synonym (cvterm_id);
236 -- ================================================
237 -- TABLE: cvterm_dbxref
238 -- ================================================
240 create table cvterm_dbxref (
241 cvterm_dbxref_id serial not null,
242 primary key (cvterm_dbxref_id),
243 cvterm_id int not null,
244 foreign key (cvterm_id) references cvterm (cvterm_id),
245 dbxref_id int not null,
246 foreign key (dbxref_id) references dbxref (dbxref_id),
248 unique(cvterm_id, dbxref_id)
250 create index cvterm_dbxref_idx1 on cvterm_dbxref (cvterm_id);
251 create index cvterm_dbxref_idx2 on cvterm_dbxref (dbxref_id);