1use strict; 2use warnings; 3 4use Test::More; 5use lib qw(t/lib); 6use DBICTest; 7 8my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_ORA_${_}" } qw/DSN USER PASS/}; 9 10if (not ($dsn && $user && $pass)) { 11 plan skip_all => 'Set $ENV{DBICTEST_ORA_DSN}, _USER and _PASS to run this test. ' . 12 'Warning: This test drops and creates a table called \'track\''; 13} 14else { 15 eval "use DateTime; use DateTime::Format::Oracle;"; 16 if ($@) { 17 plan skip_all => 'needs DateTime and DateTime::Format::Oracle for testing'; 18 } 19 else { 20 plan tests => 10; 21 } 22} 23 24# DateTime::Format::Oracle needs this set 25$ENV{NLS_DATE_FORMAT} = 'DD-MON-YY'; 26$ENV{NLS_TIMESTAMP_FORMAT} = 'YYYY-MM-DD HH24:MI:SSXFF'; 27$ENV{NLS_LANG} = 'AMERICAN_AMERICA.WE8ISO8859P1'; 28 29my $schema = DBICTest::Schema->connect($dsn, $user, $pass); 30 31# Need to redefine the last_updated_on column 32my $col_metadata = $schema->class('Track')->column_info('last_updated_on'); 33$schema->class('Track')->add_column( 'last_updated_on' => { 34 data_type => 'date' }); 35$schema->class('Track')->add_column( 'last_updated_at' => { 36 data_type => 'timestamp' }); 37 38my $dbh = $schema->storage->dbh; 39 40#$dbh->do("alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SSXFF'"); 41 42eval { 43 $dbh->do("DROP TABLE track"); 44}; 45$dbh->do("CREATE TABLE track (trackid NUMBER(12), cd NUMBER(12), position NUMBER(12), title VARCHAR(255), last_updated_on DATE, last_updated_at TIMESTAMP, small_dt DATE)"); 46 47# insert a row to play with 48my $new = $schema->resultset('Track')->create({ trackid => 1, cd => 1, position => 1, title => 'Track1', last_updated_on => '06-MAY-07', last_updated_at => '2009-05-03 21:17:18.5' }); 49is($new->trackid, 1, "insert sucessful"); 50 51my $track = $schema->resultset('Track')->find( 1 ); 52 53is( ref($track->last_updated_on), 'DateTime', "last_updated_on inflated ok"); 54 55is( $track->last_updated_on->month, 5, "DateTime methods work on inflated column"); 56 57#note '$track->last_updated_at => ', $track->last_updated_at; 58is( ref($track->last_updated_at), 'DateTime', "last_updated_at inflated ok"); 59 60is( $track->last_updated_at->nanosecond, 500_000_000, "DateTime methods work with nanosecond precision"); 61 62my $dt = DateTime->now(); 63$track->last_updated_on($dt); 64$track->last_updated_at($dt); 65$track->update; 66 67is( $track->last_updated_on->month, $dt->month, "deflate ok"); 68is( int $track->last_updated_at->nanosecond, int $dt->nanosecond, "deflate ok with nanosecond precision"); 69 70# test datetime_setup 71 72$schema->storage->disconnect; 73 74delete $ENV{NLS_DATE_FORMAT}; 75delete $ENV{NLS_TIMESTAMP_FORMAT}; 76 77$schema->connection($dsn, $user, $pass, { 78 on_connect_call => 'datetime_setup' 79}); 80 81$dt = DateTime->now(); 82 83my $timestamp = $dt->clone; 84$timestamp->set_nanosecond( int 500_000_000 ); 85 86$track = $schema->resultset('Track')->find( 1 ); 87$track->update({ last_updated_on => $dt, last_updated_at => $timestamp }); 88 89$track = $schema->resultset('Track')->find(1); 90 91is( $track->last_updated_on, $dt, 'DateTime round-trip as DATE' ); 92is( $track->last_updated_at, $timestamp, 'DateTime round-trip as TIMESTAMP' ); 93 94is( int $track->last_updated_at->nanosecond, int 500_000_000, 95 'TIMESTAMP nanoseconds survived' ); 96 97# clean up our mess 98END { 99 if($schema && ($dbh = $schema->storage->dbh)) { 100 $dbh->do("DROP TABLE track"); 101 } 102} 103 104