Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

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


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


      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)
      

        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?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: CUFP [id://961793]
Approved by planetscape
Front-paged by Arunbear
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (10)
As of 2015-07-06 23:05 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (85 votes), past polls