Commit | Line | Data |
407bc860 |
1 | #!/usr/local/bin/perl |
2 | # vim: set ft=perl: |
3 | |
4 | BEGIN { print "1..1\n" } |
5 | |
6 | my $create = q| |
7 | |
8 | -- The cvterm module design is based on the ontology |
9 | |
10 | -- ================================================ |
11 | -- TABLE: cv |
12 | -- ================================================ |
13 | |
14 | create table cv ( |
15 | cv_id serial not null, |
16 | primary key (cv_id), |
17 | cvname varchar not null, |
18 | cvdefinition text, |
19 | |
20 | unique(cvname) |
21 | ); |
22 | |
23 | -- ================================================ |
24 | -- TABLE: cvterm |
25 | -- ================================================ |
26 | |
27 | create table cvterm ( |
28 | cvterm_id serial not null, |
29 | primary key (cvterm_id), |
30 | cv_id int not null, |
31 | foreign key (cv_id) references cv (cv_id), |
32 | name varchar(255) not null, |
33 | termdefinition text, |
407bc860 |
34 | dbxref_id int, |
35 | foreign key (dbxref_id) references dbxref (dbxref_id), |
36 | |
37 | unique(termname, cv_id) |
38 | ); |
39 | create index cvterm_idx1 on cvterm (cv_id); |
ba1a1626 |
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 |
43 | |
44 | |
45 | -- ================================================ |
46 | -- TABLE: cvrelationship |
47 | -- ================================================ |
48 | |
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), |
58 | |
59 | unique(reltype_id, subjterm_id, objterm_id) |
60 | ); |
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); |
64 | |
65 | |
66 | -- ================================================ |
67 | -- TABLE: cvpath |
68 | -- ================================================ |
69 | |
70 | create table cvpath ( |
71 | cvpath_id serial not null, |
72 | primary key (cvpath_id), |
73 | reltype_id int, |
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), |
79 | cv_id int not null, |
80 | foreign key (cv_id) references cv (cv_id), |
81 | pathdistance int, |
82 | |
83 | unique (subjterm_id, objterm_id) |
84 | ); |
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); |
89 | |
90 | |
91 | -- ================================================ |
92 | -- TABLE: cvtermsynonym |
93 | -- ================================================ |
94 | |
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, |
99 | |
100 | unique(cvterm_id, termsynonym) |
101 | ); |
102 | create index cvterm_synonym_idx1 on cvterm_synonym (cvterm_id); |
103 | |
104 | -- ================================================ |
105 | -- TABLE: cvterm_dbxref |
106 | -- ================================================ |
107 | |
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), |
115 | |
116 | unique(cvterm_id, dbxref_id) |
117 | ); |
118 | create index cvterm_dbxref_idx1 on cvterm_dbxref (cvterm_id); |
119 | create index cvterm_dbxref_idx2 on cvterm_dbxref (dbxref_id); |
407bc860 |
120 | |
121 | |; |
407bc860 |
122 | |
123 | use SQL::Translator; |
124 | use Data::Dumper; |
125 | |
126 | $SQL::Translator::DEBUG = 1; |
127 | |
128 | my $tr = SQL::Translator->new(parser => "PostgreSQL", |
129 | producer => "MySQL" |
130 | #producer => "SQL::Translator::Producer::MySQL::translate" |
131 | #producer => sub { Dumper($_[1]) } |
132 | ); |
133 | |
134 | print "not " unless ($tr->translate(\$create)); |
135 | print "ok 1 # pointless test -- plz fix me!\n"; |
136 | |
137 | __END__ |
138 | __DATA__ |
139 | |
140 | -- The cvterm module design is based on the ontology |
141 | |
142 | -- ================================================ |
143 | -- TABLE: cv |
144 | -- ================================================ |
145 | |
146 | create table cv ( |
147 | cv_id serial not null, |
148 | primary key (cv_id), |
149 | cvname varchar not null, |
150 | cvdefinition text, |
151 | |
152 | unique(cvname) |
153 | ); |
154 | |
155 | -- ================================================ |
156 | -- TABLE: cvterm |
157 | -- ================================================ |
158 | |
159 | create table cvterm ( |
160 | cvterm_id serial not null, |
161 | primary key (cvterm_id), |
162 | cv_id int not null, |
163 | foreign key (cv_id) references cv (cv_id), |
164 | name varchar(255) not null, |
165 | termdefinition text, |
166 | -- the primary dbxref for this term. Other dbxrefs may be cvterm_dbxref |
167 | dbxref_id int, |
168 | foreign key (dbxref_id) references dbxref (dbxref_id), |
169 | |
170 | unique(termname, cv_id) |
171 | -- The unique key on termname, termtype_id ensures that all terms are |
172 | -- unique within a given cv |
173 | ); |
174 | create index cvterm_idx1 on cvterm (cv_id); |
175 | |
176 | |
177 | -- ================================================ |
178 | -- TABLE: cvrelationship |
179 | -- ================================================ |
180 | |
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), |
190 | |
191 | unique(reltype_id, subjterm_id, objterm_id) |
192 | ); |
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); |
196 | |
197 | |
198 | -- ================================================ |
199 | -- TABLE: cvpath |
200 | -- ================================================ |
201 | |
202 | create table cvpath ( |
203 | cvpath_id serial not null, |
204 | primary key (cvpath_id), |
205 | reltype_id int, |
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), |
211 | cv_id int not null, |
212 | foreign key (cv_id) references cv (cv_id), |
213 | pathdistance int, |
214 | |
215 | unique (subjterm_id, objterm_id) |
216 | ); |
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); |
221 | |
222 | |
223 | -- ================================================ |
224 | -- TABLE: cvtermsynonym |
225 | -- ================================================ |
226 | |
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, |
231 | |
232 | unique(cvterm_id, termsynonym) |
233 | ); |
234 | create index cvterm_synonym_idx1 on cvterm_synonym (cvterm_id); |
235 | |
236 | -- ================================================ |
237 | -- TABLE: cvterm_dbxref |
238 | -- ================================================ |
239 | |
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), |
247 | |
248 | unique(cvterm_id, dbxref_id) |
249 | ); |
250 | create index cvterm_dbxref_idx1 on cvterm_dbxref (cvterm_id); |
251 | create index cvterm_dbxref_idx2 on cvterm_dbxref (dbxref_id); |
252 | |
ba1a1626 |
253 | |