Commit | Line | Data |
407bc860 |
1 | #!/usr/local/bin/perl |
2 | # vim: set ft=perl: |
3 | |
08f434d7 |
4 | use strict; |
30e3372e |
5 | use Test::More tests => 1; |
08f434d7 |
6 | use SQL::Translator; |
7 | use Data::Dumper; |
407bc860 |
8 | |
9 | my $create = q| |
10 | |
11 | -- The cvterm module design is based on the ontology |
12 | |
13 | -- ================================================ |
14 | -- TABLE: cv |
15 | -- ================================================ |
16 | |
17 | create table cv ( |
18 | cv_id serial not null, |
19 | primary key (cv_id), |
20 | cvname varchar not null, |
21 | cvdefinition text, |
22 | |
23 | unique(cvname) |
24 | ); |
25 | |
26 | -- ================================================ |
27 | -- TABLE: cvterm |
28 | -- ================================================ |
29 | |
30 | create table cvterm ( |
31 | cvterm_id serial not null, |
32 | primary key (cvterm_id), |
33 | cv_id int not null, |
34 | foreign key (cv_id) references cv (cv_id), |
35 | name varchar(255) not null, |
36 | termdefinition text, |
407bc860 |
37 | dbxref_id int, |
38 | foreign key (dbxref_id) references dbxref (dbxref_id), |
39 | |
40 | unique(termname, cv_id) |
41 | ); |
42 | create index cvterm_idx1 on cvterm (cv_id); |
ba1a1626 |
43 | -- the primary dbxref for this term. Other dbxrefs may be cvterm_dbxref |
44 | -- The unique key on termname, termtype_id ensures that all terms are |
45 | -- unique within a given cv |
46 | |
47 | |
48 | -- ================================================ |
49 | -- TABLE: cvrelationship |
50 | -- ================================================ |
51 | |
52 | create table cvrelationship ( |
53 | cvrelationship_id serial not null, |
54 | primary key (cvrelationship_id), |
55 | reltype_id int not null, |
56 | foreign key (reltype_id) references cvterm (cvterm_id), |
57 | subjterm_id int not null, |
58 | foreign key (subjterm_id) references cvterm (cvterm_id), |
59 | objterm_id int not null, |
60 | foreign key (objterm_id) references cvterm (cvterm_id), |
61 | |
62 | unique(reltype_id, subjterm_id, objterm_id) |
63 | ); |
64 | create index cvrelationship_idx1 on cvrelationship (reltype_id); |
65 | create index cvrelationship_idx2 on cvrelationship (subjterm_id); |
66 | create index cvrelationship_idx3 on cvrelationship (objterm_id); |
67 | |
68 | |
69 | -- ================================================ |
70 | -- TABLE: cvpath |
71 | -- ================================================ |
72 | |
73 | create table cvpath ( |
74 | cvpath_id serial not null, |
75 | primary key (cvpath_id), |
76 | reltype_id int, |
77 | foreign key (reltype_id) references cvterm (cvterm_id), |
78 | subjterm_id int not null, |
79 | foreign key (subjterm_id) references cvterm (cvterm_id), |
80 | objterm_id int not null, |
81 | foreign key (objterm_id) references cvterm (cvterm_id), |
82 | cv_id int not null, |
83 | foreign key (cv_id) references cv (cv_id), |
84 | pathdistance int, |
85 | |
86 | unique (subjterm_id, objterm_id) |
87 | ); |
88 | create index cvpath_idx1 on cvpath (reltype_id); |
89 | create index cvpath_idx2 on cvpath (subjterm_id); |
90 | create index cvpath_idx3 on cvpath (objterm_id); |
91 | create index cvpath_idx4 on cvpath (cv_id); |
92 | |
93 | |
94 | -- ================================================ |
95 | -- TABLE: cvtermsynonym |
96 | -- ================================================ |
97 | |
98 | create table cvtermsynonym ( |
99 | cvterm_id int not null, |
100 | foreign key (cvterm_id) references cvterm (cvterm_id), |
101 | termsynonym varchar(255) not null, |
102 | |
103 | unique(cvterm_id, termsynonym) |
104 | ); |
2da7994b |
105 | |
106 | -- The table "cvterm_synonym" doesn't exist, so |
107 | -- creating an index on it screws things up! |
108 | -- create index cvterm_synonym_idx1 on cvterm_synonym (cvterm_id); |
ba1a1626 |
109 | |
110 | -- ================================================ |
111 | -- TABLE: cvterm_dbxref |
112 | -- ================================================ |
113 | |
114 | create table cvterm_dbxref ( |
115 | cvterm_dbxref_id serial not null, |
116 | primary key (cvterm_dbxref_id), |
117 | cvterm_id int not null, |
118 | foreign key (cvterm_id) references cvterm (cvterm_id), |
119 | dbxref_id int not null, |
120 | foreign key (dbxref_id) references dbxref (dbxref_id), |
121 | |
122 | unique(cvterm_id, dbxref_id) |
123 | ); |
124 | create index cvterm_dbxref_idx1 on cvterm_dbxref (cvterm_id); |
125 | create index cvterm_dbxref_idx2 on cvterm_dbxref (dbxref_id); |
407bc860 |
126 | |
127 | |; |
407bc860 |
128 | |
08f434d7 |
129 | my $tr = SQL::Translator->new( |
130 | parser => "PostgreSQL", |
131 | producer => "MySQL" |
407bc860 |
132 | ); |
ba1a1626 |
133 | |
08f434d7 |
134 | ok( $tr->translate(\$create), 'Translate PG2My' ); |