Nate's Stuff

I have decided to open source a project that I have been working off and on since 2003.  I have always been a big fan of code generation and felt like I knew what it takes to do it right. iGen is actually the second separate attempt.  My original attempt is named SmartDAL and is also included in the downloads below.

What is iGen?

iGen is simply a fancy XSLT processing engine.  It works by having 2-3 main concepts.

1. XML Provider - The XML provider is responsible for providing XML to the XSLT templates.  Currently it has Oracle provider and SQL Server provider.  Each of these will generate SQL that will be used in the XSLT transformation. 
2. XSLT Transformations - The XSLT transformations are a set of files that will transform the XML that is generated from the XML Provider.  The transformations also use a standard set of variables and accepts custom variables.
3. File Splitter - The result of an XSLT transformation results in a single file only.  If you wish for multiple files as the output then you need to use the built-in splitter. 

This is the main screen.  The top portion is our XML Providers.  You can see that both SQL Server and Oracle are there.  This is where you configure which tables, proc's, and database relationships will be used to generate the XML data.  It may be desirable to leave out certain tables or proc's so you can choose which ones you wish to include. 

The options screen is used to setup project workspaces.  The idea behind this concept is that ever developer has their favorite place they like to develop code.  I like to have a D:\Projects\[Client]\[Project] structure.  Others use C:\Source.  Whatever the path it's nice to keep those different and still be able to generate code!  We will use  these workspaces in later screens.

This next screen shot is of the XSLT settings dialog.  Let me go through the controls one by one.

1. Settings Description - This is simply the text that will be displayed in the parent dialog.  Generally a short description of what you this template does is best.
2. XSLT Template - This is the location of the template you wish to process.  Notice that there is a drop-down box and a partial path.  This is part of the workspaces mentioned above.  Of course the path does not need to be part of the workspace provided. This should always be a valid XSLT transformation.
3. Data Provider - This selects which XML Data Provider will be used for this transformation.  Because the XML that is generated does not have to follow any specific schema you are required to select a provider that is compatible with the XSLT.
4. Filename Extension - This gives the user the ability to name the file output.  If you are generating a single file then type the entire file name here.  If you are generating multiple files then put a suffix if desired and the filename extension.  In the example above the files will end with "_INSERT.sql".  The rest of the filename is generated by the XSLT transformation.
5. Workspace / Path - This is the output directory for the transformation.  Again, you can see that we are using a different workspace than defined above.  If you browse to a directory that is inside of the selected workspace path then you will only see a partial path in this box.  Otherwise you will see a full path.
6. Parameter Window - This is where you can add custom parameters for your XSLT.  These parameters can be found inside of the XSLT defined at the top of the file.  These parameters can be optional but it really depends on how the XSLT was written.

You press the generate button and away we go.  You will see what is happening and how long until it finishes.  When you are done you may choose to save your settings (after all it can be a lot of work setting these templates up). 

This is an XSLT template.  You can see from the screen shot (params highlighted in yellow) that this transformation expects 5 params.  ProcPrefix, FileNameExtension, DatabaseSchema, DatabaseName, and DatabaseUserName (in no particular order).   The FileNameExtension parameter is provided by the XSLT properties window in the "Filename Extension" fields.  The rest are custom parameters and the XSLT generation engine will not know if they are required or not.  As you can see from the screen shots above I have defined all but the "ProcPrefix".  This template is designed to make ProcPrefix optional.  It will run just fine without it.  But for those who like all of their stored procs to start with "proc_" or "sp" then they may choose to set a value for this parameter in the Parameter window in the XSLT properties dialog. 

Note also the use of "#[<xsl:value-of select="@name" /><xsl:value-of select="$filenameextension" />]#" in the example.  The #[ and ]# are used by the file splitter.  The value inside of the tokens is used as the filename.  Ex: #[proc_USERS_INSERT.sql]#

If you wish to create a custom XML Provider you simply need to implement the correct interface and place the DLL in the application directory during startup. 

Known Issues:

• The Oracle XML provider is a little more advanced than the SQL XML provider.  The oracle provider will provide relationship information that the SQL provider does not yet.
• The Oracle provider is dog slow!  This is actually a performance issue with querying relationship information from an Oracle database.  For those who have to use this, I am sorry!  If it makes you feel better the XML data is cached once it is created so if you goof up on the first run the second one will be much faster.
• The generated classes are somewhat geared toward an Oracle Smart-Client application.  These templates and classes were all "perfected" with the Oracle client.  I much prefer to use Microsoft SQL Server 2005/2008 but sometimes you don't get to choose!  If you wish to modify the templates to work for SQL Server it probably isn't a lot of work.
• .tmp files are left over after generation. These are the original results from the transformation.  They can be useful for debugging.
• There is a "lib" directory inside of the templates directory.  This is an open source XSLT function library.  We use it to mainly make sure the case of our objects is what we need it to be.

Screen Shot of SmartDAL:

I hope you enjoy it!  Please drop me a line if you download it and if you are using it.

Downloads:

• iGen Source
• iGen Runtime / Binary
• SmartDAL Source / Binary

Thanks!
Nathan Zaugg

Credits
I wasn't alone in working on iGen.  I had a lot of help from the team that used it.  Bret Cutler, Richard Brower, and Garth Harris.  An awesome team -- I would love to work with these guys on another project!

• Open up a new query window
• Execute the command:
  • ALTER AUTHORIZATION ON Database::[MY_DATABASE_NAME] TO [MY_USER_NAME]
    • Replace [MY_DATABASE_NAME] with the name of your database
    • Replace [MY_USER_NAME] with a valid user in your system but one that does not yet exist in the restored database
    • Example:  ALTER AUTHORIZATION ON Database::CorporateDB TO sa

I hope this helps someone!  It's not as easy to change as I think it should be!

Nathan Zaugg