Parse and change an SQL statement

[philgoetz]

I know the module SQL::Statement can parse an SQL statement. But I need to automatically convert statements back and forth between Sybase and SQLite, so I need to parse a statement, then CHANGE parts of it (e.g., change column names to avoid reserved words or to emulate Sybase functions), and then construct a new SQL statement for the other database type to execute.

For instance, I would want to convert

"SELECT len(ec#), getdate(), user_name() FROM ident"

into

"SELECT length(ec_num), date(), $ENV{USER} FROM identity"

(SQLite does not allow the column names 'ec#' or 'ident'.)

As far as I can tell, SQL::Statement doesn't let you construct a new statement out of constituent parts. Any idea what can?

[chrestomanci]

Where is this SQL that needs translating coming from? Did you write it (or another programmer on your team). Is it from a legacy codebase? Is it being machine generated by another program that you can't modify?

If you, or a team mate are writing the SQL, them SQL::Abstract might be an option. You write your query in a perl like syntax, and it turns it into SQL in whatever dialect you specify. It can't do everything, but does most thing fairly well, including joins of arbitrary complexity, however it is only practical if you are writing the queries, rather than something else.

If the SQL is hard coded into a legacy codebase, or is being generated by a legacy program, then I think you will have to write a translator yourself. The good news is doing so is a finite problem. If it is old hard coded SQL, then you just need to find them all and write translation. If it is an old generator, then it is unlikely to be generating anything unexpected (like a human programmer might), so again you need to figure out how it is building it's queries, and deconstruct them, so that you can re-generate them in the other dialect.

[clueless newbie]

I've played with Percona's Maatkit which has an SQLParser and it did an excellent job with the sql that I pushed through( Granted that my set wasn't extremely large). Have a look at http://code.google.com/p/maatkit/source/browse/trunk/common/SQLParser.pm.

[philgoetz]

In fact, SQL::Statement appears to be unusable even for just parsing. The key function is column_defs(), which is supposed to return the columns in the statement; and it isn't defined. Trying to execute

our $SqlParser = SQL::Parser->new() or die "Could not create an SQL::P
+arser";
my $parse = SQL::Statement->new('SELECT foo FROM bar', $SqlParser);
my @columns = $parse->column_defs();

results in the error

Can't locate object method "column_defs" via package "SQL::Statement" 
+at /local/ifs_projects/prok/function/src/lib/pgoetzUtils.pm line 939.

[CountZero]

I tried this and it works:

use Modern::Perl;
use Data::Dump qw /dump/;

use SQL::Parser;
use SQL::Statement;

my $SqlParser = SQL::Parser->new() or die "Could not create an SQL::Pa
+rser";
my $parse = SQL::Statement->new('SELECT fie, max(foo), count(baz) FROM
+ bar JOIN fee WHERE foo > 10', $SqlParser);
my @columns = $parse->column_defs();
say dump @columns;

Output:

  { fullorg => "fie", type => "column", value => "fie" },
  {
    alias    => "MAX",
    arg      => { fullorg => "foo", type => "column", value => "foo" },
    argstr   => "foo",
    distinct => "ALL",
    fullorg  => "max (foo)",
    name     => "MAX",
    type     => "setfunc",
  },
  {
    alias    => "COUNT",
    arg      => { fullorg => "baz", type => "column", value => "baz" },
    argstr   => "baz",
    distinct => "ALL",
    fullorg  => "count (baz)",
    name     => "COUNT",
    type     => "setfunc",
  },

[jandrew]

Will DBI Solve your problem?

[bdo]

How about SQL::Translator (https://metacpan.org/module/SQL::Translator) ?

[GrandFather]

As documented out of the box SQL::Translator only handles CREATE and ALTER statements. The OP gave a SELECT statement as a specific example so the suggested module is unlikely to fit the bill.

[bdo]

Oh right, I missed that distinction.