.NET Zone is brought to you in partnership with:

I’ve been a Windows developer since 3.0 and caught the Visual Basic wave early with v1. I’ve released a “production” application in every version of VB since then (except VB for DOS). Focusing on enterprise, line-of-business development I’ve built Call Center Applications, Mortgage finance systems, Customer Relationship Management tools and more recently I’ve been in the Litigation Support/Electronic Data Discovery/Electronically Stored Information space. Greg is a DZone MVB and is not an employee of DZone and has posted 475 posts at DZone. You can read more from them at their website. View Full User Profile

Generating SQL Railroad Diagrams

10.17.2013
| 3304 views |
  • submit to reddit

simple talk - How to get SQL Railroad Diagrams from MSDN BNF syntax notation.

On SQL Server Books-On-Line, in the Transact-SQL Reference (database Engine), every SQL Statement has its syntax represented in ‘Backus–Naur Form’ notation (BNF) syntax. For a programmer in a hurry, this should be ideal because It is the only quick way to understand and appreciate all the permutations of the syntax. It is a great feature once you get your eye in. It isn’t the only way to get the information; You can, of course, reverse-engineer an understanding of the syntax from the examples, but your understanding won’t be complete, and you’ll have wasted time doing it. BNF is a good start in representing the syntax: Oracle and SQLite go one step further, and have proper railroad diagrams for their syntax, which is a far more accessible way of doing it.

There are three problems with the BNF on MSDN. Firstly, it is isn’t a standard version of BNF, but an ancient fork from EBNF, inherited from Sybase. Secondly, it is excruciatingly difficult to understand, and thirdly it has a number of syntactic and semantic errors. The page describing DML triggers, for example, currently has the absurd BNF error that makes it state that all statements in the body of the trigger must be separated by commas. There are a few other detail problems too. Here is the offending syntax for a DML trigger, pasted from MSDN.

image

...

I’ve been trying to create railroad diagrams for all the important SQL Server SQL statements, as good as you’d find for Oracle, and have so far published the CREATE TABLE and ALTER TABLE railroad diagrams based on the BNF. Although I’ve been aware of them, I’ve never realised until recently how many errors there are. Then, Colin Daley created a translator for the SQL Server dialect of BNF which outputs standard EBNF notation used by the W3C. The example MSDN BNF for the trigger would be rendered as …

...

Colin’s intention was to allow anyone to paste SQL Server’s BNF notation into his website-based parser, and from this generate classic railroad diagrams via Gunther Rademacher's Railroad Diagram Generator.  Colin's application does this for you: you're not aware that you are moving to a different site.  Because Colin's 'translator' it is a parser, it will pick up syntax errors. Once you’ve fixed the syntax errors, you will get the syntax in the form of a human-readable railroad diagram and, in this form, the semantic mistakes become flamingly obvious.

Gunter’s Railroad Diagram Generator is brilliant. To be able, after correcting the MSDN dialect of BNF, to generate a standard EBNF, and from thence to create railroad diagrams for SQL Server’s syntax that are as good as Oracle’s, is a great boon, and many thanks to Colin for the idea. Here is the result of the W3C EBNF from Colin’s application then being run through the Railroad diagram generator.

image

Now that’s much better, you’ll agree. This is pretty easy to understand, and at this point any error is immediately obvious.

This should be seriously useful, and it is to me. However there is that snag. The BNF is generally incorrect, and you can’t expect the average visitor to mess about with it.

The answer is, of course, to correct the BNF on MSDN and maybe even add railroad diagrams for the syntax. Stop giggling! I agree it won’t happen. In the meantime, we need to collaboratively store and publish these corrected syntaxes ourselves as we do them. How? GitHub? SQL Server Central?  Simple-Talk? What should those of us who use the system do with our corrected EBNF so that anyone can use them without hassle?

Grammar Translator

If you are familiar with the Grammar Translator, go ahead and create railroad diagrams from the Transact-SQL Reference. Otherwise, please see the FAQ. In particular, be sure to try thetutorial.

image

image

Welcome to Railroad Diagram Generator!

This is a tool for creating syntax diagrams, also known as railroad diagrams, from context-free grammars specified in EBNF. Syntax diagrams have been used for decades now, so the concept is well-known, and some tools for diagram generation are in existence. The features of this one are

  • usage of the W3C's EBNF notation,
  • web-scraping of grammars from W3C specifications,
  • online editing of grammars,
  • diagram presentation in SVG,
  • and it was completely written in web languages (XQueryXHTMLCSSJavaScript).

image

There's nothing like a diagram to help grok something (and the MSDN BNF SQL stuff really makes my brain hurt...)

Published at DZone with permission of Greg Duncan, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Comments

Christopher Deckers replied on Sat, 2013/10/19 - 2:11pm

Hi Greg,

In case you want to produce those railroad diagrams offline (without using a website), I created a free Java library called RRDiagram that produces such diagrams. I actually used Gunther's output as an insipiration so it is very similar, and it also allows more configuration options including in the format of the BNF.

Cheers,
-Christopher

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.