Commit | Line | Data |
91c62709 |
1 | use strict; |
2 | use warnings; |
3 | use Test::More; |
4 | use SQL::Translator; |
5 | use Data::Dumper; |
6 | |
7 | my $create = q| |
8 | |
9 | -- The cvterm module design is based on the ontology |
10 | |
11 | -- ================================================ |
12 | -- TABLE: cv |
13 | -- ================================================ |
14 | |
15 | create table cv ( |
16 | cv_id serial not null, |
17 | primary key (cv_id), |
18 | cvname varchar not null, |
19 | cvdefinition text, |
20 | |
21 | unique(cvname) |
22 | ); |
23 | |
24 | -- ================================================ |
25 | -- TABLE: cvterm |
26 | -- ================================================ |
27 | |
28 | create table cvterm ( |
29 | cvterm_id serial not null, |
30 | primary key (cvterm_id), |
31 | cv_id int not null, |
32 | foreign key (cv_id) references cv (cv_id), |
33 | termname varchar(255) not null, |
34 | termdefinition text, |
35 | dbxref_id int, |
36 | foreign key (dbxref_id) references dbxref (dbxref_id), |
37 | |
38 | unique(termname, cv_id) |
39 | ); |
40 | create index cvterm_idx1 on cvterm (cv_id); |
41 | -- the primary dbxref for this term. Other dbxrefs may be cvterm_dbxref |
42 | -- The unique key on termname, termtype_id ensures that all terms are |
43 | -- unique within a given cv |
44 | |
45 | |
46 | COMMENT ON TABLE cvterm IS |
47 | 'A term, class or concept within an ontology |
48 | or controlled vocabulary'; |
49 | COMMENT ON COLUMN cvterm.cv_id IS |
50 | 'The cv/ontology/namespace to which this cvterm belongs'; |
51 | COMMENT ON COLUMN cvterm.termname IS |
52 | 'A concise human-readable name describing the meaning of the cvterm'; |
53 | COMMENT ON COLUMN cvterm.termdefinition IS |
54 | 'A human-readable text definition'; |
55 | COMMENT ON COLUMN cvterm.dbxref_id IS |
56 | 'A human-readable text definition'; |
57 | COMMENT ON INDEX cvterm_c1 IS |
58 | 'the OBO identifier is globally unique'; |
59 | |
60 | |
61 | -- ================================================ |
62 | -- TABLE: cvrelationship |
63 | -- ================================================ |
64 | |
65 | create table cvrelationship ( |
66 | cvrelationship_id serial not null, |
67 | primary key (cvrelationship_id), |
68 | reltype_id int not null, |
69 | foreign key (reltype_id) references cvterm (cvterm_id), |
70 | subjterm_id int not null, |
71 | foreign key (subjterm_id) references cvterm (cvterm_id), |
72 | objterm_id int not null, |
73 | foreign key (objterm_id) references cvterm (cvterm_id), |
74 | |
75 | unique(reltype_id, subjterm_id, objterm_id) |
76 | ); |
77 | create index cvrelationship_idx1 on cvrelationship (reltype_id); |
78 | create index cvrelationship_idx2 on cvrelationship (subjterm_id); |
79 | create index cvrelationship_idx3 on cvrelationship (objterm_id); |
80 | |
81 | |
82 | -- ================================================ |
83 | -- TABLE: cvpath |
84 | -- ================================================ |
85 | |
86 | create table cvpath ( |
87 | cvpath_id serial not null, |
88 | primary key (cvpath_id), |
89 | reltype_id int, |
90 | foreign key (reltype_id) references cvterm (cvterm_id), |
91 | subjterm_id int not null, |
92 | foreign key (subjterm_id) references cvterm (cvterm_id), |
93 | objterm_id int not null, |
94 | foreign key (objterm_id) references cvterm (cvterm_id), |
95 | cv_id int not null, |
96 | foreign key (cv_id) references cv (cv_id), |
97 | pathdistance int, |
98 | |
99 | unique (subjterm_id, objterm_id) |
100 | ); |
101 | create index cvpath_idx1 on cvpath (reltype_id); |
102 | create index cvpath_idx2 on cvpath (subjterm_id); |
103 | create index cvpath_idx3 on cvpath (objterm_id); |
104 | create index cvpath_idx4 on cvpath (cv_id); |
105 | |
106 | |
107 | -- ================================================ |
108 | -- TABLE: cvtermsynonym |
109 | -- ================================================ |
110 | |
111 | create table cvtermsynonym ( |
112 | cvterm_id int not null, |
113 | foreign key (cvterm_id) references cvterm (cvterm_id), |
114 | termsynonym varchar(255) not null, |
115 | |
116 | unique(cvterm_id, termsynonym) |
117 | ); |
118 | |
119 | -- The table "cvterm_synonym" doesn't exist, so |
120 | -- creating an index on it screws things up! |
121 | -- create index cvterm_synonym_idx1 on cvterm_synonym (cvterm_id); |
122 | |
123 | -- ================================================ |
124 | -- TABLE: cvterm_dbxref |
125 | -- ================================================ |
126 | |
127 | create table cvterm_dbxref ( |
128 | cvterm_dbxref_id serial not null, |
129 | primary key (cvterm_dbxref_id), |
130 | cvterm_id int not null, |
131 | foreign key (cvterm_id) references cvterm (cvterm_id), |
132 | dbxref_id int not null, |
133 | foreign key (dbxref_id) references dbxref (dbxref_id), |
134 | |
135 | unique(cvterm_id, dbxref_id) |
136 | ); |
137 | create index cvterm_dbxref_idx1 on cvterm_dbxref (cvterm_id); |
138 | create index cvterm_dbxref_idx2 on cvterm_dbxref (dbxref_id); |
139 | |
140 | |; |
141 | |
142 | my $tr = SQL::Translator->new({ |
143 | from => "PostgreSQL", |
144 | to => "MySQL" |
145 | }); |
146 | |
147 | ok( $tr->translate(data => $create), 'Translate PG2My' ) or warn $@; |
148 | done_testing; |