Ressources documentaires pour Mandriva Linux et les Logiciels Libres

Billets libellés PostgreSQL

Using Parse-Dia-SQL to generate SQL from UML Dia diagram and how to install it under Mageia

dia2sql_1It can be tedious when designing an application ( and not using frameworks like Symfony ) to design your database and then create corresponding SQL scripts. Thanks to Parse-Dia-SQL you can design your database using Dia and then generate corresponding SQL script for your database.

Ubuntu users just need to install libparse-dia-sql-perl package.

Windows may download Parse-Dia-SQL installer directly from Dia homepage :

Now let’s see how to install Parse-Dia-SQL under Mageia :

  • Install minimal set of dependencies. To found corresponding Mageia packages, use urpmf –provides –literal. For example to found Html::Lint, just do : urpmf --provides --literal 'perl(Html::Lint)'
  • Install corresponding minimal set of dependencies for : urpmi perl-base perl-Log-Log4perl perl-XML-DOM perl-HTML-Lint perl-Text-Table perl-Text-Aligner perl-Test-Exception
  • Install CPAN : urpmi perl-devel make
  • Now install Parse-Dia-SQL from CPAN : cpan /Parse-Dia-SQL/
  • parsediasql should be installed now in /usr/local/bin/
  • Now to create a SQL file from your Dia diagram, just do : parsediasql --file db_model_file.dia --db dbtype
    • Supported values for dbtype are : mysql-innodb, mysql-myisam, postgres ( for PostgreSQL ), db2, oracle, sqlite3, sqlite3fk, mssql and html

Now to create your UML model with Dia, use Dia’s UML shapes :

  • SQL table are UML Class
  • To mark an attribute as being a primary key, set Visibility = protected
  • To add NOT NULL and DEFAULT xxx to your attributes, first add the default value and then the NULL or NOT NULL  in Value field. For example to have DEFAULT CURRENT_TIMESTAMP NOT NULL on your SQL file, just add CURRENT_TIMESTAMP NOT NULL in Value field
  • Additional indexes can be create in [Operations] tab where :
    • Operation Name = Index name ( e.g table_col_idx )
    • Operation Type = Index type ( INDEX or UNIQUE INDEX )
    • Parameters =  Index columns ( one parameter name for each column name in the index )
    • Stereotype: Index type — For PostgreSQL, you can choose other than btree ( hash, gist, gin ). Please note that GIN indexes may be useful for JSON datatype
  • Foreign-keys constraint are done with UML aggregation tool ( line with a diamond at one end ) in which in its properties you are going to specify foreign-key direction and columns

Please note that for PostgreSQL no need to use typemaps for autoincrement columns, just put SERIAL as attribute type 🙂


Automatic SQL generation using Dia

Database design with Dia

Utilisation de Dia2SQL ( Fr )

Using Dia to Interact With tedia2sql ( Parse-Dia-SQL is a fork of tedia2sql to support latest Dia version, documentation for tedia2sql is relevant for Parse-Dia-SQL too )

Parse-Dia-SQL homepage



My Tweets