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);
}
}
lmpx.com only provides a reader for public news (NNTP) servers. It is not
affiliated with the servers or forums shown here and is not responsible for
the content of articles, which is written by their respective authors.