use store_column instead of set_column on insert
[dbsrgits/DBIx-Class-Historic.git] / t / 72pg.t
CommitLineData
70350518 1use strict;
2use warnings;
3
4use Test::More;
9ba627e3 5use Test::Exception;
70350518 6use lib qw(t/lib);
7use DBICTest;
8
89add887 9{
10 package DBICTest::Schema::Casecheck;
11
12 use strict;
13 use warnings;
14 use base 'DBIx::Class';
15
3ff5b740 16 __PACKAGE__->load_components(qw/Core/);
9ba627e3 17 __PACKAGE__->table('testschema.casecheck');
89add887 18 __PACKAGE__->add_columns(qw/id name NAME uc_name/);
d9916234 19 __PACKAGE__->column_info_from_storage(1);
89add887 20 __PACKAGE__->set_primary_key('id');
21
22}
23
9ba627e3 24{
25 package DBICTest::Schema::ArrayTest;
26
27 use strict;
28 use warnings;
29 use base 'DBIx::Class';
30
31 __PACKAGE__->load_components(qw/Core/);
32 __PACKAGE__->table('testschema.array_test');
33 __PACKAGE__->add_columns(qw/id arrayfield/);
34 __PACKAGE__->column_info_from_storage(1);
35 __PACKAGE__->set_primary_key('id');
36
37}
38
0567538f 39my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_PG_${_}" } qw/DSN USER PASS/};
40
41#warn "$dsn $user $pass";
42
9ba627e3 43plan skip_all => 'Set $ENV{DBICTEST_PG_DSN}, _USER and _PASS to run this test '.
44 '(note: This test drops and creates tables called \'artist\', \'casecheck\', \'array_test\' and \'sequence_test\''.
45 ' as well as following sequences: \'pkid1_seq\', \'pkid2_seq\' and \'nonpkid_seq\''.
46 ' as well as following schemas: \'testschema\'!)'
47 unless ($dsn && $user && $pass);
0567538f 48
0567538f 49
89869c75 50plan tests => 37;
9ba627e3 51
52DBICTest::Schema->load_classes( 'Casecheck', 'ArrayTest' );
34a9e8a0 53my $schema = DBICTest::Schema->connect($dsn, $user, $pass);
0567538f 54
114780ee 55# Check that datetime_parser returns correctly before we explicitly connect.
56SKIP: {
57 eval { require DateTime::Format::Pg };
58 skip "DateTime::Format::Pg required", 2 if $@;
59
60 my $store = ref $schema->storage;
61 is($store, 'DBIx::Class::Storage::DBI', 'Started with generic storage');
62
63 my $parser = $schema->storage->datetime_parser;
64 is( $parser, 'DateTime::Format::Pg', 'datetime_parser is as expected');
65}
66
3ff5b740 67my $dbh = $schema->storage->dbh;
68$schema->source("Artist")->name("testschema.artist");
39b8d119 69$schema->source("SequenceTest")->name("testschema.sequence_test");
bb452615 70{
71 local $SIG{__WARN__} = sub {};
72 $dbh->do("CREATE SCHEMA testschema;");
9ba627e3 73 $dbh->do("CREATE TABLE testschema.artist (artistid serial PRIMARY KEY, name VARCHAR(100), rank INTEGER NOT NULL DEFAULT '13', charfield CHAR(10), arrayfield INTEGER[]);");
bb452615 74 $dbh->do("CREATE TABLE testschema.sequence_test (pkid1 integer, pkid2 integer, nonpkid integer, name VARCHAR(100), CONSTRAINT pk PRIMARY KEY(pkid1, pkid2));");
75 $dbh->do("CREATE SEQUENCE pkid1_seq START 1 MAXVALUE 999999 MINVALUE 0");
76 $dbh->do("CREATE SEQUENCE pkid2_seq START 10 MAXVALUE 999999 MINVALUE 0");
77 $dbh->do("CREATE SEQUENCE nonpkid_seq START 20 MAXVALUE 999999 MINVALUE 0");
78 ok ( $dbh->do('CREATE TABLE testschema.casecheck (id serial PRIMARY KEY, "name" VARCHAR(1), "NAME" VARCHAR(2), "UC_NAME" VARCHAR(3));'), 'Creation of casecheck table');
9ba627e3 79 ok ( $dbh->do('CREATE TABLE testschema.array_test (id serial PRIMARY KEY, arrayfield INTEGER[]);'), 'Creation of array_test table');
bb452615 80}
0567538f 81
3ff5b740 82# This is in Core now, but it's here just to test that it doesn't break
83$schema->class('Artist')->load_components('PK::Auto');
0567538f 84
3ff5b740 85my $new = $schema->resultset('Artist')->create({ name => 'foo' });
0567538f 86
b6b65a3e 87is($new->artistid, 1, "Auto-PK worked");
88
3ff5b740 89$new = $schema->resultset('Artist')->create({ name => 'bar' });
b6b65a3e 90
91is($new->artistid, 2, "Auto-PK worked");
92
a953d8d9 93my $test_type_info = {
94 'artistid' => {
103e3e03 95 'data_type' => 'integer',
96 'is_nullable' => 0,
fc22fbac 97 'size' => 4,
a953d8d9 98 },
99 'name' => {
103e3e03 100 'data_type' => 'character varying',
101 'is_nullable' => 1,
ae515736 102 'size' => 100,
fc22fbac 103 'default_value' => undef,
103e3e03 104 },
85df19d7 105 'rank' => {
106 'data_type' => 'integer',
107 'is_nullable' => 0,
108 'size' => 4,
109 'default_value' => 13,
110
111 },
103e3e03 112 'charfield' => {
113 'data_type' => 'character',
a953d8d9 114 'is_nullable' => 1,
fc22fbac 115 'size' => 10,
116 'default_value' => undef,
103e3e03 117 },
9ba627e3 118 'arrayfield' => {
119 'data_type' => 'integer[]',
120 'is_nullable' => 1,
121 'size' => undef,
122 'default_value' => undef,
123 },
a953d8d9 124};
125
fc22fbac 126
3ff5b740 127my $type_info = $schema->storage->columns_info_for('testschema.artist');
fc22fbac 128my $artistid_defval = delete $type_info->{artistid}->{default_value};
129like($artistid_defval,
4d272ce5 130 qr/^nextval\('([^\.]*\.){0,1}artist_artistid_seq'::(?:text|regclass)\)/,
fc22fbac 131 'columns_info_for - sequence matches Pg get_autoinc_seq expectations');
132is_deeply($type_info, $test_type_info,
133 'columns_info_for - column data types');
a953d8d9 134
ed8bc835 135SKIP: {
71a39061 136 skip "SQL::Abstract < 1.49 does not pass through arrayrefs", 4
89869c75 137 if $SQL::Abstract::VERSION < 1.49;
9ba627e3 138
139 lives_ok {
140 $schema->resultset('ArrayTest')->create({
141 arrayfield => [1, 2],
142 });
143 } 'inserting arrayref as pg array data';
144
145 lives_ok {
146 $schema->resultset('ArrayTest')->update({
147 arrayfield => [3, 4],
148 });
149 } 'updating arrayref as pg array data';
c224117b 150
151 $schema->resultset('ArrayTest')->create({
152 arrayfield => [5, 6],
153 });
154
155 my $count;
156 lives_ok {
157 $count = $schema->resultset('ArrayTest')->search({
158 arrayfield => \[ '= ?' => [3, 4] ], #TODO anything less ugly than this?
159 })->count;
160 } 'comparing arrayref to pg array data does not blow up';
161 is($count, 1, 'comparing arrayref to pg array data gives correct result');
9ba627e3 162}
163
164
3ff5b740 165my $name_info = $schema->source('Casecheck')->column_info( 'name' );
ae515736 166is( $name_info->{size}, 1, "Case sensitive matching info for 'name'" );
167
3ff5b740 168my $NAME_info = $schema->source('Casecheck')->column_info( 'NAME' );
ae515736 169is( $NAME_info->{size}, 2, "Case sensitive matching info for 'NAME'" );
170
3ff5b740 171my $uc_name_info = $schema->source('Casecheck')->column_info( 'uc_name' );
ae515736 172is( $uc_name_info->{size}, 3, "Case insensitive matching info for 'uc_name'" );
173
95ba7ee4 174# Test SELECT ... FOR UPDATE
175my $HaveSysSigAction = eval "require Sys::SigAction" && !$@;
176if ($HaveSysSigAction) {
177 Sys::SigAction->import( 'set_sig_handler' );
178}
179
180SKIP: {
181 skip "Sys::SigAction is not available", 3 unless $HaveSysSigAction;
182 # create a new schema
183 my $schema2 = DBICTest::Schema->connect($dsn, $user, $pass);
184 $schema2->source("Artist")->name("testschema.artist");
185
186 $schema->txn_do( sub {
187 my $artist = $schema->resultset('Artist')->search(
188 {
189 artistid => 1
190 },
191 {
192 for => 'update'
193 }
194 )->first;
195 is($artist->artistid, 1, "select for update returns artistid = 1");
196
197 my $artist_from_schema2;
198 my $error_ok = 0;
199 eval {
200 my $h = set_sig_handler( 'ALRM', sub { die "DBICTestTimeout" } );
201 alarm(2);
202 $artist_from_schema2 = $schema2->resultset('Artist')->find(1);
203 $artist_from_schema2->name('fooey');
204 $artist_from_schema2->update;
205 alarm(0);
206 };
207 if (my $e = $@) {
208 $error_ok = $e =~ /DBICTestTimeout/;
209 }
210
211 # Make sure that an error was raised, and that the update failed
212 ok($error_ok, "update from second schema times out");
213 ok($artist_from_schema2->is_column_changed('name'), "'name' column is still dirty from second schema");
214 });
215}
216
217SKIP: {
218 skip "Sys::SigAction is not available", 3 unless $HaveSysSigAction;
219 # create a new schema
220 my $schema2 = DBICTest::Schema->connect($dsn, $user, $pass);
221 $schema2->source("Artist")->name("testschema.artist");
222
223 $schema->txn_do( sub {
224 my $artist = $schema->resultset('Artist')->search(
225 {
226 artistid => 1
227 },
228 )->first;
229 is($artist->artistid, 1, "select for update returns artistid = 1");
230
231 my $artist_from_schema2;
232 my $error_ok = 0;
233 eval {
234 my $h = set_sig_handler( 'ALRM', sub { die "DBICTestTimeout" } );
235 alarm(2);
236 $artist_from_schema2 = $schema2->resultset('Artist')->find(1);
237 $artist_from_schema2->name('fooey');
238 $artist_from_schema2->update;
239 alarm(0);
240 };
241 if (my $e = $@) {
242 $error_ok = $e =~ /DBICTestTimeout/;
243 }
244
245 # Make sure that an error was NOT raised, and that the update succeeded
246 ok(! $error_ok, "update from second schema DOES NOT timeout");
247 ok(! $artist_from_schema2->is_column_changed('name'), "'name' column is NOT dirty from second schema");
248 });
249}
250
d6feb60f 251SKIP: {
252 skip "Oracle Auto-PK tests are broken", 16;
34a9e8a0 253
d6feb60f 254 # test auto increment using sequences WITHOUT triggers
d6feb60f 255 for (1..5) {
39b8d119 256 my $st = $schema->resultset('SequenceTest')->create({ name => 'foo' });
257 is($st->pkid1, $_, "Oracle Auto-PK without trigger: First primary key");
258 is($st->pkid2, $_ + 9, "Oracle Auto-PK without trigger: Second primary key");
259 is($st->nonpkid, $_ + 19, "Oracle Auto-PK without trigger: Non-primary key");
d6feb60f 260 }
261 my $st = $schema->resultset('SequenceTest')->create({ name => 'foo', pkid1 => 55 });
262 is($st->pkid1, 55, "Oracle Auto-PK without trigger: First primary key set manually");
39b8d119 263}
d6feb60f 264
3ff5b740 265END {
266 if($dbh) {
267 $dbh->do("DROP TABLE testschema.artist;");
268 $dbh->do("DROP TABLE testschema.casecheck;");
39b8d119 269 $dbh->do("DROP TABLE testschema.sequence_test;");
9ba627e3 270 $dbh->do("DROP TABLE testschema.array_test;");
39b8d119 271 $dbh->do("DROP SEQUENCE pkid1_seq");
272 $dbh->do("DROP SEQUENCE pkid2_seq");
273 $dbh->do("DROP SEQUENCE nonpkid_seq");
3ff5b740 274 $dbh->do("DROP SCHEMA testschema;");
275 }
276}
0567538f 277