Yet Another Code Generator

• Download YacgenSetup - 1.1 MB
• Download YacgenSource - 1.34 MB

Introduction

What is Yet Another Code Generator (YACGEN)?
YACGEN is an template-driven code generator. Features include:

• YACGEN reads database schemas and allows for almost unlimited custom attributes to be assigned to individual objects (databases, tables and columns)
• YACGEN schemas can assign values via custom Python code executed during schema import and update
• schemas can include attributes that identify object relationships
  
• Generates output from user templates
• YACGEN templates require no code
• YACGEN is code agnostic and architecture agnostic. Output is not limited to any language, environment or platform
• Generates code from GUI or command line interface
• Designed to support multiple database schemas (Currently only Microsoft SQL Server)

This article is the first part of a series of articles on YACGEN. It covers the concept and design of YACGEN, how to import and maintain database schemas and generate output from templates. Details on template generation and maintenance will be provided in future articles.

As of writing YACGen is in Beta release. YACGen is released as an open source application. Project can be viewed at http://www.codeplex.com/YACGEN .

A article covering template creation can be found here: YACGen Template Generation

Background

Won't spend a lot of time on the merit's of code generation since so much has been written elsewhere. In short code generation can eliminate time consuming and repetitive coding and build consistent and (in theory) high quality and error free code.

I stress 'in theory' since the code generated is only as good as the templates it is based upon. However, the beauty of code generation is if there are issues with underlying templates code can be regenerated with little effort.

YACGEN requirements came out of the of how I approach most projects (and I don't think they differ from the majority of developers out there). I usually start with a relational database containing strongly defined relationships.

I would build data access through SQL stored procedures, provide supporting data access and business layers and finally a user interface. For an ASP.NET 3-tier C# application this would comprise of a 4-5 files per table. These would cover store procedure, data access, business logic and ASP.NET pages.

The requirement was to create a system that could store database schemas and application specific information. I needed to be able to quickly and easily extend the processed database schema. So for any given field I could identify if it was included in a grid, part of a sub-form or needed to be handled different, such as a picture. These attributes would not hardcoded in the schema, they are simply flags that the appropriate template would read to determine the generated output.

Ok, this sounds like a lot of other code generators available. How is YACGEN different? YACGEN:

• Provides large numbers of additional schema fields
• Allows for execution of user defined scripts against schema during database processing
  
• Generates output from templates requiring no code

Extensive Schema Support

For any given table or field object I wanted to be able to store a LOT of information but also make it easily accessible. YACGEN stores 15 user definable flags, multiple messages, evaluation logic, descriptions, code snippets and layout coordinates for any given field. If that's not enough you can add any number user defined values. This information is presented on a logically laid out form.

The following images shows the two pages of properties available for each field. The bolded items are fixed elements imported from the database layout. All the other elements can be modified, the corresponding labels are suggestions. To determine the property name hover over any text or checkbox for a few seconds.

Execute Script During Schema Processing

YACGEN can execute user defined Python code snippets during database schema processing. These code snippets can populate YACGEN schema elements.

You may encounter abnormal or legacy naming conventions you wish to change without having to modify hardcoded logic and recompile the application. For example, if you encounter tables prefixed with tbl you can easily change the logic to strip this during the schema processing.

For a given table you may have DAL objects representing an individual instance as well as a collection of these objects. Using the Northwind database Territory table as an example you would have a Territory object and a Territories collection. When the table is processed the code snippet will populate the YACGEN schema properties with the appropriate values.

For each field you would logically have a corresponding label on a form. Naturally you want to expand field descriptions into meaningful labels. So TerritoryDescription would become Territory Description. Usually you would manually update after the database schema is processed, but using the scripting approach the field populating can be automated during import.

The following image shows tables properties. The alternate names and SQL select statements were populated during the imported by script.

The Python interpreter is implemented using Microsoft IronPython engine. The reason for using this is it's very powerful, provides access applications objects and doesn't require any on-the-fly compiling. The drawback is yet another language to learn.

The Python script is located under the Table Flag Text tab under the DB Layout Settings tab. They are stored in the gpr file, the default default.gpr which is loaded at runtime.

Codeless Templates

YACGEN requires no code to generate output. One thing I noticed with a great number of code generators is the fact they require code to generate code. There isn't anything wrong with this approach, it's a powerful approach since you have access to all programming constructs and debugging facilities the underlying language provides.

Personally I find creating templates programmatically for large amounts of code, especially ASP.NET pages with large amounts of HTML, rather tedious. For such templates I would mock up the appropriate layout in Visual Studio, paste it into a YACGEN template and them make the appropriate modifications to insert field properties.

The approach taken in YACGEN is to analogous to a report generator. There are several levels, similar to report bands, that contain pre, post and body templates, like report header, body and footer. Future implementations may use code templates as well. A single template can generate large modules of code. For example, one of the included templates builds C# business layer implementing delete, select, update, insert and paging methods.

Using the Application

YACGEN is divided into 4 main areas:

Tab	Description
Template Layout	Design and template maintenance
Database Layout	Database schema processing and maintenance
Generate Layout	Generate output from specified template
DB Layout Settings	Default settings maintenance

To import database schema:

1. Select Database Layout tab.
2. Select New Database.
   Currently only Microsoft SQL servers are supported, even though you have the option of selecting MySQL or Oracle.
   
3. Enter server name or IP address of SQL database in Server Name text box.
4. Select Use NT Authentication checkbox to use your network authentication credential, otherwise enter a SQL user ID and password.
5. Select the Populate Database button.
   
6. A dialog box will appear listing available databases. Select one or more databases you wish to inventory.
   

YACGEN will proceed to inventory the selected databases. If a given database already exists in the schema YACGEN will update any changes.

Once the schema is created you can proceed updating any custom values. To save the layout, select the Save Database layout button. This will prompt for layout name. The layout is stored in a file with .dbd extension.

To generate output:

1. Select the Generate Output tab.
2. Select the .tld template file to generate code from.
   
3. Select the output directory where results will be generated.
4. Select the output file name.
   
5. Select database .dbd file. and database you wish to use.
6. Select objects to process, either all table, all tables and views or a single view or table.
7. Select the Generate Output button to generate output.

The rest of the fields are optional. Any field can contain table properties which are replaced during the generation process. In the following example the output filename and class name are specified using the <#TableAltName#> property defined in the table schema. These properties will be detailed in future articles.

The included BuildSP.gpr generation file uses the TableSPs.tld template to build SQL server stored procedure code to delete, insert, update select and paging logic for whichever table(s) you specify.

Each template contains the layout for one output file. So for a complete ASP.NET 3-tier application there would be a stored procedure, DAL, BLL and ASP.NET template file. For the ASP.NET there would be 2 templates, one for the designer code and one for the background code.

The download includes 5 sample templates:

Template	Description
TablesSPs.tld	Generates SQL server stored procedures
ControllerObjectCS.tld	BLL logic
DAObjectCS.tld	DAL logic
GridODS.tld	ASP.NET ODS grid template
GridODSCS.tld	ASP.NET ODS grid template C# background code

Using the GUI to generate logic for a large selection of table objects can be time consuming. There is a command line tool, yacgencl, that generates output. This allows for project batch building. The syntax is:

yacgencl gprfile.gpr (key1 value1, key2, value2 , keyn, valuen)

A generation template .gpr file is the only required file. The following command line would generate output based on settings in buildsp.gpr:

yacgencl "c:\program files\YACGEN\templates\buildsp.gpr" 

YACGencl can take any number of key pair values as parameters. This allows for any generation or table properties to be set from the command line during generation. The following command line would generate output changing the TableName, OutputDirectory and Process properties:

C:\Program Files\YACGEN>yacgencl "c:\program files\YACGEN\templates\buildsp.gpr"
TableName Products OutputDirectory "c:\data\vs\nwind\sps\\" Process "Single Table or View"

Future Enhancements

Possible future enhancements include improved interface, more database support and improved template processing.

History

Aug 14 2008 YACGen Beta 2 2008.08.14

- added association generation for ORM support
- create properties dynamically during table generation
- fixed bug where field null values not getting updated
- more detailed error messages
- enable conditional field updating
- add table schema to table screen
- Add database port
- set control entity logic
- build single file
- create BuildAssociations flag
- process tables that aren't part of dbo schema
- add custom control assignment
- add provider ID

July 17 2008 YACGen Beta 1 2008.07.17 Minor bug and interface fixes
July 11 2008 YACGen Beta 1 2008.07.11 Minor changes, fixes interface quirks.
July 8 2008 YACGen Beta 1.

About The Author

Stein Borge is a developer living in Australia. Love computer and related technology, working and playing with computers over 20 years. Started with Turbo Pascal and worked with xBase, Clipper, C++,VB, VBScript, C# as well a working with a host of backend technologies. Published Windows scripting books Managing Enterprise Systems and Automating Windows Administration. Currently focusing on .NET based Web development and related technologies.