#!/usr/bin/perl
# vim: set ft=perl:
use strict;
use vars qw($parsed);
# The format of all of these tests is highly dependant on the internals
# of SQL::Parser, and are therefore subject to change. If these tests
# suddenly stop passing, blame Jeff Zucker.
plan tests => 57;
# Parse an SQL statement, and return the data we care about.
sub p {
my $parser = DBD::Google::parser->new;
$parser->parse($_[0])
or die $parser->errstr;
return $parser->decompose;
}
# This retrieves the code ref specified for the first
# column in $sql, named $funcname, and applies @testdata
# to it. We expect $expected. E.g.:
#
# apply_function("select uc(foo) from google",
# "uc", "HELLO", "hello");
sub apply_function {
my ($sql, $funcname, $expected, @testdata) = @_;
ok($parsed = p($sql), "Parsed statement");
my $c = $parsed->{'COLUMNS'}->[0]->{'FUNCTION'};
is(ref($c),
'CODE',
"$parsed->{'COLUMNS'}->[0]->{'FUNCTION'} => CODE ref");
if (ref($expected) eq 'CODE') {
my $r = $c->(undef, @testdata);
$expected->($r);
}
else {
is($c->(undef, @testdata),
$expected,
"$funcname(@testdata) -> $expected");
}
}
# ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
# Basic test
# ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
ok($parsed = p('SELECT * FROM google'),
"Parsed statement");
is(ref($parsed->{'COLUMNS'}),
'ARRAY',
"\$parsed->columns is an array");
ok(!$parsed->{'WHERE'},
"\$parsed->where is not defined");
is(scalar @{ $parsed->{'COLUMNS'} },
8,
"Correct number of columns");
# ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
# Basic test with limit
# ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
ok($parsed = p('SELECT * FROM google LIMIT 0, 10'),
"Parsed statement");
is(scalar @{ $parsed->{'COLUMNS'} },
8,
"Correct number of columns");
is($parsed->{'LIMIT'}->{'limit'},
10,
"\$parsed->limit->limit => 10");
is($parsed->{'LIMIT'}->{'offset'},
0,
"\$parsed->limit->offset => 0");
# ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
# More extensive, multiline statement. (The multiline aspect was more
# important in the pre-SQL::Parser days; now it's a historical
# artifact.)
# ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
ok($parsed = p('
SELECT
title
FROM
google
WHERE
q = "perl"
LIMIT
40, 80'), "Parsed statement");
is(ref($parsed->{'COLUMNS'}),
"ARRAY",
"\$parsed->columns => array");
is(scalar(@{ $parsed->{'COLUMNS'} }),
1,
"scalar \@\$parsed->columns == 1");
is($parsed->{'COLUMNS'}->[0]->{'FIELD'},
"title",
"\$parsed->columns(0)->field == 'title'");
is($parsed->{'COLUMNS'}->[0]->{'ALIAS'},
"title",
"\$parsed->columns(0)->alias == 'title'");
is(ref($parsed->{'COLUMNS'}->[0]->{'FUNCTION'}),
"CODE",
"\$parsed->columns(0)->function OK");
is($parsed->{'WHERE'},
'perl',
"Multi-line SQL statement parses correctly");
is($parsed->{'LIMIT'}->{'offset'},
40,
"\$parsed->limit->offset => 40");
is($parsed->{'LIMIT'}->{'limit'},
80,
"\$parsed->limit->limit => 80");
# ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
ok($parsed = p('SELECT title, url, summary FROM google WHERE q = "foo"'),
"Parsed statement");
is(scalar @{ $parsed->{'COLUMNS'} },
3,
"Correct number of columns");
is($parsed->{'COLUMNS'}->[0]->{'ALIAS'},
'title',
'No alias correctly defined');
is($parsed->{'WHERE'},
'foo',
"WHERE q = 'foo'");
# ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
ok($parsed = p("select * from google limit 5"),
"Parsed statement");
is($parsed->{'LIMIT'}->{'offset'},
undef,
"\$parsed->limit->offset => undef");
is($parsed->{'LIMIT'}->{'limit'},
5,
"\$parsed->limit->limit => 5");
# ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
# Rename methodName to method_name, dynamically
# ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
ok($parsed = p('SELECT directory_title FROM google'),
"Parsed statement");
is(scalar @{ $parsed->{'COLUMNS'} },
1,
"Correct number of columns");
is($parsed->{'COLUMNS'}->[0]->{'FIELD'},
"directory_title",
"directory_title => directoryTitle");
# ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
# Function tests
#
# Builtin and random functions
# ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
# Predefined functions
apply_function("select html_strip(summary) from google where q = 'perl'",
qw(html_strip hello <b>hello</b>));
# Arbitrary builtin functions
apply_function("SELECT uc(title) FROM google WHERE q = 'perl'",
qw(uc HELLO hello));
apply_function("SELECT oct(title) FROM google",
qw(oct 1309 2435));
apply_function("SELECT length(title) FROM google",
"length", 12, "Hello, world");
apply_function("SELECT quotemeta(URL) FROM google",
"quotemeta", '\\\n', '\n');
apply_function("SELECT quotemeta(URL) FROM google",
"quotemeta", '\[', '[');
apply_function("SELECT quotemeta(URL) FROM google",
"quotemeta", '\\\0', '\0');
SKIP: {
skip "No network!" => 3
if $ENV{'NO_NET'};
apply_function("SELECT Net::hostent::gethost(hostName) FROM google",
"Net::hostent::gethost",
sub { isa_ok($_[0], "Net::hostent") },
"localhost");
}
# Arbitrary functions: Foo::Bar::baz() style
SKIP: {
skip "Can't load Digest::MD5" => 3
unless eval { require Digest::MD5 };
my $md5 = "c822c1b63853ed273b89687ac505f9fa";
apply_function('select Digest::MD5::md5_hex(title) from google',
"Digest::MD5::md5_hex", $md5, "google");
}
# Arbitrary functions: Foo::Bar->baz() style
SKIP: {
skip "Can't load URI" => 3
unless eval { require URI };
apply_function('SELECT URI->new(URL) from google where q = "apache"',
"URI->new", sub { isa_ok($_[0], "URI::http") },
qw(//www.google.com/search http));
}