dbscript 1.04 released

The code for the latest version of dbscript has been sitting around for a while, and I did not find the time to release it until a recent bug report forced encouraged me to finally publish it. What’s more, I promised to provide an update!

So what’s new in dbscript 1.04?

Architectural Cleanup

Up to dbscript 1.03, every new supported database engine and every new object type stored their values in either base columns or object type-specific columns. The object type-specific columns are now mapped onto the same columns in the Object table (using some meta data tables, and an internal code generator which also creates the NHibernate classes).

New functionality

Dependency Diagrams (based on T-SQL analysis of views, stored procedures and functions)

namespace parameter for C# and VB.Net value scripts

Internal Cleanup

Recognize sql server 2008 and 2008R2

Multi-comparison is performed in C# rather than TSQL, reducing the chance of a TSQL timeout, and allows adding/removing project versions after a calculation has been performed.

XmlSchemaCollections are treated as types instead of objects

UI Cleanup

A couple of UI inconsistences have been fixed

Compare Object HTML-escapes correctly

Administrators can delete projects

MediaWiki login

dbscript user Mark made pointed out that dbscript does not work with current versions of MediaWiki anymore. A bit of research found that the login form adds a new hidden parameter wpLoginToken which needs to be submitted in the login POST.

dbscript 1.04 has been modified to provide this parameter, and hopefully this fix solves the issue for everybody having problems generating MediaWiki documentation of their databases.

As always, the latest version of dbscript is available for download here.

Displaying CRM 2011 Entity Customizations using XSLT

Sometimes it is necessary to extract information about customized entities and attributes in text form, e.g. to document entities or to compare two solutions.

An exported CRM solution is a Zip archive containing a couple of XML files, the most important of them being the customizations.xml.

This XML file can be extracted from the zip, and further processed by an XSLT file, such as the one I am going to describe here.

The output of this XSLT is a text file listing all customized entities, and their fields in tabular form: name, data type, required, display name, description. For clarity, tabs are shown as \t in the listing.

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" 
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="text" indent="yes"/>

The Entity template displays the entity name and invoke the attributes template for every attribute:

  <xsl:template match="ImportExportXml/Entities/Entity">
  <xsl:text>
\t</xsl:text>
Entity <xsl:value-of select="Name/."/> (<xsl:value-of 
                               select="Name/@OriginalName"/>)
  <xsl:if test="EntityInfo/entity/attributes/attribute">
    <xsl:apply-templates 
      select="EntityInfo/entity/attributes/attribute"></xsl:apply-templates>
  </xsl:if>
  </xsl:template>

The Attribute template displays the attribute’s properties. The @langcode condition needs to be adjusted to the language you want to extract from your customizations.xml.

  <xsl:template match="EntityInfo/entity/attributes/attribute">
    <xsl:text>
\t</xsl:text>
    <xsl:value-of select="@PhysicalName"/>
    <xsl:text>\t</xsl:text><xsl:value-of select="Type"/>
    <xsl:if test="MaxLength">(<xsl:value-of 
                               select="MaxLength"/>)</xsl:if>
    <xsl:text>\t</xsl:text><xsl:value-of 
                               select="RequiredLevel" ></xsl:value-of>
    <xsl:text>\t"</xsl:text>
    <xsl:value-of 
      select="displaynames/displayname[@languagecode='1031']/@description" />
    <xsl:text>"</xsl:text>
    <xsl:if test="Descriptions">
    <xsl:text>\t// </xsl:text>
      <xsl:value-of 
      select="Descriptions/Description[@languagecode='1031']/@description" />
    </xsl:if>
    <xsl:if test="optionset/options">
      <xsl:apply-templates 
        select="optionset/options/option"></xsl:apply-templates>
    </xsl:if>
  </xsl:template>

The Option template displays the option value (for bool and picklist) and its description

  <xsl:template match="optionset/options/option">
    <xsl:text>
\t\t</xsl:text>
    <xsl:value-of select="@value"/>
    <xsl:text>\t"</xsl:text>
    <xsl:value-of 
      select="labels/label[@languagecode='1031']/@description"/>
    <xsl:text>"</xsl:text>
  </xsl:template>

Ignore every other text node in the source XML:

  <xsl:template match="text()">
  </xsl:template>
</xsl:stylesheet>

Feel free to adjust the output according to your needs.

Database Changes Done Right

I love reading The Daily WTF dealing with programmers’ stories, experiences, and the occasional code nightmare. While it’s fun to read, it also makes you aware of how easily you can mistakes yourself, especially in an unfamiliar programming language, even after years of programming experience.

A recent article was titled Database Changes Done Right, and I was surprised that it was not fun or horror story, but rather a serious article on database change management, along with a couple of rules to watch when implementing database schema changes (yes, those ugly dev – test – production stages).

I also felt connected with the topic of the article, because for a couple of years now, I develop software that should help other developers ease this burden of managing database changes:

dbscript (blog, website, download) is a web application that stores database versions in repository (i.e. MSSQL database), calculates schema change scripts, stores definitions of table value initialization scripts and C# constant declarations, and keeps track of product version at customer installations. dbscript focuses on MS SQL Server databases in these areas, but also supports Oracle and PostgreSql databases for documentation purposes: document your database schema in a wiki or in a Word HTML file, or generate a data diagram with just a couple of mouse clicks.

SMOscript (blog, website, download) is a command-line tool that generates SQL scripts for all objects in a MSSQL database (tables, views, stored procedures, etc) to a single file, or to one file per object. These generates scripts can be kept in directories covered by source control systems (SVN, TFS, etc), and thus SMOscript simplifies storing the scripts for each database version in a source code repository.

