8 use Test::SQL::Translator qw(maybe_plan);
12 'SQL::Translator::Parser::PostgreSQL',
13 'SQL::Translator::Producer::MySQL',
19 -- The cvterm module design is based on the ontology
21 -- ================================================
23 -- ================================================
26 cv_id serial not null,
28 cvname varchar not null,
34 -- ================================================
36 -- ================================================
39 cvterm_id serial not null,
40 primary key (cvterm_id),
42 foreign key (cv_id) references cv (cv_id),
43 name varchar(255) not null,
46 foreign key (dbxref_id) references dbxref (dbxref_id),
48 unique(termname, cv_id)
50 create index cvterm_idx1 on cvterm (cv_id);
51 -- the primary dbxref for this term. Other dbxrefs may be cvterm_dbxref
52 -- The unique key on termname, termtype_id ensures that all terms are
53 -- unique within a given cv
56 COMMENT ON TABLE cvterm IS
57 'A term, class or concept within an ontology
58 or controlled vocabulary';
59 COMMENT ON COLUMN cvterm.cv_id IS
60 'The cv/ontology/namespace to which this cvterm belongs';
61 COMMENT ON COLUMN cvterm.name IS
62 'A concise human-readable name describing the meaning of the cvterm';
63 COMMENT ON COLUMN cvterm.termdefinition IS
64 'A human-readable text definition';
65 COMMENT ON COLUMN cvterm.dbxref_id IS
66 'A human-readable text definition';
67 COMMENT ON INDEX cvterm_c1 IS
68 'the OBO identifier is globally unique';
71 -- ================================================
72 -- TABLE: cvrelationship
73 -- ================================================
75 create table cvrelationship (
76 cvrelationship_id serial not null,
77 primary key (cvrelationship_id),
78 reltype_id int not null,
79 foreign key (reltype_id) references cvterm (cvterm_id),
80 subjterm_id int not null,
81 foreign key (subjterm_id) references cvterm (cvterm_id),
82 objterm_id int not null,
83 foreign key (objterm_id) references cvterm (cvterm_id),
85 unique(reltype_id, subjterm_id, objterm_id)
87 create index cvrelationship_idx1 on cvrelationship (reltype_id);
88 create index cvrelationship_idx2 on cvrelationship (subjterm_id);
89 create index cvrelationship_idx3 on cvrelationship (objterm_id);
92 -- ================================================
94 -- ================================================
97 cvpath_id serial not null,
98 primary key (cvpath_id),
100 foreign key (reltype_id) references cvterm (cvterm_id),
101 subjterm_id int not null,
102 foreign key (subjterm_id) references cvterm (cvterm_id),
103 objterm_id int not null,
104 foreign key (objterm_id) references cvterm (cvterm_id),
106 foreign key (cv_id) references cv (cv_id),
109 unique (subjterm_id, objterm_id)
111 create index cvpath_idx1 on cvpath (reltype_id);
112 create index cvpath_idx2 on cvpath (subjterm_id);
113 create index cvpath_idx3 on cvpath (objterm_id);
114 create index cvpath_idx4 on cvpath (cv_id);
117 -- ================================================
118 -- TABLE: cvtermsynonym
119 -- ================================================
121 create table cvtermsynonym (
122 cvterm_id int not null,
123 foreign key (cvterm_id) references cvterm (cvterm_id),
124 termsynonym varchar(255) not null,
126 unique(cvterm_id, termsynonym)
129 -- The table "cvterm_synonym" doesn't exist, so
130 -- creating an index on it screws things up!
131 -- create index cvterm_synonym_idx1 on cvterm_synonym (cvterm_id);
133 -- ================================================
134 -- TABLE: cvterm_dbxref
135 -- ================================================
137 create table cvterm_dbxref (
138 cvterm_dbxref_id serial not null,
139 primary key (cvterm_dbxref_id),
140 cvterm_id int not null,
141 foreign key (cvterm_id) references cvterm (cvterm_id),
142 dbxref_id int not null,
143 foreign key (dbxref_id) references dbxref (dbxref_id),
145 unique(cvterm_id, dbxref_id)
147 create index cvterm_dbxref_idx1 on cvterm_dbxref (cvterm_id);
148 create index cvterm_dbxref_idx2 on cvterm_dbxref (dbxref_id);
150 -- ================================================
151 -- TABLE: cvterm_geom
152 -- ================================================
154 create table cvterm_geom (
155 cvterm_geom_id serial not null,
156 primary key (cvterm_geom_id),
157 cvterm_id int not null,
158 foreign key (cvterm_id) references cvterm (cvterm_id),
159 cvterm_geom geometry,
160 constraint "enforce_dims_cvterm_geom" CHECK ((st_ndims(cvterm_geom) = 2)),
161 constraint "enforce_srid_cvterm_geom" CHECK ((st_srid(cvterm_geom) = -1)),
162 constraint "enforce_geotype_cvterm_geom" CHECK ((geometrytype(cvterm_geom) = 'POINT'::text OR cvterm_geom IS NULL)),
169 my $tr = SQL::Translator->new(
170 parser => "PostgreSQL",
174 ok( $tr->translate(\$create), 'Translate PG2My' ) or diag($tr->error);