dbscript 1.04 released

October 21, 2012

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

September 19, 2012

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

March 12, 2012

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

September 1, 2011

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

September 15, 2010

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

July 11, 2010

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

June 9, 2010

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:


Follow

Get every new post delivered to your Inbox.

Join 66 other followers