Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked

Dancer + SQL::Translator

by jeffa (Bishop)
on Mar 27, 2012 at 00:16 UTC ( #961793=CUFP: print w/replies, xml ) Need Help??

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 ); } };


(the triplet paradiddle with high-hat)

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

    and leaving MySQL in with:

    ERROR (line 789): Invalid constraint: Was expecting primary key def, o +r unique key def, or foreign key def

    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.

      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. :)


      (the triplet paradiddle with high-hat)

        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) );

        When the table was originally created those columns were only timestamps with no precision.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: CUFP [id://961793]
Approved by planetscape
Front-paged by Arunbear
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (5)
As of 2017-02-21 16:05 GMT
Find Nodes?
    Voting Booth?
    Before electricity was invented, what was the Electric Eel called?

    Results (313 votes). Check out past polls.