# vi:filetype=
# Tests for the OpenResty::RestyScript module. (View part)
my $skip;
my $ExePath;
BEGIN {
use FindBin;
$ExePath = "$FindBin::Bin/../haskell/bin/restyscript";
if (!-f $ExePath) {
$skip = "$ExePath is not found.\n";
return;
}
if (!-x $ExePath) {
$skip = "$ExePath is not an executable.\n";
return;
}
};
use Test::Base $skip ? (skip_all => $skip) : ();
#use Smart::Comments;
use lib 'lib';
use OpenResty::RestyScript;
#plan tests => 3 * blocks();
plan tests => 118;
sub quote {
my $s = shift;
if (!defined $s) { $s = '' }
$s =~ s/\n/{NEW_LINE}/g;
$s =~ s/\r/{RETURN}/g;
$s =~ s/\t/{TAB}/g;
'$y$' . $s . '$y$';
}
sub quote_ident {
qq/"$_[0]"/
}
run {
my $block = shift;
my $name = $block->name;
my %in_vars;
my $in_vars = $block->in_vars;
if (defined $in_vars) {
my @ln = split /\n+/, $in_vars;
map {
my ($var, $val) = split /=/, $_, 2;
$in_vars{$var} = $val;
} @ln;
}
my $sql = $block->sql or die "$name - No --- sql section found.\n";
my $view = OpenResty::RestyScript->new('view', $sql);
my ($frags, $stats);
eval {
($frags, $stats) = $view->compile;
};
### Fragments: $frags
### Stats: $stats
my $res;
if ($@ && !defined $block->error) { warn $@ }
elsif (defined $block->error) {
my $error = $block->error || '';
$error =~ s/^\s+$//g;
(my $got = $@) =~ s/^expecting .*\n//ms;
is $got, $error, "$name - error msg ok";
}
#%in_vars,
my (@models, @cols, @vars, @unbound);
if ($stats) {
@models = @{ $stats->{modelList} };
my @m;
for my $model (@models) {
if ($model =~ s/^\$//) {
if (defined $in_vars{$model}) {
push @m, $in_vars{$model};
} else {
push @m, '$'.$model;
}
} else {
push @m, $model;
}
}
@models = @m;
}
### @models
my $ex_models = $block->models;
if (defined $ex_models) {
is join(' ', @models), $block->models, "$name - model list ok";
}
my @bits;
for my $frag (@$frags) {
if (ref $frag) { # being a variable
my ($var, $type) = @$frag;
my $quote = $type eq 'symbol' ? \"e_ident : \"e;
push @vars, $var;
if (!defined $in_vars{$var}) {
push @unbound, $var;
push @bits, $quote->('');
} else {
push @bits, $quote->($in_vars{$var});
}
} else {
push @bits, $frag;
}
}
my $pgsql = @bits ? (join '', @bits) : '';
my $ex_cols = $block->cols;
if (defined $ex_cols) {
is join(' ', @cols), $block->cols, "$name - model cols ok";
}
if (defined $block->out) {
(my $expected = $block->out) =~ s/\n$//g;
is $pgsql, $expected, "$name - sql emittion ok";
}
my $ex_vars = $block->vars;
if (defined $ex_vars) {
is join(' ', @vars), $ex_vars, "$name - var list ok";
}
my $ex_unbound = $block->unbound;
if (defined $ex_unbound) {
is join(' ', @unbound), $ex_unbound, "$name - unbound var list ok";
}
};
__DATA__
=== TEST 1: Simple
--- sql
select * from Carrie
--- error
--- models: Carrie
--- out: select * from "Carrie"
=== TEST 2: where clause
--- sql
select * from Carrie where name='zhxj'
--- error
--- models: Carrie
--- out: select * from "Carrie" where "name" = 'zhxj'
=== TEST 3: with trailing ;
--- sql
select * from Carrie;
--- error
--- out: select * from "Carrie"
=== TEST 4: Bad token
--- sql
select * from Carrie blah
--- error
"view" (line 1, column 22):
unexpected "b"
=== TEST 5: Unexpected end of input
--- sql
select *
from Carrie
where
--- error
"view" (line 4, column 1):
unexpected end of input
=== TEST 6: Aggregate function 'count'
--- sql
select count(*)
from Carrie
where name='zhxj';
--- error
--- models: Carrie
--- out: select "count"(*) from "Carrie" where "name" = 'zhxj'
=== TEST 7: Group by
--- sql
select sum ( * ) as blah
from People, Blah
where name='zhxj'
group by name
--- error
--- models: People Blah
--- out
select "sum"(*) as "blah" from "People", "Blah" where "name" = 'zhxj' group by "name"
=== TEST 8: Bad ";"
--- sql
select sum ( * )
from People, Blah
where name='zhxj';
group by name
--- error
"view" (line 4, column 1):
unexpected "g"
=== TEST 9: 'and' in where
--- sql
select *
from foo
where name = 'Hi' and age > 4;
--- error
--- models: foo
--- out: select * from "foo" where ("name" = 'Hi' and "age" > 4)
=== TEST 10: 'or' in where
--- sql
select *
from blah
where name = 'Hi' or age <= 3;
--- error
--- models: blah
--- out: select * from "blah" where ("name" = 'Hi' or "age" <= 3)
=== TEST 11: escaped single quotes
--- sql
select *
from blah
where name = '''Hi' or age <= 3;
--- error
--- out: select * from "blah" where ("name" = '''Hi' or "age" <= 3)
=== TEST 12: unmatched single quotes
--- sql
select *
from blah
where name = ''''Hi' or age <= 3;
--- error
"view" (line 3, column 18):
unexpected "H"
=== TEST 13: unmatched single quotes
--- sql
select *
from blah
where name = ''
--- error
--- models: blah
--- out: select * from "blah" where "name" = ''
=== TEST 14: empty string literals
--- sql
select *
from blah
where name = '' or age <= 3;
--- error
--- models: blah
--- out: select * from "blah" where ("name" = '' or "age" <= 3)
=== TEST 15: sql injection
--- sql
select *
from blah
where name = '\'' and #@!##$@ --' or age <= 3;
--- error
"view" (line 3, column 23):
unexpected "#"
=== TEST 16: $q$ ... $q$
--- sql
select *
from blah
where name = $q$Laser's gift...$$ \n\nhehe $q$ and age > 3;
--- error
--- models: blah
--- out: select * from "blah" where ("name" = 'Laser''s gift...$$ \\n\\nhehe ' and "age" > 3)
=== TEST 17: $q$ ... $q$ ... $q$
--- sql
select *
from blah
where name = $q$Laser's gift...$q$ update nhehe $q$ and age > 3;
--- error
"view" (line 3, column 36):
unexpected "u"
=== TEST 18: $q$ ... $a$ ... $q$
--- sql
select *
from blah
where name = $q$Laser's gift...$a$ update nhehe $q$ and age > 3;
--- out
select * from "blah" where ("name" = 'Laser''s gift...$a$ update nhehe ' and "age" > 3)
=== TEST 19: $q$q$q$
--- sql
select *
from blah
where name = $q$q$q$ and age > 3;
--- error
--- models: blah
--- out: select * from "blah" where ("name" = 'q' and "age" > 3)
=== TEST 20: empty string literals
--- sql
select *
from Book, Student
where Book.browser = Student.name and Book.title = '' or age <= 3;
--- error
--- models: Book Student
--- out: select * from "Book", "Student" where (("Book"."browser" = "Student"."name" and "Book"."title" = '') or "age" <= 3)
=== TEST 21: offset & limit
--- sql
select * from Carrie limit 1 offset 0
--- error
--- out: select * from "Carrie" limit 1 offset 0
=== TEST 22: proc call
--- sql
select hello(1) from Carrie limit 1 offset 0
--- error
--- out: select "hello"(1) from "Carrie" limit 1 offset 0
=== TEST 23: proc call with more parameters
--- sql
select hello(1, '2') from Carrie limit 1 offset 0
--- error
--- out: select "hello"(1, '2') from "Carrie" limit 1 offset 0
=== TEST 24: proc names with underscores
--- sql
select hello_world(1, '2') from Carrie limit 1 offset 0
--- models: Carrie
--- out: select "hello_world"(1, '2') from "Carrie" limit 1 offset 0
=== TEST 25: from a proc call
--- sql
select * from hello_world(1, '2')
--- models:
--- out: select * from "hello_world"(1, '2')
=== TEST 26: from a proc call
--- sql
select * from foo where bar = 'a''b\'\\' and a >= 3
--- models: foo
--- out: select * from "foo" where ("bar" = 'a''b''\\' and "a" >= 3)
=== TEST 27: Test the literal
--- sql
select * from foo where bar = '\n\t\r\'\\'''
--- error
--- out: select * from "foo" where "bar" = '\n\t\r''\\'''
=== TEST 28: Test the literal
--- sql
select * from foo where bar = $$hi$$
--- error
--- unbound:
--- out: select * from "foo" where "bar" = 'hi'
=== TEST 29: Test the literal
--- sql
select * from foo where bar = $hello$hi$h$$hello$ and a>3
--- error
--- unbound:
--- out: select * from "foo" where ("bar" = 'hi$h$' and "a" > 3)
=== TEST 30: variable interpolation
--- sql
select * from $model where $col = 'hello'
--- models: $model
--- vars: model col
--- unbound: model col
--- out: select * from "" where $y$$y$ = 'hello'
=== TEST 31: variable interpolation
--- sql
select * from $model where $col = 'hello'
--- in_vars
model=blah
col=foo
--- models: blah
--- vars: model col
--- unbound:
--- out: select * from "blah" where $y$foo$y$ = 'hello'
=== TEST 32: variable interpolation
--- sql
select * from $model where col = $value order by $col
--- in_vars
model=blah
col=baz
value='howdy'
--- models: blah
--- vars: model value col
--- unbound:
--- out: select * from "blah" where "col" = $y$'howdy'$y$ order by "baz" asc
=== TEST 33: unbound vars in literals
--- sql
select * from $model_1, $model_2 where $col = $value and $blah = $val2
--- in_vars
model_1=Cat
--- models: Cat $model_2
--- unbound: model_2 col value blah val2
--- out: select * from "Cat", "" where ($y$$y$ = $y$$y$ and $y$$y$ = $y$$y$)
=== TEST 34: nude keywords
--- sql
select * from from where select='abc'
--- out
select * from "from" where "select" = 'abc'
=== TEST 35: keywords with "
--- sql
select * from "from" where "select"='abc'
--- error
--- models: from
--- out: select * from "from" where "select" = 'abc'
=== TEST 36: order by with asc
--- sql
select * from blah order by id asc
--- error
--- out: select * from "blah" order by "id" asc
=== TEST 37: order by with asc/desc
--- sql
select * from blah order by id desc, name asc
--- error
--- out: select * from "blah" order by "id" desc, "name" asc
=== TEST 38: | default is now invalid
--- sql
select * from blah offset $offset | 0 limit $limit | 32
--- in_vars
--- error
"view" (line 1, column 35):
unexpected "|"
=== TEST 39: offset & limit
--- sql
select * from blah offset $offset limit $limit
--- in_vars
offset=2
limit=3
--- out
select * from "blah" offset $y$2$y$ limit $y$3$y$
=== TEST 40: column alias
--- sql
select Post.id as ID from Post
--- out: select "Post"."id" as "ID" from "Post"
--- models: Post
=== TEST 41: union
--- sql
(select Blah) union ( select Foo where id >= 3 )
--- out: ((select "Blah") union (select "Foo" where "id" >= 3))
--- models:
=== TEST 42: intersect
--- sql
( select Blah ) intersect (select Foo where id >= 3)
--- out: ((select "Blah") intersect (select "Foo" where "id" >= 3))
--- models:
=== TEST 43: except
--- sql
(select Blah) except( select Foo where id >= 3)
--- out: ((select "Blah") except (select "Foo" where "id" >= 3))
--- models:
=== TEST 44: big union
--- sql
(select id, title
from Post
where id > $current
order by id asc
limit 1)
union
(select id, title
from Post
where id < $current
order by id desc
limit 1)
--- out: ((select "id", "title" from "Post" where "id" > $y$$y$ order by "id" asc limit 1) union (select "id", "title" from "Post" where "id" < $y$$y$ order by "id" desc limit 1))
=== TEST 45: select literals
--- sql: select 0 as id, 'a' as title, 32
--- out: select 0 as "id", 'a' as "title", 32
=== TEST 46: regression in 19-view.t
--- sql: select $select_col from A order by $order_by
--- in_vars
select_col=id
order_by=id
--- out: select $y$id$y$ from "A" order by "id" asc
=== TEST 47: union all
--- sql: (select 3) union all (select 4)
--- out: ((select 3) union all (select 4))
=== TEST 48: date_part
--- sql
select id, title, date_part('day', created) as day
from Post
where date_part('year', created) = 2008
--- out: select "id", "title", "date_part"('day', "created") as "day" from "Post" where "date_part"('year', "created") = 2008
=== TEST 49: bug
--- sql
select count(*)
from $model
--- out: select "count"(*) from ""
--- models: $model
=== TEST 50: like and other operators
--- sql
select * from Post where id like '%Hello%'
--- out: select * from "Post" where "id" like '%Hello%'
--- models: Post
=== TEST 51: random operators
--- sql
select sum(1) as count, sum(3+ 2 * (3 - 5^7)) from Post
--- out: select "sum"(1) as "count", "sum"((3 + (2 * (3 - (5 ^ 7))))) from "Post"
=== TEST 52: % and /
--- sql
select 32 % (3 ^ (7- 5) / 25 )
--- out: select (32 % ((3 ^ (7 - 5)) / 25))
=== TEST 53: ||
--- sql
select '32' || '56'
--- out: select ('32' || '56')
=== TEST 54: || in proc calls
--- sql
select date_part('year', created) || date_part('mon' || 'th', created) from Post
--- out: select ("date_part"('year', "created") || "date_part"(('mon' || 'th'), "created")) from "Post"
--- models: Post
=== TEST 55: || with vars
--- sql
select * from Post where title like '%' || $keyword || '%'
--- in_vars
keyword=Perl
--- out: select * from "Post" where "title" like (('%' || $y$Perl$y$) || '%')
=== TEST 56: blog archive listing
--- sql
select (date_part('year', created) || '-'
|| date_part('month', created) || '-01')::date
as year_month,
sum(1) as count
from Post
group by year_month
order by year_month desc
offset $offset
limit $limit
--- in_vars
offset=0
limit=12
--- out: select ((("date_part"('year', "created") || '-') || "date_part"('month', "created")) || '-01')::"date" as "year_month", "sum"(1) as "count" from "Post" group by "year_month" order by "year_month" desc offset $y$0$y$ limit $y$12$y$
=== TEST 57: try to_char
--- sql
select to_char(created, 'YYYY-MM-01') :: date as year_month,
sum(1) as count
from Post
group by year_month
order by year_month desc
offset $offset
limit $limit
--- in_vars
offset=0
limit=12
--- out: select "to_char"("created", 'YYYY-MM-01')::"date" as "year_month", "sum"(1) as "count" from "Post" group by "year_month" order by "year_month" desc offset $y$0$y$ limit $y$12$y$
=== TEST 58: carrie's view
--- sql
select * from yisou_comments_fetch_results($parentid,'',$orderby,$offset,$count,$child_offset,$child_count,$dsc)
--- in_vars
offset=0
--- out: select * from "yisou_comments_fetch_results"($y$$y$, '', $y$$y$, $y$0$y$, $y$$y$, $y$$y$, $y$$y$, $y$$y$)
=== TEST 59: for @@ operator
--- sql
select * from table where field @@ to_tsquery('chinesecfg', $keyword)
--- in_vars
keyword='Hello'
--- out: select * from "table" where "field" @@ "to_tsquery"('chinesecfg', $y$'Hello'$y$)
=== TEST 60: for >>= operator
--- sql
select * from table where iprange >>= '202.165.100.1'
--- out: select * from "table" where "iprange" >>= '202.165.100.1'
=== TEST 61: for distinct
--- sql
select distinct ca, cb from table where ca > 0
--- out: select distinct "ca", "cb" from "table" where "ca" > 0