BEGIN {
use_ok(
'DBIx::MyParseX'
);
};
ok(
my
$parser
= DBIx::MyParse->new(
database
=>
'test'
,
datadir
=>
'tmp'
),
"Initializing Parser"
);
my
$sql
=
<<"EOSQL";
select
customers.first_name,
last_name,
sum( purchase_history.amt ) as total_purchase
from customers
inner join purchase_history using ( ACCT_NUM )
where
customers.puchase_date = 20071210
and customers.last_name = 'Brown'
group by first_name DESC
order by total_purchase desc
limit 10
EOSQL
ok(
$sql
,
"Simple SQL statement created"
);
ok(
my
$query
=
$parser
->parse(
$sql
) ,
"Parsing simple query"
);
diag
"Testing table renaming routines ..."
;
diag
"Attempting to renaming table 'customers' to 'friends' ..."
;
diag
"Here is the SQL:\n\n$sql\n"
;
ok(
$query
->renameTable(
"customers"
,
"friends"
) ,
"Renaming tables"
);
my
$new_sql
=
$query
->
print
;
my
$matched
=
"SELECT `friends`.`first_name`, `last_name`, sum(`purchase_history`.`amt`) AS `total_purchase` FROM (`test`.`friends` INNER JOIN `test`.`purchase_history` USING (`ACCT_NUM`)) WHERE (( `friends`.`puchase_date` = 20071210 ) and ( `friends`.`last_name` = 'Brown' )) GROUP BY `first_name` ORDER BY `total_purchase` DESC LIMIT 10"
;
ok (
$new_sql
eq
$matched
,
'Checking rewrite'
);
$sql
=
<<"EOSQL";
select a.col_1, b.col_2 as from_b, max(c.col_3) as maxima
from table_1 a
inner join table_2 b
where a.col_1 > 5
group by col_1, from_b DESC
limit 4
EOSQL
ok(
$query
=
$parser
->parse(
$sql
),
"Parsing complex query"
);
diag(
"Logical tests ...."
);
ok( quick_parse(
"select * from temp"
)->hasSelect,
"hasSelect"
);
ok( ! quick_parse(
"delete * from test"
)->hasSelect,
"! hasSelect"
);
ok( quick_parse(
"select * from temp"
)->hasFrom,
"hasFrom"
);
ok( ! quick_parse(
"delete * from test"
)->hasFrom,
"! hasFrom"
);
ok( quick_parse(
"select * from temp"
)->hasTables,
"hasTables"
);
ok( quick_parse(
"select * from temp where a = 1"
)->hasWhere,
"hasWhere"
);
ok( quick_parse(
"select * from temp limit 10"
)->hasLimit,
"hasLimit"
);
ok( quick_parse(
"select * from temp order by a"
)->hasOrder,
"hasOrder"
);
ok( quick_parse(
"select * from temp having max(a)"
)->hasHaving,
"hasHaving"
);
ok( quick_parse(
"select * from temp group by a"
)->hasGroup,
"hasGroup"
);
sub
quick_parse {
return
(
DBIx::MyParse->new(
database
=>
'test'
,
datadir
=>
'tmp'
)->parse(
shift
)
);
}