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
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 
Posted by devio 
Posted by devio 
Posted by devio 
