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