dbscript New Version 0.99

The latest version 0.99 of dbscript has been released today providing new functionality and a couple of fixes.

Data diagrams looked a bit distorted if the data model contained circular foreign key constraints. I sketched the problem in my article on cycle detection, and the data diagram now excludes circular foreign keys in the calculation of the tables’ positions.

Comparison results can be restricted to “scopes”, such as new objects only, dropped objects only, etc. This makes it easier to generate schema migration scripts without dropping objects, for example.

Documentation Generators provide a preview to the generated content, and the generated XML now contains the project and project version identifiers to enable linking and referencing in the generator’s output.

Scripting a table in the object’s Generate/Create page now includes all constraints and indexes. (The project version script always included child objects). The same applies to object comparisons of tables, so that changes to indexes etc are easily identifiable.

New Functions

Besides generating .png data diagrams, dbscript now has the capability to generate data diagrams for Dia, an open-source diagrammer. The layout routine is the same as for png’s, but the output is Dia’s native XML format. Generating for Dia means that developers can freely layout and edit the diagram according to their needs, and export it to other formats. I described this feature earlier, and included samples.

Schema comparison is one basic feature of dbscript, and the new version compares multiple versions in one operation. After defining which schema versions to compare, you get a comparison matrix showing the number of differences between any two versions.

If the selected versions are versions of the same schema at different points of time, the comparison timeline shows each object ever changing in any of the versions, along with an indicator of the change.

Within a project, you can define Branches (as known from version control systems) and assign project versions to a branch. This alone would not be too overwhelming, but branches are a precondition of the update notification system, which I will describe in a future post.

The latest version of dbscript is available for download here.

Please leave comments and feedback.

Multi-Version Comparison – Timeline

Results of multi-version comparisons can be displayed as a matrix, where each element displays the number of differences between the versions indicated by row and column. The main diagonal remains empty.

Revised matrix, showing versions in chronological order (instead of reversed order):

If you have available the set of all comparisons, you can also pick out the most relevant (in my opinion): order the versions by date, use only the comparisons between two (chronologically) adjacent versions, and display a timeline of database object changes:

The timeline has a row for each database objects that has ever changed in any of the schemas (= project versions). Symbols in columns indicate whether an object was present in a project version (box), or has changed (create, alter, drop) between two versions.

The symbols will be hyperlinked to existing pages displaying the original information in the database (object in project version or object in comparison).

Multi-Version Comparison

If you want to compare multiple versions of a database schema, for example to get an overview of which table or view was created or dropped in which version, you need to compare each version with every other version, resulting in n*(n-1) or n*(n-1)/2 comparisons, depending on what your requirements are.

In my first steps toward multi-version comparison I created a list editor to assign all the project versions (schema versions) to be compared, and display the result in a matrix containing the number of differences.

This screenshot displays the comparison matrix of dbscript versions 0.91 through 0.98.

In case you wonder why the matrix is not symmetrical: CREATE and DROP have a different number of differences. Whereas a dropped table is a difference of 1, a created table usually also has a primary key, constraints, indexes, which add to the number of differences.

The goal of this new functionality is to also include a display similar to the two-version comparison result, but covering all versions.

It’s not very clear to me yet, so your ideas are welcome

Creating Dia Data Diagrams from Database Schema

As I stated in earlier posts, you can use dbscript to generate PNG Data Diagrams (MS SQL Server, Oracle, PostgreSQL).

The generated PNG files are intended to give you an overview of the data model. The great drawback is that the information is graphical only, as it contains no information on the original data.

Upcoming version 0.99 will also include the capability to generate Dia files representing a database schema. Dia is an open-source diagram creation program which runs on Windows and Linux.

The advantages of generating in Dia format are that it allows users / developers to modify the generated diagrams, and that general Dia functionality can be used as stated on their website:

[It] can export diagrams to a number of formats, including EPS, SVG, XFIG, WMF and PNG, and can print diagrams (including ones that span multiple pages).

There is one minor issue in generating and opening Dia files: since Dia automatically sizes Table shapes according to their contents, it is not possible to predict what the table shape size will turn out to be (see FAQ). Thus when you open a generated Dia file for the first time, the foreign key connectors will look disconnected from the tables, even though they are not (in the data).

You need to manually enforce connector layout following these steps:

• Select All (Ctrl-A)
• Move selected objects (Cursor-Left, then Cursor-Right)

which will layout the foreign key connectors as expected.

Visit the gallery to view PNG images generated by Dia from diagrams created by dbscript.

Download Dia files with database models of

• MS AdventureWorks 2005
• OpenNMS
• Oracle Demo Schema

You need to install Dia to view the contents of these files.

Version 0.98 of dbscript Released

The latest version 0.98 of dbscript supports PostgreSQL databases in its documentation generation capabilities.

After importing the database dictionary (via direct connection using ADO.Net and Npgsql) can document a PostgreSQL database in all currently supported documentation format:

MediaWiki

Data Diagram (PNG)

HTML

ScrewTurn wiki

Integration support for PostgreSQL had some consequences: More and more functionality is handled separately for each database engine.

Database import was obviously the first one, since the data access classes (SqlConnection, SqlCommand) in .Net are different for every database library. Same goes for the database dictionary, which is best retrieve from the native system catalogs.

For import and upload, data access classes have been introduced to distinguish the different object types and their properties of each database engine. I mention work on the data access classes in a series of articles already.

In version 0.98, XML generation and object script generation are implemented separately. This results in XSL style sheets being now related to certain a database engine.

For Oracle, XML and object script generation have been updated, and the XSL style sheets have been adjusted to Oracle-specific objects and properties. The results were documented earlier.

The latest version of dbscript is available for download here.

Please leave comments and feedback.

Generating Database Documentation for ScrewTurn wikis

Updated XSL style sheets for dbscript documentation generators creating ScrewTurn content.

See here for sample output:

MS SQL AdventureWorks

Oracle Demo Schema

PostgreSQL OpenNMS

ScrewTurn also allows external Page Providers (instead of stored static pages)

MS SQL AdventureWorks (by Page Provider)

Documenting Oracle Databases

I described the capabilities of dbscript to generate MediaWiki content and a single HTML page documenting an Oracle database in a previous post.

During development of PostgreSQL support (MediaWiki, HTML) it became clear that an XSL style sheet (among other things) needed to become specific to a database engine.

I therefore updated the XSL style sheets for Oracle support, and these are the results:

• MediaWiki documentation of the Oracle Demo Shema (Sales, HR)
• Single HTML file documenting the Oracle Demo Schema

Creating HTML documentation of PostgreSQL databases

dbscript ships with a couple of XSLT style sheets which transform an XML representation of a database schema into MediaWiki, HTML, or, if you create them on your own, any format you wish.

After writing the previous post on PostgreSQL support in dbscript, I fixed the XSLT for HTML generation, created a Documentation Generator in dbscript, and this is the resulting HTML documentation of the OpenNMS data model.

Creating MediaWiki Documentation of PostgreSQL Databases

After hacking away the last couple of days, dbscript finally is able to perform another trick: Importing database schemas from PostgreSQL databases using the Npgsql data provider.

Internally, the database abstraction layer is now completely based on interfaces as sketched in a previous post. Rather than asking a Database Engine object, “can you do this,  and can you do this?” giving boolean answers, we ask the object: do you implement a functionality and all the required methods, which is a simple “is” operation.

The other aspect is that the newly created data access layer simplifies handling the differences between the database engines (object types, table columns, etc).

These differences also resulted in a reference from XSLT style sheets to a Database Engine to allow more specific content creation in Document Generators.

So here are the new documentation samples, based on the data model of OpenNMS:

• MediaWiki documentation of all database schema objects in OpenNMS

The data diagram of the OpenNMS database schema:

PostgreSQL is the 3rd database engine supported by dbscript, after MS SQL Server and Oracle.

Please note that this version still needs some polish. The most current stable version is available for download here.

Managing Customers, Project Versions and Installations

If you develop software and you have a couple of customers, you will pretty soon lose track of which customer has which software version installed and when.

I now implemented a long over-due item on my todo list to manage customers, contacts, and their installation of software versions.

After you create a Customer record in dbscript, you can assign it Project Versions with shipping date and/or installation date. These Installations are visible from both the Customer record and the Project Version record.

The Project page gives you an overview of which Customer had software version installations, and which is the most current Project Version installed:

The latest version of dbscript can be downloaded here.

By the way, the current version of dbscript 0.97 also has an improved Data Diagram generator which performs better for data models with more than 100 tables.