1#!/usr/bin/perl -w
2
3use strict;
4
5use Test::More tests => 32;
6
7BEGIN
8{
9  require 't/test-lib.pl';
10  use_ok('Rose::DB::Object');
11  use_ok('Rose::DB::Object::Manager');
12}
13
14our(%HAVE, $DID_SETUP);
15
16#
17# Tests
18#
19
20#$Rose::DB::Object::Manager::Debug = 1;
21
22foreach my $db_type (qw(mysql pg pg_with_schema informix sqlite))
23{
24  SKIP:
25  {
26    skip("$db_type tests", 6)  unless($HAVE{$db_type});
27  }
28
29  next  unless($HAVE{$db_type});
30
31  Rose::DB->default_type($db_type);
32
33  unless($DID_SETUP++)
34  {
35    # Load classes
36    use FindBin qw($Bin);
37    use lib "$Bin/lib";
38    require My::DB::Gene::Main;
39    require My::DB::Unigene::Main;
40  }
41
42  # Run tests
43
44  is(join(', ', map { $_->name } My::DB::Gene2Unigene->meta->foreign_keys),
45     'Rose_db_object_g_main, Rose_db_object_ug_main', "foreign_keys 1 - $db_type");
46
47  is(join(', ', map { $_->name . ' ' . $_->type} My::DB::Gene::Main->meta->relationships),
48     'unigenes many to many', "relationships 1 - $db_type");
49
50  is(join(', ', map { $_->name . ' ' . $_->type} My::DB::Unigene::Main->meta->relationships),
51     'genes many to many', "relationships 2 - $db_type");
52
53  is(scalar @Rose::DB::Object::Metadata::Deferred_Relationships || 0, 0,
54     "deferred relationships - $db_type");
55
56  # XXX: switch entirely to per-db SQL?
57  #My::DB::Gene::Main->meta->init_with_db(Rose::DB->new);
58  #My::DB::Unigene::Main->meta->init_with_db(Rose::DB->new);
59
60  my $g = My::DB::Gene::Main->new;
61  eval { $g->unigenes };
62  ok(!$@, "unigenes - $db_type");
63
64  $g = My::DB::Unigene::Main->new;
65  eval { $g->genes };
66  ok(!$@, "genes - $db_type");
67}
68
69BEGIN
70{
71  our %HAVE;
72
73  #
74  # PostgreSQL
75  #
76
77  my $dbh;
78
79  eval
80  {
81    $dbh = Rose::DB->new('pg_admin')->retain_dbh()
82      or die Rose::DB->error;
83  };
84
85  if(!$@ && $dbh)
86  {
87    $HAVE{'pg'} = 1;
88    $HAVE{'pg_with_schema'} = 1;
89
90    # Drop existing tables and create schema, ignoring errors
91    {
92      local $dbh->{'RaiseError'} = 0;
93      local $dbh->{'PrintError'} = 0;
94      $dbh->do('DROP TABLE Rose_db_object_g_ug CASCADE');
95      $dbh->do('DROP TABLE Rose_db_object_ug_main CASCADE');
96      $dbh->do('DROP TABLE Rose_db_object_g_main CASCADE');
97      $dbh->do('DROP TABLE Rose_db_object_private.Rose_db_object_g_ug CASCADE');
98      $dbh->do('DROP TABLE Rose_db_object_private.Rose_db_object_ug_main CASCADE');
99      $dbh->do('DROP TABLE Rose_db_object_private.Rose_db_object_g_main CASCADE');
100      $dbh->do('CREATE SCHEMA Rose_db_object_private');
101    }
102
103    $dbh->do(<<"EOF");
104CREATE TABLE Rose_db_object_ug_main
105(
106  ug_id         VARCHAR PRIMARY KEY NOT NULL,
107  species       VARCHAR,
108  symbol        VARCHAR,
109  description   VARCHAR,
110  cytoband      VARCHAR,
111  scount        INTEGER,
112  homol         VARCHAR,
113  rest_expr     VARCHAR,
114  mgi           VARCHAR
115)
116EOF
117
118    $dbh->do(<<"EOF");
119CREATE TABLE Rose_db_object_g_main
120(
121  tax_id           INTEGER,
122  gene_id          INTEGER PRIMARY KEY,
123  symbol           VARCHAR,
124  locustag         VARCHAR,
125  chromosome       VARCHAR,
126  map_location     VARCHAR,
127  description      VARCHAR,
128  gene_type        VARCHAR,
129  symbol_from_nomenclature_auth    VARCHAR,
130  full_name_from_nomenclature_auth VARCHAR,
131  nomenclature_status              VARCHAR,
132  discontinued     BOOLEAN DEFAULT FALSE,
133  new_gene_id      INTEGER DEFAULT NULL
134)
135EOF
136
137    $dbh->do(<<"EOF");
138CREATE TABLE Rose_db_object_g_ug
139(
140  g_ug_id SERIAL PRIMARY KEY,
141
142  gene_id INTEGER REFERENCES Rose_db_object_g_main (gene_id)
143    ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE,
144
145  ug_id VARCHAR REFERENCES Rose_db_object_ug_main (ug_id)
146    ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE
147)
148EOF
149
150    $dbh->do(<<"EOF");
151CREATE TABLE Rose_db_object_private.Rose_db_object_ug_main
152(
153  ug_id         VARCHAR PRIMARY KEY NOT NULL,
154  species       VARCHAR,
155  symbol        VARCHAR,
156  description   VARCHAR,
157  cytoband      VARCHAR,
158  scount        INTEGER,
159  homol         VARCHAR,
160  rest_expr     VARCHAR,
161  mgi           VARCHAR
162)
163EOF
164
165    $dbh->do(<<"EOF");
166CREATE TABLE Rose_db_object_private.Rose_db_object_g_main
167(
168  tax_id           INTEGER,
169  gene_id          INTEGER PRIMARY KEY,
170  symbol           VARCHAR,
171  locustag         VARCHAR,
172  chromosome       VARCHAR,
173  map_location     VARCHAR,
174  description      VARCHAR,
175  gene_type        VARCHAR,
176  symbol_from_nomenclature_auth    VARCHAR,
177  full_name_from_nomenclature_auth VARCHAR,
178  nomenclature_status              VARCHAR,
179  discontinued     BOOLEAN DEFAULT FALSE,
180  new_gene_id      INTEGER DEFAULT NULL
181)
182EOF
183
184    $dbh->do(<<"EOF");
185CREATE TABLE Rose_db_object_private.Rose_db_object_g_ug
186(
187  g_ug_id SERIAL PRIMARY KEY,
188
189  gene_id INTEGER REFERENCES Rose_db_object_private.Rose_db_object_g_main (gene_id)
190    ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE,
191
192  ug_id VARCHAR REFERENCES Rose_db_object_private.Rose_db_object_ug_main (ug_id)
193    ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE
194)
195EOF
196
197    $dbh->disconnect;
198  }
199
200  #
201  # MySQL
202  #
203
204  eval
205  {
206    my $db = Rose::DB->new('mysql_admin');
207    $dbh = $db->retain_dbh or die Rose::DB->error;
208
209    die "MySQL version too old"  unless($db->database_version >= 4_000_000 &&
210                                        mysql_supports_innodb());
211
212    # Drop existing tables, ignoring errors
213    {
214      local $dbh->{'RaiseError'} = 0;
215      local $dbh->{'PrintError'} = 0;
216      $dbh->do('DROP TABLE Rose_db_object_g_ug CASCADE');
217      $dbh->do('DROP TABLE Rose_db_object_ug_main CASCADE');
218      $dbh->do('DROP TABLE Rose_db_object_g_main CASCADE');
219    }
220
221    $dbh->do(<<"EOF");
222CREATE TABLE Rose_db_object_ug_main
223(
224  ug_id         VARCHAR(255) NOT NULL PRIMARY KEY,
225  species       VARCHAR(255),
226  symbol        VARCHAR(255),
227  description   VARCHAR(255),
228  cytoband      VARCHAR(255),
229  scount        INT,
230  homol         VARCHAR(255),
231  rest_expr     VARCHAR(255),
232  mgi           VARCHAR(255)
233)
234ENGINE=InnoDB
235EOF
236  };
237
238  if(!$@ && $dbh)
239  {
240    $HAVE{'mysql'} = 1;
241
242    $dbh->do(<<"EOF");
243CREATE TABLE Rose_db_object_g_main
244(
245  gene_id          INT NOT NULL PRIMARY KEY,
246  tax_id           INT,
247  symbol           VARCHAR(255),
248  locustag         VARCHAR(255),
249  chromosome       VARCHAR(255),
250  map_location     VARCHAR(255),
251  description      VARCHAR(255),
252  gene_type        VARCHAR(255),
253  symbol_from_nomenclature_auth    VARCHAR(255),
254  full_name_from_nomenclature_auth VARCHAR(255),
255  nomenclature_status              VARCHAR(255),
256  discontinued     INT DEFAULT 0,
257  new_gene_id      INT DEFAULT NULL
258)
259ENGINE=InnoDB
260EOF
261
262    $dbh->do(<<"EOF");
263CREATE TABLE Rose_db_object_g_ug
264(
265  g_ug_id  INT PRIMARY KEY,
266  gene_id  INT NOT NULL,
267  ug_id    VARCHAR(255) NOT NULL,
268
269  INDEX(gene_id),
270  INDEX(ug_id),
271
272  FOREIGN KEY (gene_id) REFERENCES Rose_db_object_g_main (gene_id),
273  FOREIGN KEY (ug_id) REFERENCES Rose_db_object_ug_main (ug_id)
274)
275ENGINE=InnoDB
276EOF
277
278    $dbh->disconnect;
279  }
280
281  #
282  # Informix
283  #
284
285  eval
286  {
287    $dbh = Rose::DB->new('informix_admin')->retain_dbh()
288      or die Rose::DB->error;
289  };
290
291  if(!$@ && $dbh)
292  {
293    $HAVE{'informix'} = 1;
294
295    # Drop existing tables, ignoring errors
296    {
297      local $dbh->{'RaiseError'} = 0;
298      local $dbh->{'PrintError'} = 0;
299      $dbh->do('DROP TABLE Rose_db_object_g_ug');
300      $dbh->do('DROP TABLE Rose_db_object_ug_main');
301      $dbh->do('DROP TABLE Rose_db_object_g_main');
302    }
303
304    $dbh->do(<<"EOF");
305CREATE TABLE Rose_db_object_ug_main
306(
307  ug_id         VARCHAR(255) NOT NULL PRIMARY KEY,
308  species       VARCHAR(255),
309  symbol        VARCHAR(255),
310  description   VARCHAR(255),
311  cytoband      VARCHAR(255),
312  scount        INT,
313  homol         VARCHAR(255),
314  rest_expr     VARCHAR(255),
315  mgi           VARCHAR(255)
316)
317EOF
318
319    $dbh->do(<<"EOF");
320CREATE TABLE Rose_db_object_g_main
321(
322  tax_id           INT,
323  gene_id          INT PRIMARY KEY,
324  symbol           VARCHAR(255),
325  locustag         VARCHAR(255),
326  chromosome       VARCHAR(255),
327  map_location     VARCHAR(255),
328  description      VARCHAR(255),
329  gene_type        VARCHAR(255),
330  symbol_from_nomenclature_auth    VARCHAR(255),
331  full_name_from_nomenclature_auth VARCHAR(255),
332  nomenclature_status              VARCHAR(255),
333  discontinued     INT DEFAULT 0,
334  new_gene_id      INT DEFAULT NULL
335)
336EOF
337
338    $dbh->do(<<"EOF");
339CREATE TABLE Rose_db_object_g_ug
340(
341  g_ug_id  SERIAL PRIMARY KEY,
342  gene_id  INT REFERENCES Rose_db_object_g_main (gene_id),
343  ug_id    VARCHAR(255) REFERENCES Rose_db_object_ug_main (ug_id)
344)
345EOF
346
347    $dbh->disconnect;
348  }
349
350  #
351  # SQLite
352  #
353
354  eval
355  {
356    $dbh = Rose::DB->new('sqlite_admin')->retain_dbh()
357      or die Rose::DB->error;
358  };
359
360  if(!$@ && $dbh)
361  {
362    $HAVE{'sqlite'} = 1;
363
364    # Drop existing tables, ignoring errors
365    {
366      local $dbh->{'RaiseError'} = 0;
367      local $dbh->{'PrintError'} = 0;
368      $dbh->do('DROP TABLE Rose_db_object_g_ug');
369      $dbh->do('DROP TABLE Rose_db_object_ug_main');
370      $dbh->do('DROP TABLE Rose_db_object_g_main');
371    }
372
373    $dbh->do(<<"EOF");
374CREATE TABLE Rose_db_object_ug_main
375(
376  ug_id         VARCHAR(255) NOT NULL PRIMARY KEY,
377  species       VARCHAR(255),
378  symbol        VARCHAR(255),
379  description   VARCHAR(255),
380  cytoband      VARCHAR(255),
381  scount        INT,
382  homol         VARCHAR(255),
383  rest_expr     VARCHAR(255),
384  mgi           VARCHAR(255)
385)
386EOF
387
388    $dbh->do(<<"EOF");
389CREATE TABLE Rose_db_object_g_main
390(
391  tax_id           INT,
392  gene_id          INT PRIMARY KEY,
393  symbol           VARCHAR(255),
394  locustag         VARCHAR(255),
395  chromosome       VARCHAR(255),
396  map_location     VARCHAR(255),
397  description      VARCHAR(255),
398  gene_type        VARCHAR(255),
399  symbol_from_nomenclature_auth    VARCHAR(255),
400  full_name_from_nomenclature_auth VARCHAR(255),
401  nomenclature_status              VARCHAR(255),
402  discontinued     INT DEFAULT 0,
403  new_gene_id      INT DEFAULT NULL
404)
405EOF
406
407    $dbh->do(<<"EOF");
408CREATE TABLE Rose_db_object_g_ug
409(
410  g_ug_id  INTEGER PRIMARY KEY AUTOINCREMENT,
411  gene_id  INTEGER REFERENCES Rose_db_object_g_main (gene_id),
412  ug_id    VARCHAR(255) REFERENCES Rose_db_object_ug_main (ug_id)
413)
414EOF
415
416    $dbh->disconnect;
417  }
418}
419
420END
421{
422  # Delete test table
423
424  if($HAVE{'pg'})
425  {
426    # PostgreSQL
427    my $dbh = Rose::DB->new('pg_admin')->retain_dbh()
428      or die Rose::DB->error;
429
430    $dbh->do('DROP TABLE Rose_db_object_g_ug CASCADE');
431    $dbh->do('DROP TABLE Rose_db_object_ug_main CASCADE');
432    $dbh->do('DROP TABLE Rose_db_object_g_main CASCADE');
433    $dbh->do('DROP TABLE Rose_db_object_private.Rose_db_object_g_ug CASCADE');
434    $dbh->do('DROP TABLE Rose_db_object_private.Rose_db_object_ug_main CASCADE');
435    $dbh->do('DROP TABLE Rose_db_object_private.Rose_db_object_g_main CASCADE');
436
437    $dbh->disconnect;
438  }
439
440  if($HAVE{'mysql'})
441  {
442    # MySQL
443    my $dbh = Rose::DB->new('mysql_admin')->retain_dbh()
444      or die Rose::DB->error;
445
446    $dbh->do('DROP TABLE Rose_db_object_g_ug CASCADE');
447    $dbh->do('DROP TABLE Rose_db_object_ug_main CASCADE');
448    $dbh->do('DROP TABLE Rose_db_object_g_main CASCADE');
449
450    $dbh->disconnect;
451  }
452
453  if($HAVE{'informix'})
454  {
455    # Informix
456    my $dbh = Rose::DB->new('informix_admin')->retain_dbh()
457      or die Rose::DB->error;
458
459    $dbh->do('DROP TABLE Rose_db_object_g_ug CASCADE');
460    $dbh->do('DROP TABLE Rose_db_object_ug_main CASCADE');
461    $dbh->do('DROP TABLE Rose_db_object_g_main CASCADE');
462
463    $dbh->disconnect;
464  }
465
466  if($HAVE{'sqlite'})
467  {
468    # Informix
469    my $dbh = Rose::DB->new('sqlite_admin')->retain_dbh()
470      or die Rose::DB->error;
471
472    $dbh->do('DROP TABLE Rose_db_object_g_ug');
473    $dbh->do('DROP TABLE Rose_db_object_ug_main');
474    $dbh->do('DROP TABLE Rose_db_object_g_main');
475
476    $dbh->disconnect;
477  }
478}
479