Dancer + SQL::Translator

I have recently discovered a fairly new project, Dancer and a fairly old one, SQL::Translator. The following code details a Dancer handler which, with the proper credentials supplied will translate the given database into either a text file which contains the create definitions to create a schema or a PNG file that contains an ER diagram of the schema. MySQL is the target database, but others should work as well.

use Dancer ':syntax';
use SQL::Translator;

get '/render' => sub {
    my $db = params->{db};
    my $format = params->{format} || 'text';

    my $translator = SQL::Translator->new(
        from        => 'DBI',
        to          => 'MySQL',
        parser_args => {
            dsn         => "dbi:mysql:$db",
            db_user     => '',
            db_password => '',
        },
    );
    my $sql = $translator->translate( data => '' );

    if ($format eq 'text') {
        content_type 'text';
        return $sql;
    }
    elsif ($format eq 'png') {
        content_type 'png';
        my $translator = SQL::Translator->new(
            from          => 'MySQL',
            to            => 'GraphViz',
            producer_args => {
                output_type   => 'png',
                natural_join  => 1,
            },
        );
        return $translator->translate( data => $sql );
    }
};
[download]

jeffa

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)

Comment on Dancer + SQL::Translator Select or Download Code

Replies are listed 'Best First'.
Re: Dancer + SQL::Translator by mje (Curate) on Mar 27, 2012 at 14:03 UTC
Thanks for the reminder about SQL::Translator. I added a call to "dance;" at the end to run it from the command line directly, changed the DSN etc to point at our Oracle db and I changed those "MySQL"s to "Oracle". Unfortunately, although the SQL is generated the png generation failed for me immediately with xxx: `ERROR (line 9): Invalid statement: Was expecting remark, or run, or pr +ompt, or create, or table comment, or comment on table, or comment on + column, or alter, or drop` [download] and leaving MySQL in with: `ERROR (line 789): Invalid constraint: Was expecting primary key def, o +r unique key def, or foreign key def` [download] The generated SQL in both cases looks ok as far as I can tell but the line number reported seems to bear no relationship to the SQL at that line number. :-( Obviously the same happens with sqlt-graph. BTW, I thought GraphViz was deprecated in favour of Graphviz2.	[reply] [d/l] [select]
Re^2: Dancer + SQL::Translator by jeffa (Bishop) on Mar 27, 2012 at 15:31 UTC
I saw that error creep up many times when i was trying to put this together. Looks like more will be necessary than simply replacing the name of the database vendor/driver. Take a look at the value for `$sql` and see what part(s) are invalid for Oracle. As for GraphViz2, normally i would jump up to upgrade but in this case i just need something quick and dirty to help me visualize my problem that needs to be solved. Thanks for your time. :) jeffa L-LL-L--L-LL-L--L-LL-L-- -R--R-RR-R--R-RR-R--R-RR B--B--B--B--B--B--B--B-- H---H---H---H---H---H--- (the triplet paradiddle with high-hat)	[reply] [d/l] [select]
Re^3: Dancer + SQL::Translator by mje (Curate) on Mar 28, 2012 at 12:18 UTC
It is the double bracketed values in the timestamp definition. `CREATE TABLE "ACCOUNT" ( "ACCOUNT_ID" number(38) NOT NULL, "DATE_TIME_UTC" timestamp(6)(11), "CURRENCY_ID" number(38) NOT NULL, "NAME" varchar2(128) NOT NULL, "BALANCE" number(14) NOT NULL, "ACCOUNT_TYPE_ID" number(38) NOT NULL, "CREATED_BY_USER" varchar2(128), "CREATED_DATE_TIME_UTC" timestamp(6)(11), "MODIFIED_BY_USER" varchar2(128), "MODIFIED_DATE_TIME_UTC" timestamp(6)(11) );` [download] When the table was originally created those columns were only timestamps with no precision.	[reply] [d/l]
Re^4: Dancer + SQL::Translator by mje (Curate) on Mar 28, 2012 at 16:18 UTC