oraddlscript (blog, website, download) is a command-line tool similar to SMOscript, but covering Oracle databases.

checktsql (blog, website, download) is a Windows tool that verifies all objects in a database (views, functions, stored procedures) by executing them or selecting from them (each in a transaction that will be rolled back), and reporting any errors that occurred during their invocation.

This product summary does not cover the complete functionality of each program. As programs evolve, they gain a lot of functionality, as users (including me!) add requirements and contribute ideas. Feel free to add yours!

Database Dependency Diagrams

Preparing the release of the next version of dbscript, I can proudly present you one of its new functions: to generate a Dependency Diagram of all objects contained in a database:

dependency diagram of AdventureWorks 2008 (png)

dependency diagram of AdventureWorks 2008 (dia screenshot)As usual, the diagrams can be generated in .png and .dia format.

Dependency analysis and dependency diagram generation are currently only implemented for MS SQL Server databases.

dbscript 1.03 released

dbscript, the integrated database versioning, documentation and code generation web application, has finally been migrated to .Net 3.5 (SP 1).

This makes it easier to install, as the separate installation of ASP.Net Ajax libraries is not necessary anymore.

The draw-back is that it is no longer possible to run the web application on Windows 2000 (Server), if that is a concern to anybody.

Generated Documentation

	MS SQL Server	Oracle	PostgreSql	edmx
dbscript HTML	view	view	view
dbscript Single HTML	view	view	view	view view
HTML (single file)	view	view	view	view view
MediaWiki	view	view	view	view view
ScrewTurn Wiki (V2)	view	view	view
ScrewTurn Wiki (V3)	view	view	view
Word HTML	view	view	view	view view

dbscript is available for download here.

Integrated Database Versioning and Documentation with dbscript 1.02

dbscript 1.02 adds support for edmx-based projects. If have previously written about generating documentation out of edmx files, and these documentation generators are now part of dbscript.

This is the updated table of Documentation Generators in dbscript 1.02:

Generated Documentation

	MS SQL Server	Oracle	PostgreSql	edmx
dbscript HTML	view	view	view
dbscript Single HTML	view	view	view	view view
HTML (single file)	view	view	view	view view
MediaWiki	view	view	view	view view
ScrewTurn Wiki (V2)	view	view	view
ScrewTurn Wiki (V3)	view	view	view
Word HTML	view	view	view	view view

The XSLT files on which the Documentation Generators depend for their functionality are now computed by a separate tool I wrote for this purpose, making the generated documentation more consistent throughout the different formats and databases.

Developers are free to adjust the shipped XSLT style sheets according to their layout and content needs.

Other features

• Data diagrams can now be restricted to a certain Object Selection resulting in a diagram showing only a part of the full model.
• Update notifications are implemented via JSONP
• Some fixes

dbscript is available for download here.

Generating Documentation of Entity Framework edmx Files

The upcoming version 1.02 of dbscript supports uploading, versioning and documenting Entity Framework Model Files (extension .edmx).

I have described earlier how to write XSLT files to convert the contents of edmx files into MediaWiki markup.

Follow these links to view sample output generated by dbscript:

• List of table mappings (including links to table and view documentation generated by dbscript)
• List of table/column mappings
• generated C# Entity Names

dbscript Documents

New documents are available containing product information on dbscript.

The documents can be viewed on the dbscript homepage.

Integrated Database Versioning and Documentation with dbscript 1.01

I have written about the planned features for dbscript 1.01 in recent posts, and the new version was released yesterday.

This is the updated table of Documentation Generators in dbscript 1.01:

Generated Documentation

	MS SQL Server	Oracle	PostgreSql
dbscript HTML	view
dbscript Single HTML	view
HTML (single file)	view	view	view
MediaWiki	view	view	view
ScrewTurn Wiki (V2)	view	view	view
ScrewTurn Wiki (V3)	view	view	view
Word HTML	view

The Documentation Generators in the first two lines are part of the dbscript web application (see demo links above).

This means that a developer can import a database schema (or upload a SQL DDL script) which will be stored as a version of a schema or database, define a Documentation Generator, and immediately view all information on this schema inside dbscript without requiring a third party application.

Developers are free to adjust the shipped XSLT style sheets according to their layout and content needs.

Documentation Contents

All XSLTs have been cleaned up to include information about table constraints and indexes, view indexes and triggers, and database triggers.

The information about table indexes is now displayed in separate tables, rather than inside the columns table.

Documentation also includes descriptions of database objects and columns.

Generic Projects

Users can create generic (i.e. database-independent) projects, if they just require the Versioning, UpdateNotifications and Installations features.

Other features

• Administrators can now delete a whole project with all its information in one go.
• Acknowledgements page
• Creating a Documentation Generator adds default Generator Parts for easier setup

dbscript is available for download here.

Documentation Generators in dbscript 1.01

Cleaning up the documentation generators mentioned in my previous post.

dbscript 1.01 will include generators for the formats listed in the tables below:

Generated Documentation

	MS SQL Server	Oracle	PostgreSql
dbscript HTML	✓
dbscript Single HTML	✓
HTML (single file)	view	view	view
MediaWiki	view	view	view
ScrewTurn Wiki (V2)	view	view	view
ScrewTurn Wiki (V3)	view	view	view
Word HTML	view

This table lists the XSLT style sheets shipped with dbscript.

Users are free to create their own style sheets or adapt existing style sheets for their use.

Generated Data Diagrams

	MS SQL Server	Oracle	PostgreSql
PNG	view	view	view
Dia	view	view	view

Documentation samples are based on these freely available demo projects:

MS SQL Server	AdventureWorks (2005)
Oracle	Oracle Demo Schema
PostgreSql	OpenNMS