| Commit | Line | Data |
|---|
| 407bc860 |
1 | #!/usr/local/bin/perl |
| 2 | # vim: set ft=perl: |
| 3 | |
| 4 | BEGIN { print "1..1\n" } |
| 5 | |
| 6 | my $create = q| |
| 7 | |
| 8 | -- The cvterm module design is based on the ontology |
| 9 | |
| 10 | -- ================================================ |
| 11 | -- TABLE: cv |
| 12 | -- ================================================ |
| 13 | |
| 14 | create table cv ( |
| 15 | cv_id serial not null, |
| 16 | primary key (cv_id), |
| 17 | cvname varchar not null, |
| 18 | cvdefinition text, |
| 19 | |
| 20 | unique(cvname) |
| 21 | ); |
| 22 | |
| 23 | -- ================================================ |
| 24 | -- TABLE: cvterm |
| 25 | -- ================================================ |
| 26 | |
| 27 | create table cvterm ( |
| 28 | cvterm_id serial not null, |
| 29 | primary key (cvterm_id), |
| 30 | cv_id int not null, |
| 31 | foreign key (cv_id) references cv (cv_id), |
| 32 | name varchar(255) not null, |
| 33 | termdefinition text, |
| 407bc860 |
34 | dbxref_id int, |
| 35 | foreign key (dbxref_id) references dbxref (dbxref_id), |
| 36 | |
| 37 | unique(termname, cv_id) |
| 38 | ); |
| 39 | create index cvterm_idx1 on cvterm (cv_id); |
| ba1a1626 |
40 | -- the primary dbxref for this term. Other dbxrefs may be cvterm_dbxref |
| 41 | -- The unique key on termname, termtype_id ensures that all terms are |
| 42 | -- unique within a given cv |
| 43 | |
| 44 | |
| 45 | -- ================================================ |
| 46 | -- TABLE: cvrelationship |
| 47 | -- ================================================ |
| 48 | |
| 49 | create table cvrelationship ( |
| 50 | cvrelationship_id serial not null, |
| 51 | primary key (cvrelationship_id), |
| 52 | reltype_id int not null, |
| 53 | foreign key (reltype_id) references cvterm (cvterm_id), |
| 54 | subjterm_id int not null, |
| 55 | foreign key (subjterm_id) references cvterm (cvterm_id), |
| 56 | objterm_id int not null, |
| 57 | foreign key (objterm_id) references cvterm (cvterm_id), |
| 58 | |
| 59 | unique(reltype_id, subjterm_id, objterm_id) |
| 60 | ); |
| 61 | create index cvrelationship_idx1 on cvrelationship (reltype_id); |
| 62 | create index cvrelationship_idx2 on cvrelationship (subjterm_id); |
| 63 | create index cvrelationship_idx3 on cvrelationship (objterm_id); |
| 64 | |
| 65 | |
| 66 | -- ================================================ |
| 67 | -- TABLE: cvpath |
| 68 | -- ================================================ |
| 69 | |
| 70 | create table cvpath ( |
| 71 | cvpath_id serial not null, |
| 72 | primary key (cvpath_id), |
| 73 | reltype_id int, |
| 74 | foreign key (reltype_id) references cvterm (cvterm_id), |
| 75 | subjterm_id int not null, |
| 76 | foreign key (subjterm_id) references cvterm (cvterm_id), |
| 77 | objterm_id int not null, |
| 78 | foreign key (objterm_id) references cvterm (cvterm_id), |
| 79 | cv_id int not null, |
| 80 | foreign key (cv_id) references cv (cv_id), |
| 81 | pathdistance int, |
| 82 | |
| 83 | unique (subjterm_id, objterm_id) |
| 84 | ); |
| 85 | create index cvpath_idx1 on cvpath (reltype_id); |
| 86 | create index cvpath_idx2 on cvpath (subjterm_id); |
| 87 | create index cvpath_idx3 on cvpath (objterm_id); |
| 88 | create index cvpath_idx4 on cvpath (cv_id); |
| 89 | |
| 90 | |
| 91 | -- ================================================ |
| 92 | -- TABLE: cvtermsynonym |
| 93 | -- ================================================ |
| 94 | |
| 95 | create table cvtermsynonym ( |
| 96 | cvterm_id int not null, |
| 97 | foreign key (cvterm_id) references cvterm (cvterm_id), |
| 98 | termsynonym varchar(255) not null, |
| 99 | |
| 100 | unique(cvterm_id, termsynonym) |
| 101 | ); |
| 102 | create index cvterm_synonym_idx1 on cvterm_synonym (cvterm_id); |
| 103 | |
| 104 | -- ================================================ |
| 105 | -- TABLE: cvterm_dbxref |
| 106 | -- ================================================ |
| 107 | |
| 108 | create table cvterm_dbxref ( |
| 109 | cvterm_dbxref_id serial not null, |
| 110 | primary key (cvterm_dbxref_id), |
| 111 | cvterm_id int not null, |
| 112 | foreign key (cvterm_id) references cvterm (cvterm_id), |
| 113 | dbxref_id int not null, |
| 114 | foreign key (dbxref_id) references dbxref (dbxref_id), |
| 115 | |
| 116 | unique(cvterm_id, dbxref_id) |
| 117 | ); |
| 118 | create index cvterm_dbxref_idx1 on cvterm_dbxref (cvterm_id); |
| 119 | create index cvterm_dbxref_idx2 on cvterm_dbxref (dbxref_id); |
| 407bc860 |
120 | |
| 121 | |; |
| 407bc860 |
122 | |
| 123 | use SQL::Translator; |
| 124 | use Data::Dumper; |
| 125 | |
| bd9f7203 |
126 | $SQL::Translator::DEBUG = 0; |
| 407bc860 |
127 | |
| 128 | my $tr = SQL::Translator->new(parser => "PostgreSQL", |
| 129 | producer => "MySQL" |
| 130 | #producer => "SQL::Translator::Producer::MySQL::translate" |
| 131 | #producer => sub { Dumper($_[1]) } |
| 132 | ); |
| 133 | |
| 134 | print "not " unless ($tr->translate(\$create)); |
| 135 | print "ok 1 # pointless test -- plz fix me!\n"; |
| 136 | |
| 137 | __END__ |
| 138 | __DATA__ |
| 139 | |
| 140 | -- The cvterm module design is based on the ontology |
| 141 | |
| 142 | -- ================================================ |
| 143 | -- TABLE: cv |
| 144 | -- ================================================ |
| 145 | |
| 146 | create table cv ( |
| 147 | cv_id serial not null, |
| 148 | primary key (cv_id), |
| 149 | cvname varchar not null, |
| 150 | cvdefinition text, |
| 151 | |
| 152 | unique(cvname) |
| 153 | ); |
| 154 | |
| 155 | -- ================================================ |
| 156 | -- TABLE: cvterm |
| 157 | -- ================================================ |
| 158 | |
| 159 | create table cvterm ( |
| 160 | cvterm_id serial not null, |
| 161 | primary key (cvterm_id), |
| 162 | cv_id int not null, |
| 163 | foreign key (cv_id) references cv (cv_id), |
| 164 | name varchar(255) not null, |
| 165 | termdefinition text, |
| 166 | -- the primary dbxref for this term. Other dbxrefs may be cvterm_dbxref |
| 167 | dbxref_id int, |
| 168 | foreign key (dbxref_id) references dbxref (dbxref_id), |
| 169 | |
| 170 | unique(termname, cv_id) |
| 171 | -- The unique key on termname, termtype_id ensures that all terms are |
| 172 | -- unique within a given cv |
| 173 | ); |
| 174 | create index cvterm_idx1 on cvterm (cv_id); |
| 175 | |
| 176 | |
| 177 | -- ================================================ |
| 178 | -- TABLE: cvrelationship |
| 179 | -- ================================================ |
| 180 | |
| 181 | create table cvrelationship ( |
| 182 | cvrelationship_id serial not null, |
| 183 | primary key (cvrelationship_id), |
| 184 | reltype_id int not null, |
| 185 | foreign key (reltype_id) references cvterm (cvterm_id), |
| 186 | subjterm_id int not null, |
| 187 | foreign key (subjterm_id) references cvterm (cvterm_id), |
| 188 | objterm_id int not null, |
| 189 | foreign key (objterm_id) references cvterm (cvterm_id), |
| 190 | |
| 191 | unique(reltype_id, subjterm_id, objterm_id) |
| 192 | ); |
| 193 | create index cvrelationship_idx1 on cvrelationship (reltype_id); |
| 194 | create index cvrelationship_idx2 on cvrelationship (subjterm_id); |
| 195 | create index cvrelationship_idx3 on cvrelationship (objterm_id); |
| 196 | |
| 197 | |
| 198 | -- ================================================ |
| 199 | -- TABLE: cvpath |
| 200 | -- ================================================ |
| 201 | |
| 202 | create table cvpath ( |
| 203 | cvpath_id serial not null, |
| 204 | primary key (cvpath_id), |
| 205 | reltype_id int, |
| 206 | foreign key (reltype_id) references cvterm (cvterm_id), |
| 207 | subjterm_id int not null, |
| 208 | foreign key (subjterm_id) references cvterm (cvterm_id), |
| 209 | objterm_id int not null, |
| 210 | foreign key (objterm_id) references cvterm (cvterm_id), |
| 211 | cv_id int not null, |
| 212 | foreign key (cv_id) references cv (cv_id), |
| 213 | pathdistance int, |
| 214 | |
| 215 | unique (subjterm_id, objterm_id) |
| 216 | ); |
| 217 | create index cvpath_idx1 on cvpath (reltype_id); |
| 218 | create index cvpath_idx2 on cvpath (subjterm_id); |
| 219 | create index cvpath_idx3 on cvpath (objterm_id); |
| 220 | create index cvpath_idx4 on cvpath (cv_id); |
| 221 | |
| 222 | |
| 223 | -- ================================================ |
| 224 | -- TABLE: cvtermsynonym |
| 225 | -- ================================================ |
| 226 | |
| 227 | create table cvtermsynonym ( |
| 228 | cvterm_id int not null, |
| 229 | foreign key (cvterm_id) references cvterm (cvterm_id), |
| 230 | termsynonym varchar(255) not null, |
| 231 | |
| 232 | unique(cvterm_id, termsynonym) |
| 233 | ); |
| 234 | create index cvterm_synonym_idx1 on cvterm_synonym (cvterm_id); |
| 235 | |
| 236 | -- ================================================ |
| 237 | -- TABLE: cvterm_dbxref |
| 238 | -- ================================================ |
| 239 | |
| 240 | create table cvterm_dbxref ( |
| 241 | cvterm_dbxref_id serial not null, |
| 242 | primary key (cvterm_dbxref_id), |
| 243 | cvterm_id int not null, |
| 244 | foreign key (cvterm_id) references cvterm (cvterm_id), |
| 245 | dbxref_id int not null, |
| 246 | foreign key (dbxref_id) references dbxref (dbxref_id), |
| 247 | |
| 248 | unique(cvterm_id, dbxref_id) |
| 249 | ); |
| 250 | create index cvterm_dbxref_idx1 on cvterm_dbxref (cvterm_id); |
| 251 | create index cvterm_dbxref_idx2 on cvterm_dbxref (dbxref_id); |
| 252 | |
| ba1a1626 |
253 | |
projects / dbsrgits/SQL-Translator.git / blame