Hello

I fixed bug #240459 in dia2sql.

Problem was that dia2sql crashes when there was no Protected attribute
in diagram. Protected attribute is considered as primary key.

So I added small test if value is defined, and added small warning with
explanation how to create a primary key in Dia diagram.

Fixes script is attached.


Have a nice day

Georgik
-- 
-=- -=- -=- -=- -=- -=- -=- -=- -=- -=- -=- -=- -=- -=-
 Bc. Juraj Michalek - http://georgik.blucina.net
       Every application is a game. 
 The question is: How much you can enjoy it.
   Games for Linux - http://games.linux.sk
#!/usr/bin/perl -w
#
# dia2sql.pl - version 1.2 - 2001/03/20
# Entity-relationship with UML diagrams from Dia
#
# Copyright (c) 2001 by Alexander Troppmann
# http://www.cocktaildreams.de - [EMAIL PROTECTED]
#
# This program releases under the GNU Public License;
# You can redistribute it or modify it under the terms of GPL.
#
# Featurelist:
# - Creates a table for each UML class
# - All attributes of a Dia UML class will be converted to SQL statements:
#   "Name" = SQL column name,
#   "Type" = SQL datatype and also additional attributes,
#   "Visibility" = if set to "protected" the attribute will be a primary key
#   "Value" = if set a default value will be defined for this column
# - DROP TABLE statement can be created
# - For each column starting with "FK_" a foreign key constraint will be
#   created (PostgreSQL only). Do not use "REFERENCES" attributes in the UML
#   diagram!
# - Support for indexed columns (PostgreSQL only)
#
# Required stuff:
# - First get the expat library (> v1.95.0) as RPM package or from
#   http://www.jclark.com/xml/expat.html which is a XML1.0 parser written in C.
# - Second install XML::Parser from CPAN, just type in at your shell prompt:
#
#      [EMAIL PROTECTED]:~ > perl -MCPAN -e 'install XML::Parser'
#
# That's all! :-)

use strict;

use XML::Parser;

eval "use Compress::Zlib;";
my $use_zlib = ($@) ? 0 : 1;

use vars qw($VERSION $DEBUG $CREATECOMMENTS $MYSQL $POSTGRESQL $DROPTABLE 
$CREATEDROPTABLES);
use vars qw($infile $outfile);
use vars qw($buffer $ctag $prefix $text $table $tablename $column $columnname
            $columnvalue $columnvi $commentprefix $columntype);
use vars qw(%attr @myTables $myTablename $myColumnname $myColumnvalue 
$myColumntype
            $myIndexMarker %myIndexMarker %myIndexColumns $myKeyMarker 
%myKeyMarker %myPrimaryKeys
            %myTableContents $myTableUsesIndizes $myTableUsesForeignKeys);
use vars qw(@myTablesIncludingFKs);
$VERSION = 'dia2sql.pl v1.2';

###########################################################
## CONFIG - MAKE ANY CHANGES HERE

$DEBUG = 0;                     # 1 if you want to get debug messages to STDERR
$CREATECOMMENTS = 0;            # 1 if you want to get comments like mysqldump 
does

$MYSQL = 1;                                     # choose either MySQL or 
PostgreSQL support
$POSTGRESQL = 0;                        # by setting 0 and 1 values here

$CREATEDROPTABLES = 0;          # 1 if you want to have "DROP TABLE" statements

if($MYSQL) {
        $DROPTABLE = "DROP TABLE IF EXISTS";    # DROP TABLE statement to be 
used
} elsif($POSTGRESQL) {
        $DROPTABLE = "DROP TABLE";
} else {
        die "ERROR: You didn't choose a database!\n";
}


###########################################################
## VARIABLES

$table = 0;                             # 1 if inside table
$tablename = 0;                 # 1 if tablename tag detected
$column = 0;                    # 1 if inside column
$columnname = 0;                # 1 if columnname detected
$columntype = 0;                # 1 if datatype for column detected
$columnvi = 0;                  # 1 if "visibility" for primary key definitions
$columnvalue = 0;               # 1 if value attribute detected
$commentprefix = ($MYSQL) ? '# ' : '-- ';

undef($myTablename);                    # current table
undef(%myTableContents);                # hash with columns for a certain table
undef(%myKeyMarker);                    # marker for primary keys
undef(%myIndexMarker);                  # marker for indexed column
undef(%myPrimaryKeys);                  # hash with primary keys for a certain 
table
undef(%myIndexColumns);                 # columns for index
undef($myColumnvalue);                  # default value for datatype
undef($myTableUsesForeignKeys); # is my current table using foreign keys?
undef($myTableUsesIndizes);             # is my current table using indexed 
columns?
undef(@myTablesIncludingFKs);   # build these tables later
undef(@myTables);                               # build tables first (cause 
they're referenced)


###########################################################
## INIT

## get infile (and outfile) from command line
#
if(@ARGV < 1) {

        die <<"_USAGE_END_"
Usage: dia2sql.pl file.dia [file.sql]

$VERSION - (c) 2001 by Alexander Troppmann

Converts xml data input from Dia to sql statements. If file.sql is not
specified the sql statements will be printed to STDOUT.

Edit dia2sql.pl and change the configuration at top of the Perl script.
Make sure you have defined the right database (MySQL or PostgreSQL) for
SQL output.

_USAGE_END_

} else {

        $infile = shift;                # input file, Dia XML formatted
        $outfile = shift;               # output file, filled with SQL 
statements

        if($outfile) {
                open(STDOUT, ">$outfile") or die "$outfile: $!n";
        }

}

if($MYSQL) {
        if($DEBUG) { print STDERR "Creating SQL output for MySQL\n"; }
} elsif($POSTGRESQL) {
        if($DEBUG) { print STDERR "Creating SQL output for PostgreSQL\n"; }
}

## init xml parser and parse input file
#
my $parser = new XML::Parser(Style => 'Stream');

## test if we are using a compressed dia file
#
if ($use_zlib) {
  my ($zlib_stream, $zlib_status) = deflateInit();

  my $gz = gzopen ($infile, "rb");
  die "Failed to open '$infile'\n" unless ($gz);

  my $xml_content = '';
  while ($gz->gzread ($buffer) > 0) {
    $xml_content .= $buffer;
  }
  chomp $xml_content;

  die "'$infile' is not an XML file.\n"
    if ($xml_content !~ /^[<][?]xml/);

  $parser->parse ($xml_content);
} else {
  $parser->parsefile ($infile);
}

## create sql output
#
&createSql();


## cleanup and exit
#
if($outfile) {
        close(STDOUT);
}

exit;


###########################################################
## SUB ROUTINES

## called if parser enters new tag
#
$prefix = '';                           # patch for Dia v0.83 by Georges 
Khaznadar

sub StartTag {
        my $p = shift;                  # parser context
        $ctag = shift;                  # name of this tag element
        %attr = %_;                             # hash with attributes

        if($ctag eq 'dia:diagram') {
            # then all other tags will be prefixed with dia:
                # (patch for Dia v0.83 by Georges Khaznadar)
            $prefix = 'dia:';
        }

        $prefix = '' unless (defined $prefix);
        $ctag = '' unless (defined $ctag);
        $attr{'type'} = '' unless (defined $attr{'type'});

        if($ctag eq $prefix.'object' and $attr{'type'} eq 'UML - Class') {
                $table = 1;
                $myTableUsesForeignKeys = 0;
                $myTableUsesIndizes = 0;
        } elsif($ctag eq $prefix.'composite' and $attr{'type'} eq 
'umlattribute' and $table) {
                $column = 1;
        } elsif($table and $ctag eq $prefix.'attribute' and $attr{'name'} eq 
'name' and !$column) {
                $tablename = 1;
        } elsif($column and $ctag eq $prefix.'attribute' and $attr{'name'} eq 
'name' and !$tablename) {
                $columnname = 1;
        } elsif($column and $ctag eq $prefix.'attribute' and $attr{'name'} eq 
'value' and $column) {
                $columnvalue = 1;
        } elsif($column and $ctag eq $prefix.'attribute' and $attr{'name'} eq 
'type') {
                $columntype = 1;
        } elsif($column and $ctag eq $prefix.'attribute' and $attr{'name'} eq 
'visibility') {
                $columnvi = 1;
        } elsif($columnvi and $ctag eq $prefix.'enum') {
                if($attr{'val'} == 2) { $myKeyMarker = 1; }             # 
primary key found
                else { $myKeyMarker = 0; }
                if($attr{'val'} == 1) { $myIndexMarker = 1; }           # index 
column found
                else { $myIndexMarker = 0; }
        }

}

## called if parser leaves a tag
#
sub EndTag {

        my $p = shift;
        $ctag = shift;

        $prefix = '' unless (defined $prefix);
        $ctag = '' unless (defined $ctag);
        $table = '' unless (defined $table);

        if($ctag eq $prefix.'object' and $table) {

                $table = 0;
                $myTableUsesIndizes = 0;

                if($myTableUsesForeignKeys) {
                        push @myTablesIncludingFKs, $myTablename;
                        $myTableUsesForeignKeys = 0;
                        if($DEBUG) { print STDERR "Table '$myTablename' has 
foreign keys\n"; }
                } else {
                        push @myTables, $myTablename;
                        if($DEBUG) { print STDERR "Table '$myTablename' (may be 
referenced by other tables)\n"; }
                }

        } elsif($ctag eq $prefix.'composite' and $column) {

                $column = 0;

                $myColumnname =~ m/(([A-Za-z0-9_-]+)
                                    (\s*=\s*([A-Za-z0-9_-]+)
                                    (\.([A-Za-z0-9_-]+))?)?)/x;

                my ($col_name, $foreign_table, $foreign_field) = ($2, $4, $6);

                my $sql = "$col_name $myColumntype";

                if($myKeyMarker) {
                        push @{$myPrimaryKeys{$myTablename}}, "$col_name";
                        if($MYSQL) {
                                $sql .= " NOT NULL";
                        }

                }

                if($myIndexMarker and $POSTGRESQL) {
                        push @{$myIndexColumns{$myTablename}}, "$col_name";
                        $myTableUsesIndizes = 1;
                        if($DEBUG) { print STDERR "Indexed column found!\n"; }
                }

                if($myColumnvalue) {
                        $sql .= (uc($myColumnvalue) eq 'NULL')
                          ? " DEFAULT NULL"
                            : " DEFAULT '$myColumnvalue'";
                }

                if ($foreign_table) {
                  $sql .= " REFERENCES $foreign_table";
                  $myTableUsesForeignKeys = 1;
                  if($DEBUG)
                    { print STDERR "Foreign key found!\n"; }
                  $sql .= " ($foreign_field)"
                    if ($foreign_field);
                }

                $myColumnvalue = '';

                push @{$myTableContents{$myTablename}}, $sql;
                if($DEBUG) { print STDERR "Added new column data \"$sql\"\n"; }

        } elsif($ctag eq $prefix.'attribute' and $tablename) {
                $tablename = 0;
        } elsif($ctag eq $prefix.'attribute' and $column and $columnname) {
                $columnname = 0;
        } elsif($ctag eq $prefix.'attribute' and $column and $columnvalue) {
                $columnvalue = 0;
        } elsif($ctag eq $prefix.'attribute' and $column and $columntype) {
                $columntype = 0;
        } elsif($ctag eq $prefix.'enum' and $column and $columnvi) {
                $columnvi = 0;
        }

}


## called for text between any tags
#
sub Text {

        $text = $_;

        if($text =~ /^\s+$/) { return; }                # skip whitespaces

        if($ctag eq $prefix.'string' and $tablename) {
                $text =~ s/(^#|#$)//g;                          # remove hash 
characters
                $myTablename = $text;
                if($DEBUG) { print STDERR "\nTable: $myTablename\n"; }
        } elsif($ctag eq $prefix.'string' and $columnname) {
                $text =~ s/(^#|#$)//g;              # remove hash characters
                $myColumnname = $text;
                if($DEBUG) { print STDERR "Columnname: $myColumnname\n"; }
        } elsif($ctag eq $prefix.'string' and $columnvalue) {
                $text =~ s/(^#|#$)//g;
                $text =~ s/('|")/\\$1/sg;
                $myColumnvalue = $text;
                if($DEBUG) { print STDERR "Columnvalue: $myColumnvalue\n"; }
        } elsif($ctag eq $prefix.'string' and $columntype) {
                $text =~ s/(^#|#$)//g;              # remove hash characters
                $myColumntype = $text;
                if($DEBUG) { print STDERR "Columntype: $myColumntype\n"; }
        }

}


## create sql output
#
sub createSql {

        my($columns,$keys,$sql,$date);

        if($DEBUG) { print STDERR "\nWriting SQL statements...\n"; }

        if($CREATECOMMENTS) {
                $date = `date`; chop($date);
                print $commentprefix."Created by $VERSION (".$date.")\n\n";
        }

        if($DEBUG) { print STDERR "\nFirst build tables referenced by other 
tables...\n"; }
        foreach(@myTables) {
                &buildTable($_);
        }

        if($DEBUG) { print STDERR "\nBuild tables including foreign keys...\n"; 
}
        foreach(@myTablesIncludingFKs) {
                &buildTable($_);
        }

        if($DEBUG) { print STDERR "Done!\n\n"; }

}


## build sql table
#
sub buildTable($) {

        my $tablename = shift;

        if($DEBUG) { print STDERR "Working on '$tablename':\n"; }

        if($CREATECOMMENTS) {
                print $commentprefix."\n".$commentprefix.
                  "Table structure for table '$tablename'\n".
                    $commentprefix."\n\n";
        }

        if($CREATEDROPTABLES) {
                if($DEBUG) { print STDERR "-> Creating DROP TABLE statement\n"; 
}
                print "$DROPTABLE $tablename;\n";
        }

        if($DEBUG) { print STDERR "-> Collect table columns\n"; }
        my $columns = join(",\n\t",@{$myTableContents{$tablename}});
        my $index = $myPrimaryKeys{$tablename};
        my $keys;
        if (defined($index)) {
                $keys = "PRIMARY KEY(".join(",",@{$index}).")";
        }

        if($DEBUG) { print STDERR "-> Create CREATE TABLE statement\n"; }
        my $sql = "CREATE TABLE $tablename (\n\t".$columns;

        if(defined(@{$myPrimaryKeys{$tablename}})) {
                if($DEBUG) { print STDERR "-> Adding PRIMARY KEY 
definitions\n"; }
                $sql .= ",\n\t".$keys."\n);\n\n";
        } else {
                $sql .= "\n);\n\n";
                print STDERR "-> Warning: Primary key not defined! Set primary 
atribute".
                        "as Protected in dia model\n\n";
        }

        print $sql;

        $sql = '';
        if(defined $myIndexColumns{$tablename}
           && @{$myIndexColumns{$tablename}}) {                 # add indexed 
columns
                foreach $column (@{$myIndexColumns{$tablename}}) {
                        if($DEBUG) { print STDERR "-> Adding INDEX for 
$tablename ($column)\n"; }
                        $sql .= "CREATE INDEX ".$column."_idx ON $tablename 
($column);\n";
                }
        }

        print "$sql\n";
}

Reply via email to