Bug #62131 for Parse-Dia-SQL: Add support for uml mode

Thu Oct 14 11:37:37 2010 felix.ostmann [...] thewar.de - Ticket created

I have patched 4 things: - add the flag --uml to the parsediasql - first exec small packages, then do all inserts (wrong order) - type bigserial is also int8 - in UML-Mode the naming for n:m tables was wrong. With this change we get correnct n:m tables: create table admins2admin_roles ( admin_role_id serial not null, admin_id serial not null, constraint pk_admins2admin_roles primary key (admin_role_id,admin_id) ) ; alter table admins2admin_roles add constraint admins2admin_roles_fk_Admin_rolesAdmin_role_id foreign key (admin_role_id) references admin_roles (admin_role_id) on delete cascade; alter table admins2admin_roles add constraint admins2admin_roles_fk_AdminsAdmin_id foreign key (admin_id) references admins (admin_id) on delete cascade; befor there was the wrong naming for the foreign key: admins2admin_roles_fk_AdminsAdmin_id foreign key (admin_role_id) references admins (admin_id) on delete cascade;

Subject:

n:m-table.png

Download n:m-table.png
image/png 27.1k

Subject:

parse-dia-sql-0.17.patch

diff -ru Parse-Dia-SQL-0.16/bin/parsediasql Parse-Dia-SQL-0.17/bin/parsediasql --- Parse-Dia-SQL-0.16/bin/parsediasql 2010-01-18 21:39:17.000000000 +0100 +++ Parse-Dia-SQL-0.17/bin/parsediasql 2010-10-14 14:48:20.000000000 +0200 @@ -1,4 +1,7 @@ -#!/usr/bin/perl +#!/home/ostmann/perl5lib/bin/perl + +eval 'exec /home/ostmann/perl5lib/bin/perl -S $0 ${1+"$@"}' + if 0; # not running under some shell # $Id: parsediasql,v 1.8 2009/11/17 12:21:25 aff Exp $ @@ -15,18 +18,20 @@ my $file = undef; my $ignore_type_mismatch = undef; my $db = undef; +my $uml = 0; GetOptions( "help|?" => \$help, "file=s" => \$file, "db=s" => \$db, + "uml" => \$uml, "ignore_type_mismatch" => \$ignore_type_mismatch, ) or pod2usage(2); pod2usage(1) if $help; pod2usage(qq{Missing argument 'file'}) if !$file; pod2usage(qq{Missing argument 'db'}) if !$db; -my $dia = Parse::Dia::SQL->new(file => $file, db => $db, ignore_type_mismatch => $ignore_type_mismatch); +my $dia = Parse::Dia::SQL->new(file => $file, db => $db, ignore_type_mismatch => $ignore_type_mismatch, uml => $uml); print $dia->get_sql(); __END__ diff -ru Parse-Dia-SQL-0.16/lib/Parse/Dia/SQL/Output.pm Parse-Dia-SQL-0.17/lib/Parse/Dia/SQL/Output.pm --- Parse-Dia-SQL-0.16/lib/Parse/Dia/SQL/Output.pm 2010-05-21 07:19:36.000000000 +0200 +++ Parse-Dia-SQL-0.17/lib/Parse/Dia/SQL/Output.pm 2010-10-14 14:48:01.000000000 +0200 @@ -219,14 +219,14 @@ . $self->{newline} . $self->get_permissions_create() . $self->{newline} - . "-- get_inserts" - . $self->{newline} - . $self->get_inserts() - . $self->{newline} . "-- get_smallpackage_post_sql" . $self->{newline} . $self->get_smallpackage_post_sql() . $self->{newline} + . "-- get_inserts" + . $self->{newline} + . $self->get_inserts() + . $self->{newline} . "-- get_associations_create" . $self->{newline} . $self->get_associations_create(); diff -ru Parse-Dia-SQL-0.16/lib/Parse/Dia/SQL/Utils.pm Parse-Dia-SQL-0.17/lib/Parse/Dia/SQL/Utils.pm --- Parse-Dia-SQL-0.16/lib/Parse/Dia/SQL/Utils.pm 2010-02-02 08:30:36.000000000 +0100 +++ Parse-Dia-SQL-0.17/lib/Parse/Dia/SQL/Utils.pm 2010-10-14 14:47:50.000000000 +0200 @@ -546,7 +546,7 @@ $self->{log}->info(qq{Replaced $typeName with smallint}) if $self->{log}->is_info(); return 'smallint'; } - if ( lc($typeName) eq 'int8' ) { + if ( lc($typeName) eq 'bigserial' or lc($typeName) eq 'int8' ) { $self->{log}->info(qq{Replaced $typeName with bigint}) if $self->{log}->is_info(); return 'bigint'; } diff -ru Parse-Dia-SQL-0.16/lib/Parse/Dia/SQL.pm Parse-Dia-SQL-0.17/lib/Parse/Dia/SQL.pm --- Parse-Dia-SQL-0.16/lib/Parse/Dia/SQL.pm 2010-05-21 07:25:11.000000000 +0200 +++ Parse-Dia-SQL-0.17/lib/Parse/Dia/SQL.pm 2010-10-14 15:05:37.000000000 +0200 @@ -1370,13 +1370,16 @@ && $leftMult =~ /^z?many$/ && $rightMult =~ /^z?many$/ ) { + # while in uml-mode the names switch + my $leftEndRole = $self->{uml} ? $rightEnd{'role'} : $leftEnd{'role'}; + my $rightEndRole = $self->{uml} ? $leftEnd{'role'} : $rightEnd{'role'}; # If the classification above failed, and the association is # many-to-many; generate the centre (join) table, its constraints # and the classes' primary keys (if needed) $ok = $self->generate_many_to_many_association( - $assocName, $leftClass, $leftEnd{'role'}, - $rightClass, $rightEnd{'role'} + $assocName, $leftClass, $leftEndRole, + $rightClass, $rightEndRole ); } else {

Fri Oct 15 13:42:49 2010 aff [...] cpan.org - Correspondence added

Subject:

Parse-Dia-SQL ticket #62131

On Thu Oct 14 11:37:37 2010, Sadrak wrote: Hi Sadrak. Thank you for your interest in Parse-Dia-SQL. Show quoted text

> I have patched 4 things:

Great! Show quoted text

> - add the flag --uml to the parsediasql > - in UML-Mode the naming for n:m tables was wrong.

What do you mean by "uml mode" exactly? Can you explain please ? Show quoted text

> - first exec small packages, then do all inserts (wrong order)

I don't understand why that would be better, please explain. Show quoted text

> - type bigserial is also int8

Ok. Again, thank you for your time and effort. Best regards, Andreas --

Fri Oct 15 13:42:49 2010 The RT System itself - Status changed from 'new' to 'open'

Sat Oct 16 06:22:07 2010 felix.ostmann [...] thewar.de - Correspondence added

From:

felix.ostmann [...] thewar.de

Am Fr 15. Okt 2010, 13:42:49, AFF schrieb: Show quoted text

> On Thu Oct 14 11:37:37 2010, Sadrak wrote: > > Hi Sadrak. Thank you for your interest in Parse-Dia-SQL. >

> > I have patched 4 things:

> > Great! >

> > - add the flag --uml to the parsediasql > > - in UML-Mode the naming for n:m tables was wrong.

> > What do you mean by "uml mode" exactly? Can you explain please ?

look at http://tedia2sql.tigris.org/usingtedia2sql.html and search for "uml mode". Some sections are explain for ERD mode and for UML mode. I forgot to explain the --uml flag in parsediasql (--help dont say anything about that flag) Show quoted text

>

> > - first exec small packages, then do all inserts (wrong order)

> > I don't understand why that would be better, please explain.

I have to check that again, currently i cannot access the .dia :-/ without that change, our dia did not work, more at monday :) Perhaps a flag for ordering will help there more, i will check (and write a patch). Show quoted text

>

> > - type bigserial is also int8

> > Ok. > > Again, thank you for your time and effort. > > Best regards, > Andreas

Tue Oct 19 13:07:41 2010 felix.ostmann [...] thewar.de - Correspondence added

From:

felix.ostmann [...] thewar.de

Now the patch looks good. I removed the positionswap for smallpackages. Our Dia make some triggers and inserts and the insert have to executed after the triggers ... but it is better to hold the inserts in a seperate file and execute after the deploy of the schema. i added a short help for the --uml flag in parsediasql Am Sa 16. Okt 2010, 06:22:07, Sadrak schrieb: Show quoted text

> Am Fr 15. Okt 2010, 13:42:49, AFF schrieb:

> > On Thu Oct 14 11:37:37 2010, Sadrak wrote: > > > > Hi Sadrak. Thank you for your interest in Parse-Dia-SQL. > >

> > > I have patched 4 things:

> > > > Great! > >

> > > - add the flag --uml to the parsediasql > > > - in UML-Mode the naming for n:m tables was wrong.

> > > > What do you mean by "uml mode" exactly? Can you explain please ?

> > look at http://tedia2sql.tigris.org/usingtedia2sql.html and search for > "uml mode". Some sections are explain for ERD mode and for UML mode. > > I forgot to explain the --uml flag in parsediasql (--help dont say > anything about that flag) > >

> >

> > > - first exec small packages, then do all inserts (wrong order)

> > > > I don't understand why that would be better, please explain.

> > I have to check that again, currently i cannot access the .dia :-/ > without that change, our dia did not work, more at monday :) Perhaps a > flag for ordering will help there more, i will check (and write a patch). > >

> >

> > > - type bigserial is also int8

> > > > Ok. > > > > Again, thank you for your time and effort. > > > > Best regards, > > Andreas

>

Subject:

parse-dia-sql-0.17.patch

