Commit | Line | Data |
407bc860 |
1 | #!/usr/local/bin/perl |
2 | # vim: set ft=perl: |
3 | |
08f434d7 |
4 | use strict; |
2d691ec1 |
5 | use Test::More; |
08f434d7 |
6 | use SQL::Translator; |
7 | use Data::Dumper; |
2d691ec1 |
8 | use Test::SQL::Translator qw(maybe_plan); |
9 | |
10 | BEGIN { |
aee4b66e |
11 | maybe_plan(1, |
d90d858e |
12 | 'SQL::Translator::Parser::PostgreSQL', |
13 | 'SQL::Translator::Producer::MySQL', |
14 | ); |
2d691ec1 |
15 | } |
407bc860 |
16 | |
17 | my $create = q| |
18 | |
aee4b66e |
19 | -- The cvterm module design is based on the ontology |
407bc860 |
20 | |
21 | -- ================================================ |
22 | -- TABLE: cv |
23 | -- ================================================ |
24 | |
25 | create table cv ( |
26 | cv_id serial not null, |
27 | primary key (cv_id), |
28 | cvname varchar not null, |
29 | cvdefinition text, |
30 | |
31 | unique(cvname) |
32 | ); |
33 | |
34 | -- ================================================ |
35 | -- TABLE: cvterm |
36 | -- ================================================ |
37 | |
38 | create table cvterm ( |
39 | cvterm_id serial not null, |
40 | primary key (cvterm_id), |
41 | cv_id int not null, |
42 | foreign key (cv_id) references cv (cv_id), |
43 | name varchar(255) not null, |
44 | termdefinition text, |
407bc860 |
45 | dbxref_id int, |
46 | foreign key (dbxref_id) references dbxref (dbxref_id), |
47 | |
48 | unique(termname, cv_id) |
49 | ); |
50 | create index cvterm_idx1 on cvterm (cv_id); |
ba1a1626 |
51 | -- the primary dbxref for this term. Other dbxrefs may be cvterm_dbxref |
aee4b66e |
52 | -- The unique key on termname, termtype_id ensures that all terms are |
ba1a1626 |
53 | -- unique within a given cv |
54 | |
55 | |
e4a9818d |
56 | COMMENT ON TABLE cvterm IS |
57 | 'A term, class or concept within an ontology |
58 | or controlled vocabulary'; |
59 | COMMENT ON COLUMN cvterm.cv_id IS |
60 | 'The cv/ontology/namespace to which this cvterm belongs'; |
61 | COMMENT ON COLUMN cvterm.name IS |
62 | 'A concise human-readable name describing the meaning of the cvterm'; |
bb2929ae |
63 | COMMENT ON COLUMN cvterm.termdefinition IS |
e4a9818d |
64 | 'A human-readable text definition'; |
65 | COMMENT ON COLUMN cvterm.dbxref_id IS |
66 | 'A human-readable text definition'; |
aee4b66e |
67 | COMMENT ON INDEX cvterm_c1 IS |
e4a9818d |
68 | 'the OBO identifier is globally unique'; |
69 | |
70 | |
ba1a1626 |
71 | -- ================================================ |
72 | -- TABLE: cvrelationship |
73 | -- ================================================ |
74 | |
75 | create table cvrelationship ( |
76 | cvrelationship_id serial not null, |
77 | primary key (cvrelationship_id), |
78 | reltype_id int not null, |
79 | foreign key (reltype_id) references cvterm (cvterm_id), |
80 | subjterm_id int not null, |
81 | foreign key (subjterm_id) references cvterm (cvterm_id), |
82 | objterm_id int not null, |
83 | foreign key (objterm_id) references cvterm (cvterm_id), |
84 | |
85 | unique(reltype_id, subjterm_id, objterm_id) |
86 | ); |
87 | create index cvrelationship_idx1 on cvrelationship (reltype_id); |
88 | create index cvrelationship_idx2 on cvrelationship (subjterm_id); |
89 | create index cvrelationship_idx3 on cvrelationship (objterm_id); |
90 | |
91 | |
92 | -- ================================================ |
93 | -- TABLE: cvpath |
94 | -- ================================================ |
95 | |
96 | create table cvpath ( |
97 | cvpath_id serial not null, |
98 | primary key (cvpath_id), |
99 | reltype_id int, |
100 | foreign key (reltype_id) references cvterm (cvterm_id), |
101 | subjterm_id int not null, |
102 | foreign key (subjterm_id) references cvterm (cvterm_id), |
103 | objterm_id int not null, |
104 | foreign key (objterm_id) references cvterm (cvterm_id), |
105 | cv_id int not null, |
106 | foreign key (cv_id) references cv (cv_id), |
107 | pathdistance int, |
108 | |
109 | unique (subjterm_id, objterm_id) |
110 | ); |
111 | create index cvpath_idx1 on cvpath (reltype_id); |
112 | create index cvpath_idx2 on cvpath (subjterm_id); |
113 | create index cvpath_idx3 on cvpath (objterm_id); |
114 | create index cvpath_idx4 on cvpath (cv_id); |
115 | |
116 | |
117 | -- ================================================ |
118 | -- TABLE: cvtermsynonym |
119 | -- ================================================ |
120 | |
121 | create table cvtermsynonym ( |
122 | cvterm_id int not null, |
123 | foreign key (cvterm_id) references cvterm (cvterm_id), |
124 | termsynonym varchar(255) not null, |
125 | |
126 | unique(cvterm_id, termsynonym) |
127 | ); |
2da7994b |
128 | |
aee4b66e |
129 | -- The table "cvterm_synonym" doesn't exist, so |
2da7994b |
130 | -- creating an index on it screws things up! |
131 | -- create index cvterm_synonym_idx1 on cvterm_synonym (cvterm_id); |
ba1a1626 |
132 | |
133 | -- ================================================ |
134 | -- TABLE: cvterm_dbxref |
135 | -- ================================================ |
136 | |
137 | create table cvterm_dbxref ( |
138 | cvterm_dbxref_id serial not null, |
139 | primary key (cvterm_dbxref_id), |
140 | cvterm_id int not null, |
141 | foreign key (cvterm_id) references cvterm (cvterm_id), |
142 | dbxref_id int not null, |
143 | foreign key (dbxref_id) references dbxref (dbxref_id), |
144 | |
145 | unique(cvterm_id, dbxref_id) |
146 | ); |
147 | create index cvterm_dbxref_idx1 on cvterm_dbxref (cvterm_id); |
148 | create index cvterm_dbxref_idx2 on cvterm_dbxref (dbxref_id); |
407bc860 |
149 | |
e83ad71c |
150 | -- ================================================ |
151 | -- TABLE: cvterm_geom |
152 | -- ================================================ |
153 | |
154 | create table cvterm_geom ( |
155 | cvterm_geom_id serial not null, |
156 | primary key (cvterm_geom_id), |
157 | cvterm_id int not null, |
158 | foreign key (cvterm_id) references cvterm (cvterm_id), |
159 | cvterm_geom geometry, |
aee4b66e |
160 | constraint "enforce_dims_cvterm_geom" CHECK ((st_ndims(cvterm_geom) = 2)), |
161 | constraint "enforce_srid_cvterm_geom" CHECK ((st_srid(cvterm_geom) = -1)), |
162 | constraint "enforce_geotype_cvterm_geom" CHECK ((geometrytype(cvterm_geom) = 'POINT'::text OR cvterm_geom IS NULL)), |
e83ad71c |
163 | unique(cvterm_id) |
164 | ); |
165 | |
166 | |
407bc860 |
167 | |; |
407bc860 |
168 | |
08f434d7 |
169 | my $tr = SQL::Translator->new( |
170 | parser => "PostgreSQL", |
171 | producer => "MySQL" |
407bc860 |
172 | ); |
ba1a1626 |
173 | |
d13e0f32 |
174 | ok( $tr->translate(\$create), 'Translate PG2My' ) or diag($tr->error); |