9 -- The cvterm module design is based on the ontology
11 -- ================================================
13 -- ================================================
16 cv_id serial not null,
18 cvname varchar not null,
24 -- ================================================
26 -- ================================================
29 cvterm_id serial not null,
30 primary key (cvterm_id),
32 foreign key (cv_id) references cv (cv_id),
33 termname varchar(255) not null,
36 foreign key (dbxref_id) references dbxref (dbxref_id),
38 unique(termname, cv_id)
40 create index cvterm_idx1 on cvterm (cv_id);
41 -- the primary dbxref for this term. Other dbxrefs may be cvterm_dbxref
42 -- The unique key on termname, termtype_id ensures that all terms are
43 -- unique within a given cv
46 COMMENT ON TABLE cvterm IS
47 'A term, class or concept within an ontology
48 or controlled vocabulary';
49 COMMENT ON COLUMN cvterm.cv_id IS
50 'The cv/ontology/namespace to which this cvterm belongs';
51 COMMENT ON COLUMN cvterm.termname IS
52 'A concise human-readable name describing the meaning of the cvterm';
53 COMMENT ON COLUMN cvterm.termdefinition IS
54 'A human-readable text definition';
55 COMMENT ON COLUMN cvterm.dbxref_id IS
56 'A human-readable text definition';
57 COMMENT ON INDEX cvterm_c1 IS
58 'the OBO identifier is globally unique';
61 -- ================================================
62 -- TABLE: cvrelationship
63 -- ================================================
65 create table cvrelationship (
66 cvrelationship_id serial not null,
67 primary key (cvrelationship_id),
68 reltype_id int not null,
69 foreign key (reltype_id) references cvterm (cvterm_id),
70 subjterm_id int not null,
71 foreign key (subjterm_id) references cvterm (cvterm_id),
72 objterm_id int not null,
73 foreign key (objterm_id) references cvterm (cvterm_id),
75 unique(reltype_id, subjterm_id, objterm_id)
77 create index cvrelationship_idx1 on cvrelationship (reltype_id);
78 create index cvrelationship_idx2 on cvrelationship (subjterm_id);
79 create index cvrelationship_idx3 on cvrelationship (objterm_id);
82 -- ================================================
84 -- ================================================
87 cvpath_id serial not null,
88 primary key (cvpath_id),
90 foreign key (reltype_id) references cvterm (cvterm_id),
91 subjterm_id int not null,
92 foreign key (subjterm_id) references cvterm (cvterm_id),
93 objterm_id int not null,
94 foreign key (objterm_id) references cvterm (cvterm_id),
96 foreign key (cv_id) references cv (cv_id),
99 unique (subjterm_id, objterm_id)
101 create index cvpath_idx1 on cvpath (reltype_id);
102 create index cvpath_idx2 on cvpath (subjterm_id);
103 create index cvpath_idx3 on cvpath (objterm_id);
104 create index cvpath_idx4 on cvpath (cv_id);
107 -- ================================================
108 -- TABLE: cvtermsynonym
109 -- ================================================
111 create table cvtermsynonym (
112 cvterm_id int not null,
113 foreign key (cvterm_id) references cvterm (cvterm_id),
114 termsynonym varchar(255) not null,
116 unique(cvterm_id, termsynonym)
119 -- The table "cvterm_synonym" doesn't exist, so
120 -- creating an index on it screws things up!
121 -- create index cvterm_synonym_idx1 on cvterm_synonym (cvterm_id);
123 -- ================================================
124 -- TABLE: cvterm_dbxref
125 -- ================================================
127 create table cvterm_dbxref (
128 cvterm_dbxref_id serial not null,
129 primary key (cvterm_dbxref_id),
130 cvterm_id int not null,
131 foreign key (cvterm_id) references cvterm (cvterm_id),
132 dbxref_id int not null,
133 foreign key (dbxref_id) references dbxref (dbxref_id),
135 unique(cvterm_id, dbxref_id)
137 create index cvterm_dbxref_idx1 on cvterm_dbxref (cvterm_id);
138 create index cvterm_dbxref_idx2 on cvterm_dbxref (dbxref_id);
142 my $tr = SQL::Translator->new({
143 from => "PostgreSQL",
147 ok( $tr->translate(data => $create), 'Translate PG2My' ) or warn $@;