Introduction
What is Yet Another Code Generator (YACGEN)?
YACGEN is a 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. The project can be viewed here.
An article covering template creation can be found at YACGen Template Generation.
Background
I won't spend a lot of time on the merits 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 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 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 differently, such as a picture. These attributes would not be 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 of user defined values. This information is presented on a logically laid out form.
The following images show 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 table 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 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 then make the appropriate modifications to insert field properties.
The approach taken in YACGEN is 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:
- Select Database Layout tab.
- Select New Database.
Currently only Microsoft SQL servers are supported, even though you have the option of selecting MySQL or Oracle. - Enter server name or IP address of SQL database in Server Name text box.
- Select Use NT Authentication checkbox to use your network authentication credential, otherwise enter a SQL user ID and password.
- Select the Populate Database button.
- 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 with 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:
- Select the Generate Output tab.
- Select the *.tld template file to generate code from.
- Select the output directory where results will be generated.
- Select the output file name.
- Select database *.dbd file. and database you wish to use.
- Select objects to process, either all tables, all tables and views or a single view or table.
- 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 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
- Dec 22 2008 YACGen Beta 3 2008.12.22
The 2008.12.22 build changes the template layout (additional levels) so existing templates must be reloaded and saved before they can be used.
- Allow multiple root levels in templates
- Select individual tables or views for processing
- Added Python conditional expression evaluation under template layout
- Added numerous additional fields and flags to property pages
- 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 as 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.
