1#!/usr/bin/perl -w 2 3use strict; 4 5use Test::More tests => 33; 6 7BEGIN 8{ 9 require 't/test-lib.pl'; 10 use_ok('Rose::DB::Object'); 11} 12 13our %Have; 14 15# 16# Tests 17# 18 19#$Rose::DB::Object::Manager::Debug = 1; 20 21SKIP: 22{ 23 skip("migration tests", 32) unless($Have{'pg'} && $Have{'mysql'}); 24 25 #$DB::single = 1; 26 #$Rose::DB::Object::Debug = 1; 27 28 my $db_pg = Rose::DB->new('pg'); 29 my $db_ws = Rose::DB->new('pg_with_schema'); 30 my $db_my = Rose::DB->new('mysql'); 31 32 my $a1 = Album->new(id => 1, db => $db_pg, name => 'One', year => 2001, dt => '1/2/2003 4:56:12')->save; 33 my $a2 = Album->new(id => 2, db => $db_pg, name => 'Two', year => 2002, dt => '2/2/2003 4:56:12')->save; 34 my $a3 = Album->new(id => 1, db => $db_ws, name => 'OneWS', year => 2003, dt => '3/2/2003 4:56:12')->save; 35 my $a4 = Album->new(id => 2, db => $db_my, name => 'TwoMy', year => 2004, dt => '4/2/2003 4:56:12')->save; 36 37 # pg -> pg with schema 38 $a2->db($db_ws); 39 $a2->delete; 40 $a2->save; 41 42 $a2 = Album->new(id => 2, db => $db_ws)->load; 43 is($a2->name, 'Two', 'pg -> pg with schema'); 44 45 # pg with schema -> pg 46 $a3->db($db_pg); 47 $a3->save; 48 49 $a3 = Album->new(id => 1, db => $db_pg)->load; 50 is($a3->name, 'OneWS', 'pg with schema -> pg'); 51 52 $a1 = Album->new(id => 1, db => $db_pg)->load; 53 $a2 = Album->new(id => 2, db => $db_pg)->load; 54 $a3 = Album->new(id => 1, db => $db_ws)->load; 55 $a4 = Album->new(id => 2, db => $db_my)->load; 56 57 # pg -> mysql 58 $a2->db($db_my); 59 $a2->delete; 60 $a2->save; 61 62 $a2 = Album->new(id => 2, db => $db_my)->load; 63 is($a2->name, 'Two', 'pg -> mysql'); 64 65 # pg with schema -> mysql 66 $a3->db($db_my); 67 $a3->delete; 68 $a3->save; 69 70 $a3 = Album->new(id => 1, db => $db_my)->load; 71 is($a3->name, 'OneWS', 'pg with schema -> mysql 1'); 72 is($a3->dt->month, 3, 'pg with schema -> mysql 2'); 73 74 $a1 = Album->new(id => 1, db => $db_pg)->load; 75 $a2 = Album->new(id => 2, db => $db_pg)->load; 76 $a3 = Album->new(id => 1, db => $db_ws)->load; 77 $a4 = Album->new(id => 2, db => $db_my)->load; 78 79 $a4->name('TwoMy'); 80 $a4->save; 81 82 # mysql -> pg 83 $a4->db($db_pg); 84 $a4->save; 85 86 $a4 = Album->new(id => 2, db => $db_my)->load; 87 is($a4->name, 'TwoMy', 'mysql -> pg'); 88 89 # mysql -> pg with schema 90 $a4 = Album->new(id => 2, db => $db_my)->load; 91 $a4->db($db_ws); 92 93 $a4->save; 94 95 $a4 = Album->new(id => 2, db => $db_ws)->load; 96 is($a4->name, 'TwoMy', 'mysql -> pg with schema'); 97 98 $a1 = Album->new(id => 1, db => $db_pg)->load; 99 $a2 = Album->new(id => 2, db => $db_pg)->load; 100 $a3 = Album->new(id => 1, db => $db_ws)->load; 101 $a4 = Album->new(id => 2, db => $db_my)->load; 102 103 is($a1->dt->month, 3, 'dt check 1'); 104 is($a2->dt->month, 2, 'dt check 2'); 105 106 is($a3->dt->month, 3, 'dt check 2'); 107 is($a4->dt->month, 2, 'dt check 3'); 108 109 # 110 # Test with schema override 111 # 112 113 # Rose::DB::MySQL currently supports schema as a stand-in for database. 114 # We need to turn that off for this test because we don't control the 115 # database(s) the test suite runs against. 116 Rose::DB::MySQL->supports_schema(0); 117 118 $a1 = AlbumWS->new(id => 10, db => $db_pg, name => 'Ten', year => 2001, dt => '1/2/2003 4:56:12')->save; 119 $a2 = AlbumWS->new(id => 20, db => $db_pg, name => 'Twe', year => 2002, dt => '2/2/2003 4:56:12')->save; 120 $a3 = AlbumWS->new(id => 30, db => $db_ws, name => 'Thi', year => 2003, dt => '3/2/2003 4:56:12')->save; 121 $a4 = AlbumWS->new(id => 40, db => $db_my, name => 'For', year => 2004, dt => '4/2/2003 4:56:12')->save; 122 123 $a1->db($db_my); 124 $a1->save(insert => 1); 125 $a1 = AlbumWS->new(id => 10, db => $db_my)->load; 126 is($a1->name, 'Ten', 'pg forced schema -> mysql 1'); 127 is($a1->dt->month, 1, 'pg forced schema -> mysql 2'); 128 129 $a2->db($db_my); 130 $a2->save(insert => 1); 131 $a2 = AlbumWS->new(id => 20, db => $db_my)->load; 132 is($a2->name, 'Twe', 'pg forced schema -> mysql 3'); 133 is($a2->dt->month, 2, 'pg forced schema -> mysql 4'); 134 135 $a3->db($db_my); 136 $a3->save(insert => 1); 137 $a3 = AlbumWS->new(id => 30, db => $db_my)->load; 138 is($a3->name, 'Thi', 'pg forced schema -> mysql 5'); 139 is($a3->dt->month, 3, 'pg forced schema -> mysql 6'); 140 141 $a4->db($db_pg); 142 $a4->save(insert => 1); 143 $a4 = AlbumWS->new(id => 40, db => $db_ws)->load; 144 is($a4->name, 'For', 'mysql -> pg forced schema 7'); 145 is($a4->dt->month, 4, 'pg forced schema -> mysql 8'); 146 147 # 148 # Test multi-pk with sequences 149 # 150 151 $a1 = Code->new(name => 'One', db => $db_pg, id2 => 2)->save; 152 $a2 = Code->new(name => 'Two', db => $db_ws, id2 => 3)->save; 153 154 $a3 = Code->new(name => 'Thr', db => $db_my, id2 => 5, id3 => 6)->save; 155 156 is($a1->id1, 1, 'multi-pk check pk 1'); 157 is($a1->id2, 2, 'multi-pk check pk 2'); 158 is($a1->id3, 1, 'multi-pk check pk 3'); 159 160 is($a2->id1, 1, 'multi-pk check pk 4'); 161 is($a2->id2, 3, 'multi-pk check pk 5'); 162 is($a2->id3, 2, 'multi-pk check pk 6'); 163 164 is($a3->id1, 1, 'multi-pk check pk 7'); 165 is($a3->id2, 5, 'multi-pk check pk 8'); 166 is($a3->id3, 6, 'multi-pk check pk 9'); 167 168 # pg -> mysql 169 $a1->db($db_my); 170 $a1->delete; 171 $a1->save; 172 $a1 = Code->new(id1 => 1, id2 => 2, id3 => 1)->load; 173 is($a1->name, 'One', 'multi-pk pg -> mysql'); 174 175 # pg with schema -> mysql 176 $a2->db($db_my); 177 $a2->save(insert => 1); 178 $a2 = Code->new(id1 => 1, id2 => 3, id3 => 2, db => $db_my)->load; 179 is($a2->name, 'Two', 'multi-pk pg with schema -> mysql'); 180 181 # mysql -> pg 182 $a3->db($db_pg); 183 $a3->save(insert => 1); 184 $a3 = Code->new(id1 => 1, id2 => 5, id3 => 6, db => $db_pg)->load; 185 is($a3->name, 'Thr', 'multi-pk mysql -> pg'); 186 187 # mysql -> pg with schema 188 $a3->db($db_ws); 189 $a3->save(insert => 1); 190 $a3 = Code->new(id1 => 1, id2 => 5, id3 => 6, db => $db_ws)->load; 191 is($a3->name, 'Thr', 'multi-pk mysql -> pg with schema'); 192} 193 194BEGIN 195{ 196 our %Have; 197 198 # 199 # PostgreSQL 200 # 201 202 my $dbh; 203 204 eval 205 { 206 $dbh = Rose::DB->new('pg_admin')->retain_dbh() 207 or die Rose::DB->error; 208 }; 209 210 if(!$@ && $dbh) 211 { 212 $Have{'pg'} = 1; 213 $Have{'pg_with_schema'} = 1; 214 215 # Drop existing tables and create schema, ignoring errors 216 { 217 local $dbh->{'RaiseError'} = 0; 218 local $dbh->{'PrintError'} = 0; 219 $dbh->do('DROP TABLE Rose_db_object_private.rdbo_albums CASCADE'); 220 $dbh->do('DROP TABLE rdbo_albums CASCADE'); 221 $dbh->do('DROP TABLE rdbo_codes CASCADE'); 222 $dbh->do('DROP TABLE Rose_db_object_private.rdbo_codes CASCADE'); 223 $dbh->do('DROP SEQUENCE Rose_db_object_private.rdbo_seq CASCADE'); 224 $dbh->do('CREATE SCHEMA Rose_db_object_private'); 225 } 226 227 $dbh->do(<<"EOF"); 228CREATE TABLE rdbo_albums 229( 230 id SERIAL PRIMARY KEY, 231 name VARCHAR(32) UNIQUE, 232 artist VARCHAR(32), 233 year INTEGER, 234 dt TIMESTAMP 235) 236EOF 237 238 $dbh->do(<<"EOF"); 239CREATE TABLE Rose_db_object_private.rdbo_albums 240( 241 id SERIAL PRIMARY KEY, 242 name VARCHAR(32) UNIQUE, 243 artist VARCHAR(32), 244 year INTEGER, 245 dt TIMESTAMP 246) 247EOF 248 249 $dbh->do('CREATE SEQUENCE Rose_db_object_private.rdbo_seq'); 250 251 $dbh->do(<<"EOF"); 252CREATE TABLE rdbo_codes 253( 254 id1 SERIAL NOT NULL, 255 id2 INT NOT NULL, 256 id3 INT NOT NULL DEFAULT nextval('Rose_db_object_private.rdbo_seq'), 257 name VARCHAR(32) UNIQUE, 258 259 PRIMARY KEY(id1, id2, id3) 260) 261EOF 262 263 $dbh->do(<<"EOF"); 264CREATE TABLE Rose_db_object_private.rdbo_codes 265( 266 id1 SERIAL NOT NULL, 267 id2 INT NOT NULL, 268 id3 INT NOT NULL DEFAULT nextval('Rose_db_object_private.rdbo_seq'), 269 name VARCHAR(32) UNIQUE, 270 271 PRIMARY KEY(id1, id2, id3) 272) 273EOF 274 275 $dbh->disconnect; 276 277 Rose::DB->default_type('pg'); 278 279 package Album; 280 our @ISA = qw(Rose::DB::Object); 281 Album->meta->table('rdbo_albums'); 282 Album->meta->auto_initialize; 283 284 package AlbumWS; 285 our @ISA = qw(Rose::DB::Object); 286 AlbumWS->meta->table('rdbo_albums'); 287 AlbumWS->meta->schema('Rose_db_object_private'); 288 AlbumWS->meta->auto_initialize; 289 290 package Code; 291 our @ISA = qw(Rose::DB::Object); 292 Code->meta->table('rdbo_codes'); 293 Code->meta->auto_initialize; 294 } 295 296 # 297 # MySQL 298 # 299 300 eval 301 { 302 $dbh = Rose::DB->new('mysql_admin')->retain_dbh() 303 or die Rose::DB->error; 304 }; 305 306 if(!$@ && $dbh) 307 { 308 $Have{'mysql'} = 1; 309 310 # Drop existing tables and create schema, ignoring errors 311 { 312 local $dbh->{'RaiseError'} = 0; 313 local $dbh->{'PrintError'} = 0; 314 $dbh->do('DROP TABLE rdbo_albums CASCADE'); 315 $dbh->do('DROP TABLE rdbo_codes CASCADE'); 316 } 317 318 $dbh->do(<<"EOF"); 319CREATE TABLE rdbo_albums 320( 321 id INT PRIMARY KEY AUTO_INCREMENT, 322 name VARCHAR(32) UNIQUE, 323 artist VARCHAR(32), 324 year INTEGER, 325 dt TIMESTAMP 326) 327EOF 328 329 $dbh->do(<<"EOF"); 330CREATE TABLE rdbo_codes 331( 332 id1 INT NOT NULL AUTO_INCREMENT, 333 id2 INT NOT NULL, 334 id3 INT NOT NULL, 335 name VARCHAR(32) UNIQUE, 336 337 PRIMARY KEY(id1, id2, id3) 338) 339EOF 340 341 $dbh->disconnect; 342 } 343} 344 345END 346{ 347 # Delete test tables 348 349 if($Have{'pg'}) 350 { 351 # PostgreSQL 352 my $dbh = Rose::DB->new('pg_admin')->retain_dbh() 353 or die Rose::DB->error; 354 355 $dbh->do('DROP TABLE Rose_db_object_private.rdbo_albums CASCADE'); 356 $dbh->do('DROP TABLE rdbo_albums CASCADE'); 357 $dbh->do('DROP TABLE rdbo_codes CASCADE'); 358 $dbh->do('DROP TABLE Rose_db_object_private.rdbo_codes CASCADE'); 359 $dbh->do('DROP SEQUENCE Rose_db_object_private.rdbo_seq CASCADE'); 360 $dbh->do('DROP SCHEMA Rose_db_object_private CASCADE'); 361 362 $dbh->disconnect; 363 } 364 365 if($Have{'mysql'}) 366 { 367 # MySQL 368 my $dbh = Rose::DB->new('mysql_admin')->retain_dbh() 369 or die Rose::DB->error; 370 371 $dbh->do('DROP TABLE rdbo_albums CASCADE'); 372 $dbh->do('DROP TABLE rdbo_codes CASCADE'); 373 374 $dbh->disconnect; 375 } 376} 377