Author: turnstep
Date: Tue Jul 21 07:09:07 2009
New Revision: 13093
Modified:
DBD-Pg/trunk/t/12placeholders.t
Log:
Move geom quoting tests up, to prevent search_path problems.
Modified: DBD-Pg/trunk/t/12placeholders.t
==============================================================================
--- DBD-Pg/trunk/t/12placeholders.t (original)
+++ DBD-Pg/trunk/t/12placeholders.t Tue Jul 21 07:09:07 2009
@@ -141,575 +141,575 @@
my $expected = $backslash eq 'off' ? qr{unsafe} : qr{};
like ($@, $expected, $t);
-$t='Calling do() with non-DML placeholder works';
-$sth->finish();
-$dbh->commit();
-eval {
- $dbh->do(q{SET search_path TO ?}, undef, 'pg_catalog');
-};
-is ($@, q{}, $t);
+## Test quoting of geometric types
-$t='Calling do() with DML placeholder works';
-$dbh->commit();
-eval {
- $dbh->do(q{SELECT ?::text}, undef, 'public');
-};
-is ($@, q{}, $t);
+my @geotypes = qw/point line lseg box path polygon circle/;
-$t='Calling do() with invalid crowded placeholders fails cleanly';
-$dbh->commit();
-eval {
- $dbh->do(q{SELECT ??}, undef, 'public', 'error');
-};
-is($dbh->state, '42601', $t);
+$dbh->do("SET search_path TO DEFAULT");
+eval { $dbh->do("DROP TABLE dbd_pg_test_geom"); }; $dbh->commit();
-$t='Prepare/execute with non-DML placeholder works';
+$SQL = 'CREATE TABLE dbd_pg_test_geom (';
+for my $type (@geotypes) {
+ $SQL .= "x$type $type,";
+}
+$SQL =~ s/,$/)/;
+$dbh->do($SQL);
$dbh->commit();
-eval {
- $sth = $dbh->prepare(q{SET search_path TO ?});
- $sth->execute('pg_catalog');
-};
-is ($@, q{}, $t);
-$t='Prepare/execute does not allow geometric operators';
-$dbh->commit();
-eval {
- $sth = $dbh->prepare(q{SELECT ?- lseg '(1,0),(1,1)'});
- $sth->execute();
-};
-like ($@, qr{unbound placeholder}, $t);
+my %typemap = (
+ point => PG_POINT,
+ line => PG_LINE,
+ lseg => PG_LSEG,
+ box => PG_BOX,
+ path => PG_PATH,
+ polygon => PG_POLYGON,
+ circle => PG_CIRCLE,
+);
-$t='Prepare/execute allows geometric operator ?- when dollaronly is set';
-$dbh->commit();
-$dbh->{pg_placeholder_dollaronly} = 1;
-eval {
- $sth = $dbh->prepare(q{SELECT ?- lseg '(1,0),(1,1)'});
- $sth->execute();
- $sth->finish();
-};
-is ($@, q{}, $t);
+my $testdata = q{
+point datatype integers
+12,34
+'12,34'
+(12,34)
-$t='Prepare/execute allows geometric operator ?# when dollaronly set';
-$dbh->commit();
-eval {
- $sth = $dbh->prepare(q{SELECT lseg'(1,0),(1,1)' ?# lseg '(2,3),(4,5)'});
- $sth->execute();
- $sth->finish();
-};
-is ($@, q{}, $t);
+point datatype floating point numbers
+1.34,667
+'1.34,667'
+(1.34,667)
-$t=q{Value of placeholder_dollaronly can be retrieved};
-is ($dbh->{pg_placeholder_dollaronly}, 1, $t);
+point datatype exponential numbers
+1e34,9E4
+'1e34,9E4'
+(1e+34,90000)
-$t=q{Prepare/execute does not allow use of raw ? and :foo forms};
-$dbh->{pg_placeholder_dollaronly} = 0;
-eval {
- $sth = $dbh->prepare(q{SELECT uno ?: dos ? tres :foo bar $1});
- $sth->execute();
- $sth->finish();
-};
-like ($@, qr{mix placeholder}, $t);
+point datatype plus and minus signs
+1e+34,-.45
+'1e+34,-.45'
+(1e+34,-0.45)
-$t='Prepare/execute allows use of raw ? and :foo forms when dollaronly set';
-$dbh->{pg_placeholder_dollaronly} = 1;
-eval {
- $sth = $dbh->prepare(q{SELECT uno ?: dos ? tres :foo bar $1},
{pg_placeholder_dollaronly => 1});
- $sth->{pg_placeholder_dollaronly} = 1;
- $sth->execute();
- $sth->finish();
-};
-like ($@, qr{unbound placeholder}, $t);
+point datatype invalid number
+123,abc
+ERROR: Invalid input for geometric type
+ERROR: any
-$t='Prepare works with pg_placeholder_dollaronly';
-$dbh->{pg_placeholder_dollaronly} = 0;
-eval {
- $sth = $dbh->prepare(q{SELECT uno ?: dos ? tres :foo bar $1},
{pg_placeholder_dollaronly => 1});
- $sth->execute();
- $sth->finish();
-};
-like ($@, qr{unbound placeholder}, $t);
+point datatype invalid format
+123
+'123'
+ERROR: any
-$t='Prepare works with identical named placeholders';
-eval {
- $sth = $dbh->prepare(q{SELECT :row, :row, :row, :yourboat});
- $sth->finish();
-};
-is ($@, q{}, $t);
+point datatype invalid format
+123,456,789
+'123,456,789'
+ERROR: any
-$t='Prepare works with placeholders after double slashes';
-eval {
- $dbh->do(q{CREATE OPERATOR // ( PROCEDURE=bit, LEFTARG=int,
RIGHTARG=int )});
- $sth = $dbh->prepare(q{SELECT ? // ?});
- $sth->execute(1,2);
- $sth->finish();
-};
-is ($@, q{}, $t);
+point datatype invalid format
+<(2,4),6>
+ERROR: Invalid input for geometric type
+ERROR: any
-$t='Dollar quotes starting with a number are not treated as valid identifiers';
-eval {
- $sth = $dbh->prepare(q{SELECT $123$ $123$});
- $sth->execute(1);
- $sth->finish();
-};
-like ($@, qr{Invalid placeholders}, $t);
+point datatype invalid format
+[(1,2)]
+ERROR: Invalid input for geometric type
+ERROR: any
-$t='Dollar quotes with invalid characters are not parsed as identifiers';
-for my $char (qw!+ / : @ [ `!) {
- eval {
- $sth = $dbh->prepare(qq{SELECT \$abc${char}\$ 123
\$abc${char}\$});
- $sth->execute();
- $sth->finish();
- };
- like ($@, qr{syntax error}, $t);
-}
+line datatype integers
+12,34
+'12,34'
+ERROR: not yet implemented
-$t='Dollar quotes with valid characters are parsed as identifiers';
-$dbh->rollback();
-for my $char (qw{0 9 A Z a z}) {
- eval {
- $sth = $dbh->prepare(qq{SELECT \$abc${char}\$ 123
\$abc${char}\$});
- $sth->execute();
- $sth->finish();
- };
- is ($@, q{}, $t);
-}
+line datatype floating point numbers
+1.34,667
+'1.34,667'
+ERROR: not yet implemented
-SKIP: {
- skip 'Cannot run backslash_quote tet on Postgres < 8.2', 1 if
$pgversion < 80200;
+line datatype exponential numbers
+1e34,9E4
+'1e34,9E4'
+ERROR: not yet implemented
- $t='Backslash quoting inside double quotes is parsed correctly';
- $dbh->do(q{SET backslash_quote = 'on'});
- $dbh->commit();
- eval {
- $sth = $dbh->prepare(q{SELECT * FROM "\" WHERE a=?});
- $sth->execute(1);
- $sth->finish();
- };
- like ($@, qr{relation ".*" does not exist}, $t);
-}
+line datatype plus and minus signs
+1e+34,-.45
+'1e+34,-.45'
+ERROR: not yet implemented
-$dbh->rollback();
+line datatype invalid number
+123,abc
+ERROR: Invalid input for geometric type
+ERROR: not yet implemented
-SKIP: {
- skip 'Cannot adjust standard_conforming_strings for testing on this
version of Postgres', 2 if $pgversion < 80200;
- $t='Backslash quoting inside single quotes is parsed correctly with
standard_conforming_strings off';
- eval {
- $dbh->do(q{SET standard_conforming_strings = 'off'});
- $sth = $dbh->prepare(q{SELECT '\', ?});
- $sth->execute();
- $sth->finish();
- };
- like ($@, qr{unterminated quoted string}, $t);
- $dbh->rollback();
- $t='Backslash quoting inside single quotes is parsed correctly with
standard_conforming_strings on';
- eval {
- $dbh->do(q{SET standard_conforming_strings = 'on'});
- $sth = $dbh->prepare(q{SELECT '\', ?::int});
- $sth->execute(1);
- $sth->finish();
- };
- is ($@, q{}, $t);
-}
+lseg datatype invalid format
+12,34
+'12,34'
+ERROR: any
+lseg datatype integers
+(12,34),(56,78)
+'(12,34),(56,78)'
+[(12,34),(56,78)]
-$t='Valid integer works when quoting with SQL_INTEGER';
-my $val;
-$val = $dbh->quote('123', SQL_INTEGER);
-is ($val, 123, $t);
+lseg datatype floating point and exponential numbers
+(1.2,3.4),(5e3,7E1)
+'(1.2,3.4),(5e3,7E1)'
+[(1.2,3.4),(5000,70)]
-$t='Invalid integer fails to pass through when quoting with SQL_INTEGER';
-$val = -1;
-eval {
- $val = $dbh->quote('123abc', SQL_INTEGER);
-};
-like ($@, qr{Invalid integer}, $t);
-is($val, -1, $t);
-my $prefix = 'Valid float value works when quoting with SQL_FLOAT';
-for my $float ('123','0.00','0.234','23.31562',
'1.23e04','6.54e+02','4e-3','NaN','Infinity','-infinity') {
- $t = "$prefix (value=$float)";
- $val = -1;
- eval { $val = $dbh->quote($float, SQL_FLOAT); };
- is ($@, q{}, $t);
- is ($val, $float, $t);
+box datatype invalid format
+12,34
+'12,34'
+ERROR: any
- next unless $float =~ /\w/;
+box datatype integers
+(12,34),(56,78)
+'(12,34),(56,78)'
+(56,78),(12,34)
- my $lcfloat = lc $float;
- $t = "$prefix (value=$lcfloat)";
- $val = -1;
- eval { $val = $dbh->quote($lcfloat, SQL_FLOAT); };
- is ($@, q{}, $t);
- is ($val, $lcfloat, $t);
+box datatype floating point and exponential numbers
+(1.2,3.4),(5e3,7E1)
+'(1.2,3.4),(5e3,7E1)'
+(5000,70),(1.2,3.4)
- my $ucfloat = uc $float;
- $t = "$prefix (value=$ucfloat)";
- $val = -1;
- eval { $val = $dbh->quote($ucfloat, SQL_FLOAT); };
- is ($@, q{}, $t);
- is ($val, $ucfloat, $t);
-}
-$prefix = 'Invalid float value fails when quoting with SQL_FLOAT';
-for my $float ('3abc','123abc','','123e+04e+34','NaNum','-infinitee') {
- $t = "$prefix (value=$float)";
- $val = -1;
- eval { $val = $dbh->quote($float, SQL_FLOAT); };
- like ($@, qr{Invalid number}, $t);
- is ($val, -1, $t);
-}
+path datatype invalid format
+12,34
+'12,34'
+ERROR: any
-$dbh->rollback();
+path datatype integers
+(12,34),(56,78)
+'(12,34),(56,78)'
+((12,34),(56,78))
-## Test placeholders plus binding
-$t='Bound placeholders enforce data types when not using server side prepares';
-$dbh->trace(0);
-$dbh->{pg_server_prepare} = 0;
-$sth = $dbh->prepare('SELECT (1+?+?)::integer');
-$sth->bind_param(1, 1, SQL_INTEGER);
-eval {
- $sth->execute('10foo',20);
-};
-like ($@, qr{Invalid integer}, 'Invalid integer test 2');
+path datatype floating point and exponential numbers
+(1.2,3.4),(5e3,7E1)
+'(1.2,3.4),(5e3,7E1)'
+((1.2,3.4),(5000,70))
-## Test quoting of the "name" type
-$prefix = q{The 'name' data type does correct quoting};
+path datatype alternate bracket format
+[(1.2,3.4),(5e3,7E1)]
+'[(1.2,3.4),(5e3,7E1)]'
+[(1.2,3.4),(5000,70)]
-for my $word (qw/User user USER trigger Trigger/) {
- $t = qq{$prefix for the word "$word"};
- my $got = $dbh->quote($word, { pg_type => PG_NAME });
- $expected = qq{"$word"};
- is($got, $expected, $t);
-}
+path datatype many elements
+(1.2,3.4),(5,6),(7,8),(-9,10)
+'(1.2,3.4),(5,6),(7,8),(-9,10)'
+((1.2,3.4),(5,6),(7,8),(-9,10))
-for my $word (qw/auser userz user-user/) {
- $t = qq{$prefix for the word "$word"};
- my $got = $dbh->quote($word, { pg_type => PG_NAME });
- $expected = qq{$word};
- is($got, $expected, $t);
-}
+path datatype fails with braces
+{(1,2),(3,4)}
+ERROR: Invalid input for path type
+ERROR: any
-## Test quoting of booleans
-my %booltest = (
-undef => 'NULL',
-'t' => 'TRUE',
-'T' => 'TRUE',
-'true' => 'TRUE',
-'TRUE' => 'TRUE',
-1 => 'TRUE',
-01 => 'TRUE',
-'1' => 'TRUE',
-'0E0' => 'TRUE',
-'0e0' => 'TRUE',
-'0 but true' => 'TRUE',
-'0 BUT TRUE' => 'TRUE',
-'f' => 'FALSE',
-'F' => 'FALSE',
-0 => 'FALSE',
-00 => 'FALSE',
-'0' => 'FALSE',
-'false' => 'FALSE',
-'FALSE' => 'FALSE',
-12 => 'ERROR',
-'01' => 'ERROR',
-'00' => 'ERROR',
-' false' => 'ERROR',
-' TRUE' => 'ERROR',
-'FALSEY' => 'ERROR',
-'trueish' => 'ERROR',
-'0E0E0' => 'ERROR', ## Jungle love...
-'0 but truez' => 'ERROR',
-);
+polygon datatype invalid format
+12,34
+'12,34'
+ERROR: any
-while (my ($name,$val) = each %booltest) {
- $name = undef if $name eq 'undef';
- $t = sprintf 'Boolean quoting of %s',
- defined $name ? qq{"$name"} : 'undef';
- eval { $result = $dbh->quote($name, {pg_type => PG_BOOL}); };
+polygon datatype integers
+(12,34),(56,78)
+'(12,34),(56,78)'
+((12,34),(56,78))
+
+polygon datatype floating point and exponential numbers
+(1.2,3.4),(5e3,7E1)
+'(1.2,3.4),(5e3,7E1)'
+((1.2,3.4),(5000,70))
+
+polygon datatype many elements
+(1.2,3.4),(5,6),(7,8),(-9,10)
+'(1.2,3.4),(5,6),(7,8),(-9,10)'
+((1.2,3.4),(5,6),(7,8),(-9,10))
+
+polygon datatype fails with brackets
+[(1,2),(3,4)]
+ERROR: Invalid input for geometric type
+ERROR: any
+
+
+
+circle datatype invalid format
+(12,34)
+'(12,34)'
+ERROR: any
+
+circle datatype integers
+<(12,34),5>
+'<(12,34),5>'
+<(12,34),5>
+
+circle datatype floating point and exponential numbers
+<(-1.2,2E2),3e3>
+'<(-1.2,2E2),3e3>'
+<(-1.2,200),3000>
+
+circle datatype fails with brackets
+[(1,2),(3,4)]
+ERROR: Invalid input for circle type
+ERROR: any
+
+};
+
+$testdata =~ s/^\s+//;
+my $curtype = '';
+for my $line (split /\n\n+/ => $testdata) {
+ my ($text,$input,$quoted,$rows) = split /\n/ => $line;
+ next if ! $text;
+ $t = "Geometric type test: $text";
+ (my $type) = ($text =~ m{(\w+)});
+ last if $type eq 'LAST';
+ if ($curtype ne $type) {
+ $curtype = $type;
+ eval { $dbh->do('DEALLOCATE geotest'); }; $dbh->commit();
+ $dbh->do(qq{PREPARE geotest($type) AS INSERT INTO
dbd_pg_test_geom(x$type) VALUES (\$1)});
+ $sth = $dbh->prepare(qq{INSERT INTO dbd_pg_test_geom(x$type)
VALUES (?)});
+ $sth->bind_param(1, '', {pg_type => $typemap{$type} });
+ }
+ $dbh->do('DELETE FROM dbd_pg_test_geom');
+ eval { $qresult = $dbh->quote($input, {pg_type => $typemap{$type}}); };
+ if ($@) {
+ if ($quoted !~ /ERROR: (.+)/) {
+ fail "$t error: $@";
+ }
+ else {
+ like ($@, qr{$1}, $t);
+ }
+ }
+ else {
+ is ($qresult, $quoted, $t);
+ }
+ $dbh->commit();
+
+ eval { $dbh->do("EXECUTE geotest('$input')"); };
if ($@) {
- if ($val eq 'ERROR' and $@ =~ /Invalid boolean/) {
+ if ($rows !~ /ERROR: (.+)/) {
+ fail "$t error: $@";
+ }
+ else {
+ ## Do any error for now: i18n worries
pass $t;
}
+ }
+ $dbh->commit();
+
+ eval { $sth->execute($input); };
+ if ($@) {
+ if ($rows !~ /ERROR: (.+)/) {
+ fail $t;
+ }
else {
- fail "Failure at $t: $...@\n";
+ ## Do any error for now: i18n worries
+ pass $t;
}
- $dbh->rollback();
}
- else {
- is ($result, $val, $t);
+ $dbh->commit();
+
+ if ($rows !~ /ERROR/) {
+ $SQL = "SELECT x$type FROM dbd_pg_test_geom";
+ my $expected = [[$rows],[$rows]];
+ $result = $dbh->selectall_arrayref($SQL);
+ is_deeply($result, $expected, $t);
}
}
-## Test quoting of geometric types
-
-my @geotypes = qw/point line lseg box path polygon circle/;
-
-$dbh->do("SET search_path TO DEFAULT");
-eval { $dbh->do("DROP TABLE dbd_pg_test_geom"); }; $dbh->commit();
-
-$SQL = 'CREATE TABLE dbd_pg_test_geom (';
-for my $type (@geotypes) {
- $SQL .= "x$type $type,";
-}
-$SQL =~ s/,$/)/;
-$dbh->do($SQL);
+$t='Calling do() with non-DML placeholder works';
+$sth->finish();
$dbh->commit();
+eval {
+ $dbh->do(q{SET search_path TO ?}, undef, 'pg_catalog');
+};
+is ($@, q{}, $t);
-my %typemap = (
- point => PG_POINT,
- line => PG_LINE,
- lseg => PG_LSEG,
- box => PG_BOX,
- path => PG_PATH,
- polygon => PG_POLYGON,
- circle => PG_CIRCLE,
-);
-
-my $testdata = q{
-point datatype integers
-12,34
-'12,34'
-(12,34)
-
-point datatype floating point numbers
-1.34,667
-'1.34,667'
-(1.34,667)
-
-point datatype exponential numbers
-1e34,9E4
-'1e34,9E4'
-(1e+34,90000)
-
-point datatype plus and minus signs
-1e+34,-.45
-'1e+34,-.45'
-(1e+34,-0.45)
-
-point datatype invalid number
-123,abc
-ERROR: Invalid input for geometric type
-ERROR: any
-
-point datatype invalid format
-123
-'123'
-ERROR: any
-
-point datatype invalid format
-123,456,789
-'123,456,789'
-ERROR: any
-
-point datatype invalid format
-<(2,4),6>
-ERROR: Invalid input for geometric type
-ERROR: any
-
-point datatype invalid format
-[(1,2)]
-ERROR: Invalid input for geometric type
-ERROR: any
+$t='Calling do() with DML placeholder works';
+$dbh->commit();
+eval {
+ $dbh->do(q{SELECT ?::text}, undef, 'public');
+};
+is ($@, q{}, $t);
-line datatype integers
-12,34
-'12,34'
-ERROR: not yet implemented
+$t='Calling do() with invalid crowded placeholders fails cleanly';
+$dbh->commit();
+eval {
+ $dbh->do(q{SELECT ??}, undef, 'public', 'error');
+};
+is($dbh->state, '42601', $t);
-line datatype floating point numbers
-1.34,667
-'1.34,667'
-ERROR: not yet implemented
+$t='Prepare/execute with non-DML placeholder works';
+$dbh->commit();
+eval {
+ $sth = $dbh->prepare(q{SET search_path TO ?});
+ $sth->execute('pg_catalog');
+};
+is ($@, q{}, $t);
-line datatype exponential numbers
-1e34,9E4
-'1e34,9E4'
-ERROR: not yet implemented
+$t='Prepare/execute does not allow geometric operators';
+$dbh->commit();
+eval {
+ $sth = $dbh->prepare(q{SELECT ?- lseg '(1,0),(1,1)'});
+ $sth->execute();
+};
+like ($@, qr{unbound placeholder}, $t);
-line datatype plus and minus signs
-1e+34,-.45
-'1e+34,-.45'
-ERROR: not yet implemented
+$t='Prepare/execute allows geometric operator ?- when dollaronly is set';
+$dbh->commit();
+$dbh->{pg_placeholder_dollaronly} = 1;
+eval {
+ $sth = $dbh->prepare(q{SELECT ?- lseg '(1,0),(1,1)'});
+ $sth->execute();
+ $sth->finish();
+};
+is ($@, q{}, $t);
-line datatype invalid number
-123,abc
-ERROR: Invalid input for geometric type
-ERROR: not yet implemented
+$t='Prepare/execute allows geometric operator ?# when dollaronly set';
+$dbh->commit();
+eval {
+ $sth = $dbh->prepare(q{SELECT lseg'(1,0),(1,1)' ?# lseg '(2,3),(4,5)'});
+ $sth->execute();
+ $sth->finish();
+};
+is ($@, q{}, $t);
+$t=q{Value of placeholder_dollaronly can be retrieved};
+is ($dbh->{pg_placeholder_dollaronly}, 1, $t);
-lseg datatype invalid format
-12,34
-'12,34'
-ERROR: any
+$t=q{Prepare/execute does not allow use of raw ? and :foo forms};
+$dbh->{pg_placeholder_dollaronly} = 0;
+eval {
+ $sth = $dbh->prepare(q{SELECT uno ?: dos ? tres :foo bar $1});
+ $sth->execute();
+ $sth->finish();
+};
+like ($@, qr{mix placeholder}, $t);
-lseg datatype integers
-(12,34),(56,78)
-'(12,34),(56,78)'
-[(12,34),(56,78)]
+$t='Prepare/execute allows use of raw ? and :foo forms when dollaronly set';
+$dbh->{pg_placeholder_dollaronly} = 1;
+eval {
+ $sth = $dbh->prepare(q{SELECT uno ?: dos ? tres :foo bar $1},
{pg_placeholder_dollaronly => 1});
+ $sth->{pg_placeholder_dollaronly} = 1;
+ $sth->execute();
+ $sth->finish();
+};
+like ($@, qr{unbound placeholder}, $t);
-lseg datatype floating point and exponential numbers
-(1.2,3.4),(5e3,7E1)
-'(1.2,3.4),(5e3,7E1)'
-[(1.2,3.4),(5000,70)]
+$t='Prepare works with pg_placeholder_dollaronly';
+$dbh->{pg_placeholder_dollaronly} = 0;
+eval {
+ $sth = $dbh->prepare(q{SELECT uno ?: dos ? tres :foo bar $1},
{pg_placeholder_dollaronly => 1});
+ $sth->execute();
+ $sth->finish();
+};
+like ($@, qr{unbound placeholder}, $t);
+$t='Prepare works with identical named placeholders';
+eval {
+ $sth = $dbh->prepare(q{SELECT :row, :row, :row, :yourboat});
+ $sth->finish();
+};
+is ($@, q{}, $t);
-box datatype invalid format
-12,34
-'12,34'
-ERROR: any
+$t='Prepare works with placeholders after double slashes';
+eval {
+ $dbh->do(q{CREATE OPERATOR // ( PROCEDURE=bit, LEFTARG=int,
RIGHTARG=int )});
+ $sth = $dbh->prepare(q{SELECT ? // ?});
+ $sth->execute(1,2);
+ $sth->finish();
+};
+is ($@, q{}, $t);
-box datatype integers
-(12,34),(56,78)
-'(12,34),(56,78)'
-(56,78),(12,34)
+$t='Dollar quotes starting with a number are not treated as valid identifiers';
+eval {
+ $sth = $dbh->prepare(q{SELECT $123$ $123$});
+ $sth->execute(1);
+ $sth->finish();
+};
+like ($@, qr{Invalid placeholders}, $t);
-box datatype floating point and exponential numbers
-(1.2,3.4),(5e3,7E1)
-'(1.2,3.4),(5e3,7E1)'
-(5000,70),(1.2,3.4)
+$t='Dollar quotes with invalid characters are not parsed as identifiers';
+for my $char (qw!+ / : @ [ `!) {
+ eval {
+ $sth = $dbh->prepare(qq{SELECT \$abc${char}\$ 123
\$abc${char}\$});
+ $sth->execute();
+ $sth->finish();
+ };
+ like ($@, qr{syntax error}, $t);
+}
+$t='Dollar quotes with valid characters are parsed as identifiers';
+$dbh->rollback();
+for my $char (qw{0 9 A Z a z}) {
+ eval {
+ $sth = $dbh->prepare(qq{SELECT \$abc${char}\$ 123
\$abc${char}\$});
+ $sth->execute();
+ $sth->finish();
+ };
+ is ($@, q{}, $t);
+}
-path datatype invalid format
-12,34
-'12,34'
-ERROR: any
+SKIP: {
+ skip 'Cannot run backslash_quote tet on Postgres < 8.2', 1 if
$pgversion < 80200;
-path datatype integers
-(12,34),(56,78)
-'(12,34),(56,78)'
-((12,34),(56,78))
+ $t='Backslash quoting inside double quotes is parsed correctly';
+ $dbh->do(q{SET backslash_quote = 'on'});
+ $dbh->commit();
+ eval {
+ $sth = $dbh->prepare(q{SELECT * FROM "\" WHERE a=?});
+ $sth->execute(1);
+ $sth->finish();
+ };
+ like ($@, qr{relation ".*" does not exist}, $t);
+}
-path datatype floating point and exponential numbers
-(1.2,3.4),(5e3,7E1)
-'(1.2,3.4),(5e3,7E1)'
-((1.2,3.4),(5000,70))
+$dbh->rollback();
-path datatype alternate bracket format
-[(1.2,3.4),(5e3,7E1)]
-'[(1.2,3.4),(5e3,7E1)]'
-[(1.2,3.4),(5000,70)]
+SKIP: {
+ skip 'Cannot adjust standard_conforming_strings for testing on this
version of Postgres', 2 if $pgversion < 80200;
+ $t='Backslash quoting inside single quotes is parsed correctly with
standard_conforming_strings off';
+ eval {
+ $dbh->do(q{SET standard_conforming_strings = 'off'});
+ $sth = $dbh->prepare(q{SELECT '\', ?});
+ $sth->execute();
+ $sth->finish();
+ };
+ like ($@, qr{unterminated quoted string}, $t);
+ $dbh->rollback();
-path datatype many elements
-(1.2,3.4),(5,6),(7,8),(-9,10)
-'(1.2,3.4),(5,6),(7,8),(-9,10)'
-((1.2,3.4),(5,6),(7,8),(-9,10))
+ $t='Backslash quoting inside single quotes is parsed correctly with
standard_conforming_strings on';
+ eval {
+ $dbh->do(q{SET standard_conforming_strings = 'on'});
+ $sth = $dbh->prepare(q{SELECT '\', ?::int});
+ $sth->execute(1);
+ $sth->finish();
+ };
+ is ($@, q{}, $t);
+}
-path datatype fails with braces
-{(1,2),(3,4)}
-ERROR: Invalid input for path type
-ERROR: any
+$t='Valid integer works when quoting with SQL_INTEGER';
+my $val;
+$val = $dbh->quote('123', SQL_INTEGER);
+is ($val, 123, $t);
-polygon datatype invalid format
-12,34
-'12,34'
-ERROR: any
+$t='Invalid integer fails to pass through when quoting with SQL_INTEGER';
+$val = -1;
+eval {
+ $val = $dbh->quote('123abc', SQL_INTEGER);
+};
+like ($@, qr{Invalid integer}, $t);
+is($val, -1, $t);
-polygon datatype integers
-(12,34),(56,78)
-'(12,34),(56,78)'
-((12,34),(56,78))
+my $prefix = 'Valid float value works when quoting with SQL_FLOAT';
+for my $float ('123','0.00','0.234','23.31562',
'1.23e04','6.54e+02','4e-3','NaN','Infinity','-infinity') {
+ $t = "$prefix (value=$float)";
+ $val = -1;
+ eval { $val = $dbh->quote($float, SQL_FLOAT); };
+ is ($@, q{}, $t);
+ is ($val, $float, $t);
-polygon datatype floating point and exponential numbers
-(1.2,3.4),(5e3,7E1)
-'(1.2,3.4),(5e3,7E1)'
-((1.2,3.4),(5000,70))
+ next unless $float =~ /\w/;
-polygon datatype many elements
-(1.2,3.4),(5,6),(7,8),(-9,10)
-'(1.2,3.4),(5,6),(7,8),(-9,10)'
-((1.2,3.4),(5,6),(7,8),(-9,10))
+ my $lcfloat = lc $float;
+ $t = "$prefix (value=$lcfloat)";
+ $val = -1;
+ eval { $val = $dbh->quote($lcfloat, SQL_FLOAT); };
+ is ($@, q{}, $t);
+ is ($val, $lcfloat, $t);
-polygon datatype fails with brackets
-[(1,2),(3,4)]
-ERROR: Invalid input for geometric type
-ERROR: any
+ my $ucfloat = uc $float;
+ $t = "$prefix (value=$ucfloat)";
+ $val = -1;
+ eval { $val = $dbh->quote($ucfloat, SQL_FLOAT); };
+ is ($@, q{}, $t);
+ is ($val, $ucfloat, $t);
+}
+$prefix = 'Invalid float value fails when quoting with SQL_FLOAT';
+for my $float ('3abc','123abc','','123e+04e+34','NaNum','-infinitee') {
+ $t = "$prefix (value=$float)";
+ $val = -1;
+ eval { $val = $dbh->quote($float, SQL_FLOAT); };
+ like ($@, qr{Invalid number}, $t);
+ is ($val, -1, $t);
+}
+$dbh->rollback();
-circle datatype invalid format
-(12,34)
-'(12,34)'
-ERROR: any
+## Test placeholders plus binding
+$t='Bound placeholders enforce data types when not using server side prepares';
+$dbh->trace(0);
+$dbh->{pg_server_prepare} = 0;
+$sth = $dbh->prepare('SELECT (1+?+?)::integer');
+$sth->bind_param(1, 1, SQL_INTEGER);
+eval {
+ $sth->execute('10foo',20);
+};
+like ($@, qr{Invalid integer}, 'Invalid integer test 2');
-circle datatype integers
-<(12,34),5>
-'<(12,34),5>'
-<(12,34),5>
+## Test quoting of the "name" type
+$prefix = q{The 'name' data type does correct quoting};
-circle datatype floating point and exponential numbers
-<(-1.2,2E2),3e3>
-'<(-1.2,2E2),3e3>'
-<(-1.2,200),3000>
+for my $word (qw/User user USER trigger Trigger/) {
+ $t = qq{$prefix for the word "$word"};
+ my $got = $dbh->quote($word, { pg_type => PG_NAME });
+ $expected = qq{"$word"};
+ is($got, $expected, $t);
+}
-circle datatype fails with brackets
-[(1,2),(3,4)]
-ERROR: Invalid input for circle type
-ERROR: any
+for my $word (qw/auser userz user-user/) {
+ $t = qq{$prefix for the word "$word"};
+ my $got = $dbh->quote($word, { pg_type => PG_NAME });
+ $expected = qq{$word};
+ is($got, $expected, $t);
+}
-};
+## Test quoting of booleans
-$testdata =~ s/^\s+//;
-my $curtype = '';
-for my $line (split /\n\n+/ => $testdata) {
- my ($text,$input,$quoted,$rows) = split /\n/ => $line;
- next if ! $text;
- $t = "Geometric type test: $text";
- (my $type) = ($text =~ m{(\w+)});
- last if $type eq 'LAST';
- if ($curtype ne $type) {
- $curtype = $type;
- eval { $dbh->do('DEALLOCATE geotest'); }; $dbh->commit();
- $dbh->do(qq{PREPARE geotest($type) AS INSERT INTO
dbd_pg_test_geom(x$type) VALUES (\$1)});
- $sth = $dbh->prepare(qq{INSERT INTO dbd_pg_test_geom(x$type)
VALUES (?)});
- $sth->bind_param(1, '', {pg_type => $typemap{$type} });
- }
- $dbh->do('DELETE FROM dbd_pg_test_geom');
- eval { $qresult = $dbh->quote($input, {pg_type => $typemap{$type}}); };
- if ($@) {
- if ($quoted !~ /ERROR: (.+)/) {
- fail "$t error: $@";
- }
- else {
- like ($@, qr{$1}, $t);
- }
- }
- else {
- is ($qresult, $quoted, $t);
- }
- $dbh->commit();
+my %booltest = (
+undef => 'NULL',
+'t' => 'TRUE',
+'T' => 'TRUE',
+'true' => 'TRUE',
+'TRUE' => 'TRUE',
+1 => 'TRUE',
+01 => 'TRUE',
+'1' => 'TRUE',
+'0E0' => 'TRUE',
+'0e0' => 'TRUE',
+'0 but true' => 'TRUE',
+'0 BUT TRUE' => 'TRUE',
+'f' => 'FALSE',
+'F' => 'FALSE',
+0 => 'FALSE',
+00 => 'FALSE',
+'0' => 'FALSE',
+'false' => 'FALSE',
+'FALSE' => 'FALSE',
+12 => 'ERROR',
+'01' => 'ERROR',
+'00' => 'ERROR',
+' false' => 'ERROR',
+' TRUE' => 'ERROR',
+'FALSEY' => 'ERROR',
+'trueish' => 'ERROR',
+'0E0E0' => 'ERROR', ## Jungle love...
+'0 but truez' => 'ERROR',
+);
- eval { $dbh->do("EXECUTE geotest('$input')"); };
+while (my ($name,$val) = each %booltest) {
+ $name = undef if $name eq 'undef';
+ $t = sprintf 'Boolean quoting of %s',
+ defined $name ? qq{"$name"} : 'undef';
+ eval { $result = $dbh->quote($name, {pg_type => PG_BOOL}); };
if ($@) {
- if ($rows !~ /ERROR: (.+)/) {
- fail "$t error: $@";
- }
- else {
- ## Do any error for now: i18n worries
+ if ($val eq 'ERROR' and $@ =~ /Invalid boolean/) {
pass $t;
}
- }
- $dbh->commit();
-
- eval { $sth->execute($input); };
- if ($@) {
- if ($rows !~ /ERROR: (.+)/) {
- fail $t;
- }
else {
- ## Do any error for now: i18n worries
- pass $t;
+ fail "Failure at $t: $...@\n";
}
+ $dbh->rollback();
}
- $dbh->commit();
-
- if ($rows !~ /ERROR/) {
- $SQL = "SELECT x$type FROM dbd_pg_test_geom";
- my $expected = [[$rows],[$rows]];
- $result = $dbh->selectall_arrayref($SQL);
- is_deeply($result, $expected, $t);
+ else {
+ is ($result, $val, $t);
}
}