diff -ru Parse-Dia-SQL-0.16/bin/parsediasql Parse-Dia-SQL-0.17/bin/parsediasql --- Parse-Dia-SQL-0.16/bin/parsediasql 2010-01-18 21:39:17.000000000 +0100 +++ Parse-Dia-SQL-0.17/bin/parsediasql 2010-10-19 19:03:55.000000000 +0200 @@ -15,18 +15,20 @@ my $file = undef; my $ignore_type_mismatch = undef; my $db = undef; +my $uml = 0; GetOptions( "help|?" => \$help, "file=s" => \$file, "db=s" => \$db, + "uml" => \$uml, "ignore_type_mismatch" => \$ignore_type_mismatch, ) or pod2usage(2); pod2usage(1) if $help; pod2usage(qq{Missing argument 'file'}) if !$file; pod2usage(qq{Missing argument 'db'}) if !$db; -my $dia = Parse::Dia::SQL->new(file => $file, db => $db, ignore_type_mismatch => $ignore_type_mismatch); +my $dia = Parse::Dia::SQL->new(file => $file, db => $db, ignore_type_mismatch => $ignore_type_mismatch, uml => $uml); print $dia->get_sql(); __END__ @@ -45,6 +47,7 @@ file - filename of Dia file db - Database type (e.g. 'db2') + uml - Using uml mode (default is erd mode) ignore_type_mismatch - Allows foreign keys to have a different type than the primary key it references, if true. diff -ru Parse-Dia-SQL-0.16/lib/Parse/Dia/SQL/Utils.pm Parse-Dia-SQL-0.17/lib/Parse/Dia/SQL/Utils.pm --- Parse-Dia-SQL-0.16/lib/Parse/Dia/SQL/Utils.pm 2010-02-02 08:30:36.000000000 +0100 +++ Parse-Dia-SQL-0.17/lib/Parse/Dia/SQL/Utils.pm 2010-10-19 18:46:50.000000000 +0200 @@ -546,7 +546,7 @@ $self->{log}->info(qq{Replaced $typeName with smallint}) if $self->{log}->is_info(); return 'smallint'; } - if ( lc($typeName) eq 'int8' ) { + if ( lc($typeName) eq 'bigserial' or lc($typeName) eq 'int8' ) { $self->{log}->info(qq{Replaced $typeName with bigint}) if $self->{log}->is_info(); return 'bigint'; } diff -ru Parse-Dia-SQL-0.16/lib/Parse/Dia/SQL.pm Parse-Dia-SQL-0.17/lib/Parse/Dia/SQL.pm --- Parse-Dia-SQL-0.16/lib/Parse/Dia/SQL.pm 2010-05-21 07:25:11.000000000 +0200 +++ Parse-Dia-SQL-0.17/lib/Parse/Dia/SQL.pm 2010-10-19 18:46:50.000000000 +0200 @@ -1370,13 +1370,16 @@ && $leftMult =~ /^z?many$/ && $rightMult =~ /^z?many$/ ) { + # while in uml-mode the names switch + my $leftEndRole = $self->{uml} ? $rightEnd{'role'} : $leftEnd{'role'}; + my $rightEndRole = $self->{uml} ? $leftEnd{'role'} : $rightEnd{'role'}; # If the classification above failed, and the association is # many-to-many; generate the centre (join) table, its constraints # and the classes' primary keys (if needed) $ok = $self->generate_many_to_many_association( - $assocName, $leftClass, $leftEnd{'role'}, - $rightClass, $rightEnd{'role'} + $assocName, $leftClass, $leftEndRole, + $rightClass, $rightEndRole ); } else {

Wed Oct 20 16:29:50 2010 aff [...] cpan.org - Subject changed from (no value) to 'Add support for uml mode'

Wed Oct 20 16:29:51 2010 aff [...] cpan.org - Given to AFF

Wed Oct 20 16:55:28 2010 aff [...] cpan.org - Correspondence added

On Tue Oct 19 13:07:41 2010, Sadrak wrote: Show quoted text

> Now the patch looks good.

Thank you for your clarification. The changes you suggest appear reasonable. I would really like to see a .dia model that takes advantage of the "uml mode", as well as what output you would expect. Do you think you could provide that please? Best regards, Andreas --

Thu Oct 21 12:53:37 2010 felix.ostmann [...] thewar.de - Correspondence added

Subject:	Re: [rt.cpan.org #62131] Add support for uml mode
Date:	Thu, 21 Oct 2010 18:53:24 +0200
To:	bug-Parse-Dia-SQL [...] rt.cpan.org
From:	Felix Ostmann <felix.ostmann [...] thewar.de>

I added a small part of our dia as img to the bugreport, is this enough? Or should i make a greater .dia with more komponents? Am 20.10.2010 22:55, schrieb Andreas Faafeng via RT: Show quoted text

> <URL: https://rt.cpan.org/Ticket/Display.html?id=62131> > > On Tue Oct 19 13:07:41 2010, Sadrak wrote: >

>> Now the patch looks good. >>

> Thank you for your clarification. The changes you suggest appear > reasonable. I would really like to see a .dia model that takes > advantage of the "uml mode", as well as what output you would expect. > Do you think you could provide that please? > > Best regards, > Andreas >

Fri Oct 22 01:18:44 2010 aff [...] cpan.org - Correspondence added

On Thu Oct 21 12:53:37 2010, Sadrak wrote: Show quoted text

> I added a small part of our dia as img to the bugreport, is this enough? > Or should i make a greater .dia with more komponents?

If you could supply a .dia file that represents the image you already have submitted, that would be ok. Optimally I would like 2 files: One .dia file, and one test (.t) file that checks its content. E.g. like this http://cpansearch.perl.org/src/AFF/Parse-Dia-SQL-0.16_01/t/950-rt51433.t Regards, Andreas --

Fri Oct 22 13:07:50 2010 felix.ostmann [...] thewar.de - Correspondence added

Subject:	Re: [rt.cpan.org #62131] Add support for uml mode
Date:	Fri, 22 Oct 2010 19:07:36 +0200
To:	bug-Parse-Dia-SQL [...] rt.cpan.org
From:	Felix Ostmann <felix.ostmann [...] thewar.de>

ah, i understand! OK, hopefully i can write this at this weekend! Have a nice day! Am 22.10.2010 07:18, schrieb Andreas Faafeng via RT: Show quoted text

> <URL: https://rt.cpan.org/Ticket/Display.html?id=62131> > > On Thu Oct 21 12:53:37 2010, Sadrak wrote: >

>> I added a small part of our dia as img to the bugreport, is this enough? >> Or should i make a greater .dia with more komponents? >>

> If you could supply a .dia file that represents the image you already > have submitted, that would be ok. Optimally I would like 2 files: One > .dia file, and one test (.t) file that checks its content. E.g. like this > > http://cpansearch.perl.org/src/AFF/Parse-Dia-SQL-0.16_01/t/950-rt51433.t > > Regards, > Andreas >

Mon Nov 15 03:58:19 2010 aff [...] cpan.org - Status changed from 'open' to 'stalled'

Wed Jan 26 11:20:30 2011 felix.ostmann [...] thewar.de - Correspondence added

From:

felix.ostmann [...] thewar.de

I have found another problem! I think the generated relations are always wrong! I tested with your test: 620-output-get-schema-create-many-to-many.t And simple print out the print $subclass->get_associations_create(); That locks wrong! It produce the following: alter table student_course add constraint stdn_crs_fk_StntSn foreign key (course_id) references student (ssn) on delete cascade; alter table student_course add constraint lTeT8iBKfXObJYiSrq foreign key (ssn) references course (course_id) on delete cascade; So the ssn is referenced to the course_id and the course_id to the ssn! It should be: alter table student_course add constraint stdn_crs_fk_StntSn foreign key (ssn) references student (ssn) on delete cascade; alter table student_course add constraint lTeT8iBKfXObJYiSrq foreign key (course_id) references course (course_id) on delete cascade; And the constraint names are bad! Perhaps i patched the wrong place: the generate_many_to_many_association use the wrong roles, but i am not sure if the arguments are wrong or internal the call to save_foreign_key use the wrong role. So the patch modify following: - the test for many-to-many - swaps $leftEnd{'role'} and $rightEnd{'role'} in generate_many_to_many_association - add -uml to the parsedia executable - bigserial is equal to int8

Subject:

parsediasql.patch

diff -ru Parse-Dia-SQL-0.16/bin/parsediasql Parse-Dia-SQL-0.17/bin/parsediasql --- Parse-Dia-SQL-0.16/bin/parsediasql 2010-01-18 21:39:17.000000000 +0100 +++ Parse-Dia-SQL-0.17/bin/parsediasql 2011-01-26 16:42:52.000000000 +0100 @@ -15,18 +15,20 @@ my $file = undef; my $ignore_type_mismatch = undef; my $db = undef; +my $uml = 0; GetOptions( "help|?" => \$help, "file=s" => \$file, "db=s" => \$db, + "uml" => \$uml, "ignore_type_mismatch" => \$ignore_type_mismatch, ) or pod2usage(2); pod2usage(1) if $help; pod2usage(qq{Missing argument 'file'}) if !$file; pod2usage(qq{Missing argument 'db'}) if !$db; -my $dia = Parse::Dia::SQL->new(file => $file, db => $db, ignore_type_mismatch => $ignore_type_mismatch); +my $dia = Parse::Dia::SQL->new(file => $file, db => $db, ignore_type_mismatch => $ignore_type_mismatch, uml => $uml); print $dia->get_sql(); __END__ @@ -45,6 +47,7 @@ file - filename of Dia file db - Database type (e.g. 'db2') + uml - Using uml mode (default is erd mode) ignore_type_mismatch - Allows foreign keys to have a different type than the primary key it references, if true. diff -ru Parse-Dia-SQL-0.16/lib/Parse/Dia/SQL/Utils.pm Parse-Dia-SQL-0.17/lib/Parse/Dia/SQL/Utils.pm --- Parse-Dia-SQL-0.16/lib/Parse/Dia/SQL/Utils.pm 2010-02-02 08:30:36.000000000 +0100 +++ Parse-Dia-SQL-0.17/lib/Parse/Dia/SQL/Utils.pm 2011-01-26 16:42:52.000000000 +0100 @@ -546,7 +546,7 @@ $self->{log}->info(qq{Replaced $typeName with smallint}) if $self->{log}->is_info(); return 'smallint'; } - if ( lc($typeName) eq 'int8' ) { + if ( lc($typeName) eq 'bigserial' or lc($typeName) eq 'int8' ) { $self->{log}->info(qq{Replaced $typeName with bigint}) if $self->{log}->is_info(); return 'bigint'; } diff -ru Parse-Dia-SQL-0.16/lib/Parse/Dia/SQL.pm Parse-Dia-SQL-0.17/lib/Parse/Dia/SQL.pm --- Parse-Dia-SQL-0.16/lib/Parse/Dia/SQL.pm 2010-05-21 07:25:11.000000000 +0200 +++ Parse-Dia-SQL-0.17/lib/Parse/Dia/SQL.pm 2011-01-26 17:10:57.000000000 +0100 @@ -1370,13 +1370,13 @@ && $leftMult =~ /^z?many$/ && $rightMult =~ /^z?many$/ ) { - # If the classification above failed, and the association is # many-to-many; generate the centre (join) table, its constraints # and the classes' primary keys (if needed) $ok = $self->generate_many_to_many_association( - $assocName, $leftClass, $leftEnd{'role'}, - $rightClass, $rightEnd{'role'} + $assocName, + $leftClass, $rightEnd{'role'}, + $rightClass, $leftEnd{'role'}, ); } else { diff -ru Parse-Dia-SQL-0.16/t/620-output-get-schema-create-many-to-many.t Parse-Dia-SQL-0.17/t/620-output-get-schema-create-many-to-many.t --- Parse-Dia-SQL-0.16/t/620-output-get-schema-create-many-to-many.t 2009-11-13 09:28:23.000000000 +0100 +++ Parse-Dia-SQL-0.17/t/620-output-get-schema-create-many-to-many.t 2011-01-26 17:11:01.000000000 +0100 @@ -9,7 +9,7 @@ use File::Spec::Functions; use lib catdir qw ( blib lib ); -plan tests => 14; +plan tests => 16; use lib q{lib}; use_ok ('Parse::Dia::SQL'); @@ -17,7 +17,7 @@ use_ok ('Parse::Dia::SQL::Output::DB2'); # 1. parse input -my $diasql = Parse::Dia::SQL->new( file => catfile(qw(t data many_to_many.dia)), db => 'db2' ); +my $diasql = Parse::Dia::SQL->new( file => catfile(qw(t data many_to_many.dia)), db => 'db2', uml => 1 ); isa_ok($diasql, q{Parse::Dia::SQL}, q{Expect a Parse::Dia::SQL object}); is($diasql->convert(), 1, q{Expect convert to return 1}); @@ -53,6 +53,18 @@ create \s+ table \s+ student_course \s* .*|six, q{Check syntax for sql create table student_course}); -# Note: Associations are tested in 650-output-get-create-associations.t +# 4. associations +my $assoc = $subclass->get_associations_create(); + +like($assoc, qr|.* +alter \s+ table \s+ student_course \s+ add \s+ constraint \s+ \w+ \s+ +foreign \s+ key \s+ $ssn$ \s+ +references \s+ student \s+ $ssn$ \s+ on \s+ delete \s+ cascade; \s* +.*|six, q{Check syntax for sql alter table add constraint rel1}); +like($assoc, qr|.* +alter \s+ table \s+ student_course \s+ add \s+ constraint \s+ \w+ \s+ +foreign \s+ key \s+ $course_id$ \s+ +references \s+ course \s+ $course_id$ \s+ on \s+ delete \s+ cascade; \s* +.*|six, q{Check syntax for sql alter table add constraint rel2}); __END__

Wed Jan 26 11:20:31 2011 The RT System itself - Status changed from 'stalled' to 'open'

Thu Jan 27 04:38:53 2011 aff [...] cpan.org - Correspondence added

On Wed Jan 26 11:20:30 2011, Sadrak wrote: Show quoted text

> I have found another problem! I think the generated relations are always > wrong!

Thanks Felix, I will have a look at it. -- Andreas

Tue Feb 15 11:18:27 2011 aff [...] cpan.org - Correspondence added

Felix Please check if 0.16_02 solves the issues. Regards, Andreas

Tue Feb 15 11:18:28 2011 aff [...] cpan.org - Status changed from 'open' to 'resolved'

Wed Feb 16 03:14:00 2011 aff [...] cpan.org - Fixed in 0.16_02 added

Thu Feb 17 02:03:33 2011 aff [...] cpan.org - Fixed in 0.17 added