Managing Update Notifications with dbscript

dbscript keeps track of all versions of a Project. This information can be used (and is being used) to provide update notifications to indicate newer versions of an application are available.

An application has a name and a current version number, and passes these two values to a URL which receives the list of more recent versions.

In dbscript, every project has a name, and an optional Update ID. The application name the application passes to the URL must match either of these values.

Similarly, every Project Version has a version identifier, an Update ID and an Update Message. By checking updates, only the versions’ Update ID is considered.

If there are any newer versions (determined by creation date of the version, or the explicitly assigned Version Date), the list of newer versions is returned.

dbscript also takes into account if you assigned project versions to Branches. Then the result is restricted to project versions in the same branch as the inquiring project version.

dbscript provides two URLs to handle update notification requests:

  • checkupdates.aspx returns an HTML page which can be included in a web application using an iframe
  • checkupdates_xml.ashx returns an XML document which can be parsed by the client application

(all dbscript-related terms are capitalized)

The latest version of dbscript is available for download here.

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

More Documentation Generators for dbscript

The number of documentation generators included in dbscript is growing steadily.

Version 1.00 already implemented

and fixed the

All links show documentation on the MS AdventureWorks (2005) database.

Version 1.01 further includes

The XSLT style sheets shipping with dbscript need a bit of revising too, for example, to include separate sections for table indexes and constraints which are currently not output.

dbscript 1.00 released

The major new feature of version 1.00 of dbscript is the new SQL parser with the following improvements:

  • support for SQL Server 2005 and 2008 features
  • detailed log of parser results (successfully parsed statements as well as skipped clauses and text ranges)
  • parser continues after unrecognized or faulty statements
  • interpretation of sp_addextendedproperty to extract object and column descriptions (MS_Description)
  • dependency analysis of views, stored procedures, etc.

The descriptions and dependencies extracted by parsing or importing from a live database are included in SQL scripts and generated XML files, and thus available in all generated documentation. XSL style sheets have been adapted to display object and column descriptions.

Output samples of generated documentation:

Single HTML file documentation

MediaWiki documentation

ScrewTurn Wiki documentation

The new features lay the groundwork for future versions of dbscript the implement parsers for other SQL dialects (schema import from database is already supported for Oracle and PostgreSql), dependency diagrams, and more.

dbscript is available for download here.

Please send your feedback 😉

New SQL Parser in dbscript

I wrote about my plans to add a new SQL parser engine into dbscript a couple of months ago. Now the time has come to actually implement it for T-SQL (MS SQL Server; Oracle and PostgreSQL will follow in future versions), and I found that I did not foresee all the consequences of my initial intent. The overall architecture remained the same though.

The grammar definition allows to define attributes on non-terminals. Using the SQL Server versions as attributes (2005, 2008), I can mark commands or clauses as their support or feature has been introduced in a specific version, and store the database version as property of the uploaded Project Version.

The parser skips the parts of the uploaded file that it cannot parse, and writes these parts to the upload log. Up to now, the parser would simply fail if it considered the SQL file somehow invalid.

The (C#) object representation of a parsed SQL command has a boolean flag IsHandled for each non-terminal. The code processing the object representation needs to mark every non-terminal object as being handled (i.e. translated into schema information stored in the database). Objects that have not been flagged will also be listed in the upload log.

Next, the new parser allowed me to rewrite the dependency analysis. Until now, dbscript only analyzed view dependencies to order the CREATE VIEW statements. Dependency analysis has now been extended to all database objects.

One more major issue that has been solved is parsing and interpreting EXECUTE statements. Thus an EXEC sp_addextendedproperty is interpreted as adding a description to a database object.

This obviously led to changes in the XML generation of database schemas. Each object now lists its descriptions and the dependencies on and references from other database objects.

The XSL stylesheets which translate a project version XML into markup or HTML have been revised to reflect the additional information in the generated XML.

All taken together, the new parser adds:

  • better feedback on which parts of the uploaded SQL file have been processed
  • dependency analysis
  • object and column descriptions

See the following links documenting AdventureWorks OLTP 2008 (version 2005) for the effects of the new functionality:

Single HTML file documentation

MediaWiki documentation

ScrewTurn Wiki documentation

In both wikis, compare the (old-style) “wikibot” section with the new section “automatically generated” to see the changes.

The next version of dbscript with the mythical version number “1.0” will be released soon 😉

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 😉