8 use Test::SQL::Translator qw(maybe_plan);
12 'SQL::Translator::Parser::MySQL',
13 'SQL::Translator::Producer::PostgreSQL');
18 -- The cvterm module design is based on the ontology
20 -- ================================================
22 -- ================================================
25 cv_id serial not null,
27 cvname varchar not null,
33 -- ================================================
35 -- ================================================
38 cvterm_id serial not null,
39 primary key (cvterm_id),
41 foreign key (cv_id) references cv (cv_id),
42 name varchar(255) not null,
45 foreign key (dbxref_id) references dbxref (dbxref_id),
47 unique(termname, cv_id)
49 create index cvterm_idx1 on cvterm (cv_id);
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
55 -- ================================================
56 -- TABLE: cvrelationship
57 -- ================================================
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),
69 unique(reltype_id, subjterm_id, objterm_id)
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);
76 -- ================================================
78 -- ================================================
81 cvpath_id serial not null,
82 primary key (cvpath_id),
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),
90 foreign key (cv_id) references cv (cv_id),
93 unique (subjterm_id, objterm_id)
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);
101 -- ================================================
102 -- TABLE: cvtermsynonym
103 -- ================================================
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,
110 unique(cvterm_id, termsynonym)
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);
117 -- ================================================
118 -- TABLE: cvterm_dbxref
119 -- ================================================
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),
129 unique(cvterm_id, dbxref_id)
131 create index cvterm_dbxref_idx1 on cvterm_dbxref (cvterm_id);
132 create index cvterm_dbxref_idx2 on cvterm_dbxref (dbxref_id);
136 my $tr = SQL::Translator->new(
137 parser => "PostgreSQL",
141 ok( $tr->translate(\$create), 'Translate PG2My' );