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 { |
11 | maybe_plan(1, |
12 | 'SQL::Translator::Parser::MySQL', |
13 | 'SQL::Translator::Producer::PostgreSQL'); |
14 | } |
407bc860 |
15 | |
16 | my $create = q| |
17 | |
18 | -- The cvterm module design is based on the ontology |
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 |
51 | -- The unique key on termname, termtype_id ensures that all terms are |
52 | -- unique within a given cv |
53 | |
54 | |
55 | -- ================================================ |
56 | -- TABLE: cvrelationship |
57 | -- ================================================ |
58 | |
59 | create table cvrelationship ( |
60 | cvrelationship_id serial not null, |
61 | primary key (cvrelationship_id), |
62 | reltype_id int not null, |
63 | foreign key (reltype_id) references cvterm (cvterm_id), |
64 | subjterm_id int not null, |
65 | foreign key (subjterm_id) references cvterm (cvterm_id), |
66 | objterm_id int not null, |
67 | foreign key (objterm_id) references cvterm (cvterm_id), |
68 | |
69 | unique(reltype_id, subjterm_id, objterm_id) |
70 | ); |
71 | create index cvrelationship_idx1 on cvrelationship (reltype_id); |
72 | create index cvrelationship_idx2 on cvrelationship (subjterm_id); |
73 | create index cvrelationship_idx3 on cvrelationship (objterm_id); |
74 | |
75 | |
76 | -- ================================================ |
77 | -- TABLE: cvpath |
78 | -- ================================================ |
79 | |
80 | create table cvpath ( |
81 | cvpath_id serial not null, |
82 | primary key (cvpath_id), |
83 | reltype_id int, |
84 | foreign key (reltype_id) references cvterm (cvterm_id), |
85 | subjterm_id int not null, |
86 | foreign key (subjterm_id) references cvterm (cvterm_id), |
87 | objterm_id int not null, |
88 | foreign key (objterm_id) references cvterm (cvterm_id), |
89 | cv_id int not null, |
90 | foreign key (cv_id) references cv (cv_id), |
91 | pathdistance int, |
92 | |
93 | unique (subjterm_id, objterm_id) |
94 | ); |
95 | create index cvpath_idx1 on cvpath (reltype_id); |
96 | create index cvpath_idx2 on cvpath (subjterm_id); |
97 | create index cvpath_idx3 on cvpath (objterm_id); |
98 | create index cvpath_idx4 on cvpath (cv_id); |
99 | |
100 | |
101 | -- ================================================ |
102 | -- TABLE: cvtermsynonym |
103 | -- ================================================ |
104 | |
105 | create table cvtermsynonym ( |
106 | cvterm_id int not null, |
107 | foreign key (cvterm_id) references cvterm (cvterm_id), |
108 | termsynonym varchar(255) not null, |
109 | |
110 | unique(cvterm_id, termsynonym) |
111 | ); |
2da7994b |
112 | |
113 | -- The table "cvterm_synonym" doesn't exist, so |
114 | -- creating an index on it screws things up! |
115 | -- create index cvterm_synonym_idx1 on cvterm_synonym (cvterm_id); |
ba1a1626 |
116 | |
117 | -- ================================================ |
118 | -- TABLE: cvterm_dbxref |
119 | -- ================================================ |
120 | |
121 | create table cvterm_dbxref ( |
122 | cvterm_dbxref_id serial not null, |
123 | primary key (cvterm_dbxref_id), |
124 | cvterm_id int not null, |
125 | foreign key (cvterm_id) references cvterm (cvterm_id), |
126 | dbxref_id int not null, |
127 | foreign key (dbxref_id) references dbxref (dbxref_id), |
128 | |
129 | unique(cvterm_id, dbxref_id) |
130 | ); |
131 | create index cvterm_dbxref_idx1 on cvterm_dbxref (cvterm_id); |
132 | create index cvterm_dbxref_idx2 on cvterm_dbxref (dbxref_id); |
407bc860 |
133 | |
134 | |; |
407bc860 |
135 | |
08f434d7 |
136 | my $tr = SQL::Translator->new( |
137 | parser => "PostgreSQL", |
138 | producer => "MySQL" |
407bc860 |
139 | ); |
ba1a1626 |
140 | |
08f434d7 |
141 | ok( $tr->translate(\$create), 'Translate PG2My' ); |