#!/usr/bin/perl # vim: set ft=perl: use strict; use Test::More; use SQL::Translator; use Data::Dumper; use Test::SQL::Translator qw(maybe_plan); BEGIN { maybe_plan(1, 'SQL::Translator::Parser::MySQL', 'SQL::Translator::Producer::PostgreSQL'); } my $create = q| -- The cvterm module design is based on the ontology -- ================================================ -- TABLE: cv -- ================================================ create table cv ( cv_id serial not null, primary key (cv_id), cvname varchar not null, cvdefinition text, unique(cvname) ); -- ================================================ -- TABLE: cvterm -- ================================================ create table cvterm ( cvterm_id serial not null, primary key (cvterm_id), cv_id int not null, foreign key (cv_id) references cv (cv_id), name varchar(255) not null, termdefinition text, dbxref_id int, foreign key (dbxref_id) references dbxref (dbxref_id), unique(termname, cv_id) ); create index cvterm_idx1 on cvterm (cv_id); -- the primary dbxref for this term. Other dbxrefs may be cvterm_dbxref -- The unique key on termname, termtype_id ensures that all terms are -- unique within a given cv COMMENT ON TABLE cvterm IS 'A term, class or concept within an ontology or controlled vocabulary'; COMMENT ON COLUMN cvterm.cv_id IS 'The cv/ontology/namespace to which this cvterm belongs'; COMMENT ON COLUMN cvterm.name IS 'A concise human-readable name describing the meaning of the cvterm'; COMMENT ON COLUMN cvterm.termdefinition IS 'A human-readable text definition'; COMMENT ON COLUMN cvterm.dbxref_id IS 'A human-readable text definition'; COMMENT ON INDEX cvterm_c1 IS 'the OBO identifier is globally unique'; -- ================================================ -- TABLE: cvrelationship -- ================================================ create table cvrelationship ( cvrelationship_id serial not null, primary key (cvrelationship_id), reltype_id int not null, foreign key (reltype_id) references cvterm (cvterm_id), subjterm_id int not null, foreign key (subjterm_id) references cvterm (cvterm_id), objterm_id int not null, foreign key (objterm_id) references cvterm (cvterm_id), unique(reltype_id, subjterm_id, objterm_id) ); create index cvrelationship_idx1 on cvrelationship (reltype_id); create index cvrelationship_idx2 on cvrelationship (subjterm_id); create index cvrelationship_idx3 on cvrelationship (objterm_id); -- ================================================ -- TABLE: cvpath -- ================================================ create table cvpath ( cvpath_id serial not null, primary key (cvpath_id), reltype_id int, foreign key (reltype_id) references cvterm (cvterm_id), subjterm_id int not null, foreign key (subjterm_id) references cvterm (cvterm_id), objterm_id int not null, foreign key (objterm_id) references cvterm (cvterm_id), cv_id int not null, foreign key (cv_id) references cv (cv_id), pathdistance int, unique (subjterm_id, objterm_id) ); create index cvpath_idx1 on cvpath (reltype_id); create index cvpath_idx2 on cvpath (subjterm_id); create index cvpath_idx3 on cvpath (objterm_id); create index cvpath_idx4 on cvpath (cv_id); -- ================================================ -- TABLE: cvtermsynonym -- ================================================ create table cvtermsynonym ( cvterm_id int not null, foreign key (cvterm_id) references cvterm (cvterm_id), termsynonym varchar(255) not null, unique(cvterm_id, termsynonym) ); -- The table "cvterm_synonym" doesn't exist, so -- creating an index on it screws things up! -- create index cvterm_synonym_idx1 on cvterm_synonym (cvterm_id); -- ================================================ -- TABLE: cvterm_dbxref -- ================================================ create table cvterm_dbxref ( cvterm_dbxref_id serial not null, primary key (cvterm_dbxref_id), cvterm_id int not null, foreign key (cvterm_id) references cvterm (cvterm_id), dbxref_id int not null, foreign key (dbxref_id) references dbxref (dbxref_id), unique(cvterm_id, dbxref_id) ); create index cvterm_dbxref_idx1 on cvterm_dbxref (cvterm_id); create index cvterm_dbxref_idx2 on cvterm_dbxref (dbxref_id); -- ================================================ -- TABLE: cvterm_geom -- ================================================ create table cvterm_geom ( cvterm_geom_id serial not null, primary key (cvterm_geom_id), cvterm_id int not null, foreign key (cvterm_id) references cvterm (cvterm_id), cvterm_geom geometry, constraint "enforce_dims_cvterm_geom" CHECK ((st_ndims(cvterm_geom) = 2)), constraint "enforce_srid_cvterm_geom" CHECK ((st_srid(cvterm_geom) = -1)), constraint "enforce_geotype_cvterm_geom" CHECK ((geometrytype(cvterm_geom) = 'POINT'::text OR cvterm_geom IS NULL)), unique(cvterm_id) ); |; my $tr = SQL::Translator->new( parser => "PostgreSQL", producer => "MySQL" ); ok( $tr->translate(\$create), 'Translate PG2My' ) or diag($tr->error);