No ERD to SQL code generation in Visio 2003/2007 Pro
I just spent a couple of days designing a complex database ERD in Visio 2003 Professional thinking that once I was done I could just click a button and have it generate all my SQL CREATE table statements and all referential rules, etc. I thought wrong.
The ERD to SQL Forward code generation feature is nowhere to be found in Visio 2003 Pro, nor is it in 2007 Pro. This seems incredibly bizzare as it's the most useful feature you could have in an ERD designer. What's the point of spending time and effort to create a complex diagram and then having to hand-write all the SQL yourself even though all the information is already modeled?
Doing a Google search looking for solutions suggests the feature is only available in Visio Enterprise Architect 2003 and Visio Architect 2005. Ok, fair enough, but, (there's always a but) you can only install VEA2003 if you already own and have installed Visual Studio 2003 Professional, likewise you need VS2005 to install VEA2005. Ok, so Microsoft wants me to spend some good cash to get this feature...can I try out a demo first? No! VEA2003 and VEA2005 are only offered through an MSDN Premium subscription.
Now there's no way I'm going to spend that kind of money for a feature that's commonly available in most free open-source DB architect products (Power Architect, Open Source Architect, MySQL Workbench). The sad thing is Microsoft is making it incredibly difficult for people to even try out this feature in their discontinued old software and have no new products to carry the functionality over. Maybe they've never had a need for proper design toolage in their own products?
Regardless, I'm now in a situation where I have an almost complete but useless Visio ERD diagram. The only solution I've found is to install a third-party plugin to convert the diagram into XML, and then use another third-party XSLT to transform the XML into SQL statements. Definitely not something I'd like to adopt as my design/maintenance strategy (I'm bound to hit unexpected bugs all over the place along the way).
I may instead try to use the above solution to generate my SQL script and then import that into another tool (either MySQL Workbench or Power Architect) and continue using that going forward. I expect this'll cost me a few good hours of needless rework.
UPDATE: I've gone with the third party Orthogonal Toolbox Visio plugin mentioned in the solution above. The plugin basically allows you to export an XML representation of your schema. Not very useful in itself, but if you apply an XSLT stylesheet it's possible to easily convert this into SQL CREATE TABLE statements.
The solution page mentioned above provides an SQL Server 2005 XSLT transformation sheet which seems to work quite well, although not immediately compatible with other RDBMS systems. In my case, I'm interested in forward-engineering my Visio ERD (described in MS Access Physical Data Type mode) into a PostgreSQL database instance. Luckly, modifying the XSLT to achieve compatibility is quite trivial. Took about half an hour of tweaking to get my Visio ERD imported into a working PG schema without any errors.
For anyone that's interested, the PostgreSQL XSLT I'm currently using can be downloaded from here. A few notes that you should take into account if considering using this approach:
- Design your ERD with Physical Data Type (MS Access) types (Visio 2003 Pro);
- Make sure your ERD is correct in all aspects, i.e. all relationships clearly defined with correct referential integrity rules, etc;
- Mark system generated primary keys as COUTER type, these will get converted to PGSQL BIGSERIAL.
- Likewise, MS Access LONGTEXT gets mapped to TEXT, BIT gets mapped to BOOLEAN, DATETIME to TIMESTAMP, CHAR to CHAR, VARCHAR to VARCHAR, LONG to BIGINT.
- The generated schema will include DROP TABLE IF EXISTS CASCADE at the very top such that it can be re-executed with no other manual steps required.
- The generated schema DDL will be all in one line, each command semi-colon delimited. This is so that it can be incorporated easily into a Talend script. Feel free to put in some <br/> statements after every semi-colon in the XSLT to have one command per line.