With the scripts provided in this package you do not have to write SQL statements for schema creation for yourself. Simply use Dia to draw your SQL tables (as UML classes) and convert them to SQL with a single click. The generated SQL file will contain SQL statements that create tables along with indexes and partially defined views (views are available only for MS SQL). Currently only MySQL and MS SQL are supported.
Basically the only thing you need to worry about is to save your Dia file without compression (you can tick this option in the Save As window).
Important note! Please note that the output SQL will drop any existing tables that it creates! When a table is dropped you have to expect that any data it contains will be wiped out. Remember to make a backup of your database before you use the output SQL to create new tables.
Copyright © 2007 by Maciej Jaros ("Nux")
License: GFDL or CC-BY-SA (whichever you like best)
Download for this version: dia2sql.1.2.0.zip.
You can use scripts that will process your Dia file and output a ready-to-use SQL file, but you can also use almost any modern browser to do basically the same job. Using a script means that you will be able to generate SQL with a single click (command), but you will also need a command line XSLT processor and a batch file. Windows users will find both of them in the package, but there are also many processors that will work on Linux and other that you can download from Internet.
Additionally you can also use your average browser for the same job. Unfortunately this won't work automaticaly, so this will take a little longer, plus the processing itself is usually slower.
This package contains ready-to-use scripts that were tested on Windows XP. I'm using Microsoft's free XSL processor so it should work on any Windows NT (which includes Windows 2000). And again - you just need to remember to save Dia files without compression. If you're not sure how it works just run "_msxsl.bat" (it will use the example Dia file to generate SQL files for each supported database engines).
If you are using some other operating system or some older version of Windows you may use some other XSLT processor - see Bob Stayton's list of XSLT processor and if you are using Linux you may try Minoru XSL Processor. If you don't want to install additional applications you may also use a browser (e.g. Firefox) to generate the SQL.
OK. So here's how it works. You can simply use the provided _msxsl.bat or create your own batch file. The syntax is quite simple - MSXSL takes DIA file (XML) as the first parameter, XSL as the second one and finally the output file (SQL) is given after the "-o" switch. You could even redirect the output directly to SQL's command line utility, but you should check the SQL file at least on the first run. If you use some other XSLT processor you will have to at least modify the batch file for yourself.
Using a browser for transforming Dia to SQL is not so easy, but you will probably be able to use your favourite browser for this job. You can even try with IE ;).
Firstly you will need to add a line that will tell the browser which XSL file to use. This means you need to edit the Dia file and after the first line add something like:
<?xml-stylesheet type="text/xsl" href="xml2sql/dia2mysql.xsl"?>
Then you will need to rename the dia file adding the ".xml" extension. After that open the XML file in your browser and save the output.
Saving output may not be as easy as it seems to be. If you just save the page your browser will most probably just save the original XML file (not the output processed with XSL). Copying and pasting won't do any better. In order to properly save the output with Firefox you should do something like:
Dia uses XML to save the diagram you create in it. If you use the standard UML class elements, the XSL file should work for you without any problems or extra knowledge. In addition to very intuitive behaviour of the conversion there are some special functionalities I needed and one may not expect - check below to see what I mean.
This is simple. You will get an SQL table for every class created with Dia. The table will have columns named as you named the attributes. If you use classes that are marked as "abstract", they will not be included in the generated SQL statements unless you use special MS SQL views XSL. I believe this is desired behaviour and I hope this won't surprise anybody. Note that the views won't be fully defined - you will still need to write the rest of the SQL in the SELECT statement.
And now something you might not expect. First of all I've implemented a few type conversions.
| Dia type | MySQL | MS SQL | Notes |
|---|---|---|---|
| string, str | varchar(30) | varchar(30) | why 30? - why not :) |
| string(m), str(m) | varchar(m) | varchar(m) | m – should be an integer |
| integer(m), int(m) | - | int | MS SQL doesn't allow such constraints for int |
| binary, bin, image, blob | blob | image | why MS SQL calls this an image? I have no idea. |
| enum | tinyint | tinyint | If you want to use enum for MySQL, feel free to adjust the XSL file to your needs |
| bool, boolean | tinyint(1) | bit |
This is what I usually use and expect. Any other types should be SQL-ready as they get inserted as-is. If you want any other extra conversions, contact me or change the XSL file yourself (you will find the code for this in 'parse-attributes-type' template).
If you want you can use full column SQL type specification in the Dia's type field - it will get inserted as-is. I'm sure in most cases you won't want this as it will make the diagram less readable. This is why I've implemented some tags that you may put in the comment field. Note that, if you want, you may also make the comments visible in your Dia diagram by changing an appropriate option of your class. This is very useful if you are planning to print your diagrams for docs.
| tag (in Dia comment) | MySQL | MS SQL | Notes |
|---|---|---|---|
| [pk] | PRIMARY KEY | PRIMARY KEY | added below columns specification, but for the appropriate (current) column; currently if you want to use more than one column for some key you will need to edit the output SQL file for yourself |
| [key] | INDEX | - | similar to the above |
| [key(n)] | INDEX (partial) | - | similar to the above, but this defines a partial index (n is a number of characters used for indexing) |
| [REF tbl (col)] | - | REFERENCE | adds a reference (foreign key) from the current column and table to the given table (tbl) and column (col) |
| [unsigned], [u] | UNSIGNED | UNSIGNED | |
| [nn] | NOT NULL | NOT NULL | I've made this default for keys so you do not need use this along with [pk] nor [key] tag |
| [uniq] | UNIQUE | UNIQUE | |
| [a], [auto] | AUTO_INCREMENT | IDENTITY | |
| [[anything you want]] | as-is | as-is | here you can add everything you want - it inserts everything between '[[' and ']]' as-is into the column definition; note that this is similar to adding the same in the Dia's type field, but putting extra constraints here will make your diagram a little more readable |
Remember you need to add the above to the comment field, not to the type field. If you have ideas for any other "tags" please contact me. If you think they will be too specific for others to use, then simply change the XSL yourself (you will find the code for this in 'parse-attributes-type-extra' template and 'parse-ref' for MS SQL keys).
I haven't found any problems with running the output SQL, but please be aware that you should still know at least basics of MySQL/MSSQL syntax to work with those files. Why? I simply cannot guarantee that the output SQL will always work as you expect with any diagrams. And so you should be able to figure it out yourself whether something is wrong or not!
Important note! You should expect that all new tables will overwrite any existing ones. Remember to make a backup of your database before you use the output SQL code to create new tables.
Having said all that – if you find some weird behaviour or have any ideas for enhancements - please contact me.
There are some characters you shouldn't use in comments and other fields. The most important thing is that you shouldn't use '#' in any fields. In comments you should also be careful with '[' and ']' and on some rare occasions '(' and ')' could be misinterpreted. Please report any problems with using '[', ']', '(' and ')' - I will try to fix them.
And now for some explanations... The main problem with parsing special characters is that I haven't found a decent way to work with strings in XSL and so I've made some workarounds. Dia uses '#' characters in its output file around any strings used in a diagram. I have no idea why this is done as there doesn't seem to be any logic in that, but since they are used I have to strip them from all strings. Doing this in XSL is not easy so if '#' will be used in any of parsed strings anything after the first '#' will be disregarded.
Other characters I've mentioned shouldn't really be a problem. I can't really think of any real situation in which it would be problematic as they are used along with the mentioned tags. Still, theoretically, because of parsing (n) thingies some outputs might not look like as expected.
I consider any of these a bug, so please report them to me. Unfortunately I don't think I will be able to do anything with the '#' problem, but any others should be fixable in one way or the other.