Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

Regexp for Mysql 5.1 join Problem

by Ben Win Lue (Friar)
on Jun 12, 2006 at 14:41 UTC ( [id://554821]=perlquestion: print w/replies, xml ) Need Help??

Ben Win Lue has asked for the wisdom of the Perl Monks concerning the following question:

Dear brethren,

I am trying to find a Perl fix for a Mysql problem. We are trying to migrate from Mysql 4.0something to Mysql 5.1.

Our application has grown organically and is using a couple of thousands of statement of which some have the ugly form:

select * from table1 a, table2 b left join table3 c on b.id = c.id where a.id = b.id
Statements like this are running with 4.0 smooth but cause errors with 5.1. The problem is caused by the mix of two join syntaxes: comma and "left join".
Since most of our statements are executed by a library function, we had the idea of identifying and fixing these statements on the fly.
The fix would be transforming the above statement to:
select * from table1 a inner join table2 b left join table3 c on b.id = c.id where a.id = b.id
which is not causing problems.

Did somebody run already in the same problem and has a neat regexp to identify and fix?
Is there a enlightened brother who could give me a hint for a regexp, that does the job?

Humbly yours

Replies are listed 'Best First'.
Re: Regexp for Mysql 5.1 join Problem
by CountZero (Bishop) on Jun 12, 2006 at 15:46 UTC
    Did you have a look at SQL::Statement. It can parse SQL statements into Perl OO.

    I would start from the OO-representation to transform it into the form you require.

    CountZero

    "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://554821]
Approved by Joost
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others having a coffee break in the Monastery: (7)
As of 2024-03-28 19:44 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found