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 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';
62 COMMENT ON COLUMN cvterm.termdefinition IS
63 'A human-readable text definition';
64 COMMENT ON COLUMN cvterm.dbxref_id IS
65 'A human-readable text definition';
66 COMMENT ON INDEX cvterm_c1 IS
67 'the OBO identifier is globally unique';
70 -- ================================================
71 -- TABLE: cvrelationship
72 -- ================================================
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),
84 unique(reltype_id, subjterm_id, objterm_id)
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);
91 -- ================================================
93 -- ================================================
96 cvpath_id serial not null,
97 primary key (cvpath_id),
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),
105 foreign key (cv_id) references cv (cv_id),
108 unique (subjterm_id, objterm_id)
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);
116 -- ================================================
117 -- TABLE: cvtermsynonym
118 -- ================================================
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,
125 unique(cvterm_id, termsynonym)
128 -- The table "cvterm_synonym" doesn't exist, so
129 -- creating an index on it screws things up!
130 -- create index cvterm_synonym_idx1 on cvterm_synonym (cvterm_id);
132 -- ================================================
133 -- TABLE: cvterm_dbxref
134 -- ================================================
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),
144 unique(cvterm_id, dbxref_id)
146 create index cvterm_dbxref_idx1 on cvterm_dbxref (cvterm_id);
147 create index cvterm_dbxref_idx2 on cvterm_dbxref (dbxref_id);
151 my $tr = SQL::Translator->new(
152 parser => "PostgreSQL",
156 ok( $tr->translate(\$create), 'Translate PG2My' ) or diag($tr->error);