Hi,
Thanks, Jon. I've kept the part of your code that does the regular
expression stuff (I think) -- why exactly is this necessary?
Also, Re: Darren's message, using .schema instead of .dump from within
sqlite3.exe seems to work. However, the SQLite parser doesn't seem to
accept primary/foreign key stuff, such as a file containing just:
CREATE TABLE test (first, last primary key);
This is accepted by the SQLite parser of sqlite3.exe. Did I miss
something? I thought I would be able to add primary keys and foreign
keys so that my ER diagrams show links between tables.
Thanks,
Dan
Jon Schutz wrote:
> On Sat, 2008-07-26 at 23:25 -0400, Daniel Zingaro wrote:
>
>> Hi all,
>>
>> (I hope this is the right place for this. The mailing list name says
>> developers, which I'm most definitely not. If there's another mailing
>> list to talk about using SQL::translator, please let me know.)
>>
>> I'm going to be teaching a databases course in September and looking for
>> a tool for making ER diagrams from databases. I'm visually impaired and
>> so tools like Visio (that people seem to use) are out. I think
>> SQL::Translator can help, but:
>>
>> I installed SQL::Translator, but I could not install Test::Differences.
>> Is this important? ... If yes, I can provide all the details of the
>> failed installation(though it seems to be working, unless the below
>> problem is related).
>>
>> Next, I installed SQLite (sqlite3.exe), created a sample database, used
>> '.dump' to make a .sql file, and tried to give this to sqlt-graph to
>> output an ER diagram for me. It complains saying that line 3 is
>> unexpected. The dumped .sql is as follows:
>>
>> BEGIN TRANSACTION;
>> CREATE TABLE test (first, last);
>> INSERT INTO "test" VALUES('tick','tock');
>> COMMIT;
>>
>> Am I doing something wrong?
>>
>> Also, I'd appreciate any insights into the best way to generate ER
>> diagrams. Should I be using sqlt-graph at all? (There seems to be other
>> options.) Has anyone had more luck with dot or neato??
>>
>> OK... I'll stop here :$. Thanks a lot,
>> Dan
>>
>>
>
> I have successfully used SQL::Translator and dot to produce a graphical
> representation of quite complex schema. I'm not sure I could recommend
> the output for the visually impaired though - I imagine you'd need to
> put some effort into getting font sizes/scales correct. It was some
> time ago when I used this but I recall that there were a couple gotchas
> - one with getting the SQL::Translator args right for going from a
> DBIx::Class schema to GraphViz, and the other with tidying up the dot
> output as some characters came through improperly escaped. The crude
> script I use is pasted below. I go DBIx::Class to MySQL to GraphViz; if
> you already have a SQLite schema, then you probably only need the second
> half.
>
>
>
> use SQL::Translator;
> use strict;
> my $outfile = "schema.sql";
> my $dotfile = "schema.dot";
> my $psfile = "schema.ps";
> my $schema = "Local::My::Schema"; # this is what we want to dump
>
> my $translator = SQL::Translator->new(
> debug => 0,
> trace => 0,
> parser =>
> 'SQL::Translator::Parser::DBIx::Class',
> parser_args => { 'DBIx::Schema' =>
> $schema },
> to => 'MySQL',
> );
> my $output = $translator->translate();
> die $translator->error unless $output;
> write_file($outfile, $output);
>
>
> my $grapher = SQL::Translator->new(
> from => 'MySQL',
> to => 'GraphViz',
> producer_args => {
> show_fields => 1,
> output_type => 'canon',
> show_constraints => 1,
> show_sizes => 1,
> show_datatypes => 1,
> },
> ) or die SQL::Translator->error;
>
> my $dot = $grapher->translate(\$output);
> die "Error: " . $grapher->error unless $dot;
>
> $dot =~ s/\\([{}|])/$1/g;
> $dot =~ s/\\\\\n//og;
> $dot =~ s/size\s*=\s*"8.5,11"/size = "7.5,10.5"/;
>
> write_file($dotfile, $dot);
>
> my $dotcmd = "/usr/bin/dot";
> $dotcmd = "/usr/local/bin/dot" unless -e $dotcmd;
> die "graphviz must be installed to create PostScript file" unless -e $dotcmd;
>
> `$dotcmd -Tps $dotfile -o $psfile`;
> print STDERR "Wrote $psfile\n";
>
>
>
> sub write_file
> {
> my ($file, $data) = @_;
>
> open(my $out, ">", $file) or die "Failed to open $file for writing: $!";
> print $out $data;
> close($out);
>
> print STDERR "Wrote $file\n";
> }
>
>
>
> Regards,
>
>
>
-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
--
sqlfairy-developers mailing list
sqlfairy-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlfairy-developers